baiyue-firstleg/scripts/prod-database-schema-acceptance.mjs

#!/usr/bin/env node

import { readFileSync } from 'node:fs'
import { spawnSync } from 'node:child_process'
import path from 'node:path'
import process from 'node:process'
import { fileURLToPath } from 'node:url'

const SCRIPT_DIR = path.dirname(fileURLToPath(import.meta.url))
const REPO_ROOT = path.resolve(SCRIPT_DIR, '..')
const EXPECTED_SQL = path.join(REPO_ROOT, 'deploy/sql/海外区域统一增量-MySQL8.sql')
const MIGRATION_SQL = path.join(REPO_ROOT, 'deploy/sql/migrations/overseas/baiyue_lastleg/V1__align_lastleg_production_schema.sql')
const MYSQL_CANDIDATES = [
  process.env.MYSQL_BIN,
  'C:\\Program Files\\MySQL\\MySQL Server 8.4\\bin\\mysql.exe',
  'mysql',
].filter(Boolean)

function parseArgs(argv) {
  const args = { apply: false, confirmProductionDdl: false }
  for (let index = 0; index < argv.length; index += 1) {
    const item = argv[index]
    if (item === '--apply') args.apply = true
    else if (item === '--confirm-production-ddl') args.confirmProductionDdl = true
    else if (item === '--help' || item === '-h') args.help = true
    else throw new Error(`未知参数：${item}`)
  }
  return args
}

function printUsage() {
  console.log('用法：node prod-database-schema-acceptance.mjs [--apply --confirm-production-ddl]')
  console.log('默认只读核对；--apply 仅执行仓储中的增量迁移，不执行 DML。')
}

function loadConfig() {
  const required = ['CHONGCHONG_ENV', 'CHONGCHONG_DB_HOST', 'CHONGCHONG_DB_PORT', 'CHONGCHONG_DB_USERNAME', 'CHONGCHONG_DB_PASSWORD']
  const missing = required.filter((key) => !process.env[key])
  if (missing.length) throw new Error(`缺少数据库环境变量：${missing.join(', ')}`)
  if (process.env.CHONGCHONG_ENV !== 'prod') throw new Error('生产数据库脚本要求 CHONGCHONG_ENV=prod')
  return Object.fromEntries(required.map((key) => [key, process.env[key]]))
}

function expectedSchema() {
  const sql = readFileSync(EXPECTED_SQL, 'utf8')
  const tables = new Map()
  const tablePattern = /CREATE TABLE\s+(?:IF NOT EXISTS\s+)?(ll_[a-z0-9_]+)\s*\(([\s\S]*?)\n\) ENGINE=/gi
  for (const match of sql.matchAll(tablePattern)) {
    const columns = new Set()
    for (const line of match[2].split(/\r?\n/)) {
      const field = line.match(/^\s+`?([a-z][a-z0-9_]*)`?\s+/i)
      if (field && !/^(primary|unique|key|constraint|check|foreign)$/i.test(field[1])) columns.add(field[1])
    }
    tables.set(match[1], columns)
  }
  return tables
}

function mysql(config, sql) {
  let lastError = ''
  for (const executable of MYSQL_CANDIDATES) {
    const result = spawnSync(executable, [
      '--protocol=TCP', '-h', config.CHONGCHONG_DB_HOST, '-P', config.CHONGCHONG_DB_PORT,
      '-u', config.CHONGCHONG_DB_USERNAME, '--database=baiyue_lastleg',
      '--batch', '--skip-column-names', '--default-character-set=utf8mb4',
    ], { input: sql, encoding: 'utf8', env: { ...process.env, MYSQL_PWD: config.CHONGCHONG_DB_PASSWORD } })
    if (result.error?.code === 'ENOENT') {
      lastError = result.error.message
      continue
    }
    if (result.status !== 0) throw new Error((result.stderr || result.stdout || 'mysql 执行失败').trim())
    return result.stdout
  }
  throw new Error(`找不到 mysql 客户端：${lastError}`)
}

function readActualSchema(config) {
  const output = mysql(config, "SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = 'baiyue_lastleg' ORDER BY table_name, ordinal_position;")
  const tables = new Map()
  for (const line of output.trim().split(/\r?\n/).filter(Boolean)) {
    const [table, column] = line.split('\t')
    if (!table?.startsWith('ll_') || !column) continue
    if (!tables.has(table)) tables.set(table, new Set())
    tables.get(table).add(column)
  }
  return tables
}

function diff(expected, actual) {
  const missingTables = [...expected.keys()].filter((table) => !actual.has(table))
  const extraTables = [...actual.keys()].filter((table) => !expected.has(table))
  const missingColumns = []
  const extraColumns = []
  for (const [table, columns] of expected) {
    const current = actual.get(table) ?? new Set()
    for (const column of columns) if (!current.has(column)) missingColumns.push(`${table}.${column}`)
    for (const column of current) if (!columns.has(column)) extraColumns.push(`${table}.${column}`)
  }
  return { missingTables, extraTables, missingColumns, extraColumns }
}

function printDiff(result) {
  console.log(`期望表 ${result.expectedTables}，实际表 ${result.actualTables}`)
  console.log(`缺失表 ${result.missingTables.length}，缺失字段 ${result.missingColumns.length}，额外字段 ${result.extraColumns.length}`)
  if (result.missingTables.length) console.log(`缺失表：${result.missingTables.join(', ')}`)
  if (result.extraTables.length) console.log(`额外表：${result.extraTables.join(', ')}`)
  if (result.missingColumns.length) console.log(`缺失字段：${result.missingColumns.join(', ')}`)
  if (result.extraColumns.length) console.log(`额外字段：${result.extraColumns.join(', ')}`)
}

function audit(config) {
  const expected = expectedSchema()
  const actual = readActualSchema(config)
  const result = { ...diff(expected, actual), expectedTables: expected.size, actualTables: actual.size }
  printDiff(result)
  return result
}

function main() {
  const args = parseArgs(process.argv.slice(2))
  if (args.help) return printUsage()
  const config = loadConfig()
  let result = audit(config)
  if (args.apply) {
    if (!args.confirmProductionDdl) throw new Error('--apply 必须同时提供 --confirm-production-ddl')
    console.log('执行生产增量迁移：仅 ADD COLUMN / MODIFY COLUMN 放宽可空性 / CREATE TABLE，不执行 DML。')
    mysql(config, readFileSync(MIGRATION_SQL, 'utf8'))
    result = audit(config)
  }
  const passed = result.missingTables.length === 0 && result.missingColumns.length === 0
  if (!passed) {
    console.error('PROD_SCHEMA_ACCEPTANCE_FAIL')
    process.exitCode = 1
  } else {
    console.log('PROD_SCHEMA_ACCEPTANCE_PASS')
  }
}

try {
  main()
} catch (error) {
  console.error(`PROD_SCHEMA_ACCEPTANCE_ERROR: ${error.message}`)
  process.exitCode = 1
}