"""登录前查令牌为什么失效：被吊销（含挤下线）还是过期（API3，api-design §2）。

和 auth_resolve_session 一样是 SECURITY DEFINER，所有者 bd_auth，只返回状态，不返回会话内容（data-model §2.3）。

Revision ID: 0004
"""

from __future__ import annotations

from alembic import op

revision = "0004"
down_revision = "0003"


def upgrade() -> None:
    op.get_bind().exec_driver_sql("""
CREATE FUNCTION auth_session_state(p_token_hash bytea)
RETURNS TABLE (revoke_reason text, expired boolean)
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = app AS $$
  SELECT s.revoke_reason, s.expires_at <= now()
  FROM user_session s WHERE s.token_hash = p_token_hash
$$;
ALTER FUNCTION auth_session_state(bytea) OWNER TO bd_auth;
GRANT EXECUTE ON FUNCTION auth_session_state(bytea) TO bd_app;
""")


def downgrade() -> None:
    op.get_bind().exec_driver_sql("DROP FUNCTION auth_session_state(bytea)")