DatabaseCreation: views.sql

-- преглед на сите активни продукти
CREATE OR REPLACE VIEW vw_active_products AS 
SELECT 
        p.product_id,
    p.title,
    p.price,
    p.currency,
        
    CASE
        WHEN p.quantity <= 5 THEN 1
            ELSE 0
        END AS low_stock,
        
    ( SELECT pi.image_url
         FROM productimages pi
         WHERE pi.product_id = p.product_id
         ORDER BY pi.image_id
         LIMIT 1) AS thumbnail,
         
    --za filter
    u.user_id AS seller_id,
    c.category_id,
    p.location
    
    
FROM product p
JOIN category c ON c.category_id = p.category_id
JOIN appuser u ON u.user_id = p.seller_id
WHERE p.is_active = 1 AND p.quantity > 0 AND u.is_active = 1;


-------------------------------------------------------
-- детален преглед на избран продукт
DROP VIEW vw_product_details;

CREATE OR REPLACE VIEW vw_product_details AS 

WITH product_rating_reviews AS (
         SELECT r.product_id,
          COALESCE(avg(r.rating), 0::numeric) AS average_rating,
          count(r.review_id) AS total_reviews
          FROM review r
          GROUP BY r.product_id
        ), 
        
         product_images AS (
         SELECT pi.product_id,
          array_agg(pi.image_url ORDER BY pi.image_id) AS images,
          min(pi.image_url::text) AS thumbnail
          FROM productimages pi
          GROUP BY pi.product_id
        )
        
 SELECT 
        p.product_id,
    p.title,
    p.description,
    p.price,
    p.currency,
    p.created_at,
    p.location,
        CASE
            WHEN p.quantity <= 5 THEN 1
            ELSE 0
        END AS low_stock,
        
    c.name,
    
    u.username AS seller_username,
    u.first_name,
    u.last_name,
    
    --od gore od expressions
    ps.average_rating,
    ps.total_reviews,
    i.images,
    i.thumbnail
    
FROM product p
JOIN category c ON c.category_id = p.category_id
JOIN appuser u ON u.user_id = p.seller_id
LEFT JOIN product_rating_reviews ps ON ps.product_id = p.product_id
LEFT JOIN product_images i ON i.product_id = p.product_id;


-----------------------------------------------------
-- преглед на reviews за конкретен продукт
DROP VIEW vw_product_reviews

CREATE OR REPLACE VIEW vw_product_reviews AS 
SELECT 
        r.review_id,
    r.product_id,
    p.title,
    r.rating,
    r.comment,
    r.created_at,
    
    r.buyer_id,
    ub.username AS buyer_username,
    r.seller_id
    
FROM review r
JOIN product p ON p.product_id = r.product_id
JOIN appuser ub ON ub.user_id = r.buyer_id;


-------------------------------------------------------------
--------------------------------------------------------------
-- преглед на листата со омилени продукти на корисникот.
CREATE OR REPLACE VIEW vw_favorites AS 
SELECT 
        f.user_id,
        
    p.product_id,
    p.title,
    p.price,
    p.currency,
    
    CASE
        WHEN p.quantity <= 5 THEN 1
        ELSE 0
    END AS low_stock, 
    
    ( SELECT pi.image_url
         FROM productimages pi
         WHERE pi.product_id = p.product_id
         ORDER BY pi.image_id
         LIMIT 1) AS thumbnail
         
FROM favorites f
JOIN product p ON f.product_id = p.product_id;


explain analyze
SELECT *
FROM vw_favorites
where user_id = 5;

--------------------------------------------------------
------------------------------------------------------------
-- преглед на сите ставки во кошничката на корисникот.
CREATE OR REPLACE VIEW vw_cart_items AS
SELECT 
    ci.cart_id, --filter
    ci.cart_item_id,
    
    ci.product_id,
    p.title,
    p.price AS current_price,
    p.currency,
    ci.quantity,
    ci.price_at_time,
    
    (ci.quantity * ci.price_at_time) AS subtotal,
    
    (
        SELECT image_url
        FROM productimages pi
        WHERE pi.product_id = p.product_id
        ORDER BY image_id
        LIMIT 1
    ) AS thumbnail
    
FROM cartitems ci
JOIN product p ON p.product_id = ci.product_id


----------------------------------------------------
-- сумиран приказ на кошничката.
drop view vw_cart_summary;

CREATE OR REPLACE VIEW vw_cart_summary AS
SELECT 
    c.cart_id,
    c.created_at,
    
    c.user_id, --filter

    c.total_price AS cart_total,
    
    COUNT(ci.cart_item_id) AS total_items,
    SUM(ci.quantity) AS total_quantity
    
FROM cart c
JOIN cartitems ci ON ci.cart_id = c.cart_id

GROUP BY c.cart_id;



-------------------------------------------------
-------------------------------------------------
-- преглед на сите продукти кои припаѓаат на одредена нарачка
drop view vw_order_items

CREATE OR REPLACE VIEW v_order_items AS
SELECT 
    oi.order_id, -- filter
    oi.order_item_id,
    
    oi.product_id,
    p.title,
    p.price AS current_price,
    p.currency,
    oi.quantity,
    oi.price_at_time,
    
    (oi.quantity * oi.price_at_time) AS subtotal,
    
    (
        SELECT image_url
        FROM productimages pi
        WHERE pi.product_id = p.product_id
        ORDER BY image_id
        LIMIT 1
    ) AS thumbnail
    
FROM orderitems oi
JOIN product p ON p.product_id = oi.product_id;


--------------------------------------------------------
-- сумиран приказ на нарачка 
CREATE OR REPLACE VIEW v_order_summary AS
SELECT
    o.order_id,
    o.created_at AS order_date,
    o.status AS order_status,
    o.total_price,
    
    u.first_name || ' ' || u.last_name AS buyer_name,
    ua.street || ' ' || ua.house_number AS street_address,
    ua.city,
    
    c.name AS carrier_name,
    s.tracking_number,
    
    (SELECT COUNT(*) FROM orderitems oi WHERE oi.order_id = o.order_id) AS total_items,
    (SELECT SUM(quantity) FROM orderitems oi WHERE oi.order_id = o.order_id) AS total_quantity
    
FROM "order" o
JOIN appuser u ON u.user_id = o.buyer_id
JOIN shipment s ON s.order_id = o.order_id
JOIN carriers c ON c.carrier_id = s.carrier_id
JOIN useraddress ua ON ua.user_address_id = s.user_address_id;

---------------------------------------------------------------------------
---------------------------------------------------------------------------
-- dashboard приказ со статистики за продуктите на одреден продавач
CREATE OR REPLACE VIEW v_seller_dashboard AS
WITH active_package AS (
    SELECT DISTINCT ON (seller_id)
        seller_id,
        package_id
    FROM UserPackages
    WHERE CURRENT_TIMESTAMP BETWEEN start_date AND end_date
    ORDER BY seller_id, end_date DESC
)
SELECT
    u.user_id                                   AS seller_id,
    u.first_name,
    u.last_name,
    COUNT(DISTINCT pr.product_id)               AS total_products,
    SUM(oi.quantity)                            AS total_sales,
    SUM(oi.quantity * oi.price_at_time)         AS total_revenue,
    ROUND(AVG(r.rating), 2)                     AS avg_rating,
    pck.name                                    AS package_name
FROM AppUser u
LEFT JOIN Product pr     ON pr.seller_id    = u.user_id
LEFT JOIN OrderItems oi  ON oi.product_id   = pr.product_id
LEFT JOIN Review r       ON r.product_id    = pr.product_id
LEFT JOIN active_package ap ON ap.seller_id = u.user_id
LEFT JOIN Package pck    ON pck.package_id  = ap.package_id
WHERE u.is_verified = 1
GROUP BY u.user_id, u.first_name, u.last_name, pck.name;
 


EXPLAIN ANALYZE
SELECT * 
FROM v_seller_dashboard
WHERE seller_id = 120824;


--------------------------------------------------------------
-- преглед на кориснички профил
drop view v_user_profile

CREATE OR REPLACE VIEW v_user_profile as

WITH primary_location AS (
    SELECT user_id, city || ', ' || country AS location
    FROM UserAddress
    WHERE is_primary = 1
),
user_orders AS (
    SELECT buyer_id, COUNT(*) AS total_orders
    FROM "order"
    GROUP BY buyer_id
)
SELECT
    u.user_id,
    u.first_name,
    u.last_name,
    u.email,
    u.phone_number,
    pl.location AS primary_location,
    uo.total_orders,
    u.created_at
FROM AppUser u
LEFT JOIN primary_location pl   ON pl.user_id   = u.user_id
LEFT JOIN user_orders uo        ON uo.buyer_id  = u.user_id;



explain analyze
SELECT * FROM v_user_profile 
WHERE user_id  = 600;