-- Add missing columns to training_tasks table

-- Add name column
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS name VARCHAR(255);
UPDATE training_tasks SET name = 'Training ' || substring(task_id::text, 1, 8) WHERE name IS NULL;
ALTER TABLE training_tasks ALTER COLUMN name SET NOT NULL;

-- Add description column
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS description TEXT;

-- Add admin_token column (for multi-tenant support)
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS admin_token VARCHAR(255);

-- Add task_type column
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS task_type VARCHAR(20) DEFAULT 'train';

-- Add recurring schedule columns
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS cron_expression VARCHAR(50);
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS is_recurring BOOLEAN DEFAULT FALSE;

-- Add result metrics columns (for display without parsing JSONB)
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS result_metrics JSONB;
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS document_count INTEGER DEFAULT 0;
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS metrics_mAP DOUBLE PRECISION;
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS metrics_precision DOUBLE PRECISION;
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS metrics_recall DOUBLE PRECISION;

-- Rename metrics to config if exists
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.columns
               WHERE table_name = 'training_tasks' AND column_name = 'metrics'
               AND NOT EXISTS (SELECT 1 FROM information_schema.columns
                              WHERE table_name = 'training_tasks' AND column_name = 'config')) THEN
        ALTER TABLE training_tasks RENAME COLUMN metrics TO config;
    END IF;
END $$;

-- Add updated_at column
ALTER TABLE training_tasks ADD COLUMN IF NOT EXISTS updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW();

-- Create index on name
CREATE INDEX IF NOT EXISTS idx_training_tasks_name ON training_tasks(name);

-- Create index on metrics_mAP
CREATE INDEX IF NOT EXISTS idx_training_tasks_mAP ON training_tasks(metrics_mAP);