// Read-only MySQL connector for the legacy reports databases (AWS RDS or any
// MySQL host). Uses the mysql2 driver when installed (`npm install mysql2`);
// degrades gracefully when absent. Per-site config in sites.config.json:
//   "db": { "kind": "newsroom" | "newspress", "host": "...", "port": 3306,
//           "database": "hyundai_usa", "user": "lavpulse_ro", "password": "...",
//           "ssl": true }

import { getSite, isDemo } from './ga4.js';

let mysqlMod;
async function driver() {
  if (mysqlMod !== undefined) return mysqlMod;
  try { mysqlMod = (await import('mysql2/promise')).default || (await import('mysql2/promise')); }
  catch { mysqlMod = null; }
  return mysqlMod;
}

// Pools are keyed by site id AND the exact connection config. If a site's DB
// settings change, the old pool is closed and a fresh one is created — a site
// can never keep answering from a previously configured database.
const pools = new Map();
export const confKey = (c) => c ? `${c.host}|${c.port || 3306}|${c.database}|${c.user}` : 'none';
async function poolFor(siteId, c) {
  const key = siteId + '::' + confKey(c);
  const hit = pools.get(siteId);
  if (hit && hit.key === key) return hit.pool;
  if (hit) { try { hit.pool.end(); } catch { /* noop */ } pools.delete(siteId); }
  const m = await driver();
  if (!m) throw new Error('MySQL driver not installed — run `npm install mysql2` in the project folder and restart.');
  const p = m.createPool({
    host: c.host, port: Number(c.port || 3306), database: c.database,
    user: c.user, password: c.password,
    ssl: c.ssl === false ? undefined : { rejectUnauthorized: false },
    connectionLimit: 4, connectTimeout: 8000, dateStrings: true,
  });
  pools.set(siteId, { key, pool: p });
  return p;
}
async function pool(site) {
  return poolFor(site.id, site.db);
}

export function dbKind(site) {
  // A REAL configured reports DB always beats the GA-demo flag — a site can
  // have live databases before its GA4 property is wired up. Only the global
  // DEMO_MODE showcase forces demo data everywhere.
  if (process.env.DEMO_MODE !== '1' && site.db?.host && site.db?.kind) return site.db.kind;
  if (isDemo(site)) return ['newsroom', 'newspress'][hash(site.id) % 2]; // demo showcase
  return null;
}
const hash = (s) => [...String(s)].reduce((a, c) => ((a * 31 + c.charCodeAt(0)) >>> 0), 7);

export function dbConfigured(site) {
  return Boolean(dbKind(site));
}
export function dbIsDemo(site) {
  if (process.env.DEMO_MODE === '1') return true;
  return !site.db?.host || !site.db?.kind;
}

const cache = new Map();
const TTL = 10 * 60 * 1000;
const STALE_TTL = 60 * 60 * 1000; // stale results are still useful for 1 h
const inflight = new Map();       // identical concurrent queries share ONE promise
export async function q(siteId, sql, params = []) {
  const site = getSite(siteId);
  // Cache key includes the connection config, so cached rows from a previous
  // database can never be served after the site is repointed.
  const key = siteId + '|' + confKey(site.db) + '|' + sql + '|' + JSON.stringify(params);
  const hit = cache.get(key);
  if (hit && Date.now() - hit.t < TTL) return hit.v;
  // Same query already running (rapid range/market switching, resize storms):
  // join it instead of queueing a duplicate on the 4-connection pool.
  if (inflight.has(key)) {
    // If we have a stale copy, serve it INSTANTLY and let the running
    // refresh update the cache for the next call.
    if (hit && Date.now() - hit.t < STALE_TTL) return hit.v;
    return inflight.get(key);
  }
  const run = (async () => {
    const p = await pool(site);
    const [rows] = await p.query({ sql, timeout: 45000 }, params);
    cache.set(key, { t: Date.now(), v: rows });
    return rows;
  })();
  const stored = run.finally(() => inflight.delete(key));
  // CRITICAL: mark the stored promise handled. When a stale result is served
  // and the background refresh then fails (e.g. a query timeout on a huge
  // unindexed table), an unmarked rejection here would CRASH the whole
  // process — awaiting callers still receive the rejection normally.
  stored.catch(() => {});
  inflight.set(key, stored);
  // Stale-while-revalidate: a stale hit answers immediately; the refresh
  // continues in the background.
  if (hit && Date.now() - hit.t < STALE_TTL) return hit.v;
  return stored;
}

// Same, against a site's OPTIONAL media-monitoring database (site.monitoring).
const mpools = new Map();
export function monitoringConfigured(site) {
  return Boolean(site.monitoring?.host && site.monitoring?.database);
}
export async function qm(siteId, sql, params = []) {
  const site = getSite(siteId);
  const c = site.monitoring;
  if (!c?.host) throw new Error('No monitoring database configured for this site.');
  const key = siteId + '::' + confKey(c);
  let hit = mpools.get(siteId);
  if (!hit || hit.key !== key) {
    if (hit) { try { hit.pool.end(); } catch { /* noop */ } }
    const m = await driver();
    if (!m) throw new Error('MySQL driver not installed — run `npm install mysql2` and restart.');
    hit = { key, pool: m.createPool({
      host: c.host, port: Number(c.port || 3306), database: c.database,
      user: c.user, password: c.password,
      ssl: c.ssl === false ? undefined : { rejectUnauthorized: false },
      connectionLimit: 4, connectTimeout: 8000, dateStrings: true,
    }) };
    mpools.set(siteId, hit);
  }
  const ck = 'm|' + key + '|' + sql + '|' + JSON.stringify(params);
  const ch = cache.get(ck);
  if (ch && Date.now() - ch.t < TTL) return ch.v;
  const [rows] = await hit.pool.query({ sql, timeout: 20000 }, params);
  cache.set(ck, { t: Date.now(), v: rows });
  return rows;
}

// Read the CENTRAL reports DB's `sites` registry (id, name, type,
// database_connection) — used by the one-click importer in the Sites admin.
export async function centralSites(creds) {
  const m = await driver();
  if (!m) throw new Error('MySQL driver not installed — run `npm install mysql2` and restart.');
  const conn = await m.createConnection({
    host: creds.host, port: Number(creds.port || 3306), database: creds.database,
    user: creds.user, password: creds.password,
    ssl: creds.ssl === false ? undefined : { rejectUnauthorized: false },
    connectTimeout: 8000,
  });
  try {
    const [rows] = await conn.query('SELECT id, name, type, database_connection FROM sites ORDER BY name');
    return rows.map((r) => ({
      id: r.id, name: r.name,
      kind: Number(r.type) === 1 ? 'newspress' : 'newsroom',
      connectionKey: r.database_connection,
    }));
  } finally {
    try { await conn.end(); } catch { /* noop */ }
  }
}

// Connection test used by the Sites admin — tries creds and probes the schema.
export async function testConnection(creds) {
  const m = await driver();
  if (!m) return { ok: false, error: 'MySQL driver not installed — run `npm install mysql2` and restart the app.' };
  let conn;
  try {
    conn = await m.createConnection({
      host: creds.host, port: Number(creds.port || 3306), database: creds.database,
      user: creds.user, password: creds.password,
      ssl: creds.ssl === false ? undefined : { rejectUnauthorized: false },
      connectTimeout: 8000,
    });
    const [tables] = await conn.query('SHOW TABLES');
    const names = tables.map((r) => Object.values(r)[0]);
    const has = (t) => names.includes(t);
    if (creds.mode === 'monitoring') {
      // Recognise the real myNewspress production schema first: an `articles`
      // table whose columns include published_at + headline/title.
      let isMyNews = false;
      if (has('articles')) {
        try {
          const [cols] = await conn.query('DESCRIBE `articles`');
          const low = new Set(cols.map((c) => String(c.Field).toLowerCase()));
          isMyNews = low.has('published_at') && (low.has('headline') || low.has('title'));
        } catch { /* noop */ }
      }
      const artTable = isMyNews ? 'articles'
        : ['coverage', 'clips', 'mentions', 'media_monitoring', 'monitoring_articles', 'press_coverage', 'items']
          .find((t) => names.includes(t)) || null;
      let articles = null;
      if (artTable) {
        try { const [[cnt]] = await conn.query(`SELECT COUNT(*) AS n FROM \`${artTable}\``); articles = cnt.n; } catch { /* noop */ }
      }
      const central = !artTable && (has('tenants') || has('domains'));
      // Central DB? Read the tenants registry and hand back the actual tenant
      // schema names (my-news_<id>), so picking the right one is one click.
      let tenants = null;
      if (central && has('tenants')) {
        try {
          // Which real columns does this tenants table have?
          const [tcols] = await conn.query('DESCRIBE `tenants`');
          const tset = new Set(tcols.map((c) => String(c.Field).toLowerCase()));
          const where = [
            tset.has('platform_id') ? 'platform_id = 1' : null, // 1 = myNews (2 = myEvents)
            tset.has('deleted_at') ? 'deleted_at IS NULL' : null,
          ].filter(Boolean);
          const [rows] = await conn.query(
            'SELECT id' + (has('domains') ? ', (SELECT domain FROM domains WHERE domains.tenant_id = tenants.id LIMIT 1) AS domain' : '') +
            ', data FROM tenants' + (where.length ? ' WHERE ' + where.join(' AND ') : '') + ' ORDER BY id LIMIT 100');
          const parseData = (raw) => {
            if (raw && typeof raw === 'object') return raw; // JSON column → mysql2 pre-parses
            try { return JSON.parse(raw || '{}') || {}; } catch { return {}; }
          };
          tenants = rows.map((r) => {
            const d = parseData(r.data);
            return {
              id: r.id, domain: r.domain || null,
              database: d.tenancy_db_name || `my-news_${r.id}`,
              name: d.name || null,
              slug: d.old_tenant_id != null ? String(d.old_tenant_id) : null, // the old slug IS the client name for myNews
              clientId: d.client_id ?? null,
            };
          });
          // Resolve display names via the central clients registry.
          const missing = tenants.filter((t) => !t.name && t.clientId != null);
          if (missing.length && has('clients')) {
            const ids = [...new Set(missing.map((t) => t.clientId))];
            const [cr] = await conn.query(`SELECT id, name FROM clients WHERE id IN (${ids.map(() => '?').join(',')})`, ids);
            const byId = Object.fromEntries(cr.map((c) => [String(c.id), c.name]));
            for (const t of missing) t.name = byId[String(t.clientId)] || null;
          }
          const titleCase = (s) => String(s).replace(/[-_]+/g, ' ').replace(/\b\w/g, (c) => c.toUpperCase());
          for (const t of tenants) {
            if (!t.name && t.slug && !/^\d+$/.test(t.slug)) t.name = titleCase(t.slug);
            if (!t.name && t.domain) t.name = titleCase(String(t.domain).split('.')[0]);
            if (!t.name) t.name = /^\d+$/.test(String(t.id)) ? `Tenant ${t.id}` : titleCase(t.id);
          }
          tenants.sort((a, b) => String(a.name).localeCompare(String(b.name)));
        } catch { /* registry unreadable — hint still shows */ }
      }
      return { ok: true, tables: names.length, monitoringTable: artTable, articles, tenants,
               detectedKind: isMyNews ? 'mynewspress' : central ? 'mynewspress-central' : artTable ? 'monitoring' : 'unknown',
               hint: central ? 'This is the myNewspress CENTRAL database — switch the database name to this client\'s TENANT schema (the one containing the articles table).'
                 : !artTable ? 'No coverage/articles table found in this database — double-check you connected the client\'s myNewspress TENANT schema.' : null };
    }
    const looksNewspress = has('companies') && has('releases');
    const looksNewsroom = !has('companies') && has('releases') && (has('models') || has('release_categories'));
    const [[counts]] = await conn.query('SELECT (SELECT COUNT(*) FROM releases) AS releases');
    return {
      ok: true,
      tables: names.length,
      releases: counts.releases,
      detectedKind: looksNewspress ? 'newspress' : looksNewsroom ? 'newsroom' : 'unknown',
    };
  } catch (err) {
    return { ok: false, error: String(err.message || err) };
  } finally {
    try { await conn?.end(); } catch { /* noop */ }
  }
}
