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

// Get all GRNs with pagination
const getAllGrns = async (req, res) => {
  try {
    const page = parseInt(req.query.page) || 1;
    const limit = parseInt(req.query.limit) || 10;
    const offset = (page - 1) * limit;
    const search = req.query.search || "";
    const status = req.query.status || "";

    console.log("Fetching GRNs with params:", { page, limit, offset, search, status });

    let whereConditions = ["g.is_deleted = false"];
    const queryParams = [];
    let paramIndex = 1;

    if (search) {
      whereConditions.push(`(
        g.grn_number ILIKE $${paramIndex} OR 
        COALESCE(g.po_number, '') ILIKE $${paramIndex} OR 
        COALESCE(g.vendor_name, '') ILIKE $${paramIndex} OR
        COALESCE(g.gate_entry_no, '') ILIKE $${paramIndex}
      )`);
      queryParams.push(`%${search}%`);
      paramIndex++;
    }

    if (status) {
      whereConditions.push(`COALESCE(g.grn_status, 'Draft') = $${paramIndex}`);
      queryParams.push(status);
      paramIndex++;
    }

    const whereClause = whereConditions.join(" AND ");

    const query = `
      SELECT 
        g.id,
        g.grn_number,
        g.grn_date,
        g.po_number,
        g.vendor_name,
        g.gate_entry_no,
        g.invoice_no,
        g.delivery_challan_no,
        g.remarks,
        COALESCE(g.grn_status, 'Draft') as grn_status,
        g.created_at,
        g.vehicle_id,
        cv.vehicle_number,
        COALESCE(COUNT(gi.id), 0) as item_count,
        COALESCE(SUM(gi.received_qty), 0) as total_received,
        COALESCE(SUM(gi.rejected_qty), 0) as total_rejected
      FROM grn g
      LEFT JOIN commercial_vehicles cv ON cv.id = g.vehicle_id
      LEFT JOIN grn_items gi ON gi.grn_id = g.id
      WHERE ${whereClause}
      GROUP BY g.id, cv.vehicle_number, g.grn_number, g.grn_date, g.po_number, 
               g.vendor_name, g.gate_entry_no, g.invoice_no, g.delivery_challan_no, 
               g.remarks, g.grn_status, g.created_at, g.vehicle_id, cv.vehicle_number
      ORDER BY g.created_at DESC
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
    `;

    queryParams.push(limit, offset);
    
    const result = await pool.query(query, queryParams);

    let countQuery = `SELECT COUNT(*) FROM grn g WHERE ${whereClause}`;
    const countParams = queryParams.slice(0, -2);
    const countResult = await pool.query(countQuery, countParams);
    const totalCount = parseInt(countResult.rows[0].count);

    return res.status(200).json({
      success: true,
      data: result.rows,
      pagination: {
        total: totalCount,
        page,
        limit,
        totalPages: Math.ceil(totalCount / limit),
      },
    });
  } catch (error) {
    console.error("Get All GRNs Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch GRNs",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

// Get all GRNs without pagination
const getAllGrnsWithoutPagination = async (req, res) => {
  try {
    const result = await pool.query(
      `
      SELECT 
        g.id,
        g.grn_number,
        g.grn_date,
        g.po_number,
        g.vendor_name,
        COALESCE(g.grn_status, 'Draft') as grn_status
      FROM grn g
      WHERE g.is_deleted = false
      ORDER BY g.created_at DESC
      `
    );

    return res.status(200).json({
      success: true,
      data: result.rows,
    });
  } catch (error) {
    console.error("Get All GRNs Without Pagination Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch GRNs",
    });
  }
};

const getGrnById = async (req, res) => {
  try {
    const { id } = req.params;

    const grnResult = await pool.query(
      `
      SELECT 
        g.id,
        g.grn_number,
        g.grn_date,
        g.po_number,
        g.vendor_name,
        g.gate_entry_no,
        g.invoice_no,
        g.delivery_challan_no,
        g.remarks,
        g.vehicle_id,
        g.org_id,
        COALESCE(g.grn_status, 'Draft') as grn_status,
        g.created_at,
        cv.vehicle_number,
        cv.driver_name,
        cv.driver_number,
        cv.movement_type,
        cv.in_weight,
        cv.out_weight,
        cv.warehouse_id,
        w.warehouse_name,
        w.warehouse_code,
        u.first_name as created_by_name,
        u2.first_name as updated_by_name,
        pv.id as vendor_id,
        pv.vendor_code,
        pv.contact_person,
        pv.phone as vendor_phone,
        pv.email as vendor_email,
        pv.gstin as vendor_gstin,
        pv.pan as vendor_pan,
        pv.address as vendor_address,
        pv.city as vendor_city,
        pv.state as vendor_state,
        pv.country as vendor_country,
        pv.pincode as vendor_pincode,
        pv.shipping_addresses,
        pv.license_number as vendor_license_number
      FROM grn g
      LEFT JOIN commercial_vehicles cv ON cv.id = g.vehicle_id
      LEFT JOIN warehouses w ON w.id = cv.warehouse_id
      LEFT JOIN users u ON u.id = g.created_by
      LEFT JOIN users u2 ON u2.id = g.updated_by
      LEFT JOIN procurement_vendors pv ON pv.vendor_name = g.vendor_name AND pv.org_id = g.org_id AND pv.is_deleted = false
      WHERE g.id = $1 AND g.is_deleted = false
      `,
      [id]
    );

    console.log("=== DEBUG GRN VENDOR JOIN ===");
    console.log("GRN ID:", id);
    console.log("GRN vendor_name:", grnResult.rows[0]?.vendor_name);
    console.log("GRN org_id:", grnResult.rows[0]?.org_id);
    console.log("Vendor ID from join:", grnResult.rows[0]?.vendor_id);
    console.log("Vendor details found:", grnResult.rows[0]?.vendor_id ? "YES" : "NO");
    console.log("Full row keys:", Object.keys(grnResult.rows[0] || {}));

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

    const grn = grnResult.rows[0];

    console.log("vendor_address from DB:", grn.vendor_address);
    console.log("vendor_city from DB:", grn.vendor_city);
    console.log("vendor_state from DB:", grn.vendor_state);
    console.log("vendor_pincode from DB:", grn.vendor_pincode);

    const itemsResult = await pool.query(
      `
      SELECT 
        gi.id,
        gi.grn_id,
        gi.item_name,
        gi.ordered_qty,
        gi.received_qty,
        gi.rejected_qty,
        gi.accepted_qty,
        gi.unit_price,
        gi.total_price,
        gi.rejected_unit_price,
        gi.rejected_total_price,
        gi.grand_total,
        gi.batch_number,
        gi.mfg_date,
        gi.expiry_date,
        gi.seal_number,
        gi.store_location,
        gi.created_at,
        gi.updated_at
      FROM grn_items gi
      WHERE gi.grn_id = $1
      ORDER BY gi.id ASC
      `,
      [id]
    );

    const vendorAddressParts = [];
    if (grn.vendor_address) vendorAddressParts.push(grn.vendor_address);
    if (grn.vendor_city) vendorAddressParts.push(grn.vendor_city);
    if (grn.vendor_state) vendorAddressParts.push(grn.vendor_state);
    if (grn.vendor_pincode) vendorAddressParts.push(grn.vendor_pincode);
    if (grn.vendor_country) vendorAddressParts.push(grn.vendor_country);
    
    const fullAddress = vendorAddressParts.length > 0 ? vendorAddressParts.join(', ') : null;

    console.log("Built fullAddress:", fullAddress);
    console.log("vendorDetails exists:", grn.vendor_id ? "YES" : "NO");

    const vendorDetails = grn.vendor_id ? {
      id: grn.vendor_id,
      vendor_code: grn.vendor_code,
      vendor_name: grn.vendor_name,
      contact_person: grn.contact_person,
      phone: grn.vendor_phone,
      email: grn.vendor_email,
      gstin: grn.vendor_gstin,
      pan: grn.vendor_pan,
      address: grn.vendor_address,
      city: grn.vendor_city,
      state: grn.vendor_state,
      country: grn.vendor_country,
      pincode: grn.vendor_pincode,
      full_address: fullAddress,
      shipping_addresses: grn.shipping_addresses,
      license_number: grn.vendor_license_number
    } : null;

    return res.status(200).json({
      success: true,
      data: {
        ...grn,
        vendor_details: vendorDetails,
        items: itemsResult.rows,
      },
    });
  } catch (error) {
    console.error("Get GRN By ID Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch GRN details",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};

const updateGrnItems = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { grnId } = req.params;
    const { items } = req.body;
    const userId = req.session?.user?.id || null;

    if (!items || !Array.isArray(items) || items.length === 0) {
      return res.status(400).json({
        success: false,
        message: "Items array is required"
      });
    }

    const grnCheck = await client.query(
      "SELECT id FROM grn WHERE id = $1 AND is_deleted = false",
      [grnId]
    );
    
    if (grnCheck.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "GRN not found"
      });
    }

    await client.query("BEGIN");

    const updatedItems = [];
    
    for (const item of items) {
      const acceptedQty = parseFloat(item.accepted_qty) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const acceptedTotalPrice = acceptedQty * unitPrice;
      
      const rejectedQty = parseFloat(item.rejected_qty) || 0;
      const rejectedUnitPrice = parseFloat(item.rejected_unit_price) || 0;
      const rejectedTotalPrice = rejectedQty * rejectedUnitPrice;
      
      const grandTotal = acceptedTotalPrice + rejectedTotalPrice;
      
      const result = await client.query(
        `UPDATE grn_items 
        SET 
          ordered_qty = COALESCE($1, ordered_qty),
          received_qty = COALESCE($2, received_qty),
          accepted_qty = COALESCE($3, accepted_qty),
          rejected_qty = COALESCE($4, rejected_qty),
          unit_price = COALESCE($5, unit_price),
          total_price = $6,
          rejected_unit_price = COALESCE($7, rejected_unit_price),
          rejected_total_price = $8,
          grand_total = $9,
          batch_number = COALESCE($10, batch_number),
          mfg_date = $11,
          expiry_date = $12,
          seal_number = COALESCE($13, seal_number),
          store_location = COALESCE($14, store_location),
          updated_at = NOW()
        WHERE id = $15 AND grn_id = $16
        RETURNING *
        `,
        [
          item.ordered_qty || 0,
          item.received_qty || 0,
          acceptedQty,
          rejectedQty,
          unitPrice,
          acceptedTotalPrice,
          rejectedUnitPrice,
          rejectedTotalPrice,
          grandTotal,
          item.batch_number || null,
          item.mfg_date || null,
          item.expiry_date || null,
          item.seal_number || null,
          item.store_location || 'Main Store',
          item.id,
          grnId
        ]
      );
      
      if (result.rows[0]) {
        updatedItems.push(result.rows[0]);
        console.log(`Updated item ${item.id}:`, {
          accepted_total: acceptedTotalPrice,
          rejected_total: rejectedTotalPrice,
          grand_total: grandTotal
        });
      }
    }

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: `${updatedItems.length} items updated successfully`,
      data: updatedItems
    });
    
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Batch Update GRN Items Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to update GRN items",
      error: process.env.NODE_ENV === "development" ? error.message : undefined
    });
  } finally {
    client.release();
  }
};

// Update GRN header
const updateGrnHeader = async (req, res) => {
  try {
    const { grnId } = req.params;
    const { remarks, invoice_no, delivery_challan_no } = req.body;
    const userId = req.session?.user?.id || null;

    const result = await pool.query(
      `
      UPDATE grn 
      SET 
        remarks = COALESCE($1, remarks),
        invoice_no = COALESCE($2, invoice_no),
        delivery_challan_no = COALESCE($3, delivery_challan_no),
        updated_by = $4,
        updated_at = NOW()
      WHERE id = $5 AND is_deleted = false
      RETURNING *
      `,
      [remarks, invoice_no, delivery_challan_no, userId, grnId]
    );

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

    return res.status(200).json({
      success: true,
      message: "GRN updated successfully",
      data: result.rows[0],
    });
  } catch (error) {
    console.error("Update GRN Header Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to update GRN",
    });
  }
};

// Delete GRN (soft delete)
const deleteGrn = async (req, res) => {
  try {
    const { id } = req.params;
    const userId = req.session?.user?.id || null;

    const result = await pool.query(
      `UPDATE grn 
       SET is_deleted = true, 
           updated_by = $2, 
           updated_at = NOW() 
       WHERE id = $1 AND is_deleted = false
       RETURNING id`,
      [id, userId]
    );

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

    return res.status(200).json({
      success: true,
      message: "GRN deleted successfully"
    });
  } catch (error) {
    console.error("Delete GRN Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to delete GRN"
    });
  }
};

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

  try {
    const {
      grnNumber,
      poId,
      poNumber,
      vendorName,
      gateEntryNo,
      invoiceNo,
      deliveryChallanNo,
      vehicleId,
      createdBy,
    } = req.body;

    if (!grnNumber || !vehicleId) {
      return res.status(400).json({
        success: false,
        message: "GRN number and vehicle ID are required",
      });
    }

    await client.query("BEGIN");

    const result = await client.query(
      `INSERT INTO grn (
        grn_number,
        grn_date,
        po_id,
        po_number,
        vendor_name,
        gate_entry_no,
        invoice_no,
        delivery_challan_no,
        vehicle_id,
        created_by,
        grn_status,
        created_at,
        updated_at
      )
      VALUES ($1, CURRENT_DATE, $2, $3, $4, $5, $6, $7, $8, $9, 'Draft', NOW(), NOW())
      RETURNING *`,
      [
        grnNumber,
        poId || null,
        poNumber || null,
        vendorName || null,
        gateEntryNo || null,
        invoiceNo || null,
        deliveryChallanNo || null,
        vehicleId,
        createdBy || null,
      ]
    );

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: "GRN created successfully",
      data: result.rows[0],
    });
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Create GRN Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to create GRN",
    });
  } finally {
    client.release();
  }
};

// Get GRN by Vehicle ID
const getGrnByVehicleId = async (req, res) => {
  try {
    const { vehicleId } = req.params;

    const result = await pool.query(
      `
      SELECT 
        g.*,
        COALESCE(g.grn_status, 'Draft') as grn_status,
        cv.vehicle_number,
        cv.driver_name,
        cv.driver_number
      FROM grn g
      LEFT JOIN commercial_vehicles cv ON cv.id = g.vehicle_id
      WHERE g.vehicle_id = $1 AND g.is_deleted = false
      ORDER BY g.created_at DESC
      LIMIT 1
      `,
      [vehicleId]
    );

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

    return res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (error) {
    console.error("Get GRN By Vehicle Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch GRN",
    });
  }
};

// Get GRN by GRN Number
const getGrnByGrnNumber = async (req, res) => {
  try {
    const { grnNumber } = req.params;

    const result = await pool.query(
      `
      SELECT 
        g.*,
        COALESCE(g.grn_status, 'Draft') as grn_status,
        cv.vehicle_number
      FROM grn g
      LEFT JOIN commercial_vehicles cv ON cv.id = g.vehicle_id
      WHERE g.grn_number = $1 AND g.is_deleted = false
      `,
      [grnNumber]
    );

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

    return res.status(200).json({
      success: true,
      data: result.rows[0],
    });
  } catch (error) {
    console.error("Get GRN By Number Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch GRN",
    });
  }
};

// Test endpoint
const testGrnData = async (req, res) => {
  try {
    const result = await pool.query(`
      SELECT 
        id,
        grn_number,
        grn_date,
        po_number,
        vendor_name,
        gate_entry_no
      FROM grn 
      WHERE is_deleted = false 
      LIMIT 5
    `);
    
    return res.status(200).json({
      success: true,
      message: "Test GRN data",
      data: result.rows,
    });
  } catch (error) {
    console.error("Test GRN Error:", error);
    return res.status(500).json({
      success: false,
      message: "Test failed",
      error: error.message
    });
  }
};

const path = require('path');
const puppeteer = require('puppeteer');
const fs = require('fs');


// Helper functions for PDF generation
const numberToWords = (num) => {
  const a = ["","ONE","TWO","THREE","FOUR","FIVE","SIX","SEVEN","EIGHT","NINE",
    "TEN","ELEVEN","TWELVE","THIRTEEN","FOURTEEN","FIFTEEN","SIXTEEN",
    "SEVENTEEN","EIGHTEEN","NINETEEN"];
  const b = ["","","TWENTY","THIRTY","FORTY","FIFTY","SIXTY","SEVENTY","EIGHTY","NINETY"];
  const inWords = (n) => {
    if (n < 20) return a[n];
    if (n < 100) return b[Math.floor(n/10)] + " " + a[n%10];
    if (n < 1000) return a[Math.floor(n/100)] + " HUNDRED " + inWords(n%100);
    if (n < 100000) return inWords(Math.floor(n/1000)) + " THOUSAND " + inWords(n%1000);
    if (n < 10000000) return inWords(Math.floor(n/100000)) + " LAKH " + inWords(n%100000);
    return inWords(Math.floor(n/10000000)) + " CRORE " + inWords(n%10000000);
  };
  if (!num || num === 0) return "ZERO";
  return inWords(Math.round(num)).trim();
};

const escapeHtml = (v) => {
  if (!v) return "";
  return v.toString()
    .replace(/&/g, "&amp;")
    .replace(/</g, "&lt;")
    .replace(/>/g, "&gt;")
    .replace(/"/g, "&quot;")
    .replace(/'/g, "&#39;");
};

const downloadGrnPdf = async (req, res) => {
  let browser = null;
  
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }

    const numberToWords = (num) => {
      const a = ["","ONE","TWO","THREE","FOUR","FIVE","SIX","SEVEN","EIGHT","NINE",
        "TEN","ELEVEN","TWELVE","THIRTEEN","FOURTEEN","FIFTEEN","SIXTEEN",
        "SEVENTEEN","EIGHTEEN","NINETEEN"];
      const b = ["","","TWENTY","THIRTY","FORTY","FIFTY","SIXTY","SEVENTY","EIGHTY","NINETY"];
      const inWords = (n) => {
        if (n < 20) return a[n];
        if (n < 100) return b[Math.floor(n/10)] + " " + a[n%10];
        if (n < 1000) return a[Math.floor(n/100)] + " HUNDRED " + inWords(n%100);
        if (n < 100000) return inWords(Math.floor(n/1000)) + " THOUSAND " + inWords(n%1000);
        if (n < 10000000) return inWords(Math.floor(n/100000)) + " LAKH " + inWords(n%100000);
        return inWords(Math.floor(n/10000000)) + " CRORE " + inWords(n%10000000);
      };
      if (!num || num === 0) return "ZERO";
      return inWords(Math.round(num)).trim();
    };

    const fmt = (num) => {
      const n = Number(num);
      if (isNaN(n)) return "0.00";
      return n.toLocaleString("en-IN", {
        minimumFractionDigits: 2,
        maximumFractionDigits: 2,
      });
    };

    const escapeHtml = (v) => (v || "").toString()
      .replace(/&/g,"&amp;").replace(/</g,"&lt;").replace(/>/g,"&gt;");

    // Fetch GRN data
    const result = await pool.query(
      `SELECT 
        g.id, g.grn_number, g.grn_date, g.po_number, g.vendor_name,
        g.gate_entry_no, g.invoice_no, g.delivery_challan_no,
        cv.vehicle_number,
        pv.contact_person, pv.phone as vendor_phone, pv.email as vendor_email,
        pv.gstin as vendor_gstin, pv.address as vendor_address,
        pv.city as vendor_city, pv.state as vendor_state,
        pv.pincode as vendor_pincode, pv.country as vendor_country,
        COALESCE(
          (SELECT json_agg(json_build_object(
            'item_name', gi.item_name,
            'ordered_qty', gi.ordered_qty,
            'received_qty', gi.received_qty,
            'accepted_qty', gi.accepted_qty,
            'rejected_qty', gi.rejected_qty,
            'unit_price', gi.unit_price,
            'rejected_unit_price', gi.rejected_unit_price,
            'store_location', gi.store_location
          ) ORDER BY gi.id)
          FROM grn_items gi WHERE gi.grn_id = g.id
        ), '[]'::json) as items
      FROM grn g
      LEFT JOIN commercial_vehicles cv ON cv.id = g.vehicle_id
      LEFT JOIN procurement_vendors pv ON pv.vendor_name = g.vendor_name 
        AND pv.org_id = g.org_id AND pv.is_deleted = false
      WHERE g.id = $1 AND g.org_id = $2 AND g.is_deleted = false`,
      [id, orgId]
    );

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

    const grn = result.rows[0];
    const items = grn.items || [];

    // Calculate totals
    let totalOrderedQty = 0, totalReceivedQty = 0, totalAcceptedQty = 0,
        totalRejectedQty = 0, totalAcceptedValue = 0, totalRejectedValue = 0, grandTotal = 0;

    const processedItems = items.map(item => {
      const orderedQty = parseFloat(item.ordered_qty) || 0;
      const receivedQty = parseFloat(item.received_qty) || 0;
      const acceptedQty = parseFloat(item.accepted_qty) || 0;
      const rejectedQty = parseFloat(item.rejected_qty) || 0;
      const unitPrice = parseFloat(item.unit_price) || 0;
      const rejectedUnitPrice = parseFloat(item.rejected_unit_price) || 0;
      const acceptedValue = acceptedQty * unitPrice;
      const rejectedValue = rejectedQty * rejectedUnitPrice;
      const itemTotal = acceptedValue + rejectedValue;

      totalOrderedQty += orderedQty;
      totalReceivedQty += receivedQty;
      totalAcceptedQty += acceptedQty;
      totalRejectedQty += rejectedQty;
      totalAcceptedValue += acceptedValue;
      totalRejectedValue += rejectedValue;
      grandTotal += itemTotal;

      return { ...item, orderedQty, receivedQty, acceptedQty, rejectedQty, 
               unitPrice, rejectedUnitPrice, acceptedValue, rejectedValue, itemTotal };
    });

    // Fetch company details
    let companyData = {
      name: "VIVEK BIO-PRODUCTS PRIVATE LIMITED",
      address: "Flat No. - 406, 4th Floor, Kadiris Apuroopa Urban, Botanical Garden Road Kondapur, Hyderabad, Telangana, India - 500084",
      gst: "36AAICV8996R1ZU",
      cin: "U24100TG2022PTC165236",
      phone: "8019637711",
      email: "sales@vivekbio.com",
    };

    try {
      const companyResult = await pool.query(
        `SELECT org_name, address, gst_number, cin_number, phone_number, email FROM organizations WHERE id = $1 LIMIT 1`,
        [orgId]
      );
      if (companyResult.rows.length > 0) {
        const org = companyResult.rows[0];
        companyData.name = org.org_name || companyData.name;
        companyData.address = org.address || companyData.address;
        companyData.gst = org.gst_number || companyData.gst;
        companyData.cin = org.cin_number || companyData.cin;
        companyData.phone = org.phone_number || companyData.phone;
        companyData.email = org.email || companyData.email;
      }
    } catch (err) {
      // Use defaults
    }

    // Build vendor address
    const vendorFullAddr = [
      grn.vendor_address, grn.vendor_city, grn.vendor_state, 
      grn.vendor_pincode, grn.vendor_country
    ].filter(Boolean).join(", ") || "N/A";

    // Build item rows
    let itemRows = '';
    processedItems.forEach((item, i) => {
      itemRows += `
        <tr>
          <td class="text-center">${i+1}</td>
          <td class="text-left">${escapeHtml(item.item_name || "-")}</td>
          <td class="text-center">${item.orderedQty.toFixed(2)}</td>
          <td class="text-center">${item.receivedQty.toFixed(2)}</td>
          <td class="text-center">${item.acceptedQty.toFixed(2)}</td>
          <td class="text-center">${item.rejectedQty.toFixed(2)}</td>
          <td class="text-right">${fmt(item.unitPrice)}</td>
          <td class="text-right">${fmt(item.acceptedValue)}</td>
          <td class="text-right">${fmt(item.rejectedUnitPrice)}</td>
          <td class="text-right">${fmt(item.rejectedValue)}</td>
          <td class="text-right">${fmt(item.itemTotal)}</td>
          <td class="text-center">${escapeHtml(item.store_location || "-")}</td>
        </tr>`;
    });

    const formattedDate = grn.grn_date ? new Date(grn.grn_date).toLocaleDateString("en-GB") : "-";
    const totalInWords = numberToWords(grandTotal);

    // Load template
    const templatePath = path.join(__dirname, "../../templates/grn-pdf-template.html");
    
    if (!fs.existsSync(templatePath)) {
      return res.status(404).json({
        success: false,
        message: `Template file not found at ${templatePath}`,
      });
    }
    
    let html = fs.readFileSync(templatePath, "utf8");

    // Replace placeholders
    html = html
      .replace(/{{COMPANY_LOGO}}/g, "")
      .replace(/{{COMPANY_NAME}}/g, escapeHtml(companyData.name))
      .replace(/{{COMPANY_ADDRESS}}/g, escapeHtml(companyData.address))
      .replace(/{{COMPANY_GST}}/g, escapeHtml(companyData.gst))
      .replace(/{{COMPANY_CIN}}/g, escapeHtml(companyData.cin))
      .replace(/{{COMPANY_PHONE}}/g, escapeHtml(companyData.phone))
      .replace(/{{COMPANY_EMAIL}}/g, escapeHtml(companyData.email))
      .replace(/{{COMPANY_CITY}}/g, "Hyderabad")
      .replace(/{{GRN_NUMBER}}/g, escapeHtml(grn.grn_number))
      .replace(/{{GRN_DATE}}/g, formattedDate)
      .replace(/{{VENDOR_NAME}}/g, escapeHtml(grn.vendor_name || "N/A"))
      .replace(/{{VENDOR_CONTACT}}/g, escapeHtml(grn.contact_person || "N/A"))
      .replace(/{{VENDOR_PHONE}}/g, escapeHtml(grn.vendor_phone || "N/A"))
      .replace(/{{VENDOR_EMAIL}}/g, escapeHtml(grn.vendor_email || "N/A"))
      .replace(/{{VENDOR_GSTIN}}/g, escapeHtml(grn.vendor_gstin || "N/A"))
      .replace(/{{VENDOR_ADDRESS}}/g, escapeHtml(vendorFullAddr))
      .replace(/{{PO_REFERENCE}}/g, escapeHtml(grn.po_number || "N/A"))
      .replace(/{{INVOICE_NO}}/g, escapeHtml(grn.invoice_no || "N/A"))
      .replace(/{{DELIVERY_CHALLAN}}/g, escapeHtml(grn.delivery_challan_no || "N/A"))
      .replace(/{{GATE_ENTRY_NO}}/g, escapeHtml(grn.gate_entry_no || "N/A"))
      .replace(/{{VEHICLE_NO}}/g, escapeHtml(grn.vehicle_number || "N/A"))
      .replace(/{{ITEMS_ROWS}}/g, itemRows)
      .replace(/{{TOTAL_ORDERED_QTY}}/g, totalOrderedQty)
      .replace(/{{TOTAL_RECEIVED_QTY}}/g, totalReceivedQty)
      .replace(/{{TOTAL_ACCEPTED_QTY}}/g, totalAcceptedQty)
      .replace(/{{TOTAL_REJECTED_QTY}}/g, totalRejectedQty)
      .replace(/{{TOTAL_ACCEPTED_VALUE}}/g, fmt(totalAcceptedValue))
      .replace(/{{TOTAL_REJECTED_VALUE}}/g, fmt(totalRejectedValue))
      .replace(/{{GRAND_TOTAL}}/g, fmt(grandTotal))
      .replace(/{{AMOUNT_IN_WORDS}}/g, totalInWords)
      .replace(/{{RECEIVED_BY}}/g, "System")
      .replace(/{{VERIFIED_BY}}/g, "System")
      .replace(/{{STORE_KEEPER}}/g, "System");

    // Generate PDF
    browser = await puppeteer.launch({
      headless: 'new',
      args: ['--no-sandbox', '--disable-setuid-sandbox', '--disable-dev-shm-usage']
    });
    
    const page = await browser.newPage();
    await page.setContent(html, { waitUntil: 'networkidle0', timeout: 15000 });
    
    const pdfBuffer = await page.pdf({
      format: 'A4',
      printBackground: true,
      margin: { top: '15px', right: '15px', bottom: '15px', left: '15px' }
    });
    
    await browser.close();
    
    res.setHeader('Content-Type', 'application/pdf');
    res.setHeader('Content-Disposition', `attachment; filename="GRN-${grn.grn_number}.pdf"`);
    return res.send(pdfBuffer);

  } catch (error) {
    if (browser) await browser.close().catch(console.error);
    console.error("Download GRN PDF Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to generate PDF",
      error: process.env.NODE_ENV === "development" ? error.message : undefined,
    });
  }
};



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

  try {
    const {
      grn_number,
      grn_date,
      vendor_name,
      po_number,
      invoice_no,
      delivery_challan_no,
      gate_entry_no,
      remarks,
      items
    } = req.body;

    const userId = req.session?.user?.id || null;
    const orgId  = req.session?.user?.orgId || null;

    if (!grn_number) {
      return res.status(400).json({ success:false, message:"GRN number is required" });
    }
    if (!vendor_name) {
      return res.status(400).json({ success:false, message:"Vendor name is required" });
    }
    if (!items || !Array.isArray(items) || items.length === 0) {
      return res.status(400).json({ success:false, message:"At least one item is required" });
    }

    const existingGrn = await client.query(
      `SELECT id FROM grn WHERE grn_number = $1 AND is_deleted = false`, [grn_number]
    );
    if (existingGrn.rows.length > 0) {
      return res.status(400).json({ success:false, message:`GRN number ${grn_number} already exists` });
    }

    await client.query("BEGIN");

    const grnResult = await client.query(
      `INSERT INTO grn (
        grn_number, grn_date, po_number, vendor_name, gate_entry_no,
        invoice_no, delivery_challan_no, remarks, grn_status,
        created_by, org_id, created_at, updated_at
      ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,NOW(),NOW())
      RETURNING id`,
      [
        grn_number, grn_date || new Date(), po_number||null, vendor_name,
        gate_entry_no||null, invoice_no||null, delivery_challan_no||null,
        remarks||null, 'Draft', userId, orgId
      ]
    );

    const grnId = grnResult.rows[0].id;

    const createdItems = [];
    for (const item of items) {
      const orderedQty       = parseFloat(item.ordered_qty)        || 0;
      const receivedQty      = parseFloat(item.received_qty)       || 0;
      const acceptedQty      = parseFloat(item.accepted_qty)       || receivedQty;
      const rejectedQty      = parseFloat(item.rejected_qty)       || 0;
      const unitPrice        = parseFloat(item.unit_price)         || 0;
      const totalPrice       = acceptedQty * unitPrice;
      const rejectedUnitPrice= parseFloat(item.rejected_unit_price)|| 0;
      const rejectedTotalPrice = rejectedQty * rejectedUnitPrice;
      const grandTotal       = totalPrice + rejectedTotalPrice;

      const itemResult = await client.query(
        `INSERT INTO grn_items (
          grn_id, item_name, ordered_qty, received_qty, accepted_qty, rejected_qty,
          unit_price, total_price, rejected_unit_price, rejected_total_price,
          grand_total, batch_number, mfg_date, expiry_date, seal_number,
          store_location, created_at
        ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,NOW())
        RETURNING id`,
        [
          grnId, item.item_name, orderedQty, receivedQty, acceptedQty, rejectedQty,
          unitPrice, totalPrice, rejectedUnitPrice, rejectedTotalPrice, grandTotal,
          item.batch_number||null, item.mfg_date||null, item.expiry_date||null,
          item.seal_number||null, item.store_location||"Main Store"
        ]
      );
      createdItems.push({ id: itemResult.rows[0].id, ...item });
    }

    await client.query("COMMIT");

    return res.status(201).json({
      success: true,
      message: "Manual GRN created successfully",
      data: { id: grnId, grn_number, items: createdItems }
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Create Manual GRN Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to create manual GRN",
      error: process.env.NODE_ENV === "development" ? error.message : undefined
    });
  } finally {
    client.release();
  }
};

// Update GRN Details with vehicle selection
const updateGrnDetails = async (req, res) => {
  const client = await pool.connect();

  try {
    const { grnId } = req.params;
    const { po_number, invoice_no, delivery_challan_no, gate_entry_no, vehicle_id } = req.body;
    const userId = req.session?.user?.id || null;

    console.log("Updating GRN details:", { grnId, po_number, invoice_no, delivery_challan_no, gate_entry_no, vehicle_id });

    const grnCheck = await client.query(
      "SELECT id FROM grn WHERE id = $1 AND is_deleted = false", [grnId]
    );
    if (grnCheck.rows.length === 0) {
      return res.status(404).json({ success:false, message:"GRN not found" });
    }

    await client.query("BEGIN");

    const result = await client.query(
      `UPDATE grn 
       SET 
         po_number          = COALESCE($1, po_number),
         invoice_no         = COALESCE($2, invoice_no),
         delivery_challan_no= COALESCE($3, delivery_challan_no),
         gate_entry_no      = COALESCE($4, gate_entry_no),
         vehicle_id         = COALESCE($5, vehicle_id),
         updated_by         = $6,
         updated_at         = NOW()
       WHERE id = $7
       RETURNING id, po_number, invoice_no, delivery_challan_no, gate_entry_no, vehicle_id`,
      [po_number||null, invoice_no||null, delivery_challan_no||null,
       gate_entry_no||null, vehicle_id||null, userId, grnId]
    );

    let vehicle_number = null;
    if (vehicle_id) {
      const vRes = await client.query(
        `SELECT vehicle_number FROM commercial_vehicles WHERE id = $1`, [vehicle_id]
      );
      if (vRes.rows.length > 0) vehicle_number = vRes.rows[0].vehicle_number;
    }

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "GRN details updated successfully",
      data: { ...result.rows[0], vehicle_number }
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Update GRN Details Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to update GRN details: " + error.message,
      error: process.env.NODE_ENV === "development" ? error.message : undefined
    });
  } finally {
    client.release();
  }
};

// Approve GRN - Update grn_status to 'Approved'
const approveGrn = async (req, res) => {
  try {
    const { id } = req.params;
    const userId = req.session?.user?.id || null;

    // Check if GRN exists
    const grnCheck = await pool.query(
      "SELECT id, grn_status FROM grn WHERE id = $1 AND is_deleted = false",
      [id]
    );
    
    if (grnCheck.rows.length === 0) {
      return res.status(404).json({
        success: false,
        message: "GRN not found"
      });
    }

    // Check if already approved
    if (grnCheck.rows[0].grn_status === "Approved") {
      return res.status(400).json({
        success: false,
        message: "GRN is already approved"
      });
    }

    // Update GRN status to Approved
    const result = await pool.query(
      `UPDATE grn 
       SET grn_status = 'Approved',
           updated_by = $2,
           updated_at = NOW()
       WHERE id = $1 AND is_deleted = false
       RETURNING id, grn_number, grn_status`,
      [id, userId]
    );

    return res.status(200).json({
      success: true,
      message: "GRN approved successfully",
      data: result.rows[0]
    });
    
  } catch (error) {
    console.error("Approve GRN Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to approve GRN",
      error: process.env.NODE_ENV === "development" ? error.message : undefined
    });
  }
};

// Add this function to grnController.js

// Get GRNs by PO ID with items
const getGrnsByPoId = async (req, res) => {
  try {
    const { poId } = req.params;
    const orgId = req.user?.orgId;

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

    const result = await pool.query(
      `SELECT 
        g.id,
        g.grn_number,
        g.grn_date,
        g.po_id,
        g.po_number,
        g.vendor_name,
        g.grn_status,
        COALESCE(
          (SELECT json_agg(json_build_object(
            'id', gi.id,
            'item_name', gi.item_name,
            'ordered_qty', gi.ordered_qty,
            'received_qty', gi.received_qty,
            'accepted_qty', gi.accepted_qty,
            'rejected_qty', gi.rejected_qty,
            'unit_price', gi.unit_price,
            'total_price', gi.total_price,
            'rejected_unit_price', gi.rejected_unit_price,
            'rejected_total_price', gi.rejected_total_price,
            'grand_total', gi.grand_total
          ) ORDER BY gi.id)
          FROM grn_items gi WHERE gi.grn_id = g.id
        ), '[]'::json) as items
      FROM grn g
      WHERE g.po_id = $1 AND g.org_id = $2 AND g.is_deleted = false
      ORDER BY g.created_at DESC`,
      [poId, orgId]
    );

    return res.status(200).json({
      success: true,
      data: result.rows
    });
  } catch (error) {
    console.error("Get GRNs by PO ID Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch GRNs for PO",
      error: process.env.NODE_ENV === "development" ? error.message : undefined
    });
  }
};

// Get approval counts for multiple GRNs (batch endpoint)
const getGrnApprovalCounts = async (req, res) => {
  try {
    const { grnIds } = req.query;
    const orgId = req.user?.orgId;
    
    if (!orgId) {
      return res.status(401).json({ success: false, message: "Unauthorized" });
    }
    
    if (!grnIds) {
      return res.status(400).json({ 
        success: false, 
        message: "grnIds query parameter is required" 
      });
    }
    
    const ids = grnIds.split(',').map(id => parseInt(id));
    
    if (ids.length === 0) {
      return res.status(400).json({ 
        success: false, 
        message: "Invalid grnIds format" 
      });
    }
    
    // Get approval progress for each GRN from grn_approval_progress table
    const progressResult = await pool.query(
      `SELECT 
        pap.grn_id,
        pap.status,
        pap.current_step,
        pap.approved_by,
        pap.approved_at,
        pap.comments,
        u.first_name,
        u.last_name,
        u.email,
        u.role
       FROM grn_approval_progress pap
       LEFT JOIN users u ON pap.approved_by = u.id
       WHERE pap.grn_id = ANY($1::int[])
       ORDER BY pap.grn_id, pap.current_step`,
      [ids]
    );
    
    // Get workflow total approvers for GRN module (module_type_id = 1 for GRN)
    const workflowResult = await pool.query(
      `SELECT COUNT(*) as total_approvers 
       FROM approval_workflows 
       WHERE module_type_id = 1`
    );
    
    const totalApprovers = parseInt(workflowResult.rows[0]?.total_approvers || 0);
    
    // Build response object
    const approvalData = {};
    
    // Initialize all GRN IDs with default values
    ids.forEach(grnId => {
      approvalData[grnId] = {
        approved: 0,
        rejected: 0,
        total: totalApprovers,
        current_step: 1,
        status: 'pending',
        approverStatuses: []
      };
    });
    
    // Fill in the data from query results
    progressResult.rows.forEach(row => {
      const grnId = row.grn_id;
      if (approvalData[grnId]) {
        if (row.status === 'approved') {
          approvalData[grnId].approved++;
        } else if (row.status === 'rejected') {
          approvalData[grnId].rejected++;
        }
        approvalData[grnId].current_step = row.current_step;
        approvalData[grnId].status = row.status;
        
        // Add approver statuses if needed
        approvalData[grnId].approverStatuses.push({
          user_id: row.approved_by,
          status: row.status,
          approved_at: row.approved_at,
          comments: row.comments,
          first_name: row.first_name,
          last_name: row.last_name
        });
      }
    });
    
    res.json({
      success: true,
      data: approvalData,
      workflowTotalApprovers: totalApprovers
    });
    
  } catch (error) {
    console.error("Error in getGrnApprovalCounts:", error);
    res.status(500).json({ 
      success: false, 
      message: error.message 
    });
  }
};


// Export all functions
module.exports = {
  createGrn,
  getAllGrns,
  getAllGrnsWithoutPagination,
  getGrnById,
  getGrnByVehicleId,
  getGrnByGrnNumber,
  updateGrnHeader,
  updateGrnItems,
  deleteGrn,
  testGrnData,
  downloadGrnPdf,
  createManualGrn,
  updateGrnDetails,
  approveGrn,
  getGrnsByPoId,
  getGrnApprovalCounts
};