Tiktok-Rezepte/backend/analytics-db.ts

import Database from 'better-sqlite3';
import { dirname, join } from 'path';
import { fileURLToPath } from 'url';
import { getUserById } from './db.js';

const __dirname = dirname(fileURLToPath(import.meta.url));
const DB_FILE = process.env.ANALYTICS_PATH || join(__dirname, 'analytics.db');

const db = new Database(DB_FILE);
db.pragma('journal_mode = WAL');

// Tabelle erstellen falls nicht vorhanden
db.exec(`
  CREATE TABLE IF NOT EXISTS events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    user_id INTEGER,
    timestamp TEXT,
    metadata TEXT
  );
`);

export interface AnalyticsEvent {
  id?: number;
  type: string;
  user_id: number | null;
  timestamp: string;
  [key: string]: any;
}

function now(): string {
  return new Date().toISOString().replace('T', ' ').slice(0, 19);
}

export function trackEvent(type: string, userId?: number | null, metadata: Record<string, any> = {}): AnalyticsEvent {
  const timestamp = now();
  const metaStr = JSON.stringify(metadata);

  const stmt = db.prepare('INSERT INTO events (type, user_id, timestamp, metadata) VALUES (?, ?, ?, ?)');
  const info = stmt.run(type, userId || null, timestamp, metaStr);

  return {
    id: info.lastInsertRowid as number,
    type,
    user_id: userId || null,
    timestamp,
    ...metadata,
  };
}

export function getEvents({ from, to, type, user_id, limit = 100 }: { from?: string; to?: string; type?: string; user_id?: number | string; limit?: number } = {}): AnalyticsEvent[] {
  let query = 'SELECT * FROM events WHERE 1=1';
  const params: (string | number)[] = [];

  if (from) {
    query += ' AND timestamp >= ?';
    params.push(from);
  }
  if (to) {
    query += ' AND timestamp <= ?';
    params.push(to);
  }
  if (type) {
    query += ' AND type = ?';
    params.push(type);
  }
  if (user_id) {
    query += ' AND user_id = ?';
    params.push(Number(user_id));
  }

  query += ' ORDER BY timestamp DESC LIMIT ?';
  params.push(limit);

  const rows = db.prepare(query).all(...params) as { id: number; type: string; user_id: number | null; timestamp: string; metadata: string }[];

  return rows.map(r => {
    let meta = {};
    try { meta = JSON.parse(r.metadata || '{}'); } catch {}
    return {
      id: r.id,
      type: r.type,
      user_id: r.user_id,
      timestamp: r.timestamp,
      ...meta,
    };
  });
}

export function getOverview() {
  const today = new Date().toISOString().slice(0, 10);
  const weekAgo = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString().slice(0, 10);
  const yesterday = new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString().slice(0, 10);

  const allPageviews = db.prepare("SELECT timestamp FROM events WHERE type = 'page_view'").all() as { timestamp: string }[];
  const pageviewsToday = allPageviews.filter(e => e.timestamp.startsWith(today)).length;
  const pageviewsYesterday = allPageviews.filter(e => e.timestamp.startsWith(yesterday)).length;
  const pageviewsWeek = allPageviews.filter(e => e.timestamp.slice(0, 10) >= weekAgo).length;
  const pageviewsTotal = allPageviews.length;

  const activeUsersTotal = (db.prepare("SELECT COUNT(DISTINCT user_id) as count FROM events WHERE user_id IS NOT NULL").get() as { count: number }).count;
  const activeUsersToday = (db.prepare("SELECT COUNT(DISTINCT user_id) as count FROM events WHERE user_id IS NOT NULL AND timestamp LIKE ?").get(`${today}%`) as { count: number }).count;

  const recipesCreated = (db.prepare("SELECT COUNT(*) as count FROM events WHERE type = 'recipe_created'").get() as { count: number }).count;
  const recipesUpdated = (db.prepare("SELECT COUNT(*) as count FROM events WHERE type = 'recipe_updated'").get() as { count: number }).count;
  const recipesDeleted = (db.prepare("SELECT COUNT(*) as count FROM events WHERE type = 'recipe_deleted'").get() as { count: number }).count;
  const logins = (db.prepare("SELECT COUNT(*) as count FROM events WHERE type = 'user_login'").get() as { count: number }).count;

  return {
    pageviews: { today: pageviewsToday, yesterday: pageviewsYesterday, week: pageviewsWeek, total: pageviewsTotal },
    activeUsers: { total: activeUsersTotal, today: activeUsersToday },
    recipes: { created: recipesCreated, updated: recipesUpdated, deleted: recipesDeleted },
    logins,
  };
}

export function getPageviewsByDay(from?: string, to?: string) {
  let query = "SELECT SUBSTR(timestamp, 1, 10) as day, COUNT(*) as count FROM events WHERE type = 'page_view'";
  const params: string[] = [];

  if (from) {
    query += " AND SUBSTR(timestamp, 1, 10) >= ?";
    params.push(from);
  }
  if (to) {
    query += " AND SUBSTR(timestamp, 1, 10) <= ?";
    params.push(to);
  }

  query += " GROUP BY day ORDER BY day ASC";
  const rows = db.prepare(query).all(...params) as { day: string; count: number }[];
  return rows.map(r => ({ date: r.day, count: r.count }));
}

export function getTopUsers(limit = 10) {
  const rows = db.prepare(`
    SELECT user_id,
           SUM(CASE WHEN type = 'user_login' THEN 1 ELSE 0 END) as logins,
           SUM(CASE WHEN type = 'recipe_created' THEN 1 ELSE 0 END) as recipes_created,
           SUM(CASE WHEN type = 'recipe_updated' THEN 1 ELSE 0 END) as recipes_updated,
           COUNT(*) as total_events
    FROM events
    WHERE user_id IS NOT NULL
    GROUP BY user_id
    ORDER BY total_events DESC
    LIMIT ?
  `).all(limit) as any[];

  return rows;
}

export function getTopRecipes(limit = 10) {
  const pageviews = db.prepare("SELECT metadata FROM events WHERE type = 'page_view'").all() as { metadata: string }[];
  const recipeViews: Record<string, number> = {};

  for (const row of pageviews) {
    try {
      const meta = JSON.parse(row.metadata || '{}');
      if (meta.path && meta.path.startsWith('/recipe/')) {
        const id = meta.path.split('/')[2];
        if (id) {
          recipeViews[id] = (recipeViews[id] || 0) + 1;
        }
      }
    } catch {}
  }

  return Object.entries(recipeViews)
    .map(([recipe_id, views]) => ({ recipe_id: Number(recipe_id), views }))
    .sort((a, b) => b.views - a.views)
    .slice(0, limit);
}

export function getRecentActivity(limit = 50): AnalyticsEvent[] {
  return getEvents({ limit });
}