<?php
/**
 * Database installer + helpers
 */
class AIT_AIRCE_DB {

    const SCHEMA_VERSION = 1;

    public static function get_schema_option_name() {
        return 'ait-airce-db-schema-version';
    }

    public function install() {
        $this->maybe_upgrade();
    }

    public function maybe_upgrade() {
        $installed = intval( get_option( self::get_schema_option_name(), 0 ) );
        if ( $installed >= self::SCHEMA_VERSION ) {
            return;
        }

        $this->create_tables();
        update_option( self::get_schema_option_name(), self::SCHEMA_VERSION );
    }

    public static function table( $suffix ) {
        global $wpdb;
        return $wpdb->prefix . 'ait_airce_' . $suffix;
    }

    protected function create_tables() {
        global $wpdb;

        require_once ABSPATH . 'wp-admin/includes/upgrade.php';

        $charset_collate = $wpdb->get_charset_collate();

        $t_index = self::table( 'index_item' );
        $t_embed = self::table( 'embedding' );
        $t_cache = self::table( 'reco_cache' );

        $sql_index = "CREATE TABLE {$t_index} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            post_id BIGINT UNSIGNED NOT NULL,
            post_type VARCHAR(32) NOT NULL,
            status VARCHAR(20) NOT NULL DEFAULT 'active',
            content_hash CHAR(32) NOT NULL,
            content_updated_gmt DATETIME NOT NULL,
            indexed_at_gmt DATETIME NULL,
            model_key VARCHAR(64) NOT NULL,
            embed_dim SMALLINT UNSIGNED NOT NULL DEFAULT 0,
            index_version INT UNSIGNED NOT NULL DEFAULT 1,
            last_error VARCHAR(255) NULL,
            PRIMARY KEY  (id),
            UNIQUE KEY uniq_post (post_id),
            KEY idx_status_type (status, post_type),
            KEY idx_indexed (indexed_at_gmt)
        ) {$charset_collate};";

        $sql_embed = "CREATE TABLE {$t_embed} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            index_item_id BIGINT UNSIGNED NOT NULL,
            vector_json LONGTEXT NOT NULL,
            vector_norm FLOAT NULL,
            created_at_gmt DATETIME NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uniq_item (index_item_id),
            KEY idx_created (created_at_gmt)
        ) {$charset_collate};";

        $sql_cache = "CREATE TABLE {$t_cache} (
            id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
            source_post_id BIGINT UNSIGNED NOT NULL,
            context_key VARCHAR(64) NOT NULL DEFAULT 'default',
            algo_version INT UNSIGNED NOT NULL DEFAULT 1,
            generated_at_gmt DATETIME NOT NULL,
            items_json LONGTEXT NOT NULL,
            PRIMARY KEY (id),
            UNIQUE KEY uniq_source_context (source_post_id, context_key, algo_version),
            KEY idx_generated (generated_at_gmt)
        ) {$charset_collate};";

        dbDelta( $sql_index );
        dbDelta( $sql_embed );
        dbDelta( $sql_cache );
    }

    /**
     * Simple stats helper for the Dashboard.
     *
     * @return int
     */
    public function count_indexed_items() {
        global $wpdb;
        $t_index = esc_sql( self::table( 'index_item' ) );

        // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared -- table name from trusted source
        $count = $wpdb->get_var( "SELECT COUNT(*) FROM {$t_index} WHERE status = 'active'" );
        return is_numeric( $count ) ? (int) $count : 0;
    }
}