<?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.org.AgentMapper">

    <resultMap id="AgentVOMap" type="com.ga.core.vo.org.AgentVO">
        <id property="agentId"     column="agent_id"/>
        <result property="orgId"   column="org_id"/>
        <result property="gradeId" column="grade_id"/>
        <result property="agentName" column="agent_name"/>
        <result property="residentNo" column="resident_no" typeHandler="com.ga.common.mybatis.EncryptTypeHandler"/>
        <result property="licenseNo" column="license_no"/>
        <result property="phone"     column="phone"/>
        <result property="email"     column="email"/>
        <result property="bankCode"  column="bank_code"/>
        <result property="accountNo" column="account_no" typeHandler="com.ga.common.mybatis.EncryptTypeHandler"/>
        <result property="status"     column="status"/>
        <result property="joinDate"   column="join_date"/>
        <result property="leaveDate"  column="leave_date"/>
        <result property="taxType"    column="tax_type"/>
        <result property="businessNo" column="business_no"/>
        <result property="vatType"    column="vat_type"/>
        <result property="createdAt"  column="created_at"/>
        <result property="updatedAt"  column="updated_at"/>
    </resultMap>

    <resultMap id="AgentRespMap" type="com.ga.core.vo.org.AgentResp"/>
    <resultMap id="AgentExcelMap" type="com.ga.core.vo.org.AgentExcelVO">
        <result property="residentNo" column="resident_no" typeHandler="com.ga.common.mybatis.EncryptTypeHandler"/>
    </resultMap>
    <resultMap id="AgentHistoryMap" type="com.ga.core.vo.org.AgentOrgHistoryVO"/>

    <sql id="agentJoinCols">
        a.agent_id, a.agent_name, a.license_no, a.phone, a.email, a.status,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='AGENT_STATUS' AND cc.code=a.status) AS status_name,
        a.org_id, o.org_name,
        a.grade_id, g.grade_name,
        a.join_date, a.leave_date,
        a.tax_type,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='TAX_TYPE' AND cc.code=a.tax_type) AS tax_type_name,
        a.business_no,
        a.vat_type,
        (SELECT cc.code_name FROM common_code cc WHERE cc.group_code='VAT_TYPE' AND cc.code=a.vat_type) AS vat_type_name
    </sql>

    <select id="selectDetailById" resultMap="AgentRespMap">
        SELECT <include refid="agentJoinCols"/>,
               (SELECT COUNT(*) FROM contract c WHERE c.agent_id = a.agent_id AND c.status = 'ACTIVE') AS contract_count,
               (SELECT COALESCE(SUM(s.net_amount),0) FROM settle_master s WHERE s.agent_id = a.agent_id) AS total_commission
        FROM agent a
        LEFT JOIN organization o ON o.org_id = a.org_id
        LEFT JOIN grade g        ON g.grade_id = a.grade_id
        WHERE a.agent_id = #{agentId}
    </select>

    <select id="selectById" resultMap="AgentVOMap">
        SELECT * FROM agent WHERE agent_id = #{agentId}
    </select>

    <select id="selectList" resultMap="AgentRespMap">
        SELECT <include refid="agentJoinCols"/>
        FROM agent a
        LEFT JOIN organization o ON o.org_id = a.org_id
        LEFT JOIN grade g        ON g.grade_id = a.grade_id
        <where>
            <if test="searchKeyword != null and searchKeyword != ''">
                <choose>
                    <when test="searchType == 'license'">
                        AND a.license_no = #{searchKeyword}
                    </when>
                    <otherwise>
                        AND a.agent_name LIKE CONCAT('%', #{searchKeyword}, '%')
                    </otherwise>
                </choose>
            </if>
            <if test="status != null and status != ''">AND a.status = #{status}</if>
            <if test="gradeId != null">AND a.grade_id = #{gradeId}</if>
            <if test="orgId != null">
                <choose>
                    <when test="includeSubOrgs == true">
                        AND a.org_id IN (
                            WITH RECURSIVE d AS (
                                SELECT org_id FROM organization WHERE org_id = #{orgId}
                                UNION ALL
                                SELECT o.org_id FROM organization o JOIN d ON o.parent_org_id = d.org_id
                            )
                            SELECT org_id FROM d
                        )
                    </when>
                    <otherwise>
                        AND a.org_id = #{orgId}
                    </otherwise>
                </choose>
            </if>
        </where>
        ORDER BY a.agent_id DESC
    </select>

    <!-- 엑셀 다운로드용 Cursor (스트리밍) -->
    <select id="selectCursorForExcel" resultMap="AgentExcelMap" fetchSize="500" resultSetType="FORWARD_ONLY">
        SELECT a.agent_name, a.license_no, o.org_name, g.grade_name,
               a.phone, a.email, a.resident_no, a.status, a.join_date
        FROM agent a
        LEFT JOIN organization o ON o.org_id = a.org_id
        LEFT JOIN grade g        ON g.grade_id = a.grade_id
        <where>
            <if test="status != null and status != ''">AND a.status = #{status}</if>
            <if test="orgId != null">AND a.org_id = #{orgId}</if>
        </where>
        ORDER BY a.agent_id
    </select>

    <select id="existsByLicenseNo" resultType="int">
        SELECT COUNT(*) FROM agent WHERE license_no = #{licenseNo}
    </select>

    <insert id="insert" parameterType="com.ga.core.vo.org.AgentVO" useGeneratedKeys="true" keyProperty="agentId">
        INSERT INTO agent (org_id, grade_id, agent_name, resident_no, license_no, phone, email,
                           bank_code, account_no, status, join_date,
                           tax_type, business_no, vat_type, created_at)
        VALUES (#{orgId}, #{gradeId}, #{agentName},
                #{residentNo,typeHandler=com.ga.common.mybatis.EncryptTypeHandler},
                #{licenseNo}, #{phone}, #{email}, #{bankCode},
                #{accountNo,typeHandler=com.ga.common.mybatis.EncryptTypeHandler},
                COALESCE(#{status},'ACTIVE'), #{joinDate},
                COALESCE(#{taxType},'BUSINESS_INCOME'), #{businessNo}, COALESCE(#{vatType},'NONE'),
                #{createdAt})
    </insert>

    <update id="update" parameterType="com.ga.core.vo.org.AgentVO">
        UPDATE agent SET
            org_id = #{orgId},
            grade_id = #{gradeId},
            agent_name = #{agentName},
            resident_no = #{residentNo,typeHandler=com.ga.common.mybatis.EncryptTypeHandler},
            license_no = #{licenseNo},
            phone = #{phone},
            email = #{email},
            bank_code = #{bankCode},
            account_no = #{accountNo,typeHandler=com.ga.common.mybatis.EncryptTypeHandler},
            status = #{status},
            join_date = #{joinDate},
            leave_date = #{leaveDate},
            tax_type = #{taxType},
            business_no = #{businessNo},
            vat_type = #{vatType},
            updated_at = #{updatedAt}
        WHERE agent_id = #{agentId}
    </update>

    <update id="updateStatus">
        UPDATE agent SET status = #{status}, updated_at = NOW() WHERE agent_id = #{agentId}
    </update>

    <update id="updateOrgGrade">
        UPDATE agent SET org_id = #{orgId}, grade_id = #{gradeId}, updated_at = NOW()
        WHERE agent_id = #{agentId}
    </update>

    <resultMap id="AgentOrgHistoryRespMap" type="com.ga.core.vo.org.AgentOrgHistoryResp">
        <id     property="historyId"      column="history_id"/>
        <result property="agentId"        column="agent_id"/>
        <result property="agentName"      column="agent_name"/>
        <result property="fromOrgId"      column="from_org_id"/>
        <result property="fromOrgName"    column="from_org_name"/>
        <result property="toOrgId"        column="to_org_id"/>
        <result property="toOrgName"      column="to_org_name"/>
        <result property="fromGradeId"    column="from_grade_id"/>
        <result property="fromGradeName"  column="from_grade_name"/>
        <result property="toGradeId"      column="to_grade_id"/>
        <result property="toGradeName"    column="to_grade_name"/>
        <result property="changeType"     column="change_type"/>
        <result property="changeTypeName" column="change_type_name"/>
        <result property="changeDate"     column="change_date"/>
        <result property="changeReason"   column="change_reason"/>
    </resultMap>

    <select id="selectHistory" resultMap="AgentHistoryMap">
        SELECT * FROM agent_org_history WHERE agent_id = #{agentId} ORDER BY change_date DESC, history_id DESC
    </select>

    <!-- 이력 조회 — 조인으로 이름 필드 포함 (AgentOrgHistoryResp 매핑) -->
    <select id="selectHistoryResp" resultMap="AgentOrgHistoryRespMap">
        SELECT
            h.history_id,
            h.agent_id,
            a.agent_name,
            h.from_org_id,
            fo.org_name                                                     AS from_org_name,
            h.to_org_id,
            to_.org_name                                                    AS to_org_name,
            h.from_grade_id,
            fg.grade_name                                                   AS from_grade_name,
            h.to_grade_id,
            tg.grade_name                                                   AS to_grade_name,
            h.change_type,
            (SELECT cc.code_name FROM common_code cc
              WHERE cc.group_code = 'CHANGE_TYPE' AND cc.code = h.change_type)  AS change_type_name,
            h.change_date,
            h.change_reason
        FROM agent_org_history h
        LEFT JOIN agent        a   ON a.agent_id   = h.agent_id
        LEFT JOIN organization fo  ON fo.org_id    = h.from_org_id
        LEFT JOIN organization to_ ON to_.org_id   = h.to_org_id
        LEFT JOIN grade        fg  ON fg.grade_id  = h.from_grade_id
        LEFT JOIN grade        tg  ON tg.grade_id  = h.to_grade_id
        WHERE h.agent_id = #{agentId}
        ORDER BY h.change_date DESC, h.history_id DESC
    </select>

    <insert id="insertHistory" parameterType="com.ga.core.vo.org.AgentOrgHistoryVO" useGeneratedKeys="true" keyProperty="historyId">
        INSERT INTO agent_org_history (agent_id, from_org_id, to_org_id, from_grade_id, to_grade_id,
                                       change_type, change_date, change_reason, created_at, created_by)
        VALUES (#{agentId}, #{fromOrgId}, #{toOrgId}, #{fromGradeId}, #{toGradeId},
                #{changeType}, #{changeDate}, #{changeReason}, #{createdAt}, #{createdBy})
    </insert>

</mapper>
