import { isEmpty, isNotEmpty } from "@/db/utils"
import type { Filter, JoinOperator } from "@/types/table"
import { addDays, endOfDay, startOfDay } from "date-fns"
import {
  and,
  eq,
  gt,
  gte,
  ilike,
  inArray,
  lt,
  lte,
  ne,
  notIlike,
  notInArray,
  or,
  type AnyColumn,
  type SQL,
  type Table,
} from "drizzle-orm"
import type { PgTable, PgView } from "drizzle-orm/pg-core"

type TableOrView = PgTable | PgView<any>

// 조인된 테이블들의 타입 정의
export interface JoinedTables {
  [key: string]: TableOrView
}

// 커스텀 컬럼 매핑 타입 정의
export interface CustomColumnMapping {
  [filterId: string]: {
    table: TableOrView
    column: string
  } | AnyColumn
}

/**
 * Enhanced filterColumns function that supports joined tables and custom column mapping.
 * 
 * This function can handle filters that reference columns from different tables
 * in a joined query, and allows for custom mapping of filter IDs to actual table columns.
 */
export function filterColumns<T extends TableOrView>({
  table,
  filters,
  joinOperator,
  joinedTables,
  customColumnMapping,
}: {
  table: T
  filters: Filter<T>[]
  joinOperator: JoinOperator
  joinedTables?: JoinedTables
  customColumnMapping?: CustomColumnMapping
}): SQL | undefined {

  const joinFn = joinOperator === "and" ? and : or

  const conditions = filters.map((filter) => {
    const column = getColumnWithMapping(table, filter.id, joinedTables, customColumnMapping)
    
    if (!column) {
      console.warn(`Column not found for filter ID: ${filter.id}`)
      return undefined
    }

    switch (filter.operator) {
      case "eq":
        if (Array.isArray(filter.value)) {
          return inArray(column, filter.value)
        } else if (
          column.dataType === "boolean" &&
          typeof filter.value === "string"
        ) {
          return eq(column, filter.value === "true")
        } else if (filter.type === "date") {
          const date = new Date(filter.value)
          const start = startOfDay(date)
          const end = endOfDay(date)
          return and(gte(column, start), lte(column, end))
        } else {
          return eq(column, filter.value)
        }
      case "ne":
        if (Array.isArray(filter.value)) {
          return notInArray(column, filter.value)
        } else if (column.dataType === "boolean") {
          return ne(column, filter.value === "true")
        } else if (filter.type === "date") {
          const date = new Date(filter.value)
          const start = startOfDay(date)
          const end = endOfDay(date)
          return or(lt(column, start), gt(column, end))
        } else {
          return ne(column, filter.value)
        }
      case "iLike":
        return filter.type === "text" && typeof filter.value === "string"
          ? ilike(column, `%${filter.value}%`)
          : undefined
      case "notILike":
        return filter.type === "text" && typeof filter.value === "string"
          ? notIlike(column, `%${filter.value}%`)
          : undefined
      case "lt":
        return filter.type === "number"
          ? lt(column, filter.value)
          : filter.type === "date" && typeof filter.value === "string"
            ? lt(column, endOfDay(new Date(filter.value)))
            : undefined
      case "lte":
        return filter.type === "number"
          ? lte(column, filter.value)
          : filter.type === "date" && typeof filter.value === "string"
            ? lte(column, endOfDay(new Date(filter.value)))
            : undefined
      case "gt":
        return filter.type === "number"
          ? gt(column, filter.value)
          : filter.type === "date" && typeof filter.value === "string"
            ? gt(column, startOfDay(new Date(filter.value)))
            : undefined
      case "gte":
        return filter.type === "number"
          ? gte(column, filter.value)
          : filter.type === "date" && typeof filter.value === "string"
            ? gte(column, startOfDay(new Date(filter.value)))
            : undefined
      case "isBetween":
        return filter.type === "date" &&
          Array.isArray(filter.value) &&
          filter.value.length === 2
          ? and(
              filter.value[0]
                ? gte(column, startOfDay(new Date(filter.value[0])))
                : undefined,
              filter.value[1]
                ? lte(column, endOfDay(new Date(filter.value[1])))
                : undefined
            )
          : undefined
      case "isRelativeToToday":
        if (filter.type === "date" && typeof filter.value === "string") {
          const today = new Date()
          const [amount, unit] = filter.value.split(" ") ?? []
          let startDate: Date
          let endDate: Date

          if (!amount || !unit) return undefined

          switch (unit) {
            case "days":
              startDate = startOfDay(addDays(today, parseInt(amount)))
              endDate = endOfDay(startDate)
              break
            case "weeks":
              startDate = startOfDay(addDays(today, parseInt(amount) * 7))
              endDate = endOfDay(addDays(startDate, 6))
              break
            case "months":
              startDate = startOfDay(addDays(today, parseInt(amount) * 30))
              endDate = endOfDay(addDays(startDate, 29))
              break
            default:
              return undefined
          }

          return and(gte(column, startDate), lte(column, endDate))
        }
        return undefined
      case "isEmpty":
        return isEmpty(column)
      case "isNotEmpty":
        return isNotEmpty(column)

      default:
        throw new Error(`Unsupported operator: ${filter.operator}`)
    }
  })

  const validConditions = conditions.filter(
    (condition) => condition !== undefined
  )

  return validConditions.length > 0 ? joinFn(...validConditions) : undefined
}

/**
 * Enhanced column getter - 간단한 수정 버전
 */
function getColumnWithMapping<T extends TableOrView>(
  table: T,
  columnKey: keyof T | string,
  joinedTables?: JoinedTables,
  customColumnMapping?: CustomColumnMapping
): AnyColumn | undefined {
  
  // 1. 커스텀 매핑이 있는 경우 우선 확인
  if (customColumnMapping && columnKey in customColumnMapping) {
    const mapping = customColumnMapping[columnKey as string]
    
    if (typeof mapping === 'object' && 'dataType' in mapping) {
      return mapping as AnyColumn
    }
    
    if (typeof mapping === 'object' && 'table' in mapping && 'column' in mapping) {
      try {
        if (mapping.table && typeof mapping.column === 'string') {
          const column = (mapping.table as any)[mapping.column];
          if (column !== undefined) {
            return column as AnyColumn;
          }
        }
      } catch (error) {
        console.warn(`Failed to get column ${mapping.column} from table:`, error)
      }
    }
  }
  
  // 2. 메인 테이블에서 컬럼 찾기 - 수정된 부분
  if (typeof columnKey === 'string') {
    // ✅ in 연산자 대신 직접 접근해서 undefined 체크
    try {
      const column = (table as any)[columnKey];
      if (column !== undefined && column !== null) {
        return column as AnyColumn;
      }
    } catch (error) {
      // 직접 접근 실패 시 조용히 넘어감
    }
  } else {
    // keyof T 타입인 경우 기존 함수 사용
    try {
      return getColumn(table, columnKey);
    } catch (error) {
      // getColumn 실패 시 조용히 넘어감
    }
  }
  
  // 3. 조인된 테이블들에서 컬럼 찾기
  if (joinedTables && typeof columnKey === 'string') {
    for (const [tableName, joinedTable] of Object.entries(joinedTables)) {
      try {
        const column = (joinedTable as any)[columnKey];
        if (column !== undefined && column !== null) {
          return column as AnyColumn;
        }
      } catch (error) {
        // 조용히 넘어감
      }
    }
  }
  
  // 4. 컬럼을 찾지 못한 경우
  return undefined;
}

/**
 * Get table column (기존 함수 유지).
 */
export function getColumn<T extends TableOrView>(
  table: T,
  columnKey: keyof T
): AnyColumn {
  return table[columnKey] as AnyColumn
}

/**
 * Safe helper to get column from any table with string key
 */
export function getColumnSafe(table: TableOrView, columnKey: string): AnyColumn | undefined {
  try {
    if (columnKey in table) {
      return (table as any)[columnKey] as AnyColumn
    }
  } catch (error) {
    console.warn(`Failed to get column ${columnKey} from table:`, error)
  }
  return undefined
}

// 사용 예시를 위한 타입 정의
export type FilterColumnsParams<T extends TableOrView> = Parameters<typeof filterColumns<T>>[0]