CREATE TABLE IF NOT EXISTS fuel_types (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(120) NOT NULL,
    code VARCHAR(40) NULL,
    unit_label VARCHAR(30) NOT NULL DEFAULT 'Litre',
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_fuel_types_name (name),
    UNIQUE KEY uq_fuel_types_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fuel_price_history (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    fuel_type_id BIGINT UNSIGNED NOT NULL,
    city_id BIGINT UNSIGNED NULL,
    location_id BIGINT UNSIGNED NULL,
    price_per_liter DECIMAL(12,2) NOT NULL,
    effective_date DATE NOT NULL,
    vendor_name VARCHAR(150) NULL,
    remarks TEXT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_fuel_price_history_type (fuel_type_id),
    KEY idx_fuel_price_history_city (city_id),
    KEY idx_fuel_price_history_location (location_id),
    KEY idx_fuel_price_history_effective_date (effective_date),
    CONSTRAINT fk_fuel_price_history_type
        FOREIGN KEY (fuel_type_id) REFERENCES fuel_types(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_fuel_price_history_city
        FOREIGN KEY (city_id) REFERENCES cities(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_fuel_price_history_location
        FOREIGN KEY (location_id) REFERENCES locations(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS vehicle_fuel_entries (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NULL,
    fuel_type_id BIGINT UNSIGNED NULL,
    city_id BIGINT UNSIGNED NULL,
    location_id BIGINT UNSIGNED NULL,
    price_history_id BIGINT UNSIGNED NULL,
    record_date DATE NOT NULL,
    month_year CHAR(7) NOT NULL,
    vehicle_no VARCHAR(60) NULL,
    vehicle_type_label VARCHAR(150) NULL,
    driver_name VARCHAR(200) NULL,
    fuel_purchase_liters DECIMAL(12,2) NULL,
    naira_per_liter DECIMAL(12,2) NULL,
    total_amount DECIMAL(14,2) NULL,
    begin_km INT NULL,
    end_km INT NULL,
    monthly_mileage_km INT NULL,
    distance_km INT NULL,
    total_fuel_consumption_ltr_month DECIMAL(12,2) NULL,
    baseline_difference DECIMAL(12,2) NULL,
    over_baseline_rate DECIMAL(12,2) NULL,
    notes TEXT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_vehicle_fuel_entries_vehicle (vehicle_id),
    KEY idx_vehicle_fuel_entries_driver (driver_id),
    KEY idx_vehicle_fuel_entries_type (fuel_type_id),
    KEY idx_vehicle_fuel_entries_city (city_id),
    KEY idx_vehicle_fuel_entries_location (location_id),
    KEY idx_vehicle_fuel_entries_month (month_year),
    CONSTRAINT fk_vehicle_fuel_entries_vehicle
        FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_vehicle_fuel_entries_driver
        FOREIGN KEY (driver_id) REFERENCES drivers(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_vehicle_fuel_entries_type
        FOREIGN KEY (fuel_type_id) REFERENCES fuel_types(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_vehicle_fuel_entries_city
        FOREIGN KEY (city_id) REFERENCES cities(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_vehicle_fuel_entries_location
        FOREIGN KEY (location_id) REFERENCES locations(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    CONSTRAINT fk_vehicle_fuel_entries_price
        FOREIGN KEY (price_history_id) REFERENCES fuel_price_history(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO fuel_types (name, code, unit_label)
VALUES
('Petrol', 'PMS', 'Litre'),
('Diesel', 'AGO', 'Litre'),
('Kerosene', 'DPK', 'Litre'),
('Gas', 'LPG', 'Kg')
ON DUPLICATE KEY UPDATE
    code = VALUES(code),
    unit_label = VALUES(unit_label),
    is_active = 1;