-- bmcte_extension_v1.sql — Table for BMCTE experiment results
-- Run on neon-64gb arxiv DB or any PostgreSQL with pgvector

CREATE SCHEMA IF NOT EXISTS bmcte;

CREATE TABLE IF NOT EXISTS bmcte.experiment_runs (
    id              SERIAL PRIMARY KEY,
    experiment_id   TEXT NOT NULL,           -- 'bmcte_extension_v1'
    run_hash        TEXT NOT NULL UNIQUE,    -- sha256 of (N,p,K,seed)
    N               INTEGER NOT NULL,        -- mode count
    p               INTEGER NOT NULL,        -- photon number
    K               INTEGER NOT NULL,        -- sample count
    seed            INTEGER NOT NULL,        -- RNG seed
    lambda_theory   DOUBLE PRECISION,        -- exp(-p²/N)
    entropy_measured DOUBLE PRECISION,       -- Shannon entropy (bits)
    nonzero_modes   INTEGER,                 -- modes with prob > 1e-15
    weight_mean     DOUBLE PRECISION,        -- mean |Per(M)|²
    weight_variance DOUBLE PRECISION,        -- var |Per(M)|²
    weight_cv       DOUBLE PRECISION,        -- coefficient of variation
    ryser_overflow  BOOLEAN DEFAULT FALSE,   -- any NaN/Inf in weights
    used_shots      INTEGER,                 -- successful samples
    runtime_ms      DOUBLE PRECISION,        -- wall-clock ms
    used_gpu        BOOLEAN DEFAULT FALSE,   -- GPU or CPU
    hardware        JSONB,                   -- {cpu, gpu, ram}
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    receipt_hash    TEXT                     -- SHA256 of full receipt JSON
);

CREATE INDEX IF NOT EXISTS idx_bmcte_runs_Np ON bmcte.experiment_runs (N, p);
CREATE INDEX IF NOT EXISTS idx_bmcte_runs_experiment ON bmcte.experiment_runs (experiment_id);

-- Aggregated view for quick analysis
CREATE OR REPLACE VIEW bmcte.summary AS
SELECT
    N, p,
    COUNT(*) as n_runs,
    ROUND(AVG(lambda_theory)::numeric, 6) as lambda_theory,
    ROUND(AVG(entropy_measured)::numeric, 6) as entropy_mean,
    ROUND(STDDEV(entropy_measured)::numeric, 6) as entropy_std,
    ROUND(MIN(entropy_measured)::numeric, 6) as entropy_min,
    ROUND(MAX(entropy_measured)::numeric, 6) as entropy_max,
    ROUND(AVG(nonzero_modes)::numeric, 1) as nonzero_modes_mean,
    ROUND(AVG(weight_cv)::numeric, 6) as weight_cv_mean,
    ROUND(AVG(runtime_ms)::numeric, 1) as runtime_ms_mean,
    SUM(CASE WHEN ryser_overflow THEN 1 ELSE 0 END) as overflow_count
FROM bmcte.experiment_runs
GROUP BY N, p
ORDER BY N, p;