const fs = require('fs');

// ===== LOAD DATA =====
const rohsTables = JSON.parse(fs.readFileSync('d:/Workspaces/slaj/_rohs_tables.json', 'utf8'));
const dbOverlap = JSON.parse(fs.readFileSync('d:/Workspaces/slaj/_db_overlap_cols.json', 'utf8'));
const dbAllNames = JSON.parse(fs.readFileSync('d:/Workspaces/slaj/_slaj_table_names.json', 'utf8'));
const sqlContent = fs.readFileSync('d:/Workspaces/slaj/docs/slaj-rohs_tbl-all_without-data_hg.sql', 'utf8');

// ===== CONFIG =====
const TARGET_SCHEMA = 'slaj';
const SOURCE_SCHEMA = 'SLAJ_ROHS';

// ===== HELPERS =====
const isSys = t => /^SYS_/i.test(t);
const skipPat = /^(MLOG\$_|MV_|T_VWMS_)|_(BAK\d*$|MW\d*$|MW$|0731$|1125$|BAK25$|0326$)|_20250[47]\d{2}$|BAK2025|_2025011[03]$|_20250110$|_20250113$/;
const isBak = t => skipPat.test(t);

const rohsReal = Object.keys(rohsTables).filter(t => !isBak(t) && !isSys(t));
const rohsBak = Object.keys(rohsTables).filter(t => isBak(t) && !isSys(t));

const dbLowerMap = {};
dbAllNames.forEach(t => { dbLowerMap[t.toLowerCase()] = t; });

// Classify
const newTables = rohsReal.filter(t => !dbLowerMap[t.toLowerCase()]);
const overlapTables = rohsReal.filter(t => dbLowerMap[t.toLowerCase()]);

function normType(t) {
    t = t.toUpperCase().trim().replace(/\s+/g,' ').replace(/\(\d+(?:,\d+)?\)/g,'');
    const m = {BPCHAR:'CHAR',VARCHAR:'VARCHAR',NUMERIC:'NUMERIC',DECIMAL:'NUMERIC',INT4:'INTEGER',INT8:'BIGINT',BOOL:'BOOLEAN',FLOAT8:'DOUBLE PRECISION',TIMESTAMPTZ:'TIMESTAMP',FLOAT4:'REAL',INT2:'SMALLINT'};
    return m[t] || t;
}

// ===== EXTRACT ORIGINAL DDL FOR TABLES =====
function extractDDL(tableName) {
    // Extract CREATE TABLE block from SQL file
    const escaped = tableName.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
    const re = new RegExp(`CREATE TABLE "${SOURCE_SCHEMA}"\\."${escaped}"[\\s\\S]*?;`, 'g');
    const m = sqlContent.match(re);
    return m ? m[0] : null;
}

function convertTypeForPg(type) {
    // CHAR(n) -> character(n), VARCHAR(n) -> character varying(n)
    // Keep mostly as-is since PostgreSQL understands both
    return type.trim();
}

function toSnakeCase(str) {
    return str.replace(/([A-Z])/g, '_$1').toLowerCase().replace(/^_/, '').replace(/_{2,}/g, '_');
}

// ===== GENERATE NEW TABLE DDL =====
let newTableDDL = `-- ============================================================
-- 六项机制(ROHS) 新表 DDL — 目标 schema: ${TARGET_SCHEMA}
-- 生成时间: 2026-07-10
-- 共 ${newTables.length} 张表
-- 执行方式: psql -h 39.98.38.2 -p 5866 -U highgo -d highgo -f this_file.sql
-- ============================================================

`;

let rollbackNewTables = `-- ============================================================
-- 回滚脚本: 删除新建的 ${newTables.length} 张表
-- 执行方式: psql -h 39.98.38.2 -p 5866 -U highgo -d highgo -f this_file.sql
-- ============================================================

`;

// Write 73 new tables DDL
for (const tname of newTables.sort()) {
    const ddl = extractDDL(tname);
    const lowerName = tname.toLowerCase();
    const cols = rohsTables[tname] || [];

    newTableDDL += `-- ============================================================\n`;
    newTableDDL += `-- Table: ${lowerName}  (原 ROHS 表: ${SOURCE_SCHEMA}.${tname})\n`;
    newTableDDL += `-- ${cols.length} columns\n`;

    if (ddl) {
        // Transform: change schema, make table name lowercase, make column names lowercase
        let transformed = ddl;
        // Change schema
        transformed = transformed.replace(new RegExp(`"${SOURCE_SCHEMA}"`, 'g'), `${TARGET_SCHEMA}`);
        // Change table name
        transformed = transformed.replace(new RegExp(`"${tname}"`, 'g'), `${lowerName}`);

        // Lowercase column names (quoted identifiers)
        transformed = transformed.replace(/"(\w+)"/g, (match, col) => {
            // Skip schema.table.column patterns already handled
            // Check if it's a known column name from this table
            const colUpper = col.toUpperCase();
            const isKnownCol = cols.some(c => c.name === colUpper);
            if (isKnownCol) {
                return `"${col.toLowerCase()}"`;
            }
            return match;
        });

        // Also handle unquoted column names at the start of lines (before type)
        for (const col of cols) {
            if (col.name !== col.name.toLowerCase()) {
                const re = new RegExp(`(\\s)"?${col.name}"?(\\s+)`, 'g');
                transformed = transformed.replace(re, `$1${col.name.toLowerCase()}$2`);
            }
        }

        newTableDDL += transformed + '\n\n';
    } else {
        // Generate DDL from parsed columns
        newTableDDL += `CREATE TABLE ${TARGET_SCHEMA}.${lowerName} (\n`;
        const colDefs = cols.map(c => `    "${c.name.toLowerCase()}" ${c.type}`).join(',\n');
        newTableDDL += colDefs;
        newTableDDL += `\n);\n\n`;
    }

    rollbackNewTables += `DROP TABLE IF EXISTS ${TARGET_SCHEMA}.${lowerName} CASCADE;\n`;
}

fs.writeFileSync('d:/Workspaces/slaj/deliverables/01_new_tables_ddl.sql', newTableDDL);
fs.writeFileSync('d:/Workspaces/slaj/deliverables/01_new_tables_rollback.sql', rollbackNewTables);
console.log('Generated: 01_new_tables_ddl.sql + rollback');

// ===== GENERATE DIFF TABLE ALTER =====
let alterScript = `-- ============================================================
-- 差异表 ALTER 脚本 — 37张重叠表的结构对齐
-- 目标: 使 slaj schema 中的表兼容 ROHS 应用
-- 执行方式: psql -h 39.98.38.2 -p 5866 -U highgo -d highgo -f this_file.sql
-- ============================================================

`;

let rollbackAlter = `-- ============================================================
-- 回滚脚本: 撤销差异表的 ALTER 操作
-- ============================================================

`;

const diffDetails = [];

for (const tname of overlapTables.sort()) {
    const dbName = dbLowerMap[tname.toLowerCase()];
    const rCols = rohsTables[tname] || [];
    const dCols = dbOverlap[dbName] || [];

    const rMap = {}; rCols.forEach(c => { rMap[c.name] = c.type; });
    const dMap = {}; dCols.forEach(c => { dMap[c.name] = c.type; });

    const rOnly = Object.keys(rMap).filter(n => !(n in dMap));
    const dOnly = Object.keys(dMap).filter(n => !(n in rMap));
    const common = Object.keys(rMap).filter(n => n in dMap);

    // Find real type diffs
    const realTypeDiffs = [];
    const trivialDiffs = [];
    for (const cn of common) {
        const rt = normType(rMap[cn]);
        const dt = normType(dMap[cn]);
        if (rt !== dt) {
            realTypeDiffs.push({col: cn, rohs: rMap[cn], db: dMap[cn], rohsNorm: rt, dbNorm: dt});
        } else if (rMap[cn] !== dMap[cn]) {
            trivialDiffs.push({col: cn, rohs: rMap[cn], db: dMap[cn]});
        }
    }

    const hasRealDiff = rOnly.length > 0 || dOnly.length > 0 || realTypeDiffs.length > 0;
    if (!hasRealDiff) continue;

    // Special case: tables with completely different column systems (decision: keep both)
    const radicalTables = ['T_BUSI_REGULAR_EVALU', 'T_BUSI_REVIEW_INFO', 'T_DICT_HIDD_ENG'];
    if (radicalTables.includes(tname)) {
        // These tables: ROHS version stays separate, create as new table with _rohs suffix
        const lowerName = tname.toLowerCase() + '_rohs';
        const ddl = extractDDL(tname);
        alterScript += `\n-- ============================================================\n`;
        alterScript += `-- ${tname}: 列名体系完全不同，按决策#5两套并存 — 创建 ROHS 版本为 ${lowerName}\n`;
        alterScript += `-- ============================================================\n\n`;

        if (ddl) {
            let transformed = ddl;
            transformed = transformed.replace(new RegExp(`"${SOURCE_SCHEMA}"`, 'g'), `${TARGET_SCHEMA}`);
            transformed = transformed.replace(new RegExp(`"${tname}"`, 'g'), `${lowerName}`);
            // Lowercase known column names
            for (const col of rCols) {
                if (col.name !== col.name.toLowerCase()) {
                    const re = new RegExp(`"${col.name}"`, 'g');
                    transformed = transformed.replace(re, `"${col.name.toLowerCase()}"`);
                }
            }
            alterScript += transformed + '\n\n';
        }
        rollbackAlter += `DROP TABLE IF EXISTS ${TARGET_SCHEMA}.${lowerName} CASCADE;\n`;

        diffDetails.push({
            rohs: tname, db: dbName, type: 'radical',
            action: `两套并存，ROHS版创建为 ${lowerName}`,
            fields: rCols.map(c => ({rohs: c.name, db: c.name, action: '新建ROHS版表'})),
        });
        continue;
    }

    const lowerName = dbName;
    const alters = [];
    const rollbacks = [];

    // 1. Rename STR_TO_DATE -> TO_DATE (ROHS side)
    if (rOnly.includes('STR_TO_DATE') && dOnly.includes('TO_DATE')) {
        alters.push(`-- 重命名 STR_TO_DATE -> TO_DATE (决策#4)  [注意: 此列从ROHS代码视角改名，安监代码不受影响]`);
        // We keep TO_DATE, no ALTER needed on DB side since DB already has TO_DATE.
        // But we need to tell the ROHS code team that this column is TO_DATE, not STR_TO_DATE.
        // Actually, the ROHS code expects STR_TO_DATE and DB has TO_DATE.
        // Decision was: unify to TO_DATE. So ROHS code must change.
        // On DB side, nothing to do for the column rename (DB already has TO_DATE).
        // But ROHS has STR_TO_DATE which doesn't exist in DB. Nothing to drop.
        alters.push(`-- [仅代码侧改动] ROHS代码需将 STR_TO_DATE 改为 TO_DATE，DB侧无需操作`);
        rOnly.splice(rOnly.indexOf('STR_TO_DATE'), 1);
    }

    // 2. Add columns that ROHS has but DB doesn't
    for (const cn of rOnly) {
        const colType = rMap[cn];
        const lowerCol = cn.toLowerCase();
        // Use wider compatible type
        let pgType = colType;
        if (colType === 'BPCHAR' || colType === 'CHAR(1)') pgType = 'VARCHAR(4)';
        if (colType.startsWith('CHAR(')) pgType = 'VARCHAR(' + colType.match(/\d+/)[0] + ')';
        alters.push(`ALTER TABLE ${TARGET_SCHEMA}.${lowerName} ADD COLUMN "${lowerCol}" ${pgType};`);
        rollbacks.push(`ALTER TABLE ${TARGET_SCHEMA}.${lowerName} DROP COLUMN IF EXISTS "${lowerCol}";`);
    }

    // 3. Add columns that DB has but ROHS doesn't (for ROHS's future use, nullable)
    for (const cn of dOnly) {
        const colType = dMap[cn];
        const lowerCol = cn.toLowerCase();
        let pgType = colType;
        if (pgType === 'BPCHAR') pgType = 'VARCHAR(4)';
        if (pgType === 'CHAR') pgType = 'VARCHAR(4)';
        alters.push(`-- [安监独有列，ROHS兼容] ALTER TABLE ${TARGET_SCHEMA}.${lowerName} ADD COLUMN IF NOT EXISTS "${lowerCol}" ${pgType};`);
        // No rollback — we're not adding, just documenting. DB already has it.
        alters.push(`-- 列 "${lowerCol}" 在安监中已存在，无需操作`);
    }

    // 4. Type changes for real diffs
    for (const td of realTypeDiffs) {
        const lowerCol = td.col.toLowerCase();
        // Determine target type (use wider)
        let targetType = td.rohsNorm === 'TIMESTAMP' ? 'TIMESTAMP' : 'VARCHAR(255)';
        if (td.rohsNorm === 'NUMERIC' && td.dbNorm === 'NUMERIC') targetType = 'NUMERIC';
        if (td.rohsNorm === 'DATE' && td.dbNorm === 'TIMESTAMP') targetType = 'TIMESTAMP';
        if (td.dbNorm === 'DATE' && td.rohsNorm === 'TIMESTAMP') targetType = 'TIMESTAMP';
        if (td.rohsNorm === 'VARCHAR' && td.dbNorm === 'CHAR') targetType = 'VARCHAR(100)';
        if (td.rohsNorm === 'NUMERIC' && td.dbNorm === 'VARCHAR') targetType = 'VARCHAR(100)';

        alters.push(`-- 类型对齐: ${td.rohs} -> ${td.db} (统一为 ${targetType})`);
        alters.push(`ALTER TABLE ${TARGET_SCHEMA}.${lowerName} ALTER COLUMN "${lowerCol}" TYPE ${targetType} USING "${lowerCol}"::${targetType};`);
        rollbacks.push(`-- 回滚类型变更 [${lowerCol}]: 需手动恢复原类型 ${td.db}`);
    }

    if (alters.length > 0) {
        alterScript += `\n-- ============================================================\n`;
        alterScript += `-- ${tname}  <->  ${dbName}  (${rCols.length}c / ${dCols.length}c)\n`;
        alterScript += `-- ============================================================\n\n`;
        alterScript += alters.join('\n') + '\n';
    }

    if (rollbacks.length > 0) {
        rollbackAlter += `\n-- ${tname} rollback\n`;
        rollbackAlter += rollbacks.join('\n') + '\n';
    }

    // Build field mapping
    const allFields = [...new Set([...Object.keys(rMap), ...Object.keys(dMap)])];
    const fields = allFields.map(f => {
        const rType = rMap[f] || '(不存在)';
        const dType = dMap[f] || '(不存在)';
        let action = '';
        if (!(f in dMap)) action = '新增列（ROHS独有）';
        else if (!(f in rMap)) action = '安监独有（ROHS可忽略）';
        else if (normType(rType) !== normType(dType)) action = `类型变更: ${dType} → ${rType}`;
        else if (f === 'STR_TO_DATE' && 'TO_DATE' in dMap) action = 'ROHS侧改为 TO_DATE';
        else action = '一致';
        return {rohs: f, db: f === 'STR_TO_DATE' && 'TO_DATE' in dMap ? 'TO_DATE' : f, rohsType: rType, dbType: dType, action};
    });
    diffDetails.push({rohs: tname, db: dbName, type: 'diff', action: `${alters.length}个ALTER`, fields});
}

fs.writeFileSync('d:/Workspaces/slaj/deliverables/02_diff_tables_alter.sql', alterScript);
fs.writeFileSync('d:/Workspaces/slaj/deliverables/02_diff_tables_rollback.sql', rollbackAlter);
console.log('Generated: 02_diff_tables_alter.sql + rollback');

// ===== GENERATE FIELD MAPPING =====
let mappingMD = `# 字段映射表 — ROHS → 安监(slaj)

> 给改 ROHS 代码的团队使用。列名变更、类型变更汇总。

## 一、新表（${newTables.length}张）

以下表在安监中不存在，直接新建。ROHS 代码侧需将连接 schema 从 SLAJ_ROHS 改为 slaj，表名改为小写。

| ROHS 表名 | 安监表名 | 列数 | 备注 |
|-----------|---------|------|------|
`;

for (const t of newTables.sort()) {
    newTableDDL += '';
    const cols = rohsTables[t] || [];
    mappingMD += `| ${t} | ${t.toLowerCase()} | ${cols.length} | 新表 |\n`;
}

mappingMD += `\n## 二、重叠表字段映射（${diffDetails.length}张）\n\n`;

for (const d of diffDetails) {
    mappingMD += `### ${d.rohs} → ${d.db}\n\n`;
    mappingMD += `**操作**: ${d.action}\n\n`;
    mappingMD += `| ROHS列名 | 安监列名 | ROHS类型 | 安监类型 | 备注 |\n`;
    mappingMD += `|----------|---------|----------|---------|------|\n`;
    for (const f of d.fields) {
        mappingMD += `| ${f.rohs} | ${f.db} | ${f.rohsType} | ${f.dbType} | ${f.action} |\n`;
    }
    mappingMD += '\n';
}

// Also document all overlap tables (including 28 full matches)
const fullMatchTables = overlapTables.filter(t => {
    const dbName = dbLowerMap[t.toLowerCase()];
    const rCols = rohsTables[t] || [];
    const dCols = dbOverlap[dbName] || [];
    const rMap = {}; rCols.forEach(c => { rMap[c.name] = c.type; });
    const dMap = {}; dCols.forEach(c => { dMap[c.name] = c.type; });
    const rOnly = Object.keys(rMap).filter(n => !(n in dMap));
    const dOnly = Object.keys(dMap).filter(n => !(n in rMap));
    const common = Object.keys(rMap).filter(n => n in dMap);
    const realTypeDiffs = common.filter(cn => normType(rMap[cn]) !== normType(dMap[cn]));
    return rOnly.length === 0 && dOnly.length === 0 && realTypeDiffs.length === 0;
});

mappingMD += `\n## 三、完全一致的表（${fullMatchTables.length}张，无需任何改动）\n\n`;
mappingMD += `| ROHS表名 | 安监表名 | 列数 |\n`;
mappingMD += `|----------|---------|------|\n`;
for (const t of fullMatchTables.sort()) {
    const dbName = dbLowerMap[t.toLowerCase()];
    const cols = rohsTables[t] || [];
    mappingMD += `| ${t} | ${dbName} | ${cols.length} |\n`;
}

mappingMD += `\n## 四、ROHS 代码改动清单\n\n`;
mappingMD += `### 必须改动\n\n`;
mappingMD += `1. **Schema 改名**: \`SLAJ_ROHS\` → \`slaj\`（数据库连接配置）\n`;
mappingMD += `2. **表名小写**: 所有表名从大写改为小写（如 \`ATT_ENG_BASE\` → \`att_eng_base\`）\n`;
mappingMD += `3. **列名小写**: 所有列名从大写改为小写\n`;
mappingMD += `4. **STR_TO_DATE → TO_DATE**: 涉及 ${diffDetails.filter(d => d.fields.some(f => f.action.includes('TO_DATE'))).length} 张表\n`;
mappingMD += `5. **3张独立表**: \`T_BUSI_REGULAR_EVALU\` → \`t_busi_regular_evalu_rohs\`, 同理 \`T_BUSI_REVIEW_INFO\`, \`T_DICT_HIDD_ENG\`\n\n`;
mappingMD += `### 建议改动\n\n`;
mappingMD += `1. TIMESTAMP 类型字段：如果代码中将 TIMESTAMP 转为 Date 处理，部分字段在安监侧是 DATE 类型，需确认兼容性\n`;
mappingMD += `2. 新增列（取并集）：代码中如果有 \`SELECT *\` 需确认是否受影响\n`;

fs.writeFileSync('d:/Workspaces/slaj/deliverables/03_field_mapping.md', mappingMD);
console.log('Generated: 03_field_mapping.md');

// ===== GENERATE MASTER EXECUTION GUIDE =====
let guide = `# 合并执行指南

## 执行顺序

### 第一步: 备份
\`\`\`bash
pg_dump -h 39.98.38.2 -p 5866 -U highgo -d highgo -n slaj --schema-only > slaj_schema_backup_$(date +%Y%m%d).sql
\`\`\`

### 第二步: 执行新表DDL（无风险）
\`\`\`bash
psql -h 39.98.38.2 -p 5866 -U highgo -d highgo -f deliverables/01_new_tables_ddl.sql
\`\`\`
涉及 ${newTables.length} 张新表。若失败，执行回滚:
\`\`\`bash
psql -h 39.98.38.2 -p 5866 -U highgo -d highgo -f deliverables/01_new_tables_rollback.sql
\`\`\`

### 第三步: 执行差异表ALTER
\`\`\`bash
psql -h 39.98.38.2 -p 5866 -U highgo -d highgo -f deliverables/02_diff_tables_alter.sql
\`\`\`
涉及 ${diffDetails.length} 张重叠表的字段调整。若失败，执行回滚:
\`\`\`bash
psql -h 39.98.38.2 -p 5866 -U highgo -d highgo -f deliverables/02_diff_tables_rollback.sql
\`\`\`

### 第四步: ROHS 代码改动
参照 \`deliverables/03_field_mapping.md\`，另一团队修改 ROHS 代码适配新 schema。

## 交付物清单

| 文件 | 说明 |
|------|------|
| \`01_new_tables_ddl.sql\` | ${newTables.length} 张新表建表语句 |
| \`01_new_tables_rollback.sql\` | 新表回滚（DROP TABLE） |
| \`02_diff_tables_alter.sql\` | ${diffDetails.length} 张重叠表结构对齐 ALTER |
| \`02_diff_tables_rollback.sql\` | ALTER 回滚脚本 |
| \`03_field_mapping.md\` | 字段映射表（给改代码团队） |
`;

fs.writeFileSync('d:/Workspaces/slaj/deliverables/README.md', guide);
console.log('Generated: README.md (execution guide)');
console.log('\n=== DONE ===');
console.log(`New tables: ${newTables.length}`);
console.log(`Diff tables: ${diffDetails.length}`);
console.log(`Full match: ${fullMatchTables.length}`);