<?php

use App\Database\Migration;

/**
 * Phase 4a – Central Media Manager v1
 *
 * 1. Creates iw_media_folders (lightweight folder hierarchy)
 * 2. Extends iw_media with: folder_id (FK), title, description
 *
 * Safe re-run: uses information_schema checks before ALTER TABLE
 * to prevent "Duplicate column" errors on repeated execution.
 */
class CreateMediaFoldersExtendMedia extends Migration
{
    public function up(): void
    {
        // 1. Create media folders table
        $this->db->query("
            CREATE TABLE IF NOT EXISTS iw_media_folders (
                id          INT          AUTO_INCREMENT PRIMARY KEY,
                name        VARCHAR(255) NOT NULL,
                slug        VARCHAR(255) NOT NULL,
                parent_id   INT          DEFAULT NULL,
                sort_order  INT          NOT NULL DEFAULT 0,
                created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
                UNIQUE KEY  uk_slug      (slug),
                INDEX       idx_parent   (parent_id)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");

        // 2. Extend iw_media with folder reference and richer metadata
        //    Use information_schema check to be safely re-runnable
        $hasFolderId = (int) $this->db->fetchColumn(
            "SELECT COUNT(*)
             FROM   information_schema.COLUMNS
             WHERE  TABLE_SCHEMA = DATABASE()
               AND  TABLE_NAME   = 'iw_media'
               AND  COLUMN_NAME  = 'folder_id'"
        );

        if ($hasFolderId === 0) {
            $this->db->query("
                ALTER TABLE iw_media
                    ADD COLUMN folder_id   INT          DEFAULT NULL AFTER id,
                    ADD COLUMN title       VARCHAR(255) DEFAULT NULL AFTER alt_text,
                    ADD COLUMN description TEXT         DEFAULT NULL AFTER title,
                    ADD INDEX  idx_folder  (folder_id),
                    ADD CONSTRAINT fk_media_folder
                        FOREIGN KEY (folder_id)
                        REFERENCES iw_media_folders(id)
                        ON DELETE SET NULL
            ");
        }
    }

    public function down(): void
    {
        $this->db->query("ALTER TABLE iw_media DROP FOREIGN KEY fk_media_folder");
        $this->db->query("ALTER TABLE iw_media DROP INDEX idx_folder");
        $this->db->query("
            ALTER TABLE iw_media
                DROP COLUMN folder_id,
                DROP COLUMN title,
                DROP COLUMN description
        ");
        $this->db->query("DROP TABLE IF EXISTS iw_media_folders");
    }
}
