import { sql, asc, desc, ilike, or, type SQL, type Column } from 'drizzle-orm'
import type { PgSelect } from 'drizzle-orm/pg-core'
import type { PaginatedResponse } from '@lunarfront/shared/schemas'

/**
 * Apply pagination (offset + limit) to a Drizzle query.
 */
export function withPagination<T extends PgSelect>(qb: T, page: number, limit: number) {
  return qb.limit(limit).offset((page - 1) * limit)
}

/**
 * Apply sorting to a Drizzle query.
 * `sortableColumns` maps query param names to actual Drizzle columns.
 */
export function withSort<T extends PgSelect>(
  qb: T,
  sortField: string | undefined,
  order: 'asc' | 'desc',
  sortableColumns: Record<string, Column>,
  defaultSort: Column,
) {
  const column = sortField ? sortableColumns[sortField] : defaultSort
  if (!column) return qb.orderBy(order === 'desc' ? desc(defaultSort) : asc(defaultSort))
  return qb.orderBy(order === 'desc' ? desc(column) : asc(column))
}

/**
 * Build an ilike search condition across multiple columns.
 */
export function buildSearchCondition(query: string, columns: Column[]): SQL | undefined {
  if (!query || columns.length === 0) return undefined
  const pattern = `%${query}%`
  const conditions = columns.map((col) => ilike(col, pattern))
  return conditions.length === 1 ? conditions[0] : or(...conditions)
}

/**
 * Get total count for a table with optional where condition.
 */
export async function getCount(
  db: { execute: (q: SQL) => Promise<{ rows: Record<string, unknown>[] }> },
  tableName: string,
  whereCondition?: SQL,
): Promise<number> {
  const countQuery = whereCondition
    ? sql`SELECT count(*)::int as count FROM ${sql.identifier(tableName)} WHERE ${whereCondition}`
    : sql`SELECT count(*)::int as count FROM ${sql.identifier(tableName)}`

  const result = await db.execute(countQuery)
  return (result.rows?.[0]?.count as number) ?? 0
}

/**
 * Build a standard paginated response.
 */
export function paginatedResponse<T>(
  data: T[],
  total: number,
  page: number,
  limit: number,
): PaginatedResponse<T> {
  return {
    data,
    pagination: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
    },
  }
}
