DatabaseProgramming: procedures.sql

-- =============================================================================
-- PROCEDURE 1: Register a New User
-- =============================================================================
-- p_type is 'client', 'vendor' or 'management' and decides which of
-- p_client_id, p_vendor_id and p_role_id is required. p_password_hash is
-- stored as given; hashing is the caller's job. New users start inactive.
CREATE OR REPLACE PROCEDURE sp_register_user(
    OUT p_user_id       int4,
    IN  p_type          text,
    IN  p_first_name    text,
    IN  p_last_name     text,
    IN  p_email         text,
    IN  p_password_hash text,
    IN  p_client_id     int4 DEFAULT NULL,
    IN  p_vendor_id     int4 DEFAULT NULL,
    IN  p_role_id       int4 DEFAULT NULL
)
LANGUAGE plpgsql AS $$
BEGIN
    CASE p_type
        WHEN 'client' THEN
            IF p_client_id IS NULL THEN
                RAISE EXCEPTION 'p_client_id is required when registering a client user';
            END IF;
            PERFORM fn_assert_client_exists(p_client_id);
        WHEN 'vendor' THEN
            IF p_vendor_id IS NULL THEN
                RAISE EXCEPTION 'p_vendor_id is required when registering a vendor user';
            END IF;
            PERFORM fn_assert_vendor_exists(p_vendor_id);
        WHEN 'management' THEN
            IF p_role_id IS NULL THEN
                RAISE EXCEPTION 'p_role_id is required when registering a management user';
            END IF;
            PERFORM fn_assert_role_exists(p_role_id);
        ELSE
            RAISE EXCEPTION 'Invalid user type: %. Must be client, vendor, or management', p_type;
    END CASE;

    PERFORM fn_assert_not_blank(p_first_name, 'first_name');
    PERFORM fn_assert_not_blank(p_last_name, 'last_name');
    PERFORM fn_assert_not_blank(p_email, 'email');
    PERFORM fn_assert_not_blank(p_password_hash, 'password_hash');

    INSERT INTO "User" (type, first_name, last_name, email, password_hash)
    VALUES (p_type, p_first_name, p_last_name, p_email, p_password_hash)
    RETURNING user_id INTO p_user_id;

    CASE p_type
        WHEN 'client' THEN
            INSERT INTO Client_User (user_id, client_id)
            VALUES (p_user_id, p_client_id);
        WHEN 'vendor' THEN
            INSERT INTO Vendor_User (user_id, vendor_id)
            VALUES (p_user_id, p_vendor_id);
        WHEN 'management' THEN
            INSERT INTO Management_User (user_id, role_id)
            VALUES (p_user_id, p_role_id);
        ELSE
            RAISE EXCEPTION 'Invalid user type: %. Must be client, vendor, or management', p_type;
    END CASE;
END;
$$;


-- =============================================================================
-- PROCEDURE 2: Activate a User
-- =============================================================================
CREATE OR REPLACE PROCEDURE sp_activate_user(
    IN p_user_id int4
)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE "User"
    SET    is_active = true
    WHERE  user_id   = p_user_id
      AND  is_active = false;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User % does not exist or is already active', p_user_id;
    END IF;
END;
$$;


-- =============================================================================
-- PROCEDURE 3: Deactivate a User
-- =============================================================================
CREATE OR REPLACE PROCEDURE sp_deactivate_user(
    IN p_user_id int4
)
LANGUAGE plpgsql AS $$
DECLARE
    v_is_management bool;
BEGIN
    UPDATE "User"
    SET    is_active = false
    WHERE  user_id   = p_user_id
      AND  is_active = true;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'User % does not exist or is already inactive', p_user_id;
    END IF;

    -- A management user who leaves must not stay assigned to open tickets
    SELECT EXISTS (
        SELECT 1 FROM Management_User WHERE user_id = p_user_id
    ) INTO v_is_management;

    IF v_is_management THEN
        UPDATE Dispute_Ticket
        SET    assigned_management_user_id = NULL
        WHERE  assigned_management_user_id = p_user_id
          AND  is_resolved                 = false;
    END IF;
END;
$$;


-- =============================================================================
-- PROCEDURE 4: Create a Contract with an Initial Project
-- =============================================================================
-- The contract (p_cvc_*) and its first project (p_project_*) have separate
-- start and end dates. p_contract_number is the contract's external
-- reference and p_total_value the framework value stated in the contract
-- document. p_currency_code is meant to be an ISO 4217 code; only its
-- format (three upper-case letters) is checked.
CREATE OR REPLACE PROCEDURE sp_create_contract_with_project(
    OUT p_contract_id        int4,
    OUT p_project_id         int4,
    IN  p_client_id          int4,
    IN  p_vendor_id          int4,
    IN  p_contract_title     text,
    IN  p_project_name       text,
    IN  p_status_id          int4,
    IN  p_budget             numeric(10,2),
    IN  p_contract_number    text          DEFAULT NULL,
    IN  p_cvc_start_date     date          DEFAULT CURRENT_DATE,
    IN  p_cvc_end_date       date          DEFAULT NULL,
    IN  p_total_value        numeric(10,2) DEFAULT NULL,
    IN  p_currency_code      text          DEFAULT NULL,
    IN  p_terms_summary      text          DEFAULT NULL,
    IN  p_project_start_date date          DEFAULT CURRENT_DATE,
    IN  p_project_end_date   date          DEFAULT NULL
)
LANGUAGE plpgsql AS $$
BEGIN
    PERFORM fn_assert_not_blank(p_contract_title, 'contract_title');
    PERFORM fn_assert_not_blank(p_project_name, 'project_name');

    PERFORM fn_assert_positive(p_budget, 'Budget');

    PERFORM fn_assert_client_exists(p_client_id);
    PERFORM fn_assert_vendor_exists(p_vendor_id);
    PERFORM fn_assert_status_exists(p_status_id);

    INSERT INTO Client_Vendor_Contract (
        client_id, vendor_id, contract_title, contract_number,
        start_date, end_date, total_value, currency_code, terms_summary
    )
    VALUES (
        p_client_id, p_vendor_id, p_contract_title, p_contract_number,
        p_cvc_start_date, p_cvc_end_date, p_total_value, p_currency_code, p_terms_summary
    )
    RETURNING contract_id INTO p_contract_id;

    INSERT INTO Project (
        contract_id, status_id, project_name, start_date, end_date, budget
    )
    VALUES (
        p_contract_id, p_status_id, p_project_name,
        p_project_start_date, p_project_end_date, p_budget
    )
    RETURNING project_id INTO p_project_id;
END;
$$;


-- =============================================================================
-- PROCEDURE 5: Update Project Status
-- =============================================================================
-- The change is recorded in Project_Status_History with exactly one actor:
-- p_vendor_user_id or p_management_user_id. An unchanged status returns
-- without adding history.
CREATE OR REPLACE PROCEDURE sp_update_project_status(
    IN p_project_id         int4,
    IN p_new_status_id      int4,
    IN p_vendor_user_id     int4 DEFAULT NULL,
    IN p_management_user_id int4 DEFAULT NULL,
    IN p_comment            text DEFAULT NULL
)
LANGUAGE plpgsql AS $$
DECLARE
    v_current_status_id int4;
BEGIN
    -- Exactly one actor: a vendor user or a management user, never both or neither
    IF (p_vendor_user_id IS NULL) = (p_management_user_id IS NULL) THEN
        RAISE EXCEPTION
            'Provide exactly one actor: p_vendor_user_id or p_management_user_id';
    END IF;

    PERFORM fn_assert_status_exists(p_new_status_id);

    SELECT status_id INTO v_current_status_id
    FROM Project
    WHERE project_id = p_project_id;

    IF v_current_status_id = p_new_status_id THEN
        RAISE NOTICE 'Project % is already at status % – no update performed',
            p_project_id, p_new_status_id;
        RETURN;
    END IF;

    -- A reviewed project stays finished: this procedure never returns it to an
    -- open status
    IF EXISTS (SELECT 1 FROM Review WHERE project_id = p_project_id)
       AND (SELECT status_name FROM Project_Status WHERE status_id = p_new_status_id)
           NOT IN ('Completed', 'Cancelled') THEN
        RAISE EXCEPTION 'Project % has a review and cannot return to an open status', p_project_id;
    END IF;

    UPDATE Project
    SET    status_id = p_new_status_id
    WHERE  project_id = p_project_id;

    -- The project, the actor and the actor's vendor are checked by trg_status_history_actor
    INSERT INTO Project_Status_History (
        project_id, status_id, vendor_user_id, management_user_id, comment
    )
    VALUES (
        p_project_id, p_new_status_id, p_vendor_user_id, p_management_user_id, p_comment
    );
END;
$$;


-- =============================================================================
-- PROCEDURE 6: Update Project Budget
-- =============================================================================
CREATE OR REPLACE PROCEDURE sp_update_project_budget(
    IN p_project_id int4,
    IN p_new_budget numeric(10,2)
)
LANGUAGE plpgsql AS $$
BEGIN
    PERFORM fn_assert_positive(p_new_budget, 'Budget');

    -- trg_project_budget_audit records the old and new budget in
    -- Project_Budget_Audit when the value changes; an unchanged value adds no
    -- audit row
    UPDATE Project
    SET    budget = p_new_budget
    WHERE  project_id = p_project_id;

    IF NOT FOUND THEN
        PERFORM fn_assert_project_exists(p_project_id);
    END IF;
END;
$$;


-- =============================================================================
-- PROCEDURE 7: Submit a Review with Scores
-- =============================================================================
-- p_scores is a non-empty JSON array of {dimension_id, score_value} objects
-- with distinct, existing dimensions and scores from 1 to 5. A project gets
-- at most one review, written by a user of its client.
CREATE OR REPLACE PROCEDURE sp_submit_review(
    OUT p_review_id      int4,
    IN  p_project_id     int4,
    IN  p_client_user_id int4,
    IN  p_summary_text   text,
    IN  p_scores         jsonb
)
LANGUAGE plpgsql AS $$
DECLARE
    v_dimension_id int4;
    v_score_value  int4;
BEGIN
    PERFORM fn_assert_not_blank(p_summary_text, 'summary_text');

    -- Shape of p_scores: a non-empty array of objects with a JSON number as
    -- dimension_id and score_value; the casts to int4 come later
    IF p_scores IS NULL OR jsonb_typeof(p_scores) <> 'array' THEN
        RAISE EXCEPTION 'p_scores must be a JSON array of {dimension_id, score_value} objects';
    END IF;

    IF jsonb_array_length(p_scores) = 0 THEN
        RAISE EXCEPTION 'p_scores must contain at least one dimension score';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM jsonb_array_elements(p_scores) AS t(v)
        WHERE jsonb_typeof(v) <> 'object'
           OR jsonb_typeof(v->'dimension_id') IS DISTINCT FROM 'number'
           OR jsonb_typeof(v->'score_value') IS DISTINCT FROM 'number'
    ) THEN
        RAISE EXCEPTION
            'Each element of p_scores must be an object with numeric dimension_id and score_value';
    END IF;

    PERFORM fn_assert_project_exists(p_project_id);
    PERFORM fn_assert_client_user_exists(p_client_user_id);

    -- The client user must belong to the client of the project; NULL is a
    -- mismatch
    IF NOT coalesce(fn_is_client_user_of_project(p_client_user_id, p_project_id), false) THEN
        RAISE EXCEPTION
            'Client user % does not belong to the client associated with project %',
            p_client_user_id, p_project_id;
    END IF;

    IF EXISTS (SELECT 1 FROM Review WHERE project_id = p_project_id) THEN
        RAISE EXCEPTION 'A review already exists for project %', p_project_id;
    END IF;

    -- Duplicate dimensions
    IF EXISTS (
        SELECT 1
        FROM (
            SELECT (v->>'dimension_id')::int4 AS dim_id
            FROM jsonb_array_elements(p_scores) AS t(v)
        ) dims
        GROUP BY dim_id
        HAVING COUNT(*) > 1
    ) THEN
        RAISE EXCEPTION 'Duplicate dimension_id found in p_scores';
    END IF;

    -- Every dimension must exist
    SELECT (v->>'dimension_id')::int4
    INTO   v_dimension_id
    FROM   jsonb_array_elements(p_scores) AS t(v)
    WHERE  NOT EXISTS (
        SELECT 1 FROM Rating_Dimension rd
        WHERE rd.dimension_id = (v->>'dimension_id')::int4
    )
    LIMIT  1;

    IF FOUND THEN
        PERFORM fn_assert_dimension_exists(v_dimension_id);
    END IF;

    -- Every score must be within 1..5
    SELECT (v->>'score_value')::int4
    INTO   v_score_value
    FROM   jsonb_array_elements(p_scores) AS t(v)
    WHERE  (v->>'score_value')::int4 NOT BETWEEN 1 AND 5
    LIMIT  1;

    IF FOUND THEN
        RAISE EXCEPTION 'Score value must be between 1 and 5 (received: %)', v_score_value;
    END IF;

    -- That the project is finished (Completed or Cancelled) is checked by trg_review_finished_project
    INSERT INTO Review (project_id, client_user_id, summary_text)
    VALUES (p_project_id, p_client_user_id, p_summary_text)
    RETURNING review_id INTO p_review_id;

    INSERT INTO Review_Score (review_id, dimension_id, score_value)
    SELECT p_review_id, (v->>'dimension_id')::int4, (v->>'score_value')::int4
    FROM   jsonb_array_elements(p_scores) AS t(v);
END;
$$;


-- =============================================================================
-- PROCEDURE 8: File a Dispute Ticket
-- =============================================================================
-- The dispute is filed by a user of the vendor that delivered the reviewed
-- project.
CREATE OR REPLACE PROCEDURE sp_file_dispute(
    OUT p_ticket_id      int4,
    IN  p_review_id      int4,
    IN  p_vendor_user_id int4,
    IN  p_reason         text
)
LANGUAGE plpgsql AS $$
DECLARE
    v_project_id int4;
BEGIN
    PERFORM fn_assert_not_blank(p_reason, 'reason');

    PERFORM fn_assert_review_exists(p_review_id);
    PERFORM fn_assert_vendor_user_exists(p_vendor_user_id);

    -- At most one unresolved dispute per vendor user and review
    IF EXISTS (
        SELECT 1 FROM Dispute_Ticket
        WHERE review_id      = p_review_id
          AND vendor_user_id = p_vendor_user_id
          AND is_resolved    = false
    ) THEN
        RAISE EXCEPTION
            'Vendor user % already has an unresolved dispute on review %',
            p_vendor_user_id, p_review_id;
    END IF;

    -- The vendor user must belong to the vendor of the reviewed project; NULL
    -- is a mismatch
    SELECT project_id
    INTO   v_project_id
    FROM   Review
    WHERE  review_id = p_review_id;

    IF NOT coalesce(fn_is_vendor_user_of_project(p_vendor_user_id, v_project_id), false) THEN
        RAISE EXCEPTION
            'Vendor user % does not belong to the vendor associated with review %',
            p_vendor_user_id, p_review_id;
    END IF;

    INSERT INTO Dispute_Ticket (review_id, vendor_user_id, reason)
    VALUES (p_review_id, p_vendor_user_id, p_reason)
    RETURNING ticket_id INTO p_ticket_id;
END;
$$;


-- =============================================================================
-- PROCEDURE 9: Resolve a Dispute Ticket
-- =============================================================================
CREATE OR REPLACE PROCEDURE sp_resolve_dispute(
    IN p_ticket_id                   int4,
    IN p_assigned_management_user_id int4,
    IN p_resolution_note             text
)
LANGUAGE plpgsql AS $$
BEGIN
    PERFORM fn_assert_not_blank(p_resolution_note, 'resolution_note');

    -- The assigned management user is checked by trg_dispute_ticket_resolve
    -- on the UPDATE below, which also fills resolved_at
    UPDATE Dispute_Ticket
    SET    assigned_management_user_id = p_assigned_management_user_id,
           resolution_note             = p_resolution_note,
           is_resolved                 = true
    WHERE  ticket_id   = p_ticket_id
      AND  is_resolved = false;

    IF NOT FOUND THEN
        RAISE EXCEPTION
            'Ticket % does not exist or is already resolved', p_ticket_id;
    END IF;
END;
$$;


-- =============================================================================
-- PROCEDURE 10: Publish a Review
-- =============================================================================
CREATE OR REPLACE PROCEDURE sp_publish_review(
    IN p_review_id int4
)
LANGUAGE plpgsql AS $$
BEGIN
    -- trg_review_publish_guard refuses the UPDATE while the review has an
    -- unresolved dispute
    UPDATE Review
    SET    is_published = true
    WHERE  review_id    = p_review_id
      AND  is_published = false;

    IF NOT FOUND THEN
        RAISE EXCEPTION
            'Review % does not exist or is already published', p_review_id;
    END IF;
END;
$$;


-- =============================================================================
-- PROCEDURE 11: Renew a Vendor Subscription
-- =============================================================================
-- p_new_negotiated_price is required for tiers with custom pricing and must
-- be NULL for fixed-price tiers. p_new_contract_id is the Vendor_Subscription
-- key of the new period.
CREATE OR REPLACE PROCEDURE sp_renew_vendor_subscription(
    OUT p_new_contract_id      int4,
    IN  p_vendor_id            int4,
    IN  p_new_tier_id          int4,
    IN  p_new_start_date       date          DEFAULT CURRENT_DATE,
    IN  p_new_end_date         date          DEFAULT NULL,
    IN  p_new_negotiated_price numeric(10,2) DEFAULT NULL
)
LANGUAGE plpgsql AS $$
DECLARE
    v_current_start date;
BEGIN
    PERFORM fn_assert_vendor_exists(p_vendor_id);

    IF p_new_start_date IS NULL THEN
        RAISE EXCEPTION 'start_date is required';
    END IF;

    IF p_new_end_date IS NOT NULL AND p_new_end_date <= p_new_start_date THEN
        RAISE EXCEPTION
            'end_date (%) must be after start_date (%)', p_new_end_date, p_new_start_date;
    END IF;

    -- The current period is closed at the renewal start (end_date is
    -- exclusive; an earlier end date is kept), so the new period must start
    -- after the current one began, or closing it would leave an empty period
    SELECT start_date
    INTO   v_current_start
    FROM   Vendor_Subscription
    WHERE  vendor_id = p_vendor_id
      AND  is_active = true;

    IF v_current_start IS NOT NULL AND v_current_start >= p_new_start_date THEN
        RAISE EXCEPTION
            'The current period of vendor % began on %; the new period must start after that',
            p_vendor_id, v_current_start;
    END IF;

    UPDATE Vendor_Subscription
    SET    is_active = false,
           end_date  = LEAST(end_date, p_new_start_date)
    WHERE  vendor_id = p_vendor_id
      AND  is_active = true;

    -- The tier and the negotiated price are checked by trg_vendor_subscription_pricing
    INSERT INTO Vendor_Subscription (
        vendor_id, tier_id, negotiated_price, start_date, end_date
    )
    VALUES (
        p_vendor_id, p_new_tier_id, p_new_negotiated_price,
        p_new_start_date, p_new_end_date
    )
    RETURNING contract_id INTO p_new_contract_id;
END;
$$;