<?php

declare(strict_types=1);

namespace CpCreation\VitiCore\Bridge\Doctrine\Migrations;

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

final class Version20260619100000 extends AbstractMigration
{
    private const AUDIT_TABLES = [
        'cart_promo_audit',
        'contact_label_audit',
        'contact_label_translation_audit',
        'depot_depot_audit',
        'depot_depot_translation_audit',
        'event_event_audit',
        'event_event_translation_audit',
        'locale_locale_audit',
        'media_media_audit',
        'news_news_audit',
        'news_news_translation_audit',
        'popup_popup_audit',
        'popup_popup_translation_audit',
        'product_category_audit',
        'product_category_translation_audit',
        'product_degressive_audit',
        'product_option_audit',
        'product_option_translation_audit',
        'product_price_audit',
        'product_price_code_audit',
        'product_price_group_audit',
        'product_product_audit',
        'product_product_files_audit',
        'product_product_image_audit',
        'product_product_translation_audit',
        'product_tax_audit',
        'product_type_audit',
        'product_type_translation_audit',
        'product_variant_audit',
        'product_variant_image_audit',
        'product_variant_option_audit',
        'product_variant_option_image_audit',
        'product_variant_option_translation_audit',
        'shop_config_disable_country_translation_audit',
        'shop_config_translation_audit',
        'shop_delivery_method_audit',
        'shop_delivery_method_translation_audit',
        'shop_information_block_audit',
        'shop_information_block_translation_audit',
        'shop_shipping_additional_audit',
        'shop_shipping_additional_manual_audit',
        'shop_shipping_auto_audit',
        'shop_shipping_auto_sup_audit',
        'shop_shipping_config_audit',
        'shop_shipping_config_translation_audit',
        'shop_shipping_country_audit',
        'shop_shipping_county_audit',
        'shop_shipping_price_audit',
        'shop_shipping_tax_audit',
        'shop_shipping_zone_audit',
        'shop_shop_config_audit',
        'shop_shop_config_disable_country_audit',
        'shop_shop_config_multiple_audit',
        'socialnetwork_socialnetwork_audit',
        'user_user_audit',
    ];

    private const INDEXED_COLUMNS = [
        'type',
        'object_id',
        'discriminator',
        'transaction_hash',
        'blame_id',
        'created_at',
    ];

    public function getDescription(): string
    {
        return 'Cree les tables audit des entites suivies.';
    }

    public function up(Schema $schema): void
    {
        foreach (self::AUDIT_TABLES as $tableName) {
            $this->createAuditTable($tableName);
        }
    }

    public function down(Schema $schema): void
    {
        foreach (array_reverse(self::AUDIT_TABLES) as $tableName) {
            $this->addSql(sprintf('DROP TABLE IF EXISTS %s CASCADE', $tableName));
            $this->addSql(sprintf('DROP SEQUENCE IF EXISTS %s_id_seq CASCADE', $tableName));
        }
    }

    private function createAuditTable(string $tableName): void
    {
        $this->addSql(sprintf(
            <<<'SQL'
CREATE TABLE IF NOT EXISTS %s (
    id SERIAL NOT NULL,
    type VARCHAR(10) NOT NULL,
    object_id VARCHAR(255) NOT NULL,
    discriminator VARCHAR(255) DEFAULT NULL,
    transaction_hash VARCHAR(40) DEFAULT NULL,
    diffs JSON DEFAULT NULL,
    blame_id VARCHAR(255) DEFAULT NULL,
    blame_user VARCHAR(255) DEFAULT NULL,
    blame_user_fqdn VARCHAR(255) DEFAULT NULL,
    blame_user_firewall VARCHAR(100) DEFAULT NULL,
    ip VARCHAR(45) DEFAULT NULL,
    created_at TIMESTAMP(0) WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY(id)
)
SQL,
            $tableName
        ));

        foreach (self::INDEXED_COLUMNS as $columnName) {
            $this->addSql(sprintf(
                'CREATE INDEX IF NOT EXISTS %s ON %s (%s)',
                $this->getAuditIndexName($tableName, $columnName),
                $tableName,
                $columnName
            ));
        }

        $this->addSql(sprintf(
            "COMMENT ON COLUMN %s.created_at IS '(DC2Type:datetime_immutable)'",
            $tableName
        ));
    }

    private function getAuditIndexName(string $tableName, string $columnName): string
    {
        return sprintf('%s_%s_idx', $columnName, md5($tableName));
    }
}
