'use server'

import type { WebhooksPage, WebhooksQuery } from './types'
import { requireRole, UserRole } from '@admin/auth/middleware'
import db from '@admin/db'
import { webhookDeliveries, webhookSubscriptions, webhooks } from '@admin/db/schema'
import { getExclusiveDateUpperBound } from '@admin/utils/date/get-exclusive-date-upper-bound'
import { and, asc, count, desc, eq, gte, ilike, isNull, lt, or, sql } from 'drizzle-orm'

// Deliberately uncached: filters and sorting depend on delivery rows written
// inside after(), where cache invalidation is unavailable, so cached reads
// could never be refreshed.
export async function getWebhooks(query?: WebhooksQuery): Promise<WebhooksPage> {
  await requireRole([UserRole.ADMIN])

  try {
    const latestDeliveries = db
      .selectDistinctOn([webhookDeliveries.webhookId], {
        webhookId: webhookDeliveries.webhookId,
        success: webhookDeliveries.success,
        createdAt: webhookDeliveries.createdAt
      })
      .from(webhookDeliveries)
      .orderBy(webhookDeliveries.webhookId, desc(webhookDeliveries.createdAt))
      .as('latest_deliveries')
    const subscriptionCounts = db
      .select({
        webhookId: webhookSubscriptions.webhookId,
        eventCount: count().as('event_count')
      })
      .from(webhookSubscriptions)
      .groupBy(webhookSubscriptions.webhookId)
      .as('subscription_counts')
    const eventCount = sql<number>`coalesce(${subscriptionCounts.eventCount}, 0)`

    const conditions = []
    const search = query?.search
    if (search && typeof search === 'string' && search.trim()) {
      const searchTerm = `%${search.trim().toLowerCase()}%`
      conditions.push(or(ilike(webhooks.name, searchTerm), ilike(webhooks.url, searchTerm)))
    }
    if (query?.enabled !== undefined) {
      conditions.push(eq(webhooks.enabled, query.enabled))
    }
    if (query?.lastDelivery === 'delivered') {
      conditions.push(eq(latestDeliveries.success, true))
    }
    if (query?.lastDelivery === 'failed') {
      conditions.push(eq(latestDeliveries.success, false))
    }
    if (query?.lastDelivery === 'never') {
      conditions.push(isNull(latestDeliveries.webhookId))
    }
    if (query?.createdAtFrom) {
      conditions.push(gte(webhooks.createdAt, query.createdAtFrom))
    }
    const createdAtExclusiveTo = getExclusiveDateUpperBound(query?.createdAtTo)
    if (createdAtExclusiveTo) {
      conditions.push(lt(webhooks.createdAt, createdAtExclusiveTo))
    }

    const baseQuery = db
      .select({
        id: webhooks.id,
        name: webhooks.name,
        url: webhooks.url,
        enabled: webhooks.enabled,
        createdAt: webhooks.createdAt,
        updatedAt: webhooks.updatedAt,
        eventCount,
        lastDeliverySuccess: latestDeliveries.success,
        lastDeliveryAt: latestDeliveries.createdAt
      })
      .from(webhooks)
      .leftJoin(subscriptionCounts, eq(subscriptionCounts.webhookId, webhooks.id))
      .leftJoin(latestDeliveries, eq(latestDeliveries.webhookId, webhooks.id))

    const sortableColumns = {
      name: webhooks.name,
      url: webhooks.url,
      enabled: webhooks.enabled,
      createdAt: webhooks.createdAt,
      updatedAt: webhooks.updatedAt,
      eventCount,
      lastDeliveryAt: latestDeliveries.createdAt
    } as const
    const sortColumn =
      query?.orderBy && query.orderBy in sortableColumns
        ? sortableColumns[query.orderBy as keyof typeof sortableColumns]
        : webhooks.createdAt
    const sortFn = query?.orderDirection === 'desc' ? desc : asc
    const orderClause = sortFn(sortColumn)

    const orderedQuery =
      conditions.length > 0
        ? baseQuery.where(and(...conditions)).orderBy(orderClause)
        : baseQuery.orderBy(orderClause)

    const rawLimit = query?.limit
    const limit =
      typeof rawLimit === 'number'
        ? Number.isFinite(rawLimit)
          ? Math.max(1, rawLimit)
          : 1
        : undefined
    const rawOffset = query?.offset
    const offset =
      typeof rawOffset === 'number' && Number.isFinite(rawOffset) ? Math.max(0, rawOffset) : 0
    const isPaginated = typeof limit === 'number'
    const results = isPaginated
      ? await (offset > 0 ? orderedQuery.limit(limit).offset(offset) : orderedQuery.limit(limit))
      : await orderedQuery

    const total = isPaginated
      ? Number(
          (
            await db
              .select({ count: sql`count(*)` })
              .from(webhooks)
              .leftJoin(subscriptionCounts, eq(subscriptionCounts.webhookId, webhooks.id))
              .leftJoin(latestDeliveries, eq(latestDeliveries.webhookId, webhooks.id))
              .where(conditions.length > 0 ? and(...conditions) : undefined)
          )[0]?.count ?? 0
        )
      : results.length

    return {
      webhooks: results.map((row) => ({
        id: row.id,
        name: row.name,
        url: row.url,
        enabled: row.enabled,
        createdAt: row.createdAt,
        updatedAt: row.updatedAt,
        eventCount: Number(row.eventCount ?? 0),
        lastDeliverySuccess: row.lastDeliverySuccess ?? null,
        lastDeliveryAt: row.lastDeliveryAt ?? null
      })),
      total
    }
  } catch (error) {
    console.error('Error fetching webhooks:', error)
    throw new Error('Failed to fetch webhooks')
  }
}