DatabaseProgramming: 01 Customer SIM activation.sql

DROP PROCEDURE IF EXISTS public.proc_activate_subscription(bigint, bigint, bigint);
DROP PROCEDURE IF EXISTS public.proc_activate_subscription(bigint, bigint, bigint, bigint, date);

CREATE PROCEDURE public.proc_activate_subscription(
    p_account_id bigint,
    p_plan_id bigint,
    p_contract_id bigint,
    p_sim_id bigint,
    p_activation_date date DEFAULT CURRENT_DATE
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_subscription_id bigint;
    v_subscription_number text;
    v_activation_time timestamptz;
BEGIN
    IF p_activation_date > CURRENT_DATE THEN
        RAISE EXCEPTION 'Activation date % cannot be in the future.', p_activation_date;
    END IF;

    PERFORM 1 FROM public.accounts
    WHERE account_id = p_account_id AND account_status = 'active'
    FOR UPDATE;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Account % does not exist or is not active.', p_account_id;
    END IF;

    PERFORM 1 FROM public.plans
    WHERE plan_id = p_plan_id AND status = 'active';
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Plan % does not exist or is not active.', p_plan_id;
    END IF;

    PERFORM 1 FROM public.contracts
    WHERE contract_id = p_contract_id
      AND account_id = p_account_id
      AND status = 'active'
      AND start_date <= p_activation_date
      AND (end_date IS NULL OR end_date >= p_activation_date);
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Contract % is not active for account % on %.', p_contract_id, p_account_id, p_activation_date;
    END IF;

    PERFORM 1 FROM public.sim_cards
    WHERE sim_id = p_sim_id AND status = 'available'
    FOR UPDATE;
    IF NOT FOUND OR EXISTS (
        SELECT 1 FROM public.sim_card_subscription_history
        WHERE sim_id = p_sim_id AND end_date IS NULL
    ) THEN
        RAISE EXCEPTION 'SIM % does not exist, is unavailable, or is already assigned.', p_sim_id;
    END IF;

    v_subscription_id := nextval(pg_get_serial_sequence('public.subscriptions', 'subscription_id'));
    v_subscription_number := 'SUB-' || lpad(v_subscription_id::text, 10, '0');
    v_activation_time := (p_activation_date + time '09:00') AT TIME ZONE 'Europe/Skopje';

    INSERT INTO public.subscriptions (
        subscription_id, account_id, plan_id, contract_id, subscription_number,
        activation_date, status, billing_start_date
    ) VALUES (
        v_subscription_id, p_account_id, p_plan_id, p_contract_id, v_subscription_number,
        p_activation_date, 'active', p_activation_date
    );

    INSERT INTO public.subscription_status_history (
        subscription_id, old_status, new_status, changed_at, reason
    ) VALUES (
        v_subscription_id, NULL, 'active', v_activation_time, 'Subscription activated through proc_activate_subscription'
    );

    INSERT INTO public.sim_card_subscription_history (sim_id, subscription_id, start_date)
    VALUES (p_sim_id, v_subscription_id, v_activation_time);

    UPDATE public.sim_cards
    SET status = 'active', issued_at = COALESCE(issued_at, v_activation_time)
    WHERE sim_id = p_sim_id;

    RAISE NOTICE 'Subscription % activated successfully with SIM %.', v_subscription_number, p_sim_id;
END;
$$;