CREATE TABLE IF NOT EXISTS missions (
    id BINARY(16) PRIMARY KEY,
    flight_id BINARY(16) NOT NULL,
    type VARCHAR(20) NOT 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_missions_flight FOREIGN KEY (flight_id) REFERENCES flights(id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT chk_missions_type CHECK (type IN ('CAT', 'SPO', 'NCO', 'HEMS'))
);

CREATE UNIQUE INDEX uidx_missions_flight_id ON missions(flight_id);
CREATE INDEX idx_missions_type ON missions(type);
CREATE INDEX idx_missions_deleted_at ON missions(deleted_at);