<?php

declare(strict_types=1);

namespace CpCreation\VitiCore\Bridge\Doctrine\Migrations;

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

final class Version20260723200000 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Rend multilingue le complément à la politique de gestion des avis.';
    }

    public function up(Schema $schema): void
    {
        $this->abortIf(!$this->connection->getDatabasePlatform() instanceof PostgreSQLPlatform, 'Cette migration nécessite PostgreSQL.');

        $this->addSql(<<<'SQL'
            CREATE TABLE marketing_review_request_config_translation (
                id UUID NOT NULL,
                translatable_id UUID DEFAULT NULL,
                policy_additional_content TEXT DEFAULT NULL,
                locale VARCHAR(5) NOT NULL,
                PRIMARY KEY(id)
            )
            SQL);
        $this->addSql('CREATE INDEX IDX_916637702C2AC5D3 ON marketing_review_request_config_translation (translatable_id)');
        $this->addSql('CREATE UNIQUE INDEX marketing_review_request_config_translation_unique_translation ON marketing_review_request_config_translation (translatable_id, locale)');
        $this->addSql("COMMENT ON COLUMN marketing_review_request_config_translation.id IS '(DC2Type:uuid)'");
        $this->addSql("COMMENT ON COLUMN marketing_review_request_config_translation.translatable_id IS '(DC2Type:uuid)'");
        $this->addSql('ALTER TABLE marketing_review_request_config_translation ADD CONSTRAINT FK_916637702C2AC5D3 FOREIGN KEY (translatable_id) REFERENCES marketing_review_request_configuration (id) ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE');
        $this->addSql(<<<'SQL'
            INSERT INTO marketing_review_request_config_translation (
                id,
                translatable_id,
                policy_additional_content,
                locale
            )
            SELECT
                md5(id::text || ':fr')::uuid,
                id,
                policy_additional_content,
                'fr'
            FROM marketing_review_request_configuration
            WHERE policy_additional_content IS NOT NULL
            SQL);
        $this->addSql('ALTER TABLE marketing_review_request_configuration DROP policy_additional_content');
    }

    public function down(Schema $schema): void
    {
        $this->abortIf(!$this->connection->getDatabasePlatform() instanceof PostgreSQLPlatform, 'Cette migration nécessite PostgreSQL.');

        $this->addSql('ALTER TABLE marketing_review_request_configuration ADD policy_additional_content TEXT DEFAULT NULL');
        $this->addSql(<<<'SQL'
            UPDATE marketing_review_request_configuration configuration
            SET policy_additional_content = translation.policy_additional_content
            FROM marketing_review_request_config_translation translation
            WHERE translation.translatable_id = configuration.id
              AND translation.locale = 'fr'
            SQL);
        $this->addSql('DROP TABLE marketing_review_request_config_translation');
    }
}
