-- Migration: Agent Learning Infrastructure
-- Created: 2026-02-04
-- Description: Database tables to support agent self-learning and memory persistence

-- Table to store learned lessons for each agent
CREATE TABLE IF NOT EXISTS agent_lessons (
    id SERIAL PRIMARY KEY,
    agent_name VARCHAR(255) NOT NULL,
    lesson_text TEXT NOT NULL,
    severity VARCHAR(50) DEFAULT 'medium',  -- low, medium, high, critical
    category VARCHAR(100),  -- security, performance, code_quality, etc.
    source_issue_type VARCHAR(100),  -- sql_injection, xss, n+1_query, etc.
    
    -- Metrics
    times_applied INTEGER DEFAULT 0,  -- How many times this lesson was used
    effectiveness_score FLOAT DEFAULT 0.0,  -- 0-1, based on whether it prevented issues
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT true,
    expires_at TIMESTAMP,  -- Optional expiration for time-sensitive lessons
    
    -- Prevent duplicate lessons
    UNIQUE(agent_name, lesson_text)
);

-- Table to track when lessons are applied
CREATE TABLE IF NOT EXISTS agent_lesson_applications (
    id SERIAL PRIMARY KEY,
    lesson_id INTEGER REFERENCES agent_lessons(id) ON DELETE CASCADE,
    agent_name VARCHAR(255) NOT NULL,
    
    -- Context
    pipeline_run_id VARCHAR(255),  -- Link to the pipeline run
    project_name VARCHAR(255),
    
    -- Outcome tracking
    applied_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    prevented_issue BOOLEAN,  -- Did it actually prevent a similar issue?
    outcome_verified_at TIMESTAMP,
    outcome_notes TEXT
);

-- Table to track agent learning history and statistics
CREATE TABLE IF NOT EXISTS agent_learning_stats (
    id SERIAL PRIMARY KEY,
    agent_name VARCHAR(255) NOT NULL UNIQUE,
    
    -- Counts
    total_lessons_learned INTEGER DEFAULT 0,
    active_lessons INTEGER DEFAULT 0,
    total_applications INTEGER DEFAULT 0,
    successful_preventions INTEGER DEFAULT 0,
    
    -- Metrics
    overall_effectiveness FLOAT DEFAULT 0.0,  -- % of applications that prevented issues
    last_lesson_learned_at TIMESTAMP,
    last_lesson_applied_at TIMESTAMP,
    
    -- Timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table to track refinement runs (when AgentRefiner analyzes issues)
CREATE TABLE IF NOT EXISTS agent_refinement_runs (
    id SERIAL PRIMARY KEY,
    
    -- Context
    triggered_by VARCHAR(255),  -- rework, manual, scheduled
    pipeline_run_id VARCHAR(255),
    
    -- Input
    issues_analyzed INTEGER DEFAULT 0,
    issue_types JSON,  -- Array of issue types found
    
    -- Output
    lessons_generated INTEGER DEFAULT 0,
    agents_affected JSON,  -- Array of agent names that received lessons
    
    -- Metadata
    started_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    completed_at TIMESTAMP,
    status VARCHAR(50) DEFAULT 'pending',  -- pending, completed, failed
    error_message TEXT
);

-- Indexes for performance
CREATE INDEX idx_agent_lessons_agent_name ON agent_lessons(agent_name);
CREATE INDEX idx_agent_lessons_active ON agent_lessons(is_active) WHERE is_active = true;
CREATE INDEX idx_agent_lessons_category ON agent_lessons(category);
CREATE INDEX idx_agent_lessons_created_at ON agent_lessons(created_at);

CREATE INDEX idx_lesson_applications_lesson_id ON agent_lesson_applications(lesson_id);
CREATE INDEX idx_lesson_applications_agent_name ON agent_lesson_applications(agent_name);
CREATE INDEX idx_lesson_applications_applied_at ON agent_lesson_applications(applied_at);

CREATE INDEX idx_refinement_runs_created_at ON agent_refinement_runs(started_at);
CREATE INDEX idx_refinement_runs_status ON agent_refinement_runs(status);

-- Function to update agent stats
CREATE OR REPLACE FUNCTION update_agent_learning_stats()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO agent_learning_stats (
        agent_name,
        total_lessons_learned,
        active_lessons,
        last_lesson_learned_at,
        updated_at
    )
    VALUES (
        NEW.agent_name,
        1,
        1,
        NEW.created_at,
        NOW()
    )
    ON CONFLICT (agent_name) 
    DO UPDATE SET
        total_lessons_learned = agent_learning_stats.total_lessons_learned + 1,
        active_lessons = (
            SELECT COUNT(*) 
            FROM agent_lessons 
            WHERE agent_name = NEW.agent_name AND is_active = true
        ),
        last_lesson_learned_at = NEW.created_at,
        updated_at = NOW();
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to auto-update stats when lessons are added
CREATE TRIGGER trigger_update_agent_stats
    AFTER INSERT ON agent_lessons
    FOR EACH ROW
    EXECUTE FUNCTION update_agent_learning_stats();

-- Function to update effectiveness scores
CREATE OR REPLACE FUNCTION calculate_lesson_effectiveness()
RETURNS TRIGGER AS $$
DECLARE
    total_apps INTEGER;
    successful_apps INTEGER;
    effectiveness FLOAT;
BEGIN
    -- Calculate for the specific lesson
    SELECT COUNT(*), COUNT(*) FILTER (WHERE prevented_issue = true)
    INTO total_apps, successful_apps
    FROM agent_lesson_applications
    WHERE lesson_id = NEW.lesson_id;
    
    IF total_apps > 0 THEN
        effectiveness := successful_apps::FLOAT / total_apps::FLOAT;
        
        UPDATE agent_lessons
        SET 
            effectiveness_score = effectiveness,
            times_applied = total_apps,
            updated_at = NOW()
        WHERE id = NEW.lesson_id;
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to update effectiveness when application outcome is verified
CREATE TRIGGER trigger_calculate_effectiveness
    AFTER INSERT OR UPDATE OF prevented_issue ON agent_lesson_applications
    FOR EACH ROW
    WHEN (NEW.outcome_verified_at IS NOT NULL)
    EXECUTE FUNCTION calculate_lesson_effectiveness();

-- Sample data for testing (optional)
-- INSERT INTO agent_lessons (agent_name, lesson_text, category, source_issue_type, severity)
-- VALUES 
--     ('BackendDevelopmentAgent', 'Always use parameterized queries to prevent SQL injection', 'security', 'sql_injection', 'critical'),
--     ('FrontendDevelopmentAgent', 'Escape user content to prevent XSS attacks', 'security', 'xss', 'critical');
