<?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.ProductMapper">

    <resultMap id="ProductVOMap" type="com.ga.core.vo.product.ProductVO"/>
    <resultMap id="ProductRespMap" type="com.ga.core.vo.product.ProductResp"/>

    <select id="selectList" resultMap="ProductRespMap">
        SELECT p.product_id, p.company_id, c.company_code, c.company_name,
               p.product_code, p.product_name, p.insurance_type,
               (SELECT cc.code_name FROM common_code cc
                 WHERE cc.group_code='INSURANCE_TYPE' AND cc.code=p.insurance_type) AS insurance_type_name,
               p.product_group, p.pay_period_type, p.is_active, p.launch_date, p.end_date
        FROM product p
        JOIN insurance_company c ON c.company_id = p.company_id
        <where>
            <if test="companyId != null">AND p.company_id = #{companyId}</if>
            <if test="insuranceType != null and insuranceType != ''">AND p.insurance_type = #{insuranceType}</if>
            <if test="keyword != null and keyword != ''">
                AND (p.product_name LIKE CONCAT('%', #{keyword}, '%')
                  OR p.product_code = #{keyword})
            </if>
            <if test="isActive != null and isActive != ''">AND p.is_active = #{isActive}</if>
        </where>
        ORDER BY p.product_id DESC
    </select>

    <select id="selectDetailById" resultMap="ProductRespMap">
        SELECT p.product_id, p.company_id, c.company_code, c.company_name,
               p.product_code, p.product_name, p.insurance_type,
               p.product_group, p.pay_period_type, p.is_active, p.launch_date, p.end_date
        FROM product p
        JOIN insurance_company c ON c.company_id = p.company_id
        WHERE p.product_id = #{productId}
    </select>

    <select id="selectById" resultMap="ProductVOMap">
        SELECT * FROM product WHERE product_id = #{productId}
    </select>

    <select id="existsByCode" resultType="int">
        SELECT COUNT(*) FROM product WHERE company_id = #{companyId} AND product_code = #{productCode}
    </select>

    <insert id="insert" parameterType="com.ga.core.vo.product.ProductVO" useGeneratedKeys="true" keyProperty="productId">
        INSERT INTO product (company_id, product_code, product_name, insurance_type,
                             product_group, pay_period_type, is_active, launch_date, end_date)
        VALUES (#{companyId}, #{productCode}, #{productName}, #{insuranceType},
                #{productGroup}, #{payPeriodType}, COALESCE(#{isActive},'Y'),
                #{launchDate}, #{endDate})
    </insert>

    <update id="update" parameterType="com.ga.core.vo.product.ProductVO">
        UPDATE product SET
            product_name = #{productName},
            insurance_type = #{insuranceType},
            product_group = #{productGroup},
            pay_period_type = #{payPeriodType},
            is_active = #{isActive},
            launch_date = #{launchDate},
            end_date = #{endDate}
        WHERE product_id = #{productId}
    </update>

    <delete id="deleteById">DELETE FROM product WHERE product_id = #{productId}</delete>

</mapper>
