<?php
/**
 * Database layer for site chatbot RAG chunks.
 *
 * @package Maca_Menu_List
 */

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

/**
 * Chunks table name.
 *
 * @return string
 */
function maca_menulist_db_site_chat_chunks_table() {
    global $wpdb;

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

// phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange

/**
 * Create site chat chunks table.
 *
 * @return void
 */
function maca_menulist_db_create_site_chat_tables() {
    global $wpdb;

    if (!isset($wpdb) || empty($wpdb)) {
        return;
    }

    $charset_collate = $wpdb->get_charset_collate();
    $table = maca_menulist_db_site_chat_chunks_table();

    $sql = "CREATE TABLE $table (
        id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
        chunk_key varchar(191) NOT NULL DEFAULT '',
        source_type varchar(32) NOT NULL DEFAULT '',
        source_id varchar(64) NOT NULL DEFAULT '',
        lang varchar(8) NOT NULL DEFAULT '',
        title varchar(255) NOT NULL DEFAULT '',
        content longtext NOT NULL,
        embedding longtext NOT NULL,
        meta_json longtext DEFAULT NULL,
        updated_at datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        PRIMARY KEY  (id),
        UNIQUE KEY chunk_key (chunk_key),
        KEY source_type (source_type),
        KEY source_lookup (source_type, source_id)
    ) $charset_collate;";

    require_once ABSPATH . 'wp-admin/includes/upgrade.php';
    dbDelta($sql);
}

/**
 * Ensure site chat table exists.
 *
 * @return void
 */
function maca_menulist_db_ensure_site_chat_tables() {
    global $wpdb;

    static $ensured = false;

    if ($ensured) {
        return;
    }

    $table = maca_menulist_db_site_chat_chunks_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $exists = $wpdb->get_var($wpdb->prepare('SHOW TABLES LIKE %s', $table));

    if ($exists !== $table) {
        maca_menulist_db_create_site_chat_tables();
    }

    $ensured = true;
}

/**
 * Delete all indexed chunks.
 *
 * @return void
 */
function maca_menulist_db_site_chat_delete_all_chunks() {
    global $wpdb;

    maca_menulist_db_ensure_site_chat_tables();

    $table = maca_menulist_db_site_chat_chunks_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $wpdb->query("TRUNCATE TABLE $table");
}

/**
 * Insert or replace one chunk row.
 *
 * @param array<string, mixed> $data Chunk data.
 * @return bool
 */
function maca_menulist_db_site_chat_upsert_chunk($data) {
    global $wpdb;

    maca_menulist_db_ensure_site_chat_tables();

    $chunk_key = isset($data['chunk_key']) ? sanitize_text_field((string) $data['chunk_key']) : '';
    $content = isset($data['content']) ? (string) $data['content'] : '';

    if ($chunk_key === '' || trim($content) === '') {
        return false;
    }

    $table = maca_menulist_db_site_chat_chunks_table();
    $row = array(
        'chunk_key' => $chunk_key,
        'source_type' => isset($data['source_type']) ? sanitize_key((string) $data['source_type']) : '',
        'source_id' => isset($data['source_id']) ? sanitize_text_field((string) $data['source_id']) : '',
        'lang' => isset($data['lang']) ? sanitize_text_field((string) $data['lang']) : '',
        'title' => isset($data['title']) ? sanitize_text_field((string) $data['title']) : '',
        'content' => $content,
        'embedding' => isset($data['embedding']) ? (string) $data['embedding'] : '[]',
        'meta_json' => isset($data['meta_json']) ? (string) $data['meta_json'] : null,
        'updated_at' => current_time('mysql'),
    );

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $existing_id = $wpdb->get_var(
        $wpdb->prepare(
            "SELECT id FROM $table WHERE chunk_key = %s LIMIT 1",
            $chunk_key
        )
    );

    if ($existing_id) {
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return false !== $wpdb->update($table, $row, array('id' => (int) $existing_id));
    }

    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    return false !== $wpdb->insert($table, $row);
}

/**
 * Count indexed chunks.
 *
 * @return int
 */
function maca_menulist_db_site_chat_count_chunks() {
    global $wpdb;

    maca_menulist_db_ensure_site_chat_tables();

    $table = maca_menulist_db_site_chat_chunks_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    return (int) $wpdb->get_var("SELECT COUNT(*) FROM $table");
}

/**
 * Load all chunks for similarity search.
 *
 * @return array<int, object>
 */
function maca_menulist_db_site_chat_get_all_chunks() {
    global $wpdb;

    maca_menulist_db_ensure_site_chat_tables();

    $table = maca_menulist_db_site_chat_chunks_table();
    // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
    $rows = $wpdb->get_results("SELECT id, chunk_key, source_type, source_id, lang, title, content, embedding, meta_json FROM $table");

    return is_array($rows) ? $rows : array();
}

// phpcs:enable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.SchemaChange
