AdvancedDatabaseDevelopment: advanced_database_development.sql

SET search_path TO project;

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM pg_type t
        JOIN pg_namespace n ON n.oid = t.typnamespace
        WHERE n.nspname = 'project'
          AND t.typname = 'approval_decision_domain'
    ) THEN
        CREATE DOMAIN project.approval_decision_domain AS VARCHAR(128)
        CHECK (VALUE IN ('approved', 'rejected'));
    END IF;
END $$;

DROP VIEW IF EXISTS project.v_quarterly_room_utilization;
DROP VIEW IF EXISTS project.v_pending_reservation_details;

DROP TRIGGER IF EXISTS trg_prevent_room_overlap ON project.reservations;
DROP TRIGGER IF EXISTS ctg_reservation_has_resource ON project.reservations;
DROP TRIGGER IF EXISTS ctg_reservation_equipment_has_resource ON project.reservation_equipment;
DROP TRIGGER IF EXISTS trg_validate_equipment_availability_on_request ON project.reservation_equipment;
DROP TRIGGER IF EXISTS trg_validate_equipment_availability_on_reservation ON project.reservations;
DROP TRIGGER IF EXISTS trg_validate_approval_direct_write ON project.approvals;
DROP TRIGGER IF EXISTS trg_sync_reservation_status_after_approval ON project.approvals;

DROP FUNCTION IF EXISTS project.fn_prevent_room_overlap();
DROP FUNCTION IF EXISTS project.fn_check_reservation_has_resource();
DROP FUNCTION IF EXISTS project.fn_check_reservation_equipment_resource();
DROP FUNCTION IF EXISTS project.fn_validate_equipment_availability(INTEGER);
DROP FUNCTION IF EXISTS project.fn_trg_validate_equipment_availability();
DROP FUNCTION IF EXISTS project.fn_validate_approval_direct_write();
DROP FUNCTION IF EXISTS project.fn_sync_reservation_status_after_approval();
DROP FUNCTION IF EXISTS project.fn_approve_or_reject_reservation(
    INTEGER,
    INTEGER,
    project.approval_decision_domain,
    VARCHAR
);

CREATE OR REPLACE FUNCTION project.fn_prevent_room_overlap()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.room_id IS NULL OR NEW.status NOT IN ('pending', 'approved') THEN
        RETURN NEW;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM project.reservations r
        WHERE r.room_id = NEW.room_id
          AND r.reservation_date = NEW.reservation_date
          AND r.status IN ('pending', 'approved')
          AND r.reservation_id <> COALESCE(NEW.reservation_id, -1)
          AND NEW.start_time < r.end_time
          AND NEW.end_time > r.start_time
    ) THEN
        RAISE EXCEPTION
            'Room reservation overlap detected for room_id %, date %, time interval % - %.',
            NEW.room_id,
            NEW.reservation_date,
            NEW.start_time,
            NEW.end_time;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_prevent_room_overlap
BEFORE INSERT OR UPDATE OF room_id, reservation_date, start_time, end_time, status
ON project.reservations
FOR EACH ROW
EXECUTE FUNCTION project.fn_prevent_room_overlap();

CREATE OR REPLACE FUNCTION project.fn_check_reservation_has_resource()
RETURNS TRIGGER AS $$
DECLARE
    v_has_equipment BOOLEAN;
BEGIN
    SELECT EXISTS (
        SELECT 1
        FROM project.reservation_equipment re
        WHERE re.reservation_id = NEW.reservation_id
    )
    INTO v_has_equipment;

    IF NEW.room_id IS NULL AND NOT v_has_equipment THEN
        RAISE EXCEPTION
            'Reservation % must include at least one resource: either a room or requested equipment.',
            NEW.reservation_id;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE CONSTRAINT TRIGGER ctg_reservation_has_resource
AFTER INSERT OR UPDATE OF room_id
ON project.reservations
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION project.fn_check_reservation_has_resource();

CREATE OR REPLACE FUNCTION project.fn_check_reservation_equipment_resource()
RETURNS TRIGGER AS $$
DECLARE
    v_reservation_id INTEGER;
    v_room_id INTEGER;
    v_equipment_count INTEGER;
BEGIN
    v_reservation_id := COALESCE(NEW.reservation_id, OLD.reservation_id);

    SELECT room_id
    INTO v_room_id
    FROM project.reservations
    WHERE reservation_id = v_reservation_id;

    IF NOT FOUND THEN
        RETURN COALESCE(NEW, OLD);
    END IF;

    SELECT COUNT(*)
    INTO v_equipment_count
    FROM project.reservation_equipment
    WHERE reservation_id = v_reservation_id;

    IF v_room_id IS NULL AND v_equipment_count = 0 THEN
        RAISE EXCEPTION
            'Reservation % cannot remain without both room and requested equipment.',
            v_reservation_id;
    END IF;

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

CREATE CONSTRAINT TRIGGER ctg_reservation_equipment_has_resource
AFTER INSERT OR UPDATE OR DELETE
ON project.reservation_equipment
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW
EXECUTE FUNCTION project.fn_check_reservation_equipment_resource();

CREATE OR REPLACE FUNCTION project.fn_validate_equipment_availability(
    p_reservation_id INTEGER
)
RETURNS VOID AS $$
DECLARE
    rec RECORD;
    v_overlapping_quantity INTEGER;
BEGIN
    FOR rec IN
        SELECT
            req.equipment_id,
            e.name AS equipment_name,
            e.stock_quantity,
            req.requested_quantity,
            res.reservation_date,
            res.start_time,
            res.end_time,
            res.status
        FROM project.reservation_equipment req
        JOIN project.reservations res
            ON req.reservation_id = res.reservation_id
        JOIN project.equipment e
            ON req.equipment_id = e.equipment_id
        WHERE req.reservation_id = p_reservation_id
    LOOP
        IF rec.status NOT IN ('pending', 'approved') THEN
            CONTINUE;
        END IF;

        SELECT COALESCE(SUM(req2.requested_quantity), 0)
        INTO v_overlapping_quantity
        FROM project.reservation_equipment req2
        JOIN project.reservations res2
            ON req2.reservation_id = res2.reservation_id
        WHERE req2.equipment_id = rec.equipment_id
          AND res2.reservation_id <> p_reservation_id
          AND res2.status IN ('pending', 'approved')
          AND res2.reservation_date = rec.reservation_date
          AND rec.start_time < res2.end_time
          AND rec.end_time > res2.start_time;

        IF v_overlapping_quantity + rec.requested_quantity > rec.stock_quantity THEN
            RAISE EXCEPTION
                'Not enough general stock for equipment %. Requested quantity %, overlapping active quantity %, available stock %.',
                rec.equipment_name,
                rec.requested_quantity,
                v_overlapping_quantity,
                rec.stock_quantity;
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION project.fn_trg_validate_equipment_availability()
RETURNS TRIGGER AS $$
BEGIN
    PERFORM project.fn_validate_equipment_availability(
        COALESCE(NEW.reservation_id, OLD.reservation_id)
    );

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

CREATE TRIGGER trg_validate_equipment_availability_on_request
AFTER INSERT OR UPDATE
ON project.reservation_equipment
FOR EACH ROW
EXECUTE FUNCTION project.fn_trg_validate_equipment_availability();

CREATE TRIGGER trg_validate_equipment_availability_on_reservation
AFTER UPDATE OF reservation_date, start_time, end_time, status
ON project.reservations
FOR EACH ROW
WHEN (NEW.status IN ('pending', 'approved'))
EXECUTE FUNCTION project.fn_trg_validate_equipment_availability();

CREATE OR REPLACE FUNCTION project.fn_validate_approval_direct_write()
RETURNS TRIGGER AS $$
DECLARE
    v_role VARCHAR(128);
    v_status VARCHAR(128);
BEGIN
    SELECT role
    INTO v_role
    FROM project.users
    WHERE user_id = NEW.approver_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Approver user % does not exist.', NEW.approver_id;
    END IF;

    IF v_role NOT IN ('admin', 'approver') THEN
        RAISE EXCEPTION
            'User % cannot approve reservations because role % is not allowed.',
            NEW.approver_id,
            v_role;
    END IF;

    SELECT status
    INTO v_status
    FROM project.reservations
    WHERE reservation_id = NEW.reservation_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Reservation % does not exist.', NEW.reservation_id;
    END IF;

    IF v_status NOT IN ('pending', NEW.decision) THEN
        RAISE EXCEPTION
            'Approval can be created only for pending reservations or reservations already synchronized with the same decision. Current status is %.',
            v_status;
    END IF;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_validate_approval_direct_write
BEFORE INSERT OR UPDATE
ON project.approvals
FOR EACH ROW
EXECUTE FUNCTION project.fn_validate_approval_direct_write();

CREATE OR REPLACE FUNCTION project.fn_sync_reservation_status_after_approval()
RETURNS TRIGGER AS $$
BEGIN
    UPDATE project.reservations
    SET status = NEW.decision
    WHERE reservation_id = NEW.reservation_id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_sync_reservation_status_after_approval
AFTER INSERT
ON project.approvals
FOR EACH ROW
EXECUTE FUNCTION project.fn_sync_reservation_status_after_approval();

CREATE OR REPLACE FUNCTION project.fn_approve_or_reject_reservation(
    p_reservation_id INTEGER,
    p_approver_id INTEGER,
    p_decision project.approval_decision_domain,
    p_note VARCHAR DEFAULT NULL
)
RETURNS TABLE (
    out_approval_id INTEGER,
    out_reservation_id INTEGER,
    out_status VARCHAR,
    out_decision VARCHAR,
    out_decision_time TIMESTAMP
) AS $$
DECLARE
    v_status VARCHAR(128);
    v_role VARCHAR(128);
    v_approval_id INTEGER;
    v_decision_time TIMESTAMP;
BEGIN
    SELECT status
    INTO v_status
    FROM project.reservations
    WHERE reservation_id = p_reservation_id
    FOR UPDATE;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Reservation % does not exist.', p_reservation_id;
    END IF;

    IF v_status <> 'pending' THEN
        RAISE EXCEPTION
            'Only pending reservations can be approved or rejected. Current status is %.',
            v_status;
    END IF;

    SELECT role
    INTO v_role
    FROM project.users
    WHERE user_id = p_approver_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Approver user % does not exist.', p_approver_id;
    END IF;

    IF v_role NOT IN ('admin', 'approver') THEN
        RAISE EXCEPTION
            'User % cannot approve reservations because role % is not allowed.',
            p_approver_id,
            v_role;
    END IF;

    UPDATE project.reservations
    SET status = p_decision::VARCHAR
    WHERE reservation_id = p_reservation_id;

    INSERT INTO project.approvals (
        reservation_id,
        approver_id,
        decision,
        decision_time,
        note
    )
    VALUES (
        p_reservation_id,
        p_approver_id,
        p_decision::VARCHAR,
        CURRENT_TIMESTAMP,
        p_note
    )
    RETURNING approval_id, decision_time
    INTO v_approval_id, v_decision_time;

    RETURN QUERY
    SELECT
        v_approval_id,
        p_reservation_id,
        p_decision::VARCHAR,
        p_decision::VARCHAR,
        v_decision_time;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE VIEW project.v_pending_reservation_details AS
SELECT
    res.reservation_id,
    u.full_name AS requester_name,
    res.reservation_date,
    res.start_time,
    res.end_time,
    COALESCE(r.room_code, 'No room') AS room_code,
    COALESCE(b.name, 'No building') AS building_name,
    res.status,
    COALESCE(eq.requested_equipment, 'No equipment') AS requested_equipment
FROM project.reservations res
JOIN project.users u
    ON res.user_id = u.user_id
LEFT JOIN project.rooms r
    ON res.room_id = r.room_id
LEFT JOIN project.buildings b
    ON r.building_id = b.building_id
LEFT JOIN (
    SELECT
        req.reservation_id,
        string_agg(e.name || ' x' || req.requested_quantity, ', ' ORDER BY e.name) AS requested_equipment
    FROM project.reservation_equipment req
    JOIN project.equipment e
        ON req.equipment_id = e.equipment_id
    GROUP BY req.reservation_id
) eq
    ON res.reservation_id = eq.reservation_id
WHERE res.status = 'pending';

CREATE OR REPLACE VIEW project.v_quarterly_room_utilization AS
WITH room_reservation_base AS (
    SELECT
        date_trunc('quarter', res.reservation_date)::date AS quarter_start,
        b.name AS building_name,
        r.room_id,
        r.room_code,
        r.type,
        r.capacity,
        res.reservation_id,
        res.status,
        EXTRACT(EPOCH FROM (res.end_time - res.start_time)) / 3600.0 AS reservation_hours
    FROM project.reservations res
    JOIN project.rooms r
        ON res.room_id = r.room_id
    JOIN project.buildings b
        ON r.building_id = b.building_id
    WHERE res.room_id IS NOT NULL
),
room_quarter_summary AS (
    SELECT
        quarter_start,
        building_name,
        room_id,
        room_code,
        type,
        capacity,
        COUNT(reservation_id) AS total_room_reservations,
        COUNT(*) FILTER (WHERE status = 'approved') AS approved_reservations,
        COUNT(*) FILTER (WHERE status = 'rejected') AS rejected_reservations,
        COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled_reservations,
        COUNT(*) FILTER (WHERE status = 'pending') AS pending_reservations,
        ROUND(SUM(reservation_hours), 2) AS requested_hours,
        ROUND(COALESCE(SUM(reservation_hours) FILTER (WHERE status = 'approved'), 0), 2) AS approved_hours
    FROM room_reservation_base
    GROUP BY
        quarter_start,
        building_name,
        room_id,
        room_code,
        type,
        capacity
)
SELECT
    quarter_start,
    building_name,
    room_code,
    type,
    capacity,
    total_room_reservations,
    approved_reservations,
    rejected_reservations,
    cancelled_reservations,
    pending_reservations,
    requested_hours,
    approved_hours,
    ROUND(
        approved_hours / NULLIF(SUM(approved_hours) OVER (PARTITION BY quarter_start), 0) * 100,
        2
    ) AS approved_hours_share_percent,
    DENSE_RANK() OVER (
        PARTITION BY quarter_start
        ORDER BY approved_hours DESC, total_room_reservations DESC, room_code
    ) AS utilization_rank,
    CASE
        WHEN approved_hours >= 4 THEN 'high_usage'
        WHEN approved_hours >= 2 THEN 'medium_usage'
        WHEN approved_hours > 0 THEN 'low_usage'
        ELSE 'no_approved_usage'
    END AS utilization_level
FROM room_quarter_summary;