DatabaseCreation: views.sql

--======
--1 active product catalog            +
--======
SET search_path TO myschema;
DROP VIEW IF EXISTS v_active_product_catalog;

CREATE VIEW v_active_product_catalog AS
SELECT
    v.variant_id,
    v.sku,
    p.name AS product_name,
    p.brand,
    c.category_name,
    prof.first_name || ' ' || prof.last_name AS seller_name,
    SUM(ii.quantity) AS seller_stock,
    v.price,
    STRING_AGG(DISTINCT wh.location, ', ') AS available_in_cities,
    CASE
        WHEN SUM(ii.quantity) >= 10 THEN 'IN_STOCK'
        WHEN SUM(ii.quantity) > 0 THEN 'LOW_STOCK'
        ELSE 'OUT_OF_STOCK'
    END AS stock_status
FROM product_variants v
JOIN products p ON v.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
JOIN inventory_items ii ON v.variant_id = ii.variant_id
JOIN warehouses wh ON ii.warehouse_id = wh.warehouse_id
JOIN user_profiles prof ON wh.user_id = prof.user_id
WHERE ii.quantity > 0
GROUP BY
    v.variant_id,
    v.sku,
    p.name,
    p.brand,
    c.category_name,
    prof.user_id,
    prof.first_name,
    prof.last_name,
    v.price
ORDER BY
    p.name,
    seller_name;

--======
--2 order command center full +
--======
DROP VIEW IF EXISTS v_order_command_center_full;

CREATE VIEW v_order_command_center_full AS
SELECT
    o.order_id,
    o.status AS order_status,
    o.order_date,
    cp.first_name || ' ' || cp.last_name AS customer_name,
    cu.email AS customer_email,
    o.total_amount AS order_total,
    sm.method_name AS shipping_via,
    a.city || ', ' || a.street_address AS delivery_destination,
    s.tracking_number,
    s.status AS shipment_status,
    cour_p.first_name || ' ' || cour_p.last_name AS assigned_courier,
    (SELECT string_agg(p.name || ' (Qty: ' || oi.quantity || ')', ', ')
     FROM ORDER_ITEMS oi
     JOIN PRODUCT_VARIANTS v ON oi.variant_id = v.variant_id
     JOIN PRODUCTS p ON v.product_id = p.product_id
     WHERE oi.order_id = o.order_id) AS items_ordered
FROM ORDERS o
JOIN USERS cu ON o.user_id = cu.user_id
JOIN USER_PROFILES cp ON cu.user_id = cp.user_id
JOIN SHIPPING_METHODS sm ON o.shipping_method_id = sm.method_id
JOIN ADDRESSES a ON o.user_id = a.user_id AND a.is_default = true
LEFT JOIN SHIPMENTS s ON o.order_id = s.order_id
LEFT JOIN USER_PROFILES cour_p ON s.courier_id = cour_p.user_id
WHERE o.status <> 'CART';

--======
--3 seller items fulfillment +
--======
DROP VIEW IF EXISTS v_seller_item_fulfillment;

CREATE OR REPLACE VIEW v_seller_item_fulfillment AS
SELECT
    seller_p.user_id AS seller_id,
    seller_p.first_name AS seller_store_name,
    wh.warehouse_name AS pickup_location,

    m.name AS product_brand,

    o.order_id,
    o.order_date,
    cust_p.first_name || ' ' || cust_p.last_name AS customer_name,

    p.name AS product_name,
    v.sku,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price)::numeric(12,2) AS item_total_revenue,

    s.status AS shipment_status,
    cour_p.first_name AS courier_service
FROM ORDER_ITEMS oi
JOIN PRODUCT_VARIANTS v ON oi.variant_id = v.variant_id
JOIN PRODUCTS p ON v.product_id = p.product_id
JOIN MANUFACTURERS m ON v.manufacturer_id = m.manufacturer_id
JOIN ORDERS o ON oi.order_id = o.order_id
JOIN USER_PROFILES cust_p ON o.user_id = cust_p.user_id

LEFT JOIN SHIPMENT_ITEMS si ON oi.order_item_id = si.order_item_id
LEFT JOIN PRODUCT_INSTANCES pi ON si.instance_id = pi.instance_id
LEFT JOIN WAREHOUSES wh ON pi.warehouse_id = wh.warehouse_id
LEFT JOIN USER_PROFILES seller_p ON wh.user_id = seller_p.user_id

LEFT JOIN SHIPMENTS s ON o.order_id = s.order_id
LEFT JOIN USER_PROFILES cour_p ON s.courier_id = cour_p.user_id

WHERE o.status NOT IN ('CART', 'CANCELLED');

--======
--4 product selection matrix +
--======
DROP VIEW IF EXISTS v_product_selection_matrix;

CREATE VIEW v_product_selection_matrix AS
SELECT
    p.product_id,
    p.name AS product_model,
    v.variant_id,
    v.sku,
    v.price,
    v.stock_total,
    (SELECT string_agg(pa.attribute_name || ': ' || av.attr_value, ', ')
     FROM PRODUCT_ATTRIBUTE_VALUES av
     JOIN PRODUCT_ATTRIBUTES pa ON av.attribute_id = pa.attribute_id
     WHERE av.variant_id = v.variant_id) AS selection_specs,
    v.product_variant_picture_url AS image_url
FROM PRODUCTS p
JOIN PRODUCT_VARIANTS v ON p.product_id = v.product_id
WHERE v.stock_total > 0
ORDER BY p.name, v.price ASC;

--======
--5 product average rating +
--======
DROP VIEW IF EXISTS v_product_ratings_avg;

CREATE VIEW v_product_ratings_avg AS
SELECT
    p.product_id,
    p.name AS product_name,
    ROUND(AVG(r.rating), 2) AS average_rating,
    COUNT(r.review_id) AS total_reviews,
    MIN(r.rating) AS worst_rating,
    MAX(r.rating) AS best_rating
FROM PRODUCTS p
LEFT JOIN REVIEWS r ON p.product_id = r.product_id
GROUP BY p.product_id, p.name;

--======
--6 monthly sales report  +
--======
DROP VIEW IF EXISTS v_monthly_sales_report;

CREATE VIEW v_monthly_sales_report AS
SELECT
    TO_CHAR(order_date, 'YYYY-MM') AS sales_month,
    COUNT(order_id) AS total_orders,
    SUM(total_amount) AS gross_revenue,
    ROUND(AVG(total_amount), 2) AS average_order_value
FROM ORDERS
WHERE status IN ('PAID', 'SHIPPED', 'COMPLETED')
GROUP BY sales_month
ORDER BY sales_month DESC;

--======
--7 seller revenue report +
--======
DROP VIEW IF EXISTS v_seller_revenue_report;

CREATE VIEW v_seller_revenue_report AS
SELECT
    prof.user_id AS seller_id,
    prof.first_name AS seller_name,
    COUNT(DISTINCT o.order_id) AS successful_orders_count,
    COUNT(si.shipment_item_id) AS total_items_sold,
    SUM(oi.unit_price)::numeric(14,2) AS total_gross_revenue,
    ROUND(AVG(oi.unit_price), 2) AS avg_item_price,
    COUNT(DISTINCT p.product_id) AS unique_products_sold
FROM user_profiles prof
JOIN warehouses wh ON prof.user_id = wh.user_id
JOIN product_instances pi ON wh.warehouse_id = pi.warehouse_id
JOIN shipment_items si ON pi.instance_id = si.instance_id
JOIN order_items oi ON si.order_item_id = oi.order_item_id
JOIN orders o ON oi.order_id = o.order_id
JOIN product_variants v ON oi.variant_id = v.variant_id
JOIN products p ON v.product_id = p.product_id
WHERE o.status IN ('PAID', 'SHIPPED', 'COMPLETED')
GROUP BY prof.user_id, prof.first_name
ORDER BY total_gross_revenue DESC;

--======
--8 active warranties  -
--======
DROP VIEW IF EXISTS v_active_warranty_tracker;
CREATE VIEW v_active_warranty_tracker AS
SELECT
    u.email AS customer_email,
    prof.first_name || ' ' || prof.last_name AS customer_name,
    p.name AS product_name,
    pi.serial_number,
    o.order_date AS purchase_date,
    w.duration_months,
    (o.order_date + (w.duration_months || ' months')::interval)::date AS warranty_expiry_date,
    CASE
        WHEN (o.order_date + (w.duration_months || ' months')::interval) > NOW() THEN 'ACTIVE'
        ELSE 'EXPIRED'
    END AS warranty_status
FROM warranties w
JOIN product_instances pi ON w.instance_id = pi.instance_id
JOIN shipment_items si ON pi.instance_id = si.instance_id
JOIN order_items oi ON si.order_item_id = oi.order_item_id
JOIN orders o ON oi.order_id = o.order_id
JOIN users u ON o.user_id = u.user_id
JOIN user_profiles prof ON u.user_id = prof.user_id
JOIN product_variants v ON pi.variant_id = v.variant_id
JOIN products p ON v.product_id = p.product_id;

--======
--9 inventory drift sync report +
--======
DROP VIEW IF EXISTS v_inventory_drift_sync_report;
CREATE VIEW v_inventory_drift_sync_report AS
WITH instance_counts AS (
    SELECT variant_id, COUNT(*) AS physical_available_count
    FROM product_instances
    WHERE status = 'AVAILABLE'
    GROUP BY variant_id
),
warehouse_sums AS (
    SELECT variant_id, SUM(quantity) AS inventory_stock
    FROM inventory_items
    GROUP BY variant_id
)
SELECT
    v.variant_id,
    p.name AS product_name,
    v.sku,
    v.stock_total AS master_stock,
    COALESCE(w.inventory_stock, 0) AS aggregate_inventory_stock,
    COALESCE(ic.physical_available_count, 0) AS available_physical_instances,
    v.stock_total - COALESCE(w.inventory_stock, 0) AS master_vs_inventory_drift,
    COALESCE(w.inventory_stock, 0) - COALESCE(ic.physical_available_count, 0) AS inventory_vs_instance_drift
FROM product_variants v
JOIN products p ON v.product_id = p.product_id
LEFT JOIN warehouse_sums w ON v.variant_id = w.variant_id
LEFT JOIN instance_counts ic ON v.variant_id = ic.variant_id;

--======
--10 cart value view because CART.total_amount must remain NULL
--======
DROP VIEW IF EXISTS v_cart_current_value;
CREATE VIEW v_cart_current_value AS
SELECT
    o.order_id,
    o.user_id,
    COUNT(oi.order_item_id) AS cart_item_rows,
    SUM(oi.quantity) AS cart_total_units,
    SUM(oi.quantity * oi.unit_price)::numeric(12,2) AS cart_current_value
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
WHERE o.status = 'CART'
GROUP BY o.order_id, o.user_id;

--======
--11 derived fulfillment status for each order item.
--======
DROP VIEW IF EXISTS v_order_item_fulfillment_status;
CREATE VIEW v_order_item_fulfillment_status AS
SELECT
    oi.order_item_id,
    oi.order_id,
    oi.variant_id,
    oi.quantity AS ordered_quantity,
    COUNT(si.shipment_item_id)::int AS shipped_quantity,
    (oi.quantity - COUNT(si.shipment_item_id))::int AS missing_quantity,
    CASE
        WHEN COUNT(si.shipment_item_id) = 0 THEN 'NOT_SHIPPED'
        WHEN COUNT(si.shipment_item_id) < oi.quantity THEN 'PARTIALLY_SHIPPED'
        ELSE 'FULLY_SHIPPED'
    END AS fulfillment_status
FROM order_items oi
LEFT JOIN shipment_items si ON si.order_item_id = oi.order_item_id
GROUP BY oi.order_item_id, oi.order_id, oi.variant_id, oi.quantity;

--======
--12 shipment + main payment summary through order_id.
--======
DROP VIEW IF EXISTS v_shipment_payment_summary;
CREATE VIEW v_shipment_payment_summary AS
SELECT
    s.shipment_id,
    s.order_id,
    s.status AS shipment_status,
    s.tracking_number,
    s.shipped_date,
    s.estimated_arrival,
    s.delivered_date,
    s.courier_id,
    p.payment_id,
    p.payment_method,
    p.amount AS payment_amount,
    p.payment_status,
    p.payment_date
FROM shipments s
LEFT JOIN payments p ON p.order_id = s.order_id
                   AND p.transaction_id = 'TXN-' || s.order_id || '-MAIN';

--======
--13  customer items for delivery +
--======
DROP VIEW IF EXISTS v_customer_order_tracking;

CREATE VIEW v_customer_order_tracking AS
SELECT
    o.user_id AS customer_id,
    o.order_id,
    o.order_date,
    o.status AS order_status,
    o.total_amount,

    (
        SELECT string_agg(p.name || ' (x' || oi.quantity || ')', ', ')
        FROM order_items oi
        JOIN product_variants v ON oi.variant_id = v.variant_id
        JOIN products p ON v.product_id = p.product_id
        WHERE oi.order_id = o.order_id
    ) AS items_list,

    (
        SELECT string_agg(DISTINCT seller_prof.first_name || ' ' || seller_prof.last_name, ' & ')
        FROM order_items oi
        JOIN shipment_items si ON si.order_item_id = oi.order_item_id
        JOIN product_instances pi ON pi.instance_id = si.instance_id
        JOIN warehouses wh ON wh.warehouse_id = pi.warehouse_id
        JOIN user_profiles seller_prof ON seller_prof.user_id = wh.user_id
        WHERE oi.order_id = o.order_id
    ) AS sellers,

    s.tracking_number,
    s.status AS shipping_status,
    c_prof.first_name || ' ' || c_prof.last_name AS courier_name,
    s.estimated_arrival
FROM orders o
LEFT JOIN shipments s ON o.order_id = s.order_id
LEFT JOIN user_profiles c_prof ON s.courier_id = c_prof.user_id
WHERE o.status <> 'CART'
ORDER BY o.order_date DESC;