import fs from 'node:fs';
import mysql from 'mysql2/promise';

const cfg = JSON.parse(fs.readFileSync('sites.config.json', 'utf8'));
const sites = (cfg.sites || []).filter((s) => s.monitoring?.host && s.monitoring?.database);
if (!sites.length) { console.log('No sites with a monitoring database configured.'); process.exit(0); }

const out = [];
const seen = new Set();
const trunc = (v) => { const s = v == null ? 'NULL' : String(v); return s.length > 220 ? s.slice(0, 220) + '…' : s; };
for (const s of sites) {
  const c = s.monitoring;
  const key = `${c.host}|${c.port || 3306}|${c.database}`;
  if (seen.has(key)) continue;
  seen.add(key);
  out.push(`\n================ ${s.name || s.id} — ${c.database} @ ${c.host} ================\n`);
  let conn;
  try {
    conn = await mysql.createConnection({
      host: c.host, port: Number(c.port || 3306), database: c.database,
      user: c.user, password: c.password,
      ssl: c.ssl === false ? undefined : { rejectUnauthorized: false },
      connectTimeout: 8000,
    });
    const [tables] = await conn.query('SHOW TABLES');
    const names = tables.map((r) => Object.values(r)[0]);
    for (const t of names) {
      try {
        const [[cnt]] = await conn.query(`SELECT COUNT(*) AS n FROM \`${t}\``);
        const [[ct]] = await conn.query(`SHOW CREATE TABLE \`${t}\``);
        out.push(`\n--- TABLE ${t} (${cnt.n} rows) ---`);
        out.push(ct['Create Table'] || ct['Create View'] || '');
        // Small lookup/config tables + anything region/market/category/
        // sentiment/competitor-ish: dump the rows so labels are visible.
        const interesting = /market|region|categor|sentiment|competitor|sov|type|tag|client|feed|topic|country/i.test(t);
        if (cnt.n > 0 && (cnt.n <= 60 || interesting)) {
          const lim = Math.min(cnt.n, interesting ? 200 : 60);
          const [rows] = await conn.query(`SELECT * FROM \`${t}\` LIMIT ${lim}`);
          out.push(`SAMPLE ROWS (${rows.length}):`);
          for (const r of rows) out.push('  ' + JSON.stringify(Object.fromEntries(Object.entries(r).map(([k, v]) => [k, trunc(v)]))));
        } else if (cnt.n > 0) {
          const [rows] = await conn.query(`SELECT * FROM \`${t}\` LIMIT 3`);
          out.push('SAMPLE ROWS (3):');
          for (const r of rows) out.push('  ' + JSON.stringify(Object.fromEntries(Object.entries(r).map(([k, v]) => [k, trunc(v)]))));
        }
      } catch (e) { out.push(`  (error on ${t}: ${e.message})`); }
    }
  } catch (e) {
    out.push('CONNECTION ERROR: ' + e.message);
  } finally { try { await conn?.end(); } catch {} }
}
fs.writeFileSync('mm-structure.txt', out.join('\n'));
console.log('Written mm-structure.txt (' + Math.round(out.join('\n').length / 1024) + ' KB) — upload this file to the chat.');
