CREATE TABLE IF NOT EXISTS service_intervals (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    vehicle_id BIGINT UNSIGNED DEFAULT NULL,
    interval_type ENUM('mileage','time','both') NOT NULL DEFAULT 'both',
    interval_km INT UNSIGNED NOT NULL DEFAULT 0,
    interval_days INT UNSIGNED NOT NULL DEFAULT 0,
    last_service_date DATE DEFAULT NULL,
    last_service_mileage INT UNSIGNED DEFAULT 0,
    due_date DATE DEFAULT NULL,
    due_mileage INT UNSIGNED DEFAULT 0,
    task_description TEXT,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL DEFAULT NULL,
    FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

ALTER TABLE vehicles
    ADD COLUMN current_odometer INT UNSIGNED DEFAULT 0 AFTER exit_date,
    ADD COLUMN last_service_date DATE DEFAULT NULL AFTER current_odometer,
    ADD COLUMN last_service_odometer INT UNSIGNED DEFAULT 0 AFTER last_service_date;