import { 
  ColumnFiltersState, 
  SortingState, 
  PaginationState,
  GroupingState
} from "@tanstack/react-table";
import { 
  SQL, 
  and, 
  or, 
  eq, 
  ilike, 
  like, 
  gt, 
  lt, 
  gte, 
  lte, 
  inArray, 
  asc, 
  desc, 
  not,
  sql
} from "drizzle-orm";
import { PgTable } from "drizzle-orm/pg-core";

/**
 * Table State를 Drizzle Query 조건으로 변환하는 유틸리티
 */
export class TableQueryBuilder {
  private table: PgTable;
  private searchableColumns: string[];

  constructor(table: PgTable, searchableColumns: string[] = []) {
    this.table = table;
    this.searchableColumns = searchableColumns;
  }

  /**
   * Pagination State -> Limit/Offset
   */
  getPagination(pagination: PaginationState) {
    return {
      limit: pagination.pageSize,
      offset: pagination.pageIndex * pagination.pageSize,
    };
  }

  /**
   * Sorting State -> Order By
   */
  getOrderBy(sorting: SortingState) {
    if (!sorting.length) return [];

    return sorting.map((sort) => {
      // 컬럼 이름이 테이블에 존재하는지 확인
      const column = this.table[sort.id as keyof typeof this.table];
      if (!column) return null;

      return sort.desc ? desc(column) : asc(column);
    }).filter(Boolean) as SQL[];
  }

  /**
   * Column Filters -> Where Clause
   */
  getWhere(columnFilters: ColumnFiltersState, globalFilter?: string) {
    const conditions: SQL[] = [];

    // 1. Column Filters
    for (const filter of columnFilters) {
      const column = this.table[filter.id as keyof typeof this.table];
      if (!column) continue;

      const value = filter.value;

      // 값의 타입에 따라 적절한 연산자 선택 (기본적인 예시)
      if (Array.isArray(value)) {
        // 범위 필터 (예: 날짜, 숫자 범위)
        if (value.length === 2) {
          const [min, max] = value;
          if (min !== null && max !== null) {
            conditions.push(and(gte(column, min), lte(column, max))!);
          } else if (min !== null) {
            conditions.push(gte(column, min)!);
          } else if (max !== null) {
            conditions.push(lte(column, max)!);
          }
        } 
        // 다중 선택 (Select)
        else {
          conditions.push(inArray(column, value)!);
        }
      } else if (typeof value === 'string') {
        // 텍스트 검색 (Partial Match)
        conditions.push(ilike(column, `%${value}%`)!);
      } else if (typeof value === 'boolean') {
        conditions.push(eq(column, value)!);
      } else if (typeof value === 'number') {
        conditions.push(eq(column, value)!);
      }
    }

    // 2. Global Filter (검색창)
    if (globalFilter && this.searchableColumns.length > 0) {
      const searchConditions = this.searchableColumns.map(colName => {
        const column = this.table[colName as keyof typeof this.table];
        if (!column) return null;
        return ilike(column, `%${globalFilter}%`);
      }).filter(Boolean) as SQL[];

      if (searchConditions.length > 0) {
        conditions.push(or(...searchConditions)!);
      }
    }

    return conditions.length > 0 ? and(...conditions) : undefined;
  }

  /**
   * Grouping State -> Group By & Select
   * 주의: Group By 사용 시 집계 함수가 필요할 수 있음
   */
  getGroupBy(grouping: GroupingState) {
    return grouping.map(g => {
      const column = this.table[g as keyof typeof this.table];
      return column;
    }).filter(Boolean);
  }
}