Views

1) View for Approved Builds Page

Purpose:
-Shows all the approved builds built by users, sorted by price by default
-Calculates average ratings for each build, so the user can compare the builds with that metric.
-Includes all data needed for filtering (name search using the build names) and sorting (by price, rating and date).

CREATE OR REPLACE VIEW v_approved_builds_browse AS
SELECT 
    b.id,
    b.user_id,
    b.name,
    b.created_at,
    b.total_price,
    u.username AS creator_username,
    COUNT(DISTINCT fb.user_id) AS favorite_count,
    COUNT(DISTINCT rb.user_id) AS total_ratings,
    COALESCE(AVG(rb.value::float), 0) AS avg_rating,
    COUNT(DISTINCT r.id) AS review_count
FROM build b
JOIN users u ON u.id = b.user_id
LEFT JOIN favorite_build fb ON fb.build_id = b.id
LEFT JOIN rating_build rb ON rb.build_id = b.id
LEFT JOIN review r ON r.build_id = b.id
WHERE b.is_approved = TRUE
GROUP BY b.id, b.user_id, b.name, b.created_at, b.total_price, u.username;

2) View for User Dashboard

Purpose:
-Shows the user's created builds with their metrics
-Shows the user's favorited builds from other creators
-Combines data from both queries to one view
-Includes average ratings for all the builds displayed

CREATE OR REPLACE VIEW v_user_dashboard_data AS
WITH user_created_builds AS (
    SELECT 
        b.id AS build_id,
        b.user_id AS dashboard_user_id,
        b.user_id AS creator_id,
        u.username AS creator_username,
        b.name,
        b.created_at,
        b.total_price,
        b.is_approved,
        'created' AS build_source,
        COALESCE(AVG(rb.value::float), 0) AS avg_rating,
        COUNT(DISTINCT rb.user_id) AS rating_count
    FROM build b
    JOIN users u ON u.id = b.user_id
    LEFT JOIN rating_build rb ON rb.build_id = b.id
    GROUP BY b.id, b.user_id, u.username, b.name, b.created_at, b.total_price, b.is_approved
),
user_favorited_builds AS (
    SELECT 
        b.id AS build_id,
        fb.user_id AS dashboard_user_id,
        b.user_id AS creator_id,
        u.username AS creator_username,
        b.name,
        b.created_at,
        b.total_price,
        b.is_approved,
        'favorited' AS build_source,
        COALESCE(AVG(rb.value::float), 0) AS avg_rating,
        COUNT(DISTINCT rb.user_id) AS rating_count
    FROM favorite_build fb
    JOIN build b ON b.id = fb.build_id
    JOIN users u ON u.id = b.user_id
    LEFT JOIN rating_build rb ON rb.build_id = b.id
    GROUP BY b.id, fb.user_id, b.user_id, u.username, b.name, b.created_at, b.total_price, b.is_approved
)
SELECT * FROM user_created_builds
UNION ALL
SELECT * FROM user_favorited_builds;

3) View for Build Details Page

Purpose:
-Pre-joins build with the creator's username
-Aggregates rating statistics
-Counts reviews and favorites for the build

CREATE OR REPLACE VIEW v_build_details_summary AS
SELECT 
    b.id AS build_id,
    b.user_id,
    b.name,
    b.created_at,
    b.description,
    b.total_price,
    b.is_approved,
    u.username AS creator_username,
    COALESCE(AVG(rb.value::float), 0) AS average_rating,
    COUNT(DISTINCT rb.user_id) AS rating_count,
    COUNT(DISTINCT r.id) AS review_count,
    COUNT(DISTINCT fb.user_id) AS favorite_count
FROM build b
JOIN users u ON u.id = b.user_id
LEFT JOIN rating_build rb ON rb.build_id = b.id
LEFT JOIN review r ON r.build_id = b.id
LEFT JOIN favorite_build fb ON fb.build_id = b.id
GROUP BY b.id, b.user_id, b.name, b.created_at, b.description, 
         b.total_price, b.is_approved, u.username;

4) View for Admin Dashboard

Purpose:
-Shows component and build suggestions waiting for approval or rejection
-Shows user information for both builds and components
-Provides data for the admin verification queue

CREATE OR REPLACE VIEW v_admin_pending_items AS
WITH pending_builds AS (
    SELECT 
        b.id,
        'build' AS item_type,
        b.user_id,
        u.username,
        u.email,
        b.name AS item_name,
        b.description,
        b.created_at AS submitted_date,
        b.total_price,
        NULL AS component_type,
        NULL AS link,
        'pending' AS status
    FROM build b
    JOIN users u ON u.id = b.user_id
    WHERE b.is_approved = FALSE
),
pending_suggestions AS (
    SELECT 
        s.id,
        'suggestion' AS item_type,
        s.user_id,
        u.username,
        u.email,
        s.component_type AS item_name,
        s.description,
        NULL::DATE AS submitted_date,
        NULL::NUMERIC AS total_price,
        s.component_type,
        s.link,
        s.status
    FROM suggestions s
    JOIN users u ON u.id = s.user_id
    WHERE s.status = 'pending'
)
SELECT * FROM pending_builds
UNION ALL
SELECT * FROM pending_suggestions
ORDER BY item_type, id DESC;