CREATE TABLE IF NOT EXISTS flights (
    id BINARY(16) PRIMARY KEY,
    mission_code VARCHAR(64) NOT NULL,
    mission_type VARCHAR(32) NOT NULL,
    date DATE NOT NULL,
    duty_roster_id BINARY(16) NULL,
    reserve_ac_id BINARY(16) NULL,
    created_at DATETIME NULL,
    created_by BINARY(16) NULL,
    updated_at DATETIME NULL,
    updated_by BINARY(16) NULL,
    deleted_at DATETIME NULL,
    deleted_by BINARY(16) NULL,
    CONSTRAINT fk_flights_reserve_ac FOREIGN KEY (reserve_ac_id) REFERENCES reserve_acs(id),
    CONSTRAINT chk_flights_mission_type CHECK (mission_type IN ('hems', 'cat', 'spo', 'nco'))
);

CREATE INDEX idx_flights_mission_code ON flights(mission_code);
CREATE INDEX idx_flights_mission_type ON flights(mission_type);
CREATE INDEX idx_flights_date ON flights(date);
CREATE INDEX idx_flights_duty_roster_id ON flights(duty_roster_id);
CREATE INDEX idx_flights_reserve_ac_id ON flights(reserve_ac_id);
CREATE INDEX idx_flights_deleted_at ON flights(deleted_at);