CREATE TABLE IF NOT EXISTS reserve_acs (
    id BINARY(16) PRIMARY KEY,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    helicopter_id BINARY(16) NOT NULL,
    inspection_id BINARY(16) 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 chk_reserve_ac_status CHECK (status IN ('pending', 'approved', 'onprogress')),
    CONSTRAINT fk_reserve_acs_helicopter FOREIGN KEY (helicopter_id) REFERENCES helicopters(id),
    CONSTRAINT fk_reserve_acs_inspection FOREIGN KEY (inspection_id) REFERENCES flight_inspections(id)
);

CREATE INDEX idx_reserve_acs_helicopter_id ON reserve_acs(helicopter_id);
CREATE INDEX idx_reserve_acs_inspection_id ON reserve_acs(inspection_id);
CREATE INDEX idx_reserve_acs_deleted_at ON reserve_acs(deleted_at);

ALTER TABLE flight_inspections DROP FOREIGN KEY fk_flight_inspections_helicopter;
DROP INDEX idx_flight_inspections_helicopter_id ON flight_inspections;
DROP INDEX idx_flight_inspections_operation_id ON flight_inspections;
DROP INDEX idx_flight_inspections_helicopter_date ON flight_inspections;
ALTER TABLE flight_inspections DROP COLUMN helicopter_id;
ALTER TABLE flight_inspections DROP COLUMN operation_id;