-- Migration: Self-Improvement System Tables
-- Part of SELF_IMPROVEMENT_DESIGN_PLAN implementation
-- Creates tables for autonomous self-improvement loop: Monitor → Analyzer → Executor → Learner

-- ============================================================================
-- METRIC BASELINES
-- Hybrid baseline tracking using EMA for trend detection and rolling average
-- for stable thresholds. Scored 0.84/1.0 in design analysis.
-- ============================================================================
CREATE TABLE metric_baselines (
    id TEXT PRIMARY KEY,
    metric_name TEXT NOT NULL UNIQUE,

    -- Rolling average baseline (24-hour window)
    rolling_avg_value REAL NOT NULL DEFAULT 0.0,
    rolling_avg_sample_count INTEGER NOT NULL DEFAULT 0,
    rolling_avg_window_start TIMESTAMP,

    -- EMA baseline (alpha = 0.1 for trend detection)
    ema_value REAL NOT NULL DEFAULT 0.0,
    ema_alpha REAL NOT NULL DEFAULT 0.1,

    -- Thresholds derived from baselines
    warning_threshold REAL,
    critical_threshold REAL,

    -- Metadata
    last_updated TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    metadata TEXT  -- JSON for additional context
);

CREATE INDEX idx_baselines_metric ON metric_baselines(metric_name);

-- ============================================================================
-- SELF-IMPROVEMENT DIAGNOSES
-- Records of detected issues with root cause analysis and suggested actions.
-- Generated by Analyzer phase using reflection-v1 pipe.
-- ============================================================================
CREATE TABLE self_diagnoses (
    id TEXT PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Trigger information
    trigger_metric TEXT NOT NULL,           -- 'error_rate', 'latency', 'quality', 'fallback_rate'
    trigger_type TEXT NOT NULL,             -- JSON: TriggerMetric enum serialization
    observed_value REAL NOT NULL,
    baseline_value REAL NOT NULL,
    deviation_pct REAL NOT NULL,            -- How far from baseline (%)

    -- Severity and description
    severity TEXT NOT NULL,                 -- 'info', 'warning', 'high', 'critical'
    description TEXT NOT NULL,
    suspected_cause TEXT,

    -- Action
    suggested_action TEXT NOT NULL,         -- JSON: SuggestedAction enum serialization
    action_rationale TEXT,

    -- Lifecycle
    status TEXT NOT NULL DEFAULT 'pending', -- 'pending', 'executing', 'completed', 'rolled_back', 'superseded', 'awaiting_approval'
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_diagnoses_status ON self_diagnoses(status);
CREATE INDEX idx_diagnoses_created ON self_diagnoses(created_at DESC);
CREATE INDEX idx_diagnoses_severity ON self_diagnoses(severity);
CREATE INDEX idx_diagnoses_trigger ON self_diagnoses(trigger_metric);

-- ============================================================================
-- ACTION EXECUTION HISTORY
-- Complete record of executed self-improvement actions with before/after
-- metrics for reward calculation and learning.
-- ============================================================================
CREATE TABLE self_improvement_actions (
    id TEXT PRIMARY KEY,
    diagnosis_id TEXT NOT NULL REFERENCES self_diagnoses(id),

    -- Action details
    action_type TEXT NOT NULL,              -- 'adjust_param', 'toggle_feature', 'restart_service', 'clear_cache', 'scale_resource', 'no_op'
    action_params TEXT NOT NULL,            -- JSON: full action specification

    -- State snapshots for rollback
    pre_state TEXT NOT NULL,                -- JSON: config state before change
    post_state TEXT,                        -- JSON: config state after change (null until applied)

    -- Metrics snapshots for reward calculation
    metrics_before TEXT NOT NULL,           -- JSON: {error_rate, latency_p95_ms, quality_score, sample_count}
    metrics_after TEXT,                     -- JSON: same schema, collected after stabilization

    -- Timing
    executed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    verified_at TIMESTAMP,                  -- When post-execution verification completed
    completed_at TIMESTAMP,                 -- When learning phase finished

    -- Outcome
    outcome TEXT NOT NULL DEFAULT 'pending', -- 'pending', 'success', 'failed', 'rolled_back'
    rollback_reason TEXT,

    -- Reward calculation (normalized to [-1.0, 1.0])
    normalized_reward REAL,
    reward_breakdown TEXT,                  -- JSON: {error_reward, latency_reward, quality_reward, weights}

    -- Learning (populated by Learner phase)
    lessons_learned TEXT,                   -- JSON: insights from reflection-v1

    -- Pipe tracking (for pipe effectiveness metrics)
    diagnosis_pipe TEXT,
    diagnosis_latency_ms INTEGER,
    diagnosis_parse_success INTEGER,        -- Boolean: 1 = success, 0 = failure
    action_pipe TEXT,
    action_latency_ms INTEGER,
    validation_performed INTEGER,           -- Boolean: 1 = detection-v1 was run
    validation_passed INTEGER               -- Boolean: 1 = no critical biases detected
);

CREATE INDEX idx_actions_diagnosis ON self_improvement_actions(diagnosis_id);
CREATE INDEX idx_actions_outcome ON self_improvement_actions(outcome);
CREATE INDEX idx_actions_type ON self_improvement_actions(action_type);
CREATE INDEX idx_actions_reward ON self_improvement_actions(normalized_reward);
CREATE INDEX idx_actions_executed ON self_improvement_actions(executed_at DESC);

-- ============================================================================
-- CIRCUIT BREAKER STATE
-- Tracks circuit breaker state to prevent cascading failures from repeated
-- bad self-improvement actions. Default: 3 failures opens, 2 successes closes.
-- ============================================================================
CREATE TABLE circuit_breaker_state (
    id TEXT PRIMARY KEY DEFAULT 'main',

    -- State machine
    state TEXT NOT NULL DEFAULT 'closed',   -- 'closed', 'open', 'half_open'

    -- Counters
    consecutive_failures INTEGER NOT NULL DEFAULT 0,
    consecutive_successes INTEGER NOT NULL DEFAULT 0,
    total_failures INTEGER NOT NULL DEFAULT 0,
    total_successes INTEGER NOT NULL DEFAULT 0,

    -- Timing
    last_failure TIMESTAMP,
    last_success TIMESTAMP,
    last_state_change TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Configuration (stored for audit and consistency)
    failure_threshold INTEGER NOT NULL DEFAULT 3,
    success_threshold INTEGER NOT NULL DEFAULT 2,
    recovery_timeout_secs INTEGER NOT NULL DEFAULT 3600  -- 1 hour
);

-- Insert default circuit breaker row
INSERT OR IGNORE INTO circuit_breaker_state (id) VALUES ('main');

-- ============================================================================
-- COOLDOWN PERIODS
-- Enforces minimum time between self-improvement actions to allow metrics
-- to stabilize and prevent oscillation.
-- ============================================================================
CREATE TABLE cooldown_periods (
    id TEXT PRIMARY KEY,
    started_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ends_at TIMESTAMP NOT NULL,
    reason TEXT,
    triggered_by TEXT REFERENCES self_improvement_actions(id),
    is_active INTEGER NOT NULL DEFAULT 1    -- Boolean: 1 = active, 0 = expired
);

CREATE INDEX idx_cooldown_active ON cooldown_periods(is_active, ends_at);
CREATE INDEX idx_cooldown_ends ON cooldown_periods(ends_at);

-- ============================================================================
-- ACTION EFFECTIVENESS TRACKING
-- Aggregated statistics per action type and signature for learning which
-- actions are most effective in different situations.
-- ============================================================================
CREATE TABLE action_effectiveness (
    id TEXT PRIMARY KEY,
    action_type TEXT NOT NULL,
    action_signature TEXT NOT NULL,         -- Hash of action params for grouping similar actions

    -- Statistics
    total_attempts INTEGER NOT NULL DEFAULT 0,
    successful_attempts INTEGER NOT NULL DEFAULT 0,
    failed_attempts INTEGER NOT NULL DEFAULT 0,
    rolled_back_attempts INTEGER NOT NULL DEFAULT 0,

    -- Reward statistics
    avg_reward REAL NOT NULL DEFAULT 0.0,
    max_reward REAL,
    min_reward REAL,
    reward_variance REAL,                   -- For statistical analysis

    -- Timing
    first_attempt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_attempt TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Confidence in this action type
    effectiveness_score REAL NOT NULL DEFAULT 0.5,  -- [0, 1]

    UNIQUE(action_type, action_signature)
);

CREATE INDEX idx_effectiveness_type ON action_effectiveness(action_type);
CREATE INDEX idx_effectiveness_score ON action_effectiveness(effectiveness_score DESC);

-- ============================================================================
-- PIPE EFFECTIVENESS TRACKING
-- Tracks effectiveness of different Langbase pipes for self-improvement
-- to inform decisions about when to create specialized pipes.
-- ============================================================================
CREATE TABLE pipe_effectiveness (
    id TEXT PRIMARY KEY,
    pipe_name TEXT NOT NULL UNIQUE,
    phase TEXT NOT NULL,                    -- 'diagnosis', 'action_selection', 'validation', 'learning'

    -- Usage statistics
    total_calls INTEGER NOT NULL DEFAULT 0,
    successful_parses INTEGER NOT NULL DEFAULT 0,
    parse_failures INTEGER NOT NULL DEFAULT 0,

    -- Latency statistics
    avg_latency_ms REAL,
    min_latency_ms INTEGER,
    max_latency_ms INTEGER,

    -- Quality metrics (phase-specific)
    avg_quality_score REAL,                 -- For diagnosis: accuracy; for actions: positive reward rate

    -- Timing
    first_use TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_use TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_pipe_effectiveness_phase ON pipe_effectiveness(phase);
CREATE INDEX idx_pipe_effectiveness_quality ON pipe_effectiveness(avg_quality_score DESC);

-- ============================================================================
-- SYSTEM SETTINGS
-- Key-value store for system-level settings like enable/disable state.
-- ============================================================================
CREATE TABLE IF NOT EXISTS system_settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL,
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Insert default self-improvement enabled state
INSERT OR IGNORE INTO system_settings (key, value) VALUES ('self_improvement_enabled', 'true');
