Symfony Profiler

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;

/**
 * dtb_maintenance_schoolテーブルのインデックスと外部キーを再作成
 */
final class Version20251119162204 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'dtb_maintenance_schoolテーブルのインデックスと外部キー制約を正しい順序で再作成';
    }

    public function up(Schema $schema): void
    {
        // MySQLの場合のみ実行
        if ($this->connection->getDatabasePlatform()->getName() !== 'mysql') {
            return;
        }

        // 既存の外部キー制約を確認して削除
        $foreignKeys = $this->connection->fetchAllAssociative("
            SELECT CONSTRAINT_NAME 
            FROM information_schema.TABLE_CONSTRAINTS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'dtb_maintenance_school' 
            AND CONSTRAINT_TYPE = 'FOREIGN KEY'
        ");
        
        foreach ($foreignKeys as $fk) {
            $this->addSql("ALTER TABLE dtb_maintenance_school DROP FOREIGN KEY " . $fk['CONSTRAINT_NAME']);
        }
        
        // 古いインデックスを削除
        $indexes = $this->connection->fetchAllAssociative("
            SELECT DISTINCT INDEX_NAME 
            FROM information_schema.STATISTICS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'dtb_maintenance_school' 
            AND INDEX_NAME IN ('idx_maintenance_settings', 'idx_school', 'IDX_C00A091853B3712F', 'IDX_C00A0918C32A47EE')
        ");
        
        foreach ($indexes as $index) {
            $this->addSql("DROP INDEX " . $index['INDEX_NAME'] . " ON dtb_maintenance_school");
        }
        
        // 無効な参照データをクリーンアップ（maintenance_settings_idが存在しないレコードを削除）
        $this->addSql("
            DELETE FROM dtb_maintenance_school 
            WHERE maintenance_settings_id IS NOT NULL 
            AND maintenance_settings_id NOT IN (SELECT id FROM dtb_maintenance_settings)
        ");
        
        // 無効な参照データをクリーンアップ（school_idが存在しないレコードを削除）
        $this->addSql("
            DELETE FROM dtb_maintenance_school 
            WHERE school_id IS NOT NULL 
            AND school_id NOT IN (SELECT school_id FROM dtb_school)
        ");
        
        // 新しいインデックスを作成（存在しない場合のみ）
        $existingIndexes = $this->connection->fetchAllAssociative("
            SELECT DISTINCT INDEX_NAME 
            FROM information_schema.STATISTICS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'dtb_maintenance_school' 
            AND INDEX_NAME = 'IDX_C00A091853B3712F'
        ");
        
        if (empty($existingIndexes)) {
            $this->addSql("CREATE INDEX IDX_C00A091853B3712F ON dtb_maintenance_school (maintenance_settings_id)");
        }
        
        $existingIndexes2 = $this->connection->fetchAllAssociative("
            SELECT DISTINCT INDEX_NAME 
            FROM information_schema.STATISTICS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = 'dtb_maintenance_school' 
            AND INDEX_NAME = 'IDX_C00A0918C32A47EE'
        ");
        
        if (empty($existingIndexes2)) {
            $this->addSql("CREATE INDEX IDX_C00A0918C32A47EE ON dtb_maintenance_school (school_id)");
        }
        
        // 外部キー制約を再作成
        $this->addSql("
            ALTER TABLE dtb_maintenance_school 
            ADD CONSTRAINT FK_C00A091853B3712F 
            FOREIGN KEY (maintenance_settings_id) REFERENCES dtb_maintenance_settings (id)
        ");
        
        $this->addSql("
            ALTER TABLE dtb_maintenance_school 
            ADD CONSTRAINT FK_C00A0918C32A47EE 
            FOREIGN KEY (school_id) REFERENCES dtb_school (school_id)
        ");
    }

    public function down(Schema $schema): void
    {
        // 必要に応じてロールバック処理を記述
    }
}
app/DoctrineMigrations/Version20251119162204.php line 1
