-- Model versions table for tracking trained model deployments.
-- Each training run can produce a model version for inference.

CREATE TABLE IF NOT EXISTS model_versions (
    version_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    version VARCHAR(50) NOT NULL,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    model_path VARCHAR(512) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'inactive',
    is_active BOOLEAN NOT NULL DEFAULT FALSE,

    -- Training association
    task_id UUID REFERENCES training_tasks(task_id) ON DELETE SET NULL,
    dataset_id UUID REFERENCES training_datasets(dataset_id) ON DELETE SET NULL,

    -- Training metrics
    metrics_mAP DOUBLE PRECISION,
    metrics_precision DOUBLE PRECISION,
    metrics_recall DOUBLE PRECISION,
    document_count INTEGER NOT NULL DEFAULT 0,

    -- Training configuration snapshot
    training_config JSONB,

    -- File info
    file_size BIGINT,

    -- Timestamps
    trained_at TIMESTAMP WITH TIME ZONE,
    activated_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_model_versions_version ON model_versions(version);
CREATE INDEX IF NOT EXISTS idx_model_versions_status ON model_versions(status);
CREATE INDEX IF NOT EXISTS idx_model_versions_is_active ON model_versions(is_active);
CREATE INDEX IF NOT EXISTS idx_model_versions_task_id ON model_versions(task_id);
CREATE INDEX IF NOT EXISTS idx_model_versions_dataset_id ON model_versions(dataset_id);
CREATE INDEX IF NOT EXISTS idx_model_versions_created ON model_versions(created_at);

-- Ensure only one active model at a time
CREATE UNIQUE INDEX IF NOT EXISTS idx_model_versions_single_active
    ON model_versions(is_active) WHERE is_active = TRUE;

-- Comment
COMMENT ON TABLE model_versions IS 'Trained model versions for inference deployment';