/**
 * Webhook query helpers — webhook-endpoint-detail (wave-11 leaf-D).
 *
 * All helpers are tenant-scoped: every statement carries a tenant_id WHERE clause.
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 */
import { and, desc, eq, gte, lte, sql } from 'drizzle-orm'
import { z } from 'zod'
import type { Db } from '../client'
import {
  webhookEndpoints,
  webhookDeliveries,
  webhookDeliveryLog,
  tenantApiKeys,
} from '../schema/webhooks'
import { auditLog } from '../schema/audit-log'

// ── Domain types ──────────────────────────────────────────────────────────────

export interface WebhookEndpointObject {
  id: string
  tenant_id: string
  url: string
  events: string[]
  is_active: boolean
  description: string | null
  created_at: string
  updated_at: string
}

export interface WebhookDeliveryObject {
  id: string
  tenant_id: string
  endpoint_id: string
  event_type: string
  status: string
  request_body: string | null
  response_status: number | null
  response_body: string | null
  latency_ms: number | null
  attempt: number
  created_at: string
}

export interface ListWebhookDeliveriesOptions {
  status?: string
  from?: string
  to?: string
  page?: number
  limit?: number
}

export interface WebhookDeliveryLogObject {
  id: string
  tenant_id: string | null
  endpoint_id: string | null
  source: string
  source_ip: string | null
  event_type: string | null
  status: string
  signature_valid: boolean
  created_at: string
}

export interface TenantApiKeyObject {
  id: string
  tenant_id: string
  name: string
  key_prefix: string
  scopes: string[]
  expires_at: string | null
  last_used_at: string | null
  created_by: string
  revoked_at: string | null
  created_at: string
  monthly_quota?: number | null
}

// ── Validation schemas ────────────────────────────────────────────────────────

export const createWebhookSchema = z.object({
  url: z.string().url().refine((u) => u.startsWith('https://'), {
    message: 'URL must use HTTPS',
  }),
  events: z.array(z.string()).optional().default([]),
  description: z.string().max(500).optional(),
})

export const updateWebhookSchema = z.object({
  url: z
    .string()
    .url()
    .refine((u) => u.startsWith('https://'), { message: 'URL must use HTTPS' })
    .optional(),
  events: z.array(z.string()).optional(),
  is_active: z.boolean().optional(),
  description: z.string().max(500).nullable().optional(),
})

export type CreateWebhookInput = z.infer<typeof createWebhookSchema>
export type UpdateWebhookInput = z.infer<typeof updateWebhookSchema>

// ── Serializers ───────────────────────────────────────────────────────────────

function serializeEndpoint(row: typeof webhookEndpoints.$inferSelect): WebhookEndpointObject {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    url: row.url,
    events: (row.events as string[]) ?? [],
    is_active: row.isActive,
    description: row.description ?? null,
    created_at: row.createdAt.toISOString(),
    updated_at: row.updatedAt.toISOString(),
  }
}

function serializeDelivery(row: typeof webhookDeliveries.$inferSelect): WebhookDeliveryObject {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    endpoint_id: row.endpointId,
    event_type: row.eventType,
    status: row.status,
    request_body: row.requestBody ?? null,
    response_status: row.responseStatus ?? null,
    response_body: row.responseBody ?? null,
    latency_ms: row.latencyMs ?? null,
    attempt: row.attempt,
    created_at: row.createdAt.toISOString(),
  }
}

function serializeLog(row: typeof webhookDeliveryLog.$inferSelect): WebhookDeliveryLogObject {
  return {
    id: row.id,
    tenant_id: row.tenantId ?? null,
    endpoint_id: row.endpointId ?? null,
    source: row.source,
    source_ip: row.sourceIp ?? null,
    event_type: row.eventType ?? null,
    status: row.status,
    signature_valid: row.signatureValid,
    created_at: row.createdAt.toISOString(),
  }
}

function serializeApiKey(row: typeof tenantApiKeys.$inferSelect): TenantApiKeyObject {
  return {
    id: row.id,
    tenant_id: row.tenantId,
    name: row.name,
    key_prefix: row.keyPrefix,
    scopes: (row.scopes as string[]) ?? [],
    expires_at: row.expiresAt?.toISOString() ?? null,
    last_used_at: row.lastUsedAt?.toISOString() ?? null,
    created_by: row.createdBy,
    revoked_at: row.revokedAt?.toISOString() ?? null,
    created_at: row.createdAt.toISOString(),
    monthly_quota: row.monthlyQuota ?? null,
  }
}

// ── Endpoint helpers ──────────────────────────────────────────────────────────

export async function listWebhookEndpoints(
  db: Db,
  tenantId: string,
): Promise<WebhookEndpointObject[]> {
  const rows = await db
    .select()
    .from(webhookEndpoints)
    .where(eq(webhookEndpoints.tenantId, tenantId))
    .orderBy(desc(webhookEndpoints.createdAt))
  return rows.map(serializeEndpoint)
}

export async function getWebhookEndpoint(
  db: Db,
  tenantId: string,
  id: string,
): Promise<WebhookEndpointObject | null> {
  const [row] = await db
    .select()
    .from(webhookEndpoints)
    .where(and(eq(webhookEndpoints.tenantId, tenantId), eq(webhookEndpoints.id, id)))
  return row ? serializeEndpoint(row) : null
}

export async function getWebhookEndpointWithSecret(
  db: Db,
  tenantId: string,
  id: string,
): Promise<(typeof webhookEndpoints.$inferSelect) | null> {
  const [row] = await db
    .select()
    .from(webhookEndpoints)
    .where(and(eq(webhookEndpoints.tenantId, tenantId), eq(webhookEndpoints.id, id)))
  return row ?? null
}

export async function createWebhookEndpoint(
  db: Db,
  tenantId: string,
  data: CreateWebhookInput & { secretEncrypted: string },
): Promise<WebhookEndpointObject> {
  const [row] = await db
    .insert(webhookEndpoints)
    .values({
      tenantId,
      url: data.url,
      events: data.events ?? [],
      secretEncrypted: data.secretEncrypted,
      description: data.description ?? null,
    })
    .returning()
  return serializeEndpoint(row!)
}

export async function updateWebhookEndpoint(
  db: Db,
  tenantId: string,
  id: string,
  data: UpdateWebhookInput,
): Promise<WebhookEndpointObject | null> {
  const updates: Partial<typeof webhookEndpoints.$inferInsert> = {}
  if (data.url !== undefined) updates.url = data.url
  if (data.events !== undefined) updates.events = data.events
  if (data.is_active !== undefined) updates.isActive = data.is_active
  if (data.description !== undefined) updates.description = data.description
  updates.updatedAt = new Date()

  const [row] = await db
    .update(webhookEndpoints)
    .set(updates)
    .where(and(eq(webhookEndpoints.tenantId, tenantId), eq(webhookEndpoints.id, id)))
    .returning()
  return row ? serializeEndpoint(row) : null
}

export async function updateWebhookSecret(
  db: Db,
  tenantId: string,
  id: string,
  secretEncrypted: string,
): Promise<boolean> {
  const result = await db
    .update(webhookEndpoints)
    .set({ secretEncrypted, updatedAt: new Date() })
    .where(and(eq(webhookEndpoints.tenantId, tenantId), eq(webhookEndpoints.id, id)))
  return (result.length ?? 0) > 0
}

export async function deleteWebhookEndpoint(
  db: Db,
  tenantId: string,
  id: string,
): Promise<boolean> {
  const result = await db
    .delete(webhookEndpoints)
    .where(and(eq(webhookEndpoints.tenantId, tenantId), eq(webhookEndpoints.id, id)))
  return (result.length ?? 0) > 0
}

// ── Delivery helpers ──────────────────────────────────────────────────────────

export async function listWebhookDeliveries(
  db: Db,
  tenantId: string,
  endpointId: string,
  options: ListWebhookDeliveriesOptions = {},
): Promise<WebhookDeliveryObject[]> {
  const limit = options.limit ?? 50
  const page = options.page ?? 1
  const offset = (page - 1) * limit
  const filters = [
    eq(webhookDeliveries.tenantId, tenantId),
    eq(webhookDeliveries.endpointId, endpointId),
  ]

  if (options.status) filters.push(eq(webhookDeliveries.status, options.status))
  if (options.from) filters.push(gte(webhookDeliveries.createdAt, new Date(options.from)))
  if (options.to) filters.push(lte(webhookDeliveries.createdAt, new Date(options.to)))

  const rows = await db
    .select()
    .from(webhookDeliveries)
    .where(and(...filters))
    .orderBy(desc(webhookDeliveries.createdAt))
    .limit(limit)
    .offset(offset)
  return rows.map(serializeDelivery)
}

export async function getWebhookDelivery(
  db: Db,
  tenantId: string,
  deliveryId: string,
): Promise<WebhookDeliveryObject | null> {
  const [row] = await db
    .select()
    .from(webhookDeliveries)
    .where(
      and(
        eq(webhookDeliveries.tenantId, tenantId),
        eq(webhookDeliveries.id, deliveryId),
      ),
    )
  return row ? serializeDelivery(row) : null
}

export async function insertWebhookDelivery(
  db: Db,
  values: Omit<typeof webhookDeliveries.$inferInsert, 'id' | 'createdAt'>,
): Promise<WebhookDeliveryObject> {
  const [row] = await db.insert(webhookDeliveries).values(values).returning()
  return serializeDelivery(row!)
}

export async function updateWebhookDelivery(
  db: Db,
  tenantId: string,
  id: string,
  updates: Partial<
    Pick<
      typeof webhookDeliveries.$inferInsert,
      'status' | 'responseStatus' | 'responseBody' | 'latencyMs'
    >
  >,
): Promise<void> {
  await db
    .update(webhookDeliveries)
    .set(updates)
    .where(and(eq(webhookDeliveries.tenantId, tenantId), eq(webhookDeliveries.id, id)))
}

// ── Inbound log helpers ───────────────────────────────────────────────────────

export async function insertWebhookDeliveryLog(
  db: Db,
  values: Omit<typeof webhookDeliveryLog.$inferInsert, 'id' | 'createdAt'>,
): Promise<void> {
  await db.insert(webhookDeliveryLog).values(values)
}

export async function listWebhookDeliveryLogs(
  db: Db,
  tenantId: string,
  limit = 50,
  offset = 0,
): Promise<WebhookDeliveryLogObject[]> {
  const rows = await db
    .select()
    .from(webhookDeliveryLog)
    .where(eq(webhookDeliveryLog.tenantId, tenantId))
    .orderBy(desc(webhookDeliveryLog.createdAt))
    .limit(limit)
    .offset(offset)
  return rows.map(serializeLog)
}

// ── Retention helpers ─────────────────────────────────────────────────────────

export async function deleteOldWebhookDeliveryLogs(
  db: Db,
  retentionDays: number,
): Promise<number> {
  const cutoff = new Date(Date.now() - retentionDays * 24 * 60 * 60 * 1000)
  const result = await db
    .delete(webhookDeliveryLog)
    .where(sql`${webhookDeliveryLog.createdAt} < ${cutoff.toISOString()}`)
  return result.length ?? 0
}

// ── API key helpers ───────────────────────────────────────────────────────────

export async function getApiKeyByHash(
  db: Db,
  keyHash: string,
): Promise<(typeof tenantApiKeys.$inferSelect) | null> {
  const [row] = await db
    .select()
    .from(tenantApiKeys)
    .where(and(eq(tenantApiKeys.keyHash, keyHash), sql`${tenantApiKeys.revokedAt} IS NULL`))
  return row ?? null
}

export async function listTenantApiKeys(
  db: Db,
  tenantId: string,
): Promise<TenantApiKeyObject[]> {
  const rows = await db
    .select()
    .from(tenantApiKeys)
    .where(eq(tenantApiKeys.tenantId, tenantId))
    .orderBy(desc(tenantApiKeys.createdAt))
  return rows.map(serializeApiKey)
}

export async function createTenantApiKey(
  db: Db,
  tenantId: string,
  actorId: string,
  data: {
    name: string
    keyPrefix: string
    keyHash: string
    scopes: string[]
    expiresAt?: Date | null
  },
): Promise<TenantApiKeyObject> {
  const [row] = await db
    .insert(tenantApiKeys)
    .values({
      tenantId,
      name: data.name,
      keyPrefix: data.keyPrefix,
      keyHash: data.keyHash,
      scopes: data.scopes,
      expiresAt: data.expiresAt ?? null,
      createdBy: actorId,
    })
    .returning()

  await db.insert(auditLog).values({
    tenantId,
    actorId,
    actorType: 'user',
    entityType: 'tenant_api_key',
    entityId: row!.id,
    action: 'api_key.created',
    changes: {
      name: [null, data.name],
      scopes: [null, data.scopes],
    },
  })

  return serializeApiKey(row!)
}

export async function revokeTenantApiKey(
  db: Db,
  tenantId: string,
  id: string,
  actorId: string,
): Promise<boolean> {
  const existing = await getApiKeyForTenant(db, tenantId, id)
  if (!existing) return false

  const revokedAt = new Date()

  await db.transaction(async (tx) => {
    await tx
      .update(tenantApiKeys)
      .set({ revokedAt })
      .where(
        and(
          eq(tenantApiKeys.id, id),
          eq(tenantApiKeys.tenantId, tenantId),
          sql`${tenantApiKeys.revokedAt} IS NULL`,
        ),
      )

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'tenant_api_key',
      entityId: id,
      action: 'api_key.revoked',
      changes: {
        revoked_at: [existing.revokedAt?.toISOString() ?? null, revokedAt.toISOString()],
      },
    })
  })

  return true
}

export async function updateApiKeyLastUsed(
  db: Db,
  tenantId: string,
  id: string,
): Promise<void> {
  await db
    .update(tenantApiKeys)
    .set({ lastUsedAt: new Date() })
    .where(and(eq(tenantApiKeys.tenantId, tenantId), eq(tenantApiKeys.id, id)))
}

// ── api-usage-quota-ui (wave-12) helpers ──────────────────────────────────────

/**
 * Fetch a single API key scoped to a tenant (for the in-app quota/usage routes).
 * Returns null if the key is not found or is revoked.
 */
export async function getApiKeyForTenant(
  db: Db,
  tenantId: string,
  keyId: string,
): Promise<(typeof tenantApiKeys.$inferSelect) | null> {
  const [row] = await db
    .select()
    .from(tenantApiKeys)
    .where(
      and(
        eq(tenantApiKeys.id, keyId),
        eq(tenantApiKeys.tenantId, tenantId),
        sql`${tenantApiKeys.revokedAt} IS NULL`,
      ),
    )
  return row ?? null
}

/**
 * Update the monthly_quota for an API key (OWNER-only mutation).
 * Emits an audit log entry in the same transaction.
 * Returns the updated { id, monthly_quota } or null if not found.
 */
export async function updateApiKeyMonthlyQuota(
  db: Db,
  tenantId: string,
  keyId: string,
  monthlyQuota: number | null,
  actorId: string,
): Promise<{ id: string; monthly_quota: number | null } | null> {
  const existing = await getApiKeyForTenant(db, tenantId, keyId)
  if (!existing) return null

  const result = await db.transaction(async (tx) => {
    const [updated] = await tx
      .update(tenantApiKeys)
      .set({ monthlyQuota })
      .where(
        and(
          eq(tenantApiKeys.id, keyId),
          eq(tenantApiKeys.tenantId, tenantId),
          sql`${tenantApiKeys.revokedAt} IS NULL`,
        ),
      )
      .returning({ id: tenantApiKeys.id, monthly_quota: tenantApiKeys.monthlyQuota })

    if (!updated) return null

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'tenant_api_key',
      entityId: keyId,
      action: 'api_key.quota_updated',
      changes: {
        monthly_quota: [existing.monthlyQuota ?? null, monthlyQuota],
      },
    })

    return updated
  })

  return result
}