<?php

namespace SerpIQ\Api\Controllers;

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

final class Pages_Controller {
	private static ?Pages_Controller $instance = null;

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

	public function register_routes(): void {
		register_rest_route(
			'serpiq/v1',
			'/pages',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'list' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_list_args( array( 'page_url' ) ),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/page-keywords',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'keywords_for_page' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => array_merge(
					$this->common_list_args( array( 'clicks', 'impressions', 'ctr', 'position', 'query' ) ),
					array(
						'pageUrl' => array(
							'required'          => true,
							'type'              => 'string',
							'sanitize_callback' => 'esc_url_raw',
						),
					)
				),
			)
		);

		register_rest_route(
			'serpiq/v1',
			'/pages-with-change',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'list_with_change' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => $this->common_list_args( array( 'page_url', 'clicks', 'impressions', 'ctr', 'position' ) ),
			)
		);
	}

	public function validate_date_arg( mixed $value, \WP_REST_Request $request, string $param ): bool {
		unset( $request, $param );
		$v = is_scalar( $value ) ? (string) $value : '';
		$v = trim( $v );
		if ( '' === $v ) {
			return true;
		}
		return (bool) preg_match( '/^\d{4}-\d{2}-\d{2}$/', $v );
	}

	/**
	 * Common list args used across read-only report endpoints.
	 *
	 * @param string[] $allowed_order
	 * @return array<string, array<string, mixed>>
	 */
	private function common_list_args( array $allowed_order ): array {
		return array(
			'start'   => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
				'validate_callback' => array( $this, 'validate_date_arg' ),
			),
			'end'     => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
				'validate_callback' => array( $this, 'validate_date_arg' ),
			),
			'search'  => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
			),
			'country' => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
			),
			'device'  => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_text_field',
			),
			'page'    => array(
				'required'          => false,
				'type'              => 'integer',
				'default'           => 1,
				'sanitize_callback' => 'absint',
			),
			'perPage' => array(
				'required'          => false,
				'type'              => 'integer',
				'default'           => 50,
				'sanitize_callback' => 'absint',
			),
			'orderBy' => array(
				'required'          => false,
				'type'              => 'string',
				'sanitize_callback' => 'sanitize_key',
				'enum'              => $allowed_order,
			),
			'orderDir' => array(
				'required'          => false,
				'type'              => 'string',
				'default'           => 'DESC',
				'sanitize_callback' => 'sanitize_text_field',
				'enum'              => array( 'ASC', 'DESC', 'asc', 'desc' ),
			),
		);
	}

	public function list( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$start = $this->sanitize_date( (string) $request->get_param( 'start' ) ) ?? gmdate( 'Y-m-d', strtotime( '-28 day' ) );
		$end = $this->sanitize_date( (string) $request->get_param( 'end' ) ) ?? gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$search = sanitize_text_field( (string) $request->get_param( 'search' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$page = max( 1, absint( $request->get_param( 'page' ) ) );
		$per_page = absint( $request->get_param( 'perPage' ) );
		$per_page = $per_page > 0 ? min( 200, $per_page ) : 50;
		$offset = ( $page - 1 ) * $per_page;

		$order_by = sanitize_key( (string) $request->get_param( 'orderBy' ) );
		$order_dir = strtoupper( (string) $request->get_param( 'orderDir' ) );
		$order_dir = in_array( $order_dir, array( 'ASC', 'DESC' ), true ) ? $order_dir : 'DESC';
		$allowed_order = array( 'clicks', 'impressions', 'ctr', 'position', 'page_url' );
		if ( ! in_array( $order_by, $allowed_order, true ) ) {
			$order_by = 'clicks';
		}

		$table = $wpdb->prefix . 'serpiq_pages';
		$where = 'date BETWEEN %s AND %s';
		$params = array( $start, $end );

		if ( '' !== $country ) {
			$where .= ' AND country = %s';
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$where .= ' AND device = %s';
			$params[] = $device;
		}
		if ( '' !== $search ) {
			$where .= ' AND page_url LIKE %s';
			$params[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		$sql_count = "SELECT COUNT(DISTINCT page_url) FROM %i WHERE {$where}";
		$total = (int) $wpdb->get_var( $wpdb->prepare( $sql_count, array_merge( array( $table ), $params ) ) );

		$sql = "
			SELECT
				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 {$where}
			GROUP BY page_url
			ORDER BY %i {$order_dir}
			LIMIT %d OFFSET %d
		";

		$params2 = array_merge( array( $table ), $params, array( $order_by, $per_page, $offset ) );
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params2 ), ARRAY_A );

		return new \WP_REST_Response(
			array(
				'page'    => $page,
				'perPage' => $per_page,
				'total'   => $total,
				'items'   => is_array( $rows ) ? $rows : array(),
			),
			200
		);
	}

	/**
	 * List pages with metrics for the current period and the immediately previous period
	 * (same length), to allow "change" calculations on the frontend.
	 *
	 * Returns items of the shape:
	 * [
	 *   'page_url' => '...',
	 *   'current'  => [ 'clicks' => ..., 'impressions' => ..., 'ctr' => ..., 'position' => ... ],
	 *   'previous' => [ 'clicks' => ..., 'impressions' => ..., 'ctr' => ..., 'position' => ... ],
	 * ]
	 */
	public function list_with_change( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$start   = $this->sanitize_date( (string) $request->get_param( 'start' ) ) ?? gmdate( 'Y-m-d', strtotime( '-28 day' ) );
		$end     = $this->sanitize_date( (string) $request->get_param( 'end' ) ) ?? gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$search  = sanitize_text_field( (string) $request->get_param( 'search' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$start_ts = strtotime( $start );
		$end_ts   = strtotime( $end );
		if ( false === $start_ts || false === $end_ts || $start_ts > $end_ts ) {
			return new \WP_REST_Response(
				array(
					'items' => array(),
				),
				200
			);
		}

		$days          = (int) floor( ( $end_ts - $start_ts ) / DAY_IN_SECONDS ) + 1;
		$prev_end_ts   = $start_ts - DAY_IN_SECONDS;
		$prev_start_ts = $prev_end_ts - ( $days - 1 ) * DAY_IN_SECONDS;
		$prev_start    = gmdate( 'Y-m-d', $prev_start_ts );
		$prev_end      = gmdate( 'Y-m-d', $prev_end_ts );

		$table = $wpdb->prefix . 'serpiq_pages';

		// Shared filters (country, device, search).
		$filter_sql  = '';
		$filter_args = array();
		if ( '' !== $country ) {
			$filter_sql   .= ' AND country = %s';
			$filter_args[] = $country;
		}
		if ( '' !== $device ) {
			$filter_sql   .= ' AND device = %s';
			$filter_args[] = $device;
		}
		if ( '' !== $search ) {
			$filter_sql   .= ' AND page_url LIKE %s';
			$filter_args[] = '%' . $wpdb->esc_like( $search ) . '%';
		}

		// Current period.
		$sql_current = "
			SELECT
				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
			{$filter_sql}
			GROUP BY page_url
			ORDER BY clicks DESC
			LIMIT 5000
		";

		$params_current = array_merge( array( $table ), array( $start, $end ), $filter_args );
		$rows_current   = $wpdb->get_results( $wpdb->prepare( $sql_current, $params_current ), ARRAY_A );

		// Previous period.
		$sql_prev = "
			SELECT
				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
			{$filter_sql}
			GROUP BY page_url
			ORDER BY clicks DESC
			LIMIT 5000
		";

		$params_prev = array_merge( array( $table ), array( $prev_start, $prev_end ), $filter_args );
		$rows_prev   = $wpdb->get_results( $wpdb->prepare( $sql_prev, $params_prev ), ARRAY_A );

		$current_map = array();
		if ( is_array( $rows_current ) ) {
			foreach ( $rows_current as $row ) {
				$url                    = isset( $row['page_url'] ) ? (string) $row['page_url'] : '';
				$current_map[ $url ] = array(
					'clicks'      => (float) ( $row['clicks'] ?? 0 ),
					'impressions' => (float) ( $row['impressions'] ?? 0 ),
					'ctr'         => (float) ( $row['ctr'] ?? 0 ),
					'position'    => (float) ( $row['position'] ?? 0 ),
				);
			}
		}

		$prev_map = array();
		if ( is_array( $rows_prev ) ) {
			foreach ( $rows_prev as $row ) {
				$url                 = isset( $row['page_url'] ) ? (string) $row['page_url'] : '';
				$prev_map[ $url ] = array(
					'clicks'      => (float) ( $row['clicks'] ?? 0 ),
					'impressions' => (float) ( $row['impressions'] ?? 0 ),
					'ctr'         => (float) ( $row['ctr'] ?? 0 ),
					'position'    => (float) ( $row['position'] ?? 0 ),
				);
			}
		}

		$all_urls = array_unique(
			array_merge(
				array_keys( $current_map ),
				array_keys( $prev_map )
			)
		);

		$items = array();
		foreach ( $all_urls as $url ) {
			$items[] = array(
				'page_url' => $url,
				'current'  => $current_map[ $url ] ?? array(
					'clicks'      => 0,
					'impressions' => 0,
					'ctr'         => 0,
					'position'    => 0,
				),
				'previous' => $prev_map[ $url ] ?? array(
					'clicks'      => 0,
					'impressions' => 0,
					'ctr'         => 0,
					'position'    => 0,
				),
			);
		}

		return new \WP_REST_Response(
			array(
				'items' => $items,
			),
			200
		);
	}

	public function keywords_for_page( \WP_REST_Request $request ): \WP_REST_Response {
		global $wpdb;

		$page_url_raw = (string) $request->get_param( 'pageUrl' );
		$page_url     = '' !== $page_url_raw ? esc_url_raw( $page_url_raw ) : '';
		if ( '' === $page_url ) {
			return new \WP_REST_Response(
				array(
					'page'    => 1,
					'perPage' => 10,
					'total'   => 0,
					'items'   => array(),
				),
				200
			);
		}

		$start   = $this->sanitize_date( (string) $request->get_param( 'start' ) ) ?? gmdate( 'Y-m-d', strtotime( '-28 day' ) );
		$end     = $this->sanitize_date( (string) $request->get_param( 'end' ) ) ?? gmdate( 'Y-m-d', strtotime( '-1 day' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$page     = max( 1, absint( $request->get_param( 'page' ) ) );
		$per_page = absint( $request->get_param( 'perPage' ) );
		$per_page = $per_page > 0 ? min( 500, $per_page ) : 25;
		$offset   = ( $page - 1 ) * $per_page;

		$order_by  = sanitize_key( (string) $request->get_param( 'orderBy' ) );
		$order_dir = strtoupper( (string) $request->get_param( 'orderDir' ) );
		$order_dir = in_array( $order_dir, array( 'ASC', 'DESC' ), true ) ? $order_dir : 'DESC';
		$allowed_order = array( 'clicks', 'impressions', 'ctr', 'position', 'query' );
		if ( ! in_array( $order_by, $allowed_order, true ) ) {
			$order_by = 'clicks';
		}

		$table  = $wpdb->prefix . 'serpiq_queries';
		$where  = 'date BETWEEN %s AND %s AND page_url = %s';
		$params = array( $start, $end, $page_url );

		if ( '' !== $country ) {
			$where    .= ' AND country = %s';
			$params[] = $country;
		}
		if ( '' !== $device ) {
			$where    .= ' AND device = %s';
			$params[] = $device;
		}

		$sql_count = "
			SELECT COUNT(DISTINCT query)
			FROM %i
			WHERE {$where}
		";
		$total     = (int) $wpdb->get_var( $wpdb->prepare( $sql_count, array_merge( array( $table ), $params ) ) );

		$sql = "
			SELECT
				query,
				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 {$where}
			GROUP BY query
			ORDER BY %i {$order_dir}
			LIMIT %d OFFSET %d
		";

		$params2 = array_merge( array( $table ), $params, array( $order_by, $per_page, $offset ) );
		$rows    = $wpdb->get_results( $wpdb->prepare( $sql, $params2 ), ARRAY_A );

		return new \WP_REST_Response(
			array(
				'page'    => $page,
				'perPage' => $per_page,
				'total'   => $total,
				'items'   => is_array( $rows ) ? $rows : array(),
			),
			200
		);
	}

	private function sanitize_date( string $date ): ?string {
		if ( '' === $date ) {
			return null;
		}
		return preg_match( '/^\d{4}-\d{2}-\d{2}$/', $date ) ? $date : null;
	}
}

