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

// Helper to detect if value is empty or undefined
const isEmpty = (value: any) => value === undefined || value === null || value === "";

export interface DrizzleTableState {
  sorting?: SortingState;
  columnFilters?: ColumnFiltersState;
  globalFilter?: string;
  pagination?: PaginationState;
  grouping?: GroupingState;
}

export class DrizzleTableAdapter<TData> {
  private columnMap: Map<string, PgColumn>;

  constructor(
    private table: PgTable | PgView,
    private columns: ColumnDef<TData, any>[]
  ) {
    // Create a map of accessorKey -> Drizzle Column for fast lookup
    this.columnMap = new Map();
    // @ts-ignore - getTableColumns works on views in newer drizzle versions or we can cast
    const drizzleColumns = getTableColumns(table as any); 
    
    columns.forEach(col => {
      // We currently only support accessorKey which maps directly to a DB column
      if ('accessorKey' in col && typeof col.accessorKey === 'string') {
        const dbCol = drizzleColumns[col.accessorKey];
        if (dbCol) {
          this.columnMap.set(col.accessorKey, dbCol);
        }
      }
    });
  }

  private getColumn(columnId: string): PgColumn | undefined {
    return this.columnMap.get(columnId);
  }

  /**
   * Build the WHERE clause based on column filters and global filter
   */
  getWhere(columnFilters?: ColumnFiltersState, globalFilter?: string): SQL | undefined {
    const conditions: SQL[] = [];

    // 1. Column Filters
    if (columnFilters) {
      for (const filter of columnFilters) {
        const column = this.getColumn(filter.id);
        if (!column) continue;

        const value = filter.value;
        if (isEmpty(value)) continue;

        // Handle Array (range or multiple select)
        if (Array.isArray(value)) {
          // Range filter (e.g. [min, max])
          if (value.length === 2 && (typeof value[0] === 'number' || typeof value[0] === 'string')) {
            const [min, max] = value;
            if (!isEmpty(min) && !isEmpty(max)) {
              conditions.push(and(gte(column, min), lte(column, max))!);
            } else if (!isEmpty(min)) {
              conditions.push(gte(column, min)!);
            } else if (!isEmpty(max)) {
              conditions.push(lte(column, max)!);
            }
          } 
          // Multi-select (IN)
          else if (value.length > 0) {
             conditions.push(inArray(column, value)!);
          }
        } 
        // Boolean
        else if (typeof value === 'boolean') {
          conditions.push(eq(column, value)!);
        } 
        // Number
        else if (typeof value === 'number') {
          conditions.push(eq(column, value)!);
        }
        // String (Search)
        else if (typeof value === 'string') {
          conditions.push(ilike(column, `%${value}%`)!);
        }
      }
    }

    // 2. Global Filter
    if (globalFilter) {
      const searchConditions: SQL[] = [];
      this.columnMap.forEach((column) => {
         // Implicitly supports only text-compatible columns for ilike
         // Drizzle might throw if type mismatch, so user should be aware
         searchConditions.push(ilike(column, `%${globalFilter}%`));
      });
      
      if (searchConditions.length > 0) {
        conditions.push(or(...searchConditions)!);
      }
    }

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

  /**
   * Build the ORDER BY clause
   */
  getOrderBy(sorting?: SortingState): SQL[] {
    if (!sorting || !sorting.length) return [];

    return sorting.map((sort) => {
      const column = this.getColumn(sort.id);
      if (!column) return null;
      return sort.desc ? desc(column) : asc(column);
    }).filter(Boolean) as SQL[];
  }

  /**
   * Build the GROUP BY clause
   */
  getGroupBy(grouping?: GroupingState): SQL[] {
    if (!grouping || !grouping.length) return [];
    
    return grouping.map(g => this.getColumn(g)).filter(Boolean) as SQL[];
  }

  /**
   * Get Limit and Offset
   */
  getPagination(pagination?: PaginationState) {
    if (!pagination) return { limit: 10, offset: 0 };
    return {
      limit: pagination.pageSize,
      offset: pagination.pageIndex * pagination.pageSize,
    };
  }

  /**
   * Helper to apply all state to a query builder.
   * Returns the modifier objects that can be passed to drizzle query builder.
   */
  getQueryParts(state: DrizzleTableState) {
    return {
      where: this.getWhere(state.columnFilters, state.globalFilter),
      orderBy: this.getOrderBy(state.sorting),
      groupBy: this.getGroupBy(state.grouping),
      ...this.getPagination(state.pagination)
    };
  }
}