-- Create providers table
CREATE TABLE IF NOT EXISTS hbcc_providers (
    id SERIAL PRIMARY KEY,
    provider_code VARCHAR(50) NOT NULL UNIQUE,
    chp_name VARCHAR(100),
    county VARCHAR(100),
    number_of_children INTEGER,
    marital_status VARCHAR(100),
    education_level VARCHAR(100),
    interviewee_name VARCHAR(100),
    interviewee_contact VARCHAR(100),
    interviewee_gender VARCHAR(100),
    interviewee_age INTEGER,
    religion VARCHAR(100),
    owner_status VARCHAR(100),
    owner_type VARCHAR(100),
    hours_per_day INTEGER,
    days_per_week INTEGER,
    years_operating INTEGER,
    overnight_care VARCHAR(100),
    hbcc_story TEXT,
    sub_county VARCHAR(100),
    ward VARCHAR(100),
    village VARCHAR(100),
    landmark TEXT,
    age_group_children_count INTEGER,
    age_groups TEXT,
    youngest_child_story TEXT,
    has_assistant_caregivers VARCHAR(100),
    assistant_caregivers_timing TEXT,
    special_needs_children VARCHAR(100),
    special_needs_children_count INTEGER,
    special_needs_age_groups TEXT,
    special_needs_types TEXT,
    screening_need VARCHAR(100),
    fee_structure VARCHAR(100),
    fee_per_child DECIMAL(10,2),
    payment_method VARCHAR(100),
    estimated_daily_income DECIMAL(10,2),
    in_kind_contribution TEXT,
    daily_expense DECIMAL(10,2),
    main_expenses TEXT,
    food_expense DECIMAL(10,2),
    supplies_expense DECIMAL(10,2),
    utilities_expense DECIMAL(10,2),
    rent_expense DECIMAL(10,2),
    other_expenses TEXT,
    food_affordability VARCHAR(100),
    supplies_affordability VARCHAR(100),
    utilities_affordability VARCHAR(100),
    rent_affordability VARCHAR(100),
    familiar_with_resources VARCHAR(100),
    is_chp VARCHAR(100),
    chp_visit_frequency VARCHAR(100),
    know_chp_in_area VARCHAR(100),
    awareness_level VARCHAR(100),
    services_awareness TEXT,
    other_services TEXT,
    none_services TEXT,
    access_level VARCHAR(100),
    health_services TEXT,
    other_health_services TEXT,
    accessed_services TEXT,
    health_services_status VARCHAR(500),
    vaccinations_status VARCHAR(500),
    growth_monitoring_status VARCHAR(500),
    deworming_status VARCHAR(500),
    vas_status VARCHAR(500),
    other_health_services_status VARCHAR(500),
    status VARCHAR(20) DEFAULT 'approved',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
