<?php

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

class ProductCostSnapshotService
{
    private $pdo;

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

    public function isInstalled()
    {
        try {
            $stmt = $this->pdo->query("SHOW TABLES LIKE 'billing_line_cost_snapshots'");
            return (bool)$stmt->fetchColumn();
        } catch (Throwable $e) {
            return false;
        }
    }

    public function snapshotDocumentSafe($environment, $factoDocumentId, $userId = null)
    {
        try {
            return $this->snapshotDocument($environment, $factoDocumentId, $userId);
        } catch (Throwable $e) {
            error_log('product cost snapshot error [' . $environment . '/' . $factoDocumentId . ']: ' . $e->getMessage());
            return array('installed' => true, 'ok' => false, 'message' => $e->getMessage(), 'lines' => 0, 'costed' => 0, 'missing' => 0);
        }
    }

    public function snapshotDocument($environment, $factoDocumentId, $userId = null)
    {
        if (!$this->isInstalled()) {
            return array('installed' => false, 'ok' => true, 'lines' => 0, 'costed' => 0, 'missing' => 0);
        }

        $environment = strtolower(trim((string)$environment)) === 'production' ? 'production' : 'sandbox';
        $documentId = trim((string)$factoDocumentId);
        if ($documentId === '') throw new InvalidArgumentException('Documento de facturación inválido para costeo.');

        $docStmt = $this->pdo->prepare("SELECT document_id,environment,issue_date,document_type_taxbureau,crm_customer_id,receiver_legal_name,COALESCE(NULLIF(currency_code,''),'CLP') AS currency_code,exchange_rate_value FROM facto_documents WHERE environment=? AND BINARY document_id=BINARY ? LIMIT 1");
        $docStmt->execute(array($environment, $documentId));
        $document = $docStmt->fetch(PDO::FETCH_ASSOC);
        if (!$document) throw new RuntimeException('No se encontró el documento local antes de congelar su costo.');

        $lineStmt = $this->pdo->prepare('SELECT * FROM billing_document_lines WHERE environment=? AND BINARY facto_document_id=BINARY ? ORDER BY id');
        $lineStmt->execute(array($environment, $documentId));
        $lines = $lineStmt->fetchAll(PDO::FETCH_ASSOC);
        $result = array('installed' => true, 'ok' => true, 'lines' => count($lines), 'costed' => 0, 'missing' => 0);
        if (!$lines) return $result;

        $date = $this->dateValue($document['issue_date'] ?? null);
        $siiCode = trim((string)($document['document_type_taxbureau'] ?? ''));
        $sign = in_array($siiCode, array('61','112'), true) ? -1 : 1;
        $currency = strtoupper(trim((string)($document['currency_code'] ?? 'CLP')) ?: 'CLP');
        $rawExchangeRate = (float)($document['exchange_rate_value'] ?? 0);
        if ($currency !== 'CLP' && $rawExchangeRate <= 0) throw new RuntimeException('El documento en moneda extranjera no tiene tipo de cambio histórico validado.');
        $exchangeRate = $currency === 'CLP' ? 1.0 : $rawExchangeRate;

        $insert = $this->pdo->prepare("INSERT INTO billing_line_cost_snapshots
            (billing_document_line_id,environment,facto_document_id,document_date,document_type_taxbureau,customer_id,customer_name,currency_code,exchange_rate_value,product_id,sku,product_name,cost_version_id,quantity,unit_standard_cost,total_standard_cost,net_revenue,contribution_margin,margin_percent,snapshot_source,cost_quality,snapshot_json,created_by)
            VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
            ON DUPLICATE KEY UPDATE
              product_id=IF(cost_quality='missing',VALUES(product_id),product_id),
              product_name=IF(cost_quality='missing',VALUES(product_name),product_name),
              cost_version_id=IF(cost_quality='missing',VALUES(cost_version_id),cost_version_id),
              unit_standard_cost=IF(cost_quality='missing',VALUES(unit_standard_cost),unit_standard_cost),
              total_standard_cost=IF(cost_quality='missing',VALUES(total_standard_cost),total_standard_cost),
              net_revenue=IF(cost_quality='missing',VALUES(net_revenue),net_revenue),
              contribution_margin=IF(cost_quality='missing',VALUES(contribution_margin),contribution_margin),
              margin_percent=IF(cost_quality='missing',VALUES(margin_percent),margin_percent),
              snapshot_source=IF(cost_quality='missing',VALUES(snapshot_source),snapshot_source),
              cost_quality=IF(cost_quality='missing',VALUES(cost_quality),cost_quality),
              snapshot_json=IF(cost_quality='missing',VALUES(snapshot_json),snapshot_json),
              updated_at=IF(cost_quality='missing',CURRENT_TIMESTAMP,updated_at)");

        foreach ($lines as $line) {
            $product = $this->resolveProduct($line);
            $cost = $this->resolveCost($product, $date);
            $quantity = abs((float)($line['quantity'] ?? 0));
            $netRevenue = round(abs((float)($line['net_amount'] ?? 0)) * $exchangeRate * $sign, 2);
            $unitCost = $cost['unit_cost'];
            $totalCost = $unitCost === null ? null : round($quantity * $unitCost * $sign, 2);
            $margin = $totalCost === null ? null : round($netRevenue - $totalCost, 2);
            $marginPct = $margin === null || abs($netRevenue) < 0.005 ? null : round(($margin / $netRevenue) * 100, 4);
            $snapshot = array(
                'line_net_original' => (float)($line['net_amount'] ?? 0),
                'currency_code' => $currency,
                'exchange_rate_value' => $exchangeRate,
                'costing_method' => $cost['costing_method'],
                'effective_cost_date' => $date,
                'sign' => $sign,
            );
            $insert->execute(array(
                (int)$line['id'], $environment, $documentId, $date, $siiCode ?: null,
                !empty($document['crm_customer_id']) ? (int)$document['crm_customer_id'] : null,
                $document['receiver_legal_name'] ?? null, $currency, $exchangeRate,
                $product ? (int)$product['id'] : null, $line['sku'] ?? ($product['sku'] ?? null),
                $product['name'] ?? ($line['description'] ?? null), $cost['version_id'], $quantity,
                $unitCost, $totalCost, $netRevenue, $margin, $marginPct,
                $cost['source'], $cost['quality'], json_encode($snapshot, JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                $userId ? (int)$userId : null,
            ));
            if ($cost['quality'] === 'missing') $result['missing']++; else $result['costed']++;
        }
        return $result;
    }

    public function snapshotMissingRange($from, $to, $environment = 'production', $userId = null, $limit = 1000)
    {
        if (!$this->isInstalled()) throw new RuntimeException('Ejecuta primero la migración de costeo v8.9.');
        $from = $this->dateValue($from);
        $to = $this->dateValue($to);
        if ($from > $to) throw new InvalidArgumentException('La fecha inicial no puede ser posterior a la fecha final.');
        $environment = strtolower(trim((string)$environment)) === 'sandbox' ? 'sandbox' : 'production';
        $limit = max(1, min(5000, (int)$limit));
        $sql = "SELECT DISTINCT fd.document_id
                FROM facto_documents fd
                JOIN billing_document_lines l ON l.environment=fd.environment AND BINARY l.facto_document_id=BINARY fd.document_id
                LEFT JOIN billing_line_cost_snapshots s ON s.billing_document_line_id=l.id
                WHERE fd.environment=? AND fd.received_issued_flag=1 AND fd.document_status=1
                  AND fd.issue_date BETWEEN ? AND ? AND (s.id IS NULL OR s.cost_quality='missing')
                ORDER BY fd.issue_date,fd.document_id LIMIT " . $limit;
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($environment, $from, $to));
        $totals = array('documents' => 0, 'lines' => 0, 'costed' => 0, 'missing' => 0);
        foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $documentId) {
            $row = $this->snapshotDocumentSafe($environment, $documentId, $userId);
            $totals['documents']++;
            $totals['lines'] += (int)($row['lines'] ?? 0);
            $totals['costed'] += (int)($row['costed'] ?? 0);
            $totals['missing'] += (int)($row['missing'] ?? 0);
        }
        return $totals;
    }

    private function resolveProduct(array $line)
    {
        if (!empty($line['product_id'])) {
            $stmt = $this->pdo->prepare('SELECT id,sku,name,cost,unit FROM products WHERE id=? LIMIT 1');
            $stmt->execute(array((int)$line['product_id']));
            $product = $stmt->fetch(PDO::FETCH_ASSOC);
            if ($product) return $product;
        }
        $sku = trim((string)($line['sku'] ?? ''));
        if ($sku === '') return null;
        $stmt = $this->pdo->prepare('SELECT id,sku,name,cost,unit FROM products WHERE UPPER(TRIM(sku))=UPPER(TRIM(?)) ORDER BY is_active DESC,id LIMIT 1');
        $stmt->execute(array($sku));
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: null;
    }

    private function resolveCost($product, $date)
    {
        if (!$product) return array('version_id' => null, 'unit_cost' => null, 'source' => 'missing_product', 'quality' => 'missing', 'costing_method' => null);
        $stmt = $this->pdo->prepare("SELECT id,costing_method,unit_standard_cost FROM product_cost_versions WHERE product_id=? AND status='approved' AND effective_from<=? AND (effective_to IS NULL OR effective_to>=?) ORDER BY effective_from DESC,version_number DESC LIMIT 1");
        $stmt->execute(array((int)$product['id'], $date, $date));
        $version = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($version && (float)$version['unit_standard_cost'] > 0) {
            $legacy = (string)$version['costing_method'] === 'legacy';
            return array(
                'version_id' => (int)$version['id'],
                'unit_cost' => (float)$version['unit_standard_cost'],
                'source' => $legacy ? 'legacy_version' : 'approved_version',
                'quality' => $legacy ? 'provisional' : 'validated',
                'costing_method' => $version['costing_method'],
            );
        }
        if ((float)($product['cost'] ?? 0) > 0) {
            return array('version_id' => null, 'unit_cost' => (float)$product['cost'], 'source' => 'legacy_product_cost', 'quality' => 'provisional', 'costing_method' => 'legacy');
        }
        return array('version_id' => null, 'unit_cost' => null, 'source' => 'missing_cost', 'quality' => 'missing', 'costing_method' => null);
    }

    private function dateValue($value)
    {
        $value = substr(trim((string)$value), 0, 10);
        if (!preg_match('/^\d{4}-\d{2}-\d{2}$/', $value) || !strtotime($value)) {
            throw new InvalidArgumentException('Fecha inválida para costeo.');
        }
        return $value;
    }
}
