#!/bin/bash

# Configurações
USUARIO="root"
SENHA="toninho13"
BANCO="qualifica_brasil"
ARQUIVO_OUTPUT="ajustes_gerados.sql"

echo "🧠 Gerando comandos para ajustar collation utf8mb4_general_ci → utf8mb4_0900_ai_ci..."

# Remover FK
echo "-- DROP FOREIGN KEYS" > $ARQUIVO_OUTPUT
mysql -u $USUARIO -p$SENHA -D $BANCO -Nse "
SELECT CONCAT(
    'ALTER TABLE \`', kcu.TABLE_NAME, '\` DROP FOREIGN KEY \`', rc.CONSTRAINT_NAME, '\`;'
)
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
WHERE rc.CONSTRAINT_SCHEMA = '$BANCO'
  AND kcu.REFERENCED_TABLE_SCHEMA = '$BANCO'
  AND kcu.REFERENCED_COLUMN_NAME IN (
      SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = '$BANCO'
        AND COLLATION_NAME = 'utf8mb4_general_ci'
  );
" >> $ARQUIVO_OUTPUT

# ALTER TABLE com collation corrigido
echo -e "\n-- ALTER TABLEs com nova collation" >> $ARQUIVO_OUTPUT
mysql -u $USUARIO -p$SENHA -D $BANCO -Nse "
SELECT CONCAT(
    'ALTER TABLE \`', TABLE_NAME, '\` MODIFY \`', COLUMN_NAME, '\` ',
    COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci',
    IF(IS_NULLABLE = 'NO', ' NOT NULL', ''),
    CASE
        WHEN COLUMN_DEFAULT IS NULL THEN ''
        WHEN DATA_TYPE IN ('varchar', 'char', 'text') THEN CONCAT(' DEFAULT \'', REPLACE(COLUMN_DEFAULT, '\\\\', '\\\\\\\\'), '\'')
        ELSE CONCAT(' DEFAULT ', COLUMN_DEFAULT)
    END,
    ';'
)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = '$BANCO'
  AND COLLATION_NAME = 'utf8mb4_general_ci';
" >> $ARQUIVO_OUTPUT

# Recriar FKs
echo -e "\n-- RECRIAR FOREIGN KEYS" >> $ARQUIVO_OUTPUT
mysql -u $USUARIO -p$SENHA -D $BANCO -Nse "
SELECT CONCAT(
    'ALTER TABLE \`', kcu.TABLE_NAME, '\` ADD CONSTRAINT \`', rc.CONSTRAINT_NAME,
    '\` FOREIGN KEY (\`', kcu.COLUMN_NAME, '\`) REFERENCES \`', kcu.REFERENCED_TABLE_NAME,
    '\`(\`', kcu.REFERENCED_COLUMN_NAME, '\`);'
)
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
    ON rc.CONSTRAINT_NAME = kcu.CONSTRAINT_NAME
    AND rc.CONSTRAINT_SCHEMA = kcu.CONSTRAINT_SCHEMA
WHERE rc.CONSTRAINT_SCHEMA = '$BANCO'
  AND kcu.REFERENCED_TABLE_SCHEMA = '$BANCO'
  AND kcu.REFERENCED_COLUMN_NAME IN (
      SELECT COLUMN_NAME
      FROM INFORMATION_SCHEMA.COLUMNS
      WHERE TABLE_SCHEMA = '$BANCO'
        AND COLLATION_NAME = 'utf8mb4_general_ci'
  );
" >> $ARQUIVO_OUTPUT

echo "✅ Comandos gerados em para adaptar a base qualifica_brasil para as collations do MySQL 8.0: $ARQUIVO_OUTPUT"
echo "📝 Revise o arquivo antes de executar: mysql -u $USUARIO -p$SENHA $BANCO < $ARQUIVO_OUTPUT"

