source: database/drizzle/util/seed.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: 43.1 KB
Line 
1import pg from 'pg';
2
3const dataSQL = `
4TRUNCATE TABLE
5 suggestions,
6 cooler_cpu_sockets,
7 case_mobo_form_factors,
8 case_ps_form_factors,
9 case_storage_form_factors,
10 build_component,
11 favorite_build,
12 rating_build,
13 review,
14 build,
15 admins,
16 users,
17 network_card,
18 network_adapter,
19 cables,
20 sound_card,
21 optical_drive,
22 memory_card,
23 storage,
24 motherboard,
25 cooler,
26 pc_case,
27 power_supply,
28 memory,
29 gpu,
30 cpu,
31 components
32RESTART IDENTITY CASCADE;
33
34INSERT INTO users (username, password, email) VALUES
35('tome', 'tg', 'tome.gjorgiev@gmail.com'),
36('mihail', 'mn', 'mihail.naumov@gmail.com'),
37('stefan', 'sv', 'stefan.velkovski@gmail.com'),
38('admin', 'admin', 'admin@gmail.com'),
39('pc_wizard', 'pw', 'wizard@gmail.com'),
40('budget_king', 'bk', 'budget@gmail.com'),
41('rgb_lover', 'rgb', 'rgblover@gmail.com'),
42('streamer_pro', 'sp', 'streamer@gmail.com'),
43('office_guy', 'og', 'office@gmail.com'),
44('linux_fan', 'lf', 'linux@gmail.com'),
45('first_timer', 'ft', 'noob@gmail.com');
46
47INSERT INTO admins (user_id) VALUES (4);
48
49INSERT INTO components (name, brand, price, type, img_url) VALUES
50('Ryzen 5 5600X', 'AMD', 199.99, 'cpu', 'https://static.hardwaredb.net/badges/ryzen-5-5600.png'),
51('RTX 3060', 'NVIDIA', 329.99, 'gpu', 'https://static.hardwaredb.net/badges/geforce-rtx-3060.png'),
52('16GB DDR4 Kit', 'Corsair', 79.99, 'memory', 'https://cwsmgmt.corsair.com/press/CORSAIRLogo2020_stack_W.png'),
53('650W PSU', 'EVGA', 89.99, 'power_supply', 'https://cdn.brandfetch.io/iddPf9bbl3/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
54('Mid Tower Case', 'NZXT', 119.99, 'case', 'https://nzxt.com/cdn/shop/files/h5-elite-hero-black.png?v=1744789660&width=2000'),
55('Air Cooler', 'Noctua', 69.99, 'cooler', 'https://cdn.brandfetch.io/idSeoCDyH9/w/400/h/400/theme/dark/icon.png?c=1dxbfHSJFAPEGdCLU4o5B'),
56('B550 Motherboard', 'ASUS', 149.99, 'motherboard', 'https://cdn.brandfetch.io/idGnlhbTXH/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
57('1TB NVMe SSD', 'Samsung', 129.99, 'storage', 'https://cdn.brandfetch.io/iduaw_nOnR/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
58('Sound Card', 'Creative', 59.99, 'sound_card', NULL),
59('Network Card', 'Intel', 39.99, 'network_card', 'https://cdn.brandfetch.io/idTGhLyv09/w/400/h/400/theme/dark/icon.png?c=1dxbfHSJFAPEGdCLU4o5B'),
60('Network Adapter', 'TP-Link', 29.99, 'network_adapter', 'https://cdn.brandfetch.io/idfUqCiOVX/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
61('Optical Drive', 'LG', 19.99, 'optical_drive', 'https://cdn.brandfetch.io/idEI6u48uh/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
62('Memory Card', 'SanDisk', 15.99, 'memory_card', 'https://cdn.brandfetch.io/idM3tf3Iq8/w/800/h/800/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
63('Cables Pack', 'Corsair', 9.99, 'cables', 'https://cwsmgmt.corsair.com/press/CORSAIRLogo2020_stack_W.png'),
64('Ryzen 5 7600', 'AMD', 229.00, 'cpu', 'https://static.hardwaredb.net/badges/ryzen-5-7600.png'),
65('Ryzen 7 7800X3D', 'AMD', 399.00, 'cpu', 'https://static.hardwaredb.net/badges/ryzen-7-7800x3d.png'),
66('Ryzen 9 7950X', 'AMD', 599.00, 'cpu', 'https://static.hardwaredb.net/badges/ryzen-9-7950x.png'),
67('Core i3-13100F', 'Intel', 119.99, 'cpu', 'https://static.hardwaredb.net/badges/core-i3-13100.png'),
68('Core i5-13600K', 'Intel', 319.99, 'cpu', 'https://static.hardwaredb.net/badges/core-i5-13600k.png'),
69('Core i7-14700K', 'Intel', 409.99, 'cpu', 'https://static.hardwaredb.net/badges/core-i7-14700k.png'),
70('Core i9-14900KS', 'Intel', 689.99, 'cpu', 'https://static.hardwaredb.net/badges/core-i9-14900ks.png'),
71('Radeon RX 6600', 'PowerColor', 199.99, 'gpu', 'https://static.hardwaredb.net/badges/radeon-rx-6600.png'),
72('Radeon RX 7600', 'Sapphire', 269.99, 'gpu', 'https://static.hardwaredb.net/badges/radeon-rx-7600.png'),
73('Radeon RX 7800 XT', 'XFX', 499.99, 'gpu', 'https://static.hardwaredb.net/badges/radeon-rx-7800-xt.png'),
74('Radeon RX 7900 XTX', 'Sapphire', 999.99, 'gpu', 'https://static.hardwaredb.net/badges/radeon-rx-7900-xtx.png'),
75('GeForce RTX 3050', 'MSI', 229.99, 'gpu', 'https://static.hardwaredb.net/badges/geforce-rtx-3050.png'),
76('GeForce RTX 4060', 'Zotac', 299.99, 'gpu', 'https://static.hardwaredb.net/badges/geforce-rtx-4060.png'),
77('GeForce RTX 4070 Super', 'ASUS', 599.99, 'gpu', 'https://static.hardwaredb.net/badges/geforce-rtx-4070-super.png'),
78('GeForce RTX 4080 Super', 'Gigabyte', 999.99, 'gpu', 'https://static.hardwaredb.net/badges/geforce-rtx-4080-super.png'),
79('GeForce RTX 4090', 'NVIDIA', 1599.99, 'gpu', 'https://static.hardwaredb.net/badges/geforce-rtx-4090.png'),
80('B650M DS3H', 'Gigabyte', 149.99, 'motherboard', 'https://images.seeklogo.com/logo-png/39/1/gigabyte-logo-png_seeklogo-398170.png'),
81('X670E AORUS Master', 'Gigabyte', 459.99, 'motherboard', 'https://images.seeklogo.com/logo-png/39/1/gigabyte-logo-png_seeklogo-398170.png'),
82('B760M Bomber WiFi', 'MSI', 129.99, 'motherboard', 'https://images.seeklogo.com/logo-png/30/1/msi-logo-png_seeklogo-304877.png'),
83('Z790 Maximus Hero', 'ASUS', 599.99, 'motherboard', 'https://cdn.brandfetch.io/idGnlhbTXH/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
84('Vengeance LPX 16GB', 'Corsair', 39.99, 'memory', 'https://cwsmgmt.corsair.com/press/CORSAIRLogo2020_stack_W.png'),
85('Ripjaws V 32GB', 'G.Skill', 69.99, 'memory', 'https://cdn.brandfetch.io/id8HgeVn1l/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
86('Trident Z5 RGB 32GB', 'G.Skill', 114.99, 'memory', 'https://cdn.brandfetch.io/id8HgeVn1l/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
87('Dominator Platinum 64GB', 'Corsair', 289.99, 'memory', 'https://cwsmgmt.corsair.com/press/CORSAIRLogo2020_stack_W.png'),
88('Crucial P3 1TB', 'Crucial', 64.99, 'storage', 'https://cdn.brandfetch.io/idcQwroMOv/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
89('WD Black SN850X 2TB', 'Western Digital', 159.99, 'storage', 'https://cdn.brandfetch.io/id6bAnMJ1y/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
90('Samsung 990 Pro 4TB', 'Samsung', 349.99, 'storage', 'https://cdn.brandfetch.io/iduaw_nOnR/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
91('H5 Flow', 'NZXT', 94.99, 'case', 'https://nzxt.com/cdn/shop/files/h5-flow-rgb-h5-flow-rgb-primary-lg.png?v=1744863470&width=2000'),
92('4000D Airflow', 'Corsair', 104.99, 'case', 'https://assets.corsair.com/image/upload/c_pad,q_auto,h_1024,w_1024,f_auto/products/Cases/base-4000d-airflow-config/Gallery/4000D_AF_BLACK_01.webp'),
93('O11 Dynamic Evo', 'Lian Li', 159.99, 'case', 'https://www.pbtech.co.nz/imgprod/C/H/CHALAN2074__1.jpg?h=2971221114'),
94('Versa H18', 'Thermaltake', 49.99, 'case', 'https://cdn.mwave.com.au/images/400/AC11503_2.jpg'),
95('Smart 500W', 'Thermaltake', 39.99, 'power_supply', 'https://cdn.brandfetch.io/idkwuFYTlH/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
96('RM750e', 'Corsair', 99.99, 'power_supply', 'https://cwsmgmt.corsair.com/press/CORSAIRLogo2020_stack_W.png'),
97('SuperNOVA 1000 GT', 'EVGA', 169.99, 'power_supply', 'https://cdn.brandfetch.io/iddPf9bbl3/w/400/h/400/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B'),
98('Peerless Assassin 120 SE', 'Thermalright', 33.90, 'cooler', 'https://cdn.brandfetch.io/id2Wov4r9a/w/339/h/339/theme/dark/icon.png?c=1dxbfHSJFAPEGdCLU4o5B'),
99('Kraken Elite 360', 'NZXT', 279.99, 'cooler', 'https://cdn.brandfetch.io/id6LxRitGO/w/1080/h/1080/theme/dark/icon.jpeg?c=1dxbfHSJFAPEGdCLU4o5B');
100
101INSERT INTO cpu (component_id, socket, cores, threads, base_clock, boost_clock, tdp) VALUES
102(1, 'AM4', 6, 12, 3.7, 4.6, 65),
103((SELECT id FROM components WHERE name='Ryzen 5 7600'), 'AM5', 6, 12, 3.8, 5.1, 65),
104((SELECT id FROM components WHERE name='Ryzen 7 7800X3D'), 'AM5', 8, 16, 4.2, 5.0, 120),
105((SELECT id FROM components WHERE name='Ryzen 9 7950X'), 'AM5', 16, 32, 4.5, 5.7, 170),
106((SELECT id FROM components WHERE name='Core i3-13100F'), 'LGA1700', 4, 8, 3.4, 4.5, 58),
107((SELECT id FROM components WHERE name='Core i5-13600K'), 'LGA1700', 14, 20, 3.5, 5.1, 125),
108((SELECT id FROM components WHERE name='Core i7-14700K'), 'LGA1700', 20, 28, 3.4, 5.6, 125),
109((SELECT id FROM components WHERE name='Core i9-14900KS'), 'LGA1700', 24, 32, 3.2, 6.2, 150);
110
111INSERT INTO gpu (component_id, vram, tdp, base_clock, boost_clock, chipset, length) VALUES
112(2, 12, 170, 1.32, 1.78, 'RTX 3060', 242),
113((SELECT id FROM components WHERE name='Radeon RX 6600'), 8, 132, 2.0, 2.4, 'RX 6600', 200),
114((SELECT id FROM components WHERE name='Radeon RX 7600'), 8, 165, 2.2, 2.6, 'RX 7600', 240),
115((SELECT id FROM components WHERE name='Radeon RX 7800 XT'), 16, 263, 2.1, 2.4, 'RX 7800 XT', 280),
116((SELECT id FROM components WHERE name='Radeon RX 7900 XTX'), 24, 355, 2.3, 2.5, 'RX 7900 XTX', 320),
117((SELECT id FROM components WHERE name='GeForce RTX 3050'), 8, 130, 1.5, 1.7, 'RTX 3050', 200),
118((SELECT id FROM components WHERE name='GeForce RTX 4060'), 8, 115, 1.8, 2.4, 'RTX 4060', 220),
119((SELECT id FROM components WHERE name='GeForce RTX 4070 Super'), 12, 220, 1.9, 2.5, 'RTX 4070 S', 260),
120((SELECT id FROM components WHERE name='GeForce RTX 4080 Super'), 16, 320, 2.2, 2.5, 'RTX 4080 S', 300),
121((SELECT id FROM components WHERE name='GeForce RTX 4090'), 24, 450, 2.2, 2.5, 'RTX 4090', 340);
122
123INSERT INTO motherboard (component_id, socket, chipset, form_factor, ram_type, num_ram_slots, max_ram_capacity, pci_express_slots) VALUES
124(7, 'AM4', 'B550', 'ATX', 'DDR4', 4, 128, 3),
125((SELECT id FROM components WHERE name='B650M DS3H'), 'AM5', 'B650', 'Micro-ATX', 'DDR5', 4, 128, 2),
126((SELECT id FROM components WHERE name='X670E AORUS Master'), 'AM5', 'X670E', 'ATX', 'DDR5', 4, 192, 3),
127((SELECT id FROM components WHERE name='B760M Bomber WiFi'), 'LGA1700', 'B760', 'Micro-ATX', 'DDR4', 2, 64, 1),
128((SELECT id FROM components WHERE name='Z790 Maximus Hero'), 'LGA1700', 'Z790', 'ATX', 'DDR5', 4, 192, 3);
129
130INSERT INTO memory (component_id, type, speed, capacity, modules) VALUES
131(3, 'DDR4', 3200, 16, 2),
132((SELECT id FROM components WHERE name='Vengeance LPX 16GB'), 'DDR4', 3200, 16, 2),
133((SELECT id FROM components WHERE name='Ripjaws V 32GB'), 'DDR4', 3600, 32, 2),
134((SELECT id FROM components WHERE name='Trident Z5 RGB 32GB'), 'DDR5', 6000, 32, 2),
135((SELECT id FROM components WHERE name='Dominator Platinum 64GB'), 'DDR5', 6400, 64, 2);
136
137INSERT INTO storage (component_id, type, capacity, form_factor) VALUES
138(8, 'NVMe', 1000, 'M.2'),
139((SELECT id FROM components WHERE name='Crucial P3 1TB'), 'NVMe', 1000, 'M.2'),
140((SELECT id FROM components WHERE name='WD Black SN850X 2TB'), 'NVMe', 2000, 'M.2'),
141((SELECT id FROM components WHERE name='Samsung 990 Pro 4TB'), 'NVMe', 4000, 'M.2');
142
143INSERT INTO pc_case (component_id, cooler_max_height, gpu_max_length) VALUES
144(5, 165, 300),
145((SELECT id FROM components WHERE name='H5 Flow'), 165, 365),
146((SELECT id FROM components WHERE name='4000D Airflow'), 170, 360),
147((SELECT id FROM components WHERE name='O11 Dynamic Evo'), 167, 422),
148((SELECT id FROM components WHERE name='Versa H18'), 155, 350);
149
150INSERT INTO case_mobo_form_factors (case_id, form_factor) VALUES
151(5, 'ATX'), (5, 'Micro-ATX');
152
153INSERT INTO case_mobo_form_factors (case_id, form_factor) VALUES
154 ((SELECT id FROM components WHERE name = 'H5 Flow'), 'ATX'),
155 ((SELECT id FROM components WHERE name = 'H5 Flow'), 'Micro-ATX'),
156 ((SELECT id FROM components WHERE name = '4000D Airflow'), 'ATX'),
157 ((SELECT id FROM components WHERE name = '4000D Airflow'), 'Micro-ATX'),
158 ((SELECT id FROM components WHERE name = 'O11 Dynamic Evo'), 'ATX'),
159 ((SELECT id FROM components WHERE name = 'O11 Dynamic Evo'), 'Micro-ATX'),
160 ((SELECT id FROM components WHERE name = 'Versa H18'), 'Micro-ATX');
161
162INSERT INTO case_storage_form_factors (case_id, form_factor, num_slots) VALUES
163 (5, 'M.2', 2),
164 ((SELECT id FROM components WHERE name = 'H5 Flow'), 'M.2', 3),
165 ((SELECT id FROM components WHERE name = '4000D Airflow'), 'M.2', 3),
166 ((SELECT id FROM components WHERE name = 'O11 Dynamic Evo'), 'M.2', 4),
167 ((SELECT id FROM components WHERE name = 'Versa H18'), 'M.2', 2);
168
169INSERT INTO power_supply (component_id, type, wattage, form_factor) VALUES
170(4, 'Modular', 650, 'ATX'),
171((SELECT id FROM components WHERE name='Smart 500W'), 'Non-Modular', 500, 'ATX'),
172((SELECT id FROM components WHERE name='RM750e'), 'Fully Modular', 750, 'ATX'),
173((SELECT id FROM components WHERE name='SuperNOVA 1000 GT'), 'Fully Modular', 1000, 'ATX');
174
175INSERT INTO cooler (component_id, type, height, max_tdp_supported) VALUES
176(6, 'Air', 158, 150),
177((SELECT id FROM components WHERE name='Peerless Assassin 120 SE'), 'Air', 155, 245),
178((SELECT id FROM components WHERE name='Kraken Elite 360'), 'Liquid', 55, 300);
179
180INSERT INTO cooler_cpu_sockets (cooler_id, socket) VALUES
181(6, 'AM4'), (6, 'AM5');
182
183INSERT INTO cooler_cpu_sockets (cooler_id, socket) VALUES
184 ((SELECT id FROM components WHERE name = 'Peerless Assassin 120 SE'), 'AM4'),
185 ((SELECT id FROM components WHERE name = 'Peerless Assassin 120 SE'), 'AM5'),
186 ((SELECT id FROM components WHERE name = 'Peerless Assassin 120 SE'), 'LGA1700');
187
188INSERT INTO cooler_cpu_sockets (cooler_id, socket) VALUES
189 ((SELECT id FROM components WHERE name = 'Kraken Elite 360'), 'AM4'),
190 ((SELECT id FROM components WHERE name = 'Kraken Elite 360'), 'AM5'),
191 ((SELECT id FROM components WHERE name = 'Kraken Elite 360'), 'LGA1700');
192
193INSERT INTO case_ps_form_factors (case_id, form_factor) VALUES
194 (5, 'ATX'),
195 ((SELECT id FROM components WHERE name = 'H5 Flow'), 'ATX'),
196 ((SELECT id FROM components WHERE name = '4000D Airflow'), 'ATX'),
197 ((SELECT id FROM components WHERE name = 'O11 Dynamic Evo'), 'ATX'),
198 ((SELECT id FROM components WHERE name = 'Versa H18'), 'ATX');
199
200INSERT INTO sound_card (component_id, sample_rate, bit_depth, chipset, interface, channel) VALUES (9, 192000, 24, 'SoundCore', 'PCIe', '7.1');
201INSERT INTO network_card (component_id, num_ports, speed, interface) VALUES (10, 2, 1000, 'PCIe');
202INSERT INTO network_adapter (component_id, wifi_version, interface, num_antennas) VALUES (11, 'WiFi 6', 'PCIe', 3);
203INSERT INTO optical_drive (component_id, form_factor, type, interface, write_speed, read_speed) VALUES (12, '5.25"', 'DVD-RW', 'SATA', 16, 16);
204INSERT INTO memory_card (component_id, num_slots, interface) VALUES (13, 1, '2.5');
205INSERT INTO cables (component_id, length_cm, type) VALUES (14, 50, 'SATA');
206
207INSERT INTO build (user_id, name, created_at, description, total_price, is_approved) VALUES
208(1, 'Gaming Build', CURRENT_DATE, 'Mid-range gaming PC', 1139.92, TRUE);
209INSERT INTO build_component (build_id, component_id) VALUES
210(1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8);
211
212INSERT INTO build (user_id, name, created_at, description, total_price, is_approved) VALUES
213((SELECT id FROM users WHERE username='budget_king'), 'Console Killer 2025', '2024-12-01', 'Cheap entry level gaming PC', 580.00, TRUE);
214INSERT INTO build_component (build_id, component_id) VALUES
215((SELECT id FROM build WHERE name='Console Killer 2025'), (SELECT id FROM components WHERE name='Core i3-13100F')),
216((SELECT id FROM build WHERE name='Console Killer 2025'), (SELECT id FROM components WHERE name='Radeon RX 6600')),
217((SELECT id FROM build WHERE name='Console Killer 2025'), (SELECT id FROM components WHERE name='B760M Bomber WiFi')),
218((SELECT id FROM build WHERE name='Console Killer 2025'), (SELECT id FROM components WHERE name='Vengeance LPX 16GB')),
219((SELECT id FROM build WHERE name='Console Killer 2025'), (SELECT id FROM components WHERE name='Crucial P3 1TB')),
220((SELECT id FROM build WHERE name='Console Killer 2025'), (SELECT id FROM components WHERE name='Smart 500W')),
221((SELECT id FROM build WHERE name='Console Killer 2025'), (SELECT id FROM components WHERE name='Versa H18'));
222
223INSERT INTO build (user_id, name, created_at, description, total_price, is_approved) VALUES
224((SELECT id FROM users WHERE username='streamer_pro'), 'Pro Streaming Rig', '2025-01-15', 'Handles OBS and 1440p gaming flawlessly', 2150.00, TRUE);
225INSERT INTO build_component (build_id, component_id) VALUES
226((SELECT id FROM build WHERE name='Pro Streaming Rig'), (SELECT id FROM components WHERE name='Core i7-14700K')),
227((SELECT id FROM build WHERE name='Pro Streaming Rig'), (SELECT id FROM components WHERE name='GeForce RTX 4080 Super')),
228((SELECT id FROM build WHERE name='Pro Streaming Rig'), (SELECT id FROM components WHERE name='Z790 Maximus Hero')),
229((SELECT id FROM build WHERE name='Pro Streaming Rig'), (SELECT id FROM components WHERE name='Trident Z5 RGB 32GB')),
230((SELECT id FROM build WHERE name='Pro Streaming Rig'), (SELECT id FROM components WHERE name='WD Black SN850X 2TB')),
231((SELECT id FROM build WHERE name='Pro Streaming Rig'), (SELECT id FROM components WHERE name='SuperNOVA 1000 GT')),
232((SELECT id FROM build WHERE name='Pro Streaming Rig'), (SELECT id FROM components WHERE name='H5 Flow')),
233((SELECT id FROM build WHERE name='Pro Streaming Rig'), (SELECT id FROM components WHERE name='Kraken Elite 360'));
234
235INSERT INTO build (user_id, name, created_at, description, total_price, is_approved) VALUES
236((SELECT id FROM users WHERE username='pc_wizard'), 'Team Red Value King', '2025-02-10', 'Pure rasterization performance', 1450.00, TRUE);
237INSERT INTO build_component (build_id, component_id) VALUES
238((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM components WHERE name='Ryzen 7 7800X3D')),
239((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM components WHERE name='Radeon RX 7800 XT')),
240((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM components WHERE name='B650M DS3H')),
241((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM components WHERE name='Trident Z5 RGB 32GB')),
242((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM components WHERE name='Crucial P3 1TB')),
243((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM components WHERE name='RM750e')),
244((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM components WHERE name='4000D Airflow')),
245((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM components WHERE name='Peerless Assassin 120 SE'));
246
247INSERT INTO build (user_id, name, created_at, description, total_price, is_approved) VALUES
248((SELECT id FROM users WHERE username='rgb_lover'), 'God Tier 4090 Build', '2025-03-01', 'Money is no object. 4K 144Hz Ultra.', 4200.00, TRUE);
249INSERT INTO build_component (build_id, component_id) VALUES
250((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM components WHERE name='Core i9-14900KS')),
251((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM components WHERE name='GeForce RTX 4090')),
252((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM components WHERE name='Z790 Maximus Hero')),
253((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM components WHERE name='Dominator Platinum 64GB')),
254((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM components WHERE name='Samsung 990 Pro 4TB')),
255((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM components WHERE name='SuperNOVA 1000 GT')),
256((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM components WHERE name='O11 Dynamic Evo')),
257((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM components WHERE name='Kraken Elite 360'));
258
259INSERT INTO build (user_id, name, created_at, description, total_price, is_approved) VALUES
260((SELECT id FROM users WHERE username='first_timer'), 'Snow White Build', '2024-11-20', 'First time building, went for looks', 1199.00, TRUE);
261INSERT INTO build_component (build_id, component_id) VALUES
262((SELECT id FROM build WHERE name='Snow White Build'), (SELECT id FROM components WHERE name='Ryzen 5 7600')),
263((SELECT id FROM build WHERE name='Snow White Build'), (SELECT id FROM components WHERE name='GeForce RTX 4070 Super')),
264((SELECT id FROM build WHERE name='Snow White Build'), (SELECT id FROM components WHERE name='B650M DS3H')),
265((SELECT id FROM build WHERE name='Snow White Build'), (SELECT id FROM components WHERE name='Trident Z5 RGB 32GB')),
266((SELECT id FROM build WHERE name='Snow White Build'), (SELECT id FROM components WHERE name='Crucial P3 1TB')),
267((SELECT id FROM build WHERE name='Snow White Build'), (SELECT id FROM components WHERE name='RM750e')),
268((SELECT id FROM build WHERE name='Snow White Build'), (SELECT id FROM components WHERE name='H5 Flow'));
269
270INSERT INTO build (user_id, name, created_at, description, total_price, is_approved) VALUES
271((SELECT id FROM users WHERE username='linux_fan'), 'Arch Linux Dev Box', '2024-10-15', 'Compilation beast, no Nvidia drivers needed', 920.00, TRUE);
272INSERT INTO build_component (build_id, component_id) VALUES
273((SELECT id FROM build WHERE name='Arch Linux Dev Box'), (SELECT id FROM components WHERE name='Core i5-13600K')),
274((SELECT id FROM build WHERE name='Arch Linux Dev Box'), (SELECT id FROM components WHERE name='Radeon RX 7600')),
275((SELECT id FROM build WHERE name='Arch Linux Dev Box'), (SELECT id FROM components WHERE name='B760M Bomber WiFi')),
276((SELECT id FROM build WHERE name='Arch Linux Dev Box'), (SELECT id FROM components WHERE name='Ripjaws V 32GB')),
277((SELECT id FROM build WHERE name='Arch Linux Dev Box'), (SELECT id FROM components WHERE name='Crucial P3 1TB')),
278((SELECT id FROM build WHERE name='Arch Linux Dev Box'), (SELECT id FROM components WHERE name='RM750e')),
279((SELECT id FROM build WHERE name='Arch Linux Dev Box'), (SELECT id FROM components WHERE name='Versa H18'));
280
281INSERT INTO build (user_id, name, created_at, description, total_price, is_approved) VALUES
282((SELECT id FROM users WHERE username='office_guy'), '1080p Gamer', '2025-01-05', 'Just for Fortnite and Apex', 810.00, TRUE);
283INSERT INTO build_component (build_id, component_id) VALUES
284((SELECT id FROM build WHERE name='1080p Gamer'), (SELECT id FROM components WHERE name='Core i3-13100F')),
285((SELECT id FROM build WHERE name='1080p Gamer'), (SELECT id FROM components WHERE name='GeForce RTX 4060')),
286((SELECT id FROM build WHERE name='1080p Gamer'), (SELECT id FROM components WHERE name='B760M Bomber WiFi')),
287((SELECT id FROM build WHERE name='1080p Gamer'), (SELECT id FROM components WHERE name='Vengeance LPX 16GB')),
288((SELECT id FROM build WHERE name='1080p Gamer'), (SELECT id FROM components WHERE name='Crucial P3 1TB')),
289((SELECT id FROM build WHERE name='1080p Gamer'), (SELECT id FROM components WHERE name='Smart 500W')),
290((SELECT id FROM build WHERE name='1080p Gamer'), (SELECT id FROM components WHERE name='Versa H18'));
291
292INSERT INTO build (user_id, name, created_at, description, total_price, is_approved) VALUES
293((SELECT id FROM users WHERE username='pc_wizard'), 'Silent Night', '2024-12-25', 'Zero RPM fan mode build', 2450.00, TRUE);
294INSERT INTO build_component (build_id, component_id) VALUES
295((SELECT id FROM build WHERE name='Silent Night'), (SELECT id FROM components WHERE name='Core i7-14700K')),
296((SELECT id FROM build WHERE name='Silent Night'), (SELECT id FROM components WHERE name='GeForce RTX 4080 Super')),
297((SELECT id FROM build WHERE name='Silent Night'), (SELECT id FROM components WHERE name='Z790 Maximus Hero')),
298((SELECT id FROM build WHERE name='Silent Night'), (SELECT id FROM components WHERE name='Trident Z5 RGB 32GB')),
299((SELECT id FROM build WHERE name='Silent Night'), (SELECT id FROM components WHERE name='WD Black SN850X 2TB')),
300((SELECT id FROM build WHERE name='Silent Night'), (SELECT id FROM components WHERE name='SuperNOVA 1000 GT')),
301((SELECT id FROM build WHERE name='Silent Night'), (SELECT id FROM components WHERE name='4000D Airflow')),
302((SELECT id FROM build WHERE name='Silent Night'), (SELECT id FROM components WHERE name='Peerless Assassin 120 SE'));
303
304INSERT INTO build (user_id, name, created_at, description, total_price, is_approved) VALUES
305((SELECT id FROM users WHERE username='rgb_lover'), 'Radeon Ultimate', '2025-03-10', '4K Gaming without Nvidia tax', 2850.00, TRUE);
306INSERT INTO build_component (build_id, component_id) VALUES
307((SELECT id FROM build WHERE name='Radeon Ultimate'), (SELECT id FROM components WHERE name='Ryzen 9 7950X')),
308((SELECT id FROM build WHERE name='Radeon Ultimate'), (SELECT id FROM components WHERE name='Radeon RX 7900 XTX')),
309((SELECT id FROM build WHERE name='Radeon Ultimate'), (SELECT id FROM components WHERE name='X670E AORUS Master')),
310((SELECT id FROM build WHERE name='Radeon Ultimate'), (SELECT id FROM components WHERE name='Dominator Platinum 64GB')),
311((SELECT id FROM build WHERE name='Radeon Ultimate'), (SELECT id FROM components WHERE name='Samsung 990 Pro 4TB')),
312((SELECT id FROM build WHERE name='Radeon Ultimate'), (SELECT id FROM components WHERE name='SuperNOVA 1000 GT')),
313((SELECT id FROM build WHERE name='Radeon Ultimate'), (SELECT id FROM components WHERE name='O11 Dynamic Evo')),
314((SELECT id FROM build WHERE name='Radeon Ultimate'), (SELECT id FROM components WHERE name='Kraken Elite 360'));
315
316INSERT INTO rating_build (build_id, user_id, value) VALUES (1, 2, 5);
317INSERT INTO review (build_id, user_id, content, created_at) VALUES (1, 2, 'Still runs everything in 2025!', CURRENT_DATE);
318
319INSERT INTO rating_build (build_id, user_id, value) VALUES
320((SELECT id FROM build WHERE name='Console Killer 2025'), (SELECT id FROM users WHERE username='rgb_lover'), 3),
321((SELECT id FROM build WHERE name='Console Killer 2025'), (SELECT id FROM users WHERE username='pc_wizard'), 4);
322INSERT INTO review (build_id, user_id, content, created_at) VALUES
323((SELECT id FROM build WHERE name='Console Killer 2025'), (SELECT id FROM users WHERE username='rgb_lover'), 'No RGB, 3 stars.', '2024-12-05');
324
325INSERT INTO rating_build (build_id, user_id, value) VALUES
326((SELECT id FROM build WHERE name='Pro Streaming Rig'), (SELECT id FROM users WHERE username='pc_wizard'), 5);
327
328INSERT INTO rating_build (build_id, user_id, value) VALUES
329((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM users WHERE username='budget_king'), 5),
330((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM users WHERE username='first_timer'), 4);
331INSERT INTO review (build_id, user_id, content, created_at) VALUES
332((SELECT id FROM build WHERE name='Team Red Value King'), (SELECT id FROM users WHERE username='budget_king'), 'Best FPS per dollar.', '2025-02-12');
333
334INSERT INTO rating_build (build_id, user_id, value) VALUES
335((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM users WHERE username='streamer_pro'), 5),
336((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM users WHERE username='budget_king'), 2);
337INSERT INTO review (build_id, user_id, content, created_at) VALUES
338((SELECT id FROM build WHERE name='God Tier 4090 Build'), (SELECT id FROM users WHERE username='budget_king'), 'Waaaaay too much money.', '2025-03-05');
339
340INSERT INTO rating_build (build_id, user_id, value) VALUES
341((SELECT id FROM build WHERE name='Snow White Build'), (SELECT id FROM users WHERE username='rgb_lover'), 5),
342((SELECT id FROM build WHERE name='Snow White Build'), (SELECT id FROM users WHERE username='office_guy'), 4);
343
344INSERT INTO rating_build (build_id, user_id, value) VALUES
345((SELECT id FROM build WHERE name='Arch Linux Dev Box'), (SELECT id FROM users WHERE username='linux_fan'), 5),
346((SELECT id FROM build WHERE name='Arch Linux Dev Box'), (SELECT id FROM users WHERE username='first_timer'), 1);
347INSERT INTO review (build_id, user_id, content, created_at) VALUES
348((SELECT id FROM build WHERE name='Arch Linux Dev Box'), (SELECT id FROM users WHERE username='first_timer'), 'Could not install Windows easily.', '2024-10-20');
349
350INSERT INTO rating_build (build_id, user_id, value) VALUES
351((SELECT id FROM build WHERE name='1080p Gamer'), (SELECT id FROM users WHERE username='streamer_pro'), 2),
352((SELECT id FROM build WHERE name='1080p Gamer'), (SELECT id FROM users WHERE username='budget_king'), 3);
353INSERT INTO review (build_id, user_id, content, created_at) VALUES
354((SELECT id FROM build WHERE name='1080p Gamer'), (SELECT id FROM users WHERE username='streamer_pro'), 'Stutters in Warzone.', '2025-01-10');
355
356INSERT INTO rating_build (build_id, user_id, value) VALUES
357((SELECT id FROM build WHERE name='Silent Night'), (SELECT id FROM users WHERE username='rgb_lover'), 2),
358((SELECT id FROM build WHERE name='Silent Night'), (SELECT id FROM users WHERE username='office_guy'), 5);
359
360INSERT INTO rating_build (build_id, user_id, value) VALUES
361((SELECT id FROM build WHERE name='Radeon Ultimate'), (SELECT id FROM users WHERE username='pc_wizard'), 5),
362((SELECT id FROM build WHERE name='Radeon Ultimate'), (SELECT id FROM users WHERE username='linux_fan'), 5);
363
364INSERT INTO suggestions (user_id, admin_id, link, admin_comment, description, status, component_type) VALUES
365(1, 4, 'https://www.nvidia.com/en-us/geforce/graphics-cards/40-series/rtx-4070-family/', NULL, 'Consider adding RTX 4070', 'pending', 'gpu');
366`
367
368const triggerSQL = `
369CREATE OR REPLACE FUNCTION update_build_total_price()
370RETURNS TRIGGER
371LANGUAGE plpgsql AS $$
372DECLARE
373 target_build_id INT;
374BEGIN
375 IF (TG_OP = 'DELETE') THEN
376 target_build_id := OLD.build_id;
377 ELSE
378 target_build_id := NEW.build_id;
379 END IF;
380
381 UPDATE "build"
382 SET "total_price" = (
383 SELECT COALESCE(SUM(c.price * bc.num_components), 0)
384 FROM "build_component" bc
385 JOIN "components" c ON bc.component_id = c.id
386 WHERE bc.build_id = target_build_id
387 )
388 WHERE id = target_build_id;
389
390 RETURN NULL;
391END;
392$$;
393
394DROP TRIGGER IF EXISTS trigger_auto_update_price ON "build_component";
395CREATE TRIGGER trigger_auto_update_price
396AFTER INSERT OR UPDATE OR DELETE ON "build_component"
397FOR EACH ROW
398EXECUTE FUNCTION update_build_total_price();
399
400
401
402CREATE OR REPLACE FUNCTION check_review_validity()
403RETURNS TRIGGER
404LANGUAGE plpgsql AS $$
405DECLARE
406 build_owner_id INT;
407BEGIN
408 SELECT user_id INTO build_owner_id FROM "build" WHERE id = NEW.build_id;
409
410 IF NEW.user_id = build_owner_id THEN
411 RAISE EXCEPTION 'Cannot review own builds.';
412 END IF;
413
414 RETURN NEW;
415END;
416$$;
417
418DROP TRIGGER IF EXISTS trigger_check_self_review ON "review";
419CREATE TRIGGER trigger_check_self_review
420BEFORE INSERT ON "review"
421FOR EACH ROW
422EXECUTE FUNCTION check_review_validity();
423
424
425
426CREATE OR REPLACE FUNCTION check_rating_validity()
427RETURNS TRIGGER
428LANGUAGE plpgsql AS $$
429DECLARE
430 build_owner_id INT;
431BEGIN
432 SELECT user_id INTO build_owner_id FROM "build" WHERE id = NEW.build_id;
433
434 IF NEW.user_id = build_owner_id THEN
435 RAISE EXCEPTION 'Cannot rate own builds.';
436 END IF;
437
438 RETURN NEW;
439END;
440$$;
441
442DROP TRIGGER IF EXISTS trigger_check_self_rating ON "rating_build";
443CREATE TRIGGER trigger_check_self_rating
444BEFORE INSERT ON "rating_build"
445FOR EACH ROW
446EXECUTE FUNCTION check_rating_validity();
447`
448
449const functionSQL = `
450CREATE OR REPLACE FUNCTION get_report_top_components()
451 RETURNS TABLE (
452 type TEXT,
453 brand TEXT,
454 name TEXT,
455 usage_count BIGINT,
456 avg_build_rating NUMERIC
457 )
458 LANGUAGE sql
459AS $$
460SELECT
461 c.type,
462 c.brand,
463 c.name,
464 COUNT(bc.component_id) AS usage_count,
465 AVG(rb.value) AS avg_build_rating
466FROM components c
467 JOIN build_component bc ON c.id = bc.component_id
468 JOIN build b ON bc.build_id = b.id
469 JOIN rating_build rb ON b.id = rb.build_id
470WHERE b.created_at >= CURRENT_DATE - INTERVAL '1 year'
471GROUP BY c.type, c.brand, c.name
472HAVING AVG(rb.value) >= 4.5
473ORDER BY usage_count DESC, avg_build_rating DESC
474LIMIT 15;
475$$;
476
477
478
479CREATE OR REPLACE FUNCTION get_report_user_reputation_leaderboard()
480 RETURNS TABLE (
481 username TEXT,
482 email TEXT,
483 approved_builds_count BIGINT,
484 total_favorites_received BIGINT,
485 avg_rating_received NUMERIC,
486 reputation_score NUMERIC
487 )
488 LANGUAGE sql
489AS $$
490WITH build_stats AS (
491 SELECT
492 b.id AS build_id,
493 b.user_id,
494 COUNT(DISTINCT fb.user_id) AS favorites_count,
495 AVG(rb.value) AS avg_rating
496 FROM build b
497 LEFT JOIN favorite_build fb ON b.id = fb.build_id
498 LEFT JOIN rating_build rb ON b.id = rb.build_id
499 WHERE b.is_approved = TRUE
500 GROUP BY b.id, b.user_id
501),
502 user_stats AS (
503 SELECT
504 user_id,
505 COUNT(build_id) AS approved_builds_count,
506 COALESCE(SUM(favorites_count), 0) AS total_favorites_received,
507 AVG(avg_rating) AS avg_rating_received
508 FROM build_stats
509 GROUP BY user_id
510 )
511SELECT
512 u.username,
513 u.email,
514 us.approved_builds_count,
515 us.total_favorites_received,
516 ROUND(CAST(COALESCE(us.avg_rating_received, 0) AS numeric), 2) AS avg_rating_received,
517 (
518 (us.approved_builds_count * 10) +
519 (us.total_favorites_received * 5) +
520 (COALESCE(us.avg_rating_received, 0) * 20)
521 ) AS reputation_score
522FROM user_stats us
523 JOIN users u ON u.id = us.user_id
524ORDER BY reputation_score DESC
525LIMIT 10;
526$$;
527
528
529
530CREATE OR REPLACE FUNCTION get_report_price_to_performance()
531 RETURNS TABLE (
532 build_name TEXT,
533 cpu_model TEXT,
534 gpu_model TEXT,
535 total_price NUMERIC,
536 performance_score NUMERIC,
537 price_to_performance_index NUMERIC
538 )
539 LANGUAGE sql
540AS $$
541WITH cpu_per_build AS (
542 SELECT
543 b.id AS build_id,
544 c.name AS cpu_model,
545 cpu.cores,
546 cpu.base_clock
547 FROM build b
548 JOIN build_component bc ON b.id = bc.build_id
549 JOIN components c ON bc.component_id = c.id
550 JOIN cpu ON c.id = cpu.component_id
551 WHERE LOWER(c.type) = LOWER('CPU')
552),
553 gpu_per_build AS (
554 SELECT
555 b.id AS build_id,
556 c.name AS gpu_model,
557 gpu.vram
558 FROM build b
559 JOIN build_component bc ON b.id = bc.build_id
560 JOIN components c ON bc.component_id = c.id
561 JOIN gpu ON c.id = gpu.component_id
562 WHERE LOWER(c.type) = LOWER('GPU')
563 )
564SELECT
565 b.name AS build_name,
566 cpu.cpu_model,
567 gpu.gpu_model,
568 b.total_price,
569 (cpu.cores * cpu.base_clock + gpu.vram * 100) AS performance_score,
570 ROUND(
571 CAST(
572 (cpu.cores * cpu.base_clock + gpu.vram * 100) / NULLIF(b.total_price, 0)
573 AS numeric),
574 4
575 ) AS price_to_performance_index
576FROM build b
577 JOIN cpu_per_build cpu ON b.id = cpu.build_id
578 JOIN gpu_per_build gpu ON b.id = gpu.build_id
579WHERE b.total_price > 0
580ORDER BY price_to_performance_index DESC
581LIMIT 20;
582$$;
583
584
585
586CREATE OR REPLACE FUNCTION get_report_budget_tier_popularity()
587 RETURNS TABLE (
588 price_tier TEXT,
589 builds_count BIGINT,
590 avg_favorites NUMERIC,
591 avg_rating NUMERIC,
592 unique_builders BIGINT,
593 engagement_score NUMERIC
594 )
595 LANGUAGE sql
596AS $$
597WITH price_tier_builds AS (
598 SELECT
599 b.id AS build_id,
600 b.user_id,
601 b.total_price,
602 CASE
603 WHEN b.total_price < 500 THEN 'Budget'
604 WHEN b.total_price < 1000 THEN 'Mid-Range'
605 WHEN b.total_price < 2000 THEN 'High-End'
606 ELSE 'Enthusiast'
607 END AS price_tier
608 FROM build b
609 WHERE b.is_approved = TRUE
610 AND b.created_at >= CURRENT_DATE - INTERVAL '6 months'
611),
612 favorites AS (
613 SELECT
614 build_id,
615 COUNT(DISTINCT user_id) AS favorites_count
616 FROM favorite_build
617 GROUP BY build_id
618 ),
619 engagement_stats AS (
620 SELECT
621 ptb.price_tier,
622 COUNT(DISTINCT ptb.build_id) AS builds_count,
623 AVG(COALESCE(f.favorites_count, 0)) AS avg_favorites,
624 AVG(rb.value) AS avg_rating,
625 COUNT(DISTINCT ptb.user_id) AS unique_builders
626 FROM price_tier_builds ptb
627 LEFT JOIN rating_build rb ON ptb.build_id = rb.build_id
628 LEFT JOIN favorites f ON ptb.build_id = f.build_id
629 GROUP BY ptb.price_tier
630 )
631SELECT
632 price_tier,
633 builds_count,
634 ROUND(CAST(avg_favorites AS numeric), 1) AS avg_favorites,
635 ROUND(CAST(COALESCE(avg_rating, 0) AS numeric), 2) AS avg_rating,
636 unique_builders,
637 ROUND(
638 CAST(
639 (builds_count * 2) +
640 (avg_favorites * 3) +
641 (COALESCE(avg_rating, 0) * 10) +
642 (unique_builders * 1.5)
643 AS numeric),
644 2
645 ) AS engagement_score
646FROM engagement_stats
647ORDER BY engagement_score DESC
648LIMIT 15;
649$$;
650
651
652
653CREATE OR REPLACE FUNCTION get_report_compatibility()
654 RETURNS TABLE (
655 cpu_combo TEXT,
656 motherboard_chipset TEXT,
657 total_builds BIGINT,
658 avg_satisfaction NUMERIC,
659 success_rate NUMERIC
660 )
661 LANGUAGE sql
662AS $$
663WITH cpu_mobo_pairs AS (
664 SELECT
665 cpu_comp.brand AS cpu_brand,
666 cpu_comp.name AS cpu_model,
667 mobo.chipset AS motherboard_chipset,
668 b.id AS build_id,
669 b.user_id,
670 b.created_at
671 FROM build b
672 JOIN build_component bc_cpu ON b.id = bc_cpu.build_id
673 JOIN components cpu_comp ON bc_cpu.component_id = cpu_comp.id
674 JOIN cpu ON cpu.component_id = cpu_comp.id
675 JOIN build_component bc_mobo ON b.id = bc_mobo.build_id
676 JOIN components mobo_comp ON bc_mobo.component_id = mobo_comp.id
677 JOIN motherboard mobo ON mobo.component_id = mobo_comp.id
678 WHERE b.is_approved = TRUE
679),
680 recent_activity AS (
681 SELECT DISTINCT build_id
682 FROM review
683 WHERE created_at >= CURRENT_DATE - INTERVAL '3 months'
684 ),
685 pair_metrics AS (
686 SELECT
687 cmp.cpu_brand,
688 cmp.cpu_model,
689 cmp.motherboard_chipset,
690 COUNT(DISTINCT cmp.build_id) AS total_builds,
691 AVG(COALESCE(rb.value, 0)) AS avg_satisfaction,
692 COUNT(DISTINCT ra.build_id) AS active_builds
693 FROM cpu_mobo_pairs cmp
694 LEFT JOIN rating_build rb ON cmp.build_id = rb.build_id
695 LEFT JOIN recent_activity ra ON cmp.build_id = ra.build_id
696 GROUP BY
697 cmp.cpu_brand,
698 cmp.cpu_model,
699 cmp.motherboard_chipset
700 HAVING COUNT(DISTINCT cmp.build_id) >= 2
701 )
702SELECT
703 CONCAT(cpu_brand, ' ', cpu_model) AS cpu_combo,
704 motherboard_chipset,
705 total_builds,
706 ROUND(CAST(avg_satisfaction AS numeric), 2) AS avg_satisfaction,
707 ROUND(
708 CAST(
709 (avg_satisfaction / 5.0) * 0.6 +
710 (CAST(active_builds AS DECIMAL) / total_builds) * 0.4
711 AS numeric),
712 3
713 ) AS success_rate
714FROM pair_metrics
715ORDER BY success_rate DESC, total_builds DESC
716LIMIT 15;
717$$;
718
719
720
721CREATE OR REPLACE FUNCTION get_report_storage_optimization()
722 RETURNS TABLE (
723 config_type TEXT,
724 ssd_brand TEXT,
725 builds_count BIGINT,
726 avg_storage_cost NUMERIC,
727 avg_total_capacity_gb NUMERIC,
728 avg_build_rating NUMERIC,
729 storage_cost_pct NUMERIC,
730 optimization_score NUMERIC
731 )
732 LANGUAGE sql
733AS $$
734WITH storage_configs AS (
735 SELECT
736 b.id AS build_id,
737 b.total_price,
738
739 ssd_comp.brand AS ssd_brand,
740 ssd_storage.capacity AS ssd_capacity,
741 ssd_comp.price AS ssd_price,
742
743 hdd_storage.capacity AS hdd_capacity,
744 hdd_comp.price AS hdd_price,
745
746 CASE
747 WHEN hdd_storage.capacity IS NULL THEN 'SSD-Only'
748 WHEN ssd_storage.capacity < 512 THEN 'SSD-Boot-HDD-Storage'
749 ELSE 'SSD-Primary-HDD-Archive'
750 END AS config_type
751 FROM build b
752 JOIN build_component bc_ssd ON b.id = bc_ssd.build_id
753 JOIN components ssd_comp ON bc_ssd.component_id = ssd_comp.id
754 JOIN storage ssd_storage ON ssd_storage.component_id = ssd_comp.id
755
756 LEFT JOIN build_component bc_hdd ON b.id = bc_hdd.build_id
757 LEFT JOIN components hdd_comp ON bc_hdd.component_id = hdd_comp.id
758 LEFT JOIN storage hdd_storage ON hdd_storage.component_id = hdd_comp.id
759
760 WHERE ssd_comp.type = 'storage'
761 AND b.is_approved = TRUE
762 AND b.created_at >= CURRENT_DATE - INTERVAL '1 year'
763
764 AND (
765 ssd_storage.type ILIKE '%nvme%'
766 OR ssd_storage.type ILIKE '%ssd%'
767 OR ssd_storage.type ILIKE '%m.2%'
768 OR ssd_storage.form_factor ILIKE '%m.2%'
769 )
770 AND (
771 hdd_storage.component_id IS NULL
772 OR hdd_storage.type ILIKE '%hdd%'
773 OR hdd_storage.form_factor ILIKE '%3.5%'
774 )
775),
776 config_performance AS (
777 SELECT
778 sc.config_type,
779 sc.ssd_brand,
780 COUNT(sc.build_id) AS builds_count,
781 AVG(sc.ssd_price + COALESCE(sc.hdd_price, 0)) AS avg_storage_cost,
782 AVG(sc.ssd_capacity + COALESCE(sc.hdd_capacity, 0)) AS avg_total_capacity,
783 AVG(rb.value) AS avg_build_rating,
784 AVG((sc.ssd_price + COALESCE(sc.hdd_price, 0)) / NULLIF(sc.total_price, 0)) AS storage_cost_ratio
785 FROM storage_configs sc
786 LEFT JOIN rating_build rb ON sc.build_id = rb.build_id
787 GROUP BY sc.config_type, sc.ssd_brand
788 HAVING COUNT(sc.build_id) >= 2
789 )
790SELECT
791 config_type,
792 ssd_brand,
793 builds_count,
794 ROUND(CAST(avg_storage_cost AS numeric), 2) AS avg_storage_cost,
795 ROUND(CAST(avg_total_capacity AS numeric), 0) AS avg_total_capacity_gb,
796 ROUND(CAST(COALESCE(avg_build_rating, 0) AS numeric), 2) AS avg_build_rating,
797 ROUND(CAST(COALESCE(storage_cost_ratio, 0) * 100 AS numeric), 1) AS storage_cost_pct,
798 ROUND(
799 CAST(
800 (COALESCE(avg_build_rating, 0) / 5.0 * 40) +
801 (avg_total_capacity / NULLIF(avg_storage_cost, 0) * 0.5) +
802 ((1 - COALESCE(storage_cost_ratio, 0)) * 30) +
803 (LN(CAST(builds_count + 1 AS numeric)) * 5)
804 AS numeric),
805 2
806 ) AS optimization_score
807FROM config_performance
808ORDER BY optimization_score DESC, builds_count DESC
809LIMIT 15;
810$$;
811`
812
813async function seed() {
814 if (!process.env.DATABASE_URL) {
815 throw new Error('DATABASE_URL environment variable not set');
816 }
817
818 const client = new pg.Client({
819 connectionString: process.env.DATABASE_URL,
820 });
821
822 try {
823 await client.connect();
824
825 console.log('Checking if database needs seeding...');
826
827 const result = await client.query('SELECT COUNT(*) as count FROM users');
828 const count = parseInt(result.rows[0].count);
829
830 if (count === 0) {
831 console.log('Seeding initial data...');
832 await client.query(dataSQL);
833 console.log('Data seeded successfully.');
834 } else {
835 console.log('Database already contains data, skipping data seed.');
836 }
837
838 console.log('Applying Database Triggers...');
839 await client.query(triggerSQL);
840
841 console.log('Applying Database Functions...');
842 await client.query(functionSQL);
843
844 console.log('Database seeded successfully...');
845 } catch (error) {
846 console.error('Seed failed:', error);
847 throw error;
848 } finally {
849 await client.end();
850 }
851}
852
853seed().catch((err) => {
854 console.error(err);
855 process.exit(1);
856});
Note: See TracBrowser for help on using the repository browser.