<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--
  PublicAuthMapper — 공개 회원가입·비밀번호 재설정 영속.
  보안(계약 §0-3): password_hash·code_hash 는 저장/검증 용도로만 다루며 어떤 조회에서도 응답으로 SELECT 하지 않는다.
-->
<mapper namespace="com.zioinfo.kintex.auth.mapper.PublicAuthMapper">

    <!-- 이메일 중복(정규화된 소문자 기준). 존재 시 1. -->
    <select id="countByEmail" resultType="int">
        SELECT COUNT(*) FROM app_user WHERE email = #{email}
    </select>

    <!-- 신규 사용자. 표준 기본값(USER/ACTIVE/EMAIL), hall_manager=false. -->
    <insert id="insertUser">
        INSERT INTO app_user (id, email, display_name, password_hash, hall_manager,
                              role_code, status, verify_method, company_name)
        VALUES (#{id}, #{email}, #{displayName}, #{passwordHash}, false,
                'USER', 'ACTIVE', 'EMAIL', #{companyName})
    </insert>

    <!-- inviteCode → 활성 행사 식별자(best-effort). 없으면 null. -->
    <select id="findEventIdByInvite" resultType="string">
        SELECT id FROM event
        WHERE id = #{code} AND status = 'active'
        LIMIT 1
    </select>

    <!-- 초대 매핑(EXHIBITOR). 유니크(event_id,user_id,role_code) 충돌 시 무시. -->
    <insert id="insertEventMember">
        INSERT INTO event_member (id, event_id, user_id, role_code)
        VALUES (#{id}, #{eventId}, #{userId}, 'EXHIBITOR')
        ON CONFLICT (event_id, user_id, role_code) DO NOTHING
    </insert>

    <!-- 활성 사용자 존재 시 userId(비밀번호 찾기). 없으면 null. -->
    <select id="findActiveUserIdByEmail" resultType="string">
        SELECT id FROM app_user
        WHERE email = #{email} AND status = 'ACTIVE'
        LIMIT 1
    </select>

    <!-- 재설정 코드 저장. -->
    <insert id="insertResetCode">
        INSERT INTO password_reset (email, code_hash, expires_at, used, attempts)
        VALUES (#{email}, #{codeHash}, #{expiresAt}, false, 0)
    </insert>

    <!-- 최신 미사용 재설정 행. code_hash 는 검증에만 사용. -->
    <select id="findLatestActiveReset" resultType="map">
        SELECT id         AS "id",
               code_hash  AS "codeHash",
               expires_at AS "expiresAt",
               attempts   AS "attempts"
        FROM password_reset
        WHERE email = #{email} AND used = false
        ORDER BY created_at DESC
        LIMIT 1
    </select>

    <!-- 1회성 소진. -->
    <update id="markResetUsed">
        UPDATE password_reset SET used = true WHERE id = #{id}
    </update>

    <!-- 검증 실패 누적. -->
    <update id="incrementResetAttempts">
        UPDATE password_reset SET attempts = attempts + 1 WHERE id = #{id}
    </update>

    <!-- 비밀번호 갱신 + 실패 카운트/잠금 해제. -->
    <update id="updatePasswordByEmail">
        UPDATE app_user
        SET password_hash      = #{passwordHash},
            failed_login_count = 0,
            locked_until       = NULL,
            updated_at         = now()
        WHERE email = #{email}
    </update>

</mapper>
