-- Genesis Mother Database Schema (v1.0)
-- Implementing Patent P4 (Immutable Audit Trail) and P9 (Adaptive Thresholds)

-- 1. Immutable Audit Trail (Patent P4)
CREATE TABLE IF NOT EXISTS audit_trail (
    id SERIAL PRIMARY KEY,
    timestamp TIMESTAMP DEFAULT NOW(),
    event_type VARCHAR(50) NOT NULL,
    worker_id VARCHAR(100),
    input_hash VARCHAR(64),
    output_hash VARCHAR(64),
    validation_passed BOOLEAN,
    gate1_score FLOAT,
    gate2_score FLOAT,
    gate3_score FLOAT,
    overall_score FLOAT,
    validation_report JSONB,
    metadata JSONB,
    -- Immutability: enforced by application logic and append-only policy
    created_at TIMESTAMP DEFAULT NOW()
);

-- Index for fast queries
CREATE INDEX IF NOT EXISTS idx_audit_timestamp ON audit_trail(timestamp DESC);
CREATE INDEX IF NOT EXISTS idx_audit_worker ON audit_trail(worker_id);
CREATE INDEX IF NOT EXISTS idx_audit_validation ON audit_trail(validation_passed);

-- 2. Worker Trust Scores (Patent P9 - Adaptive Thresholds)
CREATE TABLE IF NOT EXISTS worker_trust (
    worker_id VARCHAR(100) PRIMARY KEY,
    total_validations INTEGER DEFAULT 0,
    passed_validations INTEGER DEFAULT 0,
    trust_score FLOAT DEFAULT 0.5,
    last_updated TIMESTAMP DEFAULT NOW(),
    metadata JSONB
);

-- 3. Validation Thresholds (Patent P9 - Adaptive)
CREATE TABLE IF NOT EXISTS validation_config (
    id SERIAL PRIMARY KEY,
    config_key VARCHAR(100) UNIQUE NOT NULL,
    config_value JSONB NOT NULL,
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Default Thresholds
INSERT INTO validation_config (config_key, config_value) VALUES
    ('gate1_threshold', '{"min_score": 0.8, "required_checks": ["signature", "audit", "privacy"]}'),
    ('gate2_threshold', '{"min_score": 0.8, "required_checks": ["consensus", "confidence", "risk"]}'),
    ('gate3_threshold', '{"min_score": 0.8, "required_checks": ["currency", "hallucination", "adaptive"]}')
ON CONFLICT (config_key) DO NOTHING;
