import { eq, and, count, ilike, inArray, isNull, type Column } from 'drizzle-orm'
import type { PostgresJsDatabase } from 'drizzle-orm/postgres-js'
import { storageFolders, storageFolderPermissions, storageFiles } from '../db/schema/storage.js'
import { userRoles } from '../db/schema/rbac.js'
import type { StorageProvider } from '../storage/index.js'
import { randomUUID } from 'crypto'
import { withPagination, withSort, buildSearchCondition, paginatedResponse } from '../utils/pagination.js'
import type { PaginationInput } from '@lunarfront/shared/schemas'

const MAX_PARENT_DEPTH = 50

const ACCESS_RANK: Record<string, number> = { traverse: 0, view: 1, edit: 2, admin: 3 }

function highestAccess(a: string, b: string): 'admin' | 'edit' | 'view' | 'traverse' {
  return (ACCESS_RANK[a] ?? 0) >= (ACCESS_RANK[b] ?? 0) ? a as any : b as any
}

function getExtension(contentType: string): string {
  const map: Record<string, string> = {
    'image/jpeg': 'jpg', 'image/png': 'png', 'image/webp': 'webp',
    'application/pdf': 'pdf',
    'application/vnd.openxmlformats-officedocument.wordprocessingml.document': 'docx',
    'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet': 'xlsx',
    'application/msword': 'doc', 'application/vnd.ms-excel': 'xls',
    'text/plain': 'txt', 'text/csv': 'csv',
  }
  // Sanitize: only allow alphanumeric extensions
  const fallback = (contentType.split('/')[1] ?? 'bin').replace(/[^a-zA-Z0-9]/g, '')
  return map[contentType] ?? (fallback || 'bin')
}

// --- Permission Service ---

export const StoragePermissionService = {
  /**
   * Check if a user has at least the given minimum access level on a folder.
   * Use this instead of canAccess() to enforce traverse vs view distinction.
   */
  async hasAccess(db: PostgresJsDatabase<any>, folderId: string, userId: string, minLevel: 'traverse' | 'view' | 'edit' | 'admin' = 'view'): Promise<boolean> {
    const level = await this.getAccessLevel(db, folderId, userId)
    if (!level) return false
    return ACCESS_RANK[level] >= ACCESS_RANK[minLevel]
  },

  /**
   * @deprecated Use hasAccess() with explicit minLevel instead.
   * canAccess returns true for traverse, which may not be intended.
   */
  async canAccess(db: PostgresJsDatabase<any>, folderId: string, userId: string): Promise<boolean> {
    const level = await this.getAccessLevel(db, folderId, userId)
    return level !== null
  },

  async getAccessLevel(db: PostgresJsDatabase<any>, folderId: string, userId: string): Promise<'admin' | 'edit' | 'view' | 'traverse' | null> {
    const visited = new Set<string>()
    return this._getAccessLevelInner(db, folderId, userId, visited, false)
  },

  /**
   * Inner recursive access level resolver with cycle protection and depth limit.
   * isInherited: true when recursing into parent folders — isPublic only applies to the directly requested folder.
   */
  async _getAccessLevelInner(
    db: PostgresJsDatabase<any>, folderId: string, userId: string,
    visited: Set<string>, isInherited: boolean,
  ): Promise<'admin' | 'edit' | 'view' | 'traverse' | null> {
    if (visited.has(folderId) || visited.size >= MAX_PARENT_DEPTH) return null
    visited.add(folderId)

    const [folder] = await db.select({ isPublic: storageFolders.isPublic, createdBy: storageFolders.createdBy, parentId: storageFolders.parentId })
      .from(storageFolders).where(eq(storageFolders.id, folderId)).limit(1)
    if (!folder) return null

    // Creator always has admin
    if (folder.createdBy === userId) return 'admin'

    // Collect the best explicit permission on this folder
    let best: 'admin' | 'edit' | 'view' | 'traverse' | null = null

    // Check direct user permission
    const [userPerm] = await db.select({ accessLevel: storageFolderPermissions.accessLevel }).from(storageFolderPermissions)
      .where(and(eq(storageFolderPermissions.folderId, folderId), eq(storageFolderPermissions.userId, userId)))
      .limit(1)
    if (userPerm) best = userPerm.accessLevel

    // Check role-based permissions — pick highest across all roles
    const userRoleRows = await db.select({ roleId: userRoles.roleId }).from(userRoles).where(eq(userRoles.userId, userId))
    if (userRoleRows.length > 0) {
      const roleIds = userRoleRows.map((r) => r.roleId)
      const rolePerms = await db.select({ accessLevel: storageFolderPermissions.accessLevel }).from(storageFolderPermissions)
        .where(and(eq(storageFolderPermissions.folderId, folderId), inArray(storageFolderPermissions.roleId, roleIds)))
      for (const rp of rolePerms) {
        best = best ? highestAccess(best, rp.accessLevel) : rp.accessLevel
      }
    }

    if (best) return best

    // Check parent (inherited permissions) — only explicit permissions inherit, not isPublic
    if (folder.parentId) {
      const inherited = await this._getAccessLevelInner(db, folder.parentId, userId, visited, true)
      if (inherited) return inherited
    }

    // isPublic only grants view on the directly requested folder, not via inheritance
    // This prevents a public grandparent from granting view on a private child
    if (!isInherited && folder.isPublic) return 'view'

    return null
  },

  async listPermissions(db: PostgresJsDatabase<any>, folderId: string) {
    return db.select().from(storageFolderPermissions).where(eq(storageFolderPermissions.folderId, folderId))
  },

  async getPermissionById(db: PostgresJsDatabase<any>, permissionId: string) {
    const [perm] = await db.select().from(storageFolderPermissions).where(eq(storageFolderPermissions.id, permissionId)).limit(1)
    return perm ?? null
  },

  async setPermission(db: PostgresJsDatabase<any>, folderId: string, roleId: string | undefined, userId: string | undefined, accessLevel: string) {
    // Remove existing permission for this role/user on this folder
    if (roleId) {
      await db.delete(storageFolderPermissions).where(and(eq(storageFolderPermissions.folderId, folderId), eq(storageFolderPermissions.roleId, roleId)))
    }
    if (userId) {
      await db.delete(storageFolderPermissions).where(and(eq(storageFolderPermissions.folderId, folderId), eq(storageFolderPermissions.userId, userId)))
    }

    const [perm] = await db.insert(storageFolderPermissions).values({
      folderId,
      roleId: roleId ?? null,
      userId: userId ?? null,
      accessLevel: accessLevel as any,
    }).returning()

    // Auto-grant 'traverse' up the ancestor chain so the user can navigate to this folder
    if (accessLevel !== 'traverse') {
      await this.ensureTraverseAncestors(db, folderId, roleId, userId)
    }

    return perm
  },

  /**
   * Ensure all ancestor folders have at least 'traverse' access for this role/user.
   * Never upgrades — only adds traverse where there's no existing permission.
   */
  async ensureTraverseAncestors(db: PostgresJsDatabase<any>, folderId: string, roleId: string | undefined, userId: string | undefined) {
    const [folder] = await db.select({ parentId: storageFolders.parentId }).from(storageFolders).where(eq(storageFolders.id, folderId)).limit(1)
    if (!folder?.parentId) return

    const visited = new Set<string>()
    let currentParentId: string | null = folder.parentId
    while (currentParentId && !visited.has(currentParentId) && visited.size < MAX_PARENT_DEPTH) {
      visited.add(currentParentId)

      const whereClause = roleId
        ? and(eq(storageFolderPermissions.folderId, currentParentId), eq(storageFolderPermissions.roleId, roleId))
        : and(eq(storageFolderPermissions.folderId, currentParentId), eq(storageFolderPermissions.userId, userId!))

      const [existing] = await db.select({ id: storageFolderPermissions.id }).from(storageFolderPermissions).where(whereClause).limit(1)

      if (!existing) {
        await db.insert(storageFolderPermissions).values({
          folderId: currentParentId,
          roleId: roleId ?? null,
          userId: userId ?? null,
          accessLevel: 'traverse' as any,
        })
      }

      const [parent] = await db.select({ parentId: storageFolders.parentId }).from(storageFolders).where(eq(storageFolders.id, currentParentId)).limit(1)
      currentParentId = parent?.parentId ?? null
    }
  },

  async removePermission(db: PostgresJsDatabase<any>, permissionId: string) {
    // Fetch before deleting so we can clean up ancestors
    const [perm] = await db.select().from(storageFolderPermissions).where(eq(storageFolderPermissions.id, permissionId)).limit(1)
    if (!perm) return null

    const [deleted] = await db.delete(storageFolderPermissions).where(eq(storageFolderPermissions.id, permissionId)).returning()

    // Clean up orphaned traverse permissions on ancestors
    await this.cleanupTraverseAncestors(db, perm.folderId, perm.roleId ?? undefined, perm.userId ?? undefined)

    return deleted ?? null
  },

  /**
   * After removing a permission, check if traverse grants on ancestors are still needed.
   * A traverse permission is orphaned if the user/role has no other permissions on the
   * folder itself or any of its descendants.
   */
  async cleanupTraverseAncestors(db: PostgresJsDatabase<any>, folderId: string, roleId: string | undefined, userId: string | undefined) {
    const [folder] = await db.select({ parentId: storageFolders.parentId }).from(storageFolders).where(eq(storageFolders.id, folderId)).limit(1)
    if (!folder?.parentId) return

    const visited = new Set<string>()
    let currentId: string | null = folder.parentId
    while (currentId && !visited.has(currentId) && visited.size < MAX_PARENT_DEPTH) {
      visited.add(currentId)

      // Find the traverse permission for this ancestor
      const whereClause = roleId
        ? and(eq(storageFolderPermissions.folderId, currentId), eq(storageFolderPermissions.roleId, roleId), eq(storageFolderPermissions.accessLevel, 'traverse'))
        : and(eq(storageFolderPermissions.folderId, currentId), eq(storageFolderPermissions.userId, userId!), eq(storageFolderPermissions.accessLevel, 'traverse'))

      const [traversePerm] = await db.select({ id: storageFolderPermissions.id }).from(storageFolderPermissions).where(whereClause).limit(1)
      if (!traversePerm) break // No traverse to clean, stop walking up

      // Check if there are any other (non-traverse) permissions for this user/role on descendants
      // Use materialized path prefix to find all descendants efficiently
      const [currentFolder] = await db.select({ path: storageFolders.path, name: storageFolders.name }).from(storageFolders).where(eq(storageFolders.id, currentId)).limit(1)
      if (!currentFolder) break

      const descendantPath = `${currentFolder.path}${currentFolder.name}/`
      const descendants = await db.select({ id: storageFolders.id }).from(storageFolders)
        .where(ilike(storageFolders.path, `${descendantPath}%`))

      const descendantIds = descendants.map((d) => d.id)
      // Also include the folder itself
      descendantIds.push(currentId)

      const permWhereBase = roleId
        ? eq(storageFolderPermissions.roleId, roleId)
        : eq(storageFolderPermissions.userId, userId!)

      let hasOtherPerms = false
      if (descendantIds.length > 0) {
        // Count how many perms exist - if only the traverse perm on this folder, it's orphaned
        const allPerms = await db.select({ id: storageFolderPermissions.id, accessLevel: storageFolderPermissions.accessLevel, folderId: storageFolderPermissions.folderId })
          .from(storageFolderPermissions)
          .where(and(inArray(storageFolderPermissions.folderId, descendantIds), permWhereBase))

        hasOtherPerms = allPerms.some((p) => p.id !== traversePerm.id)
      }

      if (!hasOtherPerms) {
        // Orphaned — remove the traverse permission
        await db.delete(storageFolderPermissions).where(eq(storageFolderPermissions.id, traversePerm.id))
      } else {
        break // Still needed, stop walking up
      }

      // Walk up
      const [parent] = await db.select({ parentId: storageFolders.parentId }).from(storageFolders).where(eq(storageFolders.id, currentId)).limit(1)
      currentId = parent?.parentId ?? null
    }
  },
}

// --- Folder Service ---

export const StorageFolderService = {
  async create(db: PostgresJsDatabase<any>, input: { name: string; parentId?: string; isPublic?: boolean; createdBy?: string }) {
    // Build materialized path
    let path = '/'
    if (input.parentId) {
      const [parent] = await db.select({ path: storageFolders.path, name: storageFolders.name }).from(storageFolders).where(eq(storageFolders.id, input.parentId)).limit(1)
      if (parent) path = `${parent.path}${parent.name}/`
    }

    const [folder] = await db.insert(storageFolders).values({
      name: input.name,
      parentId: input.parentId ?? null,
      path,
      isPublic: input.isPublic ?? true,
      createdBy: input.createdBy ?? null,
    }).returning()
    return folder
  },

  async getById(db: PostgresJsDatabase<any>, id: string) {
    const [folder] = await db.select().from(storageFolders).where(eq(storageFolders.id, id)).limit(1)
    return folder ?? null
  },

  async listChildren(db: PostgresJsDatabase<any>, parentId: string | null) {
    const where = parentId ? eq(storageFolders.parentId, parentId) : isNull(storageFolders.parentId)
    return db.select().from(storageFolders).where(where).orderBy(storageFolders.name)
  },

  async listAllAccessible(db: PostgresJsDatabase<any>, userId: string) {
    const allFolders = await db.select().from(storageFolders).orderBy(storageFolders.name)
    const accessible = []
    for (const folder of allFolders) {
      const level = await StoragePermissionService.getAccessLevel(db, folder.id, userId)
      if (level) accessible.push(folder)
    }
    return accessible
  },

  async update(db: PostgresJsDatabase<any>, id: string, input: { name?: string; isPublic?: boolean }) {
    const [folder] = await db.update(storageFolders).set({ ...input, updatedAt: new Date() }).where(eq(storageFolders.id, id)).returning()
    return folder ?? null
  },

  async delete(db: PostgresJsDatabase<any>, id: string) {
    // CASCADE handles permissions and files via FK
    const [folder] = await db.delete(storageFolders).where(eq(storageFolders.id, id)).returning()
    return folder ?? null
  },

  async getBreadcrumbs(db: PostgresJsDatabase<any>, folderId: string): Promise<{ id: string; name: string }[]> {
    const crumbs: { id: string; name: string }[] = []
    let currentId: string | null = folderId
    while (currentId) {
      const [folder] = await db.select({ id: storageFolders.id, name: storageFolders.name, parentId: storageFolders.parentId }).from(storageFolders).where(eq(storageFolders.id, currentId)).limit(1)
      if (!folder) break
      crumbs.unshift({ id: folder.id, name: folder.name })
      currentId = folder.parentId
    }
    return crumbs
  },
}

// --- File Service ---

export const StorageFileService = {
  async upload(db: PostgresJsDatabase<any>, storage: StorageProvider, input: {
    folderId: string
    data: Buffer
    filename: string
    contentType: string
    uploadedBy?: string
  }) {
    const fileId = randomUUID()
    const ext = getExtension(input.contentType)
    const storagePath = `storage/${input.folderId}/${fileId}.${ext}`

    await storage.put(storagePath, input.data, input.contentType)

    const [file] = await db.insert(storageFiles).values({
      id: fileId,
      folderId: input.folderId,
      filename: input.filename,
      path: storagePath,
      contentType: input.contentType,
      sizeBytes: input.data.length,
      uploadedBy: input.uploadedBy ?? null,
    }).returning()
    return file
  },

  async listByFolder(db: PostgresJsDatabase<any>, folderId: string, params: PaginationInput) {
    const baseWhere = eq(storageFiles.folderId, folderId)
    const searchCondition = params.q
      ? buildSearchCondition(params.q, [storageFiles.filename])
      : undefined
    const where = searchCondition ? and(baseWhere, searchCondition) : baseWhere

    const sortableColumns: Record<string, Column> = {
      filename: storageFiles.filename,
      size_bytes: storageFiles.sizeBytes,
      created_at: storageFiles.createdAt,
    }

    let query = db.select().from(storageFiles).where(where).$dynamic()
    query = withSort(query, params.sort, params.order, sortableColumns, storageFiles.filename)
    query = withPagination(query, params.page, params.limit)

    const [data, [{ total }]] = await Promise.all([
      query,
      db.select({ total: count() }).from(storageFiles).where(where),
    ])

    return paginatedResponse(data, total, params.page, params.limit)
  },

  async getById(db: PostgresJsDatabase<any>, id: string) {
    const [file] = await db.select().from(storageFiles).where(eq(storageFiles.id, id)).limit(1)
    return file ?? null
  },

  async delete(db: PostgresJsDatabase<any>, storage: StorageProvider, id: string) {
    const file = await this.getById(db, id)
    if (!file) return null
    await storage.delete(file.path)
    const [deleted] = await db.delete(storageFiles).where(eq(storageFiles.id, id)).returning()
    return deleted ?? null
  },

  async search(db: PostgresJsDatabase<any>, query: string, params: PaginationInput) {
    const searchCondition = buildSearchCondition(query, [storageFiles.filename])
    if (!searchCondition) return paginatedResponse([], 0, params.page, params.limit)

    const sortableColumns: Record<string, Column> = {
      filename: storageFiles.filename,
      created_at: storageFiles.createdAt,
    }

    let q = db.select().from(storageFiles).where(searchCondition).$dynamic()
    q = withSort(q, params.sort, params.order, sortableColumns, storageFiles.filename)
    q = withPagination(q, params.page, params.limit)

    const [data, [{ total }]] = await Promise.all([
      q,
      db.select({ total: count() }).from(storageFiles).where(searchCondition),
    ])

    return paginatedResponse(data, total, params.page, params.limit)
  },
}
