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

const getAllBookings = async (req, res) => {
    const { search, startDate, endDate, status, filterType, page = 1, limit = 10000 } = req.query;
    const { tenant_id } = req;

    const pageNum = parseInt(page);
    const limitNum = parseInt(limit);
    const offset = (pageNum - 1) * limitNum;

    try {
        let sql = `
            FROM bookings b
            JOIN cars c ON b.car_id = c.id
            LEFT JOIN customers cust ON b.cus_id = cust.cus_id
            WHERE b.tenant_id = $1
        `;
        let values = [tenant_id];
        let paramIdx = 2;

        if (filterType === 'lastWeek') {
            if (status && status !== 'all') {
                values.push(status);
                sql += ` AND LOWER(b.status) = LOWER($${paramIdx})`;
                paramIdx++;
            }
        } else if (status && status !== 'all') {
            values.push(status);
            sql += ` AND LOWER(b.status) = LOWER($${paramIdx})`;
            paramIdx++;
        }

        if (filterType === 'lastWeek') {
            if (startDate && endDate) {
                values.push(endDate);
                sql += `
                    AND b.end_date::date >= $${paramIdx}::date - INTERVAL '7 days'
                    AND b.end_date::date <= $${paramIdx}::date
                `;
                paramIdx++;
            } else {
                sql += `
                    AND b.end_date::date >= CURRENT_DATE - INTERVAL '7 days'
                    AND b.end_date::date <= CURRENT_DATE
                `;
            }
        } else {
            if (startDate && endDate) {
                values.push(startDate);
                values.push(endDate);
                sql += `
                    AND b.start_date >= $${paramIdx}
                    AND b.end_date <= $${paramIdx + 1}
                `;
                paramIdx += 2;
            }
        }

        if (search) {
            values.push(`%${search}%`);
            sql += `
                AND (
                    cust.full_name ILIKE $${paramIdx} OR
                    cust.email ILIKE $${paramIdx} OR
                    cust.mobile ILIKE $${paramIdx} OR
                    c.brand ILIKE $${paramIdx}
                )
            `;
            paramIdx++;
        }

        const countSql = `SELECT COUNT(*) ` + sql;
        const countResult = await query(countSql, values);
        const totalCount = parseInt(countResult.rows[0].count);
        const totalPages = Math.ceil(totalCount / limitNum);

        let dataSql = `
            SELECT
                b.booking_id AS id,
                b.*,
                c.brand,
                c.model,
                c.year,
                c.image_url,
                cust.full_name AS customer_name,
                cust.email AS customer_email,
                cust.mobile AS customer_phone,
                cust.license_number AS customer_license,
                cust.license_expiry_date,
                cust.id_expiry_date
        ` + sql;

        const dataValues = [...values];

        dataValues.push(limitNum);
        dataValues.push(offset);

        dataSql += `
            ORDER BY b.end_date DESC
            LIMIT $${paramIdx}
            OFFSET $${paramIdx + 1}
        `;

        const result = await query(dataSql, dataValues);

        res.json({
            bookings: result.rows,
            currentPage: pageNum,
            pageSize: limitNum,
            totalBookings: totalCount,
            totalPages,
            hasNext: pageNum < totalPages,
            hasPrevious: pageNum > 1
        });

    } catch (err) {
        console.error("🔥 SQL ERROR:", err.message);
        res.status(500).json({ message: err.message });
    }
};

//const addBooking = async (req, res) => {
  //  const data = req.body;
    //const { tenant_id } = req;
//
  //  try {
    //    let car_images = JSON.stringify([]);
      //  let car_videos = JSON.stringify([]);
//
  //      if (req.files) {
    //        if (req.files['car_images']) car_images = JSON.stringify(req.files['car_images'].map(f => f.filename));
      //      if (req.files['car_videos']) car_videos = JSON.stringify(req.files['car_videos'].map(f => f.filename));
        //}
const addBooking = async (req, res) => {
    const data = req.body;
    const { tenant_id } = req;

    // ===== ADD THIS: Log incoming data =====
    console.log("📝 Booking Data Received:");
    console.log("  - start_date:", data.start_date);
    console.log("  - end_date:", data.end_date);
    console.log("  - cus_id:", data.cus_id);
    console.log("  - car_id:", data.car_id);
    console.log("  - base_price:", data.base_price);
    console.log("  - payment_method:", data.payment_method);
    console.log("  - tenant_id:", tenant_id);

    try {
        let car_images = JSON.stringify([]);
        let car_videos = JSON.stringify([]);

        if (req.files) {
            if (req.files['car_images']) car_images = JSON.stringify(req.files['car_images'].map(f => f.filename));
            if (req.files['car_videos']) car_videos = JSON.stringify(req.files['car_videos'].map(f => f.filename));
        }

        const sql = `
    INSERT INTO bookings (
        cus_id, car_id, status, booking_mode, start_date, end_date,
        base_price, discount_code, discount_percent, discount_amount, total_price,
        tax_amount, advance_paid, deposit, payment_type, payment_status, booking_date,
        starting_kms, ending_kms, fuel_level, return_fuel_level, notes,
        car_images, car_videos,
        deposit_remarks, advance_remarks, invoice_remarks,
        cleaning_charges, damage_charges, excess_km_charges, salik_charges,
        additional_charges, pickup_location, drop_location, trip_package, section_charges,
        tenant_id
    )
    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, $25, $26, $27,
        $28, $29, $30, $31, $32, $33, $34, $35, $36, $37
    )
    RETURNING *, booking_id AS id`;

const values = [
    data.cus_id,
    data.car_id,
    data.status || 'confirmed',
    data.booking_mode,
    data.start_date||null,
    data.end_date||null,
    data.base_price || 0,
    data.discount_code,
    data.discount_percent || 0,
    data.discount_amount || 0,
    data.total_price || 0,
    data.tax_amount || 0,
    data.advance_paid || 0,
    data.deposit || 0,
    data.payment_method,
    data.payment_status || 'Unpaid',
    data.booking_date || new Date(),
    data.starting_kms,
    data.ending_kms || null,
    data.fuel_level,
    data.return_fuel_level || null,
    data.notes,
    car_images,
    car_videos,
    data.deposit_remarks,
    data.advance_remarks,
    data.invoice_remarks,
    data.cleaning_charges || 0,
    data.damage_charges || 0,
    data.excess_km_charges || 0,
    data.salik_charges || 0,
    data.additional_charges ? JSON.stringify(data.additional_charges) : '[]',
    data.pickup_location,
    data.drop_location,
    data.trip_package || 'Weekly',
    data.section_charges ? (typeof data.section_charges === 'string' ? data.section_charges : JSON.stringify(data.section_charges)) : '[]',
    tenant_id
];
        const result = await query(sql, values);
        res.json(result.rows[0]);
    } catch (err) {
        console.error("Insert Error:", err.message);
        res.status(500).json({ message: "Error creating booking", error: err.message });
    }
};

const updateBookingDetails = async (req, res) => {
    const { id } = req.params;
    const data = req.body;
    const { tenant_id } = req;

    try {
        let final_images = JSON.parse(data.existing_images || "[]");
        let final_videos = JSON.parse(data.existing_videos || "[]");

        if (req.files) {
            if (req.files['car_images']) {
                const newImgs = req.files['car_images'].map(f => f.filename);
                final_images = [...final_images, ...newImgs];
            }
            if (req.files['car_videos']) {
                const newVids = req.files['car_videos'].map(f => f.filename);
                final_videos = [...final_videos, ...newVids];
            }
        }

        const sql = `
        UPDATE bookings SET
            base_price = $1,
            tax_amount = $2,
            advance_paid = $3,
            total_price = $4,
            payment_status = $5,
            payment_type = $6,
            notes = $7,
            start_date = $8,
            end_date = $9,
            fuel_level = $10,
            starting_kms = $11,
            status = $12,
            deposit = $13,
            car_images = $14,
            car_videos = $15,
            deposit_remarks = $16,
            advance_remarks = $17,
            invoice_remarks = $18,
            ending_kms = $19,
            return_fuel_level = $20,
            booking_date = $21,
            cleaning_charges = $22,
            damage_charges = $23,
            excess_km_charges = $24,
            salik_charges = $25,
            additional_charges = $26,
            pickup_location = $27,
            drop_location = $28,
            payment_date = $29,
            trip_package = $30,
            section_charges = $31
        WHERE booking_id = $32 AND tenant_id = $33
        RETURNING *, booking_id AS id
        `;

        const values = [
            data.base_price || 0,
            data.tax_amount || 0,
            data.advance_paid || 0,
            data.total_price || 0,
            data.payment_status,
            data.payment_method,
            data.notes,
            data.start_date||null,
            data.end_date||null,
            data.fuel_level,
            data.starting_kms,
            data.status,
            data.deposit || 0,
            JSON.stringify(final_images),
            JSON.stringify(final_videos),
            data.deposit_remarks,
            data.advance_remarks,
            data.invoice_remarks,
            data.ending_kms === "" || data.ending_kms === undefined ? null : data.ending_kms,
            data.return_fuel_level === "" || data.return_fuel_level === undefined ? null : data.return_fuel_level,
            data.booking_date === "" ? null : data.booking_date,
            data.cleaning_charges || 0,
            data.damage_charges || 0,
            data.excess_km_charges || 0,
            data.salik_charges || 0,
            data.additional_charges ? JSON.stringify(data.additional_charges) : '[]',
            data.pickup_location,
            data.drop_location,
            data.payment_date,
            data.trip_package || 'Weekly',
            data.section_charges ? (typeof data.section_charges === 'string' ? data.section_charges : JSON.stringify(data.section_charges)) : '[]',
            id,
            tenant_id
        ];

//        const result = await query(sql, values);
  //      if (result.rows.length === 0) return res.status(404).json({ message: 'Booking not found' });
//
  //      if (data.car_id && data.ending_kms !== undefined && data.ending_kms !== "" && data.return_fuel_level !== undefined && data.return_fuel_level !== "") {
    //        await query(
      //          `UPDATE cars SET current_km=$1, current_fuel=$2
        //         WHERE id=$3 AND tenant_id=$4`,
 //               [data.ending_kms, data.return_fuel_level, data.car_id, tenant_id]
   //         );
 //       }

   //     res.json(result.rows[0]);
//
  //  } catch (err) {
    //    console.error("Update Error:", err.message);
     //   res.status(500).json({ message: err.message });
    //}

 // ===== ADD THIS: Log values going to database =====
        console.log("📊 Values for SQL:");
        console.log("  - start_date (value):", values[4]);
        console.log("  - end_date (value):", values[5]);
        console.log("  - payment_method:", values[14]);
        console.log("  - payment_status:", values[15]);
        console.log("  - booking_date:", values[16]);

        const result = await query(sql, values);
        res.json(result.rows[0]);
    } catch (err) {
        // ===== ADD THIS: Log full error details =====
        console.error("❌ FULL ERROR:", err);
        console.error("❌ ERROR CODE:", err.code);
        console.error("❌ ERROR DETAIL:", err.detail);
        console.error("❌ ERROR WHERE:", err.where);
        console.error("❌ ERROR MESSAGE:", err.message);
        res.status(500).json({ message: "Error creating booking", error: err.message });
    }
};

const updateCharges = async (req, res) => {
    const { id } = req.params;
    const data = req.body;
    const { tenant_id } = req;

    try {
        const sql = `
        UPDATE bookings SET
            cleaning_charges = $1,
            damage_charges = $2,
            excess_km_charges = $3,
            salik_charges = $4,
            additional_charges = $5,
            section_charges = $6,
            total_price = $7
        WHERE booking_id = $8 AND tenant_id = $9
        RETURNING *`;

        const values = [
            data.cleaning_charges || 0,
            data.damage_charges || 0,
            data.excess_km_charges || 0,
            data.salik_charges || 0,
            data.additional_charges ? JSON.stringify(data.additional_charges) : '[]',
            data.section_charges ? (typeof data.section_charges === 'string' ? data.section_charges : JSON.stringify(data.section_charges)) : '[]',
            data.total_price || 0,
            id,
            tenant_id
        ];

        const result = await query(sql, values);
        if (result.rows.length === 0) return res.status(404).json({ message: 'Booking not found' });

        res.json(result.rows[0]);
    } catch (err) {
        console.error("Update Charges Error:", err.message);
        res.status(500).json({ message: err.message });
    }
};

const updateBookingStatus = async (req, res) => {
    const { id } = req.params;
    const { status } = req.body;
    const { tenant_id } = req;

    try {
        const result = await query(
            'UPDATE bookings SET status = $1, updated_at = NOW() WHERE booking_id = $2 AND tenant_id = $3 RETURNING *, booking_id AS id',
            [status, id, tenant_id]
        );
        if (result.rows.length === 0) return res.status(404).json({ message: 'Booking not found' });
        res.json(result.rows[0]);
    } catch (err) {
        console.error(err);
        res.status(500).json({ message: 'Server error' });
    }
};

const updatePaymentStatus = async (req, res) => {
    const { id } = req.params;
    const { payment_status, payment_date, amount_collected, payment_history } = req.body;
    const { tenant_id } = req;

    try {
        const result = await query(
            `UPDATE bookings SET
                payment_status = $1, payment_date = $2, amount_collected = $3, payment_history = $4
             WHERE booking_id = $5 AND tenant_id = $6
             RETURNING booking_id, payment_status, payment_date, amount_collected, payment_history`,
            [payment_status, payment_date, amount_collected || 0, payment_history || null, id, tenant_id]
        );
        if (result.rows.length === 0) return res.status(404).json({ message: 'Booking not found' });
        res.json(result.rows[0]);
    } catch (err) {
        console.error(err);
        res.status(500).json({ message: err.message });
    }
};

const getBookingById = async (req, res) => {
    const { id } = req.params;
    const { tenant_id } = req;

    try {
        const result = await query(`
            SELECT
                b.*,
                c.brand, c.model, c.year, c.kilometers,
                cust.full_name, cust.mobile, cust.email, cust.license_number, cust.license_expiry_date, cust.id_expiry_date
            FROM bookings b
            JOIN cars c ON b.car_id = c.id
            JOIN customers cust ON b.cus_id = cust.cus_id
            WHERE b.booking_id = $1 AND b.tenant_id = $2
        `, [id, tenant_id]);

        if (result.rows.length === 0) {
            return res.status(404).json({ message: 'Booking not found' });
        }
        res.json(result.rows[0]);
    } catch (err) {
        console.error(err);
        res.status(500).json({ message: 'Server error fetching booking' });
    }
};

const getInvoiceById = async (req, res) => {
    const { id } = req.params;
    const { tenant_id } = req;

    try {
        const result = await query(`
            SELECT
                b.*,
                c.brand, c.model, c.year,
                cust.full_name,
                cust.mobile,
                cust.email,
                cust.license_number,
                cust.license_expiry_date,
                cust.id_expiry_date,
                cust.area,
                cust.district,
                cust.id_type
            FROM bookings b
            LEFT JOIN cars c ON b.car_id = c.id
            LEFT JOIN customers cust ON b.cus_id = cust.cus_id
            WHERE b.booking_id = $1 AND b.tenant_id = $2
        `, [id, tenant_id]);

        if (result.rows.length === 0) {
            return res.status(404).json({ message: "Not found" });
        }

        res.json(result.rows[0]);

    } catch (err) {
        console.error(err);
        res.status(500).json({ message: "Error fetching invoice" });
    }
};

const getDailyBookingStats = async (req, res) => {
    try {
        const days = parseInt(req.query.days) || 30;
        const { tenant_id } = req;

        const result = await query(`
            SELECT
                DATE(created_at) AS date,
                COUNT(*) AS booking_count,
                COALESCE(SUM(total_price), 0) AS revenue
            FROM bookings
            WHERE tenant_id = $1
              AND created_at >= CURRENT_DATE - INTERVAL '${days} days'
            GROUP BY DATE(created_at)
            ORDER BY DATE(created_at) ASC
        `, [tenant_id]);

        res.json({ stats: result.rows });
    } catch (err) {
        console.error('Error fetching daily booking stats:', err.message);
        res.status(500).json({ message: err.message });
    }
};

const getTopVehicles = async (req, res) => {
    try {
        const { tenant_id } = req;

        const result = await query(`
            SELECT
                c.id, c.brand AS make, c.model, c.car_mulkiya_id AS registration_number,
                COUNT(b.booking_id) AS "totalBookings",
                COALESCE(SUM(b.total_price), 0) AS "totalRevenue"
            FROM cars c
            JOIN bookings b ON c.id = b.car_id
            WHERE (LOWER(b.status) = 'confirmed' OR LOWER(b.status) = 'on-trip' OR LOWER(b.status) = 'completed')
              AND c.tenant_id = $1
              AND b.tenant_id = $1
            GROUP BY c.id
            ORDER BY "totalRevenue" DESC
            LIMIT 5
        `, [tenant_id]);

        res.json({ topVehicles: result.rows });
    } catch (err) {
        console.error('Error fetching top vehicles:', err.message);
        res.status(500).json({ message: err.message });
    }
};

const getRecentlyCompleted = async (req, res) => {
    try {
        const { tenant_id } = req;

        const sql = `
            SELECT b.booking_id AS id, b.customer_name, b.total_price, b.end_date,
                   c.brand, c.model, c.image_url
            FROM bookings b
            JOIN cars c ON b.car_id = c.id
            WHERE LOWER(b.status) = 'completed'
              AND b.tenant_id = $1
              AND b.updated_at >= NOW() - INTERVAL '7 days'
            ORDER BY b.updated_at DESC LIMIT 5;
        `;
        const result = await query(sql, [tenant_id]);
        res.json(result.rows);
    } catch (err) {
        console.error(err);
        res.status(500).json({ message: 'Error' });
    }
};

module.exports = {
    getAllBookings,
    updateBookingStatus,
    updatePaymentStatus,
    updateBookingDetails,
    updateCharges,
    addBooking,
    getRecentlyCompleted,
    getBookingById,
    getInvoiceById,
    getDailyBookingStats,
    getTopVehicles
};