<?php

require_once __DIR__ . '/../../core/database.php';
require_once __DIR__ . '/../documents/PurchaseOrderPdfRenderer.php';
require_once __DIR__ . '/../notifications/EmailService.php';
require_once __DIR__ . '/BillingManualDocumentService.php';

/**
 * Órdenes de compra internas ICYLAB.
 *
 * Una OC es un documento comercial y no se envía a FACTO ni al SII. La
 * numeración, el PDF, la trazabilidad y el correo pertenecen a ICYLAB OS.
 */
class BillingPurchaseOrderService
{
    private $pdo;
    private $manual;
    private $appRoot;

    public function __construct(?PDO $pdo = null)
    {
        $this->pdo = $pdo ?: db();
        $this->manual = new BillingManualDocumentService($this->pdo);
        $this->appRoot = realpath(dirname(__DIR__, 2));
    }

    public function bootstrap(): array
    {
        $settings = $this->companySettings();
        return array(
            'payment_terms' => $this->pdo->query("SELECT id,term_key,term_label,days FROM billing_payment_terms WHERE is_active=1 ORDER BY sort_order,days,id")->fetchAll(PDO::FETCH_ASSOC),
            'cost_centers' => $this->pdo->query("SELECT id,center_code,center_name FROM finance_centers WHERE is_active=1 AND center_type IN ('cost','both') ORDER BY center_name")->fetchAll(PDO::FETCH_ASSOC),
            'currencies' => $this->pdo->query("SELECT currency_code,currency_name FROM billing_currencies WHERE is_active=1 ORDER BY CASE WHEN currency_code='CLP' THEN 0 ELSE 1 END,currency_code")->fetchAll(PDO::FETCH_ASSOC),
            'company' => array(
                'name' => $settings['company_name'] ?? 'ICYLAB SPA',
                'delivery_address' => $settings['company_address'] ?? '',
                'delivery_district' => $settings['company_comuna'] ?? '',
                'delivery_city' => $settings['company_ciudad'] ?? '',
                'purchase_order_default_terms' => $settings['purchase_order_default_terms'] ?? '',
            ),
        );
    }

    public function searchSuppliers(string $query = '', int $limit = 25): array
    {
        $limit = max(1, min(60, $limit));
        $where = "deleted_at IS NULL AND (is_supplier=1 OR counterparty_type IN ('supplier','both')) AND COALESCE(supplier_status,'active')<>'blocked'";
        $params = array();
        $query = trim($query);
        if ($query !== '') {
            $where .= ' AND (customer_name LIKE ? OR commercial_name LIKE ? OR legal_name LIKE ? OR customer_tax_id LIKE ? OR email LIKE ? OR contact_name LIKE ?)';
            $like = '%' . $query . '%';
            $params = array_fill(0, 6, $like);
        }
        $sql = "SELECT id,customer_name,commercial_name,legal_name,customer_tax_id,email,email_dte,contact_name,phone,address,district,city,region,country,business_activity,supplier_status,supplier_default_cost_center_id,supplier_payment_terms_id,payment_terms FROM crm_customers WHERE {$where} ORDER BY COALESCE(NULLIF(commercial_name,''),NULLIF(legal_name,''),customer_name) LIMIT {$limit}";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function searchProducts(string $query = '', int $limit = 30): array
    {
        return $this->manual->searchProducts($query, $limit);
    }

    public function listOrders(array $filters = array(), int $limit = 200): array
    {
        $limit = max(1, min(500, $limit));
        $where = array('1=1');
        $params = array();
        $query = trim((string)($filters['q'] ?? ''));
        if ($query !== '') {
            $where[] = '(po.order_number LIKE ? OR po.supplier_legal_name LIKE ? OR po.supplier_tax_id LIKE ?)';
            $like = '%' . $query . '%';
            array_push($params, $like, $like, $like);
        }
        $status = strtolower(trim((string)($filters['status'] ?? '')));
        if (in_array($status, array('issued', 'sent', 'cancelled'), true)) {
            $where[] = 'po.status=?';
            $params[] = $status;
        }
        $sql = "SELECT po.*,fc.center_name AS cost_center_name,u.name AS requested_by_name FROM billing_purchase_orders po LEFT JOIN finance_centers fc ON fc.id=po.cost_center_id LEFT JOIN users u ON u.id=po.requested_by_user_id WHERE " . implode(' AND ', $where) . " ORDER BY po.issue_date DESC,po.id DESC LIMIT {$limit}";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function getOrder(int $orderId): array
    {
        $order = $this->loadOrder($orderId);
        if (!$order) throw new RuntimeException('Orden de compra no encontrada.');
        $order['items'] = $this->loadItems($orderId);
        return $order;
    }

    public function create(array $input, ?int $userId = null): array
    {
        $data = $this->validate($input);
        $supplier = $this->supplier((int)$data['supplier_id']);
        if (!$supplier) throw new InvalidArgumentException('Selecciona un proveedor activo.');

        $this->pdo->beginTransaction();
        $absolutePath = null;
        try {
            $orderNumber = $this->nextNumber((int)substr($data['issue_date'], 0, 4));
            $stmt = $this->pdo->prepare("INSERT INTO billing_purchase_orders
                (order_number,status,supplier_customer_id,issue_date,expected_delivery_date,currency_code,payment_terms,cost_center_id,
                 supplier_legal_name,supplier_tax_id,supplier_email,supplier_contact_name,supplier_phone,supplier_address,
                 delivery_address,delivery_district,delivery_city,net_amount,discount_amount,tax_amount,total_amount,
                 notes,terms_conditions,requested_by_user_id,created_by)
                VALUES (?,'generating',?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
            $stmt->execute(array(
                $orderNumber,
                (int)$supplier['id'],
                $data['issue_date'],
                $data['expected_delivery_date'] ?: null,
                $data['currency_code'],
                $data['payment_terms'],
                $data['cost_center_id'] ?: null,
                $this->supplierName($supplier),
                trim((string)$supplier['customer_tax_id']),
                $data['supplier_email'] ?: ($supplier['email_dte'] ?: $supplier['email']),
                trim((string)$supplier['contact_name']),
                trim((string)$supplier['phone']),
                trim(implode(', ', array_filter(array($supplier['address'], $supplier['district'], $supplier['city'])))),
                $data['delivery_address'],
                $data['delivery_district'],
                $data['delivery_city'],
                $data['net_amount'],
                $data['discount_amount'],
                $data['tax_amount'],
                $data['total_amount'],
                $data['notes'],
                $data['terms_conditions'],
                $userId ?: null,
                $userId ?: null,
            ));
            $orderId = (int)$this->pdo->lastInsertId();

            $lineStmt = $this->pdo->prepare("INSERT INTO billing_purchase_order_items
                (purchase_order_id,product_id,sku,description,quantity,unit_measure,unit_price,discount_percentage,line_net_amount,tax_rate,tax_amount,total_amount,sort_order)
                VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)");
            foreach ($data['items'] as $index => $item) {
                $lineStmt->execute(array(
                    $orderId,
                    $item['product_id'] ?: null,
                    $item['sku'],
                    $item['description'],
                    $item['quantity'],
                    $item['unit_measure'],
                    $item['unit_price'],
                    $item['discount_percentage'],
                    $item['line_net_amount'],
                    $item['tax_rate'],
                    $item['tax_amount'],
                    $item['total_amount'],
                    $index + 1,
                ));
            }

            $order = $this->loadOrder($orderId);
            $items = $this->loadItems($orderId);
            $pdf = (new PurchaseOrderPdfRenderer())->render($order, $items, $this->companySettings());
            list($absolutePath, $relativePath) = $this->pdfDestination($orderNumber, $data['issue_date']);
            $this->writeFile($absolutePath, $pdf);
            $this->pdo->prepare("UPDATE billing_purchase_orders SET status='issued',pdf_file_path=?,issued_at=NOW(),updated_at=NOW() WHERE id=?")
                ->execute(array($relativePath, $orderId));
            $this->pdo->commit();

            return array(
                'id' => $orderId,
                'order_number' => $orderNumber,
                'status' => 'issued',
                'supplier' => $this->supplierName($supplier),
                'supplier_email' => $data['supplier_email'] ?: ($supplier['email_dte'] ?: $supplier['email']),
                'currency_code' => $data['currency_code'],
                'total_amount' => $data['total_amount'],
                'pdf_url' => '/modules/billing/purchase_order_file.php?id=' . $orderId,
            );
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            if ($absolutePath && is_file($absolutePath)) @unlink($absolutePath);
            throw $e;
        }
    }

    public function sendEmail(int $orderId, array $input, ?int $userId = null): array
    {
        $order = $this->getOrder($orderId);
        if ($order['status'] === 'cancelled') throw new RuntimeException('No es posible enviar una orden anulada.');
        $to = trim((string)($input['to'] ?? $order['supplier_email']));
        if (!filter_var($to, FILTER_VALIDATE_EMAIL)) throw new InvalidArgumentException('Indica un email de proveedor válido.');
        $subject = trim((string)($input['subject'] ?? '')) ?: 'Orden de compra ' . $order['order_number'] . ' · ICYLAB';
        $body = trim((string)($input['body'] ?? ''));
        if ($body === '') {
            $body = '<p>Hola,</p><p>Adjuntamos la orden de compra <strong>' . $this->esc($order['order_number']) . '</strong> emitida por ICYLAB.</p><p>Total: <strong>' . $this->esc($order['currency_code']) . ' ' . number_format((float)$order['total_amount'], $order['currency_code'] === 'CLP' ? 0 : 2, ',', '.') . '</strong>.</p><p>Por favor confirma la recepción y condiciones de entrega.</p><p>Saludos,<br>ICYLAB</p>';
        }
        $absolute = $this->regeneratePdf($order);

        $log = $this->pdo->prepare("INSERT INTO billing_purchase_order_emails (purchase_order_id,recipient_email,subject,body,status,sent_by) VALUES (?,?,?,?, 'pending',?)");
        $log->execute(array($orderId, $to, $subject, $body, $userId ?: null));
        $logId = (int)$this->pdo->lastInsertId();
        try {
            (new EmailService())->send($to, $subject, $body, '', array(array(
                'path' => $absolute,
                'name' => $order['order_number'] . '.pdf',
                'mime' => 'application/pdf',
            )));
            $this->pdo->prepare("UPDATE billing_purchase_order_emails SET status='sent',sent_at=NOW() WHERE id=?")->execute(array($logId));
            $this->pdo->prepare("UPDATE billing_purchase_orders SET status='sent',supplier_email=?,sent_to_email=?,sent_at=NOW(),sent_by_user_id=?,updated_at=NOW() WHERE id=?")
                ->execute(array($to, $to, $userId ?: null, $orderId));
        } catch (Throwable $e) {
            $this->pdo->prepare("UPDATE billing_purchase_order_emails SET status='error',error_message=? WHERE id=?")
                ->execute(array(substr($e->getMessage(), 0, 2000), $logId));
            throw $e;
        }
        return array('id' => $orderId, 'order_number' => $order['order_number'], 'status' => 'sent', 'sent_to' => $to);
    }

    public function resolveOrderPdf(int $orderId): array
    {
        $order = $this->loadOrder($orderId);
        if (!$order) throw new RuntimeException('Orden de compra no encontrada.');
        return array('path' => $this->regeneratePdf($order), 'order_number' => $order['order_number']);
    }

    private function regeneratePdf(array $order): string
    {
        $items = $this->loadItems((int)$order['id']);
        if (!$items) throw new RuntimeException('La orden de compra no contiene productos.');
        $pdf = (new PurchaseOrderPdfRenderer())->render($order, $items, $this->companySettings());
        list($absolutePath, $relativePath) = $this->pdfDestination((string)$order['order_number'], (string)$order['issue_date']);
        $this->writeFile($absolutePath, $pdf);
        $this->pdo->prepare('UPDATE billing_purchase_orders SET pdf_file_path=?,updated_at=NOW() WHERE id=?')
            ->execute(array($relativePath, (int)$order['id']));
        return $absolutePath;
    }

    private function validate(array $input): array
    {
        $supplierId = (int)($input['supplier_id'] ?? 0);
        if ($supplierId <= 0) throw new InvalidArgumentException('Selecciona un proveedor.');
        $issueDate = $this->date((string)($input['issue_date'] ?? date('Y-m-d')), 'Fecha de emisión');
        $deliveryDate = trim((string)($input['expected_delivery_date'] ?? ''));
        if ($deliveryDate !== '') {
            $deliveryDate = $this->date($deliveryDate, 'Fecha de entrega');
            if ($deliveryDate < $issueDate) throw new InvalidArgumentException('La fecha de entrega no puede ser anterior a la emisión.');
        }
        $currency = strtoupper(trim((string)($input['currency_code'] ?? 'CLP')));
        if (!preg_match('/^[A-Z]{3}$/', $currency)) throw new InvalidArgumentException('Moneda inválida.');
        $currencyStmt = $this->pdo->prepare('SELECT COUNT(*) FROM billing_currencies WHERE currency_code=? AND is_active=1');
        $currencyStmt->execute(array($currency));
        if ((int)$currencyStmt->fetchColumn() < 1) throw new InvalidArgumentException('La moneda seleccionada no está habilitada.');

        $costCenterId = (int)($input['cost_center_id'] ?? 0);
        if ($costCenterId > 0) {
            $centerStmt = $this->pdo->prepare("SELECT COUNT(*) FROM finance_centers WHERE id=? AND is_active=1 AND center_type IN ('cost','both')");
            $centerStmt->execute(array($costCenterId));
            if ((int)$centerStmt->fetchColumn() !== 1) throw new InvalidArgumentException('El centro de costo seleccionado no está disponible.');
        }

        $rawItems = $input['items'] ?? array();
        if (!is_array($rawItems) || !$rawItems || count($rawItems) > 100) throw new InvalidArgumentException('Agrega entre 1 y 100 productos.');
        $items = array();
        $net = 0.0;
        $discount = 0.0;
        $tax = 0.0;
        $total = 0.0;
        foreach ($rawItems as $row) {
            if (!is_array($row)) continue;
            $description = $this->text($row['description'] ?? '', 500);
            if ($description === '') throw new InvalidArgumentException('Cada línea debe tener una descripción.');
            $quantity = round((float)($row['quantity'] ?? 0), 3);
            $unitPrice = round((float)($row['unit_price'] ?? 0), 6);
            $discountPct = round((float)($row['discount_percentage'] ?? 0), 2);
            if ($quantity <= 0 || $unitPrice < 0 || $discountPct < 0 || $discountPct > 100) throw new InvalidArgumentException('Revisa cantidad, precio y descuento de las líneas.');
            $base = round($quantity * $unitPrice, 2);
            $lineDiscount = round($base * $discountPct / 100, 2);
            $lineNet = round($base - $lineDiscount, 2);
            $taxRate = !empty($row['tax_exempt']) ? 0.0 : 19.0;
            $lineTax = round($lineNet * $taxRate / 100, 2);
            $lineTotal = round($lineNet + $lineTax, 2);
            $items[] = array(
                'product_id' => (int)($row['product_id'] ?? 0),
                'sku' => $this->text($row['sku'] ?? '', 80),
                'description' => $description,
                'quantity' => $quantity,
                'unit_measure' => $this->text($row['unit_measure'] ?? 'UN', 20) ?: 'UN',
                'unit_price' => $unitPrice,
                'discount_percentage' => $discountPct,
                'line_net_amount' => $lineNet,
                'tax_rate' => $taxRate,
                'tax_amount' => $lineTax,
                'total_amount' => $lineTotal,
            );
            $net += $lineNet;
            $discount += $lineDiscount;
            $tax += $lineTax;
            $total += $lineTotal;
        }
        if (!$items || $total <= 0) throw new InvalidArgumentException('La orden debe tener un total mayor que cero.');

        return array(
            'supplier_id' => $supplierId,
            'supplier_email' => $this->text($input['supplier_email'] ?? '', 190),
            'issue_date' => $issueDate,
            'expected_delivery_date' => $deliveryDate,
            'currency_code' => $currency,
            'payment_terms' => $this->text($input['payment_terms'] ?? '', 160),
            'cost_center_id' => $costCenterId,
            'delivery_address' => $this->text($input['delivery_address'] ?? '', 255),
            'delivery_district' => $this->text($input['delivery_district'] ?? '', 100),
            'delivery_city' => $this->text($input['delivery_city'] ?? '', 100),
            'notes' => $this->text($input['notes'] ?? '', 1200),
            'terms_conditions' => $this->text($input['terms_conditions'] ?? '', 2000),
            'items' => $items,
            'net_amount' => round($net, 2),
            'discount_amount' => round($discount, 2),
            'tax_amount' => round($tax, 2),
            'total_amount' => round($total, 2),
        );
    }

    private function nextNumber(int $year): string
    {
        $this->pdo->prepare('INSERT INTO billing_purchase_order_sequences (sequence_year,last_number) VALUES (?,0) ON DUPLICATE KEY UPDATE sequence_year=VALUES(sequence_year)')->execute(array($year));
        $stmt = $this->pdo->prepare('SELECT last_number FROM billing_purchase_order_sequences WHERE sequence_year=? FOR UPDATE');
        $stmt->execute(array($year));
        $number = (int)$stmt->fetchColumn() + 1;
        $this->pdo->prepare('UPDATE billing_purchase_order_sequences SET last_number=?,updated_at=NOW() WHERE sequence_year=?')->execute(array($number, $year));
        return sprintf('OC-%04d-%05d', $year, $number);
    }

    private function supplier(int $supplierId)
    {
        $stmt = $this->pdo->prepare("SELECT * FROM crm_customers WHERE id=? AND deleted_at IS NULL AND (is_supplier=1 OR counterparty_type IN ('supplier','both')) AND COALESCE(supplier_status,'active')<>'blocked' LIMIT 1");
        $stmt->execute(array($supplierId));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    private function loadOrder(int $orderId)
    {
        $stmt = $this->pdo->prepare("SELECT po.*,fc.center_name AS cost_center_name,u.name AS requested_by_name,supplier.country AS supplier_country FROM billing_purchase_orders po LEFT JOIN finance_centers fc ON fc.id=po.cost_center_id LEFT JOIN users u ON u.id=po.requested_by_user_id LEFT JOIN crm_customers supplier ON supplier.id=po.supplier_customer_id WHERE po.id=? LIMIT 1");
        $stmt->execute(array($orderId));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    private function loadItems(int $orderId): array
    {
        $stmt = $this->pdo->prepare('SELECT * FROM billing_purchase_order_items WHERE purchase_order_id=? ORDER BY sort_order,id');
        $stmt->execute(array($orderId));
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    private function companySettings(): array
    {
        $row = $this->pdo->query('SELECT * FROM system_settings WHERE id=1 LIMIT 1')->fetch(PDO::FETCH_ASSOC);
        return $row ?: array();
    }

    private function pdfDestination(string $orderNumber, string $issueDate): array
    {
        if (!$this->appRoot) throw new RuntimeException('No fue posible resolver la carpeta de la aplicación.');
        $year = substr($issueDate, 0, 4);
        $directory = $this->appRoot . '/storage/billing/purchase_orders/' . $year;
        if (!is_dir($directory) && !mkdir($directory, 0770, true) && !is_dir($directory)) throw new RuntimeException('No fue posible crear storage para órdenes de compra.');
        $filename = preg_replace('/[^A-Za-z0-9_.-]/', '_', $orderNumber) . '.pdf';
        return array($directory . '/' . $filename, 'storage/billing/purchase_orders/' . $year . '/' . $filename);
    }

    private function writeFile(string $path, string $content): void
    {
        $temporary = $path . '.tmp-' . bin2hex(random_bytes(6));
        if (file_put_contents($temporary, $content, LOCK_EX) === false || !rename($temporary, $path)) {
            @unlink($temporary);
            throw new RuntimeException('No fue posible guardar el PDF de la orden de compra.');
        }
        @chmod($path, 0660);
    }

    private function resolvePdf(string $relativePath): string
    {
        if (!$this->appRoot) throw new RuntimeException('No fue posible resolver la carpeta de la aplicación.');
        $storage = realpath($this->appRoot . '/storage/billing');
        $absolute = realpath($this->appRoot . '/' . ltrim(str_replace('\\', '/', $relativePath), '/'));
        if (!$storage || !$absolute || strpos($absolute, $storage . DIRECTORY_SEPARATOR) !== 0 || !is_file($absolute)) throw new RuntimeException('Archivo PDF no disponible.');
        return $absolute;
    }

    private function supplierName(array $supplier): string
    {
        return trim((string)($supplier['legal_name'] ?: ($supplier['commercial_name'] ?: $supplier['customer_name'])));
    }

    private function date(string $value, string $label): string
    {
        $date = DateTime::createFromFormat('!Y-m-d', trim($value));
        if (!$date || $date->format('Y-m-d') !== trim($value)) throw new InvalidArgumentException($label . ' inválida.');
        return $date->format('Y-m-d');
    }

    private function text($value, int $max): string
    {
        $value = trim(preg_replace('/\s+/u', ' ', (string)$value));
        return function_exists('mb_substr') ? mb_substr($value, 0, $max, 'UTF-8') : substr($value, 0, $max);
    }

    private function esc($value): string
    {
        return htmlspecialchars((string)$value, ENT_QUOTES, 'UTF-8');
    }
}
