<?php

use App\Database\Migration;

/**
 * Phase 2.2 – Galerie x Media Manager Integration
 *
 * Extends iw_gallery_images with optional media_id FK reference.
 * Allows gallery images to reference existing Media Manager assets
 * instead of storing duplicate files. Maintains backward compatibility:
 * – file_path remains a non-null column (existing images)
 * – media_id is NULL for legacy/duplicated images
 * – media_id is NOT NULL for new references to Media Manager
 *
 * This separation allows gradual migration and coexistence of both models.
 *
 * Safe re-run: uses information_schema check before ALTER TABLE
 * to prevent "Duplicate column" errors on repeated execution.
 */
class ExtendGalleryWithMediaReference extends Migration
{
    public function up(): void
    {
        // Check if column already exists before altering
        $hasMediaId = (int) $this->db->fetchColumn(
            "SELECT COUNT(*)
             FROM   information_schema.COLUMNS
             WHERE  TABLE_SCHEMA = DATABASE()
               AND  TABLE_NAME   = 'iw_gallery_images'
               AND  COLUMN_NAME  = 'media_id'"
        );

        if ($hasMediaId === 0) {
            // Add optional media_id reference (initially NULL for all existing rows)
            $this->db->query("
                ALTER TABLE iw_gallery_images
                ADD COLUMN media_id INT DEFAULT NULL AFTER album_id,
                ADD INDEX idx_media (media_id),
                ADD CONSTRAINT fk_gallery_images_media
                    FOREIGN KEY (media_id)
                    REFERENCES iw_media(id)
                    ON DELETE SET NULL
            ");
        }
    }

    public function down(): void
    {
        // Remove foreign key and column
        $this->db->query("
            ALTER TABLE iw_gallery_images
            DROP FOREIGN KEY fk_gallery_images_media
        ");

        $this->db->query("
            ALTER TABLE iw_gallery_images
            DROP INDEX idx_media
        ");

        $this->db->query("
            ALTER TABLE iw_gallery_images
            DROP COLUMN media_id
        ");
    }
}
