CREATE TABLE IF NOT EXISTS vehicle_assignments (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    vehicle_id BIGINT UNSIGNED NOT NULL,
    driver_id BIGINT UNSIGNED NOT NULL,
    assignment_start DATE NOT NULL,
    assignment_end DATE NULL,
    status ENUM('active','inactive','suspended') NOT NULL DEFAULT 'active',
    leased_days INT NULL,
    notes TEXT NULL,
    created_by_user_id BIGINT UNSIGNED 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_assignments_vehicle (vehicle_id),
    KEY idx_vehicle_assignments_driver (driver_id),
    KEY idx_vehicle_assignments_dates (assignment_start, assignment_end),
    KEY idx_vehicle_assignments_status (status),
    CONSTRAINT fk_vehicle_assignments_vehicle
        FOREIGN KEY (vehicle_id) REFERENCES vehicles(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_vehicle_assignments_driver
        FOREIGN KEY (driver_id) REFERENCES drivers(id)
        ON UPDATE CASCADE
        ON DELETE CASCADE,
    CONSTRAINT fk_vehicle_assignments_created_by
        FOREIGN KEY (created_by_user_id) REFERENCES users(id)
        ON UPDATE CASCADE
        ON DELETE SET NULL,
    UNIQUE KEY uk_vehicle_assignment_active (vehicle_id, driver_id, assignment_start)
        COMMENT 'Prevent duplicate active assignments for same vehicle-driver-start date'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Vehicle to driver assignments with date ranges';