/**
 * Paginated notifications query — notification-center.
 *
 * Provides cursor-paginated, filterable full notification history for the
 * GET /api/notifications/all endpoint. Uses the existing `notifications` table
 * and `notifications_inbox` index — no new schema.
 */
import { and, eq, isNull, desc, sql, inArray } from 'drizzle-orm'
import type { Db } from '../client'
import { notifications } from '../schema'

export interface NotificationsAllRow {
  id: string
  type: string
  titleKey: string
  bodyKey: string | null
  params: Record<string, unknown>
  entityType: string | null
  entityId: string | null
  readAt: Date | null
  createdAt: Date
}

export interface NotificationsAllResult {
  rows: NotificationsAllRow[]
  total: number
  unreadCount: number
  hasNextPage: boolean
}

export interface NotificationsAllInput {
  tenantId: string
  userId: string
  typeFilter?: string[] | null  // concrete NotificationType values; null = all
  unreadOnly: boolean
  cursorTs?: Date | null
  cursorId?: string | null
  limit: number
}

/**
 * Returns paginated notifications with total count and unread count.
 *
 * Cursor is composite (created_at DESC, id DESC): fetches rows strictly
 * before (cursorTs, cursorId) so there are no duplicates across pages.
 */
export async function getNotificationsAll(
  db: Db,
  input: NotificationsAllInput,
): Promise<NotificationsAllResult> {
  const { tenantId, userId, typeFilter, unreadOnly, cursorTs, cursorId, limit } = input

  // ── Build WHERE conditions ──────────────────────────────────────────────────
  const baseConditions = [
    eq(notifications.tenantId, tenantId),
    eq(notifications.userId, userId),
  ]

  if (typeFilter && typeFilter.length > 0) {
    baseConditions.push(inArray(notifications.type, typeFilter))
  }

  if (unreadOnly) {
    baseConditions.push(isNull(notifications.readAt))
  }

  // ── Paginated rows (limit + 1 to detect next page) ─────────────────────────
  const pageConditions = [...baseConditions]
  if (cursorTs && cursorId) {
    // Composite cursor: (created_at, id) < (cursorTs, cursorId) in DESC order
    pageConditions.push(
      sql`(${notifications.createdAt}, ${notifications.id}) < (${cursorTs}::timestamptz, ${cursorId}::uuid)`,
    )
  }

  const pageRows = await db
    .select({
      id: notifications.id,
      type: notifications.type,
      titleKey: notifications.titleKey,
      bodyKey: notifications.bodyKey,
      params: notifications.params,
      entityType: notifications.entityType,
      entityId: notifications.entityId,
      readAt: notifications.readAt,
      createdAt: notifications.createdAt,
    })
    .from(notifications)
    .where(and(...pageConditions))
    .orderBy(desc(notifications.createdAt), desc(notifications.id))
    .limit(limit + 1)

  const hasNextPage = pageRows.length > limit
  const rows = hasNextPage ? pageRows.slice(0, limit) : pageRows

  // ── Total count (same filters, no cursor/limit) ────────────────────────────
  const totalResult = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(notifications)
    .where(and(...baseConditions))

  const total = totalResult[0]?.count ?? 0

  // ── Unread count (ignores type/status filters) ─────────────────────────────
  const unreadResult = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(notifications)
    .where(
      and(
        eq(notifications.tenantId, tenantId),
        eq(notifications.userId, userId),
        isNull(notifications.readAt),
      ),
    )

  const unreadCount = unreadResult[0]?.count ?? 0

  return {
    rows: rows as NotificationsAllRow[],
    total,
    unreadCount,
    hasNextPage,
  }
}