<?php
/**
 * Sources index class.
 *
 * Handles CRUD operations for the `fibofilters_sources` table and provides helpers
 * for mapping between source IDs and names.
 *
 * @package FiboFilters\Index
 * @since   1.10.0
 */

declare( strict_types=1 );

namespace FiboFilters\Index;

use FiboFilters\Struct\QueryContext;
use FiboFilters\Util\Helpers;
use FiboFilters\WPDBException;

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

/**
 * Class Sources
 *
 * @since 1.10.0
 */
class Sources extends AbstractIndex {
	/**
	 * {@inheritdoc}
	 */
	public function get_table_type(): string {
		return 'filters_sources';
	}

	/**
	 * {@inheritdoc}
	 */
	public function get_cache_group(): string {
		return 'sources_query';
	}

	/**
	 * Builds a full table name based on the index role, without including the language, which is stored as a column in the table.
	 *
	 * @param QueryContext $ctx Query context.
	 *
	 * @return string          Fully‑qualified table name.
	 */
	public function get_table_name( QueryContext $ctx ): string {
		return $this->db_util->get_table_name(
			$this->get_table_type(),
			$ctx->get_index_role()
		);
	}

	/**
	 * Get source ID or create it if it does not exist (cached).
	 *
	 * @param string            $source_name Source name.
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return int Source ID.
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_source_id_or_create( string $source_name, ?QueryContext $ctx = null ): int {
		$ctx = $ctx ?? new QueryContext();

		$cache_key = 'saved_sources_' . md5( $source_name );

		$cached = $this->get_cached_items( $cache_key, $ctx );
		if ( ! empty( $cached ) ) {
			return $cached;
		}

		$sources   = $this->get_all_sources( $ctx );
		$source_id = $sources[ $source_name ] ?? 0;

		if ( empty( $source_id ) ) {
			// Insert.
			$source_id = $this->save_source( $source_name, $ctx );
			// Refresh cache after inserting.
			$this->cache->delete( 'all_sources' . $this->create_suffix_cache_key( $ctx ), $this->get_cache_group() );
		}

		$source_id = empty( $source_id ) ? 0 : $source_id;

		$this->set_cached_items( $cache_key, $source_id, $ctx );

		return $source_id;
	}

	/**
	 * Return all sources as `name=>id` map.
	 *
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return array<string,int> Map of source name to ID.
	 *
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_all_sources( ?QueryContext $ctx = null ): array {
		return $this->get_sources( [ 'cache_key' => 'all_sources' ], $ctx );
	}

	/**
	 * Generic sources getter.
	 *
	 * @param array<string,mixed> $args {
	 *     Query arguments.
	 *
	 * @type bool                 $cache Whether to use object cache. Default false.
	 * @type string               $cache_key Cache key. Default 'sources'.
	 * @type string               $select Select clause. Default 'source_id, name'.
	 * @type bool                 $format Format to name=>id map. Default true.
	 * }
	 *
	 * @param QueryContext|null   $ctx Query context.
	 *
	 * @return array<int|mixed,mixed> Raw results or formatted map.
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_sources( array $args, ?QueryContext $ctx = null ): array {
		$ctx ??= new QueryContext();
		$wpdb = $this->get_wpdb( $ctx );

		$defaults = [
			'cache'     => true,
			'cache_key' => 'sources_' . Helpers::generate_cache_hash( $args ),
			'select'    => 'source_id, name',
			'format'    => true,
		];

		$args = wp_parse_args( $args, $defaults );

		$cached = $this->get_cached_items( $args['cache_key'], $ctx );
		if ( $args['cache'] && is_array( $cached ) ) {
			return $cached;
		}

		$table_sources = $this->get_table_name( $ctx );

		$select = $args['select'];

		$where = '';

		// Language.
		if ( ! empty( $ctx->get_lang() ) ) {
			$where .= $wpdb->prepare( ' AND lang = %s', $ctx->get_lang() );
		}

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = "SELECT $select
				FROM $table_sources
                WHERE 1 = 1
                $where
		";
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		$sources = $wpdb->get_results( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared

		if ( ! is_array( $sources ) ) {
			return [];
		}

		if ( $args['format'] ) {
			$sources = $this->format_sources( $sources );
		}

		if ( $args['cache'] ) {
			$this->set_cached_items( $args['cache_key'], $sources, $ctx );
		}

		return $sources;
	}

	/**
	 * Return source ID by name using bulk‑safe cache.
	 *
	 * @param string            $source_name Source name.
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return int Source ID or 0.
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_source_id_by_name( string $source_name, ?QueryContext $ctx = null ): int {
		$sources = $this->get_all_sources( $ctx );

		return $sources[ $source_name ] ?? 0;
	}

	/**
	 * Return source name by ID using bulk‑safe cache.
	 *
	 * @param int               $source_id Source ID.
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return string Source name or empty string.
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_source_name_by_id( int $source_id, ?QueryContext $ctx = null ): string {
		$sources = $this->get_all_sources( $ctx );

		$result = array_search( $source_id, $sources, true );

		return $result === false ? '' : (string) $result;
	}


	/**
	 * Retrieves a single source field (e.g., ID or name) based on its identifier.
	 *
	 * Supports lookup by source name or ID and allows returning a single column or multiple values.
	 *
	 * @param array<string,mixed> $args {
	 *     Arguments to control the query.
	 *
	 * @type string               $value Source name or ID value to search.
	 * @type string               $get Field to return: 'source_id' or 'name'. Default 'source_id'.
	 * @type string               $by Field to match against: 'name' or 'source_id'. Default 'name'.
	 * @type bool                 $cache Whether to use the object cache. Default false.
	 * @type string               $cache_key Cache key. Default 'get_source'.
	 * @type string               $output Whether to return scalar or array: 'get_var' or 'get_col'. Default 'get_var'.
	 * }
	 *
	 * @param QueryContext|null   $ctx Query context.
	 *
	 * @return string|int|array Scalar value or array depending on `$output`.
	 *
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_source( array $args, ?QueryContext $ctx = null ) {
		$ctx ??= new QueryContext();
		$wpdb = $this->get_wpdb( $ctx );

		$defaults = [
			'value'     => '',
			'get'       => 'source_id',
			'by'        => 'name',
			'cache'     => true,
			'cache_key' => 'source_' . Helpers::generate_cache_hash( $args ),
			'output'    => 'get_var',
		];

		$args = wp_parse_args( $args, $defaults );

		$cache_key = $args['cache_key'] . '_' . Helpers::generate_cache_hash( $args['value'] ) . '_by_' . $args['by'];
		$cached    = $this->get_cached_items( $cache_key, $ctx );
		if ( $args['cache'] && $cached !== false ) {
			return $cached;
		}

		$select = '*';
		if ( in_array( $args['get'], [ 'source_id', 'name' ], true ) ) {
			$select = $args['get'];
		}

		$where = '';
		if ( $args['by'] === 'source_id' ) {
			$where .= $wpdb->prepare( ' AND source_id = %d', absint( $args['value'] ) );
		}
		if ( $args['by'] === 'name' ) {
			$where .= $wpdb->prepare( ' AND name = %s', $args['value'] );
		}

		// Language.
		if ( ! empty( $ctx->get_lang() ) ) {
			$where .= $wpdb->prepare( ' AND lang = %s', $ctx->get_lang() );
		}

		$table_sources = $this->get_table_name( $ctx );

		// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared
		$sql = "SELECT $select
              FROM $table_sources
              WHERE 1 = 1
              $where";
		// phpcs:enable WordPress.DB.PreparedSQL.InterpolatedNotPrepared

		if ( $args['output'] === 'get_col' ) {
			$source = $wpdb->get_col( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		} else {
			$source = $wpdb->get_var( $sql ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared
		}

		if ( $args['get'] === 'source_id' ) {
			$source = absint( $source );
		}

		if ( $args['cache'] ) {
			$this->set_cached_items( $cache_key, $source, $ctx );
		}

		return $source;
	}

	/**
	 * Insert a new source row.
	 *
	 * @param string            $source_name Source (filter) name.
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return int Insert‑ID of the new source.
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function save_source( string $source_name, ?QueryContext $ctx = null ): int {
		$ctx ??= new QueryContext();
		$wpdb = $this->get_wpdb( $ctx );

		$table_sources = $this->get_table_name( $ctx );

		$data = [
			'name' => $source_name,
			'lang' => $ctx->get_lang(),
		];

		$format = [
			'%s', // name.
			'%s', // lang.
		];

		$wpdb->insert( $table_sources, $data, $format );

		return $wpdb->db->insert_id;
	}

	/**
	 * Converts raw source rows into a [name => source_id] map.
	 *
	 * @param array<int|object|array> $sources Raw result set (objects or arrays).
	 *
	 * @return array<string,int> Map of source name to ID.
	 */
	public function format_sources( array $sources ): array {

		$sources = wp_list_pluck( $sources, 'source_id', 'name' );

		if ( ! empty( $sources ) ) {
			$sources = array_map( 'intval', $sources );
		}

		return $sources;
	}
}
