CREATE OR REPLACE FUNCTION fn_check_sim_activation_status(
    p_sim_id BIGINT
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    v_sim_status TEXT;
    v_active_subscription BIGINT;
BEGIN
    SELECT status
    INTO v_sim_status
    FROM 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 sim_card_subscription_history
    WHERE sim_id = p_sim_id
      AND end_date IS NULL
    LIMIT 1;

    IF v_active_subscription IS NOT NULL THEN
        RETURN 'SIM is already assigned to active subscription ' || v_active_subscription || '.';
    END IF;

    IF v_sim_status <> 'available' THEN
        RETURN 'SIM exists but status is ' || v_sim_status || ', not available.';
    END IF;

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

SELECT fn_check_sim_activation_status(1);

-- mozhe da se koristi za sim activation procedurot