<?php

use App\Database\Migration;

/**
 * Migration 015 – Download Module (Phase 8)
 *
 * Creates:
 *   iw_download_categories  – download categories (own type, not shared with blog)
 *   iw_downloads            – downloads with media_id FK (featured image),
 *                             file_path for the downloadable file, slug, SEO fields
 */
class CreateDownloadTables extends Migration
{
    public function up(): void
    {
        $this->db->query("
            CREATE TABLE IF NOT EXISTS iw_download_categories (
                id          INT          AUTO_INCREMENT PRIMARY KEY,
                name        VARCHAR(100) NOT NULL,
                slug        VARCHAR(100) NOT NULL UNIQUE,
                description TEXT         DEFAULT NULL,
                sort_order  INT          NOT NULL DEFAULT 0
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");

        $this->db->query("
            CREATE TABLE IF NOT EXISTS iw_downloads (
                id              INT           AUTO_INCREMENT PRIMARY KEY,
                title           VARCHAR(255)  NOT NULL,
                slug            VARCHAR(255)  NOT NULL UNIQUE,
                excerpt         TEXT          DEFAULT NULL,
                content         LONGTEXT      DEFAULT NULL,
                file_path       VARCHAR(500)  DEFAULT NULL,
                file_name       VARCHAR(255)  DEFAULT NULL,
                file_size       INT           DEFAULT NULL,
                file_type       VARCHAR(100)  DEFAULT NULL,
                media_id        INT           DEFAULT NULL,
                category_id     INT           DEFAULT NULL,
                status          ENUM('draft','published') NOT NULL DEFAULT 'draft',
                sort_order      INT           NOT NULL DEFAULT 0,
                seo_title       VARCHAR(255)  DEFAULT NULL,
                seo_description TEXT          DEFAULT NULL,
                created_by      INT           DEFAULT NULL,
                created_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at      DATETIME      NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

                INDEX idx_status      (status),
                INDEX idx_category    (category_id),
                INDEX idx_media       (media_id),
                INDEX idx_sort_order  (sort_order),

                FOREIGN KEY (category_id) REFERENCES iw_download_categories(id) ON DELETE SET NULL,
                FOREIGN KEY (media_id)    REFERENCES iw_media(id)               ON DELETE SET NULL,
                FOREIGN KEY (created_by)  REFERENCES iw_users(id)               ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
    }

    public function down(): void
    {
        $this->db->query("DROP TABLE IF EXISTS iw_downloads");
        $this->db->query("DROP TABLE IF EXISTS iw_download_categories");
    }
}
