DatabaseCreation: DML.sql

-- Прво ги бришеме старите погледи за да нема конфликт
DROP VIEW IF EXISTS v_notifications CASCADE;
DROP VIEW IF EXISTS v_user_favourites CASCADE;
DROP VIEW IF EXISTS v_reviews CASCADE;
DROP VIEW IF EXISTS trip_transport_view CASCADE;
DROP VIEW IF EXISTS v_trip_cost CASCADE;
DROP VIEW IF EXISTS v_trip_members_full CASCADE;
DROP VIEW IF EXISTS user_profile_overview CASCADE;
DROP VIEW IF EXISTS v_trip_itinerary_byDay CASCADE;
DROP VIEW IF EXISTS v_trip_overview CASCADE;
DROP VIEW IF EXISTS v_category_tree CASCADE;
DROP VIEW IF EXISTS popular_places_view_by_reviews CASCADE;
DROP VIEW IF EXISTS v_place_details CASCADE;

---

-- 1. v_place_details
CREATE OR REPLACE VIEW v_place_details AS
SELECT
    p.place_id,
    p.name,
    p.rating,
    p.avg_price,
    ci.name AS city,
    co.name AS country,
    c.name AS category
FROM PLACE p
JOIN CITY ci ON p.city_id = ci.city_id
JOIN COUNTRY co ON ci.country_id = co.county_id -- Поправено: референца кон county_id
JOIN CATEGORY c ON p.category_id = c.category_id;

-- 2. v_trip_overview
CREATE OR REPLACE VIEW v_trip_overview AS
SELECT
    t.trip_id,
    t.title,
    u.username,
    ci.name AS city,
    co.name AS country,
    t.start_date,
    t.end_date,
    t.total_budget,
    t.estimated_cost,
    ts.status_name
FROM TRIP t
JOIN APP_USER u ON t.user_id = u.user_id
JOIN CITY ci ON t.city_id = ci.city_id
JOIN COUNTRY co ON ci.country_id = co.county_id -- Поправено: референца кон county_id
LEFT JOIN TRIP_STATUS ts ON t.status_id = ts.status_id;

-- 3. popular_places_view_by_reviews
CREATE OR REPLACE VIEW popular_places_view_by_reviews AS
SELECT
    p.place_id,
    p.name,
    COUNT(r.review_id) AS total_reviews
FROM PLACE p
LEFT JOIN PLAN_ITEM pi ON p.place_id = pi.place_id
LEFT JOIN REVIEW r ON pi.plan_item_id = r.plan_item_id
GROUP BY p.place_id, p.name;

-- 4. v_category_tree
CREATE OR REPLACE VIEW v_category_tree AS
SELECT
    c1.category_id,
    c1.name AS category,
    c2.name AS parent_category
FROM CATEGORY c1
LEFT JOIN CATEGORY c2 ON c1.parent_id = c2.category_id;

-- 5. v_trip_itinerary_byDay
CREATE OR REPLACE VIEW v_trip_itinerary_byDay AS
SELECT
    t.trip_id,
    td.day_number,
    td.actual_date,
    p.name AS place,
    pi.estimated_cost,
    st.scheduled_start_time,
    st.scheduled_end_time
FROM TRIP_DAY td
JOIN TRIP t ON td.trip_id = t.trip_id
JOIN PLAN_ITEM pi ON pi.trip_day_id = td.trip_day_id
JOIN PLACE p ON pi.place_id = p.place_id
LEFT JOIN SCHEDULE_TIME st ON pi.schedule_time_id = st.schedule_time_id;

-- 6. user_profile_overview
CREATE OR REPLACE VIEW user_profile_overview AS
SELECT
    u.user_id,
    u.username,
    u.first_name,
    u.last_name,
    COUNT(t.trip_id) AS total_trips
FROM APP_USER u
LEFT JOIN TRIP t ON u.user_id = t.user_id
GROUP BY u.user_id, u.username, u.first_name, u.last_name;

-- 7. v_trip_members_full
CREATE OR REPLACE VIEW v_trip_members_full AS
SELECT
    tm.trip_id,
    tm.username,
    tm.first_name,
    tm.last_name,
    tm.role
FROM TRIP_MEMBER tm;

-- 8. v_trip_cost
CREATE OR REPLACE VIEW v_trip_cost AS
SELECT
    t.trip_id,
    t.title,
    t.total_budget,
    t.estimated_cost,
    (t.total_budget - t.estimated_cost) AS remaining_budget
FROM TRIP t;

-- 9. trip_transport_view
CREATE OR REPLACE VIEW trip_transport_view AS
SELECT
    t.trip_id,
    tm.name AS transport_mode,
    ds.distance_km,
    ds.duration_minutes
FROM TRIP t
JOIN DISTANCE_SEGMENT ds ON t.trip_id = ds.trip_id
JOIN TRANSPORT_MODE tm ON ds.transport_mode_id = tm.transport_mode_id;

-- 10. v_reviews
CREATE OR REPLACE VIEW v_reviews AS
SELECT
    r.review_id,
    u.username,
    p.name AS place,
    r.rating,
    r.comment,
    r.visited_at
FROM REVIEW r
LEFT JOIN APP_USER u ON r.user_id = u.user_id
LEFT JOIN PLAN_ITEM pi ON r.plan_item_id = pi.plan_item_id
LEFT JOIN PLACE p ON pi.place_id = p.place_id;

-- 11. v_user_favourites
CREATE OR REPLACE VIEW v_user_favourites AS
SELECT
    f.user_id,
    u.username,
    p.place_id,
    p.name AS place_name,
    p.rating
FROM FAVOURITES f
JOIN APP_USER u ON f.user_id = u.user_id
JOIN PLACE p ON f.place_id = p.place_id;

-- 12. v_notifications
CREATE OR REPLACE VIEW v_notifications AS
SELECT
    n.notification_id,
    tm.username,
    gc.message_text,
    n.is_read
FROM NOTIFICATION n
LEFT JOIN TRIP_MEMBER tm ON n.trip_member_id = tm.trip_member_id
LEFT JOIN GROUP_CHAT gc ON n.group_id = gc.group_id;


-- 13. v_analysis_trip_cost_details

CREATE OR REPLACE VIEW v_analysis_trip_cost_details AS
SELECT
    t.trip_id,
    t.title AS trip_title,
    t.total_budget,
    t.estimated_cost AS trip_estimated_cost,

    td.trip_day_id,
    td.day_number,
    td.actual_date,

    pi.plan_item_id,
    pi.estimated_cost AS activity_cost,

    p.place_id,
    p.name AS place_name,
    p.avg_price AS place_avg_price,

    st.scheduled_start_time,
    st.scheduled_end_time
FROM trip t
JOIN trip_day td
    ON t.trip_id = td.trip_id
JOIN plan_item pi
    ON td.trip_day_id = pi.trip_day_id
JOIN place p
    ON pi.place_id = p.place_id
LEFT JOIN schedule_time st
    ON pi.schedule_time_id = st.schedule_time_id;


SELECT current_schema();



EXPLAIN ANALYZE
SELECT * FROM v_trip_member_simple WHERE trip_member_id = 138014;

-- index za view_1

EXPLAIN ANALYZE
SELECT * FROM v_place_details WHERE place_id = 541;


-- index za view_2

EXPLAIN ANALYZE
SELECT * FROM v_trip_overview WHERE trip_id = 55919;


-- index za view_3

EXPLAIN analyze
SELECT * FROM popular_places_view_by_reviews WHERE place_id = 175;


-- index za view_4

EXPLAIN analyze
SELECT * FROM v_category_tree WHERE category_id = 100;


-- index za view_5

EXPLAIN analyze
SELECT * FROM v_trip_itinerary_byDay WHERE trip_id = 150;


-- index za view_6

EXPLAIN analyze
SELECT * FROM user_profile_overview WHERE user_id = 243212;


-- index za view_7

EXPLAIN analyze
SELECT * FROM v_trip_members_full WHERE trip_id = 1;


-- index za view_8

EXPLAIN analyze
SELECT * FROM v_trip_cost WHERE trip_id = 100892;


-- index za view_9

EXPLAIN analyze
SELECT * FROM trip_transport_view WHERE trip_id = 1001045;


-- index za view_10

EXPLAIN analyze
SELECT * FROM v_reviews WHERE review_id = 353885;


-- index za view_11

EXPLAIN analyze
SELECT * FROM v_user_favourites WHERE user_id = 487177;

-- index za view_12

EXPLAIN analyze
SELECT * FROM v_notifications WHERE notification_id = 412857;

-- index za view_13
EXPLAIN ANALYZE
SELECT * FROM v_analysis_trip_cost_details WHERE trip_id = 500;


---------- novi views
DROP VIEW IF EXISTS v_heavy_trip_members_plan CASCADE;


-- view 14

CREATE OR REPLACE VIEW v_heavy_trip_members_plan AS
SELECT
    t.trip_id,
    t.title,
    t.start_date,
    t.end_date,
    t.total_budget,
    t.estimated_cost,

    tm.trip_member_id,
    tm.user_id AS member_user_id,
    tm.username AS member_username,
    tm.role,

    td.trip_day_id,
    td.day_number,
    td.actual_date,

    pi.plan_item_id,
    pi.place_id,
    pi.schedule_time_id,
    pi.estimated_cost AS plan_item_cost,
    pi.notes
FROM trip t
JOIN trip_member tm
    ON t.trip_id = tm.trip_id
JOIN trip_day td
    ON t.trip_id = td.trip_id
JOIN plan_item pi
    ON td.trip_day_id = pi.trip_day_id;