
-- ============================================================
-- YUMDER SQLite Schema
-- Synchronisiert am 2026-07-24 mit dem tatsaechlichen Live-Stand der
-- Produktions-DB (per PRAGMA table_info abgeglichen). Vorherige Versionen
-- dieser Datei fehlten mehrere Spalten/Tabellen, die per direktem SQL live
-- ergaenzt wurden, aber nie hier nachgezogen wurden - das hier ist jetzt die
-- verbindliche Referenz fuer Neuinstallationen.
-- ============================================================

PRAGMA foreign_keys = ON;

-- Sessions: eine Gruppen-Session mit Ort + globalen Vorfiltern
CREATE TABLE IF NOT EXISTS sessions (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    code            TEXT UNIQUE NOT NULL,       -- kurzer Share-Code, z.B. "AB12CD"
    city            TEXT NOT NULL,
    postal_code     TEXT,
    lat             REAL,
    lon             REAL,
    radius_km       REAL NOT NULL DEFAULT 3,
    diet_filter     TEXT,                       -- z.B. 'vegan','vegetarian','halal','gluten_free'
    cuisine_tags    TEXT,                       -- JSON-Array als Text, OSM-cuisine values
    price_min       INTEGER DEFAULT 1,          -- price_rating 1-4
    price_max       INTEGER DEFAULT 4,
    exclude_fast_food INTEGER NOT NULL DEFAULT 0, -- Boolean 0/1
    exclude_chains  INTEGER NOT NULL DEFAULT 0,   -- Boolean 0/1, nutzt chain_blacklist
    open_now_only   INTEGER NOT NULL DEFAULT 0,   -- Boolean 0/1, filtert per opening_hours live
    expand_level    INTEGER NOT NULL DEFAULT 0,   -- 0-2: "Weitere Karten anfordern"-Stufe (session-weit synchron)
    status          TEXT NOT NULL DEFAULT 'active', -- active | finished | expired
    created_by_participant_id INTEGER,          -- Session-Ersteller (FK später gesetzt)
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    expires_at      TEXT NOT NULL DEFAULT (datetime('now', '+48 hours')),
    last_activity_at TEXT NOT NULL DEFAULT (datetime('now')) -- Heartbeat aus /poll
);

CREATE INDEX IF NOT EXISTS idx_sessions_code ON sessions(code);
CREATE INDEX IF NOT EXISTS idx_sessions_expires ON sessions(expires_at);
CREATE INDEX IF NOT EXISTS idx_sessions_created ON sessions(created_at);

-- Teilnehmer: anonyme User pro Session (LocalStorage-ID)
CREATE TABLE IF NOT EXISTS participants (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    anonymous_id    TEXT NOT NULL,              -- UUID aus LocalStorage des Clients
    display_name    TEXT,
    is_creator       INTEGER NOT NULL DEFAULT 0, -- Boolean: darf Joker ziehen
    vetos_used       INTEGER NOT NULL DEFAULT 0, -- max 3 pro Session
    is_active        INTEGER NOT NULL DEFAULT 1, -- für 2/3-Mehrheitsberechnung
    joined_at        TEXT NOT NULL DEFAULT (datetime('now')),
    last_seen_at     TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(session_id, anonymous_id)
);

CREATE INDEX IF NOT EXISTS idx_participants_session ON participants(session_id);

-- Restaurants: Cache aus OSM/Overpass + Fallback-Bild-Infos + strikte Diaet-Tags
CREATE TABLE IF NOT EXISTS restaurants (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    osm_id               TEXT UNIQUE,            -- z.B. "node/123456" oder NULL bei rein manuellen Sponsoren-Einträgen
    name                 TEXT NOT NULL,
    lat                  REAL,
    lon                  REAL,
    address              TEXT,
    postal_code          TEXT,                   -- PLZ, separat fuer Adressanzeige
    suburb               TEXT,                   -- Stadtteil, z.B. 'Weststadt'
    cuisine              TEXT,                   -- OSM cuisine tag(s), komma-getrennt
    price_rating         INTEGER,                -- 1-4, aus OSM price:range oder geschätzt
    is_fast_food         INTEGER NOT NULL DEFAULT 0,
    phone                TEXT,
    website              TEXT,
    opening_hours_raw    TEXT,                    -- roher OSM opening_hours-String
    osm_image_url        TEXT,                    -- Stufe 1: OSM/Wikipedia/Wikidata
    admin_image_url      TEXT,                    -- Stufe 2: manuell vom Admin hinterlegt
    fallback_image_url   TEXT,                    -- Stufe 3: Unsplash/Pexels-Pool nach cuisine-Tag
    image_source_used    TEXT,                    -- 'osm' | 'admin' | 'fallback' | 'none' (für "Symbolbild"-Label)
    diet_vegan           TEXT,                    -- roher OSM diet:vegan-Tag ('yes'|'only'|'no'|NULL)
    diet_vegetarian      TEXT,
    diet_halal           TEXT,
    diet_gluten_free     TEXT,
    cached_at            TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at           TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_restaurants_geo ON restaurants(lat, lon);
CREATE INDEX IF NOT EXISTS idx_restaurants_osm ON restaurants(osm_id);

-- Lokaler Bilder-Pool je Kategorie (z.B. 'pizza','sushi','restaurant'), befuellt
-- ueber lib/image-resolver.ts + lib/image-cache.ts. Bilder werden dabei lokal
-- unter public/img/pexels-cache/ heruntergeladen und hier per Pfad referenziert,
-- statt direkt auf externe Pexels-URLs zu verweisen (Unabhaengigkeit von
-- Pexels-Rate-Limits/Downtime).
CREATE TABLE IF NOT EXISTS image_pool (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    category    TEXT NOT NULL,
    image_url   TEXT NOT NULL,                   -- lokaler Pfad, z.B. /img/pexels-cache/pizza/abc123.jpg
    created_at  TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(category, image_url)
);

CREATE INDEX IF NOT EXISTS idx_image_pool_category ON image_pool(category);

-- Swipes: persönliche Entscheidungen je Teilnehmer und Restaurant innerhalb einer Session
CREATE TABLE IF NOT EXISTS swipes (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    participant_id  INTEGER NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
    restaurant_id   INTEGER NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
    direction       TEXT NOT NULL CHECK(direction IN ('yes','no')), -- Veto läuft separat über vetos-Tabelle
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(session_id, participant_id, restaurant_id)
);

CREATE INDEX IF NOT EXISTS idx_swipes_session_restaurant ON swipes(session_id, restaurant_id);
CREATE INDEX IF NOT EXISTS idx_swipes_created ON swipes(created_at);

-- Vetos: globale, unwiderrufliche Kick-Events (max 3 pro Teilnehmer und Session)
CREATE TABLE IF NOT EXISTS vetos (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    participant_id  INTEGER NOT NULL REFERENCES participants(id) ON DELETE CASCADE,
    restaurant_id   INTEGER NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(session_id, restaurant_id) -- ein Veto pro Restaurant reicht, um es global zu kicken
);

-- Matches: berechnete Treffer inkl. Zustimmungsprozent
CREATE TABLE IF NOT EXISTS matches (
    id                      INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id              INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    restaurant_id           INTEGER NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
    yes_count               INTEGER NOT NULL,
    active_participant_count INTEGER NOT NULL,
    match_percentage        REAL NOT NULL,       -- yes_count / active_participant_count * 100
    is_joker_result          INTEGER NOT NULL DEFAULT 0,
    pool_count               INTEGER,            -- bei Joker: Groesse des Auswahl-Pools ("aus X unentschiedenen")
    created_at               TEXT NOT NULL DEFAULT (datetime('now')),
    UNIQUE(session_id, restaurant_id)
);

CREATE INDEX IF NOT EXISTS idx_matches_created ON matches(created_at);

-- Schicksals-Joker-Ziehungen (Protokoll, gewichtete Zufallsauswahl unter Ja-Restaurants)
CREATE TABLE IF NOT EXISTS joker_draws (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    restaurant_id   INTEGER NOT NULL REFERENCES restaurants(id) ON DELETE CASCADE,
    drawn_at        TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Gesponserte Restaurants: Admin-verwaltete Einträge (dauerhaft, kein 48h-Cleanup)
CREATE TABLE IF NOT EXISTS sponsored_restaurants (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    restaurant_id        INTEGER REFERENCES restaurants(id) ON DELETE SET NULL, -- Verknüpfung zu OSM-Cache, optional
    name                 TEXT NOT NULL,
    image_url            TEXT,
    address               TEXT,
    city                  TEXT,
    postal_code           TEXT,
    lat                   REAL,
    lon                   REAL,
    filter_cuisine_tags   TEXT,                   -- JSON-Array, für Umkreis-/Filter-Matching
    discount_text         TEXT,                    -- z.B. "20% auf alle Hauptgerichte"
    disclaimer_text       TEXT,                    -- z.B. Hinweis zu Bedingungen des Rabatts
    opening_hours_raw     TEXT,                    -- roher opening_hours-String, analog zu restaurants
    reservation_url       TEXT,                    -- Deeplink fuer "Anrufen/Reservieren"-Button
    lieferando_url        TEXT,                    -- Lieferando-Ref-Link fuer den Bestell-Button
    priority              INTEGER NOT NULL DEFAULT 0, -- höher = weiter oben im Stapel
    active                INTEGER NOT NULL DEFAULT 1,
    active_from           TEXT,
    active_until          TEXT,
    created_at            TEXT NOT NULL DEFAULT (datetime('now'))
);

-- In-Card-Werbung (z.B. AdSense-Format), wegswipebar
CREATE TABLE IF NOT EXISTS ads (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    title           TEXT,
    image_url       TEXT,
    link_url        TEXT,
    frequency       INTEGER NOT NULL DEFAULT 8, -- alle X Karten einblenden
    active          INTEGER NOT NULL DEFAULT 1,
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Admin-Zugänge: bcrypt-Hash + TOTP-2FA + Backup-Codes
CREATE TABLE IF NOT EXISTS admin_users (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    username             TEXT UNIQUE NOT NULL,
    password_hash        TEXT NOT NULL,          -- bcrypt
    totp_secret           TEXT,                   -- Base32-Secret für otplib, NULL solange nicht aktiviert
    totp_enabled          INTEGER NOT NULL DEFAULT 0,
    backup_codes          TEXT,                    -- JSON-Array gehashter Einmal-Codes
    last_login_at         TEXT,
    created_at            TEXT NOT NULL DEFAULT (datetime('now'))
);

-- Admin-Session-Cookies (server-seitig verwaltet, HTTP-only)
CREATE TABLE IF NOT EXISTS admin_sessions (
    id              TEXT PRIMARY KEY,             -- zufälliger Session-Token
    admin_user_id   INTEGER NOT NULL REFERENCES admin_users(id) ON DELETE CASCADE,
    created_at      TEXT NOT NULL DEFAULT (datetime('now')),
    expires_at      TEXT NOT NULL
);

-- Kettennamen-Blacklist (für price/chain-Ausschluss-Filter)
CREATE TABLE IF NOT EXISTS chain_blacklist (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    name     TEXT UNIQUE NOT NULL                -- z.B. "McDonald's", "Burger King"
);

-- Übersetzungsstrings (i18n), zusätzlich zu statischen JSON-Dateien für dynamische Admin-Texte
CREATE TABLE IF NOT EXISTS translations (
    id      INTEGER PRIMARY KEY AUTOINCREMENT,
    key      TEXT NOT NULL,
    lang     TEXT NOT NULL CHECK(lang IN ('de','en')),
    value    TEXT NOT NULL,
    UNIQUE(key, lang)
);

-- Outbound-Click-Tracking fuer den Dashboard-Funnel (Webseite/Maps/Anruf/Reservieren/Bestellen)
CREATE TABLE IF NOT EXISTS outbound_clicks (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      INTEGER NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    restaurant_id   INTEGER REFERENCES restaurants(id) ON DELETE SET NULL,
    click_type      TEXT NOT NULL CHECK(click_type IN ('maps','call','reservation','order','website')),
    created_at      TEXT NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_outbound_clicks_session ON outbound_clicks(session_id);
CREATE INDEX IF NOT EXISTS idx_outbound_clicks_created ON outbound_clicks(created_at);
CREATE INDEX IF NOT EXISTS idx_outbound_clicks_type ON outbound_clicks(click_type);

-- ============================================================
-- Analytics / Reporting Hilfstabellen
-- Diese Tabellen werden historisch auch dynamisch im Code
-- angelegt; ab hier sind sie Teil des offiziellen Schemas.
-- ============================================================

-- Zaehler fuer schnelle Lifetime-/Dashboard-Statistiken
CREATE TABLE IF NOT EXISTS app_counters (
    key   TEXT PRIMARY KEY,
    value INTEGER NOT NULL DEFAULT 0
);

CREATE TRIGGER IF NOT EXISTS trg_cnt_sessions
AFTER INSERT ON sessions
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('sessions', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_matches
AFTER INSERT ON matches
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('matches', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_swipes
AFTER INSERT ON swipes
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('swipes', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_participants
AFTER INSERT ON participants
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('participants', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_joker
AFTER INSERT ON joker_draws
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('joker_draws', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_sessions_with_match
AFTER INSERT ON matches
WHEN NOT EXISTS (
    SELECT 1 FROM matches WHERE session_id = NEW.session_id AND id != NEW.id
)
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('sessions_with_match', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_total
AFTER INSERT ON outbound_clicks
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_total', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_sessions_with_forward
AFTER INSERT ON outbound_clicks
WHEN NOT EXISTS (
    SELECT 1 FROM outbound_clicks WHERE session_id = NEW.session_id AND id != NEW.id
)
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('sessions_with_forward', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_maps
AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'maps'
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_maps', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_call
AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'call'
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_call', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_website
AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'website'
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_website', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_reservation
AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'reservation'
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_reservation', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

CREATE TRIGGER IF NOT EXISTS trg_cnt_outbound_clicks_order
AFTER INSERT ON outbound_clicks WHEN NEW.click_type = 'order'
BEGIN
    INSERT INTO app_counters(key, value) VALUES ('outbound_clicks_order', 1)
    ON CONFLICT(key) DO UPDATE SET value = value + 1;
END;

-- Pro-Restaurant Lifetime-Statistiken (ueberlebt Session-Cleanup)
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;

-- Wiederkehrer-Erkennung ueber anonyme LocalStorage-IDs
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'))
);

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;

-- Fehler-Log (Client + Server)
CREATE TABLE IF NOT EXISTS error_logs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    source TEXT NOT NULL,
    message TEXT NOT NULL,
    context TEXT,
    url TEXT,
    user_agent TEXT,
    session_code TEXT
);

CREATE INDEX IF NOT EXISTS idx_error_logs_created_at ON error_logs(created_at DESC);
