DDL, DML and Views: planora_views.sql

-- views
-- za host da vidi detalji za eden booking
CREATE OR REPLACE VIEW vw_booking_overview AS
SELECT
    b.booking_id,
    b.booking_status,
    b.booked_at,
    b.check_in_date,
    b.check_out_date,
    (b.check_out_date - b.check_in_date) AS nights,
    b.guests_count,
    b.total_price,

    g.guest_id,
    u_g.user_id AS guest_user_id,
    u_g.first_name || ' ' || u_g.last_name AS guest_full_name,
    u_g.email AS guest_email,

    r.room_id,
    r.room_name,
    r.capacity AS room_capacity,
    r.price_per_night,
    rt.type_name AS room_type,

    p.property_id,
    p.title AS property_title,
    p.status AS property_status,
    lt.type_name AS listing_type,

    a.city,
    a.street,
    co.country_name,

    u_h.user_id AS host_user_id,
    u_h.first_name || ' ' || u_h.last_name AS host_full_name,
    u_h.email AS host_email

FROM bookings b
JOIN guests g ON g.guest_id = b.guest_id
JOIN users u_g ON u_g.user_id = g.user_id
JOIN rooms r ON r.room_id = b.room_id
JOIN room_types rt ON rt.room_type_id = r.room_type_id
JOIN properties p ON p.property_id = r.property_id
JOIN listing_types lt ON lt.listing_type_id = p.listing_type_id
JOIN addresses a ON a.address_id = p.address_id
JOIN countries co ON co.country_id = a.country_id
JOIN hosts h ON h.user_id = p.host_id
JOIN users u_h ON u_h.user_id = h.user_id;

-- za host da vidi status na plakjanje za sekoj booking
CREATE OR REPLACE VIEW vw_payment_status AS
SELECT
    b.booking_id,
    b.booking_status,
    b.total_price AS booking_total,
    b.check_in_date,
    b.check_out_date,

    pay.payment_id,
    pay.payment_status,
    pay.amount AS payment_amount,
    pay.paid_at,
    pm.method_name AS payment_method,

    CASE
        WHEN pay.payment_id IS NULL THEN 'NO_PAYMENT'
        WHEN pay.payment_status = 'PAID' AND pay.amount < b.total_price THEN 'UNDERPAID'
        WHEN pay.payment_status = 'PAID' AND pay.amount > b.total_price THEN 'OVERPAID'
        ELSE pay.payment_status
    END AS reconciliation_status,

    u_g.first_name || ' ' || u_g.last_name AS guest_full_name,
    u_g.email AS guest_email,

    p.property_id,
    p.title AS property_title,

    u_h.first_name || ' ' || u_h.last_name AS host_full_name

FROM bookings b
JOIN guests g ON g.guest_id = b.guest_id
JOIN users u_g ON u_g.user_id = g.user_id
JOIN rooms r ON r.room_id = b.room_id
JOIN properties p ON p.property_id = r.property_id
JOIN hosts h ON h.user_id = p.host_id
JOIN users u_h ON u_h.user_id = h.user_id
LEFT JOIN payments pay ON pay.booking_id = b.booking_id
LEFT JOIN payment_methods pm ON pm.payment_method_id = pay.payment_method_id;


-- za gosti da mozat da vidat celosni informacii za eden property
CREATE OR REPLACE VIEW vw_property_summary AS
SELECT
    p.property_id,
    p.title,
    p.description,
    p.base_price,
    p.max_guests,
    p.status,
    p.created_at,
    lt.type_name AS listing_type,

    a.street,
    a.city,
    a.zip_code,
    co.country_name,
    co.country_code,

    h.user_id AS host_id,
    u.first_name || ' ' || u.last_name AS host_full_name,
    u.email AS host_email,

    COUNT(DISTINCT r.room_id) AS room_count,
    MIN(r.price_per_night) AS min_room_price,
    MAX(r.price_per_night) AS max_room_price,

    COUNT(DISTINCT rv.review_id) AS review_count,
    ROUND(AVG(rv.rating)::numeric, 2) AS avg_rating,

    cp.policy_name,
    cp.refund_percentage,
    cp.days_before_checking

FROM properties p
JOIN listing_types lt ON lt.listing_type_id = p.listing_type_id
JOIN addresses a ON a.address_id = p.address_id
JOIN countries co ON co.country_id = a.country_id
JOIN hosts h ON h.user_id = p.host_id
JOIN users u ON u.user_id = h.user_id
LEFT JOIN rooms r ON r.property_id = p.property_id
LEFT JOIN reviews rv ON rv.property_id = p.property_id
LEFT JOIN cancellation_policies cp ON cp.property_id = p.property_id
GROUP BY
    p.property_id, p.title, p.description, p.base_price, p.max_guests,
    p.status, p.created_at, lt.type_name,
    a.street, a.city, a.zip_code,
    co.country_name, co.country_code,
    h.user_id, u.first_name, u.last_name, u.email,
    cp.policy_name, cp.refund_percentage, cp.days_before_checking;


-- za host da vidi svoi performansi
CREATE OR REPLACE VIEW vw_host_performance AS
SELECT
    h.user_id AS host_id,
    u.first_name || ' ' || u.last_name AS host_full_name,
    u.email,
    u.created_at AS member_since,

    COUNT(DISTINCT p.property_id) AS total_properties,
    COUNT(DISTINCT r.room_id) AS total_rooms,

    COUNT(DISTINCT b.booking_id) AS total_bookings,
    COUNT(DISTINCT b.booking_id) FILTER (WHERE b.booking_status = 'COMPLETED') AS completed_bookings,
    COUNT(DISTINCT b.booking_id) FILTER (WHERE b.booking_status = 'CANCELLED') AS cancelled_bookings,

    COALESCE(SUM(pay.amount) FILTER (WHERE pay.payment_status = 'PAID'), 0) AS total_revenue,

    ROUND(AVG(rv.rating)::numeric, 2) AS avg_rating,
    COUNT(DISTINCT rv.review_id) AS total_reviews

FROM hosts h
JOIN users u ON u.user_id = h.user_id
LEFT JOIN properties p ON p.host_id = h.user_id
LEFT JOIN rooms r ON r.property_id = p.property_id
LEFT JOIN bookings b ON b.room_id = r.room_id
LEFT JOIN payments pay ON pay.booking_id = b.booking_id
LEFT JOIN reviews rv ON rv.property_id = p.property_id
GROUP BY h.user_id, u.first_name, u.last_name, u.email, u.created_at;



-- za sekoj gost da se vidi negovata aktivnost
CREATE OR REPLACE VIEW vw_guest_activity AS
SELECT
    g.guest_id,
    u.user_id,
    u.first_name || ' ' || u.last_name AS guest_full_name,
    u.email,
    u.phone,
    u.created_at AS member_since,

    COUNT(DISTINCT b.booking_id) AS total_bookings,
    COUNT(DISTINCT b.booking_id) FILTER (WHERE b.booking_status = 'COMPLETED') AS completed_bookings,
    COUNT(DISTINCT b.booking_id) FILTER (WHERE b.booking_status = 'CANCELLED') AS cancelled_bookings,
    COUNT(DISTINCT b.booking_id) FILTER (WHERE b.booking_status = 'NO_SHOW') AS no_shows,

    COALESCE(SUM(b.total_price) FILTER (WHERE b.booking_status = 'COMPLETED'), 0) AS total_spend,

    ROUND(AVG(b.total_price) FILTER (WHERE b.booking_status = 'COMPLETED')::numeric, 2) AS avg_booking_value,

    COUNT(DISTINCT rv.review_id) AS reviews_written,
    ROUND(AVG(rv.rating)::numeric, 2) AS avg_rating_given,

    COUNT(DISTINCT fl.favorite_id) AS total_favorites,

    MAX(b.booked_at) AS last_booking_at

FROM guests g
JOIN users u ON u.user_id = g.user_id
LEFT JOIN bookings b ON b.guest_id = g.guest_id
LEFT JOIN reviews rv ON rv.guest_id = g.guest_id
LEFT JOIN favorite_listings fl ON fl.user_id = u.user_id
WHERE g.guest_id <> -1
GROUP BY g.guest_id, u.user_id, u.first_name, u.last_name, u.email, u.phone, u.created_at;



-- pregled na prihodi po property
CREATE OR REPLACE VIEW vw_revenue_by_property AS
SELECT
    p.property_id,
    p.title AS property_title,
    p.status AS property_status,
    lt.type_name AS listing_type,
    a.city,
    co.country_name,

    u.user_id AS host_id,
    u.first_name || ' ' || u.last_name AS host_full_name,

    COUNT(DISTINCT b.booking_id) AS total_bookings,
    COUNT(DISTINCT b.booking_id) FILTER (WHERE b.booking_status = 'COMPLETED') AS completed_bookings,
    COUNT(DISTINCT b.booking_id) FILTER (WHERE b.booking_status = 'CANCELLED') AS cancelled_bookings,

    COALESCE(SUM(pay.amount) FILTER (WHERE pay.payment_status = 'PAID'), 0) AS total_revenue,

    ROUND(
        COALESCE(SUM(pay.amount) FILTER (WHERE pay.payment_status = 'PAID'), 0)
        / NULLIF(COUNT(DISTINCT b.booking_id) FILTER (WHERE pay.payment_status = 'PAID'), 0),
    2) AS avg_revenue_per_booking,

    ROUND(AVG(rv.rating)::numeric, 2) AS avg_rating,
    COUNT(DISTINCT rv.review_id) AS review_count

FROM properties p
JOIN listing_types lt ON lt.listing_type_id = p.listing_type_id
JOIN addresses a ON a.address_id = p.address_id
JOIN countries co ON co.country_id = a.country_id
JOIN hosts h ON h.user_id = p.host_id
JOIN users u ON u.user_id = h.user_id
LEFT JOIN rooms r ON r.property_id = p.property_id
LEFT JOIN bookings b ON b.room_id = r.room_id
LEFT JOIN payments pay ON pay.booking_id = b.booking_id
LEFT JOIN reviews rv ON rv.property_id = p.property_id
GROUP BY
    p.property_id, p.title, p.status, lt.type_name,
    a.city, co.country_name, u.user_id, u.first_name, u.last_name;



-- Za host i za gost - celosni informacii za sekoj review
CREATE OR REPLACE VIEW vw_review_summary AS
SELECT
    rv.review_id,
    rv.rating,
    rv.comment,
    rv.created_at AS reviewed_at,

    rv.booking_id,
    b.check_in_date,
    b.check_out_date,
    b.booking_status,

    u_g.user_id AS guest_user_id,
    u_g.first_name || ' ' || u_g.last_name AS guest_full_name,

    p.property_id,
    p.title AS property_title,
    a.city,
    co.country_name,

    u_h.user_id AS host_user_id,
    u_h.first_name || ' ' || u_h.last_name AS host_full_name,

    ROUND(AVG(rv.rating) OVER (PARTITION BY p.property_id)::numeric, 2) AS property_avg_rating

FROM reviews rv
JOIN bookings b ON b.booking_id = rv.booking_id
JOIN guests g ON g.guest_id = rv.guest_id
JOIN users u_g ON u_g.user_id = g.user_id
JOIN properties p ON p.property_id = rv.property_id
JOIN addresses a ON a.address_id = p.address_id
JOIN countries co ON co.country_id = a.country_id
JOIN hosts h ON h.user_id = p.host_id
JOIN users u_h ON u_h.user_id = h.user_id;



-- za gosti da vidat detalji za sekoja soba
CREATE OR REPLACE VIEW vw_room_details AS
SELECT
    r.room_id,
    r.room_name,
    r.status AS room_status,
    r.capacity,
    r.extra_capacity,
    r.capacity + r.extra_capacity AS max_capacity,
    r.price_per_night,
    r.extra_guest_price,
    r.description AS room_description,
    rt.type_name AS room_type,

    p.property_id,
    p.title AS property_title,
    p.status AS property_status,
    p.base_price AS property_base_price,
    lt.type_name AS listing_type,

    a.city,
    a.street,
    co.country_name,

    u.user_id AS host_id,
    u.first_name || ' ' || u.last_name AS host_full_name,

    COUNT(DISTINCT ra.amenity_id) AS amenity_count

FROM rooms r
JOIN room_types rt ON rt.room_type_id = r.room_type_id
JOIN properties p ON p.property_id = r.property_id
JOIN listing_types lt ON lt.listing_type_id = p.listing_type_id
JOIN addresses a ON a.address_id = p.address_id
JOIN countries co ON co.country_id = a.country_id
JOIN hosts h ON h.user_id = p.host_id
JOIN users u ON u.user_id = h.user_id
LEFT JOIN room_amenities ra ON ra.room_id = r.room_id
LEFT JOIN bookings b ON b.room_id = r.room_id
LEFT JOIN availability_blocks ab ON ab.room_id = r.room_id
GROUP BY
    r.room_id, r.room_name, r.status, r.capacity, r.extra_capacity,
    r.price_per_night, r.extra_guest_price, r.description,
    rt.type_name, p.property_id, p.title, p.status, p.base_price,
    lt.type_name, a.city, a.street, co.country_name,
    u.user_id, u.first_name, u.last_name;



-- Za gosti da vidat koga nekoja soba vo nekoj property e slobodna
CREATE OR REPLACE VIEW vw_availability_windows AS
SELECT
    aw.availability_window_id,
    aw.room_id,

    r.room_name,
    r.capacity,
    r.extra_capacity,
    r.price_per_night,
    r.status AS room_status,

    aw.available_date,
    aw.status AS availability_status,

    CASE
        WHEN aw.available_date < CURRENT_DATE THEN 'EXPIRED'
        WHEN aw.status = 'AVAILABLE' THEN 'AVAILABLE'
        WHEN aw.status = 'TAKEN' THEN 'TAKEN'
        ELSE aw.status
    END AS final_availability_status

FROM availability_windows aw
JOIN rooms r ON r.room_id = aw.room_id
WHERE r.status = 'ACTIVE';