<?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">
<mapper namespace="auth">

  <!-- 비밀번호 조회 -->
  <select id="selectUserPassword" parameterType="map" resultType="map">
    SELECT
        USER_PASSWORD
    FROM USER_INFO
    WHERE USER_ID = #{user_id}
  </select>

  <!-- 사용자 전체 정보 조회 -->
  <select id="selectUserInfo" parameterType="map" resultType="map">
    SELECT
        SABUN
        , USER_ID
        , USER_NAME
        , USER_NAME_ENG
        , USER_NAME_CN
        , DEPT_CODE
        , DEPT_NAME
        , POSITION_CODE
        , POSITION_NAME
        , EMAIL
        , TEL
        , CELL_PHONE
        , USER_TYPE
        , USER_TYPE_NAME
        , PARTNER_OBJID
        , COMPANY_CODE
        , LOCALE
        , PHOTO
        , FORCE_PASSWORD_CHANGE
    FROM USER_INFO
    WHERE UPPER(USER_ID) = UPPER(#{user_id})
  </select>

  <!-- 사용자 권한명 목록 조회 -->
  <select id="selectUserAuth" parameterType="map" resultType="map">
    SELECT
        AM.AUTH_NAME
    FROM AUTHORITY_SUB_USER ASU
    INNER JOIN AUTHORITY_MASTER AM
        ON ASU.MASTER_OBJID = AM.OBJID
    WHERE ASU.USER_ID = #{user_id}
  </select>

  <!-- 회사명 조회 -->
  <select id="selectCompanyName" parameterType="map" resultType="map">
    SELECT
        COMPANY_NAME
    FROM COMPANY_MNG
    WHERE (COMPANY_CODE = #{company_code} OR COMPANY_CODE = '*')
    ORDER BY CASE WHEN COMPANY_CODE = #{company_code} THEN 0 ELSE 1 END
    LIMIT 1
  </select>

  <!-- 회사 코드 존재 여부 확인 (switch-company용) -->
  <select id="selectCompanyByCode" parameterType="map" resultType="map">
    SELECT
        COMPANY_CODE
        , COMPANY_NAME
    FROM COMPANY_MNG
    WHERE COMPANY_CODE = #{company_code}
  </select>

  <!-- 비밀번호 해시 업데이트 (BCrypt 점진 재해시 + 회원가입 후 강제 변경 등) -->
  <update id="updateUserPassword" parameterType="map">
    UPDATE USER_INFO
    SET USER_PASSWORD = #{user_password}
    WHERE USER_ID = #{user_id}
  </update>

  <!-- 사용자 본인 비밀번호 변경 + 강제 변경 플래그 해제 (RUN_082) -->
  <update id="updateUserPasswordAndClearForce" parameterType="map">
    UPDATE USER_INFO
    SET USER_PASSWORD = #{user_password}
      , FORCE_PASSWORD_CHANGE = FALSE
    WHERE UPPER(USER_ID) = UPPER(#{user_id})
  </update>

  <!-- 락아웃 판단용: 최근 N분 내 동일 user_id 의 로그인 실패 건수 -->
  <!-- LOGIN_RESULT 컬럼은 character varying 이고 'true'/'false' 문자열로 저장됨 (boolean 아님) -->
  <select id="countRecentLoginFailures" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM LOGIN_ACCESS_LOG
    WHERE UPPER(USER_ID) = UPPER(#{user_id})
      AND LOGIN_RESULT = 'false'
      AND LOG_TIME &gt; NOW() - (#{lockout_window_minutes}::text || ' minutes')::interval
  </select>

  <!-- 로그인 접근 로그 기록 -->
  <insert id="insertLoginLog" parameterType="map">
    INSERT INTO LOGIN_ACCESS_LOG (
        LOG_TIME
        , SYSTEM_NAME
        , USER_ID
        , LOGIN_RESULT
        , ERROR_MESSAGE
        , REMOTE_ADDR
        , RECPTN_DT
        , RECPTN_RSLT_DTL
        , RECPTN_RSLT
        , RECPTN_RSLT_CD
    ) VALUES (
        NOW()
        , #{system_name}
        , UPPER(#{user_id})
        , #{login_result}
        , #{error_message}
        , #{remote_addr}
        , #{recptn_dt}
        , #{recptn_rslt_dtl}
        , #{recptn_rslt}
        , #{recptn_rslt_cd}
    )
  </insert>

  <!-- 아이디 중복 확인 (signup용) -->
  <select id="selectUserById" parameterType="map" resultType="map">
    SELECT
        USER_ID
    FROM USER_INFO
    WHERE USER_ID = #{user_id}
  </select>

  <!-- 차량번호 중복 확인 (signup용) -->
  <select id="selectVehicleByNumber" parameterType="map" resultType="map">
    SELECT
        VEHICLE_NUMBER
    FROM VEHICLES
    WHERE VEHICLE_NUMBER = #{vehicle_number}
  </select>

  <!-- 공차중계 회원가입 - 사용자 등록 -->
  <insert id="insertUserSignup" parameterType="map">
    INSERT INTO USER_INFO (
        USER_ID
        , USER_PASSWORD
        , USER_NAME
        , CELL_PHONE
        , LICENSE_NUMBER
        , VEHICLE_NUMBER
        , COMPANY_CODE
        , USER_TYPE
        , SIGNUP_TYPE
        , STATUS
        , CREATED_DATE
    ) VALUES (
        #{user_id}
        , #{user_password}
        , #{user_name}
        , #{phone_number}
        , #{license_number}
        , #{vehicle_number}
        , #{signup_company_code}
        , NULL
        , 'DRIVER'
        , 'active'
        , NOW()
    )
  </insert>

  <!-- 공차중계 회원가입 - 차량 등록 -->
  <insert id="insertVehicle" parameterType="map">
    INSERT INTO VEHICLES (
        VEHICLE_NUMBER
        , VEHICLE_TYPE
        , DRIVER_NAME
        , DRIVER_PHONE
        , STATUS
        , COMPANY_CODE
        , USER_ID
        , CREATED_DATE
        , UPDATED_DATE
    ) VALUES (
        #{vehicle_number}
        , #{vehicle_type}
        , #{user_name}
        , #{phone_number}
        , 'off'
        , #{signup_company_code}
        , #{user_id}
        , NOW()
        , NOW()
    )
  </insert>

</mapper>
