<?php

use App\Database\Migration;

class CreateBlogTables extends Migration
{
    public function up(): void
    {
        $this->db->query("
            CREATE TABLE IF NOT EXISTS iw_blog_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_blog_posts (
                id INT AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(255) NOT NULL,
                slug VARCHAR(255) NOT NULL UNIQUE,
                content LONGTEXT,
                excerpt TEXT DEFAULT NULL,
                featured_image VARCHAR(500) DEFAULT NULL,
                status ENUM('draft','published') NOT NULL DEFAULT 'draft',
                category_id INT DEFAULT NULL,
                author_id INT DEFAULT NULL,
                published_at DATETIME DEFAULT NULL,
                seo_title VARCHAR(255) DEFAULT NULL,
                seo_description TEXT 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_published (status, published_at),
                INDEX idx_category (category_id),
                INDEX idx_author (author_id),
                FOREIGN KEY (category_id) REFERENCES iw_blog_categories(id) ON DELETE SET NULL,
                FOREIGN KEY (author_id) REFERENCES iw_users(id) ON DELETE SET NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");

        $this->db->query("
            CREATE TABLE IF NOT EXISTS iw_blog_tags (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(100) NOT NULL,
                slug VARCHAR(100) NOT NULL UNIQUE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");

        $this->db->query("
            CREATE TABLE IF NOT EXISTS iw_blog_post_tags (
                post_id INT NOT NULL,
                tag_id INT NOT NULL,
                PRIMARY KEY (post_id, tag_id),
                FOREIGN KEY (post_id) REFERENCES iw_blog_posts(id) ON DELETE CASCADE,
                FOREIGN KEY (tag_id) REFERENCES iw_blog_tags(id) ON DELETE CASCADE
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
        ");
    }

    public function down(): void
    {
        $this->db->query("DROP TABLE IF EXISTS iw_blog_post_tags");
        $this->db->query("DROP TABLE IF EXISTS iw_blog_tags");
        $this->db->query("DROP TABLE IF EXISTS iw_blog_posts");
        $this->db->query("DROP TABLE IF EXISTS iw_blog_categories");
    }
}
