CREATE_CANDIDATES_TABLE.sql

-- ============================================================================
-- CREATE CANDIDATES TABLE FOR TALLY CENTER
-- Run this in Supabase SQL Editor
-- ============================================================================

CREATE TABLE IF NOT EXISTS public.candidates (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    party TEXT,
    is_our_candidate BOOLEAN DEFAULT FALSE,
    color TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc'::text, NOW())
);

-- Insert all 7 candidates
INSERT INTO public.candidates (name, party, is_our_candidate, color) VALUES
('Dan Musinguzi Nabaasa', 'NRM', TRUE, '#FFD700'),
('Aja Apollo', 'Independent', FALSE, '#FF6B6B'),
('Kamara Godfrey', 'FDC', FALSE, '#4ECDC4'),
('Muhwezi Herbert', 'Independent', FALSE, '#95E1D3'),
('Zatwosha Patrick', 'Independent', FALSE, '#F38181'),
('Bakashaba Robert', 'Independent', FALSE, '#AA96DA'),
('Jessy Mutesi', 'Independent', FALSE, '#FCBAD3')
ON CONFLICT DO NOTHING;

-- Enable RLS (optional, adjust based on your security needs)
ALTER TABLE public.candidates ENABLE ROW LEVEL SECURITY;

-- Create policy to allow read access
CREATE POLICY "Allow public read access to candidates"
ON public.candidates FOR SELECT
TO anon, authenticated
USING (true);

-- Create policy to allow insert/update/delete for authenticated users
CREATE POLICY "Allow authenticated users to manage candidates"
ON public.candidates FOR ALL
TO authenticated
USING (true)
WITH CHECK (true);

SELECT * FROM public.candidates ORDER BY is_our_candidate DESC, name;