-- ============================================================
-- Strata Platform — SQLite Schema v1.0
-- Generated: March 2026
-- Migration from: JSON flat-file storage (/deck-data/*.json)
-- ============================================================

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

-- ────────────────────────────────────────
-- REPS (Team Members)
-- Source: reps.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS reps (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT,
    code_hash TEXT NOT NULL,
    role TEXT DEFAULT 'rep',
    active INTEGER DEFAULT 1,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ────────────────────────────────────────
-- TOKENS (Prospect & Rep Access Tokens)
-- Source: tokens.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tokens (
    id TEXT PRIMARY KEY,
    name TEXT,
    company TEXT,
    type TEXT DEFAULT 'prospect',
    status TEXT DEFAULT 'active',
    deck_version TEXT,
    profile TEXT,
    created_by TEXT REFERENCES reps(id),
    created_at TEXT DEFAULT (datetime('now')),
    first_opened TEXT,
    timed_duration INTEGER DEFAULT 86400,
    vaults_enabled INTEGER DEFAULT 1,
    quiz_enabled INTEGER DEFAULT 1,
    sf_record_id TEXT,
    sf_record_type TEXT,
    metadata TEXT
);

CREATE INDEX IF NOT EXISTS idx_tokens_status ON tokens(status);
CREATE INDEX IF NOT EXISTS idx_tokens_created_by ON tokens(created_by);
CREATE INDEX IF NOT EXISTS idx_tokens_deck_version ON tokens(deck_version);

-- ────────────────────────────────────────
-- TOKEN DECK OVERRIDES (Delta Architecture)
-- Source: token-deck-{tokenId}.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS token_deck_overrides (
    token_id TEXT PRIMARY KEY REFERENCES tokens(id) ON DELETE CASCADE,
    copy_overrides TEXT,
    media_overrides TEXT,
    order_override TEXT,
    disabled_slides TEXT,
    vault_toggles TEXT,
    quiz_placements TEXT,
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ────────────────────────────────────────
-- ANALYTICS EVENTS
-- Source: analytics.json, analytics-{deckVer}.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS analytics_events (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token_id TEXT REFERENCES tokens(id),
    deck_version TEXT,
    event_type TEXT NOT NULL,
    slide_id TEXT,
    duration REAL,
    metadata TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_analytics_token ON analytics_events(token_id);
CREATE INDEX IF NOT EXISTS idx_analytics_type ON analytics_events(event_type);
CREATE INDEX IF NOT EXISTS idx_analytics_created ON analytics_events(created_at);
CREATE INDEX IF NOT EXISTS idx_analytics_slide ON analytics_events(slide_id);

-- ────────────────────────────────────────
-- AI INSIGHTS
-- Source: ai-insights.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ai_insights (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token_id TEXT REFERENCES tokens(id),
    engagement_score TEXT,
    summary TEXT,
    talking_points TEXT,
    objections TEXT,
    follow_up_timing TEXT,
    raw_response TEXT,
    generated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_insights_token ON ai_insights(token_id);
CREATE INDEX IF NOT EXISTS idx_insights_score ON ai_insights(engagement_score);

-- ────────────────────────────────────────
-- SLIDE CONTENT (Per-Deck Text Overrides)
-- Source: slide-copy-{deckVer}.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS slide_content (
    deck_version TEXT NOT NULL,
    slide_id TEXT NOT NULL,
    headline TEXT,
    body TEXT,
    bullets TEXT,
    metadata TEXT,
    updated_by TEXT,
    updated_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (deck_version, slide_id)
);

-- ────────────────────────────────────────
-- SLIDE MEDIA (Per-Deck Image/Video Overrides)
-- Source: slide-media-{deckVer}.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS slide_media (
    deck_version TEXT NOT NULL,
    slide_id TEXT NOT NULL,
    bg_image TEXT,
    inline_image TEXT,
    video_url TEXT,
    side_image TEXT,
    overlay TEXT,
    metadata TEXT,
    updated_by TEXT,
    updated_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (deck_version, slide_id)
);

-- ────────────────────────────────────────
-- SLIDE ORDER (Per-Deck Ordering)
-- Source: slide-order-{deckVer}.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS slide_order (
    deck_version TEXT PRIMARY KEY,
    order_list TEXT,
    updated_by TEXT,
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ────────────────────────────────────────
-- SLIDE VISIBILITY (Per-Deck Show/Hide)
-- Source: slide-visibility-{deckVer}.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS slide_visibility (
    deck_version TEXT NOT NULL,
    slide_id TEXT NOT NULL,
    visible INTEGER DEFAULT 1,
    updated_by TEXT,
    updated_at TEXT DEFAULT (datetime('now')),
    PRIMARY KEY (deck_version, slide_id)
);

-- ────────────────────────────────────────
-- QUIZ RESPONSES
-- Source: quiz-responses.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS quiz_responses (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token_id TEXT REFERENCES tokens(id),
    question_id TEXT,
    answer TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_quiz_token ON quiz_responses(token_id);

-- ────────────────────────────────────────
-- TRANSCRIPTS
-- Source: transcripts.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS transcripts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token_id TEXT REFERENCES tokens(id),
    content TEXT,
    source TEXT DEFAULT 'manual',
    created_by TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_transcripts_token ON transcripts(token_id);

-- ────────────────────────────────────────
-- COMMENTS (Slide-Level Discussion Threads)
-- Source: comments.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS comments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    slide_id TEXT,
    deck_version TEXT,
    author TEXT,
    content TEXT,
    parent_id INTEGER REFERENCES comments(id),
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_comments_slide ON comments(slide_id);
CREATE INDEX IF NOT EXISTS idx_comments_deck ON comments(deck_version);

-- ────────────────────────────────────────
-- PLAYBOOK (Sales Playbook Library)
-- Source: playbook.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS playbook_entries (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    category TEXT,
    title TEXT,
    content TEXT,
    tags TEXT,
    created_by TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_playbook_category ON playbook_entries(category);

-- ────────────────────────────────────────
-- SLIDE BANK (Question/Insight Bank)
-- Source: slide-bank.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS slide_bank (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    type TEXT,
    question TEXT,
    options TEXT,
    correct_answer TEXT,
    explanation TEXT,
    slide_id TEXT,
    metadata TEXT,
    created_by TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ────────────────────────────────────────
-- SETTINGS (Platform Configuration)
-- Source: settings.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT,
    updated_by TEXT,
    updated_at TEXT DEFAULT (datetime('now'))
);

-- ────────────────────────────────────────
-- AUDIT LOG (All System Actions)
-- NEW: No JSON source, starts fresh
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS audit_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    user_id TEXT,
    action TEXT NOT NULL,
    target_type TEXT,
    target_id TEXT,
    diff TEXT,
    ip_address TEXT,
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_audit_user ON audit_log(user_id);
CREATE INDEX IF NOT EXISTS idx_audit_action ON audit_log(action);
CREATE INDEX IF NOT EXISTS idx_audit_created ON audit_log(created_at);

-- ────────────────────────────────────────
-- SALESFORCE SYNC LOG
-- NEW: No JSON source, starts fresh
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS sf_sync_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    token_id TEXT REFERENCES tokens(id),
    sf_record_id TEXT,
    sync_type TEXT,
    payload TEXT,
    response TEXT,
    status TEXT DEFAULT 'pending',
    created_at TEXT DEFAULT (datetime('now'))
);

CREATE INDEX IF NOT EXISTS idx_sf_token ON sf_sync_log(token_id);
CREATE INDEX IF NOT EXISTS idx_sf_status ON sf_sync_log(status);

-- ────────────────────────────────────────
-- DECK PROFILES (Predefined Configurations)
-- Source: deck-profiles.json
-- ────────────────────────────────────────
CREATE TABLE IF NOT EXISTS deck_profiles (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    config TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    updated_at TEXT DEFAULT (datetime('now'))
);