<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ — NUCLEAR+++
// File: /migrations/014_linkedin_connection.php
// Description: Creates amai_linkedin_connection table for LinkedIn OAuth channel - NUCLEAR+++
// Last Edited: 2026-04-13
// ==================================================

namespace AutoMarketerAI\Migrations;

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

final class Migration_014_LinkedInConnection
{
    // Migration version identifier — never change once shipped
    public const VERSION = '014';
    public const DESCRIPTION = 'Create amai_linkedin_connection table';

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

        $charset = $wpdb->get_charset_collate();

        // --------------------------------------------------
        // amai_linkedin_connection
        // Stores the connected LinkedIn Organization Page.
        // access_token_enc and refresh_token_enc are both
        // AES-256-CBC encrypted at rest via KeyVault.
        // organization_urn holds the full URN string
        // (urn:li:organization:XXXXX) — used as the author
        // on all ugcPosts API calls.
        // One row per tenant — same pattern as FB connection.
        // --------------------------------------------------
        $table = $wpdb->prefix . 'amai_linkedin_connection';

        $sql = "CREATE TABLE IF NOT EXISTS {$table} (
            id                  BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            tenant_id           VARCHAR(64)     NOT NULL DEFAULT 'default',
            organization_urn    VARCHAR(128)    NOT NULL DEFAULT '',
            organization_id     VARCHAR(64)     NOT NULL DEFAULT '',
            organization_name   VARCHAR(255)    NOT NULL DEFAULT '',
            page_picture_url    VARCHAR(512)    NULL,
            page_followers      INT UNSIGNED    NULL,
            access_token_enc    TEXT            NOT NULL,
            refresh_token_enc   TEXT            NULL,
            token_expires_at    DATETIME        NULL,
            refresh_expires_at  DATETIME        NULL,
            member_id           VARCHAR(64)     NOT NULL DEFAULT '',
            member_name         VARCHAR(255)    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};";

        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_linkedin_connection");
    }
}