const { query } = require('../db');
const getAllCars = async (req, res) => {
    const { search, status } = req.query;
    try {
        let conditions = [];
        let values = [];
        let paramIdx = 1;

        if (search) {
            const searchTerm = `%${search}%`;
            conditions.push(`(c.brand ILIKE $${paramIdx} OR c.model ILIKE $${paramIdx} OR c.description ILIKE $${paramIdx})`);
            values.push(searchTerm);
            paramIdx++;
        }
        if (status) {
            conditions.push(`c.status = $${paramIdx}`);
            values.push(status);
            paramIdx++;
        }

        const whereClause = conditions.length > 0 ? `WHERE ${conditions.join(' AND ')}` : '';

        const result = await query(
            `SELECT c.*, 
                    (
                        SELECT cust.full_name 
                        FROM bookings b 
                        JOIN customers cust ON b.cus_id = cust.cus_id
                        WHERE b.car_id = c.id AND b.status IN ('confirmed', 'on_trip', 'pending')
                        ORDER BY b.created_at DESC 
                        LIMIT 1
                    ) as reserved_by_customer,
                    (
                        SELECT b.booking_id
                        FROM bookings b 
                        WHERE b.car_id = c.id AND b.status IN ('confirmed', 'on_trip', 'pending')
                        ORDER BY b.created_at DESC 
                        LIMIT 1
                    ) as reserved_booking_id
             FROM cars c
             ${whereClause} 
             ORDER BY c.created_at DESC`,
            values
        );
        
        res.json(result.rows);
    } catch (err) {
        console.error("Database Query Error:", err.message);
        res.status(500).json({ message: 'Server error', error: err.message });
    }
};
const addCar = async (req, res) => {
    const {
        brand, model, year, rental_options, 
        image_url, description,
        insurance, owner, 
        current_km,
        current_fuel,
        start_date_of_insurance, end_date_of_insurance, car_mulkiya_id, start_date_of_mulkiya, end_date_of_mulkiya,
        maintenance_company, maintenance_start_date, maintenance_end_date,last_battery_change,
last_oil_change_km,
oil_change_interval,
 plate_number  

    } = req.body;

    try {
        const result = await query(
            `INSERT INTO cars (
                brand, model, year, rental_options,
                image_url, description, insurance, owner, 
                current_km, current_fuel, 
                start_date_of_insurance, end_date_of_insurance, car_mulkiya_id, start_date_of_mulkiya, end_date_of_mulkiya,
                maintenance_company, maintenance_start_date, maintenance_end_date,
last_battery_change,
last_oil_change_km,
oil_change_interval,
 plate_number  

            )
            VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18,$19,$20,$21,$22) 
            RETURNING *`,
            [
                brand, model, year, JSON.stringify(rental_options || []), 
                image_url, description, insurance, owner, 
                current_km, current_fuel, 
                start_date_of_insurance, end_date_of_insurance, car_mulkiya_id, start_date_of_mulkiya, end_date_of_mulkiya,
                maintenance_company, maintenance_start_date, maintenance_end_date,last_battery_change,
last_oil_change_km,
oil_change_interval,
 plate_number  

            ]
        );
        res.status(201).json(result.rows[0]);
    } catch (err) {
        console.error("Error adding car:", err);
        if (err.code === '23505' && err.constraint === 'unique_car_mulkiya_id') {
            return res.status(400).json({ message: 'Car Mulkiya ID must be unique. Another car already has this ID.' });
        }
        res.status(500).json({ message: 'Server error' });
    }
};
const updateCar = async (req, res) => {
    const { id } = req.params;
    const {
        brand, model, year, rental_options, 
        image_url, description, status,
        insurance, owner, 
        current_km,
        current_fuel,
        start_date_of_insurance, end_date_of_insurance, car_mulkiya_id, 
        start_date_of_mulkiya, end_date_of_mulkiya,
        maintenance_company, maintenance_start_date, maintenance_end_date,
        last_battery_change,
        last_oil_change_km,
        oil_change_interval,
         plate_number  
    } = req.body;

    try {
        const result = await query(
            `UPDATE cars SET 
                brand=$1, model=$2, year=$3, rental_options=$4, 
                image_url=$5, description=$6, status=$7, 
                insurance=$8, owner=$9, 
                current_km=$10, current_fuel=$11,
                start_date_of_insurance=$12, end_date_of_insurance=$13, 
                car_mulkiya_id=$14, start_date_of_mulkiya=$15, end_date_of_mulkiya=$16, 
                maintenance_company=$17, maintenance_start_date=$18, maintenance_end_date=$19,
                last_battery_change=$20,
                last_oil_change_km=$21,
                oil_change_interval=$22, 
                 plate_number =$23,
             WHERE id=$24 RETURNING *`,  // ← CHANGE $25 to $23
            [
                brand, model, year, JSON.stringify(rental_options || []), 
                image_url, description, status, insurance, owner, 
                current_km, current_fuel, 
                start_date_of_insurance, end_date_of_insurance, 
                car_mulkiya_id, start_date_of_mulkiya, end_date_of_mulkiya, 
                maintenance_company, maintenance_start_date, maintenance_end_date,
                last_battery_change,
                last_oil_change_km,
                oil_change_interval,
                 plate_number  ,
                id  // ← This is parameter #23
            ]
        );

        if (result.rows.length === 0) return res.status(404).json({ message: 'Car not found' });
        res.json(result.rows[0]);
    } catch (err) {
        console.error("Error updating car:", err);
        if (err.code === '23505' && err.constraint === 'unique_car_mulkiya_id') {
            return res.status(400).json({ message: 'Car Mulkiya ID must be unique. Another car already has this ID.' });
        }
        res.status(500).json({ message: 'Server error' });
    }
};
const deleteCar = async (req, res) => {
    const { id } = req.params;
    try {
        const result = await query('DELETE FROM cars WHERE id = $1 RETURNING *', [id]);
        if (result.rows.length === 0) {
            return res.status(404).json({ message: 'Car not found' });
        }
        res.json({ message: 'Car deleted' });
    } catch (err) {
        res.status(500).json({ message: 'Server error' });
    }
};
const getCarPricing = async (req, res) => {
  const { id } = req.params;

  try {
    const result = await query(
      "SELECT rental_options FROM cars WHERE id=$1",
      [id]
    );

    if (result.rows.length === 0) {
      return res.json([]);
    }

    const options = result.rows[0].rental_options || [];

    const formatted = options.map(o => ({
      mode: o.category,
      label: o.category,
      price: o.price,
      km: o.km_limit 
    }));

    res.json(formatted);

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

const getCarsWithAlerts = async (req, res) => {
    try {
        const result = await query(`
            SELECT 
                id, brand, model, year, 
                end_date_of_insurance,
                end_date_of_mulkiya,
                maintenance_end_date,
                last_battery_change,
                last_oil_change_km,
                oil_change_interval,
                current_km,
                image_url,
                status
            FROM cars
            WHERE 
                end_date_of_insurance IS NOT NULL 
                OR end_date_of_mulkiya IS NOT NULL 
                OR maintenance_end_date IS NOT NULL
                OR last_battery_change IS NOT NULL
                OR (last_oil_change_km IS NOT NULL AND oil_change_interval IS NOT NULL)
            ORDER BY created_at DESC
        `);
        
        const cars = result.rows;
        const alerts = [];
        const today = new Date();
        today.setHours(0, 0, 0, 0);

        cars.forEach(car => {
            // 1. Insurance Alert
            if (car.end_date_of_insurance) {
                const endDate = new Date(car.end_date_of_insurance);
                endDate.setHours(0, 0, 0, 0);
                const daysDiff = Math.ceil((endDate - today) / (1000 * 60 * 60 * 24));
                if (daysDiff >= 0 && daysDiff <= 15) {
                    const formattedDate = endDate.toISOString().split('T')[0];
                    alerts.push({
                        car_id: car.id,
                        brand: car.brand,
                        model: car.model,
                        year: car.year,
                        image_url: car.image_url,
                        status: car.status,
                        type: 'insurance',
                        title: 'Insurance Expiring Soon',
                        message: `Insurance expires in ${daysDiff} days (${formattedDate})`,
                        end_date: car.end_date_of_insurance,
                        days_remaining: daysDiff,
                        priority: daysDiff <= 5 ? 'high' : 'medium'
                    });
                }
            }

            // 2. Mulkiya Alert
            if (car.end_date_of_mulkiya) {
                const endDate = new Date(car.end_date_of_mulkiya);
                endDate.setHours(0, 0, 0, 0);
                const daysDiff = Math.ceil((endDate - today) / (1000 * 60 * 60 * 24));
                if (daysDiff >= 0 && daysDiff <= 15) {
                    const formattedDate = endDate.toISOString().split('T')[0];
                    alerts.push({
                        car_id: car.id,
                        brand: car.brand,
                        model: car.model,
                        year: car.year,
                        image_url: car.image_url,
                        status: car.status,
                        type: 'mulkiya',
                        title: 'Mulkiya Expiring Soon',
                        message: `Mulkiya expires in ${daysDiff} days (${formattedDate})`,
                        end_date: car.end_date_of_mulkiya,
                        days_remaining: daysDiff,
                        priority: daysDiff <= 5 ? 'high' : 'medium'
                    });
                }
            }

            // 3. Maintenance Alert
            if (car.maintenance_end_date) {
                const endDate = new Date(car.maintenance_end_date);
                endDate.setHours(0, 0, 0, 0);
                const daysDiff = Math.ceil((endDate - today) / (1000 * 60 * 60 * 24));
                if (daysDiff >= 0 && daysDiff <= 15) {
                    alerts.push({
                        car_id: car.id,
                        brand: car.brand,
                        model: car.model,
                        year: car.year,
                        image_url: car.image_url,
                        status: car.status,
                        type: 'maintenance',
                        title: 'Maintenance Period Ending Soon',
                        message: `Maintenance ends in ${daysDiff} days`,
                        end_date: car.maintenance_end_date,
                        days_remaining: daysDiff,
                        priority: daysDiff <= 5 ? 'high' : 'medium'
                    });
                }
            }

            // 4. Battery Alert
            if (car.last_battery_change) {
                const batteryDate = new Date(car.last_battery_change);
                batteryDate.setHours(0, 0, 0, 0);
                const daysDiff = Math.ceil((batteryDate - today) / (1000 * 60 * 60 * 24));
                if (daysDiff >= 0 && daysDiff <= 15) {
                    const formattedDate = batteryDate.toISOString().split('T')[0];
                    alerts.push({
                        car_id: car.id,
                        brand: car.brand,
                        model: car.model,
                        year: car.year,
                        image_url: car.image_url,
                        status: car.status,
                        type: 'battery',
                        title: 'Battery Change Due Soon',
                        message: `Battery change scheduled in ${daysDiff} days (${formattedDate})`,
                        end_date: car.last_battery_change,
                        days_remaining: daysDiff,
                        priority: daysDiff <= 5 ? 'high' : 'medium'
                    });
                }
            }

            // 5. Oil Change Alert - ✅ KEEP THIS
            if (car.last_oil_change_km && car.oil_change_interval && car.current_km !== null && car.current_km !== undefined) {
                const lastOil = Number(car.last_oil_change_km);
                const interval = Number(car.oil_change_interval);
                const currentKm = Number(car.current_km);
                
                const dueKm = lastOil + interval;
                const kmRemaining = dueKm - currentKm;
                
                if (currentKm >= (dueKm - 100)) {
                    let title, message, priority;
                    if (kmRemaining > 0) {
                        title = '🛢️ Oil Change Due Soon';
                        message = `Oil change due in ${kmRemaining} KM. Last change: ${lastOil} KM. Interval: ${interval} KM.`;
                        priority = kmRemaining <= 50 ? 'high' : 'medium';
                    } else {
                        title = '🚨 Oil Change Overdue!';
                        message = `Oil change overdue by ${Math.abs(kmRemaining)} KM. Last change: ${lastOil} KM. Interval: ${interval} KM.`;
                        priority = 'high';
                    }
                    alerts.push({
                        car_id: car.id,
                        brand: car.brand,
                        model: car.model,
                        year: car.year,
                        image_url: car.image_url,
                        status: car.status,
                        type: 'oil_change',
                        title: title,
                        message: message,
                        end_date: null,
                        days_remaining: kmRemaining,
                        priority: priority
                    });
                }
            }
        });

        alerts.sort((a, b) => {
            if (a.priority === 'high' && b.priority !== 'high') return -1;
            if (a.priority !== 'high' && b.priority === 'high') return 1;
            return a.days_remaining - b.days_remaining;
        });

        res.json({ alerts });
    } catch (err) {
        console.error('Error fetching alerts:', err);
        res.status(500).json({ message: 'Server error', error: err.message });
    }
};

module.exports = { 
  getAllCars, 
  addCar, 
  updateCar, 
  deleteCar,
  getCarPricing,
 getCarsWithAlerts
};