<?php

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

class ProductCostingService
{
    private $pdo;
    private $snapshots;
    private $salesCodes = array('33','34','39','56','61','110','111','112');

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

    public function getWorkspace($from, $to)
    {
        $this->assertInstalled();
        list($from, $to) = $this->dateRange($from, $to);
        $products = $this->products();
        $coverage = $this->coverage($from, $to);
        $margins = $this->margins($from, $to, $coverage);
        $metrics = array(
            'active_products' => 0,
            'approved_standard' => 0,
            'provisional_legacy' => 0,
            'without_cost' => 0,
            'drafts' => 0,
        );
        foreach ($products as $product) {
            if ((int)$product['is_active'] === 1) $metrics['active_products']++;
            if (!empty($product['draft_version_id'])) $metrics['drafts']++;
            if (empty($product['approved_version_id']) || (float)$product['unit_standard_cost'] <= 0) $metrics['without_cost']++;
            elseif ($product['costing_method'] === 'legacy') $metrics['provisional_legacy']++;
            else $metrics['approved_standard']++;
        }
        return array(
            'range' => array('from' => $from, 'to' => $to),
            'metrics' => $metrics,
            'coverage' => $coverage,
            'margins' => $margins,
            'products' => $products,
            'component_types' => $this->componentTypes(),
        );
    }

    public function getProduct($productId)
    {
        $this->assertInstalled();
        $productId = (int)$productId;
        $stmt = $this->pdo->prepare("SELECT p.*,COALESCE(c.name,'Sin categoría') AS category_name FROM products p LEFT JOIN product_categories c ON c.id=p.category_id WHERE p.id=? LIMIT 1");
        $stmt->execute(array($productId));
        $product = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$product) throw new RuntimeException('Producto no encontrado.');
        $versionsStmt = $this->pdo->prepare("SELECT v.*,u.name AS approved_by_name FROM product_cost_versions v LEFT JOIN users u ON u.id=v.approved_by WHERE v.product_id=? ORDER BY v.version_number DESC");
        $versionsStmt->execute(array($productId));
        $versions = $versionsStmt->fetchAll(PDO::FETCH_ASSOC);
        $componentsStmt = $this->pdo->prepare('SELECT * FROM product_cost_components WHERE cost_version_id=? ORDER BY sort_order,id');
        foreach ($versions as &$version) {
            $componentsStmt->execute(array((int)$version['id']));
            $version['components'] = $componentsStmt->fetchAll(PDO::FETCH_ASSOC);
        }
        unset($version);
        $template = null;
        foreach ($versions as $version) {
            if ($version['status'] === 'draft') { $template = $version; break; }
        }
        if (!$template) {
            foreach ($versions as $version) {
                if ($version['status'] === 'approved') { $template = $version; break; }
            }
        }
        return array('product' => $product, 'versions' => $versions, 'template' => $template, 'component_types' => $this->componentTypes());
    }

    public function saveDraft(array $data, $userId)
    {
        $this->assertInstalled();
        $productId = (int)($data['product_id'] ?? 0);
        $versionId = (int)($data['version_id'] ?? 0);
        $product = $this->product($productId);
        $effectiveFrom = $this->dateValue($data['effective_from'] ?? date('Y-m-d'));
        $outputQuantity = round((float)($data['output_quantity'] ?? 1), 4);
        $yieldPercent = round((float)($data['yield_percent'] ?? 100), 4);
        $outputUnit = trim((string)($data['output_unit'] ?? $product['unit'] ?? 'unidad'));
        $notes = trim((string)($data['notes'] ?? ''));
        if ($outputQuantity <= 0) throw new InvalidArgumentException('La cantidad obtenida debe ser mayor que cero.');
        if ($yieldPercent <= 0 || $yieldPercent > 100) throw new InvalidArgumentException('El rendimiento debe estar entre 0 y 100%.');
        if ($outputUnit === '') $outputUnit = 'unidad';
        $components = $this->normalizeComponents($data['components_json'] ?? array());
        if (!$components) throw new InvalidArgumentException('Agrega al menos un componente de costo.');
        $totals = $this->componentTotals($components, $outputQuantity, $yieldPercent);

        $this->pdo->beginTransaction();
        try {
            $before = null;
            if ($versionId > 0) {
                $stmt = $this->pdo->prepare("SELECT * FROM product_cost_versions WHERE id=? AND product_id=? FOR UPDATE");
                $stmt->execute(array($versionId, $productId));
                $before = $stmt->fetch(PDO::FETCH_ASSOC);
                if (!$before || $before['status'] !== 'draft') throw new RuntimeException('Solo se puede editar una versión en borrador.');
                $sql = "UPDATE product_cost_versions SET effective_from=?,costing_method='standard',output_quantity=?,output_unit=?,yield_percent=?,material_cost=?,labor_cost=?,variable_overhead_cost=?,fixed_overhead_cost=?,other_cost=?,total_batch_cost=?,unit_standard_cost=?,notes=?,updated_by=?,updated_at=NOW() WHERE id=?";
                $this->pdo->prepare($sql)->execute(array($effectiveFrom,$outputQuantity,$outputUnit,$yieldPercent,$totals['material'],$totals['labor'],$totals['variable_overhead'],$totals['fixed_overhead'],$totals['other'],$totals['batch'],$totals['unit'],$notes,$userId ?: null,$versionId));
                $this->pdo->prepare('DELETE FROM product_cost_components WHERE cost_version_id=?')->execute(array($versionId));
            } else {
                $numberStmt = $this->pdo->prepare('SELECT COALESCE(MAX(version_number),0)+1 FROM product_cost_versions WHERE product_id=? FOR UPDATE');
                $numberStmt->execute(array($productId));
                $versionNumber = (int)$numberStmt->fetchColumn();
                $sql = "INSERT INTO product_cost_versions (product_id,version_number,effective_from,status,costing_method,output_quantity,output_unit,yield_percent,material_cost,labor_cost,variable_overhead_cost,fixed_overhead_cost,other_cost,total_batch_cost,unit_standard_cost,notes,created_by,updated_by) VALUES (?,?,?,'draft','standard',?,?,?,?,?,?,?,?,?,?,?,?,?)";
                $this->pdo->prepare($sql)->execute(array($productId,$versionNumber,$effectiveFrom,$outputQuantity,$outputUnit,$yieldPercent,$totals['material'],$totals['labor'],$totals['variable_overhead'],$totals['fixed_overhead'],$totals['other'],$totals['batch'],$totals['unit'],$notes,$userId ?: null,$userId ?: null));
                $versionId = (int)$this->pdo->lastInsertId();
            }
            $this->insertComponents($versionId, $components);
            $after = $this->version($versionId);
            $this->audit($productId, $versionId, $before ? 'draft_updated' : 'draft_created', $before, $after, $userId);
            $this->pdo->commit();
            return array('version_id' => $versionId, 'unit_standard_cost' => $totals['unit'], 'total_batch_cost' => $totals['batch']);
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }
    }

    public function approveVersion($versionId, $userId)
    {
        $this->assertInstalled();
        $versionId = (int)$versionId;
        $this->pdo->beginTransaction();
        try {
            $stmt = $this->pdo->prepare('SELECT * FROM product_cost_versions WHERE id=? FOR UPDATE');
            $stmt->execute(array($versionId));
            $version = $stmt->fetch(PDO::FETCH_ASSOC);
            if (!$version || $version['status'] !== 'draft') throw new RuntimeException('La versión ya no está disponible para aprobación.');
            if ((float)$version['unit_standard_cost'] <= 0) throw new RuntimeException('El costo unitario debe ser mayor que cero antes de aprobar.');
            if ($version['effective_from'] > date('Y-m-d')) throw new RuntimeException('Una versión futura debe aprobarse cuando llegue su fecha de vigencia para no adelantar el costo del catálogo.');
            $future = $this->pdo->prepare("SELECT COUNT(*) FROM product_cost_versions WHERE product_id=? AND status='approved' AND id<>? AND effective_from>?");
            $future->execute(array((int)$version['product_id'], $versionId, $version['effective_from']));
            if ((int)$future->fetchColumn() > 0) throw new RuntimeException('Ya existe una versión aprobada con una fecha posterior. Ajusta la vigencia del borrador.');
            $this->pdo->prepare("UPDATE product_cost_versions SET status='retired',effective_to=?,updated_by=?,updated_at=NOW() WHERE product_id=? AND status='approved' AND id<>? AND effective_from=?")
                ->execute(array($version['effective_from'],$userId ?: null,(int)$version['product_id'],$versionId,$version['effective_from']));
            $this->pdo->prepare("UPDATE product_cost_versions SET effective_to=DATE_SUB(?,INTERVAL 1 DAY),updated_by=?,updated_at=NOW() WHERE product_id=? AND status='approved' AND id<>? AND effective_from<? AND (effective_to IS NULL OR effective_to>=?)")
                ->execute(array($version['effective_from'],$userId ?: null,(int)$version['product_id'],$versionId,$version['effective_from'],$version['effective_from']));
            $this->pdo->prepare("UPDATE product_cost_versions SET status='approved',approved_by=?,approved_at=NOW(),updated_by=?,updated_at=NOW() WHERE id=?")
                ->execute(array($userId ?: null,$userId ?: null,$versionId));
            if ($version['effective_from'] <= date('Y-m-d')) {
                $this->pdo->prepare('UPDATE products SET cost=?,updated_at=NOW() WHERE id=?')->execute(array($version['unit_standard_cost'],(int)$version['product_id']));
            }
            $after = $this->version($versionId);
            $this->audit((int)$version['product_id'], $versionId, 'approved', $version, $after, $userId);
            $this->pdo->commit();
            return array('version_id' => $versionId, 'unit_standard_cost' => (float)$version['unit_standard_cost']);
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }
    }

    public function rebuildSnapshots($from, $to, $userId)
    {
        $this->assertInstalled();
        list($from, $to) = $this->dateRange($from, $to);
        return $this->snapshots->snapshotMissingRange($from, $to, 'production', $userId, 5000);
    }

    public function coverage($from, $to)
    {
        list($from, $to) = $this->dateRange($from, $to);
        $codes = "'" . implode("','", $this->salesCodes) . "'";
        $docStmt = $this->pdo->prepare("SELECT COUNT(DISTINCT fd.id) AS documents_total,COUNT(DISTINCT CASE WHEN l.id IS NOT NULL THEN fd.id END) AS documents_with_lines FROM facto_documents fd LEFT JOIN billing_document_lines l ON l.environment=fd.environment AND BINARY l.facto_document_id=BINARY fd.document_id WHERE fd.environment='production' AND fd.received_issued_flag=1 AND fd.document_status=1 AND fd.document_type_taxbureau IN ($codes) AND fd.issue_date BETWEEN ? AND ?");
        $docStmt->execute(array($from, $to));
        $docs = $docStmt->fetch(PDO::FETCH_ASSOC) ?: array();
        $lineStmt = $this->pdo->prepare("SELECT COUNT(l.id) AS lines_total,SUM(CASE WHEN s.cost_quality<>'missing' THEN 1 ELSE 0 END) AS lines_costed,SUM(CASE WHEN s.id IS NULL OR s.cost_quality='missing' THEN 1 ELSE 0 END) AS lines_missing,COALESCE(SUM(ABS(CASE WHEN s.id IS NOT NULL THEN s.net_revenue ELSE l.net_amount*COALESCE(NULLIF(fd.exchange_rate_value,0),1) END)),0) AS line_revenue_total,COALESCE(SUM(CASE WHEN s.cost_quality<>'missing' THEN ABS(s.net_revenue) ELSE 0 END),0) AS line_revenue_costed 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='production' AND fd.received_issued_flag=1 AND fd.document_status=1 AND fd.document_type_taxbureau IN ($codes) AND fd.issue_date BETWEEN ? AND ?");
        $lineStmt->execute(array($from, $to));
        $lines = $lineStmt->fetch(PDO::FETCH_ASSOC) ?: array();
        $documentsTotal = (int)($docs['documents_total'] ?? 0);
        $documentsWithLines = (int)($docs['documents_with_lines'] ?? 0);
        $revenueTotal = (float)($lines['line_revenue_total'] ?? 0);
        $docPct = $documentsTotal > 0 ? round($documentsWithLines / $documentsTotal * 100, 1) : 0.0;
        $costPct = $revenueTotal > 0 ? round((float)$lines['line_revenue_costed'] / $revenueTotal * 100, 1) : 0.0;
        return array(
            'documents_total' => $documentsTotal,
            'documents_with_lines' => $documentsWithLines,
            'documents_without_lines' => max(0, $documentsTotal - $documentsWithLines),
            'document_coverage_pct' => $docPct,
            'lines_total' => (int)($lines['lines_total'] ?? 0),
            'lines_costed' => (int)($lines['lines_costed'] ?? 0),
            'lines_missing' => (int)($lines['lines_missing'] ?? 0),
            'cost_coverage_pct' => $costPct,
            'reliable' => $documentsTotal > 0 && $docPct >= 95 && $costPct >= 90,
        );
    }

    public function margins($from, $to, ?array $coverage = null)
    {
        list($from, $to) = $this->dateRange($from, $to);
        if ($coverage === null) $coverage = $this->coverage($from, $to);
        $codes = "'" . implode("','", $this->salesCodes) . "'";
        $stmt = $this->pdo->prepare("SELECT COALESCE(SUM(net_revenue),0) AS net_revenue,COALESCE(SUM(total_standard_cost),0) AS total_cost,COALESCE(SUM(contribution_margin),0) AS contribution_margin,COALESCE(SUM(CASE WHEN cost_quality='validated' THEN ABS(net_revenue) ELSE 0 END),0) AS validated_revenue,COALESCE(SUM(CASE WHEN cost_quality='provisional' THEN ABS(net_revenue) ELSE 0 END),0) AS provisional_revenue FROM billing_line_cost_snapshots WHERE environment='production' AND document_type_taxbureau IN ($codes) AND document_date BETWEEN ? AND ? AND cost_quality<>'missing'");
        $stmt->execute(array($from, $to));
        $summary = $stmt->fetch(PDO::FETCH_ASSOC) ?: array();
        $revenue = (float)($summary['net_revenue'] ?? 0);
        $margin = (float)($summary['contribution_margin'] ?? 0);
        $summary['margin_percent'] = abs($revenue) > 0.005 ? round($margin / $revenue * 100, 1) : null;
        $summary['reliable'] = !empty($coverage['reliable']);
        $summary['quality'] = (float)($summary['provisional_revenue'] ?? 0) > 0 ? 'provisional' : (!empty($coverage['reliable']) ? 'validated' : 'missing');

        $productStmt = $this->pdo->prepare("SELECT COALESCE(NULLIF(product_name,''),NULLIF(sku,''),'Sin producto') AS label,MAX(sku) AS sku,SUM(net_revenue) AS net_revenue,SUM(total_standard_cost) AS total_cost,SUM(contribution_margin) AS contribution_margin,CASE WHEN ABS(SUM(net_revenue))>0 THEN SUM(contribution_margin)/SUM(net_revenue)*100 ELSE NULL END AS margin_percent,MAX(cost_quality) AS quality FROM billing_line_cost_snapshots WHERE environment='production' AND document_type_taxbureau IN ($codes) AND document_date BETWEEN ? AND ? AND cost_quality<>'missing' GROUP BY product_id,label ORDER BY net_revenue DESC LIMIT 50");
        $productStmt->execute(array($from, $to));
        $customerStmt = $this->pdo->prepare("SELECT COALESCE(NULLIF(customer_name,''),CONCAT('Cliente #',customer_id),'Sin cliente') AS label,SUM(net_revenue) AS net_revenue,SUM(total_standard_cost) AS total_cost,SUM(contribution_margin) AS contribution_margin,CASE WHEN ABS(SUM(net_revenue))>0 THEN SUM(contribution_margin)/SUM(net_revenue)*100 ELSE NULL END AS margin_percent,MAX(cost_quality) AS quality FROM billing_line_cost_snapshots WHERE environment='production' AND document_type_taxbureau IN ($codes) AND document_date BETWEEN ? AND ? AND cost_quality<>'missing' GROUP BY customer_id,label ORDER BY net_revenue DESC LIMIT 50");
        $customerStmt->execute(array($from, $to));
        return array('summary' => $summary, 'by_product' => $productStmt->fetchAll(PDO::FETCH_ASSOC), 'by_customer' => $customerStmt->fetchAll(PDO::FETCH_ASSOC));
    }

    private function products()
    {
        $sql = "SELECT p.id,p.sku,p.name,p.price,p.cost,p.unit,p.is_active,COALESCE(c.name,'Sin categoría') AS category_name,
                       v.id AS approved_version_id,v.version_number,v.effective_from,v.effective_to,v.costing_method,v.unit_standard_cost,
                       d.id AS draft_version_id,d.version_number AS draft_version_number,d.unit_standard_cost AS draft_unit_cost
                FROM products p
                LEFT JOIN product_categories c ON c.id=p.category_id
                LEFT JOIN product_cost_versions v ON v.id=(SELECT v2.id FROM product_cost_versions v2 WHERE v2.product_id=p.id AND v2.status='approved' AND v2.effective_from<=CURDATE() AND (v2.effective_to IS NULL OR v2.effective_to>=CURDATE()) ORDER BY v2.effective_from DESC,v2.version_number DESC LIMIT 1)
                LEFT JOIN product_cost_versions d ON d.id=(SELECT d2.id FROM product_cost_versions d2 WHERE d2.product_id=p.id AND d2.status='draft' ORDER BY d2.version_number DESC LIMIT 1)
                ORDER BY p.is_active DESC,p.name,p.sku";
        return $this->pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
    }

    private function normalizeComponents($raw)
    {
        if (is_string($raw)) {
            $decoded = json_decode($raw, true);
            $raw = is_array($decoded) ? $decoded : array();
        }
        if (!is_array($raw)) $raw = array();
        $allowed = array_keys($this->componentTypes());
        $out = array();
        foreach ($raw as $index => $item) {
            if (!is_array($item)) continue;
            $type = trim((string)($item['component_type'] ?? ''));
            $description = trim((string)($item['description'] ?? ''));
            if (!in_array($type, $allowed, true)) throw new InvalidArgumentException('Un componente tiene un tipo de costo inválido.');
            if ($description === '') throw new InvalidArgumentException('Todos los componentes deben tener una descripción.');
            $quantity = round((float)($item['quantity'] ?? 0), 4);
            $unitCost = round((float)($item['unit_cost'] ?? 0), 4);
            $waste = round((float)($item['waste_percent'] ?? 0), 4);
            if ($quantity <= 0 || $unitCost < 0) throw new InvalidArgumentException('Cantidad y costo unitario de componentes no son válidos.');
            if ($waste < 0 || $waste > 100) throw new InvalidArgumentException('La merma de un componente debe estar entre 0 y 100%.');
            $out[] = array(
                'component_type' => $type,
                'component_product_id' => !empty($item['component_product_id']) ? (int)$item['component_product_id'] : null,
                'component_code' => substr(trim((string)($item['component_code'] ?? '')), 0, 100) ?: null,
                'description' => substr($description, 0, 255),
                'quantity' => $quantity,
                'unit_measure' => substr(trim((string)($item['unit_measure'] ?? 'unidad')) ?: 'unidad', 0, 50),
                'unit_cost' => $unitCost,
                'waste_percent' => $waste,
                'total_component_cost' => round($quantity * $unitCost * (1 + $waste / 100), 2),
                'sort_order' => count($out) + 1,
            );
        }
        return $out;
    }

    private function componentTotals(array $components, $outputQuantity, $yieldPercent)
    {
        $totals = array('material'=>0.0,'labor'=>0.0,'variable_overhead'=>0.0,'fixed_overhead'=>0.0,'other'=>0.0,'batch'=>0.0,'unit'=>0.0);
        foreach ($components as $component) {
            $amount = (float)$component['total_component_cost'];
            if ($component['component_type'] === 'material') $totals['material'] += $amount;
            elseif ($component['component_type'] === 'labor') $totals['labor'] += $amount;
            elseif ($component['component_type'] === 'fixed_overhead') $totals['fixed_overhead'] += $amount;
            elseif (in_array($component['component_type'], array('service','variable_overhead'), true)) $totals['variable_overhead'] += $amount;
            else $totals['other'] += $amount;
            $totals['batch'] += $amount;
        }
        foreach ($totals as $key => $value) $totals[$key] = round($value, 2);
        $effectiveOutput = $outputQuantity * ($yieldPercent / 100);
        $totals['unit'] = round($totals['batch'] / max(0.0001, $effectiveOutput), 4);
        return $totals;
    }

    private function insertComponents($versionId, array $components)
    {
        $stmt = $this->pdo->prepare('INSERT INTO product_cost_components (cost_version_id,component_type,component_product_id,component_code,description,quantity,unit_measure,unit_cost,waste_percent,total_component_cost,sort_order) VALUES (?,?,?,?,?,?,?,?,?,?,?)');
        foreach ($components as $component) {
            $stmt->execute(array($versionId,$component['component_type'],$component['component_product_id'],$component['component_code'],$component['description'],$component['quantity'],$component['unit_measure'],$component['unit_cost'],$component['waste_percent'],$component['total_component_cost'],$component['sort_order']));
        }
    }

    private function componentTypes()
    {
        return array(
            'material' => 'Materia prima / insumo',
            'labor' => 'Mano de obra directa',
            'service' => 'Servicio directo',
            'variable_overhead' => 'Costo indirecto variable',
            'fixed_overhead' => 'Costo indirecto fijo',
            'other' => 'Otro costo',
        );
    }

    private function assertInstalled()
    {
        $stmt = $this->pdo->query("SHOW TABLES LIKE 'product_cost_versions'");
        if (!$stmt->fetchColumn()) throw new RuntimeException('El costeo de productos aún no está instalado. Ejecuta la migración v8.9 desde phpMyAdmin.');
    }

    private function product($id)
    {
        $stmt = $this->pdo->prepare('SELECT id,sku,name,cost,unit FROM products WHERE id=? LIMIT 1');
        $stmt->execute(array((int)$id));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) throw new RuntimeException('Producto no encontrado.');
        return $row;
    }

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

    private function audit($productId, $versionId, $action, $before, $after, $userId)
    {
        $stmt = $this->pdo->prepare('INSERT INTO product_cost_audit_log (product_id,cost_version_id,action_key,before_json,after_json,actor_user_id) VALUES (?,?,?,?,?,?)');
        $stmt->execute(array($productId,$versionId,$action,$before===null?null:json_encode($before,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES),$after===null?null:json_encode($after,JSON_UNESCAPED_UNICODE|JSON_UNESCAPED_SLASHES),$userId ?: null));
    }

    private function dateRange($from, $to)
    {
        $from = $this->dateValue($from ?: date('Y-m-01'));
        $to = $this->dateValue($to ?: date('Y-m-d'));
        if ($from > $to) throw new InvalidArgumentException('La fecha inicial no puede ser posterior a la fecha final.');
        return array($from, $to);
    }

    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.');
        return $value;
    }
}
