const fs = require('fs');
const content = fs.readFileSync('d:/Workspaces/slaj/docs/slaj-rohs_tbl-all_without-data_hg.sql', 'utf8');

// Split by CREATE TABLE
const blocks = content.split(/\n\s*CREATE TABLE /);
const tables = {};

let blockIdx = 0;
for (const block of blocks) {
    if (blockIdx === 0) {
        blockIdx++;
        // First block is before first CREATE TABLE - but check if it starts with CREATE TABLE
        const hm = block.match(/^\s*CREATE TABLE\s*"SLAJ_ROHS"\."(\w+)"/);
        if (!hm) continue;
        // process it anyway
    }
    const headerMatch = block.match(/^\s*"SLAJ_ROHS"\."(\w+)"/);
    if (!headerMatch) continue;
    const name = headerMatch[1];

    // Find the matching ); for this block by counting parentheses
    let depth = 0;
    let startIdx = block.indexOf('(');
    if (startIdx === -1) continue;

    let colsText = '';
    for (let i = startIdx + 1; i < block.length && depth >= 0; i++) {
        if (block[i] === '(') depth++;
        else if (block[i] === ')') {
            if (depth === 0) {
                colsText = block.substring(startIdx + 1, i);
                break;
            }
            depth--;
        }
    }

    const cols = [];
    // Split by lines and parse
    const lines = colsText.split('\n');
    for (let i = 0; i < lines.length; i++) {
        let line = lines[i].trim();
        // Accumulate continuation lines
        while (i + 1 < lines.length) {
            const next = lines[i+1].trim();
            if (next.startsWith('"') || /^(CONSTRAINT|PRIMARY KEY|UNIQUE|CHECK|EXCLUDE|USING|FOREIGN)/i.test(next) || next === '' || next.startsWith('--')) {
                break;
            }
            i++;
            line += ' ' + next;
            if (next.endsWith(',')) break;
        }
        line = line.replace(/,\s*$/, '').trim();
        if (!line || /^(CONSTRAINT|PRIMARY KEY|UNIQUE|CHECK|USING|EXCLUDE|FOREIGN)/i.test(line) || line.startsWith('--')) {
            continue;
        }

        const cm = line.match(/^"(\w+)"\s+(.+)$/);
        if (cm) {
            const colName = cm[1];
            let colType = cm[2].trim();
            // Remove trailing NOT NULL, DEFAULT, etc.
            colType = colType.replace(/\s+(?:NOT\s+NULL|DEFAULT\s+[^,]+|NULL|COLLATE\s+\w+|PRIMARY\s+KEY|REFERENCES\s+[^,]+)(?=\s|$)/gi, ' ').trim();
            cols.push({name: colName.toUpperCase(), type: colType.toUpperCase().replace(/\s+/g, ' ')});
        }
    }

    tables[name] = cols;
    blockIdx++;
}

// Save
fs.writeFileSync('d:/Workspaces/slaj/_rohs_tables.json', JSON.stringify(tables, null, 2));
console.log('Total tables:', Object.keys(tables).length);

// Categorize - skip MLOG, MV, backups, etc.
const skipSet = new Set();
for (const t of Object.keys(tables)) {
    if (t.startsWith('MLOG$_')) skipSet.add(t);
    else if (t.startsWith('MV_')) skipSet.add(t);
    else if (t.startsWith('T_VWMS_')) skipSet.add(t);
    else if (/_BAK\d*$/.test(t)) skipSet.add(t);
    else if (/_MW$/.test(t) || /_MW_/.test(t)) skipSet.add(t);
    else if (/_0731$/.test(t)) skipSet.add(t);
    else if (/_1125$/.test(t)) skipSet.add(t);
    else if (/_BAK25$/.test(t)) skipSet.add(t);
    else if (/_0326$/.test(t)) skipSet.add(t);
    else if (/_20250407$/.test(t)) skipSet.add(t);
    else if (/_20250113$/.test(t)) skipSet.add(t);
    else if (/_20250110$/.test(t)) skipSet.add(t);
    else if (/BAK2025/.test(t)) skipSet.add(t);
}

const real = Object.keys(tables).filter(t => !skipSet.has(t));
const skip = [...skipSet].sort();

console.log('Real tables:', real.length);
console.log('Skipped (backup/log/MV):', skip.length);
console.log('');

console.log('=== REAL TABLES ===');
real.sort().forEach(t => console.log(t + ' (' + tables[t].length + ' cols)'));
console.log('');
console.log('=== SKIPPED TABLES ===');
skip.forEach(t => console.log(t));