freedakgmail
123 lines
4.6 KiB
SQL
123 lines
4.6 KiB
SQL
-- name: CreateApplication :one
|
|
INSERT INTO applications (
|
|
name, slug, description, long_description, icon_url,
|
|
category_id, creator_id, dept_id,
|
|
dify_app_id, dify_app_type, dify_api_key,
|
|
app_config, welcome_message, suggested_prompts,
|
|
max_tokens, temperature, status, visibility, is_template
|
|
) VALUES (
|
|
$1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19
|
|
) RETURNING *;
|
|
|
|
-- name: GetApplicationByID :one
|
|
SELECT * FROM applications WHERE id = $1;
|
|
|
|
-- name: GetApplicationBySlug :one
|
|
SELECT * FROM applications WHERE slug = $1;
|
|
|
|
-- name: UpdateApplication :one
|
|
UPDATE applications
|
|
SET name = COALESCE(sqlc.narg('name'), name),
|
|
description = COALESCE(sqlc.narg('description'), description),
|
|
long_description = COALESCE(sqlc.narg('long_description'), long_description),
|
|
icon_url = COALESCE(sqlc.narg('icon_url'), icon_url),
|
|
category_id = COALESCE(sqlc.narg('category_id'), category_id),
|
|
app_config = COALESCE(sqlc.narg('app_config'), app_config),
|
|
welcome_message = COALESCE(sqlc.narg('welcome_message'), welcome_message),
|
|
suggested_prompts = COALESCE(sqlc.narg('suggested_prompts'), suggested_prompts),
|
|
max_tokens = COALESCE(sqlc.narg('max_tokens'), max_tokens),
|
|
temperature = COALESCE(sqlc.narg('temperature'), temperature),
|
|
visibility = COALESCE(sqlc.narg('visibility'), visibility)
|
|
WHERE id = $1
|
|
RETURNING *;
|
|
|
|
-- name: UpdateApplicationStatus :exec
|
|
UPDATE applications SET status = $2 WHERE id = $1;
|
|
|
|
-- name: DeleteApplication :exec
|
|
DELETE FROM applications WHERE id = $1 AND status = 'draft';
|
|
|
|
-- name: ListStoreApps :many
|
|
SELECT a.*, c.name as category_name, c.slug as category_slug, u.name as creator_name
|
|
FROM applications a
|
|
LEFT JOIN categories c ON a.category_id = c.id
|
|
LEFT JOIN users u ON a.creator_id = u.id
|
|
WHERE a.status = 'approved'
|
|
AND a.visibility = 'public'
|
|
AND (sqlc.narg('category_slug')::VARCHAR IS NULL OR c.slug = sqlc.narg('category_slug'))
|
|
AND (sqlc.narg('search')::VARCHAR IS NULL
|
|
OR to_tsvector('simple', a.name || ' ' || COALESCE(a.description, ''))
|
|
@@ plainto_tsquery('simple', sqlc.narg('search')))
|
|
ORDER BY
|
|
CASE WHEN sqlc.narg('sort')::VARCHAR = 'popular' THEN a.usage_count END DESC,
|
|
CASE WHEN sqlc.narg('sort')::VARCHAR = 'rating' THEN a.avg_rating END DESC,
|
|
CASE WHEN sqlc.narg('sort')::VARCHAR IS NULL OR sqlc.narg('sort') = 'latest' THEN EXTRACT(EPOCH FROM a.published_at) END DESC
|
|
LIMIT $1 OFFSET $2;
|
|
|
|
-- name: CountStoreApps :one
|
|
SELECT COUNT(*) FROM applications a
|
|
LEFT JOIN categories c ON a.category_id = c.id
|
|
WHERE a.status = 'approved'
|
|
AND a.visibility = 'public'
|
|
AND (sqlc.narg('category_slug')::VARCHAR IS NULL OR c.slug = sqlc.narg('category_slug'))
|
|
AND (sqlc.narg('search')::VARCHAR IS NULL
|
|
OR to_tsvector('simple', a.name || ' ' || COALESCE(a.description, ''))
|
|
@@ plainto_tsquery('simple', sqlc.narg('search')));
|
|
|
|
-- name: ListFeaturedApps :many
|
|
SELECT a.*, c.name as category_name, u.name as creator_name
|
|
FROM applications a
|
|
LEFT JOIN categories c ON a.category_id = c.id
|
|
LEFT JOIN users u ON a.creator_id = u.id
|
|
WHERE a.is_featured = true AND a.status = 'approved' AND a.visibility = 'public'
|
|
ORDER BY a.usage_count DESC
|
|
LIMIT $1;
|
|
|
|
-- name: ListTopApps :many
|
|
SELECT a.*, c.name as category_name, u.name as creator_name
|
|
FROM applications a
|
|
LEFT JOIN categories c ON a.category_id = c.id
|
|
LEFT JOIN users u ON a.creator_id = u.id
|
|
WHERE a.status = 'approved' AND a.visibility = 'public'
|
|
ORDER BY a.usage_count DESC
|
|
LIMIT $1;
|
|
|
|
-- name: ListCreatorApps :many
|
|
SELECT a.*, c.name as category_name
|
|
FROM applications a
|
|
LEFT JOIN categories c ON a.category_id = c.id
|
|
WHERE a.creator_id = $1
|
|
ORDER BY a.updated_at DESC
|
|
LIMIT $2 OFFSET $3;
|
|
|
|
-- name: ListTemplates :many
|
|
SELECT a.*, c.name as category_name
|
|
FROM applications a
|
|
LEFT JOIN categories c ON a.category_id = c.id
|
|
WHERE a.is_template = true AND a.status = 'approved'
|
|
ORDER BY a.usage_count DESC;
|
|
|
|
-- name: IncrementUsageCount :exec
|
|
UPDATE applications SET usage_count = usage_count + 1 WHERE id = $1;
|
|
|
|
-- name: UpdateFavoriteCount :exec
|
|
UPDATE applications SET favorite_count = favorite_count + $2 WHERE id = $1;
|
|
|
|
-- name: UpdateAppRating :exec
|
|
UPDATE applications
|
|
SET avg_rating = $2, rating_count = $3
|
|
WHERE id = $1;
|
|
|
|
-- name: ListAllApps :many
|
|
SELECT a.*, c.name as category_name, u.name as creator_name
|
|
FROM applications a
|
|
LEFT JOIN categories c ON a.category_id = c.id
|
|
LEFT JOIN users u ON a.creator_id = u.id
|
|
WHERE (sqlc.narg('status')::VARCHAR IS NULL OR a.status = sqlc.narg('status'))
|
|
ORDER BY a.created_at DESC
|
|
LIMIT $1 OFFSET $2;
|
|
|
|
-- name: CountAllApps :one
|
|
SELECT COUNT(*) FROM applications
|
|
WHERE (sqlc.narg('status')::VARCHAR IS NULL OR status = sqlc.narg('status'));
|