import assert from 'node:assert/strict'
import { readFileSync } from 'node:fs'
import { spawnSync } from 'node:child_process'
import { fileURLToPath } from 'node:url'
import test from 'node:test'
import { buildPlan, countSql, fingerprint, inspectionSql, readCatalog, writeReport } from './test-env-business-reset-plan.mjs'

const repositoryRoot = fileURLToPath(new URL('..', import.meta.url))
const sourcePaths = ['deploy/sql/头程数据库统一增量-MySQL8.sql', 'deploy/sql/海外区域统一增量-MySQL8.sql']
const sources = sourcePaths.map(name => ({ path: name, sql: readFileSync(new URL(`../${name}`, import.meta.url), 'utf8') }))
const policy = JSON.parse(readFileSync(new URL('./test-env-business-reset-policy.json', import.meta.url), 'utf8'))

/** 构造少量离线表，覆盖新增表和保留表外键的失败关闭行为。 */
function fixture() {
  const sql = `USE baiyue_order;
CREATE TABLE ord_order (
 id bigint NOT NULL,
 PRIMARY KEY (id)
) ENGINE=InnoDB COMMENT='订单';
CREATE TABLE retained_audit (
 order_id bigint,
 FOREIGN KEY (order_id) REFERENCES ord_order (id)
) ENGINE=InnoDB COMMENT='保留审计';
CREATE TRIGGER protect_order BEFORE DELETE ON ord_order FOR EACH ROW BEGIN END;
`
  const source = { path: 'fixture.sql', sql }
  return { sources: [source], policy: { version: 1, environment: 'TEST', sourceSha256: { 'fixture.sql': fingerprint(sql) }, groups: [
    { action: 'CLEAR_CANDIDATE', reason: '业务单据', tables: ['baiyue_order.ord_order'] },
    { action: 'KEEP', reason: '独立审计', tables: ['baiyue_order.retained_audit'] },
  ] } }
}

/** 结构清单只模拟表名及估算数量，不冒充真实环境验证。 */
function inventoryFor(plan) {
  return plan.tables.map(table => ({ schema: table.schema, table: table.name, type: 'BASE TABLE', estimatedRows: 7 }))
}

test('当前每张基线表显式登记，离线结果始终不可执行', () => {
  const plan = buildPlan(sources, policy)
  assert.equal(plan.summary.total, readCatalog(sources).length)
  assert.equal(plan.summary.total, policy.groups.flatMap(group => group.tables).length)
  assert.equal(plan.executionSupported, false)
  assert.equal(plan.status, 'REVIEW_REQUIRED')
  assert.equal(plan.canGenerateCountQueries, false)
  assert.ok(plan.blockers.some(item => item.code === 'LIVE_INVENTORY_REQUIRED'))
  assert.ok(!plan.blockers.some(item => ['BASELINE_CHANGED', 'UNCLASSIFIED_TABLE', 'STALE_POLICY_TABLE'].includes(item.code)))
  assert.ok(plan.tables.some(table => table.triggers.some(trigger => trigger.event === 'DELETE')))
})

test('身份、文件、报价、自营和序列保留，资金与资源混合表进入字段审阅', () => {
  const tables = new Map(buildPlan(sources, policy).tables.map(table => [table.key, table]))
  for (const key of ['baiyue_auth.auth_account', 'baiyue_iam.iam_tenant', 'baiyue_file.file_object',
    'baiyue_file.file_pda_release', 'baiyue_audit.sys_audit_log', 'baiyue_firstleg.fl_price_book_version',
    'baiyue_lastleg.ll_selfop_inventory_snapshot', 'baiyue_lastleg.ll_document_sequence', 'baiyue_order.ord_customer_address']) {
    assert.equal(tables.get(key).action, 'KEEP', key)
  }
  for (const key of ['baiyue_finance.fin_wallet_account', 'baiyue_warehouse.wh_location',
    'baiyue_lastlag_warehouse.wh_pallet', 'baiyue_lastlag_warehouse.wh_location', 'baiyue_lastleg.ll_vehicle',
    'baiyue_lastleg.ll_appointment_work_guard']) assert.equal(tables.get(key).action, 'RESET_REVIEW', key)
  assert.match(tables.get('baiyue_finance.fin_wallet_account').reason, /可用余额.*有效授信.*不能固定为零/)
  assert.equal(tables.get('baiyue_finance.fin_recharge_request').action, 'CLEAR_CANDIDATE')
  assert.equal(tables.get('baiyue_finance.fin_wallet_ledger').action, 'CLEAR_CANDIDATE')
})

test('独立审计保留而订单自身历史随单清理，人工计划与资源档案保留', () => {
  const plan = buildPlan(sources, policy)
  const tables = new Map(plan.tables.map(table => [table.key, table]))
  for (const key of ['baiyue_order.ord_order_status_history', 'baiyue_firstleg.fl_loading_pick_history',
    'baiyue_lastleg.ll_dispatch_history', 'baiyue_lastleg.ll_fleet_trip_history']) {
    assert.equal(tables.get(key).action, 'CLEAR_CANDIDATE', key)
  }
  for (const key of ['baiyue_finance.fin_audit_log', 'baiyue_firstleg.fl_sailing',
    'baiyue_firstleg.fl_sailing_history', 'baiyue_lastleg.ll_fleet_vehicle_record',
    'baiyue_lastleg.ll_fleet_vehicle_record_history', 'baiyue_iam.iam_notification_task']) {
    assert.equal(tables.get(key).action, 'KEEP', key)
  }
  assert.equal(tables.get('baiyue_file.file_export_task').action, 'REVIEW')
  assert.equal(tables.get('baiyue_file.file_export_outbox').action, 'REVIEW')
  assert.ok(!plan.blockers.some(item => item.code === 'RETAINED_CHILD_REFERENCES_CLEAR_PARENT'))
})

test('基线新增表不会继承相似前缀的删除建议', () => {
  const data = fixture()
  data.sources[0].sql += `CREATE TABLE ord_order_new (
 id bigint
) ENGINE=InnoDB;
`
  const plan = buildPlan(data.sources, data.policy)
  assert.equal(plan.tables.find(table => table.name === 'ord_order_new').action, 'REVIEW')
  assert.ok(plan.blockers.some(item => item.code === 'UNCLASSIFIED_TABLE'))
  assert.ok(plan.blockers.some(item => item.code === 'BASELINE_CHANGED'))
})

test('保留表引用待清主表时明确报告，且提取删除保护触发器', () => {
  const data = fixture()
  const plan = buildPlan(data.sources, data.policy)
  assert.ok(plan.blockers.some(item => item.code === 'RETAINED_CHILD_REFERENCES_CLEAR_PARENT'
    && item.child === 'baiyue_order.retained_audit' && item.parent === 'baiyue_order.ord_order'))
  assert.deepEqual(plan.tables.find(table => table.name === 'ord_order').triggers,
    [{ name: 'protect_order', timing: 'BEFORE', event: 'DELETE' }])
})

test('策略重复项、错误环境和未完整解析的 CREATE TABLE 拒绝处理', () => {
  const data = fixture()
  const duplicate = structuredClone(data.policy)
  duplicate.groups[0].tables.push('baiyue_order.retained_audit')
  assert.throws(() => buildPlan(data.sources, duplicate), /重复登记/)
  assert.throws(() => buildPlan(data.sources, { ...data.policy, environment: 'PROD' }), /TEST/)
  assert.throws(() => readCatalog([{ path: 'unsupported.sql', sql: 'CREATE TABLE `quoted` (id INT);' }]), /未能完整解析/)
})

test('实际清单缺表、多表或视图均阻断计数 SQL 的生成', () => {
  const data = fixture()
  const initial = buildPlan(data.sources, data.policy)
  for (const rows of [
    [],
    [...inventoryFor(initial), { schema: 'baiyue_order', table: 'unclassified', type: 'BASE TABLE' }],
    inventoryFor(initial).map(row => ({ ...row, type: 'VIEW' })),
  ]) {
    const plan = buildPlan(data.sources, data.policy, rows)
    assert.equal(plan.canGenerateCountQueries, false)
    assert.throws(() => countSql(plan), /完整且表名吻合/)
  }
})

test('完整实际表清单仅解锁 SELECT COUNT，估算行数不转为已验证精确数量', () => {
  const data = fixture()
  const initial = buildPlan(data.sources, data.policy)
  const plan = buildPlan(data.sources, data.policy, inventoryFor(initial))
  assert.equal(plan.executionSupported, false)
  assert.equal(plan.status, 'REVIEW_REQUIRED')
  assert.equal(plan.tables[0].estimatedRows, 7)
  assert.equal(plan.tables[0].exactRows, undefined)
  const sql = countSql(plan).split('\n').filter(line => line && !line.startsWith('--'))
  assert.equal(sql.length, plan.tables.length)
  assert.ok(sql.every(line => /^SELECT '[a-z_.]+' AS table_key, COUNT\(\*\) AS exact_rows FROM `[a-z_]+`\.`[a-z_]+`;$/.test(line)))
})

test('外部清单拒绝重复、范围外和 SQL 注入标识符', () => {
  const data = fixture()
  const rows = inventoryFor(buildPlan(data.sources, data.policy))
  assert.throws(() => buildPlan(data.sources, data.policy, [...rows, rows[0]]), /重复表/)
  assert.throws(() => buildPlan(data.sources, data.policy, [{ ...rows[0], schema: 'other_database' }]), /范围外/)
  assert.throws(() => buildPlan(data.sources, data.policy, [{ ...rows[0], table: 'ord_order; DROP TABLE x' }]), /标识符非法/)
  assert.throws(() => inspectionSql(["x'); DROP DATABASE x; --"]), /标识符非法/)
})

test('盘点 SQL 只有元数据 SELECT 并包含跨范围外键引用，输出路径不能覆盖源码', () => {
  const plan = buildPlan(sources, policy)
  const sql = inspectionSql(plan.schemas).split('\n').filter(line => !line.startsWith('--')).join('\n')
  const statements = sql.split(';').map(value => value.trim()).filter(Boolean)
  assert.equal(statements.length, 5)
  assert.ok(statements.every(statement => statement.startsWith('SELECT ')))
  assert.match(sql, /OR REFERENCED_TABLE_SCHEMA IN/)
  assert.throws(() => writeReport('scripts', plan), /报告目录必须/)
  assert.throws(() => writeReport('reports/../scripts/business-reset-test', plan), /报告目录必须/)
})

test('真实 CLI 默认仅打印离线摘要，拒绝 execute 参数', () => {
  const script = 'scripts/test-env-business-reset-plan.mjs'
  const result = spawnSync(process.execPath, [script], { cwd: repositoryRoot, encoding: 'utf8' })
  assert.equal(result.status, 0, result.stderr)
  assert.equal(JSON.parse(result.stdout).executionSupported, false)
  assert.equal(JSON.parse(result.stdout).output, null)
  const execute = spawnSync(process.execPath, [script, '--execute'], { cwd: repositoryRoot, encoding: 'utf8' })
  assert.equal(execute.status, 1)
  assert.match(execute.stderr, /未知、重复或缺少值/)
})