-- name: AddFavorite :exec
INSERT INTO app_favorites (user_id, app_id) VALUES ($1, $2)
ON CONFLICT DO NOTHING;

-- name: RemoveFavorite :exec
DELETE FROM app_favorites WHERE user_id = $1 AND app_id = $2;

-- name: IsFavorited :one
SELECT EXISTS(SELECT 1 FROM app_favorites WHERE user_id = $1 AND app_id = $2);

-- name: ListUserFavorites :many
SELECT a.*, c.name as category_name
FROM app_favorites f
JOIN applications a ON f.app_id = a.id
LEFT JOIN categories c ON a.category_id = c.id
WHERE f.user_id = $1
ORDER BY f.created_at DESC
LIMIT $2 OFFSET $3;

-- name: UpsertRating :one
INSERT INTO app_ratings (app_id, user_id, score, comment)
VALUES ($1, $2, $3, $4)
ON CONFLICT (app_id, user_id)
DO UPDATE SET score = EXCLUDED.score, comment = EXCLUDED.comment
RETURNING *;

-- name: GetAppAvgRating :one
SELECT COALESCE(AVG(score)::REAL, 0) as avg_rating, COUNT(*) as rating_count
FROM app_ratings WHERE app_id = $1;

-- name: ListAppRatings :many
SELECT r.*, u.name as user_name, u.avatar_url as user_avatar
FROM app_ratings r
JOIN users u ON r.user_id = u.id
WHERE r.app_id = $1
ORDER BY r.created_at DESC
LIMIT $2 OFFSET $3;