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

  <!-- 공통 컬럼 (jsonb → text 캐스팅) -->
  <sql id="templateColumns">
    template_code, template_name, template_name_eng, description, category,
    icon_name, sort_order, is_active, is_public, company_code,
    created_by, updated_by, CREATED_DATE, UPDATED_DATE,
    layout_config::text AS layout_config,
    default_size::text  AS default_size,
    preview_image
  </sql>

  <!-- 공통 WHERE 조건 (company_code 포함) -->
  <sql id="templateWhere">
    WHERE 1=1
    <if test='active != null and active != "all"'>
      AND is_active = #{active}
    </if>
    <if test='category != null and category != "" and category != "all"'>
      AND category = #{category}
    </if>
    <if test="search != null and search != ''">
      AND (template_name     ILIKE '%' || #{search} || '%'
        OR template_name_eng ILIKE '%' || #{search} || '%'
        OR description       ILIKE '%' || #{search} || '%')
    </if>
    AND (is_public = 'Y' OR (company_code = #{company_code} OR company_code = '*'))
  </sql>

  <!-- 템플릿 목록 조회 -->
  <select id="getTemplateStandardList" parameterType="map" resultType="map">
    SELECT
    <include refid="templateColumns"/>

    FROM template_standards

    <include refid="templateWhere"/>

    ORDER BY sort_order ASC, template_name ASC
    <if test="limit != null">
      LIMIT #{limit} OFFSET #{offset}
    </if>
  </select>

  <!-- 템플릿 총 건수 -->
  <select id="getTemplateStandardListCnt" parameterType="map" resultType="int">
    SELECT COUNT(*)

    FROM template_standards

    <include refid="templateWhere"/>
  </select>

  <!-- 템플릿 단건 조회 -->
  <select id="getTemplateStandardInfo" parameterType="map" resultType="map">
    SELECT
    <include refid="templateColumns"/>

    FROM template_standards

    WHERE template_code = #{template_code}
  </select>

  <!-- 템플릿 생성 -->
  <insert id="insertTemplateStandard" parameterType="map">
    INSERT INTO template_standards (
      template_code, template_name, template_name_eng, description, category,
      icon_name, default_size, layout_config, preview_image,
      sort_order, is_active, is_public, company_code,
      created_by, updated_by, CREATED_DATE, UPDATED_DATE
    ) VALUES (
      #{template_code}, #{template_name}, #{template_name_eng}, #{description}, #{category},
      #{icon_name},
      #{default_size}::jsonb,
      #{layout_config}::jsonb,
      #{preview_image},
      #{sort_order}, #{is_active}, #{is_public}, #{company_code},
      #{created_by}, #{updated_by}, NOW(), NOW()
    )
  </insert>

  <!-- 템플릿 수정 (동적 SET) -->
  <update id="updateTemplateStandard" parameterType="map">
    UPDATE template_standards
    <set>
      UPDATED_DATE = NOW(),
      <if test="template_name != null">template_name = #{template_name},</if>
      <if test="template_name_eng != null">template_name_eng = #{template_name_eng},</if>
      <if test="description != null">description = #{description},</if>
      <if test="category != null">category = #{category},</if>
      <if test="icon_name != null">icon_name = #{icon_name},</if>
      <if test="default_size != null">default_size = #{default_size}::jsonb,</if>
      <if test="layout_config != null">layout_config = #{layout_config}::jsonb,</if>
      <if test="preview_image != null">preview_image = #{preview_image},</if>
      <if test="sort_order != null">sort_order = #{sort_order},</if>
      <if test="is_active != null">is_active = #{is_active},</if>
      <if test="is_public != null">is_public = #{is_public},</if>
      <if test="updated_by != null">updated_by = #{updated_by},</if>
    </set>

    WHERE template_code = #{template_code}
  </update>

  <!-- 템플릿 삭제 -->
  <delete id="deleteTemplateStandard" parameterType="map">
    DELETE FROM template_standards

    WHERE template_code = #{template_code}
  </delete>

  <!-- 정렬 순서 단건 업데이트 -->
  <update id="updateTemplateStandardSortOrder" parameterType="map">
    UPDATE template_standards
    SET sort_order = #{sort_order}, UPDATED_DATE = NOW()

    WHERE template_code = #{template_code}
  </update>

  <!-- 카테고리 목록 (DISTINCT) -->
  <select id="getTemplateStandardCategoryList" parameterType="map" resultType="map">
    SELECT DISTINCT category

    FROM template_standards

    WHERE (is_public = 'Y' OR (company_code = #{company_code} OR company_code = '*'))
      AND is_active = 'Y'
      AND category IS NOT NULL

    ORDER BY category ASC
  </select>

</mapper>
