DatabaseProgramming: Code_phase4.sql

-- FUNCTION: Креирање trip

CREATE OR REPLACE FUNCTION fn_create_trip(
    p_user_id INT,
    p_city_name VARCHAR,
    p_start_date DATE,
    p_end_date DATE,
    p_total_budget DECIMAL(10,2),
    p_title VARCHAR DEFAULT NULL
)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_city_id INT;
    v_status_id INT;
    v_trip_id INT;
BEGIN
    IF p_start_date IS NULL THEN
        RAISE EXCEPTION 'Start date is required.';
    END IF;

    IF p_end_date IS NULL THEN
        RAISE EXCEPTION 'End date is required.';
    END IF;

    IF p_end_date < p_start_date THEN
        RAISE EXCEPTION 'End date cannot be before start date.';
    END IF;

    IF p_total_budget <= 0 THEN
        RAISE EXCEPTION 'Budget must be greater than 0.';
    END IF;

    SELECT city_id
    INTO v_city_id
    FROM city
    WHERE LOWER(name) = LOWER(p_city_name)
    LIMIT 1;

    IF v_city_id IS NULL THEN
        RAISE EXCEPTION 'City "%" does not exist.', p_city_name;
    END IF;

    IF NOT EXISTS (
        SELECT 1
        FROM app_user
        WHERE user_id = p_user_id
    ) THEN
        RAISE EXCEPTION 'User with id % does not exist.', p_user_id;
    END IF;

    INSERT INTO trip_status (
        user_id,
        status_name,
        status_comment,
        updated_at
    )
    VALUES (
        p_user_id,
        'in_progress',
        'Trip created',
        NOW()
    )
    RETURNING status_id INTO v_status_id;

    INSERT INTO trip (
        user_id,
        city_id,
        status_id,
        history_id,
        title,
        start_date,
        end_date,
        total_budget,
        estimated_cost
    )
    VALUES (
        p_user_id,
        v_city_id,
        v_status_id,
        NULL,
        COALESCE(p_title, 'Trip to ' || p_city_name),
        p_start_date,
        p_end_date,
        p_total_budget,
        0
    )
    RETURNING trip_id INTO v_trip_id;

    RETURN v_trip_id;
END;
$$;


-- test

SELECT fn_create_trip(
    1,
    'City_7',
    '2026-08-01',
    '2026-08-05',
    700.00,
    'August Skopje Trip'
) AS new_trip_id;

SELECT *
FROM trip
WHERE trip_id = 4523757 ;



-- FUNCTION: Најди trip_id

CREATE OR REPLACE FUNCTION fn_find_trip_id(
    p_username VARCHAR,
    p_city_name VARCHAR,
    p_start_date DATE,
    p_end_date DATE DEFAULT NULL,
    p_title VARCHAR DEFAULT NULL
)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    v_trip_id INT;
BEGIN
    SELECT t.trip_id
    INTO v_trip_id
    FROM trip t
    JOIN app_user u ON t.user_id = u.user_id
    JOIN city c ON t.city_id = c.city_id
    WHERE LOWER(u.username) = LOWER(p_username)
      AND LOWER(c.name) = LOWER(p_city_name)
      AND t.start_date = p_start_date
      AND (p_end_date IS NULL OR t.end_date = p_end_date)
      AND (
            p_title IS NULL
            OR LOWER(t.title) = LOWER(p_title)
          )
    ORDER BY t.trip_id DESC
    LIMIT 1;

    IF v_trip_id IS NULL THEN
        RAISE EXCEPTION 'Trip for user "%", city "%", start date "%", title "%" does not exist.',
            p_username, p_city_name, p_start_date, p_title;
    END IF;

    RETURN v_trip_id;
END;
$$;


-- test

SELECT
    CASE
        WHEN fn_find_trip_id(
            u.username,
            c.name,
            t.start_date,
            t.end_date,
            t.title
        ) = t.trip_id
        THEN 'TEST PASSED'
        ELSE 'TEST FAILED'
    END AS test_result,
    t.trip_id
FROM trip t
JOIN app_user u ON t.user_id = u.user_id
JOIN city c ON t.city_id = c.city_id
LIMIT 5;



-- FUNCTION: Пресметување вкупен estimated cost за trip

CREATE OR REPLACE FUNCTION fn_calculate_trip_estimated_cost(
    p_username VARCHAR,
    p_city_name VARCHAR,
    p_start_date DATE,
    p_end_date DATE DEFAULT NULL,
    p_title VARCHAR DEFAULT NULL
)
RETURNS DECIMAL(10,2)
LANGUAGE plpgsql
AS $$
DECLARE
    v_trip_id INT;
    v_total_cost DECIMAL(10,2);
BEGIN
    v_trip_id := fn_find_trip_id(
        p_username,
        p_city_name,
        p_start_date,
        p_end_date,
        p_title
    );

    SELECT COALESCE(SUM(pi.estimated_cost), 0)
    INTO v_total_cost
    FROM plan_item pi
    JOIN trip_day td ON pi.trip_day_id = td.trip_day_id
    WHERE td.trip_id = v_trip_id;

    RETURN v_total_cost;
END;
$$;


-- test

SELECT
    u.username,
    c.name AS city_name,
    t.start_date,
    t.end_date,
    t.title,
    t.trip_id
FROM trip t
JOIN app_user u ON t.user_id = u.user_id
JOIN city c ON t.city_id = c.city_id
ORDER BY t.trip_id DESC
LIMIT 10;



-- PROCEDURE: Додавање trip member

CREATE OR REPLACE PROCEDURE pr_add_trip_member(
    p_owner_username VARCHAR,
    p_city_name VARCHAR,
    p_start_date DATE,
    p_member_username VARCHAR,
    p_title VARCHAR DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_trip_id INT;
    v_member_user_id INT;
    v_member_first_name VARCHAR;
    v_member_last_name VARCHAR;
    v_trip_member_id INT;
BEGIN
    -- 1. Најди го trip_id
    v_trip_id := fn_find_trip_id(
        p_owner_username,
        p_city_name,
        p_start_date,
        NULL,
        p_title
    );

    -- 2. Најди user податоци за member username
    SELECT
        user_id,
        first_name,
        last_name
    INTO
        v_member_user_id,
        v_member_first_name,
        v_member_last_name
    FROM app_user
    WHERE LOWER(username) = LOWER(p_member_username);

    IF v_member_user_id IS NULL THEN
        RAISE EXCEPTION 'Member user with username "%" does not exist.', p_member_username;
    END IF;

    -- 3. Провери дали member веќе е додаден во trip
    IF EXISTS (
        SELECT 1
        FROM trip_member
        WHERE trip_id = v_trip_id
          AND user_id = v_member_user_id
    ) THEN
        RAISE EXCEPTION 'User "%" is already a member of trip %.',
            p_member_username, v_trip_id;
    END IF;

    -- 4. Додај member во trip
    INSERT INTO trip_member (
        trip_id,
        user_id,
        first_name,
        last_name,
        username,
        role
    )
    VALUES (
        v_trip_id,
        v_member_user_id,
        v_member_first_name,
        v_member_last_name,
        p_member_username,
        'member'
    )
    RETURNING trip_member_id INTO v_trip_member_id;

    RAISE NOTICE 'User "%" added to trip "%" successfully. Trip member id: %',
        p_member_username, v_trip_id, v_trip_member_id;
END;
$$;

CALL pr_add_trip_member(
    'user_1',
    'City_1',
    '2026-05-16',
    'user_64192',
    Null
);


-- test

SELECT
    tm.trip_member_id,
    tm.trip_id,
    tm.user_id,
    tm.first_name,
    tm.last_name,
    tm.username,
    tm.role,
    t.title,
    t.start_date,
    c.name AS city_name
FROM trip_member tm
JOIN trip t ON tm.trip_id = t.trip_id
JOIN city c ON t.city_id = c.city_id
ORDER BY tm.trip_member_id DESC
LIMIT 5;



-- PROCEDURE: Додавање plan item во trip

CREATE OR REPLACE PROCEDURE pr_add_plan_item_to_trip(
    p_username VARCHAR,
    p_city_name VARCHAR,
    p_start_date DATE,
    p_day_number INT,
    p_place_name VARCHAR,
    p_start_time TIME,
    p_end_time TIME,
    p_estimated_cost DECIMAL(10,2),
    p_notes TEXT DEFAULT NULL,
    p_title VARCHAR DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_trip_id INT;
    v_trip_day_id INT;
    v_place_id INT;
    v_schedule_time_id INT;
    v_plan_item_id INT;
    v_current_estimated_cost DECIMAL(10,2);
    v_total_budget DECIMAL(10,2);
BEGIN
    IF p_day_number <= 0 THEN
        RAISE EXCEPTION 'Day number must be greater than 0.';
    END IF;

    IF p_end_time <= p_start_time THEN
        RAISE EXCEPTION 'End time must be after start time.';
    END IF;

    IF p_estimated_cost < 0 THEN
        RAISE EXCEPTION 'Estimated cost cannot be negative.';
    END IF;

    v_trip_id := fn_find_trip_id(
        p_username,
        p_city_name,
        p_start_date,
        NULL,
        p_title
    );

    SELECT trip_day_id
    INTO v_trip_day_id
    FROM trip_day
    WHERE trip_id = v_trip_id
      AND day_number = p_day_number;

    IF v_trip_day_id IS NULL THEN
        RAISE EXCEPTION 'Day % does not exist for this trip.', p_day_number;
    END IF;

    SELECT p.place_id
    INTO v_place_id
    FROM place p
    JOIN trip t ON p.city_id = t.city_id
    WHERE t.trip_id = v_trip_id
      AND LOWER(p.name) = LOWER(p_place_name)
    LIMIT 1;

    IF v_place_id IS NULL THEN
        RAISE EXCEPTION 'Place "%" does not exist in the trip city.', p_place_name;
    END IF;

    INSERT INTO schedule_time (
        scheduled_start_time,
        scheduled_end_time
    )
    VALUES (
        p_start_time,
        p_end_time
    )
    RETURNING schedule_time_id INTO v_schedule_time_id;

    INSERT INTO plan_item (
        trip_day_id,
        place_id,
        schedule_time_id,
        estimated_cost,
        notes
    )
    VALUES (
        v_trip_day_id,
        v_place_id,
        v_schedule_time_id,
        p_estimated_cost,
        p_notes
    )
    RETURNING plan_item_id INTO v_plan_item_id;

    SELECT COALESCE(SUM(pi.estimated_cost), 0)
    INTO v_current_estimated_cost
    FROM plan_item pi
    JOIN trip_day td ON pi.trip_day_id = td.trip_day_id
    WHERE td.trip_id = v_trip_id;

    SELECT total_budget
    INTO v_total_budget
    FROM trip
    WHERE trip_id = v_trip_id;

    IF v_current_estimated_cost >= v_total_budget THEN
        RAISE EXCEPTION 'Cannot update trip estimated cost because it reaches/exceeds total budget.';
    END IF;

    UPDATE trip
    SET estimated_cost = v_current_estimated_cost
    WHERE trip_id = v_trip_id;

    RAISE NOTICE 'Plan item added successfully. Plan item id: %. New trip estimated cost: %',
        v_plan_item_id, v_current_estimated_cost;
END;
$$;


CALL pr_add_plan_item_to_trip(
    'user_1',
    'City_1',
    '2026-05-16',
    1,
    'Place_1068',
    '10:00',
    '12:00',
    100,
    'Visit and take photos',
    Null
);


-- test

SELECT
    u.user_id,
    u.username,
    c.city_id,
    c.name AS city_name,
    t.trip_id,
    t.title,
    t.start_date,
    t.end_date,
    t.total_budget,
    t.estimated_cost,
    td.trip_day_id,
    td.day_number,
    p.place_id,
    p.name AS place_name
FROM app_user u
JOIN trip t ON u.user_id = t.user_id
JOIN city c ON t.city_id = c.city_id
JOIN trip_day td ON t.trip_id = td.trip_id
JOIN place p ON p.city_id = c.city_id
WHERE LOWER(u.username) = LOWER('user_1')
  AND LOWER(c.name) = LOWER('City_1')
  AND t.start_date = '2026-05-16'
  AND td.day_number = 1
  AND LOWER(p.name) = LOWER('Place_1068');



-- PROCEDURE: Додавање place во favourites

CREATE OR REPLACE PROCEDURE pr_add_place_to_favourites(
    p_username VARCHAR,
    p_place_name VARCHAR
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id INT;
    v_place_id INT;
BEGIN
    SELECT user_id
    INTO v_user_id
    FROM app_user
    WHERE LOWER(username) = LOWER(p_username);

    IF v_user_id IS NULL THEN
        RAISE EXCEPTION 'User with username "%" does not exist.', p_username;
    END IF;

    SELECT place_id
    INTO v_place_id
    FROM place
    WHERE LOWER(name) = LOWER(p_place_name)
    LIMIT 1;

    IF v_place_id IS NULL THEN
        RAISE EXCEPTION 'Place "%" does not exist.', p_place_name;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM favourites
        WHERE user_id = v_user_id
          AND place_id = v_place_id
    ) THEN
        RAISE EXCEPTION 'Place "%" is already in favourites for user "%".',
            p_place_name, p_username;
    END IF;

    INSERT INTO favourites (
        user_id,
        place_id
    )
    VALUES (
        v_user_id,
        v_place_id
    );

    RAISE NOTICE 'Place "%" added to favourites for user "%".',
        p_place_name, p_username;
END;
$$;


CALL pr_add_place_to_favourites(
    'user_417155',
    'Place_854864'
);


-- test

SELECT
    f.user_id,
    u.username,
    f.place_id,
    p.name AS place_name
FROM favourites f
JOIN app_user u ON f.user_id = u.user_id
JOIN place p ON f.place_id = p.place_id
WHERE LOWER(u.username) = LOWER('user_417155')
  AND LOWER(p.name) = LOWER('Place_854864');



-- PROCEDURE: Додавање review за place од trip

CREATE OR REPLACE PROCEDURE pr_add_review_for_trip_place(
    p_username VARCHAR,
    p_city_name VARCHAR,
    p_start_date DATE,
    p_day_number INT,
    p_place_name VARCHAR,
    p_rating DECIMAL(3,2),
    p_comment TEXT DEFAULT NULL,
    p_title VARCHAR DEFAULT NULL
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_user_id INT;
    v_trip_id INT;
    v_plan_item_id INT;
    v_review_id INT;
BEGIN
    IF p_rating < 1 OR p_rating > 5 THEN
        RAISE EXCEPTION 'Rating must be between 1 and 5.';
    END IF;

    SELECT user_id
    INTO v_user_id
    FROM app_user
    WHERE LOWER(username) = LOWER(p_username);

    IF v_user_id IS NULL THEN
        RAISE EXCEPTION 'User with username "%" does not exist.', p_username;
    END IF;

    v_trip_id := fn_find_trip_id(
        p_username,
        p_city_name,
        p_start_date,
        NULL,
        p_title
    );

    SELECT pi.plan_item_id
    INTO v_plan_item_id
    FROM plan_item pi
    JOIN trip_day td ON pi.trip_day_id = td.trip_day_id
    JOIN place p ON pi.place_id = p.place_id
    WHERE td.trip_id = v_trip_id
      AND td.day_number = p_day_number
      AND LOWER(p.name) = LOWER(p_place_name)
    LIMIT 1;

    IF v_plan_item_id IS NULL THEN
        RAISE EXCEPTION 'Plan item for place "%" on day % does not exist.',
            p_place_name, p_day_number;
    END IF;

    INSERT INTO review (
        user_id,
        plan_item_id,
        rating,
        comment,
        visited_at,
        created_at
    )
    VALUES (
        v_user_id,
        v_plan_item_id,
        p_rating,
        p_comment,
        NOW(),
        NOW()
    )
    RETURNING review_id INTO v_review_id;

    RAISE NOTICE 'Review added successfully. Review id: %', v_review_id;
END;
$$;


CALL pr_add_review_for_trip_place(
    'user_417155',
    'City_246',
    '2025-11-10',
    2,
    'Place_854864',
    3,
    NULL,
    'Trip_1999999'
);


-- test

SELECT
    CASE
        WHEN p.rating = ROUND(AVG(r.rating), 2)
        THEN 'TEST PASSED: place.rating is correctly updated'
        ELSE 'TEST FAILED: place.rating is not equal to average review rating'
    END AS test_result,
    p.place_id,
    p.name AS place_name,
    p.rating AS current_place_rating,
    ROUND(AVG(r.rating), 2) AS calculated_avg_rating,
    COUNT(r.review_id) AS total_reviews
FROM place p
JOIN plan_item pi ON p.place_id = pi.place_id
LEFT JOIN review r ON pi.plan_item_id = r.plan_item_id
WHERE p.place_id = (
    SELECT pi2.place_id
    FROM review r2
    JOIN plan_item pi2 ON r2.plan_item_id = pi2.plan_item_id
    ORDER BY r2.review_id DESC
    LIMIT 1
)
GROUP BY
    p.place_id,
    p.name,
    p.rating;



-- PROCEDURE: Комплетирање trip и креирање trip history

CREATE OR REPLACE PROCEDURE pr_complete_trip(
    p_username VARCHAR,
    p_city_name VARCHAR,
    p_start_date DATE,
    p_end_date DATE DEFAULT NULL,
    p_title VARCHAR DEFAULT NULL,
    p_notes TEXT DEFAULT 'Trip completed successfully'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_trip_id INT;
    v_user_id INT;
    v_total_spent DECIMAL(10,2);
    v_history_id INT;
    v_status_id INT;
BEGIN
    SELECT user_id
    INTO v_user_id
    FROM app_user
    WHERE LOWER(username) = LOWER(p_username);

    IF v_user_id IS NULL THEN
        RAISE EXCEPTION 'User with username "%" does not exist.', p_username;
    END IF;

    v_trip_id := fn_find_trip_id(
        p_username,
        p_city_name,
        p_start_date,
        p_end_date,
        p_title
    );

    SELECT estimated_cost
    INTO v_total_spent
    FROM trip
    WHERE trip_id = v_trip_id;

    IF v_total_spent IS NULL THEN
        RAISE EXCEPTION 'Trip not found.';
    END IF;

    INSERT INTO trip_history (
        user_id,
        total_spent,
        notes,
        completed_at
    )
    VALUES (
        v_user_id,
        v_total_spent,
        p_notes,
        NOW()
    )
    RETURNING history_id INTO v_history_id;

    INSERT INTO trip_status (
        user_id,
        status_name,
        status_comment,
        updated_at
    )
    VALUES (
        v_user_id,
        'completed',
        'Trip marked as completed',
        NOW()
    )
    RETURNING status_id INTO v_status_id;

    UPDATE trip
    SET history_id = v_history_id,
        status_id = v_status_id
    WHERE trip_id = v_trip_id;

    RAISE NOTICE 'Trip % completed successfully. History id: %, Status id: %',
        v_trip_id, v_history_id, v_status_id;
END;
$$;



CALL pr_complete_trip(
    'user_88113',
    'City_119',
    '2025-12-07',
    '2025-12-18',
     NULL,
    'Trip completed successfully'
);


-- test

SELECT
    CASE
        WHEN ts.status_name = 'completed'
         AND th.history_id IS NOT NULL
        THEN 'TEST PASSED: Trip is completed'
        ELSE 'TEST FAILED: Trip is not completed'
    END AS test_result,
    t.trip_id,
    ts.status_name,
    th.history_id,
    th.total_spent,
    th.notes
FROM trip t
JOIN app_user u ON t.user_id = u.user_id
JOIN city c ON t.city_id = c.city_id
JOIN trip_status ts ON t.status_id = ts.status_id
JOIN trip_history th ON t.history_id = th.history_id
WHERE LOWER(u.username) = LOWER('user_88113')
  AND LOWER(c.name) = LOWER('City_119')
  AND t.start_date = '2025-12-07'
  AND t.end_date = '2025-12-18';




-- TRIGGER FUNCTION: Ажурирање rating на place кога ќе се додаде/промени/избрише review

CREATE OR REPLACE FUNCTION fn_update_place_rating_after_review()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
    v_place_id INT;
    v_avg_rating DECIMAL(3,2);
BEGIN
    -- Ако е DELETE, земаме OLD.plan_item_id
    IF TG_OP = 'DELETE' THEN
        SELECT pi.place_id
        INTO v_place_id
        FROM plan_item pi
        WHERE pi.plan_item_id = OLD.plan_item_id;
    ELSE
        SELECT pi.place_id
        INTO v_place_id
        FROM plan_item pi
        WHERE pi.plan_item_id = NEW.plan_item_id;
    END IF;

    IF v_place_id IS NULL THEN
        RETURN COALESCE(NEW, OLD);
    END IF;

    SELECT COALESCE(ROUND(AVG(r.rating), 2), 0)
    INTO v_avg_rating
    FROM review r
    JOIN plan_item pi ON r.plan_item_id = pi.plan_item_id
    WHERE pi.place_id = v_place_id;

    UPDATE place
    SET rating = v_avg_rating
    WHERE place_id = v_place_id;

    RAISE NOTICE 'Place % rating updated to %.', v_place_id, v_avg_rating;

    RETURN COALESCE(NEW, OLD);
END;
$$;



-- DROP стар trigger ако постои

DROP TRIGGER IF EXISTS trg_update_place_rating_after_review ON review;



-- CREATE TRIGGER


CREATE TRIGGER trg_update_place_rating_after_review
AFTER INSERT OR UPDATE OR DELETE ON review
FOR EACH ROW
EXECUTE FUNCTION fn_update_place_rating_after_review();


-- test

SELECT
    pi.plan_item_id,
    pi.place_id,
    p.name,
    p.rating
FROM plan_item pi
JOIN place p ON pi.place_id = p.place_id
ORDER BY pi.plan_item_id
LIMIT 10;