/**
 * db/analytics.js — analytics_events table CRUD.
 *
 * Owns: reading and writing analytics_events (funnel tracking).
 * Does NOT own: user auth, payment logic, health check execution.
 */

'use strict';

const pool = require('./index');
const { INTERNAL_USER_EMAILS } = require('../config/internal-accounts');

// SQL fragment shared by the funnel aggregations: drop events tied to a known
// internal/test account (Kiran's own sign-ins) while KEEPING anonymous events
// (user_id IS NULL) — those are real visitors. The internal emails are bound as
// the given positional param (a text[]) and resolved to user ids in a subquery.
// `col` qualifies the user_id column so the clause can be reused inside a
// correlated subquery that has its own table alias (e.g. 'c.user_id').
function excludeInternalClause(prefix = 'e', col = 'user_id') {
  // Oracle cannot bind an array, so `= ANY($n::text[])` becomes an explicit IN
  // list of one bind per address. Returns the binds too, because the caller has
  // to merge them into its own bind object.
  const binds = {};
  const names = INTERNAL_USER_EMAILS.map((email, i) => {
    binds[`${prefix}${i}`] = String(email).toLowerCase();
    return `:${prefix}${i}`;
  });
  const clause = names.length
    ? `(${col} IS NULL OR ${col} NOT IN (
            SELECT id FROM users WHERE lower(email) IN (${names.join(', ')})
          ))`
    : '1 = 1';
  return { clause, binds };
}

/**
 * Record a funnel event. Fire-and-forget safe — never throws.
 * @param {Object} opts
 * @param {string}  opts.eventName   — event identifier (e.g. 'signup_completed')
 * @param {number}  [opts.userId]    — authenticated user id (null for anon)
 * @param {string}  [opts.sessionId] — browser session id from sessionStorage or cookie
 * @param {string}  [opts.pagePath]  — page path event occurred on
 * @param {string}  [opts.referrer]  — HTTP referrer
 * @param {string}  [opts.ip]        — truncated IP (/24 v4, /48 v6) — never full IP
 * @param {string}  [opts.ipHash]    — SHA-256 of full IP for dedup without PII storage
 * @param {string}  [opts.userAgent] — User-Agent header value (truncated to 500 chars)
 * @param {Object}  [opts.properties]— additional key/value metadata (JSONB)
 */
async function trackEvent({ eventName, userId = null, sessionId = null, pagePath = null, referrer = null, ip = null, ipHash = null, userAgent = null, properties = null }) {
  try {
    await pool.query(
      `INSERT INTO analytics_events
         (event_name, user_id, session_id, page_path, referrer, ip, ip_hash, user_agent, properties, occurred_at)
       VALUES (:p1, :p2, :p3, :p4, :p5, :p6, :p7, :p8, :p9, CURRENT_TIMESTAMP)`,
      {
        p1: eventName,
        p2: userId || null,
        p3: sessionId || null,
        p4: pagePath ? pagePath.slice(0, 500) : null,
        p5: referrer ? referrer.slice(0, 500) : null,
        p6: ip ? ip.slice(0, 45) : null,
        p7: ipHash || null,
        p8: userAgent ? userAgent.slice(0, 500) : null,
        p9: properties ? JSON.stringify(properties) : null,
      }
    );
  } catch (err) {
    // Silent catch — analytics must never break product functionality
    console.log(JSON.stringify({ level: 'warn', msg: 'analytics.trackEvent failed', eventName, err: err.message }));
  }
}

/**
 * Funnel summary for the admin dashboard.
 * Returns row counts for each funnel step within the given date range.
 * @param {Date} from
 * @param {Date} to
 * @returns {Promise<Array<{event_name: string, count: number, unique_users: number}>>}
 */
async function getFunnelCounts({ from, to }) {
  const ex = excludeInternalClause();
  const result = await pool.query(
    `SELECT
       event_name,
       COUNT(*) AS "count",
       COUNT(DISTINCT user_id) AS unique_users,
       COUNT(DISTINCT session_id) AS unique_sessions
     FROM analytics_events
     WHERE occurred_at BETWEEN :p1 AND :p2
       AND ${ex.clause}
     GROUP BY event_name
     ORDER BY MIN(occurred_at)`,
    { p1: pool.tstz(from), p2: pool.tstz(to), ...ex.binds }
  );
  return result.rows;
}

/**
 * UTM source/medium/campaign breakdown for the admin funnel side panel.
 * Returns top sources by visitor + paid conversion.
 */
async function getUtmBreakdown({ from, to }) {
  const ex = excludeInternalClause();
  const result = await pool.query(
    `SELECT
       COALESCE(JSON_VALUE(properties, '$.utm_source'),   '(direct)') AS utm_source,
       COALESCE(JSON_VALUE(properties, '$.utm_medium'),   '(none)')   AS utm_medium,
       COALESCE(JSON_VALUE(properties, '$.utm_campaign'), '(none)')   AS utm_campaign,
       COUNT(CASE WHEN event_name = 'page_view'          THEN 1 END) AS visitors,
       COUNT(CASE WHEN event_name = 'checkout_completed' THEN 1 END) AS paid
     FROM analytics_events
     WHERE occurred_at BETWEEN :p1 AND :p2
       AND event_name IN ('page_view', 'checkout_completed')
       AND ${ex.clause}
     GROUP BY COALESCE(JSON_VALUE(properties, '$.utm_source'),   '(direct)'),
              COALESCE(JSON_VALUE(properties, '$.utm_medium'),   '(none)'),
              COALESCE(JSON_VALUE(properties, '$.utm_campaign'), '(none)')
     ORDER BY visitors DESC
     FETCH FIRST 20 ROWS ONLY`,
    { p1: pool.tstz(from), p2: pool.tstz(to), ...ex.binds }
  );
  return result.rows;
}

/**
 * Daily event volume for sparkline — returns one row per (day, event_name).
 */
async function getDailyVolume({ from, to }) {
  const ex = excludeInternalClause();
  // DATE_TRUNC('day', x)::date -> TRUNC(x), which truncates to midnight by default.
  // GROUP BY 1, 2 is expanded — Oracle supports positional ORDER BY but NOT
  // positional GROUP BY.
  const result = await pool.query(
    `SELECT
       TRUNC(occurred_at) AS day,
       event_name,
       COUNT(*) AS "count"
     FROM analytics_events
     WHERE occurred_at BETWEEN :p1 AND :p2
       AND ${ex.clause}
     GROUP BY TRUNC(occurred_at), event_name
     ORDER BY 1, 2`,
    { p1: pool.tstz(from), p2: pool.tstz(to), ...ex.binds }
  );
  return result.rows;
}

/**
 * Daily event volume for a single event — legacy sparkline helper.
 * @param {string} eventName
 * @param {Date}   from
 * @param {Date}   to
 * @returns {Promise<Array<{day: string, count: number}>>}
 */
async function getEventTrend(eventName, { from, to }) {
  const result = await pool.query(
    `SELECT
       TRUNC(occurred_at) AS day,
       COUNT(*) AS "count"
     FROM analytics_events
     WHERE event_name = :p1
       AND occurred_at BETWEEN :p2 AND :p3
     GROUP BY TRUNC(occurred_at)
     ORDER BY 1`,
    { p1: eventName, p2: pool.tstz(from), p3: pool.tstz(to) }
  );
  return result.rows;
}

// ─── Page traffic ──────────────────────────────────────────────────────────
// Named buckets for the "Visitors by Page" counters on /admin/funnel. Order is
// the display order. `paths` match the normalised path exactly; `prefixes` match
// with a trailing wildcard (so '/blog/' catches every article). Anything that
// matches nothing lands in '(other)'.
const KEY_PAGES = [
  { key: 'home',     label: 'Home',             paths: ['/'],                                     prefixes: [] },
  { key: 'blog',     label: 'Blog',             paths: ['/blog'],                                 prefixes: ['/blog/'] },
  { key: 'download', label: 'Download',         paths: ['/download'],                             prefixes: [] },
  { key: 'signup',   label: 'Signup / Login',   paths: ['/signup', '/login', '/signin'],          prefixes: [] },
  { key: 'pricing',  label: 'Pricing',          paths: ['/pricing'],                              prefixes: [] },
  { key: 'features', label: 'Features',         paths: ['/features'],                             prefixes: ['/features-', '/features/'] },
  { key: 'docs',     label: 'Docs',             paths: ['/docs'],                                 prefixes: ['/docs/'] },
  { key: 'compare',  label: 'Compare',          paths: ['/compare', '/vs-oem'],                   prefixes: [] },
  { key: 'samples',  label: 'Sample Reports',   paths: ['/sample-report', '/sample-ebs-report'],  prefixes: [] },
  { key: 'trust',    label: 'Trust & Security', paths: ['/trust', '/security'],                   prefixes: ['/security/'] },
];

// Normalise page_path → a comparable path: drop the query string / fragment and
// any trailing slash, so '/blog/', '/blog?utm_source=x' and '/blog' are one row.
// NULL paths are kept as '(unknown)' rather than collapsing into '/'.
// `col` is the qualified column name, so the same expression can be used inside a
// correlated subquery with its own alias.
const normalisedPathSql = (col = 'page_path') => `
  CASE
    WHEN ${col} IS NULL THEN '(unknown)'
    ELSE NVL(NULLIF(RTRIM(REGEXP_SUBSTR(${col}, '^[^?#]+'), '/'), ''), '/')
  END`;
const NORMALISED_PATH_SQL = normalisedPathSql();

// Build the CASE that maps a normalised path to a KEY_PAGES bucket key, plus the
// binds it needs. Patterns are code constants, but they still go through binds so
// the statement stays shareable and quoting can never bite.
function keyPageCaseSql(prefix = 'kp') {
  const binds = {};
  let n = 0;
  const whens = KEY_PAGES.map((page) => {
    const tests = [];
    if (page.paths.length) {
      const names = page.paths.map((p) => {
        const b = `${prefix}${n++}`;
        binds[b] = p;
        return `:${b}`;
      });
      tests.push(`p IN (${names.join(', ')})`);
    }
    page.prefixes.forEach((pre) => {
      const b = `${prefix}${n++}`;
      binds[b] = pre + '%';
      tests.push(`p LIKE :${b}`);
    });
    // The bucket key is inlined, not bound: a CASE whose every THEN is a bind has
    // no anchor for Oracle to resolve the result type from. Keys are code
    // constants, and the assertion keeps it that way if someone adds one.
    if (!/^[a-z][a-z0-9_]*$/.test(page.key)) {
      throw new Error(`KEY_PAGES key must be a bare identifier: ${page.key}`);
    }
    return `WHEN ${tests.join(' OR ')} THEN '${page.key}'`;
  });
  return { sql: `CASE ${whens.join(' ')} ELSE '(other)' END`, binds };
}

/**
 * Total page views + unique visitors (distinct sessions) in the range.
 * Internal/test accounts excluded; anonymous events kept.
 * @returns {Promise<{views: number, visitors: number, known_users: number}>}
 */
async function getVisitorTotals({ from, to }) {
  const ex = excludeInternalClause();
  const result = await pool.query(
    `SELECT
       COUNT(*)                    AS "views",
       COUNT(DISTINCT session_id)  AS visitors,
       COUNT(DISTINCT user_id)     AS known_users
     FROM analytics_events
     WHERE event_name = 'page_view'
       AND occurred_at BETWEEN :p1 AND :p2
       AND ${ex.clause}`,
    { p1: pool.tstz(from), p2: pool.tstz(to), ...ex.binds }
  );
  const row = result.rows[0] || {};
  return {
    views      : Number(row.views       || 0),
    visitors   : Number(row.visitors    || 0),
    known_users: Number(row.known_users || 0),
  };
}

/**
 * Visitor counters for the named key pages (Home / Blog / Download / Signup / …).
 * Bucketing happens in SQL so COUNT(DISTINCT session_id) is exact per bucket —
 * summing per-path uniques client-side would double-count multi-page sessions.
 * @returns {Promise<Array<{key, label, views, visitors}>>} always one row per
 *          KEY_PAGES entry, in display order, zero-filled when there is no traffic.
 */
async function getKeyPageTraffic({ from, to }) {
  const ex   = excludeInternalClause();
  const kase = keyPageCaseSql();
  const result = await pool.query(
    `SELECT bucket,
            COUNT(*)                   AS "views",
            COUNT(DISTINCT session_id) AS visitors
     FROM (
       SELECT session_id, ${kase.sql} AS bucket
       FROM (
         SELECT session_id, ${NORMALISED_PATH_SQL} AS p
         FROM analytics_events
         WHERE event_name = 'page_view'
           AND occurred_at BETWEEN :p1 AND :p2
           AND ${ex.clause}
       )
     )
     GROUP BY bucket`,
    { p1: pool.tstz(from), p2: pool.tstz(to), ...ex.binds, ...kase.binds }
  );

  const byBucket = Object.fromEntries(result.rows.map(r => [r.bucket, r]));
  const rows = KEY_PAGES.map(page => ({
    key     : page.key,
    label   : page.label,
    views   : Number(byBucket[page.key]?.views    || 0),
    visitors: Number(byBucket[page.key]?.visitors || 0),
  }));
  const other = byBucket['(other)'];
  if (other) {
    rows.push({
      key: 'other', label: 'Other pages',
      views: Number(other.views || 0), visitors: Number(other.visitors || 0),
    });
  }
  return rows;
}

// Events that represent forward progress, counted per originating page_path in
// the ranked table. Deliberately NOT "any non-page_view event" — that would fold
// in cta_click and tunebot_query and stop meaning "conversion".
const CONVERSION_EVENTS = [
  'signup_started', 'signup_completed', 'connection_added',
  'health_check_started', 'health_check_completed',
  'checkout_started', 'checkout_completed',
  'demo_started', 'zip_downloaded', 'form_submit_health_check',
];

// Scanner noise, not visitors: traversal probes and CMS/PHP/secret fishing that
// never corresponded to a real page here. Filtered on the RAW path before
// normalisation, so a query string like `?f=../../etc/passwd` is caught too.
const NOISE_FRAGMENTS = ['..', 'passwd', 'wp-', '.env', '.php'];

function noiseClause(column, prefix = 'nz') {
  const binds = {};
  const tests = NOISE_FRAGMENTS.map((frag, i) => {
    const b = `${prefix}${i}`;
    // Bind the pattern whole; '.' and '-' carry no special meaning to LIKE, and
    // none of these fragments contain % or _ so no ESCAPE clause is needed.
    binds[b] = `%${frag}%`;
    return `${column} NOT LIKE :${b}`;
  });
  return { clause: `(${column} IS NULL OR (${tests.join(' AND ')}))`, binds };
}

/**
 * Ranked page-view table for the funnel dashboard.
 * One row per normalised path: views, unique sessions, and the number of
 * conversion events fired from that same path in the range.
 *
 * Conversions are counted in a scalar subquery rather than a join so a page with
 * zero conversions still appears — an inner join would silently drop exactly the
 * pages worth looking at.
 *
 * @returns {Promise<Array<{page_path, views, unique_sessions, conversions}>>}
 */
async function getPageTraffic({ from, to, limit = 20 }) {
  const ex  = excludeInternalClause();
  const exC = excludeInternalClause('c', 'c.user_id');  // own binds + alias for the subquery
  const nz  = noiseClause('page_path');
  const convNames = CONVERSION_EVENTS.map((_, i) => `:ce${i}`).join(', ');
  const convBinds = Object.fromEntries(CONVERSION_EVENTS.map((n, i) => [`ce${i}`, n]));

  const result = await pool.query(
    `SELECT v.p AS page_path,
            COUNT(*)                     AS "views",
            COUNT(DISTINCT v.session_id) AS unique_sessions,
            (SELECT COUNT(*)
               FROM analytics_events c
              WHERE ${normalisedPathSql('c.page_path')} = v.p
                AND c.event_name IN (${convNames})
                AND c.occurred_at BETWEEN :p1 AND :p2
                AND ${exC.clause}
            ) AS conversions
       FROM (
         SELECT session_id, ${NORMALISED_PATH_SQL} AS p
           FROM analytics_events
          WHERE event_name = 'page_view'
            AND occurred_at BETWEEN :p1 AND :p2
            AND ${ex.clause}
            AND ${nz.clause}
       ) v
      GROUP BY v.p
      ORDER BY COUNT(*) DESC
      FETCH FIRST :lim ROWS ONLY`,
    {
      p1: pool.tstz(from), p2: pool.tstz(to), lim: limit,
      ...ex.binds, ...exC.binds, ...nz.binds, ...convBinds,
    }
  );
  return result.rows.map(r => ({
    page_path      : r.page_path,
    views          : Number(r.views           || 0),
    unique_sessions: Number(r.unique_sessions || 0),
    conversions    : Number(r.conversions     || 0),
  }));
}

/**
 * Recent events for the live feed table.
 * @returns {Promise<Array>}
 */
async function getRecentEvents(limit = 50) {
  const result = await pool.query(
    `SELECT id, event_name, user_id, session_id, page_path, referrer, properties, occurred_at
     FROM analytics_events
     ORDER BY occurred_at DESC
     FETCH FIRST :p1 ROWS ONLY`,
    { p1: limit }
  );
  return result.rows;
}

module.exports = {
  trackEvent, getFunnelCounts, getUtmBreakdown, getDailyVolume, getEventTrend, getRecentEvents,
  getVisitorTotals, getKeyPageTraffic, getPageTraffic, KEY_PAGES,
};
