Index: database/drizzle/queries/components.ts =================================================================== --- database/drizzle/queries/components.ts (revision e7f1bfa7ce76200b61fe01419065e5b4c1bc1f96) +++ database/drizzle/queries/components.ts (revision ad311c33bc79a550eeaa21f0291e015ee0021a78) @@ -11,9 +11,11 @@ ratingBuildsTable, soundCardsTable, storageTable } from "../schema"; -import {and, desc, eq, ilike} from "drizzle-orm"; +import {and, asc, desc, eq, ilike, SQL, sql} from "drizzle-orm"; import {typeConfigMap, ComponentType} from "../config/componentFieldConfig"; import {AnyPgColumn} from "drizzle-orm/pg-core"; -export async function getAllComponents(db: Database, limit?: number, componentType?: string, q?: string) { +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) { @@ -21,4 +23,22 @@ ilike(componentsTable.name, `%${q}%`) ); + } + + switch(sort) { + case 'price_asc': + sortConditions.push( + asc(buildsTable.totalPrice) + ); + break; + case 'price_desc': + sortConditions.push( + desc(buildsTable.totalPrice) + ); + break; + default: + sortConditions.push( + desc(buildsTable.createdAt) + ); + break; } @@ -45,5 +65,5 @@ ) .orderBy( - desc(componentsTable.price) + ...sortConditions ) .limit(limit || 100); // 100 placeholder @@ -213,7 +233,670 @@ } -export async function getCompatibleComponents(db: Database, buildId: number, componentType: string) { - // TO BE IMPLEMENTED - return null; +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'), + }; + + const pciExpressSlotsUsed = [ + existing.gpu, + ...existing.networkCards, + ...existing.networkAdapters, + ...existing.soundCards + ].filter(Boolean).length; + + const existingTDP = existingComponents.reduce((sum, c) => { + const tdp = c.details?.tdp ? Number(c.details.tdp) : 0; + return sum + tdp; + }, 0); + + + let compatibleComponents: any[] = []; + + switch (componentType) { + case 'cpu': + compatibleComponents = await getCompatibleCPUs(db, existing, 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, 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': + compatibleComponents = await getCompatiblePCIeComponents(db, existing, componentType, pciExpressSlotsUsed, 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, 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); + + if (existing.cooler?.details?.cpuSockets) { + const coolerSockets = existing.cooler.details.cpuSockets.map((s: any) => s.socket); + return cpus.filter(cpu => coolerSockets.includes(cpu.socket)); + } + + return cpus; +} + +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), 0 + ); + const totalCapacity = existing.memory.reduce((sum: number, m: any) => + sum + Number(m.details.capacity), 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, 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); + + if (existing.motherboard) { + const availableSlots = Number(existing.motherboard.details.pciExpressSlots); + return gpus.filter(() => (pciExpressSlotsUsed + 1) <= availableSlots); + } + + return gpus; +} + +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 + ).length; + + 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 + ).length; + + 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; + 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; }