<?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.product.ContractMapper">

    <resultMap id="ContractVOMap" type="com.ga.core.vo.product.ContractVO"/>
    <resultMap id="ContractRespMap" type="com.ga.core.vo.product.ContractResp"/>

    <sql id="contractCols">
        ct.contract_id, ct.agent_id, a.agent_name, o.org_name,
        ct.product_id, p.product_code, p.product_name,
        c.company_code, c.company_name, p.insurance_type,
        ct.policy_no, ct.contractor_name, ct.insured_name, ct.premium,
        ct.pay_cycle,
        (SELECT cc.code_name FROM common_code cc
          WHERE cc.group_code='PAY_CYCLE' AND cc.code=ct.pay_cycle) AS pay_cycle_name,
        ct.pay_period, ct.coverage_period,
        ct.status,
        (SELECT cc.code_name FROM common_code cc
          WHERE cc.group_code='CONTRACT_STATUS' AND cc.code=ct.status) AS status_name,
        ct.contract_date, ct.effective_date, ct.lapse_date,
        ct.sales_channel,
        (SELECT cc.code_name FROM common_code cc
          WHERE cc.group_code='SALES_CHANNEL' AND cc.code=ct.sales_channel) AS sales_channel_name
    </sql>

    <select id="selectList" resultMap="ContractRespMap">
        SELECT <include refid="contractCols"/>
        FROM contract ct
        JOIN agent a            ON a.agent_id = ct.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        JOIN product p          ON p.product_id = ct.product_id
        JOIN insurance_company c ON c.company_id = p.company_id
        <where>
            <if test="agentId != null">AND ct.agent_id = #{agentId}</if>
            <if test="productId != null">AND ct.product_id = #{productId}</if>
            <if test="companyId != null">AND p.company_id = #{companyId}</if>
            <if test="insuranceType != null and insuranceType != ''">AND p.insurance_type = #{insuranceType}</if>
            <if test="policyNo != null and policyNo != ''">AND ct.policy_no = #{policyNo}</if>
            <if test="status != null and status != ''">AND ct.status = #{status}</if>
            <if test="searchKeyword != null and searchKeyword != ''">
                AND (ct.contractor_name LIKE CONCAT('%', #{searchKeyword}, '%')
                  OR ct.insured_name    LIKE CONCAT('%', #{searchKeyword}, '%')
                  OR ct.policy_no = #{searchKeyword})
            </if>
            <if test="startDate != null and startDate != ''">
                AND ct.contract_date &gt;= #{startDate}::date
            </if>
            <if test="endDate != null and endDate != ''">
                AND ct.contract_date &lt;= #{endDate}::date
            </if>
        </where>
        ORDER BY ct.contract_date DESC, ct.contract_id DESC
    </select>

    <select id="selectDetailById" resultMap="ContractRespMap">
        SELECT <include refid="contractCols"/>
        FROM contract ct
        JOIN agent a            ON a.agent_id = ct.agent_id
        LEFT JOIN organization o ON o.org_id = a.org_id
        JOIN product p          ON p.product_id = ct.product_id
        JOIN insurance_company c ON c.company_id = p.company_id
        WHERE ct.contract_id = #{contractId}
    </select>

    <select id="selectById" resultMap="ContractVOMap">
        SELECT * FROM contract WHERE contract_id = #{contractId}
    </select>

    <select id="existsByPolicyNo" resultType="int">
        SELECT COUNT(*) FROM contract WHERE policy_no = #{policyNo}
    </select>

    <!--
        증권번호로 계약 조회 (P10 환수자료 업로드 시 agent/contract 해소용).
        contract.policy_no 는 UNIQUE NOT NULL(V2).
        contractId + agentId 만 필요하므로 ContractVOMap 재사용.
        미존재 시 null 반환.
    -->
    <select id="selectByPolicyNo" resultMap="ContractVOMap">
        SELECT contract_id, agent_id, policy_no, contract_date, status
        FROM contract
        WHERE policy_no = #{policyNo}
        LIMIT 1
    </select>

    <insert id="insert" parameterType="com.ga.core.vo.product.ContractVO" useGeneratedKeys="true" keyProperty="contractId">
        INSERT INTO contract (agent_id, product_id, policy_no, contractor_name, insured_name,
                              premium, pay_cycle, pay_period, coverage_period, status,
                              contract_date, effective_date, sales_channel, created_at)
        VALUES (#{agentId}, #{productId}, #{policyNo}, #{contractorName}, #{insuredName},
                #{premium}, #{payCycle}, #{payPeriod}, #{coveragePeriod},
                COALESCE(#{status},'ACTIVE'),
                #{contractDate}, #{effectiveDate}, #{salesChannel}, #{createdAt})
    </insert>

    <update id="update" parameterType="com.ga.core.vo.product.ContractVO">
        UPDATE contract SET
            agent_id = #{agentId},
            product_id = #{productId},
            contractor_name = #{contractorName},
            insured_name = #{insuredName},
            premium = #{premium},
            pay_cycle = #{payCycle},
            pay_period = #{payPeriod},
            coverage_period = #{coveragePeriod},
            status = #{status},
            effective_date = #{effectiveDate},
            lapse_date = #{lapseDate},
            revival_date = #{revivalDate},
            sales_channel = #{salesChannel},
            updated_at = #{updatedAt}
        WHERE contract_id = #{contractId}
    </update>

    <update id="updateStatus">
        UPDATE contract SET status = #{status}, updated_at = NOW() WHERE contract_id = #{contractId}
    </update>

    <select id="countByAgent" resultType="int">
        SELECT COUNT(*) FROM contract WHERE agent_id = #{agentId}
        <if test="status != null and status != ''">AND status = #{status}</if>
    </select>

</mapper>
