DatabaseProgramming: 02 Change plan procedure.sql

DROP PROCEDURE IF EXISTS public.proc_change_subscription_plan(bigint, bigint);
DROP PROCEDURE IF EXISTS public.proc_change_subscription_plan(bigint, bigint, bigint, text);

CREATE PROCEDURE public.proc_change_subscription_plan(
    p_subscription_id bigint,
    p_new_plan_id bigint,
    p_changed_by_employee_id bigint DEFAULT NULL,
    p_reason text DEFAULT 'Customer requested plan change'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_old_plan_id bigint;
    v_old_product_id bigint;
    v_new_product_id bigint;
    v_status text;
BEGIN
    SELECT s.plan_id, s.status, p.product_id
    INTO v_old_plan_id, v_status, v_old_product_id
    FROM public.subscriptions s
    JOIN public.plans p ON p.plan_id = s.plan_id
    WHERE s.subscription_id = p_subscription_id
    FOR UPDATE OF s;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Subscription % does not exist.', p_subscription_id;
    END IF;
    IF v_status NOT IN ('active', 'suspended') THEN
        RAISE EXCEPTION 'Cannot change plan for subscription with status %.', v_status;
    END IF;
    IF v_old_plan_id = p_new_plan_id THEN
        RAISE EXCEPTION 'Subscription % is already on plan %.', p_subscription_id, p_new_plan_id;
    END IF;

    SELECT product_id INTO v_new_product_id
    FROM public.plans
    WHERE plan_id = p_new_plan_id AND status = 'active';
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Plan % does not exist or is not active.', p_new_plan_id;
    END IF;
    IF v_new_product_id <> v_old_product_id THEN
        RAISE EXCEPTION 'New plan must belong to the same product as the current plan.';
    END IF;
    IF p_changed_by_employee_id IS NOT NULL AND NOT EXISTS (
        SELECT 1 FROM public.employees
        WHERE employee_id = p_changed_by_employee_id AND employment_status = 'active'
    ) THEN
        RAISE EXCEPTION 'Employee % does not exist or is not active.', p_changed_by_employee_id;
    END IF;

    PERFORM set_config('app.employee_id', COALESCE(p_changed_by_employee_id::text, ''), true);
    PERFORM set_config('app.change_reason', COALESCE(NULLIF(p_reason, ''), 'Plan change'), true);

    UPDATE public.subscriptions
    SET plan_id = p_new_plan_id
    WHERE subscription_id = p_subscription_id;

    RAISE NOTICE 'Subscription % changed from plan % to plan %. Future invoices will use the new plan.',
        p_subscription_id, v_old_plan_id, p_new_plan_id;
END;
$$;