-- Batch: Client/Patient forms migrated from larcareservices.com/portal (Zoho Forms)
-- into ABOS web forms. Idempotent (guarded by form_key/field_key existence), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/larcare_forms_seed_client_patient.sql

USE db_abos_v0.1;

SET @larcare_site_id = (SELECT id FROM dim_web_sites WHERE primary_domain = 'larcareservices.com' LIMIT 1);
SET @larcare_tenant_id = (SELECT tenant_id FROM dim_web_sites WHERE id = @larcare_site_id);
SET @larcare_business_id = (SELECT business_id FROM dim_web_sites WHERE id = @larcare_site_id);

-- ---- Background Check Result ----
INSERT INTO dim_web_forms (tenant_id, business_id, site_id, form_key, name, success_message, is_active)
SELECT @larcare_tenant_id, @larcare_business_id, @larcare_site_id, 'background-check-result',
       'Background Check Result', 'Thank you — this has been recorded.', 1
WHERE @larcare_site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'background-check-result');

SET @background_check_result_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'background-check-result');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_result_id AS a, 'noteHeading' AS b, 'Source note: the original Zoho form was disabled/inaccessible when this migration ran — these fields are a reasonable reconstruction from the form''s title and portal context, not an extraction of its real fields.' AS c, 'heading' AS d, 0 AS e, 1 AS f) AS t
WHERE @background_check_result_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_result_id AND field_key = 'noteHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @background_check_result_id AS a, 'location' AS b, 'Location' AS c, 'select' AS d, 1 AS e, 2 AS f, '{"options": ["Chester, South Carolina - 178 Columbia St.", "Hopewell, Virginia - 2304 Freeman St.", "SamariCare/Williamsburg - 710 Queensbury Lane", "St. Thomas, Virgin Islands"]}' AS g) AS t
WHERE @background_check_result_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_result_id AND field_key = 'location');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_result_id AS a, 'employeeFirstName' AS b, 'Employee First Name' AS c, 'text' AS d, 1 AS e, 3 AS f) AS t
WHERE @background_check_result_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_result_id AND field_key = 'employeeFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_result_id AS a, 'employeeLastName' AS b, 'Employee Last Name' AS c, 'text' AS d, 1 AS e, 4 AS f) AS t
WHERE @background_check_result_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_result_id AND field_key = 'employeeLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_result_id AS a, 'checkCompletedDate' AS b, 'Background Check Completed Date' AS c, 'date' AS d, 1 AS e, 5 AS f) AS t
WHERE @background_check_result_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_result_id AND field_key = 'checkCompletedDate');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @background_check_result_id AS a, 'result' AS b, 'Result' AS c, 'select' AS d, 1 AS e, 6 AS f, '{"options": ["Cleared", "Not Cleared", "Pending"]}' AS g) AS t
WHERE @background_check_result_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_result_id AND field_key = 'result');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_result_id AS a, 'notes' AS b, 'Notes' AS c, 'textarea' AS d, 0 AS e, 7 AS f) AS t
WHERE @background_check_result_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_result_id AND field_key = 'notes');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_result_id AS a, 'reviewerFirstName' AS b, 'Reviewer First Name' AS c, 'text' AS d, 0 AS e, 8 AS f) AS t
WHERE @background_check_result_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_result_id AND field_key = 'reviewerFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_result_id AS a, 'reviewerLastName' AS b, 'Reviewer Last Name' AS c, 'text' AS d, 0 AS e, 9 AS f) AS t
WHERE @background_check_result_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_result_id AND field_key = 'reviewerLastName');

-- ---- Orientation Training Tracking Form ----
INSERT INTO dim_web_forms (tenant_id, business_id, site_id, form_key, name, success_message, is_active)
SELECT @larcare_tenant_id, @larcare_business_id, @larcare_site_id, 'orientation-training-tracking',
       'Orientation Training Tracking Form', 'Thank you — the training record has been submitted.', 1
WHERE @larcare_site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'orientation-training-tracking');

SET @orientation_training_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'orientation-training-tracking');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @orientation_training_id AS a, 'caregiverFirstName' AS b, 'Caregiver First Name' AS c, 'text' AS d, 0 AS e, 1 AS f) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'caregiverFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @orientation_training_id AS a, 'caregiverLastName' AS b, 'Caregiver Last Name' AS c, 'text' AS d, 0 AS e, 2 AS f) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'caregiverLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @orientation_training_id AS a, 'trainingDate' AS b, 'Date of Orientation/Training' AS c, 'date' AS d, 1 AS e, 3 AS f) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'trainingDate');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @orientation_training_id AS a, 'trainerFirstName' AS b, 'Trainer First Name' AS c, 'text' AS d, 0 AS e, 4 AS f) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'trainerFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @orientation_training_id AS a, 'trainerLastName' AS b, 'Trainer Last Name' AS c, 'text' AS d, 0 AS e, 5 AS f) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'trainerLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @orientation_training_id AS a, 'location' AS b, 'Location' AS c, 'select' AS d, 1 AS e, 6 AS f, '{"options": ["Chester, South Carolina - 178 Columbia St.", "Hopewell, Virginia - 2304 Freeman St.", "SamariCare/Williamsburg - 710 Queensbury Lane", "St. Thomas, Virgin Islands", "Other"]}' AS g) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'location');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @orientation_training_id AS a, 'trainingSectionsCompleted' AS b, 'Training Sections Completed' AS c, 'select' AS d, 1 AS e, 7 AS f, '{"options": ["Agency Overview & Policies", "HIPAA & Client Confidentiality", "Infection Control & Universal Precautions", "Proper Body Mechanics & Transfers", "Personal Care Skills (bathing, dressing, toileting)", "Scope of Services", "Emergency Preparedness Plan", "Medication Reminders", "Use of CareTime App", "Daily Task Sheet Completion", "Client Rights & Responsibilities", "Professional Conduct & Cultural Sensitivity"]}' AS g) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'trainingSectionsCompleted');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @orientation_training_id AS a, 'startTime' AS b, 'Start Time' AS c, 'text' AS d, 1 AS e, 8 AS f) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'startTime');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @orientation_training_id AS a, 'endTime' AS b, 'End Time' AS c, 'text' AS d, 1 AS e, 9 AS f) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'endTime');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @orientation_training_id AS a, 'totalHours' AS b, 'Total Hours' AS c, 'number' AS d, 0 AS e, 10 AS f) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'totalHours');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @orientation_training_id AS a, 'trainerNotes' AS b, 'Trainer Notes / Additional Comments' AS c, 'textarea' AS d, 1 AS e, 11 AS f) AS t
WHERE @orientation_training_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @orientation_training_id AND field_key = 'trainerNotes');

-- ---- Skills Training Check-Off List ----
INSERT INTO dim_web_forms (tenant_id, business_id, site_id, form_key, name, success_message, is_active)
SELECT @larcare_tenant_id, @larcare_business_id, @larcare_site_id, 'skills-training-checkoff',
       'Skills Training Check-Off List', 'Thank you — this has been recorded.', 1
WHERE @larcare_site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'skills-training-checkoff');

SET @skills_training_checkoff_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'skills-training-checkoff');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @skills_training_checkoff_id AS a, 'location' AS b, 'Location' AS c, 'select' AS d, 1 AS e, 1 AS f, '{"options": ["Chester, South Carolina - 178 Columbia St.", "Hopewell, Virginia - 2304 Freeman St.", "SamariCare/Williamsburg - 710 Queensbury Lane", "St. Thomas, Virgin Islands"]}' AS g) AS t
WHERE @skills_training_checkoff_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @skills_training_checkoff_id AND field_key = 'location');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @skills_training_checkoff_id AS a, 'agencyName' AS b, 'Agency Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @skills_training_checkoff_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @skills_training_checkoff_id AND field_key = 'agencyName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @skills_training_checkoff_id AS a, 'caregiverFirstName' AS b, 'Caregiver First Name' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @skills_training_checkoff_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @skills_training_checkoff_id AND field_key = 'caregiverFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @skills_training_checkoff_id AS a, 'caregiverLastName' AS b, 'Caregiver Last Name' AS c, 'text' AS d, 0 AS e, 4 AS f) AS t
WHERE @skills_training_checkoff_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @skills_training_checkoff_id AND field_key = 'caregiverLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @skills_training_checkoff_id AS a, 'date' AS b, 'Date' AS c, 'date' AS d, 1 AS e, 5 AS f) AS t
WHERE @skills_training_checkoff_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @skills_training_checkoff_id AND field_key = 'date');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @skills_training_checkoff_id AS a, 'trainerSupervisor' AS b, 'Trainer/Clinical Supervisor' AS c, 'text' AS d, 1 AS e, 6 AS f) AS t
WHERE @skills_training_checkoff_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @skills_training_checkoff_id AND field_key = 'trainerSupervisor');

-- ---- Client Emergency Preparedness Document ----
INSERT INTO dim_web_forms (tenant_id, business_id, site_id, form_key, name, success_message, is_active)
SELECT @larcare_tenant_id, @larcare_business_id, @larcare_site_id, 'client-emergency-preparedness-document',
       'Client Emergency Preparedness Document', 'Thank you — the emergency preparedness document has been submitted.', 1
WHERE @larcare_site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'client-emergency-preparedness-document');

SET @client_emergency_prep_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'client-emergency-preparedness-document');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'location' AS b, 'Location' AS c, 'select' AS d, 1 AS e, 1 AS f, '{"options": ["Chester, South Carolina - 178 Columbia St.", "Hopewell, Virginia - 2304 Freeman St.", "SamariCare/Williamsburg - 710 Queensbury Lane", "St. Thomas, Virgin Islands"]}' AS g) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'location');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'clientFirstName' AS b, 'Client First Name' AS c, 'text' AS d, 0 AS e, 2 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'clientFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'clientLastName' AS b, 'Client Last Name' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'clientLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'dateOfBirth' AS b, 'Date of Birth' AS c, 'date' AS d, 1 AS e, 4 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'dateOfBirth');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'primaryCaregiverFirstName' AS b, 'Primary Caregiver First Name' AS c, 'text' AS d, 0 AS e, 5 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'primaryCaregiverFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'primaryCaregiverLastName' AS b, 'Primary Caregiver Last Name' AS c, 'text' AS d, 0 AS e, 6 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'primaryCaregiverLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'primaryCaregiverPhone' AS b, 'Primary Caregiver Phone' AS c, 'tel' AS d, 0 AS e, 7 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'primaryCaregiverPhone');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'primaryCaregiverRelationship' AS b, 'Relationship to Client (Primary Caregiver)' AS c, 'text' AS d, 1 AS e, 8 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'primaryCaregiverRelationship');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'emergencyContactFirstName' AS b, 'Emergency Contact First Name' AS c, 'text' AS d, 0 AS e, 9 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'emergencyContactFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'emergencyContactLastName' AS b, 'Emergency Contact Last Name' AS c, 'text' AS d, 0 AS e, 10 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'emergencyContactLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'emergencyContactRelationship' AS b, 'Relationship to Client (Emergency Contact)' AS c, 'text' AS d, 1 AS e, 11 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'emergencyContactRelationship');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'emergencyContactPhone' AS b, 'Emergency Contact Phone Number' AS c, 'tel' AS d, 1 AS e, 12 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'emergencyContactPhone');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'evacuationPlan' AS b, 'Evacuation & Shelter Plan' AS c, 'select' AS d, 1 AS e, 13 AS f, '{"options": ["Client will shelter in place", "Client will be transported to a safe location", "Other"]}' AS g) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'evacuationPlan');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'evacuationAddressStreet' AS b, 'Evacuation Address — Street (if transported)' AS c, 'text' AS d, 0 AS e, 14 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'evacuationAddressStreet');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'evacuationAddressCity' AS b, 'Evacuation Address — City' AS c, 'text' AS d, 0 AS e, 15 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'evacuationAddressCity');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'evacuationAddressState' AS b, 'Evacuation Address — State/Region/Province' AS c, 'text' AS d, 0 AS e, 16 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'evacuationAddressState');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'evacuationAddressZip' AS b, 'Evacuation Address — Postal / Zip Code' AS c, 'text' AS d, 0 AS e, 17 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'evacuationAddressZip');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'emergencySupplies' AS b, 'Emergency Supplies (comma-separated: Medications, Medical equipment, Identification, Emergency contacts list, Mobility Device, Personal hygiene items, Other)' AS c, 'text' AS d, 1 AS e, 18 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'emergencySupplies');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'altCareFirstName' AS b, 'Alternate Care Arrangement — First Name' AS c, 'text' AS d, 0 AS e, 19 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'altCareFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'altCareLastName' AS b, 'Alternate Care Arrangement — Last Name' AS c, 'text' AS d, 0 AS e, 20 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'altCareLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_emergency_prep_id AS a, 'altCareRelationship' AS b, 'Alternate Care — Relationship to Client' AS c, 'text' AS d, 0 AS e, 21 AS f) AS t
WHERE @client_emergency_prep_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_emergency_prep_id AND field_key = 'altCareRelationship');

-- ---- Apprenticeship Request (Step 1) ----
INSERT INTO dim_web_forms (tenant_id, business_id, site_id, form_key, name, success_message, is_active)
SELECT @larcare_tenant_id, @larcare_business_id, @larcare_site_id, 'apprenticeship-request',
       'Apprenticeship Request (Step 1)', 'Thank you — your apprenticeship request has been submitted.', 1
WHERE @larcare_site_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'apprenticeship-request');

SET @apprenticeship_request_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'apprenticeship-request');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @apprenticeship_request_id AS a, 'location' AS b, 'Location' AS c, 'select' AS d, 1 AS e, 1 AS f, '{"options": ["Chester, South Carolina - 178 Columbia St.", "Hopewell, Virginia - 2304 Freeman St.", "SamariCare/Williamsburg - 710 Queensbury Lane", "St. Thomas, Virgin Islands"]}' AS g) AS t
WHERE @apprenticeship_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @apprenticeship_request_id AND field_key = 'location');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @apprenticeship_request_id AS a, 'apprenticeFirstName' AS b, 'Apprentice First Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @apprenticeship_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @apprenticeship_request_id AND field_key = 'apprenticeFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @apprenticeship_request_id AS a, 'apprenticeLastName' AS b, 'Apprentice Last Name' AS c, 'text' AS d, 1 AS e, 3 AS f) AS t
WHERE @apprenticeship_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @apprenticeship_request_id AND field_key = 'apprenticeLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @apprenticeship_request_id AS a, 'programOfInterest' AS b, 'Program of Interest' AS c, 'select' AS d, 1 AS e, 4 AS f, '{"options": ["Certified Nursing Assistant", "Patient Care Technician", "Registered Nurse", "Business Administrator", "Website Developer", "Youth Certified Nursing Assistant"]}' AS g) AS t
WHERE @apprenticeship_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @apprenticeship_request_id AND field_key = 'programOfInterest');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @apprenticeship_request_id AS a, 'startDate' AS b, 'When Are You Interested in Starting?' AS c, 'date' AS d, 1 AS e, 5 AS f) AS t
WHERE @apprenticeship_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @apprenticeship_request_id AND field_key = 'startDate');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @apprenticeship_request_id AS a, 'goal' AS b, 'Why Do You Want to Enroll in an Apprenticeship Program?' AS c, 'textarea' AS d, 1 AS e, 6 AS f) AS t
WHERE @apprenticeship_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @apprenticeship_request_id AND field_key = 'goal');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @apprenticeship_request_id AS a, 'canTakeInPersonClasses' AS b, 'Are You Able to Dedicate Time to Take In-Person Classes If Needed?' AS c, 'select' AS d, 1 AS e, 7 AS f, '{"options": ["Yes", "No", "Unsure", "Other"]}' AS g) AS t
WHERE @apprenticeship_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @apprenticeship_request_id AND field_key = 'canTakeInPersonClasses');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @apprenticeship_request_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 0 AS e, 8 AS f) AS t
WHERE @apprenticeship_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @apprenticeship_request_id AND field_key = 'dateTime');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @apprenticeship_request_id AS a, 'signature' AS b, 'Employee Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 9 AS f) AS t
WHERE @apprenticeship_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @apprenticeship_request_id AND field_key = 'signature');