Index: database/drizzle/queries/builds.ts =================================================================== --- database/drizzle/queries/builds.ts (revision b69759dc4c8f1a000afc0d0c5c5afcc6868641fb) +++ database/drizzle/queries/builds.ts (revision c4dd17daab63f38e850e1d550d0b89c90e9485cc) @@ -1,4 +1,11 @@ import type { Database } from "../db"; -import {buildComponentsTable, buildsTable, favoriteBuildsTable, ratingBuildsTable, reviewsTable} from "../schema"; +import { + buildComponentsTable, + buildsTable, + componentsTable, + favoriteBuildsTable, + ratingBuildsTable, + reviewsTable, usersTable +} from "../schema"; import {eq, desc, and, sql, ilike} from "drizzle-orm"; @@ -72,5 +79,5 @@ export async function getApprovedBuilds(db: Database, limit?: number, q?: string) { - let queryConditions = []; + let queryConditions = [eq(buildsTable.isApproved, true)]; if (q) { @@ -91,5 +98,4 @@ .where( and ( - eq(buildsTable.isApproved, true), ...queryConditions ) @@ -110,5 +116,6 @@ name: buildsTable.name, created_at: buildsTable.createdAt, - total_price: buildsTable.totalPrice + total_price: buildsTable.totalPrice, + avgRating: sql`AVG(${ratingBuildsTable.value}::float)` }) .from(buildsTable) @@ -120,7 +127,5 @@ eq(buildsTable.isApproved, true) ) - .groupBy( - buildsTable.id - ) + .groupBy(buildsTable.id) .orderBy( desc(sql`AVG(${ratingBuildsTable.value}::float)`) @@ -131,14 +136,132 @@ } -export async function getBuildDetails(db: Database, buildId: number) { - const buildDetails = await db - .select() - .from(buildsTable) - .where( - eq(buildsTable.id, buildId) - ) - .limit(1); - - return buildDetails[0] ?? null; +export async function getBuildDetails(db: Database, buildId: number, userId?: number) { + return db.transaction(async (tx) => { + const [buildDetails] = await tx + .select({ + id: buildsTable.id, + userId: buildsTable.userId, + name: buildsTable.name, + createdAt: buildsTable.createdAt, + description: buildsTable.description, + totalPrice: buildsTable.totalPrice, + isApproved: buildsTable.isApproved, + creator: usersTable.username + }) + .from(buildsTable) + .innerJoin( + usersTable, + eq(buildsTable.userId, usersTable.id) + ) + .where( + eq(buildsTable.id, buildId) + ) + .limit(1); + + if (!buildDetails) return null; + + const components = await tx + .select({ + componentId: buildComponentsTable.componentId, + component: componentsTable + }) + .from(buildComponentsTable) + .innerJoin( + componentsTable, + eq(buildComponentsTable.componentId, componentsTable.id) + ) + .where( + eq(buildComponentsTable.buildId, buildId) + ); + + const reviews = await tx + .select({ + username: usersTable.username, + content: reviewsTable.content, + createdAt: reviewsTable.createdAt + }) + .from(reviewsTable) + .innerJoin( + usersTable, + eq(reviewsTable.userId, usersTable.id) + ) + .where( + eq(reviewsTable.buildId, buildId) + ) + .orderBy( + desc(reviewsTable.createdAt) + ); + + let [ratingStatistics] = await tx + .select({ + averageRating: sql`COALESCE(AVG(${ratingBuildsTable.value}::float), 0)`.as("averageRating"), + ratingCount: sql`COUNT(${ratingBuildsTable.value})`.as("ratingCount") + }) + .from(ratingBuildsTable) + .where( + eq(ratingBuildsTable.buildId, buildId) + ) + .groupBy(ratingBuildsTable.buildId); + + ratingStatistics = { + averageRating: Number(ratingStatistics?.averageRating ?? 0), + ratingCount: Number(ratingStatistics?.ratingCount ?? 0), + } + + let userRating = null; + let isFavorite = false; + let userReview = null; + + if(userId) { + const [rating] = await tx + .select() + .from(ratingBuildsTable) + .where( + and( + eq(ratingBuildsTable.buildId, buildId), + eq(ratingBuildsTable.userId, userId) + ) + ) + .limit(1); + + userRating = rating?.value ? Number(rating.value) : null; + + const [favorite] = await tx + .select() + .from(favoriteBuildsTable) + .where( + and( + eq(favoriteBuildsTable.buildId, buildId), + eq(favoriteBuildsTable.userId, userId) + ) + ) + .limit(1); + + isFavorite = !!favorite; + + const [review] = await tx + .select() + .from(reviewsTable) + .where( + and( + eq(reviewsTable.buildId, buildId), + eq(reviewsTable.userId, userId) + ) + ) + .limit(1); + + userReview = review?.content; + } + + return { + ...buildDetails, + components: components.map(c => c.component), + reviews: reviews, + ratingStatistics: ratingStatistics, + userRating, + userReview, + isFavorite + }; + }); } @@ -184,10 +307,10 @@ userId, buildId, - value: value.toString() + value: value }) .onConflictDoUpdate({ target: [ratingBuildsTable.userId, ratingBuildsTable.buildId], set: { - value: value.toString(), + value: value, }, }); @@ -275,5 +398,2 @@ return newBuild.id; } - - - Index: database/drizzle/queries/components.ts =================================================================== --- database/drizzle/queries/components.ts (revision b69759dc4c8f1a000afc0d0c5c5afcc6868641fb) +++ database/drizzle/queries/components.ts (revision c4dd17daab63f38e850e1d550d0b89c90e9485cc) @@ -1,14 +1,53 @@ import type { Database } from "../db"; -import { componentsTable, suggestionsTable } from "../schema"; +import {buildsTable, componentsTable } from "../schema"; +import {and, desc, eq, ilike} from "drizzle-orm"; -export async function getComponentSuggestions(db: Database) { +export async function getAllComponents(db: Database, limit?: number, q?: string, componentType?: string) { + let queryConditions = []; + if (q) { + queryConditions.push( + ilike(componentsTable.name, `%${q}%`) + ); + } + + if(componentType && componentType.trim() !== 'all') { + queryConditions.push( + eq(componentsTable.type, componentType.trim().toLowerCase()) + ); + } + + const componentsList = await db + .select({ + id: componentsTable.id, + name: componentsTable.name, + brand: componentsTable.brand, + price: componentsTable.price, + }) + .from(componentsTable) + .where( + queryConditions.length > 0 ? + and ( + ...queryConditions + ) + : undefined + ) + .orderBy( + desc(componentsTable.price) + ) + .limit(limit || 100); // 100 placeholder + + return componentsList; } -export async function setComponentSuggestionStatus(db: Database, suggestionId: number, status: string, adminComment: string) { +export async function getComponentDetails(db: Database, componentId: number) { + const [componentDetails] = await db + .select() + .from(componentsTable) + .where( + eq(componentsTable.id, componentId) + ) + .limit(1); + return componentDetails ?? null; } - -export async function getAllComponents(db: Database, componentType?: string, q?: string, page?: number, pageSize?: number) { - -} Index: database/drizzle/queries/users.ts =================================================================== --- database/drizzle/queries/users.ts (revision b69759dc4c8f1a000afc0d0c5c5afcc6868641fb) +++ database/drizzle/queries/users.ts (revision c4dd17daab63f38e850e1d550d0b89c90e9485cc) @@ -1,5 +1,5 @@ import type { Database } from "../db"; -import {adminsTable, usersTable } from "../schema"; -import { eq } from "drizzle-orm"; +import {adminsTable, suggestionsTable, usersTable} from "../schema"; +import {desc, eq} from "drizzle-orm"; export async function createUser(db: Database, username: string, email: string, passwordHash: string) { @@ -14,5 +14,5 @@ export async function getUserProfile(db: Database, userId: number) { - const user = await db + const [user] = await db .select({ username: usersTable.username, @@ -25,5 +25,5 @@ .limit(1); - return user[0] ?? null; + return user ?? null; } @@ -39,2 +39,45 @@ return admin.length > 0; } + +export async function getComponentSuggestions(db: Database) { + const suggestionsList = await db + .select() + .from(suggestionsTable) + .where( + eq(suggestionsTable.status, 'pending') + ) + .orderBy( + desc(suggestionsTable.id) + ) + + return suggestionsList; +} + +export async function setComponentSuggestionStatus(db: Database, suggestionId: number, adminId: number, status: string, adminComment: string) { + const result = await db + .update(suggestionsTable) + .set({ + adminId: adminId, + status: status, + adminComment: adminComment + }) + .where( + eq(suggestionsTable.id, suggestionId) + ); + + return result.rowCount; +} + +export async function addComponentSuggestion(db: Database, userId: number, link: string, description: string, componentType: string) { + const [newSuggestion] = await db + .insert(suggestionsTable) + .values({ + userId: userId, + link: link, + description: description, + componentType: componentType + }) + .returning({ id: suggestionsTable.id }); + + return newSuggestion.id ?? null; +}