import { describe, it, expect } from 'vitest'
import {
  classifyColumns,
  resolvePrincipal,
  buildPrincipalIndex,
  tableBaseName,
  sortFindings,
  type DbColumn,
  type DbFk,
  type DbTable,
} from '../classify.js'

const tables: DbTable[] = [
  { schema: 'core', table: 'tenant_Tenants' },
  { schema: 'core', table: 'auth_Users' },
  { schema: 'core', table: 'ref_Departments' },
  { schema: 'extensions', table: 'crm_Clients' },
  { schema: 'extensions', table: 'crm_Orders' },
  { schema: 'extensions', table: 'crm_Stages' },
]

const col = (column: string): DbColumn => ({ schema: 'extensions', table: 'crm_Orders', column, dataType: 'uniqueidentifier' })

function byColumn(findings: ReturnType<typeof classifyColumns>): Record<string, string> {
  return Object.fromEntries(findings.map((f) => [f.column, f.classification]))
}

describe('classify — principal resolution helpers', () => {
  it('tableBaseName strips the table prefix', () => {
    expect(tableBaseName('tenant_Tenants')).toBe('Tenants')
    expect(tableBaseName('crm_Clients')).toBe('Clients')
    expect(tableBaseName('NoPrefix')).toBe('NoPrefix')
  })

  it('resolvePrincipal maps a *Id column to its table (singular + plural)', () => {
    const idx = buildPrincipalIndex(tables)
    expect(resolvePrincipal('TenantId', idx)).toBe('core.tenant_Tenants')
    expect(resolvePrincipal('ClientId', idx)).toBe('extensions.crm_Clients')
    expect(resolvePrincipal('DepartmentId', idx)).toBe('core.ref_Departments')
    expect(resolvePrincipal('CorrelationId', idx)).toBeNull()
    expect(resolvePrincipal('Id', idx)).toBeNull()
  })
})

describe('classify — column classification (SmartStack.app aligned)', () => {
  it('TenantId without a FK is CRITICAL — it is NOT exempt', () => {
    const findings = classifyColumns([col('TenantId')], [], tables)
    expect(findings[0].classification).toBe('critical')
    expect(findings[0].candidatePrincipal).toBe('core.tenant_Tenants')
  })

  it('a business reference (ClientId) without a FK is CRITICAL', () => {
    const findings = classifyColumns([col('ClientId')], [], tables)
    expect(findings[0].classification).toBe('critical')
    expect(findings[0].candidatePrincipal).toBe('extensions.crm_Clients')
  })

  it('identity/audit columns are EXEMPT (CreatedByUserId, UserId)', () => {
    const findings = classifyColumns([col('CreatedByUserId'), col('UserId'), col('ChangedByUserId')], [], tables)
    const map = byColumn(findings)
    expect(map.CreatedByUserId).toBe('exempt')
    expect(map.UserId).toBe('exempt')
    expect(map.ChangedByUserId).toBe('exempt')
  })

  it('an unresolvable *Id (CorrelationId) is REVIEW, not critical', () => {
    const findings = classifyColumns([col('CorrelationId')], [], tables)
    expect(findings[0].classification).toBe('review')
  })

  it('a column already covered by a FK is OK', () => {
    const fks: DbFk[] = [{ schema: 'extensions', table: 'crm_Orders', column: 'StageId' }]
    const findings = classifyColumns([col('StageId')], fks, tables)
    expect(findings[0].classification).toBe('ok')
    expect(findings[0].hasFk).toBe(true)
  })

  it('FK match is case-insensitive on schema/table/column', () => {
    const fks: DbFk[] = [{ schema: 'EXTENSIONS', table: 'CRM_ORDERS', column: 'CLIENTID' }]
    const findings = classifyColumns([col('ClientId')], fks, tables)
    expect(findings[0].classification).toBe('ok')
  })

  it('a custom allowlist entry downgrades a would-be CRITICAL/REVIEW to EXEMPT', () => {
    // WidgetId has no principal table → normally REVIEW; custom allowlist makes it EXEMPT.
    const base = classifyColumns([col('WidgetId')], [], tables)
    expect(base[0].classification).toBe('review')
    const custom = classifyColumns([col('WidgetId')], [], tables, [/^WidgetId$/i])
    expect(custom[0].classification).toBe('exempt')
  })
})

describe('classify — sortFindings', () => {
  it('orders critical first, then review, then exempt', () => {
    const findings = classifyColumns([col('CreatedByUserId'), col('CorrelationId'), col('TenantId')], [], tables)
    const sorted = sortFindings(findings).map((f) => f.classification)
    expect(sorted).toEqual(['critical', 'review', 'exempt'])
  })
})