<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Database;

final class Campaign extends BaseModel
{
    protected static string $table = 'campaigns';
    protected static bool $softDelete = true;

    public static function paginate(int $page, int $perPage, string $status = 'all'): array
    {
        $where = 'c.tenant_id = :tid AND c.deleted_at IS NULL';
        $params = ['tid' => static::tenantId()];
        if ($status === 'sent' || $status === 'draft') {
            $where .= ' AND c.status = :st';
            $params['st'] = $status;
        }
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM campaigns c WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT c.*, l.name AS list_name, t.name AS template_name
             FROM campaigns c
             LEFT JOIN marketing_lists l ON l.id = c.list_id
             LEFT JOIN email_templates t ON t.id = c.template_id
             WHERE $where
             ORDER BY c.sent_at IS NULL, c.sent_at DESC, c.created_at DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function detail(int $id): ?array
    {
        return static::db()->fetch(
            "SELECT c.*, l.name AS list_name, t.name AS template_name
             FROM campaigns c
             LEFT JOIN marketing_lists l ON l.id = c.list_id
             LEFT JOIN email_templates t ON t.id = c.template_id
             WHERE c.id = :id AND c.tenant_id = :tid AND c.deleted_at IS NULL",
            ['id' => $id, 'tid' => static::tenantId()]
        );
    }

    public static function findInTenant(int $id, int $tenantId): ?array
    {
        return Database::instance()->fetch(
            'SELECT * FROM campaigns WHERE id = :id AND tenant_id = :tid',
            ['id' => $id, 'tid' => $tenantId]
        );
    }

    public static function stats(int $id): array
    {
        $row = static::db()->fetch(
            "SELECT COUNT(*) AS total,
                    SUM(status = 'sent') AS delivered,
                    SUM(opened_at IS NOT NULL) AS opened,
                    SUM(clicked_at IS NOT NULL) AS clicked,
                    SUM(status = 'bounced') AS bounced,
                    SUM(status = 'unsubscribed') AS unsubscribed
             FROM campaign_recipients WHERE tenant_id = :tid AND campaign_id = :id",
            ['tid' => static::tenantId(), 'id' => $id]
        );
        return $row ?: ['total' => 0, 'delivered' => 0, 'opened' => 0, 'clicked' => 0, 'bounced' => 0, 'unsubscribed' => 0];
    }

    public static function monthSummary(): array
    {
        $row = static::db()->fetch(
            "SELECT COUNT(DISTINCT c.id) AS campaigns_sent,
                    COUNT(r.id) AS delivered,
                    SUM(r.opened_at IS NOT NULL) AS opened,
                    SUM(r.clicked_at IS NOT NULL) AS clicked,
                    SUM(r.status = 'bounced') AS bounced,
                    SUM(r.status = 'unsubscribed') AS unsubscribed
             FROM campaigns c
             LEFT JOIN campaign_recipients r ON r.campaign_id = c.id
             WHERE c.tenant_id = :tid AND c.deleted_at IS NULL AND c.status = 'sent'
               AND c.sent_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')",
            ['tid' => static::tenantId()]
        );
        return $row ?: [];
    }

    public static function boardCounts(): array
    {
        $sql = "SELECT
                    SUM(status = 'sent') AS sent_n,
                    SUM(status = 'draft') AS draft_n
                FROM campaigns
                WHERE tenant_id = :tid AND deleted_at IS NULL";
        $row = static::db()->fetch($sql, ['tid' => static::tenantId()]) ?: [];
        $sent = (int) ($row['sent_n'] ?? 0);
        $draft = (int) ($row['draft_n'] ?? 0);
        return [
            'sent' => $sent,
            'draft' => $draft,
            'all' => $sent + $draft,
        ];
    }
}
