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

  <resultMap id="posMap" type="com.zioinfo.esn.domain.PosCvtVo">
    <id property="id"            column="id"/>
    <result property="tenantCode"    column="tenant_code"/>
    <result property="storeId"       column="store_id"/>
    <result property="storeName"     column="store_name"/>
    <result property="posCode"       column="pos_code"/>
    <result property="productCode"   column="product_code"/>
    <result property="productName"   column="product_name"/>
    <result property="price"         column="price"/>
    <result property="salePrice"     column="sale_price"/>
    <result property="currency"      column="currency"/>
    <result property="status"        column="status"/>
    <result property="errorMessage"  column="error_message"/>
    <result property="processedAt"   column="processed_at"/>
    <result property="createdAt"     column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="posMap">
    SELECT p.id, p.tenant_code, p.store_id, s.store_name, p.pos_code,
           p.product_code, p.product_name, p.price, p.sale_price, p.currency,
           p.status, p.error_message, p.processed_at, p.created_at
    FROM esn_pos_cvt p
    LEFT JOIN esn_store s ON p.store_id = s.id
    <where>
      <if test="tenantCode != null and tenantCode != ''">AND p.tenant_code = #{tenantCode}</if>
      <if test="storeId != null">AND p.store_id = #{storeId}</if>
      <if test="status != null and status != ''">AND p.status = #{status}</if>
      <if test="keyword != null and keyword != ''">
        AND (p.product_code ILIKE '%' || #{keyword} || '%' OR p.product_name ILIKE '%' || #{keyword} || '%')
      </if>
    </where>
    ORDER BY p.created_at DESC LIMIT 500
  </select>

  <select id="findById" resultMap="posMap">
    SELECT p.id, p.tenant_code, p.store_id, s.store_name, p.pos_code,
           p.product_code, p.product_name, p.price, p.sale_price, p.currency,
           p.status, p.error_message, p.processed_at, p.created_at
    FROM esn_pos_cvt p
    LEFT JOIN esn_store s ON p.store_id = s.id
    WHERE p.id = #{id}
  </select>

  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO esn_pos_cvt (tenant_code, store_id, pos_code, product_code, product_name,
                              price, sale_price, currency, status)
    VALUES (#{tenantCode}, #{storeId}, #{posCode}, #{productCode}, #{productName},
            #{price}, #{salePrice}, #{currency}, 'PENDING')
  </insert>

  <update id="updateStatus">
    UPDATE esn_pos_cvt SET
      status        = #{status},
      error_message = #{errorMessage},
      processed_at  = CASE WHEN #{status} = 'PROCESSED' THEN NOW() ELSE processed_at END
    WHERE id = #{id}
  </update>

  <select id="countPending" resultType="long">
    SELECT COUNT(*) FROM esn_pos_cvt WHERE status = 'PENDING'
    <if test="tenantCode != null and tenantCode != ''">AND tenant_code = #{tenantCode}</if>
  </select>

  <select id="countProcessedToday" resultType="long">
    SELECT COUNT(*) FROM esn_pos_cvt
    WHERE status = 'PROCESSED' AND processed_at::date = CURRENT_DATE
    <if test="tenantCode != null and tenantCode != ''">AND tenant_code = #{tenantCode}</if>
  </select>

</mapper>
