/**
 * Communications query helpers — system-communications-notifications.
 *
 * Covers: push_subscriptions, notifications, adapter_credentials.
 * These helpers are the ONLY db-layer allowed to import raw Drizzle tables for
 * communications. Route files MUST NOT import schema tables directly.
 */
import { eq, and, isNull, desc, sql } from 'drizzle-orm'
import type { Db } from '../client'
import {
  pushSubscriptions,
  notifications,
  adapterCredentials,
} from '../schema'

// ── Push Subscriptions ──────────────────────────────────────────────────────

export async function upsertPushSubscription(
  db: Db,
  input: {
    userId: string
    tenantId: string
    endpoint: string
    p256dh: string
    auth: string
    userAgent?: string
  },
): Promise<void> {
  await db
    .insert(pushSubscriptions)
    .values({
      userId: input.userId,
      tenantId: input.tenantId,
      endpoint: input.endpoint,
      p256dh: input.p256dh,
      auth: input.auth,
      userAgent: input.userAgent ?? null,
      lastUsedAt: new Date(),
    })
    .onConflictDoUpdate({
      target: pushSubscriptions.endpoint,
      set: {
        userId: input.userId,
        tenantId: input.tenantId,
        p256dh: input.p256dh,
        auth: input.auth,
        userAgent: input.userAgent ?? null,
        lastUsedAt: new Date(),
      },
    })
}

export async function deletePushSubscription(
  db: Db,
  userId: string,
  tenantId: string,
  endpoint: string,
): Promise<void> {
  await db
    .delete(pushSubscriptions)
    .where(
      and(
        eq(pushSubscriptions.userId, userId),
        eq(pushSubscriptions.tenantId, tenantId),
        eq(pushSubscriptions.endpoint, endpoint),
      ),
    )
}

export async function getPushSubscriptionsForUser(
  db: Db,
  userId: string,
  tenantId: string,
) {
  return db
    .select()
    .from(pushSubscriptions)
    .where(
      and(
        eq(pushSubscriptions.userId, userId),
        eq(pushSubscriptions.tenantId, tenantId),
      ),
    )
}

export async function deletePushSubscriptionByEndpoint(
  db: Db,
  endpoint: string,
): Promise<void> {
  await db.delete(pushSubscriptions).where(eq(pushSubscriptions.endpoint, endpoint))
}

export async function countPushSubscriptionsForUser(
  db: Db,
  userId: string,
  tenantId: string,
): Promise<number> {
  const result = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(pushSubscriptions)
    .where(
      and(
        eq(pushSubscriptions.userId, userId),
        eq(pushSubscriptions.tenantId, tenantId),
      ),
    )
  return result[0]?.count ?? 0
}

// ── Notifications ───────────────────────────────────────────────────────────

export async function insertNotification(
  db: Db,
  input: {
    tenantId: string
    userId: string
    type: string
    titleKey: string
    bodyKey?: string
    params?: Record<string, unknown>
    entityType?: string
    entityId?: string
  },
): Promise<{ id: string }> {
  const rows = await db
    .insert(notifications)
    .values({
      tenantId: input.tenantId,
      userId: input.userId,
      type: input.type,
      titleKey: input.titleKey,
      bodyKey: input.bodyKey ?? null,
      params: input.params ?? {},
      entityType: input.entityType ?? null,
      entityId: input.entityId ?? null,
    })
    .returning({ id: notifications.id })

  const row = rows[0]
  if (!row) throw new Error('Failed to insert notification')
  return { id: row.id }
}

export async function getNotificationsForUser(
  db: Db,
  tenantId: string,
  userId: string,
): Promise<{ unread: (typeof notifications.$inferSelect)[]; read: (typeof notifications.$inferSelect)[] }> {
  // Unread: all unread for user/tenant
  const unread = await db
    .select()
    .from(notifications)
    .where(
      and(
        eq(notifications.tenantId, tenantId),
        eq(notifications.userId, userId),
        isNull(notifications.readAt),
      ),
    )
    .orderBy(desc(notifications.createdAt))

  // Read: last 20 read for user/tenant (readAt IS NOT NULL)
  const read = await db
    .select()
    .from(notifications)
    .where(
      and(
        eq(notifications.tenantId, tenantId),
        eq(notifications.userId, userId),
        sql`${notifications.readAt} IS NOT NULL`,
      ),
    )
    .orderBy(desc(notifications.createdAt))
    .limit(20)

  return { unread, read }
}

export async function markNotificationRead(
  db: Db,
  tenantId: string,
  userId: string,
  notificationId: string,
): Promise<void> {
  await db
    .update(notifications)
    .set({ readAt: new Date() })
    .where(
      and(
        eq(notifications.id, notificationId),
        eq(notifications.tenantId, tenantId),
        eq(notifications.userId, userId),
      ),
    )
}

export async function markAllNotificationsRead(
  db: Db,
  tenantId: string,
  userId: string,
): Promise<void> {
  await db
    .update(notifications)
    .set({ readAt: new Date() })
    .where(
      and(
        eq(notifications.tenantId, tenantId),
        eq(notifications.userId, userId),
        isNull(notifications.readAt),
      ),
    )
}

// ── Adapter Credentials ─────────────────────────────────────────────────────

export async function saveAdapterCredentialRow(
  db: Db,
  input: {
    tenantId: string
    adapterId: string
    ciphertext: string
    iv: string
    authTag: string
    metadata: Record<string, unknown>
  },
): Promise<void> {
  await db
    .insert(adapterCredentials)
    .values({
      tenantId: input.tenantId,
      adapterId: input.adapterId,
      ciphertext: input.ciphertext,
      iv: input.iv,
      authTag: input.authTag,
      metadata: input.metadata,
    })
    .onConflictDoUpdate({
      target: [adapterCredentials.tenantId, adapterCredentials.adapterId],
      set: {
        ciphertext: input.ciphertext,
        iv: input.iv,
        authTag: input.authTag,
        metadata: input.metadata,
        updatedAt: new Date(),
      },
    })
}

/**
 * Find a Slack adapter credential by the Slack team ID stored in metadata.
 * Uses jsonb operator for Postgres; safe against SQL injection.
 */
export async function findSlackCredentialByTeamId(
  db: Db,
  slackTeamId: string,
): Promise<{ tenantId: string; ciphertext: string; iv: string; authTag: string } | null> {
  const rows = await db
    .select({
      tenantId: adapterCredentials.tenantId,
      ciphertext: adapterCredentials.ciphertext,
      iv: adapterCredentials.iv,
      authTag: adapterCredentials.authTag,
    })
    .from(adapterCredentials)
    .where(
      and(
        eq(adapterCredentials.adapterId, 'slack'),
        sql`${adapterCredentials.metadata}->>'slack_team_id' = ${slackTeamId}`,
      ),
    )
    .limit(1)

  return rows[0] ?? null
}

export async function loadAdapterCredentialRow(
  db: Db,
  tenantId: string,
  adapterId: string,
): Promise<{ ciphertext: string; iv: string; authTag: string } | null> {
  const rows = await db
    .select({
      ciphertext: adapterCredentials.ciphertext,
      iv: adapterCredentials.iv,
      authTag: adapterCredentials.authTag,
    })
    .from(adapterCredentials)
    .where(
      and(
        eq(adapterCredentials.tenantId, tenantId),
        eq(adapterCredentials.adapterId, adapterId),
      ),
    )
    .limit(1)

  return rows[0] ?? null
}