<?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.signage.mapper.SignDeployMapper">

  <resultMap id="signDeployMap" type="com.zioinfo.signage.domain.SignDeployVo">
    <id     property="id" column="id"/>
    <result property="tenantCode" column="tenant_code"/>
    <result property="storeCode" column="store_code"/>
    <result property="deployType" column="deploy_type"/>
    <result property="subType" column="sub_type"/>
    <result property="code" column="code"/>
    <result property="fileName" column="file_name"/>
    <result property="fileSize" column="file_size"/>
    <result property="md5" column="md5"/>
    <result property="filePath" column="file_path"/>
    <result property="status" column="status"/>
    <result property="pollIntervalSec" column="poll_interval_sec"/>
    <result property="lastPolledAt" column="last_polled_at"/>
    <result property="deployedAt" column="deployed_at"/>
    <result property="createdAt" column="created_at"/>
  </resultMap>

  <select id="findAll" resultMap="signDeployMap">
    SELECT id, tenant_code, store_code, deploy_type, sub_type, code, file_name, file_size, md5, file_path, status, poll_interval_sec, last_polled_at, deployed_at, created_at
    FROM signage_sign_deploy
    <where>
      <if test="tenantCode != null and tenantCode != ''">AND tenant_code = #{tenantCode}</if>
      <if test="status != null and status != ''">AND status = #{status}</if>
      <if test="keyword != null and keyword != ''">AND file_name ILIKE '%' || #{keyword} || '%'</if>
    </where>
    ORDER BY created_at DESC
  </select>

  <select id="findById" resultMap="signDeployMap">
    SELECT id, tenant_code, store_code, deploy_type, sub_type, code, file_name, file_size, md5, file_path, status, poll_interval_sec, last_polled_at, deployed_at, created_at FROM signage_sign_deploy WHERE id = #{id}
  </select>

  <select id="findLatestForStore" resultMap="signDeployMap">
    SELECT id, tenant_code, store_code, deploy_type, sub_type, code, file_name, file_size, md5, file_path, status, poll_interval_sec, last_polled_at, deployed_at, created_at
    FROM signage_sign_deploy
    WHERE store_code = #{storeCode} AND status IN ('PENDING','SENT')
    ORDER BY created_at DESC
    LIMIT 1
  </select>

  <update id="markPolled">
    UPDATE signage_sign_deploy SET last_polled_at = NOW(),
      status = CASE WHEN status = 'PENDING' THEN 'SENT' ELSE status END
    WHERE id = #{id}
  </update>

  <update id="markApplied">
    UPDATE signage_sign_deploy SET status = 'APPLIED', deployed_at = NOW()
    WHERE id = #{id}
  </update>

  <insert id="insert" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO signage_sign_deploy (tenant_code, store_code, deploy_type, sub_type, code, file_name, file_size, md5, file_path, status, poll_interval_sec)
    VALUES (#{tenantCode}, #{storeCode}, #{deployType}, #{subType}, #{code}, #{fileName}, #{fileSize}, #{md5}, #{filePath}, #{status}, #{pollIntervalSec})
  </insert>

  <update id="update">
    UPDATE signage_sign_deploy SET
      tenant_code = #{tenantCode},
      store_code = #{storeCode},
      deploy_type = #{deployType},
      sub_type = #{subType},
      code = #{code},
      file_name = #{fileName},
      file_size = #{fileSize},
      md5 = #{md5},
      file_path = #{filePath},
      status = #{status},
      poll_interval_sec = #{pollIntervalSec}
    WHERE id = #{id}
  </update>

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

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

</mapper>
