<?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.installment.InstallmentPlanMapper">

    <!-- ==================== ResultMap ==================== -->

    <!-- installment_plan 테이블 1:1 매핑 (CUD용) -->
    <resultMap id="VOMap" type="com.ga.core.vo.installment.InstallmentPlanVO">
        <id  property="planId"           column="plan_id"/>
        <result property="contractId"        column="contract_id"/>
        <result property="planYear"          column="plan_year"/>
        <result property="planMonth"         column="plan_month"/>
        <result property="settleMonth"       column="settle_month"/>
        <result property="expectedAmount"    column="expected_amount"/>
        <result property="paidAmount"        column="paid_amount"/>
        <result property="paidAt"            column="paid_at"/>
        <result property="paymentId"         column="payment_id"/>
        <result property="status"            column="status"/>
        <result property="createdAt"         column="created_at"/>
        <result property="createdBy"         column="created_by"/>
        <result property="updatedAt"         column="updated_at"/>
        <result property="updatedBy"         column="updated_by"/>
    </resultMap>

    <!-- 조인 결과 포함 API 응답용 -->
    <resultMap id="RespMap" type="com.ga.core.vo.installment.InstallmentPlanResp">
        <id  property="planId"           column="plan_id"/>
        <result property="contractId"        column="contract_id"/>
        <result property="policyNo"          column="policy_no"/>
        <result property="productName"       column="product_name"/>
        <result property="agentId"           column="agent_id"/>
        <result property="agentName"         column="agent_name"/>
        <result property="planYear"          column="plan_year"/>
        <result property="planMonth"         column="plan_month"/>
        <result property="settleMonth"       column="settle_month"/>
        <result property="expectedAmount"    column="expected_amount"/>
        <result property="paidAmount"        column="paid_amount"/>
        <result property="paidAt"            column="paid_at"/>
        <result property="paymentId"         column="payment_id"/>
        <result property="status"            column="status"/>
        <result property="statusName"        column="status_name"/>
    </resultMap>

    <!-- ==================== 공통 SELECT 컬럼 ==================== -->

    <sql id="selectRespCols">
        p.plan_id,
        p.contract_id,
        c.policy_no,
        pr.product_name,
        c.agent_id,
        a.agent_name,
        p.plan_year,
        p.plan_month,
        p.settle_month,
        p.expected_amount,
        p.paid_amount,
        p.paid_at,
        p.payment_id,
        p.status,
        (SELECT cc.code_name FROM common_code cc
         WHERE cc.group_code = 'INSTALLMENT_STATUS' AND cc.code = p.status) AS status_name
    </sql>

    <!-- ==================== SELECT ==================== -->

    <!-- 목록 조회 (검색 조건 + 페이징) -->
    <select id="selectList" parameterType="com.ga.core.vo.installment.InstallmentPlanSearchParam"
            resultMap="RespMap">
        SELECT <include refid="selectRespCols"/>
        FROM installment_plan p
        JOIN contract c  ON c.contract_id = p.contract_id
        JOIN product  pr ON pr.product_id = c.product_id
        JOIN agent    a  ON a.agent_id = c.agent_id
        <where>
            <if test="contractId != null">
                AND p.contract_id = #{contractId}
            </if>
            <if test="settleMonth != null and settleMonth != ''">
                AND p.settle_month = #{settleMonth}
            </if>
            <if test="status != null and status != ''">
                AND p.status = #{status}
            </if>
            <if test="planYear != null">
                AND p.plan_year = #{planYear}
            </if>
        </where>
        ORDER BY p.contract_id, p.plan_year, p.plan_month
    </select>

    <!-- 단건 조회 (Resp: 조인 포함) -->
    <select id="selectDetailById" resultMap="RespMap">
        SELECT <include refid="selectRespCols"/>
        FROM installment_plan p
        JOIN contract c  ON c.contract_id = p.contract_id
        JOIN product  pr ON pr.product_id = c.product_id
        JOIN agent    a  ON a.agent_id = c.agent_id
        WHERE p.plan_id = #{planId}
    </select>

    <!-- 단건 조회 (VO: CUD용) -->
    <select id="selectById" resultMap="VOMap">
        SELECT plan_id, contract_id, plan_year, plan_month, settle_month,
               expected_amount, paid_amount, paid_at, payment_id, status,
               created_at, created_by, updated_at, updated_by
        FROM installment_plan
        WHERE plan_id = #{planId}
    </select>

    <!-- 계약의 전체 분급 계획 조회 (계약 상세 화면용, 연차/월 순 정렬) -->
    <select id="selectByContract" resultMap="VOMap">
        SELECT plan_id, contract_id, plan_year, plan_month, settle_month,
               expected_amount, paid_amount, paid_at, payment_id, status,
               created_at, created_by, updated_at, updated_by
        FROM installment_plan
        WHERE contract_id = #{contractId}
        ORDER BY plan_year, plan_month
    </select>

    <!-- 정산월의 SCHEDULED 분급 계획 조회 (배치 지급 처리용) -->
    <select id="selectScheduledByMonth" resultMap="VOMap">
        SELECT plan_id, contract_id, plan_year, plan_month, settle_month,
               expected_amount, paid_amount, paid_at, payment_id, status,
               created_at, created_by, updated_at, updated_by
        FROM installment_plan
        WHERE settle_month = #{settleMonth}
          AND status = 'SCHEDULED'
        ORDER BY contract_id, plan_year, plan_month
    </select>

    <!-- ==================== INSERT ==================== -->

    <insert id="insert" parameterType="com.ga.core.vo.installment.InstallmentPlanVO"
            useGeneratedKeys="true" keyProperty="planId">
        INSERT INTO installment_plan (
            contract_id, plan_year, plan_month, settle_month,
            expected_amount, paid_amount, status,
            created_at, created_by
        ) VALUES (
            #{contractId}, #{planYear}, #{planMonth}, #{settleMonth},
            #{expectedAmount}, COALESCE(#{paidAmount}, 0),
            COALESCE(#{status}, 'SCHEDULED'),
            NOW(), #{createdBy}
        )
    </insert>

    <!-- 7년치 분급 계획 일괄 생성 (계약 등록 시 배치 처리) -->
    <insert id="insertBatch">
        INSERT INTO installment_plan (
            contract_id, plan_year, plan_month, settle_month,
            expected_amount, paid_amount, status,
            created_at, created_by
        ) VALUES
        <foreach collection="list" item="p" separator=",">
            (#{p.contractId}, #{p.planYear}, #{p.planMonth}, #{p.settleMonth},
             #{p.expectedAmount}, COALESCE(#{p.paidAmount}, 0),
             COALESCE(#{p.status}, 'SCHEDULED'),
             NOW(), #{p.createdBy})
        </foreach>
    </insert>

    <!-- ==================== UPDATE ==================== -->

    <update id="update" parameterType="com.ga.core.vo.installment.InstallmentPlanVO">
        UPDATE installment_plan
        <set>
            <if test="settleMonth != null and settleMonth != ''">settle_month = #{settleMonth},</if>
            <if test="expectedAmount != null">expected_amount = #{expectedAmount},</if>
            <if test="paidAmount != null">paid_amount = #{paidAmount},</if>
            <if test="paidAt != null">paid_at = #{paidAt},</if>
            <if test="paymentId != null">payment_id = #{paymentId},</if>
            <if test="status != null and status != ''">status = #{status},</if>
            updated_at = NOW(),
            updated_by = #{updatedBy}
        </set>
        WHERE plan_id = #{planId}
    </update>

    <!-- 상태만 변경 (PAID / DEFERRED / CANCELLED) -->
    <update id="updateStatus">
        UPDATE installment_plan
        SET status = #{status}, updated_at = NOW()
        WHERE plan_id = #{planId}
    </update>

    <!-- 계약 삭제 시 연관 분급 계획 일괄 삭제 -->
    <delete id="deleteByContract">
        DELETE FROM installment_plan WHERE contract_id = #{contractId}
    </delete>

</mapper>
