-- Drop existing table
DROP TABLE IF EXISTS providers;

-- Create table with updated schema
CREATE TABLE providers (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- General Details
    county VARCHAR(50) NOT NULL,
    provider_code VARCHAR(50) NOT NULL UNIQUE,
    chp_name VARCHAR(100) NOT NULL,
    number_of_children INTEGER NOT NULL,
    marital_status VARCHAR(50) NOT NULL,
    education_level VARCHAR(50) NOT NULL,
    
    -- Interviewee Details
    interviewee_name VARCHAR(100) NOT NULL,
    interviewee_contact VARCHAR(20) NOT NULL,
    interviewee_gender VARCHAR(20) NOT NULL,
    interviewee_age INTEGER NOT NULL,
    religion VARCHAR(50) NOT NULL,
    
    -- Operations
    owner_status VARCHAR(50) NOT NULL,
    owner_type VARCHAR(50) NOT NULL,
    hours_per_day INTEGER NOT NULL,
    days_per_week INTEGER NOT NULL,
    years_operating INTEGER NOT NULL,
    overnight_care VARCHAR(50) NOT NULL,
    hbcc_story TEXT NOT NULL,
    
    -- Location
    sub_county VARCHAR(100) NOT NULL,
    ward VARCHAR(100) NOT NULL,
    village VARCHAR(100) NOT NULL,
    landmark TEXT NOT NULL,
    
    -- Child Age Group
    age_group_children_count INTEGER NOT NULL,
    age_groups TEXT NOT NULL,
    youngest_child_story TEXT NOT NULL,
    
    -- Caregiver Details
    has_assistant_caregivers VARCHAR(50) NOT NULL,
    assistant_caregivers_timing VARCHAR(50) NOT NULL,
    special_needs_children VARCHAR(50) NOT NULL,
    
    -- Special Needs
    special_needs_children_count VARCHAR(50) NOT NULL,
    special_needs_age_groups TEXT NOT NULL,
    special_needs_types TEXT NOT NULL,
    screening_need TEXT NOT NULL,
    
    -- Funding
    fee_structure VARCHAR(50) NOT NULL,
    fee_per_child DECIMAL(10,2) NOT NULL,
    payment_method VARCHAR(50) NOT NULL,
    estimated_daily_income DECIMAL(10,2) NOT NULL,
    in_kind_contribution TEXT NOT NULL,
    
    -- Expenses
    daily_expense DECIMAL(10,2) NOT NULL,
    main_expenses TEXT NOT NULL,
    food_expense DECIMAL(10,2) NOT NULL,
    supplies_expense DECIMAL(10,2) NOT NULL,
    utilities_expense DECIMAL(10,2) NOT NULL,
    rent_expense DECIMAL(10,2) NOT NULL,
    other_expenses TEXT NOT NULL,
    
    -- Affordability
    food_affordability VARCHAR(50) NOT NULL,
    supplies_affordability VARCHAR(50) NOT NULL,
    utilities_affordability VARCHAR(50) NOT NULL,
    rent_affordability VARCHAR(50) NOT NULL,
    
    -- Health General
    familiar_with_resources VARCHAR(50) NOT NULL,
    is_chp VARCHAR(50) NOT NULL,
    chp_visit_frequency VARCHAR(50) NOT NULL,
    know_chp_in_area VARCHAR(50) NOT NULL,
    
    -- Health Awareness
    awareness_level VARCHAR(50) NOT NULL,
    services_awareness TEXT NOT NULL,
    other_services TEXT NOT NULL,
    none_services TEXT NOT NULL,
    
    -- Health Access
    access_level VARCHAR(50) NOT NULL,
    health_services TEXT NOT NULL,
    other_health_services TEXT NOT NULL,
    accessed_services TEXT NOT NULL,
    
    -- Health History
    health_services_status VARCHAR(50) NOT NULL,
    vaccinations_status VARCHAR(50) NOT NULL,
    growth_monitoring_status VARCHAR(50) NOT NULL,
    deworming_status VARCHAR(50) NOT NULL,
    vas_status VARCHAR(50) NOT NULL,
    other_health_services_status VARCHAR(50) NOT NULL,

    -- Status
    status VARCHAR(20) DEFAULT 'pending'
);
