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

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

// Update your saveFiles function in backend controller
const saveFiles = async (files, poNumber) => {
    const savedFiles = [];
    if (!files || files.length === 0) return savedFiles;

    for (const file of files) {
        // Get the file path - multer stores path in 'path' or 'destination' + 'filename'
        const filePath = file.path || path.join(file.destination, file.filename);
        
        // Extract the relative path from 'uploads/' onwards
        const relativePath = filePath.split('uploads')[1] || `/${file.filename}`;
        const webPath = `/uploads${relativePath}`;
        
        savedFiles.push({
            originalName: file.originalname,
            fileName: file.filename,
            filePath: webPath, 
            fullPath: filePath,
            fileSize: file.size,
            mimeType: file.mimetype,
            uploadDate: new Date().toISOString(),
            year: new Date().getFullYear().toString(),
            month: (new Date().getMonth() + 1).toString().padStart(2, "0")
        });
    }
    return savedFiles;
};

// ─────────────────────────────────────────────
// GET PO DETAILS BY PO NUMBER (vendor & amount only)
// ─────────────────────────────────────────────
const getPODetailsForProforma = async (req, res) => {
    try {
        const { po_number } = req.params;
        const orgId = req.user?.orgId;

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

        const orgIdStr = String(orgId);

        const result = await pool.query(
            `SELECT 
                po.po_number,
                po.vendor_name,
                po.total_amount,
                po.po_date,
                po.status as po_status
             FROM purchase_orders po
             WHERE po.po_number = $1 
               AND po.org_id = $2 
               AND po.is_deleted = false`,
            [po_number, orgIdStr]
        );

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

        // Check if upload already exists for this PO
        const existingUpload = await pool.query(
            `SELECT id, po_number, upload_date, po_status 
             FROM upload_Proforma 
             WHERE po_number = $1 AND org_id = $2 AND is_deleted = false`,
            [po_number, orgIdStr]
        );

        res.json({
            success: true,
            data: {
                ...result.rows[0],
                hasExistingUpload: existingUpload.rows.length > 0,
                existingUploadId: existingUpload.rows[0]?.id || null
            }
        });
    } catch (err) {
        console.error("Error in getPODetailsForProforma:", err);
        res.status(500).json({ success: false, message: err.message });
    }
};

// ─────────────────────────────────────────────
// CREATE UPLOAD Proforma
// ─────────────────────────────────────────────
const createUploadProforma = async (req, res) => {
    const client = await pool.connect();
    
    try {
        const {
            po_number,
            proforma_invoice_number,
            proforma_invoice_date,
            advance_required,
            advance_amount, // Changed from advance_percentage to advance_amount
            remarks,
            po_status
        } = req.body;

        const orgId = req.user?.orgId;
        const created_by = req.user?.userId || req.user?.email || null;

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

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

        const orgIdStr = String(orgId);

        // Fetch vendor and amount from purchase_orders
        const poResult = await client.query(
            `SELECT vendor_name, total_amount, po_date 
             FROM purchase_orders 
             WHERE po_number = $1 AND org_id = $2 AND is_deleted = false`,
            [po_number, orgIdStr]
        );

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

        const poData = poResult.rows[0];
        const vendor_name = poData.vendor_name;
        const total_amount = parseFloat(poData.total_amount);

        // Validate advance amount if advance is required
        let finalAdvanceAmount = null;
        
        if (advance_required === true || advance_required === "true") {
            const advanceAmt = parseFloat(advance_amount);
            
            if (isNaN(advanceAmt) || advanceAmt <= 0) {
                return res.status(400).json({
                    success: false,
                    message: "Valid advance amount is required when advance payment is enabled"
                });
            }
            
            if (advanceAmt >= total_amount) {
                return res.status(400).json({
                    success: false,
                    message: `Advance amount (₹${advanceAmt.toLocaleString('en-IN')}) must be less than total amount (₹${total_amount.toLocaleString('en-IN')})`
                });
            }
            
            finalAdvanceAmount = advanceAmt;
        }

        // Check for duplicate
        const existingUpload = await client.query(
            `SELECT id FROM upload_Proforma 
             WHERE po_number = $1 AND org_id = $2 AND is_deleted = false`,
            [po_number, orgIdStr]
        );

        if (existingUpload.rows.length > 0) {
            return res.status(400).json({
                success: false,
                message: "Proforma already uploaded for this PO number"
            });
        }

        // Save uploaded files
        const files = req.files || [];
        const savedFiles = await saveFiles(files, po_number);

        await client.query("BEGIN");

        const uploadDate = new Date().toLocaleDateString('en-CA');
        
        const result = await client.query(
            `INSERT INTO upload_Proforma (
                po_number, vendor_name, proforma_invoice_number, proforma_invoice_date,
                total_amount, advance_required, advance_amount, remarks,
                po_status, upload_date, file_paths, org_id, created_by, updated_by
            ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14)
            RETURNING *`,
            [
                po_number,
                vendor_name,
                proforma_invoice_number || null,
                proforma_invoice_date || null,
                total_amount,
                advance_required === true || advance_required === "true",
                finalAdvanceAmount,
                remarks || null,
                po_status || "draft",
                uploadDate,
                JSON.stringify(savedFiles),
                orgIdStr,
                created_by,
                created_by
            ]
        );

        await client.query("COMMIT");

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

    } catch (err) {
        await client.query("ROLLBACK");
        console.error("Error in createUploadProforma:", err);
        res.status(500).json({ success: false, message: err.message });
    } finally {
        client.release();
    }
};

// ─────────────────────────────────────────────
// GET ALL UPLOAD Proforma (with pagination)
// ─────────────────────────────────────────────
const getAllUploadProforma = async (req, res) => {
    try {
        const orgId = req.user?.orgId;
        if (!orgId) {
            return res.status(401).json({
                success: false,
                message: "Unauthorized - Organization ID missing"
            });
        }

        const orgIdStr = String(orgId);
        const { search = "", page = 1, limit = 10, sortBy = "created_at", sortOrder = "DESC" } = req.query;
        const offset = (parseInt(page) - 1) * parseInt(limit);
        const searchTerm = `%${search}%`;

        const countResult = await pool.query(
            `SELECT COUNT(*) AS total
             FROM upload_Proforma
             WHERE is_deleted = false AND org_id = $1
               AND (po_number ILIKE $2 OR vendor_name ILIKE $2 OR proforma_invoice_number ILIKE $2)`,
            [orgIdStr, searchTerm]
        );

        const total = parseInt(countResult.rows[0].total);

        const result = await pool.query(
            `SELECT * FROM upload_Proforma
             WHERE is_deleted = false AND org_id = $1
               AND (po_number ILIKE $2 OR vendor_name ILIKE $2 OR proforma_invoice_number ILIKE $2)
             ORDER BY ${sortBy} ${sortOrder}
             LIMIT $3 OFFSET $4`,
            [orgIdStr, searchTerm, parseInt(limit), offset]
        );

        res.json({
            success: true,
            data: result.rows,
            pagination: {
                currentPage: parseInt(page),
                totalPages: Math.ceil(total / parseInt(limit)),
                totalEntries: total,
                itemsPerPage: parseInt(limit),
            },
        });
    } catch (err) {
        console.error("Error in getAllUploadProforma:", err);
        res.status(500).json({ success: false, message: err.message });
    }
};

// ─────────────────────────────────────────────
// GET UPLOAD Proforma BY ID
// ─────────────────────────────────────────────
const getUploadProformaById = async (req, res) => {
    try {
        const { id } = req.params;
        const orgId = req.user?.orgId;

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

        const orgIdStr = String(orgId);

        const result = await pool.query(
            `SELECT * FROM upload_Proforma
             WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
            [parseInt(id), orgIdStr]
        );

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

        res.json({
            success: true,
            data: result.rows[0]
        });
    } catch (err) {
        console.error("Error in getUploadProformaById:", err);
        res.status(500).json({ success: false, message: err.message });
    }
};

// ─────────────────────────────────────────────
// UPDATE UPLOAD Proforma
// ─────────────────────────────────────────────
const updateUploadProforma = async (req, res) => {
    const client = await pool.connect();
    
    try {
        const { id } = req.params;
        const {
            proforma_invoice_number,
            proforma_invoice_date,
            advance_required,
            advance_amount, // Changed from advance_percentage to advance_amount
            remarks,
            po_status
        } = req.body;

        const orgId = req.user?.orgId;
        const updated_by = req.user?.userId || req.user?.email;

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

        const orgIdStr = String(orgId);
        const uploadId = parseInt(id);

        // Check if record exists
        const existingRecord = await client.query(
            `SELECT id, total_amount, po_number FROM upload_Proforma
             WHERE id = $1 AND org_id = $2 AND is_deleted = false`,
            [uploadId, orgIdStr]
        );

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

        const total_amount = parseFloat(existingRecord.rows[0].total_amount);

        // Validate advance amount if advance is required
        let finalAdvanceAmount = null;
        if (advance_required === true || advance_required === "true") {
            const advanceAmt = parseFloat(advance_amount);
            
            if (isNaN(advanceAmt) || advanceAmt <= 0) {
                return res.status(400).json({
                    success: false,
                    message: "Valid advance amount is required when advance payment is enabled"
                });
            }
            
            if (advanceAmt >= total_amount) {
                return res.status(400).json({
                    success: false,
                    message: `Advance amount (₹${advanceAmt.toLocaleString('en-IN')}) must be less than total amount (₹${total_amount.toLocaleString('en-IN')})`
                });
            }
            
            finalAdvanceAmount = advanceAmt;
        }

        // Handle file uploads if any
        let savedFiles = [];
        if (req.files && req.files.length > 0) {
            const po_number = existingRecord.rows[0].po_number;
            savedFiles = await saveFiles(req.files, po_number);
            
            // Get existing files and merge
            const currentRecord = await client.query(
                `SELECT file_paths FROM upload_Proforma WHERE id = $1`,
                [uploadId]
            );
            const existingFiles = currentRecord.rows[0].file_paths || [];
            savedFiles = [...existingFiles, ...savedFiles];
        }

        await client.query("BEGIN");

        const updateFields = [];
        const queryParams = [];
        let paramCounter = 1;

        if (proforma_invoice_number !== undefined) {
            updateFields.push(`proforma_invoice_number = $${paramCounter++}`);
            queryParams.push(proforma_invoice_number || null);
        }
        if (proforma_invoice_date !== undefined) {
            updateFields.push(`proforma_invoice_date = $${paramCounter++}`);
            queryParams.push(proforma_invoice_date || null);
        }
        if (advance_required !== undefined) {
            updateFields.push(`advance_required = $${paramCounter++}`);
            queryParams.push(advance_required === true || advance_required === "true");
        }
        if (advance_amount !== undefined && finalAdvanceAmount !== null) {
            updateFields.push(`advance_amount = $${paramCounter++}`);
            queryParams.push(finalAdvanceAmount);
        } else if (advance_required === false || advance_required === "false") {
            updateFields.push(`advance_amount = $${paramCounter++}`);
            queryParams.push(null);
        }
        if (remarks !== undefined) {
            updateFields.push(`remarks = $${paramCounter++}`);
            queryParams.push(remarks || null);
        }
        if (po_status !== undefined) {
            updateFields.push(`po_status = $${paramCounter++}`);
            queryParams.push(po_status);
        }
        if (savedFiles.length > 0) {
            updateFields.push(`file_paths = $${paramCounter++}`);
            queryParams.push(JSON.stringify(savedFiles));
        }

        updateFields.push(`updated_by = $${paramCounter++}`);
        queryParams.push(updated_by);
        updateFields.push(`updated_at = NOW()`);

        queryParams.push(uploadId, orgIdStr);

        const result = await client.query(
            `UPDATE upload_Proforma 
             SET ${updateFields.join(", ")}
             WHERE id = $${paramCounter++} AND org_id = $${paramCounter++} AND is_deleted = false
             RETURNING *`,
            queryParams
        );

        await client.query("COMMIT");

        res.json({
            success: true,
            message: "Upload Proforma updated successfully",
            data: result.rows[0]
        });

    } catch (err) {
        await client.query("ROLLBACK");
        console.error("Error in updateUploadProforma:", err);
        res.status(500).json({ success: false, message: err.message });
    } finally {
        client.release();
    }
};

// ─────────────────────────────────────────────
// DELETE UPLOAD Proforma (Soft Delete)
// ─────────────────────────────────────────────
const deleteUploadProforma = async (req, res) => {
    try {
        const { id } = req.params;
        const orgId = req.user?.orgId;
        const updated_by = req.user?.userId || req.user?.email;

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

        const orgIdStr = String(orgId);

        const result = await pool.query(
            `UPDATE upload_Proforma
             SET is_deleted = true, 
                 updated_by = $1, 
                 updated_at = NOW(), 
                 deleted_at = NOW()
             WHERE id = $2 AND org_id = $3 AND is_deleted = false
             RETURNING id, po_number`,
            [updated_by, parseInt(id), orgIdStr]
        );

        if (result.rowCount === 0) {
            return res.status(404).json({
                success: false,
                message: "Upload Proforma record not found or already deleted"
            });
        }

        res.json({
            success: true,
            message: "Upload Proforma deleted successfully",
            data: result.rows[0]
        });
    } catch (err) {
        console.error("Error in deleteUploadProforma:", err);
        res.status(500).json({ success: false, message: err.message });
    }
};

module.exports = {
    getPODetailsForProforma,
    createUploadProforma,
    getAllUploadProforma,
    getUploadProformaById,
    updateUploadProforma,
    deleteUploadProforma
};