const QualityCheck = require("../../models/securityops/qcModel");
const { pool } = require("../../config/db");

exports.updateQCParameter = async (sampleId, parameterId, value, result) => {
  const qc = await QualityCheck.findOne({ sampleId });

  const parameter = qc.parameters.find(
    (p) => p.parameterId.toString() === parameterId,
  );

  if (!parameter) {
    throw new Error("Parameter not found");
  }

  parameter.history.push({
    value,
    result,
  });

  await qc.save();

  return qc;
};

// exports.getParameterHistory = async (sampleId) => {
//   const qc = await QualityCheck.findOne({
//     sampleId: sampleId.toUpperCase(), // avoid case issues
//     isDeleted: false,
//   })
//     .populate("approvedBy", "firstName email")
//     .populate("parameters.history.testedBy", "firstName")
//     // IMPORTANT
//     // .lean();
//     .lean();

//   if (!qc) return [];

//   const historyRows = [];

//   qc.parameters.forEach((param) => {
//     if (param.history && param.history.length > 0) {
//       param.history.forEach((item) => {
//         historyRows.push({
//           parameterName: param.parameterName,
//           value: item.value || "-",
//           tempStatus: item.tempStatus,
//           // result: item.tempStatus,
//           testedAt: item.testedAt,
//           // testedBy: param.approvedBy,
//           testedBy: item.testedBy?.firstName || qc.approvedBy?.firstName || "-",
//         });
//       });
//     }
//   });

//   // latest history first
//   historyRows.sort((a, b) => new Date(b.testedAt) - new Date(a.testedAt));

//   return historyRows;
// };

// exports.getParameterHistory = async (sampleId) => {
//   const query = `
//     SELECT 
//       h.created_at AS "testedAt",
//       STRING_AGG(h.parameter_name, ', ') AS "parameterName",
//       STRING_AGG(h.value::text, ', ') AS "value",
//       MAX(h.temp_status) AS "tempStatus",
//          MAX(u.first_name || ' ' || u.last_name) AS "createdBy"
//     FROM quality_checks q
//     JOIN qc_parameter_history h 
//       ON q.id = h.qc_id
//        LEFT JOIN users u 
//     ON u.id = h.created_by
//     WHERE q.sample_id = $1
//     GROUP BY h.created_at
//     ORDER BY h.created_at DESC
//   `;

//   const result = await pool.query(query, [sampleId]);

//   return result.rows;
// };

exports.getParameterHistory = async (sampleId) => {
  const query = `
    SELECT 
      h.created_at AS "testedAt",
      h.item_code AS "itemCode",
      JSON_AGG(
        JSON_BUILD_OBJECT(
          'parameterName', h.parameter_name,
          'value', h.value
        )
      ) AS "parameters",
      MAX(h.temp_status) AS "tempStatus",
      MAX(u.first_name || ' ' || u.last_name) AS "createdBy"
    FROM quality_checks q
    JOIN qc_parameter_history h 
      ON q.id = h.qc_id
    LEFT JOIN users u 
      ON u.id = h.created_by
    WHERE q.sample_id = $1
    GROUP BY h.created_at, h.item_code
    ORDER BY h.created_at DESC;
  `;

  const result = await pool.query(query, [sampleId]);

  return result.rows;
};
