/**
 * 카테고리 트리 서비스 (테스트용)
 * - 트리 구조 지원 (최대 3단계: 대분류/중분류/소분류)
 */

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

// 카테고리 값 타입
export interface CategoryValue {
  valueId: number;
  tableName: string;
  columnName: string;
  valueCode: string;
  valueLabel: string;
  valueOrder: number;
  parentValueId: number | null;
  depth: number;
  path: string | null;
  description: string | null;
  color: string | null;
  icon: string | null;
  isActive: boolean;
  isDefault: boolean;
  companyCode: string;
  createdAt: Date;
  updatedAt: Date;
  createdBy: string | null;
  updatedBy: string | null;
  children?: CategoryValue[];
}

// 카테고리 값 생성 입력
export interface CreateCategoryValueInput {
  tableName: string;
  columnName: string;
  valueCode: string;
  valueLabel: string;
  valueOrder?: number;
  parentValueId?: number | null;
  description?: string;
  color?: string;
  icon?: string;
  isActive?: boolean;
  isDefault?: boolean;
  targetCompanyCode?: string; // 최고 관리자가 특정 회사를 선택할 때 사용
}

// 카테고리 값 수정 입력
export interface UpdateCategoryValueInput {
  valueCode?: string;
  valueLabel?: string;
  valueOrder?: number;
  parentValueId?: number | null;
  description?: string;
  color?: string;
  icon?: string;
  isActive?: boolean;
  isDefault?: boolean;
}

class CategoryTreeService {
  /**
   * 카테고리 값 목록 조회 (트리 구조로 반환)
   */
  async getCategoryTree(companyCode: string, tableName: string, columnName: string): Promise<CategoryValue[]> {
    const pool = getPool();

    try {
      logger.info("카테고리 트리 조회 시작", { companyCode, tableName, columnName });

      const query = `
        SELECT 
          value_id AS "valueId",
          table_name AS "tableName",
          column_name AS "columnName",
          value_code AS "valueCode",
          value_label AS "valueLabel",
          value_order AS "valueOrder",
          parent_value_id AS "parentValueId",
          depth,
          path,
          description,
          color,
          icon,
          is_active AS "isActive",
          is_default AS "isDefault",
          company_code AS "companyCode",
          created_at AS "createdAt",
          updated_at AS "updatedAt",
          created_by AS "createdBy",
          updated_by AS "updatedBy"
        FROM category_values
        WHERE (company_code = $1 OR company_code = '*')
          AND table_name = $2
          AND column_name = $3
        ORDER BY depth ASC, value_order ASC, value_label ASC
      `;

      const result = await pool.query(query, [companyCode, tableName, columnName]);
      const flatList = result.rows as CategoryValue[];

      const tree = this.buildTree(flatList);

      logger.info("카테고리 트리 조회 완료", {
        tableName,
        columnName,
        totalCount: flatList.length,
        rootCount: tree.length,
      });

      return tree;
    } catch (error: unknown) {
      const err = error as Error;
      logger.error("카테고리 트리 조회 실패", { error: err.message, tableName, columnName });
      throw error;
    }
  }

  /**
   * 카테고리 값 목록 조회 (플랫 리스트)
   */
  async getCategoryList(companyCode: string, tableName: string, columnName: string): Promise<CategoryValue[]> {
    const pool = getPool();

    try {
      const query = `
        SELECT 
          value_id AS "valueId",
          table_name AS "tableName",
          column_name AS "columnName",
          value_code AS "valueCode",
          value_label AS "valueLabel",
          value_order AS "valueOrder",
          parent_value_id AS "parentValueId",
          depth,
          path,
          description,
          color,
          icon,
          is_active AS "isActive",
          is_default AS "isDefault",
          company_code AS "companyCode",
          created_at AS "createdAt",
          updated_at AS "updatedAt"
        FROM category_values
        WHERE (company_code = $1 OR company_code = '*')
          AND table_name = $2
          AND column_name = $3
        ORDER BY depth ASC, value_order ASC, value_label ASC
      `;

      const result = await pool.query(query, [companyCode, tableName, columnName]);
      return result.rows as CategoryValue[];
    } catch (error: unknown) {
      const err = error as Error;
      logger.error("카테고리 목록 조회 실패", { error: err.message });
      throw error;
    }
  }

  /**
   * 카테고리 값 단일 조회
   */
  async getCategoryValue(companyCode: string, valueId: number): Promise<CategoryValue | null> {
    const pool = getPool();

    try {
      const query = `
        SELECT 
          value_id AS "valueId",
          table_name AS "tableName",
          column_name AS "columnName",
          value_code AS "valueCode",
          value_label AS "valueLabel",
          value_order AS "valueOrder",
          parent_value_id AS "parentValueId",
          depth,
          path,
          description,
          color,
          icon,
          is_active AS "isActive",
          is_default AS "isDefault",
          company_code AS "companyCode",
          created_at AS "createdAt",
          updated_at AS "updatedAt"
        FROM category_values
        WHERE (company_code = $1 OR company_code = '*') AND value_id = $2
      `;

      const result = await pool.query(query, [companyCode, valueId]);
      return result.rows[0] || null;
    } catch (error: unknown) {
      const err = error as Error;
      logger.error("카테고리 값 조회 실패", { error: err.message, valueId });
      throw error;
    }
  }

  /**
   * 카테고리 값 생성
   */
  async createCategoryValue(companyCode: string, input: CreateCategoryValueInput, createdBy?: string): Promise<CategoryValue> {
    const pool = getPool();

    try {
      // depth 계산
      let depth = 1;
      let path = input.valueLabel;

      if (input.parentValueId) {
        const parent = await this.getCategoryValue(companyCode, input.parentValueId);
        if (parent) {
          depth = parent.depth + 1;
          path = parent.path ? `${parent.path}/${input.valueLabel}` : input.valueLabel;

          if (depth > 3) {
            throw new Error("카테고리는 최대 3단계까지만 가능합니다");
          }
        }
      }

      const query = `
        INSERT INTO category_values (
          table_name, column_name, value_code, value_label, value_order,
          parent_value_id, depth, path, description, color, icon,
          is_active, is_default, company_code, created_by, updated_by
        ) VALUES (
          $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $15
        )
        RETURNING 
          value_id AS "valueId",
          table_name AS "tableName",
          column_name AS "columnName",
          value_code AS "valueCode",
          value_label AS "valueLabel",
          value_order AS "valueOrder",
          parent_value_id AS "parentValueId",
          depth,
          path,
          description,
          color,
          icon,
          is_active AS "isActive",
          is_default AS "isDefault",
          company_code AS "companyCode",
          created_at AS "createdAt",
          updated_at AS "updatedAt"
      `;

      const params = [
        input.tableName,
        input.columnName,
        input.valueCode,
        input.valueLabel,
        input.valueOrder ?? 0,
        input.parentValueId ?? null,
        depth,
        path,
        input.description ?? null,
        input.color ?? null,
        input.icon ?? null,
        input.isActive ?? true,
        input.isDefault ?? false,
        companyCode,
        createdBy ?? null,
      ];

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

      logger.info("카테고리 값 생성 완료", {
        valueId: result.rows[0].valueId,
        valueLabel: input.valueLabel,
        depth,
      });

      return result.rows[0];
    } catch (error: unknown) {
      const err = error as Error;
      logger.error("카테고리 값 생성 실패", { error: err.message, input });
      throw error;
    }
  }

  /**
   * 카테고리 값 수정
   */
  async updateCategoryValue(
    companyCode: string,
    valueId: number,
    input: UpdateCategoryValueInput,
    updatedBy?: string
  ): Promise<CategoryValue | null> {
    const pool = getPool();

    try {
      const current = await this.getCategoryValue(companyCode, valueId);
      if (!current) {
        return null;
      }

      let newPath = current.path;
      let newDepth = current.depth;

      if (input.valueLabel && input.valueLabel !== current.valueLabel) {
        if (current.parentValueId) {
          const parent = await this.getCategoryValue(companyCode, current.parentValueId);
          if (parent && parent.path) {
            newPath = `${parent.path}/${input.valueLabel}`;
          } else {
            newPath = input.valueLabel;
          }
        } else {
          newPath = input.valueLabel;
        }
      }

      if (input.parentValueId !== undefined && input.parentValueId !== current.parentValueId) {
        if (input.parentValueId) {
          const newParent = await this.getCategoryValue(companyCode, input.parentValueId);
          if (newParent) {
            newDepth = newParent.depth + 1;
            const label = input.valueLabel ?? current.valueLabel;
            newPath = newParent.path ? `${newParent.path}/${label}` : label;

            if (newDepth > 3) {
              throw new Error("카테고리는 최대 3단계까지만 가능합니다");
            }
          }
        } else {
          newDepth = 1;
          newPath = input.valueLabel ?? current.valueLabel;
        }
      }

      const query = `
        UPDATE category_values
        SET 
          value_code = COALESCE($3, value_code),
          value_label = COALESCE($4, value_label),
          value_order = COALESCE($5, value_order),
          parent_value_id = $6,
          depth = $7,
          path = $8,
          description = COALESCE($9, description),
          color = COALESCE($10, color),
          icon = COALESCE($11, icon),
          is_active = COALESCE($12, is_active),
          is_default = COALESCE($13, is_default),
          updated_at = NOW(),
          updated_by = $14
        WHERE (company_code = $1 OR company_code = '*') AND value_id = $2
        RETURNING 
          value_id AS "valueId",
          table_name AS "tableName",
          column_name AS "columnName",
          value_code AS "valueCode",
          value_label AS "valueLabel",
          value_order AS "valueOrder",
          parent_value_id AS "parentValueId",
          depth,
          path,
          description,
          color,
          icon,
          is_active AS "isActive",
          is_default AS "isDefault",
          company_code AS "companyCode",
          created_at AS "createdAt",
          updated_at AS "updatedAt"
      `;

      const params = [
        companyCode,
        valueId,
        input.valueCode ?? null,
        input.valueLabel ?? null,
        input.valueOrder ?? null,
        input.parentValueId !== undefined ? input.parentValueId : current.parentValueId,
        newDepth,
        newPath,
        input.description ?? null,
        input.color ?? null,
        input.icon ?? null,
        input.isActive ?? null,
        input.isDefault ?? null,
        updatedBy ?? null,
      ];

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

      if (input.valueLabel || input.parentValueId !== undefined) {
        await this.updateChildrenPaths(companyCode, valueId, newPath || "");
      }

      logger.info("카테고리 값 수정 완료", { valueId });

      return result.rows[0] || null;
    } catch (error: unknown) {
      const err = error as Error;
      logger.error("카테고리 값 수정 실패", { error: err.message, valueId });
      throw error;
    }
  }

  /**
   * 카테고리 값이 실제 데이터 테이블에서 사용 중인지 확인
   */
  private async checkCategoryValueInUse(
    companyCode: string,
    value: CategoryValue
  ): Promise<{ inUse: boolean; count: number }> {
    const pool = getPool();

    try {
      const tableExists = await pool.query(
        `SELECT EXISTS (
          SELECT 1 FROM information_schema.tables 
          WHERE table_schema = 'public' AND table_name = $1
        ) AS exists`,
        [value.tableName]
      );

      if (!tableExists.rows[0].exists) {
        return { inUse: false, count: 0 };
      }

      const columnExists = await pool.query(
        `SELECT EXISTS (
          SELECT 1 FROM information_schema.columns 
          WHERE table_schema = 'public' AND table_name = $1 AND column_name = $2
        ) AS exists`,
        [value.tableName, value.columnName]
      );

      if (!columnExists.rows[0].exists) {
        return { inUse: false, count: 0 };
      }

      const hasCompanyCode = await pool.query(
        `SELECT EXISTS (
          SELECT 1 FROM information_schema.columns 
          WHERE table_schema = 'public' AND table_name = $1 AND column_name = 'company_code'
        ) AS exists`,
        [value.tableName]
      );

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

      if (hasCompanyCode.rows[0].exists && companyCode !== "*") {
        countQuery = `
          SELECT COUNT(*) as count FROM "${value.tableName}"
          WHERE company_code = $1
            AND ($2 = ANY(string_to_array("${value.columnName}"::text, ','))
                 OR "${value.columnName}"::text = $2)
        `;
        params = [companyCode, value.valueCode];
      } else {
        countQuery = `
          SELECT COUNT(*) as count FROM "${value.tableName}"
          WHERE $1 = ANY(string_to_array("${value.columnName}"::text, ','))
                OR "${value.columnName}"::text = $1
        `;
        params = [value.valueCode];
      }

      const result = await pool.query(countQuery, params);
      const count = parseInt(result.rows[0].count);

      return { inUse: count > 0, count };
    } catch (error: unknown) {
      const err = error as Error;
      logger.warn("카테고리 사용 여부 확인 중 오류 (무시하고 삭제 허용)", {
        error: err.message,
        tableName: value.tableName,
        columnName: value.columnName,
      });
      return { inUse: false, count: 0 };
    }
  }

  /**
   * 카테고리 값 삭제 가능 여부 사전 확인
   */
  async checkCanDelete(
    companyCode: string,
    valueId: number
  ): Promise<{ canDelete: boolean; reason?: string }> {
    const pool = getPool();

    const value = await this.getCategoryValue(companyCode, valueId);
    if (!value) {
      return { canDelete: false, reason: "카테고리 값을 찾을 수 없습니다" };
    }

    const childCheck = await pool.query(
      `SELECT COUNT(*) as count FROM category_values 
       WHERE parent_value_id = $1 AND (company_code = $2 OR company_code = '*')`,
      [valueId, companyCode]
    );
    const childCount = parseInt(childCheck.rows[0].count);

    if (childCount > 0) {
      return {
        canDelete: false,
        reason: `하위 카테고리가 ${childCount}개 존재합니다. 하위 카테고리를 먼저 삭제해주세요.`,
      };
    }

    const usageCheck = await this.checkCategoryValueInUse(companyCode, value);
    if (usageCheck.inUse) {
      return {
        canDelete: false,
        reason: `이 카테고리 값(${value.valueLabel})은 ${usageCheck.count}건의 데이터에서 사용 중이므로 삭제할 수 없습니다.`,
      };
    }

    return { canDelete: true };
  }

  /**
   * 카테고리 값 삭제 (자식 존재 및 사용 중 검증 후 삭제)
   */
  async deleteCategoryValue(companyCode: string, valueId: number): Promise<boolean> {
    const pool = getPool();

    try {
      const value = await this.getCategoryValue(companyCode, valueId);
      if (!value) {
        return false;
      }

      // 1. 자식 카테고리 존재 여부 확인
      const childCheck = await pool.query(
        `SELECT COUNT(*) as count FROM category_values 
         WHERE parent_value_id = $1 AND (company_code = $2 OR company_code = '*')`,
        [valueId, companyCode]
      );
      const childCount = parseInt(childCheck.rows[0].count);

      if (childCount > 0) {
        throw new Error(
          `VALIDATION:하위 카테고리가 ${childCount}개 존재합니다. 하위 카테고리를 먼저 삭제해주세요.`
        );
      }

      // 2. 실제 데이터에서 사용 중인지 확인
      const usageCheck = await this.checkCategoryValueInUse(companyCode, value);
      if (usageCheck.inUse) {
        throw new Error(
          `VALIDATION:이 카테고리 값(${value.valueLabel})은 ${value.tableName} 테이블에서 ${usageCheck.count}건의 데이터가 사용 중이므로 삭제할 수 없습니다.`
        );
      }

      // 3. 삭제
      await pool.query(
        `DELETE FROM category_values WHERE (company_code = $1 OR company_code = '*') AND value_id = $2`,
        [companyCode, valueId]
      );

      logger.info("카테고리 값 삭제 완료", { valueId, valueLabel: value.valueLabel });

      return true;
    } catch (error: unknown) {
      const err = error as Error;
      if (!err.message.startsWith("VALIDATION:")) {
        logger.error("카테고리 값 삭제 실패", { error: err.message, valueId });
      }
      throw error;
    }
  }

  /**
   * 하위 항목들의 path 업데이트
   */
  private async updateChildrenPaths(companyCode: string, parentValueId: number, parentPath: string): Promise<void> {
    const pool = getPool();

    const query = `
      SELECT value_id, value_label
      FROM category_values
      WHERE (company_code = $1 OR company_code = '*') AND parent_value_id = $2
    `;

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

    for (const child of result.rows) {
      const newPath = `${parentPath}/${child.value_label}`;

      await pool.query(`UPDATE category_values SET path = $1, updated_at = NOW() WHERE value_id = $2`, [
        newPath,
        child.value_id,
      ]);

      await this.updateChildrenPaths(companyCode, child.value_id, newPath);
    }
  }

  /**
   * 플랫 리스트를 트리 구조로 변환
   */
  private buildTree(flatList: CategoryValue[]): CategoryValue[] {
    const map = new Map<number, CategoryValue>();
    const roots: CategoryValue[] = [];

    for (const item of flatList) {
      map.set(item.valueId, { ...item, children: [] });
    }

    for (const item of flatList) {
      const node = map.get(item.valueId)!;

      if (item.parentValueId && map.has(item.parentValueId)) {
        const parent = map.get(item.parentValueId)!;
        parent.children = parent.children || [];
        parent.children.push(node);
      } else {
        roots.push(node);
      }
    }

    return roots;
  }

  /**
   * 테이블의 카테고리 컬럼 목록 조회
   */
  async getCategoryColumns(companyCode: string, tableName: string): Promise<{ columnName: string; columnLabel: string }[]> {
    const pool = getPool();

    try {
      const query = `
        SELECT DISTINCT column_name AS "columnName", column_label AS "columnLabel"
        FROM table_type_columns
        WHERE table_name = $1
          AND input_type = 'category'
          AND (company_code = $2 OR company_code = '*')
        ORDER BY column_name
      `;

      const result = await pool.query(query, [tableName, companyCode]);
      return result.rows;
    } catch (error: unknown) {
      const err = error as Error;
      logger.error("카테고리 컬럼 목록 조회 실패", { error: err.message, tableName });
      throw error;
    }
  }

  /**
   * 전체 카테고리 키 목록 조회 (모든 테이블.컬럼 조합)
   * category_values 테이블에서 고유한 table_name, column_name 조합을 조회
   * 라벨 정보도 함께 반환
   */
  async getAllCategoryKeys(companyCode: string): Promise<{ tableName: string; columnName: string; tableLabel: string; columnLabel: string }[]> {
    logger.info("getAllCategoryKeys 호출", { companyCode });
    const pool = getPool();

    try {
      const query = `
        SELECT DISTINCT 
          cv.table_name AS "tableName", 
          cv.column_name AS "columnName",
          COALESCE(tl.table_label, cv.table_name) AS "tableLabel",
          COALESCE(ttc.column_label, cv.column_name) AS "columnLabel"
        FROM category_values cv
        LEFT JOIN table_labels tl ON tl.table_name = cv.table_name
        LEFT JOIN table_type_columns ttc ON ttc.table_name = cv.table_name AND ttc.column_name = cv.column_name AND ttc.company_code = '*'
        WHERE cv.company_code = $1 OR cv.company_code = '*'
        ORDER BY cv.table_name, cv.column_name
      `;

      const result = await pool.query(query, [companyCode]);
      logger.info("전체 카테고리 키 목록 조회 완료", { count: result.rows.length });
      return result.rows;
    } catch (error: unknown) {
      const err = error as Error;
      logger.error("전체 카테고리 키 목록 조회 실패", { error: err.message });
      throw error;
    }
  }
}

export const categoryTreeService = new CategoryTreeService();

