-- New database schema for three-model OCR comparison system
-- This script creates a new table with model information

-- Drop the existing table if it exists
DROP TABLE IF EXISTS ocr_votes;

-- Create the new table with model information
CREATE TABLE ocr_votes (
    id SERIAL PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    model_a VARCHAR(50) NOT NULL,  -- 'gemini', 'mistral', or 'openai'
    model_b VARCHAR(50) NOT NULL,  -- 'gemini', 'mistral', or 'openai'
    model_a_output TEXT NOT NULL,
    model_b_output TEXT NOT NULL,
    vote VARCHAR(50) NOT NULL,     -- 'model_a' or 'model_b'
    image_url TEXT,
    timestamp VARCHAR(50) NOT NULL, -- Format: YYYY-MM-DD HH:MM:SS
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Create indexes for better performance
CREATE INDEX idx_ocr_votes_username ON ocr_votes(username);
CREATE INDEX idx_ocr_votes_timestamp ON ocr_votes(timestamp);
CREATE INDEX idx_ocr_votes_vote ON ocr_votes(vote);
CREATE INDEX idx_ocr_votes_models ON ocr_votes(model_a, model_b);