/**
 * OAuth 2.0 Authorization Code flow — DB query helpers (wave-12).
 *
 * Every tenant-scoped mutation runs inside db.transaction + tx.insert(auditLog)
 * (enforced by zync/require-audit-in-transaction ESLint rule).
 *
 * IMPORTANT: All token hashing and PKCE verification is done by the CALLER
 * (route handlers in apps/zync-api), not here. Only hashes are accepted as
 * arguments — plaintext tokens never enter the query layer.
 * This mirrors the pattern in auth-writes.ts.
 */
import { eq, and, isNull } from 'drizzle-orm'
import type { Db } from '../client'
import {
  oauthClients,
  oauthAuthorizationCodes,
  oauthAccessTokens,
  oauthRefreshTokens,
  oauthConnections,
} from '../schema/oauth'
import type {
  OAuthClientRow,
  NewOAuthClient,
} from '../schema/oauth'
import { auditLog } from './_audit-forward'

export type {
  OAuthClientRow,
  OAuthAuthorizationCodeRow,
  OAuthAccessTokenRow,
  OAuthRefreshTokenRow,
  OAuthConnectionRow,
} from '../schema/oauth'

// ── Client helpers ─────────────────────────────────────────────────────────────

export async function getOAuthClientByClientId(
  db: Db,
  clientId: string,
): Promise<OAuthClientRow | null> {
  const rows = await db
    .select()
    .from(oauthClients)
    .where(eq(oauthClients.clientId, clientId))
    .limit(1)
  return rows[0] ?? null
}

export async function getOAuthClientById(
  db: Db,
  id: string,
): Promise<OAuthClientRow | null> {
  const rows = await db
    .select()
    .from(oauthClients)
    .where(eq(oauthClients.id, id))
    .limit(1)
  return rows[0] ?? null
}

export async function listOAuthClientsQuery(db: Db): Promise<OAuthClientRow[]> {
  return db.select().from(oauthClients).orderBy(oauthClients.createdAt)
}

export async function createOAuthClientRecord(
  db: Db,
  values: Omit<NewOAuthClient, 'id' | 'createdAt'>,
): Promise<OAuthClientRow> {
  const rows = await db.insert(oauthClients).values(values).returning()
  return rows[0]!
}

export async function updateOAuthClientRecord(
  db: Db,
  id: string,
  patch: Partial<{
    redirectUris: unknown
    scopes: unknown
    name: string
    logoUrl: string | null
    isFirstParty: boolean
  }>,
): Promise<OAuthClientRow | null> {
  if (Object.keys(patch).length === 0) return null
  const rows = await db
    .update(oauthClients)
    .set(patch)
    .where(eq(oauthClients.id, id))
    .returning()
  return rows[0] ?? null
}

// ── Issue authorization code ───────────────────────────────────────────────────
// Caller generates the code plaintext, hashes it with hashToken(), and passes
// the hash here. Plaintext is returned to the client in the redirect only.

export async function insertAuthorizationCode(
  db: Db,
  args: {
    /** SHA-256 hash of the authorization code (never store plaintext). */
    code: string
    oauthClientId: string
    tenantId: string
    userId: string
    redirectUri: string
    scope: string
    codeChallenge: string | null
    codeChallengeMethod: string | null
    expiresAt: Date
  },
): Promise<void> {
  await db.transaction(async (tx) => {
    await tx.insert(oauthAuthorizationCodes).values({
      code: args.code,
      oauthClientId: args.oauthClientId,
      tenantId: args.tenantId,
      userId: args.userId,
      redirectUri: args.redirectUri,
      scope: args.scope,
      codeChallenge: args.codeChallenge,
      codeChallengeMethod: args.codeChallengeMethod,
      expiresAt: args.expiresAt,
    })

    await tx.insert(auditLog).values({
      tenantId: args.tenantId,
      actorId: args.userId,
      actorType: 'user',
      entityType: 'oauth_authorization_code',
      entityId: args.userId,
      action: 'oauth.code_issued',
      ip: null,
      requestId: null,
    })
  })
}

export async function lookupAuthorizationCode(
  db: Db,
  codeHash: string,
) {
  const rows = await db
    .select()
    .from(oauthAuthorizationCodes)
    .where(eq(oauthAuthorizationCodes.code, codeHash))
    .limit(1)
  return rows[0] ?? null
}

/** Atomically mark code used — false if already consumed (concurrent replay). */
export async function markAuthorizationCodeUsed(
  db: Db,
  tenantId: string,
  id: string,
  usedAt: Date,
): Promise<boolean> {
  const result = await db
    .update(oauthAuthorizationCodes)
    .set({ usedAt })
    .where(
      and(
        eq(oauthAuthorizationCodes.tenantId, tenantId),
        eq(oauthAuthorizationCodes.id, id),
        isNull(oauthAuthorizationCodes.usedAt),
      ),
    )
    .returning({ id: oauthAuthorizationCodes.id })
  return result.length > 0
}

// ── Issue tokens (called after code exchange) ──────────────────────────────────

export interface InsertTokenPairArgs {
  accessTokenHash: string
  refreshTokenHash: string
  oauthClientId: string
  tenantId: string
  userId: string
  scope: string
  familyId: string
  accessExpiresAt: Date
  refreshExpiresAt: Date
}

export async function insertTokenPair(
  db: Db,
  args: InsertTokenPairArgs,
): Promise<void> {
  await db.transaction(async (tx) => {
    await tx.insert(oauthAccessTokens).values({
      tokenHash: args.accessTokenHash,
      oauthClientId: args.oauthClientId,
      tenantId: args.tenantId,
      userId: args.userId,
      scope: args.scope,
      familyId: args.familyId,
      expiresAt: args.accessExpiresAt,
    })

    await tx.insert(oauthRefreshTokens).values({
      tokenHash: args.refreshTokenHash,
      oauthClientId: args.oauthClientId,
      tenantId: args.tenantId,
      userId: args.userId,
      scope: args.scope,
      familyId: args.familyId,
      expiresAt: args.refreshExpiresAt,
    })

    await tx
      .insert(oauthConnections)
      .values({
        oauthClientId: args.oauthClientId,
        tenantId: args.tenantId,
        userId: args.userId,
        scope: args.scope,
        lastUsedAt: new Date(),
      })
      .onConflictDoUpdate({
        target: [oauthConnections.oauthClientId, oauthConnections.tenantId, oauthConnections.userId],
        set: { scope: args.scope, lastUsedAt: new Date() },
      })

    await tx.insert(auditLog).values({
      tenantId: args.tenantId,
      actorId: args.userId,
      actorType: 'user',
      entityType: 'oauth_access_token',
      entityId: args.userId,
      action: 'oauth.token_issued',
      ip: null,
      requestId: null,
    })
  })
}

// ── Refresh token lookup ───────────────────────────────────────────────────────

export async function lookupRefreshToken(
  db: Db,
  tokenHash: string,
) {
  const rows = await db
    .select()
    .from(oauthRefreshTokens)
    .where(eq(oauthRefreshTokens.tokenHash, tokenHash))
    .limit(1)
  return rows[0] ?? null
}

// ── Rotate refresh token ───────────────────────────────────────────────────────

export interface RotateRefreshArgs {
  oldRefreshTokenId: string
  newRefreshTokenHash: string
  newAccessTokenHash: string
  familyId: string
  oauthClientId: string
  tenantId: string
  userId: string
  scope: string
  newAccessExpiresAt: Date
  newRefreshExpiresAt: Date
}

export async function rotateOAuthRefreshToken(
  db: Db,
  args: RotateRefreshArgs,
): Promise<void> {
  const now = new Date()

  await db.transaction(async (tx) => {
    const [newRt] = await tx
      .insert(oauthRefreshTokens)
      .values({
        tokenHash: args.newRefreshTokenHash,
        oauthClientId: args.oauthClientId,
        tenantId: args.tenantId,
        userId: args.userId,
        scope: args.scope,
        familyId: args.familyId,
        expiresAt: args.newRefreshExpiresAt,
      })
      .returning({ id: oauthRefreshTokens.id })

    await tx
      .update(oauthRefreshTokens)
      .set({ rotatedToId: newRt!.id })
      .where(
        and(
          eq(oauthRefreshTokens.tenantId, args.tenantId),
          eq(oauthRefreshTokens.id, args.oldRefreshTokenId),
        ),
      )

    // Revoke all existing access tokens in this family
    await tx
      .update(oauthAccessTokens)
      .set({ revokedAt: now })
      .where(eq(oauthAccessTokens.familyId, args.familyId))

    await tx.insert(oauthAccessTokens).values({
      tokenHash: args.newAccessTokenHash,
      oauthClientId: args.oauthClientId,
      tenantId: args.tenantId,
      userId: args.userId,
      scope: args.scope,
      familyId: args.familyId,
      expiresAt: args.newAccessExpiresAt,
    })

    await tx
      .update(oauthConnections)
      .set({ lastUsedAt: now })
      .where(
        and(
          eq(oauthConnections.oauthClientId, args.oauthClientId),
          eq(oauthConnections.tenantId, args.tenantId),
          eq(oauthConnections.userId, args.userId),
        ),
      )

    await tx.insert(auditLog).values({
      tenantId: args.tenantId,
      actorId: args.userId,
      actorType: 'user',
      entityType: 'oauth_refresh_token',
      entityId: args.userId,
      action: 'oauth.token_refreshed',
      ip: null,
      requestId: null,
    })
  })
}

// ── Revoke entire token family ─────────────────────────────────────────────────

export async function revokeOAuthTokenFamily(
  db: Db,
  args: {
    familyId: string
    tenantId: string
    userId: string
    oauthClientId: string
  },
): Promise<void> {
  const now = new Date()
  await db.transaction(async (tx) => {
    await tx
      .update(oauthRefreshTokens)
      .set({ revokedAt: now })
      .where(eq(oauthRefreshTokens.familyId, args.familyId))

    await tx
      .update(oauthAccessTokens)
      .set({ revokedAt: now })
      .where(eq(oauthAccessTokens.familyId, args.familyId))

    await tx
      .update(oauthConnections)
      .set({ flaggedAt: now })
      .where(
        and(
          eq(oauthConnections.oauthClientId, args.oauthClientId),
          eq(oauthConnections.tenantId, args.tenantId),
          eq(oauthConnections.userId, args.userId),
        ),
      )

    await tx.insert(auditLog).values({
      tenantId: args.tenantId,
      actorId: args.userId,
      actorType: 'user',
      entityType: 'oauth_connection',
      entityId: args.userId,
      action: 'oauth.token_family_revoked',
      ip: null,
      requestId: null,
    })
  })
}

// ── Revoke single token (by hash) ─────────────────────────────────────────────

export async function revokeAccessTokenByHash(
  db: Db,
  tokenHash: string,
): Promise<boolean> {
  const rows = await db
    .update(oauthAccessTokens)
    .set({ revokedAt: new Date() })
    .where(eq(oauthAccessTokens.tokenHash, tokenHash))
    .returning({ id: oauthAccessTokens.id })
  return rows.length > 0
}

export async function revokeRefreshTokenByHash(
  db: Db,
  tokenHash: string,
): Promise<string | null> {
  const rows = await db
    .update(oauthRefreshTokens)
    .set({ revokedAt: new Date() })
    .where(eq(oauthRefreshTokens.tokenHash, tokenHash))
    .returning({ familyId: oauthRefreshTokens.familyId })
  return rows[0]?.familyId ?? null
}

export async function revokeAccessTokensByFamily(
  db: Db,
  familyId: string,
): Promise<void> {
  await db
    .update(oauthAccessTokens)
    .set({ revokedAt: new Date() })
    .where(eq(oauthAccessTokens.familyId, familyId))
}

// ── Revoke all tokens for a client connection ──────────────────────────────────

export async function revokeClientConnectionTokens(
  db: Db,
  args: {
    oauthClientId: string
    tenantId: string
    userId: string
  },
): Promise<void> {
  const now = new Date()

  await db.transaction(async (tx) => {
    await tx
      .update(oauthRefreshTokens)
      .set({ revokedAt: now })
      .where(
        and(
          eq(oauthRefreshTokens.oauthClientId, args.oauthClientId),
          eq(oauthRefreshTokens.tenantId, args.tenantId),
          eq(oauthRefreshTokens.userId, args.userId),
        ),
      )

    await tx
      .update(oauthAccessTokens)
      .set({ revokedAt: now })
      .where(
        and(
          eq(oauthAccessTokens.oauthClientId, args.oauthClientId),
          eq(oauthAccessTokens.tenantId, args.tenantId),
          eq(oauthAccessTokens.userId, args.userId),
        ),
      )

    const deleted = await tx
      .delete(oauthConnections)
      .where(
        and(
          eq(oauthConnections.oauthClientId, args.oauthClientId),
          eq(oauthConnections.tenantId, args.tenantId),
          eq(oauthConnections.userId, args.userId),
        ),
      )
      .returning({ id: oauthConnections.id })

    if (deleted.length > 0) {
      await tx.insert(auditLog).values({
        tenantId: args.tenantId,
        actorId: args.userId,
        actorType: 'user',
        entityType: 'oauth_connection',
        entityId: deleted[0]!.id,
        action: 'oauth.connection_revoked',
        ip: null,
        requestId: null,
      })
    }
  })
}

// ── Resolve access token (resource server) ────────────────────────────────────

export interface ResolvedOAuthToken {
  tenantId: string
  userId: string
  scope: string[]
  clientId: string
  tokenId: string
  oauthClientRowId: string
  expiresAt: Date
}

export async function resolveOAuthAccessToken(
  db: Db,
  tokenHash: string,
): Promise<ResolvedOAuthToken | null> {
  const now = new Date()

  const rows = await db
    .select({
      id: oauthAccessTokens.id,
      tenantId: oauthAccessTokens.tenantId,
      userId: oauthAccessTokens.userId,
      scope: oauthAccessTokens.scope,
      expiresAt: oauthAccessTokens.expiresAt,
      revokedAt: oauthAccessTokens.revokedAt,
      clientId: oauthClients.clientId,
      oauthClientRowId: oauthClients.id,
    })
    .from(oauthAccessTokens)
    .innerJoin(oauthClients, eq(oauthAccessTokens.oauthClientId, oauthClients.id))
    .where(eq(oauthAccessTokens.tokenHash, tokenHash))
    .limit(1)

  const row = rows[0]
  if (!row) return null
  if (row.revokedAt) return null
  if (row.expiresAt < now) return null

  return {
    tenantId: row.tenantId,
    userId: row.userId,
    scope: row.scope.split(' ').filter(Boolean),
    clientId: row.clientId,
    tokenId: row.id,
    oauthClientRowId: row.oauthClientRowId,
    expiresAt: row.expiresAt,
  }
}

// ── Connection list ────────────────────────────────────────────────────────────

export interface OAuthConnectionView {
  clientId: string
  name: string
  logoUrl: string | null
  scope: string
  lastUsedAt: string | null
  flaggedAt: string | null
  createdAt: string
}

export async function listOAuthConnectionsForUser(
  db: Db,
  tenantId: string,
  userId: string,
): Promise<OAuthConnectionView[]> {
  const rows = await db
    .select({
      clientId: oauthClients.clientId,
      name: oauthClients.name,
      logoUrl: oauthClients.logoUrl,
      scope: oauthConnections.scope,
      lastUsedAt: oauthConnections.lastUsedAt,
      flaggedAt: oauthConnections.flaggedAt,
      createdAt: oauthConnections.createdAt,
    })
    .from(oauthConnections)
    .innerJoin(oauthClients, eq(oauthConnections.oauthClientId, oauthClients.id))
    .where(
      and(
        eq(oauthConnections.tenantId, tenantId),
        eq(oauthConnections.userId, userId),
      ),
    )

  return rows.map((r) => ({
    clientId: r.clientId,
    name: r.name,
    logoUrl: r.logoUrl,
    scope: r.scope,
    lastUsedAt: r.lastUsedAt ? r.lastUsedAt.toISOString() : null,
    flaggedAt: r.flaggedAt ? r.flaggedAt.toISOString() : null,
    createdAt: r.createdAt.toISOString(),
  }))
}

// ── Touch connection last_used_at (fire-and-forget) ───────────────────────────

export async function touchOAuthConnectionLastUsed(
  db: Db,
  tenantId: string,
  userId: string,
  oauthClientId: string,
): Promise<void> {
  await db
    .update(oauthConnections)
    .set({ lastUsedAt: new Date() })
    .where(
      and(
        eq(oauthConnections.oauthClientId, oauthClientId),
        eq(oauthConnections.tenantId, tenantId),
        eq(oauthConnections.userId, userId),
      ),
    )
}