<?php
declare(strict_types=1);
// ==================================================
// Nuclear Fancy Pants Edition™ -- NUCLEAR+++
// File: /home/ploi/automarketer-ai.com/public/wp-content/plugins/automarketer-ai/migrations/040_google_reviews.php
// Description: Creates wp_amai_reviews table for Review Responder module - NUCLEAR+++
// Last Edited: 2026-05-17
// ==================================================

namespace AutoMarketerAI\Migrations;

defined('ABSPATH') || exit;

final class Migration_040_GoogleReviews
{
    public const VERSION     = '040';
    public const DESCRIPTION = 'Create amai_reviews table';

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

        $table   = $wpdb->prefix . 'amai_reviews';
        $charset = $wpdb->get_charset_collate();

        $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;
        }

        // --------------------------------------------------
        // wp_amai_reviews
        // One row per Google Business Profile review.
        // comment is nullable -- Google allows star-only reviews with no text.
        // ai_draft and reply_text are nullable until generated/submitted.
        // --------------------------------------------------
        $wpdb->query(
            "CREATE TABLE `{$table}` (
                `id`              BIGINT UNSIGNED  NOT NULL AUTO_INCREMENT,
                `tenant_id`       VARCHAR(64)      NOT NULL DEFAULT 'default',
                `review_id`       VARCHAR(255)     NOT NULL,
                `reviewer_name`   VARCHAR(255)     NOT NULL DEFAULT '',
                `star_rating`     TINYINT UNSIGNED NOT NULL DEFAULT 0,
                `comment`         TEXT             NULL DEFAULT NULL,
                `review_date`     DATETIME         NOT NULL,
                `status`          ENUM('new','pending_review','replied','ignored') NOT NULL DEFAULT 'new',
                `ai_draft`        TEXT             NULL DEFAULT NULL,
                `reply_text`      TEXT             NULL DEFAULT NULL,
                `replied_at`      DATETIME         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_tenant_review` (`tenant_id`, `review_id`),
                KEY `idx_status` (`status`),
                KEY `idx_star_rating` (`star_rating`),
                KEY `idx_review_date` (`review_date`)
            ) {$charset}"
        );
    }
}