<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ -- NUCLEAR+++
// File: /home/ploi/automarketer-ai.com/public/wp-content/plugins/automarketer-ai/migrations/034_social_comments.php
// Description: Creates wp_amai_social_comments table for Comment Manager - NUCLEAR+++
// Last Edited: 2026-05-14
// ==================================================
namespace AutoMarketerAI\Migrations;
defined('ABSPATH') || exit;
class Migration_034_SocialComments
{
    public function up(): void
    {
        global $wpdb;
        $table   = $wpdb->prefix . 'amai_social_comments';
        $charset = $wpdb->get_charset_collate();

        // Idempotent -- skip if table already exists
        $exists = $wpdb->get_var(
            $wpdb->prepare(
                "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = %s AND TABLE_NAME = %s",
                DB_NAME,
                $table
            )
        );
        if ((int) $exists > 0) {
            return;
        }

        $wpdb->query(
            "CREATE TABLE `{$table}` (
                `id`                        BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
                `channel`                   VARCHAR(32)     NOT NULL,
                `platform_comment_id`       VARCHAR(128)    NOT NULL,
                `platform_post_id`          VARCHAR(128)    NOT NULL DEFAULT '',
                `parent_platform_comment_id` VARCHAR(128)   NULL DEFAULT NULL,
                `author_name`               VARCHAR(255)    NOT NULL DEFAULT '',
                `author_platform_id`        VARCHAR(128)    NOT NULL DEFAULT '',
                `content`                   TEXT            NOT NULL,
                `commented_at`              DATETIME        NOT NULL,
                `status`                    ENUM('new','pending_review','replied','ignored') NOT NULL DEFAULT 'new',
                `ai_draft`                  TEXT            NULL DEFAULT NULL,
                `reply_content`             TEXT            NULL DEFAULT NULL,
                `replied_at`               DATETIME        NULL DEFAULT NULL,
                `is_followup`              TINYINT(1)      NOT NULL DEFAULT 0,
                `reply_to_comment_id`       BIGINT UNSIGNED NULL DEFAULT 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_channel_comment` (`channel`, `platform_comment_id`),
                KEY `idx_status` (`status`),
                KEY `idx_channel_post` (`channel`, `platform_post_id`),
                KEY `idx_is_followup` (`is_followup`),
                KEY `idx_replied_at` (`replied_at`),
                CONSTRAINT `fk_reply_to_comment` FOREIGN KEY (`reply_to_comment_id`) REFERENCES `{$table}` (`id`) ON DELETE SET NULL
            ) {$charset}"
        );
    }
}