<?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="com.ga.core.mapper.user.UserMapper">

    <resultMap id="UserVOMap" type="com.ga.core.vo.user.UserVO"/>
    <resultMap id="UserRespMap" type="com.ga.core.vo.user.UserResp"/>

    <resultMap id="UserContactBriefMap" type="com.ga.core.vo.user.UserContactBrief">
        <id  property="userId"   column="user_id"/>
        <result property="userName" column="user_name"/>
        <result property="phone"    column="phone"/>
    </resultMap>

    <sql id="UserCols">
        u.user_id, u.login_id, u.password, u.user_name, u.email, u.phone, u.agent_id,
        u.status, u.fail_count, u.last_login_at, u.pwd_changed_at,
        u.created_at, u.created_by, u.updated_at, u.updated_by
    </sql>

    <select id="selectById" resultMap="UserVOMap">
        SELECT <include refid="UserCols"/>
        FROM users u WHERE u.user_id = #{userId}
    </select>

    <select id="selectByLoginId" resultMap="UserVOMap">
        SELECT <include refid="UserCols"/>
        FROM users u WHERE u.login_id = #{loginId}
    </select>

    <select id="selectDetailById" resultMap="UserRespMap">
        SELECT u.user_id, u.login_id, u.user_name, u.email, u.phone,
               u.agent_id, a.agent_name, o.org_name,
               u.status,
               (SELECT cc.code_name FROM common_code cc
                 WHERE cc.group_code = 'AGENT_STATUS' AND cc.code = u.status) AS status_name,
               u.fail_count, u.last_login_at, u.created_at
        FROM users u
        LEFT JOIN agent a ON a.agent_id = u.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        WHERE u.user_id = #{userId}
    </select>

    <select id="selectList" resultMap="UserRespMap">
        SELECT u.user_id, u.login_id, u.user_name, u.email, u.phone,
               u.agent_id, a.agent_name, o.org_name,
               u.status,
               u.fail_count, u.last_login_at, u.created_at
        FROM users u
        LEFT JOIN agent a ON a.agent_id = u.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        <if test="roleId != null">
            JOIN user_role ur ON ur.user_id = u.user_id AND ur.role_id = #{roleId}
        </if>
        <where>
            <if test="searchKeyword != null and searchKeyword != ''">
                AND (u.login_id LIKE CONCAT('%', #{searchKeyword}, '%')
                  OR u.user_name LIKE CONCAT('%', #{searchKeyword}, '%'))
            </if>
            <if test="status != null and status != ''">AND u.status = #{status}</if>
            <if test="orgId != null">AND a.org_id = #{orgId}</if>
        </where>
        ORDER BY u.created_at DESC
    </select>

    <select id="countByCondition" resultType="int">
        SELECT COUNT(*) FROM users u
        <where>
            <if test="status != null and status != ''">AND u.status = #{status}</if>
        </where>
    </select>

    <insert id="insert" parameterType="com.ga.core.vo.user.UserVO" useGeneratedKeys="true" keyProperty="userId">
        INSERT INTO users (login_id, password, user_name, email, phone, agent_id, status,
                           fail_count, pwd_changed_at, created_at, created_by)
        VALUES (#{loginId}, #{password}, #{userName}, #{email}, #{phone}, #{agentId},
                COALESCE(#{status},'ACTIVE'), 0, NOW(), #{createdAt}, #{createdBy})
    </insert>

    <update id="update" parameterType="com.ga.core.vo.user.UserVO">
        UPDATE users SET
            user_name = #{userName},
            email     = #{email},
            phone     = #{phone},
            agent_id  = #{agentId},
            status    = #{status},
            updated_at = #{updatedAt},
            updated_by = #{updatedBy}
        WHERE user_id = #{userId}
    </update>

    <update id="updatePassword">
        UPDATE users SET password = #{password}, pwd_changed_at = NOW(), fail_count = 0
        WHERE user_id = #{userId}
    </update>

    <update id="updateLastLogin">
        UPDATE users SET last_login_at = NOW(), fail_count = 0 WHERE user_id = #{userId}
    </update>

    <update id="incrementFailCount">
        UPDATE users SET fail_count = fail_count + 1 WHERE user_id = #{userId}
    </update>

    <update id="resetFailCount">
        UPDATE users SET fail_count = 0 WHERE user_id = #{userId}
    </update>

    <update id="updateStatus">
        UPDATE users SET status = #{status} WHERE user_id = #{userId}
    </update>

    <update id="updatePhone">
        UPDATE users SET phone = #{phone,jdbcType=VARCHAR} WHERE user_id = #{userId}
    </update>

    <select id="selectRoleCodes" resultType="string">
        SELECT r.role_code FROM role r
        JOIN user_role ur ON ur.role_id = r.role_id
        WHERE ur.user_id = #{userId} AND r.is_active = 'Y'
    </select>

    <insert id="insertUserRole">
        INSERT INTO user_role (user_id, role_id) VALUES (#{userId}, #{roleId})
        ON CONFLICT DO NOTHING
    </insert>

    <delete id="deleteUserRoles">
        DELETE FROM user_role WHERE user_id = #{userId}
    </delete>

    <!--
        role_code 보유 + ACTIVE 사용자 1명의 연락처 요약.
        결재 단계 알림 시 단계 담당자의 phone 획득용.
        여러 명일 경우: user_id 오름차순 (가장 먼저 등록된 담당자 우선)
    -->
    <select id="selectFirstActiveContactByRoleCode" resultMap="UserContactBriefMap">
        SELECT u.user_id, u.user_name, u.phone
          FROM users u
          JOIN user_role ur ON ur.user_id = u.user_id
          JOIN role      r  ON r.role_id  = ur.role_id
         WHERE r.role_code = #{roleCode}
           AND u.status    = 'ACTIVE'
         ORDER BY u.user_id
         LIMIT 1
    </select>

</mapper>
