-- Drop the existing hbcc_providers table if it exists

-- Create hbcc_providers table with the same structure as providers
CREATE TABLE hbcc_providers AS 
SELECT * FROM providers 
WHERE 1=0;

-- Add necessary constraints and indexes
ALTER TABLE hbcc_providers 
    ADD CONSTRAINT hbcc_providers_pkey PRIMARY KEY (id);

-- Add the missing columns if they don't exist
DO $$ 
BEGIN
    -- Check and add has_assistant_caregivers
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='has_assistant_caregivers') THEN
        ALTER TABLE hbcc_providers ADD COLUMN has_assistant_caregivers VARCHAR(255);
    END IF;

    -- Add all other columns that might be missing
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='assistant_caregivers_timing') THEN
        ALTER TABLE hbcc_providers ADD COLUMN assistant_caregivers_timing VARCHAR(255);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='special_needs_children') THEN
        ALTER TABLE hbcc_providers ADD COLUMN special_needs_children VARCHAR(255);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='special_needs_children_count') THEN
        ALTER TABLE hbcc_providers ADD COLUMN special_needs_children_count INTEGER;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='special_needs_age_groups') THEN
        ALTER TABLE hbcc_providers ADD COLUMN special_needs_age_groups TEXT[];
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='special_needs_types') THEN
        ALTER TABLE hbcc_providers ADD COLUMN special_needs_types TEXT[];
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='screening_need') THEN
        ALTER TABLE hbcc_providers ADD COLUMN screening_need TEXT;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='fee_structure') THEN
        ALTER TABLE hbcc_providers ADD COLUMN fee_structure VARCHAR(255);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='fee_per_child') THEN
        ALTER TABLE hbcc_providers ADD COLUMN fee_per_child DECIMAL(10,2);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='payment_method') THEN
        ALTER TABLE hbcc_providers ADD COLUMN payment_method VARCHAR(255);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='estimated_daily_income') THEN
        ALTER TABLE hbcc_providers ADD COLUMN estimated_daily_income DECIMAL(10,2);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='in_kind_contribution') THEN
        ALTER TABLE hbcc_providers ADD COLUMN in_kind_contribution TEXT;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='daily_expense') THEN
        ALTER TABLE hbcc_providers ADD COLUMN daily_expense DECIMAL(10,2);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='food_expense') THEN
        ALTER TABLE hbcc_providers ADD COLUMN food_expense DECIMAL(10,2);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='supplies_expense') THEN
        ALTER TABLE hbcc_providers ADD COLUMN supplies_expense DECIMAL(10,2);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='utilities_expense') THEN
        ALTER TABLE hbcc_providers ADD COLUMN utilities_expense DECIMAL(10,2);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='rent_expense') THEN
        ALTER TABLE hbcc_providers ADD COLUMN rent_expense DECIMAL(10,2);
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='other_expenses') THEN
        ALTER TABLE hbcc_providers ADD COLUMN other_expenses TEXT;
    END IF;

    -- Add timestamps if they don't exist
    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='created_at') THEN
        ALTER TABLE hbcc_providers ADD COLUMN created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;
    END IF;

    IF NOT EXISTS (SELECT 1 FROM information_schema.columns 
                  WHERE table_name='hbcc_providers' AND column_name='updated_at') THEN
        ALTER TABLE hbcc_providers ADD COLUMN updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP;
    END IF;

END $$;
