<?php

/**
 * Migration 042 – Add content scheduling support
 *
 * Adds publish_at / unpublish_at columns to iw_pages and iw_blog_posts,
 * extends the status ENUM with 'scheduled', and inserts the feature toggle.
 */

use App\Database\Migration;

class AddContentScheduling extends Migration
{
    public function up(): void
    {
        // ── iw_pages: add scheduling columns ─────────────────────────────
        $cols = $this->columnNames('iw_pages');

        if (!in_array('publish_at', $cols, true)) {
            $this->db->query("ALTER TABLE iw_pages ADD COLUMN publish_at DATETIME NULL DEFAULT NULL AFTER status");
        }
        if (!in_array('unpublish_at', $cols, true)) {
            $this->db->query("ALTER TABLE iw_pages ADD COLUMN unpublish_at DATETIME NULL DEFAULT NULL AFTER publish_at");
        }

        // Extend status ENUM to include 'scheduled' (safe to re-run)
        $this->db->query("
            ALTER TABLE iw_pages
            MODIFY COLUMN status ENUM('draft','published','scheduled') NOT NULL DEFAULT 'draft'
        ");

        // Index for scheduler queries
        $this->addIndexIfNotExists('iw_pages', 'idx_pages_scheduling', '(status, publish_at, unpublish_at)');

        // ── iw_blog_posts: add scheduling columns ────────────────────────
        $cols = $this->columnNames('iw_blog_posts');

        if (!in_array('publish_at', $cols, true)) {
            $this->db->query("ALTER TABLE iw_blog_posts ADD COLUMN publish_at DATETIME NULL DEFAULT NULL AFTER status");
        }
        if (!in_array('unpublish_at', $cols, true)) {
            $this->db->query("ALTER TABLE iw_blog_posts ADD COLUMN unpublish_at DATETIME NULL DEFAULT NULL AFTER publish_at");
        }

        // Extend status ENUM to include 'scheduled'
        $this->db->query("
            ALTER TABLE iw_blog_posts
            MODIFY COLUMN status ENUM('draft','published','scheduled') NOT NULL DEFAULT 'draft'
        ");

        // Index for scheduler queries
        $this->addIndexIfNotExists('iw_blog_posts', 'idx_posts_scheduling', '(status, publish_at, unpublish_at)');

        // ── Feature toggle ───────────────────────────────────────────────
        $exists = $this->db->fetch(
            "SELECT 1 FROM iw_settings WHERE setting_key = 'content_scheduling_enabled'"
        );
        if (!$exists) {
            $this->db->insert('iw_settings', [
                'setting_key'   => 'content_scheduling_enabled',
                'setting_value' => '1',
                'setting_group' => 'features',
            ]);
        }
    }

    public function down(): void
    {
        // Revert status ENUM
        $this->db->query("
            UPDATE iw_pages SET status = 'draft' WHERE status = 'scheduled'
        ");
        $this->db->query("
            ALTER TABLE iw_pages
            MODIFY COLUMN status ENUM('draft','published') NOT NULL DEFAULT 'draft'
        ");
        $this->db->query("ALTER TABLE iw_pages DROP COLUMN IF EXISTS publish_at");
        $this->db->query("ALTER TABLE iw_pages DROP COLUMN IF EXISTS unpublish_at");

        $this->db->query("
            UPDATE iw_blog_posts SET status = 'draft' WHERE status = 'scheduled'
        ");
        $this->db->query("
            ALTER TABLE iw_blog_posts
            MODIFY COLUMN status ENUM('draft','published') NOT NULL DEFAULT 'draft'
        ");
        $this->db->query("ALTER TABLE iw_blog_posts DROP COLUMN IF EXISTS publish_at");
        $this->db->query("ALTER TABLE iw_blog_posts DROP COLUMN IF EXISTS unpublish_at");

        $this->db->query("DELETE FROM iw_settings WHERE setting_key = 'content_scheduling_enabled'");
    }

    // ── Helpers ──────────────────────────────────────────────────────────

    private function columnNames(string $table): array
    {
        $rows = $this->db->fetchAll("SHOW COLUMNS FROM {$table}");
        return array_column($rows, 'Field');
    }

    private function addIndexIfNotExists(string $table, string $indexName, string $columns): void
    {
        $existing = $this->db->fetchAll("SHOW INDEX FROM {$table} WHERE Key_name = '{$indexName}'");
        if (empty($existing)) {
            $this->db->query("ALTER TABLE {$table} ADD INDEX {$indexName} {$columns}");
        }
    }
}
