<?php
declare(strict_types=1);

use PHPUnit\Framework\TestCase;

/**
 * Integration tests for WPDO_Query_Router SQL generation.
 *
 * Verifies that pre_get_posts correctly extracts hot-field clauses from
 * WP_Query meta_query, and that posts_join / posts_where / posts_groupby
 * emit syntactically correct SQL fragments against the integration $wpdb.
 *
 * No hot table data is written — only SQL string output is asserted.
 */
class QueryRouterIntegrationTest extends TestCase {

	private WPDO_Query_Router $router;

	// ── Fixture lifecycle ─────────────────────────────────────────────────

	public static function setUpBeforeClass(): void {
		// Register test fields in Schema Registry (in-memory singleton).
		$registry = WPDO_Schema_Registry::instance();
		$registry->register( 'test', [
			'post_type' => 'hp_listing',
			'meta_key'  => 'hp_price',
			'zone'      => 'hot',
			'data_type' => 'decimal(10,2) NOT NULL DEFAULT 0',
			'column'    => 'hp_price',
			'indexed'   => true,
		] );
		$registry->register( 'test', [
			'post_type' => 'hp_listing',
			'meta_key'  => 'hp_featured',
			'zone'      => 'hot',
			'data_type' => 'tinyint(1) NOT NULL DEFAULT 0',
			'column'    => 'hp_featured',
			'indexed'   => false,
		] );
	}

	protected function setUp(): void {
		$this->router = new WPDO_Query_Router();
		// Ensure the hot_hp_listing module is in a query-active state.
		WPDO_Feature_Flags::set( 'hot_hp_listing', 'complete' );
	}

	// ── pre_get_posts ─────────────────────────────────────────────────────

	public function test_pre_get_posts_extracts_hot_clause(): void {
		$query = new WP_Query();
		$query->set( 'post_type', 'hp_listing' );
		$query->set( 'meta_query', [
			[ 'key' => 'hp_price', 'value' => '100', 'compare' => '>=', 'type' => 'DECIMAL' ],
		] );

		$this->router->pre_get_posts( $query );

		$hot = $query->get( 'wpdo_hot_clauses' );
		$this->assertIsArray( $hot );
		$this->assertArrayHasKey( 'hp_listing', $hot );
		$this->assertCount( 1, $hot['hp_listing'] );
		$this->assertSame( 'hp_price', $hot['hp_listing'][0]['column'] );
		$this->assertSame( '>=',       $hot['hp_listing'][0]['compare'] );
		$this->assertSame( '100',      $hot['hp_listing'][0]['value'] );

		// Extracted clause must be removed from meta_query.
		$remaining = (array) $query->get( 'meta_query' );
		$this->assertEmpty( $remaining );
	}

	public function test_pre_get_posts_leaves_non_registered_key_in_meta_query(): void {
		$query = new WP_Query();
		$query->set( 'post_type', 'hp_listing' );
		$query->set( 'meta_query', [
			[ 'key' => 'hp_price',   'value' => '50',  'compare' => '=' ],
			[ 'key' => 'custom_key', 'value' => 'abc', 'compare' => '=' ],
		] );

		$this->router->pre_get_posts( $query );

		$hot       = $query->get( 'wpdo_hot_clauses' );
		$remaining = (array) $query->get( 'meta_query' );

		// hp_price hot-extracted.
		$this->assertArrayHasKey( 'hp_listing', $hot );
		$this->assertCount( 1, $hot['hp_listing'] );

		// custom_key stays in meta_query.
		$this->assertCount( 1, $remaining );
		$found_keys = array_column( array_values( $remaining ), 'key' );
		$this->assertContains( 'custom_key', $found_keys );
	}

	public function test_pre_get_posts_skips_when_no_meta_query(): void {
		$query = new WP_Query();
		$query->set( 'post_type', 'hp_listing' );
		// No meta_query set.

		$this->router->pre_get_posts( $query );

		$hot = $query->get( 'wpdo_hot_clauses' );
		// Should not have been set at all (get returns default '').
		$this->assertEmpty( $hot );
	}

	public function test_pre_get_posts_skips_when_no_post_type(): void {
		$query = new WP_Query();
		// No post_type set.
		$query->set( 'meta_query', [
			[ 'key' => 'hp_price', 'value' => '10', 'compare' => '=' ],
		] );

		$this->router->pre_get_posts( $query );

		$hot = $query->get( 'wpdo_hot_clauses' );
		$this->assertEmpty( $hot );
	}

	public function test_pre_get_posts_skips_inactive_module(): void {
		WPDO_Feature_Flags::set( 'hot_hp_listing', 'idle' );

		$query = new WP_Query();
		$query->set( 'post_type', 'hp_listing' );
		$query->set( 'meta_query', [
			[ 'key' => 'hp_price', 'value' => '99', 'compare' => '=' ],
		] );

		$this->router->pre_get_posts( $query );

		$hot       = $query->get( 'wpdo_hot_clauses' );
		$remaining = (array) $query->get( 'meta_query' );

		// No hot clauses extracted.
		$this->assertEmpty( $hot );
		// Original clause still in meta_query.
		$this->assertNotEmpty( $remaining );
	}

	public function test_pre_get_posts_preserves_relation_in_remaining(): void {
		$query = new WP_Query();
		$query->set( 'post_type', 'hp_listing' );
		$query->set( 'meta_query', [
			'relation' => 'AND',
			[ 'key' => 'hp_price',   'value' => '50', 'compare' => '>=' ],
			[ 'key' => 'custom_key', 'value' => '1',  'compare' => '=' ],
		] );

		$this->router->pre_get_posts( $query );

		$remaining = (array) $query->get( 'meta_query' );

		// relation must be preserved because custom_key remains.
		$this->assertArrayHasKey( 'relation', $remaining );
		$this->assertSame( 'AND', $remaining['relation'] );
	}

	public function test_pre_get_posts_extracts_multiple_hot_fields(): void {
		$query = new WP_Query();
		$query->set( 'post_type', 'hp_listing' );
		$query->set( 'meta_query', [
			[ 'key' => 'hp_price',    'value' => '200',  'compare' => '<=' ],
			[ 'key' => 'hp_featured', 'value' => '1',    'compare' => '=' ],
		] );

		$this->router->pre_get_posts( $query );

		$hot = $query->get( 'wpdo_hot_clauses' );
		$this->assertCount( 2, $hot['hp_listing'] );

		$columns = array_column( $hot['hp_listing'], 'column' );
		$this->assertContains( 'hp_price',    $columns );
		$this->assertContains( 'hp_featured', $columns );

		// meta_query fully cleared.
		$this->assertEmpty( (array) $query->get( 'meta_query' ) );
	}

	// ── posts_join ────────────────────────────────────────────────────────

	public function test_posts_join_generates_left_join_sql(): void {
		$query = new WP_Query();
		$query->set( 'wpdo_hot_clauses', [
			'hp_listing' => [
				[ 'column' => 'hp_price', 'compare' => '=', 'type' => 'CHAR', 'value' => '100' ],
			],
		] );

		$join = $this->router->posts_join( '', $query );

		$this->assertStringContainsString( 'LEFT JOIN', $join );
		// Full physical table name.
		$this->assertStringContainsString( 'wp_itest_wpdo_hot_hp_listing', $join );
		// Alias.
		$this->assertStringContainsString( '`wpdo_hot_hp_listing`', $join );
		// posts table join key.
		$this->assertStringContainsString( '`wp_itest_posts`', $join );
		$this->assertStringContainsString( 'post_id', $join );
	}

	public function test_posts_join_passthrough_when_no_hot_clauses(): void {
		$query      = new WP_Query();
		$original   = ' LEFT JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id)';

		$join = $this->router->posts_join( $original, $query );

		$this->assertSame( $original, $join );
	}

	public function test_posts_join_no_duplicate_join_for_same_alias(): void {
		$query = new WP_Query();
		$query->set( 'wpdo_hot_clauses', [
			'hp_listing' => [
				[ 'column' => 'hp_price', 'compare' => '=', 'type' => 'CHAR', 'value' => '100' ],
			],
		] );

		// Simulate alias already present in existing join string.
		$existing = ' LEFT JOIN `wp_itest_wpdo_hot_hp_listing` AS `wpdo_hot_hp_listing` ON (...)';
		$join     = $this->router->posts_join( $existing, $query );

		// Should appear exactly once.
		$this->assertSame( 1, substr_count( $join, '`wpdo_hot_hp_listing`' ) );
	}

	// ── posts_where ───────────────────────────────────────────────────────

	/** Helper: build a WP_Query with pre-set hot_clauses. */
	private function query_with_clauses( array $clauses ): WP_Query {
		$query = new WP_Query();
		$query->set( 'wpdo_hot_clauses', [ 'hp_listing' => $clauses ] );
		return $query;
	}

	public function test_posts_where_equality_condition(): void {
		$query = $this->query_with_clauses( [
			[ 'column' => 'hp_price', 'compare' => '=', 'type' => 'CHAR', 'value' => '99.00' ],
		] );

		$where = $this->router->posts_where( '', $query );

		$this->assertStringContainsString( '`wpdo_hot_hp_listing`.`hp_price`', $where );
		$this->assertStringContainsString( '=', $where );
		$this->assertStringContainsString( "'99.00'", $where );
	}

	public function test_posts_where_numeric_type_uses_integer_placeholder(): void {
		$query = $this->query_with_clauses( [
			[ 'column' => 'hp_featured', 'compare' => '=', 'type' => 'NUMERIC', 'value' => 1 ],
		] );

		$where = $this->router->posts_where( '', $query );

		// %d format — integer value, not quoted.
		$this->assertStringContainsString( '`wpdo_hot_hp_listing`.`hp_featured` = 1', $where );
	}

	public function test_posts_where_in_condition(): void {
		$query = $this->query_with_clauses( [
			[ 'column' => 'hp_price', 'compare' => 'IN', 'type' => 'CHAR', 'value' => [ '10.00', '20.00', '30.00' ] ],
		] );

		$where = $this->router->posts_where( '', $query );

		$this->assertStringContainsString( 'IN', $where );
		$this->assertStringContainsString( "'10.00'", $where );
		$this->assertStringContainsString( "'20.00'", $where );
		$this->assertStringContainsString( "'30.00'", $where );
	}

	public function test_posts_where_in_empty_array_generates_false_condition(): void {
		$query = $this->query_with_clauses( [
			[ 'column' => 'hp_price', 'compare' => 'IN', 'type' => 'CHAR', 'value' => [] ],
		] );

		$where = $this->router->posts_where( '', $query );

		$this->assertStringContainsString( '1=0', $where );
	}

	public function test_posts_where_between_condition(): void {
		$query = $this->query_with_clauses( [
			[ 'column' => 'hp_price', 'compare' => 'BETWEEN', 'type' => 'CHAR', 'value' => [ '10.00', '50.00' ] ],
		] );

		$where = $this->router->posts_where( '', $query );

		$this->assertStringContainsString( 'BETWEEN', $where );
		$this->assertStringContainsString( "'10.00'", $where );
		$this->assertStringContainsString( "'50.00'", $where );
	}

	public function test_posts_where_exists_generates_is_not_null(): void {
		$query = $this->query_with_clauses( [
			[ 'column' => 'hp_price', 'compare' => 'EXISTS', 'type' => 'CHAR', 'value' => '' ],
		] );

		$where = $this->router->posts_where( '', $query );

		$this->assertStringContainsString( '`wpdo_hot_hp_listing`.`hp_price` IS NOT NULL', $where );
	}

	public function test_posts_where_not_exists_generates_is_null(): void {
		$query = $this->query_with_clauses( [
			[ 'column' => 'hp_price', 'compare' => 'NOT EXISTS', 'type' => 'CHAR', 'value' => '' ],
		] );

		$where = $this->router->posts_where( '', $query );

		$this->assertStringContainsString( '`wpdo_hot_hp_listing`.`hp_price` IS NULL', $where );
	}

	public function test_posts_where_passthrough_when_no_hot_clauses(): void {
		$query    = new WP_Query();
		$original = ' AND wp_posts.post_status = \'publish\'';

		$where = $this->router->posts_where( $original, $query );

		$this->assertSame( $original, $where );
	}

	public function test_posts_where_appends_to_existing_where(): void {
		$query    = $this->query_with_clauses( [
			[ 'column' => 'hp_featured', 'compare' => '=', 'type' => 'NUMERIC', 'value' => 1 ],
		] );
		$existing = " AND wp_posts.post_status = 'publish'";

		$where = $this->router->posts_where( $existing, $query );

		$this->assertStringStartsWith( $existing, $where );
		$this->assertStringContainsString( 'hp_featured', $where );
	}

	// ── posts_groupby ─────────────────────────────────────────────────────

	public function test_posts_groupby_sets_posts_id_when_empty(): void {
		$query = new WP_Query();
		$query->set( 'wpdo_hot_clauses', [
			'hp_listing' => [
				[ 'column' => 'hp_price', 'compare' => '=', 'type' => 'CHAR', 'value' => '1' ],
			],
		] );

		$groupby = $this->router->posts_groupby( '', $query );

		$this->assertStringContainsString( 'wp_itest_posts', $groupby );
		$this->assertStringContainsString( 'ID', $groupby );
	}

	public function test_posts_groupby_preserves_existing_groupby(): void {
		$query = new WP_Query();
		$query->set( 'wpdo_hot_clauses', [
			'hp_listing' => [
				[ 'column' => 'hp_price', 'compare' => '=', 'type' => 'CHAR', 'value' => '1' ],
			],
		] );
		$existing = '`wp_itest_posts`.`ID`, `wp_itest_posts`.`post_type`';

		$groupby = $this->router->posts_groupby( $existing, $query );

		// Existing groupby preserved unchanged (not empty, so no override).
		$this->assertSame( $existing, $groupby );
	}

	public function test_posts_groupby_passthrough_when_no_hot_clauses(): void {
		$query    = new WP_Query();
		$original = '`wp_itest_posts`.`ID`';

		$groupby = $this->router->posts_groupby( $original, $query );

		$this->assertSame( $original, $groupby );
	}
}