<?php

require_once __DIR__ . '/../../core/database.php';
require_once __DIR__ . '/PaymentStatusService.php';
require_once __DIR__ . '/OpportunityAccessService.php';
require_once __DIR__ . '/OpportunityStageHistoryService.php';
require_once __DIR__ . '/RestockForecastService.php';

class OpportunityLifecycleService
{
    private $pdo;

    private $outcomeStages = array(
        'won' => 'ganada',
        'lost' => 'perdida',
        'invoiced' => 'facturada',
        'cancelled' => 'perdida',
        'no_decision' => 'perdida',
    );

    private $activeStages = array('nuevo', 'contactado', 'cotizando', 'negociacion');
    private $allStages = array('nuevo', 'contactado', 'cotizando', 'negociacion', 'ganada', 'perdida', 'facturada');

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

    public function moveStage($opportunityId, $newStage, array $user, string $source = 'kanban'): array
    {
        $opportunityId = (int)$opportunityId;
        $newStage = trim((string)$newStage);
        if ($opportunityId <= 0 || !in_array($newStage, $this->allStages, true)) {
            throw new InvalidArgumentException('Etapa de oportunidad inválida.');
        }

        $old = $this->loadOpportunity($opportunityId, true);
        OpportunityAccessService::assertCanManage($old, $user, 'mover');
        if (!empty($old['archived_at'])) {
            throw new InvalidArgumentException('La oportunidad está archivada. Debes reabrirla antes de moverla.');
        }
        if ((string)$old['stage'] === $newStage) {
            return array('id' => $opportunityId, 'stage' => $newStage, 'unchanged' => true);
        }
        if ((string)$old['stage'] === 'facturada' && $newStage !== 'facturada') {
            $isManager = in_array((string)($user['role'] ?? ''), array('admin','administracion'), true);
            if (!$isManager || $this->hasValidIssuedDocument($old)) {
                throw new InvalidArgumentException('Una oportunidad facturada no puede retroceder de etapa mientras el DTE siga vigente. Las recompras deben registrarse como nuevas oportunidades.');
            }
        }
        if ($newStage === 'ganada' && !$this->hasAcceptedQuote($opportunityId)) {
            throw new InvalidArgumentException('No puedes moverla a Ganada sin una cotización aceptada asociada.');
        }
        if ($newStage === 'facturada' && !$this->hasValidIssuedDocument($old)) {
            throw new InvalidArgumentException('No puedes moverla a Facturada sin un DTE emitido y vigente. Emite o vincula la factura primero.');
        }

        $userId = isset($user['id']) ? (int)$user['id'] : null;
        $userName = $this->userName($user);
        $oldStage = (string)$old['stage'];
        $fields = array('stage = ?', 'updated_at = NOW()');
        $params = array($newStage);

        if ($newStage === 'ganada') {
            $fields[] = 'probability = 100';
            $fields[] = 'commercial_closed_at = COALESCE(commercial_closed_at, NOW())';
            $fields[] = 'closed_at = COALESCE(closed_at, NOW())';
            $fields[] = "closure_outcome = 'won'";
            $fields[] = "closure_reason = COALESCE(NULLIF(closure_reason,''), 'Movida a Ganada desde el embudo')";
        } elseif ($newStage === 'perdida') {
            $fields[] = 'probability = 0';
            $fields[] = 'commercial_closed_at = COALESCE(commercial_closed_at, NOW())';
            $fields[] = 'closed_at = COALESCE(closed_at, NOW())';
            $fields[] = "closure_outcome = 'lost'";
            $fields[] = "closure_reason = COALESCE(NULLIF(closure_reason,''), NULLIF(lost_reason,''), 'Movida a Perdida desde el embudo')";
        } elseif ($newStage === 'facturada') {
            $fields[] = 'probability = 100';
            $fields[] = "payment_status = CASE WHEN payment_status='no_facturado' THEN 'por_cobrar' ELSE payment_status END";
            $fields[] = 'commercial_closed_at = COALESCE(commercial_closed_at, NOW())';
            $fields[] = 'closed_at = COALESCE(closed_at, NOW())';
            $fields[] = "closure_outcome = 'invoiced'";
            $fields[] = "closure_reason = COALESCE(NULLIF(closure_reason,''), 'DTE emitido y vigente')";
        } elseif (in_array($newStage, $this->activeStages, true) && in_array($oldStage, array('ganada','perdida','facturada'), true)) {
            $fields[] = 'commercial_closed_at = NULL';
            $fields[] = 'closed_at = NULL';
            $fields[] = 'closed_by_user_id = NULL';
            $fields[] = 'closure_outcome = NULL';
            $fields[] = 'closure_reason = NULL';
            $fields[] = 'closure_note = NULL';
            $fields[] = 'reopened_at = NOW()';
            $fields[] = 'reopened_by_user_id = ?';
            $params[] = $userId;
            $fields[] = 'probability = ?';
            $params[] = $this->defaultProbability($newStage);
            if ($oldStage === 'perdida') $fields[] = 'lost_reason = NULL';
        }

        $params[] = $opportunityId;
        $params[] = $oldStage;
        try {
            $this->pdo->beginTransaction();
            $stmt = $this->pdo->prepare('UPDATE crm_opportunities SET ' . implode(', ', $fields) . ' WHERE id = ? AND deleted_at IS NULL AND archived_at IS NULL AND stage = ?');
            $stmt->execute($params);
            if ($stmt->rowCount() !== 1) throw new RuntimeException('La oportunidad cambió mientras intentabas moverla.');

            OpportunityStageHistoryService::record($this->pdo, $opportunityId, $oldStage, $newStage, $userId, $userName, $source);
            $this->insertActivity($opportunityId, $old, 'Etapa de oportunidad actualizada', 'Etapa: ' . $oldStage . ' -> ' . $newStage, $userName);
            OpportunityStageHistoryService::lifecycle($this->pdo, $opportunityId, 'stage_change', $this->lifecycleSnapshot($old), array('stage' => $newStage), $userId, $userName, $source);
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }

        if ($newStage === 'facturada') {
            try {
                (new RestockForecastService($this->pdo))->refreshFromOpportunity($opportunityId, $old['facto_document_id'] ?? null, 'opportunity_stage');
            } catch (Throwable $e) {
                error_log('Restock forecast stage move ' . $opportunityId . ': ' . $e->getMessage());
            }
        }

        return array('id' => $opportunityId, 'old_stage' => $oldStage, 'stage' => $newStage, 'unchanged' => false);
    }

    public function close($opportunityId, $outcome, $reason, $note, array $user): array
    {
        $opportunityId = (int)$opportunityId;
        $outcome = trim((string)$outcome);
        $reason = trim((string)$reason);
        $note = trim((string)$note);

        if ($opportunityId <= 0) throw new InvalidArgumentException('ID de oportunidad inválido.');
        if (!isset($this->outcomeStages[$outcome])) throw new InvalidArgumentException('Resultado de cierre inválido.');
        if (in_array($outcome, array('lost', 'cancelled', 'no_decision'), true) && $reason === '') {
            throw new InvalidArgumentException('Debes indicar el motivo del cierre.');
        }

        $old = $this->loadOpportunity($opportunityId, true);
        OpportunityAccessService::assertCanManage($old, $user, 'cerrar');
        if (!empty($old['archived_at'])) return array('opportunity' => $old, 'already_archived' => true);

        if ($outcome === 'invoiced' && !$this->hasValidIssuedDocument($old)) {
            throw new InvalidArgumentException('No es posible archivar como Facturada: falta un DTE emitido y vigente asociado a la oportunidad.');
        }
        if ($outcome === 'won' && (string)$old['stage'] !== 'ganada' && !$this->hasAcceptedQuote($opportunityId)) {
            throw new InvalidArgumentException('Para cerrar como Ganada debe existir una cotización aceptada o la oportunidad debe estar en la etapa Ganada.');
        }

        $userId = isset($user['id']) ? (int)$user['id'] : null;
        $userName = $this->userName($user);
        $newStage = $this->outcomeStages[$outcome];
        $paymentStatus = $old['payment_status'] ?? 'no_facturado';
        $paymentDueDate = $old['payment_due_date'] ?? null;
        $paymentPaidDate = $old['payment_paid_date'] ?? null;
        $billingChanges = array();

        if ($outcome === 'invoiced') {
            $billingAdjustment = PaymentStatusService::applyFacturadaRules(array(
                'stage' => 'facturada',
                'old_stage' => $old['stage'] ?? '',
                'payment_status' => $paymentStatus,
                'payment_due_date' => $paymentDueDate,
                'payment_paid_date' => $paymentPaidDate,
                'payment_terms' => $old['payment_terms'] ?? ($old['facto_payment_term'] ?? 'contado'),
                'base_date' => date('Y-m-d'),
            ));
            $paymentStatus = $billingAdjustment['payment_status'];
            $paymentDueDate = $billingAdjustment['payment_due_date'];
            $paymentPaidDate = $billingAdjustment['payment_paid_date'];
            $billingChanges = $billingAdjustment['changes'];
        }

        try {
            $this->pdo->beginTransaction();
            $stmt = $this->pdo->prepare("\n                UPDATE crm_opportunities\n                SET stage = ?, payment_status = ?, payment_due_date = ?, payment_paid_date = ?,\n                    commercial_closed_at = COALESCE(commercial_closed_at, NOW()),\n                    closed_at = COALESCE(closed_at, NOW()), closed_by_user_id = ?,\n                    closure_outcome = ?, closure_reason = ?, closure_note = ?,\n                    archived_at = NOW(), archived_by_user_id = ?, archive_source = 'manual',\n                    archive_exempt_until = NULL, archive_hold_reason = NULL,\n                    reopened_at = NULL, reopened_by_user_id = NULL, updated_at = NOW()\n                WHERE id = ? AND deleted_at IS NULL AND archived_at IS NULL\n            ");
            $stmt->execute(array(
                $newStage, $paymentStatus, $paymentDueDate, $paymentPaidDate,
                $userId, $outcome, $reason !== '' ? $reason : null, $note !== '' ? $note : null,
                $userId, $opportunityId,
            ));
            if ($stmt->rowCount() !== 1) throw new RuntimeException('La oportunidad cambió mientras intentabas cerrarla.');

            OpportunityStageHistoryService::record($this->pdo, $opportunityId, $old['stage'] ?? null, $newStage, $userId, $userName, 'close');
            $descriptionParts = array('Resultado: ' . $this->outcomeLabel($outcome), 'Etapa: ' . ($old['stage'] ?? 'sin etapa') . ' -> ' . $newStage, 'Archivo: manual');
            if ($reason !== '') $descriptionParts[] = 'Motivo: ' . $reason;
            if ($note !== '') $descriptionParts[] = 'Nota: ' . $note;
            foreach ($billingChanges as $change) $descriptionParts[] = $change;
            $this->insertActivity($opportunityId, $old, 'Oportunidad cerrada y archivada', implode("\n", $descriptionParts), $userName);
            OpportunityStageHistoryService::lifecycle($this->pdo, $opportunityId, 'close_archive', $this->lifecycleSnapshot($old), array('stage' => $newStage, 'outcome' => $outcome, 'reason' => $reason, 'archive_source' => 'manual'), $userId, $userName, 'manual');
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }

        if ($outcome === 'invoiced') {
            try {
                (new RestockForecastService($this->pdo))->refreshFromOpportunity($opportunityId, $old['facto_document_id'] ?? null, 'opportunity_close');
            } catch (Throwable $e) {
                error_log('Restock forecast close ' . $opportunityId . ': ' . $e->getMessage());
            }
        }

        return array('id' => $opportunityId, 'old_stage' => $old['stage'] ?? null, 'stage' => $newStage, 'outcome' => $outcome, 'archived' => true, 'already_archived' => false);
    }

    public function reopen($opportunityId, $requestedStage, $reason, $nextAction, $nextActionDate, array $user, bool $allowFinancialReversal = false): array
    {
        $opportunityId = (int)$opportunityId;
        $requestedStage = trim((string)$requestedStage);
        $reason = trim((string)$reason);
        $nextAction = trim((string)$nextAction);
        $nextActionDate = trim((string)$nextActionDate);

        if ($opportunityId <= 0) throw new InvalidArgumentException('ID de oportunidad inválido.');
        if (!in_array($requestedStage, $this->activeStages, true)) throw new InvalidArgumentException('Etapa de retorno inválida.');
        if ($reason === '') throw new InvalidArgumentException('Debes indicar el motivo de reapertura.');
        if ($nextAction === '' || !preg_match('/^\d{4}-\d{2}-\d{2}$/', $nextActionDate)) {
            throw new InvalidArgumentException('Debes definir la próxima acción y su fecha.');
        }
        if ($nextActionDate < date('Y-m-d')) {
            throw new InvalidArgumentException('La fecha de la próxima acción no puede estar vencida.');
        }

        $old = $this->loadOpportunity($opportunityId, true);
        OpportunityAccessService::assertCanManage($old, $user, 'reabrir');
        if (empty($old['archived_at'])) return array('opportunity' => $old, 'already_open' => true);

        $wasInvoiced = (($old['closure_outcome'] ?? '') === 'invoiced' || ($old['stage'] ?? '') === 'facturada');
        if ($wasInvoiced) {
            $manager = in_array((string)($user['role'] ?? ''), array('admin','administracion'), true);
            if (!$manager || !$allowFinancialReversal) {
                throw new InvalidArgumentException('Las oportunidades facturadas no se reabren. Para una recompra crea una nueva oportunidad; una reversión requiere autorización administrativa.');
            }
            if ($this->hasValidIssuedDocument($old)) {
                throw new InvalidArgumentException('La factura asociada sigue vigente. Anúlala o regularízala antes de realizar una reversión administrativa.');
            }
        }

        $userId = isset($user['id']) ? (int)$user['id'] : null;
        $userName = $this->userName($user);
        $newProbability = $this->defaultProbability($requestedStage);
        $paymentStatus = $wasInvoiced ? 'no_facturado' : (($old['payment_status'] ?? 'no_facturado') === 'pagado' ? 'no_facturado' : ($old['payment_status'] ?? 'no_facturado'));

        try {
            $this->pdo->beginTransaction();
            $stmt = $this->pdo->prepare("\n                UPDATE crm_opportunities\n                SET stage = ?, probability = ?, payment_status = ?,\n                    commercial_closed_at = NULL, closed_at = NULL, closed_by_user_id = NULL,\n                    closure_outcome = NULL, closure_reason = NULL, closure_note = NULL,\n                    archived_at = NULL, archived_by_user_id = NULL, archive_source = NULL,\n                    archive_exempt_until = NULL, archive_hold_reason = NULL,\n                    reopened_at = NOW(), reopened_by_user_id = ?,\n                    next_action = ?, next_action_date = ?, lost_reason = NULL, updated_at = NOW()\n                WHERE id = ? AND deleted_at IS NULL AND archived_at IS NOT NULL\n            ");
            $stmt->execute(array($requestedStage, $newProbability, $paymentStatus, $userId, $nextAction, $nextActionDate, $opportunityId));
            if ($stmt->rowCount() !== 1) throw new RuntimeException('La oportunidad cambió mientras intentabas reabrirla.');

            OpportunityStageHistoryService::record($this->pdo, $opportunityId, $old['stage'] ?? null, $requestedStage, $userId, $userName, 'reopen');
            $description = 'Etapa: ' . ($old['stage'] ?? 'sin etapa') . ' -> ' . $requestedStage . "\nMotivo: " . $reason . "\nPróxima acción: " . $nextAction . ' · ' . $nextActionDate;
            if ($wasInvoiced) $description .= "\nReversión administrativa autorizada.";
            $this->insertActivity($opportunityId, $old, 'Oportunidad reabierta', $description, $userName);
            OpportunityStageHistoryService::lifecycle($this->pdo, $opportunityId, 'reopen', $this->lifecycleSnapshot($old), array('stage' => $requestedStage, 'reason' => $reason, 'next_action' => $nextAction, 'next_action_date' => $nextActionDate, 'financial_reversal' => $wasInvoiced), $userId, $userName, 'manual');
            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }

        return array('id' => $opportunityId, 'old_stage' => $old['stage'] ?? null, 'stage' => $requestedStage, 'already_open' => false);
    }

    public function archiveEligibleInvoiced(string $source = 'month_end'): array
    {
        if ($source === 'month_end') {
            $enabled = $this->dashboardSetting('crm_month_end_archive_enabled', '1');
            $runDay = max(1, min(28, (int)$this->dashboardSetting('crm_month_end_archive_day', '1')));
            if ($enabled !== '1' || (int)date('j') < $runDay) {
                return array('eligible' => 0, 'archived' => 0, 'skipped_invalid_dte' => 0, 'cutoff' => date('Y-m-01'), 'disabled' => true);
            }
        }
        $cutoff = date('Y-m-01');
        $stmt = $this->pdo->prepare("\n            SELECT * FROM crm_opportunities\n            WHERE deleted_at IS NULL\n              AND archived_at IS NULL\n              AND stage = 'facturada'\n              AND COALESCE(commercial_closed_at, closed_at, updated_at) < ?\n              AND (archive_exempt_until IS NULL OR archive_exempt_until < CURDATE())\n              AND (archive_hold_reason IS NULL OR archive_hold_reason = '')\n            ORDER BY id ASC\n        ");
        $stmt->execute(array($cutoff));
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $archived = 0;
        $skippedInvalidDte = 0;

        foreach ($rows as $row) {
            if (!$this->hasValidIssuedDocument($row)) {
                $skippedInvalidDte++;
                continue;
            }
            try {
                $this->pdo->beginTransaction();
                $update = $this->pdo->prepare("\n                    UPDATE crm_opportunities\n                    SET archived_at = NOW(), archived_by_user_id = NULL, archive_source = 'month_end',\n                        commercial_closed_at = COALESCE(commercial_closed_at, closed_at, updated_at),\n                        closed_at = COALESCE(closed_at, commercial_closed_at, updated_at),\n                        closure_outcome = 'invoiced',\n                        closure_reason = COALESCE(NULLIF(closure_reason,''), 'Archivo automático de cierre mensual'),\n                        updated_at = NOW()\n                    WHERE id = ? AND archived_at IS NULL AND stage = 'facturada'\n                ");
                $update->execute(array((int)$row['id']));
                if ($update->rowCount() === 1) {
                    $this->insertActivity((int)$row['id'], $row, 'Oportunidad archivada automáticamente', 'La oportunidad facturada fue retirada del kanban por el cierre mensual. La cobranza continúa en Finanzas.', 'Sistema');
                    OpportunityStageHistoryService::lifecycle($this->pdo, (int)$row['id'], 'archive', $this->lifecycleSnapshot($row), array('archive_source' => 'month_end', 'archived_at' => date('Y-m-d H:i:s')), null, 'Sistema', $source);
                    $archived++;
                }
                $this->pdo->commit();
            } catch (Throwable $e) {
                if ($this->pdo->inTransaction()) $this->pdo->rollBack();
                error_log('ICYLAB month-end archive failed opportunity ' . (int)$row['id'] . ': ' . $e->getMessage());
            }
        }

        return array('eligible' => count($rows), 'archived' => $archived, 'skipped_invalid_dte' => $skippedInvalidDte, 'cutoff' => $cutoff);
    }

    public function hasValidIssuedDocument(array $opportunity): bool
    {
        $factoId = trim((string)($opportunity['facto_document_id'] ?? ''));
        $opportunityId = (int)($opportunity['id'] ?? 0);
        if ($factoId === '' && $opportunityId <= 0) return false;

        $conditions = array();
        $params = array();
        if ($factoId !== '') {
            $conditions[] = 'fd.document_id = ?';
            $params[] = $factoId;
        }
        if ($opportunityId > 0) {
            $conditions[] = 'fd.crm_opportunity_id = ?';
            $params[] = $opportunityId;
            $conditions[] = 'fd.document_id IN (SELECT d.facto_document_id FROM crm_documents d WHERE d.opportunity_id=? AND d.facto_document_id IS NOT NULL)';
            $params[] = $opportunityId;
            $conditions[] = 'fd.crm_quote_id IN (SELECT q.id FROM crm_quotes q WHERE q.opportunity_id=?)';
            $params[] = $opportunityId;
            $conditions[] = 'fd.document_id IN (SELECT q.facto_document_id FROM crm_quotes q WHERE q.opportunity_id=? AND q.facto_document_id IS NOT NULL)';
            $params[] = $opportunityId;
            $normalizedTaxId = strtoupper(preg_replace('/[^0-9K]/i', '', (string)($opportunity['customer_tax_id'] ?? '')) ?: '');
            if ($normalizedTaxId !== '') {
                $conditions[] = "(fd.document_number IN (SELECT d.document_number FROM crm_documents d WHERE d.opportunity_id=? AND d.document_number IS NOT NULL) AND REPLACE(REPLACE(REPLACE(UPPER(fd.receiver_tax_id_code),'.',''),'-',''),' ','')=?)";
                $params[] = $opportunityId;
                $params[] = $normalizedTaxId;
                $conditions[] = "(fd.document_number IN (SELECT q.invoice_number FROM crm_quotes q WHERE q.opportunity_id=? AND q.invoice_number IS NOT NULL) AND REPLACE(REPLACE(REPLACE(UPPER(fd.receiver_tax_id_code),'.',''),'-',''),' ','')=?)";
                $params[] = $opportunityId;
                $params[] = $normalizedTaxId;
            }
        }

        $sql = "SELECT fd.* FROM facto_documents fd WHERE fd.environment='production' AND (" . implode(' OR ', $conditions) . ")
"
             . "AND fd.received_issued_flag=1 AND fd.document_status=1 "
             . "AND COALESCE(fd.payment_status,'')<>'anulado' "
             . "AND COALESCE(fd.document_type_taxbureau,'') IN ('33','34','39','110') "
             . "ORDER BY fd.issue_date DESC,fd.id DESC LIMIT 1";
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute($params);
        $document = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$document) return false;

        // Normaliza enlaces historicos para que las siguientes validaciones sean directas.
        if ($opportunityId > 0) {
            try {
                $this->pdo->prepare("UPDATE crm_opportunities SET facto_document_id=COALESCE(NULLIF(facto_document_id,''),?), updated_at=NOW() WHERE id=?")
                    ->execute(array($document['document_id'], $opportunityId));
                $this->pdo->prepare("UPDATE facto_documents SET crm_opportunity_id=COALESCE(crm_opportunity_id,?), crm_customer_id=COALESCE(crm_customer_id,?), link_status=CASE WHEN link_status='unlinked' THEN 'auto_linked' ELSE link_status END, linked_at=COALESCE(linked_at,NOW()) WHERE id=?")
                    ->execute(array($opportunityId, !empty($opportunity['customer_id']) ? (int)$opportunity['customer_id'] : null, (int)$document['id']));
            } catch (Throwable $e) {
                error_log('DTE link normalization opportunity ' . $opportunityId . ': ' . $e->getMessage());
            }
        }
        return true;
    }

    private function hasAcceptedQuote(int $opportunityId): bool
    {
        $stmt = $this->pdo->prepare("SELECT 1 FROM crm_quotes WHERE opportunity_id = ? AND status IN ('Aceptada','Facturada','Esperando pago','Pagada') LIMIT 1");
        $stmt->execute(array($opportunityId));
        return (bool)$stmt->fetchColumn();
    }

    private function loadOpportunity(int $opportunityId, bool $lock = false): array
    {
        $sql = "SELECT o.*, c.payment_terms, c.facto_payment_term FROM crm_opportunities o LEFT JOIN crm_customers c ON c.id=o.customer_id WHERE o.id=? AND o.deleted_at IS NULL LIMIT 1" . ($lock ? ' FOR UPDATE' : '');
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($opportunityId));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);
        if (!$row) throw new RuntimeException('Oportunidad no encontrada.');
        return $row;
    }

    private function insertActivity(int $opportunityId, array $opportunity, string $title, string $description, string $userName): void
    {
        $stmt = $this->pdo->prepare("\n            INSERT INTO crm_activities\n                (opportunity_id, customer_id, customer_name, activity_type, title, description, owner_name, activity_date)\n            VALUES (?, ?, ?, 'cambio_estado', ?, ?, ?, NOW())\n        ");
        $stmt->execute(array($opportunityId, !empty($opportunity['customer_id']) ? (int)$opportunity['customer_id'] : null, $opportunity['customer_name'] ?? null, $title, $description, $userName));
    }

    private function userName(array $user): string
    {
        return trim((string)($user['name'] ?? $user['email'] ?? 'Sistema')) ?: 'Sistema';
    }

    private function outcomeLabel(string $outcome): string
    {
        $labels = array('won' => 'Ganada', 'lost' => 'Perdida', 'invoiced' => 'Facturada', 'cancelled' => 'Cancelada', 'no_decision' => 'Sin decisión');
        return $labels[$outcome] ?? $outcome;
    }

    private function defaultProbability(string $stage): int
    {
        $values = array('nuevo' => 20, 'contactado' => 40, 'cotizando' => 60, 'negociacion' => 80);
        return $values[$stage] ?? 20;
    }

    private function dashboardSetting(string $key, string $default): string
    {
        try {
            $stmt = $this->pdo->prepare("SELECT setting_value FROM dashboard_settings WHERE setting_key = ? AND status = 'approved' LIMIT 1");
            $stmt->execute(array($key));
            $value = $stmt->fetchColumn();
            return $value === false ? $default : (string)$value;
        } catch (Throwable $e) {
            return $default;
        }
    }

    private function lifecycleSnapshot(array $row): array
    {
        $keys = array('stage','commercial_closed_at','closed_at','closure_outcome','closure_reason','archived_at','archive_source','payment_status','facto_document_id','next_action','next_action_date');
        $snapshot = array();
        foreach ($keys as $key) $snapshot[$key] = $row[$key] ?? null;
        return $snapshot;
    }
}
