/**
 * Read helpers for foundation-auth-rbac routes.
 *
 * Routes may NOT import raw Drizzle tables (`no-raw-drizzle-from-routes`); they
 * import these helpers from `@zync/db/queries` instead. Every JOIN / lookup a
 * route needs lives here so the schema tables stay encapsulated.
 */
import { and, eq, gt, isNull, sql } from 'drizzle-orm'
import type { TenantId, UserId } from '@zync/types'
import type { Db, DbTx } from '../client'
import {
  users,
  tenants,
  tenantMemberships,
  roles,
  permissions,
  rolePermissions,
  refreshTokens,
  invitations,
  adminUsers,
  adminRoles,
  userPreferences,
} from '../schema'

export async function findUserByEmail(db: Db | DbTx, email: string) {
  const [row] = await db.select().from(users).where(eq(users.email, email)).limit(1)
  return row ?? null
}

export async function findUserById(db: Db, id: UserId) {
  const [row] = await db.select().from(users).where(eq(users.id, id)).limit(1)
  return row ?? null
}

export async function findAdminByEmail(db: Db, email: string) {
  const [row] = await db.select().from(adminUsers).where(eq(adminUsers.email, email)).limit(1)
  return row ?? null
}

export async function findAdminById(db: Db, id: string) {
  const [row] = await db.select().from(adminUsers).where(eq(adminUsers.id, id)).limit(1)
  return row ?? null
}

export async function getTenantById(db: Db | DbTx, id: TenantId) {
  const [row] = await db.select().from(tenants).where(eq(tenants.id, id)).limit(1)
  return row ?? null
}

export async function getMembership(db: Db | DbTx, userId: UserId, tenantId: TenantId) {
  const [row] = await db
    .select()
    .from(tenantMemberships)
    .where(and(eq(tenantMemberships.userId, userId), eq(tenantMemberships.tenantId, tenantId)))
    .limit(1)
  return row ?? null
}

/** Resolve the expanded permission key set for a role. */
export async function getPermissionsForRole(db: Db, roleId: string): Promise<string[]> {
  const rows = await db
    .select({ key: permissions.key })
    .from(rolePermissions)
    .innerJoin(permissions, eq(rolePermissions.permissionId, permissions.id))
    .where(eq(rolePermissions.roleId, roleId))
  return rows.map((r) => r.key)
}

/** Resolve a tenant-scoped role row by name (e.g. 'OWNER'). */
export async function getRoleByName(db: Db | DbTx, tenantId: TenantId, name: string) {
  const [row] = await db
    .select()
    .from(roles)
    .where(and(eq(roles.tenantId, tenantId), eq(roles.name, name)))
    .limit(1)
  return row ?? null
}

/** Resolve a role row by id (tenant scoping is the caller's responsibility). */
export async function getRoleById(db: Db, roleId: string) {
  const [row] = await db.select().from(roles).where(eq(roles.id, roleId)).limit(1)
  return row ?? null
}

/**
 * The `/api/auth/me` view: the user, their active membership, the tenant, the
 * role name, and the expanded permission set. Returns null if no membership.
 */
export async function getMeView(db: Db, userId: UserId, tenantId: TenantId) {
  const [row] = await db
    .select({
      id: users.id,
      email: users.email,
      name: users.name,
      avatarUrl: users.avatarUrl,
      emailVerifiedAt: users.emailVerifiedAt,
      tenantId: tenants.id,
      tenantSlug: tenants.slug,
      tier: tenants.tier,
      onboardingCompleted: tenants.onboardingCompleted,
      onboardingStep: tenants.onboardingStep,
      countryCode: tenants.countryCode,
      tenantDefaultCurrency: tenants.defaultCurrency,
      tenantDefaultTimezone: tenants.defaultTimezone,
      tenantLocale: sql<string | null>`(${tenants.settings} ->> 'locale')`,
      roleId: roles.id,
      role: roles.name,
      membershipStatus: tenantMemberships.status,
      userLocale: userPreferences.locale,
      userTimezone: userPreferences.timezone,
      userDefaultCurrency: userPreferences.defaultCurrency,
    })
    .from(users)
    .innerJoin(tenantMemberships, eq(tenantMemberships.userId, users.id))
    .innerJoin(tenants, eq(tenants.id, tenantMemberships.tenantId))
    .innerJoin(roles, eq(roles.id, tenantMemberships.roleId))
    .leftJoin(
      userPreferences,
      and(
        eq(userPreferences.userId, users.id),
        eq(userPreferences.tenantId, tenantMemberships.tenantId),
      ),
    )
    .where(and(
      eq(users.id, userId),
      eq(tenantMemberships.tenantId, tenantId),
      eq(tenantMemberships.status, 'active'),
    ))
    .limit(1)
  if (!row) return null
  const perms = await getPermissionsForRole(db, row.roleId)
  return { ...row, permissions: perms }
}

/**
 * Look up a non-revoked, non-expired refresh token by its stored hash.
 * Returns null when missing/expired/revoked.
 */
export async function getActiveRefreshTokenByHash(db: Db, tokenHash: string) {
  const [row] = await db
    .select()
    .from(refreshTokens)
    .where(
      and(
        eq(refreshTokens.tokenHash, tokenHash),
        isNull(refreshTokens.revokedAt),
        gt(refreshTokens.expiresAt, new Date()),
      ),
    )
    .limit(1)
  return row ?? null
}

/** Count active (non-revoked, non-expired) refresh tokens for a user+tenant. */
export async function countActiveRefreshTokens(
  db: Db,
  userId: UserId,
  tenantId: TenantId | null,
): Promise<number> {
  const cond = tenantId
    ? and(eq(refreshTokens.userId, userId), eq(refreshTokens.tenantId, tenantId))
    : eq(refreshTokens.userId, userId)
  const [row] = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(refreshTokens)
    .where(and(cond, isNull(refreshTokens.revokedAt), gt(refreshTokens.expiresAt, new Date())))
  return row?.count ?? 0
}

/** Count active members of a tenant (for the team-seat cap). */
export async function countActiveMembers(db: Db | DbTx, tenantId: TenantId): Promise<number> {
  const [row] = await db
    .select({ count: sql<number>`count(*)::int` })
    .from(tenantMemberships)
    .where(and(eq(tenantMemberships.tenantId, tenantId), eq(tenantMemberships.status, 'active')))
  return row?.count ?? 0
}

/**
 * Resolve the user's primary active membership (the most recently created
 * active one) joined with tenant + role, for session issuance at login.
 * Returns null when the user has no active membership.
 */
export async function getPrimaryMembership(db: Db, userId: UserId) {
  const [row] = await db
    .select({
      tenantId: tenants.id,
      tenantSlug: tenants.slug,
      tier: tenants.tier,
      roleId: roles.id,
      role: roles.name,
    })
    .from(tenantMemberships)
    .innerJoin(tenants, eq(tenants.id, tenantMemberships.tenantId))
    .innerJoin(roles, eq(roles.id, tenantMemberships.roleId))
    .where(and(eq(tenantMemberships.userId, userId), eq(tenantMemberships.status, 'active')))
    .orderBy(tenantMemberships.createdAt)
    .limit(1)
  return row ?? null
}

/**
 * The user's primary membership joined with tenant data, including 2FA fields.
 * Used by the login route to determine 2FA challenge requirements.
 */
export async function getPrimaryMembershipWithTenant(db: Db, userId: UserId) {
  const [row] = await db
    .select({
      tenantId: tenants.id,
      tenantSlug: tenants.slug,
      tier: tenants.tier,
      roleId: roles.id,
      role: roles.name,
      enforce2fa: tenants.enforce2fa,
      disable2faRememberDevice: tenants.disable2faRememberDevice,
    })
    .from(tenantMemberships)
    .innerJoin(tenants, eq(tenants.id, tenantMemberships.tenantId))
    .innerJoin(roles, eq(roles.id, tenantMemberships.roleId))
    .where(and(eq(tenantMemberships.userId, userId), eq(tenantMemberships.status, 'active')))
    .orderBy(tenantMemberships.createdAt)
    .limit(1)
  return row ?? null
}

/** Membership joined with tenant + role for a specific (user, tenant) pair. */
export async function getMembershipView(db: Db, userId: UserId, tenantId: TenantId) {
  const [row] = await db
    .select({
      tenantId: tenants.id,
      tenantSlug: tenants.slug,
      tier: tenants.tier,
      roleId: roles.id,
      role: roles.name,
      status: tenantMemberships.status,
    })
    .from(tenantMemberships)
    .innerJoin(tenants, eq(tenants.id, tenantMemberships.tenantId))
    .innerJoin(roles, eq(roles.id, tenantMemberships.roleId))
    .where(and(eq(tenantMemberships.userId, userId), eq(tenantMemberships.tenantId, tenantId)))
    .limit(1)
  return row ?? null
}

/** Look up an unaccepted, unexpired invitation by its stored token hash. */
export async function getInvitationByHash(db: Db, tokenHash: string) {
  const [row] = await db
    .select()
    .from(invitations)
    .where(
      and(
        eq(invitations.tokenHash, tokenHash),
        isNull(invitations.acceptedAt),
        gt(invitations.expiresAt, new Date()),
      ),
    )
    .limit(1)
  return row ?? null
}

export async function getInvitationPublicMetadataByHash(db: Db, tokenHash: string) {
  const [row] = await db
    .select({
      email: invitations.email,
      tenantName: tenants.name,
      inviterName: sql<string | null>`NULL`,
    })
    .from(invitations)
    .innerJoin(tenants, eq(tenants.id, invitations.tenantId))
    .where(
      and(
        eq(invitations.tokenHash, tokenHash),
        isNull(invitations.acceptedAt),
        gt(invitations.expiresAt, new Date()),
      ),
    )
    .limit(1)

  return row ?? null
}


/** Returns the admin user with their role's permissions resolved. */
export async function findAdminWithPermissions(db: Db, id: string) {
  const [row] = await db
    .select({
      id: adminUsers.id,
      email: adminUsers.email,
      passwordHash: adminUsers.passwordHash,
      totpSecret: adminUsers.totpSecret,
      roleId: adminUsers.roleId,
      role: adminRoles.name,
      permissions: adminRoles.permissions,
    })
    .from(adminUsers)
    .leftJoin(adminRoles, eq(adminUsers.roleId, adminRoles.id))
    .where(eq(adminUsers.id, id))
    .limit(1)
  return row ?? null
}