<?php
/**
 * Content index model
 */

if (!defined('ABSPATH')) {
    exit;
}
// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table names from raplsaich_require_table() cannot use placeholders

class RAPLSAICH_Content_Index {

    /**
     * Table name — whitelist-validated via raplsaich_validated_table().
     */
    private static function get_table_name(): string {
        return trim(raplsaich_validated_table('raplsaich_index'), '`');
    }

    /**
     * Create index
     */
    public static function create($data) {
        global $wpdb;
        $table = self::get_table_name();

        $required = ['post_id', 'post_type', 'title', 'content', 'content_hash', 'url'];
        foreach ($required as $key) {
            if (!isset($data[$key])) {
                return false;
            }
        }

        $insert_data = [
            'post_id'      => $data['post_id'],
            'post_type'    => $data['post_type'],
            'title'        => $data['title'],
            'content'      => $data['content'],
            'content_hash' => $data['content_hash'],
            'chunk_index'  => $data['chunk_index'] ?? 0,
            'url'          => $data['url'],
        ];

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery
        return $wpdb->insert($table, $insert_data, ['%d', '%s', '%s', '%s', '%s', '%d', '%s']);
    }

    /**
     * Get index by post ID
     */
    public static function get_by_post_id($post_id) {
        global $wpdb;
        $table = self::get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results($wpdb->prepare(
            "SELECT * FROM `{$table}` WHERE post_id = %d ORDER BY chunk_index ASC",
            $post_id
        ), ARRAY_A);
    }

    /**
     * Delete index by post ID
     */
    public static function delete_by_post_id($post_id) {
        global $wpdb;
        $table = self::get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->delete($table, ['post_id' => $post_id], ['%d']);
    }

    /**
     * Delete by primary key (id)
     */
    public static function delete_by_id($id) {
        global $wpdb;
        $table = self::get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        return $wpdb->delete($table, ['id' => $id], ['%d']);
    }

    /**
     * Check if content hash exists
     */
    public static function hash_exists($post_id, $content_hash) {
        global $wpdb;
        $table = self::get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $exists = $wpdb->get_var($wpdb->prepare(
            "SELECT COUNT(*) FROM `{$table}` WHERE post_id = %d AND content_hash = %s",
            $post_id,
            $content_hash
        ));

        return $exists > 0;
    }

    /**
     * Get total index count
     */
    public static function get_count() {
        global $wpdb;
        $table = self::get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return (int) $wpdb->get_var("SELECT COUNT(DISTINCT post_id) FROM `{$table}`");
    }

    /**
     * Delete all indexes
     */
    public static function truncate() {
        global $wpdb;
        $table = self::get_table_name();
        if ($table === '') {
            return false;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->query("TRUNCATE TABLE `{$table}`");
        if ($result === false) {
            // Fallback: TRUNCATE may fail due to DB permissions or configuration
            // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, PluginCheck.Security.DirectDB.UnescapedDBParameter
            $result = $wpdb->query("DELETE FROM `{$table}`");
        }
        return $result;
    }

    /**
     * Get index list (for admin)
     */
    public static function get_list($args = []) {
        global $wpdb;
        $table = self::get_table_name();

        $defaults = [
            'per_page'  => 20,
            'page'      => 1,
            'post_type' => '',
            'orderby'   => 'indexed_at',
            'order'     => 'DESC',
        ];

        $args = wp_parse_args($args, $defaults);
        $offset = ($args['page'] - 1) * $args['per_page'];

        $where = 'chunk_index = 0'; // Only first chunk of each post
        $params = [];

        if (!empty($args['post_type'])) {
            $where .= ' AND post_type = %s';
            $params[] = $args['post_type'];
        }

        // Validate orderby
        $allowed_orderby = ['title', 'post_type', 'indexed_at'];
        $orderby_col = in_array($args['orderby'], $allowed_orderby, true) ? $args['orderby'] : 'indexed_at';
        $order = strtoupper($args['order']) === 'ASC' ? 'ASC' : 'DESC';

        $params[] = $args['per_page'];
        $params[] = $offset;

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- Table name, WHERE, and ORDER BY are safe internal values
        $sql = "SELECT * FROM `{$table}` WHERE {$where} ORDER BY {$orderby_col} {$order} LIMIT %d OFFSET %d";

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results($wpdb->prepare($sql, ...$params), ARRAY_A);
    }

    /**
     * Update embedding for a chunk
     *
     * @param int    $id               Chunk row ID
     * @param string $packed_embedding  Binary packed float32 embedding
     * @param string $model             Embedding model name
     * @return bool True on success
     */
    public static function update_embedding(int $id, string $packed_embedding, string $model): bool {
        global $wpdb;
        $table = self::get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = $wpdb->update(
            $table,
            [
                'embedding'       => $packed_embedding,
                'embedding_model' => $model,
            ],
            ['id' => $id],
            ['%s', '%s'],
            ['%d']
        );

        return $result !== false;
    }

    /**
     * Get embedding stats (total chunks vs embedded chunks)
     *
     * @return array{total_chunks: int, embedded_chunks: int, embedding_model: string|null}
     */
    public static function get_embedding_stats(): array {
        global $wpdb;
        $table = self::get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $total = (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$table}`");

        // Check if embedding column exists before querying it
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $has_col = !empty($wpdb->get_results("SHOW COLUMNS FROM `{$table}` LIKE 'embedding'"));
        if (!$has_col) {
            return ['total_chunks' => $total, 'embedded_chunks' => 0, 'embedding_model' => null];
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $embedded = (int) $wpdb->get_var("SELECT COUNT(*) FROM `{$table}` WHERE embedding IS NOT NULL");

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $model = $wpdb->get_var("SELECT embedding_model FROM `{$table}` WHERE embedding IS NOT NULL LIMIT 1");

        return [
            'total_chunks'    => $total,
            'embedded_chunks' => $embedded,
            'embedding_model' => $model,
        ];
    }

    /**
     * Get IDs of chunks without embeddings
     *
     * @param int $limit Max chunks to return
     * @return array Array of rows with id and content
     */
    public static function get_unembedded_chunks(int $limit = 100): array {
        global $wpdb;
        $table = self::get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $has_col = !empty($wpdb->get_results("SHOW COLUMNS FROM `{$table}` LIKE 'embedding'"));
        if (!$has_col) {
            return [];
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        return $wpdb->get_results($wpdb->prepare(
            "SELECT id, title, content FROM `{$table}` WHERE embedding IS NULL ORDER BY id ASC LIMIT %d",
            $limit
        ), ARRAY_A);
    }

    /**
     * Clear all embeddings (for model change or reset)
     *
     * @return bool True on success
     */
    public static function clear_all_embeddings(): bool {
        global $wpdb;
        $table = self::get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $has_col = !empty($wpdb->get_results("SHOW COLUMNS FROM `{$table}` LIKE 'embedding'"));
        if (!$has_col) {
            return true;
        }

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->query("UPDATE `{$table}` SET embedding = NULL, embedding_model = NULL");

        return $result !== false;
    }

    /**
     * Get count of indexed items grouped by post type
     */
    public static function get_post_type_counts() {
        global $wpdb;
        $table = self::get_table_name();

        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $results = $wpdb->get_results(
            "SELECT post_type, COUNT(DISTINCT post_id) as count FROM `{$table}` GROUP BY post_type ORDER BY count DESC",
            ARRAY_A
        );

        $counts = [];
        foreach ($results as $row) {
            $counts[$row['post_type']] = (int) $row['count'];
        }
        return $counts;
    }
}
