/**
 * services/digest.js
 *
 * Build a weekly (or daily) summary of the operator's activity:
 *   - new cases opened
 *   - takedowns sent / escalated / removed (with links)
 *   - findings classified
 *   - link health status (still live / removed)
 *
 * Returns a { subject, text, html } triple ready to send via the
 * email transport. Pure function — no DB writes, no side effects.
 */

'use strict';

const { db } = require('../config/db');
const { publicBaseUrl } = require('../config/publicBaseUrl');

function build({ userId, since }) {
  const sinceMs = since instanceof Date ? since.getTime() : (since || Date.now() - 7 * 86400000);
  const sinceIso = new Date(sinceMs).toISOString();
  const untilIso = new Date().toISOString();
  const u = db.prepare('SELECT id, email, name FROM users WHERE id = ?').get(userId);
  if (!u) return null;

  // Cases
  const newCases = db.prepare(`SELECT id, victim_name, status, reference_url, created_at FROM cases
    WHERE user_id = ? AND created_at >= ? ORDER BY created_at DESC`).all(userId, sinceMs);

  // Takedowns by state
  const tds = db.prepare(`SELECT t.id, t.case_id, t.domain, t.url, t.contact_email, t.status, t.sent_at, t.resolved_at,
    (SELECT status FROM link_health_checks WHERE takedown_id = t.id ORDER BY checked_at DESC LIMIT 1) as last_health_status
    FROM takedowns t
    WHERE t.user_id = ? AND (t.created_at >= ? OR t.sent_at >= ? OR t.resolved_at >= ?)
    ORDER BY t.created_at DESC LIMIT 100`).all(userId, sinceMs, sinceMs, sinceMs);

  // Findings
  const findings = db.prepare(`SELECT id, url, domain, tier, is_ncii, status, created_at FROM findings
    WHERE user_id = ? AND created_at >= ? ORDER BY created_at DESC LIMIT 20`).all(userId, sinceMs);

  // Classified (with category)
  const classified = db.prepare(`
    SELECT fc.id, fc.finding_id, fc.category, fc.confidence, f.url, f.domain
      FROM finding_classifications fc
      JOIN findings f ON f.id = fc.finding_id
     WHERE f.user_id = ? AND fc.classified_at >= ?
     ORDER BY fc.classified_at DESC LIMIT 20`).all(userId, sinceMs);

  // Counts by state
  const byStatus = {};
  for (const t of tds) byStatus[t.status] = (byStatus[t.status] || 0) + 1;
  const byHealth = {};
  for (const t of tds) {
    const h = t.last_health_status || 'never';
    byHealth[h] = (byHealth[h] || 0) + 1;
  }

  const totalRemoved = (byStatus.removed || 0) + (byStatus.responded || 0);
  const totalSent = (byStatus.sent || 0) + (byStatus.escalated || 0) + totalRemoved;
  const totalLive = byHealth.live || 0;
  const removalRate = totalSent > 0 ? Math.round((totalRemoved / totalSent) * 100) : null;

  // Subject
  const subject = `Shield weekly: ${totalSent} sent, ${totalRemoved} removed (${removalRate ?? 'n/a'}%), ${tds.filter(t => t.last_health_status === 'live').length} still live`;

  // Plain text body
  const lines = [];
  lines.push(`Hi ${u.name},`);
  lines.push('');
  lines.push(`Here's what Shield did for you from ${new Date(sinceMs).toUTCString()} to ${new Date().toUTCString()}:`);
  lines.push('');
  lines.push(`  New cases opened:    ${newCases.length}`);
  lines.push(`  Takedowns sent:      ${byStatus.sent || 0} (+ ${byStatus.escalated || 0} escalated)`);
  lines.push(`  Confirmed removed:   ${totalRemoved} (${removalRate ?? 'n/a'}% of sent)`);
  lines.push(`  Still live:          ${totalLive} (need operator attention)`);
  lines.push(`  Findings classified: ${classified.length} (auto-learner ran ${classified.length}x)`);
  lines.push('');

  if (newCases.length) {
    lines.push('--- New cases ---');
    for (const c of newCases.slice(0, 5)) {
      lines.push(`  #${c.id} ${c.victim_name} — ${c.status}${c.reference_url ? ' — ' + c.reference_url.slice(0, 60) : ''}`);
    }
    lines.push('');
  }

  if (tds.length) {
    lines.push('--- Takedown status ---');
    for (const t of tds.slice(0, 12)) {
      const sent = t.sent_at ? new Date(t.sent_at).toISOString().slice(0, 10) : 'queued';
      const health = t.last_health_status || '—';
      lines.push(`  #${t.id} ${t.domain.padEnd(15)} ${t.status.padEnd(10)} health=${health.padEnd(8)} sent=${sent} → ${t.url.slice(0, 60)}`);
    }
    if (tds.length > 12) lines.push(`  ... and ${tds.length - 12} more`);
    lines.push('');
  }

  if (totalLive > 0) {
    lines.push('--- Still live (action needed) ---');
    for (const t of tds.filter(t => t.last_health_status === 'live').slice(0, 5)) {
      lines.push(`  #${t.id} ${t.domain} — ${t.url.slice(0, 60)}`);
    }
    lines.push('  Consider escalating or following up directly with the host.');
    lines.push('');
  }

  if (classified.length) {
    lines.push('--- AI classifications ---');
    for (const c of classified.slice(0, 5)) {
      lines.push(`  #${c.id} ${c.domain} → ${c.category} (${Math.round(c.confidence * 100)}%) — ${c.url.slice(0, 60)}`);
    }
    lines.push('');
  }

  lines.push('Open your dashboard: ' + publicBaseUrl() + '/dashboard/');
  lines.push('');
  lines.push('— Shield');

  // HTML body
  const html = buildHtml({ u, newCases, tds, byStatus, byHealth, totalSent, totalRemoved, totalLive, removalRate, classified, sinceIso, untilIso });
  return { subject, text: lines.join('\n'), html };
}

function buildHtml({ u, newCases, tds, byStatus, byHealth, totalSent, totalRemoved, totalLive, removalRate, classified, sinceIso, untilIso }) {
  const tr = (cells) => '<tr>' + cells.map(c => `<td style="padding:.4rem .6rem;border-bottom:1px solid #2a2f3a">${c}</td>`).join('') + '</tr>';
  const esc = (s) => String(s || '').replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
  const statusPill = (s) => {
    const map = { sent: '#4f8cff', escalated: '#4f8cff', removed: '#22c55e', responded: '#22c55e', failed: '#ef4444', production_pending: '#f59e0b', chain_only: '#94a3b8' };
    const color = map[s] || '#94a3b8';
    return `<span style="display:inline-block;padding:.1rem .45rem;border-radius:3px;background:${color}22;color:${color};font-size:.78rem">${esc(s)}</span>`;
  };
  const healthPill = (h) => {
    const map = { live: '#ef4444', removed: '#22c55e', soft_404: '#22c55e', blocked: '#f59e0b', dns_fail: '#f59e0b', error: '#ef4444' };
    const color = map[h] || '#94a3b8';
    const label = h || '—';
    return `<span style="display:inline-block;padding:.1rem .45rem;border-radius:3px;background:${color}22;color:${color};font-size:.78rem">${esc(label)}</span>`;
  };
  return `<!doctype html><html><body style="background:#0c0e14;color:#e6e8ee;font-family:-apple-system,Segoe UI,Roboto,sans-serif;padding:1.5rem;max-width:780px;margin:0 auto">
<div style="background:#11141c;padding:1.5rem;border-radius:8px;border:1px solid #2a2f3a">
  <h1 style="margin:0 0 .25rem 0;font-size:1.4rem">Shield weekly digest</h1>
  <div style="color:#94a3b8;font-size:.85rem;margin-bottom:1rem">For ${esc(u.name)} · ${esc(sinceIso.slice(0,10))} → ${esc(untilIso.slice(0,10))}</div>
  <div style="display:grid;grid-template-columns:repeat(2,1fr);gap:.75rem;margin:1rem 0">
    <div style="background:#0c0e14;padding:.75rem;border-radius:6px"><div style="color:#94a3b8;font-size:.75rem">TAKEDOWNS SENT</div><div style="font-size:1.5rem;font-weight:600">${totalSent}</div></div>
    <div style="background:#0c0e14;padding:.75rem;border-radius:6px"><div style="color:#94a3b8;font-size:.75rem">CONFIRMED REMOVED</div><div style="font-size:1.5rem;font-weight:600;color:#22c55e">${totalRemoved}</div><div style="font-size:.7rem;color:#94a3b8">${removalRate ?? 'n/a'}% of sent</div></div>
    <div style="background:#0c0e14;padding:.75rem;border-radius:6px"><div style="color:#94a3b8;font-size:.75rem">STILL LIVE</div><div style="font-size:1.5rem;font-weight:600;color:${totalLive > 0 ? '#ef4444' : '#22c55e'}">${totalLive}</div></div>
    <div style="background:#0c0e14;padding:.75rem;border-radius:6px"><div style="color:#94a3b8;font-size:.75rem">AI CLASSIFIED</div><div style="font-size:1.5rem;font-weight:600">${classified.length}</div></div>
  </div>
  ${newCases.length ? `<h2 style="font-size:1rem;margin:1.25rem 0 .5rem">New cases</h2>
    <table style="width:100%;border-collapse:collapse;font-size:.85rem">${tr(newCases.map(c => `<b>#${c.id}</b> ${esc(c.victim_name)} — ${statusPill(c.status)}`))}</table>` : ''}
  ${tds.length ? `<h2 style="font-size:1rem;margin:1.25rem 0 .5rem">Takedown status</h2>
    <table style="width:100%;border-collapse:collapse;font-size:.78rem">
      <tr style="background:#0c0e14;color:#94a3b8">
        <th style="text-align:left;padding:.35rem .5rem">#</th>
        <th style="text-align:left;padding:.35rem .5rem">Domain</th>
        <th style="text-align:left;padding:.35rem .5rem">Status</th>
        <th style="text-align:left;padding:.35rem .5rem">Health</th>
        <th style="text-align:left;padding:.35rem .5rem">Sent</th>
        <th style="text-align:left;padding:.35rem .5rem">URL</th>
      </tr>
      ${tds.slice(0, 20).map(t => tr([
        `<b>#${t.id}</b>`, esc(t.domain), statusPill(t.status), healthPill(t.last_health_status),
        t.sent_at ? esc(new Date(t.sent_at).toISOString().slice(0,10)) : '—',
        `<span title="${esc(t.url)}">${esc(t.url.slice(0, 60))}</span>`,
      ])).join('')}
    </table>` : ''}
  <p style="margin-top:1.5rem"><a href="${publicBaseUrl()}/dashboard/" style="background:#4f8cff;color:#fff;padding:.55rem 1rem;border-radius:4px;text-decoration:none">Open dashboard →</a></p>
  <div style="color:#94a3b8;font-size:.75rem;margin-top:1.5rem;border-top:1px solid #2a2f3a;padding-top:.75rem">Shield · You're receiving this because you have a digest subscription. Manage in <a href="${publicBaseUrl()}/admin/" style="color:#94a3b8">admin → webhooks</a>.</div>
</div></body></html>`;
}

module.exports = { build };
