<?php

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

class BillingSupplierResolverService
{
    private $pdo;
    private $columns = array();

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

    public function resolveEnvironment($environment = 'sandbox', $limit = 200)
    {
        $environment = $this->normalizeEnvironment($environment);
        if (!$this->tableExists('facto_inbox_documents') || !$this->columnExists('facto_inbox_documents', 'supplier_customer_id')) {
            return array('processed' => 0, 'matched' => 0, 'created' => 0, 'missing_rut' => 0, 'errors' => 0);
        }

        $limit = max(1, min(500, (int)$limit));
        $where = array('environment = ?');
        $params = array($environment);

        if ($this->columnExists('facto_inbox_documents', 'supplier_resolution_status')) {
            $where[] = "(supplier_customer_id IS NULL OR supplier_resolution_status IN ('pending','error','missing_rut') OR payment_terms_id IS NULL OR payment_due_date IS NULL)";
        } else {
            $where[] = 'supplier_customer_id IS NULL';
        }

        $stmt = $this->pdo->prepare('SELECT id FROM facto_inbox_documents WHERE ' . implode(' AND ', $where) . ' ORDER BY document_date DESC, id DESC LIMIT ' . $limit);
        $stmt->execute($params);
        $ids = $stmt->fetchAll(PDO::FETCH_COLUMN);

        $stats = array('processed' => 0, 'matched' => 0, 'created' => 0, 'missing_rut' => 0, 'errors' => 0);
        foreach ($ids as $id) {
            try {
                $result = $this->resolveDocumentByLocalId((int)$id, $environment, true);
                $stats['processed']++;
                $status = isset($result['status']) ? $result['status'] : '';
                if ($status === 'matched_existing') {
                    $stats['matched']++;
                } elseif ($status === 'created_from_dte') {
                    $stats['created']++;
                } elseif ($status === 'missing_rut') {
                    $stats['missing_rut']++;
                }
            } catch (Throwable $e) {
                $stats['errors']++;
            }
        }

        return $stats;
    }

    public function resolveDocumentByInboxId($inboxDocumentId, $environment = 'sandbox', $createIfMissing = true)
    {
        $environment = $this->normalizeEnvironment($environment);
        $stmt = $this->pdo->prepare('SELECT id FROM facto_inbox_documents WHERE environment = ? AND inbox_document_id = ? LIMIT 1');
        $stmt->execute(array($environment, (string)$inboxDocumentId));
        $id = $stmt->fetchColumn();
        if (!$id) {
            throw new Exception('Documento recibido no encontrado para resolver proveedor.');
        }
        return $this->resolveDocumentByLocalId((int)$id, $environment, $createIfMissing);
    }

    public function resolveDocumentByLocalId($localId, $environment = 'sandbox', $createIfMissing = true)
    {
        $environment = $this->normalizeEnvironment($environment);
        $doc = $this->getInboxDocument((int)$localId, $environment);
        if (!$doc) {
            throw new Exception('Documento recibido no encontrado.');
        }

        $rut = $this->normalizeRut(isset($doc['issuer_tax_id_code']) ? $doc['issuer_tax_id_code'] : '');
        if (!$this->isUsableRut($rut)) {
            $this->updateResolution($doc, null, 'missing_rut', 'DTE sin RUT de emisor usable.', null, null, false);
            return array('status' => 'missing_rut', 'supplier' => null, 'document_id' => (int)$doc['id']);
        }

        $supplier = $this->findSupplierByRut($rut);
        $status = 'matched_existing';
        if (!$supplier) {
            if (!$createIfMissing) {
                $this->updateResolution($doc, null, 'missing_rut', 'Proveedor no existe para RUT ' . $rut . '.', null, null, false);
                return array('status' => 'missing_rut', 'supplier' => null, 'document_id' => (int)$doc['id']);
            }
            $supplier = $this->createSupplierFromDocument($doc, $rut);
            $status = 'created_from_dte';
        } else {
            $this->markSupplierFlags((int)$supplier['id'], $doc);
            $supplier = $this->getSupplierById((int)$supplier['id']);
        }

        $term = $this->resolvePaymentTermForSupplier($supplier);
        $centerId = $this->resolveCostCenterForSupplier($supplier);
        $dueDate = $this->calculateDueDate($doc, $term);

        $this->updateResolution($doc, (int)$supplier['id'], $status, $status === 'created_from_dte' ? 'Proveedor creado automaticamente desde DTE recibido.' : 'Proveedor vinculado por RUT.', $term, $centerId, true, $dueDate);
        $this->syncExistingPayableCollection($doc, (int)$supplier['id'], $term, $centerId, $dueDate);

        return array(
            'status' => $status,
            'supplier' => $this->getSupplierById((int)$supplier['id']),
            'document_id' => (int)$doc['id'],
            'payment_term' => $term,
            'finance_center_id' => $centerId,
            'due_date' => $dueDate
        );
    }

    public function listPaymentTerms()
    {
        if (!$this->tableExists('billing_payment_terms')) {
            return array();
        }
        $stmt = $this->pdo->query("SELECT id, term_key, term_label, days, is_default FROM billing_payment_terms WHERE is_active = 1 ORDER BY is_default DESC, days ASC, term_label ASC");
        return $stmt ? $stmt->fetchAll(PDO::FETCH_ASSOC) : array();
    }

    public function getDefaultPaymentTerm()
    {
        if (!$this->tableExists('billing_payment_terms')) {
            return array('id' => null, 'term_key' => 'contado', 'term_label' => 'Contado', 'days' => 0);
        }
        $stmt = $this->pdo->query("SELECT id, term_key, term_label, days FROM billing_payment_terms WHERE is_active = 1 ORDER BY is_default DESC, days ASC LIMIT 1");
        $term = $stmt ? $stmt->fetch(PDO::FETCH_ASSOC) : null;
        if (!$term) {
            return array('id' => null, 'term_key' => 'contado', 'term_label' => 'Contado', 'days' => 0);
        }
        return $term;
    }

    private function getInboxDocument($localId, $environment)
    {
        $stmt = $this->pdo->prepare('SELECT * FROM facto_inbox_documents WHERE id = ? AND environment = ? LIMIT 1');
        $stmt->execute(array((int)$localId, $environment));
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    private function findSupplierByRut($normalizedRut)
    {
        $sql = "SELECT * FROM crm_customers WHERE deleted_at IS NULL AND UPPER(REPLACE(REPLACE(customer_tax_id, '.', ''), ' ', '')) = ? LIMIT 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($normalizedRut));
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    private function getSupplierById($id)
    {
        $stmt = $this->pdo->prepare('SELECT * FROM crm_customers WHERE id = ? LIMIT 1');
        $stmt->execute(array((int)$id));
        return $stmt->fetch(PDO::FETCH_ASSOC);
    }

    private function createSupplierFromDocument(array $doc, $rut)
    {
        $payload = $this->decodeRawPayload(isset($doc['raw_payload']) ? $doc['raw_payload'] : null);
        $name = trim((string)(isset($doc['issuer_name']) ? $doc['issuer_name'] : ''));
        if ($name === '') {
            $name = 'Proveedor ' . $rut;
        }
        $activity = $this->pickPayload($payload, array('issuer_activity', 'issuer_business_activity', 'issuer_giro', 'issuer.activity', 'issuer.giro', 'business_activity'));
        $address = $this->pickPayload($payload, array('issuer_address', 'issuer_direction', 'issuer.address', 'issuer.address_line', 'issuer_address_line', 'sender_address'));
        $district = $this->pickPayload($payload, array('issuer_district', 'issuer_commune', 'issuer.comuna', 'issuer.district', 'issuer_commune_name'));
        $city = $this->pickPayload($payload, array('issuer_city', 'issuer.city', 'issuer_city_name'));
        $email = $this->pickPayload($payload, array('issuer_email', 'issuer.email', 'sender_email', 'issuer_contact_email'));
        $phone = $this->pickPayload($payload, array('issuer_phone', 'issuer.phone', 'issuer_contact_phone'));
        $term = $this->getDefaultPaymentTerm();
        $paymentTermLabel = isset($term['term_label']) ? $term['term_label'] : 'Contado';

        $columns = array(
            'customer_name', 'commercial_name', 'legal_name', 'customer_tax_id', 'tax_id_type', 'email', 'phone', 'address', 'payment_terms', 'district', 'city', 'country', 'business_activity', 'notes', 'source'
        );
        $values = array(
            $name, $name, $name, $rut, 'CL-RUT', $email, $phone, $address, $paymentTermLabel, $district, $city, 'Chile', $activity,
            'Proveedor creado automaticamente desde DTE recibido. Inbox local ID: ' . (int)$doc['id'] . '. Folio: ' . (isset($doc['document_number']) ? $doc['document_number'] : ''),
            'dte_received'
        );

        if ($this->columnExists('crm_customers', 'is_supplier')) {
            $columns[] = 'is_supplier';
            $values[] = 1;
        }
        if ($this->columnExists('crm_customers', 'supplier_status')) {
            $columns[] = 'supplier_status';
            $values[] = 'active';
        }
        if ($this->columnExists('crm_customers', 'supplier_payment_terms_id')) {
            $columns[] = 'supplier_payment_terms_id';
            $values[] = isset($term['id']) ? $term['id'] : null;
        }
        if ($this->columnExists('crm_customers', 'supplier_default_cost_center_id')) {
            $columns[] = 'supplier_default_cost_center_id';
            $values[] = null;
        }
        if ($this->columnExists('crm_customers', 'supplier_created_from_inbox_document_id')) {
            $columns[] = 'supplier_created_from_inbox_document_id';
            $values[] = (int)$doc['id'];
        }
        if ($this->columnExists('crm_customers', 'supplier_last_inbox_document_id')) {
            $columns[] = 'supplier_last_inbox_document_id';
            $values[] = (int)$doc['id'];
        }
        if ($this->columnExists('crm_customers', 'supplier_auto_created_at')) {
            $columns[] = 'supplier_auto_created_at';
            $values[] = date('Y-m-d H:i:s');
        }

        $placeholders = implode(',', array_fill(0, count($columns), '?'));
        $sql = 'INSERT INTO crm_customers (' . implode(',', $columns) . ') VALUES (' . $placeholders . ')';
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($values);
        $id = (int)$this->pdo->lastInsertId();
        return $this->getSupplierById($id);
    }

    private function markSupplierFlags($supplierId, array $doc)
    {
        $sets = array();
        $params = array();
        if ($this->columnExists('crm_customers', 'is_supplier')) {
            $sets[] = 'is_supplier = 1';
        }
        if ($this->columnExists('crm_customers', 'supplier_status')) {
            $sets[] = "supplier_status = CASE WHEN supplier_status IS NULL OR supplier_status = '' THEN 'active' ELSE supplier_status END";
        }
        if ($this->columnExists('crm_customers', 'supplier_last_inbox_document_id')) {
            $sets[] = 'supplier_last_inbox_document_id = ?';
            $params[] = (int)$doc['id'];
        }
        if ($this->columnExists('crm_customers', 'supplier_payment_terms_id')) {
            $term = $this->inferPaymentTermFromText(isset($doc['payment_terms']) ? $doc['payment_terms'] : null);
            $sets[] = 'supplier_payment_terms_id = COALESCE(supplier_payment_terms_id, ?)';
            $params[] = $term && isset($term['id']) ? $term['id'] : (isset($this->getDefaultPaymentTerm()['id']) ? $this->getDefaultPaymentTerm()['id'] : null);
        }
        if (!$sets) {
            return;
        }
        $sets[] = 'updated_at = CURRENT_TIMESTAMP';
        $params[] = $supplierId;
        $stmt = $this->pdo->prepare('UPDATE crm_customers SET ' . implode(', ', $sets) . ' WHERE id = ?');
        $stmt->execute($params);
    }

    private function resolvePaymentTermForSupplier(array $supplier = null)
    {
        if (!$supplier) {
            return $this->getDefaultPaymentTerm();
        }
        if ($this->columnExists('crm_customers', 'supplier_payment_terms_id') && !empty($supplier['supplier_payment_terms_id'])) {
            $stmt = $this->pdo->prepare('SELECT id, term_key, term_label, days FROM billing_payment_terms WHERE id = ? AND is_active = 1 LIMIT 1');
            $stmt->execute(array((int)$supplier['supplier_payment_terms_id']));
            $term = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($term) {
                return $term;
            }
        }
        $text = '';
        if (isset($supplier['facto_payment_term']) && $supplier['facto_payment_term'] !== '') {
            $text = (string)$supplier['facto_payment_term'];
        } elseif (isset($supplier['payment_terms'])) {
            $text = (string)$supplier['payment_terms'];
        }
        $term = $this->inferPaymentTermFromText($text);
        return $term ?: $this->getDefaultPaymentTerm();
    }

    private function resolveCostCenterForSupplier(array $supplier = null)
    {
        if (!$supplier || !$this->columnExists('crm_customers', 'supplier_default_cost_center_id')) {
            return null;
        }
        return !empty($supplier['supplier_default_cost_center_id']) ? (int)$supplier['supplier_default_cost_center_id'] : null;
    }

    private function inferPaymentTermFromText($text)
    {
        if (!$this->tableExists('billing_payment_terms')) {
            return null;
        }
        $text = strtolower(trim((string)$text));
        $days = null;
        if ($text === '' || strpos($text, 'contado') !== false || $text === '0') {
            $days = 0;
        } elseif (preg_match('/(\d+)/', $text, $m)) {
            $days = (int)$m[1];
        }
        if ($days === null) {
            return null;
        }
        $stmt = $this->pdo->prepare('SELECT id, term_key, term_label, days FROM billing_payment_terms WHERE is_active = 1 AND days = ? ORDER BY is_default DESC LIMIT 1');
        $stmt->execute(array($days));
        $term = $stmt->fetch(PDO::FETCH_ASSOC);
        return $term ?: null;
    }

    private function calculateDueDate(array $doc, array $term = null)
    {
        $days = $term && isset($term['days']) ? (int)$term['days'] : 0;
        $base = null;
        if (!empty($doc['document_date'])) {
            $base = $doc['document_date'];
        } elseif (!empty($doc['receive_date'])) {
            $base = substr($doc['receive_date'], 0, 10);
        } else {
            $base = date('Y-m-d');
        }
        $ts = strtotime($base . ' +' . $days . ' days');
        return $ts ? date('Y-m-d', $ts) : null;
    }

    private function updateResolution(array $doc, $supplierId, $status, $notes, array $term = null, $centerId = null, $applyTerms = true, $dueDate = null)
    {
        $sets = array();
        $params = array();
        if ($this->columnExists('facto_inbox_documents', 'supplier_customer_id')) {
            $sets[] = 'supplier_customer_id = ?';
            $params[] = $supplierId;
        }
        if ($this->columnExists('facto_inbox_documents', 'supplier_resolution_status')) {
            $sets[] = 'supplier_resolution_status = ?';
            $params[] = $status;
        }
        if ($this->columnExists('facto_inbox_documents', 'supplier_resolution_notes')) {
            $sets[] = 'supplier_resolution_notes = ?';
            $params[] = $notes;
        }
        if ($applyTerms && $this->columnExists('facto_inbox_documents', 'payment_terms_id')) {
            $sets[] = 'payment_terms_id = COALESCE(payment_terms_id, ?)';
            $params[] = $term && isset($term['id']) ? $term['id'] : null;
        }
        if ($applyTerms && $this->columnExists('facto_inbox_documents', 'payment_due_date')) {
            $sets[] = 'payment_due_date = COALESCE(payment_due_date, ?)';
            $params[] = $dueDate;
        }
        if ($centerId && $this->columnExists('facto_inbox_documents', 'finance_center_id')) {
            $sets[] = 'finance_center_id = COALESCE(finance_center_id, ?)';
            $params[] = $centerId;
        }
        if ($this->columnExists('facto_inbox_documents', 'supplier_resolved_at')) {
            $sets[] = 'supplier_resolved_at = CURRENT_TIMESTAMP';
        }
        if ($sets) {
            $sets[] = 'updated_at = CURRENT_TIMESTAMP';
            $params[] = (int)$doc['id'];
            $stmt = $this->pdo->prepare('UPDATE facto_inbox_documents SET ' . implode(', ', $sets) . ' WHERE id = ?');
            $stmt->execute($params);
        }
    }

    private function syncExistingPayableCollection(array $doc, $supplierId, array $term = null, $centerId = null, $dueDate = null)
    {
        if (!$this->tableExists('billing_collections')) {
            return;
        }
        $stmt = $this->pdo->prepare("SELECT id FROM billing_collections WHERE environment = ? AND direction = 'payable' AND inbox_document_id = ? LIMIT 1");
        $stmt->execute(array($doc['environment'], $doc['inbox_document_id']));
        $id = $stmt->fetchColumn();
        if (!$id) {
            return;
        }
        $paymentTerms = $term && isset($term['term_label']) ? $term['term_label'] : null;
        $sql = "UPDATE billing_collections SET customer_id = COALESCE(?, customer_id), due_date = COALESCE(due_date, ?), payment_terms = COALESCE(payment_terms, ?), finance_center_id = COALESCE(finance_center_id, ?), updated_at = CURRENT_TIMESTAMP WHERE id = ?";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($supplierId, $dueDate, $paymentTerms, $centerId, $id));
    }

    private function decodeRawPayload($raw)
    {
        if (!$raw) {
            return array();
        }
        $decoded = json_decode($raw, true);
        return is_array($decoded) ? $decoded : array();
    }

    private function pickPayload(array $payload, array $keys)
    {
        foreach ($keys as $key) {
            $value = $this->getPayloadValue($payload, $key);
            if ($value !== null && $value !== '') {
                return is_scalar($value) ? (string)$value : null;
            }
        }
        return null;
    }

    private function getPayloadValue(array $payload, $path)
    {
        $parts = explode('.', $path);
        $cursor = $payload;
        foreach ($parts as $part) {
            if (!is_array($cursor) || !array_key_exists($part, $cursor)) {
                return null;
            }
            $cursor = $cursor[$part];
        }
        return $cursor;
    }

    private function normalizeRut($rut)
    {
        $rut = strtoupper(trim((string)$rut));
        $rut = str_replace(array('.', ' ', '\t', '\n', '\r'), '', $rut);
        if ($rut === '' || $rut === '-' || $rut === '0') {
            return '';
        }
        if (strpos($rut, '-') === false && strlen($rut) > 1) {
            $rut = substr($rut, 0, -1) . '-' . substr($rut, -1);
        }
        return $rut;
    }

    private function isUsableRut($rut)
    {
        if ($rut === '' || $rut === '-') {
            return false;
        }
        return (bool)preg_match('/^[0-9]{5,9}-[0-9K]$/', $rut);
    }

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

    private function tableExists($table)
    {
        $stmt = $this->pdo->prepare('SHOW TABLES LIKE ?');
        $stmt->execute(array($table));
        return (bool)$stmt->fetchColumn();
    }

    private function columnExists($table, $column)
    {
        $key = $table . '.' . $column;
        if (array_key_exists($key, $this->columns)) {
            return $this->columns[$key];
        }
        $stmt = $this->pdo->prepare('SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = ? AND COLUMN_NAME = ? LIMIT 1');
        $stmt->execute(array($table, $column));
        $this->columns[$key] = (bool)$stmt->fetchColumn();
        return $this->columns[$key];
    }
}
