<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ — NUCLEAR+++
// File: /migrations/001_business_profile_and_facebook_connection.php
// Description: Creates amai_business_profile and amai_facebook_connection tables - NUCLEAR+++
// Last Edited: 2026-03-24
// ==================================================

namespace AutoMarketerAI\Migrations;

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

final class Migration_001_BusinessProfileAndFacebookConnection
{
    // Migration version identifier — never change once shipped
    public const VERSION = '001';
    public const DESCRIPTION = 'Create amai_business_profile and amai_facebook_connection tables';

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

        $charset = $wpdb->get_charset_collate();

        // --------------------------------------------------
        // amai_business_profile
        // One row per install. tenant_id reserved for future
        // multi-tenant support — matches Core API tenant slug.
        // --------------------------------------------------
        $profile_table = $wpdb->prefix . 'amai_business_profile';

        $profile_sql = "CREATE TABLE {$profile_table} (
            id                      BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            tenant_id               VARCHAR(64)     NOT NULL DEFAULT 'default',
            website_url             VARCHAR(512)    NULL,
            website_status          ENUM('has_website','no_website','declined_to_share') NOT NULL DEFAULT 'declined_to_share',
            business_category       VARCHAR(100)    NOT NULL DEFAULT '',
            business_category_other VARCHAR(255)    NULL,
            business_tone           ENUM('warm_personal','professional_informative','witty_light') NOT NULL DEFAULT 'warm_personal',
            has_physical_location   TINYINT(1)      NOT NULL DEFAULT 0,
            sells_online            ENUM('yes_store_ready','yes_wants_to','sells_services','no') NOT NULL DEFAULT 'no',
            customer_descriptor     VARCHAR(100)    NULL,
            customer_age_range      ENUM('under_25','25_to_40','40_to_55','55_plus','mixed') NOT NULL DEFAULT 'mixed',
            differentiator          TEXT            NULL,
            values_and_causes       TEXT            NULL,
            draft_review_day        TINYINT UNSIGNED NOT NULL DEFAULT 3,
            post_time_hour          TINYINT UNSIGNED NOT NULL DEFAULT 10,
            posting_mode            ENUM('approve_first','autopilot') NOT NULL DEFAULT 'approve_first',
            onboarding_step         TINYINT UNSIGNED NOT NULL DEFAULT 0,
            onboarding_completed_at DATETIME        NULL,
            website_lead_captured   TINYINT(1)      NOT NULL DEFAULT 0,
            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 (tenant_id)
        ) {$charset};";

        // --------------------------------------------------
        // amai_facebook_connection
        // Stores the connected Facebook Business Page details.
        // access_token is AES-256 encrypted at rest via
        // amai_encrypt() / amai_decrypt() helpers.
        // One row per tenant — same future-proof pattern.
        // --------------------------------------------------
        $fb_table = $wpdb->prefix . 'amai_facebook_connection';

        $fb_sql = "CREATE TABLE {$fb_table} (
            id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            tenant_id           VARCHAR(64)     NOT NULL DEFAULT 'default',
            page_id             VARCHAR(64)     NOT NULL DEFAULT '',
            page_name           VARCHAR(255)    NOT NULL DEFAULT '',
            page_category       VARCHAR(255)    NULL,
            page_picture_url    VARCHAR(512)    NULL,
            page_followers      INT UNSIGNED    NULL,
            access_token_enc    TEXT            NOT NULL,
            token_expires_at    DATETIME        NULL,
            user_fb_id          VARCHAR(64)     NOT NULL DEFAULT '',
            connected_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            last_verified_at    DATETIME        NULL,
            status              ENUM('active','expired','revoked','error') NOT NULL DEFAULT 'active',
            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 (tenant_id),
            KEY idx_status (status)
        ) {$charset};";

        // --------------------------------------------------
        // amai_business_photos
        // Uploaded or AI-generated images attached to the
        // business profile. Used as source material for posts.
        // wp_attachment_id links to WP media library when
        // the image was uploaded (NULL for AI-generated).
        // --------------------------------------------------
        $photos_table = $wpdb->prefix . 'amai_business_photos';

        $photos_sql = "CREATE TABLE {$photos_table} (
            id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            tenant_id           VARCHAR(64)     NOT NULL DEFAULT 'default',
            wp_attachment_id    BIGINT UNSIGNED NULL,
            source              ENUM('upload','ai_generated') NOT NULL DEFAULT 'upload',
            label               VARCHAR(255)    NULL,
            url                 VARCHAR(512)    NOT NULL DEFAULT '',
            ai_prompt           TEXT            NULL,
            used_in_post_count  INT UNSIGNED    NOT NULL DEFAULT 0,
            created_at          DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY idx_tenant (tenant_id),
            KEY idx_source (source)
        ) {$charset};";

        // --------------------------------------------------
        // amai_monthly_refresh
        // Stores the monthly "what's new" input from the
        // business owner. One row per month per tenant.
        // Keyed by tenant_id + refresh_month (YYYY-MM).
        // --------------------------------------------------
        $refresh_table = $wpdb->prefix . 'amai_monthly_refresh';

        $refresh_sql = "CREATE TABLE {$refresh_table} (
            id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            tenant_id       VARCHAR(64)     NOT NULL DEFAULT 'default',
            refresh_month   CHAR(7)         NOT NULL,
            notes           TEXT            NULL,
            has_news        TINYINT(1)      NOT NULL DEFAULT 0,
            has_event       TINYINT(1)      NOT NULL DEFAULT 0,
            event_details   TEXT            NULL,
            submitted_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uq_tenant_month (tenant_id, refresh_month)
        ) {$charset};";

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

        dbDelta($profile_sql);
        dbDelta($fb_sql);
        dbDelta($photos_sql);
        dbDelta($refresh_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_monthly_refresh");
        $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}amai_business_photos");
        $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}amai_facebook_connection");
        $wpdb->query("DROP TABLE IF EXISTS {$wpdb->prefix}amai_business_profile");
    }
}