-- Fleet Management Module Demo Data
-- Realistic fleet operations data with vehicles, drivers, GPS tracking, trips, maintenance, and fuel logs

-- Assuming org_id = 1, and some existing users, locations

-- ==================== DRIVERS ====================
INSERT INTO fleet_drivers (org_id, driver_code, first_name, last_name, phone, email, license_number, license_type, license_expiry_date, date_of_birth, address, emergency_contact_name, emergency_contact_phone, hire_date, employment_status, experience_years, rating, total_trips, total_distance_km, status, created_by) VALUES
(1, 'DRV-0001', 'John', 'Kamau', '+254712345678', 'john.kamau@example.com', 'DL-789456', 'PSV', '2025-08-15', '1985-03-12', 'Nairobi, Kenya', 'Mary Kamau', '+254712345679', '2020-01-10', 'active', 8, 4.75, 520, 42500.00, 'available', 1),
(1, 'DRV-0002', 'Peter', 'Mwangi', '+254723456789', 'peter.mwangi@example.com', 'DL-654321', 'HMV', '2026-02-20', '1990-07-25', 'Thika, Kenya', 'Jane Mwangi', '+254723456790', '2021-05-15', 'active', 5, 4.60, 380, 35200.00, 'available', 1),
(1, 'DRV-0003', 'David', 'Otieno', '+254734567890', 'david.otieno@example.com', 'DL-987654', 'PSV', '2025-11-10', '1988-11-03', 'Kisumu, Kenya', 'Grace Otieno', '+254734567891', '2019-08-01', 'active', 10, 4.85, 650, 58000.00, 'on-trip', 1),
(1, 'DRV-0004', 'James', 'Mutua', '+254745678901', 'james.mutua@example.com', 'DL-456789', 'HMV', '2024-12-05', '1982-05-18', 'Mombasa, Kenya', 'Lucy Mutua', '+254745678902', '2018-03-20', 'active', 12, 4.70, 720, 68000.00, 'available', 1),
(1, 'DRV-0005', 'Samuel', 'Kiplagat', '+254756789012', 'samuel.kip@example.com', 'DL-321654', 'PSV', '2026-06-30', '1992-09-14', 'Eldoret, Kenya', 'Nancy Kiplagat', '+254756789013', '2022-01-05', 'active', 3, 4.55, 210, 18500.00, 'available', 1),
(1, 'DRV-0006', 'Michael', 'Wanjala', '+254767890123', 'michael.wan@example.com', 'DL-147258', 'HMV', '2025-04-12', '1987-12-08', 'Nakuru, Kenya', 'Eunice Wanjala', '+254767890124', '2020-09-12', 'active', 7, 4.80, 490, 44000.00, 'on-trip', 1);

-- ==================== VEHICLES ====================
INSERT INTO fleet_vehicles (org_id, vehicle_number, registration_number, make, model, year, vehicle_type, fuel_type, capacity_kg, seating_capacity, vin, engine_number, color, purchase_date, purchase_price, current_value, insurance_company, insurance_policy_number, insurance_expiry_date, registration_expiry_date, fitness_certificate_expiry, pollution_certificate_expiry, permit_expiry_date, current_mileage, fuel_tank_capacity, fuel_efficiency, gps_device_id, gps_device_status, assigned_driver_id, assigned_location_id, home_location_lat, home_location_lng, status, created_by) VALUES
-- Trucks
(1, 'TRK-001', 'KCA-123A', 'Isuzu', 'FRR', 2019, 'truck', 'diesel', 5000.00, 3, 'ISUZU123456789', 'ENG-FR-001', 'White', '2019-02-15', 45000.00, 32000.00, 'Jubilee Insurance', 'POL-2024-001', '2025-02-14', '2025-02-14', '2025-02-14', '2025-02-14', '2025-02-14', 85000.00, 100.00, 8.50, 'GPS-TRK-001', 'active', 1, 1, -1.286389, 36.817223, 'active', 1),

(1, 'TRK-002', 'KCB-456B', 'Mitsubishi', 'Canter', 2020, 'truck', 'diesel', 3000.00, 3, 'MIT123456789', 'ENG-MT-002', 'Blue', '2020-06-10', 38000.00, 29000.00, 'AAR Insurance', 'POL-2024-002', '2025-06-09', '2025-06-09', '2025-06-09', '2025-06-09', '2025-06-09', 62000.00, 80.00, 9.20, 'GPS-TRK-002', 'active', 2, 1, -1.286389, 36.817223, 'active', 1),

(1, 'TRK-003', 'KCC-789C', 'Isuzu', 'NQR', 2018, 'truck', 'diesel', 7000.00, 3, 'ISUZU987654321', 'ENG-NQ-003', 'White', '2018-11-20', 52000.00, 34000.00, 'Jubilee Insurance', 'POL-2024-003', '2025-11-19', '2025-11-19', '2025-11-19', '2025-11-19', '2025-11-19', 124000.00, 120.00, 7.80, 'GPS-TRK-003', 'active', 3, 1, -1.286389, 36.817223, 'on-trip', 1),

(1, 'TRK-004', 'KCD-234D', 'Mercedes-Benz', 'Actros', 2021, 'truck', 'diesel', 10000.00, 2, 'MB123456789', 'ENG-MB-004', 'Silver', '2021-03-15', 95000.00, 82000.00, 'CIC Insurance', 'POL-2024-004', '2025-03-14', '2025-03-14', '2025-03-14', '2025-03-14', '2025-03-14', 48000.00, 200.00, 6.50, 'GPS-TRK-004', 'active', 4, 1, -1.286389, 36.817223, 'active', 1),

-- Vans
(1, 'VAN-001', 'KDA-567E', 'Toyota', 'Hiace', 2020, 'van', 'diesel', 1500.00, 14, 'TOY123456789', 'ENG-TH-005', 'White', '2020-01-08', 28000.00, 22000.00, 'Jubilee Insurance', 'POL-2024-005', '2025-01-07', '2025-01-07', '2025-01-07', '2025-01-07', '2025-01-07', 95000.00, 70.00, 11.50, 'GPS-VAN-001', 'active', 5, 1, -1.286389, 36.817223, 'active', 1),

(1, 'VAN-002', 'KDB-890F', 'Nissan', 'Caravan', 2019, 'van', 'petrol', 1200.00, 12, 'NIS123456789', 'ENG-NC-006', 'Silver', '2019-08-22', 24000.00, 18000.00, 'AAR Insurance', 'POL-2024-006', '2025-08-21', '2025-08-21', '2025-08-21', '2025-08-21', '2025-08-21', 110000.00, 65.00, 10.80, 'GPS-VAN-002', 'active', 6, 1, -1.286389, 36.817223, 'on-trip', 1),

-- Cars
(1, 'CAR-001', 'KDC-123G', 'Toyota', 'Corolla', 2021, 'car', 'petrol', 400.00, 5, 'TOY987654321', 'ENG-TC-007', 'Black', '2021-05-10', 22000.00, 18500.00, 'Jubilee Insurance', 'POL-2024-007', '2025-05-09', '2025-05-09', '2025-05-09', '2025-05-09', '2025-05-09', 38000.00, 50.00, 14.50, 'GPS-CAR-001', 'active', NULL, 1, -1.286389, 36.817223, 'active', 1),

(1, 'CAR-002', 'KDD-456H', 'Honda', 'Civic', 2020, 'car', 'petrol', 400.00, 5, 'HON123456789', 'ENG-HC-008', 'White', '2020-09-15', 20000.00, 16000.00, 'CIC Insurance', 'POL-2024-008', '2025-09-14', '2025-09-14', '2025-09-14', '2025-09-14', '2025-09-14', 52000.00, 47.00, 15.20, 'GPS-CAR-002', 'active', NULL, 1, -1.286389, 36.817223, 'active', 1),

-- Pickup
(1, 'PKP-001', 'KDE-789I', 'Toyota', 'Hilux', 2022, 'truck', 'diesel', 1000.00, 5, 'HILUX123456', 'ENG-HL-009', 'Gray', '2022-01-20', 42000.00, 39000.00, 'Jubilee Insurance', 'POL-2024-009', '2025-01-19', '2025-01-19', '2025-01-19', '2025-01-19', '2025-01-19', 22000.00, 80.00, 10.50, 'GPS-PKP-001', 'active', NULL, 1, -1.286389, 36.817223, 'active', 1),

-- Motorcycle
(1, 'MCY-001', 'KMCA-111J', 'Yamaha', 'FZ', 2021, 'motorcycle', 'petrol', 50.00, 2, 'YAM123456', 'ENG-YFZ-010', 'Red', '2021-07-12', 3500.00, 2800.00, 'AAR Insurance', 'POL-2024-010', '2025-07-11', '2025-07-11', '2025-07-11', '2025-07-11', '2025-07-11', 18000.00, 12.00, 35.00, 'GPS-MCY-001', 'active', NULL, 1, -1.286389, 36.817223, 'active', 1);

-- Update driver assignments
UPDATE fleet_vehicles SET assigned_driver_id = 1 WHERE vehicle_number = 'TRK-001';
UPDATE fleet_vehicles SET assigned_driver_id = 2 WHERE vehicle_number = 'TRK-002';
UPDATE fleet_vehicles SET assigned_driver_id = 3 WHERE vehicle_number = 'TRK-003';
UPDATE fleet_vehicles SET assigned_driver_id = 4 WHERE vehicle_number = 'TRK-004';
UPDATE fleet_vehicles SET assigned_driver_id = 5 WHERE vehicle_number = 'VAN-001';
UPDATE fleet_vehicles SET assigned_driver_id = 6 WHERE vehicle_number = 'VAN-002';

UPDATE fleet_drivers SET assigned_vehicle_id = (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-001') WHERE driver_code = 'DRV-0001';
UPDATE fleet_drivers SET assigned_vehicle_id = (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-002') WHERE driver_code = 'DRV-0002';
UPDATE fleet_drivers SET assigned_vehicle_id = (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003') WHERE driver_code = 'DRV-0003';
UPDATE fleet_drivers SET assigned_vehicle_id = (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-004') WHERE driver_code = 'DRV-0004';
UPDATE fleet_drivers SET assigned_vehicle_id = (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-001') WHERE driver_code = 'DRV-0005';
UPDATE fleet_drivers SET assigned_vehicle_id = (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-002') WHERE driver_code = 'DRV-0006';

-- ==================== TRIPS ====================
-- Completed trips
INSERT INTO fleet_trips (org_id, trip_number, vehicle_id, driver_id, trip_type, start_location, start_lat, start_lng, end_location, end_lat, end_lng, planned_start_time, actual_start_time, planned_end_time, actual_end_time, start_mileage, end_mileage, distance_km, duration_minutes, fuel_consumed_liters, fuel_cost, toll_charges, other_expenses, total_cost, cargo_weight_kg, cargo_description, max_speed_kmh, avg_speed_kmh, idle_time_minutes, harsh_braking_count, overspeeding_count, status, created_by) VALUES
(1, 'TRIP-20241118-0001', (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-001'), 1, 'delivery', 'Nairobi Warehouse', -1.286389, 36.817223, 'Mombasa Port', -4.043477, 39.668207, '2024-11-18 06:00:00', '2024-11-18 06:15:00', '2024-11-18 14:00:00', '2024-11-18 13:45:00', 84500.00, 84980.00, 480.00, 450, 56.50, 8475.00, 500.00, 200.00, 9175.00, 4500.00, 'Electronics and Hardware', 105.00, 65.00, 25, 2, 1, 'completed', 1),

(1, 'TRIP-20241119-0002', (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-002'), 2, 'delivery', 'Nairobi Warehouse', -1.286389, 36.817223, 'Nakuru Distribution', -0.303099, 36.080026, '2024-11-19 07:00:00', '2024-11-19 07:10:00', '2024-11-19 10:00:00', '2024-11-19 09:50:00', 61800.00, 61960.00, 160.00, 160, 17.40, 2610.00, 0.00, 150.00, 2760.00, 2800.00, 'Groceries', 92.00, 60.00, 15, 1, 0, 'completed', 1),

(1, 'TRIP-20241120-0003', (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-001'), 5, 'delivery', 'Nairobi Warehouse', -1.286389, 36.817223, 'Thika Town', -1.033394, 37.069334, '2024-11-20 08:00:00', '2024-11-20 08:20:00', '2024-11-20 10:00:00', '2024-11-20 09:55:00', 94850.00, 94895.00, 45.00, 95, 3.91, 586.50, 0.00, 50.00, 636.50, 1200.00, 'Retail Supplies', 88.00, 48.00, 10, 0, 0, 'completed', 1),

(1, 'TRIP-20241120-0004', (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-004'), 4, 'delivery', 'Nairobi Warehouse', -1.286389, 36.817223, 'Kisumu Central', -0.091702, 34.767956, '2024-11-20 05:00:00', '2024-11-20 05:15:00', '2024-11-20 11:00:00', '2024-11-20 10:45:00', 47800.00, 48150.00, 350.00, 330, 53.85, 8077.50, 300.00, 250.00, 8627.50, 9500.00, 'Food Products', 98.00, 63.00, 20, 1, 0, 'completed', 1);

-- In-progress trips
INSERT INTO fleet_trips (org_id, trip_number, vehicle_id, driver_id, trip_type, start_location, start_lat, start_lng, end_location, end_lat, end_lng, planned_start_time, actual_start_time, planned_end_time, start_mileage, cargo_weight_kg, cargo_description, status, created_by) VALUES
(1, 'TRIP-20241121-0005', (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 3, 'delivery', 'Nairobi Warehouse', -1.286389, 36.817223, 'Eldoret Town', 0.514277, 35.269779, '2024-11-21 06:00:00', '2024-11-21 06:10:00', '2024-11-21 12:00:00', 123950.00, 6500.00, 'Agricultural Supplies', 'in-progress', 1),

(1, 'TRIP-20241121-0006', (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-002'), 6, 'delivery', 'Nairobi Warehouse', -1.286389, 36.817223, 'Kiambu Town', -1.171462, 36.835080, '2024-11-21 09:00:00', '2024-11-21 09:15:00', '2024-11-21 11:00:00', 109980.00, 800.00, 'Office Equipment', 'in-progress', 1);

-- ==================== GPS TRACKING DATA ====================
-- Recent tracking data for all vehicles (last known positions)
INSERT INTO fleet_gps_tracking (org_id, vehicle_id, gps_device_id, timestamp, latitude, longitude, altitude, speed_kmh, heading, gps_status, ignition_status, fuel_level_percent, driver_id, location_name) VALUES
-- TRK-001 at warehouse
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-001'), 'GPS-TRK-001', '2024-11-21 10:30:00', -1.286389, 36.817223, 1661.00, 0.00, 0.00, 'active', false, 68.50, 1, 'Nairobi Warehouse'),

-- TRK-002 at warehouse
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-002'), 'GPS-TRK-002', '2024-11-21 10:32:00', -1.286500, 36.817100, 1662.00, 0.00, 0.00, 'active', false, 72.30, 2, 'Nairobi Warehouse'),

-- TRK-003 en route to Eldoret (in-progress trip)
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 'GPS-TRK-003', '2024-11-21 10:35:00', -0.520833, 35.283333, 2100.00, 78.50, 315.00, 'active', true, 45.20, 3, 'Near Nakuru'),

-- TRK-004 at warehouse
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-004'), 'GPS-TRK-004', '2024-11-21 10:28:00', -1.286200, 36.817400, 1660.00, 0.00, 0.00, 'active', false, 85.10, 4, 'Nairobi Warehouse'),

-- VAN-001 at warehouse
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-001'), 'GPS-VAN-001', '2024-11-21 10:31:00', -1.286300, 36.817300, 1661.00, 0.00, 0.00, 'active', false, 55.80, 5, 'Nairobi Warehouse'),

-- VAN-002 en route to Kiambu (in-progress trip)
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-002'), 'GPS-VAN-002', '2024-11-21 10:36:00', -1.200000, 36.825000, 1750.00, 62.30, 25.00, 'active', true, 63.40, 6, 'En route to Kiambu'),

-- CAR-001 parked at office
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'CAR-001'), 'GPS-CAR-001', '2024-11-21 10:25:00', -1.286389, 36.817223, 1661.00, 0.00, 0.00, 'active', false, 78.90, NULL, 'Office Parking'),

-- CAR-002 parked at office
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'CAR-002'), 'GPS-CAR-002', '2024-11-21 10:26:00', -1.286400, 36.817250, 1661.00, 0.00, 0.00, 'active', false, 82.10, NULL, 'Office Parking'),

-- PKP-001 parked at warehouse
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'PKP-001'), 'GPS-PKP-001', '2024-11-21 10:27:00', -1.286450, 36.817180, 1662.00, 0.00, 0.00, 'active', false, 91.20, NULL, 'Warehouse Parking'),

-- MCY-001 parked at office
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'MCY-001'), 'GPS-MCY-001', '2024-11-21 10:29:00', -1.286350, 36.817280, 1661.00, 0.00, 0.00, 'active', false, 95.50, NULL, 'Office Parking');

-- Historical GPS tracking for trip TRIP-20241121-0005 (TRK-003 to Eldoret - sample route points)
INSERT INTO fleet_gps_tracking (org_id, vehicle_id, gps_device_id, trip_id, timestamp, latitude, longitude, altitude, speed_kmh, heading, gps_status, ignition_status, fuel_level_percent, driver_id, location_name) VALUES
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 'GPS-TRK-003', (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241121-0005'), '2024-11-21 06:10:00', -1.286389, 36.817223, 1661.00, 0.00, 0.00, 'active', true, 85.00, 3, 'Nairobi Warehouse'),
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 'GPS-TRK-003', (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241121-0005'), '2024-11-21 06:30:00', -1.150000, 36.820000, 1720.00, 65.00, 350.00, 'active', true, 82.00, 3, 'Nairobi Outskirts'),
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 'GPS-TRK-003', (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241121-0005'), '2024-11-21 07:00:00', -1.000000, 36.850000, 1850.00, 75.00, 340.00, 'active', true, 78.00, 3, 'Limuru Area'),
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 'GPS-TRK-003', (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241121-0005'), '2024-11-21 08:00:00', -0.600000, 36.100000, 2050.00, 82.00, 325.00, 'active', true, 68.00, 3, 'Near Naivasha'),
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 'GPS-TRK-003', (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241121-0005'), '2024-11-21 09:00:00', -0.400000, 35.800000, 2150.00, 78.00, 315.00, 'active', true, 56.00, 3, 'Approaching Nakuru'),
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 'GPS-TRK-003', (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241121-0005'), '2024-11-21 10:00:00', -0.303099, 36.080026, 1890.00, 15.00, 310.00, 'active', true, 48.00, 3, 'Nakuru Town'),
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 'GPS-TRK-003', (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241121-0005'), '2024-11-21 10:35:00', -0.520833, 35.283333, 2100.00, 78.50, 315.00, 'active', true, 45.20, 3, 'Near Nakuru');

-- ==================== MAINTENANCE RECORDS ====================
INSERT INTO fleet_maintenance (org_id, vehicle_id, maintenance_number, maintenance_type, service_type, scheduled_date, actual_date, mileage_at_service, service_provider, service_location, technician_name, description, parts_replaced, parts_cost, labor_cost, total_cost, invoice_number, warranty_until, next_service_date, next_service_mileage, priority, status, downtime_hours, performed_by, created_by) VALUES
-- Completed maintenance
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-001'), 'MAINT-20241015-0001', 'scheduled', 'oil-change', '2024-10-15', '2024-10-15', 82000.00, 'AutoPro Services', 'Industrial Area, Nairobi', 'Joseph Kariuki', 'Regular oil change and filter replacement', 'Oil filter, Engine oil (15L)', 3500.00, 1500.00, 5000.00, 'INV-2024-1234', '2025-04-15', '2025-01-15', 87000.00, 'normal', 'completed', 2.50, 1, 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-002'), 'MAINT-20241101-0002', 'scheduled', 'tire-rotation', '2024-11-01', '2024-11-01', 61500.00, 'Tire Master Ltd', 'Mombasa Road, Nairobi', 'Peter Omondi', 'Tire rotation and wheel balancing', 'None', 0.00, 2000.00, 2000.00, 'INV-2024-2345', NULL, '2025-05-01', 71500.00, 'normal', 'completed', 1.50, 1, 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-004'), 'MAINT-20241110-0003', 'breakdown', 'brake-service', '2024-11-10', '2024-11-10', 47500.00, 'MercedesBenz Service Center', 'Mombasa Road, Nairobi', 'Engineer Mutiso', 'Brake pads replacement and system check', 'Brake pads (front & rear), Brake fluid', 8500.00, 3500.00, 12000.00, 'INV-2024-3456', '2025-11-10', '2025-02-10', 52500.00, 'high', 'completed', 4.00, 1, 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-001'), 'MAINT-20241105-0004', 'scheduled', 'general-service', '2024-11-05', '2024-11-05', 94000.00, 'Toyota Kenya', 'Thika Road, Nairobi', 'Daniel Wafula', 'General service - oil, filters, inspection', 'Oil filter, Air filter, Cabin filter, Oil (5L)', 4200.00, 2500.00, 6700.00, 'INV-2024-4567', '2025-05-05', '2025-02-05', 99000.00, 'normal', 'completed', 3.00, 1, 1),

-- Scheduled maintenance (upcoming)
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 'MAINT-20241125-0005', 'scheduled', 'general-service', '2024-11-25', NULL, NULL, 'Isuzu East Africa', 'Mombasa Road, Nairobi', NULL, 'Scheduled 125,000km service', NULL, 0.00, 0.00, 0.00, NULL, NULL, '2025-02-25', 130000.00, 'normal', 'scheduled', NULL, NULL, 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-002'), 'MAINT-20241128-0006', 'scheduled', 'oil-change', '2024-11-28', NULL, NULL, 'Nissan Service Center', 'Mombasa Road, Nairobi', NULL, 'Oil change and filter replacement', NULL, 0.00, 0.00, 0.00, NULL, NULL, '2025-02-28', 115000.00, 'normal', 'scheduled', NULL, NULL, 1),

-- Inspection due
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'CAR-001'), 'MAINT-20241130-0007', 'inspection', 'annual-inspection', '2024-11-30', NULL, NULL, 'NTSA Inspection Center', 'Nairobi', NULL, 'Annual vehicle inspection', NULL, 0.00, 0.00, 0.00, NULL, NULL, '2025-11-30', NULL, 'high', 'scheduled', NULL, NULL, 1);

-- ==================== FUEL LOGS ====================
INSERT INTO fleet_fuel_logs (org_id, vehicle_id, driver_id, trip_id, log_date, fuel_type, quantity_liters, price_per_liter, total_cost, mileage_at_fill, distance_since_last_fill, fuel_efficiency, fuel_station, fuel_station_location, receipt_number, payment_method, is_full_tank, created_by) VALUES
-- Recent fuel logs
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-001'), 1, (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241118-0001'), '2024-11-18 13:50:00', 'diesel', 58.20, 150.00, 8730.00, 84980.00, 480.00, 8.25, 'Total Mombasa', 'Mombasa Road', 'REC-2024-001', 'fuel-card', true, 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-002'), 2, (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241119-0002'), '2024-11-19 10:00:00', 'diesel', 18.50, 150.00, 2775.00, 61960.00, 160.00, 8.65, 'Shell Nakuru', 'Nakuru Town', 'REC-2024-002', 'fuel-card', true, 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-001'), 5, (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241120-0003'), '2024-11-20 10:05:00', 'diesel', 4.10, 150.00, 615.00, 94895.00, 45.00, 10.98, 'Kenol Thika', 'Thika Road', 'REC-2024-003', 'cash', true, 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-004'), 4, (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241120-0004'), '2024-11-20 11:00:00', 'diesel', 55.00, 150.00, 8250.00, 48150.00, 350.00, 6.36, 'Total Kisumu', 'Kisumu-Kakamega Road', 'REC-2024-004', 'fuel-card', true, 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 3, (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241121-0005'), '2024-11-21 06:05:00', 'diesel', 95.00, 150.00, 14250.00, 123950.00, 0.00, NULL, 'Shell Industrial Area', 'Nairobi', 'REC-2024-005', 'fuel-card', true, 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-002'), 6, (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241121-0006'), '2024-11-21 09:10:00', 'petrol', 52.00, 165.00, 8580.00, 109980.00, 0.00, NULL, 'Rubis Nairobi', 'Thika Road', 'REC-2024-006', 'fuel-card', true, 1),

-- Earlier refills
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-001'), 1, NULL, '2024-11-15 08:00:00', 'diesel', 85.00, 148.00, 12580.00, 84500.00, 520.00, 8.50, 'Shell Nairobi', 'Mombasa Road', 'REC-2024-010', 'fuel-card', true, 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'CAR-001'), NULL, NULL, '2024-11-12 14:30:00', 'petrol', 38.00, 165.00, 6270.00, 37800.00, 580.00, 15.26, 'Total Westlands', 'Westlands, Nairobi', 'REC-2024-011', 'card', true, 1);

-- ==================== FLEET EXPENSES ====================
INSERT INTO fleet_expenses (org_id, vehicle_id, expense_date, expense_type, description, amount, trip_id, vendor, receipt_number, payment_method, paid_by, status, created_by) VALUES
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-001'), '2024-11-18', 'toll', 'Nairobi-Mombasa highway toll', 500.00, (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241118-0001'), 'Kenya Highways Authority', 'TOLL-2024-001', 'cash', 1, 'paid', 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-004'), '2024-11-20', 'toll', 'Nairobi-Kisumu highway toll', 300.00, (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241120-0004'), 'Kenya Highways Authority', 'TOLL-2024-002', 'cash', 4, 'paid', 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-001'), '2024-11-20', 'parking', 'Thika parking fee', 50.00, (SELECT id FROM fleet_trips WHERE trip_number = 'TRIP-20241120-0003'), 'Thika Municipality', 'PARK-2024-001', 'cash', 5, 'paid', 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-002'), '2024-11-10', 'cleaning', 'Vehicle wash and interior cleaning', 1500.00, NULL, 'AutoClean Services', 'CLN-2024-001', 'cash', NULL, 'paid', 1),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'CAR-001'), '2024-11-01', 'insurance', 'Annual insurance premium', 18000.00, NULL, 'Jubilee Insurance', 'POL-2024-007', 'bank-transfer', NULL, 'paid', 1);

-- ==================== FLEET ALERTS ====================
INSERT INTO fleet_alerts (org_id, vehicle_id, driver_id, alert_type, alert_level, alert_message, alert_timestamp, location_lat, location_lng, acknowledged, metadata) VALUES
(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-003'), 3, 'overspeeding', 'warning', 'Vehicle TRK-003 exceeded speed limit: 105 km/h in 80 km/h zone', '2024-11-18 12:45:00', -4.043477, 39.668207, true, '{"speed": 105, "limit": 80}'),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-001'), 1, 'maintenance-due', 'warning', 'TRK-001 maintenance due in 2000 km', '2024-11-20 08:00:00', -1.286389, 36.817223, true, '{"current_mileage": 85000, "service_mileage": 87000}'),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'CAR-001'), NULL, 'insurance-expiry', 'critical', 'CAR-001 insurance expiring in 25 days', '2024-11-15 09:00:00', -1.286389, 36.817223, true, '{"expiry_date": "2025-05-09", "days_remaining": 25}'),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'VAN-002'), 6, 'harsh-brake', 'info', 'Harsh braking detected for VAN-002', '2024-11-19 15:30:00', -1.200000, 36.825000, false, '{"speed_before": 65, "speed_after": 15}'),

(1, (SELECT id FROM fleet_vehicles WHERE vehicle_number = 'TRK-004'), 4, 'idle', 'info', 'TRK-004 idling for 15 minutes', '2024-11-20 14:20:00', -0.091702, 34.767956, true, '{"idle_minutes": 15}');

COMMENT ON TABLE fleet_vehicles IS 'Fleet vehicles demo data - 10 vehicles including trucks, vans, cars, pickup, and motorcycle';
COMMENT ON TABLE fleet_drivers IS 'Fleet drivers demo data - 6 active drivers with assignments';
COMMENT ON TABLE fleet_trips IS 'Fleet trips demo data - 4 completed trips and 2 in-progress trips';
COMMENT ON TABLE fleet_gps_tracking IS 'GPS tracking demo data - Current positions and historical route for in-progress trip';
COMMENT ON TABLE fleet_maintenance IS 'Maintenance records demo data - 4 completed and 3 scheduled services';
COMMENT ON TABLE fleet_fuel_logs IS 'Fuel logs demo data - 8 refill records with efficiency calculations';
