<?php

require_once __DIR__ . '/../../core/database.php';

class TreasuryService
{
    private $pdo;

    public function __construct($pdo = null)
    {
        $this->pdo = $pdo ?: db();
    }

    public function getWorkspace($startDate, $scenarioKey = 'base', $accountId = null)
    {
        $this->assertInstalled();
        $start = $this->monday($this->validDate($startDate, date('Y-m-d')));
        $accountId = $accountId ? (int)$accountId : null;
        $scenarios = $this->scenarios();
        if (!isset($scenarios[$scenarioKey])) $scenarioKey = 'base';
        // Los compromisos futuros no tienen una cuenta bancaria de destino/origen.
        // El forecast se mantiene consolidado; el filtro de cuenta sólo afecta libro y cartolas.
        $forecast = $this->forecast($start, $scenarios[$scenarioKey], null);

        return array(
            'start_date' => $start,
            'end_date' => date('Y-m-d', strtotime($start . ' +90 days')),
            'scenario_key' => $scenarioKey,
            'selected_account_id' => $accountId,
            'accounts' => $this->accounts(),
            'account_positions' => $this->accountPositions(date('Y-m-d')),
            'finance_centers' => $this->financeCenters(),
            'scenarios' => array_values($scenarios),
            'summary' => $forecast['summary'],
            'quality' => $forecast['quality'],
            'weeks' => $forecast['weeks'],
            'ledger' => $this->ledger($accountId),
            'opening_balances' => $this->openingBalances(),
            'statement' => $this->statementWorkspace($accountId),
            'scenario_items' => $this->scenarioItems(),
            'coverage' => $forecast['coverage'],
            'coverage_confirmation' => $this->coverageConfirmation(),
        );
    }

    public function dashboardCashMetrics()
    {
        $this->assertInstalled();
        $scenarios = $this->scenarios();
        $forecast = $this->forecast($this->monday(date('Y-m-d')), $scenarios['base'], null);
        return array(
            'cash_balance' => $forecast['summary']['current_balance'],
            'minimum_balance' => $forecast['summary']['minimum_balance'],
            'runway_months' => $forecast['summary']['runway_months'],
            'deficit_weeks' => $forecast['summary']['deficit_weeks'],
            'quality' => $forecast['quality'],
            'note' => $forecast['summary']['quality_note'],
            'freshness' => $forecast['summary']['freshness'],
        );
    }

    public function saveOpeningBalance(array $input, $userId)
    {
        $this->assertInstalled();
        $accountId = $this->requiredAccount($input['cash_account_id'] ?? 0);
        $date = $this->validDate($input['balance_date'] ?? '', null);
        if (!$date) throw new InvalidArgumentException('Ingresa una fecha de saldo válida.');
        $amount = $this->decimal($input['amount'] ?? null, true);
        $currency = strtoupper(trim((string)($input['currency_code'] ?? 'CLP')));
        if (!preg_match('/^[A-Z]{3,10}$/', $currency)) $currency = 'CLP';
        $reconciled = !empty($input['is_reconciled']) ? 1 : 0;
        $notes = $this->nullableText($input['notes'] ?? '', 1000);

        $before = $this->findOne('SELECT * FROM treasury_opening_balances WHERE cash_account_id=? AND balance_date=? LIMIT 1', array($accountId, $date));
        $stmt = $this->pdo->prepare("INSERT INTO treasury_opening_balances (cash_account_id,balance_date,amount,currency_code,is_reconciled,notes,created_by,updated_by) VALUES (?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE amount=VALUES(amount),currency_code=VALUES(currency_code),is_reconciled=VALUES(is_reconciled),notes=VALUES(notes),updated_by=VALUES(updated_by),updated_at=NOW()");
        $stmt->execute(array($accountId, $date, $amount, $currency, $reconciled, $notes, $userId ?: null, $userId ?: null));
        $row = $this->findOne('SELECT * FROM treasury_opening_balances WHERE cash_account_id=? AND balance_date=? LIMIT 1', array($accountId, $date));
        $this->audit('opening_balance', (int)$row['id'], $before ? 'update' : 'create', $before, $row, $userId);
        return $row;
    }

    public function saveManualMovement(array $input, $userId)
    {
        $this->assertInstalled();
        $accountId = $this->requiredAccount($input['cash_account_id'] ?? 0);
        $date = $this->validDate($input['movement_date'] ?? '', null);
        if (!$date) throw new InvalidArgumentException('Ingresa una fecha válida.');
        $direction = strtolower(trim((string)($input['direction'] ?? '')));
        if (!in_array($direction, array('inflow','outflow'), true)) throw new InvalidArgumentException('Selecciona entrada o salida.');
        $activity = strtolower(trim((string)($input['cash_activity'] ?? 'operations')));
        if (!in_array($activity, array('operations','investment','financing'), true)) $activity = 'operations';
        $amount = $this->decimal($input['amount'] ?? null, false);
        if ($amount <= 0) throw new InvalidArgumentException('El importe debe ser mayor que cero.');
        $description = trim((string)($input['description'] ?? ''));
        if ($description === '') throw new InvalidArgumentException('Ingresa una descripción.');
        $status = strtolower(trim((string)($input['status'] ?? 'confirmed')));
        if (!in_array($status, array('draft','confirmed'), true)) $status = 'confirmed';
        $id = isset($input['id']) ? (int)$input['id'] : 0;
        $centerId = !empty($input['finance_center_id']) ? (int)$input['finance_center_id'] : null;
        $params = array(
            $accountId, $date, $this->validDate($input['value_date'] ?? '', null), $direction, $activity, $amount,
            mb_substr($description, 0, 255), $this->nullableText($input['counterparty'] ?? '', 190),
            $this->nullableText($input['external_reference'] ?? '', 120), $centerId, $status,
            $this->nullableText($input['notes'] ?? '', 1000),
        );

        $before = null;
        if ($id > 0) {
            $before = $this->findOne("SELECT * FROM treasury_manual_movements WHERE id=? AND transfer_group IS NULL AND status<>'void' LIMIT 1", array($id));
            if (!$before) throw new RuntimeException('Movimiento no encontrado o no editable.');
            $stmt = $this->pdo->prepare("UPDATE treasury_manual_movements SET cash_account_id=?,movement_date=?,value_date=?,direction=?,cash_activity=?,amount=?,description=?,counterparty=?,external_reference=?,finance_center_id=?,status=?,notes=?,confirmed_by=CASE WHEN ?='confirmed' THEN ? ELSE NULL END,confirmed_at=CASE WHEN ?='confirmed' THEN NOW() ELSE NULL END,updated_by=?,updated_at=NOW() WHERE id=?");
            $stmt->execute(array_merge($params, array($status, $userId ?: null, $status, $userId ?: null, $id)));
        } else {
            $stmt = $this->pdo->prepare("INSERT INTO treasury_manual_movements (cash_account_id,movement_date,value_date,direction,cash_activity,amount,description,counterparty,external_reference,finance_center_id,status,notes,confirmed_by,confirmed_at,created_by,updated_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,CASE WHEN ?='confirmed' THEN NOW() ELSE NULL END,?,?)");
            $stmt->execute(array_merge($params, array($status === 'confirmed' ? ($userId ?: null) : null, $status, $userId ?: null, $userId ?: null)));
            $id = (int)$this->pdo->lastInsertId();
        }
        $row = $this->findOne('SELECT * FROM treasury_manual_movements WHERE id=?', array($id));
        $this->audit('manual_movement', $id, $before ? 'update' : 'create', $before, $row, $userId);
        return $row;
    }

    public function voidManualMovement($id, $userId)
    {
        $id = (int)$id;
        $before = $this->findOne("SELECT * FROM treasury_manual_movements WHERE id=? AND status<>'void' LIMIT 1", array($id));
        if (!$before) throw new RuntimeException('Movimiento no encontrado.');
        $this->pdo->prepare("UPDATE treasury_manual_movements SET status='void',updated_by=?,updated_at=NOW() WHERE id=? OR (transfer_group IS NOT NULL AND transfer_group=?)")
            ->execute(array($userId ?: null, $id, $before['transfer_group']));
        $this->audit('manual_movement', $id, 'void', $before, null, $userId);
    }

    public function saveTransfer(array $input, $userId)
    {
        $from = $this->requiredAccount($input['from_account_id'] ?? 0);
        $to = $this->requiredAccount($input['to_account_id'] ?? 0);
        if ($from === $to) throw new InvalidArgumentException('Selecciona dos cuentas diferentes.');
        $date = $this->validDate($input['movement_date'] ?? '', null);
        if (!$date) throw new InvalidArgumentException('Ingresa una fecha válida.');
        $amount = $this->decimal($input['amount'] ?? null, false);
        if ($amount <= 0) throw new InvalidArgumentException('El importe debe ser mayor que cero.');
        $description = trim((string)($input['description'] ?? 'Transferencia interna'));
        $reference = $this->nullableText($input['external_reference'] ?? '', 120);
        $group = bin2hex(random_bytes(16));

        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare("INSERT INTO treasury_manual_movements (cash_account_id,movement_date,direction,cash_activity,amount,description,external_reference,status,transfer_group,confirmed_by,confirmed_at,created_by,updated_by) VALUES (?,?,?,?,?,?,?,?,?,?,NOW(),?,?)");
            $stmt->execute(array($from,$date,'outflow','internal_transfer',$amount,mb_substr($description,0,255),$reference,'confirmed',$group,$userId ?: null,$userId ?: null,$userId ?: null));
            $outId = (int)$this->pdo->lastInsertId();
            $stmt->execute(array($to,$date,'inflow','internal_transfer',$amount,mb_substr($description,0,255),$reference,'confirmed',$group,$userId ?: null,$userId ?: null,$userId ?: null));
            $inId = (int)$this->pdo->lastInsertId();
            $this->audit('internal_transfer', $outId, 'create', null, array('group'=>$group,'out_id'=>$outId,'in_id'=>$inId,'amount'=>$amount), $userId);
            $this->pdo->commit();
            return array('transfer_group'=>$group,'out_id'=>$outId,'in_id'=>$inId);
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }
    }

    public function saveScenarioItem(array $input, $userId)
    {
        $scenarioId = (int)($input['scenario_id'] ?? 0);
        if (!$this->findOne("SELECT id FROM treasury_scenarios WHERE id=? AND is_active=1", array($scenarioId))) throw new InvalidArgumentException('Selecciona un escenario.');
        $type = strtolower(trim((string)($input['item_type'] ?? '')));
        if (!in_array($type, array('inflow','outflow'), true)) throw new InvalidArgumentException('Selecciona entrada o salida.');
        $date = $this->validDate($input['expected_date'] ?? '', null);
        if (!$date) throw new InvalidArgumentException('Ingresa una fecha esperada válida.');
        $amount = $this->decimal($input['amount'] ?? null, false);
        if ($amount <= 0) throw new InvalidArgumentException('El importe debe ser mayor que cero.');
        $probability = min(100, max(0, $this->decimal($input['probability_pct'] ?? 100, true)));
        $description = trim((string)($input['description'] ?? ''));
        if ($description === '') throw new InvalidArgumentException('Ingresa una descripción.');
        $activity = strtolower(trim((string)($input['cash_activity'] ?? 'operations')));
        if (!in_array($activity, array('operations','investment','financing'), true)) $activity = 'operations';
        $id = (int)($input['id'] ?? 0);
        $values = array($scenarioId,$type,$date,$amount,$probability,mb_substr($description,0,255),$this->nullableText($input['counterparty'] ?? '',190),$activity,!empty($input['finance_center_id'])?(int)$input['finance_center_id']:null,$this->nullableText($input['notes'] ?? '',1000));
        $before = null;
        if ($id > 0) {
            $before = $this->findOne("SELECT * FROM treasury_scenario_items WHERE id=? AND status='active'", array($id));
            if (!$before) throw new RuntimeException('Partida de escenario no encontrada.');
            $stmt = $this->pdo->prepare("UPDATE treasury_scenario_items SET scenario_id=?,item_type=?,expected_date=?,amount=?,probability_pct=?,description=?,counterparty=?,cash_activity=?,finance_center_id=?,notes=?,updated_by=?,updated_at=NOW() WHERE id=?");
            $stmt->execute(array_merge($values,array($userId ?: null,$id)));
        } else {
            $stmt = $this->pdo->prepare("INSERT INTO treasury_scenario_items (scenario_id,item_type,expected_date,amount,probability_pct,description,counterparty,cash_activity,finance_center_id,notes,created_by,updated_by) VALUES (?,?,?,?,?,?,?,?,?,?,?,?)");
            $stmt->execute(array_merge($values,array($userId ?: null,$userId ?: null)));
            $id = (int)$this->pdo->lastInsertId();
        }
        $row = $this->findOne('SELECT * FROM treasury_scenario_items WHERE id=?',array($id));
        $this->audit('scenario_item',$id,$before?'update':'create',$before,$row,$userId);
        return $row;
    }

    public function cancelScenarioItem($id, $userId)
    {
        $id = (int)$id;
        $before = $this->findOne("SELECT * FROM treasury_scenario_items WHERE id=? AND status='active'",array($id));
        if (!$before) throw new RuntimeException('Partida de escenario no encontrada.');
        $this->pdo->prepare("UPDATE treasury_scenario_items SET status='cancelled',updated_by=?,updated_at=NOW() WHERE id=?")->execute(array($userId ?: null,$id));
        $this->audit('scenario_item',$id,'cancel',$before,null,$userId);
    }

    public function saveForecastCoverage(array $input, $userId)
    {
        $through=$this->validDate($input['confirmed_through']??'',null);
        if(!$through)throw new InvalidArgumentException('Ingresa hasta qué fecha se revisó la cobertura del forecast.');
        $before=$this->coverageConfirmation();
        $values=array(!empty($input['payroll_complete'])?1:0,!empty($input['taxes_complete'])?1:0,!empty($input['financing_complete'])?1:0,!empty($input['other_expenses_complete'])?1:0,$through,$this->nullableText($input['notes']??'',1000),$userId ?: null);
        $stmt=$this->pdo->prepare("INSERT INTO treasury_forecast_coverage (id,payroll_complete,taxes_complete,financing_complete,other_expenses_complete,confirmed_through,notes,confirmed_by,confirmed_at) VALUES (1,?,?,?,?,?,?,?,NOW()) ON DUPLICATE KEY UPDATE payroll_complete=VALUES(payroll_complete),taxes_complete=VALUES(taxes_complete),financing_complete=VALUES(financing_complete),other_expenses_complete=VALUES(other_expenses_complete),confirmed_through=VALUES(confirmed_through),notes=VALUES(notes),confirmed_by=VALUES(confirmed_by),confirmed_at=NOW(),updated_at=NOW()");
        $stmt->execute($values);
        $row=$this->coverageConfirmation();$this->audit('forecast_coverage',1,'confirm',$before,$row,$userId);return $row;
    }

    public function importStatement($accountId, array $file, $userId)
    {
        $accountId = $this->requiredAccount($accountId);
        if (!isset($file['error']) || (int)$file['error'] !== UPLOAD_ERR_OK || empty($file['tmp_name'])) throw new InvalidArgumentException('Selecciona una cartola CSV válida.');
        if ((int)($file['size'] ?? 0) > 5 * 1024 * 1024) throw new InvalidArgumentException('La cartola supera el máximo de 5 MB.');
        $extension = strtolower(pathinfo((string)($file['name'] ?? ''), PATHINFO_EXTENSION));
        if (!in_array($extension,array('csv','txt'),true)) throw new InvalidArgumentException('La cartola debe ser CSV o TXT.');
        $raw = file_get_contents($file['tmp_name']);
        if ($raw === false || trim($raw) === '') throw new InvalidArgumentException('El archivo está vacío.');
        $hash = hash('sha256',$raw);
        if ($this->findOne('SELECT id FROM treasury_statement_imports WHERE cash_account_id=? AND file_hash=?',array($accountId,$hash))) throw new RuntimeException('Esta cartola ya fue importada para la cuenta seleccionada.');

        $lines = preg_split('/\r\n|\n|\r/', $raw);
        $lines = array_values(array_filter($lines,function($line){return trim($line)!=='';}));
        if (count($lines)<2) throw new InvalidArgumentException('La cartola no contiene filas para importar.');
        $delimiter = substr_count($lines[0],';') >= max(substr_count($lines[0],','),substr_count($lines[0],"\t")) ? ';' : (substr_count($lines[0],"\t")>substr_count($lines[0],',')?"\t":',');
        $headers = array_map(array($this,'normalizeHeader'),str_getcsv(array_shift($lines),$delimiter));
        $map = $this->statementHeaderMap($headers);
        if ($map['date']===null || ($map['amount']===null && $map['debit']===null && $map['credit']===null)) throw new InvalidArgumentException('No se reconocieron las columnas. Incluye Fecha y Monto, o Fecha/Cargo/Abono.');

        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare("INSERT INTO treasury_statement_imports (cash_account_id,original_filename,file_hash,created_by) VALUES (?,?,?,?)");
            $stmt->execute(array($accountId,mb_substr((string)$file['name'],0,255),$hash,$userId ?: null));
            $importId = (int)$this->pdo->lastInsertId();
            $insert = $this->pdo->prepare("INSERT IGNORE INTO treasury_statement_rows (import_id,cash_account_id,row_date,description,reference,debit_amount,credit_amount,signed_amount,balance_after,row_hash) VALUES (?,?,?,?,?,?,?,?,?,?)");
            $inserted=0;$minDate=null;$maxDate=null;
            foreach($lines as $line){
                $cols=str_getcsv($line,$delimiter);
                $date=$this->parseStatementDate($this->col($cols,$map['date']));
                if(!$date) continue;
                $debit=$map['debit']!==null?abs($this->parseMoney($this->col($cols,$map['debit']))):0;
                $credit=$map['credit']!==null?abs($this->parseMoney($this->col($cols,$map['credit']))):0;
                $signed=$credit-$debit;
                if($debit==0 && $credit==0 && $map['amount']!==null) $signed=$this->parseMoney($this->col($cols,$map['amount']));
                if(abs($signed)<0.005) continue;
                $description=trim((string)$this->col($cols,$map['description']));
                if($description==='') $description='Movimiento bancario';
                $reference=trim((string)$this->col($cols,$map['reference']));
                $balance=$map['balance']!==null?$this->parseMoney($this->col($cols,$map['balance'])):null;
                $rowHash=hash('sha256',implode('|',array($accountId,$date,$this->normalizeHeader($description),$this->normalizeHeader($reference),number_format($signed,2,'.',''),$balance===null?'':number_format($balance,2,'.',''))));
                $insert->execute(array($importId,$accountId,$date,mb_substr($description,0,500),$reference!==''?mb_substr($reference,0,190):null,$debit,$credit,$signed,$balance,$rowHash));
                $inserted += $insert->rowCount()>0?1:0;
                if($minDate===null||$date<$minDate)$minDate=$date;if($maxDate===null||$date>$maxDate)$maxDate=$date;
            }
            if($inserted===0) throw new RuntimeException('No se importaron filas nuevas. Revisa el formato o duplicados.');
            $this->pdo->prepare('UPDATE treasury_statement_imports SET statement_from=?,statement_to=?,rows_total=? WHERE id=?')->execute(array($minDate,$maxDate,$inserted,$importId));
            $this->audit('statement_import',$importId,'import',null,array('rows'=>$inserted,'from'=>$minDate,'to'=>$maxDate),$userId);
            $this->pdo->commit();
            return array('id'=>$importId,'rows_imported'=>$inserted,'from'=>$minDate,'to'=>$maxDate);
        }catch(Throwable $e){if($this->pdo->inTransaction())$this->pdo->rollBack();throw $e;}
    }

    public function autoReconcile($accountId, $userId)
    {
        $accountId = $this->requiredAccount($accountId);
        $stmt=$this->pdo->prepare("SELECT * FROM treasury_statement_rows WHERE cash_account_id=? AND reconciliation_status='pending' ORDER BY row_date,id LIMIT 1000");
        $stmt->execute(array($accountId));$matched=0;$ambiguous=0;
        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
            $direction=(float)$row['signed_amount']>=0?'received':'paid';$amount=abs((float)$row['signed_amount']);
            $pay=$this->pdo->prepare("SELECT p.id FROM billing_payments p WHERE p.environment='production' AND COALESCE(p.is_voided,0)=0 AND p.cash_account_id=? AND p.direction=? AND ABS(p.amount-?)<0.01 AND p.payment_date BETWEEN DATE_SUB(?,INTERVAL 3 DAY) AND DATE_ADD(?,INTERVAL 3 DAY) AND NOT EXISTS (SELECT 1 FROM treasury_statement_rows sr WHERE sr.matched_payment_id=p.id AND sr.reconciliation_status='matched') ORDER BY ABS(DATEDIFF(p.payment_date,?)),p.id LIMIT 2");
            $pay->execute(array($accountId,$direction,$amount,$row['row_date'],$row['row_date'],$row['row_date']));$candidates=$pay->fetchAll(PDO::FETCH_COLUMN);
            if(count($candidates)===1){$this->matchStatementRow((int)$row['id'],'payment',(int)$candidates[0],$userId);$matched++;continue;}elseif(count($candidates)>1){$ambiguous++;continue;}
            $manualDirection=$direction==='received'?'inflow':'outflow';
            $manual=$this->pdo->prepare("SELECT m.id FROM treasury_manual_movements m WHERE m.cash_account_id=? AND m.direction=? AND m.status='confirmed' AND ABS(m.amount-?)<0.01 AND m.movement_date BETWEEN DATE_SUB(?,INTERVAL 3 DAY) AND DATE_ADD(?,INTERVAL 3 DAY) AND NOT EXISTS (SELECT 1 FROM treasury_statement_rows sr WHERE sr.matched_manual_movement_id=m.id AND sr.reconciliation_status IN ('matched','converted')) ORDER BY ABS(DATEDIFF(m.movement_date,?)),m.id LIMIT 2");
            $manual->execute(array($accountId,$manualDirection,$amount,$row['row_date'],$row['row_date'],$row['row_date']));$candidates=$manual->fetchAll(PDO::FETCH_COLUMN);
            if(count($candidates)===1){$this->matchStatementRow((int)$row['id'],'manual',(int)$candidates[0],$userId);$matched++;}elseif(count($candidates)>1){$ambiguous++;}
        }
        $this->refreshImportStatuses();
        return array('matched'=>$matched,'ambiguous'=>$ambiguous);
    }

    public function convertStatementRow($rowId, $userId)
    {
        $row=$this->findOne("SELECT * FROM treasury_statement_rows WHERE id=? AND reconciliation_status='pending'",array((int)$rowId));
        if(!$row)throw new RuntimeException('La fila bancaria ya fue procesada o no existe.');
        $movement=$this->saveManualMovement(array('cash_account_id'=>$row['cash_account_id'],'movement_date'=>$row['row_date'],'direction'=>(float)$row['signed_amount']>=0?'inflow':'outflow','cash_activity'=>'operations','amount'=>abs((float)$row['signed_amount']),'description'=>$row['description'],'external_reference'=>$row['reference'],'status'=>'confirmed','notes'=>'Creado desde cartola bancaria #'.$row['import_id']),$userId);
        $this->pdo->prepare("UPDATE treasury_statement_rows SET reconciliation_status='converted',matched_manual_movement_id=?,matched_at=NOW(),matched_by=?,updated_at=NOW() WHERE id=?")->execute(array($movement['id'],$userId ?: null,$row['id']));
        $this->refreshImportStatuses();
        $this->audit('statement_row',(int)$row['id'],'convert',$row,array('movement_id'=>$movement['id']),$userId);
        return $movement;
    }

    public function ignoreStatementRow($rowId, $userId, $notes = '')
    {
        $row=$this->findOne("SELECT * FROM treasury_statement_rows WHERE id=? AND reconciliation_status='pending'",array((int)$rowId));
        if(!$row)throw new RuntimeException('La fila bancaria ya fue procesada o no existe.');
        $this->pdo->prepare("UPDATE treasury_statement_rows SET reconciliation_status='ignored',notes=?,matched_at=NOW(),matched_by=?,updated_at=NOW() WHERE id=?")->execute(array($this->nullableText($notes,1000),$userId ?: null,$row['id']));
        $this->refreshImportStatuses();
        $this->audit('statement_row',(int)$row['id'],'ignore',$row,null,$userId);
    }

    private function forecast($start, array $scenario, $accountId)
    {
        $end=date('Y-m-d',strtotime($start.' +90 days'));
        $asOf=date('Y-m-d',strtotime($start.' -1 day'));
        $balance=$this->balanceAsOf($asOf,$accountId);
        $weeks=array();
        for($i=0;$i<13;$i++){
            $weekStart=date('Y-m-d',strtotime($start.' +'.($i*7).' days'));
            $weekEnd=date('Y-m-d',strtotime($weekStart.' +6 days'));
            $weeks[$i]=array('index'=>$i+1,'start'=>$weekStart,'end'=>$weekEnd,'realized_inflows'=>0.0,'realized_outflows'=>0.0,'committed_inflows'=>0.0,'committed_outflows'=>0.0,'scenario_inflows'=>0.0,'scenario_outflows'=>0.0,'net_flow'=>0.0,'ending_balance'=>0.0,'items'=>0);
        }
        $this->addRealizedToWeeks($weeks,$start,$end,$accountId);
        $coverage=array('receivables'=>0,'payables'=>0,'costs'=>0,'recurring_rules'=>0,'purchase_orders_excluded'=>0);
        $this->addCollectionsToWeeks($weeks,$start,$end,$coverage);
        $this->addCostsToWeeks($weeks,$start,$end,$coverage);
        $this->addRecurringRulesToWeeks($weeks,$start,$end,$coverage);
        $this->addScenarioToWeeks($weeks,$start,$end,$scenario);

        $running=(float)$balance['amount'];$min=$running;$deficit=0;$firstDeficit=null;$outflows=0;
        foreach($weeks as &$week){
            $week['net_flow']=$week['realized_inflows']-$week['realized_outflows']+$week['committed_inflows']-$week['committed_outflows']+$week['scenario_inflows']-$week['scenario_outflows'];
            $running+=$week['net_flow'];$week['ending_balance']=$running;$min=min($min,$running);$outflows+=$week['realized_outflows']+$week['committed_outflows']+$week['scenario_outflows'];
            if($running<0){$deficit++;if($firstDeficit===null)$firstDeficit=$week['start'];}
        }unset($week);
        $monthlyOutflow=$outflows>0?($outflows/13*4.345):0;
        $runway=$monthlyOutflow>0?max(0,min(99,(float)$balance['amount']/$monthlyOutflow)):null;
        $qualityData=$this->quality($accountId,$asOf,$balance,$end);
        $quality=$qualityData['validated']?'validated':'provisional';
        $freshness=$this->latestTreasuryFreshness();
        return array(
            'weeks'=>array_values($weeks),
            'quality'=>$quality,
            'coverage'=>$coverage,
            'summary'=>array(
                'opening_balance'=>(float)$balance['amount'],'current_balance'=>(float)$this->balanceAsOf(date('Y-m-d'),$accountId)['amount'],'minimum_balance'=>$min,
                'runway_months'=>$runway,'deficit_weeks'=>$deficit,'first_deficit_date'=>$firstDeficit,'projected_end_balance'=>$running,
                'projected_outflows'=>$outflows,'quality_note'=>$qualityData['note'],'freshness'=>$freshness,
            ),
        );
    }

    private function addRealizedToWeeks(array &$weeks,$start,$end,$accountId)
    {
        $where=$accountId?' AND p.cash_account_id=?':'';$params=array($start,$end);if($accountId)$params[]=$accountId;
        $stmt=$this->pdo->prepare("SELECT p.payment_date AS item_date,CASE WHEN p.direction='received' THEN 'inflow' ELSE 'outflow' END AS direction,p.amount FROM billing_payments p WHERE p.environment='production' AND COALESCE(p.is_voided,0)=0 AND p.payment_date BETWEEN ? AND ?{$where}");$stmt->execute($params);
        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row)$this->bucket($weeks,$start,$row['item_date'],'realized_'.$row['direction'].'s',(float)$row['amount']);
        $where=$accountId?' AND cash_account_id=?':'';$params=array($start,$end);if($accountId)$params[]=$accountId;
        $stmt=$this->pdo->prepare("SELECT movement_date AS item_date,direction,amount FROM treasury_manual_movements WHERE status='confirmed' AND movement_date BETWEEN ? AND ?{$where}");$stmt->execute($params);
        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row)$this->bucket($weeks,$start,$row['item_date'],'realized_'.$row['direction'].'s',(float)$row['amount']);
    }

    private function addCollectionsToWeeks(array &$weeks,$start,$end,array &$coverage)
    {
        $stmt=$this->pdo->prepare("SELECT id,direction,COALESCE(due_date,issue_date,CURDATE()) AS item_date,balance_amount FROM billing_collections WHERE environment='production' AND balance_amount>0 AND collection_status NOT IN ('pagado','anulado','incobrable','rechazado') AND COALESCE(due_date,issue_date,CURDATE())<=?");
        $stmt->execute(array($end));
        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){
            $date=$this->clampDate($row['item_date'],$start,$end);$key=$row['direction']==='receivable'?'committed_inflows':'committed_outflows';$this->bucket($weeks,$start,$date,$key,(float)$row['balance_amount']);$coverage[$row['direction']==='receivable'?'receivables':'payables']++;
        }
    }

    private function addCostsToWeeks(array &$weeks,$start,$end,array &$coverage)
    {
        if(!$this->tableExists('cost_entries'))return;
        $stmt=$this->pdo->prepare("SELECT id,COALESCE(due_date,payment_date,entry_date) AS item_date,gross_amount FROM cost_entries WHERE status='approved' AND gross_amount>0 AND linked_collection_id IS NULL AND COALESCE(due_date,payment_date,entry_date)<=?");
        $stmt->execute(array($end));
        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){$this->bucket($weeks,$start,$this->clampDate($row['item_date'],$start,$end),'committed_outflows',(float)$row['gross_amount']);$coverage['costs']++;}
        if($this->tableExists('billing_purchase_orders')){$coverage['purchase_orders_excluded']=(int)$this->pdo->query("SELECT COUNT(*) FROM billing_purchase_orders WHERE status IN ('issued','sent')")->fetchColumn();}
    }

    private function addRecurringRulesToWeeks(array &$weeks,$start,$end,array &$coverage)
    {
        if(!$this->tableExists('cost_recurring_rules'))return;
        $generated=array();
        $stmt=$this->pdo->prepare("SELECT recurring_rule_id,period FROM cost_entries WHERE recurring_rule_id IS NOT NULL AND status<>'void' AND period BETWEEN ? AND ?");
        $stmt->execute(array(substr($start,0,7),substr($end,0,7)));
        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row)$generated[$row['recurring_rule_id'].':'.$row['period']]=true;
        $rules=$this->pdo->query("SELECT id,start_period,end_period,frequency,day_of_month,gross_amount FROM cost_recurring_rules WHERE is_active=1 AND gross_amount>0")->fetchAll(PDO::FETCH_ASSOC);
        $cursor=new DateTime(substr($start,0,7).'-01');$last=new DateTime(substr($end,0,7).'-01');$periods=array();
        while($cursor<=$last){$periods[]=$cursor->format('Y-m');$cursor->modify('+1 month');}
        $intervals=array('monthly'=>1,'quarterly'=>3,'semiannual'=>6,'annual'=>12);
        foreach($rules as $rule){
            $interval=$intervals[$rule['frequency']]??1;
            foreach($periods as $period){
                if($period<$rule['start_period']||(!empty($rule['end_period'])&&$period>$rule['end_period'])||isset($generated[$rule['id'].':'.$period]))continue;
                $startParts=explode('-',$rule['start_period']);$periodParts=explode('-',$period);$months=((int)$periodParts[0]*12+(int)$periodParts[1])-((int)$startParts[0]*12+(int)$startParts[1]);
                if($months<0||$months%$interval!==0)continue;
                $date=$period.'-'.sprintf('%02d',max(1,min(28,(int)$rule['day_of_month'])));if($date>$end)continue;
                $this->bucket($weeks,$start,$this->clampDate($date,$start,$end),'committed_outflows',(float)$rule['gross_amount']);$coverage['recurring_rules']++;
            }
        }
    }

    private function addScenarioToWeeks(array &$weeks,$start,$end,array $scenario)
    {
        $stmt=$this->pdo->prepare("SELECT item_type,expected_date,amount,probability_pct FROM treasury_scenario_items WHERE scenario_id=? AND status='active' AND expected_date BETWEEN ? AND ?");$stmt->execute(array($scenario['id'],$start,$end));
        foreach($stmt->fetchAll(PDO::FETCH_ASSOC) as $row){$factor=$row['item_type']==='inflow'?(float)$scenario['inflow_factor']:(float)$scenario['outflow_factor'];$amount=(float)$row['amount']*((float)$row['probability_pct']/100)*$factor;$this->bucket($weeks,$start,$row['expected_date'],'scenario_'.$row['item_type'].'s',$amount);}
    }

    private function balanceAsOf($date,$accountId)
    {
        $accounts=$this->accounts();$total=0;$details=array();
        foreach($accounts as $account){if($accountId && (int)$account['id']!==$accountId)continue;$opening=$this->findOne('SELECT * FROM treasury_opening_balances WHERE cash_account_id=? AND balance_date<=? ORDER BY balance_date DESC,id DESC LIMIT 1',array($account['id'],$date));$amount=$opening?(float)$opening['amount']:0;$from=$opening?date('Y-m-d',strtotime($opening['balance_date'].' +1 day')):'1900-01-01';
            $stmt=$this->pdo->prepare("SELECT COALESCE(SUM(CASE WHEN direction='received' THEN amount ELSE -amount END),0) FROM billing_payments WHERE environment='production' AND COALESCE(is_voided,0)=0 AND cash_account_id=? AND payment_date BETWEEN ? AND ?");$stmt->execute(array($account['id'],$from,$date));$amount+=(float)$stmt->fetchColumn();
            $stmt=$this->pdo->prepare("SELECT COALESCE(SUM(CASE WHEN direction='inflow' THEN amount ELSE -amount END),0) FROM treasury_manual_movements WHERE status='confirmed' AND cash_account_id=? AND movement_date BETWEEN ? AND ?");$stmt->execute(array($account['id'],$from,$date));$amount+=(float)$stmt->fetchColumn();
            $total+=$amount;$details[]=array('account_id'=>(int)$account['id'],'amount'=>$amount,'opening'=>$opening);
        }return array('amount'=>$total,'details'=>$details);
    }

    private function quality($accountId,$asOf,array $balance,$forecastEnd)
    {
        $missing=0;$unreconciled=0;foreach($balance['details'] as $row){if(!$row['opening'])$missing++;elseif(empty($row['opening']['is_reconciled']))$unreconciled++;}
        $where=$accountId?' AND cash_account_id='.(int)$accountId:'';
        $pending=(int)$this->pdo->query("SELECT COUNT(*) FROM treasury_statement_rows WHERE reconciliation_status='pending'{$where}")->fetchColumn();
        $unassigned=(int)$this->pdo->query("SELECT COUNT(*) FROM billing_payments WHERE environment='production' AND COALESCE(is_voided,0)=0 AND cash_account_id IS NULL")->fetchColumn();
        $issues=array();if($missing)$issues[]=$missing.' cuenta(s) sin saldo inicial';if($unreconciled)$issues[]=$unreconciled.' saldo(s) inicial(es) no conciliado(s)';if($pending)$issues[]=$pending.' fila(s) bancarias pendientes';if($unassigned)$issues[]=$unassigned.' pago(s) sin cuenta de caja';
        $confirmation=$this->coverageConfirmation();$missingCoverage=array();foreach(array('payroll_complete'=>'remuneraciones','taxes_complete'=>'impuestos','financing_complete'=>'financiamiento','other_expenses_complete'=>'otros gastos') as $key=>$label){if(empty($confirmation[$key]))$missingCoverage[]=$label;}if($missingCoverage)$issues[]='cobertura por confirmar: '.implode(', ',$missingCoverage);if(empty($confirmation['confirmed_through'])||$confirmation['confirmed_through']<$forecastEnd)$issues[]='cobertura no confirmada hasta el fin de las 13 semanas';
        return array('validated'=>empty($issues),'missing_openings'=>$missing,'unreconciled_openings'=>$unreconciled,'pending_rows'=>$pending,'unassigned_payments'=>$unassigned,'note'=>empty($issues)?'Cobertura conciliada para las cuentas seleccionadas.':'Provisional: '.implode('; ',$issues).'.');
    }

    private function ledger($accountId)
    {
        $where=$accountId?' AND p.cash_account_id=?':'';$stmt=$this->pdo->prepare("SELECT p.id,p.payment_date AS movement_date,CASE WHEN p.direction='received' THEN 'inflow' ELSE 'outflow' END AS direction,p.amount,p.payment_method AS detail,p.external_reference,p.cash_account_id,a.account_name,COALESCE(c.document_number,'') AS document_number,'billing' AS source,p.finance_center_id,fc.center_name FROM billing_payments p LEFT JOIN billing_cash_accounts a ON a.id=p.cash_account_id LEFT JOIN billing_collections c ON c.id=p.collection_id LEFT JOIN finance_centers fc ON fc.id=p.finance_center_id WHERE p.environment='production' AND COALESCE(p.is_voided,0)=0{$where} ORDER BY p.payment_date DESC,p.id DESC LIMIT 350");$stmt->execute($accountId?array($accountId):array());$rows=$stmt->fetchAll(PDO::FETCH_ASSOC);
        $where=$accountId?' AND m.cash_account_id=?':'';$stmt=$this->pdo->prepare("SELECT m.id,m.movement_date,m.direction,m.amount,m.description AS detail,m.external_reference,m.cash_account_id,a.account_name,'' AS document_number,'manual' AS source,m.finance_center_id,fc.center_name,m.status,m.transfer_group FROM treasury_manual_movements m JOIN billing_cash_accounts a ON a.id=m.cash_account_id LEFT JOIN finance_centers fc ON fc.id=m.finance_center_id WHERE m.status='confirmed'{$where} ORDER BY m.movement_date DESC,m.id DESC LIMIT 350");$stmt->execute($accountId?array($accountId):array());$rows=array_merge($rows,$stmt->fetchAll(PDO::FETCH_ASSOC));
        usort($rows,function($a,$b){$cmp=strcmp($b['movement_date'],$a['movement_date']);return $cmp!==0?$cmp:((int)$b['id']<=> (int)$a['id']);});return array_slice($rows,0,500);
    }

    private function statementWorkspace($accountId)
    {
        $where=$accountId?' WHERE r.cash_account_id=?':'';$stmt=$this->pdo->prepare("SELECT r.*,a.account_name FROM treasury_statement_rows r JOIN billing_cash_accounts a ON a.id=r.cash_account_id{$where} ORDER BY (r.reconciliation_status='pending') DESC,r.row_date DESC,r.id DESC LIMIT 400");$stmt->execute($accountId?array($accountId):array());$rows=$stmt->fetchAll(PDO::FETCH_ASSOC);
        $where=$accountId?' WHERE i.cash_account_id=?':'';$stmt=$this->pdo->prepare("SELECT i.*,a.account_name,(SELECT COUNT(*) FROM treasury_statement_rows r WHERE r.import_id=i.id AND r.reconciliation_status='pending') AS pending_count FROM treasury_statement_imports i JOIN billing_cash_accounts a ON a.id=i.cash_account_id{$where} ORDER BY i.created_at DESC LIMIT 50");$stmt->execute($accountId?array($accountId):array());$imports=$stmt->fetchAll(PDO::FETCH_ASSOC);
        $summary=array('pending'=>0,'matched'=>0,'converted'=>0,'ignored'=>0);foreach($rows as $row){if(isset($summary[$row['reconciliation_status']]))$summary[$row['reconciliation_status']]++;}
        return array('rows'=>$rows,'imports'=>$imports,'summary'=>$summary);
    }

    private function openingBalances(){return $this->pdo->query("SELECT b.*,a.account_name,a.account_type FROM treasury_opening_balances b JOIN billing_cash_accounts a ON a.id=b.cash_account_id ORDER BY b.balance_date DESC,b.id DESC LIMIT 200")->fetchAll(PDO::FETCH_ASSOC);}
    private function coverageConfirmation(){if(!$this->tableExists('treasury_forecast_coverage'))return array('payroll_complete'=>0,'taxes_complete'=>0,'financing_complete'=>0,'other_expenses_complete'=>0,'confirmed_through'=>null,'notes'=>null);$row=$this->findOne('SELECT * FROM treasury_forecast_coverage WHERE id=1');return $row?:array('payroll_complete'=>0,'taxes_complete'=>0,'financing_complete'=>0,'other_expenses_complete'=>0,'confirmed_through'=>null,'notes'=>null);}
    private function accountPositions($date){$rows=array();foreach($this->accounts() as $account){$balance=$this->balanceAsOf($date,(int)$account['id']);$detail=$balance['details'][0]??array('opening'=>null);$rows[]=array('account_id'=>(int)$account['id'],'amount'=>(float)$balance['amount'],'opening'=>$detail['opening']);}return $rows;}
    private function scenarioItems(){return $this->pdo->query("SELECT i.*,s.scenario_key,s.scenario_name,fc.center_name FROM treasury_scenario_items i JOIN treasury_scenarios s ON s.id=i.scenario_id LEFT JOIN finance_centers fc ON fc.id=i.finance_center_id WHERE i.status='active' ORDER BY i.expected_date,i.id")->fetchAll(PDO::FETCH_ASSOC);}
    private function accounts(){return $this->pdo->query("SELECT id,account_key,account_name,account_type,currency_code,bank_name,account_number,is_default FROM billing_cash_accounts WHERE is_active=1 ORDER BY is_default DESC,sort_order,id")->fetchAll(PDO::FETCH_ASSOC);}
    private function financeCenters(){if(!$this->tableExists('finance_centers'))return array();return $this->pdo->query("SELECT id,center_code,center_name FROM finance_centers WHERE is_active=1 ORDER BY center_name")->fetchAll(PDO::FETCH_ASSOC);}
    private function scenarios(){$rows=$this->pdo->query("SELECT * FROM treasury_scenarios WHERE is_active=1 ORDER BY FIELD(scenario_key,'conservative','base','optimistic'),id")->fetchAll(PDO::FETCH_ASSOC);$out=array();foreach($rows as $row)$out[$row['scenario_key']]=$row;return $out;}

    private function matchStatementRow($rowId,$type,$targetId,$userId){$sql=$type==='payment'?"UPDATE treasury_statement_rows SET reconciliation_status='matched',matched_payment_id=?,matched_at=NOW(),matched_by=?,updated_at=NOW() WHERE id=? AND reconciliation_status='pending'":"UPDATE treasury_statement_rows SET reconciliation_status='matched',matched_manual_movement_id=?,matched_at=NOW(),matched_by=?,updated_at=NOW() WHERE id=? AND reconciliation_status='pending'";$this->pdo->prepare($sql)->execute(array($targetId,$userId ?: null,$rowId));}
    private function refreshImportStatuses(){$this->pdo->exec("UPDATE treasury_statement_imports i SET status=CASE WHEN EXISTS (SELECT 1 FROM treasury_statement_rows r WHERE r.import_id=i.id AND r.reconciliation_status='pending') THEN 'imported' ELSE 'reconciled' END,updated_at=NOW()");}
    private function latestTreasuryFreshness(){$values=array();foreach(array("SELECT MAX(updated_at) FROM treasury_opening_balances","SELECT MAX(updated_at) FROM treasury_manual_movements","SELECT MAX(updated_at) FROM treasury_statement_rows","SELECT MAX(created_at) FROM billing_payments WHERE environment='production' AND COALESCE(is_voided,0)=0") as $sql){$v=$this->pdo->query($sql)->fetchColumn();if($v)$values[]=$v;}rsort($values);return $values?$values[0]:null;}

    private function bucket(array &$weeks,$start,$date,$key,$amount){$index=(int)floor((strtotime($date)-strtotime($start))/604800);if($index<0)$index=0;if($index>12)$index=12;$weeks[$index][$key]+=$amount;$weeks[$index]['items']++;}
    private function clampDate($date,$start,$end){if(!$date||$date<$start)return $start;if($date>$end)return $end;return $date;}
    private function monday($date){$day=(int)date('N',strtotime($date));return date('Y-m-d',strtotime($date.' -'.($day-1).' days'));}
    private function validDate($date,$fallback){$date=trim((string)$date);if(preg_match('/^\d{4}-\d{2}-\d{2}$/',$date)&&strtotime($date)!==false)return $date;return $fallback;}
    private function decimal($value,$allowNegative){if($value===null||$value==='')throw new InvalidArgumentException('Ingresa un importe válido.');$number=(float)str_replace(',','.',(string)$value);if(!$allowNegative&&$number<0)$number=abs($number);return round($number,2);}
    private function nullableText($value,$max){$value=trim((string)$value);return $value===''?null:mb_substr($value,0,$max);}
    private function requiredAccount($id){$id=(int)$id;if($id<=0||!$this->findOne('SELECT id FROM billing_cash_accounts WHERE id=? AND is_active=1',array($id)))throw new InvalidArgumentException('Selecciona una cuenta de caja o banco activa.');return $id;}
    private function findOne($sql,array $params=array()){$stmt=$this->pdo->prepare($sql);$stmt->execute($params);$row=$stmt->fetch(PDO::FETCH_ASSOC);return $row?:null;}
    private function tableExists($name){$stmt=$this->pdo->prepare("SELECT COUNT(*) FROM information_schema.tables WHERE table_schema=DATABASE() AND table_name=?");$stmt->execute(array($name));return (int)$stmt->fetchColumn()===1;}
    private function assertInstalled(){if(!$this->tableExists('treasury_opening_balances')||!$this->tableExists('treasury_statement_rows')||!$this->tableExists('treasury_forecast_coverage'))throw new RuntimeException('La fase 3 de tesorería aún no está instalada. Ejecuta la migración v9.0.');}
    private function audit($type,$id,$action,$before,$after,$userId){$stmt=$this->pdo->prepare('INSERT INTO treasury_audit_log (entity_type,entity_id,action_key,before_json,after_json,actor_user_id) VALUES (?,?,?,?,?,?)');$stmt->execute(array($type,$id?:null,$action,$before?json_encode($before,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES):null,$after?json_encode($after,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES):null,$userId ?: null));}

    public function normalizeHeader($value){$value=trim(mb_strtolower((string)$value,'UTF-8'));$ascii=@iconv('UTF-8','ASCII//TRANSLIT//IGNORE',$value);if($ascii!==false)$value=$ascii;return preg_replace('/[^a-z0-9]+/','_',$value);}
    private function statementHeaderMap(array $headers){$aliases=array('date'=>array('fecha','date','fecha_movimiento','fecha_contable'),'description'=>array('descripcion','glosa','detalle','description','concepto'),'reference'=>array('referencia','reference','nro_documento','documento','numero'),'debit'=>array('cargo','cargos','debito','debit'),'credit'=>array('abono','abonos','credito','credit'),'amount'=>array('monto','importe','amount','movimiento'),'balance'=>array('saldo','balance','saldo_disponible'));$out=array();foreach($aliases as $key=>$names){$out[$key]=null;foreach($names as $name){$i=array_search($name,$headers,true);if($i!==false){$out[$key]=$i;break;}}}return $out;}
    private function col(array $cols,$index){return $index===null?'':($cols[$index]??'');}
    private function parseStatementDate($value){$value=trim((string)$value);foreach(array('d/m/Y','d-m-Y','Y-m-d','d/m/y') as $format){$date=DateTime::createFromFormat('!'.$format,$value);if($date&&$date->format($format)===$value)return $date->format('Y-m-d');}$time=strtotime($value);return $time?date('Y-m-d',$time):null;}
    private function parseMoney($value){$value=preg_replace('/[^0-9,\.\-\(\)]/','',(string)$value);$negative=strpos($value,'(')!==false;$value=str_replace(array('(',')'),'',$value);if(strpos($value,',')!==false){$value=str_replace('.','',$value);$value=str_replace(',','.',$value);}elseif(substr_count($value,'.')>1){$value=str_replace('.','',$value);}elseif(preg_match('/\.\d{3}$/',$value)){$value=str_replace('.','',$value);} $number=(float)$value;return $negative?-abs($number):$number;}
}
