<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zioinfo.cms.member.mapper.MemberMapper">

  <resultMap id="memberMap" type="com.zioinfo.cms.member.CmsMember">
    <id property="id" column="id"/>
    <result property="memberCode" column="member_code"/>
    <result property="name" column="name"/>
    <result property="emailEnc" column="email_enc"/>
    <result property="phoneEnc" column="phone_enc"/>
    <result property="segment" column="segment"/>
    <result property="status" column="status"/>
    <result property="crmCustomerId" column="crm_customer_id"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="memberMap">
    SELECT * FROM cms_member
    <where>
      <if test="segment != null and segment != ''">AND segment = #{segment}</if>
      <if test="status != null and status != ''">AND status = #{status}</if>
      <if test="keyword != null and keyword != ''">AND (name ILIKE '%'||#{keyword}||'%' OR member_code ILIKE '%'||#{keyword}||'%')</if>
    </where>
    ORDER BY id DESC
  </select>

  <select id="findById" resultMap="memberMap">SELECT * FROM cms_member WHERE id = #{id}</select>

  <insert id="insert" parameterType="com.zioinfo.cms.member.CmsMember" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO cms_member (member_code, name, email_enc, phone_enc, segment, status, crm_customer_id)
    VALUES (#{memberCode}, #{name}, #{emailEnc}, #{phoneEnc}, #{segment}, #{status}, #{crmCustomerId})
  </insert>

  <update id="update" parameterType="com.zioinfo.cms.member.CmsMember">
    UPDATE cms_member SET name = #{name}, email_enc = #{emailEnc}, phone_enc = #{phoneEnc},
      segment = #{segment}, status = #{status}, crm_customer_id = #{crmCustomerId}
    WHERE id = #{id}
  </update>

  <delete id="delete">DELETE FROM cms_member WHERE id = #{id}</delete>

</mapper>
