import {
  boolean,
  pgTable,
  primaryKey,
  text,
  uuid,
} from 'drizzle-orm/pg-core'
import { and, eq } from 'drizzle-orm'
import type { Querier } from '@platform-modules/db'
import type { RbacAdapter, RbacResolveInput } from './index.js'

export const rolesTable = pgTable('tenancy_roles', {
  id: uuid('id').primaryKey().defaultRandom(),
  tenantId: uuid('tenant_id').notNull(),
  name: text('name').notNull(),
  isSystemRole: boolean('is_system_role').notNull().default(false),
})

export const permissionsTable = pgTable('tenancy_permissions', {
  id: uuid('id').primaryKey().defaultRandom(),
  key: text('key').notNull().unique(),
})

export const rolePermissionsTable = pgTable(
  'tenancy_role_permissions',
  {
    roleId: uuid('role_id')
      .notNull()
      .references(() => rolesTable.id, { onDelete: 'cascade' }),
    permissionId: uuid('permission_id')
      .notNull()
      .references(() => permissionsTable.id, { onDelete: 'cascade' }),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.roleId, t.permissionId] }),
  }),
)

export const triadSchema = {
  roles: rolesTable,
  permissions: permissionsTable,
  rolePermissions: rolePermissionsTable,
}

export type TriadSchema = typeof triadSchema

/** Production-sourced system role set (spec excludes ACCOUNTANT). */
export const SYSTEM_ROLE_NAMES = [
  'OWNER',
  'ADMIN',
  'MEMBER',
  'VIEWER',
  'CONTRACTOR',
] as const

export type SystemRoleName = (typeof SYSTEM_ROLE_NAMES)[number]

const PERMISSION_KEYS = [
  'tasks:read',
  'tasks:write',
  'billing:manage',
  'users:manage',
  'reports:read',
  'time:read',
  'time:write',
  'time:track',
] as const

const ALL = [...PERMISSION_KEYS]
const READ_ONLY = ALL.filter((k) => k.endsWith(':read'))

export const SYSTEM_ROLE_PERMISSIONS: Record<SystemRoleName, readonly string[]> = {
  OWNER: ALL,
  ADMIN: ALL.filter((k) => k !== 'billing:manage' && k !== 'users:manage'),
  MEMBER: ['tasks:read', 'tasks:write', 'time:read', 'time:track'],
  VIEWER: READ_ONLY,
  CONTRACTOR: ['time:read', 'time:write', 'time:track', 'tasks:read'],
}

export type TriadRbac = RbacAdapter & {
  seedSystemRoles(tenantId: string): Promise<void>
  seedPermissionCatalog(): Promise<void>
}

export function createTriadRbac<S extends TriadSchema>(
  db: Querier<S>,
): TriadRbac {
  async function seedPermissionCatalog() {
    for (const key of PERMISSION_KEYS) {
      await db
        .insert(permissionsTable)
        .values({ key })
        .onConflictDoNothing({ target: permissionsTable.key })
    }
  }

  async function seedSystemRoles(tenantId: string) {
    for (const roleName of SYSTEM_ROLE_NAMES) {
      const [role] = await db
        .insert(rolesTable)
        .values({
          tenantId,
          name: roleName,
          isSystemRole: true,
        })
        .returning({ id: rolesTable.id })

      if (!role) continue

      const keys = SYSTEM_ROLE_PERMISSIONS[roleName]
      for (const key of keys) {
        const perm = await db
          .select({ id: permissionsTable.id })
          .from(permissionsTable)
          .where(eq(permissionsTable.key, key))
          .limit(1)
        const permissionId = perm[0]?.id
        if (!permissionId) continue
        await db
          .insert(rolePermissionsTable)
          .values({ roleId: role.id, permissionId })
          .onConflictDoNothing()
      }
    }
  }

  async function resolveCapabilities(input: RbacResolveInput): Promise<string[]> {
    const role = await db
      .select({ id: rolesTable.id })
      .from(rolesTable)
      .where(
        and(eq(rolesTable.tenantId, input.tenantId), eq(rolesTable.name, input.roleKey)),
      )
      .limit(1)

    const roleId = role[0]?.id
    if (!roleId) return []

    const rows = await db
      .select({ key: permissionsTable.key })
      .from(rolePermissionsTable)
      .innerJoin(permissionsTable, eq(rolePermissionsTable.permissionId, permissionsTable.id))
      .where(eq(rolePermissionsTable.roleId, roleId))

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

  return {
    seedPermissionCatalog,
    seedSystemRoles,
    resolveCapabilities,
  }
}