-- SQL equivalent of M260721180000AddOfflineTicketBatchSelection::up().
-- Run M260721120000AddOfflineTicketBulkSales.sql first.
--
-- This migration is forward-only: it adds missing nullable columns, compatible
-- indexes, and restrictive foreign keys, then backfills only NULL selections.
-- Execute the complete file from the first statement; never rerun only a failed
-- PREPARE line because its session variables are established by the preflights.
-- It never drops, truncates, deletes, replaces, or modifies an existing column.
-- MySQL DDL auto-commits, so stop at the first named ZAVVION_MIGRATION_ABORT_*
-- diagnostic, inspect the target object, and do not guess or recreate live data.

SET @zv_target_database = DATABASE();
SET @zv_abort_reason = IF(@zv_target_database IS NULL, 'NO_DATABASE_SELECTED', NULL);
SELECT COALESCE(@zv_target_database, 'none') AS target_database;

SET @zv_missing_required_columns = (
    SELECT GROUP_CONCAT(CONCAT(required_column.table_name, '.', required_column.column_name)
                        ORDER BY required_column.table_name, required_column.column_name SEPARATOR ', ')
    FROM (
        SELECT 'events' AS table_name, 'id' AS column_name
        UNION ALL SELECT 'events', 'organiser_id'
        UNION ALL SELECT 'events', 'venue_id'
        UNION ALL SELECT 'venue_seat_maps', 'id'
        UNION ALL SELECT 'venue_seat_maps', 'venue_id'
        UNION ALL SELECT 'ticket_types', 'id'
        UNION ALL SELECT 'ticket_types', 'event_id'
        UNION ALL SELECT 'ticket_types', 'venue_seat_map_id'
        UNION ALL SELECT 'offline_ticket_import_batches', 'id'
        UNION ALL SELECT 'offline_ticket_import_batches', 'organiser_id'
        UNION ALL SELECT 'offline_ticket_import_batches', 'event_id'
        UNION ALL SELECT 'offline_ticket_import_rows', 'id'
        UNION ALL SELECT 'offline_ticket_import_rows', 'batch_id'
        UNION ALL SELECT 'offline_ticket_import_rows', 'ticket_type_id'
    ) AS required_column
    LEFT JOIN information_schema.columns AS existing_column
        ON existing_column.table_schema = DATABASE()
       AND existing_column.table_name = required_column.table_name
       AND existing_column.column_name = required_column.column_name
    WHERE existing_column.column_name IS NULL
);

SELECT COALESCE(@zv_missing_required_columns, 'none') AS missing_required_columns;
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_missing_required_columns IS NOT NULL,
    'MISSING_REQUIRED_COLUMNS',
    @zv_abort_reason
);

SET @zv_non_innodb_tables = (
    SELECT GROUP_CONCAT(required_table.table_name ORDER BY required_table.table_name SEPARATOR ', ')
    FROM (
        SELECT 'events' AS table_name
        UNION ALL SELECT 'venue_seat_maps'
        UNION ALL SELECT 'ticket_types'
        UNION ALL SELECT 'offline_ticket_import_batches'
        UNION ALL SELECT 'offline_ticket_import_rows'
    ) AS required_table
    INNER JOIN information_schema.tables AS existing_table
        ON existing_table.table_schema = DATABASE()
       AND existing_table.table_name = required_table.table_name
    WHERE UPPER(existing_table.engine) <> 'INNODB'
);

SELECT COALESCE(@zv_non_innodb_tables, 'none') AS non_innodb_required_tables;
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_non_innodb_tables IS NOT NULL,
    'REQUIRED_TABLE_NOT_INNODB',
    @zv_abort_reason
);

SET @zv_seat_map_id_definition = NULL;
SET @zv_ticket_type_id_definition = NULL;

SELECT
    MAX(CASE WHEN table_name = 'venue_seat_maps' THEN CONCAT(
        COLUMN_TYPE,
        CASE
            WHEN CHARACTER_SET_NAME IS NULL THEN ''
            WHEN COLLATION_NAME IS NULL THEN CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME)
            ELSE CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME, ' COLLATE ', COLLATION_NAME)
        END
    ) END),
    MAX(CASE WHEN table_name = 'ticket_types' THEN CONCAT(
        COLUMN_TYPE,
        CASE
            WHEN CHARACTER_SET_NAME IS NULL THEN ''
            WHEN COLLATION_NAME IS NULL THEN CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME)
            ELSE CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME, ' COLLATE ', COLLATION_NAME)
        END
    ) END)
INTO @zv_seat_map_id_definition, @zv_ticket_type_id_definition
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND column_name = 'id'
  AND table_name IN ('venue_seat_maps', 'ticket_types');

SET @zv_unresolved_parent_id_definitions = NULLIF(CONCAT_WS(', ',
    IF(@zv_seat_map_id_definition IS NULL, 'venue_seat_maps.id', NULL),
    IF(@zv_ticket_type_id_definition IS NULL, 'ticket_types.id', NULL)
), '');

SELECT COALESCE(@zv_unresolved_parent_id_definitions, 'none') AS unresolved_parent_id_definitions;
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_unresolved_parent_id_definitions IS NOT NULL,
    'UNRESOLVED_PARENT_ID_DEFINITIONS',
    @zv_abort_reason
);

SET @zv_missing_parent_unique_indexes = NULLIF(CONCAT_WS(', ',
    IF((
        SELECT COUNT(*)
        FROM (
            SELECT index_name
            FROM information_schema.statistics
            WHERE table_schema = DATABASE()
              AND table_name = 'venue_seat_maps'
              AND non_unique = 0
            GROUP BY index_name
            HAVING COUNT(*) = 1
               AND MAX(column_name = 'id' AND seq_in_index = 1) = 1
        ) AS seat_map_unique_id_index
    ) = 0, 'venue_seat_maps.id', NULL),
    IF((
        SELECT COUNT(*)
        FROM (
            SELECT index_name
            FROM information_schema.statistics
            WHERE table_schema = DATABASE()
              AND table_name = 'ticket_types'
              AND non_unique = 0
            GROUP BY index_name
            HAVING COUNT(*) = 1
               AND MAX(column_name = 'id' AND seq_in_index = 1) = 1
        ) AS ticket_type_unique_id_index
    ) = 0, 'ticket_types.id', NULL)
), '');

SELECT COALESCE(@zv_missing_parent_unique_indexes, 'none') AS missing_parent_unique_indexes;
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_missing_parent_unique_indexes IS NOT NULL,
    'MISSING_PARENT_UNIQUE_INDEXES',
    @zv_abort_reason
);

SET @zv_incompatible_existing_target_columns = (
    SELECT GROUP_CONCAT(CONCAT(child.table_name, '.', child.column_name)
                        ORDER BY child.column_name SEPARATOR ', ')
    FROM information_schema.columns AS child
    INNER JOIN information_schema.columns AS parent
        ON parent.table_schema = child.table_schema
       AND ((child.column_name = 'venue_seat_map_id' AND parent.table_name = 'venue_seat_maps' AND parent.column_name = 'id')
         OR (child.column_name = 'ticket_type_id' AND parent.table_name = 'ticket_types' AND parent.column_name = 'id'))
    WHERE child.table_schema = DATABASE()
      AND child.table_name = 'offline_ticket_import_batches'
      AND child.column_name IN ('venue_seat_map_id', 'ticket_type_id')
      AND NOT (
          child.column_type <=> parent.column_type
          AND child.character_set_name <=> parent.character_set_name
          AND child.collation_name <=> parent.collation_name
      )
);

SELECT COALESCE(@zv_incompatible_existing_target_columns, 'none') AS incompatible_existing_target_columns;
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_incompatible_existing_target_columns IS NOT NULL,
    'INCOMPATIBLE_EXISTING_TARGET_COLUMNS',
    @zv_abort_reason
);

SET @zv_migration_sql = IF(
    @zv_abort_reason IS NOT NULL,
    'SELECT 1',
    IF((SELECT COUNT(*) FROM information_schema.columns
     WHERE table_schema = DATABASE()
       AND table_name = 'offline_ticket_import_batches'
       AND column_name = 'venue_seat_map_id') = 0,
    CONCAT('ALTER TABLE `offline_ticket_import_batches` ADD COLUMN `venue_seat_map_id` ', @zv_seat_map_id_definition, ' NULL'),
    'SELECT 1')
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SET @zv_abort_reason = COALESCE(@zv_abort_reason, ''NULL_DYNAMIC_SQL_ADD_SEAT_MAP_COLUMN'')'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_migration_sql = IF(
    @zv_abort_reason IS NOT NULL,
    'SELECT 1',
    IF((SELECT COUNT(*) FROM information_schema.columns
     WHERE table_schema = DATABASE()
       AND table_name = 'offline_ticket_import_batches'
       AND column_name = 'ticket_type_id') = 0,
    CONCAT('ALTER TABLE `offline_ticket_import_batches` ADD COLUMN `ticket_type_id` ', @zv_ticket_type_id_definition, ' NULL'),
    'SELECT 1')
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SET @zv_abort_reason = COALESCE(@zv_abort_reason, ''NULL_DYNAMIC_SQL_ADD_TICKET_TYPE_COLUMN'')'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_incompatible_target_columns = (
    SELECT GROUP_CONCAT(CONCAT(child.table_name, '.', child.column_name)
                        ORDER BY child.column_name SEPARATOR ', ')
    FROM information_schema.columns AS child
    INNER JOIN information_schema.columns AS parent
        ON parent.table_schema = child.table_schema
       AND ((child.column_name = 'venue_seat_map_id' AND parent.table_name = 'venue_seat_maps' AND parent.column_name = 'id')
         OR (child.column_name = 'ticket_type_id' AND parent.table_name = 'ticket_types' AND parent.column_name = 'id'))
    WHERE child.table_schema = DATABASE()
      AND child.table_name = 'offline_ticket_import_batches'
      AND child.column_name IN ('venue_seat_map_id', 'ticket_type_id')
      AND NOT (
          child.column_type <=> parent.column_type
          AND child.character_set_name <=> parent.character_set_name
          AND child.collation_name <=> parent.collation_name
      )
);

SELECT COALESCE(@zv_incompatible_target_columns, 'none') AS incompatible_target_columns;
SET @zv_missing_target_columns = (
    SELECT GROUP_CONCAT(required_column.column_name ORDER BY required_column.column_name SEPARATOR ', ')
    FROM (
        SELECT 'venue_seat_map_id' AS column_name
        UNION ALL SELECT 'ticket_type_id'
    ) AS required_column
    LEFT JOIN information_schema.columns AS existing_column
        ON existing_column.table_schema = DATABASE()
       AND existing_column.table_name = 'offline_ticket_import_batches'
       AND existing_column.column_name = required_column.column_name
    WHERE existing_column.column_name IS NULL
);
SELECT COALESCE(@zv_missing_target_columns, 'none') AS missing_target_columns_after_add;
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_missing_target_columns IS NOT NULL,
    'TARGET_COLUMNS_NOT_CREATED',
    @zv_abort_reason
);
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_incompatible_target_columns IS NOT NULL,
    'TARGET_COLUMN_DEFINITION_VERIFICATION',
    @zv_abort_reason
);

SET @zv_invalid_batch_scope_count = NULL;
SET @zv_validation_sql = IF(
    @zv_abort_reason IS NULL,
    'SELECT COUNT(*) INTO @zv_invalid_batch_scope_count
     FROM offline_ticket_import_batches AS b
     LEFT JOIN events AS e ON BINARY e.id = BINARY b.event_id
     WHERE e.id IS NULL
        OR NOT (BINARY e.organiser_id <=> BINARY b.organiser_id)',
    'SET @zv_invalid_batch_scope_count = NULL'
);
PREPARE zv_validation_stmt FROM @zv_validation_sql;
EXECUTE zv_validation_stmt;
DEALLOCATE PREPARE zv_validation_stmt;

SELECT COALESCE(@zv_invalid_batch_scope_count, -1) AS invalid_batch_event_or_organiser_rows;
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND COALESCE(@zv_invalid_batch_scope_count, -1) <> 0,
    'INVALID_BATCH_EVENT_OR_ORGANISER_SCOPE',
    @zv_abort_reason
);

SET @zv_invalid_existing_selection_count = NULL;
SET @zv_validation_sql = IF(
    @zv_abort_reason IS NULL,
    'SELECT COUNT(*) INTO @zv_invalid_existing_selection_count
     FROM offline_ticket_import_batches AS b
     LEFT JOIN events AS e ON BINARY e.id = BINARY b.event_id
     LEFT JOIN ticket_types AS tt ON BINARY tt.id = BINARY b.ticket_type_id
     LEFT JOIN venue_seat_maps AS vsm ON BINARY vsm.id = BINARY b.venue_seat_map_id
     WHERE (b.ticket_type_id IS NOT NULL AND (
               tt.id IS NULL
               OR NOT (BINARY tt.event_id <=> BINARY b.event_id)
           ))
        OR (b.venue_seat_map_id IS NOT NULL AND (
               vsm.id IS NULL
               OR e.id IS NULL
               OR NOT (BINARY vsm.venue_id <=> BINARY e.venue_id)
           ))
        OR (b.ticket_type_id IS NOT NULL
            AND b.venue_seat_map_id IS NOT NULL
            AND NOT (BINARY b.venue_seat_map_id <=> BINARY tt.venue_seat_map_id))',
    'SET @zv_invalid_existing_selection_count = NULL'
);
PREPARE zv_validation_stmt FROM @zv_validation_sql;
EXECUTE zv_validation_stmt;
DEALLOCATE PREPARE zv_validation_stmt;

SELECT COALESCE(@zv_invalid_existing_selection_count, -1) AS invalid_existing_batch_selections;
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND COALESCE(@zv_invalid_existing_selection_count, -1) <> 0,
    'INVALID_EXISTING_BATCH_SELECTIONS',
    @zv_abort_reason
);

SET @zv_invalid_ticket_type_candidate_count = NULL;
SET @zv_validation_sql = IF(
    @zv_abort_reason IS NULL,
    'SELECT COUNT(*) INTO @zv_invalid_ticket_type_candidate_count
     FROM offline_ticket_import_batches AS b
     INNER JOIN (
         SELECT BINARY r.batch_id AS batch_id,
                MIN(BINARY r.ticket_type_id) AS ticket_type_id
         FROM offline_ticket_import_rows AS r
         WHERE r.ticket_type_id IS NOT NULL
         GROUP BY BINARY r.batch_id
         HAVING COUNT(DISTINCT BINARY r.ticket_type_id) = 1
     ) AS candidate ON candidate.batch_id = BINARY b.id
     LEFT JOIN ticket_types AS tt ON BINARY tt.id = candidate.ticket_type_id
     LEFT JOIN events AS e ON BINARY e.id = BINARY b.event_id
     LEFT JOIN venue_seat_maps AS vsm ON BINARY vsm.id = BINARY tt.venue_seat_map_id
     WHERE b.ticket_type_id IS NULL
       AND (
           tt.id IS NULL
           OR e.id IS NULL
           OR NOT (BINARY tt.event_id <=> BINARY b.event_id)
           OR (tt.venue_seat_map_id IS NOT NULL AND vsm.id IS NULL)
           OR (tt.venue_seat_map_id IS NOT NULL AND NOT (BINARY vsm.venue_id <=> BINARY e.venue_id))
       )',
    'SET @zv_invalid_ticket_type_candidate_count = NULL'
);
PREPARE zv_validation_stmt FROM @zv_validation_sql;
EXECUTE zv_validation_stmt;
DEALLOCATE PREPARE zv_validation_stmt;

SELECT COALESCE(@zv_invalid_ticket_type_candidate_count, -1) AS invalid_ticket_type_backfill_candidates;
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND COALESCE(@zv_invalid_ticket_type_candidate_count, -1) <> 0,
    'INVALID_TICKET_TYPE_BACKFILL_CANDIDATES',
    @zv_abort_reason
);

SET @zv_migration_sql = IF(
    @zv_abort_reason IS NULL,
    'UPDATE offline_ticket_import_batches AS b
     SET b.ticket_type_id = (
         SELECT MIN(BINARY r.ticket_type_id)
         FROM offline_ticket_import_rows AS r
         WHERE BINARY r.batch_id = BINARY b.id
     )
     WHERE b.ticket_type_id IS NULL
       AND (
           SELECT COUNT(DISTINCT BINARY r.ticket_type_id)
           FROM offline_ticket_import_rows AS r
           WHERE BINARY r.batch_id = BINARY b.id
             AND r.ticket_type_id IS NOT NULL
       ) = 1',
    'SELECT 1'
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SET @zv_abort_reason = COALESCE(@zv_abort_reason, ''NULL_DYNAMIC_SQL_BACKFILL_TICKET_TYPE'')'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
SET @zv_ticket_type_rows_backfilled = IF(@zv_abort_reason IS NULL, ROW_COUNT(), 0);
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_migration_sql = IF(
    @zv_abort_reason IS NULL,
    'UPDATE offline_ticket_import_batches AS b
     INNER JOIN ticket_types AS tt ON BINARY tt.id = BINARY b.ticket_type_id
     SET b.venue_seat_map_id = tt.venue_seat_map_id
     WHERE b.venue_seat_map_id IS NULL
       AND tt.venue_seat_map_id IS NOT NULL',
    'SELECT 1'
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SET @zv_abort_reason = COALESCE(@zv_abort_reason, ''NULL_DYNAMIC_SQL_BACKFILL_SEAT_MAP'')'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
SET @zv_seat_map_rows_backfilled = IF(@zv_abort_reason IS NULL, ROW_COUNT(), 0);
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_invalid_final_selection_count = NULL;
SET @zv_validation_sql = IF(
    @zv_abort_reason IS NULL,
    'SELECT COUNT(*) INTO @zv_invalid_final_selection_count
     FROM offline_ticket_import_batches AS b
     LEFT JOIN events AS e ON BINARY e.id = BINARY b.event_id
     LEFT JOIN ticket_types AS tt ON BINARY tt.id = BINARY b.ticket_type_id
     LEFT JOIN venue_seat_maps AS vsm ON BINARY vsm.id = BINARY b.venue_seat_map_id
     WHERE (b.ticket_type_id IS NOT NULL AND (
               tt.id IS NULL
               OR NOT (BINARY tt.event_id <=> BINARY b.event_id)
           ))
        OR (b.venue_seat_map_id IS NOT NULL AND (
               vsm.id IS NULL
               OR e.id IS NULL
               OR NOT (BINARY vsm.venue_id <=> BINARY e.venue_id)
           ))
        OR (b.ticket_type_id IS NOT NULL
            AND NOT (BINARY b.venue_seat_map_id <=> BINARY tt.venue_seat_map_id))',
    'SET @zv_invalid_final_selection_count = NULL'
);
PREPARE zv_validation_stmt FROM @zv_validation_sql;
EXECUTE zv_validation_stmt;
DEALLOCATE PREPARE zv_validation_stmt;

SELECT COALESCE(@zv_invalid_final_selection_count, -1) AS invalid_final_batch_selections;
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND COALESCE(@zv_invalid_final_selection_count, -1) <> 0,
    'INVALID_FINAL_BATCH_SELECTIONS',
    @zv_abort_reason
);

SELECT COUNT(*),
       COALESCE(SUM(column_name = 'venue_seat_map_id' AND seq_in_index = 1 AND sub_part IS NULL AND index_type = 'BTREE'), 0)
INTO @zv_named_seat_map_index_rows, @zv_named_seat_map_index_matches
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'offline_ticket_import_batches'
  AND index_name = 'idx_offline_batch_seat_map';

SET @zv_bad_named_seat_map_index = @zv_named_seat_map_index_rows > 0
    AND NOT (@zv_named_seat_map_index_rows = 1 AND @zv_named_seat_map_index_matches = 1);
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_bad_named_seat_map_index <> 0,
    'MALFORMED_NAMED_SEAT_MAP_INDEX',
    @zv_abort_reason
);

SELECT COUNT(*) INTO @zv_compatible_seat_map_index_count
FROM (
    SELECT index_name
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'offline_ticket_import_batches'
    GROUP BY index_name
    HAVING COUNT(*) = 1
       AND MAX(column_name = 'venue_seat_map_id' AND seq_in_index = 1 AND sub_part IS NULL AND index_type = 'BTREE') = 1
) AS compatible_index;

SET @zv_migration_sql = IF(
    @zv_abort_reason IS NOT NULL,
    'SELECT 1',
    IF(@zv_compatible_seat_map_index_count = 0,
       'ALTER TABLE `offline_ticket_import_batches` ADD KEY `idx_offline_batch_seat_map` (`venue_seat_map_id`)',
       'SELECT 1')
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SET @zv_abort_reason = COALESCE(@zv_abort_reason, ''NULL_DYNAMIC_SQL_ADD_SEAT_MAP_INDEX'')'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SELECT COUNT(*),
       COALESCE(SUM(column_name = 'ticket_type_id' AND seq_in_index = 1 AND sub_part IS NULL AND index_type = 'BTREE'), 0)
INTO @zv_named_ticket_type_index_rows, @zv_named_ticket_type_index_matches
FROM information_schema.statistics
WHERE table_schema = DATABASE()
  AND table_name = 'offline_ticket_import_batches'
  AND index_name = 'idx_offline_batch_ticket_type';

SET @zv_bad_named_ticket_type_index = @zv_named_ticket_type_index_rows > 0
    AND NOT (@zv_named_ticket_type_index_rows = 1 AND @zv_named_ticket_type_index_matches = 1);
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_bad_named_ticket_type_index <> 0,
    'MALFORMED_NAMED_TICKET_TYPE_INDEX',
    @zv_abort_reason
);

SELECT COUNT(*) INTO @zv_compatible_ticket_type_index_count
FROM (
    SELECT index_name
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'offline_ticket_import_batches'
    GROUP BY index_name
    HAVING COUNT(*) = 1
       AND MAX(column_name = 'ticket_type_id' AND seq_in_index = 1 AND sub_part IS NULL AND index_type = 'BTREE') = 1
) AS compatible_index;

SET @zv_migration_sql = IF(
    @zv_abort_reason IS NOT NULL,
    'SELECT 1',
    IF(@zv_compatible_ticket_type_index_count = 0,
       'ALTER TABLE `offline_ticket_import_batches` ADD KEY `idx_offline_batch_ticket_type` (`ticket_type_id`)',
       'SELECT 1')
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SET @zv_abort_reason = COALESCE(@zv_abort_reason, ''NULL_DYNAMIC_SQL_ADD_TICKET_TYPE_INDEX'')'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SELECT COUNT(*),
       COALESCE(SUM(
           kcu.table_name = 'offline_ticket_import_batches'
           AND kcu.column_name = 'venue_seat_map_id'
           AND kcu.referenced_table_name = 'venue_seat_maps'
           AND kcu.referenced_column_name = 'id'
           AND rc.delete_rule IN ('RESTRICT', 'NO ACTION')
           AND rc.update_rule IN ('RESTRICT', 'NO ACTION')
       ), 0)
INTO @zv_named_seat_map_fk_rows, @zv_named_seat_map_fk_matches
FROM information_schema.key_column_usage AS kcu
INNER JOIN information_schema.referential_constraints AS rc
    ON rc.constraint_schema = kcu.constraint_schema
   AND rc.constraint_name = kcu.constraint_name
   AND rc.table_name = kcu.table_name
WHERE kcu.constraint_schema = DATABASE()
  AND kcu.constraint_name = 'fk_offline_batch_seat_map';

SET @zv_bad_named_seat_map_fk = @zv_named_seat_map_fk_rows > 0
    AND NOT (@zv_named_seat_map_fk_rows = 1 AND @zv_named_seat_map_fk_matches = 1);
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_bad_named_seat_map_fk <> 0,
    'MALFORMED_NAMED_SEAT_MAP_FOREIGN_KEY',
    @zv_abort_reason
);

SELECT COUNT(*) INTO @zv_compatible_seat_map_fk_count
FROM information_schema.key_column_usage AS kcu
INNER JOIN information_schema.referential_constraints AS rc
    ON rc.constraint_schema = kcu.constraint_schema
   AND rc.constraint_name = kcu.constraint_name
   AND rc.table_name = kcu.table_name
WHERE kcu.constraint_schema = DATABASE()
  AND kcu.table_name = 'offline_ticket_import_batches'
  AND kcu.column_name = 'venue_seat_map_id'
  AND kcu.referenced_table_name = 'venue_seat_maps'
  AND kcu.referenced_column_name = 'id'
  AND rc.delete_rule IN ('RESTRICT', 'NO ACTION')
  AND rc.update_rule IN ('RESTRICT', 'NO ACTION')
  AND (SELECT COUNT(*)
       FROM information_schema.key_column_usage AS same_constraint
       WHERE same_constraint.constraint_schema = kcu.constraint_schema
         AND same_constraint.constraint_name = kcu.constraint_name
         AND same_constraint.table_name = kcu.table_name) = 1;

SET @zv_migration_sql = IF(
    @zv_abort_reason IS NOT NULL,
    'SELECT 1',
    IF(@zv_compatible_seat_map_fk_count = 0,
       'ALTER TABLE `offline_ticket_import_batches` ADD CONSTRAINT `fk_offline_batch_seat_map` FOREIGN KEY (`venue_seat_map_id`) REFERENCES `venue_seat_maps` (`id`) ON DELETE RESTRICT',
       'SELECT 1')
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SET @zv_abort_reason = COALESCE(@zv_abort_reason, ''NULL_DYNAMIC_SQL_ADD_SEAT_MAP_FOREIGN_KEY'')'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SELECT COUNT(*),
       COALESCE(SUM(
           kcu.table_name = 'offline_ticket_import_batches'
           AND kcu.column_name = 'ticket_type_id'
           AND kcu.referenced_table_name = 'ticket_types'
           AND kcu.referenced_column_name = 'id'
           AND rc.delete_rule IN ('RESTRICT', 'NO ACTION')
           AND rc.update_rule IN ('RESTRICT', 'NO ACTION')
       ), 0)
INTO @zv_named_ticket_type_fk_rows, @zv_named_ticket_type_fk_matches
FROM information_schema.key_column_usage AS kcu
INNER JOIN information_schema.referential_constraints AS rc
    ON rc.constraint_schema = kcu.constraint_schema
   AND rc.constraint_name = kcu.constraint_name
   AND rc.table_name = kcu.table_name
WHERE kcu.constraint_schema = DATABASE()
  AND kcu.constraint_name = 'fk_offline_batch_ticket_type';

SET @zv_bad_named_ticket_type_fk = @zv_named_ticket_type_fk_rows > 0
    AND NOT (@zv_named_ticket_type_fk_rows = 1 AND @zv_named_ticket_type_fk_matches = 1);
SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND @zv_bad_named_ticket_type_fk <> 0,
    'MALFORMED_NAMED_TICKET_TYPE_FOREIGN_KEY',
    @zv_abort_reason
);

SELECT COUNT(*) INTO @zv_compatible_ticket_type_fk_count
FROM information_schema.key_column_usage AS kcu
INNER JOIN information_schema.referential_constraints AS rc
    ON rc.constraint_schema = kcu.constraint_schema
   AND rc.constraint_name = kcu.constraint_name
   AND rc.table_name = kcu.table_name
WHERE kcu.constraint_schema = DATABASE()
  AND kcu.table_name = 'offline_ticket_import_batches'
  AND kcu.column_name = 'ticket_type_id'
  AND kcu.referenced_table_name = 'ticket_types'
  AND kcu.referenced_column_name = 'id'
  AND rc.delete_rule IN ('RESTRICT', 'NO ACTION')
  AND rc.update_rule IN ('RESTRICT', 'NO ACTION')
  AND (SELECT COUNT(*)
       FROM information_schema.key_column_usage AS same_constraint
       WHERE same_constraint.constraint_schema = kcu.constraint_schema
         AND same_constraint.constraint_name = kcu.constraint_name
         AND same_constraint.table_name = kcu.table_name) = 1;

SET @zv_migration_sql = IF(
    @zv_abort_reason IS NOT NULL,
    'SELECT 1',
    IF(@zv_compatible_ticket_type_fk_count = 0,
       'ALTER TABLE `offline_ticket_import_batches` ADD CONSTRAINT `fk_offline_batch_ticket_type` FOREIGN KEY (`ticket_type_id`) REFERENCES `ticket_types` (`id`) ON DELETE RESTRICT',
       'SELECT 1')
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SET @zv_abort_reason = COALESCE(@zv_abort_reason, ''NULL_DYNAMIC_SQL_ADD_TICKET_TYPE_FOREIGN_KEY'')'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SELECT COUNT(*) INTO @zv_final_required_columns_present
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND table_name = 'offline_ticket_import_batches'
  AND column_name IN ('venue_seat_map_id', 'ticket_type_id');

SELECT COUNT(*) INTO @zv_final_compatible_selection_indexes
FROM (
    SELECT index_name
    FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'offline_ticket_import_batches'
    GROUP BY index_name
    HAVING COUNT(*) = 1
       AND MAX(column_name IN ('venue_seat_map_id', 'ticket_type_id')
               AND seq_in_index = 1
               AND sub_part IS NULL
               AND index_type = 'BTREE') = 1
) AS compatible_indexes;

SELECT COUNT(*) INTO @zv_final_compatible_selection_foreign_keys
FROM information_schema.key_column_usage AS kcu
INNER JOIN information_schema.referential_constraints AS rc
   ON rc.constraint_schema = kcu.constraint_schema
  AND rc.constraint_name = kcu.constraint_name
  AND rc.table_name = kcu.table_name
WHERE kcu.constraint_schema = DATABASE()
  AND kcu.table_name = 'offline_ticket_import_batches'
  AND ((kcu.column_name = 'venue_seat_map_id' AND kcu.referenced_table_name = 'venue_seat_maps' AND kcu.referenced_column_name = 'id')
    OR (kcu.column_name = 'ticket_type_id' AND kcu.referenced_table_name = 'ticket_types' AND kcu.referenced_column_name = 'id'))
  AND rc.delete_rule IN ('RESTRICT', 'NO ACTION')
  AND rc.update_rule IN ('RESTRICT', 'NO ACTION')
  AND (SELECT COUNT(*)
       FROM information_schema.key_column_usage AS same_constraint
       WHERE same_constraint.constraint_schema = kcu.constraint_schema
         AND same_constraint.constraint_name = kcu.constraint_name
         AND same_constraint.table_name = kcu.table_name) = 1;

SET @zv_abort_reason = IF(
    @zv_abort_reason IS NULL AND NOT (
        @zv_final_required_columns_present = 2
        AND @zv_final_compatible_selection_indexes = 2
        AND @zv_final_compatible_selection_foreign_keys = 2
    ),
    'FINAL_OBJECT_VERIFICATION_FAILED',
    @zv_abort_reason
);

SELECT
    @zv_final_required_columns_present AS required_columns_present,
    @zv_final_compatible_selection_indexes AS compatible_selection_indexes,
    @zv_final_compatible_selection_foreign_keys AS compatible_selection_foreign_keys,
    @zv_ticket_type_rows_backfilled AS ticket_type_rows_backfilled,
    @zv_seat_map_rows_backfilled AS seat_map_rows_backfilled,
    COALESCE(@zv_abort_reason, 'none') AS abort_reason;

SET @zv_final_abort_sql = IF(
    @zv_abort_reason IS NULL,
    'SELECT 1',
    CONCAT('SELECT * FROM `information_schema`.`ZAVVION_MIGRATION_ABORT_', @zv_abort_reason, '`')
);
PREPARE zv_final_abort_stmt FROM @zv_final_abort_sql;
EXECUTE zv_final_abort_stmt;
DEALLOCATE PREPARE zv_final_abort_stmt;