Changeset b348db4 for database/drizzle/util/seed.ts

Timestamp:

01/29/26 03:53:57 (5 months ago)

Author:

Tome <gjorgievtome@…>

Branches:

main

Children:

c586cbd, f727252

Parents:

be22289

Message:

Add stored functions and triggers

File:

• database/drizzle/util/seed.ts (modified) (4 diffs)

database/drizzle/util/seed.ts

@@ -1 +1 @@
 import pg from 'pg';
 
-const seedSQL = `
+const dataSQL = `
 TRUNCATE TABLE
     suggestions,
@@ -366 +366 @@
 `
 
-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 +828 @@
         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 +851 @@
 }
 
-main().catch((err) => {
+seed().catch((err) => {
     console.error(err);
     process.exit(1);