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

  <sql id="ruleColumns">
    rule_id          AS rule_id,
    rule_name        AS rule_name,
    description,
    separator,
    reset_period     AS reset_period,
    current_sequence AS current_sequence,
    table_name       AS table_name,
    column_name      AS column_name,
    company_code     AS company_code,
    category_column  AS category_column,
    category_value_id AS category_value_id,
    created_by       AS created_by,
    created_at       AS created_at,
    updated_at       AS updated_at
  </sql>

  <sql id="partColumns">
    id,
    part_order         AS "order",
    part_type          AS part_type,
    generation_method  AS generation_method,
    auto_config        AS auto_config,
    manual_config      AS manual_config
  </sql>

  <!-- ===================== numbering_rules CRUD ===================== -->

  <select id="getRuleList" parameterType="map" resultType="map">
    SELECT <include refid="ruleColumns"/>

    FROM numbering_rules

    WHERE 1=1
    <choose>
      <when test="company_code == &quot;*&quot;"><!-- 최고관리자: 전체 --></when>
      <otherwise>AND (company_code = #{company_code} OR company_code = '*')</otherwise>
    </choose>

    ORDER BY created_at DESC
  </select>

  <select id="getRuleById" parameterType="map" resultType="map">
    SELECT <include refid="ruleColumns"/>

    FROM numbering_rules

    WHERE rule_id = #{rule_id}
    <choose>
      <when test="company_code == &quot;*&quot;"><!-- 최고관리자: 전체 --></when>
      <otherwise>AND (company_code = #{company_code} OR company_code = '*')</otherwise>
    </choose>
  </select>

  <insert id="insertRule" parameterType="map">
    INSERT INTO numbering_rules (
      rule_id, rule_name, description, separator, reset_period,
      current_sequence, table_name, column_name, company_code,
      category_column, category_value_id, created_by, created_at, updated_at
    ) VALUES (
      #{rule_id},
      #{rule_name},
      #{description,       jdbcType=VARCHAR},
      #{separator,         jdbcType=VARCHAR},
      #{reset_period,       jdbcType=VARCHAR},
      #{current_sequence,   jdbcType=VARCHAR},
      #{table_name,         jdbcType=VARCHAR},
      #{column_name,        jdbcType=VARCHAR},
      #{company_code},
      #{category_column,    jdbcType=VARCHAR},
      #{category_value_id,   jdbcType=VARCHAR},
      #{created_by,         jdbcType=VARCHAR},
      NOW(), NOW()
    )
  </insert>

  <update id="updateRule" parameterType="map">
    UPDATE numbering_rules
    SET
      rule_name         = COALESCE(#{rule_name,         jdbcType=VARCHAR}, rule_name),
      description       = COALESCE(#{description,      jdbcType=VARCHAR}, description),
      separator         = COALESCE(#{separator,        jdbcType=VARCHAR}, separator),
      reset_period      = COALESCE(#{reset_period,      jdbcType=VARCHAR}, reset_period),
      table_name        = COALESCE(#{table_name,        jdbcType=VARCHAR}, table_name),
      column_name       = COALESCE(#{column_name,       jdbcType=VARCHAR}, column_name),
      category_column   = COALESCE(#{category_column,   jdbcType=VARCHAR}, category_column),
      category_value_id = COALESCE(#{category_value_id,  jdbcType=VARCHAR}, category_value_id),
      updated_at = NOW()

    WHERE rule_id = #{rule_id}
      AND (company_code = #{company_code} OR company_code = '*')
  </update>

  <delete id="deleteRule" parameterType="map">
    DELETE FROM numbering_rules

    WHERE rule_id = #{rule_id}
      AND (company_code = #{company_code} OR company_code = '*')
  </delete>

  <!-- ===================== numbering_rule_parts ===================== -->

  <select id="getRulePartsByRuleId" parameterType="map" resultType="map">
    SELECT <include refid="partColumns"/>

    FROM numbering_rule_parts

    WHERE rule_id = #{rule_id}
    <choose>
      <when test="company_code == &quot;*&quot;"><!-- 최고관리자: 전체 --></when>
      <otherwise>AND (company_code = #{company_code} OR company_code = '*')</otherwise>
    </choose>

    ORDER BY part_order
  </select>

  <insert id="insertRulePart" parameterType="map">
    INSERT INTO numbering_rule_parts (
      rule_id, part_order, part_type, generation_method,
      auto_config, manual_config, company_code, created_at
    ) VALUES (
      #{rule_id},
      #{order},
      #{part_type},
      #{generation_method},
      #{auto_config}::jsonb,
      #{manual_config}::jsonb,
      #{company_code},
      NOW()
    )
  </insert>

  <delete id="deleteRulePartsByRuleId" parameterType="map">
    DELETE FROM numbering_rule_parts

    WHERE rule_id = #{rule_id}
      AND (company_code = #{company_code} OR company_code = '*')
  </delete>

  <!-- ===================== numbering_rule_sequences ===================== -->

  <select id="getSequenceForPrefix" parameterType="map" resultType="map">
    SELECT current_sequence AS current_sequence

    FROM numbering_rule_sequences

    WHERE rule_id    = #{rule_id}
      AND (company_code = #{company_code} OR company_code = '*')
      AND prefix_key   = #{prefix_key}
  </select>

  <delete id="deleteSequencesByRuleId" parameterType="map">
    DELETE FROM numbering_rule_sequences

    WHERE rule_id    = #{rule_id}
      AND (company_code = #{company_code} OR company_code = '*')
  </delete>

  <update id="updateCurrentSequenceInRule" parameterType="map">
    UPDATE numbering_rules
    SET current_sequence = GREATEST(COALESCE(current_sequence, '0'), #{current_sequence}),
        updated_at = NOW()

    WHERE rule_id    = #{rule_id}
      AND (company_code = #{company_code} OR company_code = '*')
  </update>

  <!-- admin 전용: GREATEST 없이 직접 SET. 임의 값 (0 포함) 으로 내릴 수 있음 -->
  <update id="setCurrentSequenceInRule" parameterType="map">
    UPDATE numbering_rules
    SET current_sequence = #{current_sequence},
        updated_at = NOW()

    WHERE rule_id    = #{rule_id}
      AND (company_code = #{company_code} OR company_code = '*')
  </update>

  <!-- ===================== available rules ===================== -->

  <select id="getAvailableRulesForMenu" parameterType="map" resultType="map">
    SELECT <include refid="ruleColumns"/>

    FROM numbering_rules

    WHERE 1=1
    <choose>
      <when test="company_code == &quot;*&quot;"><!-- 최고관리자: 전체 --></when>
      <otherwise>AND (company_code = #{company_code} OR company_code = '*')</otherwise>
    </choose>

    ORDER BY created_at DESC
  </select>

  <select id="getAvailableRulesForScreen" parameterType="map" resultType="map">
    SELECT <include refid="ruleColumns"/>

    FROM numbering_rules

    WHERE 1=1
    <choose>
      <when test="company_code == &quot;*&quot;"><!-- 최고관리자: 전체 --></when>
      <otherwise>AND (company_code = #{company_code} OR company_code = '*')</otherwise>
    </choose>
    <if test="table_name != null and table_name != ''">
      AND table_name = #{table_name}
    </if>

    ORDER BY created_at DESC
  </select>

  <select id="getRuleByColumn" parameterType="map" resultType="map">
    SELECT
      r.rule_id          AS rule_id,
      r.rule_name        AS rule_name,
      r.description,
      r.separator,
      r.reset_period     AS reset_period,
      r.current_sequence AS current_sequence,
      r.table_name       AS table_name,
      r.column_name      AS column_name,
      r.company_code     AS company_code,
      r.category_column  AS category_column,
      r.category_value_id AS category_value_id,
      cv.value_label     AS category_value_label,
      r.created_by       AS created_by,
      r.created_at       AS created_at,
      r.updated_at       AS updated_at

    FROM numbering_rules r

    LEFT JOIN category_values cv ON r.category_value_id = cv.value_id

    WHERE (r.company_code  = #{company_code} OR r.company_code = '*')
      AND r.table_name    = #{table_name}
      AND r.column_name   = #{column_name}
      AND r.category_value_id IS NULL
    LIMIT 1
  </select>

  <select id="getRuleByColumnFallback" parameterType="map" resultType="map">
    SELECT
      r.rule_id          AS rule_id,
      r.rule_name        AS rule_name,
      r.description,
      r.separator,
      r.reset_period     AS reset_period,
      r.current_sequence AS current_sequence,
      r.table_name       AS table_name,
      r.column_name      AS column_name,
      r.company_code     AS company_code,
      r.category_column  AS category_column,
      r.category_value_id AS category_value_id,
      cv.value_label     AS category_value_label,
      r.created_by       AS created_by,
      r.created_at       AS created_at,
      r.updated_at       AS updated_at

    FROM numbering_rules r

    LEFT JOIN category_values cv ON r.category_value_id = cv.value_id

    WHERE (r.company_code  = #{company_code} OR r.company_code = '*')
      AND r.table_name    = #{table_name}
      AND (r.column_name IS NULL OR r.column_name = '')
      AND r.category_value_id IS NULL

    ORDER BY r.updated_at DESC
    LIMIT 1
  </select>

  <update id="updateRuleColumnName" parameterType="map">
    UPDATE numbering_rules
    SET column_name = #{column_name}

    WHERE rule_id    = #{rule_id}
      AND (company_code = #{company_code} OR company_code = '*')
  </update>

  <!-- ===================== copy-for-company ===================== -->

  <select id="getRulesForCopy" parameterType="map" resultType="map">
    SELECT <include refid="ruleColumns"/>

    FROM numbering_rules

    WHERE (company_code = #{company_code} OR company_code = '*')

    ORDER BY created_at
  </select>

  <select id="getRulePartsForCopy" parameterType="map" resultType="map">
    SELECT <include refid="partColumns"/>

    FROM numbering_rule_parts

    WHERE rule_id    = #{rule_id}
      AND (company_code = #{company_code} OR company_code = '*')

    ORDER BY part_order
  </select>

</mapper>
