/**
 * session-security DB query helpers — wave-13.
 *
 * All helpers accept a Db or DbTx so they can run inside transactions.
 * Routes MUST NOT import schema tables directly — use these helpers.
 */
import { and, eq, isNull, lt, sql } from 'drizzle-orm'
import type { Db, DbTx } from '../client'
import { userSessions, tenantSecuritySettings } from '../schema'

export type { UserSessionRow, NewUserSession, TenantSecuritySettingsRow } from '../schema/sessions'

// ── Session CRUD ──────────────────────────────────────────────────────────────

export interface CreateSessionArgs {
  tenantId: string
  userId: string
  tokenHash: string
  deviceName?: string | null
  ipAddress?: string | null
  countryCode?: string | null
  expiresAt: Date
}

export async function createSession(
  db: Db | DbTx,
  args: CreateSessionArgs,
): Promise<{ id: string }> {
  const [row] = await (db as Db)
    .insert(userSessions)
    .values({
      tenantId: args.tenantId,
      userId: args.userId,
      tokenHash: args.tokenHash,
      deviceName: args.deviceName ?? null,
      ipAddress: args.ipAddress ?? null,
      countryCode: args.countryCode ?? null,
      expiresAt: args.expiresAt,
    })
    .returning({ id: userSessions.id })

  return { id: row!.id }
}

export async function getSessionByTokenHash(
  db: Db | DbTx,
  tokenHash: string,
) {
  const [row] = await (db as Db)
    .select()
    .from(userSessions)
    .where(eq(userSessions.tokenHash, tokenHash))
    .limit(1)

  return row ?? null
}

export async function touchSession(
  db: Db | DbTx,
  tenantId: string,
  sessionId: string,
): Promise<void> {
  await (db as Db)
    .update(userSessions)
    .set({ lastActiveAt: new Date() })
    .where(and(eq(userSessions.tenantId, tenantId), eq(userSessions.id, sessionId)))
}

export interface RevokedSessionToken {
  tokenHash: string
  expiresAt: Date
}

export interface RevokeSessionsResult {
  tokens: RevokedSessionToken[]
}

export async function revokeSession(
  db: Db | DbTx,
  sessionId: string,
  userId: string,
  tenantId: string,
  reason: 'user' | 'admin' | 'idle_timeout' | 'suspicious',
): Promise<RevokeSessionsResult> {
  const rows = await (db as Db)
    .update(userSessions)
    .set({ revokedAt: new Date(), revokedReason: reason })
    .where(
      and(
        eq(userSessions.id, sessionId),
        eq(userSessions.userId, userId),
        eq(userSessions.tenantId, tenantId),
        isNull(userSessions.revokedAt),
      ),
    )
    .returning({ tokenHash: userSessions.tokenHash, expiresAt: userSessions.expiresAt })

  return { tokens: rows.map((r) => ({ tokenHash: r.tokenHash, expiresAt: r.expiresAt })) }
}

export async function revokeSessionByTokenHash(
  db: Db | DbTx,
  tokenHash: string,
  reason: 'user' | 'admin' | 'idle_timeout' | 'suspicious',
): Promise<RevokedSessionToken | null> {
  const rows = await (db as Db)
    .update(userSessions)
    .set({ revokedAt: new Date(), revokedReason: reason })
    .where(and(eq(userSessions.tokenHash, tokenHash), isNull(userSessions.revokedAt)))
    .returning({ tokenHash: userSessions.tokenHash, expiresAt: userSessions.expiresAt })

  const row = rows[0]
  return row ? { tokenHash: row.tokenHash, expiresAt: row.expiresAt } : null
}

export async function revokeOtherUserSessions(
  db: Db | DbTx,
  userId: string,
  tenantId: string,
  currentTokenHash: string,
  reason: 'user' | 'admin' | 'idle_timeout' | 'suspicious',
): Promise<RevokeSessionsResult> {
  const rows = await (db as Db)
    .update(userSessions)
    .set({ revokedAt: new Date(), revokedReason: reason })
    .where(
      and(
        eq(userSessions.userId, userId),
        eq(userSessions.tenantId, tenantId),
        isNull(userSessions.revokedAt),
        currentTokenHash ? sql`${userSessions.tokenHash} != ${currentTokenHash}` : sql`true`,
      ),
    )
    .returning({ tokenHash: userSessions.tokenHash, expiresAt: userSessions.expiresAt })

  return { tokens: rows.map((r) => ({ tokenHash: r.tokenHash, expiresAt: r.expiresAt })) }
}

export async function revokeAllSessionsForUser(
  db: Db | DbTx,
  userId: string,
  tenantId: string,
  reason: 'user' | 'admin' | 'idle_timeout' | 'suspicious',
): Promise<RevokeSessionsResult> {
  const rows = await (db as Db)
    .update(userSessions)
    .set({ revokedAt: new Date(), revokedReason: reason })
    .where(
      and(
        eq(userSessions.userId, userId),
        eq(userSessions.tenantId, tenantId),
        isNull(userSessions.revokedAt),
      ),
    )
    .returning({ tokenHash: userSessions.tokenHash, expiresAt: userSessions.expiresAt })

  return { tokens: rows.map((r) => ({ tokenHash: r.tokenHash, expiresAt: r.expiresAt })) }
}

export async function revokeAllTenantSessions(
  db: Db | DbTx,
  tenantId: string,
  reason: 'user' | 'admin' | 'idle_timeout' | 'suspicious',
): Promise<RevokeSessionsResult> {
  const rows = await (db as Db)
    .update(userSessions)
    .set({ revokedAt: new Date(), revokedReason: reason })
    .where(and(eq(userSessions.tenantId, tenantId), isNull(userSessions.revokedAt)))
    .returning({ tokenHash: userSessions.tokenHash, expiresAt: userSessions.expiresAt })

  return { tokens: rows.map((r) => ({ tokenHash: r.tokenHash, expiresAt: r.expiresAt })) }
}

export async function listUserSessions(
  db: Db | DbTx,
  userId: string,
  tenantId: string,
) {
  return (db as Db)
    .select()
    .from(userSessions)
    .where(
      and(
        eq(userSessions.userId, userId),
        eq(userSessions.tenantId, tenantId),
        isNull(userSessions.revokedAt),
      ),
    )
    .orderBy(sql`${userSessions.createdAt} DESC`)
}

export interface TenantUserSessionSummary {
  userId: string
  activeSessions: number
  lastActiveAt: Date | null
}

export async function listTenantUserSessionSummaries(
  db: Db | DbTx,
  tenantId: string,
): Promise<TenantUserSessionSummary[]> {
  const rows = await (db as Db)
    .select({
      userId: userSessions.userId,
      activeSessions: sql<number>`cast(count(*) as int)`,
      lastActiveAt: sql<Date | null>`max(${userSessions.lastActiveAt})`,
    })
    .from(userSessions)
    .where(and(eq(userSessions.tenantId, tenantId), isNull(userSessions.revokedAt)))
    .groupBy(userSessions.userId)
    .orderBy(sql`max(${userSessions.lastActiveAt}) DESC`)

  return rows as TenantUserSessionSummary[]
}

export async function countActiveSessions(
  db: Db | DbTx,
  userId: string,
  tenantId: string,
): Promise<number> {
  const [row] = await (db as Db)
    .select({ cnt: sql<number>`cast(count(*) as int)` })
    .from(userSessions)
    .where(
      and(
        eq(userSessions.userId, userId),
        eq(userSessions.tenantId, tenantId),
        isNull(userSessions.revokedAt),
        sql`${userSessions.expiresAt} > now()`,
      ),
    )

  return row?.cnt ?? 0
}

// ── Cleanup crons ─────────────────────────────────────────────────────────────

/**
 * Delete rows whose expires_at has passed (with 1-min grace).
 * Uses idx_sessions_expires_at (partial index on expires_at < now()).
 */
export async function sweepExpiredSessions(db: Db | DbTx): Promise<number> {
  const rows = await (db as Db)
    .delete(userSessions)
    .where(lt(userSessions.expiresAt, sql`now() - INTERVAL '1 minute'`))
    .returning({ id: userSessions.id })

  return rows.length
}

/**
 * Revoke sessions that have been idle beyond the tenant's configured timeout.
 * Runs nightly; only affects tenants with idle_timeout_minutes set.
 */
export async function cleanupIdleSessions(db: Db | DbTx): Promise<number> {
  const rows = await (db as Db)
    .update(userSessions)
    .set({ revokedAt: new Date(), revokedReason: 'idle_timeout' })
    .where(
      and(
        isNull(userSessions.revokedAt),
        sql`${userSessions.lastActiveAt} < now() - (
          SELECT (tss.idle_timeout_minutes || ' minutes')::interval
          FROM tenant_security_settings tss
          WHERE tss.tenant_id = ${userSessions.tenantId}
            AND tss.idle_timeout_minutes IS NOT NULL
        )`,
      ),
    )
    .returning({ id: userSessions.id })

  return rows.length
}

// ── Tenant security settings ──────────────────────────────────────────────────

export interface TenantSecuritySettingsPatch {
  idleTimeoutMinutes?: number | null
  maxSessionsPerUser?: number
  require2faForRoles?: string[]
  blockSuspiciousLogins?: boolean
}

export async function getTenantSecuritySettings(
  db: Db | DbTx,
  tenantId: string,
) {
  const [row] = await (db as Db)
    .select()
    .from(tenantSecuritySettings)
    .where(eq(tenantSecuritySettings.tenantId, tenantId))
    .limit(1)

  // Return defaults when no row yet
  return row ?? {
    tenantId,
    idleTimeoutMinutes: null,
    maxSessionsPerUser: 10,
    require2faForRoles: [] as string[],
    blockSuspiciousLogins: false,
    createdAt: new Date(),
    updatedAt: new Date(),
  }
}

export async function upsertTenantSecuritySettings(
  db: Db | DbTx,
  tenantId: string,
  patch: TenantSecuritySettingsPatch,
) {
  const set: Record<string, unknown> = { updatedAt: new Date() }
  if (patch.idleTimeoutMinutes !== undefined) set['idleTimeoutMinutes'] = patch.idleTimeoutMinutes
  if (patch.maxSessionsPerUser !== undefined) set['maxSessionsPerUser'] = patch.maxSessionsPerUser
  if (patch.require2faForRoles !== undefined) set['require2faForRoles'] = patch.require2faForRoles
  if (patch.blockSuspiciousLogins !== undefined) set['blockSuspiciousLogins'] = patch.blockSuspiciousLogins

  const [row] = await (db as Db)
    .insert(tenantSecuritySettings)
    .values({
      tenantId,
      idleTimeoutMinutes: patch.idleTimeoutMinutes ?? null,
      maxSessionsPerUser: patch.maxSessionsPerUser ?? 10,
      require2faForRoles: patch.require2faForRoles ?? [],
      blockSuspiciousLogins: patch.blockSuspiciousLogins ?? false,
    })
    .onConflictDoUpdate({
      target: tenantSecuritySettings.tenantId,
      set,
    })
    .returning()

  return row!
}