DatabaseProgramming: 06 SIM history status trigger.sql

-- Duplicate active SIM protection is already stronger in the DDL through the
-- temporal exclusion constraint. Remove the obsolete legacy trigger if present.
DROP TRIGGER IF EXISTS trg_prevent_duplicate_active_sim ON public.sim_card_subscription_history;
DROP FUNCTION IF EXISTS public.trg_fn_prevent_duplicate_active_sim();

-- The DDL exclusion constraint prevents one SIM from overlapping across
-- subscriptions. This complementary rule prevents one subscription from
-- holding two current SIMs.
CREATE UNIQUE INDEX IF NOT EXISTS uq_sim_history_one_current_per_subscription
    ON public.sim_card_subscription_history (subscription_id)
    WHERE end_date IS NULL;
DROP TRIGGER IF EXISTS trg_sync_sim_status_from_history ON public.sim_card_subscription_history;
CREATE OR REPLACE FUNCTION public.trg_fn_sync_sim_status_from_history()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_new_sim_id bigint;
    v_old_sim_id bigint;
    v_sim_id bigint;
    v_subscription_status text;
BEGIN
    IF TG_OP <> 'DELETE' THEN v_new_sim_id := NEW.sim_id; END IF;
    IF TG_OP <> 'INSERT' THEN v_old_sim_id := OLD.sim_id; END IF;

    FOREACH v_sim_id IN ARRAY ARRAY[v_new_sim_id, v_old_sim_id]
    LOOP
        IF v_sim_id IS NULL THEN CONTINUE; END IF;

        SELECT s.status INTO v_subscription_status
        FROM public.sim_card_subscription_history ssh
        JOIN public.subscriptions s ON s.subscription_id = ssh.subscription_id
        WHERE ssh.sim_id = v_sim_id AND ssh.end_date IS NULL
        ORDER BY ssh.start_date DESC
        LIMIT 1;

        UPDATE public.sim_cards
        SET status = CASE
            WHEN v_subscription_status = 'suspended' THEN 'suspended'
            WHEN v_subscription_status IS NOT NULL THEN 'active'
            WHEN EXISTS (
                SELECT 1 FROM public.sim_card_subscription_history h WHERE h.sim_id = v_sim_id
            ) THEN 'deactivated'
            ELSE 'available'
        END
        WHERE sim_id = v_sim_id;
    END LOOP;

    IF TG_OP = 'DELETE' THEN RETURN OLD; END IF;
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_sync_sim_status_from_history
AFTER INSERT OR UPDATE OR DELETE ON public.sim_card_subscription_history
FOR EACH ROW
EXECUTE FUNCTION public.trg_fn_sync_sim_status_from_history();