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.db?.host && s.db?.database);
if (!sites.length) { console.log('No sites with a database configured.'); process.exit(0); }

const seen = new Set();
for (const s of sites) {
  const c = s.db;
  const key = `${c.host}|${c.port || 3306}|${c.database}`;
  if (seen.has(key)) continue; // same DB shared by several sites — do it once
  seen.add(key);
  console.log(`\n── ${s.name || s.id}  (${c.database} @ ${c.host})`);
  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 LIKE 'detail_media_statistics'");
    if (!tables.length) { console.log('   table detail_media_statistics not in this DB — skipped'); continue; }
    const [idx] = await conn.query("SHOW INDEX FROM detail_media_statistics WHERE Key_name = 'idx_dms_date'");
    if (idx.length) { console.log('   index already exists — nothing to do ✔'); continue; }
    console.log('   creating index (may take a minute or two on a big table)…');
    const t0 = Date.now();
    await conn.query('CREATE INDEX idx_dms_date ON detail_media_statistics (date_event)');
    console.log(`   done in ${((Date.now() - t0) / 1000).toFixed(1)}s ✔`);
  } catch (err) {
    const msg = String(err.message || err);
    if (/denied|privilege/i.test(msg)) {
      console.log('   ✖ this DB user is read-only — the database admin needs to run:');
      console.log('     CREATE INDEX idx_dms_date ON detail_media_statistics (date_event);');
    } else {
      console.log('   ✖ ' + msg);
    }
  } finally {
    try { await conn?.end(); } catch {}
  }
}
console.log('\nAll done.');
