<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.cloudhandson.vpdbackoffice.mapper.BearerTokenMapper">
  <select id="findAll" resultType="com.cloudhandson.vpdbackoffice.domain.token.BearerTokenRecord">
    SELECT k.key_id, k.user_id, u.user_name AS username,
           CAST(NULL AS VARCHAR2(100)) AS stakeholder_user_id,
           CAST(NULL AS VARCHAR2(100)) AS stakeholder_role, CAST(NULL AS VARCHAR2(100)) AS stakeholder_channel,
           k.key_prefix, k.key_hash,
           k.expires_at, k.revoked_at, k.description
    FROM sg_access_bearer_token k JOIN sg_app_user u ON u.user_id = k.user_id
    WHERE #{includeInactive} = 1
       OR (k.revoked_at IS NULL AND k.expires_at > SYSTIMESTAMP)
    ORDER BY k.key_id DESC
  </select>

  <select id="findById" resultType="com.cloudhandson.vpdbackoffice.domain.token.BearerTokenRecord">
    SELECT k.key_id, k.user_id, u.user_name AS username,
           CAST(NULL AS VARCHAR2(100)) AS stakeholder_user_id,
           CAST(NULL AS VARCHAR2(100)) AS stakeholder_role, CAST(NULL AS VARCHAR2(100)) AS stakeholder_channel,
           k.key_prefix, k.key_hash,
           k.expires_at, k.revoked_at, k.description
    FROM sg_access_bearer_token k JOIN sg_app_user u ON u.user_id = k.user_id
    WHERE k.key_id = #{keyId}
  </select>

  <select id="findByHash" resultType="com.cloudhandson.vpdbackoffice.domain.token.BearerTokenRecord">
    SELECT k.key_id, k.user_id, u.user_name AS username,
           CAST(NULL AS VARCHAR2(100)) AS stakeholder_user_id,
           CAST(NULL AS VARCHAR2(100)) AS stakeholder_role, CAST(NULL AS VARCHAR2(100)) AS stakeholder_channel,
           k.key_prefix, k.key_hash,
           k.expires_at, k.revoked_at, k.description
    FROM sg_access_bearer_token k JOIN sg_app_user u ON u.user_id = k.user_id
    WHERE k.key_hash = #{keyHash,jdbcType=VARCHAR}
  </select>

  <select id="nextKeyId" resultType="long">
    SELECT NVL(MAX(key_id), 0) + 1 FROM sg_access_bearer_token
  </select>

  <insert id="insertToken" parameterType="com.cloudhandson.vpdbackoffice.domain.token.BearerTokenRecord">
    INSERT INTO sg_access_bearer_token (
      key_id, user_id, key_prefix, key_hash, expires_at, revoked_at, description
    ) VALUES (
      #{keyId,jdbcType=NUMERIC},
      #{userId,jdbcType=NUMERIC},
      #{keyPrefix,jdbcType=VARCHAR},
      #{keyHash,jdbcType=VARCHAR},
      #{expiresAt,jdbcType=TIMESTAMP},
      #{revokedAt,jdbcType=TIMESTAMP},
      #{description,jdbcType=VARCHAR}
    )
  </insert>

  <update id="revokeToken">
    UPDATE sg_access_bearer_token
    SET revoked_at = #{revokedAt,jdbcType=TIMESTAMP},
        description = CASE
          WHEN #{reason,jdbcType=VARCHAR} IS NULL THEN description
          ELSE SUBSTR(COALESCE(description, '') || ' revoked: ' || #{reason,jdbcType=VARCHAR}, 1, 200)
        END
    WHERE key_id = #{keyId,jdbcType=NUMERIC}
      AND revoked_at IS NULL
  </update>
</mapper>
