-- Hive atomic_facts schema
-- Connect to database `brain_atomic` before running this file.
-- Example: psql -h /Volumes/External/AI_Brain/pg -U postgres -d brain_atomic -f ~/Desktop/seed.sql

BEGIN;

CREATE TABLE IF NOT EXISTS atomic_facts (
    hash        text PRIMARY KEY,
    sentence    text NOT NULL,
    category    text NOT NULL,
    source      text NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now()
);

-- Helpful indexes for reads
CREATE INDEX IF NOT EXISTS atomic_facts_category_idx ON atomic_facts (category);
CREATE INDEX IF NOT EXISTS atomic_facts_created_at_idx ON atomic_facts (created_at);

-- Statistical reasoning persistence so ANI telemetry can be replayed from Postgres
CREATE TABLE IF NOT EXISTS statistical_reasoning_insights (
    id                   bigserial PRIMARY KEY,
    prompt               text NOT NULL,
    expected_roi         numeric NOT NULL,
    expected_revenue     numeric NOT NULL,
    confidence           numeric NOT NULL,
    success_probability  numeric NOT NULL,
    volatility_adjustment numeric NOT NULL,
    scenario_spread      jsonb NOT NULL,
    drivers              jsonb NOT NULL,
    narrative            text NOT NULL,
    notes                text,
    created_at           timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS statistical_reasoning_prompt_idx
    ON statistical_reasoning_insights (prompt);

CREATE INDEX IF NOT EXISTS statistical_reasoning_created_at_idx
    ON statistical_reasoning_insights (created_at);

COMMIT;