-- Trainable Classifiers Migration
-- 013_trainable_classifiers.sql

-- Trainable Classifiers Table
CREATE TABLE IF NOT EXISTS trainable_classifiers (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	name VARCHAR(255) NOT NULL,
	description TEXT,
	task VARCHAR(50) NOT NULL, -- 'ner', 'text_classification', 'sentence_similarity'
	status VARCHAR(50) NOT NULL DEFAULT 'draft', -- 'draft', 'training', 'active', 'inactive', 'failed'
	model_version VARCHAR(50),
	performance_metrics JSONB DEFAULT '{}', -- {f1, precision, recall, accuracy}
	trained_at TIMESTAMPTZ,
	created_by UUID REFERENCES users(id) ON DELETE SET NULL,
	created_at TIMESTAMPTZ DEFAULT NOW(),
	updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Training Data Table
CREATE TABLE IF NOT EXISTS training_data (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	classifier_id UUID REFERENCES trainable_classifiers(id) ON DELETE CASCADE,
	data_type VARCHAR(50) NOT NULL, -- 'training', 'validation', 'test'
	file_name VARCHAR(500),
	file_path VARCHAR(1000),
	file_size BIGINT,
	row_count INT,
	uploaded_at TIMESTAMPTZ DEFAULT NOW(),
	uploaded_by UUID REFERENCES users(id) ON DELETE SET NULL
);

-- Training Jobs Table
CREATE TABLE IF NOT EXISTS training_jobs (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	classifier_id UUID REFERENCES trainable_classifiers(id) ON DELETE CASCADE,
	status VARCHAR(50) NOT NULL DEFAULT 'queued', -- 'queued', 'running', 'completed', 'failed'
	options JSONB DEFAULT '{}', -- {epochs, batch_size, learning_rate}
	started_at TIMESTAMPTZ,
	completed_at TIMESTAMPTZ,
	error_message TEXT,
	performance_metrics JSONB,
	created_by UUID REFERENCES users(id) ON DELETE SET NULL,
	created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_trainable_classifiers_status ON trainable_classifiers(status);
CREATE INDEX IF NOT EXISTS idx_trainable_classifiers_task ON trainable_classifiers(task);
CREATE INDEX IF NOT EXISTS idx_training_data_classifier ON training_data(classifier_id);
CREATE INDEX IF NOT EXISTS idx_training_jobs_classifier ON training_jobs(classifier_id);
CREATE INDEX IF NOT EXISTS idx_training_jobs_status ON training_jobs(status);

