-- name: CreatePost :one
INSERT INTO posts(title, cover_id, slug, content, summary, status, sort, published_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8)
RETURNING id;

-- name: ListPosts :many
WITH paginated_posts AS (
    -- 第一步：先精准查出当前页需要的文章 ID
    SELECT id,
           title,
           cover_id,
           slug,
           summary,
           status,
           sort,
           published_at,
           created_at,
           updated_at
    FROM posts
    ORDER BY sort DESC, published_at DESC, id DESC
    LIMIT $1 OFFSET $2)
-- 第二步：用这极少量的记录去进行 JOIN
SELECT p.*,
       f.file_url                 AS cover,
       f.file_size                AS cover_size,
       c.name                     AS category_name,
       c.id                       AS category_id,
       COALESCE(ps.view_count, 0) AS view_count,
       COALESCE(
                       jsonb_agg(
                       jsonb_build_object(
                               'id', t.id,
                               'name', t.name,
                               'code', t.code
                       )
                       ORDER BY t.sort DESC, t.id
                                ) FILTER (WHERE t.id IS NOT NULL),
                       '[]'::jsonb
       )                          AS tags
FROM paginated_posts p
         LEFT JOIN files f ON f.id = p.cover_id
         LEFT JOIN post_category pc ON pc.post_id = p.id
         LEFT JOIN categories c ON c.id = pc.category_id
         LEFT JOIN post_stats ps ON ps.post_id = p.id
         LEFT JOIN post_tag pt ON pt.post_id = p.id
         LEFT JOIN tags t ON t.id = pt.tag_id
GROUP BY p.id,
         p.title,
         p.cover_id,
         p.slug,
         p.summary,
         p.status,
         p.sort,
         p.published_at,
         p.created_at,
         p.updated_at,
         f.file_url,
         f.file_size,
         c.name,
         c.id,
         ps.view_count
ORDER BY p.sort DESC, p.published_at DESC, p.id DESC;

-- name: CountPosts :one
SELECT COUNT(*)
FROM posts;

-- name: GetPostByID :one
SELECT p.*,
       f.file_url  AS cover,
       f.file_size AS cover_size,
       c.name      AS category_name,
       c.id        AS category_id,
       COALESCE(
                       array_agg(pt.tag_id ORDER BY pt.tag_id) FILTER (WHERE pt.tag_id IS NOT NULL),
                       ARRAY []::integer[]
       )           AS tags
FROM posts p
         LEFT JOIN files f ON f.id = p.cover_id
         LEFT JOIN post_category pc ON pc.post_id = p.id
         LEFT JOIN categories c ON c.id = pc.category_id
         LEFT JOIN post_tag pt ON pt.post_id = p.id
WHERE p.id = $1
GROUP BY p.id,
         f.file_url,
         f.file_size,
         c.name,
         c.id
LIMIT 1;

-- name: UpdatePost :execrows
UPDATE posts
SET title        = coalesce(sqlc.narg('title'), title),
    cover_id     = CASE WHEN @update_cover_id::boolean THEN @cover_id ELSE cover_id END,
    slug         = coalesce(sqlc.narg('slug'), slug),
    content      = coalesce(sqlc.narg('content'), content),
    summary      = coalesce(sqlc.narg('summary'), summary),
    status       = coalesce(sqlc.narg('status'), status),
    sort         = coalesce(sqlc.narg('sort'), sort),
    published_at = coalesce(sqlc.narg('published_at'), published_at)
WHERE id = sqlc.arg('id');

-- name: DeletePost :execrows
DELETE
FROM posts
WHERE id = $1;

-- web -------------------------------------------------------

-- name: GetPublicPostBySlug :one
SELECT p.id,
       p.title,
       p.slug,
       p.content,
       p.summary,
       p.published_at,
       f.file_url                 AS cover,
       COALESCE(ps.view_count, 0) AS view_count,
       c.code                     AS category_code,
       c.name                     AS category_name,
       COALESCE(t.tags, '[]')     AS tags
FROM posts p
         LEFT JOIN files f ON f.id = p.cover_id
         LEFT JOIN post_stats ps ON ps.post_id = p.id
         LEFT JOIN post_category pc ON pc.post_id = p.id
         LEFT JOIN categories c ON pc.category_id = c.id
         LEFT JOIN LATERAL (
    SELECT jsonb_agg(
                   jsonb_build_object('name', tag.name, 'code', tag.code)
           ) AS tags
    FROM post_tag pt
             JOIN tags tag ON tag.id = pt.tag_id
    WHERE pt.post_id = p.id
    ) t ON true
WHERE p.slug = $1
  AND p.status = 1
  AND p.published_at < NOW()
LIMIT 1;

-- name: ListPublishedPosts :many
WITH paginated_posts AS (
    -- 第一步：先精准查出当前页需要的文章 ID
    SELECT id,
           title,
           cover_id,
           slug,
           summary,
           sort,
           published_at
    FROM posts
    WHERE status = 1
      AND published_at < NOW()
    ORDER BY sort DESC, published_at DESC, id DESC
    LIMIT $1 OFFSET $2)
SELECT p.*,
       f.file_url                 AS cover,
       c.name                     AS category_name,
       c.id                       AS category_id,
       c.code                     AS category_code,
       COALESCE(ps.view_count, 0) AS view_count,
       COALESCE(
                       jsonb_agg(
                       jsonb_build_object(
                               'id', t.id,
                               'name', t.name,
                               'code', t.code
                       )
                       ORDER BY t.sort DESC, t.id
                                ) FILTER (WHERE t.id IS NOT NULL),
                       '[]'::jsonb
       )                          AS tags
FROM paginated_posts p
         LEFT JOIN files f ON f.id = p.cover_id
         LEFT JOIN post_category pc ON pc.post_id = p.id
         LEFT JOIN categories c ON c.id = pc.category_id
         LEFT JOIN post_stats ps ON ps.post_id = p.id
         LEFT JOIN post_tag pt ON pt.post_id = p.id
         LEFT JOIN tags t ON t.id = pt.tag_id
GROUP BY p.id,
         p.title,
         p.cover_id,
         p.slug,
         p.summary,
         p.sort,
         p.published_at,
         f.file_url,
         c.name,
         c.id,
         c.code,
         ps.view_count
ORDER BY p.sort DESC, p.published_at DESC, p.id DESC;

-- name: CountPublishedPosts :one
SELECT COUNT(*)
FROM posts
WHERE status = 1
  AND published_at < NOW();

-- name: ListArchives :many
SELECT p.id,
       p.slug,
       p.title,
       p.published_at,
       c."name" AS category_name
FROM posts p
         LEFT JOIN post_category pc ON p.id = pc.post_id
         LEFT JOIN categories c ON c.id = pc.category_id
WHERE p.status = 1
  AND p.published_at < NOW()
ORDER BY p.published_at DESC, p.id DESC;

-- name: ListCategoryStats :many
SELECT c.id,
       c.name,
       c.code,
       COUNT(p.id) AS post_count
FROM categories c
         LEFT JOIN post_category pc ON c.id = pc.category_id
         LEFT JOIN posts p ON p.id = pc.post_id
    AND p.status = 1
    AND p.published_at < NOW()
GROUP BY c.id,
         c.sort,
         c.name,
         c.code
ORDER BY c.sort DESC, c.id;

-- name: ListPublishedPostsWithFilters :many
SELECT p.id,
       p.title,
       p.slug,
       p.summary,
       p.sort,
       p.published_at,
       f.file_url                   AS cover,
       c."name"                     AS category_name,
       c.code                       AS category_code,
       COALESCE(t.tags, '[]')       AS tags,
       COALESCE(ps."view_count", 0) AS view_count
FROM posts p
         LEFT JOIN post_category pc ON pc.post_id = p.id
         LEFT JOIN categories c ON c.id = pc.category_id
         LEFT JOIN post_stats ps ON ps.post_id = p.id
         LEFT JOIN files f ON f.id = p.cover_id
         LEFT JOIN LATERAL (
    SELECT jsonb_agg(
                   jsonb_build_object('name', tag.name, 'code', tag.code)
           ) AS tags
    FROM post_tag pt
             JOIN tags tag ON tag.id = pt.tag_id
    WHERE pt.post_id = p.id
    ) t ON true
WHERE
  -- 只显示已发布的文章
    p.status = 1
  AND p.published_at < NOW()
  AND
  -- category code 过滤（NULL 或空字符串时不过滤）
    (
        COALESCE(sqlc.narg('category_code')::text, '') = ''
            OR EXISTS (SELECT 1
                       FROM post_category pc2
                                JOIN categories c2 ON c2.id = pc2.category_id
                       WHERE pc2.post_id = p.id
                         AND c2.code = sqlc.narg('category_code')::text)
        )
  AND
  -- tag code 过滤（NULL 或空字符串时不过滤）
    (
        COALESCE(sqlc.narg('tag_code')::text, '') = ''
            OR EXISTS (SELECT 1
                       FROM post_tag pt2
                                JOIN tags t2 ON t2.id = pt2.tag_id
                       WHERE pt2.post_id = p.id
                         AND t2.code = sqlc.narg('tag_code')::text)
        )
ORDER BY p.sort DESC,
         p.published_at DESC,
         p.id DESC;

-- name: GetPostsForSitemap :many
SELECT slug, created_at, updated_at
FROM posts
WHERE status = 1
  AND published_at < NOW()
ORDER BY sort DESC, published_at DESC, id DESC;