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

// ================= CUSTOMER SALES REPORT API =================
// install:
// npm install exceljs
// const pool = require("../db"); // your postgres connection
const ExcelJS = require("exceljs");


// ============================================================
// 1. GET CUSTOMER SALES REPORT
// ============================================================

const getCustomerReport = async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT
        so.customer_id,
        so.customer_name AS customer,

        COUNT(DISTINCT so.id) AS total_orders,

        COUNT(DISTINCT si.id) AS total_invoices,

        COALESCE(SUM(so.total_amount), 0) AS total_revenue,

        COALESCE(
          SUM(
            CASE
              WHEN si.id IS NULL THEN 1
              ELSE 0
            END
          ), 0
        ) AS pending_qty

      FROM sales_orders so

      LEFT JOIN sales_invoices si
        ON si.sales_order_number = so.so_number

      WHERE so.is_deleted = false

      GROUP BY
        so.customer_id,
        so.customer_name

      ORDER BY total_revenue DESC
    `);

    return res.status(200).json({
      success: true,
      message: "Customer sales report fetched successfully",
      data: result.rows,
    });

  } catch (error) {
    console.log(error);

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

// ============================================================
// 2. EXPORT CUSTOMER SALES REPORT EXCEL
// ============================================================
const getCustomerExcelReport = async (req, res) => {
  try {

    const result = await pool.query(`
      SELECT
        so.customer_id,
        so.customer_name AS customer,

        COUNT(DISTINCT so.id) AS total_orders,

        COUNT(DISTINCT si.id) AS total_invoices,

        COALESCE(SUM(so.total_amount), 0) AS total_revenue,

        COALESCE(
          SUM(
            CASE
              WHEN si.id IS NULL THEN 1
              ELSE 0
            END
          ), 0
        ) AS pending_qty

      FROM sales_orders so

      LEFT JOIN sales_invoices si
        ON si.sales_order_number = so.so_number

      WHERE so.is_deleted = false

      GROUP BY
        so.customer_id,
        so.customer_name

      ORDER BY total_revenue DESC
    `);

    const workbook = new ExcelJS.Workbook();

    const worksheet = workbook.addWorksheet("Customer Sales Report");

    worksheet.columns = [
      { header: "Customer", key: "customer", width: 35 },
      { header: "Total Orders", key: "total_orders", width: 20 },
      { header: "Total Invoices", key: "total_invoices", width: 20 },
      { header: "Total Revenue", key: "total_revenue", width: 20 },
      { header: "Pending Qty", key: "pending_qty", width: 20 },
    ];

    result.rows.forEach((row) => {
      worksheet.addRow({
        customer: row.customer,
        total_orders: row.total_orders,
        total_invoices: row.total_invoices,
        total_revenue: row.total_revenue,
        pending_qty: row.pending_qty,
      });
    });

    // Header Style
    worksheet.getRow(1).font = {
      bold: true,
    };

    worksheet.getRow(1).alignment = {
      vertical: "middle",
      horizontal: "center",
    };

    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );

    res.setHeader(
      "Content-Disposition",
      "attachment; filename=customer-sales-report.xlsx"
    );

    await workbook.xlsx.write(res);

    res.end();

  } catch (error) {
    console.log(error);

    return res.status(500).json({
      success: false,
      message: "Internal server error",
    });
  }
};
// ============================================================
// ORDER REPORT API
// ============================================================
// ============================================================
// GET SALES ORDER REPORT
// ============================================================


// const getOrderReport = async (req, res) => {

//   try {

//     const result = await pool.query(`
//       SELECT
//         so.id,
//         so.so_number,
//         so.customer_name,
//         so.total_amount AS amount,
        
//     LOWER(
//       COALESCE(so.approval_status, 'pending')
//     ) AS approval_status,

//         COALESCE(
//           SUM(soi.qty),
//           0
//         ) AS qty

//       FROM sales_orders so

//       LEFT JOIN sales_order_items soi
//         ON soi.sales_order_id = so.id
//         AND soi.is_deleted = false

//       WHERE so.is_deleted = false

//       GROUP BY
//         so.id,
//         so.so_number,
//         so.customer_name,
//         so.total_amount,
//         so.status

//       ORDER BY so.id DESC
//     `);

//     return res.status(200).json({
//       success: true,
//       message: "Sales order report fetched successfully",
//       data: result.rows,
//     });

//   } catch (error) {

//     console.log(error);

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


const getOrderReport = async (req, res) => {
  try {

    const result = await pool.query(`
     SELECT
  so.id,
  so.so_number,
  so.customer_name,
  so.total_amount AS amount,

  LOWER(
    COALESCE(so.approval_status, 'pending')
  ) AS approval_status,

  -- NEW FIELDS
  COALESCE(
    so.with_advance_payment,
    false
  ) AS with_advance_payment,

  CASE
    WHEN so.with_advance_payment = true
    THEN LOWER(
      COALESCE(
        so.advance_payment_status,
        'pending'
      )
    )
    ELSE NULL
  END AS advance_payment_status,

  -- ADD THESE
  so.delivery_date,

  soi.product_name,
  soi.uom_symbol,
  soi.rate,

  COALESCE(
    SUM(soi.qty),
    0
  ) AS qty

FROM sales_orders so

LEFT JOIN sales_order_items soi
  ON soi.sales_order_id = so.id
  AND soi.is_deleted = false

WHERE so.is_deleted = false

GROUP BY
  so.id,
  so.so_number,
  so.customer_name,
  so.total_amount,
  so.approval_status,
  so.with_advance_payment,
  so.advance_payment_status,

  -- ADD THESE ALSO
  so.delivery_date,

  soi.product_name,
  soi.uom_symbol,
  soi.rate

ORDER BY so.id DESC
    `);

    return res.status(200).json({
      success: true,
      message:
        "Sales order report fetched successfully",
      data: result.rows,
    });

  } catch (error) {

    console.log(error);

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


// ============================================================
// EXPORT SALES ORDER REPORT EXCEL
// ============================================================
// ============================================================
// EXPORT SALES ORDER REPORT EXCEL
// ============================================================

const getOrderExcelReport = async (req, res) => {

  try {

    const result = await pool.query(`
      SELECT
        so.id,
        so.so_number,
        so.customer_name,
        so.total_amount AS amount,
        so.approval_status,
        so.status,

        -- EXISTING + NEW FIELDS
        so.delivery_date,

        soi.product_name,
        soi.uom_symbol,
        soi.rate,
        soi.qty

      FROM sales_orders so

      LEFT JOIN sales_order_items soi
        ON soi.sales_order_id = so.id
        AND soi.is_deleted = false

      WHERE so.is_deleted = false

      ORDER BY so.id DESC
    `);

    const workbook = new ExcelJS.Workbook();

    const worksheet =
      workbook.addWorksheet(
        "Sales Order Report"
      );

    // UPDATED COLUMNS
    worksheet.columns = [
      {
        header: "SO Number",
        key: "so_number",
        width: 20,
      },
      {
        header: "Customer",
        key: "customer_name",
        width: 35,
      },
      {
        header: "Product",
        key: "product_name",
        width: 25,
      },
      {
        header: "Qty",
        key: "qty",
        width: 15,
      },
      {
        header: "UOM",
        key: "uom_symbol",
        width: 12,
      },
      {
        header: "Rate",
        key: "rate",
        width: 15,
      },
      {
        header: "Total Amount",
        key: "amount",
        width: 20,
      },
      {
        header: "Delivery Date",
        key: "delivery_date",
        width: 20,
      },
      {
        header: "Approval",
        key: "status",
        width: 20,
      },
    ];

    // UPDATED ROWS
    result.rows.forEach((row) => {

      worksheet.addRow({

        so_number: row.so_number,

        customer_name: row.customer_name,

        product_name: row.product_name || "-",

        qty: row.qty || 0,

        uom_symbol: row.uom_symbol || "-",

        rate: `₹ ${Number(
          row.rate || 0
        ).toLocaleString("en-IN")}`,

        amount: `₹ ${Number(
          row.amount || 0
        ).toLocaleString("en-IN")}`,

        delivery_date: row.delivery_date
          ? new Date(
              row.delivery_date
            ).toLocaleDateString("en-GB")
          : "-",

        status: row.approval_status || "-",
      });

    });

    // HEADER STYLE
    worksheet.getRow(1).font = {
      bold: true,
    };

    worksheet.getRow(1).alignment = {
      horizontal: "center",
      vertical: "middle",
    };

    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );

    res.setHeader(
      "Content-Disposition",
      "attachment; filename=sales-order-report.xlsx"
    );

    await workbook.xlsx.write(res);

    res.end();

  } catch (error) {

    console.log(error);

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


// ============================================================
// GET INVOICE REPORT
// ============================================================

const getInvoiceReport = async (req, res) => {

  try {

  const result = await pool.query(`
  SELECT
    si.id,

    si.invoice_number,

    si.customer_name,

    si.invoice_date,

    si.due_date,

    -- EXISTING TABLE FIELDS
    si.amount,

    si.total_discount,

    si.total_tax,

    si.total_amount,

    si.status,

    -- ADVANCE RELATED
    si.advance_adjusted_amount,

    si.balance_amount,

    si.balance_payable_amount,

    -- SO REF
    si.sales_order_number,

    -- ITEM DETAILS
    sii.product_name,

    sii.qty,

    sii.rate,

    sii.uom_symbol,

    sii.gst_percent,

    sii.gst_amount

  FROM sales_invoices si

  LEFT JOIN sales_invoice_items sii
    ON sii.invoice_id = si.id
    AND sii.is_deleted = false

  WHERE si.is_deleted = false

  ORDER BY si.id DESC
`);

    return res.status(200).json({
      success: true,
      message: "Invoice report fetched successfully",
      data: result.rows,
    });

  } catch (error) {

    console.log(error);

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

// ============================================================
// EXPORT INVOICE REPORT EXCEL
// ============================================================

const exportInvoiceReportExcel = async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT
        si.id,
        si.invoice_number,
        si.customer_name,
        si.sales_order_number,
        si.invoice_date,

        -- AMOUNTS
        si.total_amount AS amount,
        si.advance_adjusted_amount,
        si.balance_payable_amount,

        -- STATUS
        si.status,

        -- TOTAL QTY
        COALESCE(
          SUM(sii.qty),
          0
        ) AS qty

      FROM sales_invoices si

      LEFT JOIN sales_invoice_items sii
        ON sii.invoice_id = si.id
        AND sii.is_deleted = false

      WHERE si.is_deleted = false

      GROUP BY
        si.id,
        si.invoice_number,
        si.customer_name,
        si.sales_order_number,
        si.invoice_date,
        si.total_amount,
        si.advance_adjusted_amount,
        si.balance_payable_amount,
        si.status

      ORDER BY si.id DESC
    `);

    const workbook = new ExcelJS.Workbook();

    const worksheet =
      workbook.addWorksheet(
        "Invoice Report"
      );

    worksheet.columns = [
      {
        header: "Invoice Number",
        key: "invoice_number",
        width: 25,
      },
      {
        header: "Customer",
        key: "customer_name",
        width: 35,
      },
      {
        header: "SO Ref",
        key: "sales_order_number",
        width: 20,
      },
       {
        header: "Invoice Date",
        key: "invoice_date",
        width: 20,
      },
      {
        header: "Total Amount",
        key: "amount",
        width: 20,
      },
      {
        header: "Advance",
        key: "advance_adjusted_amount",
        width: 20,
      },
      {
        header: "Balance",
        key: "balance_payable_amount",
        width: 20,
      },
     
      {
        header: "Status",
        key: "status",
        width: 20,
      },
    ];

    result.rows.forEach((row) => {
      worksheet.addRow({
        invoice_number: row.invoice_number,
        customer_name: row.customer_name,
        sales_order_number:
          row.sales_order_number || "-",

           invoice_date: row.invoice_date
          ? new Date(row.invoice_date)
              .toLocaleDateString("en-GB")
          : "-",

      

        amount: row.amount,

        advance_adjusted_amount:
          row.advance_adjusted_amount || "-",

        balance_payable_amount:
          row.balance_payable_amount || 0,

       

        status: row.status,
      });
    });

    // HEADER STYLE
    worksheet.getRow(1).font = {
      bold: true,
    };

    worksheet.getRow(1).alignment = {
      horizontal: "center",
      vertical: "middle",
    };

    // OPTIONAL: CENTER ALIGN ALL
    worksheet.eachRow((row) => {
      row.alignment = {
        vertical: "middle",
      };
    });

    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );

    res.setHeader(
      "Content-Disposition",
      "attachment; filename=invoice-report.xlsx"
    );

    await workbook.xlsx.write(res);

    res.end();

  } catch (error) {

    console.log(error);

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

  }
};

// ============================================================
// GET DISPATCH REPORT
// ============================================================

const getDispatchReport = async (req, res) => {

  try {

    const result = await pool.query(`
      SELECT
        sd.id,
        sd.shipment_number,
        sd.vehicle_number,
        sd.customer_name,
        sd.shipment_status AS status,

        COALESCE(
          SUM(sdi.dispatch_qty),
          0
        ) AS qty

      FROM shipment_dispatches sd

      LEFT JOIN shipment_dispatch_items sdi
        ON sdi.shipment_id = sd.id

      WHERE sd.is_deleted = false

      GROUP BY
        sd.id,
        sd.shipment_number,
        sd.vehicle_number,
        sd.customer_name,
        sd.shipment_status

      ORDER BY sd.id DESC
    `);

    return res.status(200).json({
      success: true,
      message: "Dispatch report fetched successfully",
      data: result.rows,
    });

  } catch (error) {

    console.log(error);

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

// ============================================================
// EXPORT DISPATCH REPORT EXCEL
// ============================================================

const exportDispatchReportExcel = async (req, res) => {

  try {

    const result = await pool.query(`
      SELECT
        sd.id,
        sd.shipment_number,
        sd.vehicle_number,
        sd.customer_name,
        sd.shipment_status AS status,

        COALESCE(
          SUM(sdi.dispatch_qty),
          0
        ) AS qty

      FROM shipment_dispatches sd

      LEFT JOIN shipment_dispatch_items sdi
        ON sdi.shipment_id = sd.id

      WHERE sd.is_deleted = false

      GROUP BY
        sd.id,
        sd.shipment_number,
        sd.vehicle_number,
        sd.customer_name,
        sd.shipment_status

      ORDER BY sd.id DESC
    `);

    const workbook = new ExcelJS.Workbook();

    const worksheet =
      workbook.addWorksheet(
        "Dispatch Report"
      );

    worksheet.columns = [
      {
        header: "Shipment Number",
        key: "shipment_number",
        width: 25,
      },
      {
        header: "Vehicle Number",
        key: "vehicle_number",
        width: 25,
      },
      {
        header: "Customer",
        key: "customer_name",
        width: 35,
      },
      {
        header: "Qty",
        key: "qty",
        width: 15,
      },
      {
        header: "Status",
        key: "status",
        width: 20,
      },
    ];

    result.rows.forEach((row) => {

      worksheet.addRow({
        shipment_number: row.shipment_number,
        vehicle_number: row.vehicle_number,
        customer_name: row.customer_name,
        qty: row.qty,
        status: row.status,
      });

    });

    // HEADER STYLE

    worksheet.getRow(1).font = {
      bold: true,
    };

    worksheet.getRow(1).alignment = {
      horizontal: "center",
      vertical: "middle",
    };

    res.setHeader(
      "Content-Type",
      "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
    );

    res.setHeader(
      "Content-Disposition",
      "attachment; filename=dispatch-report.xlsx"
    );

    await workbook.xlsx.write(res);

    res.end();

  } catch (error) {

    console.log(error);

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


module.exports = {
    getCustomerReport,
    getCustomerExcelReport,
    getOrderReport,
    getOrderExcelReport,
    getInvoiceReport,
    exportInvoiceReportExcel,
    getDispatchReport,
    exportDispatchReportExcel,
};