CREATE OR REPLACE FUNCTION trg_fn_prevent_duplicate_active_sim()
    RETURNS TRIGGER LANGUAGE plpgsql AS $$
DECLARE
    v_existing_sub BIGINT;
BEGIN
    IF NEW.end_date IS NULL AND NEW.sim_id IS NOT NULL THEN
        SELECT subscription_id INTO v_existing_sub
          FROM sim_card_subscription_history
         WHERE sim_id = NEW.sim_id AND end_date IS NULL
           AND sim_card_subscription_history_id<>NEW.sim_card_subscription_history_id
         LIMIT 1;

        IF FOUND THEN
            RAISE EXCEPTION
                'SIM card (sim_id=%) is already actively assigned to '
                'subscription_id=%. Close the existing assignment first.',
                NEW.sim_id, v_existing_sub;
        END IF;
    END IF;
    RETURN NEW;
END;
$$;

drop trigger trg_prevent_duplicate_active_sim on sim_card_subscription_history;
drop function trg_fn_prevent_duplicate_active_sim();

CREATE OR REPLACE TRIGGER trg_prevent_duplicate_active_sim
    BEFORE UPDATE or INSERT ON sim_card_subscription_history
    FOR EACH ROW
EXECUTE FUNCTION trg_fn_prevent_duplicate_active_sim();