DatabaseProgramming: Customer sim assignment.sql

CREATE OR REPLACE PROCEDURE proc_activate_subscription(
    p_account_id BIGINT,
    p_plan_id BIGINT,
    p_sim_id BIGINT
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_subscription_id BIGINT;
    v_subscription_number TEXT;
BEGIN
    -- Check if account exists and is active
    IF NOT EXISTS (
        SELECT 1
        FROM accounts
        WHERE account_id = p_account_id
          AND account_status = 'active'
    ) THEN
        RAISE EXCEPTION 'Account % does not exist or is not active.', p_account_id;
    END IF;

    -- Check if plan exists and is active
    IF NOT EXISTS (
        SELECT 1
        FROM plans
        WHERE plan_id = p_plan_id
          AND status = 'active'
    ) THEN
        RAISE EXCEPTION 'Plan % does not exist or is not active.', p_plan_id;
    END IF;

    -- Check if SIM card exists and is available
    IF NOT EXISTS (
        SELECT 1
        FROM sim_cards
        WHERE sim_id = p_sim_id
          AND status = 'available'
    ) THEN
        RAISE EXCEPTION 'SIM card % does not exist or is not available.', p_sim_id;
    END IF;

    -- Generate subscription number
    v_subscription_number := 'SUB-' || FLOOR(RANDOM() * 100000000)::BIGINT;

    -- Create subscription
    INSERT INTO subscriptions (
        account_id,
        plan_id,
        subscription_number,
        activation_date,
        status,
        billing_start_date
    )
    VALUES (
        p_account_id,
        p_plan_id,
        v_subscription_number,
        CURRENT_DATE,
        'active',
        CURRENT_DATE
    )
    RETURNING subscription_id INTO v_subscription_id;

    -- Assign SIM card to the subscription
    INSERT INTO sim_card_subscription_history (
        sim_id,
        subscription_id,
        start_date,
        end_date
    )
    VALUES (
        p_sim_id,
        v_subscription_id,
        CURRENT_TIMESTAMP,
        NULL
    );

    -- Mark SIM card as active
    UPDATE sim_cards
    SET status = 'active',
        issued_at = CURRENT_TIMESTAMP
    WHERE sim_id = p_sim_id;

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