-- ─────────────────────────────────────────────────────────────────────────────
-- OSINT Investigation Database Schema — PostgreSQL 16 + pgvector
-- Run: psql -h 127.0.0.1 cultguard < data/schema.sql   (idempotent)
-- ─────────────────────────────────────────────────────────────────────────────

-- Extensions (must be installed by superuser before first run — devenv handles this)
CREATE EXTENSION IF NOT EXISTS vector;       -- pgvector: embedding similarity search
CREATE EXTENSION IF NOT EXISTS pg_trgm;      -- trigram fuzzy text matching
CREATE EXTENSION IF NOT EXISTS unaccent;     -- strip accents in FTS
CREATE EXTENSION IF NOT EXISTS btree_gin;    -- composite GIN indexes

-- ─── INVESTIGATIONS ──────────────────────────────────────────────────────────
-- Top-level case namespace. Every other table scopes to an investigation.
CREATE TABLE IF NOT EXISTS investigations (
    id           TEXT PRIMARY KEY,
    name         TEXT NOT NULL,
    description  TEXT,
    target_url   TEXT,
    status       TEXT NOT NULL DEFAULT 'active',  -- active | archived | closed
    started_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    closed_at    TIMESTAMPTZ,
    metadata     JSONB NOT NULL DEFAULT '{}'
);

-- ─── ENTITIES ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS entities (
    id               TEXT PRIMARY KEY,   -- FB UID / page ID / custom key
    investigation_id TEXT REFERENCES investigations(id) ON DELETE SET NULL,
    type             TEXT NOT NULL,      -- profile | page | phone | domain | ip | org
    name             TEXT,
    url              TEXT,
    platform         TEXT NOT NULL DEFAULT 'facebook',
    country          TEXT,               -- Meta-reported or inferred
    created_at       TIMESTAMPTZ,        -- when the platform account was created
    collected_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    confidence       INTEGER NOT NULL DEFAULT 50 CHECK (confidence BETWEEN 0 AND 100),
    is_sock          BOOLEAN NOT NULL DEFAULT FALSE,
    notes            TEXT,
    raw_json         JSONB,
    -- Full-text search vector (Arabic + English, generated, auto-updated by trigger)
    fts              TSVECTOR GENERATED ALWAYS AS (
                         to_tsvector('simple',
                             COALESCE(name,'') || ' ' ||
                             COALESCE(notes,'') || ' ' ||
                             COALESCE(country,'')
                         )
                     ) STORED
);

-- ─── CONTENT ─────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS content (
    id               TEXT PRIMARY KEY,   -- FB post_id / comment_id / ad_id
    investigation_id TEXT REFERENCES investigations(id) ON DELETE SET NULL,
    entity_id        TEXT REFERENCES entities(id) ON DELETE SET NULL,
    type             TEXT NOT NULL,      -- post | comment | ad | bio | caption | article
    text             TEXT,
    text_lang        TEXT,               -- ar | en | fr | mixed
    text_dialect     TEXT,               -- lebanese | palestinian | gulf | msa
    published_at     TIMESTAMPTZ,
    collected_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    likes            INTEGER,
    shares           INTEGER,
    comments         INTEGER,
    reactions        JSONB,              -- {like, love, wow, haha, sad, angry} counts
    url              TEXT,
    -- Ad-specific fields (null for non-ads)
    ad_spend_min     NUMERIC(12,2),
    ad_spend_max     NUMERIC(12,2),
    ad_impressions   JSONB,              -- {lower_bound, upper_bound}
    ad_region_dist   JSONB,              -- {"Lebanon":0.8, "UAE":0.2}
    ad_start_at      TIMESTAMPTZ,
    ad_end_at        TIMESTAMPTZ,
    raw_json         JSONB,
    fts              TSVECTOR GENERATED ALWAYS AS (
                         to_tsvector('simple', COALESCE(text,''))
                     ) STORED
);

-- ─── MEDIA ───────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS media (
    id               TEXT PRIMARY KEY,   -- FB photo/video ID or sha256[:16]
    investigation_id TEXT REFERENCES investigations(id) ON DELETE SET NULL,
    entity_id        TEXT REFERENCES entities(id) ON DELETE SET NULL,
    content_id       TEXT REFERENCES content(id) ON DELETE SET NULL,
    type             TEXT NOT NULL,      -- image | video | screenshot | thumbnail | audio
    file_path        TEXT,               -- repo-relative local path
    url_original     TEXT,
    sha256           TEXT UNIQUE,        -- dedup key
    width            INTEGER,
    height           INTEGER,
    duration_secs    REAL,               -- for video/audio
    exif_data        JSONB,
    is_ai_generated  BOOLEAN,
    ai_score         REAL CHECK (ai_score IS NULL OR ai_score BETWEEN 0 AND 1),
    reverse_search   JSONB,              -- {google_results, tineye_results} etc.
    collected_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    notes            TEXT
);

-- ─── HTTP LOG ────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS http_log (
    id               BIGSERIAL PRIMARY KEY,
    investigation_id TEXT REFERENCES investigations(id) ON DELETE SET NULL,
    url              TEXT NOT NULL,
    method           TEXT NOT NULL DEFAULT 'GET',
    request_headers  JSONB,
    request_body     TEXT,
    response_status  INTEGER,
    response_headers JSONB,
    response_body    TEXT,               -- truncated at 100KB; full file in file_path
    file_path        TEXT,
    collected_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    session_id       TEXT,               -- c_user cookie or session token used
    notes            TEXT
);

-- ─── RELATIONSHIPS ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS relationships (
    id           BIGSERIAL PRIMARY KEY,
    investigation_id TEXT REFERENCES investigations(id) ON DELETE SET NULL,
    source_id    TEXT NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    target_id    TEXT NOT NULL REFERENCES entities(id) ON DELETE CASCADE,
    type         TEXT NOT NULL,    -- follows | commented_on | admin_of | employer | linked_to | same_operator | reposts
    strength     INTEGER NOT NULL DEFAULT 50 CHECK (strength BETWEEN 0 AND 100),
    evidence     TEXT,
    collected_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT uq_relationships_edge UNIQUE (investigation_id, source_id, target_id, type)
);

-- ─── IDENTIFIERS ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS identifiers (
    id           BIGSERIAL PRIMARY KEY,
    investigation_id TEXT REFERENCES investigations(id) ON DELETE SET NULL,
    entity_id    TEXT REFERENCES entities(id) ON DELETE CASCADE,
    type         TEXT NOT NULL,    -- phone | email | username | ip | domain | wallet
    value        TEXT NOT NULL,
    country_code TEXT,
    carrier      TEXT,
    lookup_result JSONB,
    collected_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── ANNOTATIONS ─────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS annotations (
    id           BIGSERIAL PRIMARY KEY,
    investigation_id TEXT REFERENCES investigations(id) ON DELETE SET NULL,
    ref_type     TEXT NOT NULL,    -- entity | content | media | relationship | claim
    ref_id       TEXT NOT NULL,
    analyst      TEXT NOT NULL DEFAULT 'human',  -- human | llm | subagent
    note         TEXT NOT NULL,
    confidence   INTEGER CHECK (confidence IS NULL OR confidence BETWEEN 0 AND 100),
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    fts          TSVECTOR GENERATED ALWAYS AS (
                     to_tsvector('simple', COALESCE(note,''))
                 ) STORED
);

-- ─── TAGS ────────────────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS tags (
    id    SERIAL PRIMARY KEY,
    name  TEXT UNIQUE NOT NULL,
    color TEXT NOT NULL DEFAULT '#6366f1'   -- hex for Metabase display
);

CREATE TABLE IF NOT EXISTS taggings (
    tag_id   INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    ref_type TEXT NOT NULL,    -- entity | content | media | claim
    ref_id   TEXT NOT NULL,
    PRIMARY KEY (tag_id, ref_type, ref_id)
);

-- ─────────────────────────────────────────────────────────────────────────────
-- LLM INTEGRATION TABLES
-- ─────────────────────────────────────────────────────────────────────────────

-- ─── TEXT EMBEDDINGS (replaces LanceDB text_chunks) ──────────────────────────
-- bge-m3 multilingual 1024-dim — handles Arabic, English, mixed
CREATE TABLE IF NOT EXISTS text_embeddings (
    id        BIGSERIAL PRIMARY KEY,
    ref_type  TEXT NOT NULL,   -- entity | content | annotation | claim
    ref_id    TEXT NOT NULL,
    chunk_idx INTEGER NOT NULL DEFAULT 0,  -- for long-text chunking
    text      TEXT,            -- the exact text that was embedded (for inspection)
    model     TEXT NOT NULL DEFAULT 'BAAI/bge-m3',
    embedding vector(1024),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (ref_type, ref_id, chunk_idx, model)
);
-- HNSW index for fast approximate nearest-neighbour search (cosine)
CREATE INDEX IF NOT EXISTS idx_text_emb_hnsw
    ON text_embeddings USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- ─── IMAGE EMBEDDINGS (replaces LanceDB image_embeddings) ────────────────────
-- CLIP ViT-L/14 768-dim
CREATE TABLE IF NOT EXISTS image_embeddings (
    id        BIGSERIAL PRIMARY KEY,
    media_id  TEXT NOT NULL REFERENCES media(id) ON DELETE CASCADE,
    model     TEXT NOT NULL DEFAULT 'openai/clip-vit-large-patch14',
    embedding vector(768),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    UNIQUE (media_id, model)
);
CREATE INDEX IF NOT EXISTS idx_image_emb_hnsw
    ON image_embeddings USING hnsw (embedding vector_cosine_ops)
    WITH (m = 16, ef_construction = 64);

-- ─── CLAIMS — atomic structured facts extracted by LLMs ──────────────────────
-- Each row = one discrete, verifiable claim. LLMs write here; analysts verify.
CREATE TABLE IF NOT EXISTS claims (
    id               BIGSERIAL PRIMARY KEY,
    investigation_id TEXT REFERENCES investigations(id) ON DELETE SET NULL,
    subject_type     TEXT NOT NULL,    -- entity | content | media
    subject_id       TEXT NOT NULL,    -- FK target depends on subject_type
    predicate        TEXT NOT NULL,    -- affiliated_with | operates | located_in |
                                       -- phone_owner | content_source | impersonates |
                                       -- funded_by | admin_of | coordinated_with | ...
    object           TEXT NOT NULL,    -- the claim value (free text or structured)
    object_type      TEXT,             -- entity | location | org | phone | url | text
    confidence       INTEGER NOT NULL DEFAULT 50 CHECK (confidence BETWEEN 0 AND 100),
    evidence         TEXT,             -- human-readable justification
    evidence_ref_type TEXT,            -- content | media | relationship | annotation
    evidence_ref_id  TEXT,             -- FK to the supporting evidence
    source           TEXT NOT NULL DEFAULT 'llm',   -- llm | human | automated
    model            TEXT,             -- which LLM model asserted this
    llm_run_id       BIGINT,           -- FK to llm_runs (set when source='llm')
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    verified_at      TIMESTAMPTZ,
    verified_by      TEXT,             -- analyst name / LLM that verified
    status           TEXT NOT NULL DEFAULT 'unverified'  -- unverified | confirmed | refuted | stale
);

-- ─── LLM RUNS — full audit trail of all LLM interactions ─────────────────────
-- Every LLM call (analyst prompt, extraction pass, classifier, etc.) is logged.
-- Enables cost tracking, reproducibility, and re-running with new models.
CREATE TABLE IF NOT EXISTS llm_runs (
    id               BIGSERIAL PRIMARY KEY,
    investigation_id TEXT REFERENCES investigations(id) ON DELETE SET NULL,
    session_id       TEXT,             -- group related turns (e.g. a conversation thread)
    model            TEXT NOT NULL,    -- e.g. gpt-4o | claude-3-5-sonnet | llama-3.3-70b
    role             TEXT,             -- analyst | extractor | classifier | summariser | embedder
    prompt           TEXT,             -- full prompt sent (or system+user JSON)
    response         TEXT,             -- full raw response
    tool_calls       JSONB,            -- array of {name, arguments} objects called
    tool_results     JSONB,            -- array of tool return values
    tokens_in        INTEGER,
    tokens_out       INTEGER,
    cost_usd         NUMERIC(10,6),
    duration_ms      INTEGER,
    error            TEXT,             -- null on success; error message on failure
    created_at       TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- ─── TIMELINE EVENTS — denormalised event stream for Metabase ────────────────
-- Pre-computed time-ordered events. Populate from content/relationships/claims.
-- Designed so Metabase can do zero-join time-series charts.
CREATE TABLE IF NOT EXISTS timeline_events (
    id               BIGSERIAL PRIMARY KEY,
    investigation_id TEXT NOT NULL REFERENCES investigations(id) ON DELETE CASCADE,
    entity_id        TEXT REFERENCES entities(id) ON DELETE SET NULL,
    entity_name      TEXT,             -- denormalised for Metabase convenience
    event_type       TEXT NOT NULL,    -- post | ad_start | ad_end | comment | account_created |
                                       -- phone_lookup | relationship_found | claim_added |
                                       -- page_transparency | follower_spike
    event_at         TIMESTAMPTZ NOT NULL,
    description      TEXT,
    numeric_value    NUMERIC,          -- engagement count, ad spend, follower delta, etc.
    ref_type         TEXT,             -- content | media | relationship | claim
    ref_id           TEXT,
    metadata         JSONB NOT NULL DEFAULT '{}'
);

-- ─────────────────────────────────────────────────────────────────────────────
-- INDEXES
-- ─────────────────────────────────────────────────────────────────────────────

CREATE INDEX IF NOT EXISTS idx_entities_investigation ON entities(investigation_id);
CREATE INDEX IF NOT EXISTS idx_entities_type          ON entities(type);
CREATE INDEX IF NOT EXISTS idx_entities_platform      ON entities(platform);
CREATE INDEX IF NOT EXISTS idx_entities_is_sock       ON entities(is_sock) WHERE is_sock = TRUE;
CREATE INDEX IF NOT EXISTS idx_entities_fts           ON entities USING gin(fts);

CREATE INDEX IF NOT EXISTS idx_content_entity         ON content(entity_id);
CREATE INDEX IF NOT EXISTS idx_content_type           ON content(type);
CREATE INDEX IF NOT EXISTS idx_content_published      ON content(published_at DESC NULLS LAST);
CREATE INDEX IF NOT EXISTS idx_content_fts            ON content USING gin(fts);

CREATE INDEX IF NOT EXISTS idx_media_entity           ON media(entity_id);
CREATE INDEX IF NOT EXISTS idx_media_sha256           ON media(sha256);

CREATE INDEX IF NOT EXISTS idx_rel_source             ON relationships(source_id);
CREATE INDEX IF NOT EXISTS idx_rel_target             ON relationships(target_id);
CREATE INDEX IF NOT EXISTS idx_rel_type               ON relationships(type);

CREATE INDEX IF NOT EXISTS idx_ident_entity           ON identifiers(entity_id);
CREATE INDEX IF NOT EXISTS idx_ident_value            ON identifiers(value);
CREATE INDEX IF NOT EXISTS idx_ident_type_value       ON identifiers(type, value);
CREATE UNIQUE INDEX IF NOT EXISTS uq_identifiers_entity_type_value ON identifiers(entity_id, type, value);

CREATE INDEX IF NOT EXISTS idx_annotations_ref        ON annotations(ref_type, ref_id);
CREATE INDEX IF NOT EXISTS idx_annotations_fts        ON annotations USING gin(fts);

CREATE INDEX IF NOT EXISTS idx_claims_subject         ON claims(subject_type, subject_id);
CREATE INDEX IF NOT EXISTS idx_claims_predicate       ON claims(predicate);
CREATE INDEX IF NOT EXISTS idx_claims_investigation   ON claims(investigation_id);
CREATE INDEX IF NOT EXISTS idx_claims_status          ON claims(status);

CREATE INDEX IF NOT EXISTS idx_llm_runs_investigation ON llm_runs(investigation_id);
CREATE INDEX IF NOT EXISTS idx_llm_runs_session       ON llm_runs(session_id);
CREATE INDEX IF NOT EXISTS idx_llm_runs_model         ON llm_runs(model);

CREATE INDEX IF NOT EXISTS idx_timeline_inv_at        ON timeline_events(investigation_id, event_at DESC);
CREATE INDEX IF NOT EXISTS idx_timeline_entity_at     ON timeline_events(entity_id, event_at DESC);
CREATE INDEX IF NOT EXISTS idx_timeline_event_type    ON timeline_events(event_type);

CREATE INDEX IF NOT EXISTS idx_taggings_ref           ON taggings(ref_type, ref_id);

-- Trigram indexes for fuzzy search on key text fields
CREATE INDEX IF NOT EXISTS idx_entities_name_trgm  ON entities USING gin(name gin_trgm_ops);
CREATE INDEX IF NOT EXISTS idx_identifiers_val_trgm ON identifiers USING gin(value gin_trgm_ops);

-- ─────────────────────────────────────────────────────────────────────────────
-- VIEWS — Metabase-friendly, zero-join queries
-- ─────────────────────────────────────────────────────────────────────────────

-- Entity activity summary (bar charts, scorecards)
CREATE OR REPLACE VIEW v_entity_summary AS
SELECT
    e.id,
    e.name,
    e.type,
    e.platform,
    e.country,
    e.is_sock,
    e.confidence,
    e.created_at   AS account_created_at,
    e.collected_at,
    i.name         AS investigation,
    COUNT(DISTINCT c.id)    AS post_count,
    COUNT(DISTINCT CASE WHEN c.type = 'ad' THEN c.id END) AS ad_count,
    COALESCE(SUM(c.likes),    0) AS total_likes,
    COALESCE(SUM(c.shares),   0) AS total_shares,
    COALESCE(SUM(c.comments), 0) AS total_comments,
    COUNT(DISTINCT m.id)    AS media_count,
    COUNT(DISTINCT r_out.id) AS relationships_out,
    COUNT(DISTINCT r_in.id)  AS relationships_in,
    COUNT(DISTINCT cl.id)    AS claim_count
FROM entities e
LEFT JOIN investigations    i     ON e.investigation_id = i.id
LEFT JOIN content           c     ON c.entity_id = e.id
LEFT JOIN media             m     ON m.entity_id = e.id
LEFT JOIN relationships     r_out ON r_out.source_id = e.id
LEFT JOIN relationships     r_in  ON r_in.target_id  = e.id
LEFT JOIN claims            cl    ON cl.subject_id = e.id AND cl.subject_type = 'entity'
GROUP BY e.id, e.name, e.type, e.platform, e.country,
         e.is_sock, e.confidence, e.created_at, e.collected_at, i.name;

-- Network edge list (Metabase table / future network viz)
CREATE OR REPLACE VIEW v_network_edges AS
SELECT
    r.id,
    r.type         AS relationship_type,
    r.strength,
    r.evidence,
    r.collected_at,
    i.name         AS investigation,
    s.id           AS source_id,
    s.name         AS source_name,
    s.type         AS source_type,
    s.is_sock      AS source_is_sock,
    t.id           AS target_id,
    t.name         AS target_name,
    t.type         AS target_type,
    t.is_sock      AS target_is_sock
FROM relationships r
JOIN entities s ON r.source_id = s.id
JOIN entities t ON r.target_id = t.id
LEFT JOIN investigations i ON r.investigation_id = i.id;

-- Claims with subject names (table / pivot charts)
CREATE OR REPLACE VIEW v_claims_summary AS
SELECT
    cl.id,
    cl.predicate,
    cl.object,
    cl.object_type,
    cl.confidence,
    cl.status,
    cl.source,
    cl.model,
    cl.created_at,
    cl.verified_at,
    i.name   AS investigation,
    e.name   AS subject_name,
    e.type   AS subject_entity_type,
    e.is_sock AS subject_is_sock
FROM claims cl
LEFT JOIN investigations i  ON cl.investigation_id = i.id
LEFT JOIN entities e        ON cl.subject_id = e.id AND cl.subject_type = 'entity';

-- Content timeline with entity context (time-series charts)
CREATE OR REPLACE VIEW v_content_timeline AS
SELECT
    c.id,
    c.type,
    c.published_at,
    c.likes,
    c.shares,
    c.comments,
    c.ad_spend_min,
    c.ad_spend_max,
    LEFT(c.text, 200) AS text_preview,
    c.text_lang,
    c.text_dialect,
    e.name     AS entity_name,
    e.is_sock,
    e.country,
    i.name     AS investigation
FROM content c
LEFT JOIN entities      e ON c.entity_id       = e.id
LEFT JOIN investigations i ON c.investigation_id = i.id;

-- LLM cost dashboard (Metabase aggregation)
CREATE OR REPLACE VIEW v_llm_cost_summary AS
SELECT
    DATE_TRUNC('day', created_at) AS day,
    model,
    role,
    i.name        AS investigation,
    COUNT(*)      AS run_count,
    SUM(tokens_in)  AS total_tokens_in,
    SUM(tokens_out) AS total_tokens_out,
    SUM(cost_usd)   AS total_cost_usd,
    AVG(duration_ms) AS avg_duration_ms,
    COUNT(*) FILTER (WHERE error IS NOT NULL) AS error_count
FROM llm_runs lr
LEFT JOIN investigations i ON lr.investigation_id = i.id
GROUP BY DATE_TRUNC('day', created_at), model, role, i.name;

-- Embedding coverage (helps identify what still needs to be embedded)
CREATE OR REPLACE VIEW v_embedding_coverage AS
SELECT
    'content' AS table_name,
    COUNT(*)  AS total_rows,
    COUNT(te.id) AS embedded_rows,
    ROUND(100.0 * COUNT(te.id) / NULLIF(COUNT(*), 0), 1) AS coverage_pct
FROM content c
LEFT JOIN text_embeddings te ON te.ref_type = 'content' AND te.ref_id = c.id
UNION ALL
SELECT
    'entities',
    COUNT(*),
    COUNT(te.id),
    ROUND(100.0 * COUNT(te.id) / NULLIF(COUNT(*), 0), 1)
FROM entities e
LEFT JOIN text_embeddings te ON te.ref_type = 'entity' AND te.ref_id = e.id
UNION ALL
SELECT
    'media (images)',
    COUNT(*),
    COUNT(ie.id),
    ROUND(100.0 * COUNT(ie.id) / NULLIF(COUNT(*), 0), 1)
FROM media m
LEFT JOIN image_embeddings ie ON ie.media_id = m.id
WHERE m.type IN ('image', 'screenshot', 'thumbnail');
