-- +goose Up
-- Align schema for single-request takeover create flow.
-- Only apply additive changes when columns/constraints are missing.

SET @schema_name := DATABASE();

-- reserve_acs.base_id
SELECT COUNT(*) INTO @reserve_acs_has_base_id
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'reserve_acs'
  AND COLUMN_NAME = 'base_id';

SET @add_reserve_acs_base_id_sql := IF(
  @reserve_acs_has_base_id = 0,
  'ALTER TABLE reserve_acs ADD COLUMN base_id BINARY(16) NULL AFTER status',
  'SELECT 1'
);
PREPARE stmt_add_reserve_acs_base_id FROM @add_reserve_acs_base_id_sql;
EXECUTE stmt_add_reserve_acs_base_id;
DEALLOCATE PREPARE stmt_add_reserve_acs_base_id;

-- Index reserve_acs.base_id
SELECT COUNT(*) INTO @reserve_acs_has_base_id_idx
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'reserve_acs'
  AND INDEX_NAME = 'idx_reserve_acs_base_id';

SET @add_reserve_acs_base_id_idx_sql := IF(
  @reserve_acs_has_base_id_idx = 0,
  'CREATE INDEX idx_reserve_acs_base_id ON reserve_acs(base_id)',
  'SELECT 1'
);
PREPARE stmt_add_reserve_acs_base_id_idx FROM @add_reserve_acs_base_id_idx_sql;
EXECUTE stmt_add_reserve_acs_base_id_idx;
DEALLOCATE PREPARE stmt_add_reserve_acs_base_id_idx;

-- FK reserve_acs.base_id -> bases.id
SELECT COUNT(*) INTO @reserve_acs_has_base_fk
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = @schema_name
  AND TABLE_NAME = 'reserve_acs'
  AND CONSTRAINT_NAME = 'fk_reserve_acs_base'
  AND CONSTRAINT_TYPE = 'FOREIGN KEY';

SET @add_reserve_acs_base_fk_sql := IF(
  @reserve_acs_has_base_fk = 0,
  'ALTER TABLE reserve_acs ADD CONSTRAINT fk_reserve_acs_base FOREIGN KEY (base_id) REFERENCES bases(id)',
  'SELECT 1'
);
PREPARE stmt_add_reserve_acs_base_fk FROM @add_reserve_acs_base_fk_sql;
EXECUTE stmt_add_reserve_acs_base_fk;
DEALLOCATE PREPARE stmt_add_reserve_acs_base_fk;

-- Optional backfill reserve_acs.base_id from linked flight -> duty_roster.
-- Safe to run repeatedly.
UPDATE reserve_acs ra
JOIN flights f ON f.reserve_ac_id = ra.id AND f.deleted_at IS NULL
JOIN duty_rosters dr ON dr.id = f.duty_roster_id AND dr.deleted_at IS NULL
SET ra.base_id = dr.base_id
WHERE ra.base_id IS NULL
  AND ra.deleted_at IS NULL;

-- +goose Down
-- Keep data; only drop FK/index/column if present.

SET @schema_name := DATABASE();

SELECT COUNT(*) INTO @reserve_acs_has_base_fk
FROM information_schema.TABLE_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = @schema_name
  AND TABLE_NAME = 'reserve_acs'
  AND CONSTRAINT_NAME = 'fk_reserve_acs_base'
  AND CONSTRAINT_TYPE = 'FOREIGN KEY';

SET @drop_reserve_acs_base_fk_sql := IF(
  @reserve_acs_has_base_fk = 1,
  'ALTER TABLE reserve_acs DROP FOREIGN KEY fk_reserve_acs_base',
  'SELECT 1'
);
PREPARE stmt_drop_reserve_acs_base_fk FROM @drop_reserve_acs_base_fk_sql;
EXECUTE stmt_drop_reserve_acs_base_fk;
DEALLOCATE PREPARE stmt_drop_reserve_acs_base_fk;

SELECT COUNT(*) INTO @reserve_acs_has_base_id_idx
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'reserve_acs'
  AND INDEX_NAME = 'idx_reserve_acs_base_id';

SET @drop_reserve_acs_base_id_idx_sql := IF(
  @reserve_acs_has_base_id_idx = 1,
  'DROP INDEX idx_reserve_acs_base_id ON reserve_acs',
  'SELECT 1'
);
PREPARE stmt_drop_reserve_acs_base_id_idx FROM @drop_reserve_acs_base_id_idx_sql;
EXECUTE stmt_drop_reserve_acs_base_id_idx;
DEALLOCATE PREPARE stmt_drop_reserve_acs_base_id_idx;

SELECT COUNT(*) INTO @reserve_acs_has_base_id
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'reserve_acs'
  AND COLUMN_NAME = 'base_id';

SET @drop_reserve_acs_base_id_sql := IF(
  @reserve_acs_has_base_id = 1,
  'ALTER TABLE reserve_acs DROP COLUMN base_id',
  'SELECT 1'
);
PREPARE stmt_drop_reserve_acs_base_id FROM @drop_reserve_acs_base_id_sql;
EXECUTE stmt_drop_reserve_acs_base_id;
DEALLOCATE PREPARE stmt_drop_reserve_acs_base_id;