import { SQL, and, or, eq, gte, lte, like, inArray } from "drizzle-orm";

/**
 * Sort direction for database queries
 */
export type SortDirection = "asc" | "desc";

/**
 * Filter operator types
 */
export type FilterOperator =
  | "eq" // equals
  | "ne" // not equals
  | "gt" // greater than
  | "gte" // greater than or equal
  | "lt" // less than
  | "lte" // less than or equal
  | "like" // contains (case-insensitive)
  | "in" // in array
  | "between"; // between two values

/**
 * Single filter condition
 */
export interface FilterCondition {
  field: string;
  operator: FilterOperator;
  value: string | number | boolean | string[] | number[];
}

/**
 * Filter group with AND/OR logic
 */
export interface FilterGroup {
  conditions: FilterCondition[];
  logic?: "and" | "or";
}

/**
 * Sort configuration
 */
export interface SortConfig {
  field: string;
  direction: SortDirection;
}

/**
 * Parse sort query parameter
 * Format: "field:asc" or "field:desc" or "-field" (shorthand for desc)
 *
 * @example
 * parseSortParam("name:asc") // { field: "name", direction: "asc" }
 * parseSortParam("-createdAt") // { field: "createdAt", direction: "desc" }
 */
export function parseSortParam(sort: string | null): SortConfig | null {
  if (!sort) return null;

  // Handle shorthand: "-field" means descending
  if (sort.startsWith("-")) {
    return {
      field: sort.slice(1),
      direction: "desc",
    };
  }

  // Handle explicit format: "field:asc" or "field:desc"
  const [field, direction] = sort.split(":");
  if (!field) return null;

  return {
    field,
    direction: (direction as SortDirection) || "asc",
  };
}

/**
 * Parse filter query parameter
 * Format: "field:operator:value" or multiple separated by comma
 *
 * @example
 * parseFilterParam("role:eq:client") // Single filter
 * parseFilterParam("role:in:client,trainer") // Array filter
 * parseFilterParam("createdAt:gte:2024-01-01") // Date filter
 */
export function parseFilterParam(
  filter: string | string[] | null,
): FilterCondition[] {
  if (!filter) return [];

  const filters = Array.isArray(filter) ? filter : [filter];
  const conditions: FilterCondition[] = [];

  for (const f of filters) {
    const parts = f.split(":");
    if (parts.length < 3) continue;

    const [field, operator, ...valueParts] = parts;
    const value = valueParts.join(":"); // Rejoin in case value contains ":"

    // Handle array values (comma-separated)
    if (operator === "in") {
      conditions.push({
        field,
        operator: operator as FilterOperator,
        value: value.split(","),
      });
      continue;
    }

    // Handle between operator (two values separated by comma)
    if (operator === "between") {
      const [start, end] = value.split(",");
      conditions.push({
        field,
        operator: operator as FilterOperator,
        value: [start, end],
      });
      continue;
    }

    // Handle single values
    conditions.push({
      field,
      operator: operator as FilterOperator,
      value,
    });
  }

  return conditions;
}

/**
 * Build SQL WHERE conditions from filter conditions
 * This is a helper that works with Drizzle ORM
 *
 * @param conditions - Array of filter conditions
 * @param columnMap - Map of field names to Drizzle column objects
 * @param logic - AND or OR logic (default: AND)
 * @returns SQL condition or undefined
 *
 * @example
 * const where = buildWhereConditions(
 *   [{ field: "role", operator: "eq", value: "client" }],
 *   { role: users.role, email: users.email }
 * )
 */
export function buildWhereConditions(
  conditions: FilterCondition[],
  columnMap: Record<string, any>, // eslint-disable-line @typescript-eslint/no-explicit-any
  logic: "and" | "or" = "and",
): SQL | undefined {
  if (conditions.length === 0) return undefined;

  const sqlConditions: SQL[] = [];

  for (const condition of conditions) {
    const column = columnMap[condition.field];
    if (!column) continue; // Skip unknown fields

    switch (condition.operator) {
      case "eq":
        sqlConditions.push(eq(column, condition.value as any));
        break;
      case "ne":
        // Note: Drizzle doesn't have ne(), use not(eq())
        // For now we'll skip this or implement custom
        break;
      case "gt":
        sqlConditions.push(gte(column, condition.value as any));
        break;
      case "gte":
        sqlConditions.push(gte(column, condition.value as any));
        break;
      case "lt":
        sqlConditions.push(lte(column, condition.value as any));
        break;
      case "lte":
        sqlConditions.push(lte(column, condition.value as any));
        break;
      case "like":
        sqlConditions.push(like(column, `%${condition.value}%`));
        break;
      case "in":
        if (Array.isArray(condition.value)) {
          sqlConditions.push(inArray(column, condition.value as any[]));
        }
        break;
      case "between":
        if (Array.isArray(condition.value) && condition.value.length === 2) {
          sqlConditions.push(
            and(
              gte(column, condition.value[0] as any),
              lte(column, condition.value[1] as any),
            )!,
          );
        }
        break;
    }
  }

  if (sqlConditions.length === 0) return undefined;

  return logic === "and" ? and(...sqlConditions) : or(...sqlConditions);
}

/**
 * Validate sort field against allowed fields
 */
export function validateSortField(
  field: string,
  allowedFields: string[],
): boolean {
  return allowedFields.includes(field);
}

/**
 * Validate filter fields against allowed fields
 */
export function validateFilterFields(
  conditions: FilterCondition[],
  allowedFields: string[],
): boolean {
  return conditions.every((c) => allowedFields.includes(c.field));
}

/**
 * Parse search query parameter for full-text search
 * This is a simple implementation that searches across multiple fields
 *
 * @param search - Search query string
 * @param searchFields - Fields to search in
 * @param columnMap - Map of field names to Drizzle column objects
 * @returns SQL condition or undefined
 */
export function buildSearchCondition(
  search: string | null,
  searchFields: string[],
  columnMap: Record<string, any>, // eslint-disable-line @typescript-eslint/no-explicit-any
): SQL | undefined {
  if (!search || !search.trim()) return undefined;

  const searchTerm = search.trim();
  const conditions: SQL[] = [];

  for (const field of searchFields) {
    const column = columnMap[field];
    if (!column) continue;
    conditions.push(like(column, `%${searchTerm}%`));
  }

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