const{ query } = require('../db');

const addCustomer = async (req, res) => {
    try {
        const body = req.body;
        const toDB = (val) => (val === "" || val === "null" || val === undefined) ?null :val;

        // Check if mobile number already exists
        if (body.mobile) {
            const existingCustomer = await query(
                'SELECT * FROM customers WHERE mobile = $1',
                [body.mobile.trim()]
            );
            
            if (existingCustomer.rows.length> 0) {
                return res.status(400).json({ 
                    message: "Mobile number already exists. Please use a different number." 
                });
            }
        }

        const licenseFiles = (req.files&&req.files['license_file']) 
            ? req.files['license_file'].map(f =>f.filename).join(',') 
            : null;

        const idFiles = (req.files&&req.files['id_file']) 
            ? req.files['id_file'].map(f =>f.filename).join(',') 
            : null;

        const result = await query(
            `INSERT INTO customers (
                full_name, dob, gender, mobile, email, nationality, occupation,
                house_no, street_name, area, city, district, state, pincode, country_id,
                license_number, license_issued_date, license_expiry_date, license_state, license_country, license_file,
                id_type, id_number, id_file
            ) 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) 
            RETURNING *`,
            [
                body.full_name, toDB(body.dob), body.gender, body.mobile.trim(), body.email, body.nationality, body.occupation,
                body.house_no, body.street_name, body.area, body.city, body.district, body.state, body.pincode, toDB(body.country_id),
                body.license_number, toDB(body.license_issued_date), toDB(body.license_expiry_date), body.license_state, body.license_country, 
                licenseFiles, 
                body.id_type, body.id_number, 
                idFiles
            ]
        );
        res.status(201).json(result.rows[0]);
    } catch (err) {
        console.error("ADD ERROR:", err.message);
        res.status(500).json({ message: 'Server error: ' + err.message });
    }
};

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

        // Check if customer exists
        const currentData = await query('SELECT * FROM customers WHERE cus_id = $1', [id]);
        if (currentData.rows.length === 0) {
            return res.status(404).json({ message: "Customer not found" });
        }

        // Check if mobile number already exists for another customer
        if (body.mobile) {
            const existingCustomer = await query(
                'SELECT * FROM customers WHERE mobile = $1 AND cus_id != $2',
                [body.mobile.trim(), id]
            );
            
            if (existingCustomer.rows.length> 0) {
                return res.status(400).json({ 
                    message: "Mobile number already exists. Please use a different number." 
                });
            }
        }

        const toDB = (val) => (val === "" || val === "null" || val === undefined) ?null :val;

        // FILE LOGIC
        let finalLicenseFiles = body.existing_license_files || "";
        if (req.files&&req.files['license_file'] &&req.files['license_file'].length> 0) {
            const newFiles = req.files['license_file'].map(f =>f.filename).join(',');
            finalLicenseFiles = finalLicenseFiles ? `${finalLicenseFiles},${newFiles}` :newFiles;
        }

        let finalIdFiles = body.existing_id_files || "";
        if (req.files&&req.files['id_file'] &&req.files['id_file'].length> 0) {
            const newFiles = req.files['id_file'].map(f =>f.filename).join(',');
            finalIdFiles = finalIdFiles ? `${finalIdFiles},${newFiles}` :newFiles;
        }

        const result = await query(
            `UPDATE customers SET 
                full_name=$1, dob=$2, gender=$3, mobile=$4, email=$5, 
                nationality=$6, occupation=$7,
                house_no=$8, street_name=$9, area=$10, city=$11, 
                district=$12, state=$13, pincode=$14, country_id=$15,
                license_number=$16, license_issued_date=$17, 
                license_expiry_date=$18, license_state=$19, 
                license_country=$20, license_file=$21, 
                id_type=$22, id_number=$23, id_file=$24
            WHERE cus_id = $25 
            RETURNING *`,
            [
                body.full_name, toDB(body.dob), body.gender, body.mobile.trim(), 
                body.email, body.nationality, body.occupation,
                body.house_no, body.street_name, body.area, body.city, 
                body.district, body.state, body.pincode, toDB(body.country_id),
                body.license_number, toDB(body.license_issued_date), 
                toDB(body.license_expiry_date), body.license_state, 
                body.license_country, finalLicenseFiles, 
                body.id_type, body.id_number, finalIdFiles,
                id
            ]
        );

        res.json(result.rows[0]);
    } catch (err) {
        console.error("UPDATE ERROR:", err.message);
        console.error("UPDATE STACK:", err.stack);
        res.status(500).json({ message: 'Update failed: ' + err.message });
    }
};

const getAllCustomers = async (req, res) => {
    try {
        const result = await query(`
            SELECT c.*  
            FROM customers c
            ORDER BY c.cus_id DESC
        `);
        res.json(result.rows);
    } catch (err) {
        console.error("FETCH ALL ERROR:", err.message);
        res.status(500).json({ message: "Server error: " + err.message });
    }
};

const getCountryMaster = async (req, res) => {
    try {
        const result = await query('SELECT country_id, name, nationality FROM country_master ORDER BY name ASC');
        res.json(result.rows);
    } catch (err) {
        console.error("MASTER DATA ERROR:", err.message);
        res.status(500).json({ message: "Server error: " + err.message });
    }
};

const searchCustomers = async (req, res) => {
    const q = req.query.q;
    if (!q) return res.json([]);

    try {
        const result = await query(
            `SELECT *
             FROM customers
             WHERE
                full_name ILIKE $1 OR
                mobile ILIKE $1 OR
                email ILIKE $1
             ORDER BY cus_id DESC
             LIMIT 5`,
            [`%${q}%`]
        );

        res.json(result.rows);
    } catch (err) {
        console.error("SEARCH ERROR:", err.message);
        res.status(500).json({ message: "Server error: " + err.message });
    }
};

module.exports = { 
    addCustomer, 
    updateCustomer, 
    getAllCustomers, 
    searchCustomers,
    getCountryMaster
};

