-- Add enterprise fields to doctors table for comprehensive profile
ALTER TABLE doctors
ADD COLUMN IF NOT EXISTS street_address VARCHAR(255),
ADD COLUMN IF NOT EXISTS city VARCHAR(100),
ADD COLUMN IF NOT EXISTS state VARCHAR(100),
ADD COLUMN IF NOT EXISTS zip_code VARCHAR(20),
ADD COLUMN IF NOT EXISTS country VARCHAR(100),
ADD COLUMN IF NOT EXISTS education_degree VARCHAR(200),
ADD COLUMN IF NOT EXISTS education_university VARCHAR(200),
ADD COLUMN IF NOT EXISTS education_graduation_year INTEGER,
ADD COLUMN IF NOT EXISTS certifications TEXT[], -- Array of certification names
ADD COLUMN IF NOT EXISTS languages_spoken TEXT[], -- Array of languages
ADD COLUMN IF NOT EXISTS hospital_affiliations TEXT[], -- Array of hospital names
ADD COLUMN IF NOT EXISTS insurance_accepted TEXT[], -- Array of insurance provider names
ADD COLUMN IF NOT EXISTS professional_memberships TEXT[]; -- Array of membership organizations

-- Add enterprise fields to patients table for comprehensive profile
ALTER TABLE patients
ADD COLUMN IF NOT EXISTS date_of_birth DATE,
ADD COLUMN IF NOT EXISTS gender VARCHAR(20),
ADD COLUMN IF NOT EXISTS street_address VARCHAR(255),
ADD COLUMN IF NOT EXISTS city VARCHAR(100),
ADD COLUMN IF NOT EXISTS state VARCHAR(100),
ADD COLUMN IF NOT EXISTS zip_code VARCHAR(20),
ADD COLUMN IF NOT EXISTS country VARCHAR(100),
ADD COLUMN IF NOT EXISTS insurance_provider VARCHAR(200),
ADD COLUMN IF NOT EXISTS insurance_policy_number VARCHAR(100),
ADD COLUMN IF NOT EXISTS medical_history_summary TEXT,
ADD COLUMN IF NOT EXISTS current_medications TEXT[], -- Array of medication names
ADD COLUMN IF NOT EXISTS primary_care_physician_name VARCHAR(200),
ADD COLUMN IF NOT EXISTS primary_care_physician_phone VARCHAR(20),
ADD COLUMN IF NOT EXISTS preferred_language VARCHAR(50),
ADD COLUMN IF NOT EXISTS occupation VARCHAR(200),
ADD COLUMN IF NOT EXISTS marital_status VARCHAR(20);

-- Create indexes for better query performance
CREATE INDEX IF NOT EXISTS idx_doctors_city ON doctors(city);
CREATE INDEX IF NOT EXISTS idx_doctors_state ON doctors(state);
CREATE INDEX IF NOT EXISTS idx_doctors_specialization ON doctors(specialization);
CREATE INDEX IF NOT EXISTS idx_patients_city ON patients(city);
CREATE INDEX IF NOT EXISTS idx_patients_state ON patients(state);
CREATE INDEX IF NOT EXISTS idx_patients_date_of_birth ON patients(date_of_birth);

