const fs = require('fs');

// Load data
const dbOverlapCols = JSON.parse(fs.readFileSync('d:/Workspaces/slaj/_db_overlap_cols.json', 'utf8'));
const rohsTables = JSON.parse(fs.readFileSync('d:/Workspaces/slaj/_rohs_tables.json', 'utf8'));
const dbAllNames = JSON.parse(fs.readFileSync('d:/Workspaces/slaj/_slaj_table_names.json', 'utf8'));

// Normalize type for comparison
function normType(t) {
    t = t.toUpperCase().trim().replace(/\s+/g, ' ');
    // Remove length specifications for comparison
    t = t.replace(/\(\d+(?:,\d+)?\)/g, '');
    // Map equivalent types
    const map = {
        'BPCHAR': 'CHAR',
        'VARCHAR': 'VARCHAR',
        'NUMERIC': 'NUMERIC',
        'DECIMAL': 'NUMERIC',
        'INT4': 'INTEGER',
        'INT': 'INTEGER',
        'INT8': 'BIGINT',
        'BIGINT': 'BIGINT',
        'BOOL': 'BOOLEAN',
        'FLOAT8': 'DOUBLE PRECISION',
        'TIMESTAMPTZ': 'TIMESTAMP',
        'TIMESTAMP': 'TIMESTAMP',
        'DATE': 'DATE',
        'TEXT': 'TEXT',
        'BYTEA': 'BYTEA',
        'FLOAT4': 'REAL',
        'JSONB': 'JSONB',
        'JSON': 'JSON',
        'INT2': 'SMALLINT',
        'SMALLINT': 'SMALLINT',
    };
    return map[t] || t;
}

// Categorize ROHS tables
const skipPattern = /^(MLOG\$_|MV_|T_VWMS_)|_(BAK\d*$|MW\d*$|MW$|0731$|1125$|BAK25$|0326$)|_20250[47]\d{2}$|BAK2025|_2025011[03]$|_20250110$|_20250113$/;
const rohsSkip = Object.keys(rohsTables).filter(t => skipPattern.test(t));
const rohsReal = Object.keys(rohsTables).filter(t => !skipPattern.test(t));
const rohsRealTables = {};
rohsReal.forEach(t => { rohsRealTables[t] = rohsTables[t]; });

// Map DB table names (lowercase) to ROHS names (uppercase)
const dbLowerMap = {};
dbAllNames.forEach(t => { dbLowerMap[t.toLowerCase()] = t; });

// Classify all ROHS real tables
const overlap = [];    // Tables in both
const rohsOnly = [];   // ROHS tables not in DB
const rohsBak = [];    // ROHS backup/MV tables

for (const t of Object.keys(rohsRealTables)) {
    const dbName = dbLowerMap[t.toLowerCase()];
    if (dbName) {
        overlap.push({rohs: t, db: dbName});
    } else {
        rohsOnly.push(t);
    }
}

for (const t of rohsSkip) {
    const dbName = dbLowerMap[t.toLowerCase()];
    rohsBak.push({rohs: t, inDb: !!dbName});
}

// Analyze each overlap table
const fullMatch = [];
const realDiff = [];
const trivialTypeDiff = [];

for (const o of overlap) {
    const rohsCols = rohsRealTables[o.rohs] || [];
    const dbCols = dbOverlapCols[o.db] || [];

    const rohsColMap = {};
    rohsCols.forEach(c => { rohsColMap[c.name] = c.type; });
    const dbColMap = {};
    dbCols.forEach(c => { dbColMap[c.name] = c.type; });

    const rohsNames = Object.keys(rohsColMap);
    const dbNames = Object.keys(dbColMap);

    const rohsOnlyCols = rohsNames.filter(n => !dbNames.includes(n));
    const dbOnlyCols = dbNames.filter(n => !rohsNames.includes(n));
    const common = rohsNames.filter(n => dbNames.includes(n));

    // Check real type differences (after normalization)
    const realTypeDiffs = [];
    const trivialDiffs = [];
    for (const cn of common) {
        const rt = normType(rohsColMap[cn]);
        const dt = normType(dbColMap[cn]);
        if (rt !== dt) {
            realTypeDiffs.push({col: cn, rohs: rohsColMap[cn], db: dbColMap[cn], rohsNorm: rt, dbNorm: dt});
        } else if (rohsColMap[cn] !== dbColMap[cn]) {
            trivialDiffs.push({col: cn, rohs: rohsColMap[cn], db: dbColMap[cn]});
        }
    }

    if (rohsOnlyCols.length === 0 && dbOnlyCols.length === 0 && realTypeDiffs.length === 0) {
        fullMatch.push({...o, trivialDiffs: trivialDiffs.length});
    } else {
        realDiff.push({
            ...o,
            rohsOnlyCols,
            dbOnlyCols,
            realTypeDiffs,
            trivialDiffs: trivialDiffs.length,
        });
    }
}

// ======== REPORT ========
console.log('========================================');
console.log('  六项机制(ROHS) 与 安监(slaj) 数据库对比分析报告');
console.log('========================================\n');

console.log('【总体概况】');
console.log(`  ROHS SQL文件中表总数: ${Object.keys(rohsTables).length}`);
console.log(`  ROHS 真实业务表: ${Object.keys(rohsRealTables).length}`);
console.log(`  ROHS 备份/物化视图/日志表: ${rohsSkip.length}`);
console.log(`  安监 slaj schema 表总数: ${dbAllNames.length}`);
console.log(`  重叠表 (两库都有): ${overlap.length}`);
console.log(`  ROHS独有表 (安监中没有): ${rohsOnly.length}`);
console.log(`  安监独有表 (ROHS中没有): ${dbAllNames.length - overlap.length}`);

console.log(`\n【重叠表分析】(共 ${overlap.length} 张)`);
console.log(`  字段完全一致: ${fullMatch.length}`);
console.log(`  有结构性差异: ${realDiff.length}`);

if (fullMatch.length > 0) {
    console.log(`\n  --- 完全一致的表 (仅类型名写法不同) ---`);
    for (const m of fullMatch) {
        console.log(`  ✓ ${m.rohs} = ${m.db} (${rohsRealTables[m.rohs].length} cols, ${m.trivialDiffs > 0 ? m.trivialDiffs + ' trivial type diffs' : 'exact match'})`);
    }
}

if (realDiff.length > 0) {
    console.log(`\n  --- 有真实差异的表 ---`);
    for (const m of realDiff) {
        const issues = [];
        if (m.rohsOnlyCols.length) issues.push(`ROHS独有字段: [${m.rohsOnlyCols.join(', ')}]`);
        if (m.dbOnlyCols.length) issues.push(`DB独有字段: [${m.dbOnlyCols.join(', ')}]`);
        for (const td of m.realTypeDiffs) {
            issues.push(`${td.col}: ROHS=${td.rohs} vs DB=${td.db}`);
        }
        const sev = (m.rohsOnlyCols.length + m.dbOnlyCols.length + m.realTypeDiffs.length);
        const icon = m.dbOnlyCols.length + m.rohsOnlyCols.length === 0 && m.realTypeDiffs.every(d => d.col === 'GUID' && d.rohs === 'CHAR(32)' && d.db === 'BPCHAR') ? '~' : '⚠';
        console.log(`  ${icon} ${m.rohs} (${rohsRealTables[m.rohs].length}c) <-> ${m.db} (${Object.keys(dbOverlapCols[m.db]||{}).length}c): ${issues.join('; ')}`);
    }
}

console.log(`\n【ROHS独有表 - 安监中不存在的新表】(共 ${rohsOnly.length} 张)`);

// Categorize ROHS-only tables
const categoryMap = {
    'SYS_': '系统管理类',
    'T_BUSI_': '业务类',
    'T_DICT_': '字典类',
    'T_CON_': '配置类',
    'T_SSO_': 'SSO单点登录类',
    'T_VWMS_': 'VWMS类',
    'ATT_': '附件/属性类',
    'BIS_': '业务信息类',
    'OBJ_': '对象类',
    'REL_': '关系类',
    'BDMS_': 'BDMS类',
    'HARD_': '硬件类',
    'HAZARD_': '危险源类',
    'HIDD_': '隐患类',
    'PPRTY_': '产权类',
};

const cats = {};
for (const t of rohsOnly) {
    let cat = '其他';
    for (const [prefix, label] of Object.entries(categoryMap)) {
        if (t.startsWith(prefix)) { cat = label; break; }
    }
    if (!cats[cat]) cats[cat] = [];
    cats[cat].push(t);
}

for (const [cat, tables] of Object.entries(cats).sort()) {
    console.log(`\n  [${cat}] (${tables.length}张):`);
    tables.sort().forEach(t => {
        const cols = rohsRealTables[t] || [];
        console.log(`    ${t} (${cols.length} cols)`);
    });
}

console.log(`\n【ROHS备份/日志/MV表】(共 ${rohsSkip.length} 张)`);
for (const t of rohsBak) {
    console.log(`  ${t.rohs} (${(rohsTables[t.rohs]||[]).length} cols) ${t.inDb ? '← 安监中也存在' : '(安监中不存在)'}`);
}

console.log(`\n========================================`);
console.log('【合并可行性分析】');
console.log('========================================');

// Count by severity
const critDiffs = realDiff.filter(m => m.rohsOnlyCols.length > 0 || m.dbOnlyCols.length > 0 || m.realTypeDiffs.some(d => d.rohsNorm !== d.dbNorm));
const minorDiffs = realDiff.filter(m => !critDiffs.includes(m));

console.log(`
1. 重叠表 (${overlap.length}张):
   - 完全一致 (仅CHAR/BPCHAR, VARCHAR长度等PG写法差异): ${fullMatch.length}张
   - 有轻微信赖差异 (类型兼容): ${realDiff.length - critDiffs.length}张
   - 有真实差异需处理: ${critDiffs.length}张

2. 新表 (ROHS独有, ${rohsOnly.length}张):
   - 这些表可以直接在安监数据库中创建
   - 涉及：${Object.keys(cats).join('、')}

3. 合并建议:
   - 重叠表：由于重叠表字段基本一致（差异主要是PG类型名写法不同和STR_TO_DATE<->TO_DATE），
     这些表结构上可以直接兼容，不需要修改
   - ROHS独有的${rohsOnly.length}张表需要在安监数据库中新建
   - 注意：ROHS表的schema是 SLAJ_ROHS，安监的schema是 slaj，
     合并时需要统一schema或者处理好跨schema访问

4. 风险点:`);

// Find critical diffs
for (const m of realDiff) {
    const issues = [];
    if (m.rohsOnlyCols.length) issues.push(`ROHS多出的列: ${m.rohsOnlyCols.join(', ')}`);
    if (m.dbOnlyCols.length) issues.push(`DB多出的列: ${m.dbOnlyCols.join(', ')}`);
    for (const td of m.realTypeDiffs) {
        if (td.rohsNorm !== td.dbNorm) {
            issues.push(`类型不兼容: ${td.col} ROHS=${td.rohs}(${td.rohsNorm}) vs DB=${td.db}(${td.dbNorm})`);
        }
    }
    if (issues.length) {
        console.log(`   - ${m.rohs}: ${issues.join('; ')}`);
    }
}

console.log(`\n报告完毕。`);