import type Database from 'better-sqlite3';

export interface RankingEntry {
  restaurant_id: number;
  name: string;
  address: string | null;
  postal_code: string | null;
  suburb: string | null;
  city?: string | null;
  cuisine: string | null;
  lat: number | null;
  lon: number | null;
  osm_image_url: string | null;
  admin_image_url: string | null;
  fallback_image_url: string | null;
  sponsor_image_url: string | null;
  lieferando_url: string | null;
  yes_count: number;
  no_count: number;
}

/**
 * Aktuelles Zwischen-Ranking einer Session: alle geswipten Restaurants mit
 * mindestens einer Ja-Stimme, sortiert nach Ja-Stimmen absteigend (bei
 * Gleichstand weniger Nein-Stimmen zuerst, dann alphabetisch). Veto-Restaurants
 * sind ausgeschlossen, der Sieger kann optional per excludeRestaurantId
 * herausgefiltert werden - uebrig bleiben dann Platz 2, 3, ...
 */
export function computeRanking(
  db: Database.Database,
  sessionId: number,
  excludeRestaurantId?: number,
): RankingEntry[] {
  const exclude = typeof excludeRestaurantId === 'number' ? excludeRestaurantId : null;

  return db.prepare(`
    SELECT
      r.id AS restaurant_id,
      r.name,
      r.address,
      r.postal_code,
      r.suburb,
      r.cuisine,
      r.lat,
      r.lon,
      r.osm_image_url,
      r.admin_image_url,
      r.fallback_image_url,
      sp.image_url AS sponsor_image_url,
      sp.lieferando_url,
      s.yes_count,
      s.no_count
    FROM (
      SELECT
        restaurant_id,
        SUM(CASE WHEN direction = 'yes' THEN 1 ELSE 0 END) AS yes_count,
        SUM(CASE WHEN direction = 'no' THEN 1 ELSE 0 END) AS no_count
      FROM swipes
      WHERE session_id = ?
      GROUP BY restaurant_id
    ) s
    JOIN restaurants r ON r.id = s.restaurant_id
    LEFT JOIN sponsored_restaurants sp ON sp.restaurant_id = r.id
    WHERE s.yes_count > 0
      AND (? IS NULL OR s.restaurant_id != ?)
      AND s.restaurant_id NOT IN (SELECT restaurant_id FROM vetos WHERE session_id = ?)
    ORDER BY s.yes_count DESC, s.no_count ASC, r.name ASC
  `).all(sessionId, exclude, exclude, sessionId) as RankingEntry[];
}