<?php

if (! defined('ABSPATH')) {
    exit;
}

/**
 * Owns the plugin's database schema: table names and table creation.
 *
 * Every other class resolves table names through these accessors so the
 * schema has a single point of definition.
 */
class RSVL_Schema
{
    /** The chunks table: training text, split into chunks, with their embeddings. */
    public static function chunks_table(): string
    {
        global $wpdb;

        return $wpdb->prefix . 'resolver_chunks';
    }

    /** The sources table: one row per trained post, PDF or note. */
    public static function sources_table(): string
    {
        global $wpdb;

        return $wpdb->prefix . 'resolver_sources';
    }

    /** The chats table: every chat message. */
    public static function chats_table(): string
    {
        global $wpdb;

        return $wpdb->prefix . 'resolver_chats';
    }

    /** The token usage table: tokens used by each chat reply and training request. */
    public static function token_usage_table(): string
    {
        global $wpdb;

        return $wpdb->prefix . 'resolver_token_usage';
    }

    /**
     * Create database tables.
     */
    public static function create_tables()
    {
        global $wpdb;

        $charset = $wpdb->get_charset_collate();

        $knowledge_table     = self::chunks_table();
        $conversations_table = self::chats_table();

        $sql_knowledge = "CREATE TABLE {$knowledge_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            source_id BIGINT UNSIGNED DEFAULT NULL,
            source_url TEXT NOT NULL,
            chunk_index SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            total_chunks SMALLINT UNSIGNED NOT NULL DEFAULT 1,
            content TEXT NOT NULL,
            embedding LONGBLOB DEFAULT NULL,
            embedding_model VARCHAR(64) DEFAULT NULL,
            content_type VARCHAR(50) DEFAULT 'post',
            created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
            updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
            KEY idx_source_id (source_id),
            KEY idx_source_url (source_url(191)),
            KEY idx_content_type (content_type)
        ) {$charset};";

        // Written the way MySQL reports each column back (ENUM values with no
        // space after the comma, say): dbDelta() compares the two, and alters a
        // column that differs, rebuilding its whole table, on every run.
        //
        // The chat and token usage times are UTC, written by PHP with gmdate().
        // A column default would use the database server's clock, which is in
        // whatever timezone the server is set to.
        $sql_conversations = "CREATE TABLE {$conversations_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            session_id VARCHAR(64) NOT NULL,
            role ENUM('user','assistant') NOT NULL,
            message TEXT NOT NULL,
            rating ENUM('up','down') DEFAULT NULL,
            is_error TINYINT(1) NOT NULL DEFAULT 0,
            human_request TINYINT(1) NOT NULL DEFAULT 0,
            page_url TEXT DEFAULT NULL,
            user_id BIGINT UNSIGNED DEFAULT NULL,
            is_admin TINYINT(1) NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL,
            KEY idx_session (session_id),
            KEY idx_created (created_at)
        ) {$charset};";

        $token_usage_table = self::token_usage_table();
        $sql_token_usage   = "CREATE TABLE {$token_usage_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            type ENUM('chat','index') NOT NULL,
            provider VARCHAR(50) NOT NULL,
            model VARCHAR(100) NOT NULL DEFAULT '',
            session_id VARCHAR(64) DEFAULT NULL,
            prompt_tokens INT UNSIGNED NOT NULL DEFAULT 0,
            completion_tokens INT UNSIGNED NOT NULL DEFAULT 0,
            total_tokens INT UNSIGNED NOT NULL DEFAULT 0,
            created_at DATETIME NOT NULL,
            KEY idx_type_created (type, created_at),
            KEY idx_session (session_id)
        ) {$charset};";

        $posts_table = self::sources_table();

        // `title` and `content` are populated for notes only. A note has no
        // WordPress post (post_id is NULL), so this row is the only place its
        // text can live. Every other source type is backed by a real post, and
        // its title is read live from $post->post_title rather than copied
        // here, where it would go stale the moment the post is renamed — so
        // both columns stay NULL for pages, posts, products and PDFs.
        $sql_posts = "CREATE TABLE {$posts_table} (
            id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            post_id BIGINT UNSIGNED DEFAULT NULL,
            post_type VARCHAR(50) NOT NULL DEFAULT 'post',
            title TEXT DEFAULT NULL,
            content LONGTEXT DEFAULT NULL,
            indexed_at DATETIME NOT NULL,
            char_count INT UNSIGNED NOT NULL DEFAULT 0,
            found_fields TEXT DEFAULT NULL,
            training_error TEXT DEFAULT NULL,
            chat_session_id VARCHAR(64) DEFAULT NULL,
            UNIQUE KEY idx_post_id (post_id),
            KEY idx_post_type (post_type)
        ) {$charset};";

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        dbDelta($sql_knowledge);
        dbDelta($sql_conversations);
        dbDelta($sql_token_usage);
        dbDelta($sql_posts);
    }
}