DROP TRIGGER IF EXISTS trg_log_subscription_status_change ON public.subscriptions;
CREATE OR REPLACE FUNCTION public.trg_fn_log_subscription_status_change()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_employee_id bigint;
    v_reason text;
BEGIN
    v_employee_id := NULLIF(current_setting('app.employee_id', true), '')::bigint;
    v_reason := COALESCE(
        NULLIF(current_setting('app.change_reason', true), ''),
        format('Automatic log: status changed from %s to %s', OLD.status, NEW.status)
    );

    INSERT INTO public.subscription_status_history (
        subscription_id, old_status, new_status, changed_at,
        changed_by_employee_id, reason
    ) VALUES (
        NEW.subscription_id, OLD.status, NEW.status, CURRENT_TIMESTAMP,
        v_employee_id, v_reason
    );

    UPDATE public.sim_cards sc
    SET status = CASE
        WHEN NEW.status = 'active' THEN 'active'
        WHEN NEW.status = 'suspended' THEN 'suspended'
        ELSE 'deactivated'
    END
    FROM public.sim_card_subscription_history ssh
    WHERE ssh.subscription_id = NEW.subscription_id
      AND ssh.end_date IS NULL
      AND sc.sim_id = ssh.sim_id;

    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_log_subscription_status_change
AFTER UPDATE OF status ON public.subscriptions
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION public.trg_fn_log_subscription_status_change();