import { type SelectQueryBuilder, type ReferenceExpression, type OperandValueExpression, sql } from 'kysely'

type ColumnsOf<DB, T extends keyof DB> = T extends any ? keyof DB[T] & string : never

type AppearsIn<DB, TB extends keyof DB, Col extends string> = {
  [T in TB]: Col extends ColumnsOf<DB, T> ? T : never
}[TB]

type IsUnion<T, U = T> = T extends any ? ([U] extends [T] ? false : true) : never

type UniqueColumn<DB, TB extends keyof DB> = {
  [C in ColumnsOf<DB, TB>]: IsUnion<AppearsIn<DB, TB, C>> extends true ? never : C
}[ColumnsOf<DB, TB>]

type PrefixedColumn<DB, TB extends keyof DB> = TB extends any ? `${TB & string}.${keyof DB[TB] & string}` : never

type UnambiguousColumn<DB, TB extends keyof DB> = PrefixedColumn<DB, TB> | UniqueColumn<DB, TB>

type WhereInput<DB, TB extends keyof DB> = Partial<{
  [C in UnambiguousColumn<DB, TB>]:
    | OperandValueExpression<DB, TB, any>
    | readonly OperandValueExpression<DB, TB, any>[]
    | null
}>

type WhereCapable<DB, TB extends keyof DB, Self> = {
  where(lhs: ReferenceExpression<DB, TB>, op: any, rhs: any): Self
}

export function applyWhere<DB, TB extends keyof DB, QB extends WhereCapable<DB, TB, QB>>(
  builder: QB,
  where: WhereInput<DB, TB>,
): QB {
  return Object.entries(where).reduce((builder, [key, value]) => {
    const column = key as ReferenceExpression<DB, TB>

    if (value === null) {
      return builder.where(column, 'is', null)
    }

    if (Array.isArray(value)) {
      return builder.where(column, 'in', value)
    }

    if (value === undefined) {
      return builder
    }

    return builder.where(column, '=', value)
  }, builder)
}

interface PaginationInput<DB, TB extends keyof DB> {
  where: WhereInput<DB, TB>
  limit: number
  offset: number
  sort?: UnambiguousColumn<DB, TB> | `-${UnambiguousColumn<DB, TB>}`
}

export function applyPagination<DB, TB extends keyof DB, O>(
  builder: SelectQueryBuilder<DB, TB, O>,
  { limit, offset, sort }: PaginationInput<DB, TB>,
): SelectQueryBuilder<DB, TB, O> {
  let qb = builder.limit(limit).offset(offset)

  if (sort) {
    const columnName = (sort.startsWith('-') ? sort.slice(1) : sort) as UnambiguousColumn<DB, TB>

    qb = qb.orderBy(columnName, columnName === sort ? 'asc' : 'desc')
  }

  return qb
}

export async function paginate<DB, TB extends keyof DB, O>(
  baseDataQuery: SelectQueryBuilder<DB, TB, O>,
  baseCountQuery: SelectQueryBuilder<DB, TB, unknown>,
  input: PaginationInput<DB, TB>,
) {
  const dataQuery = applyPagination(input.where ? applyWhere(baseDataQuery, input.where) : baseDataQuery, input)

  let countQuery = baseCountQuery.select(sql<number>`count(*)::int`.as('totalCount'))

  if (input.where) {
    countQuery = applyWhere(countQuery, input.where)
  }

  const [countRow, data] = await Promise.all([countQuery.executeTakeFirstOrThrow(), dataQuery.execute()])

  return {
    data,
    meta: {
      totalCount: (countRow as { totalCount: number }).totalCount,
      limit: input.limit,
      offset: input.offset,
      count: data.length,
    },
  }
}