ADD_POLLING_STATION_SCHEMA.sql

-- ============================================================================
-- ADD POLLING STATION SUPPORT & COHERENT SYSTEM STRUCTURE
-- ============================================================================

-- STEP 1: Add polling_station column to voters
ALTER TABLE voters ADD COLUMN IF NOT EXISTS polling_station VARCHAR(255);
ALTER TABLE voters ADD COLUMN IF NOT EXISTS ward VARCHAR(100);

-- Create indexes for filtering
CREATE INDEX IF NOT EXISTS idx_voters_polling_station ON voters(polling_station);
CREATE INDEX IF NOT EXISTS idx_voters_ward ON voters(ward);
CREATE INDEX IF NOT EXISTS idx_voters_division ON voters(division);

-- STEP 2: Create polling_stations table with official statistics
CREATE TABLE IF NOT EXISTS polling_stations (
    id SERIAL PRIMARY KEY,
    station_name VARCHAR(255) NOT NULL UNIQUE,
    division VARCHAR(100),
    ward VARCHAR(100),
    expected_voters INTEGER,
    actual_voters INTEGER DEFAULT 0,

    -- Auto-calculated from voters table
    registered_voters INTEGER GENERATED ALWAYS AS (actual_voters) STORED,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Enable RLS
ALTER TABLE polling_stations ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS "Enable all operations for authenticated users" ON polling_stations;
CREATE POLICY "Enable all operations for authenticated users" ON polling_stations
    FOR ALL USING (true) WITH CHECK (true);

-- STEP 3: Add foreign key relationships (data coherence)
-- Note: Commented out to avoid breaking existing data
-- ALTER TABLE voters ADD CONSTRAINT fk_voters_cell
--     FOREIGN KEY (cell_id) REFERENCES cells(id) ON DELETE SET NULL;

-- STEP 4: Create materialized view for statistics
CREATE MATERIALIZED VIEW IF NOT EXISTS voter_statistics AS
SELECT
    v.division,
    v.ward,
    v.cell_name,
    v.polling_station,
    COUNT(*) as voter_count,
    COUNT(DISTINCT v.cell_name) as cells_in_polling_station,
    ps.expected_voters,
    (COUNT(*) - COALESCE(ps.expected_voters, 0)) as discrepancy
FROM voters v
LEFT JOIN polling_stations ps ON v.polling_station = ps.station_name
GROUP BY v.division, v.ward, v.cell_name, v.polling_station, ps.expected_voters;

-- Create index on materialized view
CREATE INDEX IF NOT EXISTS idx_voter_stats_division ON voter_statistics(division);
CREATE INDEX IF NOT EXISTS idx_voter_stats_ward ON voter_statistics(ward);
CREATE INDEX IF NOT EXISTS idx_voter_stats_cell ON voter_statistics(cell_name);
CREATE INDEX IF NOT EXISTS idx_voter_stats_polling ON voter_statistics(polling_station);

-- STEP 5: Create view for filtering (replaces searchable_people with more fields)
CREATE OR REPLACE VIEW searchable_people_filtered AS
SELECT
    'voter' as record_type,
    v.id,
    v.name,
    v.phone_number as phone,
    v.village,
    v.division,
    v.ward,
    v.cell_name,
    v.cell_id,
    v.polling_station,
    v.gender,
    v.age,
    v.supporter_status,
    v.contacted,
    v.notes,
    NULL as source,
    NULL as voter_id,
    v.created_at,

    -- Additional fields for filtering/sorting
    c.ward as cell_ward,
    c.division as cell_division
FROM voters v
LEFT JOIN cells c ON v.cell_id = c.id
UNION ALL
SELECT
    'contact' as record_type,
    id,
    name,
    phone,
    village,
    division,
    ward,
    cell_name,
    cell_id,
    NULL as polling_station,
    gender,
    age,
    supporter_status,
    contacted,
    notes,
    source,
    voter_id,
    created_at,

    NULL as cell_ward,
    NULL as cell_division
FROM contacts;

-- STEP 6: Create function to refresh statistics (call after updates)
CREATE OR REPLACE FUNCTION refresh_voter_statistics()
RETURNS void AS $$
BEGIN
    REFRESH MATERIALIZED VIEW voter_statistics;
END;
$$ LANGUAGE plpgsql;

-- STEP 7: Verify creation
SELECT 'Polling station system created!' as message;
SELECT 'Added: polling_station, ward columns to voters' as step1;
SELECT 'Created: polling_stations table' as step2;
SELECT 'Created: voter_statistics materialized view' as step3;
SELECT 'Created: searchable_people_filtered view with all filter fields' as step4;