<?php

namespace WPDRMS\ASP\Synonyms\ORM;

use WPDRMS\PluginCore\ORM\Model;

defined('ABSPATH') || die("You can't access this file directly.");

class Synonym extends Model {
	protected static string $table_name = 'asp_synonyms';
	protected static bool $global       = true;

	/**
	 * @var array<string, string>
	 */
	protected static array $columns = array(
		'id'                      => 'INT(11) NOT NULL AUTO_INCREMENT',
		'keyword'                 => 'VARCHAR(50) NOT NULL',
		'synonyms'                => 'TEXT NOT NULL',
		'lang'                    => 'VARCHAR(20) NOT NULL',
		'PRIMARY KEY'             => '(id)',
		'UNIQUE KEY keyword_lang' => '(keyword, lang)',
	);

	public int $id       = 0;
	public string $keyword  = '';
	public string $synonyms = '';
	public string $lang     = '';

	/**
	 * INSERT IGNORE a single synonym row.
	 *
	 * @return int rows affected
	 */
	public static function addIgnore( string $keyword, string $synonyms, string $lang ): int {
		global $wpdb;
		$table = static::getTableName();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->query(
			$wpdb->prepare(
				"INSERT IGNORE INTO `{$table}` (keyword, synonyms, lang) VALUES(%s, %s, %s)",
				$keyword,
				$synonyms,
				$lang
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * UPDATE synonyms for a keyword+lang pair.
	 *
	 * @return int rows affected
	 */
	public static function updateSynonyms( string $keyword, string $synonyms, string $lang ): int {
		global $wpdb;
		$table = static::getTableName();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->query(
			$wpdb->prepare(
				"UPDATE `{$table}` SET synonyms=%s WHERE keyword=%s AND lang=%s",
				$synonyms,
				$keyword,
				$lang
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * DELETE a row by keyword+lang.
	 *
	 * @return int rows affected
	 */
	public static function deleteByKeywordLang( string $keyword, string $lang ): int {
		global $wpdb;
		$table = static::getTableName();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $wpdb->query(
			$wpdb->prepare(
				"DELETE FROM `{$table}` WHERE keyword=%s AND lang=%s",
				$keyword,
				$lang
			)
		);
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
	}

	/**
	 * Relevance-ranked keyword search with optional offset for pagination.
	 *
	 * @param string $keyword
	 * @param string $language  Pass 'any' to search across all languages.
	 * @param int    $limit
	 * @param bool   $exact
	 * @param int    $offset
	 * @return array<int, array<string, string>>
	 */
	public static function search( string $keyword, string $language, int $limit, bool $exact, int $offset = 0 ): array {
		global $wpdb;
		$table = static::getTableName();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.LikeWildcardsInQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( $keyword === '' ) {
			if ( $language !== 'any' ) {
				$res = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT keyword, synonyms, lang, id FROM `{$table}` WHERE lang = %s ORDER BY id DESC LIMIT %d OFFSET %d",
						$language,
						$limit,
						$offset
					),
					ARRAY_A
				);
			} else {
				$res = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT keyword, synonyms, lang, id FROM `{$table}` ORDER BY id DESC LIMIT %d OFFSET %d",
						$limit,
						$offset
					),
					ARRAY_A
				);
			}
		} else {
			$kw = $exact ? $keyword : '%' . $wpdb->esc_like($keyword) . '%';
			if ( $language !== 'any' ) {
				$res = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT keyword, synonyms, lang, id,
						((case when (keyword LIKE %s) then 1 else 0 end) + (case when (keyword LIKE %s) then 1 else 0 end)) as `relevance`
						FROM `{$table}` WHERE keyword LIKE %s AND lang = %s
						ORDER BY relevance DESC, id DESC LIMIT %d OFFSET %d",
						$keyword,
						$wpdb->esc_like($keyword) . '%',
						$kw,
						$language,
						$limit,
						$offset
					),
					ARRAY_A
				);
			} else {
				$res = $wpdb->get_results(
					$wpdb->prepare(
						"SELECT keyword, synonyms, lang, id,
						((case when (keyword LIKE %s) then 1 else 0 end) + (case when (keyword LIKE %s) then 1 else 0 end)) as `relevance`
						FROM `{$table}` WHERE keyword LIKE %s
						ORDER BY relevance DESC, id DESC LIMIT %d OFFSET %d",
						$keyword,
						$wpdb->esc_like($keyword) . '%',
						$kw,
						$limit,
						$offset
					),
					ARRAY_A
				);
			}
		}
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.LikeWildcardsInQuery, WordPress.DB.DirectDatabaseQuery.NoCaching

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

	/**
	 * Returns the total number of rows matching a search (for pagination).
	 *
	 * @param string $keyword
	 * @param string $language  Pass 'any' to search across all languages.
	 * @param bool   $exact
	 * @return int
	 */
	public static function countSearch( string $keyword, string $language, bool $exact = false ): int {
		global $wpdb;
		$table = static::getTableName();
		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.LikeWildcardsInQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		if ( $keyword === '' ) {
			if ( $language !== 'any' ) {
				$count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` WHERE lang = %s", $language ) );
			} else {
				$count = $wpdb->get_var( "SELECT COUNT(*) FROM `{$table}`" );
			}
		} else {
			$kw = $exact ? $keyword : '%' . $wpdb->esc_like($keyword) . '%';
			if ( $language !== 'any' ) {
				$count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` WHERE keyword LIKE %s AND lang = %s", $kw, $language ) );
			} else {
				$count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM `{$table}` WHERE keyword LIKE %s", $kw ) );
			}
		}
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.LikeWildcardsInQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
		return (int) $count;
	}

	/**
	 * Export all synonym rows ordered by id ASC.
	 *
	 * @return array<int, array<string, string>>
	 */
	public static function exportAll(): array {
		global $wpdb;
		$table = static::getTableName();
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery.NoCaching
		$res = $wpdb->get_results(
			"SELECT keyword, synonyms, lang FROM `{$table}` ORDER BY id ASC LIMIT 100000",
			ARRAY_A
		);
		return is_array($res) ? $res : array();
	}

	/**
	 * Batch INSERT IGNORE from an import payload.
	 *
	 * @param array<int, array{keyword: string, synonyms: string, lang: string}> $rows
	 * @return int total rows inserted
	 */
	public static function bulkImportIgnore( array $rows ): int {
		global $wpdb;
		$table    = static::getTableName();
		$inserted = 0;
		$values   = array();

		foreach ( $rows as $row ) {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$values[] = $wpdb->prepare('(%s, %s, %s)', $row['keyword'], $row['synonyms'], $row['lang']);
			if ( count($values) >= 200 ) {
				$values_str = implode(', ', $values);
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$inserted += (int) $wpdb->query("INSERT IGNORE INTO `{$table}` (`keyword`, `synonyms`, `lang`) VALUES {$values_str}");
				$values = array();
			}
		}

		if ( !empty($values) ) {
			$values_str = implode(', ', $values);
			// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
			$inserted += (int) $wpdb->query("INSERT IGNORE INTO `{$table}` (`keyword`, `synonyms`, `lang`) VALUES {$values_str}");
		}

		return $inserted;
	}
}
