Index: database/drizzle/queries/builds.ts =================================================================== --- database/drizzle/queries/builds.ts (revision ee1fa4e0ca436ce6cc48ebaf2a80522872cb79eb) +++ database/drizzle/queries/builds.ts (revision ea09e98c1561d7948b3e6612bff5f2269a697451) @@ -9,4 +9,5 @@ } from "../schema"; import {eq, desc, and, sql, ilike} from "drizzle-orm"; +import {inArray} from "drizzle-orm/sql/expressions/conditions"; export async function getPendingBuilds(db: Database) { @@ -51,8 +52,14 @@ }) .where( - eq(buildsTable.id, buildId) - ); - - return result.rowCount; + and( + eq(buildsTable.id, buildId), + eq(buildsTable.isApproved, !isApproved) + ) + ) + .returning({ + id: buildsTable.id + }) + + return result.length; } @@ -257,5 +264,9 @@ ...buildDetails, components: components.map(c => c.component), - reviews: reviews, + reviews: reviews.map(r => ({ + username: r.username, + content: r.content, + createdAt: r.createdAt + })), ratingStatistics: ratingStatistics, userRating, @@ -302,5 +313,5 @@ export async function setBuildRating(db: Database, userId: number, buildId: number, value: number) { - const result = await db + const [result] = await db .insert(ratingBuildsTable) .values({ @@ -314,36 +325,16 @@ value: value, }, - }); + }) + .returning({ + userId: ratingBuildsTable.userId, + buildId: ratingBuildsTable.buildId, + value: ratingBuildsTable.value + }) + + return result; } export async function setBuildReview(db: Database, userId: number, buildId: number, content: string) { - const existing = await db - .select() - .from(reviewsTable) - .where( - and( - eq(reviewsTable.userId, userId), - eq(reviewsTable.buildId, buildId) - ) - ) - .limit(1); - - if (existing.length) { - const result = await db - .update(reviewsTable) - .set({ - content: content, - }) - .where( - and( - eq(reviewsTable.userId, userId), - eq(reviewsTable.buildId, buildId) - ) - ); - - return; - } - - const result = await db + const [result] = await db .insert(reviewsTable) .values({ @@ -352,48 +343,232 @@ content, createdAt: new Date().toISOString().split('T')[0] - }); + }) + .onConflictDoUpdate({ + target: [reviewsTable.userId, reviewsTable.buildId], + set: { + content: content, + createdAt: new Date().toISOString().split('T')[0] + }, + }) + .returning({ + userId: reviewsTable.userId, + buildId: reviewsTable.buildId, + content: reviewsTable.content, + createdAt: reviewsTable.createdAt + }) + + return result; } export async function cloneBuild(db: Database, userId: number, buildId: number) { - const [buildToClone] = await db - .select() - .from(buildsTable) - .where( - eq(buildsTable.id, buildId) - ) - .limit(1); - - if (!buildToClone) return null; - - const [newBuild] = await db - .insert(buildsTable) - .values({ - userId: userId, - name: `${buildToClone.name} (copy)`, - createdAt: new Date().toISOString().split('T')[0], - description: buildToClone.description, - totalPrice: buildToClone.totalPrice, - isApproved: false - }) - .returning({ id: buildsTable.id }); - - const components = await db - .select() - .from(buildComponentsTable) - .where( - eq(buildComponentsTable.buildId, buildId) - ); - - if(components.length) { - await db - .insert(buildComponentsTable) - .values( - components.map(component => ({ - buildId: newBuild.id, - componentId: component.componentId - })) - ); - } - - return newBuild.id; -} + return db.transaction(async (tx) => { + const [buildToClone] = await tx + .select({ + id: buildsTable.id, + userId: buildsTable.userId, + name: buildsTable.name, + description: buildsTable.description, + totalPrice: buildsTable.totalPrice, + }) + .from(buildsTable) + .where( + eq(buildsTable.id, buildId) + ) + .limit(1); + + if (!buildToClone) return null; + + const [newBuild] = await tx + .insert(buildsTable) + .values({ + userId: userId, + name: `${buildToClone.name} (copy)`, + createdAt: new Date().toISOString().split('T')[0], + description: buildToClone.description, + totalPrice: buildToClone.totalPrice, + isApproved: false + }) + .returning({ + id: buildsTable.id + }); + + const components = await tx + .select() + .from(buildComponentsTable) + .where( + eq(buildComponentsTable.buildId, buildId) + ); + + if(components.length) { + await tx + .insert(buildComponentsTable) + .values( + components.map(component => ({ + buildId: newBuild.id, + componentId: component.componentId + })) + ); + } + + const [clonedBuild] = await tx + .select({ + id: buildsTable.id, + userId: buildsTable.userId, + name: buildsTable.name, + createdAt: buildsTable.createdAt, + description: buildsTable.description, + totalPrice: buildsTable.totalPrice + }) + .from(buildsTable) + .innerJoin( + usersTable, + eq(buildsTable.userId, usersTable.id) + ) + .where( + eq(buildsTable.id, newBuild.id) + ) + .limit(1); + + const clonedComponents = await tx + .select({ + componentId: buildComponentsTable.componentId, + component: componentsTable + }) + .from(buildComponentsTable) + .innerJoin( + componentsTable, + eq(buildComponentsTable.componentId, componentsTable.id) + ) + .where( + eq(buildComponentsTable.buildId, newBuild.id) + ); + + return { + ...clonedBuild, + components: clonedComponents.map(c => c.component) + }; + }); +} + +export async function deleteBuild(db: Database, userId: number, buildId: number) { + const result = await db + .delete(buildsTable) + .where( + and( + eq(buildsTable.id, buildId), + eq(buildsTable.userId, userId) + ) + ) + .returning({ + id: buildsTable.id + }) + + return result.length; +} + +export async function addNewBuild(db: Database, userId: number, name: string, description: string, componentIds: number[]) { + return db.transaction(async (tx) => { + const components = await tx + .select({ + price: componentsTable.price + }) + .from(componentsTable) + .where( + inArray(componentsTable.id, componentIds) + ); + + const totalPrice = components.reduce((sum, c) => sum + Number(c.price), 0); + + const [newBuild] = await tx + .insert(buildsTable) + .values({ + userId: userId, + name: name, + createdAt: new Date().toISOString().split('T')[0], + description: description, + totalPrice: totalPrice.toFixed(2), + isApproved: false + }) + .returning({ + id: buildsTable.id + }); + + if(components.length) { + await tx.insert(buildComponentsTable) + .values( + componentIds.map(componentId => ({ + buildId: newBuild.id, + componentId: componentId + })) + ); + } + + return newBuild.id; + }); +} + +export async function editBuild(db: Database, userId: number, buildId: number, name: string, description: string, componentIds: number[]) { + return db.transaction(async (tx) => { + const [build] = await tx + .select({ + id: buildsTable.id, + userId: buildsTable.userId, + isApproved: buildsTable.isApproved + }) + .from(buildsTable) + .where( + and( + eq(buildsTable.id, buildId), + eq(buildsTable.userId, userId) + ) + ) + .limit(1); + + if (!build) return null; + if (build.isApproved) return null; + + const components = await tx + .select({ + id: componentsTable.id, + price: componentsTable.price + }) + .from(componentsTable) + .where( + inArray(componentsTable.id, componentIds) + ); + + const totalPrice = components.reduce((sum, c) => sum + Number(c.price), 0); + + await tx + .update(buildsTable) + .set({ + name: name, + description: description, + totalPrice: totalPrice.toFixed(2), + }) + .where( + and( + eq(buildsTable.id, buildId), + eq(buildsTable.userId, userId) + ) + ); + + await tx + .delete(buildComponentsTable) + .where( + eq(buildComponentsTable.buildId, buildId) + ); + + if(components.length) { + await tx.insert(buildComponentsTable) + .values( + componentIds.map(componentId => ({ + buildId: buildId, + componentId: componentId + })) + ); + } + + return build.id; + }); +} Index: database/drizzle/queries/components.ts =================================================================== --- database/drizzle/queries/components.ts (revision ee1fa4e0ca436ce6cc48ebaf2a80522872cb79eb) +++ database/drizzle/queries/components.ts (revision ea09e98c1561d7948b3e6612bff5f2269a697451) @@ -1,4 +1,12 @@ import type { Database } from "../db"; -import {buildsTable, componentsTable } from "../schema"; +import { + buildsTable, cablesTable, caseMoboFormFactorsTable, casePsFormFactorsTable, caseStorageFormFactorsTable, + componentsTable, coolerCPUSocketsTable, coolersTable, + CPUTable, + GPUTable, memoryCardsTable, + memoryTable, motherboardsTable, + networkAdaptersTable, + networkCardsTable, opticalDrivesTable, pcCasesTable, powerSupplyTable, soundCardsTable, storageTable +} from "../schema"; import {and, desc, eq, ilike} from "drizzle-orm"; @@ -42,5 +50,5 @@ export async function getComponentDetails(db: Database, componentId: number) { - const [componentDetails] = await db + const [component] = await db .select() .from(componentsTable) @@ -50,4 +58,266 @@ .limit(1); - return componentDetails ?? null; + if(!component) return null; + + if (!component) return null; + + let details: any = {}; + + switch (component.type) { + case 'cpu': + [details] = await db.select().from(CPUTable).where(eq(CPUTable.componentId, componentId)); + break; + case 'gpu': + [details] = await db.select().from(GPUTable).where(eq(GPUTable.componentId, componentId)); + break; + case 'memory': + [details] = await db.select().from(memoryTable).where(eq(memoryTable.componentId, componentId)); + break; + case 'power_supply': + [details] = await db.select().from(powerSupplyTable).where(eq(powerSupplyTable.componentId, componentId)); + break; + case 'case': + [details] = await db.select().from(pcCasesTable).where(eq(pcCasesTable.componentId, componentId)); + if (details) { + details.storageFormFactors = await db.select().from(caseStorageFormFactorsTable).where(eq(caseStorageFormFactorsTable.caseId, componentId)); + details.psFormFactors = await db.select().from(casePsFormFactorsTable).where(eq(casePsFormFactorsTable.caseId, componentId)); + details.moboFormFactors = await db.select().from(caseMoboFormFactorsTable).where(eq(caseMoboFormFactorsTable.caseId, componentId)); + } + break; + case 'cooler': + [details] = await db.select().from(coolersTable).where(eq(coolersTable.componentId, componentId)); + if (details) { + details.cpuSockets = await db.select().from(coolerCPUSocketsTable).where(eq(coolerCPUSocketsTable.coolerId, componentId)); + } + break; + case 'motherboard': + [details] = await db.select().from(motherboardsTable).where(eq(motherboardsTable.componentId, componentId)); + break; + case 'storage': + [details] = await db.select().from(storageTable).where(eq(storageTable.componentId, componentId)); + break; + case 'memory_card': + [details] = await db.select().from(memoryCardsTable).where(eq(memoryCardsTable.componentId, componentId)); + break; + case 'optical_drive': + [details] = await db.select().from(opticalDrivesTable).where(eq(opticalDrivesTable.componentId, componentId)); + break; + case 'sound_card': + [details] = await db.select().from(soundCardsTable).where(eq(soundCardsTable.componentId, componentId)); + break; + case 'cables': + [details] = await db.select().from(cablesTable).where(eq(cablesTable.componentId, componentId)); + break; + case 'network_adapter': + [details] = await db.select().from(networkAdaptersTable).where(eq(networkAdaptersTable.componentId, componentId)); + break; + case 'network_card': + [details] = await db.select().from(networkCardsTable).where(eq(networkCardsTable.componentId, componentId)); + break; + } + + return { + ...component, + details: details || null + }; } + +export async function addNewComponent(db: Database, name: string, brand: string, price: number, imgUrl: string, type: string, specificData: any) { + return db.transaction(async (tx) => { + const [newComponent] = await tx + .insert(componentsTable) + .values({ + name: name, + brand: brand, + price: price.toFixed(2), + imgUrl: imgUrl, + type: type + }) + .returning({ + id: componentsTable.id + }); + + const componentId = newComponent.id; + + switch (type) { + case 'cpu': + await tx.insert(CPUTable).values({ + componentId, + socket: specificData.socket, + cores: specificData.cores, + threads: specificData.threads, + baseClock: specificData.baseClock, + boostClock: specificData.boostClock, + tdp: specificData.tdp, + }); + break; + + case 'gpu': + await tx.insert(GPUTable).values({ + componentId, + vram: specificData.vram, + tdp: specificData.tdp, + baseClock: specificData.baseClock, + boostClock: specificData.boostClock, + chipset: specificData.chipset, + length: specificData.length, + }); + break; + + case 'memory': + await tx.insert(memoryTable).values({ + componentId, + type: specificData.type, + speed: specificData.speed, + capacity: specificData.capacity, + modules: specificData.modules, + }); + break; + + case 'power_supply': + await tx.insert(powerSupplyTable).values({ + componentId, + type: specificData.type, + wattage: specificData.wattage, + formFactor: specificData.formFactor, + }); + break; + + case 'case': + await tx.insert(pcCasesTable).values({ + componentId, + coolerMaxHeight: specificData.coolerMaxHeight, + gpuMaxLength: specificData.gpuMaxLength, + }); + + if (specificData.storageFormFactors) { + await tx.insert(caseStorageFormFactorsTable).values( + specificData.storageFormFactors.map((sf: any) => ({ + caseId: componentId, + formFactor: sf.formFactor, + numSlots: sf.numSlots, + })) + ); + } + + if (specificData.psFormFactors) { + await tx.insert(casePsFormFactorsTable).values( + specificData.psFormFactors.map((pf: any) => ({ + caseId: componentId, + formFactor: pf.formFactor, + })) + ); + } + + if (specificData.moboFormFactors) { + await tx.insert(caseMoboFormFactorsTable).values( + specificData.moboFormFactors.map((mf: any) => ({ + caseId: componentId, + formFactor: mf.formFactor, + })) + ); + } + break; + + case 'cooler': + await tx.insert(coolersTable).values({ + componentId, + type: specificData.type, + height: specificData.height, + maxTdpSupported: specificData.maxTdpSupported, + }); + + if (specificData.cpuSockets) { + await tx.insert(coolerCPUSocketsTable).values( + specificData.cpuSockets.map((socket: string) => ({ + coolerId: componentId, + socket: socket, + })) + ); + } + break; + + case 'motherboard': + await tx.insert(motherboardsTable).values({ + componentId, + socket: specificData.socket, + chipset: specificData.chipset, + formFactor: specificData.formFactor, + ramType: specificData.ramType, + numRamSlots: specificData.numRamSlots, + maxRamCapacity: specificData.maxRamCapacity, + }); + break; + + case 'storage': + await tx.insert(storageTable).values({ + componentId, + type: specificData.type, + capacity: specificData.capacity, + formFactor: specificData.formFactor, + }); + break; + + case 'memory_card': + await tx.insert(memoryCardsTable).values({ + componentId, + numSlots: specificData.numSlots, + interface: specificData.interface, + }); + break; + + case 'optical_drive': + await tx.insert(opticalDrivesTable).values({ + componentId, + formFactor: specificData.formFactor, + type: specificData.type, + interface: specificData.interface, + writeSpeed: specificData.writeSpeed, + readSpeed: specificData.readSpeed, + }); + break; + + case 'sound_card': + await tx.insert(soundCardsTable).values({ + componentId, + sampleRate: specificData.sampleRate, + bitDepth: specificData.bitDepth, + chipset: specificData.chipset, + interface: specificData.interface, + channel: specificData.channel, + }); + break; + + case 'cables': + await tx.insert(cablesTable).values({ + componentId, + lengthCm: specificData.lengthCm, + type: specificData.type, + }); + break; + + case 'network_adapter': + await tx.insert(networkAdaptersTable).values({ + componentId, + wifiVersion: specificData.wifiVersion, + interface: specificData.interface, + numAntennas: specificData.numAntennas, + }); + break; + + case 'network_card': + await tx.insert(networkCardsTable).values({ + componentId, + numPorts: specificData.numPorts, + speed: specificData.speed, + interface: specificData.interface, + }); + break; + + default: + return null; + } + + return componentId; + }); +} Index: database/drizzle/queries/users.ts =================================================================== --- database/drizzle/queries/users.ts (revision ee1fa4e0ca436ce6cc48ebaf2a80522872cb79eb) +++ database/drizzle/queries/users.ts (revision ea09e98c1561d7948b3e6612bff5f2269a697451) @@ -69,5 +69,5 @@ } -export async function addComponentSuggestion(db: Database, userId: number, link: string, description: string, componentType: string) { +export async function addNewComponentSuggestion(db: Database, userId: number, link: string, description: string, componentType: string) { const [newSuggestion] = await db .insert(suggestionsTable) Index: database/drizzle/schema/builds.ts =================================================================== --- database/drizzle/schema/builds.ts (revision ee1fa4e0ca436ce6cc48ebaf2a80522872cb79eb) +++ database/drizzle/schema/builds.ts (revision ea09e98c1561d7948b3e6612bff5f2269a697451) @@ -1,3 +1,3 @@ -import {pgTable, serial, integer, text, numeric, boolean, date, primaryKey, check} from "drizzle-orm/pg-core"; +import {pgTable, serial, integer, text, numeric, boolean, date, primaryKey, check, unique} from "drizzle-orm/pg-core"; import { usersTable } from "./users"; import { componentsTable } from "./components"; @@ -58,15 +58,19 @@ export const reviewsTable = pgTable("review", { - id: serial("id").primaryKey(), + id: serial("id").primaryKey(), - buildId: integer("build_id") - .notNull() - .references(() => buildsTable.id, { onDelete: "cascade", onUpdate: "cascade" }), - userId: integer("user_id") - .notNull() - .references(() => usersTable.id, { onDelete: "cascade", onUpdate: "cascade" }), - content: text("content").notNull(), - createdAt: date("created_at").notNull(), -}); + buildId: integer("build_id") + .notNull() + .references(() => buildsTable.id, { onDelete: "cascade", onUpdate: "cascade" }), + userId: integer("user_id") + .notNull() + .references(() => usersTable.id, { onDelete: "cascade", onUpdate: "cascade" }), + content: text("content").notNull(), + createdAt: date("created_at").notNull() + }, + (t) => ({ + uniqueUserBuild: unique().on(t.buildId, t.userId), + }), +); export type buildItem = typeof buildsTable.$inferSelect;