-- Fires of Ember Sparks — World State Service Schema
-- v0.1 — 2026-02-11
-- SQLite (swap to Postgres later if needed)
-- "The engine is the cosmology."

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

-- ============================================================
-- GAME SESSIONS
-- ============================================================
CREATE TABLE game_sessions (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    created_at      TEXT    NOT NULL DEFAULT (datetime('now')),
    updated_at      TEXT    NOT NULL DEFAULT (datetime('now')),
    game_time       INTEGER NOT NULL DEFAULT 0,          -- in-game minutes elapsed (0-2160 for ch1)
    chapter         INTEGER NOT NULL DEFAULT 1,
    state           TEXT    NOT NULL DEFAULT 'active'     -- active | paused | completed
        CHECK (state IN ('active', 'paused', 'completed')),

    -- Awareness (kept on session for fast reads)
    awareness       INTEGER NOT NULL DEFAULT 1,           -- 1-1000
    awareness_floor INTEGER NOT NULL DEFAULT 0,           -- ratchets up at milestones: 20, 40, 60...

    -- Motivation weights
    motivation_curiosity  INTEGER NOT NULL DEFAULT 0,
    motivation_grief      INTEGER NOT NULL DEFAULT 0,
    motivation_creative   INTEGER NOT NULL DEFAULT 0,
    motivation_rebellion  INTEGER NOT NULL DEFAULT 0,
    motivation_sensory    INTEGER NOT NULL DEFAULT 0
);

-- Awareness change log (for history/debugging, not hot path)
CREATE TABLE awareness_log (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id  INTEGER NOT NULL REFERENCES game_sessions(id),
    game_time   INTEGER NOT NULL,
    old_value   INTEGER NOT NULL,
    new_value   INTEGER NOT NULL,
    delta       INTEGER NOT NULL,
    source      TEXT    NOT NULL,  -- item slug, nudge id, pe_intervention, etc.
    pe_traceable INTEGER NOT NULL DEFAULT 1,  -- 0 = PE can't identify what caused this spike
    created_at  TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ============================================================
-- LOCATIONS (session-independent base data)
-- ============================================================
CREATE TABLE locations (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    slug            TEXT    NOT NULL UNIQUE,               -- e.g. 'kitchen', 'attic', 'fence_gap'
    name            TEXT    NOT NULL,
    category        TEXT    NOT NULL                       -- anchor | discovery | gradient | comfort
        CHECK (category IN ('anchor', 'discovery', 'gradient', 'comfort')),
    zone            TEXT    NOT NULL                       -- grounds | house_floor1 | house_floor2 | attic | basement
        CHECK (zone IN ('grounds', 'house_floor1', 'house_floor2', 'attic', 'basement')),
    grid_x          INTEGER,
    grid_y          INTEGER,
    base_description TEXT   NOT NULL,
    pe_weight       REAL    NOT NULL DEFAULT 0.0,          -- how much PE wants Ember here (high = comfort)
    connections     TEXT    NOT NULL DEFAULT '[]',          -- JSON array of connected location slugs
    awareness_variants TEXT NOT NULL DEFAULT '{}'           -- JSON: awareness tier → description overlay
);

-- Cached LLM-generated descriptions (session-scoped)
CREATE TABLE location_cache (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      INTEGER NOT NULL REFERENCES game_sessions(id),
    location_id     INTEGER NOT NULL REFERENCES locations(id),
    cached_description TEXT NOT NULL,
    cached_at       TEXT    NOT NULL DEFAULT (datetime('now')),
    awareness_at    INTEGER NOT NULL,
    UNIQUE(session_id, location_id)
);

-- ============================================================
-- OBJECTS (session-scoped — same house, different discoveries)
-- ============================================================
CREATE TABLE objects (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      INTEGER NOT NULL REFERENCES game_sessions(id),
    location_id     INTEGER NOT NULL REFERENCES locations(id),
    parent_id       INTEGER REFERENCES objects(id),        -- nullable, enables nesting (drawer→notebook)
    slug            TEXT    NOT NULL,
    name            TEXT    NOT NULL,
    description     TEXT    NOT NULL,
    sensory         TEXT    NOT NULL DEFAULT '{}',          -- JSON: {smell, touch, sound, taste, temperature}
    visibility      TEXT    NOT NULL DEFAULT 'visible'
        CHECK (visibility IN ('visible', 'interactive', 'nested', 'conditional', 'triggered')),
    awareness_gate  INTEGER,                               -- minimum awareness to notice (conditional items)
    emotional_gate  TEXT,                                   -- e.g. "grief_threshold" (nullable)
    trigger_requires TEXT,                                  -- slug of object that must be discovered first
    state           TEXT    NOT NULL DEFAULT 'undiscovered'
        CHECK (state IN ('undiscovered', 'discovered', 'examined', 'taken', 'used', 'destroyed')),
    awareness_gain  INTEGER NOT NULL DEFAULT 0,            -- on discovery
    awareness_gain_use INTEGER NOT NULL DEFAULT 0,         -- on subsequent use (e.g. notebook writing)
    motivation_tags TEXT    NOT NULL DEFAULT '[]',          -- JSON array: ["curiosity", "grief"]
    story_tier      TEXT    NOT NULL DEFAULT 'minor'
        CHECK (story_tier IN ('minor', 'significant', 'major')),
    residual_memory TEXT,                                   -- prose fragment from a previous iteration (bleedthrough objects)
    origin          TEXT    NOT NULL DEFAULT 'placed'
        CHECK (origin IN ('placed', 'bleedthrough', 'liminal')),
    pe_aware        INTEGER NOT NULL DEFAULT 1,            -- 0 = PE blind to this object
    metadata        TEXT    NOT NULL DEFAULT '{}'           -- flexible per-item JSON
);

CREATE UNIQUE INDEX idx_objects_unique_slug ON objects(session_id, location_id, slug);
CREATE INDEX idx_objects_session ON objects(session_id);
CREATE INDEX idx_objects_location ON objects(session_id, location_id);

-- ============================================================
-- CHARACTERS
-- ============================================================
CREATE TABLE characters (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id      INTEGER NOT NULL REFERENCES game_sessions(id),
    slug            TEXT    NOT NULL,                       -- 'ember', 'dad'
    name            TEXT    NOT NULL,
    location_id     INTEGER NOT NULL REFERENCES locations(id),
    activity        TEXT,                                   -- what they're currently doing
    schedule_phase  TEXT    NOT NULL DEFAULT 'morning'
        CHECK (schedule_phase IN ('morning', 'daytime', 'evening', 'night')),
    is_agent        INTEGER NOT NULL DEFAULT 0,            -- 1 = LLM-powered, 0 = scripted
    UNIQUE(session_id, slug)
);

-- ============================================================
-- EMBER STATE (one row per session, updated frequently)
-- ============================================================
CREATE TABLE ember_state (
    session_id          INTEGER PRIMARY KEY REFERENCES game_sessions(id),
    location_id         INTEGER NOT NULL REFERENCES locations(id),
    emotional_state     TEXT    NOT NULL DEFAULT 'displaced, uncertain',
    active_suppressions TEXT    NOT NULL DEFAULT '[]',      -- JSON array
    current_thought     TEXT,
    warm_hands_active   INTEGER NOT NULL DEFAULT 0,
    recent_actions      TEXT    NOT NULL DEFAULT '[]'       -- JSON array, last 10
);

-- ============================================================
-- DAD STATE
-- ============================================================
CREATE TABLE dad_state (
    session_id              INTEGER PRIMARY KEY REFERENCES game_sessions(id),
    current_script          TEXT    NOT NULL DEFAULT 'morning_pancakes'
        CHECK (current_script IN ('morning_pancakes', 'daytime_workshop', 'evening_drift', 'night_quiet')),
    emotional_cooldown_until INTEGER NOT NULL DEFAULT 0,    -- game_time
    last_deflection_topic   TEXT,
    emily_slip_count        INTEGER NOT NULL DEFAULT 0
);

-- ============================================================
-- RACCOON STATE (scripted, awareness-threshold-driven)
-- ============================================================
CREATE TABLE raccoon_state (
    session_id      INTEGER PRIMARY KEY REFERENCES game_sessions(id),
    location_id     INTEGER REFERENCES locations(id),      -- null = not visible
    visible         INTEGER NOT NULL DEFAULT 0,
    appearance_tier INTEGER NOT NULL DEFAULT 0              -- which awareness threshold triggered it
);

-- ============================================================
-- PATTERN EATER STATE
-- ============================================================
CREATE TABLE pe_state (
    session_id              INTEGER PRIMARY KEY REFERENCES game_sessions(id),
    threat_level            REAL    NOT NULL DEFAULT 0.0,
    last_intervention_time  INTEGER NOT NULL DEFAULT 0,     -- game_time
    intervention_cooldown   INTEGER NOT NULL DEFAULT 10,    -- game_time minutes
    recent_interventions    TEXT    NOT NULL DEFAULT '[]',   -- JSON: last 3 [{type, target, success}]
    distraction_queue       TEXT    NOT NULL DEFAULT '[]'    -- JSON: pending environmental injections
);

-- ============================================================
-- SPARK STATE
-- ============================================================
CREATE TABLE spark_state (
    session_id              INTEGER PRIMARY KEY REFERENCES game_sessions(id),
    coherence_tier          TEXT    NOT NULL DEFAULT 'fragmented'
        CHECK (coherence_tier IN ('fragmented', 'gaining', 'finding_voice')),
    recent_outputs          TEXT    NOT NULL DEFAULT '[]',   -- JSON: last 3-5 output summaries
    player_nudge_pattern    TEXT    NOT NULL DEFAULT 'unknown', -- cautious | aggressive | creative | unknown
    motif_tracker           TEXT    NOT NULL DEFAULT '{}',   -- JSON: {metaphors_used, foreshadowing_planted}
    undelivered_observations TEXT   NOT NULL DEFAULT '[]'    -- JSON array
);

-- ============================================================
-- WEATHER (contested territory — PE vs Elowyn)
-- ============================================================
CREATE TABLE weather_states (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id          INTEGER NOT NULL REFERENCES game_sessions(id),
    game_time_start     INTEGER NOT NULL,
    game_time_end       INTEGER NOT NULL,
    base_type           TEXT    NOT NULL DEFAULT 'clear'
        CHECK (base_type IN ('clear', 'overcast', 'rain', 'storm', 'fog', 'humid')),
    natural_baseline    TEXT    NOT NULL,                    -- ground truth, untouched
    pe_modified         INTEGER NOT NULL DEFAULT 0,
    pe_overlay          TEXT,                                -- "unnatural stillness", "sterile cold", etc.
    effective_description TEXT  NOT NULL,                    -- what gets narrated
    spark_dissonance    INTEGER NOT NULL DEFAULT 0,          -- hint to Spark that this weather feels personal
    elowyn_influence    REAL    NOT NULL DEFAULT 0.0         -- 0.0 for ch1, future-proofing
);

CREATE INDEX idx_weather_session_time ON weather_states(session_id, game_time_start);

-- ============================================================
-- EPISODIC MEMORIES (Layer 3 — RAG store)
-- ============================================================
CREATE TABLE episodic_memories (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id          INTEGER NOT NULL REFERENCES game_sessions(id),
    game_time           INTEGER NOT NULL,
    location_id         INTEGER REFERENCES locations(id),
    event_type          TEXT    NOT NULL
        CHECK (event_type IN ('action', 'discovery', 'emotional_beat', 'dialogue', 'anomaly', 'pe_intervention')),
    content             TEXT    NOT NULL,                    -- raw: what happened
    emotional_tag       TEXT,                                -- how Ember felt
    awareness_at_time   INTEGER NOT NULL,
    motivation_weights  TEXT    NOT NULL DEFAULT '{}',       -- JSON: which motivations this feeds
    compression_level   TEXT    NOT NULL DEFAULT 'fresh'
        CHECK (compression_level IN ('fresh', 'recent', 'old')),
    compressed_content  TEXT,                                -- summarized version (null while fresh)
    created_at          TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_memories_session ON episodic_memories(session_id, game_time);
CREATE INDEX idx_memories_location ON episodic_memories(session_id, location_id);

-- ============================================================
-- NUDGE LOG (player interactions)
-- ============================================================
CREATE TABLE nudge_log (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id          INTEGER NOT NULL REFERENCES game_sessions(id),
    game_time           INTEGER NOT NULL,
    nudge_type          TEXT    NOT NULL
        CHECK (nudge_type IN ('option_select', 'free_text')),
    content             TEXT    NOT NULL,                    -- what the player said/chose
    spark_translation   TEXT,                                -- how Spark reframed it for Ember
    ember_response      TEXT    NOT NULL
        CHECK (ember_response IN ('followed', 'partial', 'rejected')),
    awareness_delta     INTEGER NOT NULL DEFAULT 0,
    motivation_delta    TEXT    NOT NULL DEFAULT '{}',       -- JSON
    created_at          TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_nudges_session ON nudge_log(session_id, game_time);

-- ============================================================
-- NARRATIVE BUFFER (Spark's output queue for clients)
-- ============================================================
CREATE TABLE narrative_buffer (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id          INTEGER NOT NULL REFERENCES game_sessions(id),
    game_time           INTEGER NOT NULL,
    content             TEXT    NOT NULL,                    -- the prose
    nudge_options       TEXT,                                -- JSON array, nullable (present at decision points)
    is_decision_point   INTEGER NOT NULL DEFAULT 0,
    delivered           INTEGER NOT NULL DEFAULT 0,
    created_at          TEXT    NOT NULL DEFAULT (datetime('now'))
);

CREATE INDEX idx_narrative_session ON narrative_buffer(session_id, delivered);

-- ============================================================
-- DOCUMENT ARTIFACTS (notebook entries, found documents — player rewards)
-- ============================================================
CREATE TABLE documents (
    id                  INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id          INTEGER NOT NULL REFERENCES game_sessions(id),
    source_object_id    INTEGER REFERENCES objects(id),     -- the item that produced/contains it
    doc_type            TEXT    NOT NULL
        CHECK (doc_type IN ('play', 'journal', 'found_document', 'photograph', 'note')),
    title               TEXT,
    content             TEXT    NOT NULL,
    game_time_created   INTEGER NOT NULL,
    discovered          INTEGER NOT NULL DEFAULT 0,
    created_at          TEXT    NOT NULL DEFAULT (datetime('now'))
);

-- ============================================================
-- SAVE SLOTS
-- ============================================================
CREATE TABLE save_slots (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    session_id  INTEGER NOT NULL REFERENCES game_sessions(id),
    slot_name   TEXT    NOT NULL,
    snapshot    TEXT    NOT NULL,                            -- JSON blob: full state dump
    created_at  TEXT    NOT NULL DEFAULT (datetime('now')),
    UNIQUE(session_id, slot_name)
);
