-- name: GetAllDomains :many SELECT * FROM virtual_domains ORDER BY name ASC; -- name: GetDomainByID :one SELECT * FROM virtual_domains WHERE id = ?; -- name: GetDomainByName :one SELECT * FROM virtual_domains WHERE name = ?; -- name: CreateDomain :exec INSERT INTO virtual_domains (name) VALUES (?); -- name: DeleteDomain :exec DELETE FROM virtual_domains WHERE id = ?; -- name: GetAllDomainsWithCounts :many SELECT d.id, d.name, d.created_at, COALESCE(u.user_count, 0) as user_count, COALESCE(a.alias_count, 0) as alias_count FROM virtual_domains d LEFT JOIN (SELECT domain_id, COUNT(*) as user_count FROM virtual_users GROUP BY domain_id) u ON u.domain_id = d.id LEFT JOIN (SELECT domain_id, COUNT(*) as alias_count FROM virtual_aliases GROUP BY domain_id) a ON a.domain_id = d.id ORDER BY d.name ASC;