DatabaseCreation: views.sql

-- 1. Event sales summary
-- Used by organisers/admin dashboard.
CREATE OR REPLACE VIEW public.v_event_sales_summary AS
SELECT
    e.event_id,
    e.title AS event_title,
    o.organiser_id,
    o.company_name AS organiser_name,
    COUNT(t.ticket_id) AS tickets_sold,
    COUNT(DISTINCT oc.order_id) AS total_orders,
    COALESCE(SUM(p.amount_paid), 0) AS total_revenue,
    ROUND(AVG(p.amount_paid), 2) AS average_payment_amount,
    MIN(oc.created_at) AS first_order_at,
    MAX(oc.created_at) AS last_order_at
FROM public.event e
         JOIN public.organiser o
              ON o.organiser_id = e.organiser_id
         JOIN public.ticket_type tt
              ON tt.event_id = e.event_id
         JOIN public.ticket t
              ON t.ticket_type_id = tt.ticket_type_id
         JOIN public.order_cart oc
              ON oc.order_id = t.order_id
         LEFT JOIN public.payment p
                   ON p.order_id = oc.order_id
GROUP BY
    e.event_id,
    e.title,
    o.organiser_id,
    o.company_name;

select *
from v_event_sales_summary;

-- 2. User purchase profile
-- Used for user account page / CRM / recommendations.
CREATE OR REPLACE VIEW public.v_user_purchase_profile AS
SELECT
    u.user_id,
    u.username,
    u.email,
    COUNT(DISTINCT oc.order_id) AS total_orders,
    COUNT(t.ticket_id) AS total_tickets,
    COALESCE(SUM(p.amount_paid), 0) AS total_spent,
    ROUND(AVG(p.amount_paid), 2) AS average_order_payment,
    MIN(oc.created_at) AS first_purchase_at,
    MAX(oc.created_at) AS last_purchase_at
FROM public.user_app u
         LEFT JOIN public.order_cart oc
                   ON oc.user_id = u.user_id
         LEFT JOIN public.ticket t
                   ON t.order_id = oc.order_id
         LEFT JOIN public.payment p
                   ON p.order_id = oc.order_id
GROUP BY
    u.user_id,
    u.username,
    u.email;

select *
from v_user_purchase_profile;

-- 3. Ticket scan statistics per event
-- Used by event check-in dashboard.
CREATE OR REPLACE VIEW public.v_event_checkin_statistics AS
SELECT
    e.event_id,
    e.title AS event_title,
    COUNT(t.ticket_id) AS total_tickets,
    COUNT(*) FILTER (WHERE t.is_scanned = true) AS scanned_tickets,
    COUNT(*) FILTER (WHERE t.is_scanned = false) AS unscanned_tickets,
    ROUND(
            100.0 * COUNT(*) FILTER (WHERE t.is_scanned = true)
                / NULLIF(COUNT(t.ticket_id), 0),
            2
    ) AS scanned_percentage,
    MIN(t.scanned_at) FILTER (WHERE t.is_scanned = true) AS first_scan_at,
    MAX(t.scanned_at) FILTER (WHERE t.is_scanned = true) AS last_scan_at
FROM public.event e
         JOIN public.ticket_type tt
              ON tt.event_id = e.event_id
         JOIN public.ticket t
              ON t.ticket_type_id = tt.ticket_type_id
GROUP BY
    e.event_id,
    e.title;

select *
from v_event_checkin_statistics;

-- 4. Session occupancy and waitlist pressure
-- Used to see overloaded sessions.
CREATE OR REPLACE VIEW public.v_session_demand_overview AS
SELECT
    ess.schedule_id,
    ess.session_title,
    e.event_id,
    e.title AS event_title,
    l.name AS location_name,
    s.section_name,
    s.capacity AS section_capacity,
    COUNT(DISTINCT w.waitlist_id) AS waitlist_entries,
    COUNT(DISTINCT w.waitlist_id) FILTER (WHERE w.status = 'WAITING') AS active_waitlist_entries,
    ROUND(
            COUNT(DISTINCT w.waitlist_id)::numeric
                / NULLIF(s.capacity, 0),
            4
    ) AS waitlist_to_capacity_ratio
FROM public.event_schedule_session ess
         JOIN public.event e
              ON e.event_id = ess.event_id
         JOIN public.section s
              ON s.section_id = ess.section_id
         JOIN public.location l
              ON l.location_id = s.location_id
         LEFT JOIN public.waitlist_entry w
                   ON w.event_schedule_session_id = ess.schedule_id
GROUP BY
    ess.schedule_id,
    ess.session_title,
    e.event_id,
    e.title,
    l.name,
    s.section_name,
    s.capacity;

select *
from v_session_demand_overview;

-- 5. Event rating summary
-- Used on public event pages.
CREATE OR REPLACE VIEW public.v_event_rating_summary AS
SELECT
    e.event_id,
    e.title AS event_title,
    COUNT(r.review_id) AS review_count,
    ROUND(AVG(r.star_rating), 2) AS average_rating,
    COUNT(*) FILTER (WHERE r.star_rating = 5) AS five_star_reviews,
    COUNT(*) FILTER (WHERE r.star_rating = 4) AS four_star_reviews,
    COUNT(*) FILTER (WHERE r.star_rating <= 2) AS negative_reviews,
    MAX(r.created_at) AS latest_review_at
FROM public.event e
         LEFT JOIN public.review r
                   ON r.event_id = e.event_id
GROUP BY
    e.event_id,
    e.title;

select *
from v_event_rating_summary
order by event_id;

-- 6. Organiser performance dashboard
-- Used internally by platform admins.
CREATE OR REPLACE VIEW public.v_organiser_performance_dashboard AS
SELECT
    o.organiser_id,
    o.company_name AS organiser_name,
    COUNT(DISTINCT e.event_id) AS total_events,
    COUNT(DISTINCT t.ticket_id) AS total_tickets_sold,
    COUNT(DISTINCT oc.order_id) AS total_orders,
    COALESCE(SUM(p.amount_paid), 0) AS total_revenue,
    ROUND(AVG(r.star_rating), 2) AS average_event_rating,
    COUNT(DISTINCT r.review_id) AS total_reviews
FROM public.organiser o
         LEFT JOIN public.event e
                   ON e.organiser_id = o.organiser_id
         LEFT JOIN public.ticket_type tt
                   ON tt.event_id = e.event_id
         LEFT JOIN public.ticket t
                   ON t.ticket_type_id = tt.ticket_type_id
         LEFT JOIN public.order_cart oc
                   ON oc.order_id = t.order_id
         LEFT JOIN public.payment p
                   ON p.order_id = oc.order_id
         LEFT JOIN public.review r
                   ON r.event_id = e.event_id
GROUP BY
    o.organiser_id,
    o.company_name;

select *
from v_organiser_performance_dashboard;

-- 7. Location utilization summary
-- Used by venue/location managers.
CREATE OR REPLACE VIEW public.v_location_utilization_summary AS
SELECT
    l.location_id,
    l.name AS location_name,
    lt.type_name AS location_type,
    COUNT(DISTINCT s.section_id) AS total_sections,
    COUNT(DISTINCT seat.seat_id) AS total_seats,
    COUNT(DISTINCT ess.schedule_id) AS scheduled_sessions,
    COUNT(DISTINCT e.event_id) AS hosted_events,
    COUNT(DISTINCT seat.seat_id) FILTER (WHERE seat.is_accessible = true) AS accessible_seats,
    COUNT(DISTINCT seat.seat_id) FILTER (WHERE seat.is_available = true) AS available_seats
FROM public.location l
         JOIN public.location_type lt
              ON lt.type_id = l.type_id
         LEFT JOIN public.section s
                   ON s.location_id = l.location_id
         LEFT JOIN public.seat seat
                   ON seat.section_id = s.section_id
         LEFT JOIN public.event_schedule_session ess
                   ON ess.section_id = s.section_id
         LEFT JOIN public.event e
                   ON e.event_id = ess.event_id
GROUP BY
    l.location_id,
    l.name,
    lt.type_name;

select *
from v_location_utilization_summary;

-- 8. Refund analysis
-- Used by finance/support team.
CREATE OR REPLACE VIEW public.v_refund_analysis AS
SELECT
    rr.refund_request_id,
    rr.requested_at,
    rr.accepted_at,
    CASE
        WHEN rr.accepted_at IS NOT NULL THEN 'ACCEPTED'
        ELSE 'PENDING'
        END AS refund_status,
    u.user_id,
    u.username,
    oc.order_id,
    p.payment_id,
    p.amount_paid,
    p.method_id,
    pm.method_name,
    rr.reason
FROM public.refund_request rr
         JOIN public.user_app u
              ON u.user_id = rr.user_id
         JOIN public.payment p
              ON p.payment_id = rr.payment_id
         JOIN public.payment_method pm
              ON pm.method_id = p.method_id
         JOIN public.order_cart oc
              ON oc.order_id = p.order_id;

select *
from v_refund_analysis;

-- 9. Event full search/details view
-- Used by public event browsing/search page.
CREATE OR REPLACE VIEW public.v_event_public_details AS
SELECT
    e.event_id,
    e.title AS event_title,
    e.start_datetime,
    e.end_datetime,
    es.status_name AS event_status,
    o.company_name AS organiser_name,

    STRING_AGG(DISTINCT c.name, ', ') AS categories,

    COUNT(DISTINCT ess.schedule_id) AS session_count,
    COUNT(DISTINCT sp.sponsor_id) AS sponsor_count,
    COUNT(DISTINCT r.review_id) AS review_count,
    ROUND(AVG(r.star_rating), 2) AS average_rating,

    MIN(pt.price) AS lowest_ticket_price,
    MAX(pt.price) AS highest_ticket_price,

    COUNT(DISTINCT t.ticket_id) AS tickets_sold
FROM public.event e
         JOIN public.event_status es
              ON es.event_status_id = e.event_status_id
         JOIN public.organiser o
              ON o.organiser_id = e.organiser_id
         LEFT JOIN public.event_category ec
                   ON ec.event_id = e.event_id
         LEFT JOIN public.category c
                   ON c.id = ec.category_id
         LEFT JOIN public.event_schedule_session ess
                   ON ess.event_id = e.event_id
         LEFT JOIN public.sponsor_event se
                   ON se.event_id = e.event_id
         LEFT JOIN public.sponsor sp
                   ON sp.sponsor_id = se.sponsor_id
         LEFT JOIN public.review r
                   ON r.event_id = e.event_id
         LEFT JOIN public.ticket_type tt
                   ON tt.event_id = e.event_id
         LEFT JOIN public.price_tier pt
                   ON pt.ticket_type_id = tt.ticket_type_id
         LEFT JOIN public.ticket t
                   ON t.ticket_type_id = tt.ticket_type_id
GROUP BY
    e.event_id,
    e.title,
    e.start_datetime,
    e.end_datetime,
    es.status_name,
    o.company_name;

select *
from v_event_public_details;