Index: database/drizzle/queries/components.ts =================================================================== --- database/drizzle/queries/components.ts (revision adc31efc1b4507b5b3a779e6190ce32df4c108a5) +++ database/drizzle/queries/components.ts (revision 546a19403d89dc82739b1540b4eac3597ec29423) @@ -104,5 +104,5 @@ } -export async function getDetailsForNewComponent(componentType: string) { +export async function getDetailsNewComponent(componentType: string) { const config = typeConfigMap[componentType as ComponentType]; @@ -1067,16 +1067,16 @@ ); - const totalPrice = buildComponents.reduce((sum, c) => - sum + (Number(c.price) * c.quantity), 0 - ); - - await tx - .update(buildsTable) - .set({ - totalPrice: totalPrice.toFixed(2) - }) - .where( - eq(buildsTable.id, buildId) - ); + // const totalPrice = buildComponents.reduce((sum, c) => + // sum + (Number(c.price) * c.quantity), 0 + // ); + + // await tx + // .update(buildsTable) + // .set({ + // totalPrice: totalPrice.toFixed(2) + // }) + // .where( + // eq(buildsTable.id, buildId) + // ); return buildId; @@ -1151,16 +1151,16 @@ ); - const totalPrice = buildComponents.reduce((sum, c) => - sum + (Number(c.price) * c.quantity), 0 - ); - - await tx - .update(buildsTable) - .set({ - totalPrice: totalPrice.toFixed(2) - }) - .where( - eq(buildsTable.id, buildId) - ); + // const totalPrice = buildComponents.reduce((sum, c) => + // sum + (Number(c.price) * c.quantity), 0 + // ); + + // await tx + // .update(buildsTable) + // .set({ + // totalPrice: totalPrice.toFixed(2) + // }) + // .where( + // eq(buildsTable.id, buildId) + // ); return componentId; Index: database/drizzle/util/reports.ts =================================================================== --- database/drizzle/util/reports.ts (revision 546a19403d89dc82739b1540b4eac3597ec29423) +++ database/drizzle/util/reports.ts (revision 546a19403d89dc82739b1540b4eac3597ec29423) @@ -0,0 +1,40 @@ +import 'dotenv/config'; +import { db } from '../db'; +import { sql } from 'drizzle-orm'; + +async function main() { + console.log("Generating Reports . . ."); + + try { + const r1 = await db.execute(sql`SELECT * FROM get_report_top_components()`); + console.log('--- Top Performing Components ---'); + console.table(r1.rows); + + const r2 = await db.execute(sql`SELECT * FROM get_report_user_leaderboard()`); + console.log('--- User Reputation Leaderboard ---'); + console.table(r2.rows); + + const r3 = await db.execute(sql`SELECT * FROM get_report_price_performance()`); + console.log('--- Price-to-Performance Analysis ---'); + console.table(r3.rows); + + const r4 = await db.execute(sql`SELECT * FROM get_report_budget_tier()`); + console.log('--- Budget Tier Popularity ---'); + console.table(r4.rows); + + const r5 = await db.execute(sql`SELECT * FROM get_report_compatibility()`); + console.log('--- Component Compatibility ---'); + console.table(r5.rows); + + const r6 = await db.execute(sql`SELECT * FROM get_report_storage_optimization()`); + console.log('--- Storage Optimization ---'); + console.table(r6.rows); + + process.exit(0); + } catch (e) { + console.error(e); + process.exit(1); + } +} + +main(); Index: database/drizzle/util/seed.ts =================================================================== --- database/drizzle/util/seed.ts (revision adc31efc1b4507b5b3a779e6190ce32df4c108a5) +++ database/drizzle/util/seed.ts (revision 546a19403d89dc82739b1540b4eac3597ec29423) @@ -1,5 +1,5 @@ import pg from 'pg'; -const seedSQL = ` +const dataSQL = ` TRUNCATE TABLE suggestions, @@ -366,5 +366,450 @@ ` -async function main() { +const triggerSQL = ` +CREATE OR REPLACE FUNCTION update_build_total_price() +RETURNS TRIGGER +LANGUAGE plpgsql AS $$ +DECLARE + target_build_id INT; +BEGIN + IF (TG_OP = 'DELETE') THEN + target_build_id := OLD.build_id; + ELSE + target_build_id := NEW.build_id; + END IF; + + UPDATE "build" + SET "total_price" = ( + SELECT COALESCE(SUM(c.price * bc.num_components), 0) + FROM "build_component" bc + JOIN "components" c ON bc.component_id = c.id + WHERE bc.build_id = target_build_id + ) + WHERE id = target_build_id; + + RETURN NULL; +END; +$$; + +DROP TRIGGER IF EXISTS trigger_auto_update_price ON "build_component"; +CREATE TRIGGER trigger_auto_update_price +AFTER INSERT OR UPDATE OR DELETE ON "build_component" +FOR EACH ROW +EXECUTE FUNCTION update_build_total_price(); + + + +CREATE OR REPLACE FUNCTION check_review_validity() +RETURNS TRIGGER +LANGUAGE plpgsql AS $$ +DECLARE + build_owner_id INT; +BEGIN + SELECT user_id INTO build_owner_id FROM "build" WHERE id = NEW.build_id; + + IF NEW.user_id = build_owner_id THEN + RAISE EXCEPTION 'Cannot review own builds.'; + END IF; + + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trigger_check_self_review ON "review"; +CREATE TRIGGER trigger_check_self_review +BEFORE INSERT ON "review" +FOR EACH ROW +EXECUTE FUNCTION check_review_validity(); + + + +CREATE OR REPLACE FUNCTION check_rating_validity() +RETURNS TRIGGER +LANGUAGE plpgsql AS $$ +DECLARE + build_owner_id INT; +BEGIN + SELECT user_id INTO build_owner_id FROM "build" WHERE id = NEW.build_id; + + IF NEW.user_id = build_owner_id THEN + RAISE EXCEPTION 'Cannot rate own builds.'; + END IF; + + RETURN NEW; +END; +$$; + +DROP TRIGGER IF EXISTS trigger_check_self_rating ON "rating_build"; +CREATE TRIGGER trigger_check_self_rating +BEFORE INSERT ON "rating_build" +FOR EACH ROW +EXECUTE FUNCTION check_rating_validity(); +` + +const functionSQL = ` +CREATE OR REPLACE FUNCTION get_report_top_components() + RETURNS TABLE ( + type TEXT, + brand TEXT, + name TEXT, + usage_count BIGINT, + avg_build_rating NUMERIC + ) + LANGUAGE sql +AS $$ +SELECT + c.type, + c.brand, + c.name, + COUNT(bc.component_id) AS usage_count, + AVG(rb.value) AS avg_build_rating +FROM components c + JOIN build_component bc ON c.id = bc.component_id + JOIN build b ON bc.build_id = b.id + JOIN rating_build rb ON b.id = rb.build_id +WHERE b.created_at >= CURRENT_DATE - INTERVAL '1 year' +GROUP BY c.type, c.brand, c.name +HAVING AVG(rb.value) >= 4.5 +ORDER BY usage_count DESC, avg_build_rating DESC +LIMIT 15; +$$; + + + +CREATE OR REPLACE FUNCTION get_report_user_reputation_leaderboard() + RETURNS TABLE ( + username TEXT, + email TEXT, + approved_builds_count BIGINT, + total_favorites_received BIGINT, + avg_rating_received NUMERIC, + reputation_score NUMERIC + ) + LANGUAGE sql +AS $$ +WITH build_stats AS ( + SELECT + b.id AS build_id, + b.user_id, + COUNT(DISTINCT fb.user_id) AS favorites_count, + AVG(rb.value) AS avg_rating + FROM build b + LEFT JOIN favorite_build fb ON b.id = fb.build_id + LEFT JOIN rating_build rb ON b.id = rb.build_id + WHERE b.is_approved = TRUE + GROUP BY b.id, b.user_id +), + user_stats AS ( + SELECT + user_id, + COUNT(build_id) AS approved_builds_count, + COALESCE(SUM(favorites_count), 0) AS total_favorites_received, + AVG(avg_rating) AS avg_rating_received + FROM build_stats + GROUP BY user_id + ) +SELECT + u.username, + u.email, + us.approved_builds_count, + us.total_favorites_received, + ROUND(CAST(COALESCE(us.avg_rating_received, 0) AS numeric), 2) AS avg_rating_received, + ( + (us.approved_builds_count * 10) + + (us.total_favorites_received * 5) + + (COALESCE(us.avg_rating_received, 0) * 20) + ) AS reputation_score +FROM user_stats us + JOIN users u ON u.id = us.user_id +ORDER BY reputation_score DESC +LIMIT 10; +$$; + + + +CREATE OR REPLACE FUNCTION get_report_price_to_performance() + RETURNS TABLE ( + build_name TEXT, + cpu_model TEXT, + gpu_model TEXT, + total_price NUMERIC, + performance_score NUMERIC, + price_to_performance_index NUMERIC + ) + LANGUAGE sql +AS $$ +WITH cpu_per_build AS ( + SELECT + b.id AS build_id, + c.name AS cpu_model, + cpu.cores, + cpu.base_clock + FROM build b + JOIN build_component bc ON b.id = bc.build_id + JOIN components c ON bc.component_id = c.id + JOIN cpu ON c.id = cpu.component_id + WHERE LOWER(c.type) = LOWER('CPU') +), + gpu_per_build AS ( + SELECT + b.id AS build_id, + c.name AS gpu_model, + gpu.vram + FROM build b + JOIN build_component bc ON b.id = bc.build_id + JOIN components c ON bc.component_id = c.id + JOIN gpu ON c.id = gpu.component_id + WHERE LOWER(c.type) = LOWER('GPU') + ) +SELECT + b.name AS build_name, + cpu.cpu_model, + gpu.gpu_model, + b.total_price, + (cpu.cores * cpu.base_clock + gpu.vram * 100) AS performance_score, + ROUND( + CAST( + (cpu.cores * cpu.base_clock + gpu.vram * 100) / NULLIF(b.total_price, 0) + AS numeric), + 4 + ) AS price_to_performance_index +FROM build b + JOIN cpu_per_build cpu ON b.id = cpu.build_id + JOIN gpu_per_build gpu ON b.id = gpu.build_id +WHERE b.total_price > 0 +ORDER BY price_to_performance_index DESC +LIMIT 20; +$$; + + + +CREATE OR REPLACE FUNCTION get_report_budget_tier_popularity() + RETURNS TABLE ( + price_tier TEXT, + builds_count BIGINT, + avg_favorites NUMERIC, + avg_rating NUMERIC, + unique_builders BIGINT, + engagement_score NUMERIC + ) + LANGUAGE sql +AS $$ +WITH price_tier_builds AS ( + SELECT + b.id AS build_id, + b.user_id, + b.total_price, + CASE + WHEN b.total_price < 500 THEN 'Budget' + WHEN b.total_price < 1000 THEN 'Mid-Range' + WHEN b.total_price < 2000 THEN 'High-End' + ELSE 'Enthusiast' + END AS price_tier + FROM build b + WHERE b.is_approved = TRUE + AND b.created_at >= CURRENT_DATE - INTERVAL '6 months' +), + favorites AS ( + SELECT + build_id, + COUNT(DISTINCT user_id) AS favorites_count + FROM favorite_build + GROUP BY build_id + ), + engagement_stats AS ( + SELECT + ptb.price_tier, + COUNT(DISTINCT ptb.build_id) AS builds_count, + AVG(COALESCE(f.favorites_count, 0)) AS avg_favorites, + AVG(rb.value) AS avg_rating, + COUNT(DISTINCT ptb.user_id) AS unique_builders + FROM price_tier_builds ptb + LEFT JOIN rating_build rb ON ptb.build_id = rb.build_id + LEFT JOIN favorites f ON ptb.build_id = f.build_id + GROUP BY ptb.price_tier + ) +SELECT + price_tier, + builds_count, + ROUND(CAST(avg_favorites AS numeric), 1) AS avg_favorites, + ROUND(CAST(COALESCE(avg_rating, 0) AS numeric), 2) AS avg_rating, + unique_builders, + ROUND( + CAST( + (builds_count * 2) + + (avg_favorites * 3) + + (COALESCE(avg_rating, 0) * 10) + + (unique_builders * 1.5) + AS numeric), + 2 + ) AS engagement_score +FROM engagement_stats +ORDER BY engagement_score DESC +LIMIT 15; +$$; + + + +CREATE OR REPLACE FUNCTION get_report_compatibility() + RETURNS TABLE ( + cpu_combo TEXT, + motherboard_chipset TEXT, + total_builds BIGINT, + avg_satisfaction NUMERIC, + success_rate NUMERIC + ) + LANGUAGE sql +AS $$ +WITH cpu_mobo_pairs AS ( + SELECT + cpu_comp.brand AS cpu_brand, + cpu_comp.name AS cpu_model, + mobo.chipset AS motherboard_chipset, + b.id AS build_id, + b.user_id, + b.created_at + FROM build b + JOIN build_component bc_cpu ON b.id = bc_cpu.build_id + JOIN components cpu_comp ON bc_cpu.component_id = cpu_comp.id + JOIN cpu ON cpu.component_id = cpu_comp.id + JOIN build_component bc_mobo ON b.id = bc_mobo.build_id + JOIN components mobo_comp ON bc_mobo.component_id = mobo_comp.id + JOIN motherboard mobo ON mobo.component_id = mobo_comp.id + WHERE b.is_approved = TRUE +), + recent_activity AS ( + SELECT DISTINCT build_id + FROM review + WHERE created_at >= CURRENT_DATE - INTERVAL '3 months' + ), + pair_metrics AS ( + SELECT + cmp.cpu_brand, + cmp.cpu_model, + cmp.motherboard_chipset, + COUNT(DISTINCT cmp.build_id) AS total_builds, + AVG(COALESCE(rb.value, 0)) AS avg_satisfaction, + COUNT(DISTINCT ra.build_id) AS active_builds + FROM cpu_mobo_pairs cmp + LEFT JOIN rating_build rb ON cmp.build_id = rb.build_id + LEFT JOIN recent_activity ra ON cmp.build_id = ra.build_id + GROUP BY + cmp.cpu_brand, + cmp.cpu_model, + cmp.motherboard_chipset + HAVING COUNT(DISTINCT cmp.build_id) >= 2 + ) +SELECT + CONCAT(cpu_brand, ' ', cpu_model) AS cpu_combo, + motherboard_chipset, + total_builds, + ROUND(CAST(avg_satisfaction AS numeric), 2) AS avg_satisfaction, + ROUND( + CAST( + (avg_satisfaction / 5.0) * 0.6 + + (CAST(active_builds AS DECIMAL) / total_builds) * 0.4 + AS numeric), + 3 + ) AS success_rate +FROM pair_metrics +ORDER BY success_rate DESC, total_builds DESC +LIMIT 15; +$$; + + + +CREATE OR REPLACE FUNCTION get_report_storage_optimization() + RETURNS TABLE ( + config_type TEXT, + ssd_brand TEXT, + builds_count BIGINT, + avg_storage_cost NUMERIC, + avg_total_capacity_gb NUMERIC, + avg_build_rating NUMERIC, + storage_cost_pct NUMERIC, + optimization_score NUMERIC + ) + LANGUAGE sql +AS $$ +WITH storage_configs AS ( + SELECT + b.id AS build_id, + b.total_price, + + ssd_comp.brand AS ssd_brand, + ssd_storage.capacity AS ssd_capacity, + ssd_comp.price AS ssd_price, + + hdd_storage.capacity AS hdd_capacity, + hdd_comp.price AS hdd_price, + + CASE + WHEN hdd_storage.capacity IS NULL THEN 'SSD-Only' + WHEN ssd_storage.capacity < 512 THEN 'SSD-Boot-HDD-Storage' + ELSE 'SSD-Primary-HDD-Archive' + END AS config_type + FROM build b + JOIN build_component bc_ssd ON b.id = bc_ssd.build_id + JOIN components ssd_comp ON bc_ssd.component_id = ssd_comp.id + JOIN storage ssd_storage ON ssd_storage.component_id = ssd_comp.id + + LEFT JOIN build_component bc_hdd ON b.id = bc_hdd.build_id + LEFT JOIN components hdd_comp ON bc_hdd.component_id = hdd_comp.id + LEFT JOIN storage hdd_storage ON hdd_storage.component_id = hdd_comp.id + + WHERE ssd_comp.type = 'storage' + AND b.is_approved = TRUE + AND b.created_at >= CURRENT_DATE - INTERVAL '1 year' + + AND ( + ssd_storage.type ILIKE '%nvme%' + OR ssd_storage.type ILIKE '%ssd%' + OR ssd_storage.type ILIKE '%m.2%' + OR ssd_storage.form_factor ILIKE '%m.2%' + ) + AND ( + hdd_storage.component_id IS NULL + OR hdd_storage.type ILIKE '%hdd%' + OR hdd_storage.form_factor ILIKE '%3.5%' + ) +), + config_performance AS ( + SELECT + sc.config_type, + sc.ssd_brand, + COUNT(sc.build_id) AS builds_count, + AVG(sc.ssd_price + COALESCE(sc.hdd_price, 0)) AS avg_storage_cost, + AVG(sc.ssd_capacity + COALESCE(sc.hdd_capacity, 0)) AS avg_total_capacity, + AVG(rb.value) AS avg_build_rating, + AVG((sc.ssd_price + COALESCE(sc.hdd_price, 0)) / NULLIF(sc.total_price, 0)) AS storage_cost_ratio + FROM storage_configs sc + LEFT JOIN rating_build rb ON sc.build_id = rb.build_id + GROUP BY sc.config_type, sc.ssd_brand + HAVING COUNT(sc.build_id) >= 2 + ) +SELECT + config_type, + ssd_brand, + builds_count, + ROUND(CAST(avg_storage_cost AS numeric), 2) AS avg_storage_cost, + ROUND(CAST(avg_total_capacity AS numeric), 0) AS avg_total_capacity_gb, + ROUND(CAST(COALESCE(avg_build_rating, 0) AS numeric), 2) AS avg_build_rating, + ROUND(CAST(COALESCE(storage_cost_ratio, 0) * 100 AS numeric), 1) AS storage_cost_pct, + ROUND( + CAST( + (COALESCE(avg_build_rating, 0) / 5.0 * 40) + + (avg_total_capacity / NULLIF(avg_storage_cost, 0) * 0.5) + + ((1 - COALESCE(storage_cost_ratio, 0)) * 30) + + (LN(CAST(builds_count + 1 AS numeric)) * 5) + AS numeric), + 2 + ) AS optimization_score +FROM config_performance +ORDER BY optimization_score DESC, builds_count DESC +LIMIT 15; +$$; +` + +async function seed() { if (!process.env.DATABASE_URL) { throw new Error('DATABASE_URL environment variable not set'); @@ -383,12 +828,17 @@ const count = parseInt(result.rows[0].count); - if (count > 0) { - console.log('Database already seeded, skipping...'); - return; + if (count === 0) { + console.log('Seeding initial data...'); + await client.query(dataSQL); + console.log('Data seeded successfully.'); + } else { + console.log('Database already contains data, skipping data seed.'); } - console.log('Seeding database...'); - - await client.query(seedSQL); + console.log('Applying Database Triggers...'); + await client.query(triggerSQL); + + console.log('Applying Database Functions...'); + await client.query(functionSQL); console.log('Database seeded successfully...'); @@ -401,5 +851,5 @@ } -main().catch((err) => { +seed().catch((err) => { console.error(err); process.exit(1);