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

/* ===========================
    CREATE SALES ENQUIRY
=========================== */
// const createSalesEnquiry = async (req, res) => {
//   const org_id = req.session.user.orgId;
//   const userId = req.session.user.id;

//   try {
//     const {
//       customer_id,
//       customer_name,
//       product_id, // ✅ ADD THIS
//       product_name,
//       contact_number,
//       contact_person,
//       quantity,
//       required_delivery_date,
//       enquiry_source,
//       status,
//     } = req.body;

//     if (!product_name || !quantity) {
//       return res.status(400).json({
//         success: false,
//         message: "Customer name, product name and quantity are required",
//       });
//     }

//     const query = `
//       INSERT INTO sales_enquiry (
//        customer_id,
//   customer_name,
//   product_id,     --  ADD THIS
//   product_name,
//    contact_number,
//        contact_person,
//         quantity,
//         required_delivery_date,
//         enquiry_source,
//         status,
//         org_id,
//         created_by
//       )
//       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)
//       RETURNING *;
//     `;

//     const values = [
//       customer_id,
//       customer_name,
//       product_id, //  IMPORTANT
//       product_name,
//       contact_number,
//       contact_person,
//       quantity,
//       required_delivery_date,
//       enquiry_source,
//       status || "active",
//       org_id || null,
//       userId,
//     ];

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

//     res.status(201).json({
//       success: true,
//       message: "Sales enquiry created successfully ",
//       data: result.rows[0],
//     });
//   } catch (err) {
//     console.error("CREATE SALES ENQUIRY ERROR:", err);
//     res.status(500).json({
//       success: false,
//       message: "Create failed",
//     });
//   }
// };




const createSalesEnquiry = async (req, res) => {
  const org_id = req.session.user.orgId;
  const userId = req.session.user.id;

  const nodemailer = require("nodemailer");

  const transporter = nodemailer.createTransport({
    service: "gmail",
    auth: {
      user: process.env.MAIL_USER,
      pass: process.env.MAIL_PASS,
    },
  });

    const client = await pool.connect();

  try {
    const {
      customer_id,
      customer_name,
      item_id,
      company_name,
      item_name,
      contact_number,
      contact_person,
      quantity,
      enquiry_source,
      status,
      remarks,
    } = req.body;

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

    if (!customer_id && (!contact_number || !contact_person)) {
      return res.status(400).json({
        success: false,
        message:
          "Contact number and contact person are required for manual entry",
      });
    }

        await client.query("BEGIN");
    const query = `
      INSERT INTO sales_enquiry (
        customer_id,
        customer_name,
        company_name,
        item_id,
        item_name,
        contact_number,
        contact_person,
        quantity,
        enquiry_source,
        status,
        org_id,
        created_by,
        remarks
      )
      VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
      RETURNING *;
    `;

    const values = [
      customer_id || null,
      customer_name || null,
      company_name || null,
      item_id || null,
      item_name || null,
      contact_number || null,
      contact_person || null,
      quantity,
      enquiry_source,
      status || "active",
      org_id || null,
      userId,
      remarks || null,
    ];

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

    let customerEmail = null;
    let customerName = customer_name || "Customer";
    let finalContactPerson = contact_person || "-";
    let finalContactNumber = contact_number || "-";

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

        if (custRes.rows.length > 0) {
        const customer = custRes.rows[0];

        customerEmail = customer.email;
        customerName = customer.company_name || customerName;

        //  fallback logic
        finalContactPerson =
          contact_person || customer.contact_person || "-";

        finalContactNumber =
          contact_number || customer.phone || "-";
      }
    }


      await client.query("COMMIT");

    if (customerEmail) {
      try {
        await transporter.sendMail({
          from: `"Sales Team" <${process.env.MAIL_USER}>`,
          to: customerEmail,
          subject: "Thank You for Your Enquiry",

          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;border:1px solid #e5e7eb;">

    <!-- HEADER -->
    <tr style="background:#2563eb;color:#ffffff;">
      <td style="padding:16px 20px;font-size:18px;font-weight:600;">
        Sales Enquiry Received
      </td>
    </tr>

    <!-- BODY -->
    <tr>
      <td style="padding:20px;color:#374151;font-size:14px;line-height:1.6;">

        <p>Dear <strong>${customerName}</strong>,</p>

        <p>
          Thank you for reaching out to us. We have successfully received your enquiry.
        </p>

        <!-- DETAILS -->
        <table width="100%" style="margin-top:15px;border-collapse:collapse;border:1px solid #e5e7eb;">
          
          <tr style="background:#f9fafb;">
            <td colspan="2" style="padding:10px;font-weight:600;border-bottom:1px solid #e5e7eb;">
              Enquiry Details
            </td>
          </tr>

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

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

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">Contact Person</td>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${finalContactPerson}
            </td>
          </tr>

          <tr>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">Contact Number</td>
            <td style="padding:8px;border-bottom:1px solid #e5e7eb;">
              ${finalContactNumber}
            </td>
          </tr>

        </table>

        <p style="margin-top:20px;">
          Our team will review your enquiry and get back to you shortly.
        </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 acknowledgement email.
      </td>
    </tr>

  </table>
</div>
      `,
        });
      } catch (err) {
        console.error("ENQUIRY EMAIL ERROR:", err.message);
      }
    }

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

    res.status(201).json({
      success: true,
      message: "Sales enquiry created successfully",
      data: result.rows[0],
    });

  } catch (err) {
    await client.query("ROLLBACK");
    console.error("CREATE SALES ENQUIRY ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Create failed",
    });
  }
  finally {
  client.release();
}
};

/* ===========================
    GET SALES ENQUIRIES
=========================== */
const getSalesEnquiries = async (req, res) => {
  try {
    const org_id = req.session.user.orgId;

    //  Query params
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 10;
    const search = req.query.search || "";

    const offset = (page - 1) * limit;

    //  Base condition
    let whereClause = `WHERE is_deleted = false AND org_id = $1`;
    let values = [org_id];

    //  Search filter
    if (search) {
      values.push(`%${search}%`);
      whereClause += ` AND (
        customer_name ILIKE $${values.length}
        OR product_name ILIKE $${values.length}
      )`;
    }

    //  Main query
    const dataQuery = `
      SELECT 
        id,
        customer_id,
        customer_name,
         company_name,  
         contact_number,     -- ✅ ADD
  contact_person,
        item_id,
        item_name,
        quantity,
        required_delivery_date,
        enquiry_source,
        status,
        remarks,
        created_at
      FROM sales_enquiry
      ${whereClause}
      ORDER BY created_at DESC
      LIMIT $${values.length + 1}
      OFFSET $${values.length + 2}
    `;

    values.push(limit, offset);

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

    //  Count query
    const countQuery = `
      SELECT COUNT(*) FROM sales_enquiry ${whereClause}
    `;

    const countResult = await pool.query(
      countQuery,
      values.slice(0, values.length - 2),
    );

    const total = parseInt(countResult.rows[0].count);
    const totalPages = Math.ceil(total / limit);

    //  Response formatting
    const formattedData = result.rows.map((row) => ({
      id: row.id,
      enquiryNumber: `ENQ-${row.id}`,
      date: row.created_at,
      customer_id: row.customer_id,
      item_id: row.item_id,
      contact_number: row.contact_number,
      contact_person: row.contact_person,
      company_name: row.company_name || row.customer_name || null,
      customer_name: row.customer_name,
      item_name: row.item_name,
      quantity: row.quantity,
      delivery_date: row.required_delivery_date,
      enquiry_source: row.enquiry_source,
      status: row.status,
      remarks: row.remarks,
    }));

    res.json({
      success: true,
      data: formattedData,
      pagination: {
        total,
        page,
        limit,
        totalPages,
      },
    });
  } catch (err) {
    console.error("FETCH SALES ENQUIRY ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

/* ===========================
    GET SALES ENQUIRY BY ID
=========================== */
const getSalesEnquiryById = async (req, res) => {
  try {
    const { id } = req.params;

    // const result = await pool.query(
    //   `
    //   SELECT *
    //   FROM sales_enquiry
    //   WHERE id = $1
    //     AND is_deleted = false
    //   `,
    //   [id],
    // );
    const result = await pool.query(
      `
  SELECT 
    se.*,

    -- ✅ Get creator name
    u1.name AS created_by_name,

    -- ✅ Get updater name (if exists)
    u2.name AS updated_by_name

  FROM sales_enquiry se

  LEFT JOIN users u1 ON se.created_by = u1.id
  LEFT JOIN users u2 ON se.updated_by = u2.id

  WHERE se.id = $1
    AND se.is_deleted = false
  `,
      [id],
    );

    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Record not found",
      });
    }

    res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (err) {
    console.error("GET BY ID ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

/* ===========================
    UPDATE SALES ENQUIRY
=========================== */
const updateSalesEnquiry = async (req, res) => {
  try {
    const { id } = req.params;

    const {
      customer_id,
      customer_name,
      company_name,
      contact_number,
      contact_person,
      item_id,
      item_name,
      quantity,
      enquiry_source,
      status,
      remarks, // ✅ NEW FIELD
    } = req.body;

    // ✅ Validation
    if (!quantity) {
      return res.status(400).json({
        success: false,
        message: "Quantity is required",
      });
    }

    // 🔥 Conditional validation (same as create)
    if (!customer_id && (!contact_number || !contact_person)) {
      return res.status(400).json({
        success: false,
        message:
          "Contact number and contact person are required for manual entry",
      });
    }

    const query = `
      UPDATE sales_enquiry SET
        customer_id = COALESCE($1, customer_id),
        customer_name = COALESCE($2, customer_name),
        company_name = COALESCE($3, company_name),
        contact_number = COALESCE($4, contact_number),
        contact_person = COALESCE($5, contact_person),
        item_id = COALESCE($6, item_id),
        item_name = COALESCE($7, item_name),
        quantity = COALESCE($8, quantity),
        enquiry_source = COALESCE($9, enquiry_source),
        status = COALESCE($10, status),
        remarks = COALESCE($11, remarks), -- ✅ NEW
        updated_at = NOW()
      WHERE id = $12
        AND is_deleted = false
      RETURNING *;
    `;

    const values = [
      customer_id || null,
      customer_name || null,
      company_name || null,
      contact_number || null,
      contact_person || null,
      item_id || null,
      item_name || null,
      quantity || null,
      enquiry_source || null,
      status || null,
      remarks || null, // ✅ NEW
      id,
    ];

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

    if (!result.rows.length) {
      return res.status(404).json({
        success: false,
        message: "Record not found",
      });
    }

    res.json({
      success: true,
      message: "Updated successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error("UPDATE ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Update failed",
    });
  }
};
/* ===========================
   DELETE SALES ENQUIRY
=========================== */
const deleteSalesEnquiry = async (req, res) => {
  try {
    const { id } = req.params;

    await pool.query(
      `
      UPDATE sales_enquiry
      SET is_deleted = true,
          updated_at = NOW()
      WHERE id = $1
      `,
      [id],
    );

    res.json({
      success: true,
      message: "Deleted successfully ✅",
    });
  } catch (err) {
    console.error("DELETE ERROR:", err);
    res.status(500).json({
      success: false,
      message: "Delete failed",
    });
  }
};

module.exports = {
  createSalesEnquiry,
  getSalesEnquiries,
  getSalesEnquiryById,
  updateSalesEnquiry,
  deleteSalesEnquiry,
};
