<?php

declare(strict_types=1);

/*
 * This file is part of the VITICORE2 package.
 *
 * (c) CP Creation <web@cpcreation.fr>
 *
 * For the full copyright and license information, please view the LICENSE
 * file that was distributed with this source code.
 */

namespace CpCreation\VitiCore\Bridge\Doctrine\Migrations;

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

final class Version20260722233000 extends AbstractMigration
{
    public function getDescription(): string
    {
        return 'Ajoute les codes de type, les SKU de variant prioritaires et le snapshot SKU des lignes de commande.';
    }

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

        $this->addSql('ALTER TABLE product_type ADD code VARCHAR(64) DEFAULT NULL');
        $this->addSql(<<<'SQL'
            WITH preferred_translation AS (
                SELECT DISTINCT ON (translatable_id)
                    translatable_id,
                    UPPER(LEFT(slug, 64)) AS code
                FROM product_type_translation
                WHERE slug IS NOT NULL AND BTRIM(slug) <> ''
                ORDER BY translatable_id, CASE WHEN locale = 'fr' THEN 0 ELSE 1 END, locale
            )
            UPDATE product_type AS product_type
            SET code = preferred_translation.code
            FROM preferred_translation
            WHERE preferred_translation.translatable_id = product_type.id
            SQL);
        $this->addSql("UPDATE product_type SET code = 'TYPE-' || UPPER(LEFT(REPLACE(id::text, '-', ''), 12)) WHERE code IS NULL OR BTRIM(code) = ''");
        $this->addSql(<<<'SQL'
            WITH duplicate_codes AS (
                SELECT code
                FROM product_type
                GROUP BY code
                HAVING COUNT(*) > 1
            )
            UPDATE product_type
            SET code = LEFT(product_type.code, 46) || '-' || UPPER(LEFT(REPLACE(product_type.id::text, '-', ''), 12))
            WHERE product_type.code IN (SELECT code FROM duplicate_codes)
            SQL);
        $this->addSql('ALTER TABLE product_type ALTER code SET NOT NULL');
        $this->addSql('CREATE UNIQUE INDEX uniq_product_type_code ON product_type (code)');

        $duplicateProductSkuExists = (bool) $this->connection->fetchOne(<<<'SQL'
            SELECT EXISTS (
                SELECT 1
                FROM product_product
                WHERE sku IS NOT NULL AND BTRIM(sku) <> ''
                GROUP BY sku
                HAVING COUNT(*) > 1
            )
            SQL);
        $this->abortIf($duplicateProductSkuExists, 'Des produits utilisent le même SKU. Corrigez ces doublons avant de relancer la migration.');

        $this->addSql('CREATE UNIQUE INDEX uniq_product_product_sku ON product_product (sku)');
        $this->addSql('ALTER TABLE product_variant ADD sku VARCHAR(255) DEFAULT NULL');
        $this->addSql('CREATE UNIQUE INDEX uniq_product_variant_sku ON product_variant (sku)');

        $this->addSql('ALTER TABLE cart_cart_item ADD sku_snapshot VARCHAR(255) DEFAULT NULL');
        $this->addSql(<<<'SQL'
            UPDATE cart_cart_item AS cart_item
            SET sku_snapshot = product.sku
            FROM product_variant AS variant
            INNER JOIN product_product AS product ON product.id = variant.product_id
            WHERE cart_item.product_variant_id = variant.id
              AND product.sku IS NOT NULL
              AND BTRIM(product.sku) <> ''
            SQL);
    }

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

        $this->addSql('DROP INDEX uniq_product_variant_sku');
        $this->addSql('DROP INDEX uniq_product_product_sku');
        $this->addSql('DROP INDEX uniq_product_type_code');
        $this->addSql('ALTER TABLE cart_cart_item DROP sku_snapshot');
        $this->addSql('ALTER TABLE product_variant DROP sku');
        $this->addSql('ALTER TABLE product_type DROP code');
    }
}
