-- SQL equivalent of M260618140000AddOrganiserBoxOfficeSettings::up().
-- Select the intended application database before running this file.
-- Referencing columns inherit the deployed parent ID definitions so legacy
-- charset, collation, signedness, or ID-length differences do not cause #3780.

SET @zv_missing_parent_id_columns = (
    SELECT GROUP_CONCAT(required_table.table_name ORDER BY required_table.table_name SEPARATOR ', ')
    FROM (
        SELECT 'organisers' AS table_name
        UNION ALL SELECT 'users'
    ) 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 CONCAT(
    COLUMN_TYPE,
    IF(CHARACTER_SET_NAME IS NULL, '', CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME, ' COLLATE ', COLLATION_NAME))
)
INTO @zv_organiser_id_definition
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'organisers' AND column_name = 'id';

SELECT CONCAT(
    COLUMN_TYPE,
    IF(CHARACTER_SET_NAME IS NULL, '', CONCAT(' CHARACTER SET ', CHARACTER_SET_NAME, ' COLLATE ', COLLATION_NAME))
)
INTO @zv_user_id_definition
FROM information_schema.columns
WHERE table_schema = DATABASE() AND table_name = 'users' AND column_name = 'id';

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 = 'organiser_box_office_settings') = 0,
        CONCAT(
        'CREATE TABLE `organiser_box_office_settings` (',
        '`organiser_id` ', @zv_organiser_id_definition, ' PRIMARY KEY,',
        '`payment_link_expiry_minutes` INT NOT NULL DEFAULT 60,',
        '`updated_by` ', @zv_user_id_definition, ' NULL,',
        '`created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,',
        '`updated_at` DATETIME NULL,',
        'CONSTRAINT `fk_box_office_settings_organiser` FOREIGN KEY (`organiser_id`) REFERENCES `organisers` (`id`) ON DELETE CASCADE,',
        'CONSTRAINT `fk_box_office_settings_updated_by` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`)',
        ') ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci'
        ),
        'SELECT 1'
    )
);
PREPARE zv_migration_stmt FROM @zv_migration_sql;
EXECUTE zv_migration_stmt;
DEALLOCATE PREPARE zv_migration_stmt;