CREATE DATABASE IF NOT EXISTS gentle_companions CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE gentle_companions;
CREATE TABLE IF NOT EXISTS client_intake_submissions (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
dob DATE DEFAULT NULL,
phone VARCHAR(50) NOT NULL,
email VARCHAR(190) DEFAULT NULL,
address VARCHAR(255) NOT NULL,
city VARCHAR(100) NOT NULL,
county VARCHAR(100) DEFAULT NULL,
other_county VARCHAR(100) DEFAULT NULL,
marital_status VARCHAR(50) DEFAULT NULL,
preferred_contact VARCHAR(50) DEFAULT NULL,
referral_sources TEXT DEFAULT NULL,
other_referral_source VARCHAR(255) DEFAULT NULL,
payer_types TEXT DEFAULT NULL,
other_payer VARCHAR(255) DEFAULT NULL,
care_tasks TEXT DEFAULT NULL,
assistance_level VARCHAR(100) DEFAULT NULL,
mobility_level VARCHAR(100) DEFAULT NULL,
lives_alone VARCHAR(20) DEFAULT NULL,
lives_with VARCHAR(255) DEFAULT NULL,
physical_conditions TEXT DEFAULT NULL,
neuro_conditions TEXT DEFAULT NULL,
medical_conditions TEXT DEFAULT NULL,
behavioral_conditions TEXT DEFAULT NULL,
sensory_conditions TEXT DEFAULT NULL,
other_conditions TEXT DEFAULT NULL,
other_condition_notes TEXT DEFAULT NULL,
care_schedule VARCHAR(100) DEFAULT NULL,
caregiver_gender VARCHAR(50) DEFAULT NULL,
preferred_start_date DATE DEFAULT NULL,
emergency_relationship VARCHAR(100) DEFAULT NULL,
emergency_first_name VARCHAR(100) DEFAULT NULL,
emergency_last_name VARCHAR(100) DEFAULT NULL,
emergency_phone VARCHAR(50) DEFAULT NULL,
emergency_email VARCHAR(190) DEFAULT NULL,
authorizations TEXT DEFAULT NULL,
confirm_accuracy TINYINT(1) NOT NULL DEFAULT 0,
authorize_use TINYINT(1) NOT NULL DEFAULT 0,
signature_name VARCHAR(190) DEFAULT NULL,
signature_date DATE DEFAULT NULL,
upload_summary TEXT DEFAULT NULL,
ip_address VARCHAR(45) DEFAULT NULL,
user_agent TEXT DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE IF NOT EXISTS client_intake_uploads (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
submission_id INT UNSIGNED NOT NULL,
original_name VARCHAR(255) NOT NULL,
stored_name VARCHAR(255) NOT NULL,
mime_type VARCHAR(100) DEFAULT NULL,
file_size INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_upload_submission
FOREIGN KEY (submission_id) REFERENCES client_intake_submissions(id)
ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
