-- 1. attraction_popularity_view

CREATE VIEW attraction_popularity_view AS
SELECT
    a.name,
    COUNT(f.trip_participant_id) AS total_facultatives,
    AVG(a.entry_fee) AS average_entry_fee
FROM attractions a
JOIN facultatives f
    ON a.facultative_id = f.id
GROUP BY
    a.id,
    a.name;

-- 2. flight_revenue_view

CREATE VIEW flight_revenue_view AS
SELECT
    fb.flight_route_id,
    COUNT(*) AS total_bookings,
    SUM(fb.price) AS total_revenue,
    AVG(fb.price) AS average_ticket_price
FROM flight_bookings fb
GROUP BY fb.flight_route_id;

-- 3. hotel_booking_stats_view

CREATE VIEW hotel_booking_stats_view AS
SELECT
    h.id AS hotel_id,
    h.name AS hotel_name,
    h.star_rating,

    (
        SELECT COUNT(*)
        FROM hotel_rooms hr
        WHERE hr.hotel_id = h.id
    ) AS total_rooms,

    (
        SELECT COUNT(*)
        FROM hotel_rooms hr
        JOIN trip_participants_hotel_rooms tphr
            ON hr.id = tphr.hotel_room_id
        WHERE hr.hotel_id = h.id
    ) AS total_guests,

    (
        SELECT AVG(hr.price)
        FROM hotel_rooms hr
        WHERE hr.hotel_id = h.id
    ) AS average_room_price,

    (
        SELECT MIN(tphr.check_in)
        FROM hotel_rooms hr
        JOIN trip_participants_hotel_rooms tphr
            ON hr.id=tphr.hotel_room_id
        WHERE hr.hotel_id=h.id
    ) AS earliest_check_in,

    (
        SELECT MAX(tphr.check_out)
        FROM hotel_rooms hr
        JOIN trip_participants_hotel_rooms tphr
            ON hr.id=tphr.hotel_room_id
        WHERE hr.hotel_id=h.id
    ) AS latest_check_out

FROM hotels h;

-- 4. review_photo_engagement_view

CREATE VIEW review_photo_engagement_view AS
SELECT
    r.id AS review_id,

    r.rating,

    r.created_at,

    tp.user_id,

    COUNT(p.id) AS photo_count,

    LENGTH(r.comment) AS comment_length,

    CASE

        WHEN r.rating >= 4 THEN 'positive'

        WHEN r.rating = 3 THEN 'neutral'

        ELSE 'negative'

    END AS sentiment

FROM reviews r

JOIN trip_participants tp
    ON r.trip_participant_id = tp.id

LEFT JOIN photos_url p
    ON r.id = p.review_id

GROUP BY
    r.id,
    r.rating,
    r.created_at,
    tp.user_id,
    r.comment;

-- 5. transport_usage_stats_view

CREATE VIEW transport_usage_stats_view AS

SELECT

    fb.flight_route_id AS route_id,

    'FLIGHT' AS transport_type,

    COUNT(*) AS bookings,

    COUNT(*) AS passengers,

    SUM(fb.price) AS total_revenue,

    AVG(fb.price) AS avg_price

FROM flight_bookings fb

GROUP BY fb.flight_route_id

UNION ALL

SELECT

    bb.route_id,

    'BUS',

    COUNT(*),

    COUNT(*),

    SUM(bb.price),

    AVG(bb.price)

FROM bus_bookings bb

GROUP BY bb.route_id;

-- 6. trip_duration_stats_view

CREATE VIEW trip_duration_stats_view AS
SELECT
    t.id AS trip_id,
    AVG(te.end_date - te.start_date) AS avg_duration,
    MIN(te.end_date - te.start_date) AS shortest_trip,
    MAX(te.end_date - te.start_date) AS longest_trip
FROM trips t
JOIN trip_executions te
    ON t.id = te.trip_id
GROUP BY t.id;

-- 7. trip_execution_pacing_view

CREATE VIEW trip_execution_pacing_view AS

SELECT

te.id,

t.name,

te.start_date,

te.end_date,

(te.end_date-te.start_date) AS duration_days,

(
SELECT COUNT(*)

FROM trip_locations tl

WHERE tl.trip_id=t.id

) AS total_location_visits,

ROUND(

(
SELECT COUNT(*)

FROM trip_locations tl

WHERE tl.trip_id=t.id

)::numeric

/

NULLIF((te.end_date-te.start_date),0),

2

) AS visits_per_day,

ROUND(

(
SELECT COUNT(DISTINCT tl.location_id)

FROM trip_locations tl

WHERE tl.trip_id=t.id

)::numeric

/

NULLIF((te.end_date-te.start_date),0),

2

) AS unique_locations_per_day

FROM trip_executions te

JOIN trips t

ON te.trip_id=t.id;

-- 8. trip_execution_stats_view

CREATE VIEW trip_execution_stats_view AS

SELECT

te.id AS trip_execution_id,

t.name,

te.start_date,

te.end_date,

(te.end_date-te.start_date) AS duration_days,

(
SELECT COUNT(*)

FROM trip_participants tp

WHERE tp.trip_execution_id=te.id

) AS participant_count,

(
SELECT COUNT(*)

FROM trip_locations tl

WHERE tl.trip_id=t.id

) AS visited_locations,

(
SELECT AVG(r.rating)

FROM trip_participants tp

JOIN reviews r

ON tp.id=r.trip_participant_id

WHERE tp.trip_execution_id=te.id

) AS average_rating

FROM trip_executions te

JOIN trips t

ON te.trip_id=t.id;

-- 9. trip_locations_by_day_view

CREATE VIEW trip_locations_by_day_view AS
SELECT

day_number,

COUNT(location_id) AS total_locations,

COUNT(DISTINCT trip_id) AS total_trips

FROM trip_locations

GROUP BY day_number;

-- 10. trip_participant_count_view

CREATE VIEW trip_participant_count_view AS
SELECT

t.id AS trip_id,

t.name AS trip_name,

COUNT(tp.id) AS participants

FROM trips t

LEFT JOIN trip_executions te
ON t.id=te.trip_id

LEFT JOIN trip_participants tp
ON te.id=tp.trip_execution_id

GROUP BY

t.id,

t.name;

-- 11. trip_participant_status_view

CREATE VIEW trip_participant_status_view AS
SELECT

status,

COUNT(*) AS total_participants

FROM trip_participants

GROUP BY status;

-- 12. user_trip_review_stats_view

CREATE VIEW user_trip_review_stats_view AS
SELECT

u.id,

u.username,

(
SELECT COUNT(DISTINCT tp.trip_execution_id)

FROM trip_participants tp

WHERE tp.user_id=u.id

) AS trips_joined,

(
SELECT COUNT(*)

FROM trip_participants tp

JOIN reviews r
ON tp.id=r.trip_participant_id

WHERE tp.user_id=u.id

) AS total_reviews,

(
SELECT AVG(r.rating)

FROM trip_participants tp

JOIN reviews r
ON tp.id=r.trip_participant_id

WHERE tp.user_id=u.id

) AS average_rating,

(
SELECT MIN(r.rating)

FROM trip_participants tp

JOIN reviews r
ON tp.id=r.trip_participant_id

WHERE tp.user_id=u.id

) AS lowest_rating,

(
SELECT MAX(r.rating)

FROM trip_participants tp

JOIN reviews r
ON tp.id=r.trip_participant_id

WHERE tp.user_id=u.id

) AS highest_rating

FROM users u;