-- Add model_status column to model_versions table
ALTER TABLE model_versions ADD COLUMN model_status text;

-- Optional: Update initial values for model_status based on status for existing records
UPDATE model_versions SET model_status = status WHERE model_status IS NULL;

-- Here are the different status values used in the system:
-- 'TRAINING' or 'TRAINING_IN_PROGRESS': Model is currently being trained
-- 'TRAINING_COMPLETED': Model has completed training successfully
-- 'STARTING': Model is in the process of being started for inference
-- 'RUNNING': Model is running and available for inference
-- 'STOPPING': Model is in the process of being stopped
-- 'STOPPED': Model has been stopped
-- 'ERROR': An error occurred during training or starting

-- Index for faster queries on model_status
CREATE INDEX IF NOT EXISTS idx_model_versions_model_status ON model_versions (model_status);

-- Suggested querying methods:
-- To find the latest trained model:
-- SELECT * FROM model_versions WHERE model_status = 'TRAINING_COMPLETED' ORDER BY training_timestamp DESC LIMIT 1;

-- To find all running models:
-- SELECT * FROM model_versions WHERE model_status = 'RUNNING';

-- To find models with errors:
-- SELECT * FROM model_versions WHERE model_status = 'ERROR'; 