migrations/Version20241120090000.php line 1

<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

final class Version20241120090000 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Add compliance data model and seed FGSES programs';
    }

    public function up(Schema $schema): void
    {
        if ('mysql' !== $this->connection->getDatabasePlatform()->getName()) {
            return;
        }

        $this->addSql('CREATE TABLE program (id INT AUTO_INCREMENT NOT NULL, code VARCHAR(50) NOT NULL, name_fr VARCHAR(255) NOT NULL, name_en VARCHAR(255) NOT NULL, level VARCHAR(20) NOT NULL, active TINYINT(1) NOT NULL, created_at DATETIME NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', updated_at DATETIME DEFAULT NULL COMMENT \'(DC2Type:datetime_immutable)\', UNIQUE INDEX UNIQ_PROGRAM_CODE (code), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');

        $this->addSql('CREATE TABLE document_type (id INT AUTO_INCREMENT NOT NULL, code VARCHAR(100) NOT NULL, label VARCHAR(255) NOT NULL, category VARCHAR(50) NOT NULL, description LONGTEXT DEFAULT NULL, is_active TINYINT(1) NOT NULL, created_at DATETIME NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', updated_at DATETIME DEFAULT NULL COMMENT \'(DC2Type:datetime_immutable)\', UNIQUE INDEX UNIQ_DOCUMENT_TYPE_CODE (code), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');

        $this->addSql('CREATE TABLE program_document_requirement (id INT AUTO_INCREMENT NOT NULL, program_id INT NOT NULL, document_type_id INT NOT NULL, required TINYINT(1) NOT NULL, order_index INT NOT NULL, specific_instructions LONGTEXT DEFAULT NULL, auto_checks JSON DEFAULT NULL, created_at DATETIME NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', updated_at DATETIME DEFAULT NULL COMMENT \'(DC2Type:datetime_immutable)\', INDEX IDX_PDR_PROGRAM (program_id), INDEX IDX_PDR_DOCUMENT_TYPE (document_type_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');

        $this->addSql('CREATE TABLE candidate_submission (id INT AUTO_INCREMENT NOT NULL, candidate_id INT NOT NULL, program_id INT NOT NULL, status VARCHAR(30) NOT NULL, created_at DATETIME NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', updated_at DATETIME DEFAULT NULL COMMENT \'(DC2Type:datetime_immutable)\', INDEX IDX_CANDIDATE_SUBMISSION_CANDIDATE (candidate_id), INDEX IDX_CANDIDATE_SUBMISSION_PROGRAM (program_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');

        $this->addSql('CREATE TABLE candidate_document (id INT AUTO_INCREMENT NOT NULL, submission_id INT NOT NULL, document_type_id INT NOT NULL, file_path VARCHAR(500) NOT NULL, mime_type VARCHAR(150) NOT NULL, size BIGINT NOT NULL, uploaded_at DATETIME NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', status VARCHAR(30) NOT NULL, rejection_reason LONGTEXT DEFAULT NULL, last_auto_check_status VARCHAR(20) NOT NULL, last_auto_check_details JSON DEFAULT NULL, updated_at DATETIME DEFAULT NULL COMMENT \'(DC2Type:datetime_immutable)\', INDEX IDX_CANDIDATE_DOCUMENT_SUBMISSION (submission_id), INDEX IDX_CANDIDATE_DOCUMENT_TYPE (document_type_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');

        $this->addSql('CREATE TABLE archive_entry (id INT AUTO_INCREMENT NOT NULL, candidate_id INT NOT NULL, program_id INT NOT NULL, reference VARCHAR(100) NOT NULL, path VARCHAR(500) NOT NULL, tags JSON DEFAULT NULL, created_at DATETIME NOT NULL COMMENT \'(DC2Type:datetime_immutable)\', UNIQUE INDEX UNIQ_ARCHIVE_ENTRY_REFERENCE (reference), INDEX IDX_ARCHIVE_ENTRY_CANDIDATE (candidate_id), INDEX IDX_ARCHIVE_ENTRY_PROGRAM (program_id), PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');

        $this->addSql('ALTER TABLE program_document_requirement ADD CONSTRAINT FK_PDR_PROGRAM FOREIGN KEY (program_id) REFERENCES program (id)');
        $this->addSql('ALTER TABLE program_document_requirement ADD CONSTRAINT FK_PDR_DOCUMENT_TYPE FOREIGN KEY (document_type_id) REFERENCES document_type (id)');

        $this->addSql('ALTER TABLE candidate_submission ADD CONSTRAINT FK_CANDIDATE_SUBMISSION_CANDIDATE FOREIGN KEY (candidate_id) REFERENCES submission (id)');
        $this->addSql('ALTER TABLE candidate_submission ADD CONSTRAINT FK_CANDIDATE_SUBMISSION_PROGRAM FOREIGN KEY (program_id) REFERENCES program (id)');

        $this->addSql('ALTER TABLE candidate_document ADD CONSTRAINT FK_CANDIDATE_DOCUMENT_SUBMISSION FOREIGN KEY (submission_id) REFERENCES candidate_submission (id)');
        $this->addSql('ALTER TABLE candidate_document ADD CONSTRAINT FK_CANDIDATE_DOCUMENT_TYPE FOREIGN KEY (document_type_id) REFERENCES document_type (id)');

        $this->addSql('ALTER TABLE archive_entry ADD CONSTRAINT FK_ARCHIVE_ENTRY_CANDIDATE FOREIGN KEY (candidate_id) REFERENCES submission (id)');
        $this->addSql('ALTER TABLE archive_entry ADD CONSTRAINT FK_ARCHIVE_ENTRY_PROGRAM FOREIGN KEY (program_id) REFERENCES program (id)');

        $now = (new \DateTimeImmutable())->format('Y-m-d H:i:s');
        $this->addSql("INSERT INTO program (code, name_fr, name_en, level, active, created_at) VALUES\n            ('LIC-ECO-APP', 'Licence en Économie Appliquée', 'Licence en Économie Appliquée', 'licence', 1, '$now'),\n            ('LIC-SCI-POL', 'Licence en Science Politique', 'Licence en Science Politique', 'licence', 1, '$now'),\n            ('LIC-REL-INT', 'Licence en Relations Internationales', 'Licence en Relations Internationales', 'licence', 1, '$now'),\n            ('LIC-SCS', 'Licence en Sciences Comportementales et Sociales pour les Politiques Publiques', 'Licence en Sciences Comportementales et Sociales pour les Politiques Publiques', 'licence', 1, '$now'),\n            ('LIC-DROIT-PUB', 'Licence en Droit Public', 'Licence en Droit Public', 'licence', 1, '$now'),\n            ('MAS-EAPP', 'Master in Economic Analysis and Public Policies', 'Master in Economic Analysis and Public Policies', 'master', 1, '$now'),\n            ('MAS-QUANT-ECO', 'Master in Quantitative Economics', 'Master in Quantitative Economics', 'master', 1, '$now'),\n            ('MAS-POL-SCI', 'Master in Political Science', 'Master in Political Science', 'master', 1, '$now'),\n            ('MAS-GLOBAL-AFF', 'Master in Global Affairs', 'Master in Global Affairs', 'master', 1, '$now'),\n            ('MAS-BSS-PP', 'Master in Behavioral and Social Sciences for Public Policy', 'Master in Behavioral and Social Sciences for Public Policy', 'master', 1, '$now'),\n            ('PHD-ECO', 'PhD in Economics', 'PhD in Economics', 'phd', 1, '$now'),\n            ('PHD-POL-SCI', 'PhD in Political Science', 'PhD in Political Science', 'phd', 1, '$now'),\n            ('PHD-INT-REL', 'PhD in International Relations', 'PhD in International Relations', 'phd', 1, '$now')");
    }

    public function down(Schema $schema): void
    {
        if ('mysql' !== $this->connection->getDatabasePlatform()->getName()) {
            return;
        }

        $this->addSql('SET FOREIGN_KEY_CHECKS = 0');
        $this->addSql('DROP TABLE archive_entry');
        $this->addSql('DROP TABLE candidate_document');
        $this->addSql('DROP TABLE candidate_submission');
        $this->addSql('DROP TABLE program_document_requirement');
        $this->addSql('DROP TABLE document_type');
        $this->addSql('DROP TABLE program');
        $this->addSql('SET FOREIGN_KEY_CHECKS = 1');
    }
}