-- On-Demand Classification Jobs Migration
-- 015_on_demand_classification.sql

-- Classification Jobs Table
CREATE TABLE IF NOT EXISTS classification_jobs (
	id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
	type VARCHAR(50) NOT NULL, -- 'on_demand', 'scheduled', 'auto'
	status VARCHAR(50) NOT NULL DEFAULT 'queued', -- 'queued', 'running', 'completed', 'failed', 'cancelled'
	document_ids UUID[], -- Array of document IDs to classify
	field_ids UUID[], -- Array of structured field IDs to classify
	force_reclassify BOOLEAN DEFAULT FALSE, -- If true, reclassify even if already classified
	results JSONB, -- {documents_classified: 10, fields_classified: 5, errors: []}
	error_message TEXT,
	started_at TIMESTAMPTZ,
	completed_at TIMESTAMPTZ,
	created_by UUID REFERENCES users(id) ON DELETE SET NULL,
	created_at TIMESTAMPTZ DEFAULT NOW(),
	updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_classification_jobs_status ON classification_jobs(status);
CREATE INDEX IF NOT EXISTS idx_classification_jobs_type ON classification_jobs(type);
CREATE INDEX IF NOT EXISTS idx_classification_jobs_created_by ON classification_jobs(created_by);
CREATE INDEX IF NOT EXISTS idx_classification_jobs_started_at ON classification_jobs(started_at DESC);

