<?php

namespace Core\App\Models;

class WhatsAppMessageModel
{
    private \PDO $pdo;

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

    /**
     * Get paginated messages with optional filters
     */
    public function getPaginated(int $page, int $limit, array $filters = []): array
    {
        $offset = ($page - 1) * $limit;
        $where  = [];
        $params = [];

        if (!empty($filters['status'])) {
            $where[]  = 'status = ?';
            $params[] = $filters['status'];
        }
        if (!empty($filters['event'])) {
            $where[]  = 'event = ?';
            $params[] = $filters['event'];
        }
        if (!empty($filters['phone'])) {
            $where[]  = 'phone LIKE ?';
            $params[] = '%' . $filters['phone'] . '%';
        }

        $whereSQL = $where ? 'WHERE ' . implode(' AND ', $where) : '';

        try {
            $totalStmt = $this->pdo->prepare("SELECT COUNT(*) FROM whatsapp_messages {$whereSQL}");
            $totalStmt->execute($params);
            $total = (int)$totalStmt->fetchColumn();

            $params[] = $limit;
            $params[] = $offset;
            $rows = $this->pdo->prepare("
                SELECT * FROM whatsapp_messages
                {$whereSQL}
                ORDER BY created_at DESC
                LIMIT ? OFFSET ?
            ");
            $rows->execute($params);

            return [
                'messages'    => $rows->fetchAll(\PDO::FETCH_ASSOC),
                'total'       => $total,
                'total_pages' => $total > 0 ? (int)ceil($total / $limit) : 1,
            ];
        } catch (\Throwable $e) {
            return ['messages' => [], 'total' => 0, 'total_pages' => 1];
        }
    }

    /**
     * Get 30-day stats
     */
    public function getStats30d(): array
    {
        try {
            $row = $this->pdo->query("
                SELECT
                    COUNT(*) as total,
                    SUM(status='sent') as sent,
                    SUM(status='failed') as failed
                FROM whatsapp_messages
                WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
            ")->fetch(\PDO::FETCH_ASSOC);
            return is_array($row) ? $row : ['total' => 0, 'sent' => 0, 'failed' => 0];
        } catch (\Throwable $e) {
            return ['total' => 0, 'sent' => 0, 'failed' => 0];
        }
    }
}
