DatabaseCreation: views.sql

-- 1. AVAILABLE BOOKABLES VIEW
-- Client searches available artists/bands/DJs

CREATE OR REPLACE VIEW vw_available_bookables AS
SELECT
    b.bookable_id,
    b.display_name,
    b.bookable_type,

    l.city,

    g.genre_name,

    av.start_datetime,
    av.end_datetime,

    av.status

FROM AvailabilitySlot av

JOIN Bookable b
    ON av.bookable_id = b.bookable_id

LEFT JOIN Location l
    ON b.location_id = l.location_id

LEFT JOIN BookableGenre bg
    ON b.bookable_id = bg.bookable_id

LEFT JOIN Genre g
    ON bg.genre_id = g.genre_id

WHERE av.status = 'AVAILABLE';



-- 2. CLIENT BOOKING HISTORY VIEW
-- Logged client sees all bookings

CREATE VIEW vw_client_booking_history AS
SELECT
    cp.client_id,

    bk.booking_id,

    b.display_name,
    b.bookable_type,

    br.event_type,
    br.event_date,

    bk.booking_status,

    p.amount,
    p.payment_status

FROM ClientProfile cp

JOIN BookingRequest br
    ON cp.client_id = br.client_id

JOIN Offer o
    ON br.request_id = o.request_id

JOIN Booking bk
    ON bk.offer_id = o.offer_id

JOIN Bookable b
    ON o.bookable_id = b.bookable_id

LEFT JOIN Payment p
    ON p.booking_id = bk.booking_id;




-- 3. PUBLIC ARTIST PROFILE VIEW
--  artist public page

CREATE VIEW vw_public_artist_profiles AS
SELECT
    b.bookable_id,
    b.display_name,
    b.bookable_type,

    l.city,

    g.genre_name,

    ROUND(AVG(r.rating), 2) AS average_rating,

    COUNT(r.review_id) AS total_reviews

FROM Bookable b

LEFT JOIN Location l
    ON b.location_id = l.location_id

LEFT JOIN BookableGenre bg
    ON b.bookable_id = bg.bookable_id

LEFT JOIN Genre g
    ON bg.genre_id = g.genre_id

LEFT JOIN Offer o
    ON b.bookable_id = o.bookable_id

LEFT JOIN Booking bk
    ON bk.offer_id = o.offer_id

LEFT JOIN Review r
    ON r.booking_id = bk.booking_id

GROUP BY
    b.bookable_id,
    b.display_name,
    b.bookable_type,
    l.city,
    g.genre_name;



-- 4. TRENDING BOOKABLES VIEW
-- Homepage recommendations

CREATE VIEW vw_trending_bookables AS
SELECT
    b.bookable_id,
    b.display_name,
    b.bookable_type,

    COUNT(bk.booking_id) AS total_bookings,

    ROUND(AVG(r.rating), 2) AS average_rating

FROM Bookable b

LEFT JOIN Offer o
    ON b.bookable_id = o.bookable_id

LEFT JOIN Booking bk
    ON bk.offer_id = o.offer_id

LEFT JOIN Review r
    ON r.booking_id = bk.booking_id

GROUP BY
    b.bookable_id,
    b.display_name,
    b.bookable_type;




-- 5. ARTIST DASHBOARD VIEW
-- Artist homepage

CREATE VIEW vw_artist_dashboard AS
SELECT
    b.bookable_id,
    b.display_name,

    COUNT(bk.booking_id) AS total_bookings,

    SUM(
        CASE
            WHEN p.payment_status = 'PAID'
            THEN p.amount
            ELSE 0
        END
    ) AS total_earnings,

    ROUND(AVG(r.rating), 2) AS average_rating

FROM Bookable b

LEFT JOIN Offer o
    ON b.bookable_id = o.bookable_id

LEFT JOIN Booking bk
    ON bk.offer_id = o.offer_id

LEFT JOIN Payment p
    ON p.booking_id = bk.booking_id

LEFT JOIN Review r
    ON r.booking_id = bk.booking_id

GROUP BY
    b.bookable_id,
    b.display_name;



-- 6. ARTIST SCHEDULE VIEW
-- Artist availability page

CREATE VIEW vw_artist_schedule AS
SELECT
    b.bookable_id,
    b.display_name,

    av.slot_id,

    av.start_datetime,
    av.end_datetime,

    av.status

FROM Bookable b

JOIN AvailabilitySlot av
    ON b.bookable_id = av.bookable_id;



-- 7. ARTIST REVIEWS VIEW
-- Artist reviews page

CREATE VIEW vw_artist_reviews AS
SELECT
    b.bookable_id,
    b.display_name,

    r.review_id,

    r.rating,
    r.comment

FROM Bookable b

JOIN Offer o
    ON b.bookable_id = o.bookable_id

JOIN Booking bk
    ON bk.offer_id = o.offer_id

JOIN Review r
    ON r.booking_id = bk.booking_id;





-- 8. ARTIST INCOMING REQUESTS VIEW
-- Artist sees incoming booking requests

CREATE VIEW vw_artist_incoming_requests AS
SELECT
    b.bookable_id,
    b.display_name,

    br.request_id,

    br.event_type,
    br.event_date,

    l.city,

    o.total_price

FROM Bookable b

JOIN Offer o
    ON b.bookable_id = o.bookable_id

JOIN BookingRequest br
    ON o.request_id = br.request_id

LEFT JOIN Location l
    ON br.location_id = l.location_id;



-- 9.

CREATE VIEW vw_pending_payments AS
SELECT
    p.payment_id,
    bk.booking_id,
    b.bookable_id,
    b.display_name,
    p.amount,
    p.payment_status,
    br.event_date,
    l.city

FROM Payment p

JOIN Booking bk
    ON p.booking_id = bk.booking_id

JOIN Offer o
    ON bk.offer_id = o.offer_id

JOIN Bookable b
    ON o.bookable_id = b.bookable_id

JOIN BookingRequest br
    ON o.request_id = br.request_id

LEFT JOIN Location l
    ON br.location_id = l.location_id

WHERE p.payment_status = 'PENDING';