-- Generates a database-wide, reviewable migration plan from
-- utf8mb4_0900_ai_ci to utf8mb4_unicode_ci.
--
-- This file intentionally DOES NOT execute the generated ALTER statements.
-- MySQL DDL auto-commits, and related foreign keys must be removed and restored
-- around parent/child column conversions. Run this file first, review every
-- result row, take a fresh backup, enter a maintenance window, and only then
-- execute the generated plan in ascending phase/order.
--
-- Safety properties:
--   * requires an exact selected-database acknowledgement;
--   * changes only columns whose current collation is utf8mb4_0900_ai_ci;
--   * leaves utf8mb4_general_ci and other legacy collations unchanged;
--   * preserves column type, nullability, scalar default, and comment;
--   * generates foreign-key drops before column changes and restores after;
--   * never disables FOREIGN_KEY_CHECKS;
--   * aborts on generated/default-generated/extra column definitions;
--   * aborts when an affected table is not InnoDB.

-- Replace this value with the DB_DATABASE/DB_DSN database name used by the
-- application being repaired. Do not infer it only from the phpMyAdmin URL.
SET @zv_expected_database = 'CHANGE_ME_TO_EXACT_DATABASE_NAME';
SET @zv_source_collation = 'utf8mb4_0900_ai_ci';
SET @zv_target_collation = 'utf8mb4_unicode_ci';
SET SESSION group_concat_max_len = 16777216;

SELECT DATABASE() AS selected_database,
       @zv_expected_database AS expected_database,
       @zv_source_collation AS source_collation,
       @zv_target_collation AS target_collation;

SET @zv_preflight_sql = IF(
    DATABASE() IS NOT NULL
    AND @zv_expected_database <> 'CHANGE_ME_TO_EXACT_DATABASE_NAME'
    AND BINARY DATABASE() = BINARY @zv_expected_database,
    'SELECT 1 AS selected_database_confirmed',
    'SELECT * FROM `information_schema`.`ZAVVION_COLLATION_ABORT_WRONG_DATABASE`'
);
PREPARE zv_preflight_stmt FROM @zv_preflight_sql;
EXECUTE zv_preflight_stmt;
DEALLOCATE PREPARE zv_preflight_stmt;

SET @zv_target_collation_exists = (
    SELECT COUNT(*)
    FROM information_schema.COLLATIONS
    WHERE COLLATION_NAME = @zv_target_collation
      AND CHARACTER_SET_NAME = 'utf8mb4'
);
SET @zv_preflight_sql = IF(
    @zv_target_collation_exists = 1,
    'SELECT 1 AS target_collation_confirmed',
    'SELECT * FROM `information_schema`.`ZAVVION_COLLATION_ABORT_TARGET_UNAVAILABLE`'
);
PREPARE zv_preflight_stmt FROM @zv_preflight_sql;
EXECUTE zv_preflight_stmt;
DEALLOCATE PREPARE zv_preflight_stmt;

-- Inventory: review table sizes and every affected column before using the
-- generated plan. TABLE_ROWS is an InnoDB estimate, not an exact count.
SELECT c.TABLE_NAME,
       t.ENGINE,
       t.TABLE_ROWS AS estimated_rows,
       ROUND((t.DATA_LENGTH + t.INDEX_LENGTH) / 1024 / 1024, 2) AS estimated_size_mb,
       c.ORDINAL_POSITION,
       c.COLUMN_NAME,
       c.COLUMN_TYPE,
       c.IS_NULLABLE,
       c.COLUMN_DEFAULT,
       c.EXTRA,
       c.GENERATION_EXPRESSION,
       c.COLLATION_NAME AS current_collation,
       @zv_target_collation AS planned_collation
FROM information_schema.COLUMNS c
INNER JOIN information_schema.TABLES t
    ON t.TABLE_SCHEMA = c.TABLE_SCHEMA
   AND t.TABLE_NAME = c.TABLE_NAME
   AND t.TABLE_TYPE = 'BASE TABLE'
WHERE c.TABLE_SCHEMA = DATABASE()
  AND c.COLLATION_NAME = @zv_source_collation
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION;

SET @zv_unsupported_columns = (
    SELECT GROUP_CONCAT(
        CONCAT(c.TABLE_NAME, '.', c.COLUMN_NAME)
        ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION
        SEPARATOR ', '
    )
    FROM information_schema.COLUMNS c
    WHERE c.TABLE_SCHEMA = DATABASE()
      AND c.COLLATION_NAME = @zv_source_collation
      AND (
          COALESCE(c.EXTRA, '') <> ''
          OR COALESCE(c.GENERATION_EXPRESSION, '') <> ''
      )
);
SELECT COALESCE(@zv_unsupported_columns, 'none') AS unsupported_affected_columns;

SET @zv_preflight_sql = IF(
    @zv_unsupported_columns IS NULL,
    'SELECT 1 AS affected_column_definitions_supported',
    'SELECT * FROM `information_schema`.`ZAVVION_COLLATION_ABORT_UNSUPPORTED_COLUMN_DEFINITION`'
);
PREPARE zv_preflight_stmt FROM @zv_preflight_sql;
EXECUTE zv_preflight_stmt;
DEALLOCATE PREPARE zv_preflight_stmt;

SET @zv_cross_schema_foreign_keys = (
    SELECT GROUP_CONCAT(
        DISTINCT CONCAT(k.TABLE_NAME, '.', k.CONSTRAINT_NAME, ' -> ',
                        k.REFERENCED_TABLE_SCHEMA, '.', k.REFERENCED_TABLE_NAME)
        ORDER BY k.TABLE_NAME, k.CONSTRAINT_NAME
        SEPARATOR ', '
    )
    FROM information_schema.KEY_COLUMN_USAGE k
    INNER JOIN information_schema.COLUMNS child_column
        ON child_column.TABLE_SCHEMA = k.TABLE_SCHEMA
       AND child_column.TABLE_NAME = k.TABLE_NAME
       AND child_column.COLUMN_NAME = k.COLUMN_NAME
    INNER JOIN information_schema.COLUMNS parent_column
        ON parent_column.TABLE_SCHEMA = k.REFERENCED_TABLE_SCHEMA
       AND parent_column.TABLE_NAME = k.REFERENCED_TABLE_NAME
       AND parent_column.COLUMN_NAME = k.REFERENCED_COLUMN_NAME
    WHERE k.CONSTRAINT_SCHEMA = DATABASE()
      AND k.REFERENCED_TABLE_NAME IS NOT NULL
      AND BINARY k.REFERENCED_TABLE_SCHEMA <> BINARY DATABASE()
      AND (
          child_column.COLLATION_NAME = @zv_source_collation
          OR parent_column.COLLATION_NAME = @zv_source_collation
      )
);
SELECT COALESCE(@zv_cross_schema_foreign_keys, 'none') AS affected_cross_schema_foreign_keys;

SET @zv_preflight_sql = IF(
    @zv_cross_schema_foreign_keys IS NULL,
    'SELECT 1 AS no_affected_cross_schema_foreign_keys',
    'SELECT * FROM `information_schema`.`ZAVVION_COLLATION_ABORT_CROSS_SCHEMA_FOREIGN_KEY`'
);
PREPARE zv_preflight_stmt FROM @zv_preflight_sql;
EXECUTE zv_preflight_stmt;
DEALLOCATE PREPARE zv_preflight_stmt;

SET @zv_unsupported_engines = (
    SELECT GROUP_CONCAT(DISTINCT CONCAT(t.TABLE_NAME, ':', COALESCE(t.ENGINE, 'NULL'))
                        ORDER BY t.TABLE_NAME SEPARATOR ', ')
    FROM information_schema.TABLES t
    LEFT JOIN information_schema.COLUMNS c
        ON c.TABLE_SCHEMA = t.TABLE_SCHEMA
       AND c.TABLE_NAME = t.TABLE_NAME
       AND c.COLLATION_NAME = @zv_source_collation
    WHERE t.TABLE_SCHEMA = DATABASE()
      AND t.TABLE_TYPE = 'BASE TABLE'
      AND (t.TABLE_COLLATION = @zv_source_collation OR c.COLUMN_NAME IS NOT NULL)
      AND COALESCE(t.ENGINE, '') <> 'InnoDB'
);
SELECT COALESCE(@zv_unsupported_engines, 'none') AS unsupported_affected_table_engines;

SET @zv_preflight_sql = IF(
    @zv_unsupported_engines IS NULL,
    'SELECT 1 AS affected_table_engines_supported',
    'SELECT * FROM `information_schema`.`ZAVVION_COLLATION_ABORT_NON_INNODB_TABLE`'
);
PREPARE zv_preflight_stmt FROM @zv_preflight_sql;
EXECUTE zv_preflight_stmt;
DEALLOCATE PREPARE zv_preflight_stmt;

-- Review all foreign keys that the plan must temporarily remove and restore.
SELECT k.TABLE_NAME AS child_table,
       k.CONSTRAINT_NAME,
       GROUP_CONCAT(k.COLUMN_NAME ORDER BY k.ORDINAL_POSITION SEPARATOR ', ') AS child_columns,
       k.REFERENCED_TABLE_NAME AS parent_table,
       GROUP_CONCAT(k.REFERENCED_COLUMN_NAME ORDER BY k.ORDINAL_POSITION SEPARATOR ', ') AS parent_columns,
       rc.UPDATE_RULE,
       rc.DELETE_RULE
FROM information_schema.KEY_COLUMN_USAGE k
INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
    ON rc.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
   AND rc.CONSTRAINT_NAME = k.CONSTRAINT_NAME
   AND rc.TABLE_NAME = k.TABLE_NAME
INNER JOIN information_schema.COLUMNS child_column
    ON child_column.TABLE_SCHEMA = k.TABLE_SCHEMA
   AND child_column.TABLE_NAME = k.TABLE_NAME
   AND child_column.COLUMN_NAME = k.COLUMN_NAME
INNER JOIN information_schema.COLUMNS parent_column
    ON parent_column.TABLE_SCHEMA = k.REFERENCED_TABLE_SCHEMA
   AND parent_column.TABLE_NAME = k.REFERENCED_TABLE_NAME
   AND parent_column.COLUMN_NAME = k.REFERENCED_COLUMN_NAME
WHERE k.CONSTRAINT_SCHEMA = DATABASE()
  AND k.REFERENCED_TABLE_NAME IS NOT NULL
  AND (
      child_column.COLLATION_NAME = @zv_source_collation
      OR parent_column.COLLATION_NAME = @zv_source_collation
  )
GROUP BY k.TABLE_NAME,
         k.CONSTRAINT_NAME,
         k.REFERENCED_TABLE_NAME,
         rc.UPDATE_RULE,
         rc.DELETE_RULE
ORDER BY k.TABLE_NAME, k.CONSTRAINT_NAME;

-- Generated migration plan. Copy ONLY the migration_sql values into a new SQL
-- tab after review and backup, preserving this phase order:
--   10 drop affected foreign keys
--   20 modify only utf8mb4_0900_ai_ci columns
--   30 change affected table defaults without converting other collations
--   40 restore foreign keys with their original names and rules
WITH affected_foreign_keys AS (
    SELECT k.TABLE_SCHEMA,
           k.TABLE_NAME,
           k.CONSTRAINT_NAME,
           k.REFERENCED_TABLE_SCHEMA,
           k.REFERENCED_TABLE_NAME,
           rc.UPDATE_RULE,
           rc.DELETE_RULE,
           GROUP_CONCAT(CONCAT('`', REPLACE(k.COLUMN_NAME, '`', '``'), '`')
                        ORDER BY k.ORDINAL_POSITION SEPARATOR ', ') AS child_columns_sql,
           GROUP_CONCAT(CONCAT('`', REPLACE(k.REFERENCED_COLUMN_NAME, '`', '``'), '`')
                        ORDER BY k.ORDINAL_POSITION SEPARATOR ', ') AS parent_columns_sql
    FROM information_schema.KEY_COLUMN_USAGE k
    INNER JOIN information_schema.REFERENTIAL_CONSTRAINTS rc
        ON rc.CONSTRAINT_SCHEMA = k.CONSTRAINT_SCHEMA
       AND rc.CONSTRAINT_NAME = k.CONSTRAINT_NAME
       AND rc.TABLE_NAME = k.TABLE_NAME
    INNER JOIN information_schema.COLUMNS child_column
        ON child_column.TABLE_SCHEMA = k.TABLE_SCHEMA
       AND child_column.TABLE_NAME = k.TABLE_NAME
       AND child_column.COLUMN_NAME = k.COLUMN_NAME
    INNER JOIN information_schema.COLUMNS parent_column
        ON parent_column.TABLE_SCHEMA = k.REFERENCED_TABLE_SCHEMA
       AND parent_column.TABLE_NAME = k.REFERENCED_TABLE_NAME
       AND parent_column.COLUMN_NAME = k.REFERENCED_COLUMN_NAME
    WHERE k.CONSTRAINT_SCHEMA = DATABASE()
      AND k.REFERENCED_TABLE_NAME IS NOT NULL
      AND (
          child_column.COLLATION_NAME = @zv_source_collation
          OR parent_column.COLLATION_NAME = @zv_source_collation
      )
    GROUP BY k.TABLE_SCHEMA,
             k.TABLE_NAME,
             k.CONSTRAINT_NAME,
             k.REFERENCED_TABLE_SCHEMA,
             k.REFERENCED_TABLE_NAME,
             rc.UPDATE_RULE,
             rc.DELETE_RULE
),
column_changes AS (
    SELECT c.TABLE_SCHEMA,
           c.TABLE_NAME,
           GROUP_CONCAT(
               CONCAT(
                   'MODIFY COLUMN `', REPLACE(c.COLUMN_NAME, '`', '``'), '` ',
                   c.COLUMN_TYPE,
                   ' CHARACTER SET utf8mb4 COLLATE ', @zv_target_collation,
                   IF(c.IS_NULLABLE = 'YES', ' NULL', ' NOT NULL'),
                   CASE
                       WHEN c.COLUMN_DEFAULT IS NULL
                           THEN IF(c.IS_NULLABLE = 'YES', ' DEFAULT NULL', '')
                       ELSE CONCAT(' DEFAULT ', QUOTE(c.COLUMN_DEFAULT))
                   END,
                   CASE
                       WHEN c.COLUMN_COMMENT = '' THEN ''
                       ELSE CONCAT(' COMMENT ', QUOTE(c.COLUMN_COMMENT))
                   END
               )
               ORDER BY c.ORDINAL_POSITION
               SEPARATOR ', '
           ) AS modify_columns_sql
    FROM information_schema.COLUMNS c
    WHERE c.TABLE_SCHEMA = DATABASE()
      AND c.COLLATION_NAME = @zv_source_collation
    GROUP BY c.TABLE_SCHEMA, c.TABLE_NAME
),
migration_plan AS (
    SELECT 10 AS phase,
           CONCAT(fk.TABLE_NAME, '.', fk.CONSTRAINT_NAME) AS object_name,
           CONCAT(
               'ALTER TABLE `', REPLACE(fk.TABLE_SCHEMA, '`', '``'), '`.`',
               REPLACE(fk.TABLE_NAME, '`', '``'), '` DROP FOREIGN KEY `',
               REPLACE(fk.CONSTRAINT_NAME, '`', '``'), '`;'
           ) AS migration_sql
    FROM affected_foreign_keys fk

    UNION ALL

    SELECT 20 AS phase,
           changes.TABLE_NAME AS object_name,
           CONCAT(
               'ALTER TABLE `', REPLACE(changes.TABLE_SCHEMA, '`', '``'), '`.`',
               REPLACE(changes.TABLE_NAME, '`', '``'), '` ',
               changes.modify_columns_sql, ';'
           ) AS migration_sql
    FROM column_changes changes

    UNION ALL

    SELECT 30 AS phase,
           t.TABLE_NAME AS object_name,
           CONCAT(
               'ALTER TABLE `', REPLACE(t.TABLE_SCHEMA, '`', '``'), '`.`',
               REPLACE(t.TABLE_NAME, '`', '``'),
               '` DEFAULT CHARACTER SET utf8mb4 COLLATE ', @zv_target_collation, ';'
           ) AS migration_sql
    FROM information_schema.TABLES t
    WHERE t.TABLE_SCHEMA = DATABASE()
      AND t.TABLE_TYPE = 'BASE TABLE'
      AND t.TABLE_COLLATION = @zv_source_collation

    UNION ALL

    SELECT 40 AS phase,
           CONCAT(fk.TABLE_NAME, '.', fk.CONSTRAINT_NAME) AS object_name,
           CONCAT(
               'ALTER TABLE `', REPLACE(fk.TABLE_SCHEMA, '`', '``'), '`.`',
               REPLACE(fk.TABLE_NAME, '`', '``'), '` ADD CONSTRAINT `',
               REPLACE(fk.CONSTRAINT_NAME, '`', '``'), '` FOREIGN KEY (',
               fk.child_columns_sql, ') REFERENCES `',
               REPLACE(fk.REFERENCED_TABLE_SCHEMA, '`', '``'), '`.`',
               REPLACE(fk.REFERENCED_TABLE_NAME, '`', '``'), '` (',
               fk.parent_columns_sql, ') ON UPDATE ', fk.UPDATE_RULE,
               ' ON DELETE ', fk.DELETE_RULE, ';'
           ) AS migration_sql
    FROM affected_foreign_keys fk
)
SELECT phase, object_name, migration_sql
FROM migration_plan
ORDER BY phase, object_name;

-- Verification queries to run after the generated phase-10..40 plan succeeds.
SELECT COUNT(*) AS remaining_source_collation_columns
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLLATION_NAME = @zv_source_collation;

SELECT TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_TYPE = 'BASE TABLE'
  AND TABLE_COLLATION = @zv_source_collation
ORDER BY TABLE_NAME;