const { pool } = require("../../config/db");

// const createQCParam = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const { parameterName, value } = req.body;

//     // 1. Validation
//     if (!parameterName || !parameterName.trim()) {
//       return res.status(400).json({
//         success: false,
//         message: "Parameter name is required",
//       });
//     }

//     const trimmedName = parameterName.trim();

//     // 2. Check duplicate (ignore soft deleted)
//     const existing = await client.query(
//       `
//       SELECT id
//       FROM qc_params
//       WHERE LOWER(parameter_name) = LOWER($1)
//         AND is_deleted = false
//       `,
//       [trimmedName],
//     );

//     if (existing.rows.length > 0) {
//       return res.status(400).json({
//         success: false,
//         message: "Parameter already exists",
//       });
//     }

//     // 3. Insert
//     const insertQuery = `
//       INSERT INTO qc_params (
//         parameter_name,
//         value,
//         created_at,
//         updated_at
//       )
//       VALUES ($1, $2, NOW(), NOW())
//       RETURNING *
//     `;

//     // created_by
//     const result = await client.query(insertQuery, [
//       trimmedName,
//       value || null,
//       //   req.session?.user?.id || null,
//     ]);

//     const param = result.rows[0];

//     // 4. Response (match Mongo format)
//     return res.status(201).json({
//       success: true,
//       message: "Parameter created successfully",
//       data: {
//         _id: param.id,
//         parameterName: param.parameter_name,
//         value: param.value,
//         isDeleted: param.is_deleted,
//         createdBy: param.created_by,
//         updatedBy: param.updated_by,
//         createdAt: param.created_at,
//         updatedAt: param.updated_at,
//       },
//     });
//   } catch (error) {
//     console.error("Create QC Param Error:", error);

//     return res.status(500).json({
//       success: false,
//       message: "Failed to create parameter",
//     });
//   } finally {
//     client.release();
//   }
// };


// 28-5-26
// const createQCParam = async (req, res) => {
//   const client = await pool.connect();

//   const userId = req.session.user.id;
//   const org_id = req.session.user.orgId;
//   try {
//     const { item_id, item_name, item_code, sub_type, parameters } = req.body;

//     // 🔹 1. Validation
//     if (!item_id) {
//       return res.status(400).json({
//         success: false,
//         message: "Item is required",
//       });
//     }

//     if (!item_name || !item_name.trim()) {
//       return res.status(400).json({
//         success: false,
//         message: "Item name is required",
//       });
//     }

//     // if (!sub_type || !sub_type.trim()) {
//     //   return res.status(400).json({
//     //     success: false,
//     //     message: "Sub type is required",
//     //   });
//     // }

//     if (!Array.isArray(parameters) || parameters.length === 0) {
//       return res.status(400).json({
//         success: false,
//         message: "At least one parameter is required",
//       });
//     }

//     // 🔹 2. Clean Parameters
//     const cleanedParams = parameters.map((p, index) => {
//       if (!p.name || !p.name.trim()) {
//         throw new Error(`Parameter name missing at row ${index + 1}`);
//       }

//       // if (p.min === "" || p.max === "") {
//       //   throw new Error(`Min/Max required at row ${index + 1}`);
//       // }

//       if (Number(p.min) > Number(p.max)) {
//         throw new Error(`Min cannot be greater than Max at row ${index + 1}`);
//       }

//       return {
//         name: p.name.trim(),
//         type: p.type || "number",
//         unit: p.unit || null,
//         min: Number(p.min),
//         max: Number(p.max),
//       };
//     });

//     const subType = sub_type.trim();
//     const itemName = item_name.trim();

//     await client.query("BEGIN");

//     // 🔹 3. Duplicate Check (USE item_id, NOT NAME)
//     const existing = await client.query(
//       `
//       SELECT id
//       FROM qc_params
//       WHERE item_id = $1
//         AND LOWER(sub_type) = LOWER($2)
//         AND is_deleted = false
//       `,
//       [item_id, subType],
//     );

//     if (existing.rows.length > 0) {
//       await client.query("ROLLBACK");

//       return res.status(400).json({
//         success: false,
//         message: "Template already exists for this item & subtype",
//       });
//     }

//     // 🔹 4. Insert
//     const insertQuery = `
//       INSERT INTO qc_params (
//         item_id,
//         item_name,
//         item_code,
//         sub_type,
//         parameters,
//         org_id,
//         created_by,
//         created_at,
//         updated_at
//       )
//       VALUES ($1, $2, $3, $4, $5,$6,$7, NOW(), NOW())
//       RETURNING *
//     `;

//     const result = await client.query(insertQuery, [
//       item_id,
//       itemName,
//       item_code || null,
//       subType,
//       JSON.stringify(cleanedParams),
//       org_id,
//       userId,
//     ]);

//     await client.query("COMMIT");

//     const param = result.rows[0];

//     // 🔹 5. Response
//     return res.status(201).json({
//       success: true,
//       message: "QC template created successfully",
//       data: {
//         id: param.id,
//         itemId: param.item_id,
//         itemName: param.item_name,
//         subType: param.sub_type,
//         parameters: param.parameters,
//         createdBy: param.created_by,
//         createdAt: param.created_at,
//       },
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");

//     console.error("Create QC Param Error:", error);

//     return res.status(500).json({
//       success: false,
//       message: error.message || "Failed to create QC template",
//     });
//   } finally {
//     client.release();
//   }
// };




const createQCParam = async (req, res) => {
  const client = await pool.connect();

  try {
    const userId = req.session.user.id;
    const org_id = req.session.user.orgId;

    const {
      item_id,
      item_name,
      item_code,
      sub_type,
      parameters,
    } = req.body;

    /* ====================================================== */
    /* VALIDATION */
    /* ====================================================== */

    if (!item_id) {
      return res.status(400).json({
        success: false,
        message: "Item is required",
      });
    }

    if (!item_name || !item_name.trim()) {
      return res.status(400).json({
        success: false,
        message: "Item name is required",
      });
    }

    if (
      !Array.isArray(parameters) ||
      parameters.length === 0
    ) {
      return res.status(400).json({
        success: false,
        message: "At least one parameter is required",
      });
    }

    /* ====================================================== */
    /* CLEAN PARAMETERS */
    /* ====================================================== */

    const cleanedParams = parameters.map((p, index) => {
      /* ------------------------------ */
      /* PARAMETER NAME */
      /* ------------------------------ */

      if (!p.name || !p.name.trim()) {
        throw new Error(
          `Parameter name missing at row ${index + 1}`
        );
      }

      /* ------------------------------ */
      /* CONDITION */
      /* ------------------------------ */

      const allowedConditions = [
        "below",
        "above",
        "between",
      ];

      if (
        !p.condition ||
        !allowedConditions.includes(p.condition)
      ) {
        throw new Error(
          `Invalid condition at row ${index + 1}`
        );
      }

      /* ------------------------------ */
      /* PARSE VALUES */
      /* ------------------------------ */

      const specMin =
        p.specMin !== "" &&
        p.specMin !== null &&
        p.specMin !== undefined
          ? Number(p.specMin)
          : null;

      const specMax =
        p.specMax !== "" &&
        p.specMax !== null &&
        p.specMax !== undefined
          ? Number(p.specMax)
          : null;

      const penaltyMin =
        p.penaltyMin !== "" &&
        p.penaltyMin !== null &&
        p.penaltyMin !== undefined
          ? Number(p.penaltyMin)
          : null;

      const penaltyMax =
        p.penaltyMax !== "" &&
        p.penaltyMax !== null &&
        p.penaltyMax !== undefined
          ? Number(p.penaltyMax)
          : null;

      /* ------------------------------ */
      /* CONDITION VALIDATION */
      /* ------------------------------ */

      if (p.condition === "below") {
        if (specMax === null) {
          throw new Error(
            `Standard Max required at row ${index + 1}`
          );
        }
      }

      // if (p.condition === "above") {
      //   if (specMin === null) {
      //     throw new Error(
      //       `Standard Min required at row ${index + 1}`
      //     );
      //   }
      // }

      if (p.condition === "between") {
        if (specMin === null || specMax === null) {
          throw new Error(
            `Standard Min & Max required at row ${index + 1}`
          );
        }

        if (specMin > specMax) {
          throw new Error(
            `Standard Min cannot be greater than Max at row ${
              index + 1
            }`
          );
        }
      }

      /* ------------------------------ */
      /* RETURN CLEAN OBJECT */
      /* ------------------------------ */

      return {
        name: p.name.trim(),

        type: "number",

        unit: p.unit?.trim() || null,

        condition: p.condition,

        specMin,

        specMax,

        penaltyMin,

        penaltyMax,
      };
    });

    /* ====================================================== */
    /* CLEAN VALUES */
    /* ====================================================== */

    const itemName = item_name.trim();

    const subType = sub_type?.trim() || "";

    await client.query("BEGIN");

    /* ====================================================== */
    /* DUPLICATE CHECK */
    /* ====================================================== */

    const existing = await client.query(
      `
      SELECT id
      FROM qc_params
      WHERE item_id = $1
      AND LOWER(COALESCE(sub_type, '')) = LOWER($2)
      AND is_deleted = false
      `,
      [item_id, subType]
    );

    if (existing.rows.length > 0) {
      await client.query("ROLLBACK");

      return res.status(400).json({
        success: false,
        message:
          "QC template already exists for this item & subtype",
      });
    }

    /* ====================================================== */
    /* INSERT */
    /* ====================================================== */

    const insertQuery = `
      INSERT INTO qc_params (
        item_id,
        item_name,
        item_code,
        sub_type,
        parameters,
        org_id,
        created_by,
        created_at,
        updated_at
      )
      VALUES (
        $1,
        $2,
        $3,
        $4,
        $5,
        $6,
        $7,
        NOW(),
        NOW()
      )
      RETURNING *
    `;

    const result = await client.query(insertQuery, [
      item_id,
      itemName,
      item_code || null,
      subType,
      JSON.stringify(cleanedParams),
      org_id,
      userId,
    ]);

    await client.query("COMMIT");

    const param = result.rows[0];

    /* ====================================================== */
    /* RESPONSE */
    /* ====================================================== */

    return res.status(201).json({
      success: true,
      message: "QC template created successfully",
      data: {
        id: param.id,
        itemId: param.item_id,
        itemName: param.item_name,
        itemCode: param.item_code,
        subType: param.sub_type,
        parameters: param.parameters,
        createdBy: param.created_by,
        createdAt: param.created_at,
      },
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error(
      "CREATE QC PARAM ERROR:",
      error
    );

    return res.status(500).json({
      success: false,
      message:
        error.message ||
        "Failed to create QC template",
    });
  } finally {
    client.release();
  }
};


// const getQCParams = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const {
//       page = 1,
//       limit = 10,
//       search = "",
//       sortBy = "created_at",
//       order = "desc",
//     } = req.query;

//     const pageNumber = parseInt(page, 10);
//     const pageSize = parseInt(limit, 10);
//     const offset = (pageNumber - 1) * pageSize;

//     const sortOrder = order.toLowerCase() === "asc" ? "ASC" : "DESC";

//     // SAFE SORTING
//     const allowedSortFields = [
//       "created_at",
//       "updated_at",
//       "parameter_name",
//       "value",
//     ];

//     const sortColumn = allowedSortFields.includes(sortBy)
//       ? sortBy
//       : "created_at";

//     // Base filter
//     let whereClause = `WHERE q.is_deleted IS NOT TRUE`;
//     let values = [];
//     let paramIndex = 1;

//     // Search filter
//     if (search) {
//       whereClause += ` AND (
//         q.parameter_name ILIKE $${paramIndex}
//       )`;
//       values.push(`%${search}%`);
//       paramIndex++;
//     }

//     // Count query
//     const countQuery = `
//       SELECT COUNT(*) AS total
//       FROM qc_params q
//       ${whereClause}
//     `;

//     const countResult = await client.query(countQuery, values);
//     const totalRecords = parseInt(countResult.rows[0].total, 10);

//     // Data query
//     const dataQuery = `
//       SELECT q.*
//       FROM qc_params q
//       ${whereClause}
//       ORDER BY q.${sortColumn} ${sortOrder}
//       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
//     `;

//     values.push(pageSize, offset);

//     const result = await client.query(dataQuery, values);

//     return res.status(200).json({
//       success: true,
//       message: "Parameters fetched successfully",
//       data: result.rows,
//       pagination: {
//         totalRecords,
//         currentPage: pageNumber,
//         totalPages: Math.ceil(totalRecords / pageSize),
//         pageSize,
//       },
//     });
//   } catch (error) {
//     console.error("Error fetching QC Params:", error.message);
//     return res.status(500).json({
//       success: false,
//       message: error.message,
//     });
//   } finally {
//     client.release();
//   }
// };

const getQCParams = async (req, res) => {
  const client = await pool.connect();

  try {
    const {
      page = 1,
      limit = 10,
      search = "",
      sortBy = "created_at",
      order = "desc",
    } = req.query;

    const pageNumber = parseInt(page, 10);
    const pageSize = parseInt(limit, 10);
    const offset = (pageNumber - 1) * pageSize;

    const sortOrder = order.toLowerCase() === "asc" ? "ASC" : "DESC";

    const allowedSortFields = [
      "created_at",
      "updated_at",
      "item_name",
      "sub_type",
    ];

    const sortColumn = allowedSortFields.includes(sortBy)
      ? sortBy
      : "created_at";

    let whereClause = `WHERE qp.is_deleted IS NOT TRUE`;
    let values = [];
    let paramIndex = 1;

    if (search) {
      whereClause += ` AND (
        qp.item_name ILIKE $${paramIndex}
        OR qp.sub_type ILIKE $${paramIndex}
      )`;
      values.push(`%${search}%`);
      paramIndex++;
    }

    // 🔢 Count Query (NO JOIN needed)
    const countQuery = `
      SELECT COUNT(*) AS total
      FROM qc_params qp
      ${whereClause}
    `;

    const countResult = await client.query(countQuery, values);
    const totalRecords = parseInt(countResult.rows[0].total, 10);

    // 📦 Data Query WITH JOIN
    const dataQuery = `
      SELECT 
        qp.id,
        qp.item_id,
        qp.item_name,
         qp.item_code,
        qp.sub_type,
        qp.parameters,
        qp.created_at,
        qp.updated_at,
        u.name AS created_by
      FROM qc_params qp
      LEFT JOIN users u 
        ON u.id = qp.created_by
      ${whereClause}
      ORDER BY qp.${sortColumn} ${sortOrder}
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
    `;

    values.push(pageSize, offset);

    const result = await client.query(dataQuery, values);

    return res.json({
      success: true,
      data: result.rows,
      pagination: {
        totalRecords,
        currentPage: pageNumber,
        totalPages: Math.ceil(totalRecords / pageSize),
        pageSize,
      },
    });
  } catch (err) {
    console.error("Get QC Params Error:", err);

    return res.status(500).json({
      success: false,
      message: "Failed to fetch QC templates",
    });
  } finally {
    client.release();
  }
};

// const getQCParamsByMaterial = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const { itemId } = req.params;
//     const org_id = req.session?.user?.orgId;

//     if (!itemId) {
//       return res.status(400).json({
//         success: false,
//         message: "Item ID is required",
//       });
//     }

//     const query = `
//       SELECT
//         id,
//         item_id,
//         item_name,
//         sub_type,
//         parameters,
//         created_at
//       FROM qc_params
//       WHERE
//         is_deleted IS NOT TRUE
//         AND item_id = $1
//         AND org_id = $2
//       ORDER BY created_at DESC
//     `;

//     const result = await client.query(query, [itemId, org_id]);

//     return res.status(200).json({
//       success: true,
//       count: result.rows.length,
//       data: result.rows,
//     });

//   } catch (error) {
//     console.error("QC PARAMS FETCH ERROR:", error);

//     return res.status(500).json({
//       success: false,
//       message: "Failed to fetch QC parameters",
//     });

//   } finally {
//     client.release();
//   }
// };

// const updateQCParam = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const { id } = req.params;
//     const { parameter_name, value } = req.body;

//     if (!parameter_name) {
//       return res.status(400).json({
//         success: false,
//         message: "Parameter name is required",
//       });
//     }

//     const trimmedName = parameter_name.trim();

//     // 1. Check if record exists
//     const checkQuery = `
//       SELECT * FROM qc_params
//       WHERE id = $1 AND is_deleted IS NOT TRUE
//     `;
//     const checkResult = await client.query(checkQuery, [id]);

//     if (checkResult.rowCount === 0) {
//       return res.status(404).json({
//         success: false,
//         message: "Parameter not found",
//       });
//     }

//     // 2. Check duplicate (exclude current id)
//     const duplicateQuery = `
//       SELECT id FROM qc_params
//       WHERE parameter_name = $1
//       AND id != $2
//       AND is_deleted IS NOT TRUE
//     `;
//     const duplicateResult = await client.query(duplicateQuery, [
//       trimmedName,
//       id,
//     ]);

//     if (duplicateResult.rowCount > 0) {
//       return res.status(400).json({
//         success: false,
//         message: "Parameter already exists",
//       });
//     }

//     // 3. Update query
//     const updateQuery = `
//       UPDATE qc_params
//       SET
//         parameter_name = $1,
//         value = $2,

//         updated_at = NOW()
//       WHERE id = $3
//       RETURNING *
//     `;

//     //   updated_by = $3,

//     // const updatedBy = req.sessoin.user?.id || null;

//     const updateResult = await client.query(updateQuery, [
//       trimmedName,
//       value || null,
//       //   updatedBy,
//       id,
//     ]);

//     return res.status(200).json({
//       success: true,
//       message: "Parameter updated successfully",
//       data: updateResult.rows[0],
//     });
//   } catch (error) {
//     console.error("Error updating QC Param:", error);
//     return res.status(500).json({
//       success: false,
//       message: "Internal server error",
//     });
//   } finally {
//     client.release();
//   }
// };
const getQCParamsByMaterial = async (req, res) => {
  const client = await pool.connect();

  try {
    const { itemId, itemCode } = req.params;
    const org_id = req.session?.user?.orgId;

    if (!org_id) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized",
      });
    }

    // if (!itemCode) {
    //   return res.status(400).json({
    //     success: false,
    //     message: "Item ID or Item Code is required",
    //   });
    // }

    if (!req.query.item_codes) {
  return res.status(400).json({
    success: false,
    message: "item_codes or item_id is required",
  });
}

    let query = `
      SELECT 
        id,
        item_id,
        item_code,
        item_name,
        sub_type,
        parameters,
        created_at
      FROM qc_params
      WHERE 
        is_deleted IS NOT TRUE
        AND org_id = $1
    `;

    let values = [org_id];

    // 🔥 Dynamic filter
    // if (itemCode) {
    //   query += ` AND item_code = $2`;
    //   values.push(itemCode);
    // }
   if (req.query.item_codes) {
  const codes = req.query.item_codes.split(",");

  query += ` AND item_code = ANY($2)`;
  values.push(codes);

} else if (req.query.item_id) {
  query += ` AND item_id = $2`;
  values.push(req.query.item_id);
}

    query += ` ORDER BY created_at DESC`;

    const result = await client.query(query, values);

    return res.status(200).json({
      success: true,
      count: result.rows.length,
      data: result.rows,
    });
  } catch (error) {
    console.error("QC PARAMS FETCH ERROR:", error);

    return res.status(500).json({
      success: false,
      message: "Failed to fetch QC parameters",
    });
  } finally {
    client.release();
  }
};

// const updateQCParam = async (req, res) => {
//   const client = await pool.connect();

//   try {
//     const { id } = req.params;
//     const { item_id, item_name, item_code, sub_type, parameters } = req.body;

//     const userId = req.session?.user?.id;
//     const org_id = req.session?.user?.orgId;

//     if (!userId || !org_id) {
//       return res.status(401).json({
//         success: false,
//         message: "Unauthorized",
//       });
//     }

//     // 🔹 Validation
//     if (!item_id) {
//       return res.status(400).json({
//         success: false,
//         message: "Item is required",
//       });
//     }

//     if (!item_name || !item_name.trim()) {
//       return res.status(400).json({
//         success: false,
//         message: "Item name is required",
//       });
//     }

//     // if (!sub_type || !sub_type.trim()) {
//     //   return res.status(400).json({
//     //     success: false,
//     //     message: "Sub type is required",
//     //   });
//     // }

//     if (!Array.isArray(parameters) || parameters.length === 0) {
//       return res.status(400).json({
//         success: false,
//         message: "At least one parameter is required",
//       });
//     }

//     // 🔹 Clean Parameters
//     const cleanedParams = parameters.map((p, index) => {
//       if (!p.name || !p.name.trim()) {
//         throw new Error(`Parameter name missing at row ${index + 1}`);
//       }

//       return {
//         name: p.name.trim(),
//         type: p.type || "number",
//         unit: p.unit || null,
//         min: p.min !== "" ? Number(p.min) : null,
//         max: p.max !== "" ? Number(p.max) : null,
//       };
//     });

//     await client.query("BEGIN");

//     // 🔹 Check Exists
//     const check = await client.query(
//       `SELECT id FROM qc_params 
//        WHERE id = $1 AND org_id = $2 AND is_deleted IS NOT TRUE`,
//       [id, org_id],
//     );

//     if (check.rowCount === 0) {
//       await client.query("ROLLBACK");
//       return res.status(404).json({
//         success: false,
//         message: "Template not found",
//       });
//     }

//     // 🔹 Duplicate Check (important)
//     const duplicate = await client.query(
//       `SELECT id FROM qc_params
//        WHERE item_id = $1 
//          AND LOWER(sub_type) = LOWER($2)
//          AND id != $3
//          AND is_deleted = false`,
//       [item_id, sub_type.trim(), id],
//     );

//     if (duplicate.rows.length > 0) {
//       await client.query("ROLLBACK");
//       return res.status(400).json({
//         success: false,
//         message: "Template already exists for this item & subtype",
//       });
//     }

//     // 🔹 Update
//     const result = await client.query(
//       `
//       UPDATE qc_params
//       SET 
//         item_id = $1,
//         item_name = $2,
//         item_code = $3,
//         sub_type = $4,
//         parameters = $5,
//         updated_at = NOW(),
//         updated_by = $6
//       WHERE id = $7
//       RETURNING *
//       `,
//       [
//         item_id,
//         item_name.trim(),
//         item_code || null,
//         sub_type.trim(),
//         JSON.stringify(cleanedParams),
//         userId,
//         id,
//       ],
//     );

//     await client.query("COMMIT");

//     return res.json({
//       success: true,
//       message: "QC template updated successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");

//     console.error("Update QC Param Error:", error);

//     return res.status(500).json({
//       success: false,
//       message: error.message || "Failed to update QC template",
//     });
//   } finally {
//     client.release();
//   }
// };

const updateQCParam = async (req, res) => {
  const client = await pool.connect();

  try {
    const { id } = req.params;

    const {
      item_id,
      item_name,
      item_code,
      sub_type,
      parameters,
    } = req.body;

    const userId = req.session?.user?.id;

    const org_id = req.session?.user?.orgId;

    // =========================
    // AUTH VALIDATION
    // =========================

    if (!userId || !org_id) {
      return res.status(401).json({
        success: false,
        message: "Unauthorized access",
      });
    }

    // =========================
    // ID VALIDATION
    // =========================

    if (!id || isNaN(id)) {
      return res.status(400).json({
        success: false,
        message: "Invalid template id",
      });
    }

    // =========================
    // BASIC VALIDATION
    // =========================

    if (!item_id) {
      return res.status(400).json({
        success: false,
        message: "Item is required",
      });
    }

    if (!item_name?.trim()) {
      return res.status(400).json({
        success: false,
        message: "Item name is required",
      });
    }

    if (
      !Array.isArray(parameters) ||
      parameters.length === 0
    ) {
      return res.status(400).json({
        success: false,
        message:
          "At least one parameter is required",
      });
    }

    // =========================
    // CLEAN PARAMETERS
    // =========================

    const cleanedParams = parameters.map(
      (p, index) => {
        const name = p.name?.trim();

        if (!name) {
          throw new Error(
            `Parameter name missing at row ${
              index + 1
            }`
          );
        }

        const condition =
          p.condition || "below";

        const specMin =
          p.specMin !== "" &&
          p.specMin !== null &&
          p.specMin !== undefined
            ? Number(p.specMin)
            : null;

        const specMax =
          p.specMax !== "" &&
          p.specMax !== null &&
          p.specMax !== undefined
            ? Number(p.specMax)
            : null;

        const penaltyMin =
          p.penaltyMin !== "" &&
          p.penaltyMin !== null &&
          p.penaltyMin !== undefined
            ? Number(p.penaltyMin)
            : null;

        const penaltyMax =
          p.penaltyMax !== "" &&
          p.penaltyMax !== null &&
          p.penaltyMax !== undefined
            ? Number(p.penaltyMax)
            : null;

        // =========================
        // NUMBER VALIDATION
        // =========================

        if (
          specMin !== null &&
          isNaN(specMin)
        ) {
          throw new Error(
            `Invalid Std Min at row ${
              index + 1
            }`
          );
        }

        if (
          specMax !== null &&
          isNaN(specMax)
        ) {
          throw new Error(
            `Invalid Std Max at row ${
              index + 1
            }`
          );
        }

        if (
          penaltyMin !== null &&
          isNaN(penaltyMin)
        ) {
          throw new Error(
            `Invalid Penalty Min at row ${
              index + 1
            }`
          );
        }

        if (
          penaltyMax !== null &&
          isNaN(penaltyMax)
        ) {
          throw new Error(
            `Invalid Penalty Max at row ${
              index + 1
            }`
          );
        }

        // =========================
        // CONDITION VALIDATION
        // =========================

        if (
          condition === "below" &&
          specMax === null
        ) {
          throw new Error(
            `Std Max required at row ${
              index + 1
            }`
          );
        }

        // if (
        //   condition === "above" &&
        //   specMin === null
        // ) {
        //   throw new Error(
        //     `Std Min required at row ${
        //       index + 1
        //     }`
        //   );
        // }

        // if (
        //   condition === "between" &&
        //   (specMin === null ||
        //     specMax === null)
        // ) {
        //   throw new Error(
        //     `Std Min & Std Max required at row ${
        //       index + 1
        //     }`
        //   );
        // }

        // =========================
        // RANGE VALIDATION
        // =========================

        if (
          specMin !== null &&
          specMax !== null &&
          specMin > specMax
        ) {
          throw new Error(
            `Std Min cannot be greater than Std Max at row ${
              index + 1
            }`
          );
        }

        // if (
        //   penaltyMin !== null &&
        //   penaltyMax !== null &&
        //   penaltyMin > penaltyMax
        // ) {
        //   throw new Error(
        //     `Penalty Min cannot be greater than Penalty Max at row ${
        //       index + 1
        //     }`
        //   );
        // }

        return {
          name,
          unit: p.unit?.trim() || "%",
          condition,

          specMin,
          specMax,

          penaltyMin,
          penaltyMax,
        };
      }
    );

    await client.query("BEGIN");

    // =========================
    // CHECK TEMPLATE EXISTS
    // =========================

    const existingTemplate =
      await client.query(
        `
        SELECT id
        FROM qc_params
        WHERE id = $1
        AND org_id = $2
        AND is_deleted = false
        `,
        [id, org_id]
      );

    if (
      existingTemplate.rowCount === 0
    ) {
      await client.query("ROLLBACK");

      return res.status(404).json({
        success: false,
        message:
          "QC template not found",
      });
    }

    // =========================
    // DUPLICATE CHECK
    // =========================

    const duplicateCheck =
      await client.query(
        `
        SELECT id
        FROM qc_params
        WHERE item_id = $1
        AND COALESCE(
          LOWER(TRIM(sub_type)),
          ''
        ) =
        COALESCE(
          LOWER(TRIM($2)),
          ''
        )
        AND id != $3
        AND org_id = $4
        AND is_deleted = false
        `,
        [
          item_id,
          sub_type || "",
          id,
          org_id,
        ]
      );

    if (
      duplicateCheck.rows.length > 0
    ) {
      await client.query("ROLLBACK");

      return res.status(400).json({
        success: false,
        message:
          "Template already exists for this product & subtype",
      });
    }

    // =========================
    // UPDATE TEMPLATE
    // =========================

    const updateResult =
      await client.query(
        `
        UPDATE qc_params
        SET
          item_id = $1,
          item_name = $2,
          item_code = $3,
          sub_type = $4,
          parameters = $5,
          updated_at = NOW(),
          updated_by = $6
        WHERE id = $7
        RETURNING *
        `,
        [
          item_id,

          item_name.trim(),

          item_code?.trim() || null,

          sub_type?.trim() || null,

          JSON.stringify(
            cleanedParams
          ),

          userId,

          id,
        ]
      );

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message:
        "QC template updated successfully",
      data: updateResult.rows[0],
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error(
      "UPDATE QC PARAM ERROR:",
      error
    );

    return res.status(500).json({
      success: false,
      message:
        error.message ||
        "Failed to update QC template",
    });
  } finally {
    client.release();
  }
};

module.exports = {
  createQCParam,
  getQCParams,
  updateQCParam,
  getQCParamsByMaterial,
};
