<?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="template">

  <select id="getTemplateList" parameterType="map" resultType="map">
    SELECT
        TEMPLATE_ID
        , NAME
        , CATEGORY
        , DESCRIPTION
        , PRIMARY_TABLE
        , VIEWS
        , STATUS
        , VERSION
        , CREATED_BY
        , CREATED_DATE
        , UPDATED_BY
        , UPDATED_DATE
    FROM TEMPLATES
    WHERE 1=1
      AND IS_ACTIVE != 'D'
    <include refid="common.companyCodeFilter"/>
    <if test='keyword != null and keyword != ""'>
      AND (UPPER(NAME) LIKE '%' || UPPER(#{keyword}) || '%'
        OR UPPER(PRIMARY_TABLE) LIKE '%' || UPPER(#{keyword}) || '%')
    </if>
    <if test='status != null and status != ""'>
      AND STATUS = #{status}
    </if>
    <if test='category != null and category != ""'>
      AND CATEGORY = #{category}
    </if>
    <include refid="common.dynamicOrderBy"/>
    <include refid="common.pagination"/>
  </select>

  <select id="getTemplateListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)
    FROM TEMPLATES
    WHERE 1=1
      AND IS_ACTIVE != 'D'
    <include refid="common.companyCodeFilter"/>
    <if test='keyword != null and keyword != ""'>
      AND (UPPER(NAME) LIKE '%' || UPPER(#{keyword}) || '%'
        OR UPPER(PRIMARY_TABLE) LIKE '%' || UPPER(#{keyword}) || '%')
    </if>
    <if test='status != null and status != ""'>
      AND STATUS = #{status}
    </if>
    <if test='category != null and category != ""'>
      AND CATEGORY = #{category}
    </if>
  </select>

  <select id="getTemplateInfo" parameterType="map" resultType="map">
    SELECT
        TEMPLATE_ID
        , NAME
        , CATEGORY
        , DESCRIPTION
        , PRIMARY_TABLE
        , FIELDS
        , VIEWS
        , CONNECTIONS
        , COMPANY_CODE
        , VERSION
        , STATUS
        , CREATED_BY
        , CREATED_DATE
        , UPDATED_BY
        , UPDATED_DATE
    FROM TEMPLATES
    WHERE TEMPLATE_ID = #{template_id}
      AND IS_ACTIVE != 'D'
  </select>

  <insert id="insertTemplate" parameterType="map">
    INSERT INTO TEMPLATES (
        TEMPLATE_ID
        , NAME
        , CATEGORY
        , DESCRIPTION
        , PRIMARY_TABLE
        , FIELDS
        , VIEWS
        , CONNECTIONS
        , COMPANY_CODE
        , VERSION
        , STATUS
        , CREATED_BY
        , CREATED_DATE
        , UPDATED_BY
        , UPDATED_DATE
    ) VALUES (
        #{template_id}
        , #{name}
        , #{category}
        , #{description}
        , #{primary_table}
        , #{fields}::jsonb
        , #{views}::jsonb
        , #{connections}::jsonb
        , #{company_code}
        , 1
        , 'draft'
        , #{user_id}
        , NOW()
        , #{user_id}
        , NOW()
    )
  </insert>

  <update id="updateTemplate" parameterType="map">
    UPDATE TEMPLATES
    SET
        NAME = #{name}
        , CATEGORY = #{category}
        , DESCRIPTION = #{description}
        , PRIMARY_TABLE = #{primary_table}
        , FIELDS = #{fields}::jsonb
        , VIEWS = #{views}::jsonb
        , CONNECTIONS = #{connections}::jsonb
        , VERSION = VERSION + 1
        , UPDATED_BY = #{user_id}
        , UPDATED_DATE = NOW()
    WHERE TEMPLATE_ID = #{template_id}
      AND IS_ACTIVE != 'D'
  </update>

  <update id="publishTemplate" parameterType="map">
    UPDATE TEMPLATES
    SET
        STATUS = 'published'
        , UPDATED_BY = #{user_id}
        , UPDATED_DATE = NOW()
    WHERE TEMPLATE_ID = #{template_id}
      AND IS_ACTIVE != 'D'
  </update>

  <update id="deleteTemplate" parameterType="map">
    UPDATE TEMPLATES
    SET
        IS_ACTIVE = 'D'
        , UPDATED_BY = #{user_id}
        , UPDATED_DATE = NOW()
    WHERE TEMPLATE_ID = #{template_id}
  </update>

</mapper>
