/**
 * RBAC tables — foundation-auth-rbac.
 * roles, permissions, role_permissions, tenant_memberships.
 */
import {
  pgTable,
  uuid,
  text,
  boolean,
  timestamp,
  index,
  check,
  unique,
  primaryKey,
} from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { tenants } from './tenants'
import { users } from './users'

export const roles = pgTable(
  'roles',
  {
    id: uuid('id').primaryKey().defaultRandom(),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    name: text('name').notNull(),
    // is_system_role TRUE = cannot delete (OWNER, ADMIN, MEMBER, VIEWER, CONTRACTOR)
    isSystemRole: boolean('is_system_role').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    tenantNameUnique: unique('roles_tenant_name_unique').on(t.tenantId, t.name),
  }),
)

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

export const rolePermissions = pgTable(
  'role_permissions',
  {
    roleId: uuid('role_id')
      .notNull()
      .references(() => roles.id, { onDelete: 'cascade' }),
    permissionId: uuid('permission_id')
      .notNull()
      .references(() => permissions.id, { onDelete: 'cascade' }),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.roleId, t.permissionId] }),
  }),
)

export const tenantMemberships = pgTable(
  'tenant_memberships',
  {
    userId: uuid('user_id')
      .notNull()
      .references(() => users.id, { onDelete: 'cascade' }),
    tenantId: uuid('tenant_id')
      .notNull()
      .references(() => tenants.id, { onDelete: 'cascade' }),
    roleId: uuid('role_id')
      .notNull()
      .references(() => roles.id),
    // status: 'active' | 'frozen' | 'pending_approval'
    //   'pending_approval' = joined a require_approval tenant, awaiting admin
    //   approval (distinct from 'frozen', an admin-initiated suspension).
    status: text('status').notNull().default('active'),
    freezeReason: text('freeze_reason'),
    // Scoped accountant grant (migration 0041); true when role is ACCOUNTANT.
    isAccountant: boolean('is_accountant').notNull().default(false),
    createdAt: timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  },
  (t) => ({
    pk: primaryKey({ columns: [t.userId, t.tenantId] }),
    tenantIdx: index('tenant_memberships_tenant_id_idx').on(t.tenantId),
    statusCheck: check(
      'tenant_memberships_status_check',
      sql`${t.status} IN ('active', 'frozen', 'pending_approval')`,
    ),
  }),
)

export type RoleRow = typeof roles.$inferSelect
export type PermissionRow = typeof permissions.$inferSelect
export type TenantMembershipRow = typeof tenantMemberships.$inferSelect
export type NewTenantMembership = typeof tenantMemberships.$inferInsert