/**
 * Team member + pending invitations queries — settings-module (wave-10 leaf 2).
 *
 * listTeamMembers     — active/frozen/pending-approval members with role name
 * listPendingInvites  — uninvited, unexpired invitations for a tenant
 * updateMemberRole    — change a member's role
 * transferOwnership   — swap owner role from actor to target
 * removeMember        — delete a membership row
 */
import { and, eq, isNull, gt, sql } from 'drizzle-orm'
import type { Db } from '../client'
import { tenantMemberships, users, roles, invitations } from '../schema'
import { auditLog } from './_audit-forward'

export interface TeamMemberRow {
  id: string
  name: string | null
  email: string
  roleId: string
  role: string
  status: string
  joinedAt: Date
  hourlyCost: string | null
}

export interface PendingInviteRow {
  id: string
  email: string
  roleId: string
  role: string
  expiresAt: Date
  createdAt: Date
}

/**
 * Returns all memberships (active/frozen/pending_approval) for a tenant,
 * joined with user + role data.
 */
/** Lowercase emails of active tenant members — used for inbound expense email allowlist. */
export async function getActiveMemberEmailsForTenant(db: Db, tenantId: string): Promise<string[]> {
  const rows = await db
    .select({ email: users.email })
    .from(tenantMemberships)
    .innerJoin(users, eq(users.id, tenantMemberships.userId))
    .where(and(eq(tenantMemberships.tenantId, tenantId), eq(tenantMemberships.status, 'active')))
  return rows.map((r) => r.email.toLowerCase())
}

export async function listTeamMembers(
  db: Db,
  tenantId: string,
): Promise<TeamMemberRow[]> {
  const rows = await db
    .select({
      id: users.id,
      name: users.name,
      email: users.email,
      roleId: roles.id,
      role: roles.name,
      status: tenantMemberships.status,
      joinedAt: tenantMemberships.createdAt,
      hourlyCost: sql<string | null>`${users.hourlyCost}::text`,
    })
    .from(tenantMemberships)
    .innerJoin(users, eq(users.id, tenantMemberships.userId))
    .innerJoin(roles, eq(roles.id, tenantMemberships.roleId))
    .where(eq(tenantMemberships.tenantId, tenantId))

  return rows.map((r) => ({
    id: r.id,
    name: r.name,
    email: r.email,
    roleId: r.roleId,
    role: r.role,
    status: r.status,
    joinedAt: r.joinedAt,
    hourlyCost: r.hourlyCost,
  }))
}

export async function updateUserHourlyCost(
  db: Db,
  userId: string,
  hourlyCost: string | null,
): Promise<void> {
  await db
    .update(users)
    .set({ hourlyCost })
    .where(eq(users.id, userId))
}

/**
 * Returns pending (not yet accepted, not expired) invitations for a tenant.
 */
export async function listPendingInvites(
  db: Db,
  tenantId: string,
): Promise<PendingInviteRow[]> {
  const rows = await db
    .select({
      id: invitations.id,
      email: invitations.email,
      roleId: invitations.roleId,
      role: roles.name,
      expiresAt: invitations.expiresAt,
      createdAt: invitations.createdAt,
    })
    .from(invitations)
    .innerJoin(roles, eq(roles.id, invitations.roleId))
    .where(
      and(
        eq(invitations.tenantId, tenantId),
        isNull(invitations.acceptedAt),
        gt(invitations.expiresAt, new Date()),
      ),
    )

  return rows.map((r) => ({
    id: r.id,
    email: r.email,
    roleId: r.roleId,
    role: r.role,
    expiresAt: r.expiresAt,
    createdAt: r.createdAt,
  }))
}

/**
 * Updates the role of a tenant membership (role change).
 * Caller must verify the actor has permission and cannot escalate beyond own role.
 */
export async function updateMemberRole(
  db: Db,
  tenantId: string,
  targetUserId: string,
  newRoleId: string,
  actorId: string,
  opts?: { actorIp?: string | null; requestId?: string | null },
): Promise<void> {
  await db.transaction(async (tx) => {
    await tx
      .update(tenantMemberships)
      .set({ roleId: newRoleId })
      .where(
        and(
          eq(tenantMemberships.tenantId, tenantId),
          eq(tenantMemberships.userId, targetUserId),
        ),
      )

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'user',
      entityId: targetUserId,
      action: 'user.role_changed',
      changes: { roleId: [null, newRoleId] },
      ip: opts?.actorIp ?? null,
      requestId: opts?.requestId ?? null,
    })
  })
}

/**
 * Removes a tenant membership row entirely.
 * Caller must verify the actor cannot self-remove or remove owner.
 */
export async function removeTenantMember(
  db: Db,
  tenantId: string,
  targetUserId: string,
  actorId: string,
  opts?: { actorIp?: string | null; requestId?: string | null },
): Promise<void> {
  await db.transaction(async (tx) => {
    await tx
      .delete(tenantMemberships)
      .where(
        and(
          eq(tenantMemberships.tenantId, tenantId),
          eq(tenantMemberships.userId, targetUserId),
        ),
      )

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'user',
      entityId: targetUserId,
      action: 'user.removed',
      changes: {},
      ip: opts?.actorIp ?? null,
      requestId: opts?.requestId ?? null,
    })
  })
}

/**
 * Revokes a pending invitation by ID (hard delete).
 */
export async function revokePendingInvite(
  db: Db,
  tenantId: string,
  inviteId: string,
): Promise<void> {
  await db
    .delete(invitations)
    .where(
      and(
        eq(invitations.tenantId, tenantId),
        eq(invitations.id, inviteId),
      ),
    )
}