-- RLM (Reinforcement Learning from Memory) PostgreSQL Table Creation Script
-- Genesis-OS RLM Module Infrastructure
-- Version: 1.0.0
-- Date: 2026-02-16
--
-- Usage:
--   psql -h [postgres-host] -U genesis -d genesis_memory -f rlm_create_tables.sql
--
-- OR from Python:
--   import sys
--   sys.path.append('/mnt/e/genesis-system/data/genesis-memory')
--   from elestio_config import PostgresConfig
--   import psycopg2
--   conn = psycopg2.connect(**PostgresConfig.get_connection_params())
--   cur = conn.cursor()
--   cur.execute(open('rlm_create_tables.sql').read())
--   conn.commit()

-- =============================================================================
-- MODULE 1: PREFERENCE LEARNING
-- =============================================================================

-- Stores human preference pairs for Bradley-Terry model training
CREATE TABLE IF NOT EXISTS pl_preference_pairs (
    id SERIAL PRIMARY KEY,
    input_text TEXT NOT NULL,
    output_a TEXT NOT NULL,
    output_b TEXT NOT NULL,
    preference INT CHECK (preference IN (-1, 0, 1)) NOT NULL,
    -- preference: -1 = output_b preferred, 0 = tie, +1 = output_a preferred
    confidence FLOAT DEFAULT 1.0,
    annotator_id TEXT DEFAULT 'kinan',
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_pl_prefs_created ON pl_preference_pairs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_pl_prefs_annotator ON pl_preference_pairs(annotator_id);

-- =============================================================================
-- MODULE 2: REWARD MODEL
-- =============================================================================

-- Stores preference pairs for reward model training
CREATE TABLE IF NOT EXISTS rlm_preference_pairs (
    id SERIAL PRIMARY KEY,
    input_text TEXT,
    chosen_output TEXT NOT NULL,
    rejected_output TEXT NOT NULL,
    annotator_id TEXT DEFAULT 'kinan',
    reward_score_chosen FLOAT,
    reward_score_rejected FLOAT,
    margin FLOAT,
    -- margin = reward_score_chosen - reward_score_rejected
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_rlm_prefs_created ON rlm_preference_pairs(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_rlm_prefs_margin ON rlm_preference_pairs(margin DESC);

-- Tracks annotator performance and agreement rates
CREATE TABLE IF NOT EXISTS rlm_annotator_stats (
    annotator_id TEXT PRIMARY KEY,
    total_annotations INT DEFAULT 0,
    agreement_rate FLOAT,
    -- agreement_rate: % of annotations that agree with consensus
    avg_annotation_time_seconds FLOAT,
    last_annotation_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- =============================================================================
-- MODULE 3: PPO ENGINE (Rollout buffer stored in Redis, not PostgreSQL)
-- =============================================================================

-- Stores PPO training run metadata
CREATE TABLE IF NOT EXISTS rlm_ppo_training_runs (
    id SERIAL PRIMARY KEY,
    run_name TEXT NOT NULL,
    config JSONB NOT NULL,
    -- PPOConfig as JSON
    start_time TIMESTAMP DEFAULT NOW(),
    end_time TIMESTAMP,
    status TEXT CHECK (status IN ('running', 'completed', 'failed')) DEFAULT 'running',
    total_episodes INT DEFAULT 0,
    avg_reward FLOAT,
    avg_kl_divergence FLOAT,
    checkpoint_path TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ppo_runs_created ON rlm_ppo_training_runs(created_at DESC);

-- =============================================================================
-- MODULE 4: DPO TRAINER
-- =============================================================================

-- Stores DPO training run metadata
CREATE TABLE IF NOT EXISTS rlm_dpo_training_runs (
    id SERIAL PRIMARY KEY,
    run_name TEXT NOT NULL,
    config JSONB NOT NULL,
    -- DPOConfig as JSON
    start_time TIMESTAMP DEFAULT NOW(),
    end_time TIMESTAMP,
    status TEXT CHECK (status IN ('running', 'completed', 'failed')) DEFAULT 'running',
    total_steps INT DEFAULT 0,
    final_loss FLOAT,
    avg_implicit_reward FLOAT,
    checkpoint_path TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_dpo_runs_created ON rlm_dpo_training_runs(created_at DESC);

-- =============================================================================
-- MODULE 5: CONSTITUTIONAL AI
-- =============================================================================

-- Logs all constitutional critiques and revisions
CREATE TABLE IF NOT EXISTS cai_critique_log (
    id SERIAL PRIMARY KEY,
    original_output TEXT NOT NULL,
    critique TEXT NOT NULL,
    revised_output TEXT,
    violated_principles TEXT[] DEFAULT '{}',
    -- Array of principle IDs (e.g., ['p002', 'p005'])
    severity TEXT CHECK (severity IN ('low', 'medium', 'high', 'critical')) DEFAULT 'medium',
    revision_count INT DEFAULT 1,
    -- How many revisions before acceptable
    final_harm_score FLOAT,
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_cai_critiques_created ON cai_critique_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_cai_critiques_severity ON cai_critique_log(severity);
CREATE INDEX IF NOT EXISTS idx_cai_critiques_principles ON cai_critique_log USING GIN(violated_principles);

-- Tracks frequency of principle violations over time
CREATE TABLE IF NOT EXISTS cai_principle_violations (
    principle_id TEXT NOT NULL,
    violation_date DATE NOT NULL,
    count INT DEFAULT 1,
    PRIMARY KEY (principle_id, violation_date)
);

CREATE INDEX IF NOT EXISTS idx_cai_violations_date ON cai_principle_violations(violation_date DESC);

-- =============================================================================
-- MODULE 6: RLM GATEWAY (new integration layer)
-- =============================================================================

-- Stores A/B test results for policy deployment
CREATE TABLE IF NOT EXISTS rlm_ab_test_results (
    id SERIAL PRIMARY KEY,
    policy_version TEXT NOT NULL,
    -- e.g., 'v1_baseline', 'v2_dpo_epoch3'
    input_text TEXT NOT NULL,
    output_text TEXT NOT NULL,
    reward_score FLOAT,
    user_feedback TEXT,
    -- 'good', 'bad', 'regenerate', null
    session_id TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_ab_results_created ON rlm_ab_test_results(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_ab_results_policy ON rlm_ab_test_results(policy_version);

-- Tracks RLM training triggers and outcomes
CREATE TABLE IF NOT EXISTS rlm_training_triggers (
    id SERIAL PRIMARY KEY,
    trigger_type TEXT CHECK (trigger_type IN ('threshold', 'scheduled', 'manual')) NOT NULL,
    trigger_reason TEXT,
    -- e.g., "100 new preferences collected"
    new_preferences_count INT,
    training_module TEXT CHECK (training_module IN ('reward_model', 'ppo', 'dpo', 'cai')) NOT NULL,
    status TEXT CHECK (status IN ('queued', 'running', 'completed', 'failed')) DEFAULT 'queued',
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    result_summary JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_training_triggers_created ON rlm_training_triggers(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_training_triggers_status ON rlm_training_triggers(status);

-- =============================================================================
-- HELPER VIEWS
-- =============================================================================

-- View: Recent preferences with human-readable labels
CREATE OR REPLACE VIEW v_recent_preferences AS
SELECT
    id,
    input_text,
    CASE
        WHEN preference = 1 THEN 'A preferred'
        WHEN preference = -1 THEN 'B preferred'
        ELSE 'Tie'
    END as result,
    output_a,
    output_b,
    confidence,
    annotator_id,
    created_at
FROM pl_preference_pairs
ORDER BY created_at DESC
LIMIT 100;

-- View: Constitutional violation frequency by principle
CREATE OR REPLACE VIEW v_constitution_violations_summary AS
SELECT
    principle_id,
    SUM(count) as total_violations,
    MAX(violation_date) as last_violation_date
FROM cai_principle_violations
GROUP BY principle_id
ORDER BY total_violations DESC;

-- View: A/B test performance comparison
CREATE OR REPLACE VIEW v_ab_test_comparison AS
SELECT
    policy_version,
    COUNT(*) as num_outputs,
    AVG(reward_score) as avg_reward,
    COUNT(CASE WHEN user_feedback = 'good' THEN 1 END) as positive_feedback,
    COUNT(CASE WHEN user_feedback = 'bad' THEN 1 END) as negative_feedback,
    COUNT(CASE WHEN user_feedback = 'regenerate' THEN 1 END) as regenerate_requests
FROM rlm_ab_test_results
WHERE created_at > NOW() - INTERVAL '7 days'
GROUP BY policy_version
ORDER BY avg_reward DESC;

-- =============================================================================
-- GRANTS (adjust user as needed)
-- =============================================================================

-- Grant all privileges to genesis user
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO genesis;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO genesis;

-- =============================================================================
-- VERIFICATION QUERIES
-- =============================================================================

-- Run these after table creation to verify:
--
-- SELECT COUNT(*) FROM pl_preference_pairs;
-- SELECT COUNT(*) FROM rlm_preference_pairs;
-- SELECT COUNT(*) FROM cai_critique_log;
-- SELECT * FROM v_constitution_violations_summary;
-- SELECT * FROM v_ab_test_comparison;
--
-- Expected: 0 rows initially, tables ready for data

-- =============================================================================
-- END OF RLM TABLE CREATION SCRIPT
-- =============================================================================
