DatabaseProgramming: functions.sql

-- =============================================================================
-- FUNCTION 1: Full Name Resolution
-- =============================================================================
-- NULL when the user does not exist.
CREATE OR REPLACE FUNCTION fn_get_full_name(p_user_id int4)
RETURNS text LANGUAGE sql STABLE STRICT AS $$
    SELECT first_name || ' ' || last_name
    FROM "User"
    WHERE user_id = p_user_id;
$$;

-- =============================================================================
-- FUNCTION 2: Vendor ID Resolution via Project Contract
-- =============================================================================
-- The vendor is the vendor party on the project's contract; NULL when the
-- project does not exist.
CREATE OR REPLACE FUNCTION fn_get_vendor_id_for_project(p_project_id int4)
RETURNS int4 LANGUAGE sql STABLE STRICT AS $$
    SELECT cvc.vendor_id
    FROM Project p
    JOIN Client_Vendor_Contract cvc ON cvc.contract_id = p.contract_id
    WHERE p.project_id = p_project_id;
$$;

-- =============================================================================
-- FUNCTION 3: Client ID Resolution via Project Contract
-- =============================================================================
-- The client is the client party on the project's contract; NULL when the
-- project does not exist.
CREATE OR REPLACE FUNCTION fn_get_client_id_for_project(p_project_id int4)
RETURNS int4 LANGUAGE sql STABLE STRICT AS $$
    SELECT cvc.client_id
    FROM Project p
    JOIN Client_Vendor_Contract cvc ON cvc.contract_id = p.contract_id
    WHERE p.project_id = p_project_id;
$$;

-- =============================================================================
-- FUNCTION 4: Vendor User Ownership Check
-- =============================================================================
-- true when the user belongs to the vendor on the project's contract, false
-- when it belongs to another vendor, NULL when the user is not a vendor user,
-- the project does not exist or an argument is NULL.
CREATE OR REPLACE FUNCTION fn_is_vendor_user_of_project(p_user_id int4, p_project_id int4)
RETURNS bool LANGUAGE sql STABLE STRICT AS $$
    SELECT vu.vendor_id = fn_get_vendor_id_for_project(p_project_id)
    FROM Vendor_User vu
    WHERE vu.user_id = p_user_id;
$$;

-- =============================================================================
-- FUNCTION 5: Client User Ownership Check
-- =============================================================================
-- true when the user belongs to the client on the project's contract, false
-- when it belongs to another client, NULL when the user is not a client user,
-- the project does not exist or an argument is NULL.
CREATE OR REPLACE FUNCTION fn_is_client_user_of_project(p_user_id int4, p_project_id int4)
RETURNS bool LANGUAGE sql STABLE STRICT AS $$
    SELECT cu.client_id = fn_get_client_id_for_project(p_project_id)
    FROM Client_User cu
    WHERE cu.user_id = p_user_id;
$$;

-- =============================================================================
-- FUNCTION 6: Non-blank Text Guard
-- =============================================================================
-- Rejects NULL, empty text and text made of spaces only; p_name labels the error.
CREATE OR REPLACE FUNCTION fn_assert_not_blank(p_value text, p_name text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF btrim(coalesce(p_value, '')) = '' THEN
        RAISE EXCEPTION '% must not be blank', p_name;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 7: Positive Amount Guard
-- =============================================================================
-- Rejects NULL, zero and negative amounts; p_name labels the error.
CREATE OR REPLACE FUNCTION fn_assert_positive(p_value numeric, p_name text)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF p_value IS NULL OR p_value <= 0 THEN
        RAISE EXCEPTION '% must be greater than zero (received: %)', p_name, p_value;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 8: Project Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_project_exists(p_project_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Project WHERE project_id = p_project_id) THEN
        RAISE EXCEPTION 'Project % does not exist', p_project_id;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 9: Vendor Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_vendor_exists(p_vendor_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Vendor WHERE vendor_id = p_vendor_id) THEN
        RAISE EXCEPTION 'Vendor % does not exist', p_vendor_id;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 10: Review Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_review_exists(p_review_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Review WHERE review_id = p_review_id) THEN
        RAISE EXCEPTION 'Review % does not exist', p_review_id;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 11: Client Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_client_exists(p_client_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Client WHERE client_id = p_client_id) THEN
        RAISE EXCEPTION 'Client % does not exist', p_client_id;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 12: Project Status Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_status_exists(p_status_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Project_Status WHERE status_id = p_status_id) THEN
        RAISE EXCEPTION 'Project status % does not exist', p_status_id;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 13: Subscription Tier Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_tier_exists(p_tier_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Subscription_Tier WHERE tier_id = p_tier_id) THEN
        RAISE EXCEPTION 'Subscription tier % does not exist', p_tier_id;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 14: Role Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_role_exists(p_role_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Role WHERE role_id = p_role_id) THEN
        RAISE EXCEPTION 'Role % does not exist', p_role_id;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 15: Client User Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_client_user_exists(p_user_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Client_User WHERE user_id = p_user_id) THEN
        RAISE EXCEPTION 'Client user % does not exist', p_user_id;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 16: Vendor User Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_vendor_user_exists(p_user_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Vendor_User WHERE user_id = p_user_id) THEN
        RAISE EXCEPTION 'Vendor user % does not exist', p_user_id;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 17: Management User Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_management_user_exists(p_user_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Management_User WHERE user_id = p_user_id) THEN
        RAISE EXCEPTION 'Management user % does not exist', p_user_id;
    END IF;
END;
$$;

-- =============================================================================
-- FUNCTION 18: Rating Dimension Existence Guard
-- =============================================================================
CREATE OR REPLACE FUNCTION fn_assert_dimension_exists(p_dimension_id int4)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM Rating_Dimension WHERE dimension_id = p_dimension_id) THEN
        RAISE EXCEPTION 'Rating dimension % does not exist', p_dimension_id;
    END IF;
END;
$$;