<?php

namespace Tests\Unit\Dashboard;

use App\Services\Dashboard\PartitionedStatsService;
use CodeIgniter\Database\BaseBuilder;
use CodeIgniter\Database\BaseConnection;
use PHPUnit\Framework\TestCase;

/**
 * Which partition tables reporting is allowed to read.
 *
 * The Search 7 distribution cron writes client-facing copies of the click data to
 * `adv_clicks_client_<adv>_<YYYYMM>`. Reporting used to prefer those copies whenever
 * one merely existed for the requested month range. They are not safe to read: they
 * only hold the dates the cron actually ran for, their conversions are wiped for any
 * date whose Shorty mapping had not landed yet, and most of them are missing the
 * gender_id / age_group_id columns outright. So they were dropped, and the raw
 * `adv_clicks_` partitions are the single source for advertiser reporting.
 *
 * That was tried three times in the legacy portal before removal won — first reading
 * the copies outright, then gating them on a distribution-enrolment lookup, then
 * blending them per-date with the raw shards. A comment did not stop any of those.
 * These tests will: if the suite is green, nobody has quietly reinstated the client
 * prefix, and the allow-list still refuses to read one even if MySQL hands it over.
 *
 * PartitionedStatsService takes its connection by constructor injection, so this runs
 * on the lightweight tests/bootstrap.php with a mocked connection and no CI4 boot.
 *
 * @see \App\Services\Dashboard\PartitionedStatsService::listAdvTables
 * @see \App\Services\Dashboard\PartitionedStatsService::isSafePartitionTable
 */
final class PartitionedStatsTablesTest extends TestCase
{
    /** @var list<array{sql:string,binds:array}> */
    private $calls = [];

    /**
     * A connection whose query() records every call and replays $results in order.
     *
     * Each entry of $results is the row set for the n-th query, in the shape
     * SHOW TABLES returns (`Tables_in_keywords` => name). A missing entry yields [].
     *
     * @param list<list<array<string,string>>> $results
     */
    private function service(array $results = []): PartitionedStatsService
    {
        $this->calls = [];
        $calls       = &$this->calls;

        $conn = $this->createMock(BaseConnection::class);
        $conn->method('query')->willReturnCallback(
            static function (string $sql, $binds = null) use (&$calls, $results) {
                $i       = count($calls);
                $calls[] = ['sql' => $sql, 'binds' => is_array($binds) ? $binds : []];
                $rows    = isset($results[$i]) ? $results[$i] : [];

                return new class ($rows) {
                    /** @var list<array<string,string>> */
                    private $rows;

                    public function __construct(array $rows)
                    {
                        $this->rows = $rows;
                    }

                    public function getResultArray(): array
                    {
                        return $this->rows;
                    }
                };
            }
        );

        return new PartitionedStatsService($conn);
    }

    /** Every LIKE/range bind and every SQL string this test recorded, concatenated. */
    private function everythingSeen(): string
    {
        $out = '';
        foreach ($this->calls as $call) {
            $out .= $call['sql'] . ' ' . implode(' ', array_map('strval', $call['binds'])) . ' ';
        }

        return $out;
    }

    /**
     * The prefix probe is gone: two SHOW TABLES per detail check (clicks, then fb),
     * both against the raw prefixes. A third call would mean the client-table probe
     * is back.
     */
    public function testDetailTableLookupProbesRawPrefixesOnly(): void
    {
        $svc = $this->service();

        $svc->hasDetailTables(17258, '2026-07-01', '2026-07-31');

        $this->assertCount(2, $this->calls, 'expected exactly one SHOW TABLES per type');
        $this->assertSame('adv_clicks_17258_%', $this->calls[0]['binds'][0]);
        $this->assertSame('adv_fb_17258_%', $this->calls[1]['binds'][0]);
        $this->assertStringNotContainsStringIgnoringCase('client', $this->everythingSeen());
    }

    /**
     * The allow-list is the backstop: even when MySQL returns a client partition
     * alongside the raw one, the follow-up query must not name it. These table names
     * are interpolated into SQL, so this is a security boundary and not only a
     * correctness one.
     */
    public function testClientPartitionIsRejectedByTheAllowList(): void
    {
        $svc = $this->service([
            [
                ['Tables_in_keywords' => 'adv_clicks_17258_202607'],
                ['Tables_in_keywords' => 'adv_clicks_client_17258_202607'],
            ],
        ]);

        $svc->getDistinctCountryCodes(17258, '2026-07-01', '2026-07-31');

        $this->assertCount(2, $this->calls, 'expected SHOW TABLES then the country query');
        $this->assertStringContainsString('adv_clicks_17258_202607', $this->calls[1]['sql']);
        $this->assertStringNotContainsStringIgnoringCase('client', $this->calls[1]['sql']);
    }

    /**
     * A demographic filter reads the raw clicks shards and skips fb entirely: adv_fb_*
     * has no gender_id / age_group_id column, so the predicate would throw and the
     * caller's catch would silently zero the whole pass. Exactly one query, no fb pass.
     */
    public function testDemographicFilterReadsRawClicksAndSkipsFacebook(): void
    {
        $svc = $this->service();

        $svc->getMergedTotals(17258, '2026-07-01', '2026-07-31', ['gender_ids' => [1]]);

        $this->assertCount(1, $this->calls, 'fb must not be probed under a demographic filter');
        $this->assertSame('adv_clicks_17258_%', $this->calls[0]['binds'][0]);
        $this->assertStringNotContainsStringIgnoringCase('client', $this->everythingSeen());
    }

    /**
     * The aggregate tier is untouched by the client-table removal: a flagged advertiser
     * still resolves adv_aggr_ and never consults a clicks prefix.
     */
    public function testAggregateAdvertiserStillResolvesAggregatePrefix(): void
    {
        $this->calls = [];
        $calls       = &$this->calls;

        $result = new class {
            public function getRowArray(int $n = 0)
            {
                return ['id' => 1];
            }
        };

        $builder = $this->createMock(BaseBuilder::class);
        $builder->method('select')->willReturnSelf();
        $builder->method('where')->willReturnSelf();
        $builder->method('limit')->willReturnSelf();
        $builder->method('get')->willReturn($result);

        $conn = $this->createMock(BaseConnection::class);
        $conn->method('table')->willReturn($builder);
        $conn->method('query')->willReturnCallback(
            static function (string $sql, $binds = null) use (&$calls) {
                $calls[] = ['sql' => $sql, 'binds' => is_array($binds) ? $binds : []];

                return new class {
                    public function getResultArray(): array
                    {
                        return [];
                    }
                };
            }
        );

        (new PartitionedStatsService($conn))->usesAggregateTables(17258, '2026-07-01', '2026-07-31');

        $this->assertCount(1, $this->calls);
        $this->assertSame('adv_aggr_17258_%', $this->calls[0]['binds'][0]);
    }
}