<?php
declare(strict_types=1);

namespace App\Domain\Media\Migrations;

use App\Framework\Database\ConnectionInterface;
use App\Framework\Database\Migration\Migration;

final class CreateImageVariantsTable implements Migration
{

    public function up(ConnectionInterface $connection): void
    {
        $sql = <<<SQL
            CREATE TABLE IF NOT EXISTS image_variants (
                id              INT AUTO_INCREMENT,
                image_id        VARCHAR(26) NOT NULL,
                variant_type    VARCHAR(50) NOT NULL,
                format          VARCHAR(25) NOT NULL,
                mime_type       VARCHAR(100) NOT NULL,
                width           INT NOT NULL,
                height          INT NOT NULL,
                file_size       BIGINT NOT NULL,
                filename        VARCHAR(500) NOT NULL,
                path            VARCHAR(500) NOT NULL,
                created_at      TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at      TIMESTAMP NULL DEFAULT NULL,

                PRIMARY KEY (id),
                UNIQUE KEY uk_image_variants_combination (image_id, variant_type, format),
                CONSTRAINT fk_image_variants_image_id
                    FOREIGN KEY (image_id) REFERENCES images(ulid) ON DELETE CASCADE,
                INDEX idx_image_variants_lookup (image_id, variant_type)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SQL;

        $connection->query($sql);
    }

    public function down(ConnectionInterface $connection): void
    {
        $this->dropExistingConstraints($connection);

        $connection->execute("DROP TABLE IF EXISTS image_variants");
    }

    public function getVersion(): string
    {
        return "003";
    }

    public function getDescription(): string
    {
        return "Create Image Variant Table";
    }

    private function dropExistingConstraints(ConnectionInterface $connection): void
    {
        try {
            // Alle bestehenden Foreign Key Constraints für diese Tabelle finden und entfernen
            $constraints = $connection->query("
                SELECT CONSTRAINT_NAME
                FROM information_schema.KEY_COLUMN_USAGE
                WHERE TABLE_SCHEMA = DATABASE()
                AND TABLE_NAME = 'image_variants'
                AND REFERENCED_TABLE_NAME IS NOT NULL
            ")->fetchAll();

            foreach ($constraints as $constraint) {
                $connection->execute('ALTER TABLE image_variants DROP FOREIGN KEY ' . $constraint['CONSTRAINT_NAME']);
            }
        } catch (\Exception $e) {
            // Ignorieren wenn Tabelle nicht existiert oder keine Constraints vorhanden sind
        }
    }
}
