<?php
/**
 * Descriptors index class.
 *
 * Performs CRUD‑level queries on the 'fibofilters_descriptors' table and provides
 * helper methods for parsing and caching descriptor JSON used by the indexer.
 *
 * @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 Descriptors
 *
 * @since 1.10.0
 */
class Descriptors extends AbstractIndex {
	/**
	 * {@inheritdoc}
	 */
	public function get_table_type(): string {
		return 'filters_descriptors';
	}

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

	/**
	 * Retrieves descriptors according to provided arguments.
	 *
	 * @param array<string,mixed> $args {
	 *     Arguments controlling query behavior.
	 *
	 * @type string               $cache_key Base cache key.
	 * @type bool                 $cache Whether to use object cache.
	 * @type bool                 $ids_only Return only descriptor IDs.
	 * @type bool                 $parsed Parse JSON descriptors into arrays.
	 * }
	 *
	 * @param QueryContext|null   $ctx Query context.
	 *
	 * @return array<int,array>|array<int,string>|array<int,int>
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_descriptors( array $args, ?QueryContext $ctx = null ): array {
		$ctx ??= new QueryContext();
		$wpdb = $this->get_wpdb( $ctx );

		$defaults = [
			'cache'         => true,
			'cache_key'     => 'descriptors_' . Helpers::generate_cache_hash( $args ),
			'deleted'       => null,
			'updated_start' => '',
			'updated_end'   => '',
			'parsed'        => true,
			'select'        => 'post_id, post_or_parent_id, descriptor',
			'output'        => 'get_results',
		];

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

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

		$where = '';

		// Support deleted.
		if ( ! is_null( $args['deleted'] ) ) {
			$deleted = absint( $args['deleted'] );
			$where  .= $wpdb->prepare( ' AND deleted = %d', $deleted );
		}

		// Updated date from.
		if ( ! empty( $args['updated_start'] ) ) {
			$where .= $wpdb->prepare( ' AND updated_date >= %s  ', $args['updated_start'] );
		}

		// Updated date to.
		if ( ! empty( $args['updated_end'] ) ) {
			$where .= $wpdb->prepare( ' AND updated_date <= %s ', $args['updated_end'] );
		}

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

		$select = $args['select'];

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

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

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

		$descriptors = $args['parsed'] ? $this->parse_descriptors( $descriptors ) : $descriptors;

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

		return $descriptors;
	}

	/**
	 * Get all product descriptors.
	 *
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return array<int,array>|array<int,string>
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_all_descriptors( ?QueryContext $ctx = null ): array {
		$ctx = $ctx ?? new QueryContext();

		$args = [
			'cache_key' => 'all_descriptors',
			'deleted'   => false,
		];

		return $this->get_descriptors( $args, $ctx );
	}

	/**
	 * Wrapper returning descriptors changed within the date range.
	 *
	 * @param string            $date_end Date upper‑bound ( Y-m-d H:i:s ).
	 * @param string            $date_start Optional lower‑bound date.
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return array{descriptors: array<int,array>, deleted: array<int>} Changed descriptors and deleted IDs.
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_changed_descriptors( string $date_end, string $date_start = '', ?QueryContext $ctx = null ): array {
		$ctx = $ctx ?? new QueryContext();

		return [
			'descriptors' => $this->get_updated_descriptors( $date_end, $date_start, $ctx ),
			'deleted'     => $this->get_deleted_descriptor_ids( $date_end, $date_start, $ctx ),
		];
	}

	/**
	 * Returns only updated descriptors (added or modified, not deleted).
	 *
	 * @param string            $date_end Date upper‑bound ( Y-m-d H:i:s ).
	 * @param string            $date_start Optional lower‑bound date.
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return array<int,array>|array<int,string>
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_updated_descriptors( string $date_end, string $date_start = '', ?QueryContext $ctx = null ): array {

		// Break early.
		if ( ! $this->is_valid_mysql_datetime( $date_end )
			|| ( ! empty( $date_start ) && ! $this->is_valid_mysql_datetime( $date_start ) )
		) {
			return [];
		}

		$ctx = $ctx ?? new QueryContext();

		$cache_hash = md5( $date_start . $date_end );

		$args = [
			'cache_key'     => 'descriptors_updated_' . $cache_hash,
			'deleted'       => false,
			'updated_start' => $date_start,
			'updated_end'   => $date_end,
		];

		return $this->get_descriptors( $args, $ctx );
	}

	/**
	 * Returns IDs of descriptors marked as deleted.
	 *
	 * @param string            $date_end Date upper‑bound ( Y-m-d H:i:s ).
	 * @param string            $date_start Optional lower‑bound date.
	 * @param QueryContext|null $ctx Query context.
	 *
	 * @return int[] Array of deleted descriptor IDs.
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function get_deleted_descriptor_ids( string $date_end, string $date_start = '', ?QueryContext $ctx = null ): array {

		// Break early.
		if ( ! $this->is_valid_mysql_datetime( $date_end )
			|| ( ! empty( $date_start ) && ! $this->is_valid_mysql_datetime( $date_start ) )
		) {
			return [];
		}

		$ctx = $ctx ?? new QueryContext();

		$cache_hash = md5( $date_start . $date_end );

		$args = [
			'cache_key'     => 'descriptor_ids_deleted_' . $cache_hash,
			'parsed'        => false,
			'deleted'       => true,
			'updated_start' => $date_start,
			'updated_end'   => $date_end,
			'select'        => 'DISTINCT post_or_parent_id',
			'output'        => 'get_col',
		];

		$results = $this->get_descriptors( $args, $ctx );

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

		return $results;
	}

	/**
	 * Saves single descriptor row.
	 *
	 * @param array<string,mixed> $args Descriptor data ( post_id, descriptor, deleted, etc. ).
	 * @param QueryContext|null   $ctx Query context.
	 *
	 * @return bool Returns true if saved successfully, false otherwise.
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function save_descriptor( array $args, ?QueryContext $ctx = null ): bool {
		$ctx  = $ctx ?? new QueryContext();
		$wpdb = $this->get_wpdb( $ctx );

		$defaults = [
			'post_id'           => 0,
			'post_or_parent_id' => 0,
			'descriptor'        => '',
			'deleted'           => 0,
			'updated_date'      => '',
		];

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

		$data = [
			'post_id'           => is_numeric( $args['post_id'] ) && $args['post_id'] > 0 ? (int) $args['post_id'] : 0,
			'post_or_parent_id' => ! empty( $args['post_or_parent_id'] ) ? (int) $args['post_or_parent_id'] : (int) $args['post_id'],
			'descriptor'        => ! empty( $args['descriptor'] ) ? $this->maybe_encode_json( $args['descriptor'] ) : $args['descriptor'],
			'deleted'           => absint( $args['deleted'] ),
		];

		// Break early.
		if ( empty( $data['post_id'] ) || empty( $data['descriptor'] ) ) {
			return false;
		}

		$format = [
			'%d',
			'%d',
			'%s',
			'%d',
		];

		/**
		 * When we have an update date, we still insert the descriptor into DB because it was previously removed.
		 * However, we add the date of its modification.
		 */
		if ( ! empty( $args['updated_date'] ) ) {

			if ( ! $this->is_valid_mysql_datetime( $args['updated_date'] ) ) {
				return false;
			}

			$data = array_merge(
				$data,
				[
					'updated_date' => $args['updated_date'],
				]
			);

			$format = array_merge(
				$format,
				[
					'%s',
				]
			);
		}

		$total = $wpdb->insert( $this->get_table_name( $ctx ), $data, $format );

		return is_numeric( $total ) && $total > 0;
	}

	/**
	 * Deletes a descriptor row—soft-delete when an `updated_date`
	 * is supplied, hard-delete otherwise.
	 *
	 * Soft-delete: sets `deleted` = 1 and updates `updated_date`.
	 * Hard-delete: physically removes the row.
	 *
	 * @param array             $args {
	 *     Arguments controlling the delete operation.
	 *
	 * @type int                $post_id Required. ID of the post/descriptor.
	 * @type string             $updated_date Optional. MySQL DATETIME; when present and
	 *                                valid, triggers a soft-delete.
	 * }
	 *
	 * @param QueryContext|null $ctx Optional context (site/network). Default null.
	 *
	 * @return bool                    True on success, false on failure.
	 * @throws WPDBException When a database error occurred (only for WPDBSecond).
	 */
	public function delete_descriptor( array $args, ?QueryContext $ctx = null ): bool {
		$ctx        = $ctx ?? new QueryContext();
		$wpdb       = $this->get_wpdb( $ctx );
		$table_name = $this->get_table_name( $ctx );

		$args = wp_parse_args(
			$args,
			[
				'post_id'      => 0,
				'updated_date' => '',
			]
		);

		$post_id = is_numeric( $args['post_id'] ) ? (int) $args['post_id'] : 0;
		if ( $post_id <= 0 ) {
			return false;
		}

		// Soft-delete.
		if ( $args['updated_date'] !== '' ) {
			if ( ! $this->is_valid_mysql_datetime( $args['updated_date'] ) ) {
				return false;
			}

			$result = $wpdb->update(
				$table_name,
				[
					'deleted'      => 1,
					'updated_date' => $args['updated_date'],
				],
				[ 'post_id' => $post_id ],
				[ '%d', '%s' ],
				[ '%d' ]
			);
		} else {
			// Hard-delete.
			$result = $wpdb->delete(
				$table_name,
				[ 'post_id' => $post_id ],
				[ '%d' ]
			);
		}

		return $result !== false;
	}

	/**
	 * Parses a list of descriptor records into a nested array structure.
	 *
	 * The expected input is a list of arrays in the form:
	 * [post_id, post_or_parent_id, descriptor_json].
	 *
	 * Output format:
	 * [
	 *     post_or_parent_id => [
	 *         post_id => descriptor array,
	 *         post_id => descriptor array,
	 *     ],
	 *     ...
	 * ]
	 *
	 * @param array<int, array{0: int|string, 1: int|string, 2: string}> $descriptors List of descriptors.
	 *     Array of descriptor rows from the database.
	 *
	 * @return array<int, array<int, array>> Parsed descriptors grouped by parent ID.
	 */
	private function parse_descriptors( array $descriptors ): array {
		$parsed = [];

		foreach ( $descriptors as [$post_id, $post_or_parent_id, $descriptor_json] ) {
			$decoded = $this->parse_descriptor( $descriptor_json );

			if ( empty( $decoded ) ) {
				// Allow empty descriptor. This is needed to display variations with out-of-stock status.
				$decoded = [];
			}

			$parsed[ (int) $post_or_parent_id ][ (int) $post_id ] = $decoded;
		}

		return $parsed;
	}

	/**
	 * Decodes a JSON descriptor string into an associative array.
	 *
	 * If decoding fails or the result is not an array, an empty array is returned.
	 *
	 * @param string $descriptor JSON-encoded descriptor string.
	 *
	 * @return array<string, array>|array{} Decoded descriptor array, or empty if invalid.
	 */
	public function parse_descriptor( string $descriptor ): array {
		try {
			$data = json_decode( $descriptor, true, 512, JSON_THROW_ON_ERROR );

			return is_array( $data ) ? $data : [];
		} catch ( \JsonException $e ) {
			return [];
		}
	}

	/**
	 * Checks if the given string is a valid MySQL DATETIME format (YYYY-MM-DD HH:MM:SS).
	 *
	 * @param string $datetime The datetime string to validate.
	 *
	 * @return bool Returns true if the format is valid, false otherwise.
	 */
	private function is_valid_mysql_datetime( string $datetime ): bool {
		$d = \DateTime::createFromFormat( 'Y-m-d H:i:s', $datetime );

		return $d && $d->format( 'Y-m-d H:i:s' ) === $datetime;
	}

	/**
	 * Ensures the input is returned as a JSON string.
	 *
	 * - Non-string values (arrays, objects, ints, bools, etc.) are always
	 *   encoded with `wp_json_encode()`.
	 * - String values are first checked with `json_decode()`:
	 *     • if the string is already valid JSON, it is returned unchanged;
	 *     • otherwise the string itself is JSON-encoded.
	 *
	 * @param mixed $value Value that may need JSON encoding.
	 *
	 * @return string JSON-encoded representation.
	 */
	private function maybe_encode_json( $value ): string {
		if ( ! is_string( $value ) ) {
			$encoded = wp_json_encode( $value );

			if ( $encoded === false ) {
				return '';
			}

			return $encoded;
		}

		json_decode( $value );

		if ( json_last_error() === JSON_ERROR_NONE ) {
			return $value;
		}

		$encoded = wp_json_encode( $value );

		return (string) $encoded;
	}
}
