source: database/drizzle/queries/components.ts@ b348db4

main
Last change on this file since b348db4 was b348db4, checked in by Tome <gjorgievtome@…>, 5 months ago

Add stored functions and triggers
  • Property mode set to 100644
File size: 37.2 KB
Line 
1import type { Database } from "../db";
2import {
3 buildComponentsTable,
4 buildsTable, cablesTable, caseMoboFormFactorsTable, casePsFormFactorsTable, caseStorageFormFactorsTable,
5 componentsTable, coolerCPUSocketsTable, coolersTable,
6 CPUTable,
7 GPUTable, memoryCardsTable,
8 memoryTable, motherboardsTable,
9 networkAdaptersTable,
10 networkCardsTable, opticalDrivesTable, pcCasesTable, powerSupplyTable,
11 ratingBuildsTable, soundCardsTable, storageTable
12} from "../schema";
13import {and, asc, desc, eq, ilike, SQL, sql} from "drizzle-orm";
14import {typeConfigMap, ComponentType, requiredFields} from "../util/componentFieldConfig";
15import {AnyPgColumn} from "drizzle-orm/pg-core";
16import {inArray} from "drizzle-orm/sql/expressions/conditions";
17export async function getAllComponents(db: Database, limit?: number, componentType?: string, sort?: string, q?: string) {
18 let queryConditions = [];
19 let sortConditions = [];
20
21 if (q) {
22 queryConditions.push(
23 ilike(componentsTable.name, `%${q}%`)
24 );
25 }
26
27 switch(sort) {
28 case 'price_asc':
29 sortConditions.push(
30 asc(componentsTable.price)
31 );
32 break;
33 case 'price_desc':
34 sortConditions.push(
35 desc(componentsTable.price)
36 );
37 break;
38 default:
39 sortConditions.push(
40 desc(componentsTable.price)
41 );
42 break;
43 }
44
45 if(componentType && componentType.trim() !== 'all') {
46 queryConditions.push(
47 eq(componentsTable.type, componentType.trim().toLowerCase())
48 );
49 }
50
51 const components = await db
52 .select()
53 .from(componentsTable)
54 .where(
55 queryConditions.length > 0 ?
56 and (
57 ...queryConditions
58 )
59 : undefined
60 )
61 .orderBy(
62 ...sortConditions
63 )
64 .limit(limit || 100); // 100 placeholder
65
66 return components;
67}
68
69export async function getComponentDetails(db: Database, componentId: number) {
70 const [component] = await db
71 .select()
72 .from(componentsTable)
73 .where(
74 eq(componentsTable.id, componentId)
75 )
76 .limit(1);
77
78 if(!component) return null;
79
80 const config = typeConfigMap[component.type as ComponentType];
81
82 const [details] = await db
83 .select()
84 .from(config.table)
85 .where(
86 eq(config.table.componentId, componentId)
87 )
88 .limit(1);
89
90 if (component.type === 'case') {
91 details.storageFormFactors = await db.select().from(caseStorageFormFactorsTable).where(eq(caseStorageFormFactorsTable.caseId, componentId));
92 details.psFormFactors = await db.select().from(casePsFormFactorsTable).where(eq(casePsFormFactorsTable.caseId, componentId));
93 details.moboFormFactors = await db.select().from(caseMoboFormFactorsTable).where(eq(caseMoboFormFactorsTable.caseId, componentId));
94 }
95
96 if (component.type === 'cooler') {
97 details.cpuSockets = await db.select().from(coolerCPUSocketsTable).where(eq(coolerCPUSocketsTable.coolerId, componentId));
98 }
99
100 return {
101 ...component,
102 details: details
103 };
104}
105
106export async function getDetailsNewComponent(componentType: string) {
107 const config = typeConfigMap[componentType as ComponentType];
108
109 return {
110 type: componentType as ComponentType,
111 requiredFields: requiredFields[componentType as ComponentType] ?? [],
112 multiTables: config.multiTables ?? {},
113 };
114}
115
116export async function addNewComponent(db: Database, name: string, brand: string, price: number, imgUrl: string, type: string, specificData: any) {
117 return db.transaction(async (tx) => {
118 const [newComponent] = await tx
119 .insert(componentsTable)
120 .values({
121 name: name,
122 brand: brand,
123 price: price.toFixed(2),
124 imgUrl: imgUrl,
125 type: type
126 })
127 .returning({
128 id: componentsTable.id
129 });
130
131 const componentId = newComponent.id;
132
133 const config = typeConfigMap[type as ComponentType];
134
135 await tx
136 .insert(config.table)
137 .values({
138 componentId: componentId,
139 ...specificData
140 });
141
142 if (type === 'case') {
143 if (specificData.storageFormFactors) {
144 await tx.insert(caseStorageFormFactorsTable).values(
145 specificData.storageFormFactors.map((sf: any) => ({
146 caseId: componentId,
147 formFactor: sf.formFactor,
148 numSlots: sf.numSlots,
149 }))
150 );
151 }
152 if (specificData.psFormFactors) {
153 await tx.insert(casePsFormFactorsTable).values(
154 specificData.psFormFactors.map((pf: any) => ({
155 caseId: componentId,
156 formFactor: pf.formFactor,
157 }))
158 );
159 }
160 if (specificData.moboFormFactors) {
161 await tx.insert(caseMoboFormFactorsTable).values(
162 specificData.moboFormFactors.map((mf: any) => ({
163 caseId: componentId,
164 formFactor: mf.formFactor,
165 }))
166 );
167 }
168 }
169
170 if (type === 'cooler' && specificData.cpuSockets) {
171 await tx.insert(coolerCPUSocketsTable).values(
172 specificData.cpuSockets.map((socket: any) => ({ coolerId: componentId, socket }))
173 );
174 }
175
176 return componentId;
177 });
178}
179
180export async function getBuildComponents(db: Database, buildId: number) {
181 const [build] = await db
182 .select({
183 buildId: buildsTable.id,
184 userId: buildsTable.userId,
185 })
186 .from(buildsTable)
187 .where(
188 eq(buildsTable.id, buildId)
189 )
190 .limit(1);
191
192 if(!build) return null;
193
194 const components = await db
195 .select({
196 id: componentsTable.id,
197 type: componentsTable.type,
198 quantity: buildComponentsTable.numComponents
199 })
200 .from(buildComponentsTable)
201 .where(
202 eq(buildComponentsTable.buildId, buildId)
203 )
204 .innerJoin(
205 componentsTable,
206 eq(buildComponentsTable.componentId, componentsTable.id)
207 );
208
209 const componentsDetails = [];
210
211 for (const comp of components) {
212 const config = typeConfigMap[comp.type as ComponentType];
213
214 const [details] = await db
215 .select()
216 .from(config.table)
217 .where(
218 eq(config.table.componentId, comp.id)
219 )
220 .limit(1);
221
222 if (comp.type === 'case') {
223 details.storageFormFactors = await db.select().from(caseStorageFormFactorsTable).where(eq(caseStorageFormFactorsTable.caseId, comp.id));
224 details.psFormFactors = await db.select().from(casePsFormFactorsTable).where(eq(casePsFormFactorsTable.caseId, comp.id));
225 details.moboFormFactors = await db.select().from(caseMoboFormFactorsTable).where(eq(caseMoboFormFactorsTable.caseId, comp.id));
226 }
227
228 if (comp.type === 'cooler') {
229 details.cpuSockets = await db.select().from(coolerCPUSocketsTable).where(eq(coolerCPUSocketsTable.coolerId, comp.id));
230 }
231
232 componentsDetails.push({
233 ...comp,
234 quantity: comp.quantity,
235 details: details
236 });
237 }
238
239 return componentsDetails;
240}
241
242export async function getCompatibleComponents(db: Database, buildId: number, componentType: string, limit?: number, sort?: string) {
243 let sortCondition: any;
244
245 switch(sort) {
246 case 'price_asc':
247 sortCondition = asc(componentsTable.price);
248 break;
249 case 'price_desc':
250 sortCondition = desc(componentsTable.price);
251 break;
252 default:
253 sortCondition = desc(componentsTable.price);
254 break;
255 }
256
257 const existingComponents = await getBuildComponents(db, buildId);
258
259 if(!existingComponents) return null;
260
261 const existing = {
262 cpu: existingComponents.find(c => c.type === 'cpu'),
263 motherboard: existingComponents.find(c => c.type === 'motherboard'),
264 case: existingComponents.find(c => c.type === 'case'),
265 cooler: existingComponents.find(c => c.type === 'cooler'),
266 psu: existingComponents.find(c => c.type === 'power_supply'),
267 gpu: existingComponents.find(c => c.type === 'gpu'),
268 memory: existingComponents.filter(c => c.type === 'memory'),
269 storage: existingComponents.filter(c => c.type === 'storage'),
270 networkCards: existingComponents.filter(c => c.type === 'network_card'),
271 networkAdapters: existingComponents.filter(c => c.type === 'network_adapter'),
272 soundCards: existingComponents.filter(c => c.type === 'sound_card'),
273 memoryCards: existingComponents.filter(c => c.type === 'memory_card'),
274 opticalDrives: existingComponents.filter(c => c.type === 'optical_drive'),
275 cables: existingComponents.filter(c => c.type === 'cables')
276 };
277
278 const pciExpressSlotsUsed = [
279 existing.gpu,
280 ...existing.networkCards,
281 ...existing.networkAdapters,
282 ...existing.soundCards,
283 ...existing.memoryCards
284 ].reduce((sum: number, c: any) => {
285 if (!c) return sum;
286 return sum + (c.quantity || 1);
287 }, 0);
288
289 const existingTDP = existingComponents.reduce((sum, c) => {
290 const tdp = c.details?.tdp ? Number(c.details.tdp) : 0;
291 return sum + (tdp * (c.quantity || 1));
292 }, 0);
293
294
295 let compatibleComponents: any[] = [];
296
297 switch (componentType) {
298 case 'cpu':
299 compatibleComponents = await getCompatibleCPUs(db, existing, existingTDP, limit, sortCondition);
300 break;
301 case 'motherboard':
302 compatibleComponents = await getCompatibleMotherboards(db, existing, pciExpressSlotsUsed, limit, sortCondition);
303 break;
304 case 'cooler':
305 compatibleComponents = await getCompatibleCoolers(db, existing, limit, sortCondition);
306 break;
307 case 'gpu':
308 compatibleComponents = await getCompatibleGPUs(db, existing, existingTDP, pciExpressSlotsUsed, limit, sortCondition);
309 break;
310 case 'memory':
311 compatibleComponents = await getCompatibleMemory(db, existing, limit, sortCondition);
312 break;
313 case 'storage':
314 compatibleComponents = await getCompatibleStorage(db, existing, limit, sortCondition);
315 break;
316 case 'case':
317 compatibleComponents = await getCompatibleCases(db, existing, limit, sortCondition);
318 break;
319 case 'power_supply':
320 compatibleComponents = await getCompatiblePSUs(db, existing, existingTDP, limit, sortCondition);
321 break;
322 case 'network_card':
323 case 'network_adapter':
324 case 'sound_card':
325 case 'memory_card':
326 compatibleComponents = await getCompatiblePCIeComponents(db, existing, componentType, pciExpressSlotsUsed, limit, sortCondition);
327 break;
328 case 'optical_drive':
329 compatibleComponents = await getCompatibleOpticalDrives(db, existing, limit, sortCondition);
330 break;
331 case 'cables':
332 compatibleComponents = await getCompatibleCables(db, limit, sortCondition);
333 break;
334 default:
335 compatibleComponents = [];
336 }
337
338 if(compatibleComponents.length === 0) return null;
339
340 return compatibleComponents;
341}
342
343
344// Helper functions for checking component-specific compatibility
345async function getCompatibleCPUs(db: Database, existing: any, existingTDP?: number, limit?: number, sortCondition?: any) {
346 const conditions = [eq(componentsTable.type, 'cpu')];
347
348 if (existing.motherboard) {
349 conditions.push(
350 eq(CPUTable.socket, existing.motherboard.details.socket)
351 );
352 }
353
354 if (existing.cooler) {
355 conditions.push(
356 sql`${CPUTable.tdp} <= ${existing.cooler.details.maxTdpSupported}`
357 );
358 }
359
360 const cpus = await db
361 .select({
362 id: componentsTable.id,
363 name: componentsTable.name,
364 brand: componentsTable.brand,
365 price: componentsTable.price,
366 imgUrl: componentsTable.imgUrl,
367 type: componentsTable.type,
368 socket: CPUTable.socket,
369 cores: CPUTable.cores,
370 threads: CPUTable.threads,
371 baseClock: CPUTable.baseClock,
372 boostClock: CPUTable.boostClock,
373 tdp: CPUTable.tdp,
374 })
375 .from(componentsTable)
376 .innerJoin(
377 CPUTable,
378 eq(componentsTable.id, CPUTable.componentId)
379 )
380 .where(
381 and(
382 ...conditions
383 )
384 )
385 .orderBy(
386 sortCondition
387 )
388 .limit(limit || 100);
389
390 let filteredCPUs = cpus;
391
392 if (existing.cooler?.details?.cpuSockets) {
393 const coolerSockets = existing.cooler.details.cpuSockets.map((s: any) => s.socket);
394 filteredCPUs = filteredCPUs.filter(cpu => coolerSockets.includes(cpu.socket));
395 }
396
397 if(existing.psu?.details?.wattage && existingTDP) {
398 const psuWattage = Number(existing.psu.details.wattage) || 0;
399
400 if (psuWattage > 0) {
401 const budget = psuWattage / 1.2;
402
403 const currentCpuTdp = Number(existing.cpu?.details?.tdp) || 0;
404 const baseWithoutCpu = existingTDP - currentCpuTdp;
405
406 filteredCPUs = filteredCPUs.filter(cpu => {
407 const cpuTdp = Number(cpu.tdp) || 0;
408 return (baseWithoutCpu + cpuTdp) <= budget;
409 });
410 }
411 }
412
413 return filteredCPUs;
414}
415
416async function getCompatibleMotherboards(db: Database, existing: any, pciExpressSlotsUsed: number, limit?: number, sortCondition?: any) {
417 const conditions = [eq(componentsTable.type, 'motherboard')];
418
419 if (existing.cpu) {
420 conditions.push(
421 eq(motherboardsTable.socket, existing.cpu.details.socket)
422 );
423 }
424
425 if (existing.memory.length > 0) {
426 const ramType = existing.memory[0].details.type;
427 conditions.push(
428 eq(motherboardsTable.ramType, ramType)
429 );
430 }
431
432 if (pciExpressSlotsUsed > 0) {
433 conditions.push(
434 sql`${motherboardsTable.pciExpressSlots} >= ${pciExpressSlotsUsed}`
435 );
436 }
437
438 const motherboards = await db
439 .select({
440 id: componentsTable.id,
441 name: componentsTable.name,
442 brand: componentsTable.brand,
443 price: componentsTable.price,
444 imgUrl: componentsTable.imgUrl,
445 type: componentsTable.type,
446 socket: motherboardsTable.socket,
447 chipset: motherboardsTable.chipset,
448 formFactor: motherboardsTable.formFactor,
449 ramType: motherboardsTable.ramType,
450 numRamSlots: motherboardsTable.numRamSlots,
451 maxRamCapacity: motherboardsTable.maxRamCapacity,
452 pciExpressSlots: motherboardsTable.pciExpressSlots,
453 })
454 .from(componentsTable)
455 .innerJoin(
456 motherboardsTable,
457 eq(componentsTable.id, motherboardsTable.componentId)
458 )
459 .where(
460 and(
461 ...conditions
462 )
463 )
464 .orderBy(
465 sortCondition
466 )
467 .limit(limit || 100);
468
469 const compatibleMotherboards = motherboards.filter(mobo => {
470 if (existing.memory.length > 0) {
471 const totalModules = existing.memory.reduce((sum: number, m: any) =>
472 sum + (Number(m.details.modules) * m.quantity), 0
473 );
474 const totalCapacity = existing.memory.reduce((sum: number, m: any) =>
475 sum + (Number(m.details.capacity) * m.quantity), 0
476 );
477
478 if (totalModules > Number(mobo.numRamSlots)) return false;
479 if (totalCapacity > Number(mobo.maxRamCapacity)) return false;
480 }
481
482 return true;
483 });
484
485 if (existing.case?.details?.moboFormFactors) {
486 const caseFormFactors = existing.case.details.moboFormFactors.map((f: any) => f.formFactor);
487 return compatibleMotherboards.filter(mobo => caseFormFactors.includes(mobo.formFactor));
488 }
489
490 return compatibleMotherboards;
491}
492
493async function getCompatibleCoolers(db: Database, existing: any, limit?: number, sortCondition?: any) {
494 const conditions = [eq(componentsTable.type, 'cooler')];
495
496 if (existing.cpu) {
497 conditions.push(
498 sql`${coolersTable.maxTdpSupported} >= ${existing.cpu.details.tdp}`
499 );
500 }
501
502 if (existing.case) {
503 conditions.push(
504 sql`${coolersTable.height} <= ${existing.case.details.coolerMaxHeight}`
505 );
506 }
507
508 const coolers = await db
509 .select({
510 id: componentsTable.id,
511 name: componentsTable.name,
512 brand: componentsTable.brand,
513 price: componentsTable.price,
514 imgUrl: componentsTable.imgUrl,
515 type: componentsTable.type,
516 coolerType: coolersTable.type,
517 height: coolersTable.height,
518 maxTdpSupported: coolersTable.maxTdpSupported,
519 })
520 .from(componentsTable)
521 .innerJoin(
522 coolersTable,
523 eq(componentsTable.id, coolersTable.componentId)
524 )
525 .where(
526 and(
527 ...conditions
528 )
529 )
530 .orderBy(
531 sortCondition
532 )
533 .limit(limit || 100);
534
535
536 if(existing.cpu && coolers.length > 0) {
537 const coolerIds = coolers.map(c => c.id);
538
539 const allSockets = await db
540 .select({
541 coolerId: coolerCPUSocketsTable.coolerId,
542 socket: coolerCPUSocketsTable.socket
543 })
544 .from(coolerCPUSocketsTable)
545 .where(
546 inArray(coolerCPUSocketsTable.coolerId, coolerIds)
547 );
548 const socketsByCooler = allSockets.reduce((acc, { coolerId, socket }) => {
549 if (!acc[coolerId]) acc[coolerId] = [];
550 acc[coolerId].push(socket);
551 return acc;
552 }, {} as Record<number, string[]>);
553
554 return coolers.filter(cooler => {
555 const sockets = socketsByCooler[cooler.id] || [];
556 return sockets.includes(existing.cpu.details.socket);
557 });
558 }
559
560 return coolers;
561}
562
563async function getCompatibleGPUs(db: Database, existing: any, existingTDP: number, pciExpressSlotsUsed: number, limit?: number, sortCondition?: any) {
564 const conditions = [eq(componentsTable.type, 'gpu')];
565
566 if (existing.case) {
567 conditions.push(
568 sql`${GPUTable.length} <= ${existing.case.details.gpuMaxLength}`
569 );
570 }
571
572 const gpus = await db
573 .select({
574 id: componentsTable.id,
575 name: componentsTable.name,
576 brand: componentsTable.brand,
577 price: componentsTable.price,
578 imgUrl: componentsTable.imgUrl,
579 type: componentsTable.type,
580 vram: GPUTable.vram,
581 tdp: GPUTable.tdp,
582 baseClock: GPUTable.baseClock,
583 boostClock: GPUTable.boostClock,
584 chipset: GPUTable.chipset,
585 length: GPUTable.length,
586 })
587 .from(componentsTable)
588 .innerJoin(
589 GPUTable,
590 eq(componentsTable.id, GPUTable.componentId)
591 )
592 .where(
593 and(
594 ...conditions
595 )
596 )
597 .orderBy(
598 sortCondition
599 )
600 .limit(limit || 100);
601
602 let filteredGPUs = gpus;
603
604 if (existing.motherboard) {
605 const availableSlots = Number(existing.motherboard.details.pciExpressSlots);
606 filteredGPUs = gpus.filter(() => (pciExpressSlotsUsed + 1) <= availableSlots);
607 }
608
609 if(existing.psu?.details?.wattage && existingTDP) {
610 const psuWattage = Number(existing.psu.details.wattage) || 0;
611
612 if (psuWattage > 0) {
613 const budget = psuWattage / 1.2;
614
615 const currentGpuTdp = Number(existing.cpu?.details?.tdp) || 0;
616 const baseWithoutGpu = existingTDP - currentGpuTdp;
617
618 filteredGPUs = filteredGPUs.filter(cpu => {
619 const gpuTdp = Number(cpu.tdp) || 0;
620 return (baseWithoutGpu + gpuTdp) <= budget;
621 });
622 }
623 }
624
625 return filteredGPUs;
626}
627
628async function getCompatibleMemory(db: Database, existing: any, limit?: number, sortCondition?: any) {
629 const conditions = [eq(componentsTable.type, 'memory')];
630
631 if (existing.motherboard) {
632 conditions.push(
633 eq(memoryTable.type, existing.motherboard.details.ramType)
634 );
635 }
636
637 const memory = await db
638 .select({
639 id: componentsTable.id,
640 name: componentsTable.name,
641 brand: componentsTable.brand,
642 price: componentsTable.price,
643 imgUrl: componentsTable.imgUrl,
644 type: componentsTable.type,
645 memoryType: memoryTable.type,
646 speed: memoryTable.speed,
647 capacity: memoryTable.capacity,
648 modules: memoryTable.modules,
649 })
650 .from(componentsTable)
651 .innerJoin(
652 memoryTable,
653 eq(componentsTable.id, memoryTable.componentId)
654 )
655 .where(
656 and(
657 ...conditions
658 )
659 )
660 .orderBy(
661 sortCondition
662 )
663 .limit(limit || 100);
664
665 if (existing.motherboard) {
666 const existingModules = existing.memory.reduce((sum: number, m: any) =>
667 sum + Number(m.details.modules), 0
668 );
669 const existingCapacity = existing.memory.reduce((sum: number, m: any) =>
670 sum + Number(m.details.capacity), 0
671 );
672 const maxSlots = Number(existing.motherboard.details.numRamSlots);
673 const maxCapacity = Number(existing.motherboard.details.maxRamCapacity);
674
675 return memory.filter(mem => {
676 const newModules = existingModules + Number(mem.modules);
677 const newCapacity = existingCapacity + Number(mem.capacity);
678 return newModules <= maxSlots && newCapacity <= maxCapacity;
679 });
680 }
681
682 return memory;
683}
684
685async function getCompatibleStorage(db: Database, existing: any, limit?: number, sortCondition?: any) {
686 const storage = await db
687 .select({
688 id: componentsTable.id,
689 name: componentsTable.name,
690 brand: componentsTable.brand,
691 price: componentsTable.price,
692 imgUrl: componentsTable.imgUrl,
693 type: componentsTable.type,
694 storageType: storageTable.type,
695 capacity: storageTable.capacity,
696 formFactor: storageTable.formFactor,
697 })
698 .from(componentsTable)
699 .innerJoin(
700 storageTable,
701 eq(componentsTable.id, storageTable.componentId
702 )
703 )
704 .where(
705 eq(componentsTable.type, 'storage')
706 )
707 .orderBy(
708 sortCondition
709 )
710 .limit(limit || 100);
711
712 if (existing.case?.details?.storageFormFactors) {
713 return storage.filter(stor => {
714 const caseStorageFormFactor = existing.case.details.storageFormFactors.find(
715 (sf: any) => sf.formFactor === stor.formFactor
716 );
717
718 if (!caseStorageFormFactor) return false;
719
720 const usedSlots = existing.storage
721 .filter((s: any) => s.details.formFactor === stor.formFactor)
722 .reduce((sum: number, s: any) => sum + s.quantity, 0);
723
724 return usedSlots < Number(caseStorageFormFactor.numSlots);
725 });
726 }
727
728 return storage;
729}
730
731async function getCompatibleCases(db: Database, existing: any, limit?: number, sortCondition?: any) {
732 const conditions = [eq(componentsTable.type, 'case')];
733
734 if (existing.gpu) {
735 conditions.push(
736 sql`${pcCasesTable.gpuMaxLength} >= ${existing.gpu.details.length}`
737 );
738 }
739
740 if (existing.cooler) {
741 conditions.push(
742 sql`${pcCasesTable.coolerMaxHeight} >= ${existing.cooler.details.height}`
743 );
744 }
745
746 const cases = await db
747 .select({
748 id: componentsTable.id,
749 name: componentsTable.name,
750 brand: componentsTable.brand,
751 price: componentsTable.price,
752 imgUrl: componentsTable.imgUrl,
753 type: componentsTable.type,
754 coolerMaxHeight: pcCasesTable.coolerMaxHeight,
755 gpuMaxLength: pcCasesTable.gpuMaxLength,
756 })
757 .from(componentsTable)
758 .innerJoin(
759 pcCasesTable,
760 eq(componentsTable.id, pcCasesTable.componentId)
761 )
762 .where(
763 and(
764 ...conditions)
765 )
766 .orderBy(
767 sortCondition
768 )
769 .limit(limit || 100);
770
771 if(cases.length === 0) return [];
772
773 const caseIds = cases.map(c => c.id);
774
775 const [allStorageFF, allPsFF, allMoboFF] = await Promise.all([
776 db.select().from(caseStorageFormFactorsTable)
777 .where(
778 inArray(caseStorageFormFactorsTable.caseId, caseIds)
779 ),
780 db.select().from(casePsFormFactorsTable)
781 .where(
782 inArray(casePsFormFactorsTable.caseId, caseIds)
783 ),
784 db.select().from(caseMoboFormFactorsTable)
785 .where(
786 inArray(caseMoboFormFactorsTable.caseId, caseIds)
787 )
788 ]);
789
790 const storageByCase = allStorageFF.reduce((acc, ff) => {
791 if (!acc[ff.caseId]) acc[ff.caseId] = [];
792 acc[ff.caseId].push(ff);
793 return acc;
794 }, {} as Record<number, any[]>);
795
796 const psByCase = allPsFF.reduce((acc, ff) => {
797 if (!acc[ff.caseId]) acc[ff.caseId] = [];
798 acc[ff.caseId].push(ff);
799 return acc;
800 }, {} as Record<number, any[]>);
801
802 const moboByCase = allMoboFF.reduce((acc, ff) => {
803 if (!acc[ff.caseId]) acc[ff.caseId] = [];
804 acc[ff.caseId].push(ff);
805 return acc;
806 }, {} as Record<number, any[]>);
807
808 const casesWithDetails = cases.map(pcCase => ({
809 ...pcCase,
810 storageFormFactors: storageByCase[pcCase.id] || [],
811 psFormFactors: psByCase[pcCase.id] || [],
812 moboFormFactors: moboByCase[pcCase.id] || []
813 }));
814
815 let filteredCases = casesWithDetails;
816
817 if (existing.motherboard) {
818 filteredCases = filteredCases.filter(pcCase =>
819 pcCase.moboFormFactors.some((f: any) => f.formFactor === existing.motherboard.details.formFactor)
820 );
821 }
822
823 if (existing.psu) {
824 filteredCases = filteredCases.filter(pcCase =>
825 pcCase.psFormFactors.some((f: any) => f.formFactor === existing.psu.details.formFactor)
826 );
827 }
828
829 if (existing.storage.length > 0) {
830 filteredCases = filteredCases.filter(pcCase => {
831 return existing.storage.every((stor: any) => {
832 const storageFF = pcCase.storageFormFactors.find(
833 (sf: any) => sf.formFactor === stor.details.formFactor
834 );
835 if (!storageFF) return false;
836
837 const usedSlots = existing.storage
838 .filter((s: any) => s.details.formFactor === stor.details.formFactor)
839 .reduce((sum: number, s: any) => sum + s.quantity, 0);;
840
841 return usedSlots <= Number(storageFF.numSlots);
842 });
843 });
844 }
845
846 return filteredCases;
847}
848
849async function getCompatiblePSUs(db: Database, existing: any, existingTDP: number, limit?: number, sortCondition?: any) {
850 const conditions = [eq(componentsTable.type, 'power_supply')];
851
852 const minWattage = existingTDP * 1.2;
853 conditions.push(
854 sql`${powerSupplyTable.wattage} >= ${minWattage}`
855 );
856
857 const psus = await db
858 .select({
859 id: componentsTable.id,
860 name: componentsTable.name,
861 brand: componentsTable.brand,
862 price: componentsTable.price,
863 imgUrl: componentsTable.imgUrl,
864 type: componentsTable.type,
865 psuType: powerSupplyTable.type,
866 wattage: powerSupplyTable.wattage,
867 formFactor: powerSupplyTable.formFactor,
868 })
869 .from(componentsTable)
870 .innerJoin(
871 powerSupplyTable,
872 eq(componentsTable.id, powerSupplyTable.componentId)
873 )
874 .where(
875 and(
876 ...conditions
877 )
878 )
879 .orderBy(
880 sortCondition
881 )
882 .limit(limit || 100);
883
884 if (existing.case?.details?.psFormFactors) {
885 const casePSFormFactors = existing.case.details.psFormFactors.map((f: any) => f.formFactor);
886 return psus.filter(psu => casePSFormFactors.includes(psu.formFactor));
887 }
888
889 return psus;
890}
891
892async function getCompatiblePCIeComponents(db: Database, existing: any, componentType: string, pciExpressSlotsUsed: number, limit?: number, sortCondition?: any) {
893 let table: any;
894 let selectFields: any = {
895 id: componentsTable.id,
896 name: componentsTable.name,
897 brand: componentsTable.brand,
898 price: componentsTable.price,
899 imgUrl: componentsTable.imgUrl,
900 type: componentsTable.type,
901 };
902
903 switch (componentType) {
904 case 'network_card':
905 table = networkCardsTable;
906 selectFields = {
907 ...selectFields,
908 numPorts: networkCardsTable.numPorts,
909 speed: networkCardsTable.speed,
910 interface: networkCardsTable.interface,
911 };
912 break;
913 case 'network_adapter':
914 table = networkAdaptersTable;
915 selectFields = {
916 ...selectFields,
917 wifiVersion: networkAdaptersTable.wifiVersion,
918 interface: networkAdaptersTable.interface,
919 numAntennas: networkAdaptersTable.numAntennas,
920 };
921 break;
922 case 'sound_card':
923 table = soundCardsTable;
924 selectFields = {
925 ...selectFields,
926 sampleRate: soundCardsTable.sampleRate,
927 bitDepth: soundCardsTable.bitDepth,
928 chipset: soundCardsTable.chipset,
929 interface: soundCardsTable.interface,
930 channel: soundCardsTable.channel,
931 };
932 break;
933 case 'memory_card':
934 table = memoryCardsTable;
935 selectFields = {
936 ...selectFields,
937 numSlots: memoryCardsTable.numSlots,
938 interface: memoryCardsTable.interface,
939 };
940 break;
941 default:
942 return [];
943 }
944
945 const components = await db
946 .select(selectFields)
947 .from(componentsTable)
948 .innerJoin(
949 table,
950 eq(componentsTable.id, table.componentId)
951 )
952 .where(
953 eq(componentsTable.type, componentType)
954 )
955 .orderBy(
956 sortCondition
957 )
958 .limit(limit || 100);
959
960 if (existing.motherboard) {
961 const availableSlots = Number(existing.motherboard.details.pciExpressSlots);
962 return components.filter(() => (pciExpressSlotsUsed + 1) <= availableSlots);
963 }
964
965 return components;
966}
967
968async function getCompatibleOpticalDrives(db: Database, existing: any, limit?: number, sortCondition?: any) {
969 if (existing.opticalDrives && existing.opticalDrives.length > 0) {
970 return [];
971 }
972
973 return db
974 .select({
975 id: componentsTable.id,
976 name: componentsTable.name,
977 brand: componentsTable.brand,
978 price: componentsTable.price,
979 imgUrl: componentsTable.imgUrl,
980 type: componentsTable.type,
981 driveType: opticalDrivesTable.type,
982 formFactor: opticalDrivesTable.formFactor,
983 interface: opticalDrivesTable.interface,
984 writeSpeed: opticalDrivesTable.writeSpeed,
985 readSpeed: opticalDrivesTable.readSpeed
986 })
987 .from(componentsTable)
988 .innerJoin(
989 opticalDrivesTable,
990 eq(componentsTable.id, opticalDrivesTable.componentId)
991 )
992 .where(
993 eq(componentsTable.type, 'optical_drive')
994 )
995 .orderBy(
996 sortCondition
997 )
998 .limit(limit || 100);
999}
1000async function getCompatibleCables(db: Database, limit?: number, sortCondition?: any) {
1001 return db
1002 .select({
1003 id: componentsTable.id,
1004 name: componentsTable.name,
1005 brand: componentsTable.brand,
1006 price: componentsTable.price,
1007 imgUrl: componentsTable.imgUrl,
1008 type: componentsTable.type,
1009 lengthCm: cablesTable.lengthCm,
1010 cableType: cablesTable.type
1011 })
1012 .from(componentsTable)
1013 .innerJoin(
1014 cablesTable,
1015 eq(componentsTable.id, cablesTable.componentId)
1016 )
1017 .where(
1018 eq(componentsTable.type, 'cables')
1019 )
1020 .orderBy(
1021 sortCondition
1022 )
1023 .limit(limit || 100);
1024}
1025
1026export async function addComponentToBuild(db: Database, userId: number, buildId: number, componentId: number) {
1027 return db.transaction(async (tx) => {
1028 const [build] = await tx
1029 .select()
1030 .from(buildsTable)
1031 .where(
1032 and(
1033 eq(buildsTable.id, buildId),
1034 eq(buildsTable.userId, userId)
1035 )
1036 )
1037 .limit(1);
1038
1039 if(!build || build.isApproved) return null;
1040
1041 await tx
1042 .insert(buildComponentsTable)
1043 .values({
1044 buildId,
1045 componentId,
1046 numComponents: 1
1047 })
1048 .onConflictDoUpdate({
1049 target: [buildComponentsTable.buildId, buildComponentsTable.componentId],
1050 set: {
1051 numComponents: sql`${buildComponentsTable.numComponents} + 1`
1052 }
1053 });
1054
1055 const buildComponents = await tx
1056 .select({
1057 price: componentsTable.price,
1058 quantity: buildComponentsTable.numComponents
1059 })
1060 .from(buildComponentsTable)
1061 .innerJoin(
1062 componentsTable,
1063 eq(buildComponentsTable.componentId, componentsTable.id)
1064 )
1065 .where(
1066 eq(buildComponentsTable.buildId, buildId)
1067 );
1068
1069 // const totalPrice = buildComponents.reduce((sum, c) =>
1070 // sum + (Number(c.price) * c.quantity), 0
1071 // );
1072
1073 // await tx
1074 // .update(buildsTable)
1075 // .set({
1076 // totalPrice: totalPrice.toFixed(2)
1077 // })
1078 // .where(
1079 // eq(buildsTable.id, buildId)
1080 // );
1081
1082 return buildId;
1083 })
1084}
1085
1086export async function removeComponentFromBuild(db: Database, userId: number, buildId: number, componentId: number) {
1087 return db.transaction(async (tx) => {
1088 const [build] = await tx
1089 .select()
1090 .from(buildsTable)
1091 .where(
1092 and(
1093 eq(buildsTable.id, buildId),
1094 eq(buildsTable.userId, userId)
1095 )
1096 )
1097 .limit(1);
1098
1099 if(!build || build.isApproved) return null;
1100
1101 const [existing] = await tx
1102 .select({
1103 quantity: buildComponentsTable.numComponents
1104 })
1105 .from(buildComponentsTable)
1106 .where(
1107 and(
1108 eq(buildComponentsTable.buildId, buildId),
1109 eq(buildComponentsTable.componentId, componentId)
1110 )
1111 )
1112 .limit(1);
1113
1114 if (!existing) return null;
1115
1116 if (existing.quantity > 1) {
1117 await tx
1118 .update(buildComponentsTable)
1119 .set({
1120 numComponents: sql`${buildComponentsTable.numComponents} - 1`
1121 })
1122 .where(
1123 and(
1124 eq(buildComponentsTable.buildId, buildId),
1125 eq(buildComponentsTable.componentId, componentId)
1126 )
1127 );
1128 } else {
1129 await tx
1130 .delete(buildComponentsTable)
1131 .where(
1132 and(
1133 eq(buildComponentsTable.buildId, buildId),
1134 eq(buildComponentsTable.componentId, componentId)
1135 )
1136 );
1137 }
1138
1139 const buildComponents = await tx
1140 .select({
1141 price: componentsTable.price,
1142 quantity: buildComponentsTable.numComponents
1143 })
1144 .from(buildComponentsTable)
1145 .innerJoin(
1146 componentsTable,
1147 eq(buildComponentsTable.componentId, componentsTable.id)
1148 )
1149 .where(
1150 eq(buildComponentsTable.buildId, buildId)
1151 );
1152
1153 // const totalPrice = buildComponents.reduce((sum, c) =>
1154 // sum + (Number(c.price) * c.quantity), 0
1155 // );
1156
1157 // await tx
1158 // .update(buildsTable)
1159 // .set({
1160 // totalPrice: totalPrice.toFixed(2)
1161 // })
1162 // .where(
1163 // eq(buildsTable.id, buildId)
1164 // );
1165
1166 return componentId;
1167 })
1168}
Note: See TracBrowser for help on using the repository browser.