CREATE TABLE IF NOT EXISTS fuel_vehicle_types (
    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_vehicle_types_name (name),
    UNIQUE KEY uq_fuel_vehicle_types_code (code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE vehicle_fuel_entries
    ADD COLUMN vehicle_type_setup_id BIGINT UNSIGNED NULL AFTER vehicle_id,
    ADD KEY idx_vehicle_fuel_entries_vehicle_type_setup (vehicle_type_setup_id),
    ADD CONSTRAINT fk_vehicle_fuel_entries_vehicle_type_setup
        FOREIGN KEY (vehicle_type_setup_id) REFERENCES fuel_vehicle_types(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL;

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