/**
 * 채번 규칙 관리 서비스
 */

import { getPool } from "../database/db";
import { logger } from "../utils/logger";
import { getMenuAndChildObjids } from "./menuService";

interface NumberingRulePart {
  id?: number;
  order: number;
  partType: string;
  generationMethod: string;
  autoConfig?: any;
  manualConfig?: any;
  generatedValue?: string;
  separatorAfter?: string;
}

/**
 * 파트 배열에서 autoConfig.separatorAfter를 파트 레벨로 추출
 */
function extractSeparatorAfterFromParts(parts: any[]): any[] {
  return parts.map((part) => {
    if (part.autoConfig?.separatorAfter !== undefined) {
      part.separatorAfter = part.autoConfig.separatorAfter;
    }
    return part;
  });
}

/**
 * 파트별 개별 구분자를 사용하여 코드 결합
 * 마지막 파트의 separatorAfter는 무시됨
 */
function joinPartsWithSeparators(partValues: string[], sortedParts: any[], globalSeparator: string): string {
  let result = "";
  partValues.forEach((val, idx) => {
    result += val;
    if (idx < partValues.length - 1) {
      const sep = sortedParts[idx].separatorAfter ?? sortedParts[idx].autoConfig?.separatorAfter ?? globalSeparator;
      if (val || !result.endsWith(sep)) {
        result += sep;
      }
    }
  });
  return result;
}

interface NumberingRuleConfig {
  ruleId: string;
  ruleName: string;
  description?: string;
  parts: NumberingRulePart[];
  separator?: string;
  resetPeriod?: string;
  currentSequence?: number;
  tableName?: string;
  columnName?: string;
  companyCode?: string;
  menuObjid?: number;
  scopeType?: string;
  // 카테고리 조건
  categoryColumn?: string;
  categoryValueId?: number;
  categoryValueLabel?: string; // 조회 시 조인해서 가져옴
  createdAt?: string;
  updatedAt?: string;
  createdBy?: string;
}

class NumberingRuleService {
  /**
   * 순번(sequence) 파트를 제외한 나머지 파트 값들을 조합해 prefix_key 생성
   * 이 키가 같으면 같은 순번 계열, 다르면 001부터 재시작
   */
  private async buildPrefixKey(
    rule: NumberingRuleConfig,
    formData?: Record<string, any>,
    manualValues?: string[]
  ): Promise<string> {
    const sortedParts = [...rule.parts].sort((a: any, b: any) => a.order - b.order);
    const prefixParts: string[] = [];
    let manualIndex = 0;

    for (const part of sortedParts) {
      if (part.partType === "sequence") continue;

      if (part.generationMethod === "manual") {
        const manualValue = manualValues?.[manualIndex] || "";
        manualIndex++;
        if (manualValue) {
          prefixParts.push(manualValue);
        }
        continue;
      }

      const autoConfig = (part as any).autoConfig || {};

      switch (part.partType) {
        case "date": {
          const dateFormat = autoConfig.dateFormat || "YYYYMMDD";
          if (autoConfig.useColumnValue && autoConfig.sourceColumnName && formData) {
            const columnValue = formData[autoConfig.sourceColumnName];
            if (columnValue) {
              const dateValue = columnValue instanceof Date ? columnValue : new Date(columnValue);
              if (!isNaN(dateValue.getTime())) {
                prefixParts.push(this.formatDate(dateValue, dateFormat));
                break;
              }
            }
          }
          prefixParts.push(this.formatDate(new Date(), dateFormat));
          break;
        }

        case "text": {
          prefixParts.push(autoConfig.textValue || "TEXT");
          break;
        }

        case "number": {
          const length = autoConfig.numberLength || 3;
          const value = autoConfig.numberValue || 1;
          prefixParts.push(String(value).padStart(length, "0"));
          break;
        }

        case "category": {
          const categoryKey = autoConfig.categoryKey;
          const categoryMappings = autoConfig.categoryMappings || [];

          if (!categoryKey || !formData) {
            prefixParts.push("");
            break;
          }

          const columnName = categoryKey.includes(".")
            ? categoryKey.split(".")[1]
            : categoryKey;
          const selectedValue = formData[columnName];

          if (!selectedValue) {
            prefixParts.push("");
            break;
          }

          const selectedValueStr = String(selectedValue);
          let mapping = categoryMappings.find((m: any) => {
            if (m.categoryValueId?.toString() === selectedValueStr) return true;
            if (m.categoryValueCode && m.categoryValueCode === selectedValueStr) return true;
            if (m.categoryValueLabel === selectedValueStr) return true;
            return false;
          });

          if (!mapping) {
            try {
              const pool = getPool();
              const [catTableName, catColumnName] = categoryKey.includes(".")
                ? categoryKey.split(".")
                : [categoryKey, categoryKey];
              const cvResult = await pool.query(
                `SELECT value_id, value_label FROM category_values 
                 WHERE table_name = $1 AND column_name = $2 AND value_code = $3 LIMIT 1`,
                [catTableName, catColumnName, selectedValueStr]
              );
              if (cvResult.rows.length > 0) {
                const resolvedId = cvResult.rows[0].value_id;
                const resolvedLabel = cvResult.rows[0].value_label;
                mapping = categoryMappings.find((m: any) => {
                  if (m.categoryValueId?.toString() === String(resolvedId)) return true;
                  if (m.categoryValueLabel === resolvedLabel) return true;
                  return false;
                });
              }
            } catch { /* ignore */ }
          }

          prefixParts.push(mapping?.format || selectedValueStr);
          break;
        }

        case "reference": {
          const refColumn = autoConfig.referenceColumnName;
          if (refColumn && formData && formData[refColumn]) {
            prefixParts.push(String(formData[refColumn]));
          } else {
            prefixParts.push("");
          }
          break;
        }

        default:
          break;
      }
    }

    return prefixParts.join("|");
  }

  /**
   * prefix_key 기반으로 현재 순번 조회 (새 테이블 사용)
   */
  private async getSequenceForPrefix(
    client: any,
    ruleId: string,
    companyCode: string,
    prefixKey: string
  ): Promise<number> {
    const result = await client.query(
      `SELECT current_sequence FROM numbering_rule_sequences 
       WHERE rule_id = $1 AND company_code = $2 AND prefix_key = $3`,
      [ruleId, companyCode, prefixKey]
    );
    return result.rows.length > 0 ? result.rows[0].current_sequence : 0;
  }

  /**
   * prefix_key 기반으로 순번 증가 (UPSERT)
   */
  private async incrementSequenceForPrefix(
    client: any,
    ruleId: string,
    companyCode: string,
    prefixKey: string
  ): Promise<number> {
    const result = await client.query(
      `INSERT INTO numbering_rule_sequences (rule_id, company_code, prefix_key, current_sequence, last_allocated_at)
       VALUES ($1, $2, $3, 1, NOW())
       ON CONFLICT (rule_id, company_code, prefix_key)
       DO UPDATE SET current_sequence = numbering_rule_sequences.current_sequence + 1,
                     last_allocated_at = NOW()
       RETURNING current_sequence`,
      [ruleId, companyCode, prefixKey]
    );
    return result.rows[0].current_sequence;
  }

  /**
   * 카운터를 특정 값 이상으로 동기화 (GREATEST 사용)
   * 테이블 내 실제 최대값이 카운터보다 높을 때 카운터를 맞춰줌
   */
  private async setSequenceForPrefix(
    client: any,
    ruleId: string,
    companyCode: string,
    prefixKey: string,
    targetSequence: number
  ): Promise<number> {
    const result = await client.query(
      `INSERT INTO numbering_rule_sequences (rule_id, company_code, prefix_key, current_sequence, last_allocated_at)
       VALUES ($1, $2, $3, $4, NOW())
       ON CONFLICT (rule_id, company_code, prefix_key)
       DO UPDATE SET current_sequence = GREATEST(numbering_rule_sequences.current_sequence, $4),
                     last_allocated_at = NOW()
       RETURNING current_sequence`,
      [ruleId, companyCode, prefixKey, targetSequence]
    );
    return result.rows[0].current_sequence;
  }

  /**
   * 대상 테이블에서 해당 회사의 최대 시퀀스 번호를 조회
   * 코드의 prefix/suffix 패턴을 기반으로 sequence 부분만 추출하여 MAX 계산
   */
  private async getMaxSequenceFromTable(
    client: any,
    tableName: string,
    columnName: string,
    codePrefix: string,
    codeSuffix: string,
    seqLength: number,
    companyCode: string
  ): Promise<number> {
    try {
      // 테이블에 company_code 컬럼이 있는지 확인
      const colCheck = await client.query(
        `SELECT column_name FROM information_schema.columns
         WHERE table_name = $1 AND column_name = 'company_code'`,
        [tableName]
      );
      const hasCompanyCode = colCheck.rows.length > 0;

      // 대상 컬럼 존재 여부 확인
      const targetColCheck = await client.query(
        `SELECT column_name FROM information_schema.columns
         WHERE table_name = $1 AND column_name = $2`,
        [tableName, columnName]
      );
      if (targetColCheck.rows.length === 0) {
        logger.warn(`getMaxSequenceFromTable: 컬럼 없음 ${tableName}.${columnName}`);
        return 0;
      }

      // prefix와 suffix 사이의 sequence 부분을 추출하기 위한 위치 계산
      const prefixLen = codePrefix.length;
      const seqStart = prefixLen + 1; // SQL SUBSTRING은 1-based

      // LIKE 패턴: prefix + N자리 숫자 + suffix
      const likePattern = codePrefix + "%"  + codeSuffix;

      let sql: string;
      let params: any[];

      if (hasCompanyCode && companyCode !== "*") {
        sql = `
          SELECT MAX(
            CAST(SUBSTRING("${columnName}" FROM $1 FOR $2) AS INTEGER)
          ) as max_seq
          FROM "${tableName}"
          WHERE "${columnName}" LIKE $3
            AND company_code = $4
            AND LENGTH("${columnName}") = $5
            AND SUBSTRING("${columnName}" FROM $1 FOR $2) ~ '^[0-9]+$'
        `;
        params = [seqStart, seqLength, likePattern, companyCode, prefixLen + seqLength + codeSuffix.length];
      } else {
        sql = `
          SELECT MAX(
            CAST(SUBSTRING("${columnName}" FROM $1 FOR $2) AS INTEGER)
          ) as max_seq
          FROM "${tableName}"
          WHERE "${columnName}" LIKE $3
            AND LENGTH("${columnName}") = $4
            AND SUBSTRING("${columnName}" FROM $1 FOR $2) ~ '^[0-9]+$'
        `;
        params = [seqStart, seqLength, likePattern, prefixLen + seqLength + codeSuffix.length];
      }

      const result = await client.query(sql, params);
      const maxSeq = result.rows[0]?.max_seq ?? 0;

      logger.info("getMaxSequenceFromTable 결과", {
        tableName, columnName, codePrefix, codeSuffix,
        seqLength, companyCode, maxSeq,
      });

      return maxSeq;
    } catch (error: any) {
      logger.warn("getMaxSequenceFromTable 실패 (카운터 폴백)", {
        tableName, columnName, error: error.message,
      });
      return 0;
    }
  }

  /**
   * 규칙의 파트 구성에서 sequence 파트 전후의 prefix/suffix를 계산
   * allocateCode/previewCode에서 비-sequence 파트 값이 이미 계산된 후 호출
   */
  private buildCodePrefixSuffix(
    partValues: string[],
    sortedParts: any[],
    globalSeparator: string
  ): { prefix: string; suffix: string; seqIndex: number; seqLength: number } | null {
    const seqIndex = sortedParts.findIndex((p: any) => p.partType === "sequence");
    if (seqIndex === -1) return null;

    const seqLength = sortedParts[seqIndex].autoConfig?.sequenceLength || 3;

    // prefix: sequence 파트 이전의 모든 파트값 + 구분자
    let prefix = "";
    for (let i = 0; i < seqIndex; i++) {
      prefix += partValues[i];
      const sep = sortedParts[i].separatorAfter ?? sortedParts[i].autoConfig?.separatorAfter ?? globalSeparator;
      prefix += sep;
    }

    // suffix: sequence 파트 이후의 모든 파트값 + 구분자
    let suffix = "";
    for (let i = seqIndex + 1; i < partValues.length; i++) {
      const sep = sortedParts[i - 1].separatorAfter ?? sortedParts[i - 1].autoConfig?.separatorAfter ?? globalSeparator;
      if (i === seqIndex + 1) {
        // sequence 파트 바로 뒤 구분자
        const seqSep = sortedParts[seqIndex].separatorAfter ?? sortedParts[seqIndex].autoConfig?.separatorAfter ?? globalSeparator;
        suffix += seqSep;
      }
      suffix += partValues[i];
      if (i < partValues.length - 1) {
        const nextSep = sortedParts[i].separatorAfter ?? sortedParts[i].autoConfig?.separatorAfter ?? globalSeparator;
        suffix += nextSep;
      }
    }

    return { prefix, suffix, seqIndex, seqLength };
  }

  /**
   * 비-sequence 파트의 값을 계산하여 prefix/suffix 패턴 구축에 사용
   * sequence 파트는 빈 문자열로 반환 (이후 buildCodePrefixSuffix에서 처리)
   */
  private async computeNonSequenceValues(
    rule: NumberingRuleConfig,
    formData?: Record<string, any>
  ): Promise<string[]> {
    const sortedParts = [...rule.parts].sort((a: any, b: any) => a.order - b.order);
    return Promise.all(sortedParts.map(async (part: any) => {
      if (part.partType === "sequence") return "";
      if (part.generationMethod === "manual") return "";

      const autoConfig = part.autoConfig || {};

      switch (part.partType) {
        case "text":
          return autoConfig.textValue || "TEXT";

        case "number": {
          const length = autoConfig.numberLength || 3;
          const value = autoConfig.numberValue || 1;
          return String(value).padStart(length, "0");
        }

        case "date": {
          const dateFormat = autoConfig.dateFormat || "YYYYMMDD";
          if (autoConfig.useColumnValue && autoConfig.sourceColumnName && formData) {
            const columnValue = formData[autoConfig.sourceColumnName];
            if (columnValue) {
              const dateValue = columnValue instanceof Date ? columnValue : new Date(columnValue);
              if (!isNaN(dateValue.getTime())) {
                return this.formatDate(dateValue, dateFormat);
              }
            }
          }
          return this.formatDate(new Date(), dateFormat);
        }

        case "category": {
          const categoryKey = autoConfig.categoryKey;
          const categoryMappings = autoConfig.categoryMappings || [];
          if (!categoryKey || !formData) return "";

          const colName = categoryKey.includes(".") ? categoryKey.split(".")[1] : categoryKey;
          const selectedValue = formData[colName];
          if (!selectedValue) return "";

          const selectedValueStr = String(selectedValue);
          let mapping = categoryMappings.find((m: any) => {
            if (m.categoryValueId?.toString() === selectedValueStr) return true;
            if (m.categoryValueCode && m.categoryValueCode === selectedValueStr) return true;
            if (m.categoryValueLabel === selectedValueStr) return true;
            return false;
          });

          if (!mapping) {
            try {
              const pool = getPool();
              const [ct, cc] = categoryKey.includes(".") ? categoryKey.split(".") : [categoryKey, categoryKey];
              const cvResult = await pool.query(
                `SELECT value_id, value_label FROM category_values WHERE table_name = $1 AND column_name = $2 AND value_code = $3 LIMIT 1`,
                [ct, cc, selectedValueStr]
              );
              if (cvResult.rows.length > 0) {
                mapping = categoryMappings.find((m: any) => {
                  if (m.categoryValueId?.toString() === String(cvResult.rows[0].value_id)) return true;
                  if (m.categoryValueLabel === cvResult.rows[0].value_label) return true;
                  return false;
                });
              }
            } catch { /* ignore */ }
          }

          return mapping?.format || "";
        }

        case "reference": {
          const refColumn = autoConfig.referenceColumnName;
          if (refColumn && formData && formData[refColumn]) {
            return String(formData[refColumn]);
          }
          return "";
        }

        default:
          return "";
      }
    }));
  }

  /**
   * 대상 테이블 기반으로 실제 최대 시퀀스를 확인하고,
   * 카운터와 비교하여 더 높은 값 + 1을 반환
   */
  private async resolveNextSequence(
    client: any,
    rule: NumberingRuleConfig,
    companyCode: string,
    ruleId: string,
    prefixKey: string,
    formData?: Record<string, any>
  ): Promise<number> {
    // 1. 현재 저장된 카운터 조회
    const currentCounter = await this.getSequenceForPrefix(
      client, ruleId, companyCode, prefixKey
    );

    let baseSequence = currentCounter;

    // 2. 규칙에 tableName/columnName이 설정되어 있으면 대상 테이블에서 MAX 조회
    if (rule.tableName && rule.columnName) {
      try {
        const sortedParts = [...rule.parts].sort((a: any, b: any) => a.order - b.order);
        const patternValues = await this.computeNonSequenceValues(rule, formData);
        const psInfo = this.buildCodePrefixSuffix(patternValues, sortedParts, rule.separator || "");

        if (psInfo) {
          const maxFromTable = await this.getMaxSequenceFromTable(
            client, rule.tableName, rule.columnName,
            psInfo.prefix, psInfo.suffix, psInfo.seqLength, companyCode
          );

          if (maxFromTable > baseSequence) {
            logger.info("테이블 내 최대값이 카운터보다 높음 → 동기화", {
              ruleId, companyCode, currentCounter, maxFromTable,
            });
            baseSequence = maxFromTable;
          }
        }
      } catch (error: any) {
        logger.warn("테이블 기반 MAX 조회 실패, 카운터 기반 폴백", {
          ruleId, error: error.message,
        });
      }
    }

    // 3. 다음 시퀀스 = base + 1
    const nextSequence = baseSequence + 1;

    // 4. 카운터를 동기화 (GREATEST 사용)
    await this.setSequenceForPrefix(client, ruleId, companyCode, prefixKey, nextSequence);

    // 5. 호환성을 위해 numbering_rules.current_sequence도 업데이트
    await client.query(
      "UPDATE numbering_rules SET current_sequence = GREATEST(COALESCE(current_sequence, 0), $3) WHERE rule_id = $1 AND company_code = $2",
      [ruleId, companyCode, nextSequence]
    );

    logger.info("resolveNextSequence 완료", {
      ruleId, companyCode, prefixKey, currentCounter, baseSequence, nextSequence,
    });

    return nextSequence;
  }

  /**
   * 규칙 목록 조회 (전체)
   */
  async getRuleList(companyCode: string): Promise<NumberingRuleConfig[]> {
    try {
      logger.info("채번 규칙 목록 조회 시작", { companyCode });

      const pool = getPool();

      // 멀티테넌시: 최고 관리자만 company_code="*" 데이터를 볼 수 있음
      let query: string;
      let params: any[];

      if (companyCode === "*") {
        // 최고 관리자: 모든 회사 데이터 조회 가능
        query = `
          SELECT 
            rule_id AS "ruleId",
            rule_name AS "ruleName",
            description,
            separator,
            reset_period AS "resetPeriod",
            current_sequence AS "currentSequence",
            table_name AS "tableName",
            column_name AS "columnName",
            company_code AS "companyCode",
            category_column AS "categoryColumn",
            category_value_id AS "categoryValueId",
            created_at AS "createdAt",
            updated_at AS "updatedAt",
            created_by AS "createdBy"
          FROM numbering_rules
          ORDER BY created_at DESC
        `;
        params = [];
        logger.info("최고 관리자 전체 채번 규칙 조회");
      } else {
        // 일반 회사: 자신의 회사 데이터만 조회 (company_code="*" 제외)
        query = `
          SELECT 
            rule_id AS "ruleId",
            rule_name AS "ruleName",
            description,
            separator,
            reset_period AS "resetPeriod",
            current_sequence AS "currentSequence",
            table_name AS "tableName",
            column_name AS "columnName",
            company_code AS "companyCode",
            category_column AS "categoryColumn",
            category_value_id AS "categoryValueId",
            created_at AS "createdAt",
            updated_at AS "updatedAt",
            created_by AS "createdBy"
          FROM numbering_rules
          WHERE company_code = $1
          ORDER BY created_at DESC
        `;
        params = [companyCode];
        logger.info("회사별 채번 규칙 조회", { companyCode });
      }

      const result = await pool.query(query, params);

      // 각 규칙의 파트 정보 조회
      for (const rule of result.rows) {
        let partsQuery: string;
        let partsParams: any[];

        if (companyCode === "*") {
          // 최고 관리자: 모든 파트 조회
          partsQuery = `
            SELECT 
              id,
              part_order AS "order",
              part_type AS "partType",
              generation_method AS "generationMethod",
              auto_config AS "autoConfig",
              manual_config AS "manualConfig"
            FROM numbering_rule_parts
            WHERE rule_id = $1
            ORDER BY part_order
          `;
          partsParams = [rule.ruleId];
        } else {
          // 일반 회사: 자신의 파트만 조회
          partsQuery = `
            SELECT 
              id,
              part_order AS "order",
              part_type AS "partType",
              generation_method AS "generationMethod",
              auto_config AS "autoConfig",
              manual_config AS "manualConfig"
            FROM numbering_rule_parts
            WHERE rule_id = $1 AND company_code = $2
            ORDER BY part_order
          `;
          partsParams = [rule.ruleId, companyCode];
        }

        const partsResult = await pool.query(partsQuery, partsParams);
        rule.parts = extractSeparatorAfterFromParts(partsResult.rows);
      }

      logger.info(`채번 규칙 목록 조회 완료: ${result.rows.length}개`, {
        companyCode,
      });
      return result.rows;
    } catch (error: any) {
      logger.error(`채번 규칙 목록 조회 중 에러: ${error.message}`);
      throw error;
    }
  }

  /**
   * 현재 메뉴에서 사용 가능한 규칙 목록 조회 (메뉴 스코프)
   *
   * 메뉴 스코프 규칙:
   * - menuObjid가 제공되면 형제 메뉴의 채번 규칙 포함
   * - 우선순위: menu (형제 메뉴) > table > global
   */
  async getAvailableRulesForMenu(
    companyCode: string,
    menuObjid?: number
  ): Promise<NumberingRuleConfig[]> {
    let menuAndChildObjids: number[] = []; // catch 블록에서 접근 가능하도록 함수 최상단에 선언

    try {
      logger.info("메뉴별 사용 가능한 채번 규칙 조회 시작 (메뉴 스코프)", {
        companyCode,
        menuObjid,
      });

      const pool = getPool();

      // 1. 선택한 메뉴와 하위 메뉴 OBJID 조회 (형제 메뉴 제외)
      if (menuObjid) {
        menuAndChildObjids = await getMenuAndChildObjids(menuObjid);
        logger.info("선택한 메뉴 및 하위 메뉴 OBJID 목록", {
          menuObjid,
          menuAndChildObjids,
        });
      }

      // menuObjid가 없으면 global 규칙만 반환
      if (!menuObjid || menuAndChildObjids.length === 0) {
        let query: string;
        let params: any[];

        if (companyCode === "*") {
          // 최고 관리자: 모든 global 규칙 조회
          query = `
            SELECT 
              rule_id AS "ruleId",
              rule_name AS "ruleName",
              description,
              separator,
              reset_period AS "resetPeriod",
              current_sequence AS "currentSequence",
              table_name AS "tableName",
              column_name AS "columnName",
              company_code AS "companyCode",
              category_column AS "categoryColumn",
              category_value_id AS "categoryValueId",
              created_at AS "createdAt",
              updated_at AS "updatedAt",
              created_by AS "createdBy"
            FROM numbering_rules
            WHERE 1=1
            ORDER BY created_at DESC
          `;
          params = [];
        } else {
          // 일반 회사: 자신의 global 규칙만 조회 (company_code="*" 제외)
          query = `
            SELECT 
              rule_id AS "ruleId",
              rule_name AS "ruleName",
              description,
              separator,
              reset_period AS "resetPeriod",
              current_sequence AS "currentSequence",
              table_name AS "tableName",
              column_name AS "columnName",
              company_code AS "companyCode",
              category_column AS "categoryColumn",
              category_value_id AS "categoryValueId",
              created_at AS "createdAt",
              updated_at AS "updatedAt",
              created_by AS "createdBy"
            FROM numbering_rules
            WHERE company_code = $1             ORDER BY created_at DESC
          `;
          params = [companyCode];
        }

        const result = await pool.query(query, params);

        // 파트 정보 추가
        for (const rule of result.rows) {
          let partsQuery: string;
          let partsParams: any[];

          if (companyCode === "*") {
            partsQuery = `
              SELECT 
                id,
                part_order AS "order",
                part_type AS "partType",
                generation_method AS "generationMethod",
                auto_config AS "autoConfig",
                manual_config AS "manualConfig"
              FROM numbering_rule_parts
              WHERE rule_id = $1
              ORDER BY part_order
            `;
            partsParams = [rule.ruleId];
          } else {
            partsQuery = `
              SELECT 
                id,
                part_order AS "order",
                part_type AS "partType",
                generation_method AS "generationMethod",
                auto_config AS "autoConfig",
                manual_config AS "manualConfig"
              FROM numbering_rule_parts
              WHERE rule_id = $1 AND company_code = $2
              ORDER BY part_order
            `;
            partsParams = [rule.ruleId, companyCode];
          }

          const partsResult = await pool.query(partsQuery, partsParams);
          rule.parts = extractSeparatorAfterFromParts(partsResult.rows);
        }

        return result.rows;
      }

      // 2. 메뉴 스코프: 형제 메뉴의 채번 규칙 조회
      // 우선순위: menu (형제 메뉴) > table > global
      let query: string;
      let params: any[];

      if (companyCode === "*") {
        // 최고 관리자: 모든 규칙 조회
        query = `
          SELECT 
            rule_id AS "ruleId",
            rule_name AS "ruleName",
            description,
            separator,
            reset_period AS "resetPeriod",
            current_sequence AS "currentSequence",
            table_name AS "tableName",
            column_name AS "columnName",
            company_code AS "companyCode",
            category_column AS "categoryColumn",
            category_value_id AS "categoryValueId",
            created_at AS "createdAt",
            updated_at AS "updatedAt",
            created_by AS "createdBy"
          FROM numbering_rules
          ORDER BY created_at DESC
        `;
        params = [];
        logger.info("최고 관리자: 전체 채번 규칙 조회");
      } else {
        // 일반 회사: 자신의 규칙만 조회
        query = `
          SELECT 
            rule_id AS "ruleId",
            rule_name AS "ruleName",
            description,
            separator,
            reset_period AS "resetPeriod",
            current_sequence AS "currentSequence",
            table_name AS "tableName",
            column_name AS "columnName",
            company_code AS "companyCode",
            category_column AS "categoryColumn",
            category_value_id AS "categoryValueId",
            created_at AS "createdAt",
            updated_at AS "updatedAt",
            created_by AS "createdBy"
          FROM numbering_rules
          WHERE company_code = $1
          ORDER BY created_at DESC
        `;
        params = [companyCode];
        logger.info("회사별 채번 규칙 조회", { companyCode });
      }

      logger.info("🔍 채번 규칙 쿼리 실행", {
        queryPreview: query.substring(0, 200),
        paramsTypes: params.map((p) => typeof p),
        paramsValues: params,
      });

      const result = await pool.query(query, params);

      logger.debug("채번 규칙 쿼리 성공", { ruleCount: result.rows.length });

      // 파트 정보 추가
      for (const rule of result.rows) {
        try {
          let partsQuery: string;
          let partsParams: any[];

          if (companyCode === "*") {
            partsQuery = `
              SELECT 
                id,
                part_order AS "order",
                part_type AS "partType",
                generation_method AS "generationMethod",
                auto_config AS "autoConfig",
                manual_config AS "manualConfig"
              FROM numbering_rule_parts
              WHERE rule_id = $1
              ORDER BY part_order
            `;
            partsParams = [rule.ruleId];
          } else {
            partsQuery = `
              SELECT 
                id,
                part_order AS "order",
                part_type AS "partType",
                generation_method AS "generationMethod",
                auto_config AS "autoConfig",
                manual_config AS "manualConfig"
              FROM numbering_rule_parts
              WHERE rule_id = $1 AND company_code = $2
              ORDER BY part_order
            `;
            partsParams = [rule.ruleId, companyCode];
          }

          const partsResult = await pool.query(partsQuery, partsParams);
          rule.parts = extractSeparatorAfterFromParts(partsResult.rows);

          logger.info("✅ 규칙 파트 조회 성공", {
            ruleId: rule.ruleId,
            ruleName: rule.ruleName,
            partsCount: partsResult.rows.length,
          });
        } catch (partError: any) {
          logger.error("❌ 규칙 파트 조회 실패", {
            ruleId: rule.ruleId,
            ruleName: rule.ruleName,
            error: partError.message,
            errorCode: partError.code,
            errorStack: partError.stack,
          });
          throw partError;
        }
      }

      logger.info("메뉴별 사용 가능한 채번 규칙 조회 완료", {
        companyCode,
        menuObjid,
        menuAndChildCount: menuAndChildObjids.length,
        count: result.rowCount,
      });

      return result.rows;
    } catch (error: any) {
      logger.error("메뉴별 채번 규칙 조회 실패", {
        error: error.message,
        errorCode: error.code,
        errorStack: error.stack,
        companyCode,
        menuObjid,
        menuAndChildObjids: menuAndChildObjids || [],
      });
      throw error;
    }
  }

  /**
   * 화면용 채번 규칙 조회 (테이블 기반 필터링 - 간소화)
   * @param companyCode 회사 코드
   * @param tableName 화면의 테이블명
   * @returns 해당 테이블의 채번 규칙 목록
   */
  async getAvailableRulesForScreen(
    companyCode: string,
    tableName: string
  ): Promise<NumberingRuleConfig[]> {
    try {
      logger.info("화면용 채번 규칙 조회", {
        companyCode,
        tableName,
      });

      const pool = getPool();

      // 멀티테넌시: 최고 관리자 vs 일반 회사
      let query: string;
      let params: any[];

      if (companyCode === "*") {
        // 최고 관리자: 모든 회사의 규칙 조회 가능 (최고 관리자 전용 규칙 제외)
        query = `
          SELECT 
            rule_id AS "ruleId",
            rule_name AS "ruleName",
            description,
            separator,
            reset_period AS "resetPeriod",
            current_sequence AS "currentSequence",
            table_name AS "tableName",
            column_name AS "columnName",
            company_code AS "companyCode",
            category_column AS "categoryColumn",
            category_value_id AS "categoryValueId",
            created_at AS "createdAt",
            updated_at AS "updatedAt",
            created_by AS "createdBy"
          FROM numbering_rules
          WHERE company_code != '*'
            AND table_name = $1
          ORDER BY created_at DESC
        `;
        params = [tableName];
        logger.info("최고 관리자: 일반 회사 채번 규칙 조회");
      } else {
        // 일반 회사: 자신의 규칙만 조회
        query = `
          SELECT 
            rule_id AS "ruleId",
            rule_name AS "ruleName",
            description,
            separator,
            reset_period AS "resetPeriod",
            current_sequence AS "currentSequence",
            table_name AS "tableName",
            column_name AS "columnName",
            company_code AS "companyCode",
            category_column AS "categoryColumn",
            category_value_id AS "categoryValueId",
            created_at AS "createdAt",
            updated_at AS "updatedAt",
            created_by AS "createdBy"
          FROM numbering_rules
          WHERE company_code = $1
            AND table_name = $2
          ORDER BY created_at DESC
        `;
        params = [companyCode, tableName];
      }

      const result = await pool.query(query, params);

      // 각 규칙의 파트 정보 로드
      for (const rule of result.rows) {
        const partsQuery = `
          SELECT 
            id,
            part_order AS "order",
            part_type AS "partType",
            generation_method AS "generationMethod",
            auto_config AS "autoConfig",
            manual_config AS "manualConfig"
          FROM numbering_rule_parts
          WHERE rule_id = $1
            AND company_code = $2
          ORDER BY part_order
        `;

        const partsResult = await pool.query(partsQuery, [
          rule.ruleId,
          companyCode === "*" ? rule.companyCode : companyCode,
        ]);

        rule.parts = extractSeparatorAfterFromParts(partsResult.rows);
      }

      logger.info(`화면용 채번 규칙 조회 완료: ${result.rows.length}개`, {
        companyCode,
        tableName,
      });

      return result.rows;
    } catch (error: any) {
      logger.error("화면용 채번 규칙 조회 실패", error);
      throw error;
    }
  }

  /**
   * 특정 규칙 조회
   */
  async getRuleById(
    ruleId: string,
    companyCode: string
  ): Promise<NumberingRuleConfig | null> {
    const pool = getPool();

    // 멀티테넌시: 최고 관리자만 company_code="*" 데이터를 볼 수 있음
    let query: string;
    let params: any[];

    if (companyCode === "*") {
      // 최고 관리자: 모든 규칙 조회 가능
      query = `
        SELECT 
          rule_id AS "ruleId",
          rule_name AS "ruleName",
          description,
          separator,
          reset_period AS "resetPeriod",
          current_sequence AS "currentSequence",
          table_name AS "tableName",
          column_name AS "columnName",
          company_code AS "companyCode",
          category_column AS "categoryColumn",
          category_value_id AS "categoryValueId",
          created_at AS "createdAt",
          updated_at AS "updatedAt",
          created_by AS "createdBy"
        FROM numbering_rules
        WHERE rule_id = $1
      `;
      params = [ruleId];
    } else {
      // 일반 회사: 자신의 규칙만 조회
      query = `
        SELECT 
          rule_id AS "ruleId",
          rule_name AS "ruleName",
          description,
          separator,
          reset_period AS "resetPeriod",
          current_sequence AS "currentSequence",
          table_name AS "tableName",
          column_name AS "columnName",
          company_code AS "companyCode",
          category_column AS "categoryColumn",
          category_value_id AS "categoryValueId",
          created_at AS "createdAt",
          updated_at AS "updatedAt",
          created_by AS "createdBy"
        FROM numbering_rules
        WHERE rule_id = $1 AND company_code = $2
      `;
      params = [ruleId, companyCode];
    }

    const result = await pool.query(query, params);
    if (result.rowCount === 0) {
      return null;
    }

    const rule = result.rows[0];

    // 파트 정보 조회
    let partsQuery: string;
    let partsParams: any[];

    if (companyCode === "*") {
      partsQuery = `
        SELECT 
          id,
          part_order AS "order",
          part_type AS "partType",
          generation_method AS "generationMethod",
          auto_config AS "autoConfig",
          manual_config AS "manualConfig"
        FROM numbering_rule_parts
        WHERE rule_id = $1
        ORDER BY part_order
      `;
      partsParams = [ruleId];
    } else {
      partsQuery = `
        SELECT 
          id,
          part_order AS "order",
          part_type AS "partType",
          generation_method AS "generationMethod",
          auto_config AS "autoConfig",
          manual_config AS "manualConfig"
        FROM numbering_rule_parts
        WHERE rule_id = $1 AND company_code = $2
        ORDER BY part_order
      `;
      partsParams = [ruleId, companyCode];
    }

    const partsResult = await pool.query(partsQuery, partsParams);
    rule.parts = extractSeparatorAfterFromParts(partsResult.rows);

    return rule;
  }

  /**
   * 규칙 생성
   */
  async createRule(
    config: NumberingRuleConfig,
    companyCode: string,
    userId: string
  ): Promise<NumberingRuleConfig> {
    const pool = getPool();
    const client = await pool.connect();

    try {
      await client.query("BEGIN");

      // 마스터 삽입
      const insertRuleQuery = `
        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
        ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
        RETURNING 
          rule_id AS "ruleId",
          rule_name AS "ruleName",
          description,
          separator,
          reset_period AS "resetPeriod",
          current_sequence AS "currentSequence",
          table_name AS "tableName",
          column_name AS "columnName",
          company_code AS "companyCode",
          category_column AS "categoryColumn",
          category_value_id AS "categoryValueId",
          created_at AS "createdAt",
          updated_at AS "updatedAt",
          created_by AS "createdBy"
      `;

      const ruleResult = await client.query(insertRuleQuery, [
        config.ruleId,
        config.ruleName,
        config.description || null,
        config.separator || "-",
        config.resetPeriod || "none",
        config.currentSequence || 1,
        config.tableName || null,
        config.columnName || null,
        companyCode,
        config.categoryColumn || null,
        config.categoryValueId || null,
        userId,
      ]);

      // 파트 삽입
      const parts: NumberingRulePart[] = [];
      for (const part of config.parts) {
        const insertPartQuery = `
          INSERT INTO numbering_rule_parts (
            rule_id, part_order, part_type, generation_method,
            auto_config, manual_config, company_code
          ) VALUES ($1, $2, $3, $4, $5, $6, $7)
          RETURNING 
            id,
            part_order AS "order",
            part_type AS "partType",
            generation_method AS "generationMethod",
            auto_config AS "autoConfig",
            manual_config AS "manualConfig"
        `;

        // auto_config에 separatorAfter 포함
        const autoConfigWithSep = { ...(part.autoConfig || {}), separatorAfter: part.separatorAfter ?? "-" };
        
        const partResult = await client.query(insertPartQuery, [
          config.ruleId,
          part.order,
          part.partType,
          part.generationMethod,
          JSON.stringify(autoConfigWithSep),
          JSON.stringify(part.manualConfig || {}),
          companyCode,
        ]);

        const savedPart = partResult.rows[0];
        // autoConfig에서 separatorAfter를 추출하여 파트 레벨로 이동
        if (savedPart.autoConfig?.separatorAfter !== undefined) {
          savedPart.separatorAfter = savedPart.autoConfig.separatorAfter;
        }
        parts.push(savedPart);
      }

      await client.query("COMMIT");
      logger.info("채번 규칙 생성 완료", {
        ruleId: config.ruleId,
        companyCode,
      });
      return { ...ruleResult.rows[0], parts };
    } catch (error: any) {
      await client.query("ROLLBACK");
      logger.error("채번 규칙 생성 실패", { error: error.message });
      throw error;
    } finally {
      client.release();
    }
  }

  /**
   * 규칙 수정
   */
  async updateRule(
    ruleId: string,
    updates: Partial<NumberingRuleConfig>,
    companyCode: string
  ): Promise<NumberingRuleConfig> {
    const pool = getPool();
    const client = await pool.connect();

    try {
      await client.query("BEGIN");

      const updateRuleQuery = `
        UPDATE numbering_rules
        SET 
          rule_name = COALESCE($1, rule_name),
          description = COALESCE($2, description),
          separator = COALESCE($3, separator),
          reset_period = COALESCE($4, reset_period),
          table_name = COALESCE($5, table_name),
          column_name = COALESCE($6, column_name),
          category_column = COALESCE($7, category_column),
          category_value_id = COALESCE($8, category_value_id),
          updated_at = NOW()
        WHERE rule_id = $9 AND company_code = $10
        RETURNING 
          rule_id AS "ruleId",
          rule_name AS "ruleName",
          description,
          separator,
          reset_period AS "resetPeriod",
          current_sequence AS "currentSequence",
          table_name AS "tableName",
          column_name AS "columnName",
          company_code AS "companyCode",
          category_column AS "categoryColumn",
          category_value_id AS "categoryValueId",
          created_at AS "createdAt",
          updated_at AS "updatedAt",
          created_by AS "createdBy"
      `;

      const ruleResult = await client.query(updateRuleQuery, [
        updates.ruleName,
        updates.description,
        updates.separator,
        updates.resetPeriod,
        updates.tableName,
        updates.columnName,
        updates.categoryColumn,
        updates.categoryValueId,
        ruleId,
        companyCode,
      ]);

      if (ruleResult.rowCount === 0) {
        throw new Error("규칙을 찾을 수 없거나 권한이 없습니다");
      }

      // 파트 업데이트
      let parts: NumberingRulePart[] = [];
      if (updates.parts) {
        await client.query(
          "DELETE FROM numbering_rule_parts WHERE rule_id = $1 AND company_code = $2",
          [ruleId, companyCode]
        );

        for (const part of updates.parts) {
          const insertPartQuery = `
            INSERT INTO numbering_rule_parts (
              rule_id, part_order, part_type, generation_method,
              auto_config, manual_config, company_code
            ) VALUES ($1, $2, $3, $4, $5, $6, $7)
            RETURNING 
              id,
              part_order AS "order",
              part_type AS "partType",
              generation_method AS "generationMethod",
              auto_config AS "autoConfig",
              manual_config AS "manualConfig"
          `;

          const autoConfigWithSep = { ...(part.autoConfig || {}), separatorAfter: part.separatorAfter ?? "-" };
          
          const partResult = await client.query(insertPartQuery, [
            ruleId,
            part.order,
            part.partType,
            part.generationMethod,
            JSON.stringify(autoConfigWithSep),
            JSON.stringify(part.manualConfig || {}),
            companyCode,
          ]);

          const savedPart = partResult.rows[0];
          if (savedPart.autoConfig?.separatorAfter !== undefined) {
            savedPart.separatorAfter = savedPart.autoConfig.separatorAfter;
          }
          parts.push(savedPart);
        }
      }

      await client.query("COMMIT");
      logger.info("채번 규칙 수정 완료", { ruleId, companyCode });
      return { ...ruleResult.rows[0], parts };
    } catch (error: any) {
      await client.query("ROLLBACK");
      logger.error("채번 규칙 수정 실패", {
        ruleId,
        companyCode,
        error: error.message,
        stack: error.stack,
        updates,
      });
      throw error;
    } finally {
      client.release();
    }
  }

  /**
   * 규칙 삭제
   */
  async deleteRule(ruleId: string, companyCode: string): Promise<void> {
    const pool = getPool();
    const query = `
      DELETE FROM numbering_rules
      WHERE rule_id = $1 AND company_code = $2
    `;

    const result = await pool.query(query, [ruleId, companyCode]);

    if (result.rowCount === 0) {
      throw new Error("규칙을 찾을 수 없거나 권한이 없습니다");
    }

    logger.info("채번 규칙 삭제 완료", { ruleId, companyCode });
  }

  /**
   * 코드 미리보기 (순번 증가 없음)
   * @param ruleId 채번 규칙 ID
   * @param companyCode 회사 코드
   * @param formData 폼 데이터 (카테고리 기반 채번 시 사용)
   * @param manualInputValue 수동 입력 값 (접두어별 순번 조회용)
   */
  async previewCode(
    ruleId: string,
    companyCode: string,
    formData?: Record<string, any>,
    manualInputValue?: string
  ): Promise<string> {
    const rule = await this.getRuleById(ruleId, companyCode);
    if (!rule) throw new Error("규칙을 찾을 수 없습니다");

    // 수동 파트가 있는데 입력값이 없으면 레거시 공용 시퀀스 조회를 건너뜀
    const hasManualPart = rule.parts.some((p: any) => p.generationMethod === "manual");
    const skipSequenceLookup = hasManualPart && !manualInputValue;

    // prefix_key 기반 순번 조회 + 테이블 내 최대값과 비교
    const manualValues = manualInputValue ? [manualInputValue] : undefined;
    const prefixKey = await this.buildPrefixKey(rule, formData, manualValues);
    const pool = getPool();
    const currentSeq = skipSequenceLookup
      ? 0
      : await this.getSequenceForPrefix(pool, ruleId, companyCode, prefixKey);

    // 대상 테이블에서 실제 최대 시퀀스 조회
    let baseSeq = currentSeq;
    if (rule.tableName && rule.columnName) {
      try {
        const sortedPartsForPattern = [...rule.parts].sort((a: any, b: any) => a.order - b.order);
        const patternValues = await this.computeNonSequenceValues(rule, formData);
        const psInfo = this.buildCodePrefixSuffix(patternValues, sortedPartsForPattern, rule.separator || "");

        if (psInfo) {
          const maxFromTable = await this.getMaxSequenceFromTable(
            pool, rule.tableName, rule.columnName,
            psInfo.prefix, psInfo.suffix, psInfo.seqLength, companyCode
          );

          if (maxFromTable > baseSeq) {
            logger.info("미리보기: 테이블 내 최대값이 카운터보다 높음", {
              ruleId, companyCode, currentSeq, maxFromTable,
            });
            baseSeq = maxFromTable;
          }
        }
      } catch (error: any) {
        logger.warn("미리보기: 테이블 기반 MAX 조회 실패, 카운터 기반 폴백", {
          ruleId, error: error.message,
        });
      }
    }

    logger.info("미리보기: 순번 조회 완료", {
      ruleId, prefixKey, currentSeq, baseSeq, skipSequenceLookup,
    });

    const parts = await Promise.all(rule.parts
      .sort((a: any, b: any) => a.order - b.order)
      .map(async (part: any) => {
        if (part.generationMethod === "manual") {
          return "____";
        }

        const autoConfig = part.autoConfig || {};

        switch (part.partType) {
          case "sequence": {
            const length = autoConfig.sequenceLength || 3;
            const startFrom = autoConfig.startFrom || 1;
            const nextSequence = baseSeq + startFrom;
            return String(nextSequence).padStart(length, "0");
          }

          case "number": {
            // 숫자 (고정 자릿수)
            const length = autoConfig.numberLength || 3;
            const value = autoConfig.numberValue || 1;
            return String(value).padStart(length, "0");
          }

          case "date": {
            // 날짜 (다양한 날짜 형식)
            const dateFormat = autoConfig.dateFormat || "YYYYMMDD";

            // 컬럼 기준 생성인 경우 폼 데이터에서 날짜 추출
            if (
              autoConfig.useColumnValue &&
              autoConfig.sourceColumnName &&
              formData
            ) {
              const columnValue = formData[autoConfig.sourceColumnName];
              if (columnValue) {
                const dateValue =
                  columnValue instanceof Date
                    ? columnValue
                    : new Date(columnValue);

                if (!isNaN(dateValue.getTime())) {
                  return this.formatDate(dateValue, dateFormat);
                }
              }
            }

            return this.formatDate(new Date(), dateFormat);
          }

          case "text": {
            // 텍스트 (고정 문자열)
            return autoConfig.textValue || "TEXT";
          }

          case "category":
            return this.resolveCategoryFormat(autoConfig, formData);

          case "reference": {
            const refColumn = autoConfig.referenceColumnName;
            if (refColumn && formData && formData[refColumn]) {
              return String(formData[refColumn]);
            }
            return "REF";
          }

          default:
            logger.warn("알 수 없는 파트 타입", { partType: part.partType });
            return "";
        }
      }));

    const sortedRuleParts = rule.parts.sort((a: any, b: any) => a.order - b.order);
    const previewCode = joinPartsWithSeparators(parts, sortedRuleParts, rule.separator || "");
    logger.info("코드 미리보기 생성", {
      ruleId,
      previewCode,
      companyCode,
      hasFormData: !!formData,
    });
    return previewCode;
  }

  /**
   * 코드 할당 (저장 시점에 실제 순번 증가)
   * @param ruleId 채번 규칙 ID
   * @param companyCode 회사 코드
   * @param formData 폼 데이터 (날짜 컬럼 기준 생성 시 사용)
   * @param userInputCode 사용자가 편집한 최종 코드 (수동 입력 부분 추출용)
   */
  async allocateCode(
    ruleId: string,
    companyCode: string,
    formData?: Record<string, any>,
    userInputCode?: string
  ): Promise<string> {
    const pool = getPool();
    const client = await pool.connect();

    try {
      await client.query("BEGIN");

      const rule = await this.getRuleById(ruleId, companyCode);
      if (!rule) throw new Error("규칙을 찾을 수 없습니다");

      // 1단계: 수동 값 추출 (buildPrefixKey 전에 수행해야 prefix_key에 포함 가능)
      const manualParts = rule.parts.filter(
        (p: any) => p.generationMethod === "manual"
      );
      let extractedManualValues: string[] = [];

      if (manualParts.length > 0 && userInputCode) {
        extractedManualValues = await this.extractManualValuesFromInput(
          rule, userInputCode, formData
        );

        // 템플릿 파싱 실패 시 userInputCode 전체를 수동 값으로 사용 (수동 파트 1개인 경우만)
        if (extractedManualValues.length === 0 && manualParts.length === 1) {
          extractedManualValues = [userInputCode];
          logger.info("수동 값 추출 폴백: userInputCode 전체 사용", { userInputCode });
        }
      }

      // 2단계: prefix_key 빌드 (수동 값 포함)
      const prefixKey = await this.buildPrefixKey(rule, formData, extractedManualValues);
      const hasSequence = rule.parts.some((p: any) => p.partType === "sequence");

      // 3단계: 순번이 있으면 prefix_key 기반 UPSERT + 테이블 내 최대값 비교하여 다음 순번 결정
      let allocatedSequence = 0;
      if (hasSequence) {
        allocatedSequence = await this.resolveNextSequence(
          client, rule, companyCode, ruleId, prefixKey, formData
        );
      }

      logger.info("allocateCode: prefix_key + 테이블 기반 순번 할당", {
        ruleId, prefixKey, allocatedSequence, extractedManualValues,
      });

      let manualPartIndex = 0;
      const parts = await Promise.all(rule.parts
        .sort((a: any, b: any) => a.order - b.order)
        .map(async (part: any) => {
          if (part.generationMethod === "manual") {
            const manualValue = extractedManualValues[manualPartIndex] || "";
            manualPartIndex++;
            return manualValue;
          }

          const autoConfig = part.autoConfig || {};

          switch (part.partType) {
            case "sequence": {
              const length = autoConfig.sequenceLength || 3;
              const startFrom = autoConfig.startFrom || 1;
              const actualSequence = allocatedSequence + startFrom - 1;
              return String(actualSequence).padStart(length, "0");
            }

            case "number": {
              const length = autoConfig.numberLength || 3;
              const value = autoConfig.numberValue || 1;
              return String(value).padStart(length, "0");
            }

            case "date": {
              const dateFormat = autoConfig.dateFormat || "YYYYMMDD";

              if (
                autoConfig.useColumnValue &&
                autoConfig.sourceColumnName &&
                formData
              ) {
                const columnValue = formData[autoConfig.sourceColumnName];
                if (columnValue) {
                  const dateValue =
                    columnValue instanceof Date
                      ? columnValue
                      : new Date(columnValue);

                  if (!isNaN(dateValue.getTime())) {
                    return this.formatDate(dateValue, dateFormat);
                  }
                }
              }

              return this.formatDate(new Date(), dateFormat);
            }

            case "text": {
              return autoConfig.textValue || "TEXT";
            }

            case "category":
              return this.resolveCategoryFormat(autoConfig, formData);

            case "reference": {
              const refColumn = autoConfig.referenceColumnName;
              if (refColumn && formData && formData[refColumn]) {
                return String(formData[refColumn]);
              }
              return "";
            }

            default:
              return "";
          }
        }));

      const sortedPartsForAlloc = rule.parts.sort((a: any, b: any) => a.order - b.order);
      const allocatedCode = joinPartsWithSeparators(parts, sortedPartsForAlloc, rule.separator || "");

      await client.query("COMMIT");
      logger.info("코드 할당 완료", { ruleId, allocatedCode, companyCode });
      return allocatedCode;
    } catch (error: any) {
      await client.query("ROLLBACK");
      logger.error("코드 할당 실패", {
        ruleId,
        companyCode,
        error: error.message,
        stack: error.stack,
      });
      throw error;
    } finally {
      client.release();
    }
  }

  /**
   * @deprecated 기존 generateCode는 allocateCode를 사용하세요
   */
  async generateCode(ruleId: string, companyCode: string): Promise<string> {
    logger.warn(
      "generateCode는 deprecated 되었습니다. previewCode 또는 allocateCode를 사용하세요"
    );
    return this.allocateCode(ruleId, companyCode);
  }

  /**
   * 사용자 입력 코드에서 수동 파트 값을 추출
   * 템플릿 기반 파싱으로 수동 입력 위치("____")에 해당하는 값을 분리
   */
  private async extractManualValuesFromInput(
    rule: NumberingRuleConfig,
    userInputCode: string,
    formData?: Record<string, any>
  ): Promise<string[]> {
    const extractedValues: string[] = [];

    const previewParts = await Promise.all(rule.parts
      .sort((a: any, b: any) => a.order - b.order)
      .map(async (part: any) => {
        if (part.generationMethod === "manual") {
          return "____";
        }
        const autoConfig = part.autoConfig || {};
        switch (part.partType) {
          case "sequence": {
            const length = autoConfig.sequenceLength || 3;
            return "X".repeat(length);
          }
          case "text":
            return autoConfig.textValue || "";
          case "date":
            return "DATEPART";
          case "category":
            return this.resolveCategoryFormat(autoConfig, formData);
          case "reference": {
            const refColumn = autoConfig.referenceColumnName;
            if (refColumn && formData && formData[refColumn]) {
              return String(formData[refColumn]);
            }
            return "";
          }
          default:
            return "";
        }
      }));

    const sortedPartsForTemplate = rule.parts.sort((a: any, b: any) => a.order - b.order);
    const previewTemplate = joinPartsWithSeparators(previewParts, sortedPartsForTemplate, rule.separator || "");

    const templateParts = previewTemplate.split("____");
    if (templateParts.length > 1) {
      let remainingCode = userInputCode;
      for (let i = 0; i < templateParts.length - 1; i++) {
        const prefix = templateParts[i];
        const suffix = templateParts[i + 1];

        if (prefix && remainingCode.startsWith(prefix)) {
          remainingCode = remainingCode.slice(prefix.length);
        }

        if (suffix) {
          const suffixStart = suffix.replace(/X+|DATEPART/g, "");
          const manualEndIndex = suffixStart
            ? remainingCode.indexOf(suffixStart)
            : remainingCode.length;
          if (manualEndIndex > 0) {
            extractedValues.push(
              remainingCode.slice(0, manualEndIndex)
            );
            remainingCode = remainingCode.slice(manualEndIndex);
          }
        } else {
          extractedValues.push(remainingCode);
        }
      }
    }

    logger.info(
      `수동 입력 값 추출: userInputCode=${userInputCode}, previewTemplate=${previewTemplate}, extractedManualValues=${JSON.stringify(extractedValues)}`
    );

    return extractedValues;
  }

  /**
   * 카테고리 매핑에서 format 값을 해석
   * categoryKey + formData로 선택된 값을 찾고, 매핑 테이블에서 format 반환
   */
  private async resolveCategoryFormat(
    autoConfig: Record<string, any>,
    formData?: Record<string, any>
  ): Promise<string> {
    const categoryKey = autoConfig.categoryKey;
    const categoryMappings = autoConfig.categoryMappings || [];

    if (!categoryKey || !formData) return "";

    const columnName = categoryKey.includes(".")
      ? categoryKey.split(".")[1]
      : categoryKey;
    const selectedValue = formData[columnName];

    if (!selectedValue) return "";

    const selectedValueStr = String(selectedValue);
    let mapping = categoryMappings.find((m: any) => {
      if (m.categoryValueId?.toString() === selectedValueStr) return true;
      if (m.categoryValueCode && m.categoryValueCode === selectedValueStr) return true;
      if (m.categoryValueLabel === selectedValueStr) return true;
      return false;
    });

    // 매핑 못 찾으면 category_values에서 valueCode → valueId 역변환
    if (!mapping) {
      try {
        const pool = getPool();
        const [tableName, colName] = categoryKey.includes(".")
          ? categoryKey.split(".")
          : [categoryKey, categoryKey];
        const result = await pool.query(
          `SELECT value_id, value_label FROM category_values WHERE table_name = $1 AND column_name = $2 AND value_code = $3 LIMIT 1`,
          [tableName, colName, selectedValueStr]
        );
        if (result.rows.length > 0) {
          const resolvedId = result.rows[0].value_id;
          const resolvedLabel = result.rows[0].value_label;
          mapping = categoryMappings.find((m: any) => {
            if (m.categoryValueId?.toString() === String(resolvedId)) return true;
            if (m.categoryValueLabel === resolvedLabel) return true;
            return false;
          });
        }
      } catch { /* ignore */ }
    }

    return mapping?.format || "";
  }

  private formatDate(date: Date, format: string): string {
    const year = date.getFullYear();
    const month = String(date.getMonth() + 1).padStart(2, "0");
    const day = String(date.getDate()).padStart(2, "0");

    switch (format) {
      case "YYYY":
        return String(year);
      case "YY":
        return String(year).slice(-2);
      case "YYYYMM":
        return `${year}${month}`;
      case "YYMM":
        return `${String(year).slice(-2)}${month}`;
      case "YYYYMMDD":
        return `${year}${month}${day}`;
      case "YYMMDD":
        return `${String(year).slice(-2)}${month}${day}`;
      default:
        return `${year}${month}${day}`;
    }
  }

  async resetSequence(ruleId: string, companyCode: string): Promise<void> {
    const pool = getPool();
    // 새 테이블의 모든 prefix 순번 초기화
    await pool.query(
      "DELETE FROM numbering_rule_sequences WHERE rule_id = $1 AND company_code = $2",
      [ruleId, companyCode]
    );
    // 기존 테이블도 초기화 (호환성)
    await pool.query(
      "UPDATE numbering_rules SET current_sequence = 0, updated_at = NOW() WHERE rule_id = $1 AND company_code = $2",
      [ruleId, companyCode]
    );
    logger.info("시퀀스 초기화 완료 (prefix별 순번 포함)", { ruleId, companyCode });
  }

  /**
   * [테스트] 테스트 테이블에서 채번 규칙 목록 조회
   * numbering_rules 테이블 사용
   */
  async getRulesFromTest(
    companyCode: string,
    menuObjid?: number
  ): Promise<NumberingRuleConfig[]> {
    try {
      logger.info("[테스트] 채번 규칙 목록 조회 시작", {
        companyCode,
        menuObjid,
      });

      const pool = getPool();

      // 멀티테넌시: 최고 관리자 vs 일반 회사
      let query: string;
      let params: any[];

      if (companyCode === "*") {
        // 최고 관리자: 모든 규칙 조회
        query = `
          SELECT 
            rule_id AS "ruleId",
            rule_name AS "ruleName",
            description,
            separator,
            reset_period AS "resetPeriod",
            current_sequence AS "currentSequence",
            table_name AS "tableName",
            column_name AS "columnName",
            company_code AS "companyCode",
            category_column AS "categoryColumn",
            category_value_id AS "categoryValueId",
            created_at AS "createdAt",
            updated_at AS "updatedAt",
            created_by AS "createdBy"
          FROM numbering_rules
          ORDER BY created_at DESC
        `;
        params = [];
      } else {
        // 일반 회사: 자신의 규칙만 조회
        query = `
          SELECT 
            rule_id AS "ruleId",
            rule_name AS "ruleName",
            description,
            separator,
            reset_period AS "resetPeriod",
            current_sequence AS "currentSequence",
            table_name AS "tableName",
            column_name AS "columnName",
            company_code AS "companyCode",
            category_column AS "categoryColumn",
            category_value_id AS "categoryValueId",
            created_at AS "createdAt",
            updated_at AS "updatedAt",
            created_by AS "createdBy"
          FROM numbering_rules
          WHERE company_code = $1
          ORDER BY created_at DESC
        `;
        params = [companyCode];
      }

      const result = await pool.query(query, params);

      // 각 규칙의 파트 정보 조회
      for (const rule of result.rows) {
        const partsQuery = `
          SELECT 
            id,
            part_order AS "order",
            part_type AS "partType",
            generation_method AS "generationMethod",
            auto_config AS "autoConfig",
            manual_config AS "manualConfig"
          FROM numbering_rule_parts
          WHERE rule_id = $1 AND company_code = $2
          ORDER BY part_order
        `;
        const partsResult = await pool.query(partsQuery, [
          rule.ruleId,
          companyCode === "*" ? rule.companyCode : companyCode,
        ]);
        rule.parts = extractSeparatorAfterFromParts(partsResult.rows);
      }

      logger.info("[테스트] 채번 규칙 목록 조회 완료", {
        companyCode,
        menuObjid,
        count: result.rows.length,
      });

      return result.rows;
    } catch (error: any) {
      logger.error("[테스트] 채번 규칙 목록 조회 실패", {
        error: error.message,
        stack: error.stack,
      });
      throw error;
    }
  }

  /**
   * 테이블명 + 컬럼명 기반으로 채번규칙 조회
   * numbering_rules 테이블 사용
   */
  async getNumberingRuleByColumn(
    companyCode: string,
    tableName: string,
    columnName: string
  ): Promise<NumberingRuleConfig | null> {
    try {
      logger.info("테이블+컬럼 기반 채번 규칙 조회 시작 (테스트)", {
        companyCode,
        tableName,
        columnName,
      });

      const pool = getPool();
      const query = `
        SELECT
          r.rule_id AS "ruleId",
          r.rule_name AS "ruleName",
          r.description,
          r.separator,
          r.reset_period AS "resetPeriod",
          r.current_sequence AS "currentSequence",
          r.table_name AS "tableName",
          r.column_name AS "columnName",
          r.company_code AS "companyCode",
          r.category_column AS "categoryColumn",
          r.category_value_id AS "categoryValueId",
          cv.value_label AS "categoryValueLabel",
          r.created_at AS "createdAt",
          r.updated_at AS "updatedAt",
          r.created_by AS "createdBy"
        FROM numbering_rules r
        LEFT JOIN category_values cv ON r.category_value_id = cv.value_id
        WHERE r.company_code = $1
          AND r.table_name = $2
          AND r.column_name = $3
          AND r.category_value_id IS NULL
        LIMIT 1
      `;
      const params = [companyCode, tableName, columnName];

      let result = await pool.query(query, params);

      // fallback: column_name이 비어있는 레거시 규칙 검색
      if (result.rows.length === 0) {
        const fallbackQuery = `
          SELECT
            r.rule_id AS "ruleId",
            r.rule_name AS "ruleName",
            r.description,
            r.separator,
            r.reset_period AS "resetPeriod",
            r.current_sequence AS "currentSequence",
            r.table_name AS "tableName",
            r.column_name AS "columnName",
            r.company_code AS "companyCode",
            r.category_column AS "categoryColumn",
            r.category_value_id AS "categoryValueId",
            cv.value_label AS "categoryValueLabel",
            r.created_at AS "createdAt",
            r.updated_at AS "updatedAt",
            r.created_by AS "createdBy"
          FROM numbering_rules r
          LEFT JOIN category_values cv ON r.category_value_id = cv.value_id
          WHERE r.company_code = $1
            AND r.table_name = $2
            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
        `;
        result = await pool.query(fallbackQuery, [companyCode, tableName]);

        // 찾으면 column_name 자동 업데이트 (레거시 데이터 마이그레이션)
        if (result.rows.length > 0) {
          const foundRule = result.rows[0];
          await pool.query(
            `UPDATE numbering_rules SET column_name = $1 WHERE rule_id = $2 AND company_code = $3`,
            [columnName, foundRule.ruleId, companyCode]
          );
          result.rows[0].columnName = columnName;
          logger.info("레거시 채번 규칙 자동 매핑 완료", {
            ruleId: foundRule.ruleId,
            tableName,
            columnName,
          });
        }
      }

      if (result.rows.length === 0) {
        logger.info("테이블+컬럼 기반 채번 규칙을 찾을 수 없음", {
          companyCode,
          tableName,
          columnName,
        });
        return null;
      }

      const rule = result.rows[0];

      const partsQuery = `
        SELECT
          id,
          part_order AS "order",
          part_type AS "partType",
          generation_method AS "generationMethod",
          auto_config AS "autoConfig",
          manual_config AS "manualConfig"
        FROM numbering_rule_parts
        WHERE rule_id = $1 AND company_code = $2
        ORDER BY part_order
      `;
      const partsResult = await pool.query(partsQuery, [
        rule.ruleId,
        companyCode,
      ]);
      rule.parts = extractSeparatorAfterFromParts(partsResult.rows);

      logger.info("테이블+컬럼 기반 채번 규칙 조회 성공", {
        ruleId: rule.ruleId,
        ruleName: rule.ruleName,
      });
      return rule;
    } catch (error: any) {
      logger.error("테이블+컬럼 기반 채번 규칙 조회 실패 (테스트)", {
        error: error.message,
        stack: error.stack,
        companyCode,
        tableName,
        columnName,
      });
      throw error;
    }
  }

  /**
   * [테스트] 테스트 테이블에 채번규칙 저장
   * numbering_rules 테이블 사용
   */
  async saveRuleToTest(
    config: NumberingRuleConfig,
    companyCode: string,
    createdBy: string
  ): Promise<NumberingRuleConfig> {
    const pool = getPool();
    const client = await pool.connect();

    try {
      await client.query("BEGIN");

      logger.info("테스트 테이블에 채번 규칙 저장 시작", {
        ruleId: config.ruleId,
        ruleName: config.ruleName,
        tableName: config.tableName,
        columnName: config.columnName,
        companyCode,
      });

      // 기존 규칙 확인
      const existingQuery = `
        SELECT rule_id FROM numbering_rules 
        WHERE rule_id = $1 AND company_code = $2
      `;
      const existingResult = await client.query(existingQuery, [
        config.ruleId,
        companyCode,
      ]);

      if (existingResult.rows.length > 0) {
        // 업데이트
        const updateQuery = `
          UPDATE numbering_rules SET
            rule_name = $1,
            description = $2,
            separator = $3,
            reset_period = $4,
            table_name = $5,
            column_name = $6,
            category_column = $7,
            category_value_id = $8,
            updated_at = NOW()
          WHERE rule_id = $9 AND company_code = $10
        `;
        await client.query(updateQuery, [
          config.ruleName,
          config.description || "",
          config.separator || "-",
          config.resetPeriod || "none",
          config.tableName || "",
          config.columnName || "",
          config.categoryColumn || null,
          config.categoryValueId || null,
          config.ruleId,
          companyCode,
        ]);

        // 기존 파트 삭제
        await client.query(
          "DELETE FROM numbering_rule_parts WHERE rule_id = $1 AND company_code = $2",
          [config.ruleId, companyCode]
        );
      } else {
        // 신규 등록
        const insertQuery = `
          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_at, updated_at, created_by
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, NOW(), NOW(), $12)
        `;
        await client.query(insertQuery, [
          config.ruleId,
          config.ruleName,
          config.description || "",
          config.separator || "-",
          config.resetPeriod || "none",
          config.currentSequence || 1,
          config.tableName || "",
          config.columnName || "",
          companyCode,
          config.categoryColumn || null,
          config.categoryValueId || null,
          createdBy,
        ]);
      }

      // 파트 저장
      if (config.parts && config.parts.length > 0) {
        for (const part of config.parts) {
          const partInsertQuery = `
            INSERT INTO numbering_rule_parts (
              rule_id, part_order, part_type, generation_method,
              auto_config, manual_config, company_code, created_at
            ) VALUES ($1, $2, $3, $4, $5, $6, $7, NOW())
          `;
          const autoConfigWithSep = { ...(part.autoConfig || {}), separatorAfter: part.separatorAfter ?? "-" };
          
          await client.query(partInsertQuery, [
            config.ruleId,
            part.order,
            part.partType,
            part.generationMethod,
            JSON.stringify(autoConfigWithSep),
            JSON.stringify(part.manualConfig || {}),
            companyCode,
          ]);
        }
      }

      await client.query("COMMIT");

      logger.info("테스트 테이블에 채번 규칙 저장 완료", {
        ruleId: config.ruleId,
        companyCode,
      });

      return config;
    } catch (error: any) {
      await client.query("ROLLBACK");
      logger.error("테스트 테이블에 채번 규칙 저장 실패", {
        error: error.message,
        stack: error.stack,
        ruleId: config.ruleId,
        companyCode,
      });
      throw error;
    } finally {
      client.release();
    }
  }

  /**
   * [테스트] 테스트 테이블에서 채번규칙 삭제
   * numbering_rules 테이블 사용
   */
  async deleteRuleFromTest(ruleId: string, companyCode: string): Promise<void> {
    const pool = getPool();
    const client = await pool.connect();

    try {
      await client.query("BEGIN");

      logger.info("테스트 테이블에서 채번 규칙 삭제 시작", {
        ruleId,
        companyCode,
      });

      // 파트 먼저 삭제
      await client.query(
        "DELETE FROM numbering_rule_parts WHERE rule_id = $1 AND company_code = $2",
        [ruleId, companyCode]
      );

      // 규칙 삭제
      const result = await client.query(
        "DELETE FROM numbering_rules WHERE rule_id = $1 AND company_code = $2",
        [ruleId, companyCode]
      );

      await client.query("COMMIT");

      logger.info("테스트 테이블에서 채번 규칙 삭제 완료", {
        ruleId,
        companyCode,
        deletedCount: result.rowCount,
      });
    } catch (error: any) {
      await client.query("ROLLBACK");
      logger.error("테스트 테이블에서 채번 규칙 삭제 실패", {
        error: error.message,
        ruleId,
        companyCode,
      });
      throw error;
    } finally {
      client.release();
    }
  }

  /**
   * [테스트] 카테고리 값에 따라 적절한 채번규칙 조회
   * 1. 해당 카테고리 값에 매칭되는 규칙 찾기
   * 2. 없으면 기본 규칙(category_value_id가 NULL인) 찾기
   */
  async getNumberingRuleByColumnWithCategory(
    companyCode: string,
    tableName: string,
    columnName: string,
    categoryColumn?: string,
    categoryValueId?: number
  ): Promise<NumberingRuleConfig | null> {
    try {
      logger.info("카테고리 조건 포함 채번 규칙 조회 시작", {
        companyCode,
        tableName,
        columnName,
        categoryColumn,
        categoryValueId,
      });

      const pool = getPool();

      // 1. 카테고리 값에 매칭되는 규칙 찾기
      if (categoryColumn && categoryValueId) {
        const categoryQuery = `
          SELECT
            r.rule_id AS "ruleId",
            r.rule_name AS "ruleName",
            r.description,
            r.separator,
            r.reset_period AS "resetPeriod",
            r.current_sequence AS "currentSequence",
            r.table_name AS "tableName",
            r.column_name AS "columnName",
            r.company_code AS "companyCode",
            r.category_column AS "categoryColumn",
            r.category_value_id AS "categoryValueId",
            cv.value_label AS "categoryValueLabel",
            r.created_at AS "createdAt",
            r.updated_at AS "updatedAt",
            r.created_by AS "createdBy"
          FROM numbering_rules r
          LEFT JOIN category_values cv ON r.category_value_id = cv.value_id
          WHERE r.company_code = $1
            AND r.table_name = $2
            AND r.column_name = $3
            AND r.category_column = $4
            AND r.category_value_id = $5
          LIMIT 1
        `;
        const categoryResult = await pool.query(categoryQuery, [
          companyCode,
          tableName,
          columnName,
          categoryColumn,
          categoryValueId,
        ]);

        if (categoryResult.rows.length > 0) {
          const rule = categoryResult.rows[0];
          // 파트 정보 조회
          const partsQuery = `
            SELECT
              id,
              part_order AS "order",
              part_type AS "partType",
              generation_method AS "generationMethod",
              auto_config AS "autoConfig",
              manual_config AS "manualConfig"
            FROM numbering_rule_parts
            WHERE rule_id = $1 AND company_code = $2
            ORDER BY part_order
          `;
          const partsResult = await pool.query(partsQuery, [
            rule.ruleId,
            companyCode,
          ]);
          rule.parts = extractSeparatorAfterFromParts(partsResult.rows);

          logger.info("카테고리 조건 매칭 채번 규칙 찾음", {
            ruleId: rule.ruleId,
            categoryValueLabel: rule.categoryValueLabel,
          });
          return rule;
        }
      }

      // 2. 기본 규칙 찾기 (category_value_id가 NULL인)
      const defaultQuery = `
        SELECT
          r.rule_id AS "ruleId",
          r.rule_name AS "ruleName",
          r.description,
          r.separator,
          r.reset_period AS "resetPeriod",
          r.current_sequence AS "currentSequence",
          r.table_name AS "tableName",
          r.column_name AS "columnName",
          r.company_code AS "companyCode",
          r.category_column AS "categoryColumn",
          r.category_value_id AS "categoryValueId",
          r.created_at AS "createdAt",
          r.updated_at AS "updatedAt",
          r.created_by AS "createdBy"
        FROM numbering_rules r
        WHERE r.company_code = $1
          AND r.table_name = $2
          AND r.column_name = $3
          AND r.category_value_id IS NULL
        LIMIT 1
      `;
      const defaultResult = await pool.query(defaultQuery, [
        companyCode,
        tableName,
        columnName,
      ]);

      if (defaultResult.rows.length > 0) {
        const rule = defaultResult.rows[0];
        // 파트 정보 조회
        const partsQuery = `
          SELECT
            id,
            part_order AS "order",
            part_type AS "partType",
            generation_method AS "generationMethod",
            auto_config AS "autoConfig",
            manual_config AS "manualConfig"
          FROM numbering_rule_parts
          WHERE rule_id = $1 AND company_code = $2
          ORDER BY part_order
        `;
        const partsResult = await pool.query(partsQuery, [
          rule.ruleId,
          companyCode,
        ]);
        rule.parts = extractSeparatorAfterFromParts(partsResult.rows);

        logger.info("기본 채번 규칙 찾음 (카테고리 조건 없음)", {
          ruleId: rule.ruleId,
        });
        return rule;
      }

      logger.info("채번 규칙을 찾을 수 없음", {
        companyCode,
        tableName,
        columnName,
        categoryColumn,
        categoryValueId,
      });
      return null;
    } catch (error: any) {
      logger.error("카테고리 조건 포함 채번 규칙 조회 실패", {
        error: error.message,
        stack: error.stack,
      });
      throw error;
    }
  }

  /**
   * [테스트] 특정 테이블.컬럼의 모든 채번 규칙 조회 (카테고리 조건별)
   */
  async getRulesByTableColumn(
    companyCode: string,
    tableName: string,
    columnName: string
  ): Promise<NumberingRuleConfig[]> {
    try {
      const pool = getPool();
      const query = `
        SELECT
          r.rule_id AS "ruleId",
          r.rule_name AS "ruleName",
          r.description,
          r.separator,
          r.reset_period AS "resetPeriod",
          r.current_sequence AS "currentSequence",
          r.table_name AS "tableName",
          r.column_name AS "columnName",
          r.company_code AS "companyCode",
          r.category_column AS "categoryColumn",
          r.category_value_id AS "categoryValueId",
          cv.value_label AS "categoryValueLabel",
          r.created_at AS "createdAt",
          r.updated_at AS "updatedAt",
          r.created_by AS "createdBy"
        FROM numbering_rules r
        LEFT JOIN category_values cv ON r.category_value_id = cv.value_id
        WHERE r.company_code = $1
          AND r.table_name = $2
          AND r.column_name = $3
        ORDER BY r.category_value_id NULLS FIRST, r.created_at
      `;
      const result = await pool.query(query, [
        companyCode,
        tableName,
        columnName,
      ]);

      // 각 규칙의 파트 정보 조회
      for (const rule of result.rows) {
        const partsQuery = `
          SELECT
            id,
            part_order AS "order",
            part_type AS "partType",
            generation_method AS "generationMethod",
            auto_config AS "autoConfig",
            manual_config AS "manualConfig"
          FROM numbering_rule_parts
          WHERE rule_id = $1 AND company_code = $2
          ORDER BY part_order
        `;
        const partsResult = await pool.query(partsQuery, [
          rule.ruleId,
          companyCode,
        ]);
        rule.parts = extractSeparatorAfterFromParts(partsResult.rows);
      }

      return result.rows;
    } catch (error: any) {
      logger.error("테이블.컬럼별 채번 규칙 목록 조회 실패", {
        error: error.message,
      });
      throw error;
    }
  }

  /**
   * 회사별 채번규칙 복제 (테이블 기반)
   * numbering_rules, numbering_rule_parts 테이블 사용
   * 복제 후 화면 레이아웃의 numberingRuleId 참조도 업데이트
   */
  async copyRulesForCompany(
    sourceCompanyCode: string,
    targetCompanyCode: string
  ): Promise<{
    copiedCount: number;
    skippedCount: number;
    details: string[];
    ruleIdMap: Record<string, string>;
  }> {
    const pool = getPool();
    const client = await pool.connect();

    const result = {
      copiedCount: 0,
      skippedCount: 0,
      details: [] as string[],
      ruleIdMap: {} as Record<string, string>,
    };

    try {
      await client.query("BEGIN");

      // 0. 대상 회사의 기존 채번규칙 삭제 (깨끗하게 복제하기 위해)
      // 먼저 파트 삭제
      await client.query(
        `DELETE FROM numbering_rule_parts 
         WHERE rule_id IN (SELECT rule_id FROM numbering_rules WHERE company_code = $1)`,
        [targetCompanyCode]
      );
      // 규칙 삭제
      const deleteResult = await client.query(
        `DELETE FROM numbering_rules WHERE company_code = $1 RETURNING rule_id`,
        [targetCompanyCode]
      );
      if (deleteResult.rowCount && deleteResult.rowCount > 0) {
        logger.info("기존 채번규칙 삭제", {
          targetCompanyCode,
          deletedCount: deleteResult.rowCount,
        });
      }

      // 1. 원본 회사의 채번규칙 조회 - numbering_rules 사용
      const sourceRulesResult = await client.query(
        `SELECT * FROM numbering_rules WHERE company_code = $1`,
        [sourceCompanyCode]
      );

      logger.info("원본 채번규칙 조회", {
        sourceCompanyCode,
        count: sourceRulesResult.rowCount,
      });

      // 2. 각 채번규칙 복제
      for (const rule of sourceRulesResult.rows) {
        // 새 rule_id 생성
        const newRuleId = `rule-${Date.now()}-${Math.random().toString(36).substr(2, 9)}`;

        // 이미 존재하는지 확인 (이름 기반) - numbering_rules 사용
        const existsCheck = await client.query(
          `SELECT rule_id FROM numbering_rules 
           WHERE company_code = $1 AND rule_name = $2`,
          [targetCompanyCode, rule.rule_name]
        );

        if (existsCheck.rows.length > 0) {
          // 이미 존재하면 매핑만 추가
          result.ruleIdMap[rule.rule_id] = existsCheck.rows[0].rule_id;
          result.skippedCount++;
          result.details.push(`건너뜀 (이미 존재): ${rule.rule_name}`);
          continue;
        }

        // 채번규칙 복제 - numbering_rules 사용
        await client.query(
          `INSERT INTO numbering_rules (
            rule_id, rule_name, description, separator, reset_period,
            current_sequence, table_name, column_name, company_code,
            created_at, updated_at, created_by, category_column, category_value_id
          ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, NOW(), NOW(), $10, $11, $12)`,
          [
            newRuleId,
            rule.rule_name,
            rule.description,
            rule.separator,
            rule.reset_period,
            0, // 시퀀스 초기화
            rule.table_name,
            rule.column_name,
            targetCompanyCode,
            rule.created_by,
            rule.category_column,
            rule.category_value_id,
          ]
        );

        // 채번규칙 파트 복제 - numbering_rule_parts 사용
        const partsResult = await client.query(
          `SELECT * FROM numbering_rule_parts WHERE rule_id = $1 ORDER BY part_order`,
          [rule.rule_id]
        );

        for (const part of partsResult.rows) {
          await client.query(
            `INSERT INTO numbering_rule_parts (
              rule_id, part_order, part_type, generation_method,
              auto_config, manual_config, company_code, created_at
            ) VALUES ($1, $2, $3, $4, $5, $6, $7, NOW())`,
            [
              newRuleId,
              part.part_order,
              part.part_type,
              part.generation_method,
              part.auto_config ? JSON.stringify(part.auto_config) : null,
              part.manual_config ? JSON.stringify(part.manual_config) : null,
              targetCompanyCode,
            ]
          );
        }

        // 매핑 추가
        result.ruleIdMap[rule.rule_id] = newRuleId;
        result.copiedCount++;
        result.details.push(`복제 완료: ${rule.rule_name}`);
        logger.info("채번규칙 복제 완료", {
          ruleName: rule.rule_name,
          oldRuleId: rule.rule_id,
          newRuleId,
        });
      }

      // 3. 화면 레이아웃의 numberingRuleId 참조 업데이트
      if (Object.keys(result.ruleIdMap).length > 0) {
        logger.info("화면 레이아웃 numberingRuleId 참조 업데이트 시작", {
          targetCompanyCode,
          mappingCount: Object.keys(result.ruleIdMap).length,
        });

        // 대상 회사의 모든 화면 레이아웃 조회
        const layoutsResult = await client.query(
          `SELECT sl.layout_id, sl.properties
           FROM screen_layouts sl
           JOIN screen_definitions sd ON sl.screen_id = sd.screen_id
           WHERE sd.company_code = $1
           AND sl.properties::text LIKE '%numberingRuleId%'`,
          [targetCompanyCode]
        );

        let updatedLayouts = 0;

        for (const layout of layoutsResult.rows) {
          let propsStr = JSON.stringify(layout.properties);
          let updated = false;

          // 각 매핑에 대해 치환
          for (const [oldRuleId, newRuleId] of Object.entries(
            result.ruleIdMap
          )) {
            if (propsStr.includes(`"${oldRuleId}"`)) {
              propsStr = propsStr
                .split(`"${oldRuleId}"`)
                .join(`"${newRuleId}"`);
              updated = true;
            }
          }

          if (updated) {
            await client.query(
              `UPDATE screen_layouts SET properties = $1::jsonb WHERE layout_id = $2`,
              [propsStr, layout.layout_id]
            );
            updatedLayouts++;
          }
        }

        logger.info("화면 레이아웃 numberingRuleId 참조 업데이트 완료", {
          targetCompanyCode,
          updatedLayouts,
        });
        result.details.push(
          `화면 레이아웃 ${updatedLayouts}개의 채번규칙 참조 업데이트`
        );
      }

      await client.query("COMMIT");

      logger.info("회사별 채번규칙 복제 완료", {
        sourceCompanyCode,
        targetCompanyCode,
        copiedCount: result.copiedCount,
        skippedCount: result.skippedCount,
        ruleIdMapCount: Object.keys(result.ruleIdMap).length,
      });

      return result;
    } catch (error) {
      await client.query("ROLLBACK");
      logger.error("회사별 채번규칙 복제 실패", {
        error,
        sourceCompanyCode,
        targetCompanyCode,
      });
      throw error;
    } finally {
      client.release();
    }
  }
}

export const numberingRuleService = new NumberingRuleService();
