import { db } from '@/lib/db'; import { ratings, scripts } from '@/lib/db/schema'; import { eq, and, avg, count } from 'drizzle-orm'; import { generateId, ApiError } from './index'; export interface CreateRatingData { scriptId: string; userId: string; rating: number; // 1-5 stars } // Create or update a rating export async function rateScript(data: CreateRatingData) { try { if (data.rating < 1 || data.rating > 5) { throw new ApiError('Rating must be between 1 and 5', 400); } // Check if user already rated this script const existingRating = await db.query.ratings.findFirst({ where: and( eq(ratings.scriptId, data.scriptId), eq(ratings.userId, data.userId) ), }); let ratingRecord; if (existingRating) { // Update existing rating await db .update(ratings) .set({ rating: data.rating, updatedAt: new Date(), }) .where(eq(ratings.id, existingRating.id)); ratingRecord = { ...existingRating, rating: data.rating, updatedAt: new Date(), }; } else { // Create new rating ratingRecord = { id: generateId(), scriptId: data.scriptId, userId: data.userId, rating: data.rating, createdAt: new Date(), updatedAt: new Date(), }; await db.insert(ratings).values(ratingRecord); } // Update script's average rating and count await updateScriptRating(data.scriptId); return ratingRecord; } catch (error) { if (error instanceof ApiError) throw error; throw new ApiError(`Failed to rate script: ${error}`, 500); } } // Get user's rating for a script export async function getUserRating(scriptId: string, userId: string) { try { const userRating = await db.query.ratings.findFirst({ where: and( eq(ratings.scriptId, scriptId), eq(ratings.userId, userId) ), }); return userRating; } catch (error) { throw new ApiError(`Failed to get user rating: ${error}`, 500); } } // Get all ratings for a script export async function getScriptRatings(scriptId: string) { try { const scriptRatings = await db.query.ratings.findMany({ where: eq(ratings.scriptId, scriptId), with: { user: { columns: { id: true, username: true, displayName: true, avatarUrl: true, }, }, }, }); return scriptRatings; } catch (error) { throw new ApiError(`Failed to get script ratings: ${error}`, 500); } } // Update script's average rating and count async function updateScriptRating(scriptId: string) { try { const [stats] = await db .select({ avgRating: avg(ratings.rating), ratingCount: count(ratings.id), }) .from(ratings) .where(eq(ratings.scriptId, scriptId)); const avgRating = stats.avgRating ? Math.round(Number(stats.avgRating) * 10) / 10 : 0; const ratingCount = stats.ratingCount || 0; await db .update(scripts) .set({ rating: avgRating, ratingCount: ratingCount, }) .where(eq(scripts.id, scriptId)); return { avgRating, ratingCount }; } catch (error) { throw new ApiError(`Failed to update script rating: ${error}`, 500); } } // Delete a rating export async function deleteRating(scriptId: string, userId: string) { try { await db .delete(ratings) .where( and( eq(ratings.scriptId, scriptId), eq(ratings.userId, userId) ) ); // Update script's average rating and count await updateScriptRating(scriptId); return { success: true }; } catch (error) { throw new ApiError(`Failed to delete rating: ${error}`, 500); } } // Get rating statistics for a script export async function getScriptRatingStats(scriptId: string) { try { const stats = await db .select({ rating: ratings.rating, count: count(ratings.id), }) .from(ratings) .where(eq(ratings.scriptId, scriptId)) .groupBy(ratings.rating); const distribution = [1, 2, 3, 4, 5].map(star => { const found = stats.find(s => s.rating === star); return { stars: star, count: found ? found.count : 0, }; }); const [totals] = await db .select({ avgRating: avg(ratings.rating), totalRatings: count(ratings.id), }) .from(ratings) .where(eq(ratings.scriptId, scriptId)); return { averageRating: totals.avgRating ? Math.round(Number(totals.avgRating) * 10) / 10 : 0, totalRatings: totals.totalRatings || 0, distribution, }; } catch (error) { throw new ApiError(`Failed to get rating stats: ${error}`, 500); } }