/**
 * Custom roles DB service layer — settings-roles (wave 11).
 *
 * CRUD for tenant custom roles (is_system_role = false), using the locked
 * upstream tables: roles, permissions, role_permissions, tenant_memberships.
 *
 * System roles (OWNER, ADMIN, MEMBER, VIEWER, CONTRACTOR) are read-only here;
 * all attempts to mutate them throw SystemRoleImmutableError.
 *
 * Routes MUST NOT import raw Drizzle tables — they call these helpers.
 */
import { eq, and, count, inArray } from 'drizzle-orm'
import type { Db } from '../client'
import { roles, permissions, rolePermissions, tenantMemberships } from '../schema/rbac'
import { auditLog } from './_audit-forward'

// ── Permission vocabulary (locked set for tenant roles) ───────────────────────

export const TENANT_PERMISSION_VOCABULARY = [
  'customers:read',
  'customers:write',
  'invoices:read',
  'invoices:write',
  'inventory:read',
  'inventory:write',
  'inventory:manage',
  'projects:read',
  'projects:write',
  'tasks:read',
  'tasks:write',
  'time:read',
  'time:write',
  'expenses:read',
  'expenses:write',
  'contracts:read',
  'contracts:write',
  'proposals:read',
  'proposals:write',
  'support:read',
  'support:write',
  'settings:read',
  'settings:write',
  'users:manage',
  'reports:read',
] as const

export type TenantPermissionKey = (typeof TENANT_PERMISSION_VOCABULARY)[number]

// ── Errors ────────────────────────────────────────────────────────────────────

export class SystemRoleImmutableError extends Error {
  constructor() {
    super('System roles cannot be modified or deleted')
    this.name = 'SystemRoleImmutableError'
  }
}

export class RoleNameConflictError extends Error {
  constructor() {
    super('A role with this name already exists in this tenant')
    this.name = 'RoleNameConflictError'
  }
}

export class RoleHasMembersError extends Error {
  memberCount: number
  constructor(memberCount: number) {
    super(`Role has ${memberCount} members; reassign_to_role_id required`)
    this.name = 'RoleHasMembersError'
    this.memberCount = memberCount
  }
}

export class RoleNotFoundError extends Error {
  constructor() {
    super('Role not found')
    this.name = 'RoleNotFoundError'
  }
}

export class UnknownPermissionKeyError extends Error {
  constructor(key: string) {
    super(`Unknown permission key: ${key}`)
    this.name = 'UnknownPermissionKeyError'
  }
}

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

export interface CustomRoleDTO {
  id: string
  name: string
  is_system_role: boolean
  permissions: string[]
  member_count: number
}

// ── helpers ───────────────────────────────────────────────────────────────────

async function resolvePermissionIds(
  db: Db,
  permissionKeys: string[],
): Promise<string[]> {
  if (permissionKeys.length === 0) return []

  // Validate all keys are in vocabulary
  for (const key of permissionKeys) {
    if (!TENANT_PERMISSION_VOCABULARY.includes(key as TenantPermissionKey)) {
      throw new UnknownPermissionKeyError(key)
    }
  }

  const rows = await db
    .select({ id: permissions.id, key: permissions.key })
    .from(permissions)
    .where(inArray(permissions.key, permissionKeys))

  return rows.map((r) => r.id)
}

async function getPermissionKeysForRole(db: Db, roleId: string): Promise<string[]> {
  const rows = await db
    .select({ key: permissions.key })
    .from(rolePermissions)
    .innerJoin(permissions, eq(permissions.id, rolePermissions.permissionId))
    .where(eq(rolePermissions.roleId, roleId))

  return rows.map((r) => r.key)
}

async function getMemberCount(db: Db, roleId: string): Promise<number> {
  const [row] = await db
    .select({ cnt: count(tenantMemberships.userId) })
    .from(tenantMemberships)
    .where(eq(tenantMemberships.roleId, roleId))

  return Number(row?.cnt ?? 0)
}

// ── listCustomRoles ───────────────────────────────────────────────────────────

export async function listCustomRoles(
  db: Db,
  tenantId: string,
): Promise<CustomRoleDTO[]> {
  const roleRows = await db
    .select({
      id: roles.id,
      name: roles.name,
      isSystemRole: roles.isSystemRole,
    })
    .from(roles)
    .where(eq(roles.tenantId, tenantId))
    .orderBy(roles.isSystemRole, roles.name)

  const result: CustomRoleDTO[] = []
  for (const role of roleRows) {
    const permKeys = await getPermissionKeysForRole(db, role.id)
    const memberCount = await getMemberCount(db, role.id)
    result.push({
      id: role.id,
      name: role.name,
      is_system_role: role.isSystemRole,
      permissions: permKeys,
      member_count: memberCount,
    })
  }

  return result
}

// ── createCustomRole ──────────────────────────────────────────────────────────

export async function createCustomRole(
  db: Db,
  tenantId: string,
  actorId: string,
  name: string,
  permissionKeys: string[],
): Promise<CustomRoleDTO> {
  const permissionIds = await resolvePermissionIds(db, permissionKeys)

  let roleId: string
  try {
    await db.transaction(async (tx) => {
      const [inserted] = await tx
        .insert(roles)
        .values({ tenantId, name, isSystemRole: false })
        .returning({ id: roles.id })

      roleId = inserted!.id

      if (permissionIds.length > 0) {
        await tx.insert(rolePermissions).values(
          permissionIds.map((permId) => ({ roleId: roleId!, permissionId: permId })),
        )
      }

      await tx.insert(auditLog).values({
        tenantId,
        actorId,
        actorType: 'user',
        entityType: 'role',
        entityId: roleId!,
        action: 'role.created',
      })
    })
  } catch (err) {
    // PostgreSQL unique violation code 23505 (UNIQUE constraint)
    if (err instanceof Error && err.message.includes('roles_tenant_name_unique')) {
      throw new RoleNameConflictError()
    }
    throw err
  }

  return {
    id: roleId!,
    name,
    is_system_role: false,
    permissions: permissionKeys,
    member_count: 0,
  }
}

// ── updateCustomRole ──────────────────────────────────────────────────────────

export async function updateCustomRole(
  db: Db,
  tenantId: string,
  actorId: string,
  roleId: string,
  patch: { name?: string; permissions?: string[] },
): Promise<CustomRoleDTO> {
  const [existing] = await db
    .select({ id: roles.id, name: roles.name, isSystemRole: roles.isSystemRole, tenantId: roles.tenantId })
    .from(roles)
    .where(and(eq(roles.id, roleId), eq(roles.tenantId, tenantId)))
    .limit(1)

  if (!existing) throw new RoleNotFoundError()
  if (existing.isSystemRole) throw new SystemRoleImmutableError()

  const permissionIds =
    patch.permissions !== undefined
      ? await resolvePermissionIds(db, patch.permissions)
      : null

  try {
    await db.transaction(async (tx) => {
      if (patch.name !== undefined) {
        await tx
          .update(roles)
          .set({ name: patch.name })
          .where(and(eq(roles.tenantId, tenantId), eq(roles.id, roleId)))
      }

      if (permissionIds !== null) {
        await tx.delete(rolePermissions).where(eq(rolePermissions.roleId, roleId))
        if (permissionIds.length > 0) {
          await tx.insert(rolePermissions).values(
            permissionIds.map((permId) => ({ roleId, permissionId: permId })),
          )
        }
      }

      await tx.insert(auditLog).values({
        tenantId,
        actorId,
        actorType: 'user',
        entityType: 'role',
        entityId: roleId,
        action: 'role.updated',
      })
    })
  } catch (err) {
    if (err instanceof Error && err.message.includes('roles_tenant_name_unique')) {
      throw new RoleNameConflictError()
    }
    throw err
  }

  const permKeys = patch.permissions ?? (await getPermissionKeysForRole(db, roleId))
  const memberCount = await getMemberCount(db, roleId)

  return {
    id: roleId,
    name: patch.name ?? existing.name,
    is_system_role: false,
    permissions: permKeys,
    member_count: memberCount,
  }
}

// ── deleteCustomRole ──────────────────────────────────────────────────────────

export async function deleteCustomRole(
  db: Db,
  tenantId: string,
  actorId: string,
  roleId: string,
  reassignToRoleId?: string,
): Promise<{ reassignedUserIds: string[] }> {
  const [existing] = await db
    .select({ id: roles.id, isSystemRole: roles.isSystemRole })
    .from(roles)
    .where(and(eq(roles.id, roleId), eq(roles.tenantId, tenantId)))
    .limit(1)

  if (!existing) throw new RoleNotFoundError()
  if (existing.isSystemRole) throw new SystemRoleImmutableError()

  const memberCount = await getMemberCount(db, roleId)

  if (memberCount > 0) {
    if (!reassignToRoleId) {
      throw new RoleHasMembersError(memberCount)
    }

    // Verify the target role exists in this tenant
    const [target] = await db
      .select({ id: roles.id })
      .from(roles)
      .where(and(eq(roles.id, reassignToRoleId), eq(roles.tenantId, tenantId)))
      .limit(1)

    if (!target) throw new RoleNotFoundError()

    // Get affected user IDs before reassignment
    const memberRows = await db
      .select({ userId: tenantMemberships.userId })
      .from(tenantMemberships)
      .where(eq(tenantMemberships.roleId, roleId))

    const reassignedUserIds = memberRows.map((r) => r.userId)

    await db.transaction(async (tx) => {
      // (1) Reassign memberships
      await tx
        .update(tenantMemberships)
        .set({ roleId: reassignToRoleId })
        .where(eq(tenantMemberships.roleId, roleId))

      // (2) Delete role permissions
      await tx.delete(rolePermissions).where(eq(rolePermissions.roleId, roleId))

      // (3) Delete the role
      await tx.delete(roles).where(and(eq(roles.tenantId, tenantId), eq(roles.id, roleId)))

      await tx.insert(auditLog).values({
        tenantId,
        actorId,
        actorType: 'user',
        entityType: 'role',
        entityId: roleId,
        action: 'role.deleted',
      })
    })

    return { reassignedUserIds }
  }

  await db.transaction(async (tx) => {
    await tx.delete(rolePermissions).where(eq(rolePermissions.roleId, roleId))
    await tx.delete(roles).where(and(eq(roles.tenantId, tenantId), eq(roles.id, roleId)))

    await tx.insert(auditLog).values({
      tenantId,
      actorId,
      actorType: 'user',
      entityType: 'role',
      entityId: roleId,
      action: 'role.deleted',
    })
  })

  return { reassignedUserIds: [] }
}