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

// Ensure upload directory exists
const uploadDir = path.join(__dirname, "../../uploads/purchase-invoices");
if (!fs.existsSync(uploadDir)) {
  fs.mkdirSync(uploadDir, { recursive: true });
}

// Create Purchase Invoice from GRN
const createPurchaseInvoiceFromGRN = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { grn_id, invoice_number, invoice_amount, invoice_date, remarks } = req.body;
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

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

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

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

    if (!invoice_amount || invoice_amount <= 0) {
      return res.status(400).json({ success: false, message: "Valid invoice amount is required" });
    }

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

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

    // Check if GRN exists and is not approved
    const grnCheck = await client.query(
      `SELECT id, grn_number, po_id, po_number, vendor_name, org_id, grn_status 
       FROM grn 
       WHERE id = $1 AND is_deleted = false`,
      [grn_id]
    );

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

    const grn = grnCheck.rows[0];

    if (grn.grn_status === "Approved") {
      return res.status(400).json({ success: false, message: "GRN is already approved" });
    }

    if (String(grn.org_id) !== String(orgId)) {
      return res.status(403).json({ success: false, message: "Unauthorized access to this GRN" });
    }

    // Check if purchase invoice already exists for this GRN
    const existingInvoice = await client.query(
      `SELECT id FROM purchase_invoices WHERE grn_id = $1 AND is_deleted = false`,
      [grn_id]
    );

    if (existingInvoice.rows.length > 0) {
      return res.status(400).json({ 
        success: false, 
        message: "Purchase invoice already exists for this GRN",
        data: { id: existingInvoice.rows[0].id }
      });
    }

    // Save uploaded file
    const fileExt = path.extname(req.file.originalname);
    const fileName = `invoice_${Date.now()}_${grn.grn_number}${fileExt}`;
    const filePath = `/uploads/purchase-invoices/${fileName}`;
    const fullFilePath = path.join(uploadDir, fileName);
    
    fs.writeFileSync(fullFilePath, req.file.buffer);

    await client.query("BEGIN");

    // Create purchase invoice
    const result = await client.query(
      `INSERT INTO purchase_invoices (
        grn_id, grn_number, po_id, po_number, vendor_name,
        invoice_number, invoice_amount, invoice_date, invoice_file_path,
        remarks, status, org_id, created_by, created_at, updated_at
      ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, NOW(), NOW())
      RETURNING *`,
      [
        grn_id,
        grn.grn_number,
        grn.po_id,
        grn.po_number,
        grn.vendor_name,
        invoice_number,
        invoice_amount,
        invoice_date,
        filePath,
        remarks || null,
        "Pending",
        orgId,
        userId
      ]
    );

    await client.query("COMMIT");

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

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

// Get Purchase Invoice by ID
const getPurchaseInvoiceById = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;

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

    const result = await pool.query(
      `SELECT 
        pi.*,
        u.first_name as created_by_name
       FROM purchase_invoices pi
       LEFT JOIN users u ON u.id = pi.created_by
       WHERE pi.id = $1 AND pi.org_id = $2 AND pi.is_deleted = false`,
      [id, orgId]
    );

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

    return res.status(200).json({
      success: true,
      data: result.rows[0]
    });

  } catch (error) {
    console.error("Get Purchase Invoice Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch purchase invoice",
      error: process.env.NODE_ENV === "development" ? error.message : undefined
    });
  }
};

// Get all Purchase Invoices
const getAllPurchaseInvoices = async (req, res) => {
  try {
    const orgId = req.user?.orgId;
    const { page = 1, limit = 10, search = "", status = "" } = req.query;
    const offset = (parseInt(page) - 1) * parseInt(limit);

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

    let whereConditions = ["pi.org_id = $1", "pi.is_deleted = false"];
    const queryParams = [orgId];
    let paramIndex = 2;

    if (search) {
      whereConditions.push(`(pi.invoice_number ILIKE $${paramIndex} OR pi.grn_number ILIKE $${paramIndex} OR pi.vendor_name ILIKE $${paramIndex})`);
      queryParams.push(`%${search}%`);
      paramIndex++;
    }

    if (status) {
      whereConditions.push(`pi.status = $${paramIndex}`);
      queryParams.push(status);
      paramIndex++;
    }

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

    const countResult = await pool.query(
      `SELECT COUNT(*) as total FROM purchase_invoices pi WHERE ${whereClause}`,
      queryParams.slice(0, paramIndex - 1)
    );

    queryParams.push(parseInt(limit), offset);

    const result = await pool.query(
      `SELECT 
         pi.*,
         u.first_name as created_by_name
       FROM purchase_invoices pi
       LEFT JOIN users u ON u.id = pi.created_by
       WHERE ${whereClause}
       ORDER BY pi.created_at DESC
       LIMIT $${paramIndex} OFFSET $${paramIndex + 1}`,
      queryParams
    );

    return res.status(200).json({
      success: true,
      data: result.rows,
      pagination: {
        total: parseInt(countResult.rows[0].total),
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(parseInt(countResult.rows[0].total) / parseInt(limit))
      }
    });

  } catch (error) {
    console.error("Get All Purchase Invoices Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to fetch purchase invoices"
    });
  }
};

// Approve Purchase Invoice and approve GRN
const approvePurchaseInvoice = async (req, res) => {
  const client = await pool.connect();
  
  try {
    const { id } = req.params;
    const userId = req.user?.userId || req.user?.id;
    const orgId = req.user?.orgId;

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

    // Get purchase invoice details
    const invoiceResult = await client.query(
      `SELECT id, grn_id, status FROM purchase_invoices 
       WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
      [id, orgId]
    );

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

    const invoice = invoiceResult.rows[0];

    if (invoice.status === "Approved") {
      return res.status(400).json({ success: false, message: "Invoice is already approved" });
    }

    await client.query("BEGIN");

    // Update purchase invoice status to Approved
    await client.query(
      `UPDATE purchase_invoices 
       SET status = 'Approved', 
           approved_by = $1, 
           approved_at = NOW(),
           updated_at = NOW()
       WHERE id = $2`,
      [userId, id]
    );

    // Update GRN status to Approved
    if (invoice.grn_id) {
      await client.query(
        `UPDATE grn 
         SET grn_status = 'Approved',
             updated_by = $1,
             updated_at = NOW()
         WHERE id = $2 AND is_deleted = false`,
        [userId, invoice.grn_id]
      );
    }

    await client.query("COMMIT");

    return res.status(200).json({
      success: true,
      message: "Purchase invoice and GRN approved successfully"
    });

  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Approve Purchase Invoice Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to approve purchase invoice",
      error: process.env.NODE_ENV === "development" ? error.message : undefined
    });
  } finally {
    client.release();
  }
};

// Delete Purchase Invoice
const deletePurchaseInvoice = async (req, res) => {
  try {
    const { id } = req.params;
    const orgId = req.user?.orgId;
    const userId = req.user?.userId || req.user?.id;

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

    const result = await pool.query(
      `UPDATE purchase_invoices 
       SET is_deleted = true, 
           deleted_by = $1, 
           deleted_at = NOW()
       WHERE id = $2 AND org_id = $3 AND is_deleted = false
       RETURNING id`,
      [userId, id, orgId]
    );

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

    return res.status(200).json({
      success: true,
      message: "Purchase invoice deleted successfully"
    });

  } catch (error) {
    console.error("Delete Purchase Invoice Error:", error);
    return res.status(500).json({
      success: false,
      message: "Failed to delete purchase invoice"
    });
  }
};

module.exports = {
  createPurchaseInvoiceFromGRN,
  getPurchaseInvoiceById,
  getAllPurchaseInvoices,
  approvePurchaseInvoice,
  deletePurchaseInvoice
};