root
e90d6a185f
GetRecentArticlesByBrand now folds the latest publish record per target into an aggregated publish_status (publishing / partial_success / success / failed), falling back to the stored status when no records exist. Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
107 lines
4.7 KiB
SQL
107 lines
4.7 KiB
SQL
-- name: CountArticlesByTenantAndBrand :one
|
|
SELECT COUNT(*) FROM articles
|
|
WHERE tenant_id = sqlc.arg(tenant_id) AND brand_id = sqlc.arg(brand_id) AND deleted_at IS NULL;
|
|
|
|
-- name: CountPublishedArticlesByTenantAndBrand :one
|
|
SELECT COUNT(*) FROM articles
|
|
WHERE tenant_id = sqlc.arg(tenant_id) AND brand_id = sqlc.arg(brand_id) AND publish_status = 'success' AND deleted_at IS NULL;
|
|
|
|
-- name: CountBrandsByTenant :one
|
|
SELECT COUNT(*) FROM brands
|
|
WHERE tenant_id = sqlc.arg(tenant_id) AND deleted_at IS NULL;
|
|
|
|
-- name: GetRecentArticlesByBrand :many
|
|
SELECT a.id, a.brand_id, a.generate_status,
|
|
CASE
|
|
WHEN COALESCE(publish_status_summary.record_count, 0) = 0 THEN a.publish_status
|
|
WHEN COALESCE(publish_status_summary.has_publishing, false) THEN 'publishing'
|
|
WHEN COALESCE(publish_status_summary.has_success, false)
|
|
AND COALESCE(publish_status_summary.has_failed, false) THEN 'partial_success'
|
|
WHEN COALESCE(publish_status_summary.has_success, false) THEN 'success'
|
|
ELSE 'failed'
|
|
END AS publish_status,
|
|
a.source_type, a.created_at,
|
|
av.title, av.markdown_content, av.word_count, av.source_label,
|
|
t.template_name,
|
|
COALESCE(
|
|
NULLIF(gt.input_params_json ->> 'generation_mode', ''),
|
|
CASE WHEN a.source_type = 'custom_generation' THEN 'instant'::TEXT ELSE NULL::TEXT END
|
|
) AS generation_mode
|
|
FROM articles a
|
|
LEFT JOIN article_versions av ON av.id = a.current_version_id
|
|
LEFT JOIN article_templates t ON t.id = a.template_id
|
|
LEFT JOIN LATERAL (
|
|
SELECT COUNT(*) AS record_count,
|
|
BOOL_OR(latest.status_bucket = 'publishing') AS has_publishing,
|
|
BOOL_OR(latest.status_bucket = 'success') AS has_success,
|
|
BOOL_OR(latest.status_bucket = 'failed') AS has_failed
|
|
FROM (
|
|
SELECT DISTINCT ON (
|
|
CASE
|
|
WHEN COALESCE(pr.target_type, 'platform_account') = 'enterprise_site'
|
|
THEN 'enterprise_site:' || COALESCE(pr.target_connection_id::TEXT, '')
|
|
ELSE 'platform_account:' || pr.platform_id || ':' || COALESCE(NULLIF(pa.platform_uid, ''), pr.platform_account_id::TEXT, '')
|
|
END
|
|
)
|
|
CASE
|
|
WHEN pr.status IN ('success', 'published', 'publish_success') THEN 'success'
|
|
WHEN pr.status IN ('publishing', 'pending', 'queued', 'running') THEN 'publishing'
|
|
ELSE 'failed'
|
|
END AS status_bucket,
|
|
CASE
|
|
WHEN COALESCE(pr.target_type, 'platform_account') = 'enterprise_site'
|
|
THEN 'enterprise_site:' || COALESCE(pr.target_connection_id::TEXT, '')
|
|
ELSE 'platform_account:' || pr.platform_id || ':' || COALESCE(NULLIF(pa.platform_uid, ''), pr.platform_account_id::TEXT, '')
|
|
END AS target_identity
|
|
FROM publish_records pr
|
|
LEFT JOIN platform_accounts pa ON pa.id = pr.platform_account_id
|
|
WHERE pr.tenant_id = a.tenant_id
|
|
AND pr.article_id = a.id
|
|
AND pr.deleted_at IS NULL
|
|
ORDER BY target_identity, pr.created_at DESC, pr.id DESC
|
|
) latest
|
|
) publish_status_summary ON true
|
|
LEFT JOIN LATERAL (
|
|
SELECT input_params_json
|
|
FROM generation_tasks
|
|
WHERE article_id = a.id
|
|
ORDER BY created_at DESC
|
|
LIMIT 1
|
|
) gt ON true
|
|
WHERE a.tenant_id = sqlc.arg(tenant_id) AND a.brand_id = sqlc.arg(brand_id) AND a.deleted_at IS NULL
|
|
ORDER BY a.created_at DESC
|
|
LIMIT 10;
|
|
|
|
-- name: GetQuotaSummary :one
|
|
SELECT COALESCE(
|
|
(SELECT balance_after FROM tenant_quota_ledgers
|
|
WHERE tenant_id = sqlc.arg(tenant_id) AND quota_type = 'article_generation'
|
|
ORDER BY created_at DESC LIMIT 1), 0
|
|
)::INT AS balance;
|
|
|
|
-- name: GetActivePlanForTenant :one
|
|
SELECT p.plan_code, p.name AS plan_name, p.quota_policy_json,
|
|
s.start_at, s.end_at
|
|
FROM tenant_plan_subscriptions s
|
|
JOIN plans p ON p.id = s.plan_id
|
|
WHERE s.tenant_id = sqlc.arg(tenant_id) AND s.status = 'active' AND s.deleted_at IS NULL
|
|
AND s.end_at > NOW()
|
|
LIMIT 1;
|
|
|
|
-- name: ListKolCardsForTenant :many
|
|
SELECT sp.id AS subscription_prompt_id, sp.granted_at,
|
|
GREATEST(p.updated_at, k.updated_at)::timestamptz AS updated_at,
|
|
p.name AS prompt_name, p.platform_hint,
|
|
k.name AS package_name, k.cover_url AS package_cover,
|
|
pf.display_name AS kol_display_name
|
|
FROM kol_subscription_prompts sp
|
|
JOIN kol_prompts p ON p.id = sp.prompt_id AND p.deleted_at IS NULL
|
|
JOIN kol_packages k ON k.id = sp.package_id AND k.deleted_at IS NULL
|
|
JOIN kol_profiles pf ON pf.id = k.kol_profile_id
|
|
WHERE sp.tenant_id = sqlc.arg(tenant_id)::bigint
|
|
AND sp.status = 'active'
|
|
AND p.status = 'active'
|
|
AND p.published_revision_no IS NOT NULL
|
|
ORDER BY GREATEST(p.updated_at, k.updated_at)::timestamptz DESC, sp.granted_at DESC
|
|
LIMIT 8;
|