-- Fix missing columns in cases table
-- Migration: 024_fix_cases_columns.sql

-- Add missing columns to cases table
ALTER TABLE cases ADD COLUMN IF NOT EXISTS priority VARCHAR(50) DEFAULT 'medium';
ALTER TABLE cases ADD COLUMN IF NOT EXISTS filing_date TIMESTAMPTZ;
ALTER TABLE cases ADD COLUMN IF NOT EXISTS court VARCHAR(255);
ALTER TABLE cases ADD COLUMN IF NOT EXISTS judge VARCHAR(255);
ALTER TABLE cases ADD COLUMN IF NOT EXISTS case_value NUMERIC;
ALTER TABLE cases ADD COLUMN IF NOT EXISTS key_dates JSONB DEFAULT '{}'::jsonb;
ALTER TABLE cases ADD COLUMN IF NOT EXISTS team JSONB DEFAULT '{}'::jsonb;
ALTER TABLE cases ADD COLUMN IF NOT EXISTS metadata JSONB DEFAULT '{}'::jsonb;

-- Add missing columns to review_sets table
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS priority VARCHAR(50) DEFAULT 'medium';
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS target_completion_date TIMESTAMPTZ;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS review_type VARCHAR(50) DEFAULT 'single';
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS assignment_method VARCHAR(50) DEFAULT 'manual';
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS lead_reviewer_id UUID REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS coding_fields JSONB DEFAULT '[]'::jsonb;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS custom_fields JSONB DEFAULT '[]'::jsonb;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS ai_assistance JSONB DEFAULT '{}'::jsonb;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS analytics JSONB DEFAULT '{}'::jsonb;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS total_items INT DEFAULT 0;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS reviewed_items INT DEFAULT 0;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS flagged_items INT DEFAULT 0;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS relevant_count INT DEFAULT 0;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS irrelevant_count INT DEFAULT 0;
ALTER TABLE review_sets ADD COLUMN IF NOT EXISTS privileged_count INT DEFAULT 0;

-- Add missing columns to holds table
ALTER TABLE holds ADD COLUMN IF NOT EXISTS notice_template_id UUID;
ALTER TABLE holds ADD COLUMN IF NOT EXISTS notice_text TEXT;
ALTER TABLE holds ADD COLUMN IF NOT EXISTS acknowledgment_required BOOLEAN DEFAULT FALSE;
ALTER TABLE holds ADD COLUMN IF NOT EXISTS reminder_schedule JSONB DEFAULT '{}'::jsonb;

-- Add missing columns to hold_notifications table
ALTER TABLE hold_notifications ADD COLUMN IF NOT EXISTS custodian_email VARCHAR(255);
ALTER TABLE hold_notifications ADD COLUMN IF NOT EXISTS delivered_at TIMESTAMPTZ;

-- Add missing columns to collections table
ALTER TABLE collections ADD COLUMN IF NOT EXISTS deduplication_enabled BOOLEAN DEFAULT FALSE;
ALTER TABLE collections ADD COLUMN IF NOT EXISTS threading_enabled BOOLEAN DEFAULT FALSE;

-- Add missing columns to case_custodians table
ALTER TABLE case_custodians ADD COLUMN IF NOT EXISTS custodian_name VARCHAR(255);
ALTER TABLE case_custodians ADD COLUMN IF NOT EXISTS custodian_email VARCHAR(255);
ALTER TABLE case_custodians ADD COLUMN IF NOT EXISTS acknowledged_at TIMESTAMPTZ;

-- Create indexes for better performance
CREATE INDEX IF NOT EXISTS idx_cases_priority ON cases(priority);
CREATE INDEX IF NOT EXISTS idx_cases_filing_date ON cases(filing_date);
CREATE INDEX IF NOT EXISTS idx_review_sets_priority ON review_sets(priority);
CREATE INDEX IF NOT EXISTS idx_review_sets_lead_reviewer ON review_sets(lead_reviewer_id);

