<?php
namespace App\Modules\Inventory;

use Illuminate\Database\ConnectionInterface;
use Illuminate\Support\Facades\Crypt;

/** Physical registration-period quantities, projected from the canonical book only. */
final class StockPeriodReport
{
    public function __construct(private ConnectionInterface $db){}
    public function read(array $binding,string $fingerprint,array $filters,?string $token):array
    {
        return $this->db->transaction(function()use($binding,$fingerprint,$filters,$token){
            abort_unless($this->db->transactionLevel()===1,503);
            $this->db->statement('SET TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY');
            $this->db->statement("SET LOCAL statement_timeout='3s'");
            $reader=new \Tempo\Inventory\InventoryReader($this->db,$binding['schema'],$binding['source_tenant_id'],$binding['warehouse_ids']);
            try{$revision=$reader->revision();}catch(\UnexpectedValueException){abort(503,'Fonte de stock incompatível.');}
            $observed=time();
            if($token!==null){
                try{$cut=json_decode(Crypt::decryptString($token),true,32,JSON_THROW_ON_ERROR);}catch(\Throwable){abort(409,'Referência de relatório inválida. Actualize a consulta.');}
                abort_unless(($cut['fingerprint']??null)===$fingerprint&&($cut['revision']??null)===$revision
                    &&is_int($cut['observed']??null)&&$cut['observed']<=time()&&$cut['observed']>=time()-600,409,'A fonte ou o contexto mudou. Actualize o relatório.');
                $observed=$cut['observed'];
            }
            $from=$filters['from'].' 00:00:00';
            $until=(new \DateTimeImmutable($filters['to'],new \DateTimeZone('UTC')))->modify('+1 day')->format('Y-m-d').' 00:00:00';
            $q=$this->db->table('public.stock_movements AS m')
                ->join('public.stock_items AS i',fn($j)=>$j->on('i.id','=','m.stock_item_id')->on('i.tenant_id','=','m.tenant_id'))
                ->join('public.warehouses AS w',fn($j)=>$j->on('w.id','=','m.warehouse_id')->on('w.tenant_id','=','m.tenant_id'))
                ->where('m.tenant_id',$binding['source_tenant_id'])->whereIn('m.warehouse_id',$binding['warehouse_ids'])->where('m.moved_at','<',$until);
            if($filters['warehouse_id']!==null)$q->where('m.warehouse_id',$filters['warehouse_id']);
            if($filters['search']!==''){
                $literal='%'.str_replace(['\\','%','_'],['\\\\','\\%','\\_'],$filters['search']).'%';
                $q->where(fn($s)=>$s->where('i.code','ilike',$literal)->orWhere('i.name','ilike',$literal));
            }
            $q->select(['m.stock_item_id','m.warehouse_id','i.code','i.name','i.unit','w.code AS warehouse_code','w.name AS warehouse_name'])
                ->selectRaw('SUM(CASE WHEN m.moved_at < ? THEN m.quantity_milli::numeric ELSE 0 END)::text AS opening_milli',[$from])
                ->selectRaw('SUM(CASE WHEN m.moved_at >= ? AND m.quantity_milli > 0 THEN m.quantity_milli::numeric ELSE 0 END)::text AS incoming_milli',[$from])
                ->selectRaw('SUM(CASE WHEN m.moved_at >= ? AND m.quantity_milli < 0 THEN -m.quantity_milli::numeric ELSE 0 END)::text AS outgoing_milli',[$from])
                ->selectRaw('SUM(m.quantity_milli)::text AS closing_milli')
                ->selectRaw('COUNT(*) FILTER (WHERE m.moved_at >= ?)::text AS period_movements',[$from])
                ->groupBy('m.stock_item_id','m.warehouse_id','i.code','i.name','i.unit','w.code','w.name')
                ->orderBy('m.stock_item_id')->orderBy('m.warehouse_id');
            $total=(int)$this->db->query()->fromSub((clone $q)->reorder(),'stock_period_pairs')->count();
            $rows=$q->limit($filters['per_page'])->offset(($filters['page']-1)*$filters['per_page'])->get()->map(fn($r)=>(array)$r)->all();
            return ['status'=>'available','synthetic_only'=>true,'report'=>'physical_period','period'=>['from'=>$filters['from'],'to'=>$filters['to'],'timezone'=>'UTC','basis'=>'registration_time'],
                'rows'=>$rows,'pagination'=>['total'=>$total,'page'=>$filters['page'],'per_page'=>$filters['per_page'],'last_page'=>max(1,(int)ceil($total/$filters['per_page']))],
                'observed_at'=>gmdate('c',$observed),'snapshot'=>Crypt::encryptString(json_encode(['fingerprint'=>$fingerprint,'revision'=>$revision,'observed'=>$observed],JSON_THROW_ON_ERROR)),
                'ledger_revision'=>$revision,'capabilities'=>['write'=>false,'valuation'=>false,'reserved'=>false,'blocked'=>false],
                'notice'=>'Quantidades por data de registo UTC; designações actuais. Não há total somado entre unidades diferentes. Sem valorização, reservas ou disponibilidade. Pares sem história até ao fecho não são saldos zero.'];
        });
    }
}