Index: database/drizzle/queries/builds.ts =================================================================== --- database/drizzle/queries/builds.ts (revision ff3a6146e85f77e90dc322a0ad0c9d9eb048c629) +++ (revision ) @@ -1,571 +1,0 @@ -import type { Database } from "../db"; -import { - buildComponentsTable, - buildsTable, - componentsTable, - favoriteBuildsTable, - ratingBuildsTable, - reviewsTable, usersTable -} from "../schema"; -import {eq, desc, and, sql, ilike, asc} from "drizzle-orm"; -import {inArray} from "drizzle-orm/sql/expressions/conditions"; -import {addComponentToBuild} from "./components"; - -export async function getPendingBuilds(db: Database) { - const pendingBuilds = await db - .select({ - id: buildsTable.id, - user_id: buildsTable.userId, - name: buildsTable.name, - created_at: buildsTable.createdAt, - total_price: buildsTable.totalPrice - }) - .from(buildsTable) - .where( - eq(buildsTable.isApproved, false) - ) - .orderBy( - desc(buildsTable.createdAt) - ); - - return pendingBuilds; -} - -export async function getUserBuilds(db: Database, userId: number) { - const userBuilds = await db - .select({ - id: buildsTable.id, - user_id: buildsTable.userId, - name: buildsTable.name, - created_at: buildsTable.createdAt, - total_price: buildsTable.totalPrice - }) - .from(buildsTable) - .where( - eq(buildsTable.userId, userId) - ) - .orderBy( - desc(buildsTable.createdAt) - ); - - return userBuilds; -} - -export async function setBuildApprovalStatus(db: Database, buildId: number, isApproved: boolean){ - const [result] = await db - .update(buildsTable) - .set({ - isApproved: isApproved - }) - .where( - and( - eq(buildsTable.id, buildId), - eq(buildsTable.isApproved, !isApproved) - ) - ) - .returning({ - id: buildsTable.id - }) - - return result?.id ?? null; -} - -export async function getFavoriteBuilds(db: Database, userId: number) { - const favoriteBuilds = await db - .select({ - id: buildsTable.id, - user_id: buildsTable.userId, - name: buildsTable.name, - created_at: buildsTable.createdAt, - total_price: buildsTable.totalPrice, - avgRating: sql`COALESCE(AVG(${ratingBuildsTable.value}::float),0)` - }) - .from(buildsTable) - .innerJoin( - favoriteBuildsTable, - eq(buildsTable.id, favoriteBuildsTable.buildId) - ) - .leftJoin( - ratingBuildsTable, - eq(buildsTable.id, ratingBuildsTable.buildId) - ) - .where( - eq(favoriteBuildsTable.userId, userId) - ) - .groupBy( - buildsTable.id, - buildsTable.userId, - buildsTable.name, - buildsTable.createdAt, - buildsTable.totalPrice - ) - .orderBy( - desc(sql`COALESCE(AVG(${ratingBuildsTable.value}::float),0)`) - ); - - return favoriteBuilds; -} - -export async function getApprovedBuilds(db: Database, limit?: number, sort?: string, q?: string) { - let queryConditions = [eq(buildsTable.isApproved, true)]; - let sortCondition:any; - - if (q) { - queryConditions.push( - ilike(buildsTable.name, `%${q}%`) - ); - } - - switch(sort) { - case 'price_asc': - sortCondition = asc(buildsTable.totalPrice) - break; - case 'price_desc': - sortCondition = desc(buildsTable.totalPrice) - break; - case 'rating_desc': - sortCondition = desc(sql`COALESCE(AVG(${ratingBuildsTable.value}::float),0)`) - break; - case 'oldest': - sortCondition = asc(buildsTable.createdAt) - break; - case 'newest': - sortCondition = desc(buildsTable.createdAt) - break; - default: - sortCondition = desc(buildsTable.createdAt) - break; - } - - const approvedBuilds = await db - .select({ - id: buildsTable.id, - user_id: buildsTable.userId, - name: buildsTable.name, - created_at: buildsTable.createdAt, - total_price: buildsTable.totalPrice, - avgRating: sql`COALESCE(AVG(${ratingBuildsTable.value}::float),0)` - }) - .from(buildsTable) - .leftJoin( - ratingBuildsTable, - eq(buildsTable.id, ratingBuildsTable.buildId) - ) - .groupBy( - buildsTable.id, - buildsTable.userId, - buildsTable.name, - buildsTable.createdAt, - buildsTable.totalPrice - ) - .where( - and ( - ...queryConditions - ) - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); // 100 placeholder - - return approvedBuilds; -} - -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, - quantity: buildComponentsTable.numComponents - }) - .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, - quantity: c.quantity - })), - reviews: reviews.map(r => ({ - username: r.username, - content: r.content, - createdAt: r.createdAt - })), - ratingStatistics: ratingStatistics, - userRating, - userReview, - isFavorite - }; - }); -} - -export async function toggleFavoriteBuild(db: Database, userId: number, buildId: number) { - const existing = await db - .select() - .from(favoriteBuildsTable) - .where( - and( - eq(favoriteBuildsTable.userId, userId), - eq(favoriteBuildsTable.buildId, buildId) - ) - ) - .limit(1); - - if (existing.length > 0) { - await db - .delete(favoriteBuildsTable) - .where( - and( - eq(favoriteBuildsTable.userId, userId), - eq(favoriteBuildsTable.buildId, buildId) - ) - ); - - return null; - } - - await db - .insert(favoriteBuildsTable) - .values({ - userId, - buildId, - }); - - return true; -} - -export async function setBuildRating(db: Database, userId: number, buildId: number, value: number) { - const [result] = await db - .insert(ratingBuildsTable) - .values({ - userId, - buildId, - value: value - }) - .onConflictDoUpdate({ - target: [ratingBuildsTable.userId, ratingBuildsTable.buildId], - set: { - value: value, - }, - }) - .returning({ - userId: ratingBuildsTable.userId, - buildId: ratingBuildsTable.buildId, - value: ratingBuildsTable.value - }) - - return result ?? null; -} - -export async function setBuildReview(db: Database, userId: number, buildId: number, content: string) { - const [result] = await db - .insert(reviewsTable) - .values({ - userId, - buildId, - 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 ?? null; -} - -export async function cloneBuild(db: Database, userId: number, buildId: number) { - return db.transaction(async (tx) => { - const [buildToClone] = await tx - .select() - .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 - }); - - if(!newBuild) return null; - - const existing = await tx - .select({ - componentId: buildComponentsTable.componentId, - numComponents: buildComponentsTable.numComponents - }) - .from(buildComponentsTable) - .where(eq(buildComponentsTable.buildId, buildId)); - - if (existing.length > 0) { - await tx.insert(buildComponentsTable).values( - existing.map((r) => ({ - buildId: newBuild.id, - componentId: r.componentId, - numComponents: r.numComponents - })), - ); - } - - return newBuild.id; - }); -} - -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?.id ?? null; -} - -export async function addNewBuild(db: Database, userId: number, name: string, description: string) { - const [newBuild] = await db - .insert(buildsTable) - .values({ - userId: userId, - name: name, - createdAt: new Date().toISOString().split('T')[0], - description: description, - totalPrice: Number(0).toFixed(2), - isApproved: false - }) - .returning({ - id: buildsTable.id - }); - - return newBuild?.id ?? null; -} - -export async function editBuild(db: Database, userId: number, buildId: number) { - const [buildToEdit] = await db - .select() - .from(buildsTable) - .where( - and( - eq(buildsTable.id, buildId), - eq(buildsTable.userId, userId) - ) - ) - .limit(1); - - if (!buildToEdit || buildToEdit.isApproved) return null; - - - return buildToEdit?.id ?? null; -} - -export async function saveBuildState(db: Database, userId: number, buildId: number, name: string, description: string ) { - const [build] = await db - .select({ - id: buildsTable.id, - isApproved: buildsTable.isApproved - }) - .from(buildsTable) - .where( - and( - eq(buildsTable.id, buildId), - eq(buildsTable.userId, userId) - ) - ) - .limit(1); - - if (!build || build.isApproved) return null; - - const [updated] = await db - .update(buildsTable) - .set({ - name, - description - }) - .where( - eq(buildsTable.id, buildId) - ) - .returning({ - id: buildsTable.id - }); - - return updated?.id ?? null; -} - -export async function getBuildState(db: Database, userId: number, buildId: number) { - return db.transaction(async (tx) => { - const [build] = await tx - .select({ - id: buildsTable.id, - userId: buildsTable.userId, - isApproved: buildsTable.isApproved, - name: buildsTable.name, - description: buildsTable.description, - totalPrice: buildsTable.totalPrice, - }) - .from(buildsTable) - .where( - and( - eq(buildsTable.id, buildId), - eq(buildsTable.userId, userId) - ) - ) - .limit(1); - - if (!build || build.isApproved) return null; - - const components = await tx - .select({ - componentId: buildComponentsTable.componentId, - quantity: buildComponentsTable.numComponents - }) - .from(buildComponentsTable) - .where( - eq(buildComponentsTable.buildId, buildId) - ); - - return { - build, - components: components.map(c => ({ - id: c.componentId, - quantity: c.quantity - })) - }; - }); -} Index: database/drizzle/queries/components.ts =================================================================== --- database/drizzle/queries/components.ts (revision ff3a6146e85f77e90dc322a0ad0c9d9eb048c629) +++ (revision ) @@ -1,1168 +1,0 @@ -import type { Database } from "../db"; -import { - buildComponentsTable, - buildsTable, cablesTable, caseMoboFormFactorsTable, casePsFormFactorsTable, caseStorageFormFactorsTable, - componentsTable, coolerCPUSocketsTable, coolersTable, - CPUTable, - GPUTable, memoryCardsTable, - memoryTable, motherboardsTable, - networkAdaptersTable, - networkCardsTable, opticalDrivesTable, pcCasesTable, powerSupplyTable, - ratingBuildsTable, soundCardsTable, storageTable -} from "../schema"; -import {and, asc, desc, eq, ilike, SQL, sql} from "drizzle-orm"; -import {typeConfigMap, ComponentType, requiredFields} from "../util/componentFieldConfig"; -import {AnyPgColumn} from "drizzle-orm/pg-core"; -import {inArray} from "drizzle-orm/sql/expressions/conditions"; -export async function getAllComponents(db: Database, limit?: number, componentType?: string, sort?: string, q?: string) { - let queryConditions = []; - let sortConditions = []; - - if (q) { - queryConditions.push( - ilike(componentsTable.name, `%${q}%`) - ); - } - - switch(sort) { - case 'price_asc': - sortConditions.push( - asc(componentsTable.price) - ); - break; - case 'price_desc': - sortConditions.push( - desc(componentsTable.price) - ); - break; - default: - sortConditions.push( - desc(componentsTable.price) - ); - break; - } - - if(componentType && componentType.trim() !== 'all') { - queryConditions.push( - eq(componentsTable.type, componentType.trim().toLowerCase()) - ); - } - - const components = await db - .select() - .from(componentsTable) - .where( - queryConditions.length > 0 ? - and ( - ...queryConditions - ) - : undefined - ) - .orderBy( - ...sortConditions - ) - .limit(limit || 100); // 100 placeholder - - return components; -} - -export async function getComponentDetails(db: Database, componentId: number) { - const [component] = await db - .select() - .from(componentsTable) - .where( - eq(componentsTable.id, componentId) - ) - .limit(1); - - if(!component) return null; - - const config = typeConfigMap[component.type as ComponentType]; - - const [details] = await db - .select() - .from(config.table) - .where( - eq(config.table.componentId, componentId) - ) - .limit(1); - - if (component.type === 'case') { - 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)); - } - - if (component.type === 'cooler') { - details.cpuSockets = await db.select().from(coolerCPUSocketsTable).where(eq(coolerCPUSocketsTable.coolerId, componentId)); - } - - return { - ...component, - details: details - }; -} - -export async function getDetailsNewComponent(componentType: string) { - const config = typeConfigMap[componentType as ComponentType]; - - return { - type: componentType as ComponentType, - requiredFields: requiredFields[componentType as ComponentType] ?? [], - multiTables: config.multiTables ?? {}, - }; -} - -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; - - const config = typeConfigMap[type as ComponentType]; - - await tx - .insert(config.table) - .values({ - componentId: componentId, - ...specificData - }); - - if (type === 'case') { - 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, - })) - ); - } - } - - if (type === 'cooler' && specificData.cpuSockets) { - await tx.insert(coolerCPUSocketsTable).values( - specificData.cpuSockets.map((socket: any) => ({ coolerId: componentId, socket })) - ); - } - - return componentId; - }); -} - -export async function getBuildComponents(db: Database, buildId: number) { - const [build] = await db - .select({ - buildId: buildsTable.id, - userId: buildsTable.userId, - }) - .from(buildsTable) - .where( - eq(buildsTable.id, buildId) - ) - .limit(1); - - if(!build) return null; - - const components = await db - .select({ - id: componentsTable.id, - type: componentsTable.type, - quantity: buildComponentsTable.numComponents - }) - .from(buildComponentsTable) - .where( - eq(buildComponentsTable.buildId, buildId) - ) - .innerJoin( - componentsTable, - eq(buildComponentsTable.componentId, componentsTable.id) - ); - - const componentsDetails = []; - - for (const comp of components) { - const config = typeConfigMap[comp.type as ComponentType]; - - const [details] = await db - .select() - .from(config.table) - .where( - eq(config.table.componentId, comp.id) - ) - .limit(1); - - if (comp.type === 'case') { - details.storageFormFactors = await db.select().from(caseStorageFormFactorsTable).where(eq(caseStorageFormFactorsTable.caseId, comp.id)); - details.psFormFactors = await db.select().from(casePsFormFactorsTable).where(eq(casePsFormFactorsTable.caseId, comp.id)); - details.moboFormFactors = await db.select().from(caseMoboFormFactorsTable).where(eq(caseMoboFormFactorsTable.caseId, comp.id)); - } - - if (comp.type === 'cooler') { - details.cpuSockets = await db.select().from(coolerCPUSocketsTable).where(eq(coolerCPUSocketsTable.coolerId, comp.id)); - } - - componentsDetails.push({ - ...comp, - quantity: comp.quantity, - details: details - }); - } - - return componentsDetails; -} - -export async function getCompatibleComponents(db: Database, buildId: number, componentType: string, limit?: number, sort?: string) { - let sortCondition: any; - - switch(sort) { - case 'price_asc': - sortCondition = asc(componentsTable.price); - break; - case 'price_desc': - sortCondition = desc(componentsTable.price); - break; - default: - sortCondition = desc(componentsTable.price); - break; - } - - const existingComponents = await getBuildComponents(db, buildId); - - if(!existingComponents) return null; - - const existing = { - cpu: existingComponents.find(c => c.type === 'cpu'), - motherboard: existingComponents.find(c => c.type === 'motherboard'), - case: existingComponents.find(c => c.type === 'case'), - cooler: existingComponents.find(c => c.type === 'cooler'), - psu: existingComponents.find(c => c.type === 'power_supply'), - gpu: existingComponents.find(c => c.type === 'gpu'), - memory: existingComponents.filter(c => c.type === 'memory'), - storage: existingComponents.filter(c => c.type === 'storage'), - networkCards: existingComponents.filter(c => c.type === 'network_card'), - networkAdapters: existingComponents.filter(c => c.type === 'network_adapter'), - soundCards: existingComponents.filter(c => c.type === 'sound_card'), - memoryCards: existingComponents.filter(c => c.type === 'memory_card'), - opticalDrives: existingComponents.filter(c => c.type === 'optical_drive'), - cables: existingComponents.filter(c => c.type === 'cables') - }; - - const pciExpressSlotsUsed = [ - existing.gpu, - ...existing.networkCards, - ...existing.networkAdapters, - ...existing.soundCards, - ...existing.memoryCards - ].reduce((sum: number, c: any) => { - if (!c) return sum; - return sum + (c.quantity || 1); - }, 0); - - const existingTDP = existingComponents.reduce((sum, c) => { - const tdp = c.details?.tdp ? Number(c.details.tdp) : 0; - return sum + (tdp * (c.quantity || 1)); - }, 0); - - - let compatibleComponents: any[] = []; - - switch (componentType) { - case 'cpu': - compatibleComponents = await getCompatibleCPUs(db, existing, existingTDP, limit, sortCondition); - break; - case 'motherboard': - compatibleComponents = await getCompatibleMotherboards(db, existing, pciExpressSlotsUsed, limit, sortCondition); - break; - case 'cooler': - compatibleComponents = await getCompatibleCoolers(db, existing, limit, sortCondition); - break; - case 'gpu': - compatibleComponents = await getCompatibleGPUs(db, existing, existingTDP, pciExpressSlotsUsed, limit, sortCondition); - break; - case 'memory': - compatibleComponents = await getCompatibleMemory(db, existing, limit, sortCondition); - break; - case 'storage': - compatibleComponents = await getCompatibleStorage(db, existing, limit, sortCondition); - break; - case 'case': - compatibleComponents = await getCompatibleCases(db, existing, limit, sortCondition); - break; - case 'power_supply': - compatibleComponents = await getCompatiblePSUs(db, existing, existingTDP, limit, sortCondition); - break; - case 'network_card': - case 'network_adapter': - case 'sound_card': - case 'memory_card': - compatibleComponents = await getCompatiblePCIeComponents(db, existing, componentType, pciExpressSlotsUsed, limit, sortCondition); - break; - case 'optical_drive': - compatibleComponents = await getCompatibleOpticalDrives(db, existing, limit, sortCondition); - break; - case 'cables': - compatibleComponents = await getCompatibleCables(db, limit, sortCondition); - break; - default: - compatibleComponents = []; - } - - if(compatibleComponents.length === 0) return null; - - return compatibleComponents; -} - - -// Helper functions for checking component-specific compatibility -async function getCompatibleCPUs(db: Database, existing: any, existingTDP?: number, limit?: number, sortCondition?: any) { - const conditions = [eq(componentsTable.type, 'cpu')]; - - if (existing.motherboard) { - conditions.push( - eq(CPUTable.socket, existing.motherboard.details.socket) - ); - } - - if (existing.cooler) { - conditions.push( - sql`${CPUTable.tdp} <= ${existing.cooler.details.maxTdpSupported}` - ); - } - - const cpus = await db - .select({ - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - socket: CPUTable.socket, - cores: CPUTable.cores, - threads: CPUTable.threads, - baseClock: CPUTable.baseClock, - boostClock: CPUTable.boostClock, - tdp: CPUTable.tdp, - }) - .from(componentsTable) - .innerJoin( - CPUTable, - eq(componentsTable.id, CPUTable.componentId) - ) - .where( - and( - ...conditions - ) - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); - - let filteredCPUs = cpus; - - if (existing.cooler?.details?.cpuSockets) { - const coolerSockets = existing.cooler.details.cpuSockets.map((s: any) => s.socket); - filteredCPUs = filteredCPUs.filter(cpu => coolerSockets.includes(cpu.socket)); - } - - if(existing.psu?.details?.wattage && existingTDP) { - const psuWattage = Number(existing.psu.details.wattage) || 0; - - if (psuWattage > 0) { - const budget = psuWattage / 1.2; - - const currentCpuTdp = Number(existing.cpu?.details?.tdp) || 0; - const baseWithoutCpu = existingTDP - currentCpuTdp; - - filteredCPUs = filteredCPUs.filter(cpu => { - const cpuTdp = Number(cpu.tdp) || 0; - return (baseWithoutCpu + cpuTdp) <= budget; - }); - } - } - - return filteredCPUs; -} - -async function getCompatibleMotherboards(db: Database, existing: any, pciExpressSlotsUsed: number, limit?: number, sortCondition?: any) { - const conditions = [eq(componentsTable.type, 'motherboard')]; - - if (existing.cpu) { - conditions.push( - eq(motherboardsTable.socket, existing.cpu.details.socket) - ); - } - - if (existing.memory.length > 0) { - const ramType = existing.memory[0].details.type; - conditions.push( - eq(motherboardsTable.ramType, ramType) - ); - } - - if (pciExpressSlotsUsed > 0) { - conditions.push( - sql`${motherboardsTable.pciExpressSlots} >= ${pciExpressSlotsUsed}` - ); - } - - const motherboards = await db - .select({ - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - socket: motherboardsTable.socket, - chipset: motherboardsTable.chipset, - formFactor: motherboardsTable.formFactor, - ramType: motherboardsTable.ramType, - numRamSlots: motherboardsTable.numRamSlots, - maxRamCapacity: motherboardsTable.maxRamCapacity, - pciExpressSlots: motherboardsTable.pciExpressSlots, - }) - .from(componentsTable) - .innerJoin( - motherboardsTable, - eq(componentsTable.id, motherboardsTable.componentId) - ) - .where( - and( - ...conditions - ) - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); - - const compatibleMotherboards = motherboards.filter(mobo => { - if (existing.memory.length > 0) { - const totalModules = existing.memory.reduce((sum: number, m: any) => - sum + (Number(m.details.modules) * m.quantity), 0 - ); - const totalCapacity = existing.memory.reduce((sum: number, m: any) => - sum + (Number(m.details.capacity) * m.quantity), 0 - ); - - if (totalModules > Number(mobo.numRamSlots)) return false; - if (totalCapacity > Number(mobo.maxRamCapacity)) return false; - } - - return true; - }); - - if (existing.case?.details?.moboFormFactors) { - const caseFormFactors = existing.case.details.moboFormFactors.map((f: any) => f.formFactor); - return compatibleMotherboards.filter(mobo => caseFormFactors.includes(mobo.formFactor)); - } - - return compatibleMotherboards; -} - -async function getCompatibleCoolers(db: Database, existing: any, limit?: number, sortCondition?: any) { - const conditions = [eq(componentsTable.type, 'cooler')]; - - if (existing.cpu) { - conditions.push( - sql`${coolersTable.maxTdpSupported} >= ${existing.cpu.details.tdp}` - ); - } - - if (existing.case) { - conditions.push( - sql`${coolersTable.height} <= ${existing.case.details.coolerMaxHeight}` - ); - } - - const coolers = await db - .select({ - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - coolerType: coolersTable.type, - height: coolersTable.height, - maxTdpSupported: coolersTable.maxTdpSupported, - }) - .from(componentsTable) - .innerJoin( - coolersTable, - eq(componentsTable.id, coolersTable.componentId) - ) - .where( - and( - ...conditions - ) - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); - - - if(existing.cpu && coolers.length > 0) { - const coolerIds = coolers.map(c => c.id); - - const allSockets = await db - .select({ - coolerId: coolerCPUSocketsTable.coolerId, - socket: coolerCPUSocketsTable.socket - }) - .from(coolerCPUSocketsTable) - .where( - inArray(coolerCPUSocketsTable.coolerId, coolerIds) - ); - const socketsByCooler = allSockets.reduce((acc, { coolerId, socket }) => { - if (!acc[coolerId]) acc[coolerId] = []; - acc[coolerId].push(socket); - return acc; - }, {} as Record); - - return coolers.filter(cooler => { - const sockets = socketsByCooler[cooler.id] || []; - return sockets.includes(existing.cpu.details.socket); - }); - } - - return coolers; -} - -async function getCompatibleGPUs(db: Database, existing: any, existingTDP: number, pciExpressSlotsUsed: number, limit?: number, sortCondition?: any) { - const conditions = [eq(componentsTable.type, 'gpu')]; - - if (existing.case) { - conditions.push( - sql`${GPUTable.length} <= ${existing.case.details.gpuMaxLength}` - ); - } - - const gpus = await db - .select({ - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - vram: GPUTable.vram, - tdp: GPUTable.tdp, - baseClock: GPUTable.baseClock, - boostClock: GPUTable.boostClock, - chipset: GPUTable.chipset, - length: GPUTable.length, - }) - .from(componentsTable) - .innerJoin( - GPUTable, - eq(componentsTable.id, GPUTable.componentId) - ) - .where( - and( - ...conditions - ) - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); - - let filteredGPUs = gpus; - - if (existing.motherboard) { - const availableSlots = Number(existing.motherboard.details.pciExpressSlots); - filteredGPUs = gpus.filter(() => (pciExpressSlotsUsed + 1) <= availableSlots); - } - - if(existing.psu?.details?.wattage && existingTDP) { - const psuWattage = Number(existing.psu.details.wattage) || 0; - - if (psuWattage > 0) { - const budget = psuWattage / 1.2; - - const currentGpuTdp = Number(existing.cpu?.details?.tdp) || 0; - const baseWithoutGpu = existingTDP - currentGpuTdp; - - filteredGPUs = filteredGPUs.filter(cpu => { - const gpuTdp = Number(cpu.tdp) || 0; - return (baseWithoutGpu + gpuTdp) <= budget; - }); - } - } - - return filteredGPUs; -} - -async function getCompatibleMemory(db: Database, existing: any, limit?: number, sortCondition?: any) { - const conditions = [eq(componentsTable.type, 'memory')]; - - if (existing.motherboard) { - conditions.push( - eq(memoryTable.type, existing.motherboard.details.ramType) - ); - } - - const memory = await db - .select({ - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - memoryType: memoryTable.type, - speed: memoryTable.speed, - capacity: memoryTable.capacity, - modules: memoryTable.modules, - }) - .from(componentsTable) - .innerJoin( - memoryTable, - eq(componentsTable.id, memoryTable.componentId) - ) - .where( - and( - ...conditions - ) - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); - - if (existing.motherboard) { - const existingModules = existing.memory.reduce((sum: number, m: any) => - sum + Number(m.details.modules), 0 - ); - const existingCapacity = existing.memory.reduce((sum: number, m: any) => - sum + Number(m.details.capacity), 0 - ); - const maxSlots = Number(existing.motherboard.details.numRamSlots); - const maxCapacity = Number(existing.motherboard.details.maxRamCapacity); - - return memory.filter(mem => { - const newModules = existingModules + Number(mem.modules); - const newCapacity = existingCapacity + Number(mem.capacity); - return newModules <= maxSlots && newCapacity <= maxCapacity; - }); - } - - return memory; -} - -async function getCompatibleStorage(db: Database, existing: any, limit?: number, sortCondition?: any) { - const storage = await db - .select({ - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - storageType: storageTable.type, - capacity: storageTable.capacity, - formFactor: storageTable.formFactor, - }) - .from(componentsTable) - .innerJoin( - storageTable, - eq(componentsTable.id, storageTable.componentId - ) - ) - .where( - eq(componentsTable.type, 'storage') - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); - - if (existing.case?.details?.storageFormFactors) { - return storage.filter(stor => { - const caseStorageFormFactor = existing.case.details.storageFormFactors.find( - (sf: any) => sf.formFactor === stor.formFactor - ); - - if (!caseStorageFormFactor) return false; - - const usedSlots = existing.storage - .filter((s: any) => s.details.formFactor === stor.formFactor) - .reduce((sum: number, s: any) => sum + s.quantity, 0); - - return usedSlots < Number(caseStorageFormFactor.numSlots); - }); - } - - return storage; -} - -async function getCompatibleCases(db: Database, existing: any, limit?: number, sortCondition?: any) { - const conditions = [eq(componentsTable.type, 'case')]; - - if (existing.gpu) { - conditions.push( - sql`${pcCasesTable.gpuMaxLength} >= ${existing.gpu.details.length}` - ); - } - - if (existing.cooler) { - conditions.push( - sql`${pcCasesTable.coolerMaxHeight} >= ${existing.cooler.details.height}` - ); - } - - const cases = await db - .select({ - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - coolerMaxHeight: pcCasesTable.coolerMaxHeight, - gpuMaxLength: pcCasesTable.gpuMaxLength, - }) - .from(componentsTable) - .innerJoin( - pcCasesTable, - eq(componentsTable.id, pcCasesTable.componentId) - ) - .where( - and( - ...conditions) - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); - - if(cases.length === 0) return []; - - const caseIds = cases.map(c => c.id); - - const [allStorageFF, allPsFF, allMoboFF] = await Promise.all([ - db.select().from(caseStorageFormFactorsTable) - .where( - inArray(caseStorageFormFactorsTable.caseId, caseIds) - ), - db.select().from(casePsFormFactorsTable) - .where( - inArray(casePsFormFactorsTable.caseId, caseIds) - ), - db.select().from(caseMoboFormFactorsTable) - .where( - inArray(caseMoboFormFactorsTable.caseId, caseIds) - ) - ]); - - const storageByCase = allStorageFF.reduce((acc, ff) => { - if (!acc[ff.caseId]) acc[ff.caseId] = []; - acc[ff.caseId].push(ff); - return acc; - }, {} as Record); - - const psByCase = allPsFF.reduce((acc, ff) => { - if (!acc[ff.caseId]) acc[ff.caseId] = []; - acc[ff.caseId].push(ff); - return acc; - }, {} as Record); - - const moboByCase = allMoboFF.reduce((acc, ff) => { - if (!acc[ff.caseId]) acc[ff.caseId] = []; - acc[ff.caseId].push(ff); - return acc; - }, {} as Record); - - const casesWithDetails = cases.map(pcCase => ({ - ...pcCase, - storageFormFactors: storageByCase[pcCase.id] || [], - psFormFactors: psByCase[pcCase.id] || [], - moboFormFactors: moboByCase[pcCase.id] || [] - })); - - let filteredCases = casesWithDetails; - - if (existing.motherboard) { - filteredCases = filteredCases.filter(pcCase => - pcCase.moboFormFactors.some((f: any) => f.formFactor === existing.motherboard.details.formFactor) - ); - } - - if (existing.psu) { - filteredCases = filteredCases.filter(pcCase => - pcCase.psFormFactors.some((f: any) => f.formFactor === existing.psu.details.formFactor) - ); - } - - if (existing.storage.length > 0) { - filteredCases = filteredCases.filter(pcCase => { - return existing.storage.every((stor: any) => { - const storageFF = pcCase.storageFormFactors.find( - (sf: any) => sf.formFactor === stor.details.formFactor - ); - if (!storageFF) return false; - - const usedSlots = existing.storage - .filter((s: any) => s.details.formFactor === stor.details.formFactor) - .reduce((sum: number, s: any) => sum + s.quantity, 0);; - - return usedSlots <= Number(storageFF.numSlots); - }); - }); - } - - return filteredCases; -} - -async function getCompatiblePSUs(db: Database, existing: any, existingTDP: number, limit?: number, sortCondition?: any) { - const conditions = [eq(componentsTable.type, 'power_supply')]; - - const minWattage = existingTDP * 1.2; - conditions.push( - sql`${powerSupplyTable.wattage} >= ${minWattage}` - ); - - const psus = await db - .select({ - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - psuType: powerSupplyTable.type, - wattage: powerSupplyTable.wattage, - formFactor: powerSupplyTable.formFactor, - }) - .from(componentsTable) - .innerJoin( - powerSupplyTable, - eq(componentsTable.id, powerSupplyTable.componentId) - ) - .where( - and( - ...conditions - ) - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); - - if (existing.case?.details?.psFormFactors) { - const casePSFormFactors = existing.case.details.psFormFactors.map((f: any) => f.formFactor); - return psus.filter(psu => casePSFormFactors.includes(psu.formFactor)); - } - - return psus; -} - -async function getCompatiblePCIeComponents(db: Database, existing: any, componentType: string, pciExpressSlotsUsed: number, limit?: number, sortCondition?: any) { - let table: any; - let selectFields: any = { - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - }; - - switch (componentType) { - case 'network_card': - table = networkCardsTable; - selectFields = { - ...selectFields, - numPorts: networkCardsTable.numPorts, - speed: networkCardsTable.speed, - interface: networkCardsTable.interface, - }; - break; - case 'network_adapter': - table = networkAdaptersTable; - selectFields = { - ...selectFields, - wifiVersion: networkAdaptersTable.wifiVersion, - interface: networkAdaptersTable.interface, - numAntennas: networkAdaptersTable.numAntennas, - }; - break; - case 'sound_card': - table = soundCardsTable; - selectFields = { - ...selectFields, - sampleRate: soundCardsTable.sampleRate, - bitDepth: soundCardsTable.bitDepth, - chipset: soundCardsTable.chipset, - interface: soundCardsTable.interface, - channel: soundCardsTable.channel, - }; - break; - case 'memory_card': - table = memoryCardsTable; - selectFields = { - ...selectFields, - numSlots: memoryCardsTable.numSlots, - interface: memoryCardsTable.interface, - }; - break; - default: - return []; - } - - const components = await db - .select(selectFields) - .from(componentsTable) - .innerJoin( - table, - eq(componentsTable.id, table.componentId) - ) - .where( - eq(componentsTable.type, componentType) - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); - - if (existing.motherboard) { - const availableSlots = Number(existing.motherboard.details.pciExpressSlots); - return components.filter(() => (pciExpressSlotsUsed + 1) <= availableSlots); - } - - return components; -} - -async function getCompatibleOpticalDrives(db: Database, existing: any, limit?: number, sortCondition?: any) { - if (existing.opticalDrives && existing.opticalDrives.length > 0) { - return []; - } - - return db - .select({ - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - driveType: opticalDrivesTable.type, - formFactor: opticalDrivesTable.formFactor, - interface: opticalDrivesTable.interface, - writeSpeed: opticalDrivesTable.writeSpeed, - readSpeed: opticalDrivesTable.readSpeed - }) - .from(componentsTable) - .innerJoin( - opticalDrivesTable, - eq(componentsTable.id, opticalDrivesTable.componentId) - ) - .where( - eq(componentsTable.type, 'optical_drive') - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); -} -async function getCompatibleCables(db: Database, limit?: number, sortCondition?: any) { - return db - .select({ - id: componentsTable.id, - name: componentsTable.name, - brand: componentsTable.brand, - price: componentsTable.price, - imgUrl: componentsTable.imgUrl, - type: componentsTable.type, - lengthCm: cablesTable.lengthCm, - cableType: cablesTable.type - }) - .from(componentsTable) - .innerJoin( - cablesTable, - eq(componentsTable.id, cablesTable.componentId) - ) - .where( - eq(componentsTable.type, 'cables') - ) - .orderBy( - sortCondition - ) - .limit(limit || 100); -} - -export async function addComponentToBuild(db: Database, userId: number, buildId: number, componentId: number) { - return db.transaction(async (tx) => { - const [build] = await tx - .select() - .from(buildsTable) - .where( - and( - eq(buildsTable.id, buildId), - eq(buildsTable.userId, userId) - ) - ) - .limit(1); - - if(!build || build.isApproved) return null; - - await tx - .insert(buildComponentsTable) - .values({ - buildId, - componentId, - numComponents: 1 - }) - .onConflictDoUpdate({ - target: [buildComponentsTable.buildId, buildComponentsTable.componentId], - set: { - numComponents: sql`${buildComponentsTable.numComponents} + 1` - } - }); - - const buildComponents = await tx - .select({ - price: componentsTable.price, - quantity: buildComponentsTable.numComponents - }) - .from(buildComponentsTable) - .innerJoin( - componentsTable, - eq(buildComponentsTable.componentId, componentsTable.id) - ) - .where( - eq(buildComponentsTable.buildId, buildId) - ); - - const totalPrice = buildComponents.reduce((sum, c) => - sum + (Number(c.price) * c.quantity), 0 - ); - - await tx - .update(buildsTable) - .set({ - totalPrice: totalPrice.toFixed(2) - }) - .where( - eq(buildsTable.id, buildId) - ); - - return buildId; - }) -} - -export async function removeComponentFromBuild(db: Database, userId: number, buildId: number, componentId: number) { - return db.transaction(async (tx) => { - const [build] = await tx - .select() - .from(buildsTable) - .where( - and( - eq(buildsTable.id, buildId), - eq(buildsTable.userId, userId) - ) - ) - .limit(1); - - if(!build || build.isApproved) return null; - - const [existing] = await tx - .select({ - quantity: buildComponentsTable.numComponents - }) - .from(buildComponentsTable) - .where( - and( - eq(buildComponentsTable.buildId, buildId), - eq(buildComponentsTable.componentId, componentId) - ) - ) - .limit(1); - - if (!existing) return null; - - if (existing.quantity > 1) { - await tx - .update(buildComponentsTable) - .set({ - numComponents: sql`${buildComponentsTable.numComponents} - 1` - }) - .where( - and( - eq(buildComponentsTable.buildId, buildId), - eq(buildComponentsTable.componentId, componentId) - ) - ); - } else { - await tx - .delete(buildComponentsTable) - .where( - and( - eq(buildComponentsTable.buildId, buildId), - eq(buildComponentsTable.componentId, componentId) - ) - ); - } - - const buildComponents = await tx - .select({ - price: componentsTable.price, - quantity: buildComponentsTable.numComponents - }) - .from(buildComponentsTable) - .innerJoin( - componentsTable, - eq(buildComponentsTable.componentId, componentsTable.id) - ) - .where( - eq(buildComponentsTable.buildId, buildId) - ); - - const totalPrice = buildComponents.reduce((sum, c) => - sum + (Number(c.price) * c.quantity), 0 - ); - - await tx - .update(buildsTable) - .set({ - totalPrice: totalPrice.toFixed(2) - }) - .where( - eq(buildsTable.id, buildId) - ); - - return componentId; - }) -} Index: database/drizzle/queries/index.ts =================================================================== --- database/drizzle/queries/index.ts (revision ff3a6146e85f77e90dc322a0ad0c9d9eb048c629) +++ (revision ) @@ -1,3 +1,0 @@ -export * from "./users"; -export * from "./builds"; -export * from "./components"; Index: database/drizzle/queries/todos.ts =================================================================== --- database/drizzle/queries/todos.ts (revision 8ee45534cd667e6cced2ed96968c95d8de3b1029) +++ database/drizzle/queries/todos.ts (revision 8ee45534cd667e6cced2ed96968c95d8de3b1029) @@ -0,0 +1,10 @@ +import type { dbSqlite } from "../db"; +import { todoTable } from "../schema/todos"; + +export function insertTodo(db: ReturnType, text: string) { + return db.insert(todoTable).values({ text }); +} + +export function getAllTodos(db: ReturnType) { + return db.select().from(todoTable).all(); +} Index: database/drizzle/queries/users.ts =================================================================== --- database/drizzle/queries/users.ts (revision ff3a6146e85f77e90dc322a0ad0c9d9eb048c629) +++ (revision ) @@ -1,93 +1,0 @@ -import type { Database } from "../db"; -import {adminsTable, suggestionsTable, usersTable} from "../schema"; -import {desc, eq} from "drizzle-orm"; - -export async function createUser(db: Database, username: string, email: string, passwordHash: string) { - const [newUser] = await db - .insert(usersTable) - .values({ - username: username, - email: email, - passwordHash: passwordHash, - }) - .returning({ - id: usersTable.id, - }); - - return newUser?.id ?? null; -} - -export async function getUserProfile(db: Database, userId: number) { - const [user] = await db - .select({ - username: usersTable.username, - email: usersTable.email, - }) - .from(usersTable) - .where( - eq(usersTable.id, userId) - ) - .limit(1); - - return user ?? null; -} - -export async function isAdmin(db: Database, userId: number) { - const [admin] = await db - .selectDistinct() - .from(adminsTable) - .where( - eq(adminsTable.userId, userId) - ) - .limit(1); - - return !!admin; -} - -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) - ) - .returning({ - id: suggestionsTable.id - }); - - return result?.id ?? null; -} - -export async function addNewComponentSuggestion(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; -}