<?php
declare(strict_types=1);

namespace App\Models;

final class EmailLog extends BaseModel
{
    protected static string $table = 'emails';
    protected static bool $softDelete = false;

    public static function paginate(int $page, int $perPage, ?int $ownerId = null, string $q = '', string $filter = 'all'): array
    {
        $where = 'e.tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $where .= ' AND e.owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($q !== '') {
            $where .= ' AND (e.subject LIKE :q OR e.to_email LIKE :q2 OR e.from_email LIKE :q3 OR e.body LIKE :q4)';
            $like = '%' . $q . '%';
            $params['q'] = $like;
            $params['q2'] = $like;
            $params['q3'] = $like;
            $params['q4'] = $like;
        }
        if ($filter === 'sent' || $filter === 'logged' || $filter === 'failed') {
            $where .= ' AND e.status = :st';
            $params['st'] = $filter;
        } elseif ($filter === 'in' || $filter === 'out') {
            $where .= ' AND e.direction = :dir';
            $params['dir'] = $filter;
        }
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM emails e WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT e.*, CONCAT(u.first_name,' ',u.last_name) AS owner_name,
                    COALESCE(
                      a.name,
                      CONCAT(c.first_name, ' ', c.last_name),
                      CONCAT(l.first_name, ' ', l.last_name),
                      o.name
                    ) AS related_name
             FROM emails e
             LEFT JOIN users u ON u.id = e.owner_id
             LEFT JOIN accounts a ON e.related_type = 'account' AND a.id = e.related_id
             LEFT JOIN contacts c ON e.related_type = 'contact' AND c.id = e.related_id
             LEFT JOIN leads l ON e.related_type = 'lead' AND l.id = e.related_id
             LEFT JOIN opportunities o ON e.related_type = 'opportunity' AND o.id = e.related_id
             WHERE $where
             ORDER BY e.sent_at DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function boardCounts(?int $ownerId = null): array
    {
        $sql = "SELECT
                    COUNT(*) AS all_n,
                    SUM(status = 'sent') AS sent_n,
                    SUM(status = 'logged') AS logged_n,
                    SUM(status = 'failed') AS failed_n,
                    SUM(direction = 'in') AS in_n,
                    SUM(sent_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')) AS month_n
                FROM emails
                WHERE tenant_id = :tid";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        $row = static::db()->fetch($sql, $params) ?: [];
        return [
            'all' => (int) ($row['all_n'] ?? 0),
            'sent' => (int) ($row['sent_n'] ?? 0),
            'logged' => (int) ($row['logged_n'] ?? 0),
            'failed' => (int) ($row['failed_n'] ?? 0),
            'in' => (int) ($row['in_n'] ?? 0),
            'month' => (int) ($row['month_n'] ?? 0),
        ];
    }

    public static function sentThisMonth(?int $ownerId = null, bool $beforeToday = false): int
    {
        $sql = "SELECT COUNT(*) FROM emails WHERE tenant_id = :tid AND sent_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')";
        $params = ['tid' => static::tenantId()];
        if ($ownerId) {
            $sql .= ' AND owner_id = :oid';
            $params['oid'] = $ownerId;
        }
        if ($beforeToday) {
            $sql .= ' AND sent_at < CURDATE()';
        }
        return (int) static::db()->fetchColumn($sql, $params);
    }
}
