<?php
require_once __DIR__ . '/../Core.php';
require_once __DIR__ . '/Setting.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' . $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($row['status'], ['received', 'queued'], true)) {
                $grouped[$key]['unread_like_count']++;
            }
        }
        usort($grouped, fn($a, $b) => strcmp($b['last_message_at'], $a['last_message_at']));
        $total = count($grouped);
        $meta = paginate_meta($page, $perPage, $total);
        return ['data' => array_slice($grouped, $meta['offset'], $meta['per_page']), 'meta' => $meta];
    }


    public function mailboxConversationSummary(string $folder = 'inbox', array $filters = [], int $page = 1, int $perPage = 20): array
    {
        $filtered = array_values(array_filter(
            $this->mailboxConversationDataset($filters),
            static fn(array $conversation): bool => (string)($conversation['mailbox_folder'] ?? 'inbox') === $folder
        ));
        $total = count($filtered);
        $meta = paginate_meta($page, $perPage, $total);
        return ['data' => array_slice($filtered, $meta['offset'], $meta['per_page']), 'meta' => $meta];
    }

    public function mailboxFolderCounts(array $customFolders = []): array
    {
        $counts = ['inbox' => 0, 'outbox' => 0, 'sent' => 0, 'junk' => 0, 'trash' => 0];
        foreach ($customFolders as $folder) {
            if (!empty($folder['token'])) {
                $counts[(string)$folder['token']] = 0;
            }
        }
        foreach ($this->mailboxConversationDataset([]) as $conversation) {
            $token = (string)($conversation['mailbox_folder'] ?? 'inbox');
            if (!isset($counts[$token])) {
                $counts[$token] = 0;
            }
            $counts[$token]++;
        }
        return $counts;
    }

    public function moveConversationToFolder(string $conversationKey, string $folderToken): int
    {
        $updated = 0;
        foreach ($this->thread($conversationKey, 500) as $row) {
            if (($row['channel'] ?? '') !== 'email') {
                continue;
            }
            $meta = $this->mailboxMetaArray((string)($row['metadata_json'] ?? ''));
            if (!isset($meta['mailbox']) || !is_array($meta['mailbox'])) {
                $meta['mailbox'] = [];
            }
            $meta['mailbox']['folder'] = $folderToken;
            $updated += $this->updateMailboxMetadata((int)$row['id'], $meta);
        }
        return $updated;
    }

    public function replaceMailboxFolderToken(string $fromToken, string $toToken = 'inbox'): int
    {
        $stmt = $this->db->query('SELECT id, metadata_json FROM messages WHERE channel = "email" ORDER BY id ASC');
        $updated = 0;
        foreach ($stmt->fetchAll() as $row) {
            $meta = $this->mailboxMetaArray((string)($row['metadata_json'] ?? ''));
            if ((string)($meta['mailbox']['folder'] ?? '') !== $fromToken) {
                continue;
            }
            if (!isset($meta['mailbox']) || !is_array($meta['mailbox'])) {
                $meta['mailbox'] = [];
            }
            $meta['mailbox']['folder'] = $toToken;
            $updated += $this->updateMailboxMetadata((int)$row['id'], $meta);
        }
        return $updated;
    }

    public function thread(string $conversationKey, int $limit = 200): 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 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);
        $metadataJson = $this->inheritMailboxMetadata($threadKey, (string)$data['channel'], $data['metadata_json'] ?? 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,
            $metadataJson,
            $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 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]);
    }

    private function mailboxConversationDataset(array $filters = []): array
    {
        $rows = $this->search([
            'q' => trim((string)($filters['q'] ?? '')),
            'channel' => 'email',
            'direction' => 'all',
            'status' => 'all',
        ], 1, 10000)['data'];
        $grouped = [];
        foreach ($rows as $row) {
            if (($row['channel'] ?? '') !== 'email') {
                continue;
            }
            $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' => 'email',
                    '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,
                    'has_inbound' => false,
                    'has_outbound' => false,
                    'has_outbox' => false,
                    'explicit_folder' => null,
                ];
            }
            $grouped[$key]['message_count']++;
            if (($row['direction'] ?? '') === 'inbound') {
                $grouped[$key]['has_inbound'] = true;
                if (in_array((string)($row['status'] ?? ''), ['received', 'queued'], true)) {
                    $grouped[$key]['unread_like_count']++;
                }
            }
            if (($row['direction'] ?? '') === 'outbound') {
                $grouped[$key]['has_outbound'] = true;
                if (in_array((string)($row['status'] ?? ''), ['queued', 'processing'], true)) {
                    $grouped[$key]['has_outbox'] = true;
                }
            }
            $explicitFolder = $this->messageMailboxFolderToken((string)($row['metadata_json'] ?? ''));
            if ($explicitFolder !== null && $explicitFolder !== '' && $grouped[$key]['explicit_folder'] === null) {
                $grouped[$key]['explicit_folder'] = $explicitFolder;
            }
        }
        foreach ($grouped as &$conversation) {
            $conversation['mailbox_folder'] = $this->resolveMailboxConversationFolder($conversation);
        }
        unset($conversation);
        $result = array_values($grouped);
        usort($result, static fn(array $a, array $b): int => strcmp((string)$b['last_message_at'], (string)$a['last_message_at']));
        return $result;
    }

    private function resolveMailboxConversationFolder(array $conversation): string
    {
        $explicit = trim((string)($conversation['explicit_folder'] ?? ''));
        if ($explicit !== '') {
            return $explicit;
        }
        if (!empty($conversation['has_inbound'])) {
            return 'inbox';
        }
        if (!empty($conversation['has_outbox'])) {
            return 'outbox';
        }
        if (!empty($conversation['has_outbound'])) {
            return 'sent';
        }
        return 'inbox';
    }

    private function mailboxMetaArray(?string $metadataJson): array
    {
        $decoded = json_decode((string)$metadataJson, true);
        return is_array($decoded) ? $decoded : [];
    }

    private function messageMailboxFolderToken(?string $metadataJson): ?string
    {
        $meta = $this->mailboxMetaArray($metadataJson);
        $token = trim((string)($meta['mailbox']['folder'] ?? ''));
        return $token !== '' ? $token : null;
    }

    private function updateMailboxMetadata(int $id, array $metadata): int
    {
        $stmt = $this->db->prepare('UPDATE messages SET metadata_json = ?, updated_at = NOW() WHERE id = ?');
        $stmt->execute([json_encode($metadata), $id]);
        return $stmt->rowCount() > 0 ? $stmt->rowCount() : 1;
    }

    private function inheritMailboxMetadata(string $threadKey, string $channel, ?string $metadataJson): ?string
    {
        if ($channel !== 'email' || $threadKey === '') {
            return $metadataJson;
        }
        $meta = $this->mailboxMetaArray($metadataJson);
        if (!empty($meta['mailbox']['folder'])) {
            return json_encode($meta);
        }
        $existingFolder = $this->latestMailboxFolderForThread($threadKey);
        if ($existingFolder === null) {
            return $metadataJson;
        }
        if (!isset($meta['mailbox']) || !is_array($meta['mailbox'])) {
            $meta['mailbox'] = [];
        }
        $meta['mailbox']['folder'] = $existingFolder;
        return json_encode($meta);
    }

    private function latestMailboxFolderForThread(string $threadKey): ?string
    {
        $stmt = $this->db->prepare('SELECT metadata_json FROM messages WHERE channel = "email" AND thread_key = ? ORDER BY id DESC LIMIT 25');
        $stmt->execute([$threadKey]);
        foreach ($stmt->fetchAll() as $row) {
            $token = $this->messageMailboxFolderToken((string)($row['metadata_json'] ?? ''));
            if ($token !== null) {
                return $token;
            }
        }
        return null;
    }

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

