<?php
/**
 * ============================================================================
 * Handles DB table creation & upgrades for WordAI Agent module
 * ----------------------------------------------------------------------------
 * - Now supports MULTI-PROVIDER embeddings (OpenAI, Gemini, future providers)
 * - Stores provider + model + chunk_hash per embedding
 * ============================================================================
 */
if ( ! defined( 'ABSPATH' ) ) exit;

class SftcyWordaiOpenaiAgentInstall {

    /**
     * DB_VERSION should be bumped whenever you change table schemas.
     *
     */
    const DB_VERSION     = '1.0.4';
    const VERSION_OPTION = 'sftcy_wordai_agent_db_version';

    /**
     * ------------------------------------------------------------
     * Creates or upgrades all Agent-related tables via dbDelta()
     * ------------------------------------------------------------
     */
    public static function create_or_update_tables() {
        global $wpdb;
        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset_collate = $wpdb->get_charset_collate();

        // --- Table names
        $tbl_sources  = $wpdb->prefix . 'wordai_agent_sources';
        $tbl_chunks   = $wpdb->prefix . 'wordai_agent_chunks';
        $tbl_convos   = $wpdb->prefix . 'wordai_agent_conversations';
        $tbl_msgs     = $wpdb->prefix . 'wordai_agent_messages';
        $tbl_progress        = $wpdb->prefix . 'wordai_agent_progress';
        $tbl_embeds          = $wpdb->prefix . 'wordai_agent_embeddings';
        $tbl_selection       = $wpdb->prefix . 'wordai_agent_source_selection';
        $tbl_message_sources = $wpdb->prefix . 'wordai_agent_message_sources';
        $tbl_feedback        = $wpdb->prefix . 'wordai_agent_feedback';
        $tbl_leads           = $wpdb->prefix . 'wordai_agent_leads';
        $tbl_seo_changes = $wpdb->prefix . 'wordai_seo_changes';

        // =====================================================================
        // 🟢 SOURCES TABLE (unchanged)
        // =====================================================================
        $sql_sources = "CREATE TABLE $tbl_sources (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            source_type VARCHAR(40) DEFAULT 'post',
            source_id BIGINT UNSIGNED NULL,
            url TEXT NULL,
            title TEXT NULL,
            lang VARCHAR(12) NULL,
            tokens INT UNSIGNED DEFAULT 0,
            status VARCHAR(20) DEFAULT 'ready',
            error_msg TEXT NULL,
            last_indexed DATETIME NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY  (id),
            UNIQUE KEY uniq_source (source_type, source_id),
            KEY src_status (status)
        ) $charset_collate;";

        // =====================================================================
        // 🟢 CHUNKS TABLE (unchanged)
        // =====================================================================
        $sql_chunks = "CREATE TABLE $tbl_chunks (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            source_id BIGINT UNSIGNED NOT NULL,
            chunk_index INT UNSIGNED DEFAULT 0,
            content MEDIUMTEXT NOT NULL,
            embedding_model VARCHAR(64) DEFAULT '',
            token_count INT UNSIGNED DEFAULT 0,
            hash CHAR(64) NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY uniq_source_chunk (source_id, chunk_index),
            KEY src_id (source_id)
        ) $charset_collate;";

        // =====================================================================
        // 🟢 CONVERSATIONS TABLE (unchanged)
        // =====================================================================
        $sql_convos = "CREATE TABLE $tbl_convos (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            session_key CHAR(36) NOT NULL,
            user_email VARCHAR(190) NULL,
            visitor_hash CHAR(64) DEFAULT '',
            page_url TEXT NULL,
            page_title TEXT NULL,
            provider VARCHAR(30) DEFAULT 'openai',
            model VARCHAR(64) DEFAULT 'gpt-5.5',
            status VARCHAR(20) DEFAULT 'open',
            message_count INT UNSIGNED DEFAULT 0,
            last_source_count INT UNSIGNED DEFAULT 0,
            last_feedback TINYINT DEFAULT 0,
            last_error TEXT NULL,
            tokens_input INT UNSIGNED DEFAULT 0,
            tokens_output INT UNSIGNED DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY session_key (session_key),
            KEY provider_status (provider, status),
            KEY visitor_hash (visitor_hash)
        ) $charset_collate;";

        // =====================================================================
        // 🟢 MESSAGES TABLE (unchanged)
        // =====================================================================
        $sql_msgs = "CREATE TABLE $tbl_msgs (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            conversation_id BIGINT UNSIGNED NOT NULL,
            message_uuid CHAR(36) DEFAULT '',
            role ENUM('user','assistant') NOT NULL,
            content LONGTEXT NULL,
            provider VARCHAR(30) DEFAULT 'openai',
            model VARCHAR(64) DEFAULT '',
            page_url TEXT NULL,
            source_count INT UNSIGNED DEFAULT 0,
            fallback_used TINYINT(1) DEFAULT 0,
            latency_ms INT UNSIGNED DEFAULT 0,
            feedback_score TINYINT DEFAULT 0,
            tokens INT UNSIGNED DEFAULT 0,
            cost_usd DECIMAL(10,6) DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY conv_id (conversation_id),
            KEY role_created (role, created_at),
            KEY feedback_score (feedback_score)
        ) $charset_collate;";

        // =====================================================================
        // 🟢 EMBEDDINGS TABLE (UPDATED for multi-provider)
        // ---------------------------------------------------------------------
        // - provider:  openai | google | claude | etc.
        // - model:     e.g. text-embedding-3-small, text-embedding-004, ...
        // - chunk_hash: sha256 hash of chunk_text (for change detection)
        //
        // NOTE: We do NOT drop old columns; dbDelta() will alter the table and
        //       add the new columns if they don't exist.
        // =====================================================================
        $sql_embeds = "CREATE TABLE $tbl_embeds (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            source_id BIGINT UNSIGNED NOT NULL,
            chunk_text MEDIUMTEXT NOT NULL,
            embedding LONGTEXT NOT NULL,
            model VARCHAR(64) DEFAULT 'text-embedding-3-small',
            provider VARCHAR(20) DEFAULT 'openai',
            chunk_hash CHAR(64) DEFAULT '' NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY src_id (source_id),
            KEY provider_model_hash (provider, model, chunk_hash)
        ) $charset_collate;";

        // =====================================================================
        // 🟢 PROGRESS TABLE (unchanged)
        // =====================================================================
        $sql_progress = "CREATE TABLE $tbl_progress (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            total_items INT UNSIGNED DEFAULT 0,
            processed INT UNSIGNED DEFAULT 0,
            status VARCHAR(20) DEFAULT 'idle',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id)
        ) $charset_collate;";

        // =====================================================================
        // 🟢 SOURCE SELECTION TABLE
        // ---------------------------------------------------------------------
        // Stores the shared, provider-agnostic knowledge-base selection. The
        // selected source rows are then embedded separately for each provider.
        // =====================================================================
        $sql_selection = "CREATE TABLE $tbl_selection (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            source_row_id BIGINT UNSIGNED NOT NULL,
            selected TINYINT(1) DEFAULT 1,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY source_row_id (source_row_id),
            KEY selected (selected)
        ) $charset_collate;";

        $sql_message_sources = "CREATE TABLE $tbl_message_sources (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            message_id BIGINT UNSIGNED NOT NULL,
            conversation_id BIGINT UNSIGNED NOT NULL,
            source_id BIGINT UNSIGNED DEFAULT 0,
            source_type VARCHAR(40) DEFAULT '',
            source_title TEXT NULL,
            source_url TEXT NULL,
            similarity DECIMAL(8,6) DEFAULT 0,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            KEY message_id (message_id),
            KEY conversation_id (conversation_id),
            KEY source_id (source_id)
        ) $charset_collate;";

        $sql_feedback = "CREATE TABLE $tbl_feedback (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            message_id BIGINT UNSIGNED NOT NULL,
            conversation_id BIGINT UNSIGNED NOT NULL,
            rating TINYINT NOT NULL DEFAULT 0,
            comment TEXT NULL,
            visitor_hash CHAR(64) DEFAULT '',
            page_url TEXT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY message_id (message_id),
            KEY conversation_id (conversation_id),
            KEY rating (rating)
        ) $charset_collate;";

        $sql_leads = "CREATE TABLE $tbl_leads (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            conversation_id BIGINT UNSIGNED NOT NULL,
            visitor_hash CHAR(64) DEFAULT '',
            name VARCHAR(190) DEFAULT '',
            email VARCHAR(190) DEFAULT '',
            phone VARCHAR(80) DEFAULT '',
            company VARCHAR(190) DEFAULT '',
            message TEXT NULL,
            consent_given TINYINT(1) DEFAULT 0,
            source_page_url TEXT NULL,
            source_page_title TEXT NULL,
            first_question TEXT NULL,
            last_question TEXT NULL,
            status VARCHAR(30) DEFAULT 'new',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME NULL ON UPDATE CURRENT_TIMESTAMP,
            PRIMARY KEY (id),
            UNIQUE KEY conversation_id (conversation_id),
            KEY visitor_hash (visitor_hash),
            KEY email (email),
            KEY status (status),
            KEY created_at (created_at)
        ) $charset_collate;";

        $sql_seo_changes = "CREATE TABLE $tbl_seo_changes (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            change_uuid CHAR(36) NOT NULL,
            post_id BIGINT UNSIGNED NOT NULL,
            provider VARCHAR(40) NOT NULL,
            action_name VARCHAR(60) DEFAULT 'update' NOT NULL,
            before_json LONGTEXT NOT NULL,
            after_json LONGTEXT NOT NULL,
            user_id BIGINT UNSIGNED DEFAULT 0,
            status VARCHAR(20) DEFAULT 'applied' NOT NULL,
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            undone_at DATETIME NULL,
            PRIMARY KEY (id),
            UNIQUE KEY change_uuid (change_uuid),
            KEY post_status (post_id, status),
            KEY provider (provider)
        ) $charset_collate;";

        // =====================================================================
        // Execute dbDelta() for each SQL definition
        // =====================================================================
        dbDelta( $sql_sources );
        dbDelta( $sql_chunks );
        dbDelta( $sql_convos );
        dbDelta( $sql_msgs );
        dbDelta( $sql_embeds );
        dbDelta( $sql_progress );
        dbDelta( $sql_selection );
        dbDelta( $sql_message_sources );
        dbDelta( $sql_feedback );
        dbDelta( $sql_leads );
        dbDelta( $sql_seo_changes );

        // Store version marker for future upgrade checks
        update_option( self::VERSION_OPTION, self::DB_VERSION );
    }
}
