-- SQL equivalent of M260721120000AddOfflineTicketBulkSales::up().
-- Select the intended application database before running this file.
-- Foreign-key columns inherit each deployed parent ID definition. This keeps
-- legacy type, charset, collation, and signedness differences FK-compatible.

SET @zv_missing_parent_id_columns = (
    SELECT GROUP_CONCAT(required_table.table_name ORDER BY required_table.table_name SEPARATOR ', ')
    FROM (
        SELECT 'users' AS table_name
        UNION ALL SELECT 'organisers'
        UNION ALL SELECT 'events'
        UNION ALL SELECT 'venue_seat_maps'
        UNION ALL SELECT 'ticket_types'
        UNION ALL SELECT 'event_seats'
        UNION ALL SELECT 'orders'
        UNION ALL SELECT 'tickets'
    ) 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 = 'users' 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 = 'organisers' 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 = 'events' 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 = '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),
    MAX(CASE WHEN table_name = 'event_seats' 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 = 'orders' 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 = 'tickets' 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_user_id_definition,
    @zv_organiser_id_definition,
    @zv_event_id_definition,
    @zv_seat_map_id_definition,
    @zv_ticket_type_id_definition,
    @zv_event_seat_id_definition,
    @zv_order_id_definition,
    @zv_ticket_id_definition
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND column_name = 'id'
  AND table_name IN ('users', 'organisers', 'events', 'venue_seat_maps', 'ticket_types', 'event_seats', 'orders', 'tickets');

SET @zv_unresolved_parent_id_definitions = NULLIF(CONCAT_WS(', ',
    IF(@zv_user_id_definition IS NULL, 'users.id', NULL),
    IF(@zv_organiser_id_definition IS NULL, 'organisers.id', NULL),
    IF(@zv_event_id_definition IS NULL, 'events.id', NULL),
    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),
    IF(@zv_event_seat_id_definition IS NULL, 'event_seats.id', NULL),
    IF(@zv_order_id_definition IS NULL, 'orders.id', NULL),
    IF(@zv_ticket_id_definition IS NULL, 'tickets.id', NULL)
), '');

SELECT COALESCE(@zv_unresolved_parent_id_definitions, 'none') AS unresolved_parent_id_definitions;

SET @zv_definition_preflight_sql = IF(
    @zv_unresolved_parent_id_definitions IS NULL,
    'SELECT 1',
    'SELECT * FROM `ZAVVION_MIGRATION_ABORT_UNRESOLVED_PARENT_ID_DEFINITIONS`'
);
PREPARE zv_definition_preflight_stmt FROM @zv_definition_preflight_sql;
EXECUTE zv_definition_preflight_stmt;
DEALLOCATE PREPARE zv_definition_preflight_stmt;

SET @zv_migration_sql = IF(
    @zv_missing_parent_id_columns IS NOT NULL,
    'SELECT 1',
    IF(
        (SELECT COUNT(*) FROM information_schema.tables
         WHERE table_schema = DATABASE() AND table_name = 'event_offline_ticket_import_settings') = 0,
        CONCAT(
        'CREATE TABLE `event_offline_ticket_import_settings` (',
        '`event_id` ', @zv_event_id_definition, ' PRIMARY KEY,',
        '`is_enabled` TINYINT(1) NOT NULL DEFAULT 0,',
        '`commission_percentage` DECIMAL(7,4) NOT NULL DEFAULT 0,',
        '`updated_by` ', @zv_user_id_definition, ' NULL,',
        '`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,',
        '`updated_at` DATETIME NULL,',
        'CONSTRAINT `fk_offline_settings_event` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,',
        'CONSTRAINT `fk_offline_settings_user` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`),',
        'CONSTRAINT `chk_offline_settings_commission` CHECK (`commission_percentage` >= 0 AND `commission_percentage` <= 100)',
        ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        ),
        'SELECT 1'
    )
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SELECT * FROM `ZAVVION_MIGRATION_ABORT_NULL_DYNAMIC_SQL_EVENT_SETTINGS`'
);
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.tables
         WHERE table_schema = DATABASE() AND table_name = 'offline_ticket_import_batches') = 0,
        CONCAT(
        'CREATE TABLE `offline_ticket_import_batches` (',
        '`id` CHAR(32) PRIMARY KEY,',
        '`organiser_id` ', @zv_organiser_id_definition, ' NOT NULL,',
        '`event_id` ', @zv_event_id_definition, ' NOT NULL,',
        '`venue_seat_map_id` ', @zv_seat_map_id_definition, ' NOT NULL,',
        '`ticket_type_id` ', @zv_ticket_type_id_definition, ' NOT NULL,',
        '`uploaded_by` ', @zv_user_id_definition, ' NOT NULL,',
        '`idempotency_key` VARCHAR(100) NOT NULL,',
        '`original_filename` VARCHAR(255) NOT NULL,',
        '`source_format` ENUM(''csv'',''xlsx'') NOT NULL,',
        '`status` ENUM(''validating'',''validation_failed'',''awaiting_payment'',''ready_to_finalise'',''finalising'',''completed'',''failed'',''cancelled'') NOT NULL DEFAULT ''validating'',',
        '`payment_status` ENUM(''not_required'',''pending'',''paid'',''failed'',''refunded'') NOT NULL DEFAULT ''not_required'',',
        '`row_count` INT NOT NULL DEFAULT 0,',
        '`error_count` INT NOT NULL DEFAULT 0,',
        '`ticket_amount_minor` BIGINT NOT NULL DEFAULT 0,',
        '`commission_percentage` DECIMAL(7,4) NOT NULL DEFAULT 0,',
        '`commission_minor` BIGINT NOT NULL DEFAULT 0,',
        '`currency` CHAR(3) NOT NULL,',
        '`stripe_checkout_session_id` VARCHAR(255) NULL,',
        '`stripe_payment_intent_id` VARCHAR(255) NULL,',
        '`failure_message` VARCHAR(500) NULL,',
        '`paid_at` DATETIME NULL,',
        '`completed_at` DATETIME NULL,',
        '`created_at` DATETIME NOT NULL,',
        '`updated_at` DATETIME NULL,',
        'UNIQUE KEY `uq_offline_batch_idempotency` (`event_id`, `idempotency_key`),',
        'UNIQUE KEY `uq_offline_batch_stripe_session` (`stripe_checkout_session_id`),',
        'UNIQUE KEY `uq_offline_batch_stripe_intent` (`stripe_payment_intent_id`),',
        'KEY `idx_offline_batch_organiser_status` (`organiser_id`, `status`),',
        'KEY `idx_offline_batch_event_status` (`event_id`, `status`),',
        'KEY `idx_offline_batch_seat_map` (`venue_seat_map_id`),',
        'KEY `idx_offline_batch_ticket_type` (`ticket_type_id`),',
        'CONSTRAINT `fk_offline_batch_organiser` FOREIGN KEY (`organiser_id`) REFERENCES `organisers` (`id`),',
        'CONSTRAINT `fk_offline_batch_event` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,',
        'CONSTRAINT `fk_offline_batch_seat_map` FOREIGN KEY (`venue_seat_map_id`) REFERENCES `venue_seat_maps` (`id`),',
        'CONSTRAINT `fk_offline_batch_ticket_type` FOREIGN KEY (`ticket_type_id`) REFERENCES `ticket_types` (`id`),',
        'CONSTRAINT `fk_offline_batch_uploader` FOREIGN KEY (`uploaded_by`) REFERENCES `users` (`id`),',
        'CONSTRAINT `chk_offline_batch_counts` CHECK (`row_count` >= 0 AND `error_count` >= 0 AND `error_count` <= `row_count`),',
        'CONSTRAINT `chk_offline_batch_amounts` CHECK (`ticket_amount_minor` >= 0 AND `commission_minor` >= 0)',
        ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        ),
        'SELECT 1'
    )
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SELECT * FROM `ZAVVION_MIGRATION_ABORT_NULL_DYNAMIC_SQL_BATCHES`'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;

SET @zv_offline_batch_id_definition = NULL;

SELECT MAX(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
))
INTO @zv_offline_batch_id_definition
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'offline_ticket_import_batches' AND column_name = 'id';

SELECT COALESCE(@zv_offline_batch_id_definition, 'unresolved') AS offline_batch_id_definition;

SET @zv_batch_id_preflight_sql = IF(
    @zv_offline_batch_id_definition IS NOT NULL,
    'SELECT 1',
    'SELECT * FROM `ZAVVION_MIGRATION_ABORT_OFFLINE_BATCH_ID_DEFINITION`'
);
PREPARE zv_batch_id_preflight_stmt FROM @zv_batch_id_preflight_sql;
EXECUTE zv_batch_id_preflight_stmt;
DEALLOCATE PREPARE zv_batch_id_preflight_stmt;

SET @zv_migration_sql = IF(
    @zv_missing_parent_id_columns IS NOT NULL,
    'SELECT 1',
    IF(
        (SELECT COUNT(*) FROM information_schema.tables
         WHERE table_schema = DATABASE() AND table_name = 'offline_ticket_import_rows') = 0,
        CONCAT(
        'CREATE TABLE `offline_ticket_import_rows` (',
        '`id` CHAR(32) PRIMARY KEY,',
        '`batch_id` ', @zv_offline_batch_id_definition, ' NOT NULL,',
        '`row_number` INT NOT NULL,',
        '`source_row_hash` CHAR(64) NOT NULL,',
        '`attendee_name` VARCHAR(190) NOT NULL,',
        '`email` VARCHAR(190) NULL,',
        '`phone` VARCHAR(40) NULL,',
        '`ticket_type_label` VARCHAR(160) NOT NULL,',
        '`ticket_type_id` ', @zv_ticket_type_id_definition, ' NULL,',
        '`amount_minor` BIGINT NOT NULL DEFAULT 0,',
        '`requested_seat_label` VARCHAR(120) NULL,',
        '`event_seat_id` ', @zv_event_seat_id_definition, ' NULL,',
        '`status` ENUM(''valid'',''invalid'',''ready'',''issued'',''failed'') NOT NULL DEFAULT ''valid'',',
        '`error_message` VARCHAR(500) NULL,',
        '`order_id` ', @zv_order_id_definition, ' NULL,',
        '`ticket_id` ', @zv_ticket_id_definition, ' NULL,',
        '`raw_json` JSON NOT NULL,',
        '`created_at` DATETIME NOT NULL,',
        '`updated_at` DATETIME NULL,',
        'UNIQUE KEY `uq_offline_row_number` (`batch_id`, `row_number`),',
        'UNIQUE KEY `uq_offline_row_source` (`batch_id`, `source_row_hash`),',
        'UNIQUE KEY `uq_offline_row_order` (`order_id`),',
        'UNIQUE KEY `uq_offline_row_ticket` (`ticket_id`),',
        'UNIQUE KEY `uq_offline_row_batch_seat` (`batch_id`, `event_seat_id`),',
        'KEY `idx_offline_row_status` (`batch_id`, `status`),',
        'KEY `idx_offline_row_event_seat` (`event_seat_id`),',
        'CONSTRAINT `fk_offline_row_batch` FOREIGN KEY (`batch_id`) REFERENCES `offline_ticket_import_batches` (`id`) ON DELETE CASCADE,',
        'CONSTRAINT `fk_offline_row_ticket_type` FOREIGN KEY (`ticket_type_id`) REFERENCES `ticket_types` (`id`),',
        'CONSTRAINT `fk_offline_row_event_seat` FOREIGN KEY (`event_seat_id`) REFERENCES `event_seats` (`id`),',
        'CONSTRAINT `fk_offline_row_order` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`),',
        'CONSTRAINT `fk_offline_row_ticket` FOREIGN KEY (`ticket_id`) REFERENCES `tickets` (`id`),',
        'CONSTRAINT `chk_offline_row_number` CHECK (`row_number` > 0),',
        'CONSTRAINT `chk_offline_row_amount` CHECK (`amount_minor` >= 0)',
        ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        ),
        'SELECT 1'
    )
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SELECT * FROM `ZAVVION_MIGRATION_ABORT_NULL_DYNAMIC_SQL_ROWS`'
);
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.tables
         WHERE table_schema = DATABASE() AND table_name = 'offline_ticket_import_commission_payments') = 0,
        CONCAT(
        'CREATE TABLE `offline_ticket_import_commission_payments` (',
        '`id` CHAR(32) PRIMARY KEY,',
        '`batch_id` ', @zv_offline_batch_id_definition, ' NOT NULL,',
        '`organiser_id` ', @zv_organiser_id_definition, ' NOT NULL,',
        '`event_id` ', @zv_event_id_definition, ' NOT NULL,',
        '`stripe_checkout_session_id` VARCHAR(255) NOT NULL,',
        '`stripe_payment_intent_id` VARCHAR(255) NULL,',
        '`amount_minor` BIGINT NOT NULL,',
        '`currency` CHAR(3) NOT NULL,',
        '`status` ENUM(''succeeded'',''refunded'',''disputed'') NOT NULL DEFAULT ''succeeded'',',
        '`created_at` DATETIME NOT NULL,',
        '`updated_at` DATETIME NULL,',
        'UNIQUE KEY `uq_offline_commission_batch` (`batch_id`),',
        'UNIQUE KEY `uq_offline_commission_session` (`stripe_checkout_session_id`),',
        'UNIQUE KEY `uq_offline_commission_intent` (`stripe_payment_intent_id`),',
        'KEY `idx_offline_commission_organiser` (`organiser_id`, `created_at`),',
        'KEY `idx_offline_commission_event` (`event_id`, `created_at`),',
        'CONSTRAINT `fk_offline_commission_batch` FOREIGN KEY (`batch_id`) REFERENCES `offline_ticket_import_batches` (`id`) ON DELETE CASCADE,',
        'CONSTRAINT `fk_offline_commission_organiser` FOREIGN KEY (`organiser_id`) REFERENCES `organisers` (`id`),',
        'CONSTRAINT `fk_offline_commission_event` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,',
        'CONSTRAINT `chk_offline_commission_amount` CHECK (`amount_minor` > 0)',
        ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        ),
        'SELECT 1'
    )
);
SET @zv_migration_sql = COALESCE(
    @zv_migration_sql,
    'SELECT * FROM `ZAVVION_MIGRATION_ABORT_NULL_DYNAMIC_SQL_COMMISSIONS`'
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;