<?php
declare(strict_types=1);

namespace App\Models;

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

    public static function allActive(): array
    {
        return static::db()->fetchAll(
            'SELECT * FROM marketing_lists WHERE tenant_id = :tid AND deleted_at IS NULL ORDER BY name',
            ['tid' => static::tenantId()]
        );
    }

    public static function paginate(int $page, int $perPage): array
    {
        $where = 'tenant_id = :tid AND deleted_at IS NULL';
        $params = ['tid' => static::tenantId()];
        $total = (int) static::db()->fetchColumn("SELECT COUNT(*) FROM marketing_lists WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT * FROM marketing_lists WHERE $where ORDER BY updated_at DESC, id DESC LIMIT $perPage OFFSET $offset",
            $params
        );
        return ['rows' => $rows, 'total' => $total];
    }

    public static function boardCounts(): array
    {
        $tid = static::tenantId();
        $total = (int) static::db()->fetchColumn(
            'SELECT COUNT(*) FROM marketing_lists WHERE tenant_id = :tid AND deleted_at IS NULL',
            ['tid' => $tid]
        );
        $filtered = (int) static::db()->fetchColumn(
            "SELECT COUNT(*) FROM marketing_lists
             WHERE tenant_id = :tid AND deleted_at IS NULL
               AND filters IS NOT NULL AND TRIM(filters) NOT IN ('', '{}', '[]')",
            ['tid' => $tid]
        );
        $inCampaigns = (int) static::db()->fetchColumn(
            'SELECT COUNT(DISTINCT list_id) FROM campaigns
             WHERE tenant_id = :tid AND deleted_at IS NULL AND list_id IS NOT NULL',
            ['tid' => $tid]
        );
        return [
            'all' => $total,
            'filtered' => $filtered,
            'open' => max(0, $total - $filtered),
            'in_campaigns' => $inCampaigns,
        ];
    }

    /** @return array<int,int> */
    public static function campaignCounts(): array
    {
        $rows = static::db()->fetchAll(
            'SELECT list_id, COUNT(*) AS n FROM campaigns
             WHERE tenant_id = :tid AND deleted_at IS NULL AND list_id IS NOT NULL
             GROUP BY list_id',
            ['tid' => static::tenantId()]
        );
        $out = [];
        foreach ($rows as $r) {
            $out[(int) $r['list_id']] = (int) $r['n'];
        }
        return $out;
    }
}
