<?php
declare( strict_types=1 );

namespace FiboFilters\Indexer\Filters;

use FiboFilters\Indexer\BuildInfo;
use FiboFilters\Indexer\Logger;
use FiboFilters\Integrations\Multilingual;
use FiboFilters\Util\Cache;
use FiboFilters\Util\DbUtil;
use FiboFilters\WP\WPDB;
use FiboFilters\WPDBException;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

/**
 * Class Database
 */
class Database {
	const FILTERS_SOURCES_INDEX     = 'fibofilters_sources';
	const FILTERS_INDEX_VALUELIST   = 'fibofilters_valuelist';
	const FILTERS_INDEX_DOCLIST     = 'fibofilters_doclist';
	const FILTERS_INDEX_DESCRIPTORS = 'fibofilters_descriptors';

	/** @var Cache */
	private $cache;

	/** @var \FiboFilters\Util\DbUtil */
	private $db_util;

	/** @var Logger */
	protected $logger;

	/** @var Multilingual */
	protected $multilingual;

	/** @var WPDB */
	private $wpdb;

	/** @var BuildInfo */
	private $build_info;

	public function __construct( Cache $cache, DbUtil $db_util, Logger $logger, Multilingual $multilingual, WPDB $wpdb, BuildInfo $build_info ) {
		$this->db_util      = $db_util;
		$this->cache        = $cache;
		$this->logger       = $logger;
		$this->multilingual = $multilingual;
		$this->wpdb         = $wpdb;
		$this->build_info   = $build_info;
	}

	/**
	 * Add tables names to the $wpdb object
	 */
	public function register_tables(): void {
		global $wpdb;

		$wpdb->fibofilters_sources = $wpdb->prefix . self::FILTERS_SOURCES_INDEX;
		$wpdb->tables[]            = self::FILTERS_SOURCES_INDEX;

		$wpdb->fibofilters_valuelist = $wpdb->prefix . self::FILTERS_INDEX_VALUELIST;
		$wpdb->tables[]              = self::FILTERS_INDEX_VALUELIST;

		$wpdb->fibofilters_doclist = $wpdb->prefix . self::FILTERS_INDEX_DOCLIST;
		$wpdb->tables[]            = self::FILTERS_INDEX_DOCLIST;

		$wpdb->fibofilters_descriptors = $wpdb->prefix . self::FILTERS_INDEX_DESCRIPTORS;
		$wpdb->tables[]                = self::FILTERS_INDEX_DESCRIPTORS;
	}

	/**
	 * Create database structure from the scratch
	 *
	 * @param string $index_role Index role.
	 * @param array  $languages List of languages.
	 */
	public function create( string $index_role, array $languages = [] ): void {
		if ( empty( $languages ) ) {
			$languages = $this->multilingual->is_multilingual() ? $this->multilingual->get_languages() : [ '' ];
		}

		try {
			// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
			$this->wpdb->query( $this->sources_table_struct( $index_role ) );
			foreach ( $languages as $language ) {
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$this->wpdb->query( $this->doclist_table_struct( $index_role, $language ) );
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$this->wpdb->query( $this->valuelist_table_struct( $index_role, $language ) );
				// phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
				$this->wpdb->query( $this->descriptors_table_struct( $index_role, $language ) );
			}
		} catch ( WPDBException $e ) {
			$this->logger->handle_throwable_error( $e, '[Filters index] ' );
		}

		// Create indexes.
		$table_sources = $this->db_util->get_table_name( 'filters_sources', $index_role );
		// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$this->wpdb->query( "CREATE INDEX sources_index ON $table_sources(name,lang)" );

		foreach ( $languages as $language ) {
			$table_valuelist = $this->db_util->get_table_name( 'filters_valuelist', $index_role, $language );
			$table_doclist   = $this->db_util->get_table_name( 'filters_doclist', $index_role, $language );
			try {
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$this->wpdb->query( "CREATE INDEX valuelist_index ON $table_valuelist(value,parent_value,source_id)" );
				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$this->wpdb->query( "CREATE INDEX valuelist_index_source_id ON $table_valuelist(source_id)" );

				// phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
				$this->wpdb->query( "CREATE INDEX idx_postid_parent ON $table_doclist(post_id, post_or_parent_id)" );
			} catch ( WPDBException $e ) {
				$this->logger->handle_throwable_error( $e, '[Filters index] ' );
			}
		}
	}

	/**
	 * Get real tables belong to the filters index
	 *
	 * @param string $index_role Index role.
	 *
	 * @return string[]
	 */
	public function get_tables( string $index_role = 'main' ): array {
		$filters_tables = [];

		$tables = $this->db_util->get_all_plugin_tables();

		if ( ! empty( $tables ) ) {
			foreach ( $tables as $table ) {
				if (
					strpos( $table, self::FILTERS_SOURCES_INDEX ) !== false ||
					strpos( $table, self::FILTERS_INDEX_DOCLIST ) !== false ||
					strpos( $table, self::FILTERS_INDEX_VALUELIST ) !== false ||
					strpos( $table, self::FILTERS_INDEX_DESCRIPTORS ) !== false
				) {
					if (
						empty( $index_role ) ||
						strpos( $table, '_' . $index_role ) !== false
					) {
						$filters_tables[] = $table;
					}
				}
			}
		}

		return $filters_tables;
	}

	/**
	 *  DB structure for filters valuelist table
	 *
	 * @param string $index_role Index role.
	 * @param string $language Language.
	 *
	 * @return string
	 */
	public function valuelist_table_struct( string $index_role, string $language = '' ): string {
		$table_valuelist = $this->db_util->get_table_name( 'filters_valuelist', $index_role, $language );
		$collate         = $this->db_util->get_collate( 'filters/valuelist' );

		return "CREATE TABLE $table_valuelist (
				value_id       INT UNSIGNED NOT NULL AUTO_INCREMENT,
				value          VARCHAR(200) NOT NULL,
				max_value      VARCHAR(200) NOT NULL,
				label          VARCHAR(200) NOT NULL,
				label_url      VARCHAR(255) NOT NULL,
				parent_value   VARCHAR(255) NOT NULL,
				source_id      BIGINT UNSIGNED NOT NULL,
				`order`        BIGINT UNSIGNED NOT NULL,
				PRIMARY KEY    (value_id)
			    ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC $collate;";
	}

	/**
	 * DB structure for filters doclist table
	 *
	 * @param string $index_role Index role.
	 * @param string $language Language.
	 *
	 * @return string
	 */
	public function doclist_table_struct( string $index_role, string $language = '' ): string {
		$table_doclist = $this->db_util->get_table_name( 'filters_doclist', $index_role, $language );

		return "CREATE TABLE $table_doclist (
				id                INT UNSIGNED NOT NULL AUTO_INCREMENT,
                value_id          BIGINT UNSIGNED NOT NULL,
				post_id           BIGINT NOT NULL,
				post_or_parent_id BIGINT NOT NULL,
				PRIMARY KEY  (id),
                UNIQUE KEY unique_doclist_values (value_id, post_id, post_or_parent_id)
			    ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC COLLATE ascii_bin";
	}

	/**
	 *  DB structure for product descriptors table
	 *
	 * @param string $index_role Index role.
	 * @param string $language Language.
	 *
	 * @return string
	 */
	public function descriptors_table_struct( string $index_role, string $language = '' ): string {
		$table_descriptors = $this->db_util->get_table_name( 'filters_descriptors', $index_role, $language );

		return "CREATE TABLE $table_descriptors (
				post_id           BIGINT UNSIGNED NOT NULL UNIQUE,
				post_or_parent_id BIGINT NOT NULL,
                descriptor        LONGTEXT NOT NULL,
                deleted           TINYINT(1) NULL DEFAULT 0,
                updated_date      DATETIME NOT NULL DEFAULT '1970-01-01 00:00:01',
				PRIMARY KEY  (post_id)
			    ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC COLLATE ascii_bin";
	}

	/**
	 * DB structure for sources table
	 *
	 * @param string $index_role Index role.
	 *
	 * @return string
	 */
	public function sources_table_struct( string $index_role ): string {
		$table_sources = $this->db_util->get_table_name( 'filters_sources', $index_role );
		$collate       = $this->db_util->get_collate( 'filters/sources' );

		return "CREATE TABLE $table_sources (
				source_id    MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
                name         VARCHAR(191) NOT NULL,
				lang         VARCHAR(7) NULL,
				PRIMARY KEY  (source_id)
			    ) ENGINE=InnoDB ROW_FORMAT=DYNAMIC $collate;";
	}

	/**
	 * Remove filter tables
	 *
	 * @param string $index_role Index role.
	 */
	public function remove( string $index_role = 'main' ): void {
		global $wpdb;

		$show_errors = $wpdb->show_errors;
		$wpdb->hide_errors();

		foreach ( $this->get_tables( $index_role ) as $table ) {
			$wpdb->query( "DROP TABLE IF EXISTS $table" ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		}

		$wpdb->show_errors( $show_errors );

		$this->cache->delete( 'table_exists', 'database' );
	}

	/**
	 * Check if filters index exist
	 *
	 * @param string $index_role Index role.
	 * @param bool   $all Whether all tables must exist.
	 * @param string $language Language.
	 *
	 * @return bool
	 */
	public function filters_index_exists( string $index_role, bool $all = true, string $language = '' ): bool {
		$is_indexing = $this->build_info->is_indexing();

		$table_sources     = $this->db_util->is_table_exists( $this->db_util->get_table_name( 'filters_sources', $index_role ), $is_indexing );
		$table_doclist     = $this->db_util->is_table_exists( $this->db_util->get_table_name( 'filters_doclist', $index_role, $language ), $is_indexing );
		$table_valuelist   = $this->db_util->is_table_exists( $this->db_util->get_table_name( 'filters_valuelist', $index_role, $language ), $is_indexing );
		$table_descriptors = $this->db_util->is_table_exists( $this->db_util->get_table_name( 'filters_descriptors', $index_role, $language ), $is_indexing );

		if ( $all ) {
			return $table_sources && $table_doclist && $table_valuelist && $table_descriptors;
		} else {
			return $table_sources || $table_doclist || $table_valuelist || $table_descriptors;
		}
	}
}
