CREATE TABLE IF NOT EXISTS fuel_companies (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    code VARCHAR(60) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_fuel_companies_name (name),
    UNIQUE KEY uq_fuel_companies_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fuel_filling_stations (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(150) NOT NULL,
    state_id BIGINT UNSIGNED NULL,
    address VARCHAR(255) NULL,
    contact_phone VARCHAR(30) NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_fuel_filling_stations_name (name),
    KEY idx_fuel_filling_stations_state (state_id),
    CONSTRAINT fk_fuel_filling_stations_state
        FOREIGN KEY (state_id) REFERENCES nigeria_states(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS fuel_approval_users (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    UNIQUE KEY uq_fuel_approval_users_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE vehicle_fuel_entries
    ADD COLUMN IF NOT EXISTS company_id BIGINT UNSIGNED NULL AFTER office_id,
    ADD COLUMN IF NOT EXISTS filling_station_id BIGINT UNSIGNED NULL AFTER filling_station,
    ADD KEY IF NOT EXISTS idx_vehicle_fuel_entries_company (company_id),
    ADD KEY IF NOT EXISTS idx_vehicle_fuel_entries_filling_station (filling_station_id);

INSERT INTO permissions (code, description)
SELECT 'fuel.approve', 'Approve fuel requests'
WHERE NOT EXISTS (
    SELECT 1 FROM permissions WHERE code = 'fuel.approve'
);

INSERT INTO fuel_companies (name, code)
SELECT DISTINCT TRIM(owner), UPPER(LEFT(TRIM(owner), 10))
FROM vehicles
WHERE owner IS NOT NULL
  AND TRIM(owner) <> ''
ON DUPLICATE KEY UPDATE
    is_active = 1;

INSERT INTO fuel_companies (name, code)
SELECT DISTINCT TRIM(property), UPPER(LEFT(TRIM(property), 10))
FROM vehicles
WHERE property IS NOT NULL
  AND TRIM(property) <> ''
ON DUPLICATE KEY UPDATE
    is_active = 1;

INSERT INTO fuel_filling_stations (name)
SELECT DISTINCT TRIM(filling_station)
FROM vehicle_fuel_entries
WHERE filling_station IS NOT NULL
  AND TRIM(filling_station) <> ''
ON DUPLICATE KEY UPDATE
    is_active = 1;