-- Migration: Add nationality and job columns to residents table
-- Purpose: Store resident's nationality and occupation/job information
-- Created: 2026-05-12

-- Add new columns to residents table
ALTER TABLE residents
ADD COLUMN IF NOT EXISTS nationality VARCHAR(100),
ADD COLUMN IF NOT EXISTS job VARCHAR(255);

-- Add indexes for common queries
CREATE INDEX IF NOT EXISTS idx_residents_nationality ON residents(nationality) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_residents_job ON residents(job) WHERE deleted_at IS NULL;

-- Verify the migration
-- SELECT column_name, data_type FROM information_schema.columns 
-- WHERE table_name = 'residents' AND column_name IN ('nationality', 'job');
