-- Active Learning & Retraining Tables
-- 032_active_learning_tables.sql

CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Labeled Examples (for training)
CREATE TABLE IF NOT EXISTS labeled_examples (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    text_content TEXT NOT NULL,
    predicted_label VARCHAR(255),
    true_label VARCHAR(255) NOT NULL,
    confidence FLOAT,
    source VARCHAR(50) NOT NULL CHECK (source IN ('reviewer_correction', 'manual_label', 'import')),
    reviewed_by UUID REFERENCES users(id) ON DELETE SET NULL,
    reviewed_at TIMESTAMPTZ DEFAULT NOW(),
    metadata JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Training Datasets
CREATE TABLE IF NOT EXISTS training_datasets (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    example_ids UUID[] NOT NULL,
    status VARCHAR(50) DEFAULT 'draft' CHECK (status IN ('draft', 'ready', 'training', 'completed')),
    model_version VARCHAR(100),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Retraining Jobs
CREATE TABLE IF NOT EXISTS retraining_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    dataset_id UUID REFERENCES training_datasets(id) ON DELETE CASCADE,
    model_id UUID,
    status VARCHAR(50) DEFAULT 'queued' CHECK (status IN ('queued', 'running', 'completed', 'failed')),
    started_at TIMESTAMPTZ DEFAULT NOW(),
    completed_at TIMESTAMPTZ,
    error_message TEXT,
    performance_metrics JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_labeled_examples_source ON labeled_examples(source);
CREATE INDEX IF NOT EXISTS idx_labeled_examples_reviewed_at ON labeled_examples(reviewed_at);
CREATE INDEX IF NOT EXISTS idx_training_datasets_status ON training_datasets(status);
CREATE INDEX IF NOT EXISTS idx_retraining_jobs_status ON retraining_jobs(status);

