-- === LOGIN FUNCTION ===

CREATE OR REPLACE FUNCTION auth_get_user_for_login(p_email TEXT)
RETURNS TABLE (
  id BIGINT,
  email VARCHAR,
  password_hash TEXT,
  is_active BOOLEAN,
  role role_type,
  committee_id BIGINT,
  two_factor_enabled BOOLEAN,
  locked_until TIMESTAMPTZ
)
LANGUAGE sql
SECURITY DEFINER
AS $$
  SELECT
    u.id,
    u.email,
    u.password_hash,
    u.is_active,
    u.role,
    u.committee_id,
    u.two_factor_enabled,
    u.locked_until
  FROM app_user u
  WHERE LOWER(u.email) = LOWER(p_email)
  LIMIT 1;
$$;

REVOKE ALL ON FUNCTION auth_get_user_for_login(TEXT) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION auth_get_user_for_login(TEXT) TO br_app;