CREATE OR REPLACE FUNCTION public.fn_check_sim_activation_status(p_sim_id bigint)
RETURNS text
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_sim_status text;
    v_active_subscription bigint;
BEGIN
    SELECT status INTO v_sim_status
    FROM public.sim_cards
    WHERE sim_id = p_sim_id;

    IF NOT FOUND THEN
        RETURN 'SIM does not exist.';
    END IF;

    SELECT subscription_id INTO v_active_subscription
    FROM public.sim_card_subscription_history
    WHERE sim_id = p_sim_id AND end_date IS NULL
    ORDER BY start_date DESC
    LIMIT 1;

    IF v_active_subscription IS NOT NULL THEN
        RETURN format('SIM is already assigned to active subscription %s.', v_active_subscription);
    END IF;

    IF v_sim_status <> 'available' THEN
        RETURN format('SIM exists but status is %s, not available.', v_sim_status);
    END IF;

    RETURN 'SIM is available and can be used for activation.';
END;
$$;