<?php

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

/**
 * ICYLAB OS - Quote Lifecycle Service
 *
 * Centraliza transiciones de estado de cotizaciones y sincronización
 * comercial con oportunidades/cobranza.
 */

class QuoteLifecycleService
{
    private PDO $pdo;

    private array $allowedStatuses = array(
        'Borrador',
        'Enviada',
        'Aceptada',
        'Facturada',
        'Esperando pago',
        'Pagada',
        'Rechazada',
        'Vencida',
        'Anulada'
    );

    private array $invoiceStatuses = array('Facturada', 'Esperando pago', 'Pagada');
    private array $closedNegativeStatuses = array('Rechazada', 'Vencida', 'Anulada');

    public function __construct(PDO $pdo)
    {
        $this->pdo = $pdo;
    }

    public function allowedStatuses(): array
    {
        return $this->allowedStatuses;
    }

    public function invoiceStatuses(): array
    {
        return $this->invoiceStatuses;
    }

    public function validateStatus(string $status): string
    {
        $status = trim($status);
        if (!in_array($status, $this->allowedStatuses, true)) {
            throw new InvalidArgumentException('Estado de cotización inválido.');
        }
        return $status;
    }

    public function normalizeDate($date): ?string
    {
        $date = trim((string)$date);
        if ($date === '') {
            return null;
        }
        $ts = strtotime($date);
        return $ts ? date('Y-m-d', $ts) : null;
    }

    public function requiresInvoiceDate(string $status): bool
    {
        return in_array($status, $this->invoiceStatuses, true);
    }

    public function requiresPaymentDate(string $status): bool
    {
        return $status === 'Pagada';
    }

    public function changeStatus(int $quoteId, string $newStatus, array $options = array()): array
    {
        $newStatus = $this->validateStatus($newStatus);
        if ($quoteId <= 0) {
            throw new InvalidArgumentException('ID de cotización inválido.');
        }

        $manageTransaction = !$this->pdo->inTransaction();
        if ($manageTransaction) {
            $this->pdo->beginTransaction();
        }

        try {
            $quote = $this->loadQuote($quoteId, true);
            if (!$quote) {
                throw new RuntimeException('Cotización no encontrada.');
            }
            if (!empty($quote['deleted_at'])) {
                throw new RuntimeException('La cotización está eliminada. Restáurala antes de cambiar su estado.');
            }

            $previousStatus = (string)$quote['status'];
            $oldInvoiceValueForActivity = !empty($quote['invoice_issue_date']) ? $quote['invoice_issue_date'] : null;
            $oldPaymentValueForActivity = !empty($quote['payment_date']) ? $quote['payment_date'] : null;
            $invoiceIssueDate = $this->normalizeDate($options['invoice_issue_date'] ?? null);
            $paymentDate = $this->normalizeDate($options['payment_date'] ?? null);

            if ($this->requiresInvoiceDate($newStatus) && $invoiceIssueDate === null) {
                $invoiceIssueDate = !empty($quote['invoice_issue_date']) ? $quote['invoice_issue_date'] : date('Y-m-d');
            }

            if ($this->requiresPaymentDate($newStatus) && $paymentDate === null) {
                $paymentDate = !empty($quote['payment_date']) ? $quote['payment_date'] : date('Y-m-d');
            }

            $statusChanged = $previousStatus !== $newStatus;
            $dateChanged = false;
            $sets = array();
            $params = array();

            if ($statusChanged) {
                $sets[] = 'status = ?';
                $params[] = $newStatus;
            }

            if ($this->columnExists('crm_quotes', 'invoice_issue_date')) {
                $newInvoiceValue = $this->requiresInvoiceDate($newStatus) ? $invoiceIssueDate : null;
                $oldInvoiceValue = !empty($quote['invoice_issue_date']) ? $quote['invoice_issue_date'] : null;
                if ($newInvoiceValue !== $oldInvoiceValue) {
                    $sets[] = 'invoice_issue_date = ?';
                    $params[] = $newInvoiceValue;
                    $dateChanged = true;
                }
                $quote['invoice_issue_date'] = $newInvoiceValue;
            }

            if ($this->columnExists('crm_quotes', 'payment_date')) {
                $newPaymentValue = $this->requiresPaymentDate($newStatus) ? $paymentDate : null;
                $oldPaymentValue = !empty($quote['payment_date']) ? $quote['payment_date'] : null;
                if ($newPaymentValue !== $oldPaymentValue) {
                    $sets[] = 'payment_date = ?';
                    $params[] = $newPaymentValue;
                    $dateChanged = true;
                }
                $quote['payment_date'] = $newPaymentValue;
            }

            if (!empty($sets)) {
                $params[] = $quoteId;
                $stmt = $this->pdo->prepare('UPDATE crm_quotes SET ' . implode(', ', $sets) . ' WHERE id = ?');
                $stmt->execute($params);
            }

            $quote['status'] = $newStatus;
            $sync = $this->syncOpportunityFromQuote($quote, $previousStatus, $newStatus, array(
                'payment_date' => $paymentDate,
                'invoice_issue_date' => $invoiceIssueDate
            ));

            $recordActivity = array_key_exists('record_activity', $options) ? (bool)$options['record_activity'] : true;
            if ($recordActivity && ($statusChanged || $dateChanged || !empty($sync['changes']))) {
                $this->recordStatusActivity($quote, $previousStatus, $newStatus, array(
                    'owner_name' => $options['owner_name'] ?? $this->currentOwnerName(),
                    'status_changed' => $statusChanged,
                    'previous_invoice_issue_date' => $oldInvoiceValueForActivity,
                    'invoice_issue_date' => $invoiceIssueDate,
                    'previous_payment_date' => $oldPaymentValueForActivity,
                    'payment_date' => $paymentDate,
                    'sync_changes' => $sync['changes'] ?? array(),
                    'title' => $options['title'] ?? null,
                    'description_prefix' => $options['description_prefix'] ?? null
                ));
            }

            if ($statusChanged) {
                $this->notifyQuoteStatusChange($quote, $previousStatus, $newStatus, $sync);
            }

            if ($manageTransaction) {
                $this->pdo->commit();
            }

            return array(
                'quote_id' => $quoteId,
                'quote_number' => $quote['quote_number'],
                'previous_status' => $previousStatus,
                'new_status' => $newStatus,
                'status_changed' => $statusChanged,
                'date_changed' => $dateChanged,
                'opportunity_sync' => $sync
            );
        } catch (Throwable $e) {
            if ($manageTransaction && $this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    public function syncAfterQuoteSave(int $quoteId, ?string $previousStatus, string $newStatus, array $options = array()): array
    {
        $newStatus = $this->validateStatus($newStatus);
        if ($quoteId <= 0) {
            throw new InvalidArgumentException('ID de cotización inválido.');
        }

        $quote = $this->loadQuote($quoteId, true);
        if (!$quote) {
            throw new RuntimeException('Cotización no encontrada.');
        }
        if (!empty($quote['deleted_at'])) {
            throw new RuntimeException('La cotización está eliminada. Restáurala antes de sincronizarla.');
        }

        $oldStatus = $previousStatus !== null && $previousStatus !== '' ? $previousStatus : (string)$quote['status'];
        $sync = $this->syncOpportunityFromQuote($quote, $oldStatus, $newStatus, array(
            'payment_date' => $this->normalizeDate($options['payment_date'] ?? ($quote['payment_date'] ?? null)),
            'invoice_issue_date' => $this->normalizeDate($options['invoice_issue_date'] ?? ($quote['invoice_issue_date'] ?? null))
        ));

        $recordStatusActivity = !empty($options['record_status_activity']) && $oldStatus !== $newStatus;
        if ($recordStatusActivity) {
            $this->recordStatusActivity($quote, $oldStatus, $newStatus, array(
                'owner_name' => $options['owner_name'] ?? $this->currentOwnerName(),
                'invoice_issue_date' => $this->normalizeDate($options['invoice_issue_date'] ?? null),
                'payment_date' => $this->normalizeDate($options['payment_date'] ?? null),
                'sync_changes' => $sync['changes'] ?? array()
            ));
        }

        return $sync;
    }

    public function annulQuote(int $quoteId, array $options = array()): array
    {
        $quote = $this->loadQuote($quoteId, false);
        $label = $quote && !empty($quote['quote_number']) ? $quote['quote_number'] : ('ID ' . $quoteId);
        return $this->changeStatus($quoteId, 'Anulada', array(
            'owner_name' => $options['owner_name'] ?? $this->currentOwnerName(),
            'record_activity' => true,
            'title' => 'Cotización Anulada (' . $label . ')',
            'description_prefix' => 'El documento fue anulado lógicamente. Se conserva cabecera, ítems, documento asociado e historial comercial.'
        ));
    }

    public function softDelete(int $quoteId, array $options = array()): array
    {
        if ($quoteId <= 0) {
            throw new InvalidArgumentException('ID de cotización inválido.');
        }
        if (!$this->deletedColumnsAvailable()) {
            throw new RuntimeException('Falta ejecutar la migración P5 de soft delete de cotizaciones.');
        }

        $ownerName = trim((string)($options['owner_name'] ?? $this->currentOwnerName()));
        if ($ownerName === '') {
            $ownerName = 'Sistema';
        }
        $reason = trim((string)($options['reason'] ?? ''));

        $manageTransaction = !$this->pdo->inTransaction();
        if ($manageTransaction) {
            $this->pdo->beginTransaction();
        }

        try {
            $quote = $this->loadQuote($quoteId, true);
            if (!$quote) {
                throw new RuntimeException('Cotización no encontrada.');
            }

            if (!empty($quote['deleted_at'])) {
                if ($manageTransaction) {
                    $this->pdo->commit();
                }
                return array(
                    'quote_id' => $quoteId,
                    'quote_number' => $quote['quote_number'] ?? 'S/N',
                    'already_deleted' => true
                );
            }

            $stmt = $this->pdo->prepare("\n                UPDATE crm_quotes\n                SET deleted_at = NOW(),\n                    deleted_by = ?,\n                    deleted_reason = ?\n                WHERE id = ?\n                  AND deleted_at IS NULL\n            ");
            $stmt->execute(array($ownerName, $reason !== '' ? $reason : null, $quoteId));

            $this->recordSoftDeleteActivity($quote, $ownerName, $reason, false);

            if ($manageTransaction) {
                $this->pdo->commit();
            }

            return array(
                'quote_id' => $quoteId,
                'quote_number' => $quote['quote_number'] ?? 'S/N',
                'already_deleted' => false
            );
        } catch (Throwable $e) {
            if ($manageTransaction && $this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    public function restore(int $quoteId, array $options = array()): array
    {
        if ($quoteId <= 0) {
            throw new InvalidArgumentException('ID de cotización inválido.');
        }
        if (!$this->deletedColumnsAvailable()) {
            throw new RuntimeException('Falta ejecutar la migración P5 de soft delete de cotizaciones.');
        }

        $ownerName = trim((string)($options['owner_name'] ?? $this->currentOwnerName()));
        if ($ownerName === '') {
            $ownerName = 'Sistema';
        }

        $manageTransaction = !$this->pdo->inTransaction();
        if ($manageTransaction) {
            $this->pdo->beginTransaction();
        }

        try {
            $quote = $this->loadQuote($quoteId, true);
            if (!$quote) {
                throw new RuntimeException('Cotización no encontrada.');
            }

            if (empty($quote['deleted_at'])) {
                if ($manageTransaction) {
                    $this->pdo->commit();
                }
                return array(
                    'quote_id' => $quoteId,
                    'quote_number' => $quote['quote_number'] ?? 'S/N',
                    'already_active' => true
                );
            }

            $stmt = $this->pdo->prepare("\n                UPDATE crm_quotes\n                SET deleted_at = NULL,\n                    deleted_by = NULL,\n                    deleted_reason = NULL\n                WHERE id = ?\n            ");
            $stmt->execute(array($quoteId));

            $quote['deleted_at'] = null;
            $this->recordSoftDeleteActivity($quote, $ownerName, '', true);

            try {
                $this->syncAfterQuoteSave($quoteId, null, (string)($quote['status'] ?? 'Borrador'), array(
                    'owner_name' => $ownerName,
                    'record_status_activity' => false
                ));
            } catch (Throwable $syncError) {
                // La restauracion no debe fallar si la oportunidad fue eliminada o no existe.
            }

            if ($manageTransaction) {
                $this->pdo->commit();
            }

            return array(
                'quote_id' => $quoteId,
                'quote_number' => $quote['quote_number'] ?? 'S/N',
                'already_active' => false
            );
        } catch (Throwable $e) {
            if ($manageTransaction && $this->pdo->inTransaction()) {
                $this->pdo->rollBack();
            }
            throw $e;
        }
    }

    public function loadQuote(int $quoteId, bool $lock = false): ?array
    {
        $sql = "
            SELECT
                q.*,
                c.customer_name,
                c.commercial_name,
                c.legal_name,
                c.customer_tax_id,
                o.stage AS opportunity_stage,
                o.title AS opportunity_title,
                o.owner_name AS opportunity_owner_name,
                o.owner_user_id AS opportunity_owner_user_id,
                o.payment_status AS opportunity_payment_status,
                o.payment_due_date AS opportunity_payment_due_date,
                o.payment_paid_date AS opportunity_payment_paid_date,
                o.expected_value AS opportunity_expected_value,
                o.probability AS opportunity_probability,
                o.deleted_at AS opportunity_deleted_at
            FROM crm_quotes q
            LEFT JOIN crm_customers c ON c.id = q.customer_id
            LEFT JOIN crm_opportunities o ON o.id = q.opportunity_id
            WHERE q.id = ?
            LIMIT 1" . ($lock ? " FOR UPDATE" : "");

        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(array($quoteId));
        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        return $row ?: null;
    }

    private function syncOpportunityFromQuote(array $quote, string $previousStatus, string $newStatus, array $options = array()): array
    {
        $opportunityId = !empty($quote['opportunity_id']) ? (int)$quote['opportunity_id'] : 0;
        if ($opportunityId <= 0) {
            return array('synced' => false, 'reason' => 'Venta directa sin oportunidad asociada.', 'changes' => array());
        }

        $stmt = $this->pdo->prepare("SELECT * FROM crm_opportunities WHERE id = ? AND deleted_at IS NULL LIMIT 1 FOR UPDATE");
        $stmt->execute(array($opportunityId));
        $opportunity = $stmt->fetch(PDO::FETCH_ASSOC);

        if (!$opportunity) {
            return array('synced' => false, 'reason' => 'Oportunidad eliminada o inexistente.', 'changes' => array());
        }

        $updates = array();
        $params = array();
        $changes = array();
        $total = isset($quote['total']) ? (float)$quote['total'] : 0.0;
        $stage = (string)$opportunity['stage'];
        $paymentStatus = (string)$opportunity['payment_status'];
        $expectedValue = (float)$opportunity['expected_value'];
        $probability = (int)$opportunity['probability'];

        $targetStage = null;
        $targetPaymentStatus = null;
        $targetProbability = null;
        $targetLostReason = null;
        $targetPaidDate = null;
        $shouldUpdateExpectedValue = false;

        if (in_array($newStatus, array('Borrador', 'Enviada'), true)) {
            $targetStage = in_array($stage, array('perdida', 'facturada', 'ganada'), true) ? null : 'cotizando';
            $targetPaymentStatus = 'no_facturado';
            $shouldUpdateExpectedValue = true;
        } elseif ($newStatus === 'Aceptada') {
            $targetStage = 'ganada';
            $targetPaymentStatus = 'no_facturado';
            $targetProbability = 100;
            $shouldUpdateExpectedValue = true;
        } elseif (in_array($newStatus, array('Facturada', 'Esperando pago'), true)) {
            $targetStage = 'facturada';
            $targetPaymentStatus = 'por_cobrar';
            $targetProbability = 100;
            $shouldUpdateExpectedValue = true;
        } elseif ($newStatus === 'Pagada') {
            $targetStage = 'facturada';
            $targetPaymentStatus = 'pagado';
            $targetProbability = 100;
            $targetPaidDate = $options['payment_date'] ?? (!empty($quote['payment_date']) ? $quote['payment_date'] : date('Y-m-d'));
            $shouldUpdateExpectedValue = true;
        } elseif (in_array($newStatus, $this->closedNegativeStatuses, true)) {
            if (!$this->hasOtherOpenQuotes($opportunityId, (int)$quote['id'])) {
                $targetStage = 'perdida';
                $targetPaymentStatus = 'no_facturado';
                $targetProbability = 0;
                $targetLostReason = 'Cotización ' . ($quote['quote_number'] ?? '') . ' ' . $newStatus;
            }
        }

        if ($targetStage !== null && $targetStage !== $stage) {
            $updates[] = 'stage = ?';
            $params[] = $targetStage;
            $changes[] = 'Etapa: ' . $stage . ' -> ' . $targetStage;
        }

        if ($targetPaymentStatus !== null && $targetPaymentStatus !== $paymentStatus) {
            $updates[] = 'payment_status = ?';
            $params[] = $targetPaymentStatus;
            $changes[] = 'Cobranza: ' . $paymentStatus . ' -> ' . $targetPaymentStatus;
        }

        if ($shouldUpdateExpectedValue && round($expectedValue, 2) !== round($total, 2)) {
            $updates[] = 'expected_value = ?';
            $params[] = $total;
            $changes[] = 'Valor esperado: ' . $this->money($expectedValue) . ' -> ' . $this->money($total);
        }

        if ($targetProbability !== null && $targetProbability !== $probability) {
            $updates[] = 'probability = ?';
            $params[] = $targetProbability;
            $changes[] = 'Probabilidad: ' . $probability . '% -> ' . $targetProbability . '%';
        }

        if ($targetPaidDate !== null && $targetPaidDate !== ($opportunity['payment_paid_date'] ?? null)) {
            $updates[] = 'payment_paid_date = ?';
            $params[] = $targetPaidDate;
            $changes[] = 'Fecha de pago: ' . (($opportunity['payment_paid_date'] ?? '') ?: 'sin fecha') . ' -> ' . $targetPaidDate;
        }

        if ($targetLostReason !== null && $targetLostReason !== ($opportunity['lost_reason'] ?? null)) {
            $updates[] = 'lost_reason = ?';
            $params[] = $targetLostReason;
            $changes[] = 'Motivo de pérdida: ' . $targetLostReason;
        }

        if (!empty($updates)) {
            $updates[] = 'updated_at = NOW()';
            $params[] = $opportunityId;
            $stmtUpdate = $this->pdo->prepare('UPDATE crm_opportunities SET ' . implode(', ', $updates) . ' WHERE id = ?');
            $stmtUpdate->execute($params);
        }

        return array(
            'synced' => !empty($updates),
            'opportunity_id' => $opportunityId,
            'changes' => $changes
        );
    }

    private function hasOtherOpenQuotes(int $opportunityId, int $excludeQuoteId): bool
    {
        $deletedFilter = $this->columnExists('crm_quotes', 'deleted_at') ? "\n              AND deleted_at IS NULL" : '';
        $stmt = $this->pdo->prepare("\n            SELECT COUNT(*)\n            FROM crm_quotes\n            WHERE opportunity_id = ?\n              AND id <> ?" . $deletedFilter . "\n              AND status NOT IN ('Rechazada', 'Vencida', 'Anulada')\n        ");
        $stmt->execute(array($opportunityId, $excludeQuoteId));
        return (int)$stmt->fetchColumn() > 0;
    }


    private function notifyQuoteStatusChange(array $quote, string $previousStatus, string $newStatus, array $sync = array()): void
    {
        $quoteId = (int)($quote['id'] ?? 0);
        if ($quoteId <= 0) {
            return;
        }

        $quoteNumber = (string)($quote['quote_number'] ?? 'S/N');
        $customerName = $this->displayCustomer($quote);
        $opportunityId = !empty($quote['opportunity_id']) ? (int)$quote['opportunity_id'] : 0;
        $recipientUserId = !empty($quote['opportunity_owner_user_id']) ? (int)$quote['opportunity_owner_user_id'] : notification_actor_user_id();
        $quoteUrl = '/modules/crm/quote_view.php?id=' . $quoteId;
        $opportunityUrl = $opportunityId > 0 ? '/modules/crm/opportunity.php?id=' . $opportunityId : $quoteUrl;
        $total = $this->money((float)($quote['total'] ?? 0));
        $body = 'Cliente: ' . $customerName . "\nCotizacion: " . $quoteNumber . "\nEstado: " . $previousStatus . ' -> ' . $newStatus . "\nTotal: " . $total;

        if ($newStatus === 'Aceptada') {
            notify_crm_owner_or_admin_event(
                'quote.accepted',
                $recipientUserId,
                'Cotizacion aceptada: ' . $quoteNumber,
                $body,
                $quoteUrl,
                'quote',
                $quoteId,
                array('dedupe_key' => 'quote.accepted:' . $quoteId, 'metadata' => array('total' => (float)($quote['total'] ?? 0), 'discount_percent' => (float)($quote['discount_percent'] ?? 0)))
            );
        } elseif ($newStatus === 'Rechazada') {
            notify_crm_owner_or_admin_event(
                'quote.rejected',
                $recipientUserId,
                'Cotizacion rechazada: ' . $quoteNumber,
                $body,
                $quoteUrl,
                'quote',
                $quoteId,
                array('dedupe_key' => 'quote.rejected:' . $quoteId, 'metadata' => array('total' => (float)($quote['total'] ?? 0), 'discount_percent' => (float)($quote['discount_percent'] ?? 0)))
            );
        }

        notify_discount_exception_event(
            $quoteId,
            $quoteNumber,
            $customerName,
            (float)($quote['discount_percent'] ?? 0),
            (float)($quote['total'] ?? 0),
            array('dedupe_key' => 'quote.discount_high:' . $quoteId . ':' . number_format((float)($quote['discount_percent'] ?? 0), 2, '.', ''))
        );

        if ($opportunityId <= 0) {
            return;
        }

        if (in_array($newStatus, array('Facturada', 'Esperando pago'), true)) {
            notify_crm_owner_or_admin_event(
                'opportunity.facturada',
                $recipientUserId,
                'Oportunidad facturada por cotizacion: ' . $quoteNumber,
                $body,
                $opportunityUrl,
                'opportunity',
                $opportunityId,
                array('dedupe_key' => 'opportunity.facturada:' . $opportunityId, 'metadata' => array('total' => (float)($quote['total'] ?? 0), 'quote_id' => $quoteId))
            );

            notify_crm_owner_or_admin_event(
                'billing.por_cobrar',
                $recipientUserId,
                'Cobranza por cobrar: ' . $quoteNumber,
                $body,
                $opportunityUrl,
                'opportunity',
                $opportunityId,
                array('dedupe_key' => 'billing.por_cobrar:' . $opportunityId . ':' . $quoteId, 'metadata' => array('total' => (float)($quote['total'] ?? 0), 'quote_id' => $quoteId))
            );
        } elseif ($newStatus === 'Pagada') {
            notify_crm_owner_or_admin_event(
                'billing.paid',
                $recipientUserId,
                'Cobranza pagada: ' . $quoteNumber,
                $body,
                $opportunityUrl,
                'opportunity',
                $opportunityId,
                array('dedupe_key' => 'billing.paid:' . $opportunityId . ':' . $quoteId, 'metadata' => array('total' => (float)($quote['total'] ?? 0), 'quote_id' => $quoteId))
            );
        }
    }

    private function recordStatusActivity(array $quote, string $previousStatus, string $newStatus, array $options = array()): void
    {
        $ownerName = $options['owner_name'] ?? $this->currentOwnerName();
        $customerName = $this->displayCustomer($quote);
        $quoteNumber = $quote['quote_number'] ?? 'S/N';
        $title = $options['title'] ?? ('Cambio de Estado de Cotización (' . $newStatus . ')');

        $descriptionParts = array();
        if (!empty($options['description_prefix'])) {
            $descriptionParts[] = $options['description_prefix'];
        }

        $statusChanged = array_key_exists('status_changed', $options) ? (bool)$options['status_changed'] : ($previousStatus !== $newStatus);
        if ($statusChanged) {
            $descriptionParts[] = 'Estado: ' . $previousStatus . ' -> ' . $newStatus;
        }

        $oldInvoice = $this->normalizeDate($options['previous_invoice_issue_date'] ?? null);
        $newInvoice = $this->normalizeDate($options['invoice_issue_date'] ?? null);
        if ($oldInvoice !== $newInvoice) {
            $descriptionParts[] = 'Emisión factura: ' . ($oldInvoice ?: 'Sin definir') . ' -> ' . ($newInvoice ?: 'Sin definir');
        }

        $oldPayment = $this->normalizeDate($options['previous_payment_date'] ?? null);
        $newPayment = $this->normalizeDate($options['payment_date'] ?? null);
        if ($oldPayment !== $newPayment) {
            $descriptionParts[] = 'Fecha de pago: ' . ($oldPayment ?: 'Sin definir') . ' -> ' . ($newPayment ?: 'Sin definir');
        }

        if (!empty($options['sync_changes']) && is_array($options['sync_changes'])) {
            $descriptionParts[] = 'Sincronización comercial: ' . implode('; ', $options['sync_changes']) . '.';
        }

        if (empty($descriptionParts)) {
            return;
        }

        $stmt = $this->pdo->prepare("
            INSERT INTO crm_activities
            (opportunity_id, customer_id, customer_name, activity_type, title, description, activity_date, owner_name, created_at)
            VALUES (?, ?, ?, 'cambio_estado', ?, ?, NOW(), ?, NOW())
        ");
        $stmt->execute(array(
            !empty($quote['opportunity_id']) ? (int)$quote['opportunity_id'] : null,
            !empty($quote['customer_id']) ? (int)$quote['customer_id'] : null,
            $customerName,
            $title,
            implode("\n", $descriptionParts),
            $ownerName
        ));
    }

    private function recordSoftDeleteActivity(array $quote, string $ownerName, string $reason, bool $restored): void
    {
        $customerName = $this->displayCustomer($quote);
        $quoteNumber = $quote['quote_number'] ?? 'S/N';
        $title = $restored ? 'Cotización Restaurada (' . $quoteNumber . ')' : 'Cotización Eliminada (' . $quoteNumber . ')';

        $descriptionParts = array();
        if ($restored) {
            $descriptionParts[] = 'Cotización restaurada. Vuelve a quedar disponible en el listado comercial.';
        } else {
            $descriptionParts[] = 'Cotización eliminada mediante soft delete. No se borra cabecera, ítems, documento asociado ni historial comercial.';
        }
        $descriptionParts[] = 'Total facturable: ' . $this->money((float)($quote['total'] ?? 0)) . '.';
        if (!$restored && $reason !== '') {
            $descriptionParts[] = 'Motivo: ' . $reason;
        }

        $stmt = $this->pdo->prepare("\n            INSERT INTO crm_activities\n            (opportunity_id, customer_id, customer_name, activity_type, title, description, activity_date, owner_name, created_at)\n            VALUES (?, ?, ?, 'cotizacion', ?, ?, NOW(), ?, NOW())\n        ");
        $stmt->execute(array(
            !empty($quote['opportunity_id']) ? (int)$quote['opportunity_id'] : null,
            !empty($quote['customer_id']) ? (int)$quote['customer_id'] : null,
            $customerName,
            $title,
            implode("\n", $descriptionParts),
            $ownerName
        ));
    }

    private function deletedColumnsAvailable(): bool
    {
        return $this->columnExists('crm_quotes', 'deleted_at')
            && $this->columnExists('crm_quotes', 'deleted_by')
            && $this->columnExists('crm_quotes', 'deleted_reason');
    }

    private function displayCustomer(array $row): string
    {
        if (!empty($row['legal_name'])) return (string)$row['legal_name'];
        if (!empty($row['commercial_name'])) return (string)$row['commercial_name'];
        if (!empty($row['customer_name'])) return (string)$row['customer_name'];
        return 'Cliente General';
    }

    private function currentOwnerName(): string
    {
        if (function_exists('current_user')) {
            $user = current_user();
            if (!empty($user['name'])) return $user['name'];
            if (!empty($user['email'])) return $user['email'];
        }
        return 'Sistema';
    }

    private function columnExists(string $table, string $column): bool
    {
        static $cache = array();
        $key = $table . '.' . $column;

        if (array_key_exists($key, $cache)) {
            return $cache[$key];
        }

        $safeTable = str_replace('`', '', $table);
        $stmt = $this->pdo->prepare("SHOW COLUMNS FROM `{$safeTable}` LIKE ?");
        $stmt->execute(array($column));
        $cache[$key] = (bool)$stmt->fetch(PDO::FETCH_ASSOC);

        return $cache[$key];
    }

    private function money(float $value): string
    {
        if (function_exists('clp')) {
            return clp($value);
        }
        return '$' . number_format($value, 0, ',', '.');
    }
}
