/**
 * Admin tenant-management queries — admin-dashboard.
 *
 * All queries here are cross-tenant (no tenant_id scope) and require admin
 * session. They aggregate tenant metadata, user counts, and usage for the
 * admin control plane.
 */
import { and, asc, count, desc, eq, ilike, or, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { tenants } from '../schema/tenants'
import { roles, tenantMemberships } from '../schema/rbac'
import { users } from '../schema/users'
import { invitations } from '../schema/auth-tokens'

// ── Types ─────────────────────────────────────────────────────────────────────

export interface AdminTenantRow {
  id: string
  slug: string
  name: string
  tier: string
  countryCode: string
  createdAt: Date
  status: string
  userCount: number
  ltv: string
}

export interface AdminTenantDetail {
  id: string
  slug: string
  name: string
  tier: string
  countryCode: string
  createdAt: Date
  status: string
  activeUserCount: number
  frozenUserCount: number
  pendingCount: number
  ltv: string
}

export interface AdminTenantListParams {
  search?: string
  tier?: string
  status?: string
  sort?: 'name' | 'created_at' | 'tier' | 'status'
  order?: 'asc' | 'desc'
  page?: number
  limit?: number
}

export interface PaginatedAdminTenants {
  items: AdminTenantRow[]
  total: number
  page: number
  limit: number
}

// ── Tenant status helper ──────────────────────────────────────────────────────

/**
 * Tenants don't have a first-class "status" column — we use a convention:
 * 'suspended' is stored in tenants.settings JSONB as { suspended: true }.
 * For v1 we return 'active' for all tenants (suspension is a future feature).
 */
function tenantStatus(_tenantRow: { slug: string }): string {
  return 'active'
}

// ── List tenants ──────────────────────────────────────────────────────────────

export async function listTenantsAdmin(
  db: Db,
  params: AdminTenantListParams = {},
): Promise<PaginatedAdminTenants> {
  const { search, tier, status, sort = 'created_at', order = 'desc', page = 1, limit = 50 } = params
  const offset = (page - 1) * limit

  // Build filter conditions
  const conditions = []
  if (search) {
    conditions.push(
      or(ilike(tenants.name, `%${search}%`), ilike(tenants.slug, `%${search}%`)),
    )
  }
  if (tier) {
    conditions.push(eq(tenants.tier, tier))
  }
  if (status && status !== 'active') {
    return { items: [], total: 0, page, limit }
  }

  const whereClause = conditions.length > 0 ? and(...conditions) : undefined

  // Subquery: user count per tenant
  const memberCountSq = db
    .select({
      tenantId: tenantMemberships.tenantId,
      cnt: count(tenantMemberships.userId).as('cnt'),
    })
    .from(tenantMemberships)
    .where(eq(tenantMemberships.status, 'active'))
    .groupBy(tenantMemberships.tenantId)
    .as('member_counts')

  const orderColumn =
    sort === 'name'
      ? tenants.name
      : sort === 'tier'
        ? tenants.tier
        : tenants.createdAt

  const orderedRows = await db
    .select({
      id: tenants.id,
      slug: tenants.slug,
      name: tenants.name,
      tier: tenants.tier,
      countryCode: tenants.countryCode,
      createdAt: tenants.createdAt,
      userCount: sql<number>`COALESCE(${memberCountSq.cnt}, 0)`,
    })
    .from(tenants)
    .leftJoin(memberCountSq, eq(tenants.id, memberCountSq.tenantId))
    .where(whereClause)
    .orderBy(order === 'asc' ? asc(orderColumn) : desc(orderColumn))
    .limit(limit)
    .offset(offset)

  // Count total
  const [countRow] = await db
    .select({ total: count(tenants.id) })
    .from(tenants)
    .where(whereClause)

  const total = countRow?.total ?? 0

  const rows = orderedRows.map((r) => ({
    ...r,
    status: tenantStatus(r),
    ltv: '0', // payments aggregation is owned by billing module; placeholder
  }))

  const items =
    sort === 'status'
      ? [...rows].sort((a, b) =>
          order === 'asc' ? a.status.localeCompare(b.status) : b.status.localeCompare(a.status),
        )
      : rows

  return { items, total, page, limit }
}

// ── Tenant detail ─────────────────────────────────────────────────────────────

export async function getTenantDetailAdmin(
  db: Db,
  slug: string,
): Promise<AdminTenantDetail | null> {
  const [tenant] = await db
    .select()
    .from(tenants)
    .where(eq(tenants.slug, slug))
    .limit(1)

  if (!tenant) return null

  // Membership counts grouped by status
  const membershipCounts = await db
    .select({
      status: tenantMemberships.status,
      cnt: count(tenantMemberships.userId),
    })
    .from(tenantMemberships)
    .where(eq(tenantMemberships.tenantId, tenant.id))
    .groupBy(tenantMemberships.status)

  const activeCount = membershipCounts.find((r) => r.status === 'active')?.cnt ?? 0
  const frozenCount = membershipCounts.find((r) => r.status === 'frozen')?.cnt ?? 0
  const pendingCount = membershipCounts.find((r) => r.status === 'pending_approval')?.cnt ?? 0

  return {
    id: tenant.id,
    slug: tenant.slug,
    name: tenant.name,
    tier: tenant.tier,
    countryCode: tenant.countryCode,
    createdAt: tenant.createdAt,
    status: tenantStatus(tenant),
    activeUserCount: Number(activeCount),
    frozenUserCount: Number(frozenCount),
    pendingCount: Number(pendingCount),
    ltv: '0', // placeholder — billing module
  }
}

// ── Tenant users ──────────────────────────────────────────────────────────────

export interface AdminTenantUserRow {
  userId: string
  email: string
  name: string | null
  roleId: string
  roleName: string | null
  status: string
  freezeReason: string | null
  emailVerifiedAt: Date | null
  lastLoginAt: Date | null
  userCreatedAt: Date
  memberSince: Date
}

export interface AdminTenantUserListParams {
  page?: number
  limit?: number
}

export interface PaginatedAdminTenantUsers {
  items: AdminTenantUserRow[]
  total: number
  page: number
  limit: number
}

export async function listTenantUsersAdmin(
  db: Db,
  tenantId: string,
  params: AdminTenantUserListParams = {},
): Promise<PaginatedAdminTenantUsers> {
  const { page = 1, limit = 50 } = params
  const offset = (page - 1) * limit

  const rows = await db
    .select({
      userId: users.id,
      email: users.email,
      name: users.name,
      roleId: tenantMemberships.roleId,
      roleName: roles.name,
      status: tenantMemberships.status,
      freezeReason: tenantMemberships.freezeReason,
      emailVerifiedAt: users.emailVerifiedAt,
      lastLoginAt: users.lastLoginAt,
      userCreatedAt: users.createdAt,
      memberSince: tenantMemberships.createdAt,
    })
    .from(tenantMemberships)
    .innerJoin(users, eq(tenantMemberships.userId, users.id))
    .leftJoin(roles, eq(tenantMemberships.roleId, roles.id))
    .where(eq(tenantMemberships.tenantId, tenantId))
    .orderBy(desc(tenantMemberships.createdAt))
    .limit(limit)
    .offset(offset)

  const [countRow] = await db
    .select({ total: count(tenantMemberships.userId) })
    .from(tenantMemberships)
    .where(eq(tenantMemberships.tenantId, tenantId))

  return {
    items: rows,
    total: Number(countRow?.total ?? 0),
    page,
    limit,
  }
}

// ── Tenant-level invitation management ───────────────────────────────────────

export async function revokePendingInvitation(
  db: Db,
  tenantId: string,
  invitationId: string,
): Promise<boolean> {
  const result = await db
    .delete(invitations)
    .where(
      and(
        eq(invitations.id, invitationId),
        eq(invitations.tenantId, tenantId),
        sql`${invitations.acceptedAt} IS NULL`,
      ),
    )
    .returning({ id: invitations.id })

  return result.length > 0
}

// ── Tenant membership mutations ───────────────────────────────────────────────

export async function approvePendingMember(
  db: Db,
  tenantId: string,
  userId: string,
): Promise<boolean> {
  const result = await db
    .update(tenantMemberships)
    .set({ status: 'active' })
    .where(
      and(
        eq(tenantMemberships.tenantId, tenantId),
        eq(tenantMemberships.userId, userId),
        eq(tenantMemberships.status, 'pending_approval'),
      ),
    )
    .returning({ userId: tenantMemberships.userId })

  return result.length > 0
}

export async function freezeTenantMember(
  db: Db,
  tenantId: string,
  userId: string,
  reason: string,
): Promise<boolean> {
  const result = await db
    .update(tenantMemberships)
    .set({ status: 'frozen', freezeReason: reason })
    .where(
      and(
        eq(tenantMemberships.tenantId, tenantId),
        eq(tenantMemberships.userId, userId),
      ),
    )
    .returning({ userId: tenantMemberships.userId })

  return result.length > 0
}

export async function unfreezeTenantMember(
  db: Db,
  tenantId: string,
  userId: string,
): Promise<boolean> {
  const result = await db
    .update(tenantMemberships)
    .set({ status: 'active', freezeReason: null })
    .where(
      and(
        eq(tenantMemberships.tenantId, tenantId),
        eq(tenantMemberships.userId, userId),
        eq(tenantMemberships.status, 'frozen'),
      ),
    )
    .returning({ userId: tenantMemberships.userId })

  return result.length > 0
}

// ── Revoke refresh tokens for a user+tenant ───────────────────────────────────

export async function revokeUserRefreshTokensForTenant(
  db: Db,
  userId: string,
  tenantId: string,
): Promise<void> {
  const { refreshTokens } = await import('../schema/auth-tokens')
  await db
    .update(refreshTokens)
    .set({ revokedAt: new Date() })
    .where(
      and(
        eq(refreshTokens.userId, userId),
        eq(refreshTokens.tenantId, tenantId),
        sql`${refreshTokens.revokedAt} IS NULL`,
      ),
    )
}