<?php

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

class BillingSupplierAgingService
{
    private $pdo;

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

    public function summary(array $filters = array())
    {
        $environment = $this->normalizeEnvironment(isset($filters['environment']) ? $filters['environment'] : 'sandbox');
        billing_assert_runtime_operation('read', $environment, 'Aging de proveedores');
        // v6.12: aging calcula buckets en memoria y no ejecuta mantenimiento al leer.
        $rows = $this->fetchOpenDocuments($filters);
        $groups = $this->groupRows($rows);

        return array(
            'kpis' => $this->kpisFromGroups($groups),
            'rows' => array_values($groups),
            'filters' => array(
                'environment' => $environment,
                'search' => isset($filters['search']) ? trim((string)$filters['search']) : '',
                'bucket' => isset($filters['bucket']) ? trim((string)$filters['bucket']) : 'all',
                'center_id' => isset($filters['center_id']) ? trim((string)$filters['center_id']) : 'all',
                'limit' => isset($filters['limit']) ? max(10, min(5000, (int)$filters['limit'])) : 5000,
            ),
        );
    }

    public function detail($supplierKey, array $filters = array())
    {
        $supplierKey = trim((string)$supplierKey);
        if ($supplierKey === '') {
            throw new Exception('Proveedor requerido.');
        }

        $environment = $this->normalizeEnvironment(isset($filters['environment']) ? $filters['environment'] : 'sandbox');
        billing_assert_runtime_operation('read', $environment, 'Detalle de aging de proveedor');
        // v6.12: aging calcula buckets en memoria y no ejecuta mantenimiento al leer.
        $filters['bucket'] = 'all';
        $rows = $this->fetchOpenDocuments($filters);
        $selected = array();

        foreach ($rows as $row) {
            if ($this->supplierKey($row) !== $supplierKey) {
                continue;
            }
            $selected[] = $row;
        }

        usort($selected, function ($a, $b) {
            $dateA = isset($a['due_date']) && $a['due_date'] ? $a['due_date'] : '9999-12-31';
            $dateB = isset($b['due_date']) && $b['due_date'] ? $b['due_date'] : '9999-12-31';
            if ($dateA === $dateB) {
                return strcmp((string)$a['document_number'], (string)$b['document_number']);
            }
            return strcmp($dateA, $dateB);
        });

        $groups = $this->groupRows($selected);
        $supplier = isset($groups[$supplierKey]) ? $groups[$supplierKey] : null;

        return array(
            'supplier' => $supplier,
            'rows' => $selected,
            'environment' => $environment,
        );
    }

    public function listFinanceCenters()
    {
        $service = new BillingPayablesService($this->pdo);
        return $service->listFinanceCenters();
    }

    private function fetchOpenDocuments(array $filters)
    {
        $environment = $this->normalizeEnvironment(isset($filters['environment']) ? $filters['environment'] : 'sandbox');
        $search = isset($filters['search']) ? trim((string)$filters['search']) : '';
        $bucket = isset($filters['bucket']) ? trim((string)$filters['bucket']) : 'all';
        $centerId = isset($filters['center_id']) ? trim((string)$filters['center_id']) : 'all';
        $limit = isset($filters['limit']) ? max(10, min(5000, (int)$filters['limit'])) : 5000;

        $where = array('d.environment = ?');
        $params = array($environment);

        if ($search !== '') {
            $where[] = '(COALESCE(s.customer_name, d.issuer_name) LIKE ? OR COALESCE(s.customer_tax_id, d.issuer_tax_id_code) LIKE ? OR d.document_number LIKE ?)';
            $like = '%' . $search . '%';
            $params[] = $like;
            $params[] = $like;
            $params[] = $like;
        }

        if ($centerId !== '' && $centerId !== 'all') {
            if ($centerId === 'none') {
                $where[] = 'COALESCE(d.finance_center_id, s.supplier_default_cost_center_id) IS NULL';
            } else {
                $where[] = 'COALESCE(d.finance_center_id, s.supplier_default_cost_center_id) = ?';
                $params[] = (int)$centerId;
            }
        }

        $sql = "
            SELECT
                d.id,
                d.environment,
                d.inbox_document_id,
                d.document_id,
                d.document_date,
                d.receive_date,
                d.issuer_name,
                d.issuer_tax_id_code,
                d.document_type_id,
                d.document_type_taxbureau,
                d.document_number,
                d.net_amount,
                d.taxes_amount,
                d.total_amount,
                d.supplier_customer_id AS supplier_id,
                d.finance_center_id,
                d.payment_status,
                d.payment_due_date,
                d.payment_paid_date,
                d.payment_terms_id,
                s.customer_name AS supplier_name,
                s.customer_tax_id AS supplier_tax_id,
                s.supplier_default_cost_center_id,
                s.supplier_payment_terms_id,
                COALESCE(d.finance_center_id, s.supplier_default_cost_center_id) AS effective_finance_center_id,
                COALESCE(fc_doc.center_name, fc_supplier.center_name) AS finance_center_name,
                c.id AS collection_id,
                c.due_date AS collection_due_date,
                c.total_amount AS collection_total_amount,
                COALESCE(c.paid_amount, 0) AS paid_amount,
                c.balance_amount AS collection_balance_amount,
                c.collection_status,
                c.last_payment_date,
                pt.term_label AS payment_term_label,
                COALESCE(pt.days, 0) AS payment_term_days
            FROM facto_inbox_documents d
            LEFT JOIN crm_customers s ON s.id = d.supplier_customer_id
            LEFT JOIN finance_centers fc_doc ON fc_doc.id = d.finance_center_id
            LEFT JOIN finance_centers fc_supplier ON fc_supplier.id = s.supplier_default_cost_center_id
            LEFT JOIN billing_payment_terms pt ON pt.id = COALESCE(d.payment_terms_id, s.supplier_payment_terms_id)
            LEFT JOIN billing_collections c ON c.environment = d.environment
                AND c.direction = 'payable'
                AND c.inbox_document_id = d.inbox_document_id
            WHERE " . implode(' AND ', $where) . "
            ORDER BY d.document_date ASC, d.id ASC
            LIMIT " . (int)$limit;

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $rawRows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $rows = array();
        foreach ($rawRows as $row) {
            $rawStatus = $row['collection_status'] ?: $row['payment_status'];
            if (in_array($rawStatus, array('rechazado', 'anulado'), true)) {
                continue;
            }

            $total = $row['collection_total_amount'] !== null
                ? (float)$row['collection_total_amount']
                : (float)$row['total_amount'];
            $paid = max((float)$row['paid_amount'], 0.0);
            $balance = $row['collection_balance_amount'] !== null
                ? max((float)$row['collection_balance_amount'], 0.0)
                : max($total - $paid, 0.0);

            if ($balance <= 0.5) {
                continue;
            }

            $dueDate = $this->resolveDueDate($row);
            $daysOverdue = $this->daysOverdue($dueDate);
            $daysToDue = $this->daysToDue($dueDate);
            $status = $daysOverdue > 0 ? 'vencido' : ($paid > 0 ? 'parcial' : 'por_pagar');

            $row['aging_total_amount'] = $total;
            $row['paid_amount'] = $paid;
            $row['balance_amount'] = $balance;
            $row['due_date'] = $dueDate;
            $row['days_overdue'] = $daysOverdue;
            $row['days_to_due'] = $daysToDue;
            $row['financial_status'] = $status;

            if (!$this->matchesBucket($row, $bucket)) {
                continue;
            }

            $rows[] = $row;
        }

        return $rows;
    }

    private function groupRows(array $rows)
    {
        $groups = array();
        foreach ($rows as $row) {
            $key = $this->supplierKey($row);
            if (!isset($groups[$key])) {
                $groups[$key] = array(
                    'supplier_key' => $key,
                    'supplier_id' => !empty($row['supplier_id']) ? (int)$row['supplier_id'] : 0,
                    'supplier_name' => $this->firstNonEmpty(array($row['supplier_name'], $row['issuer_name'], 'Proveedor sin identificar')),
                    'supplier_tax_id' => $this->firstNonEmpty(array($row['supplier_tax_id'], $row['issuer_tax_id_code'], '-')),
                    'documents_count' => 0,
                    'open_balance' => 0.0,
                    'overdue_balance' => 0.0,
                    'current_balance' => 0.0,
                    'due_7_balance' => 0.0,
                    'bucket_0_30' => 0.0,
                    'bucket_31_60' => 0.0,
                    'bucket_61_90' => 0.0,
                    'bucket_90_plus' => 0.0,
                    'oldest_due_date' => null,
                    'next_due_date' => null,
                    'max_days_overdue' => 0,
                    'unlinked_supplier' => empty($row['supplier_id']) ? 1 : 0,
                    'without_center_balance' => 0.0,
                    'center_ids' => array(),
                    'center_names' => array(),
                    'finance_center_id' => null,
                    'finance_center_name' => 'Sin centro',
                );
            }

            $balance = (float)$row['balance_amount'];
            $daysOverdue = (int)$row['days_overdue'];
            $daysToDue = (int)$row['days_to_due'];
            $dueDate = $row['due_date'];

            $groups[$key]['documents_count']++;
            $groups[$key]['open_balance'] += $balance;

            if ($daysOverdue > 0) {
                $groups[$key]['overdue_balance'] += $balance;
                $groups[$key]['max_days_overdue'] = max($groups[$key]['max_days_overdue'], $daysOverdue);
                if ($daysOverdue <= 30) {
                    $groups[$key]['bucket_0_30'] += $balance;
                } elseif ($daysOverdue <= 60) {
                    $groups[$key]['bucket_31_60'] += $balance;
                } elseif ($daysOverdue <= 90) {
                    $groups[$key]['bucket_61_90'] += $balance;
                } else {
                    $groups[$key]['bucket_90_plus'] += $balance;
                }
            } else {
                $groups[$key]['current_balance'] += $balance;
                if ($daysToDue >= 0 && $daysToDue <= 7) {
                    $groups[$key]['due_7_balance'] += $balance;
                }
            }

            if ($dueDate) {
                if ($groups[$key]['oldest_due_date'] === null || $dueDate < $groups[$key]['oldest_due_date']) {
                    $groups[$key]['oldest_due_date'] = $dueDate;
                }
                if ($daysOverdue === 0 && ($groups[$key]['next_due_date'] === null || $dueDate < $groups[$key]['next_due_date'])) {
                    $groups[$key]['next_due_date'] = $dueDate;
                }
            }

            $centerId = isset($row['effective_finance_center_id']) ? $row['effective_finance_center_id'] : null;
            $centerName = $this->firstNonEmpty(array(isset($row['finance_center_name']) ? $row['finance_center_name'] : '', 'Sin centro'));
            if ($centerId) {
                $groups[$key]['center_ids'][(string)$centerId] = (int)$centerId;
                $groups[$key]['center_names'][(string)$centerId] = $centerName;
            } else {
                $groups[$key]['without_center_balance'] += $balance;
            }
        }

        foreach ($groups as &$group) {
            $centerCount = count($group['center_names']);
            if ($centerCount === 1 && $group['without_center_balance'] <= 0.5) {
                $group['finance_center_id'] = reset($group['center_ids']);
                $group['finance_center_name'] = reset($group['center_names']);
            } elseif ($centerCount > 0 && $group['without_center_balance'] > 0.5) {
                $group['finance_center_name'] = 'Mixtos / sin centro';
            } elseif ($centerCount > 1) {
                $group['finance_center_name'] = 'Multiples centros';
            } else {
                $group['finance_center_name'] = 'Sin centro';
            }
            unset($group['center_ids'], $group['center_names']);
        }
        unset($group);

        uasort($groups, function ($a, $b) {
            $overdueCompare = (float)$b['overdue_balance'] <=> (float)$a['overdue_balance'];
            if ($overdueCompare !== 0) {
                return $overdueCompare;
            }
            $openCompare = (float)$b['open_balance'] <=> (float)$a['open_balance'];
            if ($openCompare !== 0) {
                return $openCompare;
            }
            return strcasecmp((string)$a['supplier_name'], (string)$b['supplier_name']);
        });

        return $groups;
    }

    private function matchesBucket(array $row, $bucket)
    {
        if ($bucket === '' || $bucket === 'all') {
            return true;
        }
        if ($bucket === 'overdue') {
            return (int)$row['days_overdue'] > 0;
        }
        if ($bucket === 'current') {
            return (int)$row['days_overdue'] === 0;
        }
        if ($bucket === 'due_7') {
            return (int)$row['days_overdue'] === 0 && (int)$row['days_to_due'] >= 0 && (int)$row['days_to_due'] <= 7;
        }
        if ($bucket === 'no_supplier') {
            return empty($row['supplier_id']);
        }
        if ($bucket === 'no_center') {
            return empty($row['effective_finance_center_id']);
        }
        return true;
    }

    private function kpisFromGroups(array $groups)
    {
        $kpis = array(
            'suppliers_count' => count($groups),
            'open_balance' => 0.0,
            'overdue_balance' => 0.0,
            'current_balance' => 0.0,
            'due_7_balance' => 0.0,
            'documents_count' => 0,
            'unlinked_suppliers' => 0,
            'without_center_balance' => 0.0,
        );

        foreach ($groups as $group) {
            $kpis['open_balance'] += (float)$group['open_balance'];
            $kpis['overdue_balance'] += (float)$group['overdue_balance'];
            $kpis['current_balance'] += (float)$group['current_balance'];
            $kpis['due_7_balance'] += (float)$group['due_7_balance'];
            $kpis['documents_count'] += (int)$group['documents_count'];
            $kpis['without_center_balance'] += (float)$group['without_center_balance'];
            if (!empty($group['unlinked_supplier'])) {
                $kpis['unlinked_suppliers']++;
            }
        }

        return $kpis;
    }

    private function refreshBaseState($environment)
    {
        if (!billing_runtime_gate_allows('finance', $environment)) {
            return;
        }
        try {
            $service = new BillingPayablesService($this->pdo);
            $service->refreshAgingState($environment);
        } catch (Throwable $e) {
            error_log('BillingSupplierAgingService refreshBaseState: ' . $e->getMessage());
        }
    }

    private function resolveDueDate(array $row)
    {
        // La fecha se calcula siempre desde la condicion de pago vigente del proveedor.
        // No se usa un vencimiento manual previo porque Fase 6.3 lo dejo como dato calculado.
        $baseDate = $this->normalizeDate(isset($row['document_date']) ? $row['document_date'] : null);
        if (!$baseDate) {
            $baseDate = $this->normalizeDate(isset($row['receive_date']) ? $row['receive_date'] : null);
        }
        if (!$baseDate) {
            return date('Y-m-d', strtotime('-1 day'));
        }

        $days = isset($row['payment_term_days']) ? max(0, (int)$row['payment_term_days']) : 0;
        $timestamp = strtotime($baseDate . ' +' . $days . ' days');
        return $timestamp ? date('Y-m-d', $timestamp) : $baseDate;
    }

    private function daysOverdue($dueDate)
    {
        $dueDate = $this->normalizeDate($dueDate);
        if (!$dueDate) {
            return 1;
        }
        $today = new DateTimeImmutable(date('Y-m-d'));
        $due = new DateTimeImmutable($dueDate);
        if ($due >= $today) {
            return 0;
        }
        return (int)$today->diff($due)->days;
    }

    private function daysToDue($dueDate)
    {
        $dueDate = $this->normalizeDate($dueDate);
        if (!$dueDate) {
            return -1;
        }
        $today = new DateTimeImmutable(date('Y-m-d'));
        $due = new DateTimeImmutable($dueDate);
        if ($due < $today) {
            return -1;
        }
        return (int)$today->diff($due)->days;
    }

    private function supplierKey(array $row)
    {
        if (!empty($row['supplier_id'])) {
            return 'supplier:' . (int)$row['supplier_id'];
        }
        $taxId = $this->normalizeTaxId(isset($row['issuer_tax_id_code']) ? $row['issuer_tax_id_code'] : '');
        if ($taxId !== '') {
            return 'rut:' . $taxId;
        }
        return 'name:' . md5((string)(isset($row['issuer_name']) ? $row['issuer_name'] : 'sin_proveedor'));
    }

    private function firstNonEmpty(array $values)
    {
        foreach ($values as $value) {
            $value = trim((string)$value);
            if ($value !== '') {
                return $value;
            }
        }
        return '';
    }

    private function normalizeEnvironment($environment)
    {
        return $environment === 'production' ? 'production' : 'sandbox';
    }

    private function normalizeDate($value)
    {
        $value = trim((string)$value);
        if ($value === '' || $value === '0000-00-00') {
            return null;
        }
        $timestamp = strtotime($value);
        return $timestamp ? date('Y-m-d', $timestamp) : null;
    }

    private function normalizeTaxId($value)
    {
        $value = strtoupper(trim((string)$value));
        return preg_replace('/[^0-9K]/', '', $value);
    }
}
