import { getDb } from './db';

/**
 * Dauerhafte Pro-Restaurant-Statistik (Impressions/"Ja"-Swipes/Matches).
 * Ausgelagert aus app/api/admin/stats/route.ts (v7), damit auch die
 * Sponsoren-Liste (app/api/admin/sponsors/route.ts) dieselben Zahlen ohne
 * Code-Duplikat lesen kann - vorher lud die Sponsoring-Seite NIE die Stats,
 * weil sie nur GET /api/admin/sponsors aufruft, nicht /api/admin/stats.
 *
 * Grund fuer die Tabelle selbst (unveraendert seit v7): sponsorCampaign-
 * Performance zaehlte urspruenglich LIVE per COUNT(*) auf swipes/matches -
 * diese Zeilen werden aber vom 48h-Session-Cleanup per ON DELETE CASCADE
 * geloescht. restaurant_stats haengt an restaurants(id), die vom Cleanup NIE
 * geloescht werden (lib/cleanup.js loescht nur sessions), Zahlen bleiben also
 * echte Lifetime-Werte. Triggers folgen dem bewaehrten Muster aus
 * ensureCounters() in stats/route.ts (inkl. Backfill).
 */
export function ensureRestaurantStats(db: ReturnType<typeof getDb>) {
  db.exec(`
    CREATE TABLE IF NOT EXISTS restaurant_stats (
      restaurant_id INTEGER PRIMARY KEY REFERENCES restaurants(id) ON DELETE CASCADE,
      impressions   INTEGER NOT NULL DEFAULT 0,
      yes_swipes    INTEGER NOT NULL DEFAULT 0,
      matches       INTEGER NOT NULL DEFAULT 0
    );

    CREATE TRIGGER IF NOT EXISTS trg_restaurant_stats_swipe
    AFTER INSERT ON swipes
    BEGIN
      INSERT INTO restaurant_stats (restaurant_id, impressions, yes_swipes)
      VALUES (NEW.restaurant_id, 1, CASE WHEN NEW.direction = 'yes' THEN 1 ELSE 0 END)
      ON CONFLICT(restaurant_id) DO UPDATE SET
        impressions = impressions + 1,
        yes_swipes = yes_swipes + (CASE WHEN NEW.direction = 'yes' THEN 1 ELSE 0 END);
    END;

    CREATE TRIGGER IF NOT EXISTS trg_restaurant_stats_match
    AFTER INSERT ON matches
    BEGIN
      INSERT INTO restaurant_stats (restaurant_id, matches)
      VALUES (NEW.restaurant_id, 1)
      ON CONFLICT(restaurant_id) DO UPDATE SET matches = matches + 1;
    END;

    -- Tagesgenaue Zeitreihe fuer den Performance-Verlauf auf der
    -- Sponsor-Detailseite. Haengt an restaurants(id) und wird daher vom
    -- 48h-Session-Cleanup NICHT geloescht (im Gegensatz zu swipes/matches).
    CREATE TABLE IF NOT EXISTS restaurant_stats_daily (
      restaurant_id INTEGER NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
      day           TEXT NOT NULL,
      impressions   INTEGER NOT NULL DEFAULT 0,
      yes_swipes    INTEGER NOT NULL DEFAULT 0,
      matches       INTEGER NOT NULL DEFAULT 0,
      PRIMARY KEY (restaurant_id, day)
    );

    CREATE TRIGGER IF NOT EXISTS trg_restaurant_stats_daily_swipe
    AFTER INSERT ON swipes
    BEGIN
      INSERT INTO restaurant_stats_daily (restaurant_id, day, impressions, yes_swipes)
      VALUES (NEW.restaurant_id, date(NEW.created_at), 1, CASE WHEN NEW.direction = 'yes' THEN 1 ELSE 0 END)
      ON CONFLICT(restaurant_id, day) DO UPDATE SET
        impressions = impressions + 1,
        yes_swipes = yes_swipes + (CASE WHEN NEW.direction = 'yes' THEN 1 ELSE 0 END);
    END;

    CREATE TRIGGER IF NOT EXISTS trg_restaurant_stats_daily_match
    AFTER INSERT ON matches
    BEGIN
      INSERT INTO restaurant_stats_daily (restaurant_id, day, matches)
      VALUES (NEW.restaurant_id, date(NEW.created_at), 1)
      ON CONFLICT(restaurant_id, day) DO UPDATE SET matches = matches + 1;
    END;
  `);

}