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

// const transporter = nodemailer.createTransport({
//       host: "127.0.0.1",
//       port: 25,
//       secure: false,
//       ignoreTLS: true,
//       auth: {
//         user: process.env.MAIL_USER,   // noreply@vivekbioservices.in
//         pass: process.env.MAIL_PASS,   // your cpanel email password
//       },
//       tls: {
//         rejectUnauthorized: false,
//       }
//     });

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

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

//     if (!orgId) {
//       return res.status(401).json({ message: "Unauthorized" });
//     }

//     const {
//       contract_no,
//       customer_code,
//       product_id,
//       contract_qty,
//       rate,
//       valid_from,
//       valid_to,
//       delivery_terms,
//       govt_allocation_ref,
//       remarks
//     } = req.body;

//     //  Basic validations
//     if (
//       !contract_no ||
//       !product_id ||
//       !contract_qty ||
//       !rate ||
//       !valid_from ||
//       !valid_to
//     ) {
//       return res.status(400).json({ message: "Missing required fields" });
//     }

//     if (new Date(valid_to) < new Date(valid_from)) {
//       return res
//         .status(400)
//         .json({ message: "valid_to must be greater than valid_from" });
//     }

//     if (contract_qty <= 0 || rate <= 0) {
//       return res
//         .status(400)
//         .json({ message: "Quantity and rate must be greater than 0" });
//     }

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

//     // Check duplicate contract_no
//     const duplicateCheck = await client.query(
//       `SELECT id FROM sales_contracts WHERE contract_no = $1`,
//       [contract_no]
//     );

//     if (duplicateCheck.rows.length > 0) {
//       await client.query("ROLLBACK");
//       return res
//         .status(409)
//         .json({ message: "Contract number already exists" });
//     }

//     // Insert contract
//     const insertQuery = `
//       INSERT INTO sales_contracts (
//         contract_no,
//         customer_code,
//         product_id,
//         contract_qty,
//         rate,
//         valid_from,
//         valid_to,
//         delivery_terms,
//         govt_allocation_ref,
//         status,
//         uom,
//         org_id,
//         remarks,
//         created_by
//       )
//       VALUES (
//         $1,$2,$3,$4,$5,$6,$7,$8,$9,
//         'ACTIVE','KL',$10,$11,$12
//       )
//       RETURNING *;
//     `;

//     const values = [
//       contract_no,
//       customer_code,
//       product_id,
//       contract_qty,
//       rate,
//       valid_from,
//       valid_to,
//       delivery_terms || null,
//       govt_allocation_ref || null,
//       orgId,
//       remarks || null,
//       userId
//     ];

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

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

//     return res.status(201).json({
//       message: "Contract created successfully",
//       data: result.rows[0]
//     });

//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("Create Contract Error:", error);

//     return res.status(500).json({
//       message: "Internal server error"
//     });
//   } finally {
//     client.release();
//   }
// };

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

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

//     if (!orgId) {
//       return res.status(401).json({ message: "Unauthorized" });
//     }

//     const {
//       contract_title,
//       contract_type,
//       customer_code,
//       product_id,
//       contract_qty,
//       rate,
//       valid_from,
//       valid_to,
//       delivery_terms,
//       govt_allocation_ref,
//       remarks,
//     } = req.body;

//     //  validations
//     if (!product_id || !contract_qty || !rate || !valid_from || !valid_to) {
//       return res.status(400).json({ message: "Missing required fields" });
//     }

//     if (new Date(valid_to) < new Date(valid_from)) {
//       return res.status(400).json({
//         message: "valid_to must be greater than valid_from",
//       });
//     }

//     if (contract_qty <= 0 || rate <= 0) {
//       return res.status(400).json({
//         message: "Quantity and rate must be greater than 0",
//       });
//     }

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

//     // 🔥 Generate contract_no (safe + locked)
//     const prefix = "C-";

//     const lastRes = await client.query(
//       `SELECT contract_no
//        FROM sales_contracts
//        WHERE org_id = $1 AND contract_no LIKE $2
//        ORDER BY id DESC
//        LIMIT 1
//        FOR UPDATE`,
//       [orgId, `${prefix}%`],
//     );

//     let nextNumber = 1;

//     if (lastRes.rows.length > 0) {
//       const lastCode = lastRes.rows[0].contract_no; // C-001
//       const lastNum = parseInt(lastCode.replace(prefix, ""), 10);

//       if (!isNaN(lastNum)) {
//         nextNumber = lastNum + 1;
//       }
//     }

//     const contract_no = `${prefix}${String(nextNumber).padStart(3, "0")}`;

//     //  Correct INSERT (aligned placeholders)
//     const insertQuery = `
//       INSERT INTO sales_contracts (
//         contract_title,
//         contract_type,
//         contract_no,
//         customer_code,
//         product_id,
//         contract_qty,
//         rate,
//         valid_from,
//         valid_to,
//         delivery_terms,
//         govt_allocation_ref,
//         status,
//         uom,
//         org_id,
//         remarks,
//         created_by
//       )
//       VALUES (
//         $1,  -- contract_title
//         $2,  -- contract_type
//         $3,  -- contract_no
//         $4,  -- customer_code
//         $5,  -- product_id
//         $6,  -- contract_qty
//         $7,  -- rate
//         $8,  -- valid_from
//         $9,  -- valid_to
//         $10, -- delivery_terms
//         $11, -- govt_allocation_ref
//         'ACTIVE',
//         'KL',
//         $12, -- org_id
//         $13, -- remarks
//         $14  -- created_by
//       )
//       RETURNING *;
//     `;

//     const values = [
//       contract_title || null,
//       contract_type || null,
//       contract_no,
//       customer_code || null,
//       product_id,
//       contract_qty,
//       rate,
//       valid_from,
//       valid_to,
//       delivery_terms || null,
//       govt_allocation_ref || null,
//       orgId,
//       remarks || null,
//       userId,
//     ];

//     //  Step 1: Insert main contract
//     const result = await client.query(insertQuery, values);

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

//     //  Step 2: Insert into history
//     await client.query(
//       `INSERT INTO sales_contract_history (
//     contract_id,
//     contract_no,
//     customer_code,
//     product_id,
//     contract_qty,
//     rate,
//     valid_from,
//     valid_to,
//     delivery_terms,
//     govt_allocation_ref,
//     status,
//     uom,
//     org_id,
//     remarks,
//     action_type,
//     action_by
//   )
//   VALUES (
//     $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,'CREATED',$15
//   )`,
//       [
//         contract.id,
//         contract.contract_no,
//         contract.customer_code,
//         contract.product_id,
//         contract.contract_qty,
//         contract.rate,
//         contract.valid_from,
//         contract.valid_to,
//         contract.delivery_terms,
//         contract.govt_allocation_ref,
//         contract.status,
//         contract.uom,
//         contract.org_id,
//         contract.remarks,
//         userId,
//       ],
//     );

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

//     return res.status(201).json({
//       message: "Contract created successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");

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

//     if (error.code === "23505") {
//       return res.status(409).json({
//         message: "Duplicate contract number, please retry",
//       });
//     }

//     return res.status(500).json({
//       message: "Internal server error",
//     });
//   } finally {
//     client.release();
//   }
// };

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

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

    if (!orgId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    const {
      contract_title,
      contract_type,
      customer_code,
      product_id,
      contract_qty,
      rate,
      valid_from,
      valid_to,
      delivery_terms,
      govt_allocation_ref,
      remarks,
    } = req.body;

    // ✅ validations
    if (!product_id || !contract_qty || !rate || !valid_from || !valid_to) {
      return res.status(400).json({ message: "Missing required fields" });
    }

    if (new Date(valid_to) < new Date(valid_from)) {
      return res.status(400).json({
        message: "valid_to must be greater than valid_from",
      });
    }

    if (contract_qty <= 0 || rate <= 0) {
      return res.status(400).json({
        message: "Quantity and rate must be greater than 0",
      });
    }

    await client.query("BEGIN");

    /* ================= CONTRACT NUMBER ================= */

    const prefix = "C-";

    const lastRes = await client.query(
      `SELECT contract_no 
       FROM sales_contracts
       WHERE org_id = $1 AND contract_no LIKE $2
       ORDER BY id DESC
       LIMIT 1
       FOR UPDATE`,
      [orgId, `${prefix}%`],
    );

    let nextNumber = 1;

    if (lastRes.rows.length > 0) {
      const lastNum = parseInt(
        lastRes.rows[0].contract_no.replace(prefix, ""),
        10,
      );
      if (!isNaN(lastNum)) nextNumber = lastNum + 1;
    }

    const contract_no = `${prefix}${String(nextNumber).padStart(3, "0")}`;

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

    const result = await client.query(
      `
      INSERT INTO sales_contracts (
        contract_title, contract_type, contract_no, customer_code,
        product_id, contract_qty, rate, valid_from, valid_to,
        delivery_terms, govt_allocation_ref, status, uom,
        org_id, remarks, created_by
      )
      VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,'ACTIVE','KL',$12,$13,$14
      )
      RETURNING *;
      `,
      [
        contract_title || null,
        contract_type || null,
        contract_no,
        customer_code || null,
        product_id,
        contract_qty,
        rate,
        valid_from,
        valid_to,
        delivery_terms || null,
        govt_allocation_ref || null,
        orgId,
        remarks || null,
        userId,
      ],
    );

    const contract = result.rows[0];

    /* ================= HISTORY ================= */

    await client.query(
      `INSERT INTO sales_contract_history (
        contract_id, contract_no, customer_code, product_id,
        contract_qty, rate, valid_from, valid_to,
        delivery_terms, govt_allocation_ref, status, uom,
        org_id, remarks, action_type, action_by
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,'CREATED',$15)
      `,
      [
        contract.id,
        contract.contract_no,
        contract.customer_code,
        contract.product_id,
        contract.contract_qty,
        contract.rate,
        contract.valid_from,
        contract.valid_to,
        contract.delivery_terms,
        contract.govt_allocation_ref,
        contract.status,
        contract.uom,
        contract.org_id,
        contract.remarks,
        userId,
      ],
    );

    /* ================= FETCH CUSTOMER EMAIL ================= */

    const custRes = await client.query(
      `SELECT company_name, email 
       FROM customers 
       WHERE customer_code = $1`,
      [customer_code],
    );

    const customer = custRes.rows[0];
    const customerName = customer?.company_name || "Customer";

    const productRes = await client.query(
  `SELECT item_name 
   FROM procurement_itemmaster 
   WHERE id = $1`,
  [product_id]
);

const product = productRes.rows[0];
const productName = product?.item_name || "N/A";

    await client.query("COMMIT");

    /* ================= SEND EMAIL (AFTER COMMIT) ================= */

    if (customer?.email) {
      try {
        await transporter.sendMail({
          from: `"Sales Team" <${process.env.MAIL_USER}>`,
          to: customer.email,
          subject: `New Contract ${contract.contract_no} Created`,
          //   html: `
          //     <div style="font-family: Arial, sans-serif; max-width: 700px;">

          //       <p>Dear <strong>${customer.company_name}</strong>,</p>

          //       <p>
          //         We are pleased to inform you that a new contract
          //         <strong>${contract.contract_no}</strong> has been created.
          //       </p>

          //       <div style="background:#f5f7fa;padding:15px;border-radius:6px;margin:15px 0;">
          //         <p><strong>Contract Details:</strong></p>
          //         <p>Title: ${contract.contract_title || "-"}</p>
          //         <p>Type: ${contract.contract_type || "-"}</p>
          //         <p>Quantity: ${contract.contract_qty}</p>
          //         <p>Rate: ₹ ${contract.rate}</p>
          //         <p>Validity: ${contract.valid_from} → ${contract.valid_to}</p>
          //       </div>

          //       <p>
          //         Please review the contract details and proceed accordingly.
          //       </p>

          //       <p>Regards,<br/><strong>Sales Team</strong></p>

          //       <hr/>
          //       <p style="font-size:12px;color:#777">
          //         This is an automated notification.
          //       </p>
          //     </div>
          //   `,
          html: `
<div style="font-family: Arial, sans-serif; background:#f4f6f8; padding:20px;">
  <table width="100%" cellpadding="0" cellspacing="0" style="max-width:700px;margin:auto;background:#ffffff;border-radius:8px;overflow:hidden;border:1px solid #e5e7eb;">

    <!-- HEADER -->
    <tr style="background:#1f2937;color:#ffffff;">
      <td style="padding:16px 20px;font-size:18px;font-weight:600;">
        Contract Notification
      </td>
    </tr>

    <!-- BODY -->
    <tr>
      <td style="padding:20px; color:#374151; font-size:14px; line-height:1.6;">
        
        <p>Dear <strong>${customer.company_name}</strong>,</p>

        <p>
          We are pleased to inform you that a new contract 
          <strong>${contract.contract_no}</strong> has been successfully created.
        </p>

        <!-- CONTRACT DETAILS TABLE -->
        <table width="100%" cellpadding="0" cellspacing="0" style="margin-top:15px;border-collapse:collapse;border:1px solid #e5e7eb;">
          
          <tr style="background:#2563eb;">
            <td colspan="2" style="padding:10px;font-weight:600;border-bottom:1px solid #e5e7eb;">
              Contract Details
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">Title</td>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${contract.contract_title || "-"}
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">Type</td>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${contract.contract_type || "-"}
            </td>
          </tr>

          <tr>
  <td style="padding:8px;border-bottom:1px solid #e5e7eb;">Product</td>
  <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
   
      ${productName} ${contract.uom ? `(${contract.uom})` : ""}
  </td>
</tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">Quantity</td>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${contract.contract_qty}
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">Rate</td>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ₹ ${Number(contract.rate).toFixed(2)}
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">Validity</td>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${new Date(contract.valid_from).toLocaleDateString()} 
              → 
              ${new Date(contract.valid_to).toLocaleDateString()}
            </td>
          </tr>

        </table>

        <p style="margin-top:20px;">
          Kindly review the above contract details and proceed with the next steps.
        </p>

        <p>
          Regards,<br/>
          <strong>Sales Team</strong>
          <span style="font-size:12px;color:#6b7280;">
            ERP Enterprise
          </span>
        </p>

      </td>
    </tr>

    <!-- FOOTER -->
    <tr>
      <td style="background:#f9fafb;padding:12px;text-align:center;font-size:12px;color:#6b7280;">
        This is an automated email. Please do not reply.
      </td>
    </tr>

  </table>
</div>
`,
        });
      } catch (mailErr) {
        console.error("EMAIL ERROR:", mailErr.message);
        // ❗ don't fail API if email fails
      }
    }

    return res.status(201).json({
      success: true,
      message: "Contract created successfully",
      data: contract,
    });
  } catch (error) {
    await client.query("ROLLBACK");

    console.error("Create Contract Error:", error);

    if (error.code === "23505") {
      return res.status(409).json({
        message: "Duplicate contract number, please retry",
      });
    }

    return res.status(500).json({
      message: "Internal server error",
    });
  } finally {
    client.release();
  }
};

const getContracts = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    if (!orgId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    const { page = 1, limit = 10, status, search } = req.query;
    const offset = (page - 1) * limit;

    let baseQuery = `
    SELECT 
  sc.*,

  c.customer_code,
  c.company_name AS customer_name,

  p.id AS product_id,
  p.item_name AS product_name

FROM sales_contracts sc

LEFT JOIN customers c 
  ON sc.customer_code = c.customer_code

LEFT JOIN procurement_itemmaster p 
  ON sc.product_id = p.id

WHERE sc.org_id = $1
    `;

    const values = [orgId];
    let idx = 2;

    // filters
    if (status) {
      baseQuery += ` AND sc.status = $${idx++}`;
      values.push(status);
    }

    if (search) {
      baseQuery += `
        AND (
          sc.contract_no ILIKE $${idx}
          OR c.company_name ILIKE $${idx}
          OR p.product_name ILIKE $${idx}
        )
      `;
      values.push(`%${search}%`);
      idx++;
    }

    baseQuery += `
      ORDER BY sc.created_at DESC 
      LIMIT $${idx++} OFFSET $${idx++}
    `;
    values.push(limit, offset);

    const data = await pool.query(baseQuery, values);

    // COUNT (IMPORTANT: SAME FILTERS)
    let countQuery = `
      SELECT COUNT(*) 
      FROM sales_contracts sc
      LEFT JOIN customers c 
        ON sc.customer_code = c.customer_code
      LEFT JOIN sales_products p 
        ON sc.product_id = p.id
      WHERE sc.org_id = $1
    `;

    const countValues = [orgId];
    let countIdx = 2;

    if (status) {
      countQuery += ` AND sc.status = $${countIdx++}`;
      countValues.push(status);
    }

    if (search) {
      countQuery += `
        AND (
          sc.contract_no ILIKE $${countIdx}
          OR c.company_name ILIKE $${countIdx}
          OR p.product_name ILIKE $${countIdx}
        )
      `;
      countValues.push(`%${search}%`);
      countIdx++;
    }

    const totalRes = await pool.query(countQuery, countValues);

    return res.json({
      data: data.rows,
      total: parseInt(totalRes.rows[0].count),
      page: parseInt(page),
      limit: parseInt(limit),
    });
  } catch (error) {
    console.error("Get Contracts Error:", error);
    return res.status(500).json({ message: "Internal server error" });
  }
};

const getContractById = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    const { id } = req.params;

    if (!orgId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    const result = await pool.query(
      `SELECT * FROM sales_contracts WHERE id = $1 AND org_id = $2`,
      [id, orgId],
    );

    if (result.rows.length === 0) {
      return res.status(404).json({ message: "Contract not found" });
    }

    return res.json(result.rows[0]);
  } catch (error) {
    console.error("Get Contract By ID Error:", error);
    return res.status(500).json({ message: "Internal server error" });
  }
};

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

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

//     if (!orgId) {
//       return res.status(401).json({ message: "Unauthorized" });
//     }

//     const {
//       contract_title,
//       contract_type,
//       contract_no,
//       customer_code,
//       product_id,
//       contract_qty,
//       rate,
//       valid_from,
//       valid_to,
//       delivery_terms,
//       govt_allocation_ref,
//       remarks,
//       status,
//     } = req.body;

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

//     //  check exists
//     const existing = await client.query(
//       `SELECT * FROM sales_contracts WHERE id = $1 AND org_id = $2`,
//       [id, orgId],
//     );

//     if (existing.rows.length === 0) {
//       await client.query("ROLLBACK");
//       return res.status(404).json({ message: "Contract not found" });
//     }

//     //  duplicate check (exclude current id)
//     if (contract_no) {
//       const dup = await client.query(
//         `SELECT id FROM sales_contracts WHERE contract_no = $1 AND id != $2`,
//         [contract_no, id],
//       );

//       if (dup.rows.length > 0) {
//         await client.query("ROLLBACK");
//         return res
//           .status(409)
//           .json({ message: "Contract number already exists" });
//       }
//     }

//     const updateQuery = `
//   UPDATE sales_contracts
//   SET
//     contract_title = COALESCE($1, contract_title),
//     contract_type = COALESCE($2, contract_type),
//     contract_no = COALESCE($3, contract_no),
//     customer_code = COALESCE($4, customer_code),
//     product_id = COALESCE($5, product_id),
//     contract_qty = COALESCE($6, contract_qty),
//     rate = COALESCE($7, rate),
//     valid_from = COALESCE($8, valid_from),
//     valid_to = COALESCE($9, valid_to),
//     delivery_terms = COALESCE($10, delivery_terms),
//     govt_allocation_ref = COALESCE($11, govt_allocation_ref),
//     remarks = COALESCE($12, remarks),
//     status = COALESCE($13, status),
//     updated_at = CURRENT_TIMESTAMP
//   WHERE id = $14 AND org_id = $15
//   RETURNING *;
// `;

//     const values = [
//       contract_title, // $1
//       contract_type, // $2
//       contract_no, // $3
//       customer_code, // $4
//       product_id, // $5
//       contract_qty, // $6
//       rate, // $7
//       valid_from, // $8
//       valid_to, // $9
//       delivery_terms, // $10
//       govt_allocation_ref, // $11
//       remarks, // $12
//       status, // $13
//       id, // $14
//       orgId, // $15
//     ];

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

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

//     return res.json({
//       message: "Contract updated successfully",
//       data: result.rows[0],
//     });
//   } catch (error) {
//     await client.query("ROLLBACK");
//     console.error("Update Contract Error:", error);
//     return res.status(500).json({ message: "Internal server error" });
//   } finally {
//     client.release();
//   }
// };
const updateContract = async (req, res) => {
  const client = await pool.connect();

  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;
    const { id } = req.params;

    if (!orgId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    const {
      contract_title,
      contract_type,
      contract_no,
      customer_code,
      product_id,
      contract_qty,
      rate,
      valid_from,
      valid_to,
      delivery_terms,
      govt_allocation_ref,
      remarks,
      status,
    } = req.body;

    await client.query("BEGIN");

    //  Step 1: Fetch existing (LOCK row)
    const existingRes = await client.query(
      `SELECT * FROM sales_contracts 
       WHERE id = $1 AND org_id = $2
       FOR UPDATE`,
      [id, orgId],
    );

    if (existingRes.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({ message: "Contract not found" });
    }

    const oldContract = existingRes.rows[0];

    //  Step 2: Duplicate check
    if (contract_no) {
      const dup = await client.query(
        `SELECT id FROM sales_contracts 
         WHERE contract_no = $1 AND id != $2`,
        [contract_no, id],
      );

      if (dup.rows.length > 0) {
        await client.query("ROLLBACK");
        return res
          .status(409)
          .json({ message: "Contract number already exists" });
      }
    }

    //  Step 3: Update
    const updateQuery = `
      UPDATE sales_contracts
      SET
        contract_title = COALESCE($1, contract_title),
        contract_type = COALESCE($2, contract_type),
        contract_no = COALESCE($3, contract_no),
        customer_code = COALESCE($4, customer_code),
        product_id = COALESCE($5, product_id),
        contract_qty = COALESCE($6, contract_qty),
        rate = COALESCE($7, rate),
        valid_from = COALESCE($8, valid_from),
        valid_to = COALESCE($9, valid_to),
        delivery_terms = COALESCE($10, delivery_terms),
        govt_allocation_ref = COALESCE($11, govt_allocation_ref),
        remarks = COALESCE($12, remarks),
        status = COALESCE($13, status),
        updated_at = CURRENT_TIMESTAMP
      WHERE id = $14 AND org_id = $15
      RETURNING *;
    `;

    const values = [
      contract_title,
      contract_type,
      contract_no,
      customer_code,
      product_id,
      contract_qty,
      rate,
      valid_from,
      valid_to,
      delivery_terms,
      govt_allocation_ref,
      remarks,
      status,
      id,
      orgId,
    ];

    const result = await client.query(updateQuery, values);
    const updatedContract = result.rows[0];

    //  Step 4: Insert history (VERY IMPORTANT)
    await client.query(
      `INSERT INTO sales_contract_history (
        contract_id,
        contract_no,
        customer_code,
        product_id,
        contract_qty,
        rate,
        valid_from,
        valid_to,
        delivery_terms,
        govt_allocation_ref,
        status,
        uom,
        org_id,
        remarks,
        action_type,
        action_by
      )
      VALUES (
        $1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,'UPDATED',$15
      )`,
      [
        updatedContract.id,
        updatedContract.contract_no,
        updatedContract.customer_code,
        updatedContract.product_id,
        updatedContract.contract_qty,
        updatedContract.rate,
        updatedContract.valid_from,
        updatedContract.valid_to,
        updatedContract.delivery_terms,
        updatedContract.govt_allocation_ref,
        updatedContract.status,
        updatedContract.uom,
        updatedContract.org_id,
        updatedContract.remarks,
        userId,
      ],
    );

    await client.query("COMMIT");

    return res.json({
      message: "Contract updated successfully",
      data: updatedContract,
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Update Contract Error:", error);

    return res.status(500).json({
      message: "Internal server error",
    });
  } finally {
    client.release();
  }
};

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

  try {
    const orgId = req.session?.user?.orgId;
    const userId = req.session?.user?.id;
    const { id } = req.params;

    if (!orgId || !userId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    if (!id) {
      return res.status(400).json({
        message: "Contract ID is required",
      });
    }

    await client.query("BEGIN");

    // Check if exists & not already deleted
    const check = await client.query(
      `
      SELECT id FROM sales_contracts 
      WHERE id = $1 
        AND org_id = $2 
        AND is_deleted = false
      `,
      [id, orgId],
    );

    if (check.rows.length === 0) {
      await client.query("ROLLBACK");
      return res.status(404).json({
        message: "Contract not found or already deleted",
      });
    }

    //  SOFT DELETE
    await client.query(
      `
      UPDATE sales_contracts
      SET 
        is_deleted = true,
        updated_at = NOW(),
        updated_by = $3
      WHERE id = $1 AND org_id = $2
      `,
      [id, orgId, userId],
    );

    await client.query("COMMIT");

    return res.json({
      success: true,
      message: "Contract deleted successfully",
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Delete Contract Error:", error);

    return res.status(500).json({
      success: false,
      message: "Internal server error",
    });
  } finally {
    client.release();
  }
};

const getContractsByCustomer = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;
    const { customer_code } = req.params;
    const { status } = req.query;

    if (!orgId) {
      return res.status(401).json({ message: "Unauthorized" });
    }

    // =========================
    // 1. FETCH CONTRACTS
    // =========================
    let query = `
      SELECT 
        sc.id,
        sc.contract_no,
        sc.contract_title,
        sc.contract_type,
        sc.contract_qty,
        sc.rate,
        sc.valid_from,
        sc.valid_to,
        sc.status,
        p.product_name,

        (sc.contract_qty * sc.rate) AS total_value

      FROM sales_contracts sc
      LEFT JOIN sales_products p 
        ON sc.product_id = p.id

      WHERE sc.org_id = $1
      AND sc.customer_code = $2
      AND sc.is_deleted = false
    `;

    const values = [orgId, customer_code];
    let idx = 3;

    if (status) {
      query += ` AND sc.status = $${idx++}`;
      values.push(status);
    }

    query += ` ORDER BY sc.created_at DESC`;

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

    // =========================
    // 2. SUMMARY CALCULATION
    // =========================
    const total = result.rows.length;

    const active = result.rows.filter((c) => c.status === "ACTIVE").length;

    const totalValue = result.rows.reduce(
      (sum, c) => sum + Number(c.total_value || 0),
      0,
    );

    // =========================
    // 3. FORMAT RESPONSE
    // =========================
    const today = new Date();

    const formatted = result.rows.map((c) => {
      let expiry_label = "";

      const validTo = new Date(c.valid_to);

      if (validTo.toDateString() === today.toDateString()) {
        expiry_label = "Expires today";
      } else if (validTo < today) {
        expiry_label = "Expired";
      }

      return {
        ...c,
        expiry_label,
      };
    });

    return res.json({
      summary: {
        total,
        active,
        total_value: totalValue,
      },
      data: formatted,
    });
  } catch (error) {
    console.error("Customer Contract History Error:", error);
    return res.status(500).json({ message: "Internal server error" });
  }
};

module.exports = {
  createContract,
  getContracts,
  getContractById,
  updateContract,
  deleteContract,
  getContractsByCustomer,
};
