ALTER TABLE hems_operations
    ADD COLUMN IF NOT EXISTS mission_id BINARY(16) NULL;

SET @fk_exists := (
    SELECT COUNT(1)
    FROM information_schema.table_constraints
    WHERE table_schema = DATABASE()
      AND table_name = 'hems_operations'
      AND constraint_type = 'FOREIGN KEY'
      AND constraint_name = 'fk_hems_operations_mission'
);
SET @fk_sql := IF(
    @fk_exists = 0,
    'ALTER TABLE hems_operations ADD CONSTRAINT fk_hems_operations_mission FOREIGN KEY (mission_id) REFERENCES missions(id) ON UPDATE CASCADE ON DELETE SET NULL',
    'SELECT 1'
);
PREPARE stmt_fk FROM @fk_sql;
EXECUTE stmt_fk;
DEALLOCATE PREPARE stmt_fk;

SET @idx_exists := (
    SELECT COUNT(1)
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'hems_operations'
      AND index_name = 'uidx_hems_operations_mission_id'
);
SET @idx_sql := IF(
    @idx_exists = 0,
    'ALTER TABLE hems_operations ADD UNIQUE INDEX uidx_hems_operations_mission_id (mission_id)',
    'SELECT 1'
);
PREPARE stmt_idx FROM @idx_sql;
EXECUTE stmt_idx;
DEALLOCATE PREPARE stmt_idx;