/**
 * 데이터 필터 유틸리티
 * 프론트엔드의 DataFilterConfig를 SQL WHERE 절로 변환
 */

export interface ColumnFilter {
  id: string;
  columnName: string;
  operator: 
    | "equals" 
    | "not_equals" 
    | "in" 
    | "not_in" 
    | "contains" 
    | "starts_with" 
    | "ends_with" 
    | "is_null" 
    | "is_not_null"
    | "greater_than"
    | "less_than"
    | "greater_than_or_equal"
    | "less_than_or_equal"
    | "between"
    | "date_range_contains";
  value: string | string[];
  valueType: "static" | "category" | "code" | "dynamic";
  rangeConfig?: {
    startColumn: string;
    endColumn: string;
  };
}

export interface DataFilterConfig {
  enabled: boolean;
  filters: ColumnFilter[];
  matchType: "all" | "any"; // AND / OR
}

/**
 * DataFilterConfig를 SQL WHERE 조건과 파라미터로 변환
 * @param dataFilter 필터 설정
 * @param tableAlias 테이블 별칭 (예: "r", "t1") - 조인 쿼리에서 사용
 * @param startParamIndex 시작 파라미터 인덱스 (예: 1이면 $1부터 시작)
 * @returns { whereClause: string, params: any[] }
 */
export function buildDataFilterWhereClause(
  dataFilter: DataFilterConfig | undefined,
  tableAliasOrStartIndex?: string | number,
  startParamIndex: number = 1
): { whereClause: string; params: any[] } {
  if (!dataFilter || !dataFilter.enabled || !dataFilter.filters || dataFilter.filters.length === 0) {
    return { whereClause: "", params: [] };
  }

  // 파라미터 처리: 첫 번째 파라미터가 숫자면 startParamIndex, 문자열이면 tableAlias
  let tableAlias: string | undefined;
  let actualStartIndex: number;
  
  if (typeof tableAliasOrStartIndex === "number") {
    actualStartIndex = tableAliasOrStartIndex;
    tableAlias = undefined;
  } else if (typeof tableAliasOrStartIndex === "string") {
    tableAlias = tableAliasOrStartIndex;
    actualStartIndex = startParamIndex;
  } else {
    actualStartIndex = startParamIndex;
    tableAlias = undefined;
  }

  const conditions: string[] = [];
  const params: any[] = [];
  let paramIndex = actualStartIndex;

  // 테이블 별칭이 있으면 "alias."를 붙이고, 없으면 그냥 컬럼명만
  const getColumnRef = (colName: string) => {
    return tableAlias ? `${tableAlias}."${colName}"` : `"${colName}"`;
  };

  for (const filter of dataFilter.filters) {
    const { columnName, operator, value } = filter;

    if (!columnName) {
      continue; // 컬럼명이 없으면 스킵
    }

    const columnRef = getColumnRef(columnName);

    switch (operator) {
      case "equals":
        conditions.push(`${columnRef} = $${paramIndex}`);
        params.push(value);
        paramIndex++;
        break;

      case "not_equals":
        conditions.push(`${columnRef} != $${paramIndex}`);
        params.push(value);
        paramIndex++;
        break;

      case "in": {
        const inArr = Array.isArray(value) ? value : value != null && value !== "" ? [String(value)] : [];
        if (inArr.length > 0) {
          const placeholders = inArr.map((_, idx) => `$${paramIndex + idx}`).join(", ");
          conditions.push(`${columnRef} IN (${placeholders})`);
          params.push(...inArr);
          paramIndex += inArr.length;
        }
        break;
      }

      case "not_in": {
        const notInArr = Array.isArray(value) ? value : value != null && value !== "" ? [String(value)] : [];
        if (notInArr.length > 0) {
          const placeholders = notInArr.map((_, idx) => `$${paramIndex + idx}`).join(", ");
          conditions.push(`${columnRef} NOT IN (${placeholders})`);
          params.push(...notInArr);
          paramIndex += notInArr.length;
        }
        break;
      }

      case "contains":
        conditions.push(`${columnRef} LIKE $${paramIndex}`);
        params.push(`%${value}%`);
        paramIndex++;
        break;

      case "starts_with":
        conditions.push(`${columnRef} LIKE $${paramIndex}`);
        params.push(`${value}%`);
        paramIndex++;
        break;

      case "ends_with":
        conditions.push(`${columnRef} LIKE $${paramIndex}`);
        params.push(`%${value}`);
        paramIndex++;
        break;

      case "is_null":
        conditions.push(`${columnRef} IS NULL`);
        break;

      case "is_not_null":
        conditions.push(`${columnRef} IS NOT NULL`);
        break;

      case "greater_than":
        conditions.push(`${columnRef} > $${paramIndex}`);
        params.push(value);
        paramIndex++;
        break;

      case "less_than":
        conditions.push(`${columnRef} < $${paramIndex}`);
        params.push(value);
        paramIndex++;
        break;

      case "greater_than_or_equal":
        conditions.push(`${columnRef} >= $${paramIndex}`);
        params.push(value);
        paramIndex++;
        break;

      case "less_than_or_equal":
        conditions.push(`${columnRef} <= $${paramIndex}`);
        params.push(value);
        paramIndex++;
        break;

      case "between":
        if (Array.isArray(value) && value.length === 2) {
          conditions.push(`${columnRef} BETWEEN $${paramIndex} AND $${paramIndex + 1}`);
          params.push(value[0], value[1]);
          paramIndex += 2;
        }
        break;

      case "date_range_contains":
        // 날짜 범위 포함: start_date <= value <= end_date
        // filter.rangeConfig = { startColumn: "start_date", endColumn: "end_date" }
        // NULL 처리:
        // - start_date만 있고 end_date가 NULL이면: start_date <= value (이후 계속)
        // - end_date만 있고 start_date가 NULL이면: value <= end_date (이전 계속)
        // - 둘 다 있으면: start_date <= value <= end_date
        if (filter.rangeConfig && filter.rangeConfig.startColumn && filter.rangeConfig.endColumn) {
          const startCol = getColumnRef(filter.rangeConfig.startColumn);
          const endCol = getColumnRef(filter.rangeConfig.endColumn);
          
          // value가 "TODAY"면 현재 날짜로 변환
          const actualValue = filter.valueType === "dynamic" && value === "TODAY" 
            ? "CURRENT_DATE" 
            : `$${paramIndex}`;
          
          if (actualValue === "CURRENT_DATE") {
            // CURRENT_DATE는 파라미터가 아니므로 직접 SQL에 포함
            // NULL 처리: (start_date IS NULL OR start_date <= CURRENT_DATE) AND (end_date IS NULL OR end_date >= CURRENT_DATE)
            conditions.push(
              `((${startCol} IS NULL OR ${startCol} <= CURRENT_DATE) AND (${endCol} IS NULL OR ${endCol} >= CURRENT_DATE))`
            );
          } else {
            // NULL 처리: (start_date IS NULL OR start_date <= $param) AND (end_date IS NULL OR end_date >= $param)
            conditions.push(
              `((${startCol} IS NULL OR ${startCol} <= $${paramIndex}) AND (${endCol} IS NULL OR ${endCol} >= $${paramIndex}))`
            );
            params.push(value);
            paramIndex++;
          }
        }
        break;

      default:
        // 알 수 없는 연산자는 무시
        break;
    }
  }

  if (conditions.length === 0) {
    return { whereClause: "", params: [] };
  }

  // matchType에 따라 AND / OR 조합
  const logicalOperator = dataFilter.matchType === "any" ? " OR " : " AND ";
  const whereClause = `(${conditions.join(logicalOperator)})`;

  return { whereClause, params };
}

/**
 * 기존 WHERE 절에 dataFilter 조건을 추가
 * @param existingWhere 기존 WHERE 절 (예: "company_code = $1")
 * @param existingParams 기존 파라미터 배열
 * @param dataFilter 필터 설정
 * @returns { whereClause: string, params: any[] }
 */
export function appendDataFilterToWhere(
  existingWhere: string,
  existingParams: any[],
  dataFilter: DataFilterConfig | undefined
): { whereClause: string; params: any[] } {
  const { whereClause: filterWhere, params: filterParams } = buildDataFilterWhereClause(
    dataFilter,
    existingParams.length + 1
  );

  if (!filterWhere) {
    return { whereClause: existingWhere, params: existingParams };
  }

  const newWhere = existingWhere ? `${existingWhere} AND ${filterWhere}` : filterWhere;
  const newParams = [...existingParams, ...filterParams];

  return { whereClause: newWhere, params: newParams };
}

