import { getDb } from './db';

/**
 * Dauerhafte Wiederkehrer-Erkennung ueber anonyme LocalStorage-IDs hinweg.
 * anonymous_visitors haengt an KEINER session_id, ueberlebt also den
 * 48h-Session-Cleanup (lib/cleanup.js loescht nur sessions, participants
 * faellt per ON DELETE CASCADE weg - diese Tabelle bleibt unberuehrt).
 *
 * WICHTIG: Funktioniert nur zuverlaessig, seit join/route.ts eine vom Client
 * mitgeschickte anonymousId wiederverwendet, statt bei jedem Beitritt eine
 * neue randomUUID() zu erzeugen (siehe Aenderung dort). "Wiederkehrer" =
 * dieselbe anonymous_id trat in mindestens 2 unterschiedlichen Sessions als
 * Teilnehmer auf (Lifetime-Betrachtung, kein Zeitfenster).
 */
export function ensureRetentionTracking(db: ReturnType<typeof getDb>) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS anonymous_visitors (
      anonymous_id   TEXT PRIMARY KEY,
      session_count  INTEGER NOT NULL DEFAULT 0,
      first_seen_at  TEXT NOT NULL DEFAULT (datetime('now')),
      last_seen_at   TEXT NOT NULL DEFAULT (datetime('now'))
    );

    -- WHEN-Guard: nur hochzaehlen, wenn diese anonymous_id in DIESER Session
    -- noch nicht als Teilnehmer erfasst war (verhindert Doppelzaehlung bei
    -- mehrfachen INSERTs derselben Person in derselben Session, falls das
    -- je vorkommen sollte - analog zum bestehenden Muster bei
    -- trg_cnt_sessions_with_match in stats/route.ts).
    CREATE TRIGGER IF NOT EXISTS trg_anonymous_visitor_session
    AFTER INSERT ON participants
    WHEN NOT EXISTS (
      SELECT 1 FROM participants
      WHERE anonymous_id = NEW.anonymous_id
        AND session_id = NEW.session_id
        AND id != NEW.id
    )
    BEGIN
      INSERT INTO anonymous_visitors (anonymous_id, session_count, first_seen_at, last_seen_at)
      VALUES (NEW.anonymous_id, 1, datetime('now'), datetime('now'))
      ON CONFLICT(anonymous_id) DO UPDATE SET
        session_count = session_count + 1,
        last_seen_at = datetime('now');
    END;
  `);

}

export interface RetentionStats {
  totalVisitors: number;
  returningVisitors: number;
  returningPct: number;
}

export function getRetentionStats(db: ReturnType<typeof getDb>): RetentionStats {
  const totalVisitors = (
    db.prepare('SELECT COUNT(*) AS c FROM anonymous_visitors').get() as { c: number }
  ).c;
  const returningVisitors = (
    db.prepare('SELECT COUNT(*) AS c FROM anonymous_visitors WHERE session_count >= 2').get() as { c: number }
  ).c;
  return {
    totalVisitors,
    returningVisitors,
    returningPct: totalVisitors > 0 ? Math.round((returningVisitors / totalVisitors) * 1000) / 10 : 0,
  };
}