<?php

declare(strict_types=1);
namespace ABS\Modules\Reports\Repositories;

use ABS\Core\Database;
use PDO;

final class SupplierReconciliationRepository
{
    public function suppliers(): array{return Database::query("SELECT id,name,code FROM report_suppliers WHERE is_active=1 ORDER BY name")->fetchAll(PDO::FETCH_ASSOC)?:[];}

    public function supplierSummary(int $supplierId,int $year,int $month=0): array
    {
        [$sagaWhere,$sagaParams]=$this->sagaWhere($supplierId,$year,$month,'s');
        $stmt=Database::prepare("SELECT COUNT(*) documents,COALESCE(SUM(gross_value),0) gross,COALESCE(SUM(net_value),0) net,COALESCE(SUM(tax_value),0) tax FROM saga_purchase_lines s WHERE {$sagaWhere}");$stmt->execute($sagaParams);$saga=$stmt->fetch(PDO::FETCH_ASSOC)?:[];
        $params=[':supplier_id'=>$supplierId,':year'=>$year];$where='d.supplier_id=:supplier_id AND YEAR(d.invoice_date)=:year';if($month>0){$where.=' AND MONTH(d.invoice_date)=:month';$params[':month']=$month;}
        $stmt=Database::prepare("SELECT COUNT(*) documents,COALESCE(SUM(invoice_value),0) gross,COALESCE(SUM(net_value),0) net,COALESCE(SUM(tax_value),0) tax FROM report_supplier_documents d WHERE {$where}");$stmt->execute($params);$external=$stmt->fetch(PDO::FETCH_ASSOC)?:[];
        $matches=$this->matchRows($supplierId,$year,$month);
        $counts=['exact'=>0,'probable'=>0,'value_diff'=>0,'missing_external'=>0,'missing_saga'=>0];
        foreach($matches as $r){$counts[$r['status']] = ($counts[$r['status']]??0)+1;}
        return ['saga'=>$saga,'external'=>$external,'difference'=>(float)($saga['gross']??0)-(float)($external['gross']??0),'counts'=>$counts];
    }

    public function monthlyComparison(int $supplierId,int $year): array
    {
        $result=[];for($m=1;$m<=12;$m++){$x=$this->supplierSummary($supplierId,$year,$m);$result[]=['month'=>$m,'saga'=>(float)($x['saga']['gross']??0),'external'=>(float)($x['external']['gross']??0),'difference'=>(float)$x['difference'],'saga_docs'=>(int)($x['saga']['documents']??0),'external_docs'=>(int)($x['external']['documents']??0)];}return $result;
    }

    public function matchRows(int $supplierId,int $year,int $month=0): array
    {
        [$where,$params]=$this->sagaWhere($supplierId,$year,$month,'s');
        $q=Database::prepare("SELECT s.* FROM saga_purchase_lines s WHERE {$where} ORDER BY s.invoice_date,s.invoice_number");$q->execute($params);$saga=$q->fetchAll(PDO::FETCH_ASSOC)?:[];
        $p=[':supplier_id'=>$supplierId,':year'=>$year];$w='supplier_id=:supplier_id AND YEAR(invoice_date)=:year';if($month>0){$w.=' AND MONTH(invoice_date)=:month';$p[':month']=$month;}
        $q=Database::prepare("SELECT * FROM report_supplier_documents WHERE {$w} ORDER BY invoice_date,invoice_number");$q->execute($p);$external=$q->fetchAll(PDO::FETCH_ASSOC)?:[];
        $used=[];$rows=[];
        foreach($saga as $s){$best=null;$bestScore=-1;$bestIndex=null;foreach($external as $i=>$e){if(isset($used[$i]))continue;$score=$this->score($s,$e);if($score>$bestScore){$bestScore=$score;$best=$e;$bestIndex=$i;}}if($best!==null&&$bestScore>=45){$used[$bestIndex]=true;$diff=(float)$s['gross_value']-(float)$best['invoice_value'];$status=abs($diff)<=0.05&&$bestScore>=90?'exact':(abs($diff)<=0.05?'probable':'value_diff');$rows[]=$this->row($s,$best,$status,$bestScore,$diff);}else{$rows[]=$this->row($s,null,'missing_external',0,(float)$s['gross_value']);}}
        foreach($external as $i=>$e){if(!isset($used[$i]))$rows[]=$this->row(null,$e,'missing_saga',0,-(float)$e['invoice_value']);}
        usort($rows,fn($a,$b)=>strcmp((string)($a['date']??''),(string)($b['date']??'')));return $rows;
    }

    public function unmappedSuppliers(int $year): array
    {
        $q=Database::prepare("SELECT supplier_name,supplier_tax_id,COUNT(*) documents,SUM(gross_value) gross FROM saga_purchase_lines WHERE period_year=:year AND NOT EXISTS(SELECT 1 FROM report_supplier_saga_aliases a WHERE (a.saga_tax_id IS NOT NULL AND a.saga_tax_id=saga_purchase_lines.supplier_tax_id) OR UPPER(a.saga_supplier_name)=UPPER(saga_purchase_lines.supplier_name)) GROUP BY supplier_name,supplier_tax_id ORDER BY gross DESC");$q->execute([':year'=>$year]);return $q->fetchAll(PDO::FETCH_ASSOC)?:[];
    }
    public function saveAlias(int $supplierId,string $name,?string $taxId): void
    {Database::prepare("INSERT INTO report_supplier_saga_aliases(supplier_id,saga_supplier_name,saga_tax_id) VALUES(?,?,?) ON DUPLICATE KEY UPDATE supplier_id=VALUES(supplier_id)")->execute([$supplierId,trim($name),$taxId?strtoupper(preg_replace('/[^A-Z0-9]/i','',$taxId)):null]);}

    private function sagaWhere(int $supplierId,int $year,int $month,string $alias): array
    {
        $supplier=Database::prepare('SELECT * FROM report_suppliers WHERE id=?');$supplier->execute([$supplierId]);$s=$supplier->fetch(PDO::FETCH_ASSOC)?:[];
        $aliases=Database::prepare('SELECT saga_supplier_name,saga_tax_id FROM report_supplier_saga_aliases WHERE supplier_id=?');$aliases->execute([$supplierId]);$items=$aliases->fetchAll(PDO::FETCH_ASSOC)?:[];
        $names=[(string)($s['name']??''),(string)($s['code']??'')];foreach($items as $a)$names[]=(string)$a['saga_supplier_name'];$names=array_values(array_filter(array_unique(array_map([$this,'norm'],$names))));
        $clauses=[];$params=[':year'=>$year];foreach($items as $i=>$a){if(!empty($a['saga_tax_id'])){$clauses[]="{$alias}.supplier_tax_id=:tax{$i}";$params[":tax{$i}"]=$a['saga_tax_id'];}}
        foreach($names as $i=>$n){$clauses[]="UPPER(REPLACE(REPLACE(REPLACE({$alias}.supplier_name,' ',''),'.',''),'-','')) LIKE :name{$i}";$params[":name{$i}"]='%'.$n.'%';}
        if(!$clauses)$clauses[]='0=1';$where='('.implode(' OR ',$clauses).") AND {$alias}.period_year=:year";if($month>0){$where.=" AND {$alias}.period_month=:month";$params[':month']=$month;}return[$where,$params];
    }
    private function score(array $s,array $e): int
    {$score=0;$sn=$this->norm((string)$s['invoice_number']);$en=$this->norm((string)$e['invoice_number']);if($sn!==''&&$en!==''&&$sn===$en)$score+=65;elseif($sn!==''&&$en!==''&&(str_contains($sn,$en)||str_contains($en,$sn)))$score+=45;$diff=abs((float)$s['gross_value']-(float)$e['invoice_value']);if($diff<=0.05)$score+=25;elseif($diff<=1)$score+=15;$days=abs((strtotime((string)$s['invoice_date'])?:0)-(strtotime((string)$e['invoice_date'])?:0))/86400;if($days==0)$score+=10;elseif($days<=3)$score+=7;elseif($days<=7)$score+=3;return$score;}
    private function row(?array $s,?array $e,string $status,int $score,float $difference): array{return['date'=>$s['invoice_date']??$e['invoice_date']??null,'saga_id'=>$s['id']??null,'external_id'=>$e['id']??null,'invoice_number_saga'=>$s['invoice_number']??null,'invoice_number_external'=>$e['invoice_number']??null,'value_saga'=>(float)($s['gross_value']??0),'value_external'=>(float)($e['invoice_value']??0),'difference'=>$difference,'score'=>$score,'status'=>$status];}
    private function norm(string $v): string{return strtoupper(preg_replace('/[^A-Z0-9]/i','',$v)??'');}
}