-- 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'));