DatabaseProgramming: Phase4.sql

-- Phase 4(1)

-- ============================================
-- FUNCTION
-- Calculate total money spent by a participant
-- Improved with validation and realistic checks
-- ============================================

CREATE OR REPLACE FUNCTION get_total_participant_cost(
    p_participant_id INT
)
    RETURNS DECIMAL(12, 2)
AS
$$
DECLARE
    total_flight       DECIMAL(12, 2) := 0;
    total_bus          DECIMAL(12, 2) := 0;
    total_hotel        DECIMAL(12, 2) := 0;
    total_cost         DECIMAL(12, 2);
    participant_exists INT;
    participant_status VARCHAR(20);
BEGIN

    -- Check if participant exists
    SELECT COUNT(*), MAX(status)
    INTO participant_exists, participant_status
    FROM trip_participants
    WHERE id = p_participant_id;

    IF participant_exists = 0 THEN
        RAISE EXCEPTION
            'Participant with ID % does not exist',
            p_participant_id;
    END IF;

    -- Prevent calculating costs for cancelled participants
    IF participant_status = 'cancelled' THEN
        RAISE EXCEPTION
            'Cannot calculate cost for cancelled participant %',
            p_participant_id;
    END IF;

    -- Flight booking total
    SELECT COALESCE(SUM(price), 0)
    INTO total_flight
    FROM flight_bookings
    WHERE trip_participant_id = p_participant_id
      AND price >= 0;

    -- Bus booking total
    SELECT COALESCE(SUM(price), 0)
    INTO total_bus
    FROM bus_bookings
    WHERE trip_participant_id = p_participant_id
      AND price >= 0;

    -- Hotel booking total
    SELECT COALESCE(SUM(hr.price), 0)
    INTO total_hotel
    FROM trip_participants_hotel_rooms tphr
             JOIN hotel_rooms hr
                  ON hr.id = tphr.hotel_room_id
    WHERE tphr.trip_participant_id = p_participant_id
      AND hr.price >= 0;

    total_cost :=
            total_flight +
            total_bus +
            total_hotel;

    RETURN ROUND(total_cost, 2);

END;
$$ LANGUAGE plpgsql;

-- Example usage
--SELECT get_total_participant_cost(5);

-- Remove duplicated procedure
DROP PROCEDURE IF EXISTS confirm_pending_participants;

-- ============================================
-- PROCEDURE
-- Automatically confirm participants
-- Improved with realistic passport validation
-- ============================================

CREATE OR REPLACE PROCEDURE confirm_trip_participants()
AS
$$
BEGIN

    UPDATE trip_participants
    SET status = 'confirmed'
    WHERE status = 'pending'

      -- Passport must exist
      AND passport_number IS NOT NULL

      -- Remove empty spaces
      AND LENGTH(TRIM(passport_number)) BETWEEN 6 AND 12

      -- Prevent fake placeholder values
      AND UPPER(TRIM(passport_number)) NOT IN (
                                               'N/A',
                                               'UNKNOWN',
                                               'NONE',
                                               '-'
        )

      -- Passport must contain only letters and numbers
      AND passport_number ~ '^[A-Za-z0-9]+$'

      -- Passport must contain at least 2 numbers
      AND passport_number ~ '[0-9].*[0-9]'

      -- Passport must contain at least 1 letter
      AND passport_number ~ '[A-Za-z]';

    -- Notify if no rows updated
    IF NOT FOUND THEN
        RAISE NOTICE
            'No participants eligible for confirmation';
    END IF;

END;
$$ LANGUAGE plpgsql;


-- Example usage
--CALL confirm_trip_participants();

-- ============================================
-- TRIGGER FUNCTION
-- Prevent overbooking hotel rooms
-- Improved with room existence checks
-- ============================================

CREATE OR REPLACE FUNCTION check_room_capacity()
    RETURNS TRIGGER
AS
$$
DECLARE
    room_capacity INT;
    current_count INT;
BEGIN

    -- Validate room exists + capacity
    SELECT capacity
    INTO room_capacity
    FROM hotel_rooms
    WHERE id = NEW.hotel_room_id;

    IF room_capacity IS NULL THEN
        RAISE EXCEPTION
            'Hotel room with ID % does not exist',
            NEW.hotel_room_id;
    END IF;

    IF room_capacity <= 0 THEN
        RAISE EXCEPTION
            'Invalid capacity for room ID %',
            NEW.hotel_room_id;
    END IF;

    -- REALISTIC: count only overlapping bookings
    SELECT COUNT(*)
    INTO current_count
    FROM trip_participants_hotel_rooms
    WHERE hotel_room_id = NEW.hotel_room_id
      AND NEW.check_in < check_out
      AND NEW.check_out > check_in;

    IF current_count >= room_capacity THEN
        RAISE EXCEPTION
            'Room capacity exceeded for room ID % in selected dates',
            NEW.hotel_room_id;
    END IF;

    RETURN NEW;

END;
$$ LANGUAGE plpgsql;

-- Create Trigger
DROP TRIGGER IF EXISTS trg_check_room_capacity
    ON trip_participants_hotel_rooms;

CREATE TRIGGER trg_check_room_capacity
    BEFORE INSERT
    ON trip_participants_hotel_rooms
    FOR EACH ROW
EXECUTE FUNCTION check_room_capacity();

-- ============================================
-- FUNCTION
-- Calculate trip execution score
-- Improved with validation checks
-- ============================================

CREATE OR REPLACE FUNCTION get_trip_execution_score(
    p_trip_execution_id INT
)
    RETURNS NUMERIC
AS
$$
DECLARE
    participant_count INT;
    location_count    INT;
    avg_rating        NUMERIC;
    score             NUMERIC;
BEGIN

    -- Validate trip execution existence
    IF NOT EXISTS (SELECT 1
                   FROM trip_executions
                   WHERE id = p_trip_execution_id) THEN
        RAISE EXCEPTION
            'Trip execution with ID % does not exist',
            p_trip_execution_id;
    END IF;

    SELECT tev.participant_count,
           tev.visited_locations,
           tev.average_rating
    INTO
        participant_count,
        location_count,
        avg_rating
    FROM trip_execution_stats_view tev
    WHERE tev.trip_execution_id = p_trip_execution_id;

    score :=
            COALESCE(participant_count, 0) * 2 +
            COALESCE(location_count, 0) * 1.5 +
            COALESCE(avg_rating, 0) * 10;

    RETURN ROUND(score, 2);

END;
$$ LANGUAGE plpgsql;

-- ============================================
-- PROCEDURE
-- Update user role dynamically
-- Improved with role parameter and checks
-- ============================================

CREATE OR REPLACE PROCEDURE update_user_role(
    p_user_id INT,
    p_role_id INT DEFAULT NULL
)
AS
$$
DECLARE
    trip_count  INT;
    user_exists INT;
BEGIN

    -- Validate user existence
    SELECT COUNT(*)
    INTO user_exists
    FROM users
    WHERE id = p_user_id;

    IF user_exists = 0 THEN
        RAISE EXCEPTION
            'User with ID % does not exist',
            p_user_id;
    END IF;

    -- Count trips
    SELECT COUNT(*)
    INTO trip_count
    FROM trip_participants
    WHERE user_id = p_user_id
      AND status = 'confirmed';

    -- Remove old roles
    DELETE
    FROM user_role_map
    WHERE user_id = p_user_id;

    -- Assign custom role if provided
    IF p_role_id IS NOT NULL THEN

        INSERT INTO user_role_map(user_id, role_id)
        VALUES (p_user_id, p_role_id);

    ELSE

        -- Automatic role assignment
        IF trip_count >= 5 THEN

            INSERT INTO user_role_map(user_id, role_id)
            VALUES (p_user_id, 2);

        ELSE

            INSERT INTO user_role_map(user_id, role_id)
            VALUES (p_user_id, 1);

        END IF;

    END IF;

END;
$$ LANGUAGE plpgsql;

-- ============================================
-- TRIGGER FUNCTION
-- Notify if trip execution already ended
-- Improved with logical validations
-- ============================================

CREATE OR REPLACE FUNCTION auto_close_trip_execution()
    RETURNS TRIGGER
AS
$$
BEGIN

    -- Validate dates
    IF NEW.end_date < NEW.start_date THEN
        RAISE EXCEPTION
            'End date cannot be before start date';
    END IF;

    -- Notify if already ended
    IF NEW.end_date < CURRENT_DATE THEN
        RAISE NOTICE
            'Trip execution % has already ended',
            NEW.id;
    END IF;

    RETURN NEW;

END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_auto_close_trip
    ON trip_executions;

CREATE TRIGGER trg_auto_close_trip
    BEFORE UPDATE
    ON trip_executions
    FOR EACH ROW
EXECUTE FUNCTION auto_close_trip_execution();

-- ============================================
-- FUNCTION
-- Get user activity level
-- Returns detailed table
-- ============================================

CREATE OR REPLACE FUNCTION get_user_activity_level(
    p_user_id INT
)
    RETURNS TABLE
            (
                trip_count     INT,
                review_count   INT,
                total_activity INT
            )
AS
$$
BEGIN

    -- Validate user existence
    IF NOT EXISTS (SELECT 1
                   FROM users
                   WHERE id = p_user_id) THEN
        RAISE EXCEPTION
            'User with ID % does not exist',
            p_user_id;
    END IF;

    RETURN QUERY
        SELECT (SELECT COUNT(*)
                FROM trip_participants
                WHERE user_id = p_user_id)::INT,

               (SELECT COUNT(*)
                FROM trip_participants tp
                         JOIN reviews r
                              ON r.trip_participant_id = tp.id
                WHERE tp.user_id = p_user_id)::INT,

               (
                   (SELECT COUNT(*)
                    FROM trip_participants
                    WHERE user_id = p_user_id) +
                   (SELECT COUNT(*)
                    FROM trip_participants tp
                             JOIN reviews r
                                  ON r.trip_participant_id = tp.id
                    WHERE tp.user_id = p_user_id)
                   )::INT;

END;
$$ LANGUAGE plpgsql;


-- ============================================
-- TRIGGER FUNCTION
-- Validate participant status
-- Improved with participant existence checks
-- ============================================

CREATE OR REPLACE FUNCTION validate_participant_status()
    RETURNS TRIGGER
AS
$$
BEGIN

    IF NEW.status NOT IN (
                          'pending',
                          'confirmed',
                          'cancelled'
        ) THEN
        RAISE EXCEPTION
            'Invalid participant status: %',
            NEW.status;
    END IF;

    -- Prevent changing cancelled participants
    IF OLD.status = 'cancelled'
        AND NEW.status <> 'cancelled' THEN

        RAISE EXCEPTION
            'Cancelled participant status cannot be changed';

    END IF;

    RETURN NEW;

END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_validate_participant_status
    ON trip_participants;

CREATE TRIGGER trg_validate_participant_status
    BEFORE UPDATE
    ON trip_participants
    FOR EACH ROW
EXECUTE FUNCTION validate_participant_status();

-- ============================================
-- FUNCTION
-- Calculate trip duration
-- Improved with validations
-- ============================================

CREATE OR REPLACE FUNCTION trip_duration_days(
    p_trip_execution_id INT
)
    RETURNS INT
AS
$$
DECLARE
    days INT;
BEGIN

    -- Validate trip existence
    IF NOT EXISTS (SELECT 1
                   FROM trip_executions
                   WHERE id = p_trip_execution_id) THEN
        RAISE EXCEPTION
            'Trip execution with ID % does not exist',
            p_trip_execution_id;
    END IF;

    SELECT (end_date - start_date)
    INTO days
    FROM trip_executions
    WHERE id = p_trip_execution_id;

    IF days < 0 THEN
        RAISE EXCEPTION
            'Invalid trip dates';
    END IF;

    RETURN COALESCE(days, 0);

END;
$$ LANGUAGE plpgsql;

-- ============================================
-- PROCEDURE
-- Assign hotel room to participant
-- Improved with realistic validations
-- ============================================

CREATE OR REPLACE PROCEDURE assign_hotel_room(
    p_trip_participant_id INT,
    p_hotel_room_id INT,
    p_check_in DATE,
    p_check_out DATE
)
    LANGUAGE plpgsql
AS
$$
BEGIN

    -- Validate participant + status
    IF NOT EXISTS (SELECT 1
                   FROM trip_participants
                   WHERE id = p_trip_participant_id
                     AND status = 'confirmed') THEN
        RAISE EXCEPTION
            'Participant does not exist or is not confirmed';
    END IF;

    -- Validate room
    IF NOT EXISTS (SELECT 1
                   FROM hotel_rooms
                   WHERE id = p_hotel_room_id) THEN
        RAISE EXCEPTION
            'Hotel room does not exist';
    END IF;

    -- Prevent duplicate active booking
    IF EXISTS (SELECT 1
               FROM trip_participants_hotel_rooms
               WHERE trip_participant_id = p_trip_participant_id
                 AND hotel_room_id = p_hotel_room_id
                 AND check_out >= CURRENT_DATE) THEN
        RAISE EXCEPTION
            'Participant already has an active booking for this room';
    END IF;

    INSERT INTO trip_participants_hotel_rooms(trip_participant_id,
                                              hotel_room_id,
                                              check_in,
                                              check_out)
    VALUES (p_trip_participant_id,
            p_hotel_room_id,
            p_check_in,
            p_check_out);

END;
$$;

-- ============================================
-- TRIGGER FUNCTION
-- Validate hotel booking dates
-- Improved with realistic checks
-- ============================================

CREATE OR REPLACE FUNCTION validate_hotel_dates()
    RETURNS TRIGGER
AS
$$
BEGIN

    -- Check date order
    IF NEW.check_out <= NEW.check_in THEN
        RAISE EXCEPTION
            'Check-out date must be after check-in date';
    END IF;

    -- Prevent past reservations
    IF NEW.check_in < CURRENT_DATE THEN
        RAISE EXCEPTION
            'Check-in date cannot be in the past';
    END IF;

    -- Limit unrealistic long stays
    IF (NEW.check_out - NEW.check_in) > 60 THEN
        RAISE EXCEPTION
            'Hotel stay cannot exceed 60 days';
    END IF;

    RETURN NEW;

END;
$$ LANGUAGE plpgsql;

DROP TRIGGER IF EXISTS trg_validate_hotel_dates
    ON trip_participants_hotel_rooms;

CREATE TRIGGER trg_validate_hotel_dates
    BEFORE INSERT OR UPDATE
    ON trip_participants_hotel_rooms
    FOR EACH ROW
EXECUTE FUNCTION validate_hotel_dates();

-- ============================================
-- PROCEDURE
-- Cancel expired pending reservations
-- ============================================

CREATE OR REPLACE PROCEDURE cancel_expired_trip_reservations()
AS
$$
BEGIN

    UPDATE trip_participants tp
    SET status = 'cancelled'
    FROM trip_executions te
    WHERE tp.trip_execution_id = te.id
      AND tp.status = 'pending'

      -- trip already finished (NOT just started)
      AND te.end_date < CURRENT_DATE

      -- invalid passport still blocks confirmation
      AND (
        tp.passport_number IS NULL
            OR LENGTH(TRIM(tp.passport_number)) < 6
        );

    IF NOT FOUND THEN
        RAISE NOTICE
            'No expired reservations found';
    ELSE
        RAISE NOTICE
            'Expired reservations successfully cancelled';
    END IF;

END;
$$ LANGUAGE plpgsql;

-- Example usage
--CALL cancel_expired_trip_reservations();