<?php
error_reporting(E_ALL & ~E_DEPRECATED & ~E_USER_DEPRECATED);
require_once __DIR__ . '/../../vendor/autoload.php';
$container = require __DIR__ . '/../../app/bootstrap.php';

final class KpiFinancialRecon
{
    private PDO $pdo;
    private \App\Repositories\KpiRepository $repo;
    private ?\App\Services\KpiService $kpiService;
    private array $f;
    private array $deals = [];
    private array $dealById = [];
    private array $personsByContact = [];
    private array $txByDeal = [];
    private array $jsonByDeal = [];
    private bool $verbose = false;
    private bool $json = false;
    private int $traceLimit = 25;
    private string $onlyCard = '';
    private string $surface = 'performance';

    public function __construct(PDO $pdo, \App\Repositories\KpiRepository $repo, array $filters, ?\App\Services\KpiService $kpiService = null)
    {
        $this->pdo = $pdo;
        $this->repo = $repo;
        $this->kpiService = $kpiService;
        $this->f = $filters;
        $this->verbose = !empty($filters['_verbose']);
        $this->json = !empty($filters['_json']);
        $this->traceLimit = max(1, (int)($filters['_trace_limit'] ?? 25));
        $this->onlyCard = (string)($filters['_only_card'] ?? '');
        $this->surface = in_array(($filters['_surface'] ?? 'performance'), ['performance', 'kpi', 'both'], true)
            ? (string)$filters['_surface']
            : 'performance';
    }

    public function run(): int
    {
        $this->load();
        $obs = $this->observed();
        $exp = $this->expected();
        $this->print($obs, $exp);
        return 0;
    }

    private function load(): void
    {
        $this->deals = $this->pdo->query("SELECT d.*, DATE(COALESCE(d.register_time,d.created_at)) deal_date FROM deals d")->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($this->deals as $d) { $this->dealById[(string)$d['id']] = $d; }
        $persons = $this->pdo->query("SELECT didar_contact_id, DATE(COALESCE(register_time,created_at)) pdate FROM persons")->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($persons as $p) { $this->personsByContact[(string)$p['didar_contact_id']] = $p; }
        $tx = $this->pdo->query("SELECT deal_id, amount, payment_date, created_at, status FROM transactions WHERE COALESCE(amount,0)>0")->fetchAll(PDO::FETCH_ASSOC) ?: [];
        foreach ($tx as $r) { $this->txByDeal[(string)$r['deal_id']][] = $r; }
        foreach ($this->deals as $d) { $this->jsonByDeal[(string)$d['id']] = $this->jsonPayments($d); }
    }

    private function observed(): array
    {
        $dashboard = $this->repo->getPerformanceDashboardData($this->f);
        $s = ($dashboard['summary_cards'] ?? []);
        $charts = ($dashboard['charts'] ?? []);
        $charts['conversion_funnel'] = [
            ['stage' => 'لید جدید', 'count' => (int)($s['lead_total_new'] ?? 0)],
            ['stage' => 'معامله', 'count' => (int)($s['deal_conversion_total_deals'] ?? 0)],
            ['stage' => 'پرداخت', 'count' => (int)($s['with_sales_deals_count'] ?? 0)],
            ['stage' => 'تسویه کامل', 'count' => (int)($s['settled_count'] ?? 0)],
        ];
        $mk = fn($t,$m)=>$this->repo->getKpiDrilldown($this->f,$t,$m,['date'=>'','owner_didar_id'=>'','owner_name'=>'','product_name'=>'','failure_reason'=>'','activity_type'=>'']);
        $result = [
            'dashboard' => $dashboard,
            'summary' => $s,
            'charts' => $charts,
            'dr' => [
                'sales' => $mk('performance_cards','sales_total'),
                'settled' => $mk('payment_status','settled_deals_count'),
                'outstanding' => $mk('payment_status','outstanding_amount_sum'),
                'without' => $mk('performance_cards','without_payment_count'),
                'open' => $mk('performance_cards','open_pipeline_count'),
                'won' => $mk('performance_cards','won_count'),
                'lost' => $mk('deal_status','deal_lost_total'),
            ]
        ];
        if ($this->surface === 'kpi' || $this->surface === 'both') {
            $result['classic_kpi'] = $this->observedClassicKpi();
        }
        return $result;
    }

    private function observedClassicKpi(): array
    {
        if ($this->kpiService === null || !method_exists($this->kpiService, 'buildDebugSnapshot')) {
            return ['available' => false];
        }
        if (!isset($_SESSION) || !is_array($_SESSION)) {
            $_SESSION = [];
        }
        $_SESSION['role'] = $_SESSION['role'] ?? 'admin';
        $_SESSION['didar_id'] = $_SESSION['didar_id'] ?? '';
        try {
            $snapshot = $this->kpiService->buildDebugSnapshot($this->f);
            return ['available' => true, 'snapshot' => $snapshot];
        } catch (\Throwable $e) {
            return ['available' => false, 'error' => $e->getMessage()];
        }
    }

    private function expected(): array
    {
        $cohort = $this->dealCohortIds();
        $noDateFiltered = $this->dealNoDateFilteredIds();
        $salesW = $this->aggByDeal($this->paymentEvents($this->f['kpi_sales_date_basis'],$this->f['kpi_payment_status_scope'],true), $this->f['kpi_sales_count_rule']);
        $salesAll = $this->aggByDeal($this->paymentEvents($this->f['kpi_sales_date_basis'],$this->f['kpi_payment_status_scope'],false), 'all_payments');
        $payW = $this->aggByDeal($this->paymentEvents($this->f['kpi_payment_date_basis'],$this->f['kpi_payment_status_scope'],true), $this->f['kpi_payment_count_rule']);
        $payAll = $this->aggByDeal($this->paymentEvents($this->f['kpi_payment_date_basis'],$this->f['kpi_payment_status_scope'],false), 'all_payments');
        $salesAllFirst = $this->aggByDeal($this->paymentEvents($this->f['kpi_sales_date_basis'],$this->f['kpi_payment_status_scope'],false), 'first_payment_only');

        $salesSet=[]; foreach ($salesAllFirst as $id=>$a) {
            if(!isset($noDateFiltered[$id])) continue;
            $d = $this->dealById[$id] ?? null; if(!$d) continue;
            $first = (string)($a['first'] ?? '');
            if($first === '' || !$this->inRange($first)) continue;
            $salesSet[$id] = [
                'total' => (float)($salesAll[$id]['total'] ?? 0),
                'first' => $first,
                'payable' => (float)($d['payable_amount'] ?? 0),
            ];
        }
        $settled=[]; $out=[]; foreach ($payW as $id=>$a) {
            if(!isset($noDateFiltered[$id])) continue; $d=$this->dealById[$id]??null; if(!$d) continue;
            $firstSales=(string)($salesAllFirst[$id]['first']??'');
            if($firstSales==='') continue;
            if($this->f['date_from'] && $firstSales < $this->f['date_from']) continue;
            if($this->f['date_to'] && $firstSales > $this->f['date_to']) continue;
            $p=(float)($d['payable_amount']??0); $paid=(float)($payAll[$id]['total']??0);
            if(($p<=0 && $paid>0) || ($p>0 && $paid>=$p)) $settled[$id]=['payable'=>$p,'paid'=>$paid];
            elseif($p>0 && $paid>0 && $paid<$p) $out[$id]=['payable'=>$p,'paid'=>$paid,'rem'=>$p-$paid];
        }
        $without=[]; foreach ($cohort as $id=>$_) {
            $d=$this->dealById[$id]??null; if(!$d) continue; $st=(string)($d['status']??'');
            if($st==='Won'||$st==='Lost') continue;
            if(!$this->hasAnyPayment($id)) $without[$id]=['payable'=>(float)($d['payable_amount']??0)];
        }
        $lost=[]; foreach ($this->deals as $d) {
            if (!$this->lostPass($d)) continue;
            $id=(string)$d['id'];
            $lost[$id]=['payable'=>(float)($d['payable_amount']??0)];
        }
        $won=[]; foreach ($cohort as $id=>$_) {
            $d=$this->dealById[$id]??null; if(!$d) continue;
            if ((string)($d['status'] ?? '') === 'Won') {
                $dealDate=(string)($d['deal_date']??'');
                $first=(string)($salesAllFirst[$id]['first']??'');
                if($first!=='' && $dealDate!=='' && $first < $dealDate){
                    if(!$this->inRange($first)) continue;
                }
                $won[$id]=['payable'=>(float)($d['payable_amount']??0)];
            }
        }
        $convCohort=[]; foreach($cohort as $id=>$_){
            $d=$this->dealById[$id]??null; if(!$d) continue;
            $dealDate=(string)($d['deal_date']??'');
            $first=(string)($salesAllFirst[$id]['first']??'');
            if($first!=='' && $dealDate!=='' && $first < $dealDate){
                if(!$this->inRange($first)) continue;
            }
            $convCohort[$id]=true;
        }
        $convConverted=[]; foreach($convCohort as $id=>$_){
            if (((float)($salesW[$id]['total']??0)) > 0) $convConverted[$id]=true;
        }
        $open=[]; foreach ($convCohort as $id=>$_) {
            $d=$this->dealById[$id]??null; if(!$d) continue; $st=(string)($d['status']??'');
            if($st==='Won'||$st==='Lost') continue;
            $open[$id]=['payable'=>(float)($d['payable_amount']??0)];
        }
        $crossDateSalesAdded=[]; foreach($salesSet as $id=>$row){
            if(isset($convCohort[$id])) continue;
            if(!isset($noDateFiltered[$id])) continue;
            $d=$this->dealById[$id]??null; if(!$d) continue;
            $st=(string)($d['status']??'');
            $crossDateSalesAdded[$id]=true;
            $convCohort[$id]=true;
            $convConverted[$id]=true;
            if($st==='Won'){
                $won[$id]=['payable'=>(float)($d['payable_amount']??0)];
            } elseif($st==='Lost'){
                $lost[$id]=['payable'=>(float)($d['payable_amount']??0)];
            } else {
                $open[$id]=['payable'=>(float)($d['payable_amount']??0)];
            }
        }
        $convReference=[]; foreach(array_keys($salesSet) as $id){ $convReference[(string)$id]=true; }
        foreach(array_keys($without) as $id){ $convReference[(string)$id]=true; }
        foreach(array_keys($lost) as $id){ $convReference[(string)$id]=true; }
        foreach(array_keys($won) as $id){ $convReference[(string)$id]=true; }
        return [
            'sets'=>compact('salesSet','settled','out','without','open','lost','won','convConverted','convCohort','convReference','crossDateSalesAdded'),
            'cards'=>[
                'sales'=>['count'=>count($salesSet),'contract'=>array_sum(array_column($salesSet,'payable')),'collected'=>array_sum(array_column($salesSet,'total'))],
                'settled'=>['count'=>count($settled),'contract'=>array_sum(array_column($settled,'payable')),'collected'=>array_sum(array_column($settled,'paid'))],
                'outstanding'=>['count'=>count($out),'contract'=>array_sum(array_column($out,'payable')),'collected'=>array_sum(array_column($out,'paid')),'rem'=>array_sum(array_column($out,'rem'))],
                'without'=>['count'=>count($without),'contract'=>array_sum(array_column($without,'payable'))],
                'open'=>['count'=>count($open),'amount'=>array_sum(array_column($open,'payable'))],
                'lost'=>['count'=>count($lost),'amount'=>array_sum(array_column($lost,'payable'))],
                'won'=>['count'=>count($won),'amount'=>array_sum(array_column($won,'payable'))],
                'deal_conversion'=>[
                    'total_deals'=>count($convReference),
                    'converted_deals'=>count($salesSet),
                    'conversion_rate'=>count($convReference)>0?round((count($salesSet)/count($convReference))*100,2):0.0,
                    'non_conversion_rate'=>count($convReference)>0?round(((count($convReference)-count($salesSet))/count($convReference))*100,2):0.0,
                ],
            ]
        ];
    }

    private function dealCohortIds(): array
    {
        $r=[]; foreach($this->deals as $d){ if($this->dealPass($d)) $r[(string)$d['id']]=true; } return $r;
    }
    private function dealNoDateFilteredIds(): array
    {
        $r=[]; foreach($this->deals as $d){ if($this->dealPassNoDate($d)) $r[(string)$d['id']]=true; } return $r;
    }
    private function dealPass(array $d): bool
    {
        $date=(string)($d['deal_date']??''); if(!$this->inRange($date)) return false;
        return $this->dealPassNoDate($d);
    }
    private function dealPassNoDate(array $d): bool
    {
        $legacy=(int)($d['is_legacy_didar']??0)===1; $ls=$this->f['legacy_deal_scope']??'exclude';
        if($ls==='exclude'&&$legacy) return false; if($ls==='only'&&!$legacy) return false;
        $owner=(string)($d['owner_didar_id']??''); $csv=trim((string)($this->f['owner_didar_ids_csv']??'')); $one=trim((string)($this->f['owner_didar_id']??''));
        if($csv!==''){ if(!in_array($owner,array_filter(array_map('trim',explode(',',$csv))),true)) return false; }
        elseif($one!=='' && $owner!==$one) return false;
        $statusCsv=trim((string)($this->f['deal_statuses_csv']??'')); if($statusCsv!==''){
            $sg=((string)($d['status']??'')==='Won')?'won':(((string)($d['status']??'')==='Lost')?'lost':'pending');
            if(!in_array($sg,array_filter(array_map('trim',explode(',',$statusCsv))),true)) return false;
        }
        $ltCsv=trim((string)($this->f['deal_lead_types_csv']??'')); if($ltCsv!==''){
            $lt=$this->leadType($d); if(!in_array($lt,array_filter(array_map('trim',explode(',',$ltCsv))),true)) return false;
        }
        return true;
    }
    private function leadType(array $d): string {
        $p=$this->personsByContact[(string)($d['contact_didar_id']??'')]??null; if(!$p) return 'old';
        return ((string)($p['pdate']??'')===(string)($d['deal_date']??''))?'new':'old';
    }
    private function lostPass(array $d): bool
    {
        if ((string)($d['status'] ?? '') !== 'Lost') return false;
        $lostDate = substr((string)($d['lost_time'] ?? $d['created_at'] ?? ''), 0, 10);
        if ($lostDate === '' || !$this->inRange($lostDate)) return false;
        // Reuse common deal filters except deal_status filter (lost-only is already enforced) and deal-date range
        $legacy=(int)($d['is_legacy_didar']??0)===1; $ls=$this->f['legacy_deal_scope']??'exclude';
        if($ls==='exclude'&&$legacy) return false; if($ls==='only'&&!$legacy) return false;
        $owner=(string)($d['owner_didar_id']??''); $csv=trim((string)($this->f['owner_didar_ids_csv']??'')); $one=trim((string)($this->f['owner_didar_id']??''));
        if($csv!==''){ if(!in_array($owner,array_filter(array_map('trim',explode(',',$csv))),true)) return false; }
        elseif($one!=='' && $owner!==$one) return false;
        return true;
    }

    private function paymentEvents(string $basis, string $statusScope, bool $window): array
    {
        $basis=in_array($basis,['ui_payment_date','db_payment_date','system_created_at'],true)?$basis:'ui_payment_date';
        $events=[];
        if($basis==='ui_payment_date'){
            foreach($this->dealById as $id=>$_){
                $json=$this->jsonByDeal[$id]??[];
                if($json){
                    foreach($json as $p){ if(($p['amount']??0)<=0) continue; if($window&&!$this->inRange($p['date'])) continue; $events[]=['deal_id'=>$id,'date'=>$p['date'],'amount'=>$p['amount']]; }
                    continue;
                }
                foreach(($this->txByDeal[$id]??[]) as $t){
                    if(!$this->txPass($t,$statusScope)) continue; $dt=$this->normDate((string)($t['payment_date']??'')); if($dt==='') continue;
                    if($window&&!$this->inRange($dt)) continue; $events[]=['deal_id'=>$id,'date'=>$dt,'amount'=>(float)$t['amount']];
                }
            }
            return $events;
        }
        foreach($this->txByDeal as $id=>$rows){ foreach($rows as $t){
            if(!$this->txPass($t,$statusScope)) continue;
            $dt=$basis==='system_created_at'?substr((string)($t['created_at']??''),0,10):$this->normDate((string)($t['payment_date']??''));
            if($dt==='') continue; if($window&&!$this->inRange($dt)) continue;
            $events[]=['deal_id'=>$id,'date'=>$dt,'amount'=>(float)$t['amount']];
        }}
        return $events;
    }
    private function txPass(array $t,string $scope): bool { return $scope==='all_statuses' || strtolower(trim((string)($t['status']??'')))==='confirmed'; }

    private function aggByDeal(array $events, string $rule): array
    {
        $by=[]; foreach($events as $e){ $k=$e['deal_id'].'|'.$e['date']; $by[$k]=($by[$k]??['deal_id'=>$e['deal_id'],'date'=>$e['date'],'amount'=>0.0]); $by[$k]['amount']+=(float)$e['amount']; }
        $grp=[]; foreach($by as $r){ $grp[$r['deal_id']][]=$r; }
        $out=[]; foreach($grp as $id=>$rows){
            usort($rows, fn($a,$b)=>strcmp($a['date'],$b['date'])); $first=$rows[0]['date']; $sum=0.0;
            foreach($rows as $r){ if($rule==='first_payment_only' && $r['date']!==$first) continue; $sum+=(float)$r['amount']; }
            $out[$id]=['total'=>$sum,'first'=>$first];
        } return $out;
    }

    private function hasAnyPayment(string $dealId): bool
    {
        foreach(($this->txByDeal[$dealId]??[]) as $t){ if((float)($t['amount']??0)>0) return true; }
        foreach(($this->jsonByDeal[$dealId]??[]) as $p){ if((float)($p['amount']??0)>0) return true; }
        return false;
    }

    private function jsonPayments(array $deal): array
    {
        $raw=(string)($deal['payments']??''); if(trim($raw)==='') return []; $arr=json_decode($raw,true); if(!is_array($arr)) return [];
        $out=[]; foreach($arr as $i){ if(!is_array($i)) continue; $amt=(float)($i['amount']??0); if($amt<=0) continue;
            $d=$this->normDate((string)($i['date']??'')); if($d==='') $d=$this->normDate((string)($i['payment_date']??'')); if($d==='') $d=$this->jalali((string)($i['date_persian']??''));
            if($d==='') continue; $out[]=['date'=>$d,'amount'=>$amt];
        } return $out;
    }
    private function normDate(string $d): string { $d=trim($d); if($d===''||$d==='0000-00-00') return ''; if(preg_match('/^\d{4}[-\/]\d{2}[-\/]\d{2}$/',$d)) return str_replace('/','-',$d); return ''; }
    private function inRange(string $d): bool { return !($this->f['date_from'] && $d<$this->f['date_from']) && !($this->f['date_to'] && $d>$this->f['date_to']); }
    private function jalali(string $j): string {
        $j=strtr(trim(str_replace(['-','.'],'/',$j)),['۰'=>'0','۱'=>'1','۲'=>'2','۳'=>'3','۴'=>'4','۵'=>'5','۶'=>'6','۷'=>'7','۸'=>'8','۹'=>'9']);
        if(!preg_match('/^(\d{4})\/(\d{1,2})\/(\d{1,2})$/',$j,$m)) return ''; [$gy,$gm,$gd]=$this->j2g((int)$m[1],(int)$m[2],(int)$m[3]);
        return sprintf('%04d-%02d-%02d',$gy,$gm,$gd);
    }
    private function j2g(int $jy,int $jm,int $jd): array {
        $jy+=1595; $days=-355668 + 365*$jy + intdiv($jy,33)*8 + intdiv((($jy%33)+3),4) + $jd + ($jm<7?(($jm-1)*31):((($jm-7)*30)+186));
        $gy=400*intdiv($days,146097); $days%=146097; if($days>36524){$gy+=100*intdiv(--$days,36524); $days%=36524; if($days>=365)$days++; }
        $gy+=4*intdiv($days,1461); $days%=1461; if($days>365){$gy+=intdiv($days-1,365); $days=($days-1)%365;} $gd=$days+1;
        $sal=[0,31,(($gy%4===0&&$gy%100!==0)||($gy%400===0))?29:28,31,30,31,30,31,31,30,31,30,31]; $gm=1; while($gm<=12&&$gd>$sal[$gm]){$gd-=$sal[$gm];$gm++;} return [$gy,$gm,$gd];
    }

    private function print(array $obs, array $exp): void
    {
        $s=$obs['summary']; $dr=$obs['dr'];
        $outData = [
            'meta' => [
                'range' => [$this->f['date_from'], $this->f['date_to']],
                'owner' => ($this->f['owner_didar_ids_csv']?:$this->f['owner_didar_id']?:'ALL'),
                'sales_criteria' => [$this->f['kpi_sales_date_basis'], $this->f['kpi_sales_count_rule']],
                'payment_criteria' => [$this->f['kpi_payment_date_basis'], $this->f['kpi_payment_count_rule'], $this->f['kpi_payment_status_scope']],
                'legacy' => $this->f['legacy_deal_scope'],
                'surface' => $this->surface,
            ],
        ];
        $rows=[
            'sales_total'=>[
                'card_count'=>(int)($s['with_sales_deals_count']??0),
                'exp_count'=>(int)$exp['cards']['sales']['count'],
                'dr_count'=>$this->drCount($dr['sales']),
                'card_amount'=>(float)($s['total_sales_amount']??0),
                'exp_amount'=>(float)$exp['cards']['sales']['collected'],
                'dr_amount'=>$this->drSum($dr['sales'],'total_paid'),
            ],
            'settled'=>[
                'card_count'=>(int)($s['settled_count']??0),
                'exp_count'=>(int)$exp['cards']['settled']['count'],
                'dr_count'=>$this->drCount($dr['settled']),
                'card_contract'=>(float)($s['settled_amount']??0),
                'exp_contract'=>(float)$exp['cards']['settled']['contract'],
                'dr_contract'=>$this->drSum($dr['settled'],'payable_amount'),
                'card_collected'=>(float)($s['settled_collected_amount']??0),
                'exp_collected'=>(float)$exp['cards']['settled']['collected'],
                'dr_collected'=>$this->drSum($dr['settled'],'total_paid'),
            ],
            'outstanding'=>[
                'card_count'=>(int)($s['outstanding_count']??0),
                'exp_count'=>(int)$exp['cards']['outstanding']['count'],
                'dr_count'=>$this->drCount($dr['outstanding']),
                'card_contract'=>(float)($s['outstanding_contract_amount']??0),
                'exp_contract'=>(float)$exp['cards']['outstanding']['contract'],
                'dr_contract'=>$this->drSum($dr['outstanding'],'payable_amount'),
                'card_collected'=>(float)($s['outstanding_collected_amount']??0),
                'exp_collected'=>(float)$exp['cards']['outstanding']['collected'],
                'dr_collected'=>$this->drSum($dr['outstanding'],'total_paid'),
                'card_remaining'=>(float)($s['outstanding_amount']??0),
                'exp_remaining'=>(float)$exp['cards']['outstanding']['rem'],
            ],
            'without_payment'=>[
                'card_count'=>(int)($s['without_payment_count']??0),
                'exp_count'=>(int)$exp['cards']['without']['count'],
                'dr_count'=>$this->drCount($dr['without']),
                'card_contract'=>(float)($s['without_payment_amount']??0),
                'exp_contract'=>(float)$exp['cards']['without']['contract'],
                'dr_contract'=>$this->drSum($dr['without'],'payable_amount'),
            ],
            'open_pipeline'=>[
                'card_count'=>(int)($s['open_pipeline_count']??0),
                'exp_count'=>(int)$exp['cards']['open']['count'],
                'dr_count'=>$this->drCount($dr['open']),
                'card_amount'=>(float)($s['open_pipeline_amount']??0),
                'exp_amount'=>(float)$exp['cards']['open']['amount'],
                'dr_amount'=>$this->drSum($dr['open'],'payable_amount'),
            ],
            'won_sales'=>[
                'card_count'=>(int)($s['won_count']??0),
                'exp_count'=>(int)$exp['cards']['won']['count'],
                'dr_count'=>$this->drCount($dr['won']),
                'card_amount'=>(float)($s['won_amount']??0),
                'exp_amount'=>(float)$exp['cards']['won']['amount'],
                'dr_amount'=>$this->drSum($dr['won'],'payable_amount'),
            ],
            'lost_sales'=>[
                'card_count'=>(int)($s['lost_count']??0),
                'exp_count'=>(int)$exp['cards']['lost']['count'],
                'dr_count'=>$this->drCount($dr['lost']),
                'card_amount'=>(float)($s['lost_amount']??0),
                'exp_amount'=>(float)$exp['cards']['lost']['amount'],
                'dr_amount'=>$this->drSum($dr['lost'],'payable_amount'),
            ],
            'deal_conversion'=>[
                'card_total_deals'=>(int)($s['deal_conversion_total_deals']??0),
                'exp_total_deals'=>(int)$exp['cards']['deal_conversion']['total_deals'],
                'card_rate'=>(float)($s['deal_conversion_rate']??0),
                'exp_rate'=>(float)$exp['cards']['deal_conversion']['conversion_rate'],
                'card_non_rate'=>(float)($s['deal_non_conversion_rate']??0),
                'exp_non_rate'=>(float)$exp['cards']['deal_conversion']['non_conversion_rate'],
                'card_converted_estimate'=> (int) round(((float)($s['deal_conversion_total_deals']??0) * (float)($s['deal_conversion_rate']??0))/100),
                'exp_converted' => (int)$exp['cards']['deal_conversion']['converted_deals'],
            ],
            'lead_conversion_sanity'=>[
                'lead_total_new'=>(int)($s['lead_total_new']??0),
                'lead_converted'=>(int)($s['lead_converted']??0),
                'lead_rate'=>(float)($s['lead_conversion_rate']??0),
                'lead_non_rate'=>(float)($s['lead_non_conversion_rate']??0),
            ],
            'avg_conversion_sanity'=>[
                'avg_conversion_days'=>(float)($s['avg_conversion_days']??0),
            ],
            'target_sanity'=>[
                'target_has_target'=>(bool)($s['target_has_target']??false),
                'target_amount'=>(float)($s['target_amount']??0),
                'target_daily_amount'=>(float)($s['target_daily_amount']??0),
                'target_achievement_percent'=>(float)($s['target_achievement_percent']??0),
                'total_sales_amount'=>(float)($s['total_sales_amount']??0),
            ],
            'previous_period_sanity'=>[
                'previous_period_sales'=>(float)($s['previous_period_sales']??0),
                'sales_change_amount'=>(float)($s['sales_change_amount']??0),
                'sales_change_percent'=>(float)($s['sales_change_percent']??0),
                'total_sales_amount'=>(float)($s['total_sales_amount']??0),
            ],
            'cohort_partition_sanity'=>[
                'exp_total_deals'=>(int)$exp['cards']['deal_conversion']['total_deals'],
                'exp_open_count'=>(int)$exp['cards']['open']['count'],
                'exp_lost_count'=>(int)$exp['cards']['lost']['count'],
                'exp_won_count'=>(int)$exp['cards']['won']['count'],
                'exp_partition_sum'=>(int)$exp['cards']['open']['count'] + (int)$exp['cards']['lost']['count'] + (int)$exp['cards']['won']['count'],
            ],
        ];
        $cardMap = [
            'sales_total' => ['drKey' => 'sales', 'expSet' => 'salesSet'],
            'settled' => ['drKey' => 'settled', 'expSet' => 'settled'],
            'outstanding' => ['drKey' => 'outstanding', 'expSet' => 'out'],
            'without_payment' => ['drKey' => 'without', 'expSet' => 'without'],
            'open_pipeline' => ['drKey' => 'open', 'expSet' => 'open'],
            'won_sales' => ['drKey' => 'won', 'expSet' => 'won'],
            'lost_sales' => ['drKey' => 'lost', 'expSet' => 'lost'],
        ];
        if ($this->onlyCard !== '' && isset($rows[$this->onlyCard], $cardMap[$this->onlyCard])) {
            $rows = [$this->onlyCard => $rows[$this->onlyCard]];
            $cardMap = [$this->onlyCard => $cardMap[$this->onlyCard]];
        }

        if (!$this->json) {
            echo "=== KPI Financial Cards Reconciliation (Phase B) ===\n";
            echo "Range: {$this->f['date_from']} .. {$this->f['date_to']}\n";
            echo "Owner: ".($this->f['owner_didar_ids_csv']?:$this->f['owner_didar_id']?:'ALL')."\n";
            echo "Sales criteria: {$this->f['kpi_sales_date_basis']} / {$this->f['kpi_sales_count_rule']}\n";
            echo "Payment criteria: {$this->f['kpi_payment_date_basis']} / {$this->f['kpi_payment_count_rule']} / {$this->f['kpi_payment_status_scope']}\n";
            echo "Legacy: {$this->f['legacy_deal_scope']}\n\n";
        }
        foreach($rows as $k=>$r){
            $hasDr = isset($cardMap[$k]);
            $drKey = $hasDr ? $cardMap[$k]['drKey'] : null;
            $expSetKey = $hasDr ? $cardMap[$k]['expSet'] : null;
            $drIds = $hasDr ? $this->drIds($dr[$drKey]) : [];
            $expIds = $hasDr ? array_map('strval', array_keys($exp['sets'][$expSetKey] ?? [])) : [];
            sort($drIds); sort($expIds);
            $miss = $hasDr ? array_values(array_diff($expIds,$drIds)) : [];
            $extra = $hasDr ? array_values(array_diff($drIds,$expIds)) : [];
            $dup = $hasDr ? $this->drDuplicateStats($dr[$drKey]) : [];
            $drUniqueSumPayable = $hasDr ? $this->drUniqueDealSum($dr[$drKey], 'payable_amount') : 0.0;
            $drUniqueSumPaid = $hasDr ? $this->drUniqueDealSum($dr[$drKey], 'total_paid') : 0.0;
            $fail = $this->rowHasMismatch($r) || $miss || $extra;
            $trace = [];
            if ($hasDr && ($this->verbose || $fail)) {
                $trace = $this->buildTraceRows($k, $dr[$drKey], $exp['sets'][$expSetKey] ?? [], $miss, $extra);
            }
            $outData['cards'][$k] = [
                'metrics' => $r,
                'fail' => (bool)$fail,
                'missing_in_drilldown' => $miss,
                'extra_in_drilldown' => $extra,
                'drilldown' => $hasDr ? [
                    'raw_row_count' => (int)count(($dr[$drKey]['deals'] ?? [])),
                    'unique_deal_count' => (int)$this->drCount($dr[$drKey]),
                    'duplicate_deal_rows' => $dup,
                    'sum_payable_raw_rows' => (float)$this->drSum($dr[$drKey], 'payable_amount'),
                    'sum_payable_unique_deals' => (float)$drUniqueSumPayable,
                    'sum_paid_raw_rows' => (float)$this->drSum($dr[$drKey], 'total_paid'),
                    'sum_paid_unique_deals' => (float)$drUniqueSumPaid,
                ] : null,
                'heuristics' => $this->inferLikelyCauses($k, $r, $miss, $extra, $dup),
                'trace_rows' => $trace,
            ];
            if (!$this->json) {
                echo "--- {$k} ---\n";
                foreach($r as $rk=>$rv){ echo str_pad($rk,18).": ".(is_float($rv)?number_format($rv,2,'.',','):$rv)."\n"; }
                if ($hasDr) {
                    echo "dr_raw_rows         : ".count(($dr[$drKey]['deals'] ?? []))."\n";
                    echo "dr_unique_deals     : ".$this->drCount($dr[$drKey])."\n";
                }
                if ($hasDr && !empty($dup)) {
                    echo "dr_duplicates(".count($dup)."): ";
                    $dupPreview = [];
                    foreach (array_slice($dup, 0, min(10, $this->traceLimit), true) as $dealId => $cnt) {
                        $dupPreview[] = $dealId.'x'.$cnt;
                    }
                    echo implode(', ', $dupPreview)."\n";
                }
                if ($hasDr) {
                    echo "dr_sum_paid_unique   : ".number_format($drUniqueSumPaid,2,'.',',')."\n";
                    echo "dr_sum_payable_unique: ".number_format($drUniqueSumPayable,2,'.',',')."\n";
                }
                if($hasDr && $miss) echo "missing_in_drilldown(".count($miss)."): ".implode(',',array_slice($miss,0,$this->traceLimit))."\n";
                if($hasDr && $extra) echo "extra_in_drilldown(".count($extra)."): ".implode(',',array_slice($extra,0,$this->traceLimit))."\n";
                $causes = $this->inferLikelyCauses($k, $r, $miss, $extra, $dup);
                if ($causes) {
                    echo "likely_causes:\n";
                    foreach ($causes as $c) {
                        echo "  - {$c}\n";
                    }
                }
                if ($hasDr && ($this->verbose || $fail) && $trace) {
                    echo "trace_rows(".count($trace)."):\n";
                    foreach ($trace as $tr) {
                        echo "  deal {$tr['deal_id']} | in_exp=".($tr['in_expected']?'1':'0')." | in_dr=".($tr['in_drilldown']?'1':'0')
                            ." | payable=".number_format((float)$tr['payable_amount'],2,'.',',')
                            ." | exp_paid=".number_format((float)$tr['expected_paid'],2,'.',',')
                            ." | dr_paid=".number_format((float)$tr['drilldown_paid'],2,'.',',')
                            ." | title=".($tr['title']??'')."\n";
                    }
                }
                echo "\n";
            }
        }
        $chartChecks = $this->buildChartChecks($obs);
        $outData['charts'] = $chartChecks;
        $advancedTableChecks = $this->buildAdvancedTableChecks($obs, $exp);
        $outData['advanced_tables'] = $advancedTableChecks;
        $outData['source_divergence'] = $this->buildSourceDivergenceDiagnostics();
        if (isset($obs['classic_kpi'])) {
            $outData['classic_kpi'] = $this->buildClassicKpiChecks($obs['classic_kpi']);
        }
        $settledContract=(float)($s['settled_amount']??0); $settledCollected=(float)($s['settled_collected_amount']??0);
        $invPass = $settledCollected<=$settledContract;
        $dealRate = (float)($s['deal_conversion_rate'] ?? 0);
        $dealNonRate = (float)($s['deal_non_conversion_rate'] ?? 0);
        $leadRate = (float)($s['lead_conversion_rate'] ?? 0);
        $leadNonRate = (float)($s['lead_non_conversion_rate'] ?? 0);
        $rateDealSumPass = abs(($dealRate + $dealNonRate) - 100.0) <= 0.2 || ((int)($s['deal_conversion_total_deals'] ?? 0) === 0 && $dealRate == 0.0 && $dealNonRate == 0.0);
        $rateLeadSumPass = abs(($leadRate + $leadNonRate) - 100.0) <= 0.2 || ((int)($s['lead_total_new'] ?? 0) === 0 && $leadRate == 0.0 && $leadNonRate == 0.0);
        $leadConvertedPass = (int)($s['lead_converted'] ?? 0) <= (int)($s['lead_total_new'] ?? 0);
        $withoutLeOpenPass = (int)($s['without_payment_count'] ?? 0) <= (int)($s['open_pipeline_count'] ?? 0);
        $avgConversionNonNegativePass = (float)($s['avg_conversion_days'] ?? 0) >= 0;
        $prevPeriodArithmeticPass = abs((((float)($s['previous_period_sales'] ?? 0)) + ((float)($s['sales_change_amount'] ?? 0))) - ((float)($s['total_sales_amount'] ?? 0))) <= 1.0;
        $outData['invariants'] = [
            'settled_collected_lte_settled_contract' => [
                'pass' => $invPass,
                'settled_contract' => $settledContract,
                'settled_collected' => $settledCollected,
            ],
            'deal_rates_sum_to_100' => ['pass' => $rateDealSumPass, 'sum' => ($dealRate + $dealNonRate)],
            'lead_rates_sum_to_100' => ['pass' => $rateLeadSumPass, 'sum' => ($leadRate + $leadNonRate)],
            'lead_converted_lte_total_new' => ['pass' => $leadConvertedPass, 'lead_converted' => (int)($s['lead_converted'] ?? 0), 'lead_total_new' => (int)($s['lead_total_new'] ?? 0)],
            'without_payment_lte_open_pipeline' => ['pass' => $withoutLeOpenPass, 'without_payment_count' => (int)($s['without_payment_count'] ?? 0), 'open_pipeline_count' => (int)($s['open_pipeline_count'] ?? 0)],
            'avg_conversion_non_negative' => ['pass' => $avgConversionNonNegativePass, 'avg_conversion_days' => (float)($s['avg_conversion_days'] ?? 0)],
            'previous_period_arithmetic' => [
                'pass' => $prevPeriodArithmeticPass,
                'previous_period_sales' => (float)($s['previous_period_sales'] ?? 0),
                'sales_change_amount' => (float)($s['sales_change_amount'] ?? 0),
                'total_sales_amount' => (float)($s['total_sales_amount'] ?? 0),
            ],
            'oracle_deal_cohort_partition_sum' => [
                'pass' => (((int)$exp['cards']['open']['count'] + (int)$exp['cards']['lost']['count'] + (int)$exp['cards']['won']['count']) === (int)$exp['cards']['deal_conversion']['total_deals']),
                'open' => (int)$exp['cards']['open']['count'],
                'lost' => (int)$exp['cards']['lost']['count'],
                'won' => (int)$exp['cards']['won']['count'],
                'total' => (int)$exp['cards']['deal_conversion']['total_deals'],
            ],
        ];
        $outData['summary_report'] = $this->buildSummaryReport($outData);
        if ($this->json) {
            echo json_encode($outData, JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT).PHP_EOL;
            return;
        }
        echo "\n=== Chart Drill-down Reconciliation ===\n";
        foreach (($chartChecks['summary'] ?? []) as $chartKey => $info) {
            echo "{$chartKey}: checks={$info['checks']} fail={$info['fails']}\n";
        }
        echo "\n=== Advanced Tables Consistency ===\n";
        foreach (($advancedTableChecks['summary'] ?? []) as $tableKey => $info) {
            echo "{$tableKey}: checks={$info['checks']} fail={$info['fails']}\n";
        }
        if ($this->verbose) {
            foreach (($advancedTableChecks['details'] ?? []) as $tableKey => $items) {
                echo "--- table {$tableKey} details ---\n";
                foreach (array_slice($items, 0, $this->traceLimit) as $item) {
                    echo "  {$item['label']} | display={$item['display_value']} | expected={$item['expected_value']} | pass=".($item['pass']?'1':'0');
                    if (!empty($item['note'])) echo " | {$item['note']}";
                    echo "\n";
                }
            }
        }
        echo "\n=== Source Divergence Diagnostics (JSON vs Transactions) ===\n";
        $sd = $outData['source_divergence'];
        echo "checked_deals: {$sd['checked_deals']} | any_mismatch: {$sd['any_mismatch_count']} | date_mismatch: {$sd['date_mismatch_count']} | amount_mismatch: {$sd['amount_mismatch_count']}\n";
        if ($this->verbose && !empty($sd['top_mismatches'])) {
            foreach ($sd['top_mismatches'] as $row) {
                echo "  deal {$row['deal_id']} | first_ui={$row['first_ui_date']} | first_tx={$row['first_tx_db_date']} | json_total={$row['json_total']} | tx_total={$row['tx_total']} | title={$row['title']}\n";
            }
        }
        if (isset($outData['classic_kpi'])) {
            echo "\n=== Classic KPI Service Snapshot Checks ===\n";
            if (!($outData['classic_kpi']['available'] ?? false)) {
                echo "classic_kpi unavailable";
                if (!empty($outData['classic_kpi']['error'])) echo ": {$outData['classic_kpi']['error']}";
                echo "\n";
            } else {
                foreach (($outData['classic_kpi']['checks'] ?? []) as $k => $v) {
                    echo "{$k}: ".(($v['pass'] ?? false) ? 'PASS' : 'FAIL')."\n";
                }
            }
        }
        echo "\n=== Summary ===\n";
        foreach (($outData['summary_report'] ?? []) as $k => $v) {
            echo "{$k}: {$v}\n";
        }
        if ($this->verbose) {
            foreach (($chartChecks['details'] ?? []) as $chartKey => $items) {
                echo "--- chart {$chartKey} details ---\n";
                foreach (array_slice($items, 0, $this->traceLimit) as $item) {
                    echo "  {$item['label']} | display={$item['display_value']} | drill={$item['drill_value']} | pass=".($item['pass']?'1':'0');
                    if (!empty($item['note'])) echo " | {$item['note']}";
                    echo "\n";
                }
            }
        }
        echo "Invariant settled_collected <= settled_contract (observed): ".($invPass?'PASS':'FAIL')."\n";
        if(!$invPass){
            echo "  settled_contract=".number_format($settledContract,2,'.',',')." | settled_collected=".number_format($settledCollected,2,'.',',')."\n";
        }
        echo "Invariant deal rates sum to 100: ".($rateDealSumPass?'PASS':'FAIL')." (".number_format($dealRate + $dealNonRate,2,'.',',').")\n";
        echo "Invariant lead rates sum to 100: ".($rateLeadSumPass?'PASS':'FAIL')." (".number_format($leadRate + $leadNonRate,2,'.',',').")\n";
        echo "Invariant lead_converted <= lead_total_new: ".($leadConvertedPass?'PASS':'FAIL')."\n";
        echo "Invariant without_payment <= open_pipeline: ".($withoutLeOpenPass?'PASS':'FAIL')."\n";
        echo "Invariant avg_conversion_days >= 0: ".($avgConversionNonNegativePass?'PASS':'FAIL')."\n";
        echo "Invariant previous_period arithmetic (prev + delta = current): ".($prevPeriodArithmeticPass?'PASS':'FAIL')."\n";
        $oraclePartitionPass = (((int)$exp['cards']['open']['count'] + (int)$exp['cards']['lost']['count'] + (int)$exp['cards']['won']['count']) === (int)$exp['cards']['deal_conversion']['total_deals']);
        echo "Invariant oracle cohort partition (open+lost+won == total): ".($oraclePartitionPass?'PASS':'FAIL')."\n";
    }

    private function buildSourceDivergenceDiagnostics(): array
    {
        $rows = [];
        foreach ($this->dealById as $id => $deal) {
            if (!$this->dealPassNoDate($deal)) {
                continue;
            }
            $jsonTotal = $this->sumJsonByDeal((string)$id, false);
            $txTotal = $this->sumTxByDeal((string)$id, 'all_statuses', false);
            $firstUi = $this->firstJsonDate((string)$id);
            $firstTx = $this->firstTxDate((string)$id, 'db');
            $hasMismatch = false;
            $dateMismatch = false;
            $amountMismatch = false;
            if ($firstUi !== '' && $firstTx !== '' && $firstUi !== $firstTx) {
                $dateMismatch = true;
                $hasMismatch = true;
            }
            if (!$this->floatEq((float)$jsonTotal, (float)$txTotal, 1.0)) {
                $amountMismatch = true;
                $hasMismatch = true;
            }
            if (!$hasMismatch) {
                continue;
            }
            $rows[] = [
                'deal_id' => (string)$id,
                'title' => (string)($deal['title'] ?? ''),
                'first_ui_date' => $firstUi,
                'first_tx_db_date' => $firstTx,
                'json_total' => (float)$jsonTotal,
                'tx_total' => (float)$txTotal,
                'date_mismatch' => $dateMismatch,
                'amount_mismatch' => $amountMismatch,
            ];
        }
        usort($rows, static function (array $a, array $b): int {
            $scoreA = (($a['date_mismatch'] ? 1 : 0) + ($a['amount_mismatch'] ? 1 : 0));
            $scoreB = (($b['date_mismatch'] ? 1 : 0) + ($b['amount_mismatch'] ? 1 : 0));
            if ($scoreA !== $scoreB) return $scoreB <=> $scoreA;
            return strcmp((string)$a['deal_id'], (string)$b['deal_id']);
        });
        $dateMismatchCount = 0;
        $amountMismatchCount = 0;
        foreach ($rows as $r) {
            if (!empty($r['date_mismatch'])) $dateMismatchCount++;
            if (!empty($r['amount_mismatch'])) $amountMismatchCount++;
        }
        return [
            'checked_deals' => count($this->dealById),
            'any_mismatch_count' => count($rows),
            'date_mismatch_count' => $dateMismatchCount,
            'amount_mismatch_count' => $amountMismatchCount,
            'top_mismatches' => array_slice($rows, 0, $this->traceLimit),
        ];
    }

    private function buildClassicKpiChecks(array $classic): array
    {
        if (empty($classic['available'])) {
            return $classic;
        }
        $components = (array)($classic['snapshot']['components'] ?? []);
        $checks = [];
        $map = [
            'sales_component_matches_repo' => ['key' => 'sales', 'method' => 'getSales'],
            'settled_component_matches_repo' => ['key' => 'settled', 'method' => 'getSettledDeals'],
            'outstanding_component_matches_repo' => ['key' => 'outstanding', 'method' => 'getOutstandingBalance'],
            'lost_component_matches_repo' => ['key' => 'lost_sales', 'method' => 'getLostSales'],
            'open_component_matches_repo' => ['key' => 'open_pipeline', 'method' => 'getOpenPipeline'],
            'without_component_matches_repo' => ['key' => 'deals_without_payments', 'method' => 'getDealsWithoutPayments'],
            'deal_conversion_component_matches_repo' => ['key' => 'deal_conversion', 'method' => 'getDealConversionRate'],
            'lead_conversion_component_matches_repo' => ['key' => 'lead_conversion', 'method' => 'getLeadConversionRate'],
            'time_to_conversion_component_matches_repo' => ['key' => 'time_to_conversion', 'method' => 'getTimeToConversion'],
        ];
        foreach ($map as $label => $cfg) {
            $actual = $components[$cfg['key']] ?? null;
            if ($actual === null || !method_exists($this->repo, $cfg['method'])) {
                $checks[$label] = ['pass' => false, 'reason' => 'missing_component_or_method'];
                continue;
            }
            $repoValue = $this->repo->{$cfg['method']}($this->f);
            $checks[$label] = [
                'pass' => $this->jsonComparable($actual) === $this->jsonComparable($repoValue),
                'component_key' => $cfg['key'],
            ];
        }
        $classic['checks'] = $checks;
        return $classic;
    }

    private function jsonComparable($value): string
    {
        return json_encode($value, JSON_UNESCAPED_UNICODE|JSON_PRESERVE_ZERO_FRACTION|JSON_PARTIAL_OUTPUT_ON_ERROR);
    }

    private function buildSummaryReport(array $outData): array
    {
        $cardFails = 0;
        $cardChecks = 0;
        foreach (($outData['cards'] ?? []) as $card) {
            $cardChecks++;
            if (!empty($card['fail'])) $cardFails++;
        }
        $chartChecks = 0;
        $chartFails = 0;
        foreach (($outData['charts']['summary'] ?? []) as $chart) {
            $chartChecks += (int)($chart['checks'] ?? 0);
            $chartFails += (int)($chart['fails'] ?? 0);
        }
        $tableChecks = 0;
        $tableFails = 0;
        foreach (($outData['advanced_tables']['summary'] ?? []) as $tbl) {
            $tableChecks += (int)($tbl['checks'] ?? 0);
            $tableFails += (int)($tbl['fails'] ?? 0);
        }
        $invFails = 0;
        $invChecks = 0;
        foreach (($outData['invariants'] ?? []) as $inv) {
            $invChecks++;
            if (isset($inv['pass']) && !$inv['pass']) $invFails++;
        }
        $classicFails = 0;
        $classicChecks = 0;
        foreach (($outData['classic_kpi']['checks'] ?? []) as $check) {
            $classicChecks++;
            if (empty($check['pass'])) $classicFails++;
        }
        return [
            'cards_checks' => $cardChecks,
            'cards_fails' => $cardFails,
            'charts_checks' => $chartChecks,
            'charts_fails' => $chartFails,
            'advanced_table_checks' => $tableChecks,
            'advanced_table_fails' => $tableFails,
            'invariants_checks' => $invChecks,
            'invariants_fails' => $invFails,
            'classic_kpi_checks' => $classicChecks,
            'classic_kpi_fails' => $classicFails,
            'overall_status' => ($cardFails === 0 && $chartFails === 0 && $tableFails === 0 && $classicFails === 0) ? 'PASS' : 'FAIL',
        ];
    }

    private function buildAdvancedTableChecks(array $obs, array $exp): array
    {
        $dashboard = (array)($obs['dashboard'] ?? []);
        $summary = (array)($obs['summary'] ?? []);
        $charts = (array)($obs['charts'] ?? []);
        $adv = (array)(($dashboard['raw']['advanced_tables'] ?? []) ?: []);

        $details = [
            'deal_status' => [],
            'payment_mix' => [],
            'payment_status' => [],
            'conversion_status' => [],
            'conversion_time' => [],
            'failure_reasons' => [],
            'product_status' => [],
            'summary_by_user' => [],
        ];

        $sum = static function(array $rows, string $key): float {
            $s = 0.0;
            foreach ($rows as $r) { $s += (float)($r[$key] ?? 0); }
            return $s;
        };

        $rows = (array)($adv['deal_status'] ?? []);
        if ($rows) {
            $checks = [
                ['deal_total_sum', (int)$sum($rows, 'deal_total'), (int)($summary['deal_conversion_total_deals'] ?? 0), 'جمع deal_total جدول باید با مخرج نرخ تبدیل برابر باشد'],
                ['deal_pending_total_sum', (int)$sum($rows, 'deal_pending_total'), (int)($summary['open_pipeline_count'] ?? 0), 'جمع deal_pending_total جدول باید با فروش جاری برابر باشد'],
                ['deal_lost_total_sum', (int)$sum($rows, 'deal_lost_total'), (int)($summary['lost_count'] ?? 0), 'جمع deal_lost_total جدول باید با فروش ناموفق برابر باشد'],
                ['deal_with_sales_total_sum', (int)$sum($rows, 'deal_with_sales_total'), (int)($summary['with_sales_deals_count'] ?? 0), 'جمع deal_with_sales_total جدول باید با فروش کل (count) برابر باشد'],
                ['deal_without_sales_total_sum', (int)$sum($rows, 'deal_without_sales_total'), (int)($summary['without_payment_count'] ?? 0), 'جمع deal_without_sales_total جدول باید با بدون‌پرداخت برابر باشد'],
            ];
            foreach ($checks as [$label,$display,$expected,$note]) {
                $details['deal_status'][] = [
                    'label' => $label,
                    'display_value' => $display,
                    'expected_value' => $expected,
                    'pass' => ($display === $expected),
                    'note' => $note,
                ];
            }
            $partition = (int)$sum($rows,'deal_pending_total') + (int)$sum($rows,'deal_lost_total');
            $partitionWithWon = $partition + (int)$sum($rows,'deal_won_total');
            $total = (int)$sum($rows,'deal_total');
            $details['deal_status'][] = [
                'label' => 'partition_pending_plus_lost_plus_won_equals_total',
                'display_value' => $partitionWithWon,
                'expected_value' => $total,
                'pass' => ($partitionWithWon === $total),
                'note' => 'در منطق cohort نرخ تبدیل، pending + lost + won باید با total برابر شود',
            ];
            $details['deal_status'][] = [
                'label' => 'deal_won_total_sum',
                'display_value' => (int)$sum($rows, 'deal_won_total'),
                'expected_value' => (int)($summary['won_count'] ?? 0),
                'pass' => ((int)$sum($rows, 'deal_won_total') === (int)($summary['won_count'] ?? 0)),
                'note' => 'جمع deal_won_total جدول باید با کارت Won برابر باشد',
            ];
        }

        $rows = (array)($adv['payment_mix'] ?? []);
        if ($rows) {
            $with = (int)$sum($rows,'new_leads_with_payment') + (int)$sum($rows,'old_leads_with_payment');
            $without = (int)$sum($rows,'new_leads_without_payment') + (int)$sum($rows,'old_leads_without_payment');
            $failed = (int)$sum($rows,'new_leads_failed') + (int)$sum($rows,'old_leads_failed');
            $details['payment_mix'][] = ['label'=>'with_payment_total','display_value'=>$with,'expected_value'=>(int)($summary['with_sales_deals_count'] ?? 0),'pass'=>($with === (int)($summary['with_sales_deals_count'] ?? 0)),'note'=>'جمع لیدهای با پرداخت'];
            $details['payment_mix'][] = ['label'=>'without_payment_total','display_value'=>$without,'expected_value'=>(int)($summary['without_payment_count'] ?? 0),'pass'=>($without === (int)($summary['without_payment_count'] ?? 0)),'note'=>'جمع لیدهای بدون پرداخت'];
            $details['payment_mix'][] = ['label'=>'failed_total','display_value'=>$failed,'expected_value'=>(int)($summary['lost_count'] ?? 0),'pass'=>($failed === (int)($summary['lost_count'] ?? 0)),'note'=>'جمع لیدهای ناموفق'];
        }

        $rows = (array)($adv['payment_status'] ?? []);
        if ($rows) {
            $details['payment_status'][] = ['label'=>'outstanding_count','display_value'=>(int)$sum($rows,'outstanding_deals_count'),'expected_value'=>(int)($summary['outstanding_count'] ?? 0),'pass'=>((int)$sum($rows,'outstanding_deals_count') === (int)($summary['outstanding_count'] ?? 0)),'note'=>'تعداد بدهکارها'];
            $details['payment_status'][] = ['label'=>'settled_count','display_value'=>(int)$sum($rows,'settled_deals_count'),'expected_value'=>(int)($summary['settled_count'] ?? 0),'pass'=>((int)$sum($rows,'settled_deals_count') === (int)($summary['settled_count'] ?? 0)),'note'=>'تعداد تسویه‌شده‌ها'];
            $details['payment_status'][] = ['label'=>'outstanding_amount_sum','display_value'=>(float)$sum($rows,'outstanding_amount_sum'),'expected_value'=>(float)($summary['outstanding_amount'] ?? 0),'pass'=>$this->floatEq((float)$sum($rows,'outstanding_amount_sum'), (float)($summary['outstanding_amount'] ?? 0)),'note'=>'جمع مانده حساب'];
            $details['payment_status'][] = ['label'=>'payments_sum','display_value'=>(float)$sum($rows,'payments_sum'),'expected_value'=>(float)($summary['total_sales_amount'] ?? 0),'pass'=>$this->floatEq((float)$sum($rows,'payments_sum'), (float)($summary['total_sales_amount'] ?? 0)),'note'=>'جمع وصول جدول payment_status با مبلغ فروش کل'];
        }

        $rows = (array)($adv['conversion_status'] ?? []);
        if ($rows) {
            $details['conversion_status'][] = ['label'=>'total_deals_count','display_value'=>(int)$sum($rows,'total_deals_count'),'expected_value'=>(int)($summary['deal_conversion_total_deals'] ?? 0),'pass'=>((int)$sum($rows,'total_deals_count') === (int)($summary['deal_conversion_total_deals'] ?? 0)),'note'=>'مخرج نرخ تبدیل'];
            $details['conversion_status'][] = ['label'=>'sales_total_count','display_value'=>(int)$sum($rows,'sales_total_count'),'expected_value'=>(int)($summary['with_sales_deals_count'] ?? 0),'pass'=>((int)$sum($rows,'sales_total_count') === (int)($summary['with_sales_deals_count'] ?? 0)),'note'=>'تعداد فروش'];
            $weightedRate = 0.0; $weightedDen = 0.0;
            foreach ($rows as $r) {
                $weightedRate += ((float)($r['conversion_rate_total'] ?? 0)) * (float)($r['total_deals_count'] ?? 0);
                $weightedDen += (float)($r['total_deals_count'] ?? 0);
            }
            $weightedRate = $weightedDen > 0 ? round($weightedRate / $weightedDen, 2) : 0.0;
            $details['conversion_status'][] = ['label'=>'weighted_conversion_rate_total','display_value'=>$weightedRate,'expected_value'=>(float)($summary['deal_conversion_rate'] ?? 0),'pass'=>$this->floatEq($weightedRate,(float)($summary['deal_conversion_rate'] ?? 0),0.2),'note'=>'میانگین وزنی نرخ تبدیل'];
        }

        $rows = (array)($adv['conversion_time'] ?? []);
        if ($rows) {
            $sumDays = (float)$sum($rows,'sum_conversion_days_total');
            $cntDays = (float)$sum($rows,'count_conversion_days_total');
            $avg = $cntDays > 0 ? round($sumDays / $cntDays, 2) : 0.0;
            $details['conversion_time'][] = ['label'=>'weighted_avg_conversion_days_total','display_value'=>$avg,'expected_value'=>(float)($summary['avg_conversion_days'] ?? 0),'pass'=>$this->floatEq($avg,(float)($summary['avg_conversion_days'] ?? 0),0.2),'note'=>'میانگین زمان تبدیل'];
        }

        $rows = (array)($adv['failure_reasons'] ?? []);
        if ($rows) {
            $failedWithoutCurrent = (int)$sum($rows,'failed_without_current_count');
            $failedTotalByReason = (int)$sum($rows,'failed_new_count') + (int)$sum($rows,'failed_old_count');
            $details['failure_reasons'][] = ['label'=>'failed_total_by_reason_sum','display_value'=>$failedTotalByReason,'expected_value'=>(int)($summary['lost_count'] ?? 0),'pass'=>($failedTotalByReason === (int)($summary['lost_count'] ?? 0)),'note'=>'جمع دلایل شکست (new+old) باید با فروش ناموفق برابر باشد'];
            $details['failure_reasons'][] = ['label'=>'failed_without_current_count_lte_lost','display_value'=>$failedWithoutCurrent,'expected_value'=>(int)($summary['lost_count'] ?? 0),'pass'=>($failedWithoutCurrent <= (int)($summary['lost_count'] ?? 0)),'note'=>'failed_without_current_count زیرمجموعه فروش ناموفق است'];
        }

        $rows = (array)($adv['product_status'] ?? []);
        if ($rows) {
            $tableTotal = (int)$sum($rows,'sales_total_count');
            $chartTotal = 0;
            foreach ((array)($charts['product_sales_share'] ?? []) as $it) {
                $chartTotal += (int)($it['deal_count'] ?? 0);
            }
            $details['product_status'][] = ['label'=>'product_sales_total_sum','display_value'=>$tableTotal,'expected_value'=>$chartTotal,'pass'=>($tableTotal === $chartTotal),'note'=>'جمع فروش محصولات جدول و نمودار باید برابر باشد'];
        }

        $rows = (array)($adv['summary_by_user'] ?? []);
        if ($rows) {
            $details['summary_by_user'][] = ['label'=>'total_deals_count_sum','display_value'=>(int)$sum($rows,'total_deals_count'),'expected_value'=>(int)($summary['deal_conversion_total_deals'] ?? 0),'pass'=>((int)$sum($rows,'total_deals_count') === (int)($summary['deal_conversion_total_deals'] ?? 0)),'note'=>'جمع کل معاملات خلاصه کارشناسان'];
            $details['summary_by_user'][] = ['label'=>'sales_total_count_sum','display_value'=>(int)$sum($rows,'sales_total_count'),'expected_value'=>(int)($summary['with_sales_deals_count'] ?? 0),'pass'=>((int)$sum($rows,'sales_total_count') === (int)($summary['with_sales_deals_count'] ?? 0)),'note'=>'جمع فروش خلاصه کارشناسان'];
            $details['summary_by_user'][] = ['label'=>'collected_amount_sum','display_value'=>(float)$sum($rows,'total_collected_amount'),'expected_value'=>(float)($summary['total_sales_amount'] ?? 0),'pass'=>$this->floatEq((float)$sum($rows,'total_collected_amount'), (float)($summary['total_sales_amount'] ?? 0)),'note'=>'جمع وصول خلاصه کارشناسان'];
        }

        $summaryOut = [];
        foreach ($details as $tableKey => $items) {
            $fails = 0;
            foreach ($items as $it) {
                if (empty($it['pass'])) $fails++;
            }
            $summaryOut[$tableKey] = ['checks' => count($items), 'fails' => $fails];
        }
        return ['summary' => $summaryOut, 'details' => $details];
    }

    private function buildChartChecks(array $obs): array
    {
        $charts = $obs['charts'] ?? [];
        $details = [
            'daily_sales' => [],
            'daily_activities' => [],
            'deal_status_breakdown' => [],
            'product_sales_share' => [],
            'conversion_funnel' => [],
        ];

        // 1) Daily sales chart click => performance_charts / daily_sales_point by date
        foreach ((array)($charts['daily_sales'] ?? []) as $point) {
            $date = (string)($point['date'] ?? $point['payment_date'] ?? '');
            if ($date === '') continue;
            $dr = $this->repo->getKpiDrilldown($this->f, 'performance_charts', 'daily_sales_point', [
                'date' => $date, 'owner_didar_id' => '', 'owner_name' => '', 'product_name' => '', 'failure_reason' => '', 'activity_type' => ''
            ]);
            $displayCount = (int)($point['deal_count'] ?? 0);
            $drCount = (int)(($dr['meta']['total_deals'] ?? null) ?? $this->drCount($dr));
            $displayAmt = (float)($point['collected_amount'] ?? 0);
            $drPayAmt = 0.0;
            foreach (($dr['payments'] ?? []) as $p) { $drPayAmt += (float)($p['amount'] ?? 0); }
            $drDealAmt = $this->drSum($dr, 'total_paid');
            $amtPass = $this->floatEq($displayAmt, $drPayAmt) || $this->floatEq($displayAmt, $drDealAmt);
            $details['daily_sales'][] = [
                'label' => "date={$date}",
                'display_value' => "deals={$displayCount}, amount={$displayAmt}",
                'drill_value' => "deals={$drCount}, payments_amount={$drPayAmt}, deals_total_paid={$drDealAmt}",
                'pass' => ($displayCount === $drCount) && $amtPass,
                'note' => $amtPass ? '' : 'مبلغ drilldown با نقطه نمودار نمی‌خواند'
            ];
        }

        // 2) Daily activities chart click path (raw by-type rows)
        foreach ((array)($charts['daily_activities_by_type'] ?? []) as $row) {
            $date = (string)($row['date'] ?? '');
            if ($date === '') continue;
            $type = (string)($row['activity_type'] ?? '');
            $dr = $this->repo->getKpiDrilldown($this->f, 'activity_type_details', 'activity_count_total', [
                'date' => $date, 'activity_type' => $type, 'owner_didar_id' => '', 'owner_name' => '', 'product_name' => '', 'failure_reason' => ''
            ]);
            $displayCount = (int)($row['activities_count'] ?? 0);
            $drCount = (int)(($dr['meta']['total_activities'] ?? null) ?? count($dr['activities'] ?? []));
            $details['daily_activities'][] = [
                'label' => "date={$date}, type=".($type === '' ? '(empty)' : $type),
                'display_value' => $displayCount,
                'drill_value' => $drCount,
                'pass' => ($displayCount === $drCount),
                'note' => ''
            ];
        }

        // 3) Deal status donut
        $dealStatusMetricMap = [
            'جاری' => ['performance_cards', 'open_pipeline_count'],
            'دارای فروش' => ['performance_cards', 'sales_total'],
            'بدون فروش' => ['performance_cards', 'without_payment_count'],
            'ناموفق' => ['performance_cards', 'lost_count']
        ];
        foreach ((array)($charts['deal_status_breakdown'] ?? []) as $item) {
            $status = (string)($item['status'] ?? '');
            [$tableId, $metric] = $dealStatusMetricMap[$status] ?? ['performance_cards', 'open_pipeline_count'];
            $dr = $this->repo->getKpiDrilldown($this->f, $tableId, $metric, [
                'date' => '', 'owner_didar_id' => '', 'owner_name' => '', 'product_name' => '', 'failure_reason' => '', 'activity_type' => ''
            ]);
            $displayCount = (int)($item['count'] ?? 0);
            $drCount = (int)(($dr['meta']['total_deals'] ?? null) ?? $this->drCount($dr));
            $details['deal_status_breakdown'][] = [
                'label' => "status={$status}",
                'display_value' => $displayCount,
                'drill_value' => $drCount,
                'pass' => ($displayCount === $drCount),
                'note' => ''
            ];
        }

        // 4) Product share pie
        foreach ((array)($charts['product_sales_share'] ?? []) as $item) {
            $product = (string)($item['product_name'] ?? '');
            $dr = $this->repo->getKpiDrilldown($this->f, 'performance_charts', 'product_sales_share_item', [
                'date' => '', 'owner_didar_id' => '', 'owner_name' => '', 'product_name' => $product, 'failure_reason' => '', 'activity_type' => ''
            ]);
            $displayCount = (int)($item['deal_count'] ?? 0);
            $drCount = (int)(($dr['meta']['total_deals'] ?? null) ?? $this->drCount($dr));
            $details['product_sales_share'][] = [
                'label' => "product={$product}",
                'display_value' => $displayCount,
                'drill_value' => $drCount,
                'pass' => ($displayCount === $drCount),
                'note' => ''
            ];
        }

        // 5) Conversion funnel click mapping (same as UI)
        foreach ((array)($charts['conversion_funnel'] ?? []) as $item) {
            $stage = (string)($item['stage'] ?? '');
            $displayCount = (int)($item['count'] ?? 0);
            if ($stage === '') continue;
            $dr = $this->repo->getKpiDrilldown($this->f, 'performance_charts', 'conversion_funnel_stage', [
                'date' => '', 'owner_didar_id' => '', 'owner_name' => '', 'product_name' => '', 'failure_reason' => '', 'activity_type' => '', 'stage' => $stage
            ]);
            $drCount = (int)($stage === 'لید جدید'
                ? (($dr['meta']['total_leads'] ?? null) ?? count($dr['leads'] ?? []))
                : (($dr['meta']['total_deals'] ?? null) ?? $this->drCount($dr)));
            $details['conversion_funnel'][] = [
                'label' => "stage={$stage}",
                'display_value' => $displayCount,
                'drill_value' => $drCount,
                'pass' => ($displayCount === $drCount),
                'note' => ''
            ];
        }

        $summary = [];
        foreach ($details as $chartKey => $items) {
            $fails = 0;
            foreach ($items as $it) { if (empty($it['pass'])) $fails++; }
            $summary[$chartKey] = ['checks' => count($items), 'fails' => $fails];
        }

        return ['summary' => $summary, 'details' => $details];
    }

    private function floatEq(float $a, float $b, float $eps = 1.0): bool
    {
        return abs($a - $b) <= $eps;
    }

    private function rowHasMismatch(array $r): bool
    {
        foreach ($r as $key => $value) {
            if (strpos($key, 'card_') !== 0) {
                continue;
            }
            $base = substr($key, 5);
            $expKey = 'exp_' . $base;
            $drKey = 'dr_' . $base;
            if (array_key_exists($expKey, $r) && (float)$value !== (float)$r[$expKey]) {
                return true;
            }
            if (array_key_exists($drKey, $r) && (float)$value !== (float)$r[$drKey]) {
                return true;
            }
        }
        return false;
    }

    private function buildTraceRows(string $cardKey, array $drData, array $expSet, array $miss, array $extra): array
    {
        $drDeals = [];
        foreach (($drData['deals'] ?? []) as $r) {
            $id = trim((string)($r['deal_id'] ?? ''));
            if ($id === '') continue;
            if (!isset($drDeals[$id])) {
                $drDeals[$id] = ['total_paid' => 0.0, 'payable_amount' => (float)($r['payable_amount'] ?? 0), 'row_count' => 0];
            }
            $drDeals[$id]['row_count']++;
            $drDeals[$id]['total_paid'] += (float)($r['total_paid'] ?? 0);
        }
        $ids = array_values(array_unique(array_merge(array_keys($expSet), array_keys($drDeals), $miss, $extra)));
        sort($ids);
        $rows = [];
        foreach ($ids as $id) {
            $deal = $this->dealById[(string)$id] ?? [];
            $expRow = $expSet[(string)$id] ?? [];
            $drRow = $drDeals[(string)$id] ?? [];
            $rows[] = [
                'deal_id' => (string)$id,
                'title' => (string)($deal['title'] ?? ''),
                'status' => (string)($deal['status'] ?? ''),
                'deal_date' => (string)($deal['deal_date'] ?? ''),
                'in_expected' => array_key_exists((string)$id, $expSet),
                'in_drilldown' => array_key_exists((string)$id, $drDeals),
                'payable_amount' => (float)($deal['payable_amount'] ?? ($drRow['payable_amount'] ?? 0)),
                'expected_paid' => (float)($expRow['paid'] ?? $expRow['total'] ?? 0),
                'drilldown_paid' => (float)($drRow['total_paid'] ?? 0),
                'drilldown_row_count' => (int)($drRow['row_count'] ?? 0),
                'expected_remaining' => (float)($expRow['rem'] ?? 0),
                'tx_all_paid' => $this->sumTxByDeal((string)$id, 'all_statuses', false),
                'tx_confirmed_paid' => $this->sumTxByDeal((string)$id, 'confirmed_only', false),
                'json_all_paid' => $this->sumJsonByDeal((string)$id, false),
                'ui_window_paid' => $this->sumJsonByDeal((string)$id, true),
                'first_ui_date' => $this->firstJsonDate((string)$id),
                'first_tx_db_date' => $this->firstTxDate((string)$id, 'db'),
                'first_tx_created_date' => $this->firstTxDate((string)$id, 'created'),
                'card_key' => $cardKey,
            ];
        }
        return array_slice($rows, 0, $this->traceLimit);
    }

    private function drDuplicateStats(array $d): array
    {
        $counts = [];
        foreach (($d['deals'] ?? []) as $r) {
            $id = trim((string)($r['deal_id'] ?? ''));
            if ($id === '') continue;
            $counts[$id] = ($counts[$id] ?? 0) + 1;
        }
        foreach ($counts as $id => $c) {
            if ($c <= 1) unset($counts[$id]);
        }
        arsort($counts);
        return $counts;
    }

    private function drUniqueDealSum(array $d, string $field): float
    {
        $sum = 0.0;
        $seen = [];
        foreach (($d['deals'] ?? []) as $r) {
            $id = trim((string)($r['deal_id'] ?? ''));
            if ($id === '' || isset($seen[$id])) continue;
            $seen[$id] = true;
            $sum += (float)($r[$field] ?? 0);
        }
        return $sum;
    }

    private function inferLikelyCauses(string $cardKey, array $metrics, array $miss, array $extra, array $dup): array
    {
        $causes = [];
        if (!empty($dup)) {
            $causes[] = 'drilldown شامل ردیف تکراری برای یک معامله است (احتمال JOIN/aggregation اشتباه در drilldown)';
        }
        if (!empty($extra)) {
            $causes[] = 'drilldown معاملات اضافه نسبت به oracle دارد (احتمال تفاوت cohort/date-basis/legacy filter)';
        }
        if (!empty($miss)) {
            $causes[] = 'drilldown برخی معاملات oracle را برنمی‌گرداند (احتمال شرط اضافی یا source mismatch)';
        }
        if ($cardKey === 'settled') {
            if (($metrics['card_collected'] ?? 0) > ($metrics['card_contract'] ?? 0)) {
                $causes[] = 'کارت settled مبلغ وصول را بیشتر از مبلغ قرارداد نشان می‌دهد (علامت قطعی double-count در کارت)';
            }
            if (($metrics['card_count'] ?? 0) !== ($metrics['dr_count'] ?? 0)) {
                $causes[] = 'کارت settled و drilldown از موتور پرداخت متفاوت استفاده می‌کنند';
            }
        }
        if ($cardKey === 'outstanding' && (($metrics['card_count'] ?? 0) !== ($metrics['dr_count'] ?? 0))) {
            $causes[] = 'کارت outstanding با drilldown هم‌منطق نیست (احتمال aggregator متفاوت در کارت)';
        }
        if ($cardKey === 'without_payment' && (($metrics['card_count'] ?? 0) !== ($metrics['dr_count'] ?? 0))) {
            $causes[] = 'drilldown بدون‌پرداخت هنوز منطق absolute no-payment را رعایت نمی‌کند یا مسیر drilldown اشتباه است';
        }
        if ($cardKey === 'sales_total' && abs((float)($metrics['dr_amount'] ?? 0) - (float)($metrics['card_amount'] ?? 0)) > 0.01) {
            $causes[] = 'drilldown فروش مبلغ را روی همه پرداخت‌های معامله جمع می‌کند، ولی کارت فروش فقط مبلغ فروش طبق criteria (مثلاً first payment day) را نشان می‌دهد';
        }
        if ($cardKey === 'deal_conversion' && abs(((float)($metrics['card_rate'] ?? 0) + (float)($metrics['card_non_rate'] ?? 0)) - 100.0) > 0.2 && (int)($metrics['card_total_deals'] ?? 0) > 0) {
            $causes[] = 'جمع نرخ تبدیل و عدم تبدیل 100 نیست';
        }
        if ($cardKey === 'open_pipeline' && (($metrics['card_count'] ?? 0) !== ($metrics['dr_count'] ?? 0))) {
            $causes[] = 'کارت فروش جاری و drilldown وضعیت معاملات (pending) هم‌راستا نیستند';
        }
        if ($cardKey === 'lost_sales' && (($metrics['card_count'] ?? 0) !== ($metrics['dr_count'] ?? 0))) {
            $causes[] = 'کارت فروش ناموفق و drilldown (lost) هم‌راستا نیستند';
        }
        return $causes;
    }

    private function sumTxByDeal(string $dealId, string $scope = 'all_statuses', bool $window = false): float
    {
        $sum = 0.0;
        foreach (($this->txByDeal[$dealId] ?? []) as $t) {
            if (!$this->txPass($t, $scope)) continue;
            $dt = $this->normDate((string)($t['payment_date'] ?? '')) ?: substr((string)($t['created_at'] ?? ''), 0, 10);
            if ($window && !$this->inRange($dt)) continue;
            $sum += (float)($t['amount'] ?? 0);
        }
        return $sum;
    }

    private function sumJsonByDeal(string $dealId, bool $window = false): float
    {
        $sum = 0.0;
        foreach (($this->jsonByDeal[$dealId] ?? []) as $p) {
            if ($window && !$this->inRange((string)$p['date'])) continue;
            $sum += (float)($p['amount'] ?? 0);
        }
        return $sum;
    }

    private function firstJsonDate(string $dealId): string
    {
        $dates = array_map(static fn($p) => (string)($p['date'] ?? ''), $this->jsonByDeal[$dealId] ?? []);
        $dates = array_values(array_filter($dates));
        sort($dates);
        return $dates[0] ?? '';
    }

    private function firstTxDate(string $dealId, string $mode = 'db'): string
    {
        $dates = [];
        foreach (($this->txByDeal[$dealId] ?? []) as $t) {
            $d = $mode === 'created'
                ? substr((string)($t['created_at'] ?? ''), 0, 10)
                : $this->normDate((string)($t['payment_date'] ?? ''));
            if ($d !== '') $dates[] = $d;
        }
        sort($dates);
        return $dates[0] ?? '';
    }

    private function drIds(array $d): array { $ids=[]; foreach(($d['deals']??[]) as $r){ $id=trim((string)($r['deal_id']??'')); if($id!=='') $ids[]=$id; } return array_values(array_unique($ids)); }
    private function drCount(array $d): int { return count($this->drIds($d)); }
    private function drSum(array $d,string $field): float { $s=0.0; foreach(($d['deals']??[]) as $r){ $s+=(float)($r[$field]??0); } return $s; }
}

function runReconCase(object $container, array $filters): array
{
    $filters['_json'] = true;
    $filters['_verbose'] = false;
    $svc = $container->services->kpi ?? null;
    $runner = new KpiFinancialRecon($container->db->getPdo(), $container->repositories->kpi, $filters, $svc);
    ob_start();
    $runner->run();
    $raw = trim((string)ob_get_clean());
    $decoded = json_decode($raw, true);
    if (!is_array($decoded)) {
        return ['error' => 'invalid_json_output', 'raw' => $raw];
    }
    return $decoded;
}

function buildMatrixRanges(string $dateFrom, string $dateTo, string $spec = 'd1,w7,mtd,prev_month,current'): array
{
    $tokens = array_values(array_filter(array_map('trim', explode(',', $spec))));
    $ranges = [];
    $dt = new DateTime($dateTo ?: date('Y-m-d'));
    foreach ($tokens as $tok) {
        switch ($tok) {
            case 'd1':
                $from = $dt->format('Y-m-d'); $to = $dt->format('Y-m-d'); break;
            case 'w7':
                $from = (clone $dt)->modify('-6 day')->format('Y-m-d'); $to = $dt->format('Y-m-d'); break;
            case 'mtd':
                $from = $dt->format('Y-m-01'); $to = $dt->format('Y-m-d'); break;
            case 'prev_month':
                $p = (clone $dt)->modify('first day of previous month');
                $from = $p->format('Y-m-01'); $to = $p->format('Y-m-t'); break;
            case 'current':
            default:
                $from = $dateFrom; $to = $dateTo; break;
        }
        $ranges[] = ['key' => $tok, 'date_from' => $from, 'date_to' => $to];
    }
    return $ranges;
}

function buildMatrixCombos(string $preset = 'quick'): array
{
    $presets = [
        'quick' => [
            'sales_date_basis' => ['ui_payment_date','db_payment_date'],
            'sales_count_rule' => ['first_payment_only'],
            'payment_date_basis' => ['ui_payment_date','db_payment_date'],
            'payment_count_rule' => ['all_payments'],
            'payment_status_scope' => ['confirmed_only'],
            'legacy' => ['exclude','all'],
        ],
        'full' => [
            'sales_date_basis' => ['ui_payment_date','db_payment_date','system_created_at'],
            'sales_count_rule' => ['first_payment_only','all_payments'],
            'payment_date_basis' => ['ui_payment_date','db_payment_date','system_created_at'],
            'payment_count_rule' => ['all_payments','first_payment_only'],
            'payment_status_scope' => ['confirmed_only','all_statuses'],
            'legacy' => ['exclude','all','only'],
        ],
    ];
    $cfg = $presets[$preset] ?? $presets['quick'];
    $combos = [];
    foreach ($cfg['sales_date_basis'] as $sdb) foreach ($cfg['sales_count_rule'] as $scr)
    foreach ($cfg['payment_date_basis'] as $pdb) foreach ($cfg['payment_count_rule'] as $pcr)
    foreach ($cfg['payment_status_scope'] as $pss) foreach ($cfg['legacy'] as $legacy) {
        $combos[] = compact('sdb','scr','pdb','pcr','pss','legacy');
    }
    return $combos;
}

function runMatrix(object $container, array $baseFilters, array $cli): int
{
    $ownersCsv = trim((string)($cli['matrix-owners'] ?? $baseFilters['owner_didar_ids_csv'] ?? $baseFilters['owner_didar_id'] ?? ''));
    $owners = $ownersCsv !== '' ? array_values(array_filter(array_map('trim', explode(',', $ownersCsv)))) : [''];
    $preset = (string)($cli['matrix-preset'] ?? 'quick');
    $ranges = buildMatrixRanges((string)$baseFilters['date_from'], (string)$baseFilters['date_to'], (string)($cli['matrix-ranges'] ?? 'd1,w7,mtd,current'));
    $combos = buildMatrixCombos($preset);
    $limit = max(0, (int)($cli['matrix-limit'] ?? 0));
    if ($limit > 0) $combos = array_slice($combos, 0, $limit);

    $results = [];
    $summary = [
        'cases' => 0,
        'pass' => 0,
        'fail' => 0,
        'top_card_failures' => [],
        'top_chart_failures' => [],
    ];
    foreach ($owners as $owner) {
        foreach ($ranges as $range) {
            foreach ($combos as $combo) {
                $f = $baseFilters;
                $f['date_from'] = $range['date_from'];
                $f['date_to'] = $range['date_to'];
                $f['person_date_from'] = $range['date_from'];
                $f['person_date_to'] = $range['date_to'];
                $f['owner_didar_id'] = ($owner !== '' && strpos((string)$owner, ',') === false) ? $owner : '';
                $f['owner_didar_ids_csv'] = $owner;
                $f['owner_didar_ids'] = $owner !== '' ? [$owner] : [];
                $f['kpi_sales_date_basis'] = $combo['sdb'];
                $f['kpi_sales_count_rule'] = $combo['scr'];
                $f['kpi_payment_date_basis'] = $combo['pdb'];
                $f['kpi_payment_count_rule'] = $combo['pcr'];
                $f['kpi_payment_status_scope'] = $combo['pss'];
                $f['legacy_deal_scope'] = $combo['legacy'];
                $f['_surface'] = $baseFilters['_surface'] ?? 'performance';

                $rep = runReconCase($container, $f);
                $summary['cases']++;
                $overall = (string)($rep['summary_report']['overall_status'] ?? 'FAIL');
                if ($overall === 'PASS') $summary['pass']++; else $summary['fail']++;
                foreach (($rep['cards'] ?? []) as $cardKey => $card) {
                    if (!empty($card['fail'])) {
                        $summary['top_card_failures'][$cardKey] = ($summary['top_card_failures'][$cardKey] ?? 0) + 1;
                    }
                }
                foreach (($rep['charts']['summary'] ?? []) as $chartKey => $c) {
                    if ((int)($c['fails'] ?? 0) > 0) {
                        $summary['top_chart_failures'][$chartKey] = ($summary['top_chart_failures'][$chartKey] ?? 0) + 1;
                    }
                }
                $results[] = [
                    'owner' => $owner === '' ? 'ALL' : $owner,
                    'range' => $range,
                    'criteria' => $combo,
                    'summary_report' => $rep['summary_report'] ?? [],
                    'source_divergence' => $rep['source_divergence']['any_mismatch_count'] ?? null,
                    'cards_failed' => array_values(array_keys(array_filter((array)($rep['cards'] ?? []), static fn($c) => !empty($c['fail'])))),
                    'charts_failed' => array_values(array_keys(array_filter((array)($rep['charts']['summary'] ?? []), static fn($c) => ((int)($c['fails'] ?? 0) > 0)))),
                ];
            }
        }
    }
    arsort($summary['top_card_failures']);
    arsort($summary['top_chart_failures']);
    $out = ['matrix' => ['preset' => $preset, 'ranges' => $ranges, 'combo_count' => count($combos), 'owner_count' => count($owners)], 'summary' => $summary, 'cases' => $results];
    if (!empty($baseFilters['_json'])) {
        echo json_encode($out, JSON_UNESCAPED_UNICODE|JSON_PRETTY_PRINT).PHP_EOL;
    } else {
        echo "=== KPI Matrix Runner ===\n";
        echo "preset={$preset} cases={$summary['cases']} pass={$summary['pass']} fail={$summary['fail']}\n";
        echo "Top card failures:\n";
        foreach (array_slice($summary['top_card_failures'], 0, 10, true) as $k => $v) echo "  - {$k}: {$v}\n";
        echo "Top chart failures:\n";
        foreach (array_slice($summary['top_chart_failures'], 0, 10, true) as $k => $v) echo "  - {$k}: {$v}\n";
        if (!empty($cli['verbose'])) {
            foreach ($results as $r) {
                echo json_encode($r, JSON_UNESCAPED_UNICODE).PHP_EOL;
            }
        }
    }
    return ($summary['fail'] > 0 ? 1 : 0);
}

$o=getopt('',[
    'date-from:','date-to:','owner::','owners::','sales-date-basis::','sales-count-rule::',
    'payment-date-basis::','payment-count-rule::','payment-status-scope::','legacy::','deal-statuses::','deal-lead-types::',
    'verbose::','json::','trace-limit::','only-card::','surface::',
    'matrix::','matrix-preset::','matrix-ranges::','matrix-owners::','matrix-limit::'
]);
$df=(string)($o['date-from']??date('Y-m-d')); $dt=(string)($o['date-to']??date('Y-m-d'));
$owner=(string)($o['owner']??''); $owners=(string)($o['owners']??$owner);
$filters=[
    'date_from'=>$df,'date_to'=>$dt,'person_date_from'=>$df,'person_date_to'=>$dt,
    'owner_didar_id'=>($owner!=='' && strpos($owners,',')===false)?$owner:'',
    'owner_didar_ids_csv'=>$owners,'owner_didar_ids'=>$owners!==''?array_values(array_filter(array_map('trim',explode(',',$owners)))):[],
    'product_id'=>'','product_ids_csv'=>'','product_ids'=>[],
    'deal_statuses_csv'=>(string)($o['deal-statuses']??''),'deal_statuses'=>!empty($o['deal-statuses'])?array_values(array_filter(array_map('trim',explode(',',(string)$o['deal-statuses'])))):[],
    'deal_lead_types_csv'=>(string)($o['deal-lead-types']??''),'deal_lead_types'=>!empty($o['deal-lead-types'])?array_values(array_filter(array_map('trim',explode(',',(string)$o['deal-lead-types'])))):[],
    'kpi_sales_date_basis'=>(string)($o['sales-date-basis']??'ui_payment_date'),
    'kpi_sales_count_rule'=>(string)($o['sales-count-rule']??'first_payment_only'),
    'kpi_payment_date_basis'=>(string)($o['payment-date-basis']??'ui_payment_date'),
    'kpi_payment_count_rule'=>(string)($o['payment-count-rule']??'all_payments'),
    'kpi_payment_status_scope'=>(string)($o['payment-status-scope']??'confirmed_only'),
    'legacy_deal_scope'=>(string)($o['legacy']??'exclude'),
    '_verbose' => array_key_exists('verbose', $o),
    '_json' => array_key_exists('json', $o),
    '_trace_limit' => (int)($o['trace-limit'] ?? 25),
    '_only_card' => (string)($o['only-card'] ?? ''),
    '_surface' => (string)($o['surface'] ?? 'performance'),
];
$kpiService = $container->services->kpi ?? null;
if (array_key_exists('matrix', $o)) {
    exit(runMatrix($container, $filters, $o));
}
exit((new KpiFinancialRecon($container->db->getPdo(), $container->repositories->kpi, $filters, $kpiService))->run());
