fm_be/migrations/20260609_takeover_roster_rows.sql

-- +goose Up
-- Expand takeover roster child tables so takeover can store roster_detail without a duty_roster header.

SET @schema_name := DATABASE();

SELECT COUNT(*) INTO @has_takeover_roster_crews
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews';

SELECT COUNT(*) INTO @has_col_role_code
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'role_code';

SET @sql_add_role_code := IF(@has_takeover_roster_crews = 1 AND @has_col_role_code = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN role_code VARCHAR(64) NOT NULL DEFAULT ''pilot'' AFTER confirm_at',
  'SELECT 1'
);
PREPARE stmt_add_role_code FROM @sql_add_role_code;
EXECUTE stmt_add_role_code;
DEALLOCATE PREPARE stmt_add_role_code;

SELECT COUNT(*) INTO @has_col_crew_type
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'crew_type';

SET @sql_add_crew_type := IF(@has_takeover_roster_crews = 1 AND @has_col_crew_type = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN crew_type VARCHAR(32) NOT NULL DEFAULT ''main'' AFTER role_code',
  'SELECT 1'
);
PREPARE stmt_add_crew_type FROM @sql_add_crew_type;
EXECUTE stmt_add_crew_type;
DEALLOCATE PREPARE stmt_add_crew_type;

SELECT COUNT(*) INTO @has_col_user_id
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'user_id';

SET @sql_add_user_id := IF(@has_takeover_roster_crews = 1 AND @has_col_user_id = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN user_id BINARY(16) NULL AFTER crew_type',
  'SELECT 1'
);
PREPARE stmt_add_user_id FROM @sql_add_user_id;
EXECUTE stmt_add_user_id;
DEALLOCATE PREPARE stmt_add_user_id;

SELECT COUNT(*) INTO @has_col_name_label
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'name_label';

SET @sql_add_name_label := IF(@has_takeover_roster_crews = 1 AND @has_col_name_label = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN name_label VARCHAR(191) NULL AFTER user_id',
  'SELECT 1'
);
PREPARE stmt_add_name_label FROM @sql_add_name_label;
EXECUTE stmt_add_name_label;
DEALLOCATE PREPARE stmt_add_name_label;

SELECT COUNT(*) INTO @has_col_mobile_phone
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'mobile_phone';

SET @sql_add_mobile_phone := IF(@has_takeover_roster_crews = 1 AND @has_col_mobile_phone = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN mobile_phone VARCHAR(64) NULL AFTER name_label',
  'SELECT 1'
);
PREPARE stmt_add_mobile_phone FROM @sql_add_mobile_phone;
EXECUTE stmt_add_mobile_phone;
DEALLOCATE PREPARE stmt_add_mobile_phone;

SELECT COUNT(*) INTO @has_col_email
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'email';

SET @sql_add_email := IF(@has_takeover_roster_crews = 1 AND @has_col_email = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN email VARCHAR(191) NULL AFTER mobile_phone',
  'SELECT 1'
);
PREPARE stmt_add_email FROM @sql_add_email;
EXECUTE stmt_add_email;
DEALLOCATE PREPARE stmt_add_email;

SELECT COUNT(*) INTO @has_col_date_start
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'date_start';

SET @sql_add_date_start := IF(@has_takeover_roster_crews = 1 AND @has_col_date_start = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN date_start DATE NULL AFTER email',
  'SELECT 1'
);
PREPARE stmt_add_date_start FROM @sql_add_date_start;
EXECUTE stmt_add_date_start;
DEALLOCATE PREPARE stmt_add_date_start;

SELECT COUNT(*) INTO @has_col_date_end
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'date_end';

SET @sql_add_date_end := IF(@has_takeover_roster_crews = 1 AND @has_col_date_end = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN date_end DATE NULL AFTER date_start',
  'SELECT 1'
);
PREPARE stmt_add_date_end FROM @sql_add_date_end;
EXECUTE stmt_add_date_end;
DEALLOCATE PREPARE stmt_add_date_end;

SELECT COUNT(*) INTO @has_col_shift_start
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'shift_start';

SET @sql_add_shift_start := IF(@has_takeover_roster_crews = 1 AND @has_col_shift_start = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN shift_start TIME NULL AFTER date_end',
  'SELECT 1'
);
PREPARE stmt_add_shift_start FROM @sql_add_shift_start;
EXECUTE stmt_add_shift_start;
DEALLOCATE PREPARE stmt_add_shift_start;

SELECT COUNT(*) INTO @has_col_shift_end
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'shift_end';

SET @sql_add_shift_end := IF(@has_takeover_roster_crews = 1 AND @has_col_shift_end = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN shift_end TIME NULL AFTER shift_start',
  'SELECT 1'
);
PREPARE stmt_add_shift_end FROM @sql_add_shift_end;
EXECUTE stmt_add_shift_end;
DEALLOCATE PREPARE stmt_add_shift_end;

SELECT COUNT(*) INTO @has_col_flight_instructor
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'flight_instructor';

SET @sql_add_flight_instructor := IF(@has_takeover_roster_crews = 1 AND @has_col_flight_instructor = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN flight_instructor TINYINT(1) NOT NULL DEFAULT 0 AFTER shift_end',
  'SELECT 1'
);
PREPARE stmt_add_flight_instructor FROM @sql_add_flight_instructor;
EXECUTE stmt_add_flight_instructor;
DEALLOCATE PREPARE stmt_add_flight_instructor;

SELECT COUNT(*) INTO @has_col_line_checker
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'line_checker';

SET @sql_add_line_checker := IF(@has_takeover_roster_crews = 1 AND @has_col_line_checker = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN line_checker TINYINT(1) NOT NULL DEFAULT 0 AFTER flight_instructor',
  'SELECT 1'
);
PREPARE stmt_add_line_checker FROM @sql_add_line_checker;
EXECUTE stmt_add_line_checker;
DEALLOCATE PREPARE stmt_add_line_checker;

SELECT COUNT(*) INTO @has_col_supervisor
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'supervisor';

SET @sql_add_supervisor := IF(@has_takeover_roster_crews = 1 AND @has_col_supervisor = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN supervisor TINYINT(1) NOT NULL DEFAULT 0 AFTER line_checker',
  'SELECT 1'
);
PREPARE stmt_add_supervisor FROM @sql_add_supervisor;
EXECUTE stmt_add_supervisor;
DEALLOCATE PREPARE stmt_add_supervisor;

SELECT COUNT(*) INTO @has_col_examiner
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'examiner';

SET @sql_add_examiner := IF(@has_takeover_roster_crews = 1 AND @has_col_examiner = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN examiner TINYINT(1) NOT NULL DEFAULT 0 AFTER supervisor',
  'SELECT 1'
);
PREPARE stmt_add_examiner FROM @sql_add_examiner;
EXECUTE stmt_add_examiner;
DEALLOCATE PREPARE stmt_add_examiner;

SELECT COUNT(*) INTO @has_col_co_pilot
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND COLUMN_NAME = 'co_pilot';

SET @sql_add_co_pilot := IF(@has_takeover_roster_crews = 1 AND @has_col_co_pilot = 0,
  'ALTER TABLE takeover_roster_crews ADD COLUMN co_pilot TINYINT(1) NOT NULL DEFAULT 0 AFTER examiner',
  'SELECT 1'
);
PREPARE stmt_add_co_pilot FROM @sql_add_co_pilot;
EXECUTE stmt_add_co_pilot;
DEALLOCATE PREPARE stmt_add_co_pilot;

-- Keep the takeover child lookup fast and idempotent.
SELECT COUNT(*) INTO @has_takeover_roster_unique
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND INDEX_NAME = 'idx_takeover_roster_crews_unique';

SET @sql_add_takeover_roster_unique := IF(@has_takeover_roster_crews = 1 AND @has_takeover_roster_unique = 0,
  'CREATE UNIQUE INDEX idx_takeover_roster_crews_unique ON takeover_roster_crews(takeover_id, role_code, crew_type, user_id, name_label, date_start, date_end, shift_start, shift_end)',
  'SELECT 1'
);
PREPARE stmt_add_takeover_roster_unique FROM @sql_add_takeover_roster_unique;
EXECUTE stmt_add_takeover_roster_unique;
DEALLOCATE PREPARE stmt_add_takeover_roster_unique;

-- +goose Down
-- Roll back only the columns/index added for takeover roster normalization.

SET @schema_name := DATABASE();

SELECT COUNT(*) INTO @has_takeover_roster_unique
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews'
  AND INDEX_NAME = 'idx_takeover_roster_crews_unique';

SET @sql_drop_takeover_roster_unique := IF(@has_takeover_roster_unique = 1,
  'DROP INDEX idx_takeover_roster_crews_unique ON takeover_roster_crews',
  'SELECT 1'
);
PREPARE stmt_drop_takeover_roster_unique FROM @sql_drop_takeover_roster_unique;
EXECUTE stmt_drop_takeover_roster_unique;
DEALLOCATE PREPARE stmt_drop_takeover_roster_unique;

SELECT COUNT(*) INTO @has_takeover_roster_crews
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'takeover_roster_crews';

SET @sql_drop_co_pilot := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'co_pilot'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN co_pilot', 'SELECT 1');
PREPARE stmt_drop_co_pilot FROM @sql_drop_co_pilot;
EXECUTE stmt_drop_co_pilot;
DEALLOCATE PREPARE stmt_drop_co_pilot;

SET @sql_drop_examiner := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'examiner'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN examiner', 'SELECT 1');
PREPARE stmt_drop_examiner FROM @sql_drop_examiner;
EXECUTE stmt_drop_examiner;
DEALLOCATE PREPARE stmt_drop_examiner;

SET @sql_drop_supervisor := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'supervisor'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN supervisor', 'SELECT 1');
PREPARE stmt_drop_supervisor FROM @sql_drop_supervisor;
EXECUTE stmt_drop_supervisor;
DEALLOCATE PREPARE stmt_drop_supervisor;

SET @sql_drop_line_checker := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'line_checker'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN line_checker', 'SELECT 1');
PREPARE stmt_drop_line_checker FROM @sql_drop_line_checker;
EXECUTE stmt_drop_line_checker;
DEALLOCATE PREPARE stmt_drop_line_checker;

SET @sql_drop_flight_instructor := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'flight_instructor'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN flight_instructor', 'SELECT 1');
PREPARE stmt_drop_flight_instructor FROM @sql_drop_flight_instructor;
EXECUTE stmt_drop_flight_instructor;
DEALLOCATE PREPARE stmt_drop_flight_instructor;

SET @sql_drop_shift_end := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'shift_end'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN shift_end', 'SELECT 1');
PREPARE stmt_drop_shift_end FROM @sql_drop_shift_end;
EXECUTE stmt_drop_shift_end;
DEALLOCATE PREPARE stmt_drop_shift_end;

SET @sql_drop_shift_start := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'shift_start'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN shift_start', 'SELECT 1');
PREPARE stmt_drop_shift_start FROM @sql_drop_shift_start;
EXECUTE stmt_drop_shift_start;
DEALLOCATE PREPARE stmt_drop_shift_start;

SET @sql_drop_date_end := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'date_end'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN date_end', 'SELECT 1');
PREPARE stmt_drop_date_end FROM @sql_drop_date_end;
EXECUTE stmt_drop_date_end;
DEALLOCATE PREPARE stmt_drop_date_end;

SET @sql_drop_date_start := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'date_start'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN date_start', 'SELECT 1');
PREPARE stmt_drop_date_start FROM @sql_drop_date_start;
EXECUTE stmt_drop_date_start;
DEALLOCATE PREPARE stmt_drop_date_start;

SET @sql_drop_email := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'email'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN email', 'SELECT 1');
PREPARE stmt_drop_email FROM @sql_drop_email;
EXECUTE stmt_drop_email;
DEALLOCATE PREPARE stmt_drop_email;

SET @sql_drop_mobile_phone := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'mobile_phone'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN mobile_phone', 'SELECT 1');
PREPARE stmt_drop_mobile_phone FROM @sql_drop_mobile_phone;
EXECUTE stmt_drop_mobile_phone;
DEALLOCATE PREPARE stmt_drop_mobile_phone;

SET @sql_drop_name_label := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'name_label'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN name_label', 'SELECT 1');
PREPARE stmt_drop_name_label FROM @sql_drop_name_label;
EXECUTE stmt_drop_name_label;
DEALLOCATE PREPARE stmt_drop_name_label;

SET @sql_drop_user_id := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'user_id'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN user_id', 'SELECT 1');
PREPARE stmt_drop_user_id FROM @sql_drop_user_id;
EXECUTE stmt_drop_user_id;
DEALLOCATE PREPARE stmt_drop_user_id;

SET @sql_drop_crew_type := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'crew_type'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN crew_type', 'SELECT 1');
PREPARE stmt_drop_crew_type FROM @sql_drop_crew_type;
EXECUTE stmt_drop_crew_type;
DEALLOCATE PREPARE stmt_drop_crew_type;

SET @sql_drop_role_code := IF(@has_takeover_roster_crews = 1 AND EXISTS(
  SELECT 1 FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = @schema_name AND TABLE_NAME = 'takeover_roster_crews' AND COLUMN_NAME = 'role_code'
), 'ALTER TABLE takeover_roster_crews DROP COLUMN role_code', 'SELECT 1');
PREPARE stmt_drop_role_code FROM @sql_drop_role_code;
EXECUTE stmt_drop_role_code;
DEALLOCATE PREPARE stmt_drop_role_code;