geo/server/internal/tenant/repository/queries/image_folder.sql

-- name: ListImageFolders :many
SELECT f.id, f.tenant_id, f.brand_id, f.name, f.created_at, f.updated_at,
       COUNT(ia.id) FILTER (WHERE ia.status = 'active' AND ia.deleted_at IS NULL)::INT AS image_count
FROM image_folders f
LEFT JOIN image_assets ia ON ia.tenant_id = f.tenant_id AND ia.brand_id = f.brand_id AND ia.folder_id = f.id
WHERE f.tenant_id = sqlc.arg(tenant_id)
  AND f.brand_id = sqlc.arg(brand_id)
GROUP BY f.id
ORDER BY f.name;

-- name: CreateImageFolder :one
INSERT INTO image_folders (tenant_id, brand_id, name)
VALUES (sqlc.arg(tenant_id), sqlc.arg(brand_id), sqlc.arg(name))
RETURNING id;

-- name: UpdateImageFolder :exec
UPDATE image_folders
SET name = sqlc.arg(name), updated_at = NOW()
WHERE id = sqlc.arg(id)
  AND tenant_id = sqlc.arg(tenant_id)
  AND brand_id = sqlc.arg(brand_id);

-- name: GetImageFolder :one
SELECT id, tenant_id, brand_id, name, created_at, updated_at
FROM image_folders
WHERE id = sqlc.arg(id)
  AND tenant_id = sqlc.arg(tenant_id)
  AND brand_id = sqlc.arg(brand_id);

-- name: DeleteImageFolder :exec
DELETE FROM image_folders
WHERE id = sqlc.arg(id)
  AND tenant_id = sqlc.arg(tenant_id)
  AND brand_id = sqlc.arg(brand_id);

-- name: CountActiveImagesInFolder :one
SELECT COUNT(*)::INT AS count
FROM image_assets
WHERE tenant_id = sqlc.arg(tenant_id)
  AND brand_id = sqlc.arg(brand_id)
  AND folder_id = sqlc.arg(folder_id)
  AND status = 'active'
  AND deleted_at IS NULL;