CREATE OR REPLACE FUNCTION trg_fn_log_subscription_status_change()
    RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.status IS DISTINCT FROM OLD.status THEN
        INSERT INTO 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, NULL,
            'Automatic log: status changed from ' || OLD.status || ' to ' || NEW.status
        );
    END IF;
    RETURN NEW;
END;
$$;

-- na sekoja promena na subscription status go stava vo status history

DROP TRIGGER IF EXISTS trg_log_subscription_status_change ON subscriptions;
drop function trg_fn_log_subscription_status_change();

CREATE OR REPLACE TRIGGER trg_log_subscription_status_change
    AFTER UPDATE OF status ON subscriptions
    FOR EACH ROW
EXECUTE FUNCTION trg_fn_log_subscription_status_change();