import { Response } from "express";
import { AuthenticatedRequest } from "../types/auth";
import { getPool } from "../database/db";
import { logger } from "../utils/logger";

/**
 * 필터 조건을 WHERE절에 적용하는 공통 헬퍼
 * filters JSON 배열: [{ column, operator, value }]
 */
function applyFilters(
  filtersJson: string | undefined,
  existingColumns: Set<string>,
  whereConditions: string[],
  params: any[],
  startParamIndex: number,
  tableName: string,
): number {
  let paramIndex = startParamIndex;

  if (!filtersJson) return paramIndex;

  let filters: Array<{ column: string; operator: string; value: unknown }>;
  try {
    filters = JSON.parse(filtersJson as string);
  } catch {
    logger.warn("filters JSON 파싱 실패", { tableName, filtersJson });
    return paramIndex;
  }

  if (!Array.isArray(filters)) return paramIndex;

  for (const filter of filters) {
    const { column, operator = "=", value } = filter;
    if (!column || !existingColumns.has(column)) {
      logger.warn("필터 컬럼 미존재 제외", { tableName, column });
      continue;
    }

    switch (operator) {
      case "=":
        whereConditions.push(`"${column}" = $${paramIndex}`);
        params.push(value);
        paramIndex++;
        break;
      case "!=":
        whereConditions.push(`"${column}" != $${paramIndex}`);
        params.push(value);
        paramIndex++;
        break;
      case ">":
      case "<":
      case ">=":
      case "<=":
        whereConditions.push(`"${column}" ${operator} $${paramIndex}`);
        params.push(value);
        paramIndex++;
        break;
      case "in": {
        const inVals = Array.isArray(value) ? value : String(value).split(",").map(v => v.trim());
        if (inVals.length > 0) {
          const ph = inVals.map((_, i) => `$${paramIndex + i}`).join(", ");
          whereConditions.push(`"${column}" IN (${ph})`);
          params.push(...inVals);
          paramIndex += inVals.length;
        }
        break;
      }
      case "notIn": {
        const notInVals = Array.isArray(value) ? value : String(value).split(",").map(v => v.trim());
        if (notInVals.length > 0) {
          const ph = notInVals.map((_, i) => `$${paramIndex + i}`).join(", ");
          whereConditions.push(`"${column}" NOT IN (${ph})`);
          params.push(...notInVals);
          paramIndex += notInVals.length;
        }
        break;
      }
      case "like":
        whereConditions.push(`"${column}"::text ILIKE $${paramIndex}`);
        params.push(`%${value}%`);
        paramIndex++;
        break;
      case "isNull":
        whereConditions.push(`"${column}" IS NULL`);
        break;
      case "isNotNull":
        whereConditions.push(`"${column}" IS NOT NULL`);
        break;
      default:
        whereConditions.push(`"${column}" = $${paramIndex}`);
        params.push(value);
        paramIndex++;
        break;
    }
  }

  return paramIndex;
}

/**
 * 테이블 컬럼의 DISTINCT 값 조회 API (inputType: select 용)
 * GET /api/entity/:tableName/distinct/:columnName
 * 
 * 해당 테이블의 해당 컬럼에서 DISTINCT 값을 조회하여 선택박스 옵션으로 반환
 * 
 * Query Params:
 * - labelColumn: 별도의 라벨 컬럼 (선택)
 * - filters: JSON 배열 형태의 필터 조건 (선택)
 *   예: [{"column":"status","operator":"=","value":"active"}]
 */
export async function getDistinctColumnValues(req: AuthenticatedRequest, res: Response) {
  try {
    const { tableName, columnName } = req.params;
    const { labelColumn, filters: filtersParam } = req.query;

    // 유효성 검증
    if (!tableName || tableName === "undefined" || tableName === "null") {
      return res.status(400).json({
        success: false,
        message: "테이블명이 지정되지 않았습니다.",
      });
    }

    if (!columnName || columnName === "undefined" || columnName === "null") {
      return res.status(400).json({
        success: false,
        message: "컬럼명이 지정되지 않았습니다.",
      });
    }

    const companyCode = req.user!.companyCode;
    const pool = getPool();

    // 테이블의 실제 컬럼 목록 조회
    const columnsResult = await pool.query(
      `SELECT column_name FROM information_schema.columns 
       WHERE table_schema = 'public' AND table_name = $1`,
      [tableName]
    );
    const existingColumns = new Set(columnsResult.rows.map((r: any) => r.column_name));

    // 요청된 컬럼 검증
    if (!existingColumns.has(columnName)) {
      return res.status(400).json({
        success: false,
        message: `테이블 "${tableName}"에 컬럼 "${columnName}"이 존재하지 않습니다.`,
      });
    }

    // 라벨 컬럼 결정 (지정되지 않으면 값 컬럼과 동일)
    const effectiveLabelColumn = labelColumn && existingColumns.has(labelColumn as string)
      ? labelColumn as string
      : columnName;

    // WHERE 조건 (멀티테넌시)
    const whereConditions: string[] = [];
    const params: any[] = [];
    let paramIndex = 1;

    if (companyCode !== "*" && existingColumns.has("company_code")) {
      whereConditions.push(`company_code = $${paramIndex}`);
      params.push(companyCode);
      paramIndex++;
    }

    // NULL 제외
    whereConditions.push(`"${columnName}" IS NOT NULL`);
    whereConditions.push(`"${columnName}" != ''`);

    // 필터 조건 적용
    paramIndex = applyFilters(
      filtersParam as string | undefined,
      existingColumns,
      whereConditions,
      params,
      paramIndex,
      tableName,
    );

    const whereClause = whereConditions.length > 0
      ? `WHERE ${whereConditions.join(" AND ")}`
      : "";

    // 1단계: DISTINCT 값 조회
    const distinctQuery = `
      SELECT DISTINCT "${columnName}" as value, "${effectiveLabelColumn}" as label
      FROM "${tableName}"
      ${whereClause}
      ORDER BY "${effectiveLabelColumn}" ASC
      LIMIT 500
    `;
    const result = await pool.query(distinctQuery, params);

    // 2단계: 카테고리/코드 라벨 변환 (값이 있을 때만)
    if (result.rows.length > 0) {
      const rawValues = result.rows.map((r: any) => r.value);
      const labelMap: Record<string, string> = {};

      // category_values에서 라벨 조회
      try {
        const cvCompanyCondition = companyCode !== "*"
          ? `AND (company_code = $4 OR company_code = '*')`
          : "";
        const cvParams = companyCode !== "*"
          ? [tableName, columnName, rawValues, companyCode]
          : [tableName, columnName, rawValues];

        const cvResult = await pool.query(
          `SELECT value_code, value_label FROM category_values
           WHERE table_name = $1 AND column_name = $2
           AND value_code = ANY($3) AND is_active = true
           ${cvCompanyCondition}`,
          cvParams
        );
        cvResult.rows.forEach((r: any) => {
          labelMap[r.value_code] = r.value_label;
        });
      } catch (e) {
        // category_values 조회 실패 시 무시
      }

      // code_info에서 라벨 조회 (code_category 기반)
      try {
        const ttcResult = await pool.query(
          `SELECT code_category FROM table_type_columns
           WHERE table_name = $1 AND column_name = $2 AND code_category IS NOT NULL
           LIMIT 1`,
          [tableName, columnName]
        );
        const codeCategory = ttcResult.rows[0]?.code_category;

        if (codeCategory) {
          const ciCompanyCondition = companyCode !== "*"
            ? `AND (company_code = $3 OR company_code = '*')`
            : "";
          const ciParams = companyCode !== "*"
            ? [codeCategory, rawValues, companyCode]
            : [codeCategory, rawValues];

          const ciResult = await pool.query(
            `SELECT code_value, code_name FROM code_info
             WHERE code_category = $1 AND code_value = ANY($2) AND is_active = 'Y'
             ${ciCompanyCondition}`,
            ciParams
          );
          ciResult.rows.forEach((r: any) => {
            if (!labelMap[r.code_value]) {
              labelMap[r.code_value] = r.code_name;
            }
          });
        }
      } catch (e) {
        // code_info 조회 실패 시 무시
      }

      // 라벨 매핑 적용
      if (Object.keys(labelMap).length > 0) {
        result.rows.forEach((row: any) => {
          if (labelMap[row.value]) {
            row.label = labelMap[row.value];
          }
        });
      }
    }

    logger.info("컬럼 DISTINCT 값 조회 성공", {
      tableName,
      columnName,
      labelColumn: effectiveLabelColumn,
      companyCode,
      hasFilters: !!filtersParam,
      rowCount: result.rowCount,
    });

    res.json({
      success: true,
      data: result.rows,
    });
  } catch (error: any) {
    logger.error("컬럼 DISTINCT 값 조회 오류", {
      error: error.message,
      stack: error.stack,
    });
    res.status(500).json({ success: false, message: error.message });
  }
}

/**
 * 엔티티 옵션 조회 API (V2Select용)
 * GET /api/entity/:tableName/options
 * 
 * Query Params:
 * - value: 값 컬럼 (기본: id)
 * - label: 표시 컬럼 (기본: name)
 * - fields: 추가 반환 컬럼 (콤마 구분)
 * - filters: JSON 배열 형태의 필터 조건 (선택)
 *   예: [{"column":"status","operator":"=","value":"active"}]
 */
export async function getEntityOptions(req: AuthenticatedRequest, res: Response) {
  try {
    const { tableName } = req.params;
    const { value = "id", label = "name", fields, filters: filtersParam } = req.query;

    // tableName 유효성 검증
    if (!tableName || tableName === "undefined" || tableName === "null") {
      logger.warn("엔티티 옵션 조회 실패: 테이블명이 없음", { tableName });
      return res.status(400).json({
        success: false,
        message: "테이블명이 지정되지 않았습니다.",
      });
    }

    const companyCode = req.user!.companyCode;
    const pool = getPool();

    // 테이블의 실제 컬럼 목록 조회
    const columnsResult = await pool.query(
      `SELECT column_name FROM information_schema.columns 
       WHERE table_schema = 'public' AND table_name = $1`,
      [tableName]
    );
    const existingColumns = new Set(columnsResult.rows.map((r: any) => r.column_name));

    // 요청된 컬럼 검증
    const valueColumn = existingColumns.has(value as string) ? value : "id";
    const labelColumn = existingColumns.has(label as string) ? label : "name";

    // 둘 다 없으면 에러
    if (!existingColumns.has(valueColumn as string)) {
      return res.status(400).json({
        success: false,
        message: `테이블 "${tableName}"에 값 컬럼 "${value}"이 존재하지 않습니다.`,
      });
    }

    // label 컬럼이 없으면 value 컬럼을 label로도 사용
    const effectiveLabelColumn = existingColumns.has(labelColumn as string) ? labelColumn : valueColumn;

    // WHERE 조건 (멀티테넌시)
    const whereConditions: string[] = [];
    const params: any[] = [];
    let paramIndex = 1;

    if (companyCode !== "*" && existingColumns.has("company_code")) {
      whereConditions.push(`company_code = $${paramIndex}`);
      params.push(companyCode);
      paramIndex++;
    }

    // 필터 조건 적용
    paramIndex = applyFilters(
      filtersParam as string | undefined,
      existingColumns,
      whereConditions,
      params,
      paramIndex,
      tableName,
    );

    const whereClause = whereConditions.length > 0
      ? `WHERE ${whereConditions.join(" AND ")}`
      : "";

    // autoFill용 추가 컬럼 처리
    let extraColumns = "";
    if (fields && typeof fields === "string") {
      const requestedFields = fields.split(",").map((f) => f.trim()).filter(Boolean);
      const validExtra = requestedFields.filter(
        (f) => existingColumns.has(f) && f !== valueColumn && f !== effectiveLabelColumn
      );
      if (validExtra.length > 0) {
        extraColumns = ", " + validExtra.map((f) => `"${f}"`).join(", ");
      }
    }

    // 쿼리 실행 (최대 500개)
    const query = `
      SELECT ${valueColumn} as value, ${effectiveLabelColumn} as label${extraColumns}
      FROM ${tableName}
      ${whereClause}
      ORDER BY ${effectiveLabelColumn} ASC
      LIMIT 500
    `;

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

    logger.info("엔티티 옵션 조회 성공", {
      tableName,
      valueColumn,
      labelColumn: effectiveLabelColumn,
      companyCode,
      hasFilters: !!filtersParam,
      rowCount: result.rowCount,
      extraFields: extraColumns ? true : false,
    });

    res.json({
      success: true,
      data: result.rows,
    });
  } catch (error: any) {
    logger.error("엔티티 옵션 조회 오류", {
      error: error.message,
      stack: error.stack,
    });
    res.status(500).json({ success: false, message: error.message });
  }
}

/**
 * 엔티티 검색 API
 * GET /api/entity-search/:tableName
 */
export async function searchEntity(req: AuthenticatedRequest, res: Response) {
  try {
    const { tableName } = req.params;
    const {
      searchText = "",
      searchFields = "",
      filterCondition = "{}",
      page = "1",
      limit = "20",
    } = req.query;

    // tableName 유효성 검증
    if (!tableName || tableName === "undefined" || tableName === "null") {
      logger.warn("엔티티 검색 실패: 테이블명이 없음", { tableName });
      return res.status(400).json({
        success: false,
        message:
          "테이블명이 지정되지 않았습니다. 컴포넌트 설정에서 sourceTable을 확인해주세요.",
      });
    }

    // 멀티테넌시
    const companyCode = req.user!.companyCode;

    // 검색 필드 파싱
    const requestedFields = searchFields
      ? (searchFields as string).split(",").map((f) => f.trim())
      : [];

    // 🆕 테이블의 실제 컬럼 목록 조회
    const pool = getPool();
    const columnsResult = await pool.query(
      `SELECT column_name FROM information_schema.columns 
       WHERE table_schema = 'public' AND table_name = $1`,
      [tableName]
    );
    const existingColumns = new Set(columnsResult.rows.map((r: any) => r.column_name));

    // 🆕 존재하는 컬럼만 필터링
    const fields = requestedFields.filter((field) => {
      if (existingColumns.has(field)) {
        return true;
      } else {
        logger.warn(`엔티티 검색: 테이블 "${tableName}"에 컬럼 "${field}"이(가) 존재하지 않아 제외`);
        return false;
      }
    });

    const existingColumnsArray = Array.from(existingColumns);
    logger.info(`엔티티 검색 필드 확인 - 테이블: ${tableName}, 요청필드: [${requestedFields.join(", ")}], 유효필드: [${fields.join(", ")}], 테이블컬럼(샘플): [${existingColumnsArray.slice(0, 10).join(", ")}]`);

    // WHERE 조건 생성
    const whereConditions: string[] = [];
    const params: any[] = [];
    let paramIndex = 1;

    // 멀티테넌시 필터링
    if (companyCode !== "*") {
      // 🆕 company_code 컬럼이 있는 경우에만 필터링
      if (existingColumns.has("company_code")) {
        whereConditions.push(`company_code = $${paramIndex}`);
        params.push(companyCode);
        paramIndex++;
      }
    }

    // 검색 조건
    if (searchText) {
      // 유효한 검색 필드가 없으면 기본 텍스트 컬럼에서 검색
      let searchableFields = fields;
      if (searchableFields.length === 0) {
        // 기본 검색 컬럼: name, code, description 등 일반적인 컬럼명
        const defaultSearchColumns = [
          'name', 'code', 'description', 'title', 'label', 
          'item_name', 'item_code', 'item_number',
          'equipment_name', 'equipment_code', 
          'inspection_item', 'consumable_name', // 소모품명 추가
          'supplier_name', 'customer_name', 'product_name',
        ];
        searchableFields = defaultSearchColumns.filter(col => existingColumns.has(col));
        
        logger.info(`엔티티 검색: 기본 검색 필드 사용 - 테이블: ${tableName}, 검색필드: [${searchableFields.join(", ")}]`);
      }
      
      if (searchableFields.length > 0) {
        const searchConditions = searchableFields.map((field) => {
          const condition = `${field}::text ILIKE $${paramIndex}`;
          paramIndex++;
          return condition;
        });
        whereConditions.push(`(${searchConditions.join(" OR ")})`);

        // 검색어 파라미터 추가
        searchableFields.forEach(() => {
          params.push(`%${searchText}%`);
        });
      }
    }

    // 추가 필터 조건 (존재하는 컬럼만)
    // 지원 연산자: =, !=, >, <, >=, <=, in, notIn, like
    // 특수 키 형식: column__operator (예: division__in, name__like)
    const additionalFilter = JSON.parse(filterCondition as string);
    for (const [key, value] of Object.entries(additionalFilter)) {
      // 특수 키 형식 파싱: column__operator
      let columnName = key;
      let operator = "=";
      
      if (key.includes("__")) {
        const parts = key.split("__");
        columnName = parts[0];
        operator = parts[1] || "=";
      }
      
      if (!existingColumns.has(columnName)) {
        logger.warn("엔티티 검색: 필터 조건에 존재하지 않는 컬럼 제외", { tableName, key, columnName });
        continue;
      }
      
      // 연산자별 WHERE 조건 생성
      switch (operator) {
        case "=":
          whereConditions.push(`"${columnName}" = $${paramIndex}`);
          params.push(value);
          paramIndex++;
          break;
        case "!=":
          whereConditions.push(`"${columnName}" != $${paramIndex}`);
          params.push(value);
          paramIndex++;
          break;
        case ">":
          whereConditions.push(`"${columnName}" > $${paramIndex}`);
          params.push(value);
          paramIndex++;
          break;
        case "<":
          whereConditions.push(`"${columnName}" < $${paramIndex}`);
          params.push(value);
          paramIndex++;
          break;
        case ">=":
          whereConditions.push(`"${columnName}" >= $${paramIndex}`);
          params.push(value);
          paramIndex++;
          break;
        case "<=":
          whereConditions.push(`"${columnName}" <= $${paramIndex}`);
          params.push(value);
          paramIndex++;
          break;
        case "in":
          // IN 연산자: 값이 배열이거나 쉼표로 구분된 문자열
          const inValues = Array.isArray(value) ? value : String(value).split(",").map(v => v.trim());
          if (inValues.length > 0) {
            const placeholders = inValues.map((_, i) => `$${paramIndex + i}`).join(", ");
            whereConditions.push(`"${columnName}" IN (${placeholders})`);
            params.push(...inValues);
            paramIndex += inValues.length;
          }
          break;
        case "notIn":
          // NOT IN 연산자
          const notInValues = Array.isArray(value) ? value : String(value).split(",").map(v => v.trim());
          if (notInValues.length > 0) {
            const placeholders = notInValues.map((_, i) => `$${paramIndex + i}`).join(", ");
            whereConditions.push(`"${columnName}" NOT IN (${placeholders})`);
            params.push(...notInValues);
            paramIndex += notInValues.length;
          }
          break;
        case "like":
          whereConditions.push(`"${columnName}"::text ILIKE $${paramIndex}`);
          params.push(`%${value}%`);
          paramIndex++;
          break;
        default:
          // 알 수 없는 연산자는 등호로 처리
          whereConditions.push(`"${columnName}" = $${paramIndex}`);
          params.push(value);
          paramIndex++;
          break;
      }
    }

    // 페이징
    const offset = (parseInt(page as string) - 1) * parseInt(limit as string);
    const whereClause =
      whereConditions.length > 0
        ? `WHERE ${whereConditions.join(" AND ")}`
        : "";

    // 정렬 컬럼 결정: id가 있으면 id, 없으면 첫 번째 컬럼 사용
    let orderByColumn = "1"; // 기본: 첫 번째 컬럼
    if (existingColumns.has("id")) {
      orderByColumn = '"id"';
    } else {
      // PK 컬럼 조회 시도
      try {
        const pkResult = await pool.query(
          `SELECT a.attname
           FROM pg_index i
           JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
           WHERE i.indrelid = $1::regclass AND i.indisprimary
           ORDER BY array_position(i.indkey, a.attnum)
           LIMIT 1`,
          [tableName]
        );
        if (pkResult.rows.length > 0) {
          orderByColumn = `"${pkResult.rows[0].attname}"`;
        }
      } catch {
        // PK 조회 실패 시 기본값 유지
      }
    }

    // 쿼리 실행 (pool은 위에서 이미 선언됨)
    const countQuery = `SELECT COUNT(*) FROM ${tableName} ${whereClause}`;
    const dataQuery = `
      SELECT * FROM ${tableName} ${whereClause} 
      ORDER BY ${orderByColumn} DESC 
      LIMIT $${paramIndex} OFFSET $${paramIndex + 1}
    `;

    params.push(parseInt(limit as string));
    params.push(offset);

    const countResult = await pool.query(
      countQuery,
      params.slice(0, params.length - 2)
    );
    const dataResult = await pool.query(dataQuery, params);

    logger.info("엔티티 검색 성공", {
      tableName,
      searchText,
      companyCode,
      rowCount: dataResult.rowCount,
    });

    res.json({
      success: true,
      data: dataResult.rows,
      pagination: {
        total: parseInt(countResult.rows[0].count),
        page: parseInt(page as string),
        limit: parseInt(limit as string),
      },
    });
  } catch (error: any) {
    logger.error("엔티티 검색 오류", {
      error: error.message,
      stack: error.stack,
    });
    res.status(500).json({ success: false, message: error.message });
  }
}
