-- name: CreateCategory :exec
INSERT INTO categories(name, code, sort)
VALUES ($1, $2, $3);

-- name: ListCategories :many
SELECT *
FROM categories
ORDER BY sort DESC, id
LIMIT $1 OFFSET $2;

-- name: CountCategories :one
SELECT COUNT(*)
FROM categories;

-- name: ListAllCategories :many
SELECT *
FROM categories
ORDER BY sort DESC, id;

-- name: UpdateCategory :execrows
UPDATE categories
SET name = coalesce(sqlc.narg('name'), name),
    code = coalesce(sqlc.narg('code'), code),
    sort = coalesce(sqlc.narg('sort'), sort)
WHERE id = sqlc.arg('id');

-- name: DeleteCategory :execrows
DELETE
FROM categories
WHERE id = $1;

-- name: CreatePostCategory :exec
INSERT INTO post_category(post_id, category_id)
VALUES ($1, $2);

-- name: DeletePostCategoryByPostID :exec
DELETE
FROM post_category
WHERE post_id = $1;

-- name: DeletePostCategoryByCategoryID :exec
DELETE
FROM post_category
WHERE category_id = $1;