-- SQL equivalent of M260721180000AddOfflineTicketBatchSelection::up().
-- Run M260721120000AddOfflineTicketBulkSales.sql first.
-- Dynamic statements preserve compatibility with MySQL and MariaDB versions
-- that do not consistently support ADD ... IF NOT EXISTS for every object type.
-- New foreign-key columns inherit the deployed parent ID definition to avoid
-- MySQL #3780 on legacy schemas with different ID collations or lengths.

SET @zv_missing_parent_id_columns = (
    SELECT GROUP_CONCAT(required_table.table_name ORDER BY required_table.table_name SEPARATOR ', ')
    FROM (
        SELECT 'venue_seat_maps' AS table_name
        UNION ALL SELECT 'ticket_types'
        UNION ALL SELECT 'offline_ticket_import_batches'
        UNION ALL SELECT 'offline_ticket_import_rows'
    ) AS required_table
    LEFT JOIN information_schema.columns AS parent_id
        ON parent_id.table_schema = DATABASE()
       AND parent_id.table_name = required_table.table_name
       AND parent_id.column_name = 'id'
    WHERE parent_id.column_name IS NULL
);

SELECT DATABASE() AS target_database,
       COALESCE(@zv_missing_parent_id_columns, 'none') AS missing_required_parent_id_columns;

SET @zv_preflight_sql = IF(
    @zv_missing_parent_id_columns IS NULL,
    'SELECT 1',
    'SELECT * FROM `ZAVVION_MIGRATION_ABORT_MISSING_PARENT_ID_COLUMNS`'
);
PREPARE zv_preflight_stmt FROM @zv_preflight_sql;
EXECUTE zv_preflight_stmt;
DEALLOCATE PREPARE zv_preflight_stmt;

SELECT
    MAX(CASE WHEN table_name = 'venue_seat_maps' THEN CONCAT(COLUMN_TYPE, IF(CHARACTER_SET_NAME IS NULL, '', CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME, ' COLLATE ', COLLATION_NAME))) END),
    MAX(CASE WHEN table_name = 'ticket_types' THEN CONCAT(COLUMN_TYPE, IF(CHARACTER_SET_NAME IS NULL, '', CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME, ' COLLATE ', COLLATION_NAME))) 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_migration_sql = IF(
    @zv_missing_parent_id_columns 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'
    )
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_migration_sql = IF(
    @zv_missing_parent_id_columns 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'
    )
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_migration_sql = IF(
    @zv_missing_parent_id_columns IS NOT NULL,
    'SELECT 1',
    'UPDATE `offline_ticket_import_batches` AS b
     SET b.`ticket_type_id` = (
         SELECT MIN(r.`ticket_type_id`)
         FROM `offline_ticket_import_rows` AS r
         WHERE r.`batch_id` = b.`id`
     )
     WHERE b.`ticket_type_id` IS NULL
       AND (
           SELECT COUNT(DISTINCT r.`ticket_type_id`)
           FROM `offline_ticket_import_rows` AS r
           WHERE r.`batch_id` = b.`id`
             AND r.`ticket_type_id` IS NOT NULL
       ) = 1'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_migration_sql = IF(
    @zv_missing_parent_id_columns IS NOT NULL,
    'SELECT 1',
    'UPDATE `offline_ticket_import_batches` AS b
     INNER JOIN `ticket_types` AS tt ON tt.`id` = 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'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_migration_sql = IF(
    @zv_missing_parent_id_columns IS NOT NULL,
    'SELECT 1',
    IF(
        (SELECT COUNT(*) FROM information_schema.statistics
         WHERE table_schema = DATABASE()
           AND table_name = 'offline_ticket_import_batches'
           AND index_name = 'idx_offline_batch_seat_map') = 0,
        'ALTER TABLE `offline_ticket_import_batches` ADD KEY `idx_offline_batch_seat_map` (`venue_seat_map_id`)',
        'SELECT 1'
    )
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_migration_sql = IF(
    @zv_missing_parent_id_columns IS NOT NULL,
    'SELECT 1',
    IF(
        (SELECT COUNT(*) FROM information_schema.statistics
         WHERE table_schema = DATABASE()
           AND table_name = 'offline_ticket_import_batches'
           AND index_name = 'idx_offline_batch_ticket_type') = 0,
        'ALTER TABLE `offline_ticket_import_batches` ADD KEY `idx_offline_batch_ticket_type` (`ticket_type_id`)',
        'SELECT 1'
    )
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_migration_sql = IF(
    @zv_missing_parent_id_columns IS NOT NULL,
    'SELECT 1',
    IF(
        (SELECT COUNT(*) FROM information_schema.referential_constraints
         WHERE constraint_schema = DATABASE()
           AND constraint_name = 'fk_offline_batch_seat_map') = 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`)',
        'SELECT 1'
    )
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_migration_sql = IF(
    @zv_missing_parent_id_columns IS NOT NULL,
    'SELECT 1',
    IF(
        (SELECT COUNT(*) FROM information_schema.referential_constraints
         WHERE constraint_schema = DATABASE()
           AND constraint_name = 'fk_offline_batch_ticket_type') = 0,
        'ALTER TABLE `offline_ticket_import_batches` ADD CONSTRAINT `fk_offline_batch_ticket_type` FOREIGN KEY (`ticket_type_id`) REFERENCES `ticket_types` (`id`)',
        'SELECT 1'
    )
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;