<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ — NUCLEAR+++
// File: /migrations/002_scheduled_posts.php
// Description: Creates amai_scheduled_posts table for Layer 3 Content Engine - NUCLEAR+++
// Last Edited: 2026-03-28
// ==================================================

namespace AutoMarketerAI\Migrations;

// No direct access
defined('ABSPATH') || exit;

final class Migration_002_ScheduledPosts
{
    // Migration version identifier — never change once shipped
    public const VERSION = '002';
    public const DESCRIPTION = 'Create amai_scheduled_posts table';

    public function up(): void
    {
        global $wpdb;

        $charset = $wpdb->get_charset_collate();

        // --------------------------------------------------
        // amai_scheduled_posts
        // Stores AI-generated post drafts for Facebook.
        // One row per scheduled post slot per week.
        // generation_batch identifies the week (ISO 8601 week,
        // e.g. '2026-W14') so duplicate generation runs for
        // the same week can be detected and skipped.
        // facebook_post_id is populated by Layer 5 (Posting
        // Engine) after Graph API confirms the post.
        // failure_reason stores the last error on failed posts
        // so the user can see what went wrong.
        // --------------------------------------------------
        $table = $wpdb->prefix . 'amai_scheduled_posts';

        $sql = "CREATE TABLE {$table} (
            id                  BIGINT UNSIGNED     NOT NULL AUTO_INCREMENT,
            tenant_id           VARCHAR(64)         NOT NULL DEFAULT 'default',
            facebook_page_id    VARCHAR(64)         NOT NULL DEFAULT '',
            post_type           ENUM(
                                    'educational',
                                    'product',
                                    'feel_good',
                                    'behind_scenes',
                                    'engagement'
                                )                   NOT NULL,
            scheduled_date      DATE                NOT NULL,
            scheduled_time      TIME                NOT NULL DEFAULT '10:00:00',
            status              ENUM(
                                    'draft',
                                    'approved',
                                    'posted',
                                    'rejected',
                                    'failed'
                                )                   NOT NULL DEFAULT 'draft',
            post_content        TEXT                NOT NULL,
            post_image_url      VARCHAR(512)        NULL,
            facebook_post_id    VARCHAR(64)         NULL,
            generation_batch    VARCHAR(10)         NOT NULL,
            failure_reason      TEXT                NULL,
            reviewed_at         DATETIME            NULL,
            posted_at           DATETIME            NULL,
            created_at          DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
            updated_at          DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_tenant_date_type (tenant_id, scheduled_date, post_type),
            KEY idx_tenant_batch (tenant_id, generation_batch),
            KEY idx_tenant_status (tenant_id, status),
            KEY idx_scheduled_date (scheduled_date)
        ) {$charset};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        dbDelta($sql);
    }

    public function down(): void
    {
        // Forward-only migrations — down() documents intent only.
        // Never call this in production.
        global $wpdb;

        $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}amai_scheduled_posts");
    }
}