app/DoctrineMigrations/Version20250922000000.php line 1

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

/**
 * メンテナンス管理機能のためのテーブルを作成するマイグレーション
 * dtb_maintenance_settings - メンテナンス設定テーブル
 * dtb_maintenance_school - メンテナンス対象学校テーブル
 */
final class Version20250922000000 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'メンテナンス管理機能のテーブル(dtb_maintenance_settings, dtb_maintenance_school)を作成';
    }

    public function up(Schema $schema): void
    {
        $schemaManager = $this->connection->createSchemaManager();
        
        // dtb_maintenance_settingsテーブルの作成
        if (!$schemaManager->tablesExist(['dtb_maintenance_settings'])) {
            $this->addSql('
                CREATE TABLE dtb_maintenance_settings (
                    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
                    status INT UNSIGNED DEFAULT 0 NOT NULL,
                    target_type INT UNSIGNED DEFAULT 1 NOT NULL,
                    title VARCHAR(255) DEFAULT NULL,
                    content TEXT DEFAULT NULL,
                    start_time DATETIME DEFAULT NULL,
                    end_time DATETIME DEFAULT NULL,
                    create_date DATETIME NOT NULL,
                    update_date DATETIME NOT NULL,
                    PRIMARY KEY(id)
                ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = InnoDB
            ');
        }

        // dtb_maintenance_schoolテーブルの作成
        if (!$schemaManager->tablesExist(['dtb_maintenance_school'])) {
            $this->addSql('
                CREATE TABLE dtb_maintenance_school (
                    id INT UNSIGNED AUTO_INCREMENT NOT NULL,
                    maintenance_settings_id INT UNSIGNED DEFAULT NULL,
                    school_id INT UNSIGNED DEFAULT NULL,
                    title VARCHAR(255) DEFAULT NULL,
                    content TEXT DEFAULT NULL,
                    start_time DATETIME DEFAULT NULL,
                    end_time DATETIME DEFAULT NULL,
                    create_date DATETIME NOT NULL,
                    update_date DATETIME NOT NULL,
                    INDEX IDX_maintenance_settings (maintenance_settings_id),
                    INDEX IDX_school (school_id),
                    PRIMARY KEY(id)
                ) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = InnoDB
            ');
        }

        // 外部キー制約の追加
        if ($schemaManager->tablesExist(['dtb_maintenance_settings', 'dtb_maintenance_school'])) {
            $foreignKeys = $schemaManager->listTableForeignKeys('dtb_maintenance_school');
            $foreignKeyNames = array_map(function($fk) {
                return strtolower($fk->getName());
            }, $foreignKeys);
            
            // maintenance_settings_idの外部キー制約
            if (!in_array('fk_maintenance_settings_id', $foreignKeyNames)) {
                // 整合性のないデータをクリーンアップ
                $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)
                ');
                
                $this->addSql('
                    ALTER TABLE dtb_maintenance_school 
                    ADD CONSTRAINT FK_maintenance_settings_id 
                    FOREIGN KEY (maintenance_settings_id) 
                    REFERENCES dtb_maintenance_settings (id) 
                    ON DELETE CASCADE
                ');
            }

            // school_idの外部キー制約 - dtb_schoolテーブルが存在する場合のみ
            if ($schemaManager->tablesExist(['dtb_school']) && !in_array('fk_maintenance_school_id', $foreignKeyNames)) {
                // dtb_schoolのidカラムの型を確認
                $schoolColumns = $schemaManager->listTableColumns('dtb_school');
                if (isset($schoolColumns['id'])) {
                    // 整合性のないデータをクリーンアップ
                    $this->addSql('
                        DELETE FROM dtb_maintenance_school 
                        WHERE school_id IS NOT NULL 
                        AND school_id NOT IN (SELECT id FROM dtb_school)
                    ');
                    
                    $this->addSql('
                        ALTER TABLE dtb_maintenance_school 
                        ADD CONSTRAINT FK_maintenance_school_id 
                        FOREIGN KEY (school_id) 
                        REFERENCES dtb_school (id) 
                        ON DELETE CASCADE
                    ');
                }
            }
        }
    }

    public function down(Schema $schema): void
    {
        $schemaManager = $this->connection->createSchemaManager();
        
        // 外部キー制約の削除
        if ($schemaManager->tablesExist(['dtb_maintenance_school'])) {
            $foreignKeys = $schemaManager->listTableForeignKeys('dtb_maintenance_school');
            $foreignKeyNames = array_map(function($fk) {
                return strtolower($fk->getName());
            }, $foreignKeys);
            
            if (in_array('fk_maintenance_settings_id', $foreignKeyNames)) {
                $this->addSql('ALTER TABLE dtb_maintenance_school DROP FOREIGN KEY FK_maintenance_settings_id');
            }
            
            if (in_array('fk_maintenance_school_id', $foreignKeyNames)) {
                $this->addSql('ALTER TABLE dtb_maintenance_school DROP FOREIGN KEY FK_maintenance_school_id');
            }
        }
        
        // テーブルの削除
        if ($schemaManager->tablesExist(['dtb_maintenance_school'])) {
            $this->addSql('DROP TABLE dtb_maintenance_school');
        }
        
        if ($schemaManager->tablesExist(['dtb_maintenance_settings'])) {
            $this->addSql('DROP TABLE dtb_maintenance_settings');
        }
    }
}