const validator = require("validator");
const {
  transformDateTimeField,
} = require("../../../../managers/formatDateTime");
const db = require("../../../../managers/dbConfig");
const { decryptRecords } = require("../../../../managers/decryption");

const logger = require("../../../../handlers/logger");
const { determineType } = require("../../../../managers/regex");

// 3rd version of getRxHx function
const getRxHx = async (req, res) => {
  let { id, hmo, limit, offset, page, start_date, end_date } = req.pagination;
  let rxData;
  // Ensure determineType is imported or defined in scope
  let supplyData = determineType(id);

  // Base SQL query includes the join to patients table for name
  let sql = `SELECT tx.*, patients.firstName, patients.lastName, patients.gender, patients.age
    FROM tx
    LEFT JOIN patients ON tx.patient_id = patients.patient_id`;
  let sqlParams = []; // Initialize as empty, parameters will be added conditionally

  // Variables to hold the initial WHERE clause and its parameter
  let initialWhereClause = "";
  let initialWhereParam = null;

  if (id) {
    if (supplyData === "Patient Phone Number" || supplyData === "Patient ID") {
      // If it's a Patient ID or Phone Number, validate and get the patient_id
      const sql1 = `SELECT patient_id, phone_num FROM patients WHERE patient_id=? OR phone_num=?`;
      const [validID] = await db.execute(sql1, [id, id]);

      if (validID.length === 0) {
        throw "INCORRECT Patient ID or Phone Number";
      }
      // Set the initial WHERE clause to filter by patient_id
      initialWhereClause = `tx.patient_id=?`;
      initialWhereParam = validID[0]["patient_id"]; // Use the resolved patient_id
    } else if (supplyData === "Session ID") {
      // For Session ID, we need to validate its existence and get the patient_id
      // associated with it for the JOIN to work correctly.
      const sql1 = `SELECT patient_id FROM tx WHERE session_id=?`;
      const [validSession] = await db.execute(sql1, [id]); // 'id' here is the session_id

      if (validSession.length === 0) {
        throw "INCORRECT Session ID";
      }
      // Set the initial WHERE clause to filter directly by session_id
      initialWhereClause = `tx.session_id=?`;
      initialWhereParam = id; // Use the original session_id for the main query
    } else {
      // If 'id' is provided but doesn't match any known type
      throw "Invalid ID format provided.";
    }

    // Append the initial WHERE clause and its parameter if an ID was provided
    sql += ` WHERE ${initialWhereClause}`;
    sqlParams.push(initialWhereParam);
  }

  // Add HMO filtering
  if (hmo) {
    // If an initial WHERE clause was already added (due to 'id'), use AND. Otherwise, use WHERE.
    if (!sql.includes("WHERE")) {
      sql += ` WHERE tx.bill_to LIKE ? `;
    } else {
      sql += ` AND tx.bill_to LIKE ? `;
    }
    sqlParams.push(`%${hmo}%`);
  }

  // Add Date Filtering
  if (start_date && end_date) {
    // If a WHERE clause was already added, use AND. Otherwise, use WHERE.
    if (!sql.includes("WHERE")) {
      sql += ` WHERE DATE(tx.date_time) BETWEEN ? AND ? `;
    } else {
      sql += ` AND DATE(tx.date_time) BETWEEN ? AND ? `;
    }
    sqlParams.push(start_date, end_date);
  }

  // Add ORDER BY, LIMIT, and OFFSET
  sql += ` ORDER BY tx.id DESC LIMIT ? OFFSET ?`;
  sqlParams.push(limit, offset);

  // console.log("Final SQL:", sql);
  // console.log("Final SQL Params:", sqlParams);

  [rxData] = await db.execute(sql, sqlParams);
  // console.log("rxData", rxData);

  const sanitizedDateTime = transformDateTimeField(rxData);

  const encryptedData = [
    "c_o",
    "hx",
    "drug_hx",
    "o_e",
    "lab_inv",
    "diag",
    "rx",
    "dr_note",
  ];
  const decryptedData = decryptRecords(
    sanitizedDateTime,
    process.env.enc_key,
    encryptedData
  );
  // console.log(rxData);

  res.json({
    page,
    data: decryptedData,
  });
};
// 2nd version of getRxHx function
// const getRxHx = async (req, res) => {
//   let { id, hmo, limit, offset, page, start_date, end_date } = req.pagination;
//   let rxData;
//   let supplyData = determineType(id); // determineType needs to be imported/defined
//   console.log("supplyData", supplyData);

//   // Initialize sql and sqlParams outside the main 'if (id)' block
//   // The 'WHERE tx.patient_id=?' part will be conditionally added.
//   let sql = `SELECT tx.*, patients.firstName, patients.lastName, patients.gender, patients.age
//     FROM tx
//     LEFT JOIN patients ON tx.patient_id = patients.patient_id`;
//   let sqlParams = []; // Initialize as empty, will push parameters as needed

//   if (id) {
//     // Determine the actual patient_id based on the input 'id' type
//     if (supplyData === "Patient Phone Number" || supplyData === "Patient ID") {
//       const sql1 = `SELECT patient_id, phone_num FROM patients WHERE patient_id=? OR phone_num=?`;
//       const [validID] = await db.execute(sql1, [id, id]);

//       if (validID.length === 0) {
//         throw "INCORRECT Patient ID or Phone Number";
//       }
//       id = validID[0]["patient_id"]; // Reassign 'id' to the actual patient_id
//     } else if (supplyData === "Session ID") {
//       console.log("Session ID Detected");
//       const sql1 = `SELECT patient_id FROM tx WHERE session_id=?`;
//       const [validSession] = await db.execute(sql1, [id]);

//       if (validSession.length === 0) {
//         throw "INCORRECT Session ID";
//       }
//       id = validSession[0]["patient_id"]; // Reassign 'id' to the actual patient_id
//       console.log("Valid Patient ID:", id);
//     } else {
//       // If 'id' is provided but doesn't match any known type, it's an invalid ID.
//       throw "Invalid ID format provided.";
//     }

//     // Now that 'id' definitively holds the patient_id, add the WHERE clause and its parameter
//     sql += ` WHERE tx.patient_id=?`;
//     sqlParams.push(id); // Push the now-correct patient_id
//   }

//   // Add HMO filtering
//   if (hmo) {
//     // If 'id' was not provided, and hmo is, we need a WHERE clause.
//     // If 'id' was provided, the WHERE clause for patient_id is already added.
//     if (!id && !sql.includes("WHERE")) {
//       // Check if WHERE clause is not already present
//       sql += ` WHERE tx.bill_to LIKE ? `;
//     } else {
//       sql += ` AND tx.bill_to LIKE ? `;
//     }
//     sqlParams.push(`%${hmo}%`);
//   }

//   // Add Date Filtering
//   if (start_date && end_date) {
//     // Similar logic for WHERE/AND based on whether a WHERE clause exists
//     if (!id && !hmo && !sql.includes("WHERE")) {
//       // Check if WHERE clause is not already present
//       sql += ` WHERE DATE(tx.date_time) BETWEEN ? AND ? `;
//     } else {
//       sql += ` AND DATE(tx.date_time) BETWEEN ? AND ? `;
//     }
//     sqlParams.push(start_date, end_date);
//   }

//   sql += ` ORDER BY tx.id DESC LIMIT ? OFFSET ?`;
//   sqlParams.push(limit, offset);

//   // console.log("Final SQL:", sql);
//   // console.log("Final SQL Params:", sqlParams);

//   [rxData] = await db.execute(sql, sqlParams);
//   // console.log("rxData", rxData);

//   const sanitizedDateTime = transformDateTimeField(rxData);

//   const encryptedData = [
//     "c_o",
//     "hx",
//     "drug_hx",
//     "o_e",
//     "lab_inv",
//     "diag",
//     "rx",
//     "dr_note",
//   ];
//   const decryptedData = decryptRecords(
//     sanitizedDateTime,
//     process.env.enc_key,
//     encryptedData
//   );
//   // console.log(rxData);

//   res.json({
//     page,
//     data: decryptedData,
//   });
// };
// 1st version of getRxHx function
// const getRxHx = async (req, res) => {
//   let { id, hmo, limit, offset, page, start_date, end_date } = req.pagination;
//   let rxData;
//   let supplyData = determineType(id);
//   console.log("supplyData", supplyData);

//   let sql = `SELECT tx.*, patients.firstName, patients.lastName, patients.gender, patients.age
//     FROM tx
//     LEFT JOIN patients ON tx.patient_id = patients.patient_id
//     WHERE tx.patient_id=?
//     `;

//   if (id) {
//     let sqlParams = [id];
//     if (supplyData === "Patient Phone Number" || supplyData === "Patient ID") {
//       const sql1 = `SELECT patient_id, phone_num FROM patients WHERE patient_id=? OR phone_num=?`;
//       const [validID] = await db.execute(sql1, [id, id]);

//       if (validID.length === 0) throw "INCORRECT Patient ID or Phone Number";
//       id = validID[0]["patient_id"];
//     }
//     if (supplyData === "Session ID") {
//       console.log("Session ID Detected");
//       const sql1 = `SELECT patient_id FROM tx WHERE session_id=?`;
//       const [validSession] = await db.execute(sql1, [id]);

//       if (validSession.length === 0) throw "INCORRECT Session ID";
//       id = validSession[0]["patient_id"];
//       console.log("Valid Patient ID:", id);
//     }

//     if (hmo) {
//       sql += ` AND tx.bill_to LIKE ? `;
//       sqlParams.push(`%${hmo}%`);
//     }

//     // Add Date Filtering
//     if (start_date && end_date) {
//       sql += ` AND DATE(tx.date_time) BETWEEN ? AND ? `;
//       sqlParams.push(start_date, end_date);
//     }

//     sql += `ORDER BY tx.id DESC LIMIT ? OFFSET ?`;
//     sqlParams.push(limit, offset);
//     // console.log("sqlParams", sqlParams);
//     // console.log("sql", sql);

//     [rxData] = await db.execute(sql, sqlParams);
//     // console.log("rxData", rxData);
//   } else {
//     let sql = `SELECT tx.*, patients.firstName, patients.lastName, patients.gender, patients.age
//     FROM tx
//     LEFT JOIN patients ON tx.patient_id = patients.patient_id
//     `;

//     let sqlParams = [];

//     // Add Date Filtering
//     if (start_date && end_date) {
//       sql += ` WHERE DATE(tx.date_time) BETWEEN ? AND ? `;
//       sqlParams.push(start_date, end_date);
//     }

//     sql += `ORDER BY tx.id DESC LIMIT ? OFFSET ?`;
//     sqlParams.push(limit, offset);

//     [rxData] = await db.execute(sql, sqlParams);
//   }

//   const sanitizedDateTime = transformDateTimeField(rxData);

//   const encryptedData = [
//     "c_o",
//     "hx",
//     "drug_hx",
//     "o_e",
//     "lab_inv",
//     "diag",
//     "rx",
//     "dr_note",
//   ];
//   const decryptedData = decryptRecords(
//     sanitizedDateTime,
//     process.env.enc_key,
//     encryptedData
//   );
//   // console.log(rxData);

//   res.json({
//     page,
//     data: decryptedData,
//   });
// };

module.exports = { getRxHx };
