FIX_DATABASE_STRUCTURE.sql

-- ============================================================================
-- FIX DATABASE STRUCTURE - Make ward nullable in cells table
-- ============================================================================

-- Step 1: Make ward column nullable in cells table
ALTER TABLE cells ALTER COLUMN ward DROP NOT NULL;

-- Step 2: Clear old division data (6 divisions -> 3 divisions)
DELETE FROM divisions WHERE id > 3;

-- Step 3: Update divisions to correct names
UPDATE divisions SET division_name = '01 KABALE CENTRAL' WHERE id = 1;
UPDATE divisions SET division_name = '02 KABALE NORTHERN' WHERE id = 2;
UPDATE divisions SET division_name = '03 KABALE SOUTHERN' WHERE id = 3;

-- Step 4: Create mobilizers table
DROP TABLE IF EXISTS mobilizers CASCADE;

CREATE TABLE mobilizers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    village VARCHAR(100),
    ward VARCHAR(100),
    division VARCHAR(100),
    polling_station VARCHAR(255),
    role VARCHAR(100) DEFAULT 'Cell Mobilizer',
    status VARCHAR(50) DEFAULT 'Active',
    team_leader VARCHAR(100) DEFAULT 'Sam Kanyamukiza',
    date_registered TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes
CREATE INDEX idx_mobilizers_name ON mobilizers(name);
CREATE INDEX idx_mobilizers_phone ON mobilizers(phone);
CREATE INDEX idx_mobilizers_village ON mobilizers(village);
CREATE INDEX idx_mobilizers_ward ON mobilizers(ward);
CREATE INDEX idx_mobilizers_division ON mobilizers(division);
CREATE INDEX idx_mobilizers_status ON mobilizers(status);

-- Enable RLS
ALTER TABLE mobilizers ENABLE ROW LEVEL SECURITY;

-- Create policy
CREATE POLICY "Enable all operations for authenticated users" ON mobilizers
    FOR ALL
    USING (true)
    WITH CHECK (true);

-- Confirm changes
SELECT 'Database structure fixed!' as status;
SELECT division_name FROM divisions ORDER BY id;