<?php

declare(strict_types=1);

namespace DoctrineMigrations;

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

final class Version20260402055321 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Tables pour le mode compétences : référentiels, compétences, niveaux personnalisables, évaluations par compétences et résultats élèves';
    }

    public function up(Schema $schema): void
    {
        $this->addSql(<<<'SQL'
            CREATE TABLE competency_frameworks (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                tenant_id UUID NOT NULL,
                name VARCHAR(255) NOT NULL,
                is_default BOOLEAN NOT NULL DEFAULT false,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
            )
            SQL);

        $this->addSql(<<<'SQL'
            CREATE INDEX idx_competency_frameworks_tenant ON competency_frameworks(tenant_id)
            SQL);

        $this->addSql(<<<'SQL'
            CREATE TABLE competencies (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                framework_id UUID NOT NULL REFERENCES competency_frameworks(id) ON DELETE CASCADE,
                code VARCHAR(50) NOT NULL,
                name VARCHAR(255) NOT NULL,
                description TEXT,
                parent_id UUID REFERENCES competencies(id) ON DELETE SET NULL,
                sort_order INT NOT NULL DEFAULT 0
            )
            SQL);

        $this->addSql(<<<'SQL'
            CREATE INDEX idx_competencies_framework ON competencies(framework_id)
            SQL);

        $this->addSql(<<<'SQL'
            CREATE TABLE competency_levels (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                tenant_id UUID NOT NULL,
                code VARCHAR(20) NOT NULL,
                name VARCHAR(100) NOT NULL,
                color VARCHAR(7),
                sort_order INT NOT NULL DEFAULT 0,
                UNIQUE (tenant_id, code)
            )
            SQL);

        $this->addSql(<<<'SQL'
            CREATE INDEX idx_competency_levels_tenant ON competency_levels(tenant_id)
            SQL);

        $this->addSql(<<<'SQL'
            CREATE TABLE competency_evaluations (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                evaluation_id UUID NOT NULL REFERENCES evaluations(id) ON DELETE CASCADE,
                competency_id UUID NOT NULL REFERENCES competencies(id) ON DELETE CASCADE,
                UNIQUE (evaluation_id, competency_id)
            )
            SQL);

        $this->addSql(<<<'SQL'
            CREATE INDEX idx_competency_evaluations_evaluation ON competency_evaluations(evaluation_id)
            SQL);

        $this->addSql(<<<'SQL'
            CREATE TABLE student_competency_results (
                id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
                tenant_id UUID NOT NULL,
                competency_evaluation_id UUID NOT NULL REFERENCES competency_evaluations(id) ON DELETE CASCADE,
                student_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
                level_code VARCHAR(20) NOT NULL,
                created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
                UNIQUE (competency_evaluation_id, student_id)
            )
            SQL);

        $this->addSql(<<<'SQL'
            CREATE INDEX idx_competency_results_student ON student_competency_results(student_id)
            SQL);

        $this->addSql(<<<'SQL'
            CREATE INDEX idx_competency_results_tenant ON student_competency_results(tenant_id)
            SQL);
    }

    public function down(Schema $schema): void
    {
        $this->addSql('DROP TABLE IF EXISTS student_competency_results');
        $this->addSql('DROP TABLE IF EXISTS competency_evaluations');
        $this->addSql('DROP TABLE IF EXISTS competency_levels');
        $this->addSql('DROP TABLE IF EXISTS competencies');
        $this->addSql('DROP TABLE IF EXISTS competency_frameworks');
    }
}