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

/* ===========================
    CREATE CUSTOMER
=========================== */
const createCustomer = async (req, res) => {
  const orgId = req.session.user.orgId;
  const userId = req.session.user.id;
  try {
    const {
      gst,
      companyName,
      companyType,
      contactPerson,
      phone,
      email,
      pan,
      license,
      aadhaar,

      billingAddress,
      shippingAddress,
      billState,
      billCity,
      billPinCode,
      shipState,
      shipCity,
      shipPinCode,

      creditLimit,
      paymentTerms,
      advancePayment,
      payAgainstDelivery,
      deliveryAgainstPayment,
      status,
      // orgId,
    } = req.body;

    if (!companyName || !phone || !email) {
      return res.status(400).json({
        success: false,
        message: "Required fields missing",
      });
    }

    const files = req.files || {};

    const formatPath = (filePath) => {
      if (!filePath) return null;
      return filePath.replace(/\\/g, "/").split("uploads/")[1]
        ? "uploads/" + filePath.replace(/\\/g, "/").split("uploads/")[1]
        : filePath;
    };

    const panFile = formatPath(files.panFile?.[0]?.path);
    const licenseFile = formatPath(files.licenseFile?.[0]?.path);
    const aadhaarFile = formatPath(files.aadhaarFile?.[0]?.path);
    const incorporationFile = formatPath(files.incorporationFile?.[0]?.path);
    const chequeFile = formatPath(files.chequeFile?.[0]?.path);

    // const id = "C-" + Date.now();
    const prefixRes = await pool.query(
      `SELECT prefix_code 
   FROM prefixes 
   WHERE module_name = 'sales_customer' 
   AND org_id = $1 AND status = 'ACTIVE'`,
      [orgId],
    );

    const prefix = prefixRes.rows[0]?.prefix_code || "C-";
    const lastRes = await pool.query(
      `SELECT customer_code  
   FROM customers 
   WHERE org_id = $1 
   AND customer_code  LIKE $2
   ORDER BY created_at DESC 
   LIMIT 1`,
      [orgId, `${prefix}%`],
    );
    // 3. Extract last number
    let nextNumber = 1;
    if (lastRes.rows.length > 0) {
      const lastId = lastRes.rows[0].customer_code; // e.g., C-005
      const lastNum = parseInt(lastId.replace(prefix, ""), 10);

      if (!isNaN(lastNum)) {
        nextNumber = lastNum + 1;
      }
    }
    // 4. Format (001, 002...)
    const padded = String(nextNumber).padStart(3, "0");
    // 5. Final ID
    const id = `${prefix}${padded}`;

    const toNumber = (val) => (val ? Number(val) : 0);

    const query = `
      INSERT INTO customers (
        customer_code, gst, company_name, company_type,
        contact_person, phone, email,
        pan, license, aadhaar,

        pan_file, license_file, aadhaar_file, incorporation_file, cheque_file,

        billing_address, shipping_address,
        bill_state, bill_city, bill_pin_code,
        ship_state, ship_city, ship_pin_code,

        credit_limit, payment_terms,
        advance_payment, pay_against_delivery, delivery_against_payment,

        status, org_id,created_by
      )
      VALUES (
        $1,$2,$3,$4,
        $5,$6,$7,
        $8,$9,$10,
        $11,$12,$13,$14,$15,
        $16,$17,
        $18,$19,$20,
        $21,$22,$23,
        $24,$25,
        $26,$27,$28,
        $29,$30,$31
      )
      RETURNING *;
    `;

    const values = [
      id,
      gst,
      companyName,
      companyType,
      contactPerson,
      phone,
      email,
      pan,
      license,
      aadhaar,

      panFile,
      licenseFile,
      aadhaarFile,
      incorporationFile,
      chequeFile,

      billingAddress,
      shippingAddress,

      billState,
      billCity,
      billPinCode,

      shipState,
      shipCity,
      shipPinCode,

      toNumber(creditLimit),
      paymentTerms,
      toNumber(advancePayment),
      toNumber(payAgainstDelivery),
      toNumber(deliveryAgainstPayment),

      status || "active",
      orgId || null,
      userId,
    ];

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

    const customerCode = result.rows[0].customer_code;

    // Insert default shipping into new table
    if (shippingAddress) {
      await pool.query(
        `INSERT INTO customer_addresses 
     (customer_code, address, city, state, pin_code, is_default)
     VALUES ($1,$2,$3,$4,$5,true)`,
        [customerCode, shippingAddress, shipCity, shipState, shipPinCode],
      );
    }

    res.status(201).json({
      success: true,
      message: "Customer created successfully",
      data: result.rows[0],
    });
  } catch (err) {
    console.error(err);

    if (err.code === "23505") {
      return res.status(400).json({
        success: false,
        message: `Duplicate entry: ${err.constraint}`,
      });
    }

    res.status(500).json({ message: "Create failed" });
  }
};

const downloadCustomerTemplate = async (req, res) => {
  try {
    const templateData = [
      {
        "Company Name": "ABC Pvt Ltd",
        "Company Type": "Private",
        "Contact Person": "John Doe",
        Phone: "9876543210",
        Email: "abc@gmail.com",
        GST: "29ABCDE1234F1Z5",
        PAN: "ABCDE1234F",
        "Billing Address": "Bangalore",
        "Billing State": "Karnataka",
        "Billing City": "Bangalore",
        "Billing Pin Code": "560001",
        "Shipping Address": "Bangalore",
        "Shipping State": "Karnataka",
        "Shipping City": "Bangalore",
        "Shipping Pin Code": "560001",
        "Credit Limit": "100000",
        "Payment Terms": "30 Days",
        Status: "active",
      },
    ];

    const worksheet = XLSX.utils.json_to_sheet(templateData);
    const workbook = XLSX.utils.book_new();
    XLSX.utils.book_append_sheet(workbook, worksheet, "CustomerTemplate");

    const instructions = [
      {
        Field: "Company Name",
        Required: "Yes",
        Description: "Customer company name",
      },
      {
        Field: "Phone",
        Required: "Yes",
        Description: "10 digit mobile number",
      },
      { Field: "Email", Required: "Yes", Description: "Valid email address" },
      { Field: "GST", Required: "No", Description: "GST number" },
      { Field: "Credit Limit", Required: "No", Description: "Numeric value" },
    ];

    const instructionSheet = XLSX.utils.json_to_sheet(instructions);
    XLSX.utils.book_append_sheet(workbook, instructionSheet, "Instructions");

    const buffer = XLSX.write(workbook, { type: "buffer", bookType: "xlsx" });

    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
    );
    res.setHeader(
      "Content-Disposition",
      "attachment; filename=customer_master_template.xlsx",
    );

    res.send(buffer);
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: err.message });
  }
};

const checkGstDuplicate = async (req, res) => {
  try {
    const orgId = req.session?.user?.orgId;

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

    const { gst } = req.body;

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

    // 🔹 Check GST in DB
    const result = await pool.query(
      `SELECT 1 FROM customers 
       WHERE gst = $1 AND org_id = $2 
       LIMIT 1`,
      [gst, orgId]
    );

    if (result.rows.length > 0) {
      return res.status(200).json({
        success: true,
        isDuplicate: true,
        message: "GST already exists",
      });
    }

    return res.status(200).json({
      success: true,
      isDuplicate: false,
      message: "GST is available",
    });
  } catch (error) {
    console.error("GST check error:", error);
    res.status(500).json({
      success: false,
      message: "Internal server error",
    });
  }
};

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

  try {
    const file = req.file;

    if (!file) {
      return res.status(400).json({ message: "File is required" });
    }

    const workbook = XLSX.readFile(file.path);
    const sheet = workbook.Sheets[workbook.SheetNames[0]];
    const data = XLSX.utils.sheet_to_json(sheet);

    let successCount = 0;
    let errorCount = 0;
    const errors = [];

    for (let i = 0; i < data.length; i++) {
      const row = data[i];

      try {
        // if (!row["Company Name"] || !row["Phone"] || !row["Email"]) {
        //   throw new Error("Missing required fields");
        // }
        const requiredFields = ["Company Name", "Phone", "Email"];

        for (const field of requiredFields) {
          if (!row[field]) {
            throw new Error(`${field} is required`);
          }
        }

        const prefixRes = await client.query(
          `SELECT prefix_code 
   FROM prefixes 
   WHERE module_name = 'sales_customer' 
   AND org_id = $1 AND status = 'ACTIVE'`,
          [req.session.user.orgId],
        );

        const prefix = prefixRes.rows[0]?.prefix_code || "C-";

        const lastRes = await client.query(
          `SELECT customer_code 
   FROM customers 
   WHERE org_id = $1 
   AND customer_code LIKE $2
   ORDER BY created_at DESC 
   LIMIT 1`,
          [req.session.user.orgId, `${prefix}%`],
        );

        let nextNumber = 1;

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

        const padded = String(nextNumber).padStart(3, "0");
        const customerCode = `${prefix}${padded}`;
        const duplicateCheck = await client.query(
          `SELECT 1 FROM customers 
   WHERE email = $1 OR gst = $2`,
          [row["Email"], row["GST"]],
        );

        if (duplicateCheck.rows.length > 0) {
          throw new Error("Duplicate Email or GST");
        }

        await client.query(
          `INSERT INTO customers (
            customer_code, company_name, company_type,
            contact_person, phone, email, gst,
            billing_address, bill_state, bill_city, bill_pin_code,
            shipping_address, ship_state, ship_city, ship_pin_code,
            credit_limit, payment_terms, status, org_id,
  created_by
          ) VALUES (
            $1,$2,$3,$4,$5,$6,$7,
            $8,$9,$10,$11,
            $12,$13,$14,$15,
            $16,$17,$18,$19,$20
          )`,
          [
            customerCode,
            row["Company Name"],
            row["Company Type"],
            row["Contact Person"],
            row["Phone"],
            row["Email"],
            row["GST"],
            row["Billing Address"],
            row["Billing State"],
            row["Billing City"],
            row["Billing Pin Code"],
            row["Shipping Address"],
            row["Shipping State"],
            row["Shipping City"],
            row["Shipping Pin Code"],
            Number(row["Credit Limit"] || 0),
            row["Payment Terms"],
            row["Status"] || "active",
            req.session.user.orgId,
            req.session.user.id,
          ],
        );

        successCount++;
      } catch (err) {
        errorCount++;
        errors.push({
          row: i + 2,
          message: err.message,
        });
      }
    }

    res.json({
      success: true,
      data: { successCount, errorCount, errors },
    });
  } catch (err) {
    errorCount++;
    errors.push({
      row: i + 2,
      message: err.message,
    });
  } finally {
    client.release();
  }
};

const addCustomerAddress = async (req, res) => {
  const { customerCode, address, city, state, pinCode, isDefault } = req.body;

  try {
    // if setting new default → remove old default
    if (isDefault) {
      await pool.query(
        `UPDATE customer_addresses 
         SET is_default = false 
         WHERE customer_code = $1`,
        [customerCode],
      );
    }

    const result = await pool.query(
      `INSERT INTO customer_addresses 
       (customer_code, address, city, state, pin_code, is_default)
       VALUES ($1,$2,$3,$4,$5,$6)
       RETURNING *`,
      [customerCode, address, city, state, pinCode, isDefault || false],
    );

    res.json({ success: true, data: result.rows[0] });
  } catch (err) {
    console.error(err);
    res.status(500).json({ message: "Failed to add address" });
  }
};

/* ===========================
    GET CUSTOMERS (PAGINATION)
=========================== */
// const getCustomers = async (req, res) => {
//   try {
//     const page = parseInt(req.query.page) || 1;
//     const limit = parseInt(req.query.limit) || 10;
//     const offset = (page - 1) * limit;

//     const totalResult = await pool.query(
//       "SELECT COUNT(*) FROM customers WHERE is_deleted = false",
//     );

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

//     const result = await pool.query(
//       `SELECT * FROM customers
//        WHERE is_deleted = false
//        ORDER BY created_at DESC
//        LIMIT $1 OFFSET $2`,
//       [limit, offset],
//     );

//     res.json({
//       data: result.rows,
//       totalRecords,
//       totalPages,
//       currentPage: page,
//     });
//   } catch (err) {
//     res.status(500).json({ message: "Fetch failed" });
//   }
// };


const getCustomers = async (req, res) => {
  try {
    const org_id = req.session?.user?.orgId;

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

    const page = Math.max(parseInt(req.query.page) || 1, 1);
    const limit = Math.min(parseInt(req.query.limit) || 10, 100);
    const offset = (page - 1) * limit;

    // ✅ COUNT (with org filter)
    const countResult = await pool.query(
      `SELECT COUNT(*) 
       FROM customers 
       WHERE is_deleted = false 
         AND org_id = $1`,
      [org_id],
    );

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

    // ✅ MAIN QUERY (with joins)
    // const result = await pool.query(
    //   `
    //   SELECT 
    //     c.*,
    //     cu.name AS created_by_name,
    //     uu.name AS updated_by_name

    //   FROM customers c

    //   LEFT JOIN users cu 
    //     ON c.created_by = cu.id

    //   LEFT JOIN users uu 
    //     ON c.updated_by = uu.id

    //   WHERE c.is_deleted = false
    //     AND c.org_id = $1

    //   ORDER BY c.created_at DESC
    //   LIMIT $2 OFFSET $3
    //   `,
    //   [org_id, limit, offset],
    // );
    const result = await pool.query(
  `
  SELECT 
    c.*,

    CONCAT_WS(
      ', ',
      c.shipping_address,
      c.ship_city,
      c.ship_state,
      c.ship_pin_code
    ) AS full_shipping_address,

    CONCAT_WS(
      ', ',
      c.billing_address,
      c.bill_city,
      c.bill_state,
      c.bill_pin_code
    ) AS full_billing_address,

    cu.name AS created_by_name,
    uu.name AS updated_by_name

  FROM customers c

  LEFT JOIN users cu 
    ON c.created_by = cu.id

  LEFT JOIN users uu 
    ON c.updated_by = uu.id

  WHERE c.is_deleted = false
    AND c.org_id = $1

  ORDER BY c.created_at DESC
  LIMIT $2 OFFSET $3
  `,
  [org_id, limit, offset],
);

    return res.status(200).json({
      success: true,
      data: result.rows,
      pagination: {
        total: totalRecords,
        page,
        limit,
        totalPages,
      },
    });
  } catch (err) {
    console.error("GET CUSTOMERS ERROR:", err);

    return res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};

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

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

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

//     res.json(result.rows[0]);
//   } catch (err) {
//     console.error(err);
//     res.status(500).json({ message: "Fetch failed" });
//   }
// };
const getCustomerById = async (req, res) => {
  try {
    const { id } = req.params;
    const org_id = req.session?.user?.orgId;

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

    const result = await pool.query(
      `
      SELECT 
        c.*,
        cu.name AS created_by_name,
        uu.name AS updated_by_name

      FROM customers c

      LEFT JOIN users cu 
        ON c.created_by = cu.id

      LEFT JOIN users uu 
        ON c.updated_by = uu.id

      WHERE c.customer_code = $1
        AND c.org_id = $2
        AND c.is_deleted = false
      `,
      [id, org_id],
    );

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

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

    return res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};
const getSalesCustomerPriceById = async (req, res) => {
  try {
    const { id } = req.params;

    const result = await pool.query(
      `
      SELECT 
        scp.*,
        p.product_name,
        c.company_name
      FROM sales_customer_price scp
      LEFT JOIN products p 
        ON scp.product_id = p.id
      LEFT JOIN customers c 
        ON scp.customer_id = c.id
      WHERE scp.id = $1
        AND scp.is_deleted = false
      `,
      [id],
    );

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

    res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (error) {
    console.error("GET PRICE BY ID ERROR:", error);
    res.status(500).json({
      success: false,
      message: "Fetch failed",
    });
  }
};
/* ===========================
    UPDATE CUSTOMER
=========================== */
const updateCustomer = async (req, res) => {
  try {
    const { id } = req.params;
    const userId = req.session.user.id;
    const {
      gst,
      companyName,
      companyType,
      contactPerson,
      phone,
      email,
      pan,
      license,
      aadhaar,

      billingAddress,
      shippingAddress,
      billState,
      billCity,
      billPinCode,
      shipState,
      shipCity,
      shipPinCode,

      creditLimit,
      paymentTerms,
      advancePayment,
      payAgainstDelivery,
      deliveryAgainstPayment,
      status,
    } = req.body;

    const files = req.files || {};
    const formatPath = (filePath) => {
      if (!filePath) return null;
      return filePath.replace(/\\/g, "/").split("uploads/")[1]
        ? "uploads/" + filePath.replace(/\\/g, "/").split("uploads/")[1]
        : filePath;
    };

    const panFile = formatPath(files.panFile?.[0]?.path);
    const licenseFile = formatPath(files.licenseFile?.[0]?.path);
    const aadhaarFile = formatPath(files.aadhaarFile?.[0]?.path);
    const incorporationFile = formatPath(files.incorporationFile?.[0]?.path);
    const chequeFile = formatPath(files.chequeFile?.[0]?.path);

    const toNumber = (val) => (val !== undefined ? Number(val) : null);

    const query = `
      UPDATE customers SET
  gst = COALESCE($1, gst),
  company_name = COALESCE($2, company_name),
  company_type = COALESCE($3, company_type),
  contact_person = COALESCE($4, contact_person),
  phone = COALESCE($5, phone),
  email = COALESCE($6, email),
  pan = COALESCE($7, pan),
  license = COALESCE($8, license),
  aadhaar = COALESCE($9, aadhaar),

  billing_address = COALESCE($10, billing_address),
  shipping_address = COALESCE($11, shipping_address),
  bill_state = COALESCE($12, bill_state),
  bill_city = COALESCE($13, bill_city),
  bill_pin_code = COALESCE($14, bill_pin_code),
  ship_state = COALESCE($15, ship_state),
  ship_city = COALESCE($16, ship_city),
  ship_pin_code = COALESCE($17, ship_pin_code),

  credit_limit = COALESCE($18, credit_limit),
  payment_terms = COALESCE($19, payment_terms),
  advance_payment = COALESCE($20, advance_payment),
  pay_against_delivery = COALESCE($21, pay_against_delivery),
  delivery_against_payment = COALESCE($22, delivery_against_payment),

  status = COALESCE($23, status),

  pan_file = COALESCE($24, pan_file),
  license_file = COALESCE($25, license_file),
  aadhaar_file = COALESCE($26, aadhaar_file),
  incorporation_file = COALESCE($27, incorporation_file),
  cheque_file = COALESCE($28, cheque_file),
  updated_by = $29,

  updated_at = NOW()

WHERE customer_code = $30
RETURNING *;
    `;

    const values = [
      gst,
      companyName,
      companyType,
      contactPerson,
      phone,
      email,
      pan,
      license,
      aadhaar,

      billingAddress,
      shippingAddress,
      billState,
      billCity,
      billPinCode,
      shipState,
      shipCity,
      shipPinCode,

      toNumber(creditLimit),
      paymentTerms,
      toNumber(advancePayment),
      toNumber(payAgainstDelivery),
      toNumber(deliveryAgainstPayment),

      status || "active",

      panFile,
      licenseFile,
      aadhaarFile,
      incorporationFile,
      chequeFile,
      userId,
      id,
    ];

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

    const customerCode = result.rows[0].customer_code;

    // if shipping is updated → sync table
    if (shippingAddress) {
      // 1. remove old default
      await pool.query(
        `UPDATE customer_addresses 
     SET is_default = false 
     WHERE customer_code = $1`,
        [customerCode],
      );

      // 2. insert new default
      await pool.query(
        `INSERT INTO customer_addresses
     (customer_code, address, city, state, pin_code, is_default)
     VALUES ($1,$2,$3,$4,$5,true)`,
        [customerCode, shippingAddress, shipCity, shipState, shipPinCode],
      );
    }

    res.json({
      success: true,
      message: "Customer updated successfully",
      data: result.rows[0],
    });
  } catch (err) {
  console.error("UPDATE CUSTOMER ERROR:", err);

  // 🔥 PostgreSQL duplicate key error
  if (err.code === "23505") {
    return res.status(400).json({
      success: false,
      message: "Email already exists",
    });
  }

  return res.status(500).json({
    success: false,
    message: err.message || "Update failed",
  });
}
};

/* ===========================
   ✅ DELETE (SOFT DELETE)
=========================== */
const deleteCustomer = async (req, res) => {
  try {
    const { id } = req.params;

    await pool.query(
      "UPDATE customers SET is_deleted = true WHERE customer_code = $1",
      [id],
    );

    res.json({
      success: true,
      message: "Customer deleted successfully",
    });
  } catch (err) {
    res.status(500).json({ message: "Delete failed" });
  }
};

module.exports = {
  createCustomer,
  getCustomers,
  updateCustomer,
  deleteCustomer,
  getCustomerById,
  getSalesCustomerPriceById,
  downloadCustomerTemplate,
  bulkUploadCustomers,
  checkGstDuplicate,
};
