<?php

declare(strict_types=1);

namespace Numinix\SeekmodoWordPress\LocalIndex;

/**
 * MySQL inverted-index tables for Enhanced Native local search.
 */
final class Schema
{
    public const VERSION = 1;
    public const OPT_SCHEMA_VERSION = 'seekmodo_local_index_schema_version';

    public static function tableNames(): array
    {
        global $wpdb;
        $p = $wpdb->prefix;

        return [
            'index'     => $p . 'seekmodo_index',
            'stopwords' => $p . 'seekmodo_stopwords',
            'log'       => $p . 'seekmodo_search_log',
            'synonyms'  => $p . 'seekmodo_synonyms',
            'pins'      => $p . 'seekmodo_pins',
            'rescue'    => $p . 'seekmodo_rescue',
        ];
    }

    public static function install(): void
    {
        if (!function_exists('dbDelta')) {
            require_once ABSPATH . 'wp-admin/includes/upgrade.php';
        }

        global $wpdb;
        $tables = self::tableNames();
        $charset = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE {$tables['index']} (
            doc bigint(20) unsigned NOT NULL,
            term varchar(50) NOT NULL,
            term_reverse varchar(50) NOT NULL DEFAULT '',
            content smallint(5) unsigned NOT NULL DEFAULT 0,
            title smallint(5) unsigned NOT NULL DEFAULT 0,
            comment smallint(5) unsigned NOT NULL DEFAULT 0,
            tag smallint(5) unsigned NOT NULL DEFAULT 0,
            link smallint(5) unsigned NOT NULL DEFAULT 0,
            author smallint(5) unsigned NOT NULL DEFAULT 0,
            category smallint(5) unsigned NOT NULL DEFAULT 0,
            excerpt smallint(5) unsigned NOT NULL DEFAULT 0,
            taxonomy smallint(5) unsigned NOT NULL DEFAULT 0,
            customfield smallint(5) unsigned NOT NULL DEFAULT 0,
            type varchar(210) NOT NULL DEFAULT 'post',
            item bigint(20) unsigned NOT NULL DEFAULT 0,
            PRIMARY KEY (doc, term, item),
            KEY term (term),
            KEY term_reverse (term_reverse),
            KEY typeitem (type(190), item)
        ) {$charset};";

        $sql .= "CREATE TABLE {$tables['stopwords']} (
            stopword varchar(50) NOT NULL,
            PRIMARY KEY (stopword)
        ) {$charset};";

        $sql .= "CREATE TABLE {$tables['log']} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            query varchar(200) NOT NULL DEFAULT '',
            hits int(11) NOT NULL DEFAULT 0,
            logged_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
            user_id bigint(20) unsigned NOT NULL DEFAULT 0,
            source varchar(32) NOT NULL DEFAULT 'local',
            PRIMARY KEY (id),
            KEY query (query(50)),
            KEY logged_at (logged_at)
        ) {$charset};";

        $sql .= "CREATE TABLE {$tables['synonyms']} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            term varchar(100) NOT NULL DEFAULT '',
            synonym varchar(100) NOT NULL DEFAULT '',
            PRIMARY KEY (id),
            KEY term (term)
        ) {$charset};";

        $sql .= "CREATE TABLE {$tables['pins']} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            query_pattern varchar(200) NOT NULL DEFAULT '',
            post_ids text NOT NULL,
            PRIMARY KEY (id),
            KEY query_pattern (query_pattern(100))
        ) {$charset};";

        $sql .= "CREATE TABLE {$tables['rescue']} (
            id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
            query_pattern varchar(200) NOT NULL DEFAULT '',
            post_ids text NOT NULL,
            PRIMARY KEY (id),
            KEY query_pattern (query_pattern(100))
        ) {$charset};";

        dbDelta($sql);
        update_option(self::OPT_SCHEMA_VERSION, self::VERSION);
    }

    public static function needsInstall(): bool
    {
        $v = (int) get_option(self::OPT_SCHEMA_VERSION, 0);

        return $v < self::VERSION;
    }

    public static function rowCount(): int
    {
        global $wpdb;
        $tables = self::tableNames();

        return (int) $wpdb->get_var("SELECT COUNT(*) FROM {$tables['index']}");
    }

    public static function docCount(): int
    {
        global $wpdb;
        $tables = self::tableNames();

        return (int) $wpdb->get_var("SELECT COUNT(DISTINCT doc) FROM {$tables['index']}");
    }
}
