<?php

namespace WPDRMS\ASP\Index\ORM;

use WPDRMS\ASP\Index\IndexExclusionEvaluator;
use WPDRMS\PluginCore\ORM\Model;
use WPDRMS\Utils\Str;

require_once ASP_PATH . 'src/server/CompatLoader.php';

class IndexTable extends Model {
	protected static string $table_name = 'asp_index';
	protected static bool $global       = true;

	/** @var string[] */
	protected static $primary_key = array( 'doc', 'term', 'blogid' );

	/**
	 * Column definitions used by Model helpers (getColumnFormat, bulkInsert, save).
	 * createTable() is overridden to preserve per-column charset and named indexes.
	 *
	 * @var array<string, string>
	 */
	protected static array $columns = array(
		'doc'                               => "int UNSIGNED NOT NULL DEFAULT '0'",
		'term'                              => "varchar(150) NOT NULL DEFAULT '0'",
		'term_reverse'                      => "varchar(150) NOT NULL DEFAULT '0'",
		'blogid'                            => "mediumint(9) UNSIGNED NOT NULL DEFAULT '0'",
		'content'                           => "smallint(9) UNSIGNED NOT NULL DEFAULT '0'",
		'title'                             => "tinyint(3) UNSIGNED NOT NULL DEFAULT '0'",
		'comment'                           => "tinyint(3) UNSIGNED NOT NULL DEFAULT '0'",
		'tag'                               => "tinyint(3) UNSIGNED NOT NULL DEFAULT '0'",
		'link'                              => "tinyint(3) UNSIGNED NOT NULL DEFAULT '0'",
		'author'                            => "tinyint(3) UNSIGNED NOT NULL DEFAULT '0'",
		'excerpt'                           => "tinyint(3) UNSIGNED NOT NULL DEFAULT '0'",
		'customfield'                       => "smallint(9) UNSIGNED NOT NULL DEFAULT '0'",
		'post_type'                         => "varchar(50) NOT NULL DEFAULT 'post'",
		'lang'                              => "varchar(8) NOT NULL DEFAULT '0'",
		'PRIMARY KEY doctermblogid'         => '(doc, term, blogid)',
		'INDEX idx_term_ptype_blogid_lang'  => '(term(20), post_type(20), blogid, lang(8))',
		'INDEX idx_rterm_ptype_blogid_lang' => '(term_reverse(20), post_type(20), blogid, lang(8))',
		'INDEX idx_doc_blogid'              => '(doc, blogid)',
	);

	public int $doc             = 0;
	public string $term         = '';
	public string $term_reverse = '';
	public int $blogid          = 0;
	public int $content         = 0;
	public int $title           = 0;
	public int $comment         = 0;
	public int $tag             = 0;
	public int $link            = 0;
	public int $author          = 0;
	public int $excerpt         = 0;
	public int $customfield     = 0;
	public string $post_type    = 'post';
	public string $lang         = '0';

	/**
	 * Creates the index table and cleans up legacy index names.
	 */
	public static function createTable(): void {
		parent::createTable();
		global $wpdb;
		$table    = static::getTableName();
		$indices  = $wpdb->get_results( "SHOW INDEX FROM $table" ); // phpcs:ignore
		$existing = array_column( (array) $indices, 'Key_name' );
		foreach ( array( 'term_ptype_blogid', 'rterm_ptype_blogid', 'doc', 'doctermitem' ) as $old ) {
			if ( in_array( $old, $existing, true ) ) {
				$wpdb->query( "DROP INDEX $old ON $table" ); // phpcs:ignore
			}
		}
	}

	/**
	 * Called before a full re-index: recreates/repairs the table structure and sets row format.
	 */
	public static function beforeIndex( bool $compress = false ): void {
		global $wpdb;
		static::createTable();
		IndexSkipTable::createTable();
		IndexSkipTable::truncateTable();
		$table = static::getTableName();
		$wpdb->query(
			$compress
			? "ALTER TABLE $table ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8"  // phpcs:ignore
			: "ALTER TABLE $table ROW_FORMAT=DEFAULT KEY_BLOCK_SIZE=0"     // phpcs:ignore
		);
	}

	public static function optimize(): bool {
		global $wpdb;
		return (bool) $wpdb->query( 'OPTIMIZE TABLE ' . static::getTableName() ); // phpcs:ignore
	}

	/**
	 * @param int|int[] $post_id
	 */
	public static function removeDocument( $post_id ): void {
		global $wpdb;
		$table = static::getTableName();
		if ( is_array( $post_id ) ) {
			$ids = implode( ',', array_map( 'intval', $post_id ) );
			$wpdb->query( "DELETE FROM $table WHERE doc IN ($ids)" ); // phpcs:ignore
		} else {
			$wpdb->query( $wpdb->prepare( "DELETE FROM $table WHERE doc = %d", $post_id ) ); // phpcs:ignore
		}
	}

	/**
	 * Inserts tokens for a post into the index table in batches of 200.
	 *
	 * @param object            $the_post
	 * @param array<int, array> $tokens
	 * @param int               $blog_id
	 * @param string            $lang
	 * @return int Number of tokens inserted.
	 */
	public static function insertTokensToDB( object $the_post, array $tokens, int $blog_id, string $lang ): int {
		global $wpdb;
		$table  = static::getTableName();
		$values = array();

		if ( count( $tokens ) <= 0 ) {
			return 0;
		}

		foreach ( $tokens as $d ) {
			$term = trim( $d['_keyword'] );

			if ( isset( $d['_no_reverse'] ) && $d['_no_reverse'] === true ) {
				$value = $wpdb->prepare(
					'(%d, %s, %s, %d, %d, %d, %d, %d, %d, %d, %d, %d, %s, %s)',
					$the_post->ID,
					$term,
					'',
					$blog_id,
					$d['content'],
					$d['title'],
					$d['comment'],
					$d['tag'],
					$d['link'],
					$d['author'],
					$d['excerpt'],
					$d['customfield'],
					$the_post->post_type,
					$lang
				);
			} else {
				$value = $wpdb->prepare(
					'(%d, %s, REVERSE(%s), %d, %d, %d, %d, %d, %d, %d, %d, %d, %s, %s)',
					$the_post->ID,
					$term,
					$term,
					$blog_id,
					$d['content'],
					$d['title'],
					$d['comment'],
					$d['tag'],
					$d['link'],
					$d['author'],
					$d['excerpt'],
					$d['customfield'],
					$the_post->post_type,
					$lang
				);
			}

			$values[] = $value;

			if ( count( $values ) > 199 ) {
				$wpdb->query( "INSERT IGNORE INTO $table (`doc`,`term`,`term_reverse`,`blogid`,`content`,`title`,`comment`,`tag`,`link`,`author`,`excerpt`,`customfield`,`post_type`,`lang`) VALUES " . implode( ', ', $values ) ); // phpcs:ignore
				$values = array();
			}
		}

		if ( count( $values ) > 0 ) {
			$wpdb->query( "INSERT IGNORE INTO $table (`doc`,`term`,`term_reverse`,`blogid`,`content`,`title`,`comment`,`tag`,`link`,`author`,`excerpt`,`customfield`,`post_type`,`lang`) VALUES " . implode( ', ', $values ) ); // phpcs:ignore
		}

		return count( $tokens );
	}

	/**
	 * Returns post IDs to index.
	 *
	 * For extend mode (args['extend']=true) uses LEFT JOIN to find unindexed posts.
	 * For re-index mode uses a cursor (ID > $cursor) to page through all posts efficiently.
	 *
	 * @param array<string, mixed> $args
	 * @param int                  $cursor Last indexed post ID (re-index mode only).
	 * @return object[]
	 */
	public static function getPostIdsToIndex( array $args, int $cursor = 0 ): array {
		global $wpdb;
		$table       = static::getTableName();
		$parent_join = '';

		$_statuses = explode( ',', $args['post_statuses'] );
		foreach ( $_statuses as &$sv ) {
			$sv = trim( $sv );
		}
		$valid_status = "'" . implode( "', '", $_statuses ) . "'";

		if ( count( $args['post_types'] ) > 0 ) {
			$post_types = $args['post_types'];
			if ( class_exists( 'WooCommerce' ) && in_array( 'product_variation', $post_types, true ) ) {
				$post_types  = array_diff( $post_types, array( 'product_variation' ) );
				$rest        = count( $post_types ) > 0 ? " OR post.post_type IN('" . implode( "', '", $post_types ) . "') " : '';
				$parent_join = "LEFT JOIN $wpdb->posts parent ON (post.post_parent = parent.ID)";
				$restriction = " AND ( (post.post_type = 'product_variation' AND parent.post_status IN($valid_status) ) $rest )";
			} else {
				$restriction = " AND post.post_type IN ('" . implode( "', '", $post_types ) . "')";
			}
		} else {
			return array();
		}

		$mimes_restrict = '';
		if ( in_array( 'attachment', $args['post_types'], true ) ) {
			$restriction .= static::getAttachmentDirRestrictionQueryPart( $args );
			if ( $args['attachment_mime_types'] !== '' ) {
				$mimes_arr = wpd_comma_separated_to_array( $args['attachment_mime_types'] );
				if ( count( $mimes_arr ) > 0 ) {
					$mimes_restrict = "OR ( post.post_status = 'inherit' AND post.post_mime_type IN ('" . implode( "','", $mimes_arr ) . "') )";
				}
			}
		}

		$post_password = $args['post_password_protected'] == 0 ? " AND (post.post_password = '') " : '';
		$limit         = min( (int) $args['limit'], 1000 );

		$add_where             = apply_filters( 'asp/index/database/get_posts_to_index/query/add_where', '', $args )
			. IndexExclusionEvaluator::sqlWhere( $args );
		$add_where_post_status = apply_filters( 'asp/index/database/get_posts_to_index/query/add_where_post_status', '', $args );

		if ( ! empty( $args['extend'] ) ) {
			$skip_table = IndexSkipTable::getTableName();
			$q          = "SELECT post.ID
					FROM $wpdb->posts post
					$parent_join
					LEFT JOIN $table r ON (post.ID = r.doc AND r.blogid = " . (int) $args['blog_id'] . ")
					LEFT JOIN $skip_table skip ON (post.ID = skip.doc AND skip.blogid = " . (int) $args['blog_id'] . ")
					WHERE
							r.doc IS NULL
					AND
							skip.doc IS NULL
					AND
						(
							post.post_status IN ($valid_status)
							$mimes_restrict
							$add_where_post_status
						)
					$restriction
					$post_password
					$add_where
					ORDER BY post.ID ASC
					LIMIT $limit";
		} else {
			$cursor_clause = $cursor > 0 ? ' AND post.ID > ' . (int) $cursor : '';
			$q             = "SELECT post.ID
					FROM $wpdb->posts post
					$parent_join
					WHERE
						(
							post.post_status IN ($valid_status)
							$mimes_restrict
							$add_where_post_status
						)
					$restriction
					$cursor_clause
					$post_password
					$add_where
					ORDER BY post.ID ASC
					LIMIT $limit";
		}

		return $wpdb->get_results( $q ); // phpcs:ignore
	}

	/**
	 * @param array<string, mixed> $args
	 * @param bool                 $check_only Return 1 if any posts remain, 0 otherwise.
	 * @param int                  $cursor Last indexed post ID (re-index mode only).
	 * @return int
	 */
	public static function getPostIdsToIndexCount( array $args, bool $check_only = false, int $cursor = 0 ): int {
		if ( defined( 'ASP_INDEX_BYPASS_COUNT' ) ) {
			return 9999;
		}
		global $wpdb;
		$table       = static::getTableName();
		$parent_join = '';

		$_statuses = explode( ',', $args['post_statuses'] );
		foreach ( $_statuses as &$sv ) {
			$sv = trim( $sv );
		}
		$valid_status   = "'" . implode( "', '", $_statuses ) . "'";
		$mimes_restrict = '';

		if ( count( $args['post_types'] ) > 0 ) {
			$post_types = $args['post_types'];
			if ( class_exists( 'WooCommerce' ) && in_array( 'product_variation', $post_types, true ) ) {
				$post_types  = array_diff( $post_types, array( 'product_variation' ) );
				$rest        = count( $post_types ) > 0 ? " OR post.post_type IN('" . implode( "', '", $post_types ) . "') " : '';
				$parent_join = "LEFT JOIN $wpdb->posts parent ON (post.post_parent = parent.ID)";
				$restriction = " AND ( (post.post_type = 'product_variation' AND parent.post_status IN($valid_status) ) $rest )";
			} else {
				$restriction = " AND post.post_type IN ('" . implode( "', '", $post_types ) . "')";
			}
		} else {
			return 0;
		}

		if ( in_array( 'attachment', $args['post_types'], true ) ) {
			$restriction .= static::getAttachmentDirRestrictionQueryPart( $args );
			if ( $args['attachment_mime_types'] !== '' ) {
				$mimes_arr = wpd_comma_separated_to_array( $args['attachment_mime_types'] );
				if ( count( $mimes_arr ) > 0 ) {
					$mimes_restrict = "OR ( post.post_status = 'inherit' AND post.post_mime_type IN ('" . implode( "','", $mimes_arr ) . "') )";
				}
			}
		}

		$post_password         = $args['post_password_protected'] == 0 ? " AND (post.post_password = '') " : '';
		$add_where             = apply_filters( 'asp/index/database/get_posts_to_index/query/add_where', '', $args )
			. IndexExclusionEvaluator::sqlWhere( $args );
		$add_where_post_status = apply_filters( 'asp/index/database/get_posts_to_index/query/add_where_post_status', '', $args );

		if ( ! empty( $args['extend'] ) ) {
			// Extend mode: LEFT JOIN to find posts not yet in the index
			$skip_table = IndexSkipTable::getTableName();
			if ( $check_only ) {
				$q = "SELECT 1
						FROM $wpdb->posts post
						$parent_join
						LEFT JOIN $table r ON (post.ID = r.doc AND r.blogid = " . (int) $args['blog_id'] . ")
						LEFT JOIN $skip_table skip ON (post.ID = skip.doc AND skip.blogid = " . (int) $args['blog_id'] . ")
						WHERE
							r.doc IS NULL
						AND
							skip.doc IS NULL
						AND
							(
								post.post_status IN ($valid_status)
								$mimes_restrict
								$add_where_post_status
							)
						$restriction
						$post_password
						$add_where
						LIMIT 1";
			} else {
				$q = "SELECT COUNT(*)
						FROM $wpdb->posts post
						$parent_join
						LEFT JOIN $table r ON (post.ID = r.doc AND r.blogid = " . (int) $args['blog_id'] . ")
						LEFT JOIN $skip_table skip ON (post.ID = skip.doc AND skip.blogid = " . (int) $args['blog_id'] . ")
						WHERE
								r.doc IS NULL
						AND
								skip.doc IS NULL
						AND
							(
								post.post_status IN ($valid_status)
								$mimes_restrict
								$add_where_post_status
							)
						$restriction
						$post_password
						$add_where";
			}
		} else {
			// Re-index mode: table is truncated; count posts remaining beyond the cursor
			$cursor_clause = $cursor > 0 ? ' AND post.ID > ' . (int) $cursor : '';
			if ( $check_only ) {
				$q = "SELECT 1
						FROM $wpdb->posts post
						$parent_join
						WHERE
							(
								post.post_status IN ($valid_status)
								$mimes_restrict
								$add_where_post_status
							)
						$restriction
						$cursor_clause
						$post_password
						$add_where
						LIMIT 1";
			} else {
				$q = "SELECT COUNT(*)
						FROM $wpdb->posts post
						$parent_join
						WHERE
							(
								post.post_status IN ($valid_status)
								$mimes_restrict
								$add_where_post_status
							)
						$restriction
						$cursor_clause
						$post_password
						$add_where";
			}
		}

		return (int) $wpdb->get_var( $q ); // phpcs:ignore
	}

	public static function getPostsIndexed(): int {
		if ( defined( 'ASP_INDEX_BYPASS_COUNT' ) ) {
			return 9999;
		}
		global $wpdb;
		$table = static::getTableName();
		return (int) $wpdb->get_var( "SELECT COUNT(count) FROM (SELECT 1 as count FROM $table GROUP BY doc) as A" ); // phpcs:ignore
	}

	public static function getTotalKeywords(): int {
		if ( defined( 'ASP_INDEX_BYPASS_COUNT' ) ) {
			return 9999;
		}
		global $wpdb;
		$table = static::getTableName();
		$sql   = is_multisite()
			? "SELECT COUNT(doc) FROM $table"
			: "SELECT COUNT(doc) FROM $table WHERE blogid = " . get_current_blog_id();
		return (int) $wpdb->get_var( $sql ); // phpcs:ignore
	}

	public static function isEmpty(): bool {
		global $wpdb;
		$table = static::getTableName();
		return $wpdb->query( "SELECT 1 FROM $table LIMIT 1" ) == 0; // phpcs:ignore
	}

	// ----------------------------------- PRIVATE HELPERS -----------------------------------

	private static function getAttachmentDirRestrictionQueryPart( array $args ): string {
		global $wpdb;
		$attachment_dir_query = '';

		$uploads = wp_get_upload_dir();
		if ( false !== $uploads['error'] ) {
			return '';
		}

		$uploads_relative_dir = trailingslashit( str_replace( ABSPATH, '', $uploads['basedir'] ) );

		$exclude_directories = array_filter(
			array_map(
				function ( $dir ) use ( $uploads_relative_dir ) {
					return str_replace( $uploads_relative_dir, '', $dir );
				},
				$args['attachment_exclude_directories']
			)
		);
		$include_directories = array_filter(
			array_map(
				function ( $dir ) use ( $uploads_relative_dir ) {
					return str_replace( $uploads_relative_dir, '', $dir );
				},
				$args['attachment_include_directories']
			)
		);

		if ( count( $exclude_directories ) > 0 ) {
			$not_like_values       = implode(
				'AND',
				array_map(
					function ( $directory ) use ( $wpdb ) {
						return " $wpdb->postmeta.meta_value NOT LIKE '" . Str::escape( $directory ) . "%'";
					},
					$exclude_directories
				)
			);
			$attachment_dir_query .= " AND (
			(
			  (
				SELECT IF((meta_key IS NULL OR meta_value = ''), -1, COUNT(meta_id))
				FROM $wpdb->postmeta
				WHERE $wpdb->postmeta.post_id = post.ID AND $wpdb->postmeta.meta_key='_wp_attached_file'
				LIMIT 1
			  ) = -1
			 OR
			  (
				SELECT COUNT(meta_id) as mtc
				FROM $wpdb->postmeta
				WHERE $wpdb->postmeta.post_id = post.ID AND $wpdb->postmeta.meta_key='_wp_attached_file' AND
				($not_like_values)
				ORDER BY mtc
				LIMIT 1
			  ) >= 1
			) )";
		}

		if ( count( $include_directories ) > 0 ) {
			$not_like_values       = implode(
				'OR',
				array_map(
					function ( $directory ) use ( $wpdb ) {
						return " $wpdb->postmeta.meta_value LIKE '" . Str::escape( $directory ) . "%'";
					},
					$include_directories
				)
			);
			$attachment_dir_query .= " AND (
			(
			  (
				SELECT IF((meta_key IS NULL OR meta_value = ''), -1, COUNT(meta_id))
				FROM $wpdb->postmeta
				WHERE $wpdb->postmeta.post_id = post.ID AND $wpdb->postmeta.meta_key='_wp_attached_file'
				LIMIT 1
			  ) = -1
			 OR
			  (
				SELECT COUNT(meta_id) as mtc
				FROM $wpdb->postmeta
				WHERE $wpdb->postmeta.post_id = post.ID AND $wpdb->postmeta.meta_key='_wp_attached_file' AND
				($not_like_values)
				ORDER BY mtc
				LIMIT 1
			  ) >= 1
			) )";
		}

		return $attachment_dir_query;
	}
}
