-- name: InsertBundle :one
INSERT INTO bundles (
   site_id,
   name,
   created_at,
   updated_at
) VALUES ($1, $2, $3, $3) RETURNING id;

-- name: ListBundles :many
SELECT * FROM bundles WHERE site_id = $1;

-- name: GetBundleWithID :one
SELECT * FROM bundles WHERE id = $1;

-- name: GetSiteBundleInfo :many
WITH page_counts AS (
    SELECT b.bundle_id, count(*) AS page_count FROM pages b WHERE b.site_id = $1 GROUP BY bundle_id
), index_pages AS (
    SELECT p.id AS index_page_id, p.bundle_id FROM pages p WHERE p.site_id = $1 AND p.role = 'index'
)
SELECT b.bundle_id, b.page_count, p.index_page_id FROM page_counts b LEFT OUTER JOIN index_pages p ON b.bundle_id = p.bundle_id;