DatabaseCreation: views.sql

CREATE OR REPLACE VIEW vw_customer_notifications AS 
SELECT 
    n.customer_id, 
    n.notification_id, 
    n.title, 
    n.message, 
    n.created_at, 
    n.is_read, 
    n.event_id 
FROM notification n 
ORDER BY n.created_at DESC; 


CREATE OR REPLACE VIEW vw_customer_purchases AS 
SELECT 
    cust.customer_id, 
    cust.first_name, 
    cust.last_name, 
    e.name AS event_name, 
    e.start_datetime, 
    v.venue_title,    
    s.row_number,     
    s.seat_number,    
    t.ticket_id, 
    t.ticket_price, 
    t.qr_code,        
    p.amount AS paid_amount, 
    p.payment_status, 
    p.payment_date    
FROM customer cust 
JOIN ticket t ON cust.customer_id = t.customer_id 
JOIN event e ON t.event_id = e.event_id 
JOIN venue v ON e.venue_id = v.venue_id 
JOIN seat s ON t.seat_id = s.seat_id 
LEFT JOIN payment p ON t.payment_id = p.payment_id; 
 

CREATE OR REPLACE VIEW vw_event_sponsors AS 
SELECT 
    e.event_id, 
    e.name AS event_name, 
    s.sponsor_name, 
    s.contact_email, 
    st.sponsor_type_name, 
    es.sponsorship_amount, 
    es.contract_date 
FROM event e 
JOIN event_sponsorship es ON e.event_id = es.event_id 
JOIN sponsor s ON es.sponsor_id = s.sponsor_id 
JOIN sponsor_type st ON s.sponsor_type_id = st.sponsor_type_id; 
 

CREATE OR REPLACE VIEW vw_admin_roles AS 
SELECT 
    a.admin_id, 
    a.first_name, 
    a.last_name, 
    a.email, 
    CASE 
        WHEN ea.admin_id IS NOT NULL AND ta.admin_id IS NOT NULL THEN 'EVENT & TICKET ADMIN' 
        WHEN ea.admin_id IS NOT NULL THEN 'EVENT_ADMIN' 
        WHEN ta.admin_id IS NOT NULL THEN 'TICKET_ADMIN' 
        ELSE 'NO_ROLE' 
    END AS role 
FROM admin a 
LEFT JOIN event_admin ea ON a.admin_id = ea.admin_id 
LEFT JOIN ticket_admin ta ON a.admin_id = ta.admin_id; 
 
 

CREATE OR REPLACE VIEW vw_event_details AS 
SELECT 
    e.event_id, 
    e.name AS event_name, 
    e.description, 
    e.start_datetime, 
    e.end_datetime, 
    e.status, 
    v.venue_title, 
    v.street_address, 
    c.city_name, 
    co.country_name, 
    cat.category_name, 
    (SELECT em.url FROM event_media em WHERE em.event_id = e.event_id LIMIT 1) AS event_image_url 
FROM event e 
JOIN venue v ON e.venue_id = v.venue_id 
JOIN city c ON v.city_id = c.city_id 
JOIN country co ON c.country_id = co.country_id 
JOIN category cat ON e.category_id = cat.category_id; 
 
 
CREATE OR REPLACE VIEW vw_customer_profile_stats AS 
SELECT 
    c.customer_id, 
    c.first_name, 
    c.last_name, 
    c.email, 
    
    (SELECT COUNT(*) FROM ticket t 
     WHERE t.customer_id = c.customer_id AND t.status IN ('PURCHASED', 'SCANNED')) AS total_tickets, 
 
    (SELECT COUNT(DISTINCT t.event_id) FROM ticket t 
     WHERE t.customer_id = c.customer_id AND t.status IN ('PURCHASED', 'SCANNED')) AS unique_events_attended, 
 
    (SELECT COUNT(*) FROM notification n 
     WHERE n.customer_id = c.customer_id AND n.is_read = FALSE) AS unread_notifications, 
 
    CASE 
        WHEN (SELECT COUNT(*) FROM ticket t WHERE t.customer_id = c.customer_id AND t.status = 'PURCHASED') > 10 THEN 'PLATINUM FAN' 
        WHEN (SELECT COUNT(*) FROM ticket t WHERE t.customer_id = c.customer_id AND t.status = 'PURCHASED') > 5 THEN 'GOLD FAN' 
        ELSE 'STANDARD MEMBER' 
    END AS loyalty_status, 
 
    (SELECT MAX(p.payment_date) FROM payment p WHERE p.customer_id = c.customer_id) AS last_activity 
FROM customer c; 
 

CREATE VIEW vw_event_revenue AS 
SELECT 
    e.event_id, 
    e.name, 
    SUM(p.amount) AS total_revenue 
FROM event e 
JOIN ticket t ON e.event_id = t.event_id 
JOIN payment p ON t.payment_id = p.payment_id 
WHERE p.payment_status = 'COMPLETED' 
GROUP BY e.event_id, e.name; 
 

CREATE OR REPLACE VIEW vw_event_archive AS 
SELECT 
    e.event_id, 
    e.name AS event_name, 
    e.end_datetime, 
    cat.category_name, 
    ROUND(AVG(r.rating), 2) AS avg_rating, 
    COUNT(r.review_id) AS total_reviews 
FROM event e 
JOIN category cat ON e.category_id = cat.category_id 
JOIN review r ON e.event_id = r.event_id 
WHERE e.status = 'COMPLETED' 
GROUP BY e.event_id, e.name, e.end_datetime, cat.category_name; 
 

CREATE OR REPLACE VIEW vw_event_ticket_master_report AS 
SELECT 
    e.event_id, 
    e.name AS event_name, 
    v.venue_title, 
    v.capacity AS venue_capacity, 
    COUNT(t.ticket_id) AS total_generated_tickets, 
 
    COUNT(*) FILTER (WHERE t.status = 'AVAILABLE') AS available_tickets, 
 
    COUNT(*) FILTER (WHERE t.status IN ('PURCHASED', 'SCANNED')) AS total_sold_tickets, 
 
    COUNT(*) FILTER (WHERE t.status = 'RESERVED') AS currently_reserved, 
  
    COUNT(*) FILTER (WHERE t.status = 'SCANNED') AS actual_attendance, 
 
    CASE 
        WHEN v.capacity > 0 THEN 
            ROUND((COUNT(*) FILTER (WHERE t.status IN ('PURCHASED', 'SCANNED'))::NUMERIC / v.capacity) * 100, 2) 
        ELSE 0 
    END AS sales_occupancy_percent 
 
FROM event e 
JOIN venue v ON e.venue_id = v.venue_id 
LEFT JOIN ticket t ON e.event_id = t.event_id 
GROUP BY e.event_id, e.name, v.venue_title, v.capacity;