<?php
require_once __DIR__ . '/../Core.php';
require_once __DIR__ . '/MailConversation.php';
require_once __DIR__ . '/MailLabel.php';
require_once __DIR__ . '/MailRule.php';

class Message extends Model
{
    public function recent(int $limit = 10): array
    {
        $stmt = $this->db->prepare('SELECT m.*, c.first_name, c.last_name FROM messages m LEFT JOIN contacts c ON c.id = m.contact_id ORDER BY m.created_at DESC LIMIT ?');
        $stmt->bindValue(1, $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function search(array $filters, int $page = 1, int $perPage = 25): array
    {
        $where = [];
        $params = [];
        if (!empty($filters['q'])) {
            $where[] = '(m.body LIKE ? OR m.subject LIKE ? OR m.from_address LIKE ? OR m.to_address LIKE ? OR c.first_name LIKE ? OR c.last_name LIKE ? OR m.thread_key LIKE ?)';
            for ($i = 0; $i < 7; $i++) {
                $params[] = '%' . $filters['q'] . '%';
            }
        }
        foreach (['channel', 'direction', 'status'] as $field) {
            if (!empty($filters[$field]) && $filters[$field] !== 'all') {
                $where[] = 'm.' . $field . ' = ?';
                $params[] = $filters[$field];
            }
        }
        if (!empty($filters['contact_id'])) {
            $where[] = 'm.contact_id = ?';
            $params[] = (int)$filters['contact_id'];
        }
        $whereSql = $where ? ('WHERE ' . implode(' AND ', $where)) : '';
        $fromSql = ' FROM messages m LEFT JOIN contacts c ON c.id = m.contact_id ' . $whereSql;
        $countStmt = $this->db->prepare('SELECT COUNT(*)' . $fromSql);
        $countStmt->execute($params);
        $total = (int)$countStmt->fetchColumn();
        $meta = paginate_meta($page, $perPage, $total);
        $stmt = $this->db->prepare('SELECT m.*, c.first_name, c.last_name, c.company, c.tags, c.lifecycle_stage, c.score' . $fromSql . ' ORDER BY m.created_at DESC LIMIT ? OFFSET ?');
        $i = 1;
        foreach ($params as $param) {
            $stmt->bindValue($i++, $param, is_int($param) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->bindValue($i++, $meta['per_page'], PDO::PARAM_INT);
        $stmt->bindValue($i, $meta['offset'], PDO::PARAM_INT);
        $stmt->execute();
        return ['data' => $stmt->fetchAll(), 'meta' => $meta];
    }

    public function conversationSummary(array $filters = [], int $page = 1, int $perPage = 20): array
    {
        $all = $this->search($filters, 1, 5000)['data'];
        $grouped = [];
        foreach ($all as $row) {
            $key = $row['thread_key'] ?: conversation_key($row['channel'], $row['from_address'], $row['to_address'], $row['contact_id'] ? (int)$row['contact_id'] : null);
            if (!isset($grouped[$key])) {
                $grouped[$key] = [
                    'conversation_key' => $key,
                    'channel' => $row['channel'],
                    'contact_id' => $row['contact_id'],
                    'contact_name' => trim(($row['first_name'] ?? '') . ' ' . ($row['last_name'] ?? '')),
                    'address' => $row['direction'] === 'inbound' ? $row['from_address'] : $row['to_address'],
                    'last_message_at' => $row['created_at'],
                    'last_preview' => strip_tags(($row['subject'] ? $row['subject'] . ' — ' : '') . $row['body']),
                    'status' => $row['status'],
                    'message_count' => 0,
                    'unread_like_count' => 0,
                ];
            }
            $grouped[$key]['message_count']++;
            if (($row['direction'] ?? '') === 'inbound' && in_array((string)$row['status'], ['received', 'queued'], true)) {
                $grouped[$key]['unread_like_count']++;
            }
        }
        $rows = array_values($grouped);
        usort($rows, fn($a, $b) => strcmp((string)$b['last_message_at'], (string)$a['last_message_at']));
        $total = count($rows);
        $meta = paginate_meta($page, $perPage, $total);
        return ['data' => array_slice($rows, $meta['offset'], $meta['per_page']), 'meta' => $meta];
    }

    public function emailMailboxSummary(string $folderKey = 'inbox', string $query = '', int $page = 1, int $perPage = 20, array $filters = []): array
    {
        $rows = $this->emailConversationRows($query, $filters);
        if ($folderKey !== 'all') {
            $rows = array_values(array_filter($rows, static fn(array $row): bool => (string)($row['folder_key'] ?? 'inbox') === $folderKey));
        }
        $total = count($rows);
        $meta = paginate_meta($page, $perPage, $total);
        return ['data' => array_slice($rows, $meta['offset'], $meta['per_page']), 'meta' => $meta];
    }

    public function emailMailboxCounts(string $query = '', array $filters = []): array
    {
        $counts = [];
        foreach ($this->emailConversationRows($query, $filters) as $row) {
            $folderKey = (string)($row['folder_key'] ?? 'inbox');
            $counts[$folderKey] = (int)($counts[$folderKey] ?? 0) + 1;
        }
        return $counts;
    }

    public function allEmailConversationRows(string $query = '', array $filters = []): array
    {
        return $this->emailConversationRows($query, $filters);
    }

    public function describeMailboxSearch(string $query): array
    {
        $parsed = $this->parseMailboxSearchQuery($query);
        return [
            'plain_query' => $parsed['plain_query'],
            'chips' => $parsed['chips'],
            'operators' => $parsed['operators'],
        ];
    }

    public function mailQueueSummary(?int $userId = null): array
    {
        $rows = $this->emailConversationRows('', []);
        $userId = $userId ?: (int)(current_user()['id'] ?? 0);
        $summary = [
            'total' => count($rows),
            'assigned_to_me' => 0,
            'unassigned' => 0,
            'claimed' => 0,
            'pending_approval' => 0,
            'breached' => 0,
            'unread' => 0,
        ];
        foreach ($rows as $row) {
            if ((int)($row['assigned_user_id'] ?? 0) === $userId && $userId > 0) {
                $summary['assigned_to_me']++;
            }
            if (empty($row['assigned_user_id'])) {
                $summary['unassigned']++;
            }
            if (!empty($row['claimed_by_user_id'])) {
                $summary['claimed']++;
            }
            if (($row['approval_status'] ?? 'not_required') === 'pending') {
                $summary['pending_approval']++;
            }
            if (($row['sla_status'] ?? 'normal') === 'breached') {
                $summary['breached']++;
            }
            if (!empty($row['unread_like_count'])) {
                $summary['unread']++;
            }
        }
        return $summary;
    }

    public function sharedInboxSummary(?int $userId = null): array
    {
        $rows = $this->emailConversationRows('', []);
        $userId = $userId ?: (int)(current_user()['id'] ?? 0);
        $summary = [];
        foreach ($rows as $row) {
            $sharedMailboxId = (int)($row['shared_mailbox_id'] ?? 0);
            if ($sharedMailboxId <= 0) {
                continue;
            }
            if (!isset($summary[$sharedMailboxId])) {
                $summary[$sharedMailboxId] = [
                    'shared_mailbox_id' => $sharedMailboxId,
                    'thread_count' => 0,
                    'unassigned_count' => 0,
                    'assigned_to_me_count' => 0,
                    'claimed_count' => 0,
                    'pending_approval_count' => 0,
                    'breached_count' => 0,
                    'unread_count' => 0,
                ];
            }
            $summary[$sharedMailboxId]['thread_count']++;
            if (empty($row['assigned_user_id'])) {
                $summary[$sharedMailboxId]['unassigned_count']++;
            }
            if ($userId > 0 && (int)($row['assigned_user_id'] ?? 0) === $userId) {
                $summary[$sharedMailboxId]['assigned_to_me_count']++;
            }
            if (!empty($row['claimed_by_user_id'])) {
                $summary[$sharedMailboxId]['claimed_count']++;
            }
            if (($row['approval_status'] ?? 'not_required') === 'pending') {
                $summary[$sharedMailboxId]['pending_approval_count']++;
            }
            if (($row['sla_status'] ?? 'normal') === 'breached') {
                $summary[$sharedMailboxId]['breached_count']++;
            }
            if (!empty($row['unread_like_count'])) {
                $summary[$sharedMailboxId]['unread_count']++;
            }
        }
        return $summary;
    }

    public function findEmailConversation(string $conversationKey, string $query = '', array $filters = []): ?array
    {
        foreach ($this->emailConversationRows($query, $filters) as $row) {
            if (($row['conversation_key'] ?? '') === $conversationKey) {
                return $row;
            }
        }
        return null;
    }

    public function moveConversationToFolder(string $conversationKey, string $folderKey): int
    {
        $thread = $this->thread($conversationKey, 500);
        $count = 0;
        foreach ($thread as $message) {
            if (($message['channel'] ?? '') === 'email') {
                $count++;
            }
        }
        (new MailConversation())->upsert($conversationKey, [
            'folder_key' => $folderKey,
            'is_archived' => $folderKey === 'archive' ? 1 : 0,
            'snoozed_until' => $folderKey === 'snoozed' ? date('Y-m-d H:i:s', strtotime('+1 day')) : null,
        ]);
        return $count;
    }

    public function effectiveMailboxFolder(array $message): string
    {
        return $this->defaultMailboxFolder($message);
    }

    public function markFailed(int $id, string $error, array $extra = []): bool
    {
        $current = $this->find($id) ?: [];
        $metadata = $this->decodeMetadata((string)($current['metadata_json'] ?? ''));
        $metadata = array_merge($metadata, $extra, [
            'last_error' => $error,
            'failed_at' => date('Y-m-d H:i:s'),
        ]);
        return $this->updateStatus($id, 'failed', json_encode($metadata));
    }

    public function computedMailboxFolders(): array
    {
        return ['outbox', 'sent'];
    }

    public function isComputedMailboxFolder(string $folderKey): bool
    {
        return in_array(strtolower(trim($folderKey)), $this->computedMailboxFolders(), true);
    }

    public function thread(string $conversationKey, int $limit = 200): array
    {
        $stmt = $this->db->prepare('SELECT m.*, c.first_name, c.last_name, c.company, c.tags, c.lifecycle_stage, c.score FROM messages m LEFT JOIN contacts c ON c.id = m.contact_id WHERE m.thread_key = ? OR CONCAT(m.channel, ":contact:", m.contact_id) = ? OR CONCAT(m.channel, ":addr:", CASE WHEN m.direction = "inbound" THEN LOWER(REPLACE(REPLACE(REPLACE(m.from_address, " ", ""), "-", ""), "(", "")) ELSE LOWER(REPLACE(REPLACE(REPLACE(m.to_address, " ", ""), "-", ""), "(", "")) END) = ? ORDER BY m.created_at ASC LIMIT ?');
        $stmt->bindValue(1, $conversationKey);
        $stmt->bindValue(2, $conversationKey);
        $stmt->bindValue(3, $conversationKey);
        $stmt->bindValue(4, $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function find(int $id): ?array
    {
        $stmt = $this->db->prepare('SELECT * FROM messages WHERE id = ? LIMIT 1');
        $stmt->execute([$id]);
        return $stmt->fetch() ?: null;
    }

    public function statsLast30Days(): array
    {
        $sql = "SELECT COUNT(*) total,
            SUM(channel = 'sms') sms_total,
            SUM(channel = 'email') email_total,
            SUM(direction = 'outbound') outbound_total,
            SUM(direction = 'inbound') inbound_total,
            SUM(status = 'sent' OR status = 'delivered') sent_total,
            SUM(status = 'failed') failed_total,
            SUM(status = 'bounced') bounced_total
            FROM messages WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
        return $this->db->query($sql)->fetch() ?: [];
    }

    public function deliverySummary(): array
    {
        $sql = "SELECT
            SUM(status = 'delivered') delivered_count,
            SUM(status = 'sent') sent_count,
            SUM(status = 'queued') queued_count,
            SUM(status = 'failed') failed_count,
            SUM(status = 'undelivered') undelivered_count,
            SUM(status = 'bounced') bounced_count,
            (SELECT COUNT(*) FROM message_events WHERE event_type = 'open') open_count,
            (SELECT COUNT(DISTINCT message_id) FROM message_events WHERE event_type = 'open') unique_open_count,
            (SELECT COUNT(*) FROM message_events WHERE event_type = 'click') click_count,
            (SELECT COUNT(DISTINCT CONCAT(message_id, '|', COALESCE(event_value,''))) FROM message_events WHERE event_type = 'click') unique_click_count
            FROM messages WHERE direction = 'outbound'";
        return $this->db->query($sql)->fetch() ?: [];
    }

    public function analyticsDaily(int $days = 14): array
    {
        $stmt = $this->db->prepare("SELECT DATE(m.created_at) day,
            SUM(m.channel = 'sms') sms_count,
            SUM(m.channel = 'email') email_count,
            SUM(m.status = 'delivered') delivered_count,
            SUM(m.status = 'failed') failed_count,
            SUM(m.status = 'bounced') bounced_count,
            COUNT(CASE WHEN me.event_type = 'open' THEN 1 END) open_count,
            COUNT(DISTINCT CASE WHEN me.event_type = 'open' THEN me.message_id END) unique_open_count,
            COUNT(CASE WHEN me.event_type = 'click' THEN 1 END) click_count,
            COUNT(DISTINCT CASE WHEN me.event_type = 'click' THEN CONCAT(me.message_id, '|', COALESCE(me.event_value,'')) END) unique_click_count
            FROM messages m
            LEFT JOIN message_events me ON me.message_id = m.id AND DATE(me.created_at) = DATE(m.created_at)
            WHERE m.created_at >= DATE_SUB(CURDATE(), INTERVAL ? DAY)
            GROUP BY DATE(m.created_at)
            ORDER BY day ASC");
        $stmt->bindValue(1, $days, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function healthSummary(int $days = 30): array
    {
        $stmt = $this->db->prepare("SELECT
            COUNT(*) AS outbound_total,
            SUM(channel = 'email') AS email_total,
            SUM(channel = 'sms') AS sms_total,
            SUM(status = 'delivered') AS delivered_total,
            SUM(status = 'sent') AS sent_total,
            SUM(status = 'queued') AS queued_total,
            SUM(status = 'failed') AS failed_total,
            SUM(status = 'undelivered') AS undelivered_total,
            SUM(status = 'bounced') AS bounced_total,
            (SELECT COUNT(DISTINCT me.message_id)
                FROM message_events me
                INNER JOIN messages m2 ON m2.id = me.message_id
                WHERE m2.direction = 'outbound'
                  AND m2.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
                  AND me.event_type = 'open') AS unique_open_count,
            (SELECT COUNT(DISTINCT CONCAT(me.message_id, '|', COALESCE(me.event_value,'')))
                FROM message_events me
                INNER JOIN messages m3 ON m3.id = me.message_id
                WHERE m3.direction = 'outbound'
                  AND m3.created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
                  AND me.event_type = 'click') AS unique_click_count
            FROM messages
            WHERE direction = 'outbound' AND created_at >= DATE_SUB(NOW(), INTERVAL ? DAY)");
        $stmt->bindValue(1, $days, PDO::PARAM_INT);
        $stmt->bindValue(2, $days, PDO::PARAM_INT);
        $stmt->bindValue(3, $days, PDO::PARAM_INT);
        $stmt->execute();
        $row = $stmt->fetch() ?: [];
        $processed = (int)($row['delivered_total'] ?? 0) + (int)($row['bounced_total'] ?? 0) + (int)($row['failed_total'] ?? 0) + (int)($row['undelivered_total'] ?? 0);
        $emailBase = max(1, (int)($row['email_total'] ?? 0));
        $row['processed_total'] = $processed;
        $row['deliverability_rate'] = round(((int)($row['delivered_total'] ?? 0) / max(1, $processed)) * 100, 1);
        $row['bounce_rate'] = round(((int)($row['bounced_total'] ?? 0) / max(1, $emailBase)) * 100, 1);
        $row['failure_rate'] = round((((int)($row['failed_total'] ?? 0) + (int)($row['undelivered_total'] ?? 0)) / max(1, $processed)) * 100, 1);
        $row['open_rate'] = round(((int)($row['unique_open_count'] ?? 0) / max(1, $emailBase)) * 100, 1);
        $row['click_rate'] = round(((int)($row['unique_click_count'] ?? 0) / max(1, $emailBase)) * 100, 1);
        $row['queue_pressure'] = round(((int)($row['queued_total'] ?? 0) / max(1, (int)($row['outbound_total'] ?? 0))) * 100, 1);
        return $row;
    }

    public function create(array $data): int
    {
        $threadKey = $data['thread_key'] ?? conversation_key($data['channel'], $data['from_address'] ?? null, $data['to_address'] ?? null, isset($data['contact_id']) ? (int)$data['contact_id'] : null);
        $stmt = $this->db->prepare('INSERT INTO messages (campaign_id, contact_id, channel, direction, subject, body, from_address, to_address, status, external_id, media_url, attachment_path, metadata_json, thread_key, in_reply_to_external_id, references_header) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)');
        $stmt->execute([
            $data['campaign_id'] ?? null,
            $data['contact_id'] ?? null,
            $data['channel'],
            $data['direction'],
            $data['subject'] ?? null,
            $data['body'] ?? '',
            $data['from_address'] ?? null,
            $data['to_address'] ?? null,
            $data['status'] ?? 'queued',
            $data['external_id'] ?? null,
            $data['media_url'] ?? null,
            $data['attachment_path'] ?? null,
            $data['metadata_json'] ?? null,
            $threadKey,
            $data['in_reply_to_external_id'] ?? null,
            $data['references_header'] ?? null,
        ]);
        return (int)$this->db->lastInsertId();
    }

    public function updateStatus(int $id, string $status, ?string $metadataJson = null): bool
    {
        $stmt = $this->db->prepare('UPDATE messages SET status = ?, metadata_json = COALESCE(?, metadata_json), updated_at = NOW() WHERE id = ?');
        return $stmt->execute([$status, $metadataJson, $id]);
    }

    public function markFailedIfPending(int $id, ?string $metadataJson = null): bool
    {
        $stmt = $this->db->prepare("UPDATE messages SET status = 'failed', metadata_json = COALESCE(?, metadata_json), updated_at = NOW() WHERE id = ? AND status IN ('queued', 'processing')");
        return $stmt->execute([$metadataJson, $id]);
    }

    public function updateStatusByExternalId(string $externalId, string $status, ?string $metadataJson = null): bool
    {
        $stmt = $this->db->prepare('UPDATE messages SET status = ?, metadata_json = COALESCE(?, metadata_json), updated_at = NOW() WHERE external_id = ?');
        return $stmt->execute([$status, $metadataJson, $externalId]);
    }

    public function updateExternalAndStatus(int $id, ?string $externalId, string $status, ?string $metadataJson = null): bool
    {
        $stmt = $this->db->prepare('UPDATE messages SET external_id = COALESCE(?, external_id), status = ?, metadata_json = COALESCE(?, metadata_json), updated_at = NOW() WHERE id = ?');
        return $stmt->execute([$externalId, $status, $metadataJson, $id]);
    }

    public function addEvent(int $messageId, string $eventType, ?string $eventValue = null, ?string $metadataJson = null): void
    {
        $stmt = $this->db->prepare('INSERT INTO message_events (message_id, event_type, event_value, metadata_json) VALUES (?, ?, ?, ?)');
        $stmt->execute([$messageId, $eventType, $eventValue, $metadataJson]);
    }

    public function exportRows(array $filters): array
    {
        return $this->search($filters, 1, 10000)['data'];
    }

    public function campaignsForConversation(string $conversationKey, int $limit = 6): array
    {
        $stmt = $this->db->prepare('SELECT DISTINCT c.id, c.name, c.channel, c.status, c.scheduled_at, c.created_at
            FROM messages m
            INNER JOIN campaigns c ON c.id = m.campaign_id
            WHERE (m.thread_key = ? OR CONCAT(m.channel, ":contact:", m.contact_id) = ?)
              AND m.campaign_id IS NOT NULL
            ORDER BY c.created_at DESC
            LIMIT ?');
        $stmt->bindValue(1, $conversationKey);
        $stmt->bindValue(2, $conversationKey);
        $stmt->bindValue(3, $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function recentCampaignsForContact(int $contactId, int $limit = 6): array
    {
        $stmt = $this->db->prepare('SELECT DISTINCT c.id, c.name, c.channel, c.status, c.scheduled_at, c.created_at
            FROM campaign_recipients cr
            INNER JOIN campaigns c ON c.id = cr.campaign_id
            WHERE cr.contact_id = ?
            ORDER BY c.created_at DESC
            LIMIT ?');
        $stmt->bindValue(1, $contactId, PDO::PARAM_INT);
        $stmt->bindValue(2, $limit, PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    public function contactConversations(array $contact, string $channel, int $limit = 20): array
    {
        $rows = $this->contactConversationRows($contact, $channel, 500);
        $grouped = [];
        foreach ($rows as $row) {
            $key = $row['thread_key'] ?: conversation_key($channel, $row['from_address'] ?? null, $row['to_address'] ?? null, !empty($contact['id']) ? (int)$contact['id'] : null);
            $contactName = trim((string)($contact['first_name'] ?? '') . ' ' . (string)($contact['last_name'] ?? ''));
            if (!isset($grouped[$key])) {
                $grouped[$key] = [
                    'conversation_key' => $key,
                    'channel' => $channel,
                    'contact_id' => $contact['id'] ?? null,
                    'contact_name' => $contactName,
                    'address' => ($row['direction'] ?? '') === 'inbound' ? ($row['from_address'] ?? '') : ($row['to_address'] ?? ''),
                    'subject' => $row['subject'] ?? '',
                    'last_message_at' => $row['created_at'] ?? null,
                    'last_preview' => strip_tags(((string)($row['subject'] ?? '') !== '' ? $row['subject'] . ' — ' : '') . (string)($row['body'] ?? '')),
                    'status' => $row['status'] ?? '',
                    'direction' => $row['direction'] ?? '',
                    'message_count' => 0,
                    'unread_like_count' => 0,
                    'has_attachment' => !empty($row['attachment_path']),
                ];
            }
            $grouped[$key]['message_count']++;
            if (($row['direction'] ?? '') === 'inbound' && $this->isInboundUnreadLike($row)) {
                $grouped[$key]['unread_like_count']++;
            }
            if (!empty($row['attachment_path'])) {
                $grouped[$key]['has_attachment'] = true;
            }
            if ((string)($grouped[$key]['subject'] ?? '') === '' && (string)($row['subject'] ?? '') !== '') {
                $grouped[$key]['subject'] = (string)$row['subject'];
            }
            if (strcmp((string)($row['created_at'] ?? ''), (string)($grouped[$key]['last_message_at'] ?? '')) >= 0) {
                $grouped[$key]['last_message_at'] = $row['created_at'] ?? null;
                $grouped[$key]['last_preview'] = strip_tags(((string)($row['subject'] ?? '') !== '' ? $row['subject'] . ' — ' : '') . (string)($row['body'] ?? ''));
                $grouped[$key]['status'] = $row['status'] ?? '';
                $grouped[$key]['direction'] = $row['direction'] ?? '';
                $grouped[$key]['address'] = ($row['direction'] ?? '') === 'inbound' ? ($row['from_address'] ?? '') : ($row['to_address'] ?? '');
            }
        }
        $items = array_values($grouped);
        usort($items, static fn(array $a, array $b): int => strcmp((string)($b['last_message_at'] ?? ''), (string)($a['last_message_at'] ?? '')));
        return array_slice($items, 0, max(1, $limit));
    }

    public function contactThread(array $contact, string $channel, string $conversationKey, int $limit = 250): array
    {
        $rows = $this->contactConversationRows($contact, $channel, max($limit * 4, 500));
        $thread = [];
        foreach ($rows as $row) {
            $key = $row['thread_key'] ?: conversation_key($channel, $row['from_address'] ?? null, $row['to_address'] ?? null, !empty($contact['id']) ? (int)$contact['id'] : null);
            if ($key === $conversationKey) {
                $thread[] = $row;
            }
        }
        usort($thread, static fn(array $a, array $b): int => strcmp((string)($a['created_at'] ?? ''), (string)($b['created_at'] ?? '')));
        if (count($thread) > $limit) {
            $thread = array_slice($thread, -$limit);
        }
        return $thread;
    }

    private function contactConversationRows(array $contact, string $channel, int $limit = 500): array
    {
        $channel = $channel === 'sms' ? 'sms' : 'email';
        $contactId = (int)($contact['id'] ?? 0);
        $address = $channel === 'sms' ? (string)($contact['phone'] ?? '') : (string)($contact['email'] ?? '');
        $normalized = normalize_channel_address($channel, $address);
        if ($contactId <= 0 && $normalized === '') {
            return [];
        }
        $where = ['m.channel = ?'];
        $params = [$channel];
        if ($contactId > 0) {
            $where[] = 'm.contact_id = ?';
            $params[] = $contactId;
        }
        if ($normalized !== '') {
            $normFrom = $this->normalizeChannelSql($channel, 'm.from_address');
            $normTo = $this->normalizeChannelSql($channel, 'm.to_address');
            $where[] = '(' . $normFrom . ' = ? OR ' . $normTo . ' = ?)';
            $params[] = $normalized;
            $params[] = $normalized;
        }
        $sql = 'SELECT m.*, c.first_name, c.last_name, c.company, c.tags, c.lifecycle_stage, c.score FROM messages m LEFT JOIN contacts c ON c.id = m.contact_id WHERE m.channel = ? AND (' . implode(' OR ', array_slice($where, 1)) . ') ORDER BY m.created_at DESC LIMIT ?';
        $stmt = $this->db->prepare($sql);
        $i = 1;
        $stmt->bindValue($i++, $channel);
        for ($p = 1; $p < count($params); $p++) {
            $value = $params[$p];
            $stmt->bindValue($i++, $value, is_int($value) ? PDO::PARAM_INT : PDO::PARAM_STR);
        }
        $stmt->bindValue($i, max(1, $limit), PDO::PARAM_INT);
        $stmt->execute();
        return $stmt->fetchAll();
    }

    private function normalizeChannelSql(string $channel, string $field): string
    {
        if ($channel === 'sms') {
            return "REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(COALESCE({$field}, '')), ' ', ''), '-', ''), '(', ''), ')', ''), '+', ''), '.', ''), '/', '')";
        }
        return "LOWER(TRIM(COALESCE({$field}, '')))";
    }

    private function isInboundUnreadLike(array $message): bool
    {
        if ((string)($message['direction'] ?? '') !== 'inbound') {
            return false;
        }

        $metadata = $this->decodeMetadata((string)($message['metadata_json'] ?? ''));
        if (array_key_exists('unread', $metadata)) {
            return !empty($metadata['unread']);
        }
        if (array_key_exists('is_unread', $metadata)) {
            return !empty($metadata['is_unread']);
        }
        if (array_key_exists('seen', $metadata)) {
            return empty($metadata['seen']);
        }
        if (array_key_exists('is_seen', $metadata)) {
            return empty($metadata['is_seen']);
        }

        return in_array((string)($message['status'] ?? ''), ['received', 'queued'], true);
    }

    private function emailConversationRows(string $query = '', array $filters = []): array
    {
        $parsedSearch = $this->parseMailboxSearchQuery($query);
        $plainQuery = (string)($parsedSearch['plain_query'] ?? '');
        $searchOps = (array)($parsedSearch['operators'] ?? []);
        $all = $this->search([
            'q' => $plainQuery,
            'channel' => 'email',
            'direction' => 'all',
            'status' => 'all',
        ], 1, 5000)['data'];

        $grouped = [];
        foreach ($all as $row) {
            $key = $row['thread_key'] ?: conversation_key('email', $row['from_address'] ?? null, $row['to_address'] ?? null, !empty($row['contact_id']) ? (int)$row['contact_id'] : null);
            $contactName = trim(($row['first_name'] ?? '') . ' ' . ($row['last_name'] ?? ''));
            if (!isset($grouped[$key])) {
                $grouped[$key] = [
                    'conversation_key' => $key,
                    'channel' => 'email',
                    'contact_id' => $row['contact_id'],
                    'contact_name' => $contactName,
                    'company' => $row['company'] ?? '',
                    'address' => $row['direction'] === 'inbound' ? ($row['from_address'] ?? '') : ($row['to_address'] ?? ''),
                    'subject' => $row['subject'] ?? '',
                    'last_message_at' => $row['created_at'],
                    'last_preview' => strip_tags(((string)($row['subject'] ?? '') !== '' ? $row['subject'] . ' — ' : '') . (string)($row['body'] ?? '')),
                    'status' => $row['status'],
                    'direction' => $row['direction'],
                    'message_count' => 0,
                    'unread_like_count' => 0,
                    'has_attachment' => !empty($row['attachment_path']),
                    'latest_message' => $row,
                    'legacy_folder_key' => null,
                    'source_mailbox_id' => null,
                    'source_mailbox_email' => '',
                    'source_mailbox_name' => '',
                    'source_provider' => '',
                    'search_from' => [],
                    'search_to' => [],
                    'search_subjects' => [],
                ];
            }
            $grouped[$key]['message_count']++;
            if (($row['direction'] ?? '') === 'inbound' && in_array((string)($row['status'] ?? ''), ['received', 'queued'], true)) {
                $grouped[$key]['unread_like_count']++;
            }
            if (!empty($row['attachment_path'])) {
                $grouped[$key]['has_attachment'] = true;
            }
            $grouped[$key]['search_from'][] = strtolower((string)($row['from_address'] ?? ''));
            $grouped[$key]['search_to'][] = strtolower((string)($row['to_address'] ?? ''));
            $grouped[$key]['search_subjects'][] = strtolower((string)($row['subject'] ?? ''));
            if ($grouped[$key]['contact_name'] === '' && $contactName !== '') {
                $grouped[$key]['contact_name'] = $contactName;
            }
            if ((string)($grouped[$key]['company'] ?? '') === '' && (string)($row['company'] ?? '') !== '') {
                $grouped[$key]['company'] = (string)$row['company'];
            }
            if ((string)($grouped[$key]['subject'] ?? '') === '' && (string)($row['subject'] ?? '') !== '') {
                $grouped[$key]['subject'] = $row['subject'];
            }
            $metadata = $this->mailboxSourceMetadata($row);
            if (!empty($metadata['connected_mailbox_id']) && empty($grouped[$key]['source_mailbox_id'])) {
                $grouped[$key]['source_mailbox_id'] = (int)$metadata['connected_mailbox_id'];
                $grouped[$key]['source_mailbox_email'] = (string)($metadata['connected_mailbox_email'] ?? '');
                $grouped[$key]['source_mailbox_name'] = (string)($metadata['connected_mailbox_name'] ?? ($metadata['connected_mailbox_email'] ?? ''));
                $grouped[$key]['source_provider'] = (string)($metadata['provider'] ?? '');
            }
            $legacyFolder = $this->explicitMailboxFolder($row);
            if ($legacyFolder !== null) {
                $grouped[$key]['legacy_folder_key'] = $legacyFolder;
            }
            if (strcmp((string)($row['created_at'] ?? ''), (string)($grouped[$key]['last_message_at'] ?? '')) >= 0) {
                $grouped[$key]['last_message_at'] = $row['created_at'];
                $grouped[$key]['last_preview'] = strip_tags(((string)($row['subject'] ?? '') !== '' ? $row['subject'] . ' — ' : '') . (string)($row['body'] ?? ''));
                $grouped[$key]['status'] = $row['status'];
                $grouped[$key]['direction'] = $row['direction'];
                $grouped[$key]['address'] = $row['direction'] === 'inbound' ? ($row['from_address'] ?? '') : ($row['to_address'] ?? '');
                $grouped[$key]['latest_message'] = $row;
                if (!empty($metadata['connected_mailbox_id'])) {
                    $grouped[$key]['source_mailbox_id'] = (int)$metadata['connected_mailbox_id'];
                    $grouped[$key]['source_mailbox_email'] = (string)($metadata['connected_mailbox_email'] ?? '');
                    $grouped[$key]['source_mailbox_name'] = (string)($metadata['connected_mailbox_name'] ?? ($metadata['connected_mailbox_email'] ?? ''));
                    $grouped[$key]['source_provider'] = (string)($metadata['provider'] ?? '');
                }
            }
        }

        $keys = array_keys($grouped);
        $states = (new MailConversation())->indexedByKeys($keys);
        $labelsByConversation = (new MailLabel())->assignmentsByConversation($keys);
        $rules = (new MailRule())->active();

        foreach ($grouped as $key => &$summary) {
            $state = $states[$key] ?? null;
            $latest = $summary['latest_message'] ?? [];
            $folderKey = $summary['legacy_folder_key'] ?: $this->defaultMailboxFolder($latest ?: $summary);
            $bucket = $state['focus_bucket'] ?? $this->defaultFocusBucket($latest ?: $summary);
            $ruleLabels = [];

            foreach ($rules as $rule) {
                if (!(new MailRule())->match($rule, $latest ?: $summary)) {
                    continue;
                }
                if (empty($state['folder_key']) && !empty($rule['target_folder_key']) && $folderKey === 'inbox') {
                    $folderKey = (string)$rule['target_folder_key'];
                }
                if (!empty($rule['target_bucket']) && empty($state['focus_bucket'])) {
                    $bucket = (string)$rule['target_bucket'];
                }
                if (!empty($rule['target_label_key'])) {
                    $ruleLabels[] = ['label_key' => $rule['target_label_key'], 'name' => ucwords(str_replace(['-', '_'], ' ', (string)$rule['target_label_key'])), 'color_token' => 'slate'];
                }
            }

            if ($state) {
                if (!empty($state['folder_key']) && !($state['folder_key'] === 'snoozed' && !empty($state['snoozed_until']) && strtotime((string)$state['snoozed_until']) <= time())) {
                    $folderKey = (string)$state['folder_key'];
                }
                if (!empty($state['is_archived'])) {
                    $folderKey = 'archive';
                }
                if (!empty($state['snoozed_until']) && strtotime((string)$state['snoozed_until']) > time()) {
                    $folderKey = 'snoozed';
                }
                if (!empty($state['focus_bucket'])) {
                    $bucket = (string)$state['focus_bucket'];
                }
            }

            $labels = $labelsByConversation[$key] ?? [];
            foreach ($ruleLabels as $ruleLabel) {
                $found = false;
                foreach ($labels as $label) {
                    if (($label['label_key'] ?? '') === ($ruleLabel['label_key'] ?? '')) {
                        $found = true;
                        break;
                    }
                }
                if (!$found) {
                    $labels[] = $ruleLabel;
                }
            }

            $summary['folder_key'] = $folderKey;
            $summary['focus_bucket'] = $bucket;
            $summary['labels'] = $labels;
            $summary['is_starred'] = !empty($state['is_starred']);
            $summary['is_pinned'] = !empty($state['is_pinned']);
            $summary['is_muted'] = !empty($state['is_muted']);
            $summary['sensitivity_level'] = $state['sensitivity_level'] ?? 'standard';
            $summary['claimed_by_user_id'] = (int)($state['claimed_by_user_id'] ?? 0) ?: null;
            $summary['claimed_at'] = $state['claimed_at'] ?? null;
            $summary['is_archived'] = !empty($state['is_archived']);
            $summary['snoozed_until'] = $state['snoozed_until'] ?? null;
            $summary['assigned_user_id'] = (int)($state['assigned_user_id'] ?? 0) ?: null;
            $summary['shared_mailbox_id'] = (int)($state['shared_mailbox_id'] ?? 0) ?: null;
            $summary['sla_status'] = $state['sla_status'] ?? 'normal';
            $summary['approval_status'] = $state['approval_status'] ?? 'not_required';
            $summary['approval_requested_user_id'] = (int)($state['approval_requested_user_id'] ?? 0) ?: null;
            $summary['approval_decided_user_id'] = (int)($state['approval_decided_user_id'] ?? 0) ?: null;
        }
        unset($summary);

        $rows = array_values($grouped);
        $rows = array_values(array_filter($rows, function (array $row) use ($filters, $searchOps): bool {
            if (!empty($filters['bucket']) && $filters['bucket'] !== 'all' && (string)($row['focus_bucket'] ?? 'focused') !== (string)$filters['bucket']) {
                return false;
            }
            if (!empty($filters['label_key']) && $filters['label_key'] !== 'all') {
                $match = false;
                foreach (($row['labels'] ?? []) as $label) {
                    if ((string)($label['label_key'] ?? '') === (string)$filters['label_key']) {
                        $match = true;
                        break;
                    }
                }
                if (!$match) {
                    return false;
                }
            }
            if (!empty($filters['starred']) && $filters['starred'] === '1' && empty($row['is_starred'])) {
                return false;
            }
            if (!empty($filters['shared_mailbox_id']) && (int)$filters['shared_mailbox_id'] > 0 && (int)($row['shared_mailbox_id'] ?? 0) !== (int)$filters['shared_mailbox_id']) {
                return false;
            }
            if (!empty($filters['connected_mailbox_id']) && (int)$filters['connected_mailbox_id'] > 0 && (int)($row['source_mailbox_id'] ?? 0) !== (int)$filters['connected_mailbox_id']) {
                return false;
            }
            if (!empty($filters['ownership']) && $filters['ownership'] !== 'all') {
                $currentUserId = (int)(current_user()['id'] ?? 0);
                $ownership = (string)$filters['ownership'];
                if ($ownership === 'mine' && ((int)($row['assigned_user_id'] ?? 0) !== $currentUserId && (int)($row['claimed_by_user_id'] ?? 0) !== $currentUserId)) {
                    return false;
                }
                if ($ownership === 'unassigned' && (!empty($row['assigned_user_id']) || !empty($row['claimed_by_user_id']))) {
                    return false;
                }
                if ($ownership === 'claimed' && empty($row['claimed_by_user_id'])) {
                    return false;
                }
            }
            if (!empty($filters['approval']) && $filters['approval'] !== 'all' && (string)($row['approval_status'] ?? 'not_required') !== (string)$filters['approval']) {
                return false;
            }
            if (!empty($filters['sla']) && $filters['sla'] !== 'all' && (string)($row['sla_status'] ?? 'normal') !== (string)$filters['sla']) {
                return false;
            }
            if (!$this->rowMatchesMailboxSearchOperators($row, $searchOps)) {
                return false;
            }
            return true;
        }));

        usort($rows, static function (array $a, array $b): int {
            $pinCompare = ((int)!empty($b['is_pinned'])) <=> ((int)!empty($a['is_pinned']));
            if ($pinCompare !== 0) {
                return $pinCompare;
            }
            $slaWeight = static fn(string $status): int => match ($status) {
                'breached' => 4, 'due_soon' => 3, 'watch' => 2, 'resolved' => 0, default => 1,
            };
            $slaCompare = $slaWeight((string)($b['sla_status'] ?? 'normal')) <=> $slaWeight((string)($a['sla_status'] ?? 'normal'));
            if ($slaCompare !== 0) {
                return $slaCompare;
            }
            return strcmp((string)($b['last_message_at'] ?? ''), (string)($a['last_message_at'] ?? ''));
        });
        return $rows;
    }

    private function parseMailboxSearchQuery(string $query): array
    {
        $query = trim($query);
        if ($query === '') {
            return ['plain_query' => '', 'operators' => [], 'chips' => []];
        }
        preg_match_all('/(?:[^\s"]+:"[^"]*"|"[^"]*"|\S+)/', $query, $matches);
        $tokens = $matches[0] ?? [];
        $plain = [];
        $operators = [];
        $chips = [];
        foreach ($tokens as $token) {
            $token = trim((string)$token);
            if ($token === '') {
                continue;
            }
            if (preg_match('/^([a-z_]+):(.+)$/i', $token, $parts)) {
                $key = strtolower(trim((string)$parts[1]));
                $value = trim((string)$parts[2], '" ');
                if ($value !== '') {
                    $operators[$key][] = $value;
                    $chips[] = $key . ':' . $value;
                    continue;
                }
            }
            $plain[] = trim($token, '"');
        }
        return [
            'plain_query' => trim(implode(' ', $plain)),
            'operators' => $operators,
            'chips' => $chips,
        ];
    }

    private function rowMatchesMailboxSearchOperators(array $row, array $operators): bool
    {
        if (!$operators) {
            return true;
        }
        foreach ($operators as $key => $values) {
            $values = array_values(array_filter(array_map(static fn($value) => strtolower(trim((string)$value)), (array)$values)));
            if (!$values) {
                continue;
            }
            switch ($key) {
                case 'from':
                    if (!$this->valueMatchesAny((array)($row['search_from'] ?? []), $values)) {
                        return false;
                    }
                    break;
                case 'to':
                    if (!$this->valueMatchesAny((array)($row['search_to'] ?? []), $values)) {
                        return false;
                    }
                    break;
                case 'subject':
                    if (!$this->valueMatchesAny((array)($row['search_subjects'] ?? []), $values)) {
                        return false;
                    }
                    break;
                case 'contact':
                    if (!$this->stringMatchesAny((string)($row['contact_name'] ?? ''), $values)) {
                        return false;
                    }
                    break;
                case 'company':
                    if (!$this->stringMatchesAny((string)($row['company'] ?? ''), $values)) {
                        return false;
                    }
                    break;
                case 'mailbox':
                case 'source':
                    $haystack = strtolower(trim((string)($row['source_mailbox_name'] ?? '') . ' ' . (string)($row['source_mailbox_email'] ?? '')));
                    if (!$this->stringMatchesAny($haystack, $values)) {
                        return false;
                    }
                    break;
                case 'provider':
                    if (!$this->stringMatchesAny((string)($row['source_provider'] ?? ''), $values)) {
                        return false;
                    }
                    break;
                case 'in':
                case 'folder':
                    if (!$this->stringMatchesAny((string)($row['folder_key'] ?? 'inbox'), $values)) {
                        return false;
                    }
                    break;
                case 'label':
                    $labelValues = [];
                    foreach ((array)($row['labels'] ?? []) as $label) {
                        $labelValues[] = strtolower((string)($label['label_key'] ?? ''));
                        $labelValues[] = strtolower((string)($label['name'] ?? ''));
                    }
                    if (!$this->valueMatchesAny($labelValues, $values)) {
                        return false;
                    }
                    break;
                case 'is':
                    foreach ($values as $value) {
                        if (!$this->matchesIsOperator($row, $value)) {
                            return false;
                        }
                    }
                    break;
                case 'has':
                    foreach ($values as $value) {
                        if ($value === 'attachment' && empty($row['has_attachment'])) {
                            return false;
                        }
                    }
                    break;
                case 'assigned':
                case 'owner':
                    foreach ($values as $value) {
                        if (!$this->matchesAssignedOperator($row, $value)) {
                            return false;
                        }
                    }
                    break;
                case 'shared':
                    foreach ($values as $value) {
                        if (!$this->matchesSharedOperator($row, $value)) {
                            return false;
                        }
                    }
                    break;
                case 'sla':
                    if (!$this->stringMatchesAny((string)($row['sla_status'] ?? 'normal'), $values)) {
                        return false;
                    }
                    break;
                case 'approval':
                    if (!$this->stringMatchesAny((string)($row['approval_status'] ?? 'not_required'), $values)) {
                        return false;
                    }
                    break;
                case 'bucket':
                    if (!$this->stringMatchesAny((string)($row['focus_bucket'] ?? 'focused'), $values)) {
                        return false;
                    }
                    break;
                default:
                    break;
            }
        }
        return true;
    }

    private function matchesIsOperator(array $row, string $value): bool
    {
        return match ($value) {
            'unread' => !empty($row['unread_like_count']),
            'read' => empty($row['unread_like_count']),
            'starred' => !empty($row['is_starred']),
            'pinned' => !empty($row['is_pinned']),
            'muted' => !empty($row['is_muted']),
            'attachment' => !empty($row['has_attachment']),
            'archived' => (string)($row['folder_key'] ?? '') === 'archive' || !empty($row['is_archived']),
            default => true,
        };
    }

    private function matchesAssignedOperator(array $row, string $value): bool
    {
        $currentUserId = (int)(current_user()['id'] ?? 0);
        $assigned = (int)($row['assigned_user_id'] ?? 0);
        $claimed = (int)($row['claimed_by_user_id'] ?? 0);
        return match ($value) {
            'me', 'mine' => ($assigned === $currentUserId || $claimed === $currentUserId) && $currentUserId > 0,
            'unassigned', 'none' => $assigned === 0 && $claimed === 0,
            'claimed' => $claimed > 0,
            'any' => $assigned > 0 || $claimed > 0,
            default => ctype_digit($value) ? ($assigned === (int)$value || $claimed === (int)$value) : false,
        };
    }

    private function matchesSharedOperator(array $row, string $value): bool
    {
        $sharedId = (int)($row['shared_mailbox_id'] ?? 0);
        if (in_array($value, ['none', 'unassigned'], true)) {
            return $sharedId === 0;
        }
        if ($value === 'any') {
            return $sharedId > 0;
        }
        return ctype_digit($value) ? $sharedId === (int)$value : false;
    }

    private function valueMatchesAny(array $haystackValues, array $needles): bool
    {
        foreach ($haystackValues as $candidate) {
            if ($this->stringMatchesAny((string)$candidate, $needles)) {
                return true;
            }
        }
        return false;
    }

    private function stringMatchesAny(string $haystack, array $needles): bool
    {
        $haystack = strtolower(trim($haystack));
        foreach ($needles as $needle) {
            if ($needle === '') {
                continue;
            }
            if ($haystack !== '' && str_contains($haystack, strtolower($needle))) {
                return true;
            }
        }
        return false;
    }

    private function defaultMailboxFolder(array $message): string
    {
        if (($message['channel'] ?? '') !== 'email') {
            return 'inbox';
        }
        if (($message['direction'] ?? '') === 'inbound') {
            return 'inbox';
        }
        return in_array((string)($message['status'] ?? 'queued'), ['queued', 'processing'], true) ? 'outbox' : 'sent';
    }

    private function defaultFocusBucket(array $message): string
    {
        if (!empty($message['campaign_id'])) {
            return 'other';
        }
        if (!empty($message['contact_id']) || !empty($message['company'])) {
            return 'focused';
        }
        $subject = strtolower((string)($message['subject'] ?? ''));
        if (str_contains($subject, 'campaign') || str_contains($subject, 'newsletter') || str_contains($subject, 'notification')) {
            return 'other';
        }
        return 'focused';
    }

    private function explicitMailboxFolder(array $message): ?string
    {
        $metadata = $this->decodeMetadata((string)($message['metadata_json'] ?? ''));
        $folder = trim((string)($metadata['mailbox_folder'] ?? ''));
        return $folder !== '' ? strtolower($folder) : null;
    }

    private function mailboxSourceMetadata(array $message): array
    {
        $metadata = $this->decodeMetadata((string)($message['metadata_json'] ?? ''));
        return [
            'connected_mailbox_id' => (int)($metadata['connected_mailbox_id'] ?? 0) ?: null,
            'connected_mailbox_email' => (string)($metadata['connected_mailbox_email'] ?? ''),
            'connected_mailbox_name' => (string)($metadata['connected_mailbox_name'] ?? ''),
            'provider' => (string)($metadata['provider'] ?? ''),
        ];
    }

    private function decodeMetadata(string $json): array
    {
        $data = json_decode($json, true);
        return is_array($data) ? $data : [];
    }
}
