const db = require('../config/database');

const AmbulanceRequest = {
    // Get all ambulance requests
    findAll: (callback) => {
        const query = 
            `SELECT ar.*, 
             u.first_name, u.last_name, u.email,
             creator.first_name as created_by_first_name,
             creator.last_name as created_by_last_name,
             creator.email as created_by_email
            FROM ambulance_requests ar 
            JOIN users u ON ar.request_by = u.id 
            LEFT JOIN users creator ON ar.created_by = creator.id 
            ORDER BY ar.id DESC`;
        db.query(query, callback);
    },

    markPickupConfirmed: (requestId, callback) => {
        const query = `
            UPDATE ambulance_requests
            SET pickup_confirmed_at = COALESCE(pickup_confirmed_at, NOW())
            WHERE id = ?
        `;
        db.query(query, [requestId], callback);
    },

    // Set trip_started_at without changing status
    markTripStarted: (requestId, callback) => {
        const query = `
            UPDATE ambulance_requests
            SET trip_started_at = COALESCE(trip_started_at, NOW())
            WHERE id = ?
        `;
        db.query(query, [requestId], callback);
    },

    // Get all ambulance requests with full client/user data
    findAllWithClients: (callback) => {
        const query = 
            `SELECT 
                ar.*,
                u.id as user_id,
                u.first_name,
                u.last_name,
                u.email,
                u.role,
                u.is_verified,
                c.id as client_id,
                c.phone_number,
                c.insurance_scheme_id,
                c.is_cash_client,
                c.membership_number,
                c.suffix,
                c.address,
                c.date_of_birth,
                c.emergency_contact_name,
                c.emergency_contact_phone,
                creator.first_name as created_by_first_name,
                creator.last_name as created_by_last_name,
                creator.email as created_by_email
            FROM ambulance_requests ar
            INNER JOIN users u ON ar.request_by = u.id
            LEFT JOIN clients c ON u.id = c.user_id
            LEFT JOIN users creator ON ar.created_by = creator.id
            ORDER BY ar.id DESC`;
        db.query(query, callback);
    },

    // Get ambulance request by ID (note: original code uses user ID, not request ID)
    findById: (userId, callback) => {
        const query = `
            SELECT ar.*, u.first_name, u.last_name, u.email 
            FROM ambulance_requests ar 
            JOIN users u ON ar.request_by = u.id 
            WHERE u.id = ? ORDER BY ar.id DESC
        `;
        db.query(query, [userId], callback);
    },

    // Get ambulance requests by user ID
    findByUserId: (userId, callback) => {
        const query = 'SELECT * FROM ambulance_requests WHERE request_by = ? ORDER BY id DESC';
        db.query(query, [userId], callback);
    },

    // Get ambulance requests by user ID with dispatch information
    findByUserIdWithDispatch: (userId, callback) => {
        const query = `
            SELECT 
                -- Ambulance Request Details
                ar.id as request_id,
                ar.request_by,
                ar.is_urgent,
                ar.location,
                ar.destination,
                ar.latitude,
                ar.longitude,
                ar.destination_latitude,
                ar.destination_longitude,
                ar.status as request_status,
                ar.comment,
                ar.created_at as request_created_at,
                ar.updated_at as request_updated_at,
                
                -- Dispatch Details
                d.id as dispatch_id,
                d.ambulance_id,
                d.driver_id,
                d.created_at as dispatch_created_at,
                d.updated_at as dispatch_updated_at,
                
                -- Ambulance Details (if dispatched)
                a.vehicle_number,
                a.license_plate,
                a.model as ambulance_model,
                a.make as ambulance_make,
                a.status as ambulance_status,
                a.latitude as ambulance_latitude,
                a.longitude as ambulance_longitude,
                
                -- Driver Details (if dispatched)
                dr.id as driver_table_id,
                dr.license_number,
                dr.status as driver_status,
                
                -- Driver User Details (if dispatched)
                du.id as driver_user_id,
                du.first_name as driver_first_name,
                du.last_name as driver_last_name,
                du.email as driver_email
                
            FROM ambulance_requests ar
            LEFT JOIN dispatch d ON ar.id = d.ambulance_request_id
            LEFT JOIN ambulance a ON d.ambulance_id = a.id
            LEFT JOIN driver dr ON d.driver_id = dr.id
            LEFT JOIN users du ON dr.user_id = du.id
            WHERE ar.request_by = ?
            ORDER BY ar.id DESC
        `;
        db.query(query, [userId], callback);
    },

    // Get most recent pending or dispatched request by user ID with dispatch information
    findRecentPendingOrDispatchedByUserId: (userId, callback) => {
        const query = `
            SELECT 
                -- Ambulance Request Details
                ar.id as request_id,
                ar.request_by,
                ar.is_urgent,
                ar.location,
                ar.destination,
                ar.latitude,
                ar.longitude,
                ar.destination_latitude,
                ar.destination_longitude,
                ar.status as request_status,
                ar.comment,
                ar.created_at as request_created_at,
                ar.updated_at as request_updated_at,
                
                -- Dispatch Details
                d.id as dispatch_id,
                d.ambulance_id,
                d.driver_id,
                d.created_at as dispatch_created_at,
                d.updated_at as dispatch_updated_at,
                
                -- Ambulance Details (if dispatched)
                a.vehicle_number,
                a.license_plate,
                a.model as ambulance_model,
                a.make as ambulance_make,
                a.status as ambulance_status,
                a.latitude as ambulance_latitude,
                a.longitude as ambulance_longitude,
                
                -- Driver Details (if dispatched)
                dr.id as driver_table_id,
                dr.license_number,
                dr.status as driver_status,
                
                -- Driver User Details (if dispatched)
                du.id as driver_user_id,
                du.first_name as driver_first_name,
                du.last_name as driver_last_name,
                du.email as driver_email
                
            FROM ambulance_requests ar
            LEFT JOIN dispatch d ON ar.id = d.ambulance_request_id
            LEFT JOIN ambulance a ON d.ambulance_id = a.id
            LEFT JOIN driver dr ON d.driver_id = dr.id
            LEFT JOIN users du ON dr.user_id = du.id
            WHERE ar.request_by = ? 
            AND (ar.status = 'pending' OR ar.status = 'dispatched')
            ORDER BY ar.id DESC
            LIMIT 1
        `;
        db.query(query, [userId], callback);
    },

    // Get active ambulance requests assigned to a driver (via dispatch table)
    findActiveByDriverId: (driverId, callback) => {
        const query = `
            SELECT 
                ar.*,
                u.first_name,
                u.last_name,
                u.email,
                d.id AS dispatch_id,
                d.ambulance_id,
                d.driver_id
            FROM ambulance_requests ar
            JOIN dispatch d ON ar.id = d.ambulance_request_id
            JOIN users u ON ar.request_by = u.id
            WHERE d.driver_id = ?
              AND ar.status NOT IN ('completed', 'cancelled')
            ORDER BY ar.updated_at DESC
        `;
        db.query(query, [driverId], callback);
    },

    // Get recent ambulance requests for a driver (most recent first)
    findRecentByDriverId: (driverId, limit, callback) => {
        const query = `
            SELECT 
                ar.*,
                u.first_name,
                u.last_name,
                u.email,
                d.id AS dispatch_id,
                d.ambulance_id,
                d.driver_id
            FROM ambulance_requests ar
            JOIN dispatch d ON ar.id = d.ambulance_request_id
            JOIN users u ON ar.request_by = u.id
            WHERE d.driver_id = ?
            ORDER BY ar.updated_at DESC
            LIMIT ?
        `;
        db.query(query, [driverId, limit], callback);
    },

    // Get all ambulance requests assigned to a driver (includes all trips, not just started ones)
    findAllByDriverId: (driverId, callback) => {
        const query = `
            SELECT 
                ar.*,
                ar.id AS request_id,
                u.first_name,
                u.last_name,
                u.email,
                d.id AS dispatch_id,
                d.ambulance_id,
                d.driver_id
            FROM ambulance_requests ar
            JOIN dispatch d ON ar.id = d.ambulance_request_id
            JOIN users u ON ar.request_by = u.id
            WHERE d.driver_id = ?
            ORDER BY ar.updated_at DESC
        `;
        db.query(query, [driverId], callback);
    },

    // Get aggregate stats for a driver's trips
    getDriverTripStats: (driverId, callback) => {
        const query = `
            SELECT 
                SUM(CASE WHEN ar.trip_started_at IS NOT NULL THEN 1 ELSE 0 END) AS total_trips,
                SUM(
                    CASE 
                        WHEN ar.trip_started_at IS NOT NULL 
                             AND DATE(ar.trip_started_at) = CURDATE()
                        THEN 1 ELSE 0 END
                ) AS trips_today,
                AVG(
                    CASE 
                        WHEN ar.trip_started_at IS NOT NULL 
                             AND d.created_at IS NOT NULL 
                             AND TIMESTAMPDIFF(SECOND, d.created_at, ar.trip_started_at) >= 0
                        THEN TIMESTAMPDIFF(SECOND, d.created_at, ar.trip_started_at)
                        ELSE NULL
                    END
                ) AS avg_response_seconds
            FROM ambulance_requests ar
            INNER JOIN dispatch d ON ar.id = d.ambulance_request_id
            WHERE d.driver_id = ?
              AND ar.trip_started_at IS NOT NULL
        `;
        db.query(query, [driverId], callback);
    },

    // Get ambulance request details (without user join)
    findRequestById: (requestId, callback) => {
        const query = 'SELECT * FROM ambulance_requests WHERE id = ?';
        db.query(query, [requestId], callback);
    },

    // Get all pending requests (status = 'pending' and no dispatch assigned)
    findPendingRequests: (callback) => {
        const query = `
            SELECT 
                ar.*,
                u.first_name,
                u.last_name,
                u.email,
                u.phone_number
            FROM ambulance_requests ar
            LEFT JOIN dispatch d ON ar.id = d.ambulance_request_id
            JOIN users u ON ar.request_by = u.id
            WHERE ar.status = 'pending' AND d.id IS NULL
            ORDER BY ar.created_at DESC
        `;
        db.query(query, callback);
    },

    // Get ambulance request by ID with full client details and dispatch information
    findByIdWithDetails: (requestId, callback) => {
        const query = `
            SELECT 
                -- Ambulance Request Details
                ar.id as request_id,
                ar.request_by,
                ar.is_urgent,
                ar.location,
                ar.destination,
                ar.latitude,
                ar.longitude,
                ar.destination_latitude,
                ar.destination_longitude,
                ar.status as request_status,
                ar.comment,
                ar.created_at as request_created_at,
                ar.updated_at as request_updated_at,
                
                -- Client User Details
                u.id as user_id,
                u.first_name,
                u.last_name,
                u.email,
                u.role,
                u.is_verified,
                
                -- Client Details
                c.id as client_id,
                c.phone_number,
                c.insurance_scheme_id,
                c.is_cash_client,
                c.membership_number,
                c.suffix,
                c.address,
                c.date_of_birth,
                c.emergency_contact_name,
                c.emergency_contact_phone,
                
                -- Dispatch Details
                d.id as dispatch_id,
                d.ambulance_id,
                d.driver_id,
                d.created_at as dispatch_created_at,
                d.updated_at as dispatch_updated_at,
                
                -- Ambulance Details (if dispatched)
                a.vehicle_number,
                a.license_plate,
                a.model as ambulance_model,
                a.make as ambulance_make,
                a.status as ambulance_status,
                a.latitude as ambulance_latitude,
                a.longitude as ambulance_longitude,
                
                -- Driver Details (if dispatched)
                dr.id as driver_table_id,
                dr.license_number,
                dr.status as driver_status,
                
                -- Driver User Details (if dispatched)
                du.id as driver_user_id,
                du.first_name as driver_first_name,
                du.last_name as driver_last_name,
                du.email as driver_email
                
            FROM ambulance_requests ar
            INNER JOIN users u ON ar.request_by = u.id
            LEFT JOIN clients c ON u.id = c.user_id
            LEFT JOIN dispatch d ON ar.id = d.ambulance_request_id
            LEFT JOIN ambulance a ON d.ambulance_id = a.id
            LEFT JOIN driver dr ON d.driver_id = dr.id
            LEFT JOIN users du ON dr.user_id = du.id
            WHERE ar.id = ?
        `;
        db.query(query, [requestId], callback);
    },

    // Create new ambulance request
    create: (requestData, callback) => {
        const { request_by, created_by, is_urgent, location, destination, latitude, longitude, destination_latitude, destination_longitude, status } = requestData;
        
        // Debug logging
        console.log('AmbulanceRequest.create - Received data:', {
            destination_latitude,
            destination_longitude,
            destination_latitude_type: typeof destination_latitude,
            destination_longitude_type: typeof destination_longitude
        });
        
        // Validate destination coordinates are provided (required fields)
        if (destination_latitude === undefined || destination_latitude === null || destination_latitude === '') {
            console.error('ERROR: destination_latitude is missing or empty');
            return callback(new Error('destination_latitude is required'));
        }
        
        if (destination_longitude === undefined || destination_longitude === null || destination_longitude === '') {
            console.error('ERROR: destination_longitude is missing or empty');
            return callback(new Error('destination_longitude is required'));
        }
        
        // Convert to numbers to ensure proper type
        const destLat = parseFloat(destination_latitude);
        const destLng = parseFloat(destination_longitude);
        
        console.log('After parseFloat:', { destLat, destLng, destLat_type: typeof destLat, destLng_type: typeof destLng });
        
        // Validate they are valid numbers
        if (isNaN(destLat) || isNaN(destLng)) {
            console.error('ERROR: Invalid numbers after parseFloat');
            return callback(new Error('destination_latitude and destination_longitude must be valid numbers'));
        }
        
        const query = 
            `INSERT INTO ambulance_requests 
            (request_by, created_by, is_urgent, location, destination, latitude, longitude, destination_latitude, destination_longitude, status) 
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
        
        const params = [
            request_by, 
            created_by || null, 
            is_urgent || false, 
            location, 
            destination || null, 
            latitude, 
            longitude,
            destLat,
            destLng,
            status || 'pending'
        ];
        
        console.log('Final query parameters:', params);
        
        db.query(query, params, (err, results) => {
            if (err) {
                console.error('Database query error:', err);
                return callback(err);
            }
            console.log('✓ Successfully inserted request with ID:', results.insertId);
            callback(null, results);
        });
    },

    // Update ambulance request
    update: (requestId, requestData, callback) => {
        const { is_urgent, location, destination, latitude, longitude, destination_latitude, destination_longitude, status } = requestData;
        const query = 
            `UPDATE ambulance_requests 
            SET is_urgent = ?, location = ?, destination = ?, latitude = ?, longitude = ?, destination_latitude = ?, destination_longitude = ?, status = ? 
            WHERE id = ?`;
        
        db.query(query, [
            is_urgent, location, destination, latitude, longitude, destination_latitude || null, destination_longitude || null, status, requestId
        ], callback);
    },

    // Update ambulance request status and comment
    updateStatus: (requestId, status, comment, callback) => {
        // Determine if we need to set trip timestamps based on status
        const normalizedStatus = status.toLowerCase();
        let query = 'UPDATE ambulance_requests SET status = ?';
        const params = [status];
        
        // Only update comment if one is provided
        if (comment !== undefined && comment !== null) {
            query += ', comment = ?';
            params.push(comment);
        }
        
        // Set trip_started_at when status changes to 'ongoing' or 'enroute'
        // Only set if it's not already set (COALESCE keeps existing value if present)
        if (normalizedStatus === 'ongoing' || normalizedStatus === 'enroute') {
            query += ', trip_started_at = COALESCE(trip_started_at, NOW())';
        }
        
        // Set trip_completed_at when status changes to 'completed'
        // Only set if it's not already set (COALESCE keeps existing value if present)
        // Also ensure trip_started_at is set if it's null (use current time as fallback)
        if (normalizedStatus === 'completed') {
            query += ', trip_completed_at = COALESCE(trip_completed_at, NOW())';
            query += ', trip_started_at = COALESCE(trip_started_at, NOW())';
        }

        // Set pickup_confirmed_at when moving beyond dispatched
        if (normalizedStatus === 'ongoing' || normalizedStatus === 'enroute') {
            query += ', pickup_confirmed_at = COALESCE(pickup_confirmed_at, NOW())';
        }
        
        query += ' WHERE id = ?';
        params.push(requestId);
        
        // Log the query for debugging (remove in production if needed)
        console.log('Updating trip status:', { requestId, status, normalizedStatus, query, params });
        
        db.query(query, params, (err, results) => {
            if (err) {
                console.error('Error updating trip status:', err);
            } else {
                console.log('Trip status updated successfully:', results);
            }
            callback(err, results);
        });
    },

    // Delete ambulance request
    delete: (requestId, callback) => {
        const query = 'DELETE FROM ambulance_requests WHERE id = ?';
        db.query(query, [requestId], callback);
    },

    // Find ambulance requests within a date range
    findByDateRange: (startDate, endDate, callback) => {
        const query = `
            SELECT * FROM ambulance_requests 
            WHERE created_at >= ? AND created_at <= ?
            ORDER BY created_at DESC
        `;
        db.query(query, [startDate, endDate], callback);
    }
};

module.exports = AmbulanceRequest;

