<?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.license.AgentLicenseMapper">

    <resultMap id="VOMap"   type="com.ga.core.vo.license.AgentLicenseVO"/>
    <resultMap id="RespMap" type="com.ga.core.vo.license.AgentLicenseResp"/>

    <sql id="joinCols">
        al.license_id, al.agent_id, a.agent_name, o.org_name,
        al.license_type,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'LICENSE_TYPE' AND cc.code = al.license_type) AS license_type_name,
        al.license_no,
        al.issued_date, al.expires_date,
        al.renewed_from, al.status,
        (SELECT cc.code_name FROM common_code cc
            WHERE cc.group_code = 'LICENSE_STATUS' AND cc.code = al.status) AS status_name,
        (al.expires_date - CURRENT_DATE) AS days_until_expiry
    </sql>

    <select id="selectById" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM agent_license al
        LEFT JOIN agent a        ON a.agent_id = al.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        WHERE al.license_id = #{licenseId}
    </select>

    <select id="selectList" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM agent_license al
        LEFT JOIN agent a        ON a.agent_id = al.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        <where>
            <if test="agentId != null">AND al.agent_id = #{agentId}</if>
            <if test="licenseType != null and licenseType != ''">AND al.license_type = #{licenseType}</if>
            <if test="status != null and status != ''">AND al.status = #{status}</if>
        </where>
        ORDER BY al.expires_date ASC, al.agent_id
    </select>

    <!--
        만료 임박 조회: CURRENT_DATE + days 이내에 만료되는 ACTIVE 자격증.
        인덱스 idx_al_expires 활용.
    -->
    <select id="selectExpiringWithin" resultMap="RespMap">
        SELECT <include refid="joinCols"/>
        FROM agent_license al
        LEFT JOIN agent a        ON a.agent_id = al.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        WHERE al.status = 'ACTIVE'
          AND al.expires_date &lt;= (CURRENT_DATE + #{days})
          AND al.expires_date &gt;= CURRENT_DATE
        ORDER BY al.expires_date ASC
    </select>

    <insert id="insert" useGeneratedKeys="true" keyProperty="licenseId">
        INSERT INTO agent_license
            (agent_id, license_type, license_no, issued_date, expires_date,
             renewed_from, status, created_by)
        VALUES
            (#{agentId}, #{licenseType}, #{licenseNo}, #{issuedDate}, #{expiresDate},
             #{renewedFrom}, COALESCE(#{status}, 'ACTIVE'), #{createdBy})
    </insert>

    <update id="update">
        UPDATE agent_license
           SET license_no    = #{licenseNo},
               issued_date   = #{issuedDate},
               expires_date  = #{expiresDate},
               updated_at    = NOW()
         WHERE license_id = #{licenseId}
    </update>

    <update id="updateStatus">
        UPDATE agent_license
           SET status = #{status}, updated_at = NOW()
         WHERE license_id = #{licenseId}
    </update>

    <delete id="delete">
        DELETE FROM agent_license WHERE license_id = #{licenseId}
    </delete>

</mapper>
