/**
 * Membership query helpers — app-shell.
 *
 * getUserActiveMemberships: returns all active tenant memberships for a user,
 * joined with tenant + role information needed by the tenant switcher.
 * Frozen and pending-approval memberships are excluded.
 *
 * getMembersByPermission: returns distinct (userId, tenantId) pairs for all
 * active members across given tenants that hold a specific permission key.
 * Used by cron jobs (e.g. withholding-expiry-check) to find notification targets.
 */
import { and, eq, inArray } from 'drizzle-orm'
import type { Db } from '../client'
import type { UserId } from '@zync/types'
import { tenantMemberships, tenants, roles, rolePermissions, permissions } from '../schema'

export interface TenantMembershipSummary {
  tenantId: string
  tenantSlug: string
  tenantName: string
  /** logo_url is available once the settings-module adds it to tenants */
  logoUrl: string | null
  role: string
  tier: string
  status: 'active'
}

export interface MemberPermissionRow {
  userId: string
  tenantId: string
}

/**
 * Returns distinct (userId, tenantId) pairs for all active members across the
 * given tenants that hold the specified permission key (via their role).
 * Used by cron jobs to identify notification targets (e.g. payouts:manage).
 */
export async function getMembersByPermission(
  db: Db,
  tenantIds: string[],
  permissionKey: string,
): Promise<MemberPermissionRow[]> {
  return db
    .selectDistinct({ userId: tenantMemberships.userId, tenantId: tenantMemberships.tenantId })
    .from(tenantMemberships)
    .innerJoin(roles, eq(roles.id, tenantMemberships.roleId))
    .innerJoin(rolePermissions, eq(rolePermissions.roleId, roles.id))
    .innerJoin(permissions, eq(permissions.id, rolePermissions.permissionId))
    .where(
      and(
        inArray(tenantMemberships.tenantId, tenantIds),
        eq(tenantMemberships.status, 'active'),
        eq(permissions.key, permissionKey),
      ),
    )
}

/**
 * Returns all active memberships for a user across all tenants.
 * Used by GET /api/auth/memberships (app-shell tenant switcher).
 */
export async function getUserActiveMemberships(
  db: Db,
  userId: UserId,
): Promise<TenantMembershipSummary[]> {
  const rows = await db
    .select({
      tenantId: tenants.id,
      tenantSlug: tenants.slug,
      tenantName: tenants.name,
      logoUrl: tenants.logoUrl,
      tier: tenants.tier,
      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.status, 'active'),
      ),
    )

  return rows.map((r) => ({
    tenantId: r.tenantId,
    tenantSlug: r.tenantSlug,
    tenantName: r.tenantName,
    logoUrl: r.logoUrl ?? null,
    role: r.role,
    tier: r.tier,
    status: 'active' as const,
  }))
}