<?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.zioinfo.esn.mapper.StoreMapper">

  <resultMap id="storeMap" type="com.zioinfo.esn.domain.StoreVo">
    <id property="id"            column="id"/>
    <result property="tenantCode"    column="tenant_code"/>
    <result property="storeCode"     column="store_code"/>
    <result property="storeName"     column="store_name"/>
    <result property="address"       column="address"/>
    <result property="phone"         column="phone"/>
    <result property="storeGroupId"  column="store_group_id"/>
    <result property="storeGroupName" column="group_name"/>
    <result property="managerName"   column="manager_name"/>
    <result property="active"        column="is_active"/>
    <result property="createdAt"     column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="storeMap">
    SELECT s.id, s.tenant_code, s.store_code, s.store_name, s.address, s.phone,
           s.store_group_id, g.group_name, s.manager_name, s.is_active, s.created_at
    FROM esn_store s
    LEFT JOIN esn_store_group g ON s.store_group_id = g.id
    <where>
      <if test="tenantCode != null and tenantCode != ''">AND s.tenant_code = #{tenantCode}</if>
      <if test="storeGroupId != null">AND s.store_group_id = #{storeGroupId}</if>
      <if test="keyword != null and keyword != ''">
        AND (s.store_name ILIKE '%' || #{keyword} || '%' OR s.store_code ILIKE '%' || #{keyword} || '%')
      </if>
    </where>
    ORDER BY s.store_code
  </select>

  <select id="findById" resultMap="storeMap">
    SELECT s.id, s.tenant_code, s.store_code, s.store_name, s.address, s.phone,
           s.store_group_id, g.group_name, s.manager_name, s.is_active, s.created_at
    FROM esn_store s
    LEFT JOIN esn_store_group g ON s.store_group_id = g.id
    WHERE s.id = #{id}
  </select>

  <select id="findByCode" resultMap="storeMap">
    SELECT s.id, s.tenant_code, s.store_code, s.store_name, s.address, s.phone,
           s.store_group_id, g.group_name, s.manager_name, s.is_active, s.created_at
    FROM esn_store s
    LEFT JOIN esn_store_group g ON s.store_group_id = g.id
    WHERE s.store_code = #{storeCode} AND s.tenant_code = #{tenantCode}
  </select>

  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO esn_store (tenant_code, store_code, store_name, address, phone,
                            store_group_id, manager_name, is_active)
    VALUES (#{tenantCode}, #{storeCode}, #{storeName}, #{address}, #{phone},
            #{storeGroupId}, #{managerName}, #{active})
  </insert>

  <update id="update">
    UPDATE esn_store SET
      store_name    = #{storeName},
      address       = #{address},
      phone         = #{phone},
      store_group_id = #{storeGroupId},
      manager_name  = #{managerName},
      is_active     = #{active}
    WHERE id = #{id}
  </update>

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

  <select id="countActive" resultType="long">
    SELECT COUNT(*) FROM esn_store
    WHERE is_active = true
    <if test="tenantCode != null and tenantCode != ''">AND tenant_code = #{tenantCode}</if>
  </select>

  <select id="countAll" resultType="long">
    SELECT COUNT(*) FROM esn_store
    <where>
      <if test="tenantCode != null and tenantCode != ''">AND tenant_code = #{tenantCode}</if>
    </where>
  </select>

</mapper>
