<?php

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

class NotificationService
{
    /** @var PDO */
    private $pdo;
    /** @var array */
    private $tableCache = [];

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

    public static function eventTypes(): array
    {
        return [
            'opportunity.assigned' => [
                'label' => 'Oportunidad asignada',
                'module' => 'crm',
                'priority' => 'info',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando una oportunidad queda asignada a un usuario.',
            ],
            'opportunity.facturada' => [
                'label' => 'Oportunidad facturada',
                'module' => 'crm',
                'priority' => 'success',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando una oportunidad pasa a etapa facturada.',
            ],
            'billing.por_cobrar' => [
                'label' => 'Cobranza por cobrar',
                'module' => 'crm',
                'priority' => 'info',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando una cobranza queda pendiente de pago.',
            ],
            'billing.overdue' => [
                'label' => 'Cobranza atrasada',
                'module' => 'crm',
                'priority' => 'critical',
                'critical' => true,
                'email_default' => true,
                'description' => 'Cuando una cobranza supera su vencimiento.',
            ],
            'billing.paid' => [
                'label' => 'Cobranza pagada',
                'module' => 'crm',
                'priority' => 'success',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando una cobranza queda marcada como pagada.',
            ],
            'quote.created' => [
                'label' => 'Cotización creada',
                'module' => 'crm',
                'priority' => 'info',
                'critical' => false,
                'email_default' => false,
                'description' => 'Cuando se crea una nueva cotización.',
            ],
            'quote.accepted' => [
                'label' => 'Cotización aceptada',
                'module' => 'crm',
                'priority' => 'success',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando una cotización pasa a estado aceptada.',
            ],
            'quote.rejected' => [
                'label' => 'Cotización rechazada',
                'module' => 'crm',
                'priority' => 'warning',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando una cotización pasa a estado rechazada.',
            ],
            'next_action.due' => [
                'label' => 'Próxima acción vencida o próxima',
                'module' => 'crm',
                'priority' => 'warning',
                'critical' => false,
                'email_default' => true,
                'description' => 'Recordatorio de próximas acciones vencidas o próximas a vencer.',
            ],
            'document.tax_associated' => [
                'label' => 'Documento tributario asociado',
                'module' => 'crm',
                'priority' => 'info',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando se asocia una factura, nota de crédito o nota de débito a un cliente.',
            ],
            'system.sync_error' => [
                'label' => 'Error de sincronización o proceso automático',
                'module' => 'system',
                'priority' => 'critical',
                'critical' => true,
                'email_default' => true,
                'description' => 'Cuando un proceso automático falla.',
            ],
            'next_action.overdue_escalated' => [
                'label' => 'Próxima acción vencida escalada',
                'module' => 'crm',
                'priority' => 'critical',
                'critical' => true,
                'email_default' => true,
                'description' => 'Cuando una próxima acción permanece vencida por más de 48 horas.',
            ],
            'quote.accepted.no_tax_document' => [
                'label' => 'Cotización aceptada sin documento tributario',
                'module' => 'crm',
                'priority' => 'warning',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando una cotización aceptada queda sin factura o documento tributario asociado después de 24 horas.',
            ],
            'billing.due_today' => [
                'label' => 'Cobranza vence hoy',
                'module' => 'crm',
                'priority' => 'warning',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando una cobranza pendiente vence durante el día.',
            ],
            'billing.overdue_critical' => [
                'label' => 'Cobranza atrasada crítica',
                'module' => 'crm',
                'priority' => 'critical',
                'critical' => true,
                'email_default' => true,
                'description' => 'Cuando una cobranza permanece atrasada por tres días o más.',
            ],
            'opportunity.high_value' => [
                'label' => 'Oportunidad de alto valor',
                'module' => 'crm',
                'priority' => 'warning',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando una oportunidad supera el umbral comercial ejecutivo.',
            ],
            'opportunity.stalled_high_value' => [
                'label' => 'Oportunidad de alto valor estancada',
                'module' => 'crm',
                'priority' => 'critical',
                'critical' => true,
                'email_default' => true,
                'description' => 'Cuando una oportunidad relevante queda sin movimiento comercial reciente.',
            ],
            'quote.discount_high' => [
                'label' => 'Descuento comercial alto',
                'module' => 'crm',
                'priority' => 'warning',
                'critical' => false,
                'email_default' => true,
                'description' => 'Cuando una cotización supera el umbral de descuento definido para control de margen.',
            ],
        ];
    }

    public function isReady(): bool
    {
        return $this->tableExists('notifications') && $this->tableExists('notification_events');
    }

    public function notify(string $eventType, array $payload = []): array
    {
        if (!$this->isReady()) {
            return ['success' => false, 'reason' => 'notification_tables_missing'];
        }

        $catalog = self::eventTypes();
        $definition = $catalog[$eventType] ?? [
            'label' => $eventType,
            'module' => $payload['module'] ?? 'system',
            'priority' => $payload['priority'] ?? 'info',
            'critical' => !empty($payload['critical']),
            'email_default' => false,
            'description' => '',
        ];

        $payload = $this->applyDefaultGroupRouting($eventType, $payload);
        $recipients = $this->resolveRecipients($payload);
        if (empty($recipients)) {
            return ['success' => false, 'reason' => 'no_recipients'];
        }

        $module = (string)($payload['module'] ?? $definition['module'] ?? 'system');
        $priority = $this->normalizePriority((string)($payload['priority'] ?? $definition['priority'] ?? 'info'));
        $critical = array_key_exists('critical', $payload) ? (bool)$payload['critical'] : (bool)($definition['critical'] ?? false);
        $title = trim((string)($payload['title'] ?? $definition['label'] ?? $eventType));
        $body = trim((string)($payload['body'] ?? ''));
        $url = trim((string)($payload['url'] ?? ''));
        $relatedType = trim((string)($payload['related_type'] ?? '')) ?: null;
        $relatedId = isset($payload['related_id']) && $payload['related_id'] !== '' ? (int)$payload['related_id'] : null;
        $actorUserId = isset($payload['actor_user_id']) && $payload['actor_user_id'] !== '' ? (int)$payload['actor_user_id'] : null;
        $metadata = $payload['metadata'] ?? [];
        $dedupeKey = trim((string)($payload['dedupe_key'] ?? '')) ?: null;

        $eventId = $this->insertEvent([
            'event_type' => $eventType,
            'module' => $module,
            'priority' => $priority,
            'title' => $title,
            'body' => $body,
            'url' => $url,
            'related_type' => $relatedType,
            'related_id' => $relatedId,
            'actor_user_id' => $actorUserId,
            'metadata' => $metadata,
            'dedupe_key' => $dedupeKey,
        ]);

        if ($eventId <= 0) {
            return ['success' => false, 'reason' => 'duplicate_or_event_not_created'];
        }

        $createdNotifications = 0;
        $queuedEmails = 0;

        foreach ($recipients as $recipient) {
            $userId = (int)$recipient['id'];
            $notificationId = $this->insertNotification($eventId, $userId, [
                'title' => $title,
                'body' => $body,
                'url' => $url,
                'module' => $module,
                'related_type' => $relatedType,
                'related_id' => $relatedId,
                'priority' => $priority,
            ]);

            if ($notificationId > 0) {
                $createdNotifications++;
            }

            $forceEmail = !empty($payload['force_email']) || $critical;
            if ($this->shouldQueueEmail($userId, $eventType, $definition, $forceEmail)) {
                $email = trim((string)($recipient['email'] ?? ''));
                if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                    $rendered = $this->renderEmail($eventType, [
                        'title' => $title,
                        'body' => $body,
                        'url' => $url,
                        'module' => $module,
                        'event_type' => $eventType,
                        'priority' => $priority,
                        'recipient_name' => $recipient['name'] ?? '',
                    ]);
                    $queuedEmails += $this->queueEmail($eventId, $notificationId, $userId, $email, $rendered) ? 1 : 0;
                }
            }
        }

        return [
            'success' => true,
            'event_id' => $eventId,
            'notifications' => $createdNotifications,
            'emails_queued' => $queuedEmails,
        ];
    }

    public function unreadCount(int $userId): int
    {
        if (!$this->isReady() || $userId <= 0) return 0;
        $stmt = $this->pdo->prepare('SELECT COUNT(*) FROM notifications WHERE user_id = ? AND read_at IS NULL');
        $stmt->execute([$userId]);
        return (int)$stmt->fetchColumn();
    }

    public function recent(int $userId, int $limit = 10): array
    {
        if (!$this->isReady() || $userId <= 0) return [];
        $limit = max(1, min(50, $limit));
        $stmt = $this->pdo->prepare("\n            SELECT n.*, e.event_type\n            FROM notifications n\n            JOIN notification_events e ON e.id = n.event_id\n            WHERE n.user_id = ?\n            ORDER BY n.created_at DESC, n.id DESC\n            LIMIT " . $limit . "\n        ");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function listForUser(int $userId, int $limit = 100, bool $unreadOnly = false): array
    {
        if (!$this->isReady() || $userId <= 0) return [];
        $limit = max(1, min(200, $limit));
        $where = 'n.user_id = ?';
        if ($unreadOnly) {
            $where .= ' AND n.read_at IS NULL';
        }
        $stmt = $this->pdo->prepare("\n            SELECT n.*, e.event_type\n            FROM notifications n\n            JOIN notification_events e ON e.id = n.event_id\n            WHERE {$where}\n            ORDER BY n.created_at DESC, n.id DESC\n            LIMIT " . $limit . "\n        ");
        $stmt->execute([$userId]);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function markRead(int $notificationId, int $userId): bool
    {
        if (!$this->isReady() || $notificationId <= 0 || $userId <= 0) return false;
        $stmt = $this->pdo->prepare('UPDATE notifications SET read_at = COALESCE(read_at, NOW()) WHERE id = ? AND user_id = ?');
        $stmt->execute([$notificationId, $userId]);
        return $stmt->rowCount() > 0;
    }

    public function markAllRead(int $userId): int
    {
        if (!$this->isReady() || $userId <= 0) return 0;
        $stmt = $this->pdo->prepare('UPDATE notifications SET read_at = NOW() WHERE user_id = ? AND read_at IS NULL');
        $stmt->execute([$userId]);
        return $stmt->rowCount();
    }

    public function preferences(int $userId): array
    {
        $catalog = self::eventTypes();
        $result = [];
        foreach ($catalog as $type => $definition) {
            $result[$type] = [
                'event_type' => $type,
                'label' => $definition['label'],
                'description' => $definition['description'],
                'critical' => (bool)$definition['critical'],
                'email_enabled' => (bool)$definition['email_default'],
            ];
        }

        if (!$this->tableExists('notification_preferences') || $userId <= 0) {
            return $result;
        }

        $stmt = $this->pdo->prepare('SELECT event_type, email_enabled FROM notification_preferences WHERE user_id = ?');
        $stmt->execute([$userId]);
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            if (isset($result[$row['event_type']])) {
                $result[$row['event_type']]['email_enabled'] = (bool)$row['email_enabled'];
            }
        }

        return $result;
    }

    public function setEmailPreference(int $userId, string $eventType, bool $enabled): bool
    {
        if (!$this->tableExists('notification_preferences') || $userId <= 0) return false;
        if (!isset(self::eventTypes()[$eventType])) return false;

        $stmt = $this->pdo->prepare("\n            INSERT INTO notification_preferences (user_id, event_type, email_enabled, created_at, updated_at)\n            VALUES (?, ?, ?, NOW(), NOW())\n            ON DUPLICATE KEY UPDATE email_enabled = VALUES(email_enabled), updated_at = NOW()\n        ");
        $stmt->execute([$userId, $eventType, $enabled ? 1 : 0]);
        return true;
    }

    public function processEmailQueue(int $limit = 50): array
    {
        if (!$this->tableExists('notification_deliveries')) {
            return ['processed' => 0, 'sent' => 0, 'failed' => 0];
        }

        $limit = max(1, min(200, $limit));
        $stmt = $this->pdo->prepare("\n            SELECT *\n            FROM notification_deliveries\n            WHERE channel = 'email'\n              AND status IN ('queued', 'failed')\n              AND attempts < max_attempts\n              AND (next_attempt_at IS NULL OR next_attempt_at <= NOW())\n            ORDER BY priority_sort ASC, created_at ASC\n            LIMIT " . $limit . "\n        ");
        $stmt->execute();
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $emailService = new EmailService();
        $processed = 0;
        $sent = 0;
        $failed = 0;

        foreach ($rows as $row) {
            $processed++;
            $deliveryId = (int)$row['id'];
            $this->pdo->prepare("UPDATE notification_deliveries SET status = 'sending', attempts = attempts + 1, updated_at = NOW() WHERE id = ?")->execute([$deliveryId]);

            try {
                $emailService->send($row['recipient'], $row['subject'], $row['body_html'], $row['body_text'] ?? '');
                $this->pdo->prepare("\n                    UPDATE notification_deliveries\n                    SET status = 'sent', sent_at = NOW(), last_error = NULL, updated_at = NOW()\n                    WHERE id = ?\n                ")->execute([$deliveryId]);
                $sent++;
            } catch (Throwable $e) {
                $attempt = ((int)$row['attempts']) + 1;
                $delayMinutes = min(1440, (int)pow(2, max(0, $attempt - 1)) * 5);
                $finalStatus = $attempt >= (int)$row['max_attempts'] ? 'failed' : 'failed';
                $this->pdo->prepare("\n                    UPDATE notification_deliveries\n                    SET status = ?, last_error = ?, next_attempt_at = DATE_ADD(NOW(), INTERVAL ? MINUTE), updated_at = NOW()\n                    WHERE id = ?\n                ")->execute([$finalStatus, mb_substr($e->getMessage(), 0, 1000), $delayMinutes, $deliveryId]);
                $failed++;
            }
        }

        return ['processed' => $processed, 'sent' => $sent, 'failed' => $failed];
    }

    public function processDueActionNotifications(int $daysAhead = 1): int
    {
        if (!$this->isReady()) return 0;
        $daysAhead = max(0, min(14, $daysAhead));

        $stmt = $this->pdo->prepare("\n            SELECT id, customer_id, customer_name, title, owner_user_id, next_action, next_action_date\n            FROM crm_opportunities\n            WHERE deleted_at IS NULL\n              AND owner_user_id IS NOT NULL\n              AND next_action IS NOT NULL\n              AND next_action <> ''\n              AND next_action_date IS NOT NULL\n              AND stage NOT IN ('perdida', 'facturada')\n              AND next_action_date <= DATE_ADD(CURDATE(), INTERVAL ? DAY)\n            ORDER BY next_action_date ASC\n            LIMIT 100\n        ");
        $stmt->execute([$daysAhead]);
        $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

        $count = 0;
        foreach ($rows as $row) {
            $dueTs = strtotime((string)$row['next_action_date']);
            $isOverdue = $dueTs !== false && $dueTs < strtotime(date('Y-m-d'));
            $daysOverdue = $isOverdue ? max(1, (int)floor((strtotime(date('Y-m-d')) - $dueTs) / 86400)) : 0;
            $title = $isOverdue ? 'Próxima acción vencida' : 'Próxima acción próxima';
            $body = ($row['next_action'] ?: 'Acción pendiente') . ' · ' . ($row['customer_name'] ?: 'Cliente') . ' · vencimiento: ' . $row['next_action_date'];
            $result = $this->notify('next_action.due', [
                'user_id' => (int)$row['owner_user_id'],
                'title' => $title,
                'body' => $body,
                'module' => 'crm',
                'priority' => $isOverdue ? 'warning' : 'info',
                'related_type' => 'opportunity',
                'related_id' => (int)$row['id'],
                'url' => '/modules/crm/opportunity.php?id=' . (int)$row['id'],
                'dedupe_key' => 'next_action:' . (int)$row['id'] . ':' . $row['next_action_date'] . ':' . date('Y-m-d'),
            ]);
            if (!empty($result['success'])) $count++;

            if ($daysOverdue >= 2) {
                $escalated = $this->notify('next_action.overdue_escalated', [
                    'groups' => ['ceo', 'commercial_direction'],
                    'title' => 'Acción comercial vencida hace ' . $daysOverdue . ' días',
                    'body' => 'Cliente: ' . ($row['customer_name'] ?: 'Cliente') . "\nOportunidad: " . ($row['title'] ?: 'Sin título') . "\nAcción pendiente: " . ($row['next_action'] ?: 'Sin detalle') . "\nResponsable ID: " . (int)$row['owner_user_id'] . "\nVencimiento: " . $row['next_action_date'],
                    'module' => 'crm',
                    'priority' => 'critical',
                    'critical' => true,
                    'related_type' => 'opportunity',
                    'related_id' => (int)$row['id'],
                    'url' => '/modules/crm/opportunity.php?id=' . (int)$row['id'],
                    'dedupe_key' => 'next_action.escalated:' . (int)$row['id'] . ':' . date('Y-m-d'),
                ]);
                if (!empty($escalated['success'])) $count++;
            }
        }

        return $count;
    }

    public function processExecutiveMvpNotifications(): array
    {
        if (!$this->isReady()) {
            return ['processed' => 0, 'high_value_stalled' => 0, 'quote_no_tax_document' => 0, 'billing_due_today' => 0, 'billing_overdue_critical' => 0];
        }

        $result = [
            'processed' => 0,
            'high_value_stalled' => $this->processHighValueStalledOpportunities(),
            'quote_no_tax_document' => $this->processAcceptedQuotesWithoutTaxDocument(),
            'billing_due_today' => $this->processBillingDueToday(),
            'billing_overdue_critical' => $this->processBillingOverdueCritical(),
        ];
        $result['processed'] = array_sum($result) - $result['processed'];
        return $result;
    }

    public function notifyAdmins(string $eventType, array $payload = []): array
    {
        $payload['groups'] = array_values(array_unique(array_merge((array)($payload['groups'] ?? []), ['system_admins'])));
        $payload['roles'] = array_values(array_unique(array_merge((array)($payload['roles'] ?? []), ['admin'])));
        return $this->notify($eventType, $payload);
    }

    public function groupsWithMembers(): array
    {
        if (!$this->tableExists('notification_recipient_groups')) return [];
        $groups = $this->pdo->query("\n            SELECT id, group_key, name, description, is_system, is_active\n            FROM notification_recipient_groups\n            WHERE is_active = 1\n            ORDER BY FIELD(group_key, 'ceo','cfo','commercial_direction','commercial_team','operations_team','finance_team','system_admins'), name\n        ")->fetchAll(PDO::FETCH_ASSOC);

        if (empty($groups)) return [];

        $stmt = $this->pdo->query("\n            SELECT gm.group_id, u.id, u.name, u.email, u.role\n            FROM notification_recipient_group_members gm\n            JOIN users u ON u.id = gm.user_id\n            WHERE u.status = 'active' AND u.is_active = 1\n            ORDER BY u.name ASC\n        ");
        $members = [];
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $members[(int)$row['group_id']][] = $row;
        }

        foreach ($groups as &$group) {
            $group['members'] = $members[(int)$group['id']] ?? [];
            $group['member_ids'] = array_map(static function ($member) {
                return (int)$member['id'];
            }, $group['members']);
        }
        unset($group);

        return $groups;
    }

    public function activeUsers(): array
    {
        if (!$this->tableExists('users')) return [];
        $stmt = $this->pdo->query("\n            SELECT id, name, email, role\n            FROM users\n            WHERE status = 'active' AND is_active = 1\n            ORDER BY FIELD(role, 'admin','administracion','comercial','operaciones'), name\n        ");
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function saveGroupMembers(array $membersByGroupKey): int
    {
        if (!$this->tableExists('notification_recipient_groups') || !$this->tableExists('notification_recipient_group_members')) {
            return 0;
        }

        $groups = $this->pdo->query("SELECT id, group_key FROM notification_recipient_groups WHERE is_active = 1")->fetchAll(PDO::FETCH_ASSOC);
        if (empty($groups)) return 0;

        $validUsers = [];
        foreach ($this->activeUsers() as $user) {
            $validUsers[(int)$user['id']] = true;
        }

        $saved = 0;
        $this->pdo->beginTransaction();
        try {
            $delete = $this->pdo->prepare('DELETE FROM notification_recipient_group_members WHERE group_id = ?');
            $insert = $this->pdo->prepare('INSERT IGNORE INTO notification_recipient_group_members (group_id, user_id, created_at) VALUES (?, ?, NOW())');

            foreach ($groups as $group) {
                $key = (string)$group['group_key'];
                if (!array_key_exists($key, $membersByGroupKey)) {
                    continue;
                }

                $groupId = (int)$group['id'];
                $ids = is_array($membersByGroupKey[$key]) ? $membersByGroupKey[$key] : [];
                $ids = array_values(array_unique(array_filter(array_map('intval', $ids), static function ($id) use ($validUsers) {
                    return $id > 0 && isset($validUsers[$id]);
                })));

                $delete->execute([$groupId]);
                foreach ($ids as $userId) {
                    $insert->execute([$groupId, $userId]);
                    $saved += $insert->rowCount();
                }
            }

            $this->pdo->commit();
        } catch (Throwable $e) {
            if ($this->pdo->inTransaction()) $this->pdo->rollBack();
            throw $e;
        }

        return $saved;
    }

    private function processHighValueStalledOpportunities(): int
    {
        $threshold = $this->threshold('ICYLAB_NOTIFY_HIGH_VALUE_THRESHOLD', 1000000);
        $days = (int)$this->threshold('ICYLAB_NOTIFY_STALLED_DAYS', 7);

        $stmt = $this->pdo->prepare("\n            SELECT id, customer_name, title, owner_user_id, expected_value, stage, updated_at, next_action_date\n            FROM crm_opportunities\n            WHERE deleted_at IS NULL\n              AND expected_value >= ?\n              AND stage NOT IN ('perdida', 'facturada')\n              AND updated_at <= DATE_SUB(NOW(), INTERVAL ? DAY)\n            ORDER BY expected_value DESC\n            LIMIT 50\n        ");
        $stmt->execute([$threshold, $days]);

        $count = 0;
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $result = $this->notify('opportunity.stalled_high_value', [
                'user_id' => !empty($row['owner_user_id']) ? (int)$row['owner_user_id'] : null,
                'groups' => ['ceo', 'commercial_direction'],
                'title' => 'Deal de alto valor estancado: ' . ($row['title'] ?: $row['customer_name']),
                'body' => 'Cliente: ' . $row['customer_name'] . "\nEtapa: " . $row['stage'] . "\nValor esperado: $" . number_format((float)$row['expected_value'], 0, ',', '.') . "\nUltima actualizacion: " . $row['updated_at'] . (!empty($row['next_action_date']) ? "\nProxima accion: " . $row['next_action_date'] : ''),
                'module' => 'crm',
                'priority' => 'critical',
                'critical' => true,
                'related_type' => 'opportunity',
                'related_id' => (int)$row['id'],
                'url' => '/modules/crm/opportunity.php?id=' . (int)$row['id'],
                'dedupe_key' => 'opportunity.stalled_high_value:' . (int)$row['id'] . ':' . date('Y-m-d'),
            ]);
            if (!empty($result['success'])) $count++;
        }
        return $count;
    }

    private function processAcceptedQuotesWithoutTaxDocument(): int
    {
        if (!$this->tableExists('crm_quotes')) return 0;
        $stmt = $this->pdo->query("\n            SELECT q.id, q.quote_number, q.customer_id, q.opportunity_id, q.total, q.created_at, c.customer_name, o.owner_user_id\n            FROM crm_quotes q\n            LEFT JOIN crm_customers c ON c.id = q.customer_id\n            LEFT JOIN crm_opportunities o ON o.id = q.opportunity_id\n            WHERE q.deleted_at IS NULL\n              AND q.status = 'Aceptada'\n              AND (q.invoice_number IS NULL OR q.invoice_number = '')\n              AND (q.invoice_issue_date IS NULL OR q.invoice_issue_date = '')\n              AND q.created_at <= DATE_SUB(NOW(), INTERVAL 24 HOUR)\n            ORDER BY q.created_at ASC\n            LIMIT 50\n        ");

        $count = 0;
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $customer = $row['customer_name'] ?: 'Cliente';
            $result = $this->notify('quote.accepted.no_tax_document', [
                'user_id' => !empty($row['owner_user_id']) ? (int)$row['owner_user_id'] : null,
                'groups' => ['cfo', 'finance_team'],
                'title' => 'Cotizacion aceptada sin documento: ' . $row['quote_number'],
                'body' => 'Cliente: ' . $customer . "\nCotizacion: " . $row['quote_number'] . "\nTotal: $" . number_format((float)$row['total'], 0, ',', '.') . "\nCreada: " . $row['created_at'] . "\nAccion requerida: emitir o asociar documento tributario.",
                'module' => 'crm',
                'priority' => 'warning',
                'related_type' => 'quote',
                'related_id' => (int)$row['id'],
                'url' => '/modules/crm/quote_view.php?id=' . (int)$row['id'],
                'dedupe_key' => 'quote.no_tax_document:' . (int)$row['id'] . ':' . date('Y-m-d'),
            ]);
            if (!empty($result['success'])) $count++;
        }
        return $count;
    }

    private function processBillingDueToday(): int
    {
        $stmt = $this->pdo->query("\n            SELECT id, customer_name, title, owner_user_id, expected_value, payment_due_date\n            FROM crm_opportunities\n            WHERE deleted_at IS NULL\n              AND stage = 'facturada'\n              AND payment_status = 'por_cobrar'\n              AND payment_paid_date IS NULL\n              AND payment_due_date = CURDATE()\n            ORDER BY expected_value DESC\n            LIMIT 100\n        ");

        $count = 0;
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $result = $this->notify('billing.due_today', [
                'user_id' => !empty($row['owner_user_id']) ? (int)$row['owner_user_id'] : null,
                'groups' => ['cfo', 'finance_team'],
                'title' => 'Cobranza vence hoy: ' . ($row['title'] ?: $row['customer_name']),
                'body' => 'Cliente: ' . $row['customer_name'] . "\nMonto esperado: $" . number_format((float)$row['expected_value'], 0, ',', '.') . "\nVencimiento: " . $row['payment_due_date'],
                'module' => 'crm',
                'priority' => 'warning',
                'related_type' => 'opportunity',
                'related_id' => (int)$row['id'],
                'url' => '/modules/crm/opportunity.php?id=' . (int)$row['id'],
                'dedupe_key' => 'billing.due_today:' . (int)$row['id'] . ':' . date('Y-m-d'),
            ]);
            if (!empty($result['success'])) $count++;
        }
        return $count;
    }

    private function processBillingOverdueCritical(): int
    {
        $stmt = $this->pdo->query("\n            SELECT id, customer_name, title, owner_user_id, expected_value, payment_due_date, DATEDIFF(CURDATE(), payment_due_date) AS overdue_days\n            FROM crm_opportunities\n            WHERE deleted_at IS NULL\n              AND stage = 'facturada'\n              AND payment_status = 'atrasado'\n              AND payment_paid_date IS NULL\n              AND payment_due_date IS NOT NULL\n              AND payment_due_date <= DATE_SUB(CURDATE(), INTERVAL 3 DAY)\n            ORDER BY payment_due_date ASC, expected_value DESC\n            LIMIT 100\n        ");

        $count = 0;
        foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
            $result = $this->notify('billing.overdue_critical', [
                'user_id' => !empty($row['owner_user_id']) ? (int)$row['owner_user_id'] : null,
                'groups' => ['cfo', 'finance_team', 'ceo'],
                'title' => 'Cobranza critica +' . (int)$row['overdue_days'] . ' dias: ' . ($row['title'] ?: $row['customer_name']),
                'body' => 'Cliente: ' . $row['customer_name'] . "\nMonto esperado: $" . number_format((float)$row['expected_value'], 0, ',', '.') . "\nVencimiento: " . $row['payment_due_date'] . "\nDias de atraso: " . (int)$row['overdue_days'],
                'module' => 'crm',
                'priority' => 'critical',
                'critical' => true,
                'related_type' => 'opportunity',
                'related_id' => (int)$row['id'],
                'url' => '/modules/crm/opportunity.php?id=' . (int)$row['id'],
                'dedupe_key' => 'billing.overdue_critical:' . (int)$row['id'] . ':' . date('Y-m-d'),
            ]);
            if (!empty($result['success'])) $count++;
        }
        return $count;
    }

    private function applyDefaultGroupRouting(string $eventType, array $payload): array
    {
        $groups = [];
        if (!empty($payload['group'])) $groups[] = (string)$payload['group'];
        if (!empty($payload['groups']) && is_array($payload['groups'])) {
            foreach ($payload['groups'] as $group) $groups[] = (string)$group;
        }

        switch ($eventType) {
            case 'quote.accepted':
                $groups = array_merge($groups, ['cfo', 'finance_team', 'operations_team']);
                break;
            case 'quote.rejected':
                if ($this->payloadAmount($payload) >= $this->threshold('ICYLAB_NOTIFY_HIGH_VALUE_THRESHOLD', 1000000)) {
                    $groups = array_merge($groups, ['ceo', 'commercial_direction']);
                }
                break;
            case 'opportunity.facturada':
            case 'billing.por_cobrar':
            case 'billing.paid':
            case 'document.tax_associated':
                $groups = array_merge($groups, ['cfo', 'finance_team']);
                break;
            case 'billing.overdue':
                $groups = array_merge($groups, ['cfo', 'finance_team']);
                if (!empty($payload['critical'])) $groups[] = 'ceo';
                break;
            case 'system.sync_error':
                $groups = array_merge($groups, ['system_admins', 'ceo']);
                break;
            case 'opportunity.high_value':
            case 'opportunity.stalled_high_value':
                $groups = array_merge($groups, ['ceo', 'commercial_direction']);
                break;
            case 'quote.discount_high':
                $groups = array_merge($groups, ['ceo', 'cfo']);
                break;
        }

        if (!empty($groups)) {
            $payload['groups'] = array_values(array_unique(array_filter($groups)));
        }

        return $payload;
    }

    private function payloadAmount(array $payload): float
    {
        $metadata = is_array($payload['metadata'] ?? null) ? $payload['metadata'] : [];
        foreach (['amount', 'total', 'total_amount', 'expected_value'] as $key) {
            if (isset($payload[$key]) && is_numeric($payload[$key])) return (float)$payload[$key];
            if (isset($metadata[$key]) && is_numeric($metadata[$key])) return (float)$metadata[$key];
        }
        return 0.0;
    }

    private function threshold(string $constantName, float $default): float
    {
        return defined($constantName) ? (float)constant($constantName) : $default;
    }

    private function insertEvent(array $data): int
    {
        try {
            $stmt = $this->pdo->prepare("\n                INSERT INTO notification_events\n                (event_type, module, priority, title, body, url, related_type, related_id, actor_user_id, metadata_json, dedupe_key, created_at)\n                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())\n            ");
            $stmt->execute([
                $data['event_type'],
                $data['module'],
                $data['priority'],
                $data['title'],
                $data['body'],
                $data['url'],
                $data['related_type'],
                $data['related_id'],
                $data['actor_user_id'],
                json_encode($data['metadata'] ?? [], JSON_UNESCAPED_UNICODE | JSON_UNESCAPED_SLASHES),
                $data['dedupe_key'],
            ]);
            return (int)$this->pdo->lastInsertId();
        } catch (PDOException $e) {
            if ((string)$e->getCode() === '23000') {
                return 0;
            }
            throw $e;
        }
    }

    private function insertNotification(int $eventId, int $userId, array $data): int
    {
        try {
            $stmt = $this->pdo->prepare("\n                INSERT INTO notifications\n                (event_id, user_id, title, body, url, module, related_type, related_id, priority, created_at)\n                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, NOW())\n            ");
            $stmt->execute([
                $eventId,
                $userId,
                $data['title'],
                $data['body'],
                $data['url'],
                $data['module'],
                $data['related_type'],
                $data['related_id'],
                $data['priority'],
            ]);
            return (int)$this->pdo->lastInsertId();
        } catch (PDOException $e) {
            if ((string)$e->getCode() === '23000') {
                return 0;
            }
            throw $e;
        }
    }

    private function queueEmail(int $eventId, int $notificationId, int $userId, string $recipient, array $rendered): bool
    {
        if (!$this->tableExists('notification_deliveries')) return false;
        $priority = $rendered['priority'] ?? 'info';
        $prioritySort = ['critical' => 1, 'warning' => 2, 'success' => 3, 'info' => 4][$priority] ?? 4;

        $stmt = $this->pdo->prepare("\n            INSERT INTO notification_deliveries\n            (event_id, notification_id, user_id, channel, recipient, subject, body_html, body_text, status, priority_sort, next_attempt_at, created_at, updated_at)\n            VALUES (?, ?, ?, 'email', ?, ?, ?, ?, 'queued', ?, NOW(), NOW(), NOW())\n        ");
        $stmt->execute([
            $eventId,
            $notificationId ?: null,
            $userId,
            $recipient,
            $rendered['subject'],
            $rendered['html'],
            $rendered['text'],
            $prioritySort,
        ]);
        return true;
    }

    private function shouldQueueEmail(int $userId, string $eventType, array $definition, bool $forceEmail): bool
    {
        if ($forceEmail) return true;
        if (!$this->tableExists('notification_preferences')) {
            return (bool)($definition['email_default'] ?? false);
        }

        $stmt = $this->pdo->prepare('SELECT email_enabled FROM notification_preferences WHERE user_id = ? AND event_type = ? LIMIT 1');
        $stmt->execute([$userId, $eventType]);
        $value = $stmt->fetchColumn();
        if ($value === false) {
            return (bool)($definition['email_default'] ?? false);
        }
        return (bool)$value;
    }

    private function renderEmail(string $eventType, array $data): array
    {
        $template = $this->loadTemplate($eventType);
        $subjectTpl = $template['subject_template'] ?? '[ICYLAB OS] {title}';
        $htmlTpl = $template['html_template'] ?? '';
        $textTpl = $template['text_template'] ?? '';

        $subject = $this->renderString($subjectTpl, $data);
        $bodyHtml = $htmlTpl !== '' ? $this->renderString($htmlTpl, $data) : $this->defaultEmailHtml($data);
        $bodyText = $textTpl !== '' ? $this->renderString($textTpl, $data) : $this->defaultEmailText($data);

        return [
            'subject' => $subject,
            'html' => $bodyHtml,
            'text' => $bodyText,
            'priority' => $data['priority'] ?? 'info',
        ];
    }

    private function loadTemplate(string $eventType): array
    {
        if (!$this->tableExists('notification_templates')) return [];
        $stmt = $this->pdo->prepare('SELECT * FROM notification_templates WHERE event_type = ? AND is_active = 1 LIMIT 1');
        $stmt->execute([$eventType]);
        return $stmt->fetch(PDO::FETCH_ASSOC) ?: [];
    }

    private function renderString(string $template, array $data): string
    {
        $url = trim((string)($data['url'] ?? ''));
        $fullUrl = $url !== '' && strpos($url, 'http') !== 0 ? rtrim(APP_URL, '/') . $url : $url;
        $replacements = [
            '{title}' => $data['title'] ?? '',
            '{body}' => nl2br(htmlspecialchars($data['body'] ?? '', ENT_QUOTES, 'UTF-8')),
            '{body_text}' => $data['body'] ?? '',
            '{url}' => htmlspecialchars($fullUrl, ENT_QUOTES, 'UTF-8'),
            '{url_text}' => $fullUrl,
            '{app_name}' => defined('APP_NAME') ? APP_NAME : 'ICYLAB OS',
            '{recipient_name}' => $data['recipient_name'] ?? '',
        ];
        return strtr($template, $replacements);
    }

    private function defaultEmailHtml(array $data): string
    {
        $url = trim((string)($data['url'] ?? ''));
        $fullUrl = $url !== '' && strpos($url, 'http') !== 0 ? rtrim(APP_URL, '/') . $url : $url;
        $title = htmlspecialchars($data['title'] ?? 'Notificación ICYLAB OS', ENT_QUOTES, 'UTF-8');
        $body = nl2br(htmlspecialchars($data['body'] ?? '', ENT_QUOTES, 'UTF-8'));
        $cta = $fullUrl !== '' ? '<p style="margin:24px 0 0"><a href="' . htmlspecialchars($fullUrl, ENT_QUOTES, 'UTF-8') . '" style="display:inline-block;background:#111;color:#fff;text-decoration:none;padding:12px 18px;border-radius:999px;font-weight:600">Ver registro</a></p>' : '';

        return '<!doctype html><html><body style="margin:0;background:#f6f6f6;font-family:Arial,Helvetica,sans-serif;color:#111">'
            . '<table width="100%" cellpadding="0" cellspacing="0" role="presentation"><tr><td align="center" style="padding:28px 16px">'
            . '<table width="100%" cellpadding="0" cellspacing="0" role="presentation" style="max-width:620px;background:#fff;border:1px solid #e7e7e7;border-radius:18px;overflow:hidden">'
            . '<tr><td style="padding:22px 26px;border-bottom:1px solid #eee"><strong style="font-size:14px;letter-spacing:.08em">ICYLAB OS</strong></td></tr>'
            . '<tr><td style="padding:28px 26px"><h1 style="font-size:22px;line-height:1.25;margin:0 0 14px">' . $title . '</h1>'
            . '<div style="font-size:15px;line-height:1.6;color:#333">' . $body . '</div>' . $cta . '</td></tr>'
            . '<tr><td style="padding:18px 26px;border-top:1px solid #eee;font-size:12px;color:#777">Notificación automática de ICYLAB OS. No respondas directamente a este correo.</td></tr>'
            . '</table></td></tr></table></body></html>';
    }

    private function defaultEmailText(array $data): string
    {
        $url = trim((string)($data['url'] ?? ''));
        $fullUrl = $url !== '' && strpos($url, 'http') !== 0 ? rtrim(APP_URL, '/') . $url : $url;
        $text = ($data['title'] ?? 'Notificación ICYLAB OS') . "\n\n" . ($data['body'] ?? '');
        if ($fullUrl !== '') $text .= "\n\nVer registro: " . $fullUrl;
        $text .= "\n\nNotificación automática de ICYLAB OS.";
        return $text;
    }

    private function resolveRecipients(array $payload): array
    {
        $ids = [];
        if (isset($payload['user_id']) && (int)$payload['user_id'] > 0) {
            $ids[] = (int)$payload['user_id'];
        }
        if (!empty($payload['user_ids']) && is_array($payload['user_ids'])) {
            foreach ($payload['user_ids'] as $id) {
                if ((int)$id > 0) $ids[] = (int)$id;
            }
        }

        $roles = [];
        if (!empty($payload['role'])) $roles[] = (string)$payload['role'];
        if (!empty($payload['roles']) && is_array($payload['roles'])) {
            foreach ($payload['roles'] as $role) $roles[] = (string)$role;
        }

        $groups = [];
        if (!empty($payload['group'])) $groups[] = (string)$payload['group'];
        if (!empty($payload['groups']) && is_array($payload['groups'])) {
            foreach ($payload['groups'] as $group) $groups[] = (string)$group;
        }

        $recipients = [];
        if (!empty($ids)) {
            $ids = array_values(array_unique($ids));
            $placeholders = implode(',', array_fill(0, count($ids), '?'));
            $stmt = $this->pdo->prepare("SELECT id, name, email, role FROM users WHERE is_active = 1 AND status = 'active' AND id IN ($placeholders)");
            $stmt->execute($ids);
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                $recipients[(int)$row['id']] = $row;
            }
        }

        if (!empty($roles)) {
            $roles = array_values(array_unique($roles));
            $placeholders = implode(',', array_fill(0, count($roles), '?'));
            $stmt = $this->pdo->prepare("SELECT id, name, email, role FROM users WHERE is_active = 1 AND status = 'active' AND role IN ($placeholders)");
            $stmt->execute($roles);
            foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                $recipients[(int)$row['id']] = $row;
            }
        }

        if (!empty($groups)) {
            $groups = array_values(array_unique(array_filter($groups)));
            if ($this->tableExists('notification_recipient_groups') && $this->tableExists('notification_recipient_group_members')) {
                $placeholders = implode(',', array_fill(0, count($groups), '?'));
                $stmt = $this->pdo->prepare("\n                    SELECT u.id, u.name, u.email, u.role\n                    FROM notification_recipient_groups g\n                    JOIN notification_recipient_group_members gm ON gm.group_id = g.id\n                    JOIN users u ON u.id = gm.user_id\n                    WHERE g.is_active = 1\n                      AND g.group_key IN ($placeholders)\n                      AND u.is_active = 1\n                      AND u.status = 'active'\n                ");
                $stmt->execute($groups);
                foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                    $recipients[(int)$row['id']] = $row;
                }
            } else {
                $fallbackRoles = $this->fallbackRolesForGroups($groups);
                if (!empty($fallbackRoles)) {
                    $placeholders = implode(',', array_fill(0, count($fallbackRoles), '?'));
                    $stmt = $this->pdo->prepare("SELECT id, name, email, role FROM users WHERE is_active = 1 AND status = 'active' AND role IN ($placeholders)");
                    $stmt->execute($fallbackRoles);
                    foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
                        $recipients[(int)$row['id']] = $row;
                    }
                }
            }
        }

        return array_values($recipients);
    }

    private function fallbackRolesForGroups(array $groups): array
    {
        $map = [
            'ceo' => ['admin'],
            'cfo' => ['administracion'],
            'commercial_direction' => ['admin', 'administracion'],
            'commercial_team' => ['comercial'],
            'operations_team' => ['operaciones'],
            'finance_team' => ['administracion'],
            'system_admins' => ['admin'],
        ];

        $roles = [];
        foreach ($groups as $group) {
            foreach ($map[$group] ?? [] as $role) {
                $roles[] = $role;
            }
        }
        return array_values(array_unique($roles));
    }

    private function normalizePriority(string $priority): string
    {
        return in_array($priority, ['info', 'success', 'warning', 'critical'], true) ? $priority : 'info';
    }

    private function tableExists(string $table): bool
    {
        if (array_key_exists($table, $this->tableCache)) return $this->tableCache[$table];
        try {
            $stmt = $this->pdo->prepare("SHOW TABLES LIKE ?");
            $stmt->execute([$table]);
            $this->tableCache[$table] = (bool)$stmt->fetchColumn();
        } catch (Throwable $e) {
            $this->tableCache[$table] = false;
        }
        return $this->tableCache[$table];
    }
}
