CREATE_CONTACTS_SYSTEM_FIXED.sql

-- ============================================================================
-- CONTACTS SYSTEM - SEPARATE FROM VOTERS (DON'T INFLATE VOTER COUNT)
-- ============================================================================

-- Enable pg_trgm extension for fuzzy text search
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Create contacts table for field-collected contacts
CREATE TABLE IF NOT EXISTS contacts (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    village VARCHAR(100),
    ward VARCHAR(100),
    division VARCHAR(100),
    cell_name VARCHAR(100),
    cell_id INTEGER,

    -- Link to voter if matched
    voter_id INTEGER,
    match_confidence VARCHAR(20) DEFAULT 'unmatched', -- 'exact', 'high', 'medium', 'low', 'unmatched'

    -- Source tracking
    source VARCHAR(100), -- 'Contacts 1.pdf', 'DNM Master Database', etc.
    date_collected TIMESTAMP,
    collector_name VARCHAR(100),

    -- Additional fields
    gender VARCHAR(10),
    age INTEGER,
    occupation VARCHAR(100),
    supporter_status VARCHAR(50) DEFAULT 'Unknown',
    contacted BOOLEAN DEFAULT false,
    notes TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

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

-- Create indexes for fast searching
CREATE INDEX IF NOT EXISTS idx_contacts_name ON contacts(name);
CREATE INDEX IF NOT EXISTS idx_contacts_phone ON contacts(phone);
CREATE INDEX IF NOT EXISTS idx_contacts_village ON contacts(village);
CREATE INDEX IF NOT EXISTS idx_contacts_cell_name ON contacts(cell_name);
CREATE INDEX IF NOT EXISTS idx_contacts_voter_id ON contacts(voter_id);
CREATE INDEX IF NOT EXISTS idx_contacts_source ON contacts(source);
CREATE INDEX IF NOT EXISTS idx_contacts_match_confidence ON contacts(match_confidence);

-- Create full-text search index (now that pg_trgm is enabled)
CREATE INDEX IF NOT EXISTS idx_contacts_name_trgm ON contacts USING gin(name gin_trgm_ops);

-- Add foreign key to voters (optional)
-- ALTER TABLE contacts ADD CONSTRAINT fk_contacts_voter
--     FOREIGN KEY (voter_id) REFERENCES voters(id);

-- Create view for unified search (voters + contacts)
CREATE OR REPLACE VIEW searchable_people AS
SELECT
    'voter' as record_type,
    id,
    name,
    phone_number as phone,
    village,
    division,
    cell_name,
    gender,
    age,
    supporter_status,
    contacted,
    notes,
    NULL as source,
    NULL as voter_id,
    created_at
FROM voters
UNION ALL
SELECT
    'contact' as record_type,
    id,
    name,
    phone,
    village,
    division,
    cell_name,
    gender,
    age,
    supporter_status,
    contacted,
    notes,
    source,
    voter_id,
    created_at
FROM contacts;

-- Verify creation
SELECT 'Contacts system created successfully!' as message;
SELECT COUNT(*) as contact_count FROM contacts;
SELECT COUNT(*) as voter_count FROM voters;