-- Fleet Management Module
-- Comprehensive vehicle fleet management with GPS tracking, maintenance, registration, and fuel monitoring

-- Table 1: fleet_vehicles
-- Core vehicle information and registration details
CREATE TABLE IF NOT EXISTS fleet_vehicles (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    vehicle_number VARCHAR(50) NOT NULL UNIQUE,
    registration_number VARCHAR(50) NOT NULL UNIQUE,
    make VARCHAR(100) NOT NULL,
    model VARCHAR(100) NOT NULL,
    year INTEGER NOT NULL,
    vehicle_type VARCHAR(50) NOT NULL, -- truck, van, car, motorcycle, trailer
    fuel_type VARCHAR(50) NOT NULL, -- petrol, diesel, electric, hybrid
    capacity_kg DECIMAL(10, 2),
    capacity_volume DECIMAL(10, 2),
    seating_capacity INTEGER,
    vin VARCHAR(100) UNIQUE, -- Vehicle Identification Number
    engine_number VARCHAR(100),
    color VARCHAR(50),
    purchase_date DATE,
    purchase_price DECIMAL(12, 2),
    current_value DECIMAL(12, 2),
    insurance_company VARCHAR(200),
    insurance_policy_number VARCHAR(100),
    insurance_expiry_date DATE,
    registration_expiry_date DATE,
    fitness_certificate_expiry DATE,
    pollution_certificate_expiry DATE,
    permit_expiry_date DATE,
    current_mileage DECIMAL(10, 2) DEFAULT 0,
    fuel_tank_capacity DECIMAL(10, 2),
    fuel_efficiency DECIMAL(10, 2), -- km per liter
    gps_device_id VARCHAR(100),
    gps_device_status VARCHAR(50) DEFAULT 'active', -- active, inactive, faulty
    assigned_driver_id INTEGER,
    assigned_location_id INTEGER,
    home_location_lat DECIMAL(10, 6),
    home_location_lng DECIMAL(10, 6),
    status VARCHAR(50) NOT NULL DEFAULT 'active', -- active, inactive, maintenance, sold, written-off
    notes TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER,
    updated_by INTEGER
);

CREATE INDEX idx_fleet_vehicles_org ON fleet_vehicles(org_id);
CREATE INDEX idx_fleet_vehicles_status ON fleet_vehicles(status);
CREATE INDEX idx_fleet_vehicles_type ON fleet_vehicles(vehicle_type);
CREATE INDEX idx_fleet_vehicles_driver ON fleet_vehicles(assigned_driver_id);
CREATE INDEX idx_fleet_vehicles_location ON fleet_vehicles(assigned_location_id);
CREATE INDEX idx_fleet_vehicles_gps_device ON fleet_vehicles(gps_device_id);
CREATE INDEX idx_fleet_vehicles_registration_expiry ON fleet_vehicles(registration_expiry_date);

COMMENT ON TABLE fleet_vehicles IS 'Fleet vehicles master data with registration and insurance information';
COMMENT ON COLUMN fleet_vehicles.gps_device_id IS 'GPS tracker device identifier for real-time tracking';
COMMENT ON COLUMN fleet_vehicles.fuel_efficiency IS 'Average fuel efficiency in km/liter or km/kWh';

-- Table 2: fleet_drivers
-- Driver information and assignment management
CREATE TABLE IF NOT EXISTS fleet_drivers (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    user_id INTEGER, -- Link to users table if driver is an employee
    driver_code VARCHAR(50) NOT NULL UNIQUE,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    phone VARCHAR(20),
    email VARCHAR(100),
    license_number VARCHAR(100) NOT NULL UNIQUE,
    license_type VARCHAR(50), -- LMV, HMV, transport, etc.
    license_expiry_date DATE,
    date_of_birth DATE,
    address TEXT,
    emergency_contact_name VARCHAR(200),
    emergency_contact_phone VARCHAR(20),
    hire_date DATE,
    employment_status VARCHAR(50) DEFAULT 'active', -- active, on-leave, suspended, terminated
    experience_years INTEGER,
    assigned_vehicle_id INTEGER,
    rating DECIMAL(3, 2), -- Driver rating 0.00 to 5.00
    total_trips INTEGER DEFAULT 0,
    total_distance_km DECIMAL(10, 2) DEFAULT 0,
    violations_count INTEGER DEFAULT 0,
    status VARCHAR(50) NOT NULL DEFAULT 'available', -- available, on-trip, off-duty, on-leave
    notes TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER,
    updated_by INTEGER,
    FOREIGN KEY (assigned_vehicle_id) REFERENCES fleet_vehicles(id) ON DELETE SET NULL
);

CREATE INDEX idx_fleet_drivers_org ON fleet_drivers(org_id);
CREATE INDEX idx_fleet_drivers_status ON fleet_drivers(status);
CREATE INDEX idx_fleet_drivers_user ON fleet_drivers(user_id);
CREATE INDEX idx_fleet_drivers_license_expiry ON fleet_drivers(license_expiry_date);

COMMENT ON TABLE fleet_drivers IS 'Fleet drivers with license and assignment information';

-- Table 3: fleet_maintenance
-- Vehicle maintenance schedules and records
CREATE TABLE IF NOT EXISTS fleet_maintenance (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    vehicle_id INTEGER NOT NULL,
    maintenance_number VARCHAR(50) NOT NULL UNIQUE,
    maintenance_type VARCHAR(50) NOT NULL, -- scheduled, breakdown, accident, inspection
    service_type VARCHAR(100), -- oil-change, tire-rotation, brake-service, engine-repair, etc.
    scheduled_date DATE,
    actual_date DATE,
    mileage_at_service DECIMAL(10, 2),
    service_provider VARCHAR(200),
    service_location VARCHAR(200),
    technician_name VARCHAR(100),
    description TEXT,
    parts_replaced TEXT,
    parts_cost DECIMAL(10, 2) DEFAULT 0,
    labor_cost DECIMAL(10, 2) DEFAULT 0,
    total_cost DECIMAL(10, 2) DEFAULT 0,
    invoice_number VARCHAR(100),
    warranty_until DATE,
    next_service_date DATE,
    next_service_mileage DECIMAL(10, 2),
    priority VARCHAR(50) DEFAULT 'normal', -- low, normal, high, critical
    status VARCHAR(50) NOT NULL DEFAULT 'scheduled', -- scheduled, in-progress, completed, cancelled
    downtime_hours DECIMAL(10, 2),
    performed_by INTEGER, -- User ID
    notes TEXT,
    attachments JSONB, -- Invoice, photos, reports
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER,
    updated_by INTEGER,
    FOREIGN KEY (vehicle_id) REFERENCES fleet_vehicles(id) ON DELETE CASCADE
);

CREATE INDEX idx_fleet_maintenance_org ON fleet_maintenance(org_id);
CREATE INDEX idx_fleet_maintenance_vehicle ON fleet_maintenance(vehicle_id);
CREATE INDEX idx_fleet_maintenance_status ON fleet_maintenance(status);
CREATE INDEX idx_fleet_maintenance_type ON fleet_maintenance(maintenance_type);
CREATE INDEX idx_fleet_maintenance_scheduled_date ON fleet_maintenance(scheduled_date);
CREATE INDEX idx_fleet_maintenance_next_service ON fleet_maintenance(next_service_date);

COMMENT ON TABLE fleet_maintenance IS 'Vehicle maintenance schedules, service records, and cost tracking';
COMMENT ON COLUMN fleet_maintenance.downtime_hours IS 'Vehicle downtime during maintenance';

-- Table 4: fleet_gps_tracking
-- Real-time and historical GPS tracking data
CREATE TABLE IF NOT EXISTS fleet_gps_tracking (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    vehicle_id INTEGER NOT NULL,
    gps_device_id VARCHAR(100) NOT NULL,
    timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
    latitude DECIMAL(10, 6) NOT NULL,
    longitude DECIMAL(10, 6) NOT NULL,
    altitude DECIMAL(10, 2),
    speed_kmh DECIMAL(10, 2),
    heading DECIMAL(5, 2), -- 0-360 degrees
    gps_status VARCHAR(50) DEFAULT 'active', -- active, weak-signal, no-signal
    ignition_status BOOLEAN DEFAULT false,
    fuel_level_percent DECIMAL(5, 2),
    engine_temperature DECIMAL(5, 2),
    battery_voltage DECIMAL(5, 2),
    trip_id INTEGER,
    driver_id INTEGER,
    location_name VARCHAR(200), -- Reverse geocoded address
    geofence_id INTEGER, -- If inside a geofence
    alert_type VARCHAR(50), -- overspeeding, harsh-brake, idle, geofence-exit, etc.
    raw_data JSONB, -- Raw GPS device data
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (vehicle_id) REFERENCES fleet_vehicles(id) ON DELETE CASCADE,
    FOREIGN KEY (driver_id) REFERENCES fleet_drivers(id) ON DELETE SET NULL
);

CREATE INDEX idx_fleet_gps_org ON fleet_gps_tracking(org_id);
CREATE INDEX idx_fleet_gps_vehicle ON fleet_gps_tracking(vehicle_id);
CREATE INDEX idx_fleet_gps_timestamp ON fleet_gps_tracking(timestamp DESC);
CREATE INDEX idx_fleet_gps_device ON fleet_gps_tracking(gps_device_id);
CREATE INDEX idx_fleet_gps_trip ON fleet_gps_tracking(trip_id);
CREATE INDEX idx_fleet_gps_location ON fleet_gps_tracking(latitude, longitude);
CREATE INDEX idx_fleet_gps_alert ON fleet_gps_tracking(alert_type) WHERE alert_type IS NOT NULL;

COMMENT ON TABLE fleet_gps_tracking IS 'Real-time GPS tracking data with speed, location, and alerts';
COMMENT ON COLUMN fleet_gps_tracking.heading IS 'Direction of travel in degrees (0=North, 90=East, 180=South, 270=West)';

-- Table 5: fleet_trips
-- Trip management and route tracking
CREATE TABLE IF NOT EXISTS fleet_trips (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    trip_number VARCHAR(50) NOT NULL UNIQUE,
    vehicle_id INTEGER NOT NULL,
    driver_id INTEGER NOT NULL,
    trip_type VARCHAR(50) DEFAULT 'delivery', -- delivery, pickup, transfer, service, personal
    start_location VARCHAR(200),
    start_lat DECIMAL(10, 6),
    start_lng DECIMAL(10, 6),
    end_location VARCHAR(200),
    end_lat DECIMAL(10, 6),
    end_lng DECIMAL(10, 6),
    planned_start_time TIMESTAMP,
    actual_start_time TIMESTAMP,
    planned_end_time TIMESTAMP,
    actual_end_time TIMESTAMP,
    start_mileage DECIMAL(10, 2),
    end_mileage DECIMAL(10, 2),
    distance_km DECIMAL(10, 2),
    duration_minutes INTEGER,
    fuel_consumed_liters DECIMAL(10, 2),
    fuel_cost DECIMAL(10, 2),
    toll_charges DECIMAL(10, 2),
    other_expenses DECIMAL(10, 2),
    total_cost DECIMAL(10, 2),
    cargo_weight_kg DECIMAL(10, 2),
    cargo_description TEXT,
    customer_id INTEGER,
    sales_order_id INTEGER,
    delivery_note_number VARCHAR(100),
    route_taken JSONB, -- Array of lat/lng points
    stops JSONB, -- Array of stop locations with timestamps
    max_speed_kmh DECIMAL(10, 2),
    avg_speed_kmh DECIMAL(10, 2),
    idle_time_minutes INTEGER,
    harsh_braking_count INTEGER,
    overspeeding_count INTEGER,
    status VARCHAR(50) NOT NULL DEFAULT 'planned', -- planned, in-progress, completed, cancelled
    completion_notes TEXT,
    customer_signature TEXT, -- Base64 encoded signature
    delivery_proof JSONB, -- Photos, documents
    rating DECIMAL(3, 2), -- Trip rating by customer
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER,
    updated_by INTEGER,
    FOREIGN KEY (vehicle_id) REFERENCES fleet_vehicles(id) ON DELETE CASCADE,
    FOREIGN KEY (driver_id) REFERENCES fleet_drivers(id) ON DELETE CASCADE
);

CREATE INDEX idx_fleet_trips_org ON fleet_trips(org_id);
CREATE INDEX idx_fleet_trips_vehicle ON fleet_trips(vehicle_id);
CREATE INDEX idx_fleet_trips_driver ON fleet_trips(driver_id);
CREATE INDEX idx_fleet_trips_status ON fleet_trips(status);
CREATE INDEX idx_fleet_trips_start_time ON fleet_trips(actual_start_time);
CREATE INDEX idx_fleet_trips_customer ON fleet_trips(customer_id);

COMMENT ON TABLE fleet_trips IS 'Trip records with route tracking, fuel consumption, and performance metrics';
COMMENT ON COLUMN fleet_trips.route_taken IS 'GPS coordinates array showing actual route traveled';

-- Table 6: fleet_fuel_logs
-- Fuel consumption and refueling records
CREATE TABLE IF NOT EXISTS fleet_fuel_logs (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    vehicle_id INTEGER NOT NULL,
    driver_id INTEGER,
    trip_id INTEGER,
    log_date TIMESTAMP NOT NULL DEFAULT NOW(),
    fuel_type VARCHAR(50) NOT NULL,
    quantity_liters DECIMAL(10, 2) NOT NULL,
    price_per_liter DECIMAL(10, 2),
    total_cost DECIMAL(10, 2),
    mileage_at_fill DECIMAL(10, 2),
    distance_since_last_fill DECIMAL(10, 2),
    fuel_efficiency DECIMAL(10, 2), -- Calculated km/liter
    fuel_station VARCHAR(200),
    fuel_station_location VARCHAR(200),
    receipt_number VARCHAR(100),
    payment_method VARCHAR(50), -- cash, card, fuel-card, credit
    is_full_tank BOOLEAN DEFAULT true,
    notes TEXT,
    attachments JSONB, -- Receipt photos
    created_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER,
    FOREIGN KEY (vehicle_id) REFERENCES fleet_vehicles(id) ON DELETE CASCADE,
    FOREIGN KEY (driver_id) REFERENCES fleet_drivers(id) ON DELETE SET NULL,
    FOREIGN KEY (trip_id) REFERENCES fleet_trips(id) ON DELETE SET NULL
);

CREATE INDEX idx_fleet_fuel_org ON fleet_fuel_logs(org_id);
CREATE INDEX idx_fleet_fuel_vehicle ON fleet_fuel_logs(vehicle_id);
CREATE INDEX idx_fleet_fuel_driver ON fleet_fuel_logs(driver_id);
CREATE INDEX idx_fleet_fuel_trip ON fleet_fuel_logs(trip_id);
CREATE INDEX idx_fleet_fuel_date ON fleet_fuel_logs(log_date DESC);

COMMENT ON TABLE fleet_fuel_logs IS 'Fuel refill records with cost and efficiency tracking';
COMMENT ON COLUMN fleet_fuel_logs.fuel_efficiency IS 'Calculated from distance_since_last_fill / quantity_liters';

-- Table 7: fleet_expenses
-- Other vehicle-related expenses
CREATE TABLE IF NOT EXISTS fleet_expenses (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    vehicle_id INTEGER NOT NULL,
    expense_date DATE NOT NULL,
    expense_type VARCHAR(50) NOT NULL, -- toll, parking, fine, insurance, tax, permit, cleaning, misc
    description TEXT,
    amount DECIMAL(10, 2) NOT NULL,
    trip_id INTEGER,
    vendor VARCHAR(200),
    receipt_number VARCHAR(100),
    payment_method VARCHAR(50),
    paid_by INTEGER, -- User ID or driver ID
    status VARCHAR(50) DEFAULT 'pending', -- pending, approved, paid, rejected
    notes TEXT,
    attachments JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER,
    FOREIGN KEY (vehicle_id) REFERENCES fleet_vehicles(id) ON DELETE CASCADE,
    FOREIGN KEY (trip_id) REFERENCES fleet_trips(id) ON DELETE SET NULL
);

CREATE INDEX idx_fleet_expenses_org ON fleet_expenses(org_id);
CREATE INDEX idx_fleet_expenses_vehicle ON fleet_expenses(vehicle_id);
CREATE INDEX idx_fleet_expenses_type ON fleet_expenses(expense_type);
CREATE INDEX idx_fleet_expenses_date ON fleet_expenses(expense_date DESC);

COMMENT ON TABLE fleet_expenses IS 'Vehicle-related expenses tracking';

-- Table 8: fleet_geofences
-- Geofence zones for monitoring vehicle entry/exit
CREATE TABLE IF NOT EXISTS fleet_geofences (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    geofence_type VARCHAR(50) DEFAULT 'circular', -- circular, polygon
    center_lat DECIMAL(10, 6),
    center_lng DECIMAL(10, 6),
    radius_meters INTEGER, -- For circular geofences
    polygon_points JSONB, -- Array of lat/lng for polygon geofences
    alert_on_entry BOOLEAN DEFAULT false,
    alert_on_exit BOOLEAN DEFAULT false,
    alert_users JSONB, -- Array of user IDs to notify
    active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT NOW(),
    created_by INTEGER
);

CREATE INDEX idx_fleet_geofences_org ON fleet_geofences(org_id);
CREATE INDEX idx_fleet_geofences_active ON fleet_geofences(active);

COMMENT ON TABLE fleet_geofences IS 'Geofence zones for vehicle tracking alerts';

-- Table 9: fleet_alerts
-- Alert history for fleet events
CREATE TABLE IF NOT EXISTS fleet_alerts (
    id SERIAL PRIMARY KEY,
    org_id INTEGER NOT NULL,
    vehicle_id INTEGER,
    driver_id INTEGER,
    alert_type VARCHAR(50) NOT NULL, -- overspeeding, geofence-exit, maintenance-due, license-expiry, harsh-brake, idle, accident
    alert_level VARCHAR(50) DEFAULT 'info', -- info, warning, critical
    alert_message TEXT NOT NULL,
    alert_timestamp TIMESTAMP DEFAULT NOW(),
    location_lat DECIMAL(10, 6),
    location_lng DECIMAL(10, 6),
    gps_tracking_id INTEGER,
    trip_id INTEGER,
    acknowledged BOOLEAN DEFAULT false,
    acknowledged_by INTEGER,
    acknowledged_at TIMESTAMP,
    action_taken TEXT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    FOREIGN KEY (vehicle_id) REFERENCES fleet_vehicles(id) ON DELETE CASCADE,
    FOREIGN KEY (driver_id) REFERENCES fleet_drivers(id) ON DELETE SET NULL,
    FOREIGN KEY (gps_tracking_id) REFERENCES fleet_gps_tracking(id) ON DELETE SET NULL,
    FOREIGN KEY (trip_id) REFERENCES fleet_trips(id) ON DELETE SET NULL
);

CREATE INDEX idx_fleet_alerts_org ON fleet_alerts(org_id);
CREATE INDEX idx_fleet_alerts_vehicle ON fleet_alerts(vehicle_id);
CREATE INDEX idx_fleet_alerts_type ON fleet_alerts(alert_type);
CREATE INDEX idx_fleet_alerts_timestamp ON fleet_alerts(alert_timestamp DESC);
CREATE INDEX idx_fleet_alerts_acknowledged ON fleet_alerts(acknowledged);

COMMENT ON TABLE fleet_alerts IS 'Fleet monitoring alerts and notifications';

-- Add comments
COMMENT ON DATABASE current_database() IS 'TAF ERP with Fleet Management Module';
