<?php

namespace SerpIQ\Api\Controllers;

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

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

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

	public function register_routes(): void {
		register_rest_route(
			'serpiq/v1',
			'/cannibalization',
			array(
				'methods'             => 'GET',
				'callback'            => array( $this, 'list' ),
				'permission_callback' => array( \SerpIQ\Api\Rest_Permission::class, 'admin_rest_request' ),
				'args'                => 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' ) ),
					'country' => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'device'  => array( 'required' => false, 'type' => 'string', 'sanitize_callback' => 'sanitize_text_field' ),
					'min_impressions' => array( 'required' => false, 'type' => 'integer', 'sanitize_callback' => 'absint' ),
					'min_pages'       => array( 'required' => false, 'type' => 'integer', 'sanitize_callback' => 'absint' ),
					'page'    => array( 'required' => false, 'type' => 'integer', 'default' => 1, 'sanitize_callback' => 'absint' ),
					'perPage' => array( 'required' => false, 'type' => 'integer', 'default' => 50, 'sanitize_callback' => 'absint' ),
				),
			)
		);
	}

	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 );
	}

	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' ) );
		$country = sanitize_text_field( (string) $request->get_param( 'country' ) );
		$device  = sanitize_text_field( (string) $request->get_param( 'device' ) );

		$min_impressions = max( 0, absint( $request->get_param( 'min_impressions' ) ) );
		if ( $min_impressions <= 0 ) {
			$min_impressions = 50;
		}

		$min_pages = max( 2, absint( $request->get_param( 'min_pages' ) ) );

		$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;

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

		$where_extra = '';
		$params_extra = array();
		if ( '' !== $country ) {
			$where_extra .= ' AND country = %s';
			$params_extra[] = $country;
		}
		if ( '' !== $device ) {
			$where_extra .= ' AND device = %s';
			$params_extra[] = $device;
		}

		// Normalize page_url by stripping fragment (#...) so same page with different anchors counts as one.
		$norm = 'SUBSTRING_INDEX(page_url, \'#\', 1)';

		$sql_count = "
			SELECT COUNT(*) FROM (
				SELECT query
				FROM %i
				WHERE date BETWEEN %s AND %s {$where_extra}
				GROUP BY query
				HAVING COALESCE(SUM(impressions),0) >= %d AND COUNT(DISTINCT {$norm}) >= %d
			) AS sub
		";
		$count_params = array_merge( array( $table ), array( $start, $end ), $params_extra, array( $min_impressions, $min_pages ) );
		$total = (int) $wpdb->get_var( $wpdb->prepare( $sql_count, $count_params ) );

		$sql = "
			SELECT
				query,
				COUNT(DISTINCT {$norm}) AS pages_count,
				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 {$where_extra}
			GROUP BY query
			HAVING COALESCE(SUM(impressions),0) >= %d AND COUNT(DISTINCT {$norm}) >= %d
			ORDER BY impressions DESC
			LIMIT %d OFFSET %d
		";
		$params = array_merge( array( $table ), array( $start, $end ), $params_extra, array( $min_impressions, $min_pages, $per_page, $offset ) );
		$rows = $wpdb->get_results( $wpdb->prepare( $sql, $params ), ARRAY_A );

		$items = array();
		foreach ( $rows as $row ) {
			$url_params = array_merge( array( $table, $row['query'], $start, $end ), $params_extra );
			$url_sql = "SELECT DISTINCT {$norm} AS page_url FROM %i WHERE query = %s AND date BETWEEN %s AND %s {$where_extra} ORDER BY page_url LIMIT 20";
			$urls = $wpdb->get_col( $wpdb->prepare( $url_sql, $url_params ) );
			$row['page_urls'] = is_array( $urls ) ? $urls : array();
			$items[] = $row;
		}

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

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