ADD_VOTERS_TABLE.sql

-- Add Voters Table to Supabase for DNM Dashboard
-- Run this in your Supabase SQL Editor

-- Create voters table
CREATE TABLE IF NOT EXISTS voters (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    gender TEXT,
    dob TEXT,
    age INTEGER,
    village TEXT,
    polling_station TEXT,
    parish TEXT,
    division TEXT,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE(name, dob, polling_station)
);

-- Create indexes for faster searches
CREATE INDEX IF NOT EXISTS idx_voters_name ON voters(name);
CREATE INDEX IF NOT EXISTS idx_voters_polling_station ON voters(polling_station);
CREATE INDEX IF NOT EXISTS idx_voters_village ON voters(village);
CREATE INDEX IF NOT EXISTS idx_voters_division ON voters(division);
CREATE INDEX IF NOT EXISTS idx_voters_parish ON voters(parish);

-- Create text search index for name search
CREATE INDEX IF NOT EXISTS idx_voters_name_search ON voters USING gin(to_tsvector('english', name));

-- Disable RLS for easy access
ALTER TABLE voters DISABLE ROW LEVEL SECURITY;

-- Grant access
GRANT ALL ON voters TO anon, authenticated;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO anon, authenticated;

-- Success message
SELECT '✅ Voters table created successfully! Ready to upload voter data.' as message;