<?php
declare(strict_types=1);

namespace App\Models;

use App\Core\Database;

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

    public static function isBlocked(string $email): bool
    {
        $email = strtolower(trim($email));
        if ($email === '') {
            return true;
        }
        $row = static::db()->fetch(
            'SELECT id FROM unsubscribes WHERE tenant_id = :tid AND email = :em',
            ['tid' => static::tenantId(), 'em' => $email]
        );
        return $row !== null;
    }

    /** @return array<string,true> */
    public static function emailsForTenant(int $tenantId): array
    {
        $rows = Database::instance()->fetchAll(
            'SELECT email FROM unsubscribes WHERE tenant_id = :tid',
            ['tid' => $tenantId]
        );
        $out = [];
        foreach ($rows as $r) {
            $out[strtolower((string) $r['email'])] = true;
        }
        return $out;
    }

    public static function add(int $tenantId, string $email, string $source, ?int $campaignId): void
    {
        $email = strtolower(trim($email));
        if ($email === '' || !filter_var($email, FILTER_VALIDATE_EMAIL)) {
            return;
        }
        Database::instance()->query(
            'INSERT INTO unsubscribes (tenant_id, email, source, campaign_id)
             VALUES (:tid, :em, :src, :cid)
             ON DUPLICATE KEY UPDATE source = unsubscribes.source',
            ['tid' => $tenantId, 'em' => $email, 'src' => $source, 'cid' => $campaignId]
        );
    }

    public static function paginate(int $page, int $perPage, string $source = 'all', string $q = ''): array
    {
        $where = 'u.tenant_id = :tid';
        $params = ['tid' => static::tenantId()];
        if (in_array($source, ['campaign', 'manual', 'import'], true)) {
            $where .= ' AND u.source = :src';
            $params['src'] = $source;
        }
        $q = strtolower(trim($q));
        if ($q !== '') {
            $where .= ' AND u.email LIKE :q';
            $params['q'] = '%' . $q . '%';
        }
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM unsubscribes u WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT u.*, c.name AS campaign_name
             FROM unsubscribes u
             LEFT JOIN campaigns c ON c.id = u.campaign_id AND c.tenant_id = u.tenant_id
             WHERE $where
             ORDER BY u.created_at DESC
             LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function boardCounts(): array
    {
        $row = static::db()->fetch(
            "SELECT COUNT(*) AS all_n,
                    SUM(source = 'campaign') AS campaign_n,
                    SUM(source = 'manual') AS manual_n,
                    SUM(source = 'import') AS import_n,
                    SUM(created_at >= DATE_FORMAT(CURDATE(), '%Y-%m-01')) AS month_n
             FROM unsubscribes
             WHERE tenant_id = :tid",
            ['tid' => static::tenantId()]
        ) ?: [];
        return [
            'all' => (int) ($row['all_n'] ?? 0),
            'campaign' => (int) ($row['campaign_n'] ?? 0),
            'manual' => (int) ($row['manual_n'] ?? 0),
            'import' => (int) ($row['import_n'] ?? 0),
            'month' => (int) ($row['month_n'] ?? 0),
        ];
    }
}
