<?php
declare(strict_types=1);

namespace App\Models;

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

    public static function allActive(): array
    {
        return static::db()->fetchAll(
            'SELECT * FROM email_templates 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 email_templates WHERE $where", $params);
        $offset = ($page - 1) * $perPage;
        $rows = static::db()->fetchAll(
            "SELECT * FROM email_templates 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 email_templates WHERE tenant_id = :tid AND deleted_at IS NULL',
            ['tid' => $tid]
        );
        $used = (int) static::db()->fetchColumn(
            'SELECT COUNT(DISTINCT template_id) FROM campaigns
             WHERE tenant_id = :tid AND deleted_at IS NULL AND template_id IS NOT NULL',
            ['tid' => $tid]
        );
        return [
            'all' => $total,
            'used' => $used,
            'unused' => max(0, $total - $used),
        ];
    }

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