-- Drop legacy completion metadata columns from flight_inspections.
-- Completion is derived from stage tables (before/prep/after) instead.

SET @schema_name := DATABASE();

-- before_flight_completed_at
SELECT COUNT(*) INTO @has_before_completed_at
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'flight_inspections'
  AND COLUMN_NAME = 'before_flight_completed_at';
SET @drop_before_completed_at_sql := IF(
  @has_before_completed_at > 0,
  'ALTER TABLE flight_inspections DROP COLUMN before_flight_completed_at',
  'SELECT 1'
);
PREPARE stmt_drop_before_completed_at FROM @drop_before_completed_at_sql;
EXECUTE stmt_drop_before_completed_at;
DEALLOCATE PREPARE stmt_drop_before_completed_at;

-- before_flight_completed_by
SELECT COUNT(*) INTO @has_before_completed_by
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'flight_inspections'
  AND COLUMN_NAME = 'before_flight_completed_by';
SET @drop_before_completed_by_sql := IF(
  @has_before_completed_by > 0,
  'ALTER TABLE flight_inspections DROP COLUMN before_flight_completed_by',
  'SELECT 1'
);
PREPARE stmt_drop_before_completed_by FROM @drop_before_completed_by_sql;
EXECUTE stmt_drop_before_completed_by;
DEALLOCATE PREPARE stmt_drop_before_completed_by;

-- flight_prep_completed_at
SELECT COUNT(*) INTO @has_prep_completed_at
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'flight_inspections'
  AND COLUMN_NAME = 'flight_prep_completed_at';
SET @drop_prep_completed_at_sql := IF(
  @has_prep_completed_at > 0,
  'ALTER TABLE flight_inspections DROP COLUMN flight_prep_completed_at',
  'SELECT 1'
);
PREPARE stmt_drop_prep_completed_at FROM @drop_prep_completed_at_sql;
EXECUTE stmt_drop_prep_completed_at;
DEALLOCATE PREPARE stmt_drop_prep_completed_at;

-- flight_prep_completed_by
SELECT COUNT(*) INTO @has_prep_completed_by
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'flight_inspections'
  AND COLUMN_NAME = 'flight_prep_completed_by';
SET @drop_prep_completed_by_sql := IF(
  @has_prep_completed_by > 0,
  'ALTER TABLE flight_inspections DROP COLUMN flight_prep_completed_by',
  'SELECT 1'
);
PREPARE stmt_drop_prep_completed_by FROM @drop_prep_completed_by_sql;
EXECUTE stmt_drop_prep_completed_by;
DEALLOCATE PREPARE stmt_drop_prep_completed_by;

-- after_flight_completed_at
SELECT COUNT(*) INTO @has_after_completed_at
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'flight_inspections'
  AND COLUMN_NAME = 'after_flight_completed_at';
SET @drop_after_completed_at_sql := IF(
  @has_after_completed_at > 0,
  'ALTER TABLE flight_inspections DROP COLUMN after_flight_completed_at',
  'SELECT 1'
);
PREPARE stmt_drop_after_completed_at FROM @drop_after_completed_at_sql;
EXECUTE stmt_drop_after_completed_at;
DEALLOCATE PREPARE stmt_drop_after_completed_at;

-- after_flight_completed_by
SELECT COUNT(*) INTO @has_after_completed_by
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'flight_inspections'
  AND COLUMN_NAME = 'after_flight_completed_by';
SET @drop_after_completed_by_sql := IF(
  @has_after_completed_by > 0,
  'ALTER TABLE flight_inspections DROP COLUMN after_flight_completed_by',
  'SELECT 1'
);
PREPARE stmt_drop_after_completed_by FROM @drop_after_completed_by_sql;
EXECUTE stmt_drop_after_completed_by;
DEALLOCATE PREPARE stmt_drop_after_completed_by;

-- final_completed_at
SELECT COUNT(*) INTO @has_final_completed_at
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'flight_inspections'
  AND COLUMN_NAME = 'final_completed_at';
SET @drop_final_completed_at_sql := IF(
  @has_final_completed_at > 0,
  'ALTER TABLE flight_inspections DROP COLUMN final_completed_at',
  'SELECT 1'
);
PREPARE stmt_drop_final_completed_at FROM @drop_final_completed_at_sql;
EXECUTE stmt_drop_final_completed_at;
DEALLOCATE PREPARE stmt_drop_final_completed_at;

-- final_completed_by
SELECT COUNT(*) INTO @has_final_completed_by
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = @schema_name
  AND TABLE_NAME = 'flight_inspections'
  AND COLUMN_NAME = 'final_completed_by';
SET @drop_final_completed_by_sql := IF(
  @has_final_completed_by > 0,
  'ALTER TABLE flight_inspections DROP COLUMN final_completed_by',
  'SELECT 1'
);
PREPARE stmt_drop_final_completed_by FROM @drop_final_completed_by_sql;
EXECUTE stmt_drop_final_completed_by;
DEALLOCATE PREPARE stmt_drop_final_completed_by;