const pool = require("../../../../managers/dbConfig");
const { getFormattedDateTime, transformDateTimeField } = require("../../../../managers/formatDateTime");

class Lab {
  // ✅ Fetch all available lab tests
  static async getAllTests() {
    return pool.execute("SELECT * FROM lab_tests ORDER BY test_name");
  }

  // ✅ Fetch available lab tests filters by category
  static async getTestsByCategory(test_category) {
    return pool.execute(
      "SELECT * FROM lab_tests WHERE test_category = ? ORDER BY test_name",
      [test_category]
    );
  }

  // ✅ Request a lab test for a patient
  static async requestTest(patient_id, doctor_id, test_name) {
    return pool.execute(
      "INSERT INTO lab_requests (patient_id, doctor_id, test_name) VALUES (?, ?, ?)",
      [patient_id, doctor_id, test_name]
    );
  }

  // ✅ Fetch all test requests for a patient
  static async getPatientLabRequests(patient_id) {
    return pool.execute(
      `SELECT lr.*, lt.test_name 
       FROM lab_requests lr 
       JOIN lab_tests lt ON lr.test_id = lt.id 
       WHERE lr.patient_id = ? 
       ORDER BY lr.date_time DESC`,
      [patient_id]
    );
  }
  
  static async getPendingLabRequests() {
    return pool.execute(
      `SELECT *
       FROM lab_requests lr 
 
       WHERE lr.status= "pending"
       ORDER BY lr.date_time DESC`
    );
  }

  // *************************************** Fetch Lab Results with Dynamic Filters ************************************//
  static async getLabResults({
    branch_id,
    patient_id,
    fetch_date,
    start_date,
    end_date,
    tested_by,
    requested_by,
    limit,
    offset,
  }) {
    let query = `
      SELECT lr.*, lt.test_name, lt.category_id, lres.result_text, lres.result_file, 
             lres.tested_by, lres.date_time AS tested_at, lr.requested_by, lr.date_time AS requested_at
      FROM lab_results lres
      JOIN lab_requests lr ON lres.request_id = lr.id
      JOIN lab_tests lt ON lr.test_id = lt.id
      WHERE lr.branch_id = ?
    `;

    const params = [branch_id];

    if (patient_id) {
      query += " AND lr.patient_id = ?";
      params.push(patient_id);
    }

    if (fetch_date) {
      query += " AND DATE(lres.date_time) = ?";
      params.push(fetch_date);
    }

    if (start_date && end_date) {
      query += " AND lres.date_time BETWEEN ? AND ?";
      params.push(start_date, end_date);
    }

    if (tested_by) {
      query += " AND lres.tested_by = ?";
      params.push(tested_by);
    }

    if (requested_by) {
      query += " AND lr.requested_by = ?";
      params.push(requested_by);
    }

    query += " ORDER BY lres.date_time DESC LIMIT ? OFFSET ?";
    params.push(limit, offset);

    return pool.execute(query, params);
  }

  // ✅ Update the status of a lab test request
  static async updateTestStatus(request_id, status) {
    return pool.execute("UPDATE lab_requests SET status = ? WHERE id = ?", [
      status,
      request_id,
    ]);
  }

  // ✅ Add new lab test
  static async addLabTest(test_name, description, cost, test_category) {
    return pool.execute(
      "INSERT INTO lab_tests (test_name, description, cost, test_category) VALUES (?, ?, ?, ?)",
      [test_name, description, cost, test_category]
    );
  }

  // ✅ Upload lab test result
  static async uploadLabResult(
    request_id,
    result_text,
    result_file,
    tested_by
  ) {
    return pool.execute(
      "INSERT INTO lab_results (request_id, result_text, result_file, tested_by) VALUES (?, ?, ?, ?)",
      [request_id, result_text, result_file, tested_by]
    );
  }

  // ✅ Fetch test results for a patient
  static async getLatestPatientLabResults(patient_id) {
    return pool.execute(
      `SELECT lr.*, lt.test_name, lres.result_text, lres.result_file, lres.date_time 
       FROM lab_results lres 
       JOIN lab_requests lr ON lres.request_id = lr.id
       JOIN lab_tests lt ON lr.test_id = lt.id 
       WHERE lr.patient_id = ? 
       ORDER BY lres.date_time DESC`,
      [patient_id]
    );
  }

  // ✅ Fetch current test results for a patient
  static async getLatestPatientLabResults(patient_id) {
    const today = getFormattedDateTime();
    return pool.execute(
      `SELECT lr.*, lt.test_name, lres.result_text, lres.result_file, lres.date_time 
       FROM lab_results lres 
       JOIN lab_requests lr ON lres.request_id = lr.id
       JOIN lab_tests lt ON lr.test_id = lt.id 
       WHERE lr.patient_id = ? AND DATE(date_time) =?
       ORDER BY lres.date_time DESC
       LIMIT 1
       `,
      [patient_id, today]
    );
  }
}

module.exports = Lab;
