<!-- PART OF: ARCHITECTURE.md — The 20-Minute Hero Complete Architecture -->
<!-- DOCUMENT: 01-overview-and-schema.md -->
<!-- CONTENTS: Overview, AI Model Decision, Project Structure, PostgreSQL Schema -->
<!-- SPLIT: lines 1–1276 of original file -->

# The 20-Minute Hero — Complete Architecture

## Decyzja: Model AI

**Wszystkie wywołania AI → Gemini 3 Flash** (`gemini-3-flash`)

| Zadanie | Model | Powód |
|---|---|---|
| Quest generation | Gemini 3 Flash | Tanie ($0.50/1M in), szybkie, świetny JSON |
| Onboarding (rozmowa) | Gemini 3 Flash | Konwersacja nie wymaga frontier modelu |
| Photo verification (vision) | Gemini 3 Flash | Silne visual reasoning, ten sam model |

**Porównanie kosztów przy 1000 questów/dzień:**

| Model | Koszt/dzień | Koszt/miesiąc |
|---|---|---|
| Gemini 3 Flash | ~$0.19 | ~$5.70 |
| Claude Haiku 4.5 | ~$0.38 | ~$11.40 |
| GPT-5.4 / Claude Sonnet 4.6 | ~$1.14 | ~$34 |

**Kluczowe zalety Gemini 3 Flash:**
- `responseSchema` → gwarantowany Zod-validated output, zero retry logic na błędny JSON
- 1M token context window
- Na benchmarkach bije Gemini 2.5 Pro (SWE-bench 78%)
- Batch API → 50% zniżki na operacje asynchroniczne (np. weryfikacja zdjęć)

**Dependency:** `@google/genai` TypeScript SDK (`npm install @google/genai`)

---

## Project Structure

```
/srv/20hero/                          # Turborepo monorepo
├── apps/
│   ├── api/                          # Hono backend (Node.js)
│   │   ├── src/
│   │   │   ├── routes/
│   │   │   │   ├── auth.ts           # POST /firebase, /refresh, /logout, /device-token
│   │   │   │   ├── onboarding.ts     # POST /start, /{id}/message, /{id}/finalize
│   │   │   │   ├── characters.ts     # GET/PATCH character, avatars
│   │   │   │   ├── quests.ts         # GET /nearby, /active, POST /accept, /abandon
│   │   │   │   ├── proofs.ts         # POST /upload-url, /submit, GET /status
│   │   │   │   ├── voting.ts         # GET /vote-queue, POST /vote
│   │   │   │   ├── social.ts         # follow, reactions, comments, leaderboard
│   │   │   │   ├── tokens.ts         # balance, ledger, shop (MVP: disabled)
│   │   │   │   └── admin.ts          # game_config CRUD, prompt_templates CRUD
│   │   │   ├── services/
│   │   │   │   ├── auth.service.ts           # Firebase verify + jose JWT issue (Sekcja 14)
│   │   │   │   ├── character.service.ts      # create/update character
│   │   │   │   ├── quest.service.ts          # accept/abandon/complete + BullMQ scheduling
│   │   │   │   ├── quest-discovery.service.ts # find_nearby + fallback (Sekcja 5b)
│   │   │   │   ├── proof-upload.service.ts   # presigned URL + submit (Sekcja 6b)
│   │   │   │   ├── proof-verify.service.ts   # AI verify call (Sekcja 4.3)
│   │   │   │   ├── voting.service.ts         # cast_vote, resolve_vote (Sekcja 8b)
│   │   │   │   ├── xp.service.ts             # award XP + level-up logic (Sekcja 7)
│   │   │   │   ├── avatar.service.ts         # visual_anchor + image gen + S3 (Sekcja 12.6)
│   │   │   │   ├── push.service.ts           # FCM notify + rate limit (Sekcja 8c)
│   │   │   │   ├── location.service.ts       # Nominatim reverse geocoding (Sekcja 5b)
│   │   │   │   ├── weather.service.ts        # OpenWeatherMap → WeatherContext (Sekcja 16)
│   │   │   │   ├── onboarding.service.ts     # multi-turn conversation (Sekcja 4.1)
│   │   │   │   ├── social.service.ts         # follow, react, comment, leaderboard
│   │   │   │   ├── token.service.ts          # token_balances, ledger, shop
│   │   │   │   ├── s3.service.ts             # upload/download/exists helpers
│   │   │   │   ├── dedup.service.ts          # quest deduplication hash (Sekcja 4b)
│   │   │   │   ├── config.service.ts         # hot-reload game_config + prompt_templates
│   │   │   │   ├── guild.service.ts          # create/join/leave guild, GWT progress (§11.6)
│   │   │   │   ├── season.service.ts         # current season, pass activation, reward claiming (§11.7)
│   │   │   │   ├── challenge.service.ts      # weekly challenge assignment + progress (§11.8)
│   │   │   │   ├── ufs.service.ts            # updateFraudScore, resolveAction, admin queue (§34)
│   │   │   │   └── iap.service.ts            # receipt validation (Apple/Google), token grant (§10)
│   │   │   ├── jobs/                         # BullMQ workers (Sekcja 8)
│   │   │   │   ├── proof.worker.ts           # verify_proof
│   │   │   │   ├── avatar.worker.ts          # generate_visual_anchor, generate_avatar_tier
│   │   │   │   ├── notification.worker.ts    # send_quest_warning, daily_reminders, streak_warnings
│   │   │   │   ├── maintenance.worker.ts     # close_expired_quests/votes, expire_tokens
│   │   │   │   ├── season.worker.ts          # CRON: activate/close seasons, distribute season rewards
│   │   │   │   ├── challenge.worker.ts       # CRON Mon 06:00 UTC: assign weekly challenges, close prev week
│   │   │   │   └── deletion.worker.ts        # CRON daily: process deletion_requests after 30d grace period
│   │   │   ├── middleware/
│   │   │   │   ├── auth.ts                   # requireAuth, requireAdmin middleware
│   │   │   │   └── rate-limit.ts             # per-user rate limiting via ioredis
│   │   │   ├── ai/
│   │   │   │   └── gemini.ts                 # @google/genai client + prompt helpers
│   │   │   ├── config.ts                     # Zod env schema + config singleton
│   │   │   └── index.ts                      # Hono app, middleware, routes, lifespan
│   │   ├── package.json
│   │   └── tsconfig.json
│   └── mobile/                       # Expo (React Native) — aplikacja mobilna
│       ├── app/
│       │   ├── (tabs)/
│       │   │   ├── index.tsx         # Home/dashboard
│       │   │   ├── quests.tsx        # Quest list
│       │   │   └── profile.tsx       # Character profile
│       │   └── onboarding/
│       ├── components/
│       ├── lib/
│       │   ├── api.ts                # ky HTTP client + Firebase auth hooks
│       │   └── store.ts              # Zustand global state
│       ├── package.json
│       └── app.json
├── packages/
│   ├── db/                           # Drizzle ORM schema + migrations
│   │   ├── src/
│   │   │   ├── schema.ts             # wszystkie tabele jako Drizzle table definitions
│   │   │   ├── index.ts              # db instance (postgres-js + drizzle)
│   │   │   └── migrate.ts            # migration runner (drizzle-kit migrate)
│   │   ├── migrations/               # auto-generowane SQL przez drizzle-kit generate
│   │   ├── seeds/
│   │   │   ├── game_config.ts        # INSERT do game_config
│   │   │   └── prompt_templates.ts   # INSERT do prompt_templates
│   │   ├── drizzle.config.ts
│   │   └── package.json
│   ├── types/                        # Zod schemas + TypeScript types (Sekcja 3)
│   │   ├── src/
│   │   │   ├── auth.ts
│   │   │   ├── character.ts
│   │   │   ├── quest.ts
│   │   │   ├── proof.ts
│   │   │   └── social.ts
│   │   └── package.json
│   └── utils/                        # Shared utilities
│       ├── src/
│       │   ├── xp.ts                 # levelForXp, xpToNext, xpProgressPct
│       │   └── geo.ts                # geohash, haversine distance helpers
│       └── package.json
├── turbo.json
├── pnpm-workspace.yaml
├── docker-compose.yml
├── .env.example
└── ARCHITECTURE.md
```

---

## 1. PostgreSQL Schema

### Complete DDL

```sql
-- ============================================================
-- USERS & AUTH
-- ============================================================

CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "postgis";  -- for GPS point queries

-- Auth: Firebase-only (Google + Apple Sign-In) od dnia 1
-- Brak email/password — eliminuje brute-force, credential stuffing, reset-password flows
-- Backend weryfikuje id_token offline (JWKS cache), wydaje własną parę JWT (access 15min + refresh 30d)
CREATE TABLE users (
    id                       UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    firebase_uid             TEXT NOT NULL UNIQUE,       -- Google/Apple UID z Firebase
    email                    TEXT,                       -- z Firebase profile (może być NULL = Apple Hide Email)
    username                 TEXT UNIQUE,                -- wybierany przy pierwszym logowaniu
    display_name             TEXT,                       -- z Firebase profile
    photo_url                TEXT,                       -- z Firebase profile
    is_active                BOOLEAN NOT NULL DEFAULT TRUE,
    is_admin                 BOOLEAN NOT NULL DEFAULT FALSE,
    auth_provider            TEXT NOT NULL DEFAULT 'firebase',  -- 'firebase' | 'google' | 'apple'
    timezone                 TEXT DEFAULT 'Europe/Warsaw',
    -- device_tokens USUNIĘTE — patrz tabela user_devices (relacja 1:N)
    notification_prefs       JSONB NOT NULL DEFAULT '{}'::JSONB,  -- {quest_reminder: true, ...}
    reviewer_accuracy        FLOAT NOT NULL DEFAULT 0.5,  -- 0.0-1.0; aktualizowane po każdym głosowaniu; głosy od accuracy>0.7 mają wagę 2x
    is_showcase              BOOLEAN NOT NULL DEFAULT FALSE,  -- team account na cold start seed (§19.6); ukryty w normalnym feedzie
    last_location            GEOGRAPHY(POINT, 4326),              -- PostGIS — ostatnia znana lokalizacja
    last_location_updated_at TIMESTAMPTZ,
    created_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at               TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_login_at            TIMESTAMPTZ,
    deleted_at               TIMESTAMPTZ                          -- NULL = aktywny (soft delete)
);

CREATE INDEX idx_users_firebase ON users(firebase_uid);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- ============================================================
-- USER DEVICES — push tokens (zastępuje device_tokens JSONB)
-- ============================================================

-- Relacja 1:N user → urządzenia. Oddzielna tabela zamiast JSONB array.
-- Powody: per-device lifecycle (platform, app_version, last_seen), bezpieczna deduplication,
-- łatwa revocation przy wylogowaniu, brak race condition przy multi-device login.
CREATE TABLE user_devices (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id      UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    fcm_token    TEXT NOT NULL,
    platform     TEXT NOT NULL CHECK (platform IN ('android', 'ios')),
    app_version  TEXT NOT NULL,
    device_model TEXT,                   -- np. "iPhone 15 Pro", "Pixel 8"
    last_seen_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    revoked_at   TIMESTAMPTZ             -- NULL = aktywny; ustawiane przy wylogowaniu
);

CREATE UNIQUE INDEX idx_user_devices_token ON user_devices(fcm_token) WHERE revoked_at IS NULL;
CREATE INDEX idx_user_devices_user ON user_devices(user_id) WHERE revoked_at IS NULL;

-- ============================================================
-- CHARACTERS & CLASSES
-- ============================================================

-- Uniwersum: Cyber-Fantasy "Neon & Mana"
-- Starożytna magia zlała się z technologią. Zaklęcia to kod. Runy świecą neonem.
CREATE TYPE character_class AS ENUM (
    'techno_mag',           -- Techno-Mag: widzi dane jak strumienie many, optymalizuje rzeczywistość
    'chrom_paladin',        -- Chrom-Paladyn: cybernetyczny rycerz, egzekutor cyfrowej sprawiedliwości
    'widmo_biegacz',        -- Widmo-Biegacz: implanty neuralne + parkour, przechodzi miasto jak duch
    'bio_szaman',           -- Bio-Szaman: hakuje ekosystemy przez biotech, rozmawia z naturą
    'inzynier_spoleczny',   -- Inżynier Społeczny: charyzma + subtelny tech = kontrola narracji
    'kurier_cieni',         -- Kurier Cieni: cloaking-ware i magia cienia, dostarcza tajemnice
    'architekt_danych',     -- Architekt Danych: buduje mosty między starą magią a nowym kodem
    'koderyta'              -- Koderyta: stare rytuały jako executable code, debug-uje rzeczywistość
);

CREATE TABLE characters (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,
    class_key       character_class NOT NULL,   -- renamed from 'class' (reserved word in most languages)
    title           TEXT NOT NULL,          -- e.g. "Mag Logistyki II Stopnia"
    backstory       TEXT NOT NULL,          -- AI-generated backstory
    level           INT NOT NULL DEFAULT 1,
    xp_total        BIGINT NOT NULL DEFAULT 0,
    xp_to_next      INT NOT NULL DEFAULT 500,

    -- Core attributes (1–100 scale)
    attr_sila           INT NOT NULL DEFAULT 10,  -- Siła / Wytrzymałość
    attr_intelekt       INT NOT NULL DEFAULT 10,  -- Intelekt
    attr_charyzma       INT NOT NULL DEFAULT 10,  -- Charyzma
    attr_zrecznosc      INT NOT NULL DEFAULT 10,  -- Zręczność
    attr_percepcja      INT NOT NULL DEFAULT 10,  -- Percepcja

    -- Equipment (symbolic, affects quest flavor)
    equipment           JSONB NOT NULL DEFAULT '{}'::JSONB,
    -- e.g. {"weapon": "Laptop z crackami", "armor": "Peleryna z plecakiem", "artifact": "Notes z piórem"}

    -- Personality traits used for quest generation
    personality_traits  TEXT[] NOT NULL DEFAULT '{}',
    -- e.g. ["analityczny", "ciekawy", "metodyczny"]

    -- Preferencje questów (zbierane w onboardingu i aktualizowane z czasem)
    preferred_proof_types TEXT[] NOT NULL DEFAULT '{"photo","video","audio","text","multi_photo"}',
    -- Typy proofu które użytkownik preferuje; AI uwzględnia przy generowaniu
    excluded_categories TEXT[] NOT NULL DEFAULT '{}',
    -- Kategorie których NIE chce: ["social", "group", "high_budget", "night_quests"]
    privacy_level   TEXT NOT NULL DEFAULT 'public',
    -- Domyślna widoczność proofów: 'private' | 'friends' | 'public'

    onboarding_transcript JSONB,            -- Full AI conversation log

    -- Stałe cechy wizualne dla spójności avatarów między tierami
    -- Generowane raz (text call) przy tworzeniu Tier 1
    -- {distinctive_features: [...], silhouette: "..."}
    visual_anchor       JSONB,

    is_active           BOOLEAN NOT NULL DEFAULT TRUE,

    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_characters_user_id ON characters(user_id);
CREATE INDEX idx_characters_class ON characters(class_key);
CREATE INDEX idx_characters_level ON characters(level DESC);

-- Character unlock milestones
CREATE TABLE character_milestones (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id    UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    milestone_key   TEXT NOT NULL,          -- e.g. "first_quest", "level_5", "100_xp"
    unlocked_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    metadata        JSONB NOT NULL DEFAULT '{}'::JSONB
);

CREATE UNIQUE INDEX idx_milestones_char_key ON character_milestones(character_id, milestone_key);

-- ============================================================
-- ONBOARDING SESSIONS
-- ============================================================

CREATE TYPE onboarding_status AS ENUM ('in_progress', 'completed', 'abandoned');

CREATE TABLE onboarding_sessions (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status          onboarding_status NOT NULL DEFAULT 'in_progress',

    -- Conversation state
    messages        JSONB NOT NULL DEFAULT '[]'::JSONB,
    -- [{role: "assistant"|"user", content: "...", ts: "iso"}]
    turn_count      INT NOT NULL DEFAULT 0,          -- incremented each user reply

    -- AI-derived profile (built incrementally)
    derived_profile JSONB NOT NULL DEFAULT '{}'::JSONB,
    -- {traits: [...], preferred_activities: [...], location_type: "urban"|"suburban"|"nature"}

    -- Final result
    assigned_class  character_class,
    character_id    UUID REFERENCES characters(id),

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at    TIMESTAMPTZ
);

CREATE INDEX idx_onboarding_user ON onboarding_sessions(user_id);

-- ============================================================
-- QUESTS
-- ============================================================

CREATE TYPE quest_status AS ENUM (
    'available',
    'accepted',
    'in_progress',
    'proof_submitted',
    'completed',
    'failed',
    'expired'
);

CREATE TYPE quest_difficulty AS ENUM ('easy', 'medium', 'hard', 'legendary');

CREATE TYPE location_type AS ENUM (
    'urban_street',
    'park',
    'shopping_area',
    'transit_hub',
    'residential',
    'waterfront',
    'forest',
    'suburban',
    'industrial',
    'historic_district'
);

CREATE TABLE quests (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id    UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    -- Quest content
    title           TEXT NOT NULL,
    description     TEXT NOT NULL,          -- Narrative quest description (RPG flavor)
    objective       TEXT NOT NULL,          -- Clear, actionable task statement
    completion_hint TEXT NOT NULL,          -- What proof to submit
    lore_blurb      TEXT,                   -- Optional world-building flavor text

    -- Wymagania i parametry questa (pokazywane przed akceptacją)
    energy_required TEXT NOT NULL DEFAULT 'medium',
    -- 'low' (można zmęczonym) | 'medium' (normalna energia) | 'high' (potrzeba energii)
    needed_items    TEXT[],
    -- Np. ['telefon', 'notes', 'coś do pisania'] albo NULL gdy nic nie potrzeba
    companion_mode  TEXT NOT NULL DEFAULT 'solo',
    -- 'solo' | 'duo' | 'group' | 'any' — z kim można wykonać
    budget_level    TEXT NOT NULL DEFAULT 'free',
    -- 'free' | 'low' (do 10zł) | 'medium' (do 50zł)
    allowed_proof_types TEXT[] NOT NULL DEFAULT '{"photo","video","audio","text","multi_photo"}',
    -- Które typy proofu AI-akceptuje dla tego questa; AI sugeruje, user wybiera

    -- Context at generation time
    difficulty      quest_difficulty NOT NULL DEFAULT 'easy',
    estimated_minutes INT NOT NULL DEFAULT 20,
    location_type   location_type,
    weather_at_gen  JSONB,
    -- {"condition": "rain", "temp_c": 12, "wind_kmh": 20, "description": "light rain"}
    lat             DOUBLE PRECISION,
    lng             DOUBLE PRECISION,
    geohash         TEXT,                   -- Geohash for spatial bucketing (precision 6)
    place_name      TEXT,                   -- Human-readable reverse-geocoded location

    -- Generation metadata
    ai_model        TEXT NOT NULL DEFAULT 'gemini-3-flash',
    generation_prompt_hash TEXT,            -- SHA256 of prompt sent to AI (for auditing)
    generation_context JSONB,              -- Full context passed to AI

    -- XP rewards
    xp_base         INT NOT NULL DEFAULT 100,
    xp_bonus_max    INT NOT NULL DEFAULT 50,  -- Max bonus from community votes

    -- Timing
    status          quest_status NOT NULL DEFAULT 'available',
    accepted_at     TIMESTAMPTZ,
    expires_at      TIMESTAMPTZ,            -- accepted_at + 20 min + buffer
    submitted_at    TIMESTAMPTZ,
    completed_at    TIMESTAMPTZ,

    -- Proof reference
    proof_id        UUID,                   -- Set after submission

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_quests_character_id ON quests(character_id);
CREATE INDEX idx_quests_user_id ON quests(user_id);
CREATE INDEX idx_quests_status ON quests(status);
CREATE INDEX idx_quests_geohash ON quests(geohash);
CREATE INDEX idx_quests_created_at ON quests(created_at DESC);
CREATE INDEX idx_quests_expires_at ON quests(expires_at) WHERE status IN ('accepted', 'in_progress');

-- ============================================================
-- QUEST DEDUPLICATION — historia questów per user
-- ============================================================

-- Śledzi które quest_templates user już widział — używane przez find_nearby()
-- żeby nie pokazywać tych samych szablonów ponownie przez 7 dni
-- UWAGA: kolumna template_id (nie quest_id) żeby uniknąć mylenia z quests.id
CREATE TABLE quest_seen (
    user_id     UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    template_id UUID        NOT NULL REFERENCES quest_templates(id) ON DELETE CASCADE,
    seen_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, template_id)
);
CREATE INDEX idx_quest_seen_user ON quest_seen(user_id, seen_at DESC);

-- Szczegółowa historia (do debugowania i analityki)
CREATE TABLE quest_history (
    id             UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id        UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    quest_id       UUID        NOT NULL REFERENCES quests(id),
    quest_type_hash TEXT       NOT NULL,
    location_type  TEXT,
    geohash_4      TEXT,        -- pierwsze 4 znaki geohash (~20km)
    seen_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_quest_history_user ON quest_history(user_id, seen_at DESC);

-- ============================================================
-- CHARACTER AVATARS — AI-generated, ewoluują z levelem
-- ============================================================

CREATE TABLE character_avatars (
    id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id     UUID        NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    level_tier       INT         NOT NULL CHECK (level_tier BETWEEN 1 AND 20),
    -- 20 tierów — non-linear level mapping:
    --  T1=L1, T2=L2, T3=L3, T4=L4, T5=L5, T6=L7, T7=L9, T8=L11, T9=L13, T10=L15,
    --  T11=L18, T12=L22, T13=L27, T14=L33, T15=L40, T16=L50, T17=L65, T18=L80, T19=L100, T20=L120+
    image_url        TEXT,                    -- S3: avatars/{character_id}/tier_{tier}.png (512x512)
    thumbnail_url    TEXT,                    -- S3: avatars/{character_id}/tier_{tier}_thumb.png (128x128)
    is_current       BOOLEAN     NOT NULL DEFAULT TRUE,
    status           TEXT        NOT NULL DEFAULT 'pending',  -- pending|generating|ready|failed
    generation_model TEXT        NOT NULL DEFAULT 'gemini-3-pro-image-preview',
    -- Model per-tier z game_config("avatar_models"):
    --   Tier 1: pro (pierwsze wrażenie)
    --   Tier 2-4: flash (tanie, early-game)
    --   Tier 5: pro (pierwszy milestone)
    --   Tier 6-15: flash (mid-game, 2.5x tańsze)
    --   Tier 16-20: pro (legendary — najwyższa jakość na końcowe tiery)
    prompt_used      TEXT,                    -- Zachowany dla reproducibility i debugowania
    generated_at     TIMESTAMPTZ,
    UNIQUE (character_id, level_tier)
);
CREATE INDEX idx_avatars_character_current ON character_avatars(character_id) WHERE is_current = TRUE;

-- ============================================================
-- PROOF SUBMISSIONS
-- ============================================================

CREATE TYPE proof_status AS ENUM (
    'pending',           -- BullMQ job not yet picked up
    'ai_verifying',      -- BullMQ verify_proof worker running
    'ai_verified',       -- PASS, xp awarded, sent to community voting
    'ai_rejected',       -- FAIL, xp NOT awarded, attempt marked failed
    'community_voting',  -- In community vote window (24-72h per game_config)
    'accepted',          -- Community vote PASS, bonus_xp awarded
    'rejected'           -- Community vote FAIL
    -- TypeScript: ProofStatus.PENDING / .AI_VERIFYING / .AI_VERIFIED /
    --             .AI_REJECTED / .COMMUNITY_VOTING / .ACCEPTED / .REJECTED
    -- Usage:  proof.status === 'ai_verified'  ← use string literal or PROOF_STATUSES const
);

-- Wszystkie typy mediów — wyłączamy przez game_config("proofs.enabled_types"), nie schema
CREATE TYPE media_type AS ENUM (
    'photo',        -- Pojedyncze zdjęcie
    'multi_photo',  -- Zestaw 2–3 zdjęć (referencje w proof_media)
    'video',        -- Krótki film (max 60s)
    'audio',        -- Nagranie audio (max 120s)
    'text'          -- Notatka tekstowa (bez pliku S3)
);

-- Widoczność proofu: 3 poziomy (jak Instagram/Facebook)
CREATE TYPE proof_visibility AS ENUM (
    'private',      -- Tylko dla autora
    'friends',      -- Dla obserwujących (follows)
    'public'        -- Dla wszystkich
);

CREATE TABLE proofs (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    quest_id        UUID NOT NULL REFERENCES quests(id) ON DELETE CASCADE,
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    character_id    UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,

    -- Widoczność
    visibility      proof_visibility NOT NULL DEFAULT 'public',

    -- Główne media (NULL dla multi_photo i text)
    media_type      media_type NOT NULL,
    s3_key          TEXT,                   -- NULL gdy media_type = 'text'
    s3_bucket       TEXT,
    cdn_url         TEXT,                   -- Public CDN URL (after AI approval)
    thumbnail_url   TEXT,
    file_size_bytes BIGINT,
    duration_seconds FLOAT,                 -- Dla video/audio

    -- Treść tekstowa (dla text proofów i opcjonalny caption dla mediów)
    user_text       TEXT,                   -- Wymagany gdy media_type = 'text'
    user_caption    TEXT,                   -- Opcjonalny opis dla photo/video/audio

    -- EXIF / metadata
    exif_data       JSONB,
    -- {lat, lng, captured_at, device_make, device_model}
    exif_location_match BOOLEAN,
    exif_time_match     BOOLEAN,

    -- AI weryfikacja questa (czy quest wykonany?)
    status          proof_status NOT NULL DEFAULT 'pending',
    ai_verdict      TEXT,                   -- 'pass' | 'fail' | 'uncertain'
    ai_confidence   FLOAT,
    ai_reasoning    TEXT,
    ai_tags         TEXT[],
    ai_verified_at  TIMESTAMPTZ,

    -- AI feedback dla użytkownika po sukcesie (pozytywny, krótki)
    -- Osobny call Gemini po akceptacji: docenia wysiłek, zauważa motyw twórczy
    ai_completion_feedback TEXT,

    -- Content moderation (prywatność i bezpieczeństwo)
    -- Wykrywane przez AI: twarze dzieci, dokumenty, tablice rej., dane osobowe
    moderation_flags JSONB NOT NULL DEFAULT '{}'::JSONB,
    -- {"children_faces": false, "documents": false, "license_plates": false,
    --  "personal_data_on_screen": false, "nudity": false, "violence": false,
    --  "address_visible": false, "safe": true}
    moderation_checked_at TIMESTAMPTZ,

    -- Community voting
    vote_count      INT NOT NULL DEFAULT 0,
    vote_approve    INT NOT NULL DEFAULT 0,
    vote_reject     INT NOT NULL DEFAULT 0,
    voting_closes_at TIMESTAMPTZ,
    final_vote_ratio FLOAT,
    rejection_reason TEXT,

    submitted_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    resolved_at     TIMESTAMPTZ
);

CREATE INDEX idx_proofs_quest_id ON proofs(quest_id);
CREATE INDEX idx_proofs_user_id ON proofs(user_id);
CREATE INDEX idx_proofs_status ON proofs(status);
CREATE INDEX idx_proofs_visibility ON proofs(visibility);
CREATE INDEX idx_proofs_voting_closes ON proofs(voting_closes_at) WHERE status = 'community_voting';

-- Dodatkowe media dla multi_photo (referencja 1:many do proofs)
CREATE TABLE proof_media (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    proof_id        UUID NOT NULL REFERENCES proofs(id) ON DELETE CASCADE,
    s3_key          TEXT NOT NULL,
    cdn_url         TEXT,
    thumbnail_url   TEXT,
    file_size_bytes BIGINT,
    sort_order      SMALLINT NOT NULL DEFAULT 0,   -- Kolejność wyświetlania
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_proof_media_proof ON proof_media(proof_id, sort_order);

-- ============================================================
-- PROOF WORKFLOW STEPS — explicit state machine dla weryfikacji
-- ============================================================

-- Zastępuje niejasny 7-state status ENUM w proofs jako jedyne źródło prawdy.
-- Każdy krok weryfikacji ma własny wiersz z wejściem/wyjściem.
-- Idempotency: jeśli worker crashuje i retry — sprawdza czy step już 'done' → skip.
-- Umożliwia debugowanie i audit każdego kroku osobno.
CREATE TABLE proof_workflow_steps (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    proof_id     UUID NOT NULL REFERENCES proofs(id) ON DELETE CASCADE,

    step         TEXT NOT NULL,
    -- Dozwolone wartości (kolejność przepływu):
    --   'upload_confirmed'   → S3 object istnieje, rozmiar OK
    --   'exif_extracted'     → GPS i timestamp wyciągnięte z EXIF
    --   'hash_checked'       → perceptual hash obliczony, brak duplikatu
    --   'policy_scored'      → policy engine obliczył score (wszystkie sygnały)
    --   'ai_verified'        → Gemini vision call zakończony
    --   'vote_opened'        → utworzono queue voting z przypisanymi reviewerami
    --   'vote_closed'        → głosowanie zakończone, wynik zagregowany
    --   'xp_awarded'         → XP naliczone do ledgera (finalny krok)

    status       TEXT NOT NULL DEFAULT 'pending'
                 CHECK (status IN ('pending', 'running', 'done', 'failed', 'skipped')),

    input_data   JSONB,          -- dane wejściowe tego kroku
    output_data  JSONB,          -- wynik kroku (verdict, score, confidence, etc.)
    error        TEXT,           -- opis błędu jeśli status='failed'

    started_at   TIMESTAMPTZ,
    finished_at  TIMESTAMPTZ,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_pwf_proof_step ON proof_workflow_steps(proof_id, step);
CREATE INDEX idx_pwf_status ON proof_workflow_steps(status) WHERE status IN ('pending', 'running');

-- ============================================================
-- PROOF HASHES — deduplication mediów
-- ============================================================

-- Perceptual hash (dhash/phash) każdego przesłanego media (photo, multi_photo).
-- Porównywane przez Hamming distance < 10 → podejrzenie duplikatu/replay attack.
-- Osobna tabela (nie kolumna na proofs) żeby umożliwić cross-user comparison.
CREATE TABLE proof_hashes (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    proof_id     UUID NOT NULL REFERENCES proofs(id) ON DELETE CASCADE,
    user_id      UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    hash_bits    BIT(64) NOT NULL,     -- 64-bit perceptual hash (dhash)
    s3_key       TEXT NOT NULL,        -- którego pliku dotyczy (dla multi_photo: per plik)
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_proof_hashes_user ON proof_hashes(user_id, created_at DESC);
-- Uwaga: porównanie Hamming distance wymaga własnej funkcji SQL lub pg_trgm extension;
-- alternatywa: hash jako BYTEA + porównanie w aplikacji

-- ============================================================
-- VOTES
-- ============================================================

CREATE TABLE votes (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    proof_id        UUID NOT NULL REFERENCES proofs(id) ON DELETE CASCADE,
    voter_user_id   UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    voter_character_id UUID REFERENCES characters(id),

    vote            SMALLINT NOT NULL CHECK (vote IN (-1, 1)),  -- -1 reject, +1 approve
    comment         TEXT,
    voted_at        TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_votes_proof_voter ON votes(proof_id, voter_user_id);
CREATE INDEX idx_votes_proof_id ON votes(proof_id);
CREATE INDEX idx_votes_voter ON votes(voter_user_id);

-- ============================================================
-- XP LEDGER
-- ============================================================

CREATE TYPE xp_event_type AS ENUM (
    'quest_completed',
    'community_vote_received',
    'vote_cast',
    'daily_login',
    'streak_bonus',
    'level_up_bonus',
    'group_challenge',
    'admin_grant',
    'penalty'
);

CREATE TABLE xp_ledger (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    character_id     UUID NOT NULL REFERENCES characters(id) ON DELETE CASCADE,
    user_id          UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,

    event_type       xp_event_type NOT NULL,
    amount           INT NOT NULL,           -- Can be negative (penalty)
    balance_after    BIGINT NOT NULL,        -- character.xp_total after this event

    -- Idempotency — zapobiega podwójnemu naliczeniu przy retry/crash workerów
    -- Format: "{event_type}:{source_id}:{user_id}"
    -- Przykłady: "quest_completed:uuid-questa:uuid-usera"
    --            "vote_cast:uuid-proofу:uuid-votera"
    idempotency_key  TEXT UNIQUE NOT NULL,

    -- Context
    quest_id        UUID REFERENCES quests(id),
    proof_id        UUID REFERENCES proofs(id),
    vote_id         UUID REFERENCES votes(id),
    note            TEXT,
    breakdown       JSONB NOT NULL DEFAULT '{}'::JSONB,
    -- {base: 100, vote: 30, class: 20, streakMult: 1.5, capped: 0}

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_xp_character_id ON xp_ledger(character_id);
CREATE INDEX idx_xp_event_type ON xp_ledger(event_type);
CREATE INDEX idx_xp_created_at ON xp_ledger(created_at DESC);
CREATE INDEX idx_xp_character_date ON xp_ledger(character_id, created_at DESC); -- composite for ledger history queries
-- idempotency_key ma UNIQUE constraint powyżej — pełni rolę indeksu

-- ============================================================
-- SOCIAL — GROUPS
-- ============================================================

CREATE TYPE group_visibility AS ENUM ('public', 'private', 'invite_only');

CREATE TABLE groups (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            TEXT NOT NULL,
    slug            TEXT UNIQUE NOT NULL,
    description     TEXT,
    avatar_url      TEXT,
    visibility      group_visibility NOT NULL DEFAULT 'public',
    owner_user_id   UUID NOT NULL REFERENCES users(id),

    -- Stats
    member_count    INT NOT NULL DEFAULT 1,
    total_xp        BIGINT NOT NULL DEFAULT 0,  -- Aggregate group XP
    quests_completed INT NOT NULL DEFAULT 0,

    -- Settings
    require_approval BOOLEAN NOT NULL DEFAULT FALSE,
    invite_code     TEXT UNIQUE,            -- For invite_only groups

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_groups_slug ON groups(slug);
CREATE INDEX idx_groups_visibility ON groups(visibility);
CREATE INDEX idx_groups_total_xp ON groups(total_xp DESC);

CREATE TYPE member_role AS ENUM ('member', 'moderator', 'admin');

CREATE TABLE group_members (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    group_id        UUID NOT NULL REFERENCES groups(id) ON DELETE CASCADE,
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    character_id    UUID REFERENCES characters(id),  -- Active character for this group
    role            member_role NOT NULL DEFAULT 'member',
    joined_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    last_active_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE UNIQUE INDEX idx_group_members_unique ON group_members(group_id, user_id);
CREATE INDEX idx_group_members_group ON group_members(group_id);
CREATE INDEX idx_group_members_user ON group_members(user_id);

-- ============================================================
-- QUEST PATHS (ŚCIEŻKI) — tematyczne sekwencje questów
-- Domyślnie wyłączone: game_config("paths.enabled") = false
-- ============================================================

CREATE TABLE quest_paths (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    slug            TEXT UNIQUE NOT NULL,       -- "7-dni-uwaznosci", "10-questow-miejskich"
    title           TEXT NOT NULL,              -- "7 Dni Uważności"
    description     TEXT NOT NULL,
    category        TEXT NOT NULL,              -- "mindfulness", "urban", "creativity", ...
    difficulty      quest_difficulty NOT NULL DEFAULT 'easy',
    estimated_days  INT NOT NULL DEFAULT 7,     -- Ile dni zakłada ścieżka
    quest_count     INT NOT NULL DEFAULT 7,     -- Ile questów w ścieżce

    -- Warunki odblokowania
    min_level       INT NOT NULL DEFAULT 1,
    min_quests_completed INT NOT NULL DEFAULT 0,

    -- Nagrody za ukończenie całej ścieżki
    xp_bonus        INT NOT NULL DEFAULT 500,
    badge_key       TEXT,                       -- Klucz odznaki/tytułu po ukończeniu

    is_active       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_paths_category ON quest_paths(category);

-- Kroki w ścieżce — szablony questów lub kategorie do wygenerowania
CREATE TABLE quest_path_steps (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    path_id         UUID NOT NULL REFERENCES quest_paths(id) ON DELETE CASCADE,
    step_number     INT NOT NULL,               -- 1, 2, 3 ... (kolejność)
    title           TEXT NOT NULL,              -- Tytuł kroku
    description     TEXT,                       -- Wskazówka/kontekst kroku

    -- AI generuje quest z tymi wskazówkami (nie z szablonu)
    quest_category  TEXT NOT NULL,              -- np. "mindfulness", "urban_exploration"
    quest_tags      TEXT[] NOT NULL DEFAULT '{}',
    quest_difficulty quest_difficulty NOT NULL DEFAULT 'easy',
    min_minutes     INT NOT NULL DEFAULT 10,
    max_minutes     INT NOT NULL DEFAULT 30,

    -- Opcjonalne ograniczenia proofu dla tego kroku
    allowed_proof_types TEXT[],                 -- NULL = wszystkie typy

    UNIQUE (path_id, step_number)               -- jeden krok per pozycja w ścieżce
);

-- Postęp użytkownika na ścieżce
CREATE TABLE user_path_progress (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    path_id         UUID NOT NULL REFERENCES quest_paths(id) ON DELETE CASCADE,

    status          TEXT NOT NULL DEFAULT 'active',
    -- 'active' | 'completed' | 'abandoned'

    current_step    INT NOT NULL DEFAULT 1,
    steps_completed INT NOT NULL DEFAULT 0,

    started_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    completed_at    TIMESTAMPTZ,
    last_active_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    UNIQUE (user_id, path_id)                   -- Jedna aktywna ścieżka danego type na raz
);
CREATE INDEX idx_path_progress_user ON user_path_progress(user_id, status);

-- Powiązanie questów z krokami ścieżki (które questy zostały wygenerowane dla danego kroku)
CREATE TABLE path_step_quests (
    user_path_id    UUID NOT NULL REFERENCES user_path_progress(id) ON DELETE CASCADE,
    step_number     INT NOT NULL,
    quest_id        UUID NOT NULL REFERENCES quests(id),
    completed_at    TIMESTAMPTZ,
    PRIMARY KEY (user_path_id, step_number)
);

-- ============================================================
-- ACTIVITY FEED
-- ============================================================

CREATE TYPE feed_event_type AS ENUM (
    'quest_completed',
    'level_up',
    'group_joined',
    'milestone_unlocked',
    'voted_on_proof'
);

CREATE TABLE activity_feed (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    character_id    UUID REFERENCES characters(id),
    group_id        UUID REFERENCES groups(id),

    event_type      feed_event_type NOT NULL,
    title           TEXT NOT NULL,
    body            TEXT,
    media_url       TEXT,
    metadata        JSONB NOT NULL DEFAULT '{}'::JSONB,

    -- Visibility
    is_public       BOOLEAN NOT NULL DEFAULT TRUE,
    target_group_id UUID REFERENCES groups(id),  -- NULL = global feed

    created_at      TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_feed_user ON activity_feed(user_id, created_at DESC);
CREATE INDEX idx_feed_group ON activity_feed(target_group_id, created_at DESC) WHERE target_group_id IS NOT NULL;
CREATE INDEX idx_feed_public ON activity_feed(created_at DESC) WHERE is_public = TRUE;

-- ============================================================
-- LEVEL DEFINITIONS (static config table)
-- ============================================================

CREATE TABLE level_definitions (
    level           INT PRIMARY KEY,
    xp_required     INT NOT NULL,           -- Total XP to reach this level
    title_suffix    TEXT NOT NULL,          -- e.g. "I Stopnia", "Mistrz"
    unlocks         JSONB NOT NULL DEFAULT '[]'::JSONB,
    -- e.g. [{"type": "quest_difficulty", "value": "hard"}, {"type": "equipment_slot", "value": "artifact"}]
    description     TEXT
);

-- Seed data (25 levels)
INSERT INTO level_definitions (level, xp_required, title_suffix, description) VALUES
    (1,      0,      'Nowicjusz',        'Właśnie dołączyłeś do Gildii'),
    (2,      500,    'Adept',            'Pierwsze kroki za Tobą'),
    (3,      1200,   'Adept II',         'Zdobywasz doświadczenie'),
    (4,      2200,   'Aspirant',         'Twoja marka rośnie'),
    (5,      3500,   'Aspirant II',      'Odblokowujesz trudniejsze misje'),
    (6,      5500,   'Czeladnik',        ''),
    (7,      8000,   'Czeladnik II',     ''),
    (8,      11000,  'Wędrowiec',        ''),
    (9,      15000,  'Wędrowiec II',     ''),
    (10,     20000,  'Kompan',           'Legendarny próg'),
    (11,     26000,  'Kompan II',        ''),
    (12,     33000,  'Ranger',           ''),
    (13,     41000,  'Ranger II',        ''),
    (14,     50000,  'Weteran',          ''),
    (15,     60000,  'Weteran II',       ''),
    (16,     72000,  'Mistrz',           ''),
    (17,     85000,  'Mistrz II',        ''),
    (18,     100000, 'Arcymistrz',       ''),
    (19,     117000, 'Arcymistrz II',    ''),
    (20,     136000, 'Legenda',          'Top tier — Legenda Gildii'),
    (21,     160000, 'Legenda II',       ''),
    (22,     190000, 'Pół-Bóg',          ''),
    (23,     225000, 'Pół-Bóg II',       ''),
    (24,     265000, 'Bóg Przestrzeni',  ''),
    (25,     310000, 'Nieśmiertelny',    'Maximum level');
```

### game_config — wszystkie parametry gameplay

```sql
-- ============================================================
-- GAME CONFIG — hot-reloadable, bez restartu backendu
-- Zasada: ŻADNYCH wartości gameplay w kodzie ani env vars
-- ============================================================

CREATE TABLE game_config (
    key         TEXT PRIMARY KEY,
    value       JSONB        NOT NULL,
    description TEXT         NOT NULL,
    updated_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_by  TEXT                              -- admin user ID
);

INSERT INTO game_config (key, value, description) VALUES

('leveling', '{
  "coefficient": 80,
  "thresholds": [0, 80, 320, 720, 1280, 2000, 2880, 3920, 5120, 6480, 8000,
                 9680, 11520, 13520, 15680, 18000, 20480, 23120, 25920, 28880, 32000]
}', 'Progi XP per level. thresholds[N] = total XP do osiągnięcia poziomu N. Zmień coefficient LUB nadpisz thresholds ręcznie.'),

('quest_difficulty_weights', '{
  "1":  {"easy": 90, "medium": 10, "hard": 0,  "legendary": 0},
  "3":  {"easy": 65, "medium": 30, "hard": 5,  "legendary": 0},
  "5":  {"easy": 35, "medium": 40, "hard": 20, "legendary": 5},
  "8":  {"easy": 10, "medium": 35, "hard": 40, "legendary": 15},
  "12": {"easy": 5,  "medium": 20, "hard": 45, "legendary": 30}
}', 'Rozkład % trudności questów wg poziomu gracza. Klucz = minimalny poziom. Interpolowane liniowo między progami.'),

('xp_per_difficulty', '{
  "easy": 50, "medium": 100, "hard": 175, "legendary": 250
}', 'Bazowe XP za ukończenie questa danej trudności.'),

('xp_bonuses', '{
  "first_quest_daily": 25,
  "daily_cap": 500,
  "class_match_bonus": 0.20,
  "vote_xp": 10,
  "vote_xp_cap": 50,
  "streak": {"3": 1.10, "7": 1.20, "30": 1.35}
}', 'Wszystkie mnożniki i bonusy XP. class_match_bonus = extra % gdy quest pasuje do archetypu.'),

('quest_timer', '{
  "duration_minutes": 20,
  "grace_period_minutes": 2
}', 'Czas na ukończenie questa + grace period na upload dowodu po upłynięciu czasu.'),

('verification_thresholds', '{
  "auto_pass": 0.95,
  "community_queue": 0.50,
  "auto_fail": 0.30
}', 'Progi AI confidence. >auto_pass = XP natychmiast. Między community_queue a auto_pass = idzie do głosowania. <auto_fail = odrzucone.'),

-- UWAGA: Voting config jest teraz w seed.py (Section 19.3) — klucz "voting".
-- Poniższy wpis ad-hoc został usunięty, żeby uniknąć konfliktu schematów.
-- Kanoniczne wartości: voter_min_level, min_votes, voting_duration_hours, itd.

('rate_limits', '{
  "quest_generation_per_hour": 3,
  "votes_per_day": 50,
  "proof_uploads_per_day": 10
}', 'Rate limity per user. Zmień bez restartu — odczytywane przy każdym żądaniu z cache.'),

('upload_limits', '{
  "max_mb": 100,
  "allowed_mime_types": ["image/jpeg", "image/png", "image/webp", "video/mp4"],
  "max_video_seconds": 60
}', 'Limity uploadu dowodów.');
```

### prompt_templates — wszystkie prompty AI

```sql
-- ============================================================
-- PROMPT TEMPLATES — zmiana prompta bez deploymentu
-- Templates z {{ zmiennymi }} — interpolowane przez config.service.ts
-- ============================================================

CREATE TABLE prompt_templates (
    key             TEXT         PRIMARY KEY,
    system_prompt   TEXT         NOT NULL,
    user_template   TEXT         NOT NULL,  -- template z {{ zmiennymi }}
    model           TEXT         NOT NULL DEFAULT 'gemini-3-flash',
    temperature     FLOAT        NOT NULL DEFAULT 0.9,
    max_tokens      INT          NOT NULL DEFAULT 1024,
    version         INT          NOT NULL DEFAULT 1,
    description     TEXT         NOT NULL,
    updated_at      TIMESTAMPTZ  NOT NULL DEFAULT NOW(),
    updated_by      TEXT
);

-- Wartości startowe wstawiane przez seed script (treść promptów w osobnych plikach)
-- INSERT INTO prompt_templates VALUES ('quest_generation', ...), ('onboarding', ...), ('proof_verification', ...);
-- Patrz: packages/db/seeds/prompt_templates.ts
```

### Social — follows, reactions, comments, leaderboards

```sql
-- ============================================================
-- FOLLOWS — asymetryczny (Instagram-style)
-- ============================================================

CREATE TABLE follows (
    follower_id   UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    following_id  UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (follower_id, following_id),
    CHECK (follower_id != following_id)
);
CREATE INDEX idx_follows_following ON follows(following_id); -- kto mnie obserwuje

-- ============================================================
-- REACTIONS — thumbs up only na MVP, extensible przez reaction_type
-- ============================================================

CREATE TABLE reactions (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    proof_id      UUID        NOT NULL REFERENCES proofs(id) ON DELETE CASCADE,
    user_id       UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    reaction_type TEXT        NOT NULL DEFAULT 'upvote',  -- enum zarządzany w game_config
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (proof_id, user_id, reaction_type)             -- jeden danego typu per user
);
CREATE INDEX idx_reactions_proof ON reactions(proof_id);
CREATE INDEX idx_reactions_user  ON reactions(user_id);

-- ============================================================
-- COMMENTS
-- ============================================================

CREATE TABLE comments (
    id            UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    proof_id      UUID        NOT NULL REFERENCES proofs(id) ON DELETE CASCADE,
    user_id       UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content       TEXT        NOT NULL CHECK (char_length(content) BETWEEN 1 AND 500),
    is_deleted    BOOLEAN     NOT NULL DEFAULT FALSE,      -- soft delete
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_comments_proof ON comments(proof_id, created_at DESC);

-- ============================================================
-- LEADERBOARD SNAPSHOTS — cache obliczany przez BullMQ worker
-- ============================================================

CREATE TABLE leaderboard_snapshots (
    id           UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    period_type  TEXT        NOT NULL,   -- 'weekly' | 'monthly' | 'alltime'
    scope_type   TEXT        NOT NULL,   -- 'global' | 'city' | 'group' | 'archetype'
    scope_id     TEXT,                   -- city slug, group_id, archetype — NULL = global
    computed_at  TIMESTAMPTZ NOT NULL,
    valid_until  TIMESTAMPTZ NOT NULL,
    data         JSONB       NOT NULL    -- [{rank, user_id, username, avatar_url, xp, quests, tokens}]
);
CREATE INDEX idx_leaderboard_lookup
    ON leaderboard_snapshots(period_type, scope_type, scope_id, valid_until DESC);
```

### Token Economy

```sql
-- ============================================================
-- TOKEN ECONOMY — Hero Tokens za questy
-- Faza 1: wirtualne saldo (off-chain)
-- Faza 2: wypłata do prawdziwego portfela (on-chain)
-- ============================================================

-- Portfele użytkowników
CREATE TABLE wallet_addresses (
    user_id      UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    chain        TEXT NOT NULL,           -- 'ethereum' | 'polygon' | 'solana'
    address      TEXT NOT NULL,
    verified_at  TIMESTAMPTZ,             -- NULL = niezweryfikowany
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, chain)
);

-- Ledger tokenów — immutable, append-only (tak samo jak xp_ledger)
CREATE TABLE token_ledger (
    id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
    idempotency_key  TEXT        UNIQUE NOT NULL,  -- format: 'quest:{proof_id}' | 'tip:{tip_id}' — prevents double-award on BullMQ retry
    user_id          UUID        NOT NULL REFERENCES users(id),
    event_type     TEXT        NOT NULL,  -- 'quest_reward' | 'tip_received' | 'tip_sent' | 'withdrawal' | 'bonus'
    amount         NUMERIC(18,8) NOT NULL,  -- dodatnie = przychód, ujemne = wydatek
    balance_after  NUMERIC(18,8) NOT NULL,
    quest_id       UUID        REFERENCES quests(id),
    proof_id       UUID        REFERENCES proofs(id),
    tip_id         UUID,                  -- FK do tips (poniżej)
    tx_hash        TEXT,                  -- NULL = off-chain
    metadata       JSONB,                 -- breakdown: {base, difficulty_bonus, streak_bonus}
    created_at     TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_token_ledger_user ON token_ledger(user_id, created_at DESC);

-- Napiwki między użytkownikami
CREATE TABLE tips (
    id             UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
    from_user_id   UUID          NOT NULL REFERENCES users(id),
    to_user_id     UUID          NOT NULL REFERENCES users(id),
    proof_id       UUID          REFERENCES proofs(id),
    amount         NUMERIC(18,8) NOT NULL CHECK (amount > 0),
    currency       TEXT          NOT NULL DEFAULT 'HERO',  -- 'HERO' | 'USDC' | 'ETH'
    tx_hash        TEXT,                  -- NULL = wirtualny / fiat
    status         TEXT          NOT NULL DEFAULT 'pending',  -- pending|confirmed|failed
    created_at     TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    CHECK (from_user_id != to_user_id)
);
CREATE INDEX idx_tips_to_user ON tips(to_user_id, created_at DESC);

-- Wnioski o wypłatę wirtualnych tokenów na portfel
CREATE TABLE token_withdrawals (
    id            UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID          NOT NULL REFERENCES users(id),
    amount        NUMERIC(18,8) NOT NULL CHECK (amount > 0),
    chain         TEXT          NOT NULL,
    to_address    TEXT          NOT NULL,
    status        TEXT          NOT NULL DEFAULT 'pending',  -- pending|processing|confirmed|failed
    tx_hash       TEXT,
    requested_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    processed_at  TIMESTAMPTZ
);

-- Virtual token balance per user (denormalizacja dla szybkich zapytań)
-- Źródło prawdy to token_ledger — to tylko cache
CREATE TABLE token_balances (
    user_id        UUID          PRIMARY KEY REFERENCES users(id),
    balance        NUMERIC(18,8) NOT NULL DEFAULT 0,
    total_earned   NUMERIC(18,8) NOT NULL DEFAULT 0,
    total_withdrawn NUMERIC(18,8) NOT NULL DEFAULT 0,
    updated_at     TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- ============================================================
-- CUSTODIAL WALLET — NIE MVP, schema only
-- Faza 1: wirtualne saldo w token_balances (tabela powyżej)
-- Faza 2: Stripe payouts — HERO → fiat → konto bankowe
-- Faza 3: Polygon/Base ERC-20 smart contract
-- WŁĄCZENIE: token_withdrawal.enabled = true w game_config
-- KLUCZE PRYWATNE: NIGDY w bazie — zarządzane przez AWS KMS
-- ============================================================

CREATE TABLE custodial_wallets (
    id              UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id         UUID          NOT NULL UNIQUE REFERENCES users(id),

    -- Referencja do klucza w AWS KMS (klucz prywatny NIGDY tu)
    kms_key_arn     TEXT,         -- arn:aws:kms:eu-central-1:ACCOUNT:key/UUID

    -- Adresy wygenerowane z klucza KMS — NULL do czasu aktywacji Fazy 3
    evm_address     TEXT,         -- 0x... — Ethereum/Polygon/Base
    sol_address     TEXT,         -- Solana (opcjonalne)

    -- Faza systemu dla tego użytkownika
    phase           TEXT          NOT NULL DEFAULT 'virtual',  -- 'virtual'|'fiat'|'crypto'

    -- KYC (wymagane przed wypłatą Fazy 2+)
    kyc_verified    BOOLEAN       NOT NULL DEFAULT FALSE,
    kyc_provider_ref TEXT,        -- Referencja do Sumsub/KYC provider record
    kyc_verified_at TIMESTAMPTZ,

    created_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW(),
    updated_at      TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);
-- Uwaga: Tabela istnieje w schemacie, ale custodial_wallets.phase = 'virtual'
-- na MVP = brak interakcji z blockchain. Zero kodu blockchain na MVP.

-- ============================================================
-- COSMETIC PURCHASES — zakupy za tokeny HERO
-- ANTI PAY-TO-WIN: TYLKO kosmetyki. XP i levele są ZABLOKOWANE.
-- ============================================================

CREATE TABLE cosmetic_purchases (
    id             UUID          PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id        UUID          NOT NULL REFERENCES users(id),
    item_key       TEXT          NOT NULL,  -- identyfikator z game_config("token_shop")
    item_category  TEXT          NOT NULL,  -- 'avatar_frame'|'title'|'trail'|'emote'|'avatar_regen'
    price_paid     NUMERIC(18,8) NOT NULL CHECK (price_paid > 0),
    currency       TEXT          NOT NULL DEFAULT 'HERO',
    -- Nie ma tu kolumny xp_bonus, level_boost ani nic podobnego — by design.
    item_metadata  JSONB         NOT NULL DEFAULT '{}'::JSONB,
    -- {"frame_id": "neon_circuit", "rarity": "rare", "display_name": "Neonowy Obwód"}
    purchased_at   TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_cosmetic_purchases_user ON cosmetic_purchases(user_id);
CREATE INDEX idx_cosmetic_purchases_item ON cosmetic_purchases(item_key);
```

### Nowe wpisy w game_config dla social i tokenów

```sql
INSERT INTO game_config (key, value, description) VALUES

('reactions', '{
  "allowed_types": ["upvote"],
  "upvote_label": "👍",
  "future_types": ["legendary", "fire", "creative"]
}', 'Dostępne typy reakcji. Dodaj nowe typy bez zmiany kodu — tylko tu.'),

('feed_algorithm', '{
  "upvote_weight": 1.0,
  "comment_weight": 2.0,
  "half_life_hours": 12,
  "max_age_days": 7
}', 'Wagi algorytmu trending feed. score = (reactions*w + comments*w) / (age+2)^1.5'),

('token_rewards', '{
  "per_difficulty": {
    "easy": 1.0,
    "medium": 2.5,
    "hard": 5.0,
    "legendary": 12.0
  },
  "daily_cap": 25.0,
  "class_match_bonus": 0.20,
  "streak_bonus": {"3": 0.10, "7": 0.20, "30": 0.35},
  "first_quest_daily": 2,   // +2 HERO za pierwszy ukończony quest dnia (odrębne od XP bonus 25)
  "symbol": "HERO",
  "decimals": 8
}', 'Tokeny HERO za ukończone questy. daily_cap zapobiega farmowaniu.'),

('token_withdrawal', '{
  "min_withdrawal": 10.0,
  "enabled": false,
  "supported_chains": ["polygon", "solana"],
  "fee_percent": 2.0
}', 'Parametry wypłat. enabled=false na starcie — włącz gdy smart contract gotowy.'),

-- NOTE: "visual" field = human-readable description for reference/admin UI only.
-- It is NOT injected into Gemini prompts — actual image prompts are built dynamically
-- from visual_anchor JSONB + class_palette + tier number (see Section 12.6).
('avatar_tiers', '{
  "1":  {"min_level": 1,   "title": "Rekrut Gildii",              "visual": "Szary płaszcz i znoszony plecak, jedna glowing runa na ramieniu. Tło: deszczowa szara ulica."},
  "2":  {"min_level": 2,   "title": "Neofita Neonów",             "visual": "Pierwszy implant neuralny za uchem, bluza z LED paskami. Tło: nocny targ z neonami."},
  "3":  {"min_level": 3,   "title": "Aspirant Runicznego",        "visual": "Rękawica z runami świecącymi na zielono, okulary AR. Tło: dach z antenami i hologramami."},
  "4":  {"min_level": 4,   "title": "Inicjat Cyber-Magii",        "visual": "Częściowa zbroja neon-runiczna, holograficzny interfejs przy dłoni. Tło: aleja hologramów."},
  "5":  {"min_level": 5,   "title": "Adept Dwóch Światów",        "visual": "Zbroja z połączenia stali i magicznych kryształów, oczy lekko świecą. Tło: rozdroże światów."},
  "6":  {"min_level": 7,   "title": "Strażnik Neonowych Run",     "visual": "Grawerowane runy na całym ciele, aura klasy w jej kolorze. Tło: świątynia-server room."},
  "7":  {"min_level": 9,   "title": "Mistrz Cybernetyczny",       "visual": "Zaawansowane implanty widoczne pod skórą, magiczny płaszcz z przepływającymi danymi. Tło: megacity."},
  "8":  {"min_level": 11,  "title": "Arcymag Sieci",              "visual": "Orbitujące mini-drony z runami, intensywna aura klasy. Tło: centrum danych."},
  "9":  {"min_level": 13,  "title": "Kapitan Gildii",             "visual": "Odznaka Gildii wbudowana w zbroję, komenda widoczna w postawie. Tło: baza operacyjna."},
  "10": {"min_level": 15,  "title": "Archon Cyber-Magiczny",      "visual": "Kompletny strój arcana-tech, HUD holograficzny wokół głowy. Tło: korporacyjna twierdza."},
  "11": {"min_level": 18,  "title": "Władca Splotów",             "visual": "Ciało częściowo translucent z liniami energii, cyber/magic widoczne jednocześnie. Tło: sieć danych."},
  "12": {"min_level": 22,  "title": "Sędzia Nieskończoności",     "visual": "Zbroja z absorbowanych systemów, oczy jak ekrany matrycy. Tło: porzucone cybermegacity."},
  "13": {"min_level": 27,  "title": "Nieśmiertelny Protokół",     "visual": "Aura jako manifestacja danych, runy piszące się same w powietrzu. Tło: przestrzeń cyfrowa."},
  "14": {"min_level": 33,  "title": "Niszczyciel Zaporów",        "visual": "Zbroja zmieniająca kształt, reality glitch wokół postaci. Tło: pęknięta przestrzeń."},
  "15": {"min_level": 40,  "title": "Legenda Konwergencji",       "visual": "Symbol klasy żywy w chest plate, tło: Wielka Konwergencja — moment łączenia magii z kodem."},
  "16": {"min_level": 50,  "title": "Ascendant",                  "visual": "Ciało otoczone orbitującymi kryształami runiczno-cyfrowymi. Tło: przestrzeń między wymiarami."},
  "17": {"min_level": 65,  "title": "Przebudzony",                "visual": "Fizyczna forma częściowo zastąpiona energią — widmo i ciało jednocześnie. Tło: void cities."},
  "18": {"min_level": 80,  "title": "Architekt Rzeczywistości",   "visual": "Postać przepisuje świat wokół siebie, ruchome glichy terenu. Tło: collapsed reality."},
  "19": {"min_level": 100, "title": "Ewolucja",                   "visual": "Transcendencja klasy — forma niedefiniowalna, poza ludzkim rozumieniem. Tło: Big Bang runic."},
  "20": {"min_level": 120, "title": "Prageneza",                  "visual": "Sam jest Wielką Konwergencją — klasa i technologia zlały się w byt. Tło: void of pure creation."}
}', '20 tierów wizualnych avatarów z nieliniowym mapowaniem poziomów. min_level = próg dla nowego tieru.'),

('avatar_models', '{
  "1":  "gemini-3-pro-image-preview",
  "2":  "gemini-3.1-flash-image-preview",
  "3":  "gemini-3.1-flash-image-preview",
  "4":  "gemini-3.1-flash-image-preview",
  "5":  "gemini-3-pro-image-preview",
  "6":  "gemini-3.1-flash-image-preview",
  "7":  "gemini-3.1-flash-image-preview",
  "8":  "gemini-3.1-flash-image-preview",
  "9":  "gemini-3.1-flash-image-preview",
  "10": "gemini-3.1-flash-image-preview",
  "11": "gemini-3.1-flash-image-preview",
  "12": "gemini-3.1-flash-image-preview",
  "13": "gemini-3.1-flash-image-preview",
  "14": "gemini-3.1-flash-image-preview",
  "15": "gemini-3.1-flash-image-preview",
  "16": "gemini-3-pro-image-preview",
  "17": "gemini-3-pro-image-preview",
  "18": "gemini-3-pro-image-preview",
  "19": "gemini-3-pro-image-preview",
  "20": "gemini-3-pro-image-preview"
}', 'Model image gen per tier. Tier 1 + Tier 5 (milestone) + Tier 16-20 (legendary) = Pro (~$0.05). Tier 2-4 i 6-15 = Flash (~$0.02, 2.5x tańsze). Zmiana modelu = 1 UPDATE, bez redeploymentu.'),

('avatar_class_palettes', '{
  "techno_mag":         "electric blue, silver, purple",
  "chrom_paladin":      "gold, chrome silver, white",
  "widmo_biegacz":      "dark green, black, cyan",
  "bio_szaman":         "forest green, bioluminescent blue, earth brown",
  "inzynier_spoleczny": "deep red, black, subtle gold",
  "kurier_cieni":       "deep purple, black, silver",
  "architekt_danych":   "orange, white, deep blue",
  "koderyta":           "crimson, dark gold, arcane purple"
}', 'Palety kolorów per klasa — przekazywane do AI image generation prompt.'),

-- ============================================================
-- TOKEN SHOP — kosmetyki za HERO tokeny
-- NIE MVP — enabled=false dopóki token economy nie zostanie uruchomiona
-- GUARDRAIL: brak XP, brak level boost, brak pay-to-win
-- ============================================================
('token_shop', '{
  "enabled": false,
  "categories": {
    "avatar_frame": [
      {"key": "neon_circuit",      "price": 10.0,  "rarity": "common",    "name": "Neonowy Obwód"},
      {"key": "mana_flame",        "price": 25.0,  "rarity": "rare",      "name": "Płomień Many"},
      {"key": "void_glitch",       "price": 50.0,  "rarity": "epic",      "name": "Glitch Próżni"},
      {"key": "guild_sigil_gold",  "price": 100.0, "rarity": "legendary", "name": "Złote Sigil Gildii"}
    ],
    "title": [
      {"key": "cyber_prophet",     "price": 15.0,  "rarity": "rare",      "name": "Cyber-Prorok"},
      {"key": "shadow_runner",     "price": 30.0,  "rarity": "epic",      "name": "Cień Biegacza"},
      {"key": "mana_weaver",       "price": 75.0,  "rarity": "legendary", "name": "Tkacz Many"}
    ],
    "trail": [
      {"key": "neon_sparks",       "price": 20.0,  "rarity": "common",    "name": "Neonowe Iskry"},
      {"key": "mana_dust",         "price": 40.0,  "rarity": "rare",      "name": "Pył Many"}
    ],
    "avatar_regen": [
      {"key": "avatar_regen_once", "price": 5.0,   "rarity": "common",    "name": "Przegenereuj Avatar",
       "_note": "Jedyna operacyjna kategoria — pozwala na regenerację avatara bez zmiany tieru"}
    ]
  },
  "_guardrail": "PAY_TO_WIN_BLOCKED. Brak XP, brak level boost, brak stat bonus. Sklep = tylko wygląd."
}', 'Sklep kosmetyczny za HERO tokeny. enabled=false na MVP. Włącz gdy token economy aktywna.'),

-- ============================================================
-- CUSTODIAL WALLET CONFIG
-- ============================================================
('wallet_custodial', '{
  "enabled": false,
  "provider": "aws_kms",
  "region": "eu-central-1",
  "supported_chains": ["polygon", "base"],
  "kyc_required_for_withdrawal": true,
  "kyc_provider": "sumsub",
  "min_withdrawal_hero": 10.0,
  "fee_percent": 2.0,
  "phase": "virtual",
  "_mvp_note": "NIE implementować na MVP. Faza 1=virtual, Faza 2=fiat, Faza 3=crypto. Włącz przez zmianę phase + enabled=true."
}', 'Parametry custodial wallet. Phase 1=virtual (teraz). Przełączenie faz = 1 UPDATE w DB.');
```

### Tabele tworzone w Migration 001 (nie MVP-Section 1)

Poniższe tabele są tworzone w Migration 001 (Sekcja 20), a nie w Section 1 DDL, bo dotyczą funkcji rozszerzanych iteracyjnie (mapa, szablony questów, śledzenie prób). Dla kompletności referencji:

```sql
-- Katalog szablonów questów — pinned na mapie przez admina / AI
CREATE TABLE quest_templates (
    id               UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title            TEXT NOT NULL,
    description      TEXT NOT NULL,
    objective        TEXT NOT NULL,
    completion_hint  TEXT NOT NULL,
    lore_blurb       TEXT,
    difficulty       quest_difficulty NOT NULL,
    quest_tags       TEXT[] NOT NULL DEFAULT '{}',
    location         GEOGRAPHY(POINT, 4326),
    radius_visible_m INT NOT NULL DEFAULT 500,
    min_level        INT DEFAULT 1,
    is_active        BOOLEAN NOT NULL DEFAULT TRUE,
    is_ai_generated  BOOLEAN NOT NULL DEFAULT FALSE,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_quest_templates_location ON quest_templates USING GIST (location);
CREATE INDEX idx_quest_templates_active   ON quest_templates(is_active);

-- Śledzenie prób realizacji questów przez użytkowników
CREATE TABLE quest_attempts (
    id                UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id           UUID NOT NULL REFERENCES users(id),
    quest_id          UUID NOT NULL REFERENCES quests(id),  -- user quest instance (nie template)
    status            TEXT NOT NULL DEFAULT 'in_progress',
    chosen_proof_type TEXT DEFAULT NULL
                      CHECK (chosen_proof_type IN ('photo','multi_photo','video','audio','text')),
    accepted_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    expires_at        TIMESTAMPTZ NOT NULL,
    completed_at      TIMESTAMPTZ,
    abandoned_at      TIMESTAMPTZ,
    xp_awarded        INT DEFAULT 0,
    tokens_awarded    INT DEFAULT 0
);
CREATE INDEX idx_attempts_user    ON quest_attempts(user_id);
CREATE INDEX idx_attempts_status  ON quest_attempts(status);
CREATE INDEX idx_attempts_expires ON quest_attempts(expires_at);
```

> **Uwaga projektowa:** `quest_seen` jest tablicą deduplication dla find_nearby() — śledzi które szablony (`quest_templates`) user widział na mapie. Definicja powyżej w sekcji DDL (po `quest_history`). `quest_attempts` śledzi konkretne instancje questów (`quests`), nie szablony.

---

### Tabele dodane w Migration 002 — Retention, Fraud, GDPR

Poniższe tabele zostały dodane po fazie remediation i dotyczą: mid/long-term retention (guildy, sezony, wyzwania tygodniowe), anty-fraud scoring, GDPR deletion. Szczegóły implementacyjne — patrz docs/05 §11.6-9, docs/08 §33-34.

```sql
-- ============================================================
-- GUILDS — §11.6 (docs/05)
-- ============================================================

CREATE TABLE guilds (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL UNIQUE,
    description TEXT,
    leader_id   UUID NOT NULL REFERENCES users(id),
    level       INT NOT NULL DEFAULT 1,
    total_xp    BIGINT NOT NULL DEFAULT 0,
    is_public   BOOLEAN NOT NULL DEFAULT TRUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE guild_members (
    guild_id  UUID NOT NULL REFERENCES guilds(id) ON DELETE CASCADE,
    user_id   UUID NOT NULL REFERENCES users(id)  ON DELETE CASCADE,
    role      TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('leader','officer','member')),
    joined_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (guild_id, user_id)
);
CREATE INDEX idx_guild_members_user ON guild_members(user_id);

CREATE TABLE guild_weekly_challenges (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    guild_id      UUID NOT NULL REFERENCES guilds(id) ON DELETE CASCADE,
    week_start    DATE NOT NULL,
    target_type   TEXT NOT NULL,   -- 'quest_count'|'total_xp'|'hard_quests'|'unique_locations'
    target_value  INT NOT NULL,
    current_value INT NOT NULL DEFAULT 0,
    completed_at  TIMESTAMPTZ,
    reward_claimed BOOLEAN NOT NULL DEFAULT FALSE,
    UNIQUE (guild_id, week_start)
);
CREATE INDEX idx_gwc_week ON guild_weekly_challenges(week_start) WHERE completed_at IS NULL;

-- ============================================================
-- SEASONS & EVENTS — §11.7 (docs/05)
-- ============================================================

CREATE TABLE seasons (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name       TEXT NOT NULL,               -- "Sezon 1: Neonowa Wiosna"
    theme      TEXT NOT NULL,               -- klucz flavor injection w game_config
    start_date DATE NOT NULL,
    end_date   DATE NOT NULL,
    is_active  BOOLEAN NOT NULL DEFAULT FALSE,
    pass_cost  INT NOT NULL DEFAULT 200,    -- HERO; token_shop.seasonal.base
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_seasons_active ON seasons(is_active) WHERE is_active = TRUE;

CREATE TABLE season_pass (
    user_id      UUID NOT NULL REFERENCES users(id)    ON DELETE CASCADE,
    season_id    UUID NOT NULL REFERENCES seasons(id)  ON DELETE CASCADE,
    tier         TEXT NOT NULL DEFAULT 'free' CHECK (tier IN ('free','paid')),
    quest_count  INT NOT NULL DEFAULT 0,    -- ukończone questy w sezonie
    purchased_at TIMESTAMPTZ,
    PRIMARY KEY (user_id, season_id)
);
CREATE INDEX idx_season_pass_season ON season_pass(season_id);

CREATE TABLE seasonal_items (
    id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    season_id UUID NOT NULL REFERENCES seasons(id) ON DELETE CASCADE,
    item_key  TEXT NOT NULL,               -- np. "frame_neon_spring_2026"
    tier      TEXT NOT NULL CHECK (tier IN ('base','prestige')),
    name      TEXT NOT NULL,
    type      TEXT NOT NULL               -- 'frame'|'title'|'avatar'|'quest_theme'
);
CREATE INDEX idx_seasonal_items_season ON seasonal_items(season_id);

-- ============================================================
-- WEEKLY CHALLENGES — §11.8 (docs/05)
-- ============================================================

CREATE TABLE weekly_challenges (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    week_start  DATE NOT NULL,
    difficulty  TEXT NOT NULL CHECK (difficulty IN ('easy','medium','hard')),
    type        TEXT NOT NULL,             -- 'quest_count'|'xp_total'|'hard_quests'|'unique_neighborhoods'|'streak_days'
    target      INT NOT NULL,
    xp_reward   INT NOT NULL,
    hero_reward INT NOT NULL,
    UNIQUE (week_start, difficulty)
);

CREATE TABLE weekly_challenge_progress (
    user_id        UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    challenge_id   UUID NOT NULL REFERENCES weekly_challenges(id) ON DELETE CASCADE,
    current        INT NOT NULL DEFAULT 0,
    completed_at   TIMESTAMPTZ,
    reward_claimed BOOLEAN NOT NULL DEFAULT FALSE,
    PRIMARY KEY (user_id, challenge_id)
);
CREATE INDEX idx_wcp_user ON weekly_challenge_progress(user_id, challenge_id);

-- ============================================================
-- ANTI-FRAUD — §34 (docs/08)
-- ============================================================

CREATE TABLE user_fraud_scores (
    user_id      UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    score        SMALLINT NOT NULL DEFAULT 0 CHECK (score BETWEEN 0 AND 100),
    action       TEXT NOT NULL DEFAULT 'allow'
                 CHECK (action IN ('allow','shadow_monitor','manual_review','shadow_ban','ban')),
    signals      JSONB NOT NULL DEFAULT '[]',   -- [{key, ts}] historia sygnałów
    last_updated TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX idx_ufs_action ON user_fraud_scores(action) WHERE action != 'allow';

-- ============================================================
-- GDPR DELETION — §33 (docs/08)
-- ============================================================

CREATE TABLE deletion_requests (
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id       UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    requested_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    scheduled_for TIMESTAMPTZ NOT NULL,        -- requested_at + 7 days (okno na anulowanie)
    completed_at  TIMESTAMPTZ,
    cancelled_at  TIMESTAMPTZ
);
CREATE INDEX idx_deletion_requests_scheduled ON deletion_requests(scheduled_for)
  WHERE completed_at IS NULL AND cancelled_at IS NULL;
```

---

## TypeScript Implementation Reference

### Config (Zod env schema)

```typescript
// apps/api/src/config.ts
import { z } from 'zod'
import 'dotenv/config'

const envSchema = z.object({
  DATABASE_URL:            z.string().url(),
  REDIS_URL:               z.string().default('redis://localhost:6379'),
  JWT_SECRET:              z.string().min(32),
  GEMINI_API_KEY:          z.string(),
  FIREBASE_PROJECT_ID:     z.string(),
  FIREBASE_CREDENTIALS_PATH: z.string().optional(),
  S3_BUCKET:               z.string().optional(),
  S3_REGION:               z.string().default('eu-central-1'),
  WEATHER_API_KEY:         z.string().optional(),
  ENVIRONMENT:             z.enum(['development', 'staging', 'production']).default('development'),
  PORT:                    z.coerce.number().default(3000),
})

export type Config = z.infer<typeof envSchema>
export const config = envSchema.parse(process.env)
```

### Drizzle schema (replaces SQLAlchemy models)

```typescript
// packages/db/src/schema.ts
import { pgTable, uuid, text, boolean, timestamp, integer, bigint, jsonb, pgEnum } from 'drizzle-orm/pg-core'
import { geometry } from 'drizzle-orm/pg-core'

export const characterClassEnum = pgEnum('character_class', [
  'techno_mag', 'chrom_paladin', 'widmo_biegacz', 'bio_szaman',
  'inzynier_spoleczny', 'kurier_cieni', 'architekt_danych', 'koderyta',
])

export const users = pgTable('users', {
  id:                    uuid('id').primaryKey().defaultRandom(),
  firebaseUid:           text('firebase_uid').unique().notNull(),
  email:                 text('email'),
  username:              text('username').unique(),
  displayName:           text('display_name'),
  photoUrl:              text('photo_url'),
  isActive:              boolean('is_active').notNull().default(true),
  isAdmin:               boolean('is_admin').notNull().default(false),
  authProvider:          text('auth_provider').notNull().default('firebase'),
  timezone:              text('timezone').default('Europe/Warsaw'),
  // deviceTokens USUNIĘTE — patrz export const userDevices poniżej
  notificationPrefs:     jsonb('notification_prefs').notNull().default({}),
  reviewerAccuracy:      real('reviewer_accuracy').notNull().default(0.5),
  createdAt:             timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt:             timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
  lastLoginAt:           timestamp('last_login_at', { withTimezone: true }),
  deletedAt:             timestamp('deleted_at', { withTimezone: true }),
})

export const userDevices = pgTable('user_devices', {
  id:          uuid('id').primaryKey().defaultRandom(),
  userId:      uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  fcmToken:    text('fcm_token').notNull(),
  platform:    text('platform').notNull(),    // 'android' | 'ios'
  appVersion:  text('app_version').notNull(),
  deviceModel: text('device_model'),
  lastSeenAt:  timestamp('last_seen_at', { withTimezone: true }).notNull().defaultNow(),
  createdAt:   timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  revokedAt:   timestamp('revoked_at', { withTimezone: true }),
})

export const characters = pgTable('characters', {
  id:               uuid('id').primaryKey().defaultRandom(),
  userId:           uuid('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  name:             text('name').notNull(),
  classKey:         characterClassEnum('class_key').notNull(),
  title:            text('title').notNull(),
  backstory:        text('backstory').notNull(),
  level:            integer('level').notNull().default(1),
  xpTotal:          bigint('xp_total', { mode: 'number' }).notNull().default(0),
  xpToNext:         integer('xp_to_next').notNull().default(500),
  attrSila:         integer('attr_sila').notNull().default(10),
  attrIntelekt:     integer('attr_intelekt').notNull().default(10),
  attrCharyzma:     integer('attr_charyzma').notNull().default(10),
  attrZrecznosc:    integer('attr_zrecznosc').notNull().default(10),
  attrPercepcja:    integer('attr_percepcja').notNull().default(10),
  equipment:        jsonb('equipment').notNull().default({}),
  personalityTraits: text('personality_traits').array().notNull().default([]),
  visualAnchor:     jsonb('visual_anchor'),
  isActive:         boolean('is_active').notNull().default(true),
  createdAt:        timestamp('created_at', { withTimezone: true }).notNull().defaultNow(),
  updatedAt:        timestamp('updated_at', { withTimezone: true }).notNull().defaultNow(),
})
```

### Drizzle DB instance

```typescript
// packages/db/src/index.ts
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'
import * as schema from './schema'

const connection = postgres(process.env.DATABASE_URL!)
export const db = drizzle(connection, { schema })
export type DB = typeof db
```

### drizzle-kit migration config

```typescript
// packages/db/drizzle.config.ts
import { defineConfig } from 'drizzle-kit'

export default defineConfig({
  schema: './src/schema.ts',
  out:    './migrations',
  dialect: 'postgresql',
  dbCredentials: { url: process.env.DATABASE_URL! },
})
```

### Hono app entry point

```typescript
// apps/api/src/index.ts
import { Hono } from 'hono'
import { logger } from 'hono/logger'
import { secureHeaders } from 'hono/secure-headers'
import { cors } from 'hono/cors'
import { requestId } from 'hono/request-id'
import { authRouter } from './routes/auth'
import { questsRouter } from './routes/quests'
// ... other routers

const app = new Hono()

app.use(logger())
app.use(secureHeaders())
app.use(requestId())
app.use('/api/*', cors({ origin: ['https://app.20hero.com', 'exp://'] }))

app.route('/health', healthRouter)
app.route('/api/auth', authRouter)
app.route('/api/quests', questsRouter)
// ...

app.onError((err, c) => {
  if (err instanceof HTTPException) return c.json({ error: err.message }, err.status)
  console.error(err)
  return c.json({ error: 'Internal Server Error' }, 500)
})

export default { port: config.PORT, fetch: app.fetch }
```

### BullMQ queues and workers

```typescript
// apps/api/src/jobs/proof.worker.ts
import { Queue, Worker, Job } from 'bullmq'
import { redis } from '../db/redis'

export const proofQueue = new Queue('proof', { connection: redis })
export const avatarQueue = new Queue('avatar', { connection: redis })

new Worker('proof', async (job: Job) => {
  const { proofId } = job.data
  await verifyProofWithAI(proofId)
}, { connection: redis, concurrency: 5 })

// Scheduled jobs (replaces Celery beat)
await proofQueue.add('close-expired', {}, {
  repeat: { cron: '*/5 * * * *' },
  jobId: 'close-expired',
})
```

### Gemini AI (@google/genai TypeScript)

```typescript
// apps/api/src/ai/gemini.ts
import { GoogleGenAI } from '@google/genai'
import { config } from '../config'

const ai = new GoogleGenAI({ apiKey: config.GEMINI_API_KEY })

export async function generateJson<T>(
  prompt: string,
  systemInstruction: string,
  responseSchema: object,
): Promise<T> {
  const response = await ai.models.generateContent({
    model: 'gemini-3-flash',
    contents: [{ role: 'user', parts: [{ text: prompt }] }],
    config: { systemInstruction, responseMimeType: 'application/json', responseSchema },
  })
  return JSON.parse(response.text ?? '{}') as T
}
```

### XP utilities

```typescript
// packages/utils/src/xp.ts
export function levelForXp(xp: number, xpCoefficient: number, maxLevel = 120): number {
  let level = 1
  for (let n = 2; n <= maxLevel; n++) {
    if (xp >= xpCoefficient * n * (n - 1)) {
      level = n
    } else {
      break
    }
  }
  return level
}

export function xpForLevel(level: number, xpCoefficient: number): number {
  return xpCoefficient * level * (level - 1)
}

export function xpToNextLevel(xpTotal: number, xpCoefficient: number, maxLevel = 120): number {
  const currentLevel = levelForXp(xpTotal, xpCoefficient, maxLevel)
  if (currentLevel >= maxLevel) return 0
  return xpForLevel(currentLevel + 1, xpCoefficient) - xpTotal
}
```

### Monorepo config files

```json
// turbo.json
{
  "tasks": {
    "build":     { "dependsOn": ["^build"], "outputs": ["dist/**"] },
    "typecheck": { "dependsOn": ["^build"] },
    "test":      { "dependsOn": ["^build"] },
    "dev":       { "persistent": true, "cache": false }
  }
}
```

```yaml
# pnpm-workspace.yaml
packages:
  - 'apps/*'
  - 'packages/*'
```

```json
// apps/api/package.json
{
  "name": "@20hero/api",
  "dependencies": {
    "hono": "^4.0.0",
    "@hono/zod-validator": "^0.4.0",
    "zod": "^3.23.0",
    "@google/genai": "^1.0.0",
    "firebase-admin": "^12.0.0",
    "jose": "^5.0.0",
    "bullmq": "^5.0.0",
    "ioredis": "^5.3.0",
    "drizzle-orm": "^0.33.0",
    "postgres": "^3.4.0",
    "pino": "^9.0.0",
    "@20hero/db": "workspace:*",
    "@20hero/types": "workspace:*",
    "@20hero/utils": "workspace:*"
  }
}
```

---

## Migration 003 — Monetization (IAP + Sponsored Quests)

```sql
-- =============================================
-- Migration 003: IAP purchases, Hero Pass subscriptions, Sponsored quests
-- =============================================

-- In-App Purchase receipts (idempotent via purchase_token UNIQUE)
CREATE TABLE iap_purchases (
  id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id          UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  platform         TEXT        NOT NULL CHECK (platform IN ('apple', 'google')),
  product_id       TEXT        NOT NULL,                -- e.g. 'com.20hero.tokens.100'
  purchase_token   TEXT        NOT NULL UNIQUE,          -- Apple: receipt hash / Google: purchaseToken
  amount_usd       NUMERIC(10,2) NOT NULL,
  hero_tokens_granted INT      NOT NULL DEFAULT 0,
  validated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  raw_receipt      JSONB                                 -- full receipt stored for audit
);
CREATE INDEX ON iap_purchases (user_id);
CREATE INDEX ON iap_purchases (validated_at);

-- Hero Pass (season pass) activations — one per user per season
CREATE TABLE hero_pass_subscriptions (
  id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id          UUID        NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  season_id        UUID        NOT NULL REFERENCES seasons(id),
  iap_purchase_id  UUID        REFERENCES iap_purchases(id),
  tier             TEXT        NOT NULL CHECK (tier IN ('free', 'paid')),
  activated_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  UNIQUE (user_id, season_id)
);
CREATE INDEX ON hero_pass_subscriptions (season_id);

-- B2B Sponsors for Sponsored Quest campaigns
CREATE TABLE sponsors (
  id               UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  name             TEXT        NOT NULL,
  logo_url         TEXT,
  campaign_budget_eur NUMERIC(10,2),
  campaign_start_at   TIMESTAMPTZ,
  campaign_end_at     TIMESTAMPTZ,
  created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Sponsored quest linkage (alter existing quests table)
ALTER TABLE quests ADD COLUMN sponsor_id  UUID    REFERENCES sponsors(id);
ALTER TABLE quests ADD COLUMN is_sponsored BOOLEAN NOT NULL DEFAULT FALSE;
CREATE INDEX ON quests (sponsor_id) WHERE sponsor_id IS NOT NULL;
```

---