朱子纯 / erp-workflow-plugin

// lib/validate-ddl.test.mjs — 单测：docs/03 表格 ↔ DDL 5 维 diff
import { test } from 'node:test'
import assert from 'node:assert/strict'
import { parseDocsTables, parseDDL, diffSchema } from './validate-ddl.mjs'

const DOCS = `## \`t_user\`\n| 列 | 类型 |\n|---|---|\n| iId | bigint |\n| sName | varchar(50) |\n`
const DDL = `CREATE TABLE t_user ( iId bigint PRIMARY KEY, sName varchar(50) );`

test('matching schema yields empty diff', () => {
  const d = diffSchema(parseDocsTables(DOCS), parseDDL(DDL))
  assert.deepEqual(d.missingTables, [])
  assert.deepEqual(d.columnMismatches, [])
})

test('missing column is reported', () => {
  const ddl2 = `CREATE TABLE t_user ( iId bigint );`
  const d = diffSchema(parseDocsTables(DOCS), parseDDL(ddl2))
  assert.ok(d.columnMismatches.some(m => m.table === 't_user' && m.column === 'sName'))
})

// ── parseDocsTables ──────────────────────────────────────────────
test('parseDocsTables: 列名/类型 from markdown rows under ## `table` header', () => {
  const tables = parseDocsTables(DOCS)
  assert.equal(tables.size, 1)
  const t = tables.get('t_user')
  assert.ok(t, 'table t_user parsed')
  assert.deepEqual([...t.columns.keys()], ['iId', 'sName'])
  assert.equal(t.columns.get('iId'), 'bigint')
  assert.equal(t.columns.get('sName'), 'varchar(50)')
})

test('parseDocsTables: real docs/03 format — ## `t` — purpose + ### 字段 + backtick cols', () => {
  const docs = [
    '## `t_order` — 订单主表',
    '',
    '### 字段',
    '',
    '| 字段 | 类型 | Nullable | 默认 | 业务含义 |',
    '|---|---|---|---|---|',
    '| `iIncrement` | int | 否 | 自增 | 主键 |',
    '| `sId` | varchar(100) | 是 | uuid | 业务ID |',
    '',
    '### 索引',
    '- `pk` (PRIMARY): iIncrement',
    '',
    '## `t_item` — 明细',
    '',
    '| 列 | 类型 |',
    '|---|---|',
    '| iId | bigint |',
    '',
  ].join('\n')
  const tables = parseDocsTables(docs)
  assert.deepEqual([...tables.keys()].sort(), ['t_item', 't_order'])
  const order = tables.get('t_order')
  assert.deepEqual([...order.columns.keys()], ['iIncrement', 'sId'])
  assert.equal(order.columns.get('iIncrement'), 'int')
  assert.equal(order.columns.get('sId'), 'varchar(100)')
  // header separator row and header label row must be skipped
  assert.equal(order.columns.has('字段'), false)
  assert.equal(order.columns.has('---'), false)
})

// 全链路：模板格式 docs/03（### 字段 + ### 索引 + ### 外键 bullet）→ parseDocsTables 必须
// 把索引/外键解析进 Set（回归 C2：此前 parseDocsTables 从不写 indexes/foreignKeys）。
const DOCS_FULL = [
  '## `t_order` — 订单主表',
  '',
  '### 字段',
  '| 字段 | 类型 | Nullable | 默认 | 业务含义 |',
  '|---|---|---|---|---|',
  '| `iId` | bigint | 否 | 自增 | 主键 |',
  '| `sUserId` | varchar(100) | 否 | — | 用户ID |',
  '',
  '### 索引',
  '- `pk` (PRIMARY): iId',
  '- `idx_user` (index): sUserId',
  '',
  '### 外键',
  '- `fk_user`: sUserId → t_user.sId (CASCADE)',
  '',
].join('\n')
const DDL_FULL = [
  'CREATE TABLE `t_order` (',
  '  `iId` bigint NOT NULL AUTO_INCREMENT,',
  '  `sUserId` varchar(100) NOT NULL,',
  '  PRIMARY KEY (`iId`),',
  '  KEY `idx_user` (`sUserId`),',
  '  CONSTRAINT `fk_user` FOREIGN KEY (`sUserId`) REFERENCES `t_user` (`sId`)',
  ') ENGINE=InnoDB;',
].join('\n')

test('parseDocsTables: parses ### 索引 / ### 外键 bullets into sets (C2 regression)', () => {
  const t = parseDocsTables(DOCS_FULL).get('t_order')
  assert.ok(t)
  assert.ok(t.indexes.has('PRIMARY'), 'PRIMARY index normalized')
  assert.ok(t.indexes.has('idx_user'), 'named index by name')
  assert.ok(t.foreignKeys.has('sUserId->t_user(sId)'), 'FK normalized to parseDDL form')
})

test('full chain: matching docs/03 (with indexes+FK) ↔ DDL yields no diff (C2 regression)', () => {
  const d = diffSchema(parseDocsTables(DOCS_FULL), parseDDL(DDL_FULL))
  assert.deepEqual(d.indexMismatches, [], 'index dimension clean')
  assert.deepEqual(d.foreignKeyMismatches, [], 'FK dimension clean')
  assert.equal(d.hasDiff, false, 'no spurious diff on a faithful schema')
})

test('full chain: a real FK present in docs but missing from DDL is caught', () => {
  const ddlNoFk = [
    'CREATE TABLE `t_order` (',
    '  `iId` bigint NOT NULL AUTO_INCREMENT,',
    '  `sUserId` varchar(100) NOT NULL,',
    '  PRIMARY KEY (`iId`),',
    '  KEY `idx_user` (`sUserId`)',
    ') ENGINE=InnoDB;',
  ].join('\n')
  const d = diffSchema(parseDocsTables(DOCS_FULL), parseDDL(ddlNoFk))
  assert.ok(d.foreignKeyMismatches.some(m => m.side === 'docs' && m.foreignKey === 'sUserId->t_user(sId)'))
  assert.equal(d.hasDiff, true)
})

test('parseDDL: CREATE TABLE inside a comment is NOT counted as a table (L4)', () => {
  const ddl = [
    '-- CREATE TABLE ghost_line ( x int );',
    '/* CREATE TABLE ghost_block ( y int ); */',
    '# CREATE TABLE ghost_hash ( z int );',
    'CREATE TABLE real_one ( a int );',
  ].join('\n')
  const tables = parseDDL(ddl)
  assert.deepEqual([...tables.keys()], ['real_one'])
})

test('parseDocsTables: top-level ## headers like "## 一、全局约定" are NOT tables', () => {
  const docs = [
    '## 一、全局约定（人工填）',
    '- 数据库名: erp',
    '',
    '## `t_user`',
    '| 列 | 类型 |',
    '|---|---|',
    '| iId | bigint |',
    '',
  ].join('\n')
  const tables = parseDocsTables(docs)
  assert.deepEqual([...tables.keys()], ['t_user'])
})

// ── parseDDL ─────────────────────────────────────────────────────
test('parseDDL: columns, types, indexes, foreign keys (backtick-quoted)', () => {
  const ddl = [
    'CREATE TABLE `t_order` (',
    '  `iIncrement` int NOT NULL AUTO_INCREMENT,',
    '  `sId` varchar(100) DEFAULT NULL,',
    '  `sUserId` varchar(100) DEFAULT NULL,',
    '  PRIMARY KEY (`iIncrement`),',
    '  UNIQUE KEY `uk_sid` (`sId`),',
    '  KEY `idx_user` (`sUserId`),',
    '  CONSTRAINT `fk_user` FOREIGN KEY (`sUserId`) REFERENCES `t_user` (`sId`)',
    ') ENGINE=InnoDB;',
  ].join('\n')
  const tables = parseDDL(ddl)
  const t = tables.get('t_order')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['iIncrement', 'sId', 'sUserId'])
  assert.equal(t.columns.get('sId'), 'varchar(100)')
  // index keys (named) collected; PRIMARY collected too
  assert.ok(t.indexes.has('uk_sid'))
  assert.ok(t.indexes.has('idx_user'))
  assert.ok([...t.indexes].some(i => i.toUpperCase().includes('PRIMARY')))
  // foreign key collected
  assert.ok([...t.foreignKeys].some(fk => fk.includes('sUserId') && fk.includes('t_user')))
})

test('parseDDL: unquoted identifiers and inline PRIMARY KEY', () => {
  const tables = parseDDL(DDL)
  const t = tables.get('t_user')
  assert.ok(t)
  assert.deepEqual([...t.columns.keys()], ['iId', 'sName'])
  assert.equal(t.columns.get('iId'), 'bigint')
})

test('parseDDL: multiple tables', () => {
  const ddl = 'CREATE TABLE a (x int); CREATE TABLE b (y bigint);'
  const tables = parseDDL(ddl)
  assert.deepEqual([...tables.keys()].sort(), ['a', 'b'])
})

// ── diffSchema 5 dimensions ──────────────────────────────────────
test('diffSchema: missing table (in docs, not in DDL) reported', () => {
  const docs = parseDocsTables('## `t_user`\n| 列 | 类型 |\n|---|---|\n| iId | bigint |\n')
  const ddl = parseDDL('CREATE TABLE other ( z int );')
  const d = diffSchema(docs, ddl)
  assert.ok(d.missingTables.includes('t_user'))
  assert.ok(d.extraTables.includes('other'))
})

test('diffSchema: type mismatch reported', () => {
  const docs = parseDocsTables('## `t_user`\n| 列 | 类型 |\n|---|---|\n| iId | bigint |\n')
  const ddl = parseDDL('CREATE TABLE t_user ( iId int );')
  const d = diffSchema(docs, ddl)
  assert.ok(d.typeMismatches.some(m => m.table === 't_user' && m.column === 'iId' && m.docsType === 'bigint' && m.ddlType === 'int'))
})

test('diffSchema: extra column in DDL reported as columnMismatch', () => {
  const docs = parseDocsTables('## `t_user`\n| 列 | 类型 |\n|---|---|\n| iId | bigint |\n')
  const ddl = parseDDL('CREATE TABLE t_user ( iId bigint, extra varchar(10) );')
  const d = diffSchema(docs, ddl)
  assert.ok(d.columnMismatches.some(m => m.table === 't_user' && m.column === 'extra' && m.side === 'ddl'))
})

test('diffSchema: index dimension diff reported', () => {
  const docs = new Map([['t', { columns: new Map([['c', 'int']]), indexes: new Set(['idx_c']), foreignKeys: new Set() }]])
  const ddl = parseDDL('CREATE TABLE t ( c int );') // no indexes
  const d = diffSchema(docs, ddl)
  assert.ok(d.indexMismatches.some(m => m.table === 't' && m.index === 'idx_c'))
})

test('diffSchema: foreign-key dimension diff reported', () => {
  const docs = new Map([['t', { columns: new Map([['c', 'int']]), indexes: new Set(), foreignKeys: new Set(['c->other']) }]])
  const ddl = parseDDL('CREATE TABLE t ( c int );') // no FKs
  const d = diffSchema(docs, ddl)
  assert.ok(d.foreignKeyMismatches.some(m => m.table === 't' && m.foreignKey === 'c->other'))
})

test('diffSchema: hasDiff is false when everything matches, true otherwise', () => {
  const ok = diffSchema(parseDocsTables(DOCS), parseDDL(DDL))
  assert.equal(ok.hasDiff, false)
  const bad = diffSchema(parseDocsTables(DOCS), parseDDL('CREATE TABLE t_user ( iId bigint );'))
  assert.equal(bad.hasDiff, true)
})