MasoomChoudhury/processor

1-- Create custom ENUM types for status fields for data integrity2CREATE TYPE batch_status AS ENUM ('collecting', 'processing', 'completed', 'failed');3CREATE TYPE job_status AS ENUM ('pending', 'ocr_processing', 'ai_processing', 'completed', 'failed');4 5-- Table to manage batches of jobs6CREATE TABLE batches (7  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),8  status batch_status NOT NULL DEFAULT 'collecting',9  image_count INT NOT NULL DEFAULT 0,10  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),11  processed_at TIMESTAMPTZ12);13 14-- Table to manage individual image processing jobs15CREATE TABLE jobs (16  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),17  batch_id UUID REFERENCES batches(id) ON DELETE SET NULL,18  image_path TEXT NOT NULL,19  ocr_text TEXT,20  gemini_response JSONB,21  claude_response JSONB,22  status job_status NOT NULL DEFAULT 'pending',23  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),24  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()25);26 27-- Enable Realtime broadcasting on both tables28ALTER TABLE batches REPLICA IDENTITY FULL;29ALTER TABLE jobs REPLICA IDENTITY FULL;30CREATE PUBLICATION supabase_realtime FOR TABLE batches, jobs;