<?php

namespace SerpIQ\Content;

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

/**
 * Question Queries: backend model (Milestone 1).
 *
 * Uses:
 * - serpiq_queries (GSC data per query+page+date)
 * - WordPress posts (title/URL/post_id when resolvable)
 *
 * Responsibility:
 * - Aggregate query-page metrics for a period
 * - Filter queries that match configured "question terms"
 * - Classify difficulty by position (Difficult/Medium/Easy)
 * - Map difficulty to an Opportunity Score (20/60/90)
 *
 * This class does NOT expose REST routes directly.
 * Controllers can call ::compute() to power APIs and UI.
 */
final class Question_Queries {

	private static ?Question_Queries $instance = null;

	public static function instance(): Question_Queries {
		if ( null === self::$instance ) {
			self::$instance = new self();
		}
		return self::$instance;
	}

	private function __construct() {}

	/**
	 * Compute question queries for the given period.
	 *
	 * @param string $end_date    End date (Y-m-d). If empty, defaults to yesterday (UTC).
	 * @param int    $period_days Number of days in current period (28 or 90).
	 * @param array  $args        Optional filters:
	 *                            - terms (string[]|null) Custom question terms; if null, use defaults.
	 *                            - search (string)       Free-text filter on query.
	 *                            - score_min (int)
	 *                            - score_max (int)
	 * @return array{overview:array<string,mixed>,items:array<int,array<string,mixed>>}
	 */
	public function compute( string $end_date = '', int $period_days = 28, array $args = array() ): array {
		global $wpdb;

		if ( $period_days !== 90 ) {
			$period_days = 28;
		}

		if ( '' === $end_date ) {
			$end_date = gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		}

		$end_ts = strtotime( $end_date . ' 00:00:00 UTC' );
		if ( false === $end_ts ) {
			$end_ts   = strtotime( '-1 day' );
			$end_date = gmdate( 'Y-m-d', $end_ts );
		}

		$current_start_ts = $end_ts - ( $period_days - 1 ) * DAY_IN_SECONDS;
		$current_start    = gmdate( 'Y-m-d', $current_start_ts );

		// Terms: use provided ones or fallback to simple English defaults.
		$terms = array();
		if ( isset( $args['terms'] ) && is_array( $args['terms'] ) ) {
			$terms = $args['terms'];
		}
		$terms = $this->normalize_terms( $terms );
		if ( empty( $terms ) ) {
			$terms = $this->default_terms();
		}

		$search    = isset( $args['search'] ) ? (string) $args['search'] : '';
		$score_min = isset( $args['score_min'] ) ? (int) $args['score_min'] : 0;
		$score_max = isset( $args['score_max'] ) ? (int) $args['score_max'] : 100;

		$table_queries = $wpdb->prefix . 'serpiq_queries';

		$sql = "
			SELECT
				query,
				page_url,
				COALESCE(SUM(clicks),0) AS clicks,
				COALESCE(SUM(impressions),0) AS impressions,
				CASE WHEN COALESCE(SUM(impressions),0) > 0
					THEN (COALESCE(SUM(clicks),0) / COALESCE(SUM(impressions),0))
					ELSE 0 END AS ctr,
				CASE WHEN COALESCE(SUM(impressions),0) > 0
					THEN (COALESCE(SUM(position * impressions),0) / COALESCE(SUM(impressions),0))
					ELSE 0 END AS position
			FROM %i
			WHERE date BETWEEN %s AND %s
			GROUP BY query, page_url
		";

		$rows = $wpdb->get_results(
			$wpdb->prepare( $sql, array_merge( array( $table_queries ), array( $current_start, $end_date ) ) ),
			ARRAY_A
		);

		$items           = array();
		$total_clicks    = 0;
		$total_impr      = 0;
		$count_difficult = 0;
		$count_medium    = 0;
		$count_easy      = 0;

		$search_lc = strtolower( trim( $search ) );

		foreach ( (array) $rows as $row ) {
			$q = isset( $row['query'] ) ? (string) $row['query'] : '';
			$p = isset( $row['page_url'] ) ? (string) $row['page_url'] : '';

			if ( '' === $q ) {
				continue;
			}

			// Filter by search term if provided.
			if ( '' !== $search_lc && false === strpos( strtolower( $q ), $search_lc ) ) {
				continue;
			}

			// Filter only question-like queries.
			if ( ! $this->is_question_query( $q, $terms ) ) {
				continue;
			}

			$clicks      = (int) ( $row['clicks'] ?? 0 );
			$impressions = (int) ( $row['impressions'] ?? 0 );
			$ctr         = (float) ( $row['ctr'] ?? 0 );
			$position    = (float) ( $row['position'] ?? 0 );

			list( $difficulty, $score ) = $this->classify_difficulty_and_score( $position );

			if ( $score < $score_min || $score > $score_max ) {
				continue;
			}

			if ( 'difficult' === $difficulty ) {
				$count_difficult++;
			} elseif ( 'medium' === $difficulty ) {
				$count_medium++;
			} elseif ( 'easy' === $difficulty ) {
				$count_easy++;
			}

			$total_clicks += $clicks;
			$total_impr   += $impressions;

			$wp = $this->get_wp_data_for_url( $p );

			$items[] = array(
				'query'          => $q,
				'page_url'       => $p,
				'clicks'         => $clicks,
				'impressions'    => $impressions,
				'ctr'            => $ctr,
				'position'       => $position,
				'difficulty'     => $difficulty,
				'score'          => $score,
				'post_id'        => $wp['post_id'],
				'post_title'     => $wp['post_title'],
				'post_edit_link' => $wp['edit_link'],
			);
		}

		// Sort by score descending by default.
		usort(
			$items,
			static function ( $a, $b ) {
				$sa = (int) ( $a['score'] ?? 0 );
				$sb = (int) ( $b['score'] ?? 0 );
				if ( $sa === $sb ) {
					return strcasecmp( (string) $a['query'], (string) $b['query'] );
				}
				return $sb <=> $sa;
			}
		);

		$overview = array(
			'periodDays'    => $period_days,
			'currentRange'  => array(
				'start' => $current_start,
				'end'   => $end_date,
			),
			'totalQueries'  => count( $items ),
			'clicksInvolved'=> $total_clicks,
			'imprInvolved'  => $total_impr,
			'difficultCount'=> $count_difficult,
			'mediumCount'   => $count_medium,
			'easyCount'     => $count_easy,
			'terms'         => $terms,
			'scoreMin'      => $score_min,
			'scoreMax'      => $score_max,
		);

		return array(
			'overview' => $overview,
			'items'    => $items,
		);
	}

	/**
	 * Normalize a free-form list of terms (e.g. from settings).
	 *
	 * @param array<int,string> $terms
	 * @return array<int,string>
	 */
	private function normalize_terms( array $terms ): array {
		$out = array();
		foreach ( $terms as $term ) {
			$t = strtolower( trim( (string) $term ) );
			if ( '' === $t ) {
				continue;
			}
			$out[ $t ] = true;
		}
		return array_keys( $out );
	}

	/**
	 * Default English question terms.
	 *
	 * @return array<int,string>
	 */
	private function default_terms(): array {
		return array( 'how', 'what', 'where', 'when', 'why', 'who' );
	}

	/**
	 * Check whether a query matches configured question terms.
	 *
	 * @param string              $query
	 * @param array<int,string>   $terms
	 * @return bool
	 */
	private function is_question_query( string $query, array $terms ): bool {
		$q = strtolower( trim( $query ) );
		if ( '' === $q || empty( $terms ) ) {
			return false;
		}

		foreach ( $terms as $term ) {
			$t = strtolower( trim( $term ) );
			if ( '' === $t ) {
				continue;
			}
			// Starts with term (word boundary) or contains term anywhere.
			if ( 0 === strpos( $q, $t . ' ' ) || 0 === strpos( $q, $t ) || false !== strpos( $q, ' ' . $t . ' ' ) ) {
				return true;
			}
			if ( false !== strpos( $q, $t ) ) {
				return true;
			}
		}
		return false;
	}

	/**
	 * Classify difficulty and map to Opportunity Score.
	 *
	 * Difficulty buckets (by position):
	 * - Difficult: position 1–10  → score 20
	 * - Medium:    position 11–50 → score 60
	 * - Easy:      position 51+   → score 90
	 *
	 * @param float $position
	 * @return array{0:string,1:int} [difficulty,score]
	 */
	private function classify_difficulty_and_score( float $position ): array {
		if ( $position <= 0 ) {
			// Unknown position, treat as easiest to improve.
			return array( 'easy', 90 );
		}

		if ( $position >= 1 && $position <= 10 ) {
			return array( 'difficult', 20 );
		}

		if ( $position >= 11 && $position <= 50 ) {
			return array( 'medium', 60 );
		}

		return array( 'easy', 90 );
	}

	/**
	 * Resolve a page URL to a WordPress post (best-effort).
	 *
	 * @return array{post_id:int,post_title:string,edit_link:string}
	 */
	private function get_wp_data_for_url( string $url ): array {
		$url = trim( $url );
		if ( '' === $url || ! function_exists( 'url_to_postid' ) ) {
			return array(
				'post_id'    => 0,
				'post_title' => '',
				'edit_link'  => '',
			);
		}

		$post_id = url_to_postid( trailingslashit( $url ) );
		if ( $post_id <= 0 ) {
			return array(
				'post_id'    => 0,
				'post_title' => '',
				'edit_link'  => '',
			);
		}

		$post = get_post( $post_id );
		if ( ! $post || ! ( $post instanceof \WP_Post ) ) {
			return array(
				'post_id'    => 0,
				'post_title' => '',
				'edit_link'  => '',
			);
		}

		if ( 'publish' !== $post->post_status ) {
			return array(
				'post_id'    => (int) $post_id,
				'post_title' => (string) $post->post_title,
				'edit_link'  => '',
			);
		}

		$edit_link = get_edit_post_link( $post_id, 'raw' );

		return array(
			'post_id'    => (int) $post_id,
			'post_title' => (string) $post->post_title,
			'edit_link'  => $edit_link ? (string) $edit_link : '',
		);
	}
}

