AI-News/backend/app/db/queries/sql/queries.sql

-- ============================================================================
-- Articles & Favorites & Tags
-- 要求 articles 表字段至少包含：
--   id, slug, title, description, body, cover, author_id, views,
--   is_top, is_featured, sort_weight, created_at, updated_at
-- ============================================================================

-- 创建文章（支持 cover）
-- 单行返回 -> 使用 ^
-- name: create_new_article^
INSERT INTO articles (
  slug,
  title,
  description,
  body,
  cover,
  author_id,
  views
) VALUES (
  :slug,
  :title,
  :description,
  :body,
  :cover,
  (SELECT id FROM users WHERE username = :author_username),
  0
)
RETURNING
  articles.id,
  articles.slug,
  articles.title,
  articles.description,
  articles.body,
  articles.cover,
  articles.views,
  articles.is_top,
  articles.is_featured,
  articles.sort_weight,
  articles.created_at,
  articles.updated_at,
  (SELECT username FROM users WHERE id = articles.author_id) AS author_username;

-- 更新文章（包含 cover）
-- 单行（返回 updated_at） -> ^
-- name: update_article^
UPDATE articles
SET
  slug        = COALESCE(:new_slug, slug),
  title       = COALESCE(:new_title, title),
  body        = COALESCE(:new_body, body),
  description = COALESCE(:new_description, description),
  cover       = :new_cover
WHERE
  slug = :slug
  AND author_id = (SELECT id FROM users WHERE username = :author_username)
RETURNING updated_at;

-- 删除文章
-- 执行型，无返回 -> !
-- name: delete_article!
DELETE FROM articles
WHERE
  slug = :slug
  AND author_id = (SELECT id FROM users WHERE username = :author_username);

-- 根据 slug 获取单篇文章（带 cover）
-- 单行返回 -> ^
-- name: get_article_by_slug^
SELECT
  a.id,
  a.slug,
  a.title,
  a.description,
  a.body,
  a.cover,
  a.views,
  a.is_top,
  a.is_featured,
  a.sort_weight,
  a.created_at,
  a.updated_at,
  u.username AS author_username
FROM articles AS a
JOIN users AS u ON u.id = a.author_id
WHERE a.slug = :slug;

-- Feed / 列表文章（带 cover）
-- 多行结果 -> 不能用 ^
-- name: get_articles_for_feed
SELECT
  a.id,
  a.slug,
  a.title,
  a.description,
  a.body,
  a.cover,
  a.views,
  a.is_top,
  a.is_featured,
  a.sort_weight,
  a.created_at,
  a.updated_at,
  u.username AS author_username
FROM articles AS a
JOIN users AS u
  ON u.id = a.author_id
JOIN followers_to_followings AS f
  ON f.following_id = u.id
WHERE f.follower_id = (
    SELECT id FROM users WHERE username = :follower_username
)
ORDER BY a.is_top DESC, a.sort_weight DESC, a.created_at DESC
LIMIT :limit OFFSET :offset;

-- ======================================================================
-- Tags 相关
-- ======================================================================

-- 给文章添加标签
-- 执行型 -> !
-- name: add_tags_to_article!
INSERT INTO articles_to_tags (article_id, tag)
SELECT a.id, :tag
FROM articles a
WHERE a.slug = :slug;

-- 获取文章的所有标签
-- name: get_tags_for_article_by_slug
SELECT t.tag
FROM articles_to_tags t
JOIN articles a ON a.id = t.article_id
WHERE a.slug = :slug
ORDER BY t.tag;

-- ======================================================================
-- Favorites 相关
-- ======================================================================

-- 统计收藏数
-- 单值 -> ^
-- name: get_favorites_count_for_article^
SELECT COUNT(*)::int AS favorites_count
FROM favorites f
JOIN articles a ON a.id = f.article_id
WHERE a.slug = :slug;

-- 是否已收藏
-- 单值布尔 -> ^
-- name: is_article_in_favorites^
SELECT EXISTS (
  SELECT 1
  FROM favorites f
  JOIN articles a ON a.id = f.article_id
  JOIN users u ON u.id = f.user_id
  WHERE a.slug = :slug
    AND u.username = :username
) AS favorited;

-- 加入收藏
-- 执行型 -> !
-- name: add_article_to_favorites!
INSERT INTO favorites (user_id, article_id)
SELECT
  (SELECT id FROM users WHERE username = :username),
  (SELECT id FROM articles WHERE slug = :slug)
ON CONFLICT DO NOTHING;

-- 取消收藏
-- 执行型 -> !
-- name: remove_article_from_favorites!
DELETE FROM favorites
WHERE user_id = (SELECT id FROM users WHERE username = :username)
  AND article_id = (SELECT id FROM articles WHERE slug = :slug);

-- ======================================================================
-- Views 相关
-- ======================================================================

-- 访问量 +1，返回最新值
-- name: increment_article_views^
UPDATE articles
SET views = views + 1
WHERE slug = :slug
RETURNING views;