<?php
/**
 * Shared SQL filter helpers for Insights aggregations.
 */
if ( ! defined( 'ABSPATH' ) ) {
	exit;
}

class LinkCentral_Insights_Query_Builder {

	/** @var array<int, array<int>> */
	private static $category_link_ids_cache = array();

	/**
	 * Clear per-request category cache (call at start of snapshot build).
	 */
	public static function reset_request_cache() {
		self::$category_link_ids_cache = array();

		if ( class_exists( 'LinkCentral_Insights_Drilldown' ) ) {
			LinkCentral_Insights_Drilldown::reset_request_cache();
		}
	}

	/**
	 * Resolve inclusive chart period as DateTime objects (start midnight, end exclusive tomorrow).
	 *
	 * @param LinkCentral_Insights_Filters $filters Filter context.
	 * @return array{0: DateTime, 1: DateTime}
	 */
	public static function resolve_date_range( LinkCentral_Insights_Filters $filters ) {
		global $wpdb;

		if ( $filters->since_start && function_exists( 'linkcentral_fs' ) && linkcentral_fs()->can_use_premium_code__premium_only() ) {
			$scope_id = $filters->link_id ? intval( $filters->link_id ) : null;

			if ( $scope_id ) {
				$first_click = $wpdb->get_var(
					$wpdb->prepare(
						"SELECT MIN(click_date) FROM {$wpdb->prefix}linkcentral_stats WHERE link_id = %d",
						$scope_id
					)
				);
			} elseif ( $filters->category_id ) {
				$link_ids = self::get_category_link_ids( $filters->category_id );
				if ( empty( $link_ids ) ) {
					$first_click = null;
				} else {
					$first_click = $wpdb->get_var(
						$wpdb->prepare(
							"SELECT MIN(click_date) FROM {$wpdb->prefix}linkcentral_stats WHERE link_id IN (" . implode( ',', array_fill( 0, count( $link_ids ), '%d' ) ) . ')',
							$link_ids
						)
					);
				}
			} else {
				$first_click = $wpdb->get_var( "SELECT MIN(click_date) FROM {$wpdb->prefix}linkcentral_stats" );
			}

			if ( $first_click ) {
				$start = new DateTime( $first_click );
				$start->setTime( 0, 0, 0 );
				$end   = new DateTime( 'tomorrow' );
				return array( $start, $end );
			}
		}

		if ( $filters->start_date && $filters->end_date ) {
			$start = new DateTime( $filters->start_date );
			$end   = new DateTime( $filters->end_date );
			$end->modify( '+1 day' );
			return array( $start, $end );
		}

		$days = intval( $filters->preset ?: 7 );
		if ( $days <= 0 ) {
			$days = 7;
		}

		$end   = new DateTime( 'tomorrow' );
		$start = clone $end;
		$start->modify( sprintf( '-%d days', $days ) );

		return array( $start, $end );
	}

	/**
	 * Resolved period for API context (ISO date strings).
	 *
	 * @param LinkCentral_Insights_Filters $filters Filter context.
	 * @return array{start: string, end: string}
	 */
	public static function resolved_period_for_response( LinkCentral_Insights_Filters $filters ) {
		list( $start, $end ) = self::resolve_date_range( $filters );
		$end_display         = clone $end;
		$end_display->modify( '-1 day' );

		return array(
			'start' => $start->format( 'Y-m-d' ),
			'end'   => $end_display->format( 'Y-m-d' ),
		);
	}

	/**
	 * Cached link IDs for a category within the current request.
	 *
	 * @param int $category_id Category term ID.
	 * @return array<int>
	 */
	public static function get_category_link_ids( $category_id ) {
		$category_id = intval( $category_id );
		if ( ! isset( self::$category_link_ids_cache[ $category_id ] ) ) {
			self::$category_link_ids_cache[ $category_id ] = LinkCentral_Insights_Helpers::get_link_ids_by_category( $category_id );
		}
		return self::$category_link_ids_cache[ $category_id ];
	}

	/**
	 * Whether category filter yields no links (caller should return empty widget data).
	 *
	 * @param LinkCentral_Insights_Filters $filters Filter context.
	 * @return bool
	 */
	public static function category_filter_is_empty( LinkCentral_Insights_Filters $filters ) {
		if ( ! $filters->category_id ) {
			return false;
		}
		return empty( self::get_category_link_ids( $filters->category_id ) );
	}

	/**
	 * Append standard stats-table WHERE fragments for link, category, and date.
	 *
	 * @param LinkCentral_Insights_Filters $filters   Filter context.
	 * @param array                        $clauses   WHERE clause fragments (by reference).
	 * @param array                        $params    Prepared statement values (by reference).
	 * @param string                       $alias     Table alias for linkcentral_stats.
	 * @param bool                         $use_resolved_range When true, always use resolve_date_range bounds.
	 * @return bool False if category has no links and query should be skipped.
	 */
	public static function append_stats_filters(
		LinkCentral_Insights_Filters $filters,
		array &$clauses,
		array &$params,
		$alias = 's',
		$use_resolved_range = true
	) {
		if ( class_exists( 'LinkCentral_Insights_Link_Scope' ) ) {
			if ( ! LinkCentral_Insights_Link_Scope::append_stats_filters( $filters, $clauses, $params, $alias ) ) {
				return false;
			}
		}

		if ( $use_resolved_range ) {
			list( $start, $end ) = self::resolve_date_range( $filters );
			$clauses[]           = "{$alias}.click_date >= %s";
			$params[]            = $start->format( 'Y-m-d' );
			$clauses[]           = "{$alias}.click_date < %s";
			$params[]            = $end->format( 'Y-m-d' );
		} else {
			self::append_legacy_date_filter( $filters, $clauses, $params, $alias );
		}

		if ( class_exists( 'LinkCentral_Insights_Drilldown' ) ) {
			if ( ! LinkCentral_Insights_Drilldown::append_stats_filters( $filters, $clauses, $params, $alias ) ) {
				return false;
			}
		}

		return true;
	}

	/**
	 * Legacy date filter matching preset/custom/since_start without resolved MIN for since_start.
	 *
	 * @param LinkCentral_Insights_Filters $filters Filter context.
	 * @param array                        $clauses WHERE fragments.
	 * @param array                        $params  Bind values.
	 * @param string                       $alias   Table alias.
	 */
	private static function append_legacy_date_filter(
		LinkCentral_Insights_Filters $filters,
		array &$clauses,
		array &$params,
		$alias = 's'
	) {
		if ( $filters->since_start && function_exists( 'linkcentral_fs' ) && linkcentral_fs()->can_use_premium_code__premium_only() ) {
			return;
		}

		if ( $filters->start_date && $filters->end_date ) {
			$clauses[] = "{$alias}.click_date >= %s";
			$params[]  = $filters->start_date;
			$clauses[] = "{$alias}.click_date < DATE_ADD(%s, INTERVAL 1 DAY)";
			$params[]  = $filters->end_date;
			return;
		}

		if ( $filters->preset === '1' ) {
			$clauses[] = "{$alias}.click_date >= DATE_SUB(NOW(), INTERVAL 24 HOUR)";
			return;
		}

		$days      = intval( $filters->preset ?: 7 );
		$clauses[] = "{$alias}.click_date >= DATE_SUB(CURDATE(), INTERVAL %d DAY)";
		$params[]  = $days;
	}

	/**
	 * Run a prepared SELECT and return rows.
	 *
	 * @param string $query  SQL with optional placeholders.
	 * @param array  $params Bind values.
	 * @param string $output Optional $wpdb->get_results() output type (default ARRAY_A).
	 * @return array
	 */
	public static function query( $query, array $params = array(), $output = ARRAY_A ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Centralized helper; $query uses placeholders and $params are bound via prepare().
		if ( ! empty( $params ) ) {
			return $wpdb->get_results( $wpdb->prepare( $query, $params ), $output );
		}

		return $wpdb->get_results( $query, $output );
		// phpcs:enable
	}

	/**
	 * Run a prepared SELECT and return a single scalar value.
	 *
	 * @param string $query  SQL with optional placeholders.
	 * @param array  $params Bind values.
	 * @return string|null
	 */
	public static function query_var( $query, array $params = array() ) {
		global $wpdb;

		// phpcs:disable WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- Centralized helper; $query uses placeholders and $params are bound via prepare().
		if ( ! empty( $params ) ) {
			return $wpdb->get_var( $wpdb->prepare( $query, $params ) );
		}

		return $wpdb->get_var( $query );
		// phpcs:enable
	}
}
