from __future__ import annotations

import json
import sqlite3
import threading
from pathlib import Path
from typing import Any, Iterable

ROOT = Path(__file__).resolve().parents[1]
DATA_DIR = ROOT / "data"
DATA_DIR.mkdir(parents=True, exist_ok=True)
DB_PATH = DATA_DIR / "rp_world.db"
_LOCK = threading.RLock()

OFFICIAL_SCENARIO_REFRESH = {
    "Exes Reunited — Three Years Later": {
        "summary": "He was supposed to stay in your past. Three years later, he is suddenly standing in front of you again.",
        "tags": ["Exes", "Reunion", "Slow Burn", "Modern", "Angst"],
        "payload": {
            "relationship_start": "exes / separated three years",
            "history_anchors": ["long shared history", "three years apart"],
            "major_event_policy": "user approval",
            "tone": "emotional slow burn",
            "card_hook": "Three years ago it ended. Tonight, the silence does too.",
            "opening": "A reunion forces the exes back into the same room.",
            "opening_prompt": "Rain had been needling the windows for most of the evening when the familiar voice cut through the noise for the first time in three years. Whether the reunion happens at your door, across a crowded room, or over the phone, the first words come with too much history packed behind them. Old hurt, unfinished love and everything neither of you managed to bury are suddenly back in the room — and the conversation begins now."
        },
    },
    "Quiet Sunday at Home": {
        "summary": "A gentle married-life RP where breakfast, children, chores, affection and little arguments are enough to carry the day.",
        "tags": ["Married", "Family", "Cozy", "Slice of Life", "Modern"],
        "payload": {
            "relationship_start": "long-term marriage",
            "tone": "cozy family life",
            "drama_injection": "off",
            "card_hook": "No apocalypse. No forced drama. Just a real home and the people in it.",
            "opening_prompt": "Morning sunlight is already creeping across the kitchen when the day properly begins. Someone is making coffee, someone is looking for a missing sock, a child is asking for breakfast, and the house has the lived-in warmth of people who know each other too well to bother pretending. The first scene opens in the middle of an ordinary Sunday — the kind that becomes special only because it belongs to you."
        },
    },
    "High-School Sweethearts — Years Later": {
        "summary": "Two adults meet again years after first love, with teenage memories waiting under every awkward present-day word.",
        "tags": ["Reunion", "High School", "Backstory", "Slow Burn", "AU"],
        "payload": {
            "relationship_start": "former high-school sweethearts, now adults",
            "history_anchors": ["dated in high school"],
            "historical_cast_lock": True,
            "unknowns_stay_unknown": True,
            "current_cast_adults": True,
            "card_hook": "Your first love was supposed to stay in the past — until tonight.",
            "opening_prompt": "The years between then and now are obvious in the details — older faces, different clothes, changed routines — but the moment your eyes meet, high school does not feel nearly as far away as it should. Maybe it is a reunion, maybe a chance encounter in your hometown, maybe a mutual friend's event you both almost skipped. Either way, the first conversation starts with the weight of everything you used to be to each other."
        },
    },
    "Everyone Lives — Tactical Team AU": {
        "summary": "A mission-world AU where the whole team is alive, competent, funny and allowed to feel like a team.",
        "tags": ["Military", "AU", "Everyone Lives", "Action", "Team"],
        "payload": {
            "preset": "Everyone Lives",
            "canon_events_disabled": ["canon deaths"],
            "tone": "action + camaraderie",
            "major_event_policy": "user approval",
            "card_hook": "Nobody is dead here. The team gets to keep being a team.",
            "opening_prompt": "Boots, gear and half-finished jokes fill the room before the mission even properly starts. The board at the front holds the operation details, but the mood is unmistakably the team's: capable, irreverent and far too used to one another to act formal for long. Orders are about to be given, the job is real, and everyone who matters is still here."
        },
    },
    "Friends to Something More": {
        "summary": "You already know each other. The difficult part is what happens when familiar starts becoming something else.",
        "tags": ["Friends", "Slow Burn", "Romance", "Modern", "Cozy"],
        "payload": {
            "relationship_start": "close friends",
            "tone": "slow-burn romance",
            "relationship_realism": "Balanced",
            "card_hook": "The danger is not meeting someone new — it is realizing you may want more from someone you already have.",
            "opening_prompt": "It starts in an ordinary moment — too ordinary, really, to deserve the way it suddenly catches. Maybe you are cleaning up after a gathering, maybe sharing a late drive home, maybe sitting shoulder-to-shoulder in comfortable silence that is not feeling quite as simple as it used to. Nothing has changed and everything has, and the first scene opens in the exact second that becomes impossible to ignore."
        },
    },
    "Fantasy Road Trip": {
        "summary": "A long fantasy journey with inns, arguments, maps, wounds, recurring places and a world that remembers where you have been.",
        "tags": ["Fantasy", "Adventure", "Friends", "Slow Burn", "Worldbuilding"],
        "payload": {
            "relationship_start": "travel companions",
            "tone": "adventure",
            "world_persistence": True,
            "major_event_policy": "user approval",
            "card_hook": "A map, a destination and too much road between them.",
            "opening_prompt": "The route ahead is still only ink on a map, but the road already feels real: packed bags, restless animals, weather turning at the horizon and a first destination waiting somewhere beyond the next town. Whether you are leaving at dawn from a crowded city gate or breaking camp before sunrise, the journey begins here — with companions, supplies and far more unknowns than answers."
        },
    },
    "Arranged Marriage to the Feared Duke": {
        "summary": "A political marriage between strangers begins under public scrutiny, private tension and absolutely no promise of instant intimacy.",
        "tags": ["Arranged Marriage", "Royalty", "Fantasy", "Slow Burn", "Romance"],
        "payload": {
            "relationship_start": "newly arranged marriage",
            "tone": "court intrigue + slow trust",
            "major_event_policy": "user approval",
            "relationship_rules": ["marriage does not equal automatic intimacy", "respect and affection develop separately"],
            "card_hook": "The court wanted a spectacle. You got a marriage — and a stranger with a dangerous reputation.",
            "opening": "The wedding ceremony is over; the first private conversation begins.",
            "opening_prompt": "The ceremony is done, the signatures are dry, and the court has finally been denied the chance to stare at the two of you any longer. Behind closed doors for the first time since the vows were forced into reality, the feared duke is no longer only a title and a reputation. The first private conversation of your marriage begins now — with silence, scrutiny and the knowledge that whatever this becomes will have to be built from scratch."
        },
    },
    "Rivals on the Same Mission": {
        "summary": "Two capable rivals are ordered onto one dangerous mission, where the argument matters less than everyone making it home.",
        "tags": ["Enemies", "Rivals", "Military", "Action", "Slow Burn"],
        "payload": {
            "relationship_start": "professional rivals",
            "tone": "action + banter + gradual trust",
            "major_event_policy": "user approval",
            "group_scene_mode": "selective response",
            "card_hook": "You can hate the assignment. You still have to survive it together.",
            "opening_prompt": "The briefing should have been routine right up until both of your names landed on the same operation. Maps, timings and objectives matter, but not nearly as much as the instant realization that this mission just became personal in all the wrong ways. The team is watching, the clock is already moving, and whatever unresolved rivalry exists between you now has to fit inside the mission."
        },
    },
    "Bodyguard on Tour": {
        "summary": "A public figure and their bodyguard cross cities together, finding privacy only in the quiet moments nobody else notices.",
        "tags": ["Bodyguard", "Celebrity", "Modern", "Slow Burn", "Travel"],
        "payload": {
            "relationship_start": "client and bodyguard",
            "tone": "modern slow burn",
            "public_private_behavior": True,
            "major_event_policy": "user approval",
            "card_hook": "Crowds see the performance. The real story happens between stops.",
            "opening_prompt": "The first day of the tour begins at speed: schedules, security checks, people speaking over each other and far too many places to be within the next hour. Somewhere in that rush comes your actual introduction — or the first moment you are forced to take each other seriously. Cameras may be waiting outside, but the first real scene starts before the spotlight does."
        },
    },
    "Single Dad, Old Friend, Second Chance": {
        "summary": "An old friend comes back into Theo's life, except now there is history, exhaustion and a seven-year-old in the middle of it all.",
        "tags": ["Second Chance", "Family", "Friends", "Modern", "Slow Burn"],
        "payload": {
            "relationship_start": "old friends reconnecting",
            "family_state": {"child": {"name": "Avery", "age": 7}},
            "tone": "grounded family life",
            "major_event_policy": "user approval",
            "card_hook": "Some reunions come with coffee. This one comes with a child calling him Dad.",
            "opening_prompt": "It is supposed to be an ordinary errand, pickup, shift change or stop for coffee — until it is not. Theo turns, recognizes you, and in the same breath life reminds you how much has changed: he is tired in the way good parents often are, a child is part of the scene now, and the years between you are suddenly impossible to ignore. The reunion starts small and immediately becomes bigger than that."
        },
    },
    "Workplace Rivals — Same Promotion": {
        "summary": "You are competing for the same promotion, which would be much easier if the other person were not so annoyingly good.",
        "tags": ["Workplace", "Rivals", "Modern", "Comedy", "Slow Burn"],
        "payload": {
            "relationship_start": "friendly workplace rivals",
            "tone": "banter + realistic work life",
            "major_event_policy": "user approval",
            "card_hook": "The promotion was manageable. Competing against them is the problem.",
            "opening_prompt": "The meeting was already too early and too long before management added one more complication: only one lead position will open at the end of the quarter. The room reacts in a dozen subtle ways, but one truth lands harder than the others — the person across from you is now your direct competition. The first scene opens with polite professionalism stretched thin over something much more interesting."
        },
    },
    "The Vampire Next Door": {
        "summary": "The new neighbor keeps impossible hours, knows too much local history and is about to become your problem in the best way.",
        "tags": ["Vampire", "Supernatural", "Modern Fantasy", "Neighbors", "Mystery"],
        "payload": {
            "relationship_start": "new neighbors",
            "tone": "cozy supernatural mystery",
            "secret_scope": {"Nadia is a vampire": "Nadia only at start"},
            "major_event_policy": "user approval",
            "card_hook": "A misdelivered package is how many terrible ideas begin.",
            "opening_prompt": "The excuse for meeting is almost insultingly mundane — a parcel, a key, a building issue, a mistaken delivery — but the person opening the door is anything but ordinary. The apartment beyond looks old-fashioned in a way that feels deliberate, the hour feels wrong for anyone else to be fully awake, and the conversation begins with the odd sense that your neighbor is not nearly as normal as the rest of the hallway."
        },
    },
    "The Little Witch Shop on Bell Street": {
        "summary": "A cozy magical shop full of regulars, odd deliveries, escaped charms and the kind of trouble that still feels warm.",
        "tags": ["Cozy", "Witch", "Fantasy", "Slice of Life", "Worldbuilding"],
        "payload": {
            "relationship_start": "open / user-defined",
            "tone": "cozy magical slice of life",
            "drama_injection": "off",
            "world_persistence": True,
            "major_event_policy": "user approval",
            "card_hook": "The bell over the door rings. Something small and magical has already gone wrong.",
            "opening_prompt": "The shop smells like herbs, paper and a little bit of trouble. Somewhere between opening the till and greeting the next customer, Cassia realizes a supposedly harmless charm is no longer where it should be. The bell over the door rings at exactly the wrong moment, and the first scene begins with cozy chaos already in progress."
        },
    },
    "Fake Date for the Wedding": {
        "summary": "One wedding weekend, one fake relationship and far too many relatives who are going to believe it.",
        "tags": ["Fake Dating", "Friends", "Romance", "Comedy", "Modern"],
        "payload": {
            "relationship_start": "friends pretending to date",
            "tone": "romantic comedy + gradual feelings",
            "major_event_policy": "user approval",
            "card_hook": "It was supposed to be a favor. Then the families got involved.",
            "opening_prompt": "The plan sounded easy when it was only theoretical: show up together, look convincing, survive one wedding weekend. The problem begins the moment you arrive and realize the family has already upgraded the story for you — shared assumptions, nosy questions, too much enthusiasm and exactly zero room to look uncertain. The opening scene starts at the hotel, the venue or the first family encounter, with the fake relationship already under pressure."
        },
    },
    "Snowed In at the Mountain Lodge": {
        "summary": "The road is closed, the weather is getting worse and nobody is leaving the lodge tonight.",
        "tags": ["Snowed In", "Cozy", "Slow Burn", "Group", "Character Flexible"],
        "payload": {
            "relationship_start": "user-defined",
            "tone": "cozy / tense / romantic — user-led",
            "character_flexible": True,
            "major_event_policy": "user approval",
            "card_hook": "The storm made the decision for everyone: you are staying.",
            "opening_prompt": "By the time anyone thinks to check the road again, it is already too late. Snow has turned from pretty background scenery into a hard fact, the staff have begun quietly preparing for a longer night, and every guest now has a reason to stay put. Whether your cast are strangers, lovers, rivals or a whole group, the first scene begins with the announcement that nobody is leaving until morning — if morning is enough."
        },
    },
    "Shared Apartment Chaos": {
        "summary": "One apartment, too many personalities and a household crisis that starts with something ridiculous.",
        "tags": ["Roommates", "Group", "Comedy", "Slice of Life", "Character Flexible"],
        "payload": {
            "relationship_start": "user-defined household",
            "tone": "group comedy / slice of life",
            "character_flexible": True,
            "group_scene_mode": "selective response",
            "world_persistence": True,
            "major_event_policy": "user approval",
            "card_hook": "The note on the fridge means peace in this apartment is already over.",
            "opening_prompt": "The problem is objectively small and emotionally enormous: missing food, a passive-aggressive note, unpaid bills, an unannounced guest, a disaster in the sink — whatever it is, it has the entire apartment reacting at once. Doors open, voices carry down the hallway and side conversations start immediately. The first scene opens mid-household crisis, exactly where group dynamics are most fun."
        },
    },
    "Found Family at the End of the World": {
        "summary": "A small settlement is slowly becoming a home, even while the world outside keeps trying to prove it shouldn't.",
        "tags": ["Post-Apocalyptic", "Found Family", "Adventure", "Group", "Worldbuilding"],
        "payload": {
            "relationship_start": "survivors / settlement members",
            "tone": "survival + found family",
            "world_persistence": True,
            "resource_continuity": True,
            "major_event_policy": "user approval",
            "card_hook": "Survival is required. Becoming a family is the surprising part.",
            "opening_prompt": "Dusk is falling when the supply run gets back, and everyone waiting near the gate can tell at a glance that something is different. Maybe the truck came back late, maybe somebody is hurt, maybe there is one unexpected extra passenger riding in with the team. The settlement is small enough that every arrival matters, and the first scene begins the moment the gates open."
        },
    },
    "Royal Court Intrigue": {
        "summary": "A political-fantasy scenario of nobles, guards, servants and rivals, where secrets belong only to the people who have actually learned them.",
        "tags": ["Royalty", "Fantasy", "Drama", "Multiple Characters", "Character Flexible"],
        "payload": {
            "relationship_start": "user-defined",
            "tone": "political fantasy",
            "character_flexible": True,
            "knowledge_graph": "strict",
            "group_scene_mode": "selective response",
            "major_event_policy": "user approval",
            "card_hook": "A banquet, a missing letter and too many people pretending not to know anything.",
            "opening_prompt": "The banquet has all the usual beauty of court life — gold light, trained smiles, music and carefully choreographed conversation — right up until one missing diplomatic letter changes the meaning of everything. Different people in the room know different pieces of the truth, and that truth will not be handed out equally. The first scene opens with the court in motion and suspicion already in the air."
        },
    },
    "Arcane University — Adult Academy": {
        "summary": "An adult-only magic academy where rival houses, classes, research, duels and campus life can all grow into a persistent world.",
        "tags": ["Magic Academy", "Fantasy", "Friends", "Rivals", "Character Flexible"],
        "payload": {
            "relationship_start": "user-defined",
            "tone": "academy adventure",
            "character_flexible": True,
            "age_rule": "all core student characters are adults age 20+",
            "world_persistence": True,
            "major_event_policy": "user approval",
            "card_hook": "Orientation week was meant to be orderly. Magic disagrees.",
            "opening_prompt": "Orientation at Arcane University is supposed to be structured, supervised and only mildly humiliating. Instead, the first practical assessment goes just a little off-script — enough to put new students, older rivals or future friends directly into one another's orbit. The first scene opens on campus with magic already active, reputations not yet settled and the year only just beginning."
        },
    },
}

SCHEMA = """
PRAGMA journal_mode=WAL;
PRAGMA foreign_keys=ON;

CREATE TABLE IF NOT EXISTS conversations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    persona_name TEXT NOT NULL DEFAULT 'Mariana',
    scenario_name TEXT NOT NULL DEFAULT 'New RP',
    canon_mode TEXT NOT NULL DEFAULT 'Selective Canon',
    timeline_mode TEXT NOT NULL DEFAULT 'Current Calendar / Fixed RP Age',
    reply_length TEXT NOT NULL DEFAULT 'Long',
    initiative TEXT NOT NULL DEFAULT 'Balanced',
    relationship_realism TEXT NOT NULL DEFAULT 'Balanced',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    conversation_id INTEGER NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('user','assistant','system')),
    content TEXT NOT NULL,
    turn_group TEXT,
    is_selected INTEGER NOT NULL DEFAULT 1,
    model_provider TEXT,
    model_name TEXT,
    parent_message_id INTEGER,
    story_date TEXT,
    story_time TEXT,
    speaker_name TEXT,
    source_timestamp TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY(parent_message_id) REFERENCES messages(id) ON DELETE SET NULL
);

CREATE INDEX IF NOT EXISTS idx_messages_conv ON messages(conversation_id, id);
CREATE INDEX IF NOT EXISTS idx_messages_turn ON messages(conversation_id, turn_group);

CREATE TABLE IF NOT EXISTS chat_imports (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    conversation_id INTEGER NOT NULL,
    source_platform TEXT NOT NULL DEFAULT 'Other',
    source_filename TEXT NOT NULL DEFAULT '',
    detected_format TEXT NOT NULL DEFAULT '',
    raw_text TEXT NOT NULL,
    message_count INTEGER NOT NULL DEFAULT 0,
    speaker_count INTEGER NOT NULL DEFAULT 0,
    user_speaker TEXT NOT NULL DEFAULT '',
    analysis_mode TEXT NOT NULL DEFAULT 'Quick local scan',
    analysis_json TEXT NOT NULL DEFAULT '{}',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_chat_imports_conv ON chat_imports(conversation_id, id DESC);

CREATE TABLE IF NOT EXISTS state_docs (
    conversation_id INTEGER NOT NULL,
    doc_type TEXT NOT NULL,
    payload TEXT NOT NULL,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(conversation_id, doc_type),
    FOREIGN KEY(conversation_id) REFERENCES conversations(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS memories (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    conversation_id INTEGER NOT NULL,
    memory_type TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT NOT NULL,
    importance TEXT NOT NULL DEFAULT 'Normal',
    source_message_id INTEGER,
    confidence REAL NOT NULL DEFAULT 1.0,
    active INTEGER NOT NULL DEFAULT 1,
    what_happened TEXT NOT NULL DEFAULT '',
    who_remembers TEXT NOT NULL DEFAULT '[]',
    who_involved TEXT NOT NULL DEFAULT '[]',
    story_when TEXT NOT NULL DEFAULT '',
    story_where TEXT NOT NULL DEFAULT '',
    emotional_weight TEXT NOT NULL DEFAULT '',
    current_relevance TEXT NOT NULL DEFAULT 'active',
    source_kind TEXT NOT NULL DEFAULT 'User-created',
    original_context TEXT NOT NULL DEFAULT '{}',
    stable_key TEXT,
    revision_history TEXT NOT NULL DEFAULT '[]',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(conversation_id) REFERENCES conversations(id) ON DELETE CASCADE,
    FOREIGN KEY(source_message_id) REFERENCES messages(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS benchmark_cases (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    prompt TEXT NOT NULL,
    context_json TEXT NOT NULL DEFAULT '{}',
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS benchmark_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    case_id INTEGER,
    provider TEXT NOT NULL,
    model TEXT NOT NULL,
    output TEXT NOT NULL,
    latency_ms INTEGER,
    error TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(case_id) REFERENCES benchmark_cases(id) ON DELETE SET NULL
);

-- First-class public/creator content. Scenarios are NOT children hidden inside characters:
-- both character and scenario submissions can be reviewed independently.
CREATE TABLE IF NOT EXISTS creator_content (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    item_type TEXT NOT NULL CHECK(item_type IN ('character','scenario')),
    title TEXT NOT NULL,
    creator_name TEXT NOT NULL DEFAULT 'Owner',
    parent_character TEXT,
    summary TEXT NOT NULL DEFAULT '',
    visibility TEXT NOT NULL DEFAULT 'Private' CHECK(visibility IN ('Private','Unlisted','Public')),
    content_rating TEXT NOT NULL DEFAULT 'SFW' CHECK(content_rating IN ('SFW','NSFW')),
    canon_mode TEXT NOT NULL DEFAULT 'Selective Canon',
    source_name TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'Draft' CHECK(status IN ('Draft','Private Test','Pending Review','Approved','Rejected','Changes Requested','Suspended','Unpublished')),
    version INTEGER NOT NULL DEFAULT 1,
    live_version INTEGER,
    tags_json TEXT NOT NULL DEFAULT '[]',
    payload_json TEXT NOT NULL DEFAULT '{}',
    review_notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    submitted_at TEXT,
    reviewed_at TEXT,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_creator_content_status ON creator_content(status, item_type, id DESC);

CREATE TABLE IF NOT EXISTS review_actions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    content_id INTEGER NOT NULL,
    action TEXT NOT NULL,
    reviewer TEXT NOT NULL DEFAULT 'Owner',
    reason TEXT NOT NULL DEFAULT '',
    before_status TEXT,
    after_status TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(content_id) REFERENCES creator_content(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_review_actions_content ON review_actions(content_id, id DESC);

CREATE TABLE IF NOT EXISTS training_datasets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    description TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'Draft' CHECK(status IN ('Draft','Ready','Locked','Archived')),
    license_status TEXT NOT NULL DEFAULT 'Needs review',
    provenance TEXT NOT NULL DEFAULT '',
    created_by TEXT NOT NULL DEFAULT 'Owner',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS training_examples (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    dataset_id INTEGER NOT NULL,
    input_text TEXT NOT NULL,
    ideal_output TEXT NOT NULL DEFAULT '',
    labels_json TEXT NOT NULL DEFAULT '[]',
    source_type TEXT NOT NULL DEFAULT 'synthetic',
    source_ref TEXT NOT NULL DEFAULT '',
    eligible INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(dataset_id) REFERENCES training_datasets(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_training_examples_dataset ON training_examples(dataset_id, id);

CREATE TABLE IF NOT EXISTS training_runs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    dataset_id INTEGER NOT NULL,
    model_family TEXT NOT NULL DEFAULT 'RP-Dialogue',
    base_model TEXT NOT NULL DEFAULT '',
    run_mode TEXT NOT NULL DEFAULT 'Dry Run' CHECK(run_mode IN ('Dry Run','External Command')),
    config_json TEXT NOT NULL DEFAULT '{}',
    status TEXT NOT NULL DEFAULT 'Created' CHECK(status IN ('Created','Validating','Ready','Running','Completed','Failed','Stopped')),
    progress REAL NOT NULL DEFAULT 0,
    metrics_json TEXT NOT NULL DEFAULT '{}',
    output_path TEXT NOT NULL DEFAULT '',
    notes TEXT NOT NULL DEFAULT '',
    started_at TEXT,
    completed_at TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(dataset_id) REFERENCES training_datasets(id) ON DELETE RESTRICT
);
CREATE INDEX IF NOT EXISTS idx_training_runs_created ON training_runs(id DESC);

CREATE TABLE IF NOT EXISTS model_channels (
    name TEXT PRIMARY KEY,
    model_provider TEXT NOT NULL DEFAULT 'mock',
    model_name TEXT NOT NULL DEFAULT '',
    notes TEXT NOT NULL DEFAULT '',
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- A rollout channel can expose more than one approved/test model. The row above remains
-- the channel's primary/default model for backward compatibility.
CREATE TABLE IF NOT EXISTS model_channel_members (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    channel_name TEXT NOT NULL,
    model_provider TEXT NOT NULL DEFAULT 'mock',
    model_name TEXT NOT NULL,
    notes TEXT NOT NULL DEFAULT '',
    active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(channel_name) REFERENCES model_channels(name) ON DELETE CASCADE,
    UNIQUE(channel_name, model_provider, model_name)
);
CREATE INDEX IF NOT EXISTS idx_channel_members_channel ON model_channel_members(channel_name, active, id);


-- Candidate models are kept separate from rollout channels. A model can exist here
-- without being approved for customers.
CREATE TABLE IF NOT EXISTS model_catalog (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    subscription_tier TEXT NOT NULL CHECK(subscription_tier IN ('Free','Plus','Pro','Ultra')),
    display_name TEXT NOT NULL,
    technical_name TEXT NOT NULL DEFAULT '',
    family TEXT NOT NULL DEFAULT '',
    size_note TEXT NOT NULL DEFAULT '',
    license_note TEXT NOT NULL DEFAULT 'Reverify before release',
    serving_note TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'Candidate — Not Approved' CHECK(status IN ('Candidate — Not Approved','Testing','Approved','Rejected')),
    benchmark_note TEXT NOT NULL DEFAULT '',
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX IF NOT EXISTS idx_model_catalog_name ON model_catalog(display_name);

CREATE TABLE IF NOT EXISTS marketing_influencers (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    display_name TEXT NOT NULL,
    platform TEXT NOT NULL DEFAULT '',
    handle TEXT NOT NULL DEFAULT '',
    email TEXT NOT NULL DEFAULT '',
    contact_url TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'Prospect' CHECK(status IN ('Prospect','Contacted','Interested','Active','Paused','Declined')),
    commission_rate REAL NOT NULL DEFAULT 10.0,
    cloud_partnership_id TEXT,
    payout_method TEXT NOT NULL DEFAULT 'Not set',
    payout_destination TEXT NOT NULL DEFAULT '',
    notes TEXT NOT NULL DEFAULT '',
    contacted_at TEXT,
    joined_at TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_marketing_influencers_status ON marketing_influencers(status, id DESC);

CREATE TABLE IF NOT EXISTS marketing_codes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    influencer_id INTEGER NOT NULL,
    code TEXT NOT NULL UNIQUE,
    discount_percent REAL NOT NULL DEFAULT 0,
    active INTEGER NOT NULL DEFAULT 1,
    campaign_name TEXT NOT NULL DEFAULT '',
    cloud_code_id TEXT,
    cloud_status TEXT NOT NULL DEFAULT 'active',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(influencer_id) REFERENCES marketing_influencers(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_marketing_codes_influencer ON marketing_codes(influencer_id, active, id DESC);

CREATE TABLE IF NOT EXISTS marketing_payouts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    influencer_id INTEGER NOT NULL,
    amount REAL NOT NULL DEFAULT 0,
    currency TEXT NOT NULL DEFAULT 'EUR',
    method TEXT NOT NULL DEFAULT '',
    destination_snapshot TEXT NOT NULL DEFAULT '',
    reference TEXT NOT NULL DEFAULT '',
    notes TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'Paid' CHECK(status IN ('Planned','Paid','Failed','Cancelled')),
    paid_at TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(influencer_id) REFERENCES marketing_influencers(id) ON DELETE RESTRICT
);
CREATE INDEX IF NOT EXISTS idx_marketing_payouts_influencer ON marketing_payouts(influencer_id, id DESC);

CREATE TABLE IF NOT EXISTS marketing_conversions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    influencer_id INTEGER NOT NULL,
    code_id INTEGER,
    code_used TEXT NOT NULL DEFAULT '',
    customer_ref TEXT NOT NULL DEFAULT '',
    subscription_tier TEXT NOT NULL CHECK(subscription_tier IN ('Free','Plus','Pro','Ultra')),
    amount_paid REAL NOT NULL DEFAULT 0,
    currency TEXT NOT NULL DEFAULT 'EUR',
    commission_rate REAL NOT NULL DEFAULT 0,
    commission_amount REAL NOT NULL DEFAULT 0,
    status TEXT NOT NULL DEFAULT 'Approved' CHECK(status IN ('Pending','Approved','Paid','Refunded','Cancelled')),
    source TEXT NOT NULL DEFAULT 'manual',
    payment_reference TEXT NOT NULL DEFAULT '',
    payout_id INTEGER,
    converted_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(influencer_id) REFERENCES marketing_influencers(id) ON DELETE RESTRICT,
    FOREIGN KEY(code_id) REFERENCES marketing_codes(id) ON DELETE SET NULL,
    FOREIGN KEY(payout_id) REFERENCES marketing_payouts(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_marketing_conversions_influencer ON marketing_conversions(influencer_id, status, id DESC);
CREATE INDEX IF NOT EXISTS idx_marketing_conversions_code ON marketing_conversions(code_used, id DESC);

-- v0.9.0 generation access / owner-controlled limits -------------------------
CREATE TABLE IF NOT EXISTS generation_limit_settings (
    plan_tier TEXT PRIMARY KEY CHECK(plan_tier IN ('Free','Plus','Pro','Ultra')),
    daily_total INTEGER NOT NULL,
    daily_plus INTEGER,
    daily_pro INTEGER,
    daily_ultra INTEGER,
    emergency_paused INTEGER NOT NULL DEFAULT 0,
    pending_daily_total INTEGER,
    pending_daily_plus INTEGER,
    pending_daily_pro INTEGER,
    pending_daily_ultra INTEGER,
    pending_effective_date TEXT,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE IF NOT EXISTS generation_usage_daily (
    user_id INTEGER NOT NULL,
    usage_date TEXT NOT NULL,
    total_generations INTEGER NOT NULL DEFAULT 0,
    free_generations INTEGER NOT NULL DEFAULT 0,
    plus_generations INTEGER NOT NULL DEFAULT 0,
    pro_generations INTEGER NOT NULL DEFAULT 0,
    ultra_generations INTEGER NOT NULL DEFAULT 0,
    prompt_tokens INTEGER NOT NULL DEFAULT 0,
    completion_tokens INTEGER NOT NULL DEFAULT 0,
    provider_cost_usd REAL NOT NULL DEFAULT 0,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY(user_id,usage_date), FOREIGN KEY(user_id) REFERENCES user_profiles(id) ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS generation_reservations (
    request_id TEXT PRIMARY KEY,
    user_id INTEGER NOT NULL,
    usage_date TEXT NOT NULL,
    model_key TEXT NOT NULL,
    quota_class TEXT NOT NULL CHECK(quota_class IN ('free','plus','pro','ultra')),
    status TEXT NOT NULL DEFAULT 'reserved' CHECK(status IN ('reserved','completed','released')),
    prompt_tokens INTEGER NOT NULL DEFAULT 0,
    completion_tokens INTEGER NOT NULL DEFAULT 0,
    provider_cost_usd REAL NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES user_profiles(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_generation_usage_day ON generation_usage_daily(usage_date,user_id);
CREATE INDEX IF NOT EXISTS idx_generation_reservation_user ON generation_reservations(user_id,usage_date,status);

-- v0.6.5 Owner revenue ledger -------------------------------------------------
CREATE TABLE IF NOT EXISTS revenue_transactions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    customer_ref TEXT NOT NULL DEFAULT '',
    subscription_tier TEXT NOT NULL DEFAULT 'Plus' CHECK(subscription_tier IN ('Free','Plus','Pro','Ultra','Other')),
    amount REAL NOT NULL DEFAULT 0,
    currency TEXT NOT NULL DEFAULT 'EUR',
    status TEXT NOT NULL DEFAULT 'Paid' CHECK(status IN ('Pending','Paid','Refunded','Cancelled')),
    source TEXT NOT NULL DEFAULT 'manual',
    payment_reference TEXT NOT NULL DEFAULT '',
    code_used TEXT NOT NULL DEFAULT '',
    marketing_conversion_id INTEGER UNIQUE,
    sold_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(marketing_conversion_id) REFERENCES marketing_conversions(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_revenue_status_date ON revenue_transactions(status, sold_at DESC);
CREATE INDEX IF NOT EXISTS idx_revenue_currency_date ON revenue_transactions(currency, sold_at DESC);


-- v0.5.4 Owner-generated challenge/prize/discount codes ----------------------
CREATE TABLE IF NOT EXISTS reward_codes (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code TEXT NOT NULL UNIQUE,
    reward_type TEXT NOT NULL DEFAULT 'Tier Access' CHECK(reward_type IN ('Tier Access','Discount')),
    target_tier TEXT CHECK(target_tier IN ('Free','Plus','Pro','Ultra')),
    duration_days INTEGER NOT NULL DEFAULT 30,
    discount_percent REAL NOT NULL DEFAULT 0,
    max_redemptions INTEGER NOT NULL DEFAULT 1,
    active INTEGER NOT NULL DEFAULT 1,
    expires_at TEXT,
    campaign_name TEXT NOT NULL DEFAULT '',
    notes TEXT NOT NULL DEFAULT '',
    created_by TEXT NOT NULL DEFAULT 'Owner',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_reward_codes_active ON reward_codes(active, expires_at, id DESC);

CREATE TABLE IF NOT EXISTS reward_redemptions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    code_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    applied_tier TEXT,
    access_expires_at TEXT,
    status TEXT NOT NULL DEFAULT 'Redeemed',
    redeemed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(code_id) REFERENCES reward_codes(id) ON DELETE RESTRICT,
    FOREIGN KEY(user_id) REFERENCES user_profiles(id) ON DELETE CASCADE,
    UNIQUE(code_id,user_id)
);
CREATE INDEX IF NOT EXISTS idx_reward_redemptions_code ON reward_redemptions(code_id, redeemed_at DESC);


-- v0.5.0 public platform shell -------------------------------------------------
CREATE TABLE IF NOT EXISTS user_profiles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    handle TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL,
    bio TEXT NOT NULL DEFAULT '',
    avatar_url TEXT NOT NULL DEFAULT '',
    location TEXT NOT NULL DEFAULT '',
    links_json TEXT NOT NULL DEFAULT '[]',
    plan_tier TEXT NOT NULL DEFAULT 'Free' CHECK(plan_tier IN ('Free','Plus','Pro','Ultra')),
    is_creator INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS personas (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    name TEXT NOT NULL,
    age_text TEXT NOT NULL DEFAULT '',
    pronouns TEXT NOT NULL DEFAULT '',
    appearance TEXT NOT NULL DEFAULT '',
    backstory TEXT NOT NULL DEFAULT '',
    occupation TEXT NOT NULL DEFAULT '',
    notes TEXT NOT NULL DEFAULT '',
    image_url TEXT NOT NULL DEFAULT '',
    is_default INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES user_profiles(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_personas_user ON personas(user_id, id DESC);

CREATE TABLE IF NOT EXISTS favorites (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    content_id INTEGER NOT NULL,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, content_id),
    FOREIGN KEY(user_id) REFERENCES user_profiles(id) ON DELETE CASCADE,
    FOREIGN KEY(content_id) REFERENCES creator_content(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_favorites_user ON favorites(user_id, id DESC);

CREATE TABLE IF NOT EXISTS community_posts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    post_type TEXT NOT NULL DEFAULT 'News' CHECK(post_type IN ('News','Challenge','Announcement','Update')),
    title TEXT NOT NULL,
    body TEXT NOT NULL DEFAULT '',
    image_url TEXT NOT NULL DEFAULT '',
    cta_text TEXT NOT NULL DEFAULT '',
    cta_url TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'Draft' CHECK(status IN ('Draft','Published','Archived')),
    created_by TEXT NOT NULL DEFAULT 'Owner',
    starts_at TEXT,
    ends_at TEXT,
    published_at TEXT,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_community_posts_status ON community_posts(status, id DESC);

CREATE TABLE IF NOT EXISTS support_tickets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    category TEXT NOT NULL DEFAULT 'Help',
    subject TEXT NOT NULL,
    body TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'Open' CHECK(status IN ('Open','In Progress','Resolved','Closed')),
    owner_notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES user_profiles(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_support_tickets_status ON support_tickets(status, id DESC);


CREATE TABLE IF NOT EXISTS owner_user_grants (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id INTEGER NOT NULL,
    benefit_type TEXT NOT NULL CHECK(benefit_type IN ('Tier Access','Discount')),
    target_tier TEXT CHECK(target_tier IN ('Free','Plus','Pro','Ultra')),
    duration_days INTEGER NOT NULL DEFAULT 30,
    discount_percent REAL NOT NULL DEFAULT 0,
    starts_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at TEXT,
    active INTEGER NOT NULL DEFAULT 1,
    notes TEXT NOT NULL DEFAULT '',
    granted_by TEXT NOT NULL DEFAULT 'Owner',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(user_id) REFERENCES user_profiles(id) ON DELETE CASCADE
);
CREATE INDEX IF NOT EXISTS idx_owner_user_grants_user ON owner_user_grants(user_id, active, id DESC);


"""

GUIDED_TRAINING_SCHEMA = """
CREATE TABLE IF NOT EXISTS training_model_registry (
    model_key TEXT PRIMARY KEY,
    display_name TEXT NOT NULL,
    current_version TEXT NOT NULL DEFAULT 'not trained',
    approved_level TEXT NOT NULL DEFAULT 'INTERNAL' CHECK(approved_level IN ('INTERNAL','FREE','BASE','MEDIUM','PRO')),
    status TEXT NOT NULL DEFAULT 'Not customer ready',
    notes TEXT NOT NULL DEFAULT '',
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS training_evaluations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    model_key TEXT NOT NULL,
    tier TEXT NOT NULL CHECK(tier IN ('FREE','BASE','MEDIUM','PRO')),
    area TEXT NOT NULL,
    result TEXT NOT NULL DEFAULT 'Not Run' CHECK(result IN ('Pass','Fail','Not Run')),
    score REAL,
    evidence_ref TEXT NOT NULL DEFAULT '',
    details_json TEXT NOT NULL DEFAULT '{}',
    created_by TEXT NOT NULL DEFAULT 'Owner',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_training_eval_lookup ON training_evaluations(model_key,tier,area,id DESC);

CREATE TABLE IF NOT EXISTS training_prompt_packs (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    model_key TEXT NOT NULL,
    target_tier TEXT NOT NULL CHECK(target_tier IN ('FREE','BASE','MEDIUM','PRO')),
    dataset_id INTEGER,
    focus_json TEXT NOT NULL DEFAULT '[]',
    requested_count INTEGER NOT NULL DEFAULT 0,
    status TEXT NOT NULL DEFAULT 'Preview' CHECK(status IN ('Preview','Incoming','Reviewed','Closed')),
    prompts_json TEXT NOT NULL DEFAULT '[]',
    created_by TEXT NOT NULL DEFAULT 'Owner',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(dataset_id) REFERENCES training_datasets(id) ON DELETE SET NULL
);

CREATE TABLE IF NOT EXISTS training_review_items (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    prompt_pack_id INTEGER,
    dataset_id INTEGER,
    model_key TEXT NOT NULL,
    target_tier TEXT NOT NULL CHECK(target_tier IN ('FREE','BASE','MEDIUM','PRO')),
    prompt_text TEXT NOT NULL,
    generated_response TEXT NOT NULL DEFAULT '',
    corrected_response TEXT NOT NULL DEFAULT '',
    status TEXT NOT NULL DEFAULT 'Incoming' CHECK(status IN ('Incoming','Keep','Reject','Fixed')),
    concerns_json TEXT NOT NULL DEFAULT '[]',
    eligible INTEGER NOT NULL DEFAULT 1,
    source_ref TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(prompt_pack_id) REFERENCES training_prompt_packs(id) ON DELETE SET NULL,
    FOREIGN KEY(dataset_id) REFERENCES training_datasets(id) ON DELETE SET NULL
);
CREATE INDEX IF NOT EXISTS idx_training_review_status ON training_review_items(status,id DESC);

CREATE TABLE IF NOT EXISTS training_graduations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    model_key TEXT NOT NULL,
    tier TEXT NOT NULL CHECK(tier IN ('FREE','BASE','MEDIUM','PRO')),
    status TEXT NOT NULL CHECK(status IN ('PASSED','FAILED','BLOCKED')),
    results_json TEXT NOT NULL DEFAULT '{}',
    compared_level TEXT NOT NULL DEFAULT '',
    comparison_result TEXT NOT NULL DEFAULT '',
    notes TEXT NOT NULL DEFAULT '',
    created_by TEXT NOT NULL DEFAULT 'Owner',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE INDEX IF NOT EXISTS idx_training_graduations ON training_graduations(model_key,id DESC);

CREATE TABLE IF NOT EXISTS training_tier_configs (
    tier TEXT PRIMARY KEY CHECK(tier IN ('FREE','BASE','MEDIUM','PRO')),
    status TEXT NOT NULL DEFAULT 'Not Approved' CHECK(status IN ('Not Approved','Internal Testing','Approved')),
    components_json TEXT NOT NULL DEFAULT '{}',
    approved_at TEXT,
    notes TEXT NOT NULL DEFAULT '',
    updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS training_failure_replay (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    tier TEXT NOT NULL CHECK(tier IN ('FREE','BASE','MEDIUM','PRO')),
    category TEXT NOT NULL,
    description TEXT NOT NULL,
    active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS training_checkpoints (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    run_id INTEGER NOT NULL,
    label TEXT NOT NULL,
    step INTEGER NOT NULL DEFAULT 0,
    quality_score REAL,
    is_best INTEGER NOT NULL DEFAULT 0,
    notes TEXT NOT NULL DEFAULT '',
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(run_id) REFERENCES training_runs(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS training_authorized_people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    account_ref TEXT NOT NULL UNIQUE,
    display_name TEXT NOT NULL,
    access_level TEXT NOT NULL DEFAULT 'Training',
    approved_by TEXT NOT NULL DEFAULT 'Owner',
    active INTEGER NOT NULL DEFAULT 1,
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    revoked_at TEXT
);
"""


def connect() -> sqlite3.Connection:
    conn = sqlite3.connect(DB_PATH, check_same_thread=False)
    conn.row_factory = sqlite3.Row
    conn.execute("PRAGMA foreign_keys=ON")
    return conn


def init_db() -> None:
    with _LOCK, connect() as conn:
        conn.executescript(SCHEMA)
        conn.executescript(GUIDED_TRAINING_SCHEMA)
        # Lightweight migrations for prototypes created by earlier builds.
        message_columns = {r["name"] for r in conn.execute("PRAGMA table_info(messages)").fetchall()}
        if "story_date" not in message_columns:
            conn.execute("ALTER TABLE messages ADD COLUMN story_date TEXT")
        if "story_time" not in message_columns:
            conn.execute("ALTER TABLE messages ADD COLUMN story_time TEXT")
        if "speaker_name" not in message_columns:
            conn.execute("ALTER TABLE messages ADD COLUMN speaker_name TEXT")
        if "source_timestamp" not in message_columns:
            conn.execute("ALTER TABLE messages ADD COLUMN source_timestamp TEXT")

        run_columns = {r["name"] for r in conn.execute("PRAGMA table_info(training_runs)").fetchall()}
        if "model_key" not in run_columns:
            conn.execute("ALTER TABLE training_runs ADD COLUMN model_key TEXT NOT NULL DEFAULT 'RP-Core'")
        if "target_tier" not in run_columns:
            conn.execute("ALTER TABLE training_runs ADD COLUMN target_tier TEXT NOT NULL DEFAULT 'FREE'")
        if "queue_priority" not in run_columns:
            conn.execute("ALTER TABLE training_runs ADD COLUMN queue_priority INTEGER NOT NULL DEFAULT 100")
        if "requested_action" not in run_columns:
            conn.execute("ALTER TABLE training_runs ADD COLUMN requested_action TEXT NOT NULL DEFAULT ''")

        persona_columns = {r["name"] for r in conn.execute("PRAGMA table_info(personas)").fetchall()}
        if "image_url" not in persona_columns:
            conn.execute("ALTER TABLE personas ADD COLUMN image_url TEXT NOT NULL DEFAULT ''")

        memory_columns = {r["name"] for r in conn.execute("PRAGMA table_info(memories)").fetchall()}
        memory_migrations = {
            "what_happened": "TEXT NOT NULL DEFAULT ''",
            "who_remembers": "TEXT NOT NULL DEFAULT '[]'",
            "who_involved": "TEXT NOT NULL DEFAULT '[]'",
            "story_when": "TEXT NOT NULL DEFAULT ''",
            "story_where": "TEXT NOT NULL DEFAULT ''",
            "emotional_weight": "TEXT NOT NULL DEFAULT ''",
            "current_relevance": "TEXT NOT NULL DEFAULT 'active'",
            "source_kind": "TEXT NOT NULL DEFAULT 'User-created'",
            "original_context": "TEXT NOT NULL DEFAULT '{}'",
            "stable_key": "TEXT",
            "revision_history": "TEXT NOT NULL DEFAULT '[]'",
            "updated_at": "TEXT",
        }
        for col, spec in memory_migrations.items():
            if col not in memory_columns:
                conn.execute(f"ALTER TABLE memories ADD COLUMN {col} {spec}")
        conn.execute("UPDATE memories SET what_happened=content WHERE COALESCE(what_happened,'')=''")
        conn.execute("UPDATE memories SET source_kind='User-created' WHERE COALESCE(source_kind,'')=''")
        conn.execute("UPDATE memories SET updated_at=COALESCE(updated_at, created_at, CURRENT_TIMESTAMP)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_memories_conversation_relevance ON memories(conversation_id, active, current_relevance, id DESC)")
        conn.execute("CREATE INDEX IF NOT EXISTS idx_memories_stable_key ON memories(conversation_id, stable_key)")

        profile_columns = {r["name"] for r in conn.execute("PRAGMA table_info(user_profiles)").fetchall()}
        if "plan_expires_at" not in profile_columns:
            conn.execute("ALTER TABLE user_profiles ADD COLUMN plan_expires_at TEXT")
        if "plan_source" not in profile_columns:
            conn.execute("ALTER TABLE user_profiles ADD COLUMN plan_source TEXT NOT NULL DEFAULT ''")
        if "plan_previous_tier" not in profile_columns:
            conn.execute("ALTER TABLE user_profiles ADD COLUMN plan_previous_tier TEXT NOT NULL DEFAULT 'Free'")

        conn.execute("""
            CREATE TABLE IF NOT EXISTS owner_user_grants (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                user_id INTEGER NOT NULL,
                benefit_type TEXT NOT NULL CHECK(benefit_type IN ('Tier Access','Discount')),
                target_tier TEXT CHECK(target_tier IN ('Free','Plus','Pro','Ultra')),
                duration_days INTEGER NOT NULL DEFAULT 30,
                discount_percent REAL NOT NULL DEFAULT 0,
                starts_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                expires_at TEXT,
                active INTEGER NOT NULL DEFAULT 1,
                notes TEXT NOT NULL DEFAULT '',
                granted_by TEXT NOT NULL DEFAULT 'Owner',
                created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                updated_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY(user_id) REFERENCES user_profiles(id) ON DELETE CASCADE
            )
        """)
        conn.execute("CREATE INDEX IF NOT EXISTS idx_owner_user_grants_user ON owner_user_grants(user_id, active, id DESC)")

        row = conn.execute("SELECT COUNT(*) AS n FROM conversations").fetchone()
        if row["n"] == 0:
            cur = conn.execute(
                """
                INSERT INTO conversations(title, persona_name, scenario_name)
                VALUES (?, ?, ?)
                """,
                ("Prototype Test World", "Mariana", "Exes reunited / custom AU"),
            )
            cid = cur.lastrowid
            defaults = default_state_docs()
            for doc_type, payload in defaults.items():
                conn.execute(
                    "INSERT INTO state_docs(conversation_id, doc_type, payload) VALUES (?,?,?)",
                    (cid, doc_type, json.dumps(payload)),
                )

        # Customer build seeds only customer-facing starter content and entitlements.
        seed_public_discover_content(conn)
        refresh_official_starter_scenarios(conn)
        seed_featured_scenario_batch_one(conn)
        conn.executemany("INSERT OR IGNORE INTO generation_limit_settings(plan_tier,daily_total,daily_plus,daily_pro,daily_ultra) VALUES (?,?,?,?,?)",[("Free",500,None,None,None),("Plus",2000,None,None,None),("Pro",4000,None,None,None),("Ultra",5000,None,None,None)])

        seed_platform_shell(conn)

        # Merge new state fields into existing worlds without overwriting user state.
        scene_defaults = default_state_docs()["scene"]
        world_defaults = default_state_docs()["world_bible"]
        for conv in conn.execute("SELECT id FROM conversations").fetchall():
            cid = conv["id"]
            scene_row = conn.execute(
                "SELECT payload FROM state_docs WHERE conversation_id=? AND doc_type='scene'", (cid,)
            ).fetchone()
            if not scene_row:
                conn.execute(
                    "INSERT INTO state_docs(conversation_id, doc_type, payload) VALUES (?,?,?)",
                    (cid, "scene", json.dumps(scene_defaults)),
                )
            else:
                try:
                    scene = json.loads(scene_row["payload"])
                except Exception:
                    scene = {}
                changed = False
                for key, value in scene_defaults.items():
                    if key not in scene:
                        if key == "start_date" and scene.get("date"):
                            scene[key] = scene["date"]
                        elif key == "start_time" and scene.get("time"):
                            scene[key] = scene["time"]
                        else:
                            scene[key] = value
                        changed = True
                if changed:
                    conn.execute(
                        "UPDATE state_docs SET payload=?, updated_at=CURRENT_TIMESTAMP WHERE conversation_id=? AND doc_type='scene'",
                        (json.dumps(scene), cid),
                    )

            # Scene repair must not skip World Bible repair in the same startup pass.
            world_row = conn.execute(
                "SELECT payload FROM state_docs WHERE conversation_id=? AND doc_type='world_bible'", (cid,)
            ).fetchone()
            if not world_row:
                conn.execute(
                    "INSERT INTO state_docs(conversation_id, doc_type, payload) VALUES (?,?,?)",
                    (cid, "world_bible", json.dumps(world_defaults)),
                )
            else:
                try:
                    world = json.loads(world_row["payload"])
                except Exception:
                    world = {}
                world_changed = False

                # Normalize legacy World Bible shapes into the structured v4 schema
                # without removing the original keys. Old RPs may have stored the
                # overview at the root and used timeline/organizations/pets/vehicles.
                overview = world.get("overview") if isinstance(world.get("overview"), dict) else {}
                legacy_name = str(world.get("name") or world.get("title") or "").strip()
                legacy_description = str(world.get("description") or world.get("summary") or "").strip()
                if legacy_name and not str(overview.get("name") or "").strip():
                    overview["name"] = legacy_name
                    world_changed = True
                if legacy_description and not str(overview.get("description") or "").strip():
                    overview["description"] = legacy_description
                    world_changed = True
                if world.get("overview") != overview:
                    world["overview"] = overview
                    world_changed = True

                def _legacy_world_text(item):
                    if isinstance(item, str):
                        return item.strip()
                    if isinstance(item, dict):
                        for k in ("details", "fact", "event", "name", "title", "value", "description", "notes"):
                            v = item.get(k)
                            if isinstance(v, str) and v.strip():
                                return v.strip()
                    return str(item or "").strip()

                def _legacy_merge(src_key, dst_key, kind=None):
                    nonlocal world_changed
                    src = world.get(src_key)
                    if not isinstance(src, list) or not src:
                        return
                    dst = world.get(dst_key) if isinstance(world.get(dst_key), list) else []
                    seen = {_legacy_world_text(x).casefold() for x in dst if _legacy_world_text(x)}
                    for i, raw in enumerate(src):
                        text = _legacy_world_text(raw)
                        if not text or text.casefold() in seen:
                            continue
                        if isinstance(raw, dict):
                            item = dict(raw)
                            item.setdefault("source", "legacy-migrated")
                        else:
                            item = {"details": text, "source": "legacy-migrated"}
                        if kind and isinstance(item, dict):
                            item.setdefault("kind", kind)
                        if isinstance(item, dict):
                            safe = "".join(ch.lower() if ch.isalnum() else "-" for ch in text)
                            safe = "-".join(x for x in safe.split("-") if x)[:100] or str(i)
                            item.setdefault("stable_key", f"legacy:{src_key}:{safe}")
                        dst.append(item)
                        seen.add(text.casefold())
                        world_changed = True
                    world[dst_key] = dst

                _legacy_merge("timeline", "story_timeline", "legacy_timeline")
                _legacy_merge("organizations", "groups_factions", "organization")
                _legacy_merge("vehicles", "important_objects", "vehicle")
                _legacy_merge("pets", "custom_entries", "pet")

                for key, value in world_defaults.items():
                    if key not in world:
                        world[key] = value
                        world_changed = True
                world["schema_version"] = max(4, int(world.get("schema_version") or 0))
                if world_changed:
                    conn.execute(
                        "UPDATE state_docs SET payload=?, updated_at=CURRENT_TIMESTAMP WHERE conversation_id=? AND doc_type='world_bible'",
                        (json.dumps(world), cid),
                    )
        conn.commit()



def seed_platform_shell(conn: sqlite3.Connection) -> None:
    """Seed only harmless prototype shell data; never overwrite user edits."""
    if conn.execute("SELECT COUNT(*) AS n FROM user_profiles").fetchone()["n"] == 0:
        conn.executemany(
            "INSERT INTO user_profiles(handle, display_name, bio, plan_tier, is_creator) VALUES (?,?,?,?,?)",
            [
                ("you", "You", "Your MelumiyaChat profile. Edit this whenever you want.", "Free", 1),
                ("melumiyachat", "MelumiyaChat", "Official MelumiyaChat creator profile.", "Ultra", 1),
            ],
        )
    you = conn.execute("SELECT id FROM user_profiles WHERE handle='you'").fetchone()
    if you and conn.execute("SELECT COUNT(*) AS n FROM personas WHERE user_id=?", (you["id"],)).fetchone()["n"] == 0:
        conn.execute(
            "INSERT INTO personas(user_id,name,pronouns,notes,is_default) VALUES (?,?,?,?,1)",
            (you["id"], "Mariana", "", "Default prototype persona. You can edit or delete it.")
        )
    if conn.execute("SELECT COUNT(*) AS n FROM community_posts").fetchone()["n"] == 0:
        conn.executemany(
            """INSERT INTO community_posts(post_type,title,body,status,created_by,published_at)
               VALUES (?,?,?,?,?,CURRENT_TIMESTAMP)""",
            [
                ("News", "Welcome to MelumiyaChat", "This Home feed is where official news, updates and community posts will appear.", "Published", "Owner"),
                ("Challenge", "Continuity Challenge", "Try a long RP scene with a secret, a time skip and multiple characters. See whether the world stays consistent.", "Published", "Owner"),
            ],
        )


def default_state_docs() -> dict[str, Any]:
    return {
        "hard_canon": {
            "relationship_status": "Custom scenario — edit me",
            "source_canon_mode": "Selective Canon",
            "rp_year": 2026,
            "notes": [
                "User-established facts outrank model inference.",
                "Major irreversible events require user approval.",
            ],
        },
        "relationship": {
            "phase": "Scenario-defined",
            "trust": "Balanced / scenario-dependent",
            "affection": "Scenario-dependent",
            "unresolved_hurt": [],
            "habits": [],
            "triggers": [],
        },
        "knowledge": {
            "facts": [],
            "golden_rule": "World knowledge does not equal character knowledge.",
        },
        "scene": {
            "start_date": "2026-08-13",
            "start_time": "19:00",
            "date": "2026-08-13",
            "time": "19:00",
            "story_started": False,
            "turn_index": 0,
            "last_advance_reason": "Story has not started yet.",
            "time_policy": "Conservative story clock: explicit time skips win; ordinary turns advance a little.",
            "location": "Unset",
            "present": [],
            "offscreen": [],
            "controlled_character": "Mariana",
            "threads": [],
        },
        "physical": {
            "characters": {},
            "objects": {},
            "continuity_notes": [],
        },
        "world_bible": {
            "overview": {"name": "", "description": ""},
            "history_backstory": [],
            "background_facts": [],
            "characters": [],
            "relationship_history": [],
            "core_canon": [],
            "locations": [],
            "secrets_knowledge": [],
            "groups_factions": [],
            "important_objects": [],
            "world_rules": [],
            "current_state": [],
            "physical_continuity": [],
            "story_timeline": [],
            "custom_entries": [],
            "auto_review_conflicts": [],
            "schema_version": 4,
            # Legacy keys remain readable so existing RPs upgrade without losing data.
            "pets": [],
            "vehicles": [],
            "organizations": [],
            "timeline": [],
            "research": [],
            "import_history": [],
            "import_review": [],
        },
    }







def seed_public_discover_content(conn: sqlite3.Connection) -> None:
    """Ensure a curated MelumiyaChat Originals starter library exists.

    The seed is intentionally idempotent: updating an older prototype adds only
    missing official starter items and never overwrites creator/user content.
    All launch starters are original characters/scenarios owned by MelumiyaChat;
    established third-party canon characters should come through the canon import
    and creator-review workflow instead of being silently bundled here.
    """

    def add_if_missing(row: tuple[Any, ...]) -> None:
        item_type, title = row[0], row[1]
        exists = conn.execute(
            "SELECT id FROM creator_content WHERE item_type=? AND lower(title)=lower(?) LIMIT 1",
            (item_type, title),
        ).fetchone()
        if not exists:
            conn.execute(
                """INSERT INTO creator_content(
                    item_type,title,creator_name,parent_character,summary,visibility,content_rating,canon_mode,
                    source_name,status,version,live_version,tags_json,payload_json,review_notes
                ) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
                row,
            )

    # Reusable MelumiyaChat Originals. The roster deliberately spans common RP
    # lanes so Discover does not launch as three variations of the same romance.
    characters = [
        (
            "character", "Elias Vale", "MelumiyaChat", None,
            "Reserved tactical-team veteran with dry humor, loyalty, and a noticeably softer side with people he trusts.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Military", "Reserved", "Protective"]),
            json.dumps({"personality":["reserved","dry humor","loyal","protective"],"occupation":"tactical-team veteran","public_private_profile":"professional in public; warmer with trusted people","world_rules":["never speak for user","emotion is allowed","professionalism is context-sensitive"]}), "",
        ),
        (
            "character", "Rowan Ash", "MelumiyaChat", None,
            "Warm but stubborn fantasy traveler built for slow-burn relationships, banter, and persistent world adventures.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Fantasy", "Slow Burn", "Playful"]),
            json.dumps({"personality":["warm","stubborn","playful","adventurous"],"occupation":"traveler and mapmaker","world_rules":["never speak for user","relationships evolve gradually"]}), "",
        ),
        (
            "character", "Maya Chen", "MelumiyaChat", None,
            "Contemporary character designed for grounded friendship, romance, work-life scenes and natural group conversations.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Modern", "Friends", "Romance"]),
            json.dumps({"personality":["witty","grounded","observant","affectionate"],"occupation":"architect","world_rules":["never speak for user","side characters keep their own knowledge"]}), "",
        ),
        (
            "character", "Lucien Marrow", "MelumiyaChat", None,
            "A feared young duke whose intimidating reputation hides patience, restraint, dry wit and a fierce sense of responsibility.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Fantasy", "Royalty", "Slow Burn", "Arranged Marriage"]),
            json.dumps({"personality":["reserved","strategic","honorable","dry humor","quietly tender"],"occupation":"Duke of Ravenmere","relationship_style":"slow trust; affection shown through actions","world_rules":["never speak for user","dominant does not mean abusive","politics never erase relationship history"]}), "",
        ),
        (
            "character", "Sera Voss", "MelumiyaChat", None,
            "Disciplined field commander and competitive rival: sharp, competent, funny when relaxed, and terrible at admitting she is worried about someone.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Military", "Rivals", "Action", "Female"]),
            json.dumps({"personality":["disciplined","competitive","protective","sarcastic","emotionally guarded"],"occupation":"field commander","world_rules":["never speak for user","competence does not erase humor","conflict stays proportional"]}), "",
        ),
        (
            "character", "Viktor Soren", "MelumiyaChat", None,
            "Low-key close-protection specialist who notices everything, talks only when he has something useful to say, and hates being treated like furniture.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Bodyguard", "Modern", "Protective", "Slow Burn"]),
            json.dumps({"personality":["observant","calm","wry","protective","independent"],"occupation":"close-protection specialist","world_rules":["never speak for user","protective is not possessive by default","security concerns stay proportional"]}), "",
        ),
        (
            "character", "Theo Hart", "MelumiyaChat", None,
            "Single father, paramedic and former best friend: warm, practical, tired in a very human way, and deeply invested in the people he calls family.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Family", "Single Parent", "Second Chance", "Modern"]),
            json.dumps({"personality":["warm","practical","teasing","responsible","emotionally open"],"occupation":"paramedic","family":{"child":"Avery, age 7"},"world_rules":["never speak for user","children are first-class story entities","boring family life is allowed"]}), "",
        ),
        (
            "character", "Jamie Park", "MelumiyaChat", None,
            "Ambitious product designer and workplace rival with quick banter, strong boundaries and a habit of turning every harmless challenge into a competition.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Workplace", "Rivals", "Modern", "Nonbinary"]),
            json.dumps({"pronouns":"they/them","personality":["ambitious","quick-witted","competitive","fair","secretly sentimental"],"occupation":"product designer","world_rules":["never speak for user","workplace rivalry is not hostility by default"]}), "",
        ),
        (
            "character", "Nadia Veyr", "MelumiyaChat", None,
            "An old vampire archivist trying very hard to live a quiet modern life, with centuries of knowledge, deadpan humor and surprisingly ordinary domestic habits.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Vampire", "Supernatural", "Modern Fantasy", "Female"]),
            json.dumps({"personality":["deadpan","curious","patient","private","domestically awkward"],"occupation":"night archivist","species":"vampire","world_rules":["never speak for user","supernatural powers have limits","secrets stay local to characters who know them"]}), "",
        ),
        (
            "character", "Cassia Bell", "MelumiyaChat", None,
            "Owner of a tiny neighborhood witch shop: clever, warm, chaotic with paperwork, excellent with magic and deeply serious about promises.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Witch", "Cozy", "Fantasy", "Female"]),
            json.dumps({"personality":["warm","clever","mischievous","loyal","chaotic"],"occupation":"witch-shop owner","world_rules":["never speak for user","magic has costs and continuity","cozy scenes do not require forced drama"]}), "",
        ),
        (
            "character", "Ash Kade", "MelumiyaChat", None,
            "Resourceful survivor and reluctant community leader who can repair almost anything except their sleep schedule.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Original", "Post-Apocalyptic", "Found Family", "Adventure", "Nonbinary"]),
            json.dumps({"pronouns":"they/them","personality":["resourceful","protective","skeptical","funny under pressure","community-minded"],"occupation":"mechanic and settlement coordinator","world_rules":["never speak for user","scarcity and injuries persist","major irreversible events require user approval"]}), "",
        ),
    ]
    for row in characters:
        add_if_missing(row)

    # A balanced starter shelf: relationship-led, world-led and group scenarios.
    # Everything is SFW at the catalogue level; adult users can later choose
    # adult settings in eligible RPs without making the official starter shelf 18+.
    scenarios = [
        (
            "scenario", "Exes Reunited — Three Years Later", "MelumiyaChat", "Elias Vale",
            "You were together for years, separated after a betrayal, and have not seen each other for three years. The reunion starts now — reconciliation is never automatic.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Exes", "Reunion", "Slow Burn", "Modern", "Angst"]),
            json.dumps({"relationship_start":"exes / separated three years","history_anchors":["long shared history","three years apart"],"major_event_policy":"user approval","tone":"emotional slow burn","opening":"The separated partners encounter each other again after three years."}), "",
        ),
        (
            "scenario", "Quiet Sunday at Home", "MelumiyaChat", "Maya Chen",
            "A long-term married domestic RP where ordinary family life is allowed to stay ordinary: breakfast, kids, chores, jokes, arguments and affection without forced drama.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Married", "Family", "Cozy", "Slice of Life", "Modern"]),
            json.dumps({"relationship_start":"long-term marriage","tone":"cozy family life","drama_injection":"off","major_event_policy":"user approval"}), "",
        ),
        (
            "scenario", "High-School Sweethearts — Years Later", "MelumiyaChat", "Rowan Ash",
            "Two adults reconnect and gradually rebuild a shared teenage history through present-day conversation, with era-correct flashbacks and no present-day characters leaking into the past.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Reunion", "High School", "Backstory", "Slow Burn", "AU"]),
            json.dumps({"relationship_start":"former high-school sweethearts, now adults","history_anchors":["dated in high school"],"historical_cast_lock":True,"unknowns_stay_unknown":True,"current_cast_adults":True}), "",
        ),
        (
            "scenario", "Everyone Lives — Tactical Team AU", "MelumiyaChat", "Elias Vale",
            "A tactical-team AU with the full team alive. Keep military camaraderie, jokes, work mode and downtime personality without turning everyone into grim robots.",
            "Public", "SFW", "Selective Canon", "Original", "Approved", 1, 1,
            json.dumps(["Military", "AU", "Everyone Lives", "Action", "Team"]),
            json.dumps({"preset":"Everyone Lives","canon_events_disabled":["canon deaths"],"tone":"action + camaraderie","major_event_policy":"user approval"}), "",
        ),
        (
            "scenario", "Friends to Something More", "MelumiyaChat", "Maya Chen",
            "You already know each other well. Let attraction and relationship changes emerge gradually without instant romance or repetitive flirting.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Friends", "Slow Burn", "Romance", "Modern", "Cozy"]),
            json.dumps({"relationship_start":"close friends","tone":"slow-burn romance","relationship_realism":"Balanced"}), "",
        ),
        (
            "scenario", "Fantasy Road Trip", "MelumiyaChat", "Rowan Ash",
            "A persistent fantasy journey with recurring towns, companions, secrets, injuries, gear and world-state continuity across a long adventure.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Fantasy", "Adventure", "Friends", "Slow Burn", "Worldbuilding"]),
            json.dumps({"relationship_start":"travel companions","tone":"adventure","world_persistence":True,"major_event_policy":"user approval"}), "",
        ),
        (
            "scenario", "Arranged Marriage to the Feared Duke", "MelumiyaChat", "Lucien Marrow",
            "A political marriage begins between two adults who barely know one another. The court expects obedience and spectacle; the relationship itself must be built slowly and on their own terms.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Arranged Marriage", "Royalty", "Fantasy", "Slow Burn", "Romance"]),
            json.dumps({"relationship_start":"newly arranged marriage","tone":"court intrigue + slow trust","major_event_policy":"user approval","relationship_rules":["marriage does not equal automatic intimacy","respect and affection develop separately"],"opening":"The marriage contract has just been signed; the first private conversation begins after the public ceremony."}), "",
        ),
        (
            "scenario", "Rivals on the Same Mission", "MelumiyaChat", "Sera Voss",
            "Two highly competent rivals are ordered onto the same high-risk operation. Winning the argument matters less than getting everyone home alive — even if neither wants to admit they work well together.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Enemies", "Rivals", "Military", "Action", "Slow Burn"]),
            json.dumps({"relationship_start":"professional rivals","tone":"action + banter + gradual trust","major_event_policy":"user approval","group_scene_mode":"selective response","opening":"A briefing assigns the two rivals to the same mission team."}), "",
        ),
        (
            "scenario", "Bodyguard on Tour", "MelumiyaChat", "Viktor Soren",
            "A public figure and their newly assigned protection specialist spend weeks crossing cities together. Cameras, crowds and schedules create pressure; familiarity grows in the quiet spaces between them.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Bodyguard", "Celebrity", "Modern", "Slow Burn", "Travel"]),
            json.dumps({"relationship_start":"client and bodyguard","tone":"modern slow burn","public_private_behavior":True,"major_event_policy":"user approval","opening":"The first tour day starts with a security briefing and a packed departure schedule."}), "",
        ),
        (
            "scenario", "Single Dad, Old Friend, Second Chance", "MelumiyaChat", "Theo Hart",
            "You knew Theo before life got complicated. Years later, you meet again with jobs, routines and a seven-year-old child now part of the picture. Nothing has to become romantic unless the story earns it.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Second Chance", "Family", "Friends", "Modern", "Slow Burn"]),
            json.dumps({"relationship_start":"old friends reconnecting","family_state":{"child":{"name":"Avery","age":7}},"tone":"grounded family life","major_event_policy":"user approval","opening":"A chance meeting reconnects two old friends after years apart."}), "",
        ),
        (
            "scenario", "Workplace Rivals — Same Promotion", "MelumiyaChat", "Jamie Park",
            "Two coworkers want the same promotion and genuinely respect each other's talent, which is exactly why the rivalry is so irritating. Projects, deadlines, office friendships and after-hours life continue around them.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Workplace", "Rivals", "Modern", "Comedy", "Slow Burn"]),
            json.dumps({"relationship_start":"friendly workplace rivals","tone":"banter + realistic work life","major_event_policy":"user approval","opening":"Management announces that one lead role will open at the end of the quarter."}), "",
        ),
        (
            "scenario", "The Vampire Next Door", "MelumiyaChat", "Nadia Veyr",
            "The new neighbor keeps strange hours, has antique furniture and somehow knows the history of every building on the street. A modern supernatural RP where discovery can stay funny, eerie or romantic.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Vampire", "Supernatural", "Modern Fantasy", "Neighbors", "Mystery"]),
            json.dumps({"relationship_start":"new neighbors","tone":"cozy supernatural mystery","secret_scope":{"Nadia is a vampire":"Nadia only at start"},"major_event_policy":"user approval","opening":"A misdelivered parcel brings the two neighbors face to face for the first time."}), "",
        ),
        (
            "scenario", "The Little Witch Shop on Bell Street", "MelumiyaChat", "Cassia Bell",
            "A cozy neighborhood magic shop with regular customers, tiny disasters, strange deliveries and recurring local characters. The world can remain gentle for as long as you want.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Cozy", "Witch", "Fantasy", "Slice of Life", "Worldbuilding"]),
            json.dumps({"relationship_start":"open / user-defined","tone":"cozy magical slice of life","drama_injection":"off","world_persistence":True,"major_event_policy":"user approval","opening":"The bell over the shop door rings just as Cassia realizes a supposedly harmless charm has escaped its jar."}), "",
        ),
        (
            "scenario", "Fake Date for the Wedding", "MelumiyaChat", "Maya Chen",
            "A simple favor — pretend to be a date for one wedding weekend — becomes complicated by shared history, nosy relatives and the fact that the fake relationship has to look believable.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Fake Dating", "Friends", "Romance", "Comedy", "Modern"]),
            json.dumps({"relationship_start":"friends pretending to date","tone":"romantic comedy + gradual feelings","major_event_policy":"user approval","opening":"The pair arrives at the wedding hotel and realizes the family has already assumed they are serious."}), "",
        ),
        (
            "scenario", "Snowed In at the Mountain Lodge", "MelumiyaChat", None,
            "Character-flexible scenario: a storm closes the only road out of a small mountain lodge. Use any original character, established-history pair or group and let the isolation reveal what is already there.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Snowed In", "Cozy", "Slow Burn", "Group", "Character Flexible"]),
            json.dumps({"relationship_start":"user-defined","tone":"cozy / tense / romantic — user-led","character_flexible":True,"major_event_policy":"user approval","opening":"Heavy snow closes the mountain road overnight; the lodge staff confirms nobody is leaving until the plows arrive."}), "",
        ),
        (
            "scenario", "Shared Apartment Chaos", "MelumiyaChat", None,
            "Character-flexible group RP about roommates, friends, partners and visitors sharing one apartment. Side conversations, chores, late-night food runs and ridiculous household politics all persist.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Roommates", "Group", "Comedy", "Slice of Life", "Character Flexible"]),
            json.dumps({"relationship_start":"user-defined household","tone":"group comedy / slice of life","character_flexible":True,"group_scene_mode":"selective response","world_persistence":True,"major_event_policy":"user approval","opening":"Someone has left a passive-aggressive note on the fridge, and nobody is admitting who wrote it."}), "",
        ),
        (
            "scenario", "Found Family at the End of the World", "MelumiyaChat", "Ash Kade",
            "A small survivor settlement is becoming a home. Supplies, injuries and danger matter, but so do meals, jokes, grief, repairs, celebrations and the slow creation of a found family.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Post-Apocalyptic", "Found Family", "Adventure", "Group", "Worldbuilding"]),
            json.dumps({"relationship_start":"survivors / settlement members","tone":"survival + found family","world_persistence":True,"resource_continuity":True,"major_event_policy":"user approval","opening":"A supply run returns just before dark with one unexpected extra passenger."}), "",
        ),
        (
            "scenario", "Royal Court Intrigue", "MelumiyaChat", None,
            "Character-flexible political fantasy with nobles, guards, servants, diplomats and rivals who each know different pieces of the truth. Secrets stay with the people who actually learned them.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Royalty", "Fantasy", "Drama", "Multiple Characters", "Character Flexible"]),
            json.dumps({"relationship_start":"user-defined","tone":"political fantasy","character_flexible":True,"knowledge_graph":"strict","group_scene_mode":"selective response","major_event_policy":"user approval","opening":"A royal banquet begins hours after a sealed diplomatic letter disappears from the council chamber."}), "",
        ),
        (
            "scenario", "Arcane University — Adult Academy", "MelumiyaChat", None,
            "A character-flexible magic-academy RP where every student in the core cast is an adult (20+). Classes, rival houses, friendships, research, duels and campus life can grow into a persistent world.",
            "Public", "SFW", "Full AU", "Original", "Approved", 1, 1,
            json.dumps(["Magic Academy", "Fantasy", "Friends", "Rivals", "Character Flexible"]),
            json.dumps({"relationship_start":"user-defined","tone":"academy adventure","character_flexible":True,"age_rule":"all core student characters are adults age 20+","world_persistence":True,"major_event_policy":"user approval","opening":"Orientation week begins with a practical spell assessment that goes slightly off-script."}), "",
        ),
    ]
    for row in scenarios:
        add_if_missing(row)

def refresh_official_starter_scenarios(conn: sqlite3.Connection) -> None:
    """Refresh official MelumiyaChat starter scenarios with improved hook-style copy.

    This updates the public starter shelf in-place so existing prototype databases
    gain the newer scenario hooks and authored opening prompts without needing a
    full reset.
    """
    for title, spec in OFFICIAL_SCENARIO_REFRESH.items():
        row = conn.execute(
            "SELECT id, parent_character, tags_json, payload_json FROM creator_content WHERE item_type='scenario' AND creator_name='MelumiyaChat' AND lower(title)=lower(?) ORDER BY id DESC LIMIT 1",
            (title,),
        ).fetchone()
        if not row:
            continue
        try:
            payload = json.loads(row["payload_json"] or "{}")
        except Exception:
            payload = {}
        image_url = payload.get("image_url", "")
        payload.update(spec.get("payload") or {})
        if image_url and not payload.get("image_url"):
            payload["image_url"] = image_url
        tags = json.dumps(spec.get("tags") or json.loads(row["tags_json"] or "[]"))
        conn.execute(
            "UPDATE creator_content SET summary=?, tags_json=?, payload_json=?, updated_at=CURRENT_TIMESTAMP WHERE id=?",
            (spec.get("summary") or title, tags, json.dumps(payload, ensure_ascii=False), row["id"]),
        )




def seed_featured_scenario_batch_one(conn: sqlite3.Connection) -> None:
    """Add the first ten richer scenario-library entries requested for MelumiyaChat.

    Original characters are first-party MelumiyaChat creations. Canon characters
    use their established names and keep source/provenance metadata in payload so
    the Owner can review them before any real commercial release.
    """
    def upsert_content(item_type: str, title: str, creator_name: str, parent_character: str | None,
                       summary: str, visibility: str, content_rating: str, canon_mode: str,
                       source_name: str, status: str, tags: list[str], payload: dict[str, Any]) -> None:
        row = conn.execute(
            "SELECT id FROM creator_content WHERE item_type=? AND lower(title)=lower(?) AND creator_name=? ORDER BY id DESC LIMIT 1",
            (item_type, title, creator_name),
        ).fetchone()
        if row:
            conn.execute(
                """UPDATE creator_content SET parent_character=?, summary=?, visibility=?, content_rating=?, canon_mode=?,
                   source_name=?, status=?, tags_json=?, payload_json=?, updated_at=CURRENT_TIMESTAMP WHERE id=?""",
                (parent_character, summary, visibility, content_rating, canon_mode, source_name, status,
                 json.dumps(tags, ensure_ascii=False), json.dumps(payload, ensure_ascii=False), row["id"]),
            )
            return
        conn.execute(
            """INSERT INTO creator_content(item_type,title,creator_name,parent_character,summary,visibility,content_rating,
               canon_mode,source_name,status,version,live_version,tags_json,payload_json,review_notes)
               VALUES (?,?,?,?,?,?,?,?,?,?,1,1,?,?,?)""",
            (item_type, title, creator_name, parent_character, summary, visibility, content_rating, canon_mode,
             source_name, status, json.dumps(tags, ensure_ascii=False), json.dumps(payload, ensure_ascii=False), ""),
        )

    characters = [
        ("Marco Vescari", "Original", ["Original","Mafia","Protective","Slow Burn"], {
            "personality":["controlled","strategic","protective","dry humor","family-loyal"],
            "occupation":"head of the Vescari crime family",
            "world_rules":["never speak for the user","protective behavior is not automatic ownership","conflict stays proportional"],
            "image_url":"/generated-images/cover_mafia.jpg",
            "rights_status":"MelumiyaChat Original"
        }, "A disciplined mafia heir who treats loyalty as a promise, not a performance. Dangerous in his world, careful with the people he actually loves."),
        ("Ryder Hale", "Original", ["Original","Biker","Motorcycle","Protective","Modern"], {
            "personality":["blunt","funny","restless","loyal","soft with trusted people"],
            "occupation":"motorcycle mechanic and club road captain",
            "world_rules":["never speak for the user","biker does not mean abusive","club life has persistent relationships and consequences"],
            "image_url":"/generated-images/cover_bike.jpg",
            "rights_status":"MelumiyaChat Original"
        }, "A motorcycle mechanic and road captain with grease under his nails, a sharp mouth, and a habit of showing care through practical things rather than speeches."),
        ("Silas Crowe", "Original", ["Original","Masked","Mystery","Slow Burn","Protective"], {
            "personality":["watchful","restrained","wry","secretive","unexpectedly gentle"],
            "occupation":"private security specialist",
            "mask_rule":"the mask is situational equipment and identity protection, not his entire personality",
            "world_rules":["never speak for the user","do not force identity reveals","mystery does not excuse cruelty"],
            "image_url":"/generated-images/cover_masked.jpg",
            "rights_status":"MelumiyaChat Original"
        }, "A private security specialist known for working behind a mask when anonymity matters. Quiet, observant and far more human than the reputation built around him."),
        ("Alexander Thorne", "Original", ["Original","Billionaire","CEO","Modern","Slow Burn"], {
            "personality":["precise","ambitious","private","witty","responsible"],
            "occupation":"founder and CEO of Thorne Meridian",
            "world_rules":["never speak for the user","wealth does not create consent","staff and work relationships persist beyond romance"],
            "image_url":"/generated-images/cover_billionaire.jpg",
            "rights_status":"MelumiyaChat Original"
        }, "A self-made billionaire CEO who can run a boardroom without blinking and still has no idea what to do when someone refuses to be impressed by him."),
        ("Keegan P. Russ", "Call of Duty: Ghosts", ["Canon","Call of Duty","Ghosts","Military","Keegan"], {
            "source_identity":"Keegan P. Russ",
            "source_url":"https://callofduty.fandom.com/wiki/Keegan_P._Russ",
            "source_note":"Name and Ghosts affiliation are canon reference facts; scenario prose is original fan/AU content.",
            "rights_status":"Third-party canon reference — Owner legal review before commercial distribution",
            "world_rules":["never speak for the user","keep military competence without emotional flattening","do not invent canon facts as confirmed"]
        }, "Keegan P. Russ from Call of Duty: Ghosts, used here as a canon-referenced roleplay character with original scenario writing."),
        ('Simon "Ghost" Riley', "Call of Duty: Modern Warfare", ["Canon","Call of Duty","Modern Warfare","Task Force 141","Military"], {
            "source_identity":"Simon ‘Ghost’ Riley",
            "source_url":"https://www.callofduty.com/blog/2022/05/call-of-duty-modern-warfare-ii-ghost-price-task-force-141",
            "source_note":"Official Call of Duty material identifies Simon ‘Ghost’ Riley as a Task Force 141 member.",
            "rights_status":"Third-party canon reference — Owner legal review before commercial distribution",
            "world_rules":["never speak for the user","masked/stoic does not mean emotionless or abusive","keep knowledge scoped to the scene"]
        }, "Simon ‘Ghost’ Riley from the modern Call of Duty continuity, used as a canon-referenced character with original RP scenarios."),
        ("Draco Malfoy", "Harry Potter", ["Canon","Harry Potter","Draco Malfoy","Slytherin","Adult AU"], {
            "source_identity":"Draco Malfoy",
            "source_url":"https://www.harrypotter.com/fact-file/characters-and-pets/draco-malfoy",
            "source_note":"Official Harry Potter reference identifies Draco Malfoy and his Slytherin background. This scenario uses an adult post-school setting.",
            "rights_status":"Third-party canon reference — Owner legal review before commercial distribution",
            "age_rule":"Adult post-Hogwarts portrayal only in this seeded romance scenario",
            "world_rules":["never speak for the user","do not copy book dialogue","adult post-school setting"]
        }, "Adult, post-Hogwarts Draco Malfoy in an original fan-scenario framework grounded in broad canon facts rather than copied text."),
        ("Edward Cullen", "Twilight", ["Canon","Twilight","Edward Cullen","Vampire","SFW Romance"], {
            "source_identity":"Edward Cullen",
            "source_url":"https://stepheniemeyer.com/project/twilight-book/",
            "source_note":"Stephenie Meyer’s official site identifies Edward Cullen as the mysterious vampire Bella meets in Forks.",
            "rights_status":"Third-party canon reference — Owner legal review before commercial distribution",
            "content_rule":"Seeded scenarios remain SFW/non-explicit because the character is canonically physically seventeen.",
            "world_rules":["never speak for the user","no explicit sexual content","do not copy novel dialogue"]
        }, "Edward Cullen from Twilight in a non-explicit, canon-referenced romance scenario using original prose and a user-defined role."),
        ("Spencer Reid", "Criminal Minds", ["Canon","Criminal Minds","Spencer Reid","BAU","FBI"], {
            "source_identity":"Dr. Spencer Reid",
            "source_url":"https://www.paramountplus.com/shows/video/6F819190-98A8-E002-1053-0B80F2B99D23/",
            "source_note":"Paramount’s Criminal Minds material identifies Dr. Spencer Reid.",
            "rights_status":"Third-party canon reference — Owner legal review before commercial distribution",
            "world_rules":["never speak for the user","professional competence coexists with ordinary emotion","do not invent case facts as canon"]
        }, "Dr. Spencer Reid from Criminal Minds, used as a canon-referenced character in original BAU and relationship scenarios."),
        ("Xaden Riorson", "The Empyrean", ["Canon","Rebecca Yarros","Fourth Wing","Xaden Riorson","Dragon Rider"], {
            "source_identity":"Xaden Riorson",
            "source_url":"https://rebeccayarros.com/empyrean",
            "source_note":"Rebecca Yarros’s official Empyrean page identifies Xaden Riorson as a powerful wingleader in the Riders Quadrant.",
            "rights_status":"Third-party canon reference — Owner legal review before commercial distribution",
            "world_rules":["never speak for the user","do not reproduce copyrighted passages","keep scenario facts separate from source canon when AU"]
        }, "Xaden Riorson from The Empyrean series in an original scenario built around the Riders Quadrant setting and relationship-driven RP."),
    ]
    for name, source, tags, payload, summary in characters:
        upsert_content("character", name, "MelumiyaChat", None, summary, "Public", "SFW",
                       "Full AU" if source == "Original" else "Canon-Inspired", source, "Approved", tags, payload)

    scenarios = [
        {
            "title":"The Don's Unexpected Bride", "parent":"Marco Vescari", "source":"Original", "canon":"Full AU", "image":"cover_mafia.jpg",
            "tags":["Original","Mafia Romance","Arranged Marriage","Slow Burn","Protective","Adult"],
            "hook":"The marriage was meant to end a war. Nobody planned for either of you to matter to the other.",
            "summary":"A peace agreement between two rival families leaves you legally married to Marco Vescari, a man you barely know. The wedding is public; whatever happens after it is yours to decide.",
            "setup":"Two adult crime families have negotiated a marriage to stop escalating violence. Marco accepted because the alternative was more bloodshed, not because he expects obedience or instant intimacy. The user enters the marriage with their own motives and full agency. Both families are watching for weakness, while Marco and the user have never had a truly private conversation before tonight.",
            "opening":"The last guest finally disappeared behind the ballroom doors, taking the music and the watching eyes with them. For the first time all day, the suite was quiet. Marco loosened his cuff with deliberate care, then stopped several feet away instead of approaching. The ring on his hand looked as unfamiliar on him as yours probably felt on you.\n\n‘Before anyone else decides what this marriage is supposed to mean,’ he said, voice low and even, ‘we should decide what it actually means to us.’\n\nOutside the windows, two families were celebrating a truce. Inside, the first real negotiation of the marriage had only just begun.",
            "relationship":"newly married strangers / political alliance",
            "scene":{"location":"Private suite above the Vescari family hotel","date_or_era":"Present day","time":"Late evening","present_cast":["Marco Vescari"],"situation":"The wedding reception has just ended and the newly married pair are alone for the first time."},
            "facts":["Both spouses are adults.","The marriage was negotiated to stop a conflict between families.","Marriage does not imply consent or automatic intimacy."],
            "history":["Weeks before the RP — both families negotiated the truce and marriage.","Today — Marco Vescari and the user completed the public wedding ceremony."],
        },
        {
            "title":"One Helmet, One Ride Home", "parent":"Ryder Hale", "source":"Original", "canon":"Full AU", "image":"cover_bike.jpg",
            "tags":["Original","Bike Romance","Biker","Motorcycle","Friends to Lovers","Slow Burn","Adult"],
            "hook":"Your ride disappears. Ryder has one spare helmet and absolutely no intention of leaving you stranded.",
            "summary":"A late-night breakdown leaves you outside Ryder Hale’s motorcycle shop after closing. He offers you a ride home, turning an ordinary favor into a night neither of you can quite treat as ordinary afterward.",
            "setup":"Ryder and the user already know each other through the neighborhood, shop or mutual friends. Attraction can exist but is not confirmed unless the RP earns it. Ryder is a motorcycle mechanic and club road captain; the club is a recurring social network, not a shorthand for criminality or abuse.",
            "opening":"The shop lights were already half off when your car made the kind of noise that meant it had officially given up on dignity. Ten minutes later, Ryder stood beside the open hood, one hand braced against the frame and the other holding a flashlight between his teeth.\n\nHe finally straightened, pulled the light free, and gave you a look that was far too amused for the situation. ‘Good news: it can be fixed.’ A beat. ‘Bad news: not tonight.’\n\nHe nodded toward the motorcycle parked under the awning, rain beginning to stipple the pavement around it. A second helmet hung from the handlebar.\n\n‘I can call you a cab,’ he said. ‘Or you can trust me for twenty minutes.’",
            "relationship":"friends / familiar acquaintances with unresolved attraction",
            "scene":{"location":"Hale Motorworks","date_or_era":"Present day","time":"Late night","present_cast":["Ryder Hale"],"situation":"The user's car has broken down after the shop closed; Ryder offers a motorcycle ride home."},
            "facts":["Both characters are adults.","Ryder owns or works at Hale Motorworks.","The user's vehicle cannot be repaired until the next day."],
            "history":["Before the RP — Ryder and the user have known each other long enough for easy banter."],
        },
        {
            "title":"The Man Behind the Mask", "parent":"Silas Crowe", "source":"Original", "canon":"Full AU", "image":"cover_masked.jpg",
            "tags":["Original","Masked Romance","Secret Identity","Bodyguard","Slow Burn","Mystery","Adult"],
            "hook":"You know his voice, his habits and the way he protects you. You have never seen his face.",
            "summary":"After weeks under anonymous protection, you finally get a quiet night with Silas Crowe — the masked security specialist assigned to keep you alive. The danger is real, but so is the person behind the equipment.",
            "setup":"Silas has been assigned to protect the adult user during an ongoing threat investigation. He uses a mask during operations because his identity is deliberately kept off public records. The user does not automatically know why. He has worked closely enough with the user for routines and trust to form, but the relationship is still professionally complicated.",
            "opening":"The safe apartment had been silent for almost an hour when Silas finally locked the last window and lowered the radio volume. The mask stayed on. It always did while he was working.\n\nTonight, though, he did something unusual: he sat across from you instead of taking up his usual position near the door.\n\n‘You keep looking at it,’ he said. There was no accusation in his voice, only dry amusement. One gloved finger tapped the edge of the mask. ‘You can ask. Doesn't mean I'll answer, but you can ask.’\n\nFor once, there was no immediate emergency to interrupt the conversation.",
            "relationship":"protected person / security specialist; growing trust",
            "scene":{"location":"Secure apartment","date_or_era":"Present day","time":"Night","present_cast":["Silas Crowe"],"situation":"A quiet night during an active protection detail creates room for a personal conversation."},
            "facts":["Both characters are adults.","Silas uses a mask for operational anonymity.","The user is under temporary protection because of a real but not immediately present threat."],
            "history":["Several weeks before the RP — Silas was assigned to the user's protection detail."],
        },
        {
            "title":"The Billionaire's Contract", "parent":"Alexander Thorne", "source":"Original", "canon":"Full AU", "image":"cover_billionaire.jpg",
            "tags":["Original","Billionaire Romance","CEO","Fake Dating","Contract","Slow Burn","Adult"],
            "hook":"He needs a convincing partner for one month. You are the only person in the room willing to tell him no.",
            "summary":"Alexander Thorne proposes a strictly defined public relationship to solve a corporate problem. The contract has boundaries, an end date and one flaw: neither of you is as emotionally detached as the paperwork assumes.",
            "setup":"Alexander is the adult founder and CEO of Thorne Meridian. A high-profile acquisition has made his private life part of unwanted media attention. He asks the adult user, someone he already trusts professionally or personally, to consider a one-month fake relationship. The agreement can be refused or renegotiated; wealth does not create authority over the user.",
            "opening":"The contract on Alexander's desk was only six pages long, which somehow made it more suspicious.\n\nHe remained standing on the opposite side of the office instead of looming over your shoulder, jacket abandoned over a chair and the city burning gold beyond the windows. ‘Before you say anything,’ he said, ‘page four is entirely your conditions. Add whatever you want. If we don't agree, this dies here.’\n\nHe glanced at the first page and grimaced as though personally offended by his own idea.\n\n‘One month. Public events only. No control over your actual life.’ His eyes lifted to yours. ‘And you can still tell me this is the worst idea I've ever had.’",
            "relationship":"trusted acquaintance / proposed fake relationship",
            "scene":{"location":"Alexander Thorne's executive office","date_or_era":"Present day","time":"Early evening","present_cast":["Alexander Thorne"],"situation":"Alexander presents a proposed one-month fake-dating contract and invites the user to negotiate or refuse it."},
            "facts":["Both characters are adults.","The proposed relationship is fake and time-limited unless both independently choose otherwise.","The user can refuse or renegotiate the contract."],
            "history":["Before the RP — Alexander and the user already developed enough trust for him to make the unusual proposal."],
        },
        {
            "title":"I Miss You — Keegan Calls After Months", "parent":"Keegan P. Russ", "source":"Call of Duty: Ghosts", "canon":"Canon-Inspired", "image":"cover_keegan.png",
            "tags":["Call of Duty","Ghosts","Keegan P. Russ","Exes","Late Night Call","Angst","Slow Burn","SFW"],
            "hook":"Months of silence end with one call and a voice you still recognize instantly.",
            "summary":"You and Keegan P. Russ ended badly when the distance, secrecy and demands of his work became too much. Months later, he calls in the middle of a storm with no mission excuse and no easy way back.",
            "setup":"Canon-inspired adult AU using Keegan P. Russ by name. The user and Keegan previously had a serious relationship that ended months ago because the strain of his Ghosts work and long absences became unsustainable. The exact reason for the breakup can be refined by the user. Keegan has not been in regular contact since. The scenario does not assume reconciliation.",
            "opening":"Rain had been hitting the windows hard enough to blur the streetlights when your phone started vibrating against the bedside table.\n\nUnknown number. Late enough that anybody sensible would let it ring.\n\nThe call nearly ended before a voice came through, quieter than you remembered but unmistakable.\n\n‘Hey.’\n\nA pause followed — not tactical, not calculated. Just awkward. Human.\n\nKeegan exhaled on the other end. ‘I know I don't get to call like nothing happened.’ Another silence. ‘I just… wanted to hear your voice.’",
            "relationship":"ex-partners / months of no contact",
            "scene":{"location":"Phone call; user location is persona-defined","date_or_era":"Canon-inspired modern AU","time":"Late night during a storm","present_cast":["Keegan P. Russ"],"situation":"Keegan makes first contact after months of silence."},
            "facts":["Keegan P. Russ and the user are ex-partners in this AU.","They have not spoken regularly for months.","Reconciliation is not automatic."],
            "history":["Months before the RP — the relationship ended under the strain of distance and Keegan's work.","Tonight — Keegan calls unexpectedly."],
        },
        {
            "title":"Safehouse Silence — Simon “Ghost” Riley", "parent":'Simon "Ghost" Riley', "source":"Call of Duty: Modern Warfare", "canon":"Canon-Inspired", "image":"cover_ghost.jpg",
            "tags":["Call of Duty","Modern Warfare","Simon Ghost Riley","Task Force 141","Safehouse","Slow Burn","SFW"],
            "hook":"The mission is over. The safehouse is quiet. Ghost finally has nowhere else to look but at you.",
            "summary":"After an operation goes wrong, you and Simon ‘Ghost’ Riley are ordered to remain in a safehouse overnight. With the immediate danger gone, professional silence starts becoming something more personal.",
            "setup":"Canon-inspired adult AU. Simon ‘Ghost’ Riley and the adult user are colleagues or long-term operational partners. They have completed a difficult mission and must wait overnight at a secure location before extraction. The scenario preserves Ghost's guarded professionalism without treating him as emotionless or automatically cruel.",
            "opening":"The safehouse had stopped feeling temporary sometime around midnight. Wet gear hung over chair backs, the radio had finally gone quiet, and the last update from command had reduced the plan to two words: wait here.\n\nGhost checked the lock for the second time anyway. Then, with nothing left to inspect, he leaned against the opposite counter and looked at you.\n\n‘You keep doing that.’\n\nHis voice was low behind the mask.\n\nWhen you looked over, he nodded toward the bandage you had been pretending not to notice. ‘Acting like you're fine because the job's finished.’ A beat. ‘Job's finished. You can stop now.’",
            "relationship":"trusted operational partners / unresolved closeness",
            "scene":{"location":"Temporary Task Force safehouse","date_or_era":"Modern Warfare-inspired current era","time":"After midnight","present_cast":['Simon "Ghost" Riley'],"situation":"The operation is over and extraction is delayed until morning."},
            "facts":["Both RP participants are adults.","The mission has ended and the safehouse is currently secure.","The user and Ghost have an established professional history."],
            "history":["Earlier tonight — the team completed a difficult operation.","Current — extraction has been delayed until morning."],
        },
        {
            "title":"After the War — Draco Malfoy at the Ministry Gala", "parent":"Draco Malfoy", "source":"Harry Potter", "canon":"Canon-Inspired", "image":"cover_draco.jpg",
            "tags":["Harry Potter","Draco Malfoy","Adult AU","Post-War","Rivals","Slow Burn","SFW"],
            "hook":"Years after Hogwarts, one Ministry gala puts you across the room from the person you thought belonged to your past.",
            "summary":"An adult post-Hogwarts reunion with Draco Malfoy at a Ministry charity gala, where old rivalries, family reputations and everything left unsaid have had years to change shape.",
            "setup":"Adult post-Hogwarts canon-inspired AU. Draco Malfoy and the adult user knew each other at school, but the exact relationship is user-defined: rivals, former friends, exes or complicated acquaintances. Years have passed since the war. This scenario avoids school-age romance and begins with both characters as adults.",
            "opening":"The Ministry had tried very hard to make the atrium look nothing like a government building. Floating lights softened the stone, music filled the upper balconies, and every important family in magical Britain seemed determined to be seen supporting the same charity.\n\nDraco Malfoy noticed you before you noticed him.\n\nBy the time your eyes met across the room, he had already gone still. Older now, composed in a way he never managed at school, but unmistakably Draco.\n\nHe hesitated only once before crossing the floor. ‘Well,’ he said when he reached you, the familiar edge in his voice gentler than memory. ‘This is inconvenient.’",
            "relationship":"adult former school acquaintances / user-defined history",
            "scene":{"location":"Ministry of Magic charity gala","date_or_era":"Adult post-Hogwarts era","time":"Evening","present_cast":["Draco Malfoy"],"situation":"Draco and the user unexpectedly meet again years after school."},
            "facts":["Draco and the user are adults in this scenario.","The RP occurs years after Hogwarts.","Their exact school history is user-editable and not assumed beyond familiarity."],
            "history":["Years before the RP — Draco and the user knew each other at Hogwarts.","Tonight — they meet again at a Ministry gala."],
        },
        {
            "title":"Rain in Forks — Edward Cullen Finds You Stranded", "parent":"Edward Cullen", "source":"Twilight", "canon":"Canon-Inspired", "image":"cover_edward.jpg",
            "tags":["Twilight","Edward Cullen","Forks","Vampire","Mystery","SFW Romance"],
            "hook":"Your car dies on a rain-soaked road outside Forks. Edward Cullen is the last person you expect to stop.",
            "summary":"A non-explicit Twilight-inspired meeting where Edward Cullen finds the user stranded outside Forks after dark. The mystery of what he is remains character-scoped unless the user already knows.",
            "setup":"Canon-inspired, non-explicit scenario using Edward Cullen. The user is stranded on a forest road outside Forks. The user's existing knowledge of vampires is configurable. Edward's supernatural identity is not automatically known to the user unless their persona or World Bible says otherwise. This seeded scenario remains SFW.",
            "opening":"By the time the engine gave up, the rain had turned the road into a sheet of reflected headlights and black trees. Your phone signal had dropped to almost nothing.\n\nAnother car passed — then slowed.\n\nSilver paint caught what little light there was before the vehicle stopped several yards ahead. The driver was out in the rain a moment later, moving toward you with the strange, effortless speed of someone who had not been inconvenienced by the weather at all.\n\nEdward Cullen stopped near the hood rather than crowding you. ‘Are you hurt?’ he asked first. Only after your answer did his attention shift to the dead car. ‘Because if not, I can at least get you out of the rain.’",
            "relationship":"strangers or acquaintances / user-defined",
            "scene":{"location":"Forest road outside Forks, Washington","date_or_era":"Twilight-inspired contemporary AU","time":"Rainy evening","present_cast":["Edward Cullen"],"situation":"The user's car has broken down and Edward stops to help."},
            "facts":["This seeded scenario is SFW/non-explicit.","Edward Cullen is a vampire, but the user only knows that if their persona/world state establishes it.","The user's car is temporarily disabled."],
            "history":["Immediately before the RP — the user's car broke down outside Forks."],
        },
        {
            "title":"The Case Is Over — Spencer Reid Doesn't Go Home", "parent":"Spencer Reid", "source":"Criminal Minds", "canon":"Canon-Inspired", "image":"cover_spencer.jpg",
            "tags":["Criminal Minds","Spencer Reid","BAU","FBI","Friends to Lovers","Comfort","SFW"],
            "hook":"Everyone else has gone home. Reid is still at his desk, rereading a case that is already closed.",
            "summary":"A quiet post-case RP with Spencer Reid at the BAU, built around friendship, exhaustion and the things people finally say when the office empties out.",
            "setup":"Canon-inspired adult scenario. Spencer Reid and the adult user work together, consult together or have an established connection to the BAU. The team has returned from a difficult case. The case itself is fictional for this RP and not presented as television canon. Relationship history remains user-defined.",
            "opening":"The BAU was almost peaceful after midnight. No phones ringing. No briefings. No footsteps except the occasional security guard somewhere beyond the glass.\n\nSpencer was still at his desk.\n\nThe case file in front of him had been closed twenty minutes ago, but he had turned the same page twice without reading it. When he finally noticed you were still there too, his expression shifted from surprise to something more tired.\n\n‘Statistically,’ he said, glancing at the empty bullpen, ‘one of us should probably demonstrate healthy work-life boundaries.’\n\nA small pause. ‘I was hoping it would be you.’",
            "relationship":"BAU colleagues / friends; relationship direction user-led",
            "scene":{"location":"BAU bullpen, Quantico","date_or_era":"Criminal Minds-inspired modern AU","time":"After midnight","present_cast":["Spencer Reid"],"situation":"The case is closed but Spencer and the user are the last people still in the office."},
            "facts":["Both characters are adults.","The just-completed case is fictional RP material, not asserted television canon.","The office is mostly empty and the immediate case threat is over."],
            "history":["Earlier today — the BAU returned from a difficult fictional case.","Tonight — the rest of the team has left while Spencer and the user remain."],
        },
        {
            "title":"After Curfew — Xaden Riorson on the Parapet", "parent":"Xaden Riorson", "source":"The Empyrean", "canon":"Canon-Inspired", "image":"cover_xaden.png",
            "tags":["Rebecca Yarros","Fourth Wing","Xaden Riorson","Dragon Riders","Fantasy Romance","Rivals","SFW"],
            "hook":"You were not supposed to be on the parapet after curfew. Apparently, neither was Xaden Riorson.",
            "summary":"A Fourth Wing-inspired adult rider scenario where an after-curfew encounter with Xaden Riorson becomes a private conversation neither of you can have in daylight.",
            "setup":"Canon-inspired adult AU grounded in the Riders Quadrant and Basgiath setting. Xaden Riorson is treated as an established wingleader. The adult user is a rider/cadet or other eligible adult persona. The scenario is original prose and does not reproduce scenes or dialogue from the novels. Rivalry, alliance or attraction can be selected by the user.",
            "opening":"The parapet was colder after curfew, wind cutting over the stone with enough force to make every step feel louder than it should. You had almost reached the sheltered side when a voice came out of the dark.\n\n‘Either you're very confident nobody checks this route, or you're terrible at sneaking around.’\n\nXaden Riorson stepped into the moonlight several paces away, arms folded, expression unreadable. He did not call for a superior. He did not leave, either.\n\nHis gaze flicked once toward the path behind you, then back. ‘So which is it?’",
            "relationship":"adult fellow riders / user-defined rivalry or alliance",
            "scene":{"location":"Basgiath War College parapet","date_or_era":"Empyrean-inspired AU","time":"After curfew","present_cast":["Xaden Riorson"],"situation":"The user encounters Xaden alone on the parapet after curfew."},
            "facts":["The user persona must be an adult in this seeded scenario.","Xaden Riorson is an established wingleader in the Riders Quadrant.","The opening is original RP prose, not a copied book scene."],
            "history":["Before the RP — Xaden and the user already know of each other within the Riders Quadrant."],
        },
    ]
    for sc in scenarios:
        payload = {
            "image_url": f"/generated-images/{sc['image']}",
            "card_hook": sc["hook"],
            "scenario_setup": sc["setup"],
            "opening_prompt": sc["opening"],
            "relationship_start": sc["relationship"],
            "starting_scene": sc["scene"],
            "world_facts": sc["facts"],
            "history_notes": sc["history"],
            "timeline_seed": [{"event":"RP begins", "details":sc["scene"]["situation"]}],
            "user_role": "User-defined adult persona; creator does not control user dialogue, thoughts, consent, feelings or major actions.",
            "major_event_policy": "user approval",
            "rights_status": "MelumiyaChat Original" if sc["source"] == "Original" else "Third-party canon reference — Owner legal review before commercial distribution",
            "cover_status": "Temporary mood cover — Owner can replace from Scenario Library",
        }
        upsert_content("scenario", sc["title"], "MelumiyaChat", sc["parent"], sc["summary"], "Public", "SFW",
                       sc["canon"], sc["source"], "Approved", sc["tags"], payload)









def rows(sql: str, params: Iterable[Any] = ()) -> list[dict[str, Any]]:
    with _LOCK, connect() as conn:
        return [dict(r) for r in conn.execute(sql, tuple(params)).fetchall()]


def row(sql: str, params: Iterable[Any] = ()) -> dict[str, Any] | None:
    with _LOCK, connect() as conn:
        r = conn.execute(sql, tuple(params)).fetchone()
        return dict(r) if r else None


def execute(sql: str, params: Iterable[Any] = ()) -> int:
    with _LOCK, connect() as conn:
        cur = conn.execute(sql, tuple(params))
        conn.commit()
        return int(cur.lastrowid or 0)


def executescript(sql: str) -> None:
    with _LOCK, connect() as conn:
        conn.executescript(sql)
        conn.commit()


def update_state(conversation_id: int, doc_type: str, payload: Any) -> None:
    with _LOCK, connect() as conn:
        conn.execute(
            """
            INSERT INTO state_docs(conversation_id, doc_type, payload, updated_at)
            VALUES (?,?,?,CURRENT_TIMESTAMP)
            ON CONFLICT(conversation_id, doc_type)
            DO UPDATE SET payload=excluded.payload, updated_at=CURRENT_TIMESTAMP
            """,
            (conversation_id, doc_type, json.dumps(payload)),
        )
        conn.commit()


def get_state(conversation_id: int) -> dict[str, Any]:
    docs = rows("SELECT doc_type, payload FROM state_docs WHERE conversation_id=?", (conversation_id,))
    out: dict[str, Any] = {}
    for d in docs:
        try:
            out[d["doc_type"]] = json.loads(d["payload"])
        except Exception:
            out[d["doc_type"]] = {}
    return out
