DatabaseCreation: Change plan procedure.sql

CREATE OR REPLACE PROCEDURE proc_change_subscription_plan(
    p_subscription_id BIGINT,
    p_new_plan_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_old_plan_id BIGINT;
    v_status TEXT;
BEGIN
    -- zemi plan i status od momentalen subs
    SELECT plan_id, status
    INTO v_old_plan_id, v_status
    FROM subscriptions
    WHERE subscription_id = p_subscription_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Subscription % does not exist.', p_subscription_id;
    END IF;

    -- samo activni subscritpiond se eligable
    IF v_status NOT IN ('active', 'suspended') THEN
        RAISE EXCEPTION 'Cannot change plan for subscription with status %.', v_status;
    END IF;

    -- proverka dali planot postoi
    IF NOT EXISTS (
        SELECT 1
        FROM plans
        WHERE plan_id = p_new_plan_id
          AND status = 'active'
    ) THEN
        RAISE EXCEPTION 'Plan % does not exist or is not active.', p_new_plan_id;
    END IF;

    -- ne dozvoluvame ist plan povtorno
    IF v_old_plan_id = p_new_plan_id THEN
        RAISE EXCEPTION 'Subscription is already on this plan.';
    END IF;

    -- promena na planot
    UPDATE subscriptions
    SET plan_id = p_new_plan_id
    WHERE subscription_id = p_subscription_id;

    RAISE NOTICE 'Subscription % changed to plan %.',
        p_subscription_id, p_new_plan_id;
END;
$$;

--propusti (treba da menuva addons, da represmetuva fakturi...)

--test
BEGIN;

CALL proc_change_subscription_plan(1, 3);

SELECT
    s.subscription_id,
    s.plan_id,
    p.plan_name,
    s.status
FROM subscriptions s
JOIN plans p
    ON p.plan_id = s.plan_id
WHERE s.subscription_id = 1;

ROLLBACK;