Changeset ff3a614

Timestamp:

03/05/26 11:31:19 (4 months ago)

Author:

Tome <gjorgievtome@…>

Branches:

main

Parents:

41825d5

Message:

Fix SQL functions for reporting and update queries in reports

Location:

database

Files:

• drizzle/util/reports.ts (modified) (1 diff)
• drizzle/util/seed.ts (modified) (1 diff)
• migrations/0000_colorful_scream.sql (modified) (1 diff)

database/drizzle/util/reports.ts

@@ -11 +11 @@
         console.table(r1.rows);
 
-        const r2 = await db.execute(sql`SELECT * FROM get_report_user_leaderboard()`);
+        const r2 = await db.execute(sql`SELECT * FROM get_report_user_reputation_leaderboard()`);
         console.log('--- User Reputation Leaderboard ---');
         console.table(r2.rows);
 
-        const r3 = await db.execute(sql`SELECT * FROM get_report_price_performance()`);
+        const r3 = await db.execute(sql`SELECT * FROM get_report_price_to_performance()`);
         console.log('--- Price-to-Performance Analysis ---');
         console.table(r3.rows);
 
-        const r4 = await db.execute(sql`SELECT * FROM get_report_budget_tier()`);
+        const r4 = await db.execute(sql`SELECT * FROM get_report_budget_tier_popularity()`);
         console.log('--- Budget Tier Popularity ---');
         console.table(r4.rows);

database/drizzle/util/seed.ts

@@ +1 @@
+import "dotenv/config";
 import pg from 'pg';
 

database/migrations/0000_colorful_scream.sql

@@ -242 +242 @@
 ALTER TABLE "suggestions" ADD CONSTRAINT "suggestions_user_id_users_id_fk" FOREIGN KEY ("user_id") REFERENCES "public"."users"("id") ON DELETE cascade ON UPDATE cascade;--> statement-breakpoint
 ALTER TABLE "suggestions" ADD CONSTRAINT "suggestions_admin_id_admins_user_id_fk" FOREIGN KEY ("admin_id") REFERENCES "public"."admins"("user_id") ON DELETE set null ON UPDATE cascade;
+
+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;
+$$;