-- 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);

-- ---- Daily Tasksheet Report ----
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, 'daily-tasksheet-report',
       'Daily Tasksheet Report', 'Thank you — the tasksheet 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 = 'daily-tasksheet-report');

SET @daily_tasksheet_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'daily-tasksheet-report');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @daily_tasksheet_id AS a, 'location' AS b, 'Choose Reporting State' 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 @daily_tasksheet_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @daily_tasksheet_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 @daily_tasksheet_id AS a, 'aideFirstName' AS b, 'Aide First Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @daily_tasksheet_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @daily_tasksheet_id AND field_key = 'aideFirstName');

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @daily_tasksheet_id AS a, 'workWeek' AS b, 'Work Week (e.g. Work week 1,2,3,4)' AS c, 'text' AS d, 1 AS e, 6 AS f) AS t
WHERE @daily_tasksheet_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @daily_tasksheet_id AND field_key = 'workWeek');

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @daily_tasksheet_id AS a, 'activityCodes' AS b, 'Activity Codes Performed (comma-separated, e.g. 20, 21, 27 — see LarCare''s Activity Code reference sheet for the full list)' AS c, 'text' AS d, 0 AS e, 10 AS f) AS t
WHERE @daily_tasksheet_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @daily_tasksheet_id AND field_key = 'activityCodes');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @daily_tasksheet_id AS a, 'patientClassification' AS b, 'Patient Classification' AS c, 'select' AS d, 0 AS e, 12 AS f, '{"options": ["Veteran", "Medicaid", "DDSN Medicaid", "Private Care", "Private Insurance", "Grant", "Other Federal Type or State Type", "Other"]}' AS g) AS t
WHERE @daily_tasksheet_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @daily_tasksheet_id AND field_key = 'patientClassification');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @daily_tasksheet_id AS a, 'patientAddress' AS b, 'Patient Address or Service Area' AS c, 'text' AS d, 1 AS e, 13 AS f) AS t
WHERE @daily_tasksheet_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @daily_tasksheet_id AND field_key = 'patientAddress');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @daily_tasksheet_id AS a, 'servicesPerPlan' AS b, 'Services Provided According to Service Plan' AS c, 'textarea' AS d, 1 AS e, 15 AS f) AS t
WHERE @daily_tasksheet_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @daily_tasksheet_id AND field_key = 'servicesPerPlan');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @daily_tasksheet_id AS a, 'patientConcerns' AS b, 'Any Patient Concerns or Special Requests' AS c, 'textarea' AS d, 0 AS e, 16 AS f) AS t
WHERE @daily_tasksheet_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @daily_tasksheet_id AND field_key = 'patientConcerns');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @daily_tasksheet_id AS a, 'provider' AS b, 'Provider (e.g. LarCare LLC)' AS c, 'text' AS d, 1 AS e, 17 AS f) AS t
WHERE @daily_tasksheet_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @daily_tasksheet_id AND field_key = 'provider');

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

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

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

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

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

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @daily_tasksheet_id AS a, 'agreement' AS b, 'I confirm the above signature is authentic and electronically executed' AS c, 'checkbox' AS d, 1 AS e, 26 AS f) AS t
WHERE @daily_tasksheet_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @daily_tasksheet_id AND field_key = 'agreement');

-- ---- Contractor Application ----
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, 'contractor-application',
       'Contractor Application', 'Thank you for applying — our hiring team will review your application and be in touch.', 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 = 'contractor-application');

SET @contractor_application_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'contractor-application');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_application_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 @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_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 @contractor_application_id AS a, 'languages' AS b, 'Languages Spoken/Written/Understood (comma-separated: English, Spanish, Mandarin, Cantonese, others)' AS c, 'text' AS d, 0 AS e, 2 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'languages');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'assignmentAreas' AS b, 'Where Do You Want to Be Assigned (comma-separated cities/areas)' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'assignmentAreas');

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_application_id AS a, 'contractorType' AS b, 'Contractor Aide/Nurse Type' AS c, 'select' AS d, 1 AS e, 6 AS f, '{"options": ["Caregiver / Aide", "Nurse", "Admin or Operational"]}' AS g) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'contractorType');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ssn' AS b, 'Social Security Number (123-45-6789)' AS c, 'text' AS d, 1 AS e, 7 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ssn');

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'addressStreet' AS b, 'Street Address' AS c, 'text' AS d, 1 AS e, 11 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'addressStreet');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'addressCity' AS b, 'City' AS c, 'text' AS d, 1 AS e, 12 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'addressCity');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'addressState' AS b, 'State/Region/Province' AS c, 'text' AS d, 1 AS e, 13 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'addressState');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'addressZip' AS b, 'Postal / Zip Code' AS c, 'text' AS d, 1 AS e, 14 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'addressZip');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'areasCovered' AS b, 'Areas Covered' AS c, 'text' AS d, 1 AS e, 15 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'areasCovered');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_application_id AS a, 'hasCar' AS b, 'Do You Have a Working Car/Automobile to Travel to Patient Homes?' AS c, 'select' AS d, 1 AS e, 16 AS f, '{"options": ["Yes", "No"]}' AS g) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'hasCar');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'workHeading' AS b, 'Work Desired' AS c, 'heading' AS d, 0 AS e, 17 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'workHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'daysAvailable' AS b, 'Days Available to Work Each Week (comma-separated)' AS c, 'text' AS d, 1 AS e, 18 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'daysAvailable');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'educationHeading' AS b, 'Education' AS c, 'heading' AS d, 0 AS e, 19 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'educationHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_application_id AS a, 'graduatedHsOrGed' AS b, 'Graduated from High School or GED Equivalent?' AS c, 'select' AS d, 1 AS e, 20 AS f, '{"options": ["Yes", "No", "GED", "Other"]}' AS g) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'graduatedHsOrGed');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'gedCompletionDate' AS b, 'GED/High School Diploma Completion Date' AS c, 'date' AS d, 0 AS e, 21 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'gedCompletionDate');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'highSchool' AS b, 'High School' AS c, 'text' AS d, 1 AS e, 22 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'highSchool');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'highSchoolLocation' AS b, 'High School Address or City/State' AS c, 'text' AS d, 1 AS e, 23 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'highSchoolLocation');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_application_id AS a, 'graduatedCollege' AS b, 'Graduated from College?' AS c, 'select' AS d, 1 AS e, 24 AS f, '{"options": ["Yes", "No"]}' AS g) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'graduatedCollege');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'collegeName' AS b, 'College or University Name' AS c, 'text' AS d, 0 AS e, 26 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'collegeName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'collegeLocation' AS b, 'College or University Address or City/State' AS c, 'text' AS d, 0 AS e, 27 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'collegeLocation');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'employmentHeading' AS b, 'Employment History' AS c, 'heading' AS d, 0 AS e, 28 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'employmentHeading');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'employerLocation' AS b, 'Employer Address or City and State' AS c, 'text' AS d, 1 AS e, 30 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'employerLocation');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'supervisorNumber' AS b, 'Supervisor Number' AS c, 'tel' AS d, 0 AS e, 32 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'supervisorNumber');

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'companyPosition' AS b, 'Company Position' AS c, 'text' AS d, 1 AS e, 35 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'companyPosition');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'salaryAmount' AS b, 'Salary Amount or Hourly Rate (USD)' AS c, 'number' AS d, 1 AS e, 36 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'salaryAmount');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'reasonForLeaving' AS b, 'Reason for Leaving' AS c, 'textarea' AS d, 1 AS e, 37 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'reasonForLeaving');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'referencesHeading' AS b, 'References — List Two (2) Professional References' AS c, 'heading' AS d, 0 AS e, 38 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'referencesHeading');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ref1Address' AS b, 'Reference 1 — Address' AS c, 'text' AS d, 1 AS e, 40 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ref1Address');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ref1Relationship' AS b, 'Reference 1 — Relationship' AS c, 'text' AS d, 1 AS e, 41 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ref1Relationship');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ref1YearsKnown' AS b, 'Reference 1 — Years Known' AS c, 'text' AS d, 1 AS e, 42 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ref1YearsKnown');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ref1Phone' AS b, 'Reference 1 — Phone Number' AS c, 'tel' AS d, 1 AS e, 43 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ref1Phone');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ref1BestTime' AS b, 'Reference 1 — Best Time to Call' AS c, 'text' AS d, 1 AS e, 44 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ref1BestTime');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ref2Address' AS b, 'Reference 2 — Address' AS c, 'text' AS d, 1 AS e, 46 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ref2Address');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ref2Relationship' AS b, 'Reference 2 — Relationship' AS c, 'text' AS d, 1 AS e, 47 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ref2Relationship');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ref2YearsKnown' AS b, 'Reference 2 — Years Known' AS c, 'text' AS d, 1 AS e, 48 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ref2YearsKnown');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ref2Phone' AS b, 'Reference 2 — Phone Number' AS c, 'tel' AS d, 1 AS e, 49 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ref2Phone');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'ref2BestTime' AS b, 'Reference 2 — Best Time to Call' AS c, 'text' AS d, 1 AS e, 50 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'ref2BestTime');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'emergencyHeading' AS b, 'Emergency Contact' AS c, 'heading' AS d, 0 AS e, 51 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'emergencyHeading');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'emergencyContactAddress' AS b, 'Emergency Contact Address' AS c, 'text' AS d, 1 AS e, 53 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'emergencyContactAddress');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'emergencyContactRelationship' AS b, 'Emergency Contact Relationship' AS c, 'text' AS d, 1 AS e, 54 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_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 @contractor_application_id AS a, 'emergencyContactPhone' AS b, 'Emergency Contact Phone Number' AS c, 'tel' AS d, 1 AS e, 55 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_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 @contractor_application_id AS a, 'physicallyCapable' AS b, 'Are You Physically Capable of Performing the Duties of This Job Successfully?' AS c, 'select' AS d, 1 AS e, 56 AS f, '{"options": ["Yes", "No"]}' AS g) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'physicallyCapable');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_application_id AS a, 'everArrestedConvicted' AS b, 'Have You Ever Been Arrested/Convicted for Any Crime Involving Violence, Carelessness, or Integrity?' AS c, 'select' AS d, 1 AS e, 57 AS f, '{"options": ["Yes", "No"]}' AS g) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'everArrestedConvicted');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'arrestDetails' AS b, 'If Yes, Provide Details' AS c, 'textarea' AS d, 0 AS e, 58 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'arrestDetails');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'backgroundCheckAuthHeading' AS b, 'Background Check Authorization — I authorize LarCare Inc. & DBA SamariCare Services to conduct a criminal records check, drug testing, TB testing, and any other background checks deemed relevant, per the terms above.' AS c, 'heading' AS d, 0 AS e, 60 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'backgroundCheckAuthHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'backgroundCheckAuthorized' AS b, 'I Authorize the Background Check Described Above' AS c, 'checkbox' AS d, 1 AS e, 61 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'backgroundCheckAuthorized');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_application_id AS a, 'authorizedFullName' AS b, 'Authorized Signature — Full Name' AS c, 'text' AS d, 1 AS e, 62 AS f) AS t
WHERE @contractor_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_application_id AND field_key = 'authorizedFullName');

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

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

-- ---- Contractor Direct Deposit ----
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, 'contractor-direct-deposit',
       'Contractor Direct Deposit', 'Thank you — your direct deposit details have been submitted to payroll.', 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 = 'contractor-direct-deposit');

SET @contractor_direct_deposit_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'contractor-direct-deposit');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'location' AS b, 'Choose Reporting State' 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 @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_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 @contractor_direct_deposit_id AS a, 'status' AS b, 'Status' AS c, 'select' AS d, 1 AS e, 2 AS f, '{"options": ["New Direct Deposit", "Update Direct Deposit", "Other"]}' AS g) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'status');

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'contractorType' AS b, 'Contractor Aide/Nurse Type' AS c, 'select' AS d, 1 AS e, 5 AS f, '{"options": ["Aide", "Nurse", "Admin or Operational"]}' AS g) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'contractorType');

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'completedChecklist' AS b, 'Please Verify You Have Completed the Following (comma-separated: Drug test, TB Test, Background Check, Training)' AS c, 'text' AS d, 1 AS e, 8 AS f) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'completedChecklist');

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

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'accountHeading' AS b, 'Account Details' AS c, 'heading' AS d, 0 AS e, 13 AS f) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'accountHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'accountType' AS b, 'Account Type' AS c, 'select' AS d, 1 AS e, 14 AS f, '{"options": ["Checking", "Saving"]}' AS g) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'accountType');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'depositChangeType' AS b, 'Is This Your Initial Direct Deposit or a Change?' AS c, 'select' AS d, 1 AS e, 16 AS f, '{"options": ["Initial Direct Deposit (New Applicant)", "Change in Direct Deposit"]}' AS g) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'depositChangeType');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'oldAccountNumber' AS b, 'Confirm Old Account Number (if change in direct deposit)' AS c, 'text' AS d, 0 AS e, 17 AS f) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'oldAccountNumber');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'newAccountNumber' AS b, 'New Account Number' AS c, 'text' AS d, 1 AS e, 18 AS f) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'newAccountNumber');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'confirmNewAccountNumber' AS b, 'Confirm New Account Number' AS c, 'text' AS d, 1 AS e, 19 AS f) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'confirmNewAccountNumber');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'routingNumber' AS b, 'New Bank Routing Number (ABA Number)' AS c, 'text' AS d, 1 AS e, 20 AS f) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'routingNumber');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'ssn' AS b, 'SSN' AS c, 'text' AS d, 1 AS e, 21 AS f) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'ssn');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'authorizationHeading' AS b, 'Authorization — I authorize LarCare Inc. & DBA SamariCare Services to send credit/debit/adjustment entries to my account(s), per the ACH terms above, until written termination notice is received.' AS c, 'heading' AS d, 0 AS e, 22 AS f) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'authorizationHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_direct_deposit_id AS a, 'authorized' AS b, 'I Authorize the ACH Terms Described Above' AS c, 'checkbox' AS d, 1 AS e, 23 AS f) AS t
WHERE @contractor_direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_direct_deposit_id AND field_key = 'authorized');

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

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

-- ---- Contractor Health Assessment ----
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, 'contractor-health-assessment',
       'Contractor Health Assessment', 'Thank you — your health assessment 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 = 'contractor-health-assessment');

SET @contractor_health_assessment_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'contractor-health-assessment');

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_health_assessment_id AS a, 'ssn' AS b, 'SSN (123-45-6789)' AS c, 'text' AS d, 1 AS e, 4 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_id AND field_key = 'ssn');

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_health_assessment_id AS a, 'addressStreet' AS b, 'Street Address' AS c, 'text' AS d, 1 AS e, 7 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_id AND field_key = 'addressStreet');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_health_assessment_id AS a, 'addressState' AS b, 'State/Region/Province' AS c, 'text' AS d, 1 AS e, 9 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_id AND field_key = 'addressState');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_health_assessment_id AS a, 'addressZip' AS b, 'Postal / Zip Code' AS c, 'text' AS d, 1 AS e, 10 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_id AND field_key = 'addressZip');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_health_assessment_id AS a, 'emergencyHeading' AS b, 'Emergency Contact Details' AS c, 'heading' AS d, 0 AS e, 11 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_id AND field_key = 'emergencyHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_health_assessment_id AS a, 'emergencyContactFirstName' AS b, 'Emergency Contact First Name' AS c, 'text' AS d, 1 AS e, 12 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_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 @contractor_health_assessment_id AS a, 'emergencyContactLastName' AS b, 'Emergency Contact Last Name' AS c, 'text' AS d, 1 AS e, 13 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_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 @contractor_health_assessment_id AS a, 'emergencyContactNumber' AS b, 'Emergency Contact Number' AS c, 'tel' AS d, 1 AS e, 14 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_id AND field_key = 'emergencyContactNumber');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_health_assessment_id AS a, 'emergencyContactRelationship' AS b, 'Relationship' AS c, 'text' AS d, 1 AS e, 15 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_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 @contractor_health_assessment_id AS a, 'department' AS b, 'Department' AS c, 'text' AS d, 1 AS e, 16 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_id AND field_key = 'department');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_health_assessment_id AS a, 'position' AS b, 'Position' AS c, 'text' AS d, 1 AS e, 17 AS f) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_id AND field_key = 'position');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_health_assessment_id AS a, 'sex' AS b, 'Sex' AS c, 'select' AS d, 1 AS e, 18 AS f, '{"options": ["Male", "Female"]}' AS g) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_id AND field_key = 'sex');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_health_assessment_id AS a, 'maritalStatus' AS b, 'Marital Status (S, M, W, D)' AS c, 'select' AS d, 1 AS e, 19 AS f, '{"options": ["S", "M", "W", "D"]}' AS g) AS t
WHERE @contractor_health_assessment_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_health_assessment_id AND field_key = 'maritalStatus');

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

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

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

-- ---- Criminal Background Check and Sworn Statement of Affirmation ----
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, 'criminal-background-check-affirmation',
       'Criminal Background Check and Sworn Statement of Affirmation', '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 = 'criminal-background-check-affirmation');

SET @background_check_affirmation_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'criminal-background-check-affirmation');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'employeeFirstName' AS b, 'Employee First Name' AS c, 'text' AS d, 1 AS e, 1 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_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_affirmation_id AS a, 'employeeMiddleName' AS b, 'Employee Middle Name' AS c, 'text' AS d, 0 AS e, 2 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'employeeMiddleName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'employeeLastName' AS b, 'Employee Last Name' AS c, 'text' AS d, 1 AS e, 3 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_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_affirmation_id AS a, 'maidenFirstName' AS b, 'Maiden Name — First' AS c, 'text' AS d, 0 AS e, 4 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'maidenFirstName');

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'ssn' AS b, 'SSN (123-45-6789)' AS c, 'text' AS d, 1 AS e, 7 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'ssn');

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'addressState' AS b, 'State/Region/Province' AS c, 'text' AS d, 1 AS e, 10 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'addressState');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'addressZip' AS b, 'Postal / Zip Code' AS c, 'text' AS d, 1 AS e, 11 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'addressZip');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'everConvicted' AS b, 'Have You Ever Been Convicted of a Law Violation (excluding juvenile offenses)?' AS c, 'select' AS d, 1 AS e, 12 AS f, '{"options": ["Yes", "No"]}' AS g) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'everConvicted');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'convictedExplain' AS b, 'If Yes, List All and Explain' AS c, 'textarea' AS d, 0 AS e, 13 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'convictedExplain');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'pendingCharges' AS b, 'Are You the Subject of Any Pending Criminal Charges?' AS c, 'select' AS d, 1 AS e, 14 AS f, '{"options": ["Yes", "No"]}' AS g) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'pendingCharges');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'pendingChargesExplain' AS b, 'If Yes, List All and Explain' AS c, 'textarea' AS d, 0 AS e, 15 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'pendingChargesExplain');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 0 AS e, 16 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_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 @background_check_affirmation_id AS a, 'employeeSignature' AS b, 'Employee Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 17 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'employeeSignature');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @background_check_affirmation_id AS a, 'agreement' AS b, 'I swear the above statements are true and this constitutes my sworn statement of affirmation' AS c, 'checkbox' AS d, 1 AS e, 18 AS f) AS t
WHERE @background_check_affirmation_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @background_check_affirmation_id AND field_key = 'agreement');

-- ---- Drug Test Clearance Policy ----
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, 'drug-test-clearance-policy',
       'Drug Test Clearance Policy', '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 = 'drug-test-clearance-policy');

SET @drug_test_policy_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'drug-test-clearance-policy');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @drug_test_policy_id AS a, 'employeeFirstName' AS b, 'Employee First Name' AS c, 'text' AS d, 1 AS e, 1 AS f) AS t
WHERE @drug_test_policy_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @drug_test_policy_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 @drug_test_policy_id AS a, 'employeeLastName' AS b, 'Employee Last Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @drug_test_policy_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @drug_test_policy_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 @drug_test_policy_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 0 AS e, 3 AS f) AS t
WHERE @drug_test_policy_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @drug_test_policy_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 @drug_test_policy_id AS a, 'employeeSignature' AS b, 'Employee Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 4 AS f) AS t
WHERE @drug_test_policy_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @drug_test_policy_id AND field_key = 'employeeSignature');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @drug_test_policy_id AS a, 'agreement' AS b, 'I have read and agree to the Drug Test Clearance Policy' AS c, 'checkbox' AS d, 1 AS e, 5 AS f) AS t
WHERE @drug_test_policy_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @drug_test_policy_id AND field_key = 'agreement');

-- ---- Tuberculosis (TB) Test Record ----
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, 'tb-test',
       'Tuberculosis (TB) Test Record', 'Thank you — the TB test 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 = 'tb-test');

SET @tb_test_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'tb-test');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @tb_test_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 @tb_test_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @tb_test_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 @tb_test_id AS a, 'employeeFirstName' AS b, 'Employee First Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @tb_test_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @tb_test_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 @tb_test_id AS a, 'employeeLastName' AS b, 'Employee Last Name' AS c, 'text' AS d, 1 AS e, 3 AS f) AS t
WHERE @tb_test_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @tb_test_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 @tb_test_id AS a, 'email' AS b, 'Email' AS c, 'email' AS d, 1 AS e, 4 AS f) AS t
WHERE @tb_test_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @tb_test_id AND field_key = 'email');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @tb_test_id AS a, 'contractorType' AS b, 'Contractor Type' AS c, 'select' AS d, 1 AS e, 5 AS f, '{"options": ["Nurse", "Aide", "Admin or Operational"]}' AS g) AS t
WHERE @tb_test_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @tb_test_id AND field_key = 'contractorType');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @tb_test_id AS a, 'tbTestResult' AS b, 'TB Test Result' AS c, 'select' AS d, 0 AS e, 7 AS f, '{"options": ["Positive", "Negative"]}' AS g) AS t
WHERE @tb_test_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @tb_test_id AND field_key = 'tbTestResult');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @tb_test_id AS a, 'chestXrayResult' AS b, 'Chest X-Ray Result' AS c, 'select' AS d, 0 AS e, 9 AS f, '{"options": ["Positive", "Negative"]}' AS g) AS t
WHERE @tb_test_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @tb_test_id AND field_key = 'chestXrayResult');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @tb_test_id AS a, 'tbTestFile' AS b, 'Upload a Copy of TB Test' AS c, 'file' AS d, 0 AS e, 10 AS f) AS t
WHERE @tb_test_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @tb_test_id AND field_key = 'tbTestFile');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @tb_test_id AS a, 'reviewerFirstName' AS b, 'Employee Name That Reviewed the TB Test — First' AS c, 'text' AS d, 0 AS e, 11 AS f) AS t
WHERE @tb_test_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @tb_test_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 @tb_test_id AS a, 'reviewerLastName' AS b, 'Employee Name That Reviewed the TB Test — Last' AS c, 'text' AS d, 0 AS e, 12 AS f) AS t
WHERE @tb_test_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @tb_test_id AND field_key = 'reviewerLastName');

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

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

-- ---- Caregiver Conduct and Non Solicitation Agreement ----
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, 'caregiver-conduct-non-solicitation',
       'Caregiver Conduct and Non Solicitation Agreement', '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 = 'caregiver-conduct-non-solicitation');

SET @caregiver_conduct_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'caregiver-conduct-non-solicitation');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @caregiver_conduct_id AS a, 'caregiverFirstName' AS b, 'Caregiver First Name' AS c, 'text' AS d, 1 AS e, 1 AS f) AS t
WHERE @caregiver_conduct_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @caregiver_conduct_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 @caregiver_conduct_id AS a, 'caregiverLastName' AS b, 'Caregiver Last Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @caregiver_conduct_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @caregiver_conduct_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 @caregiver_conduct_id AS a, 'addressStreet' AS b, 'Street Address' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @caregiver_conduct_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @caregiver_conduct_id AND field_key = 'addressStreet');

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @caregiver_conduct_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 0 AS e, 7 AS f) AS t
WHERE @caregiver_conduct_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @caregiver_conduct_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 @caregiver_conduct_id AS a, 'employeeSignature' AS b, 'Employee Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 8 AS f) AS t
WHERE @caregiver_conduct_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @caregiver_conduct_id AND field_key = 'employeeSignature');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @caregiver_conduct_id AS a, 'agreement' AS b, 'I have read and agree to this Conduct and Non Solicitation Agreement' AS c, 'checkbox' AS d, 1 AS e, 9 AS f) AS t
WHERE @caregiver_conduct_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @caregiver_conduct_id AND field_key = 'agreement');

-- ---- Procedures/Handbook Acknowledgment ----
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, 'procedures-handbook-acknowledgment',
       'Procedures/Handbook Acknowledgment', '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 = 'procedures-handbook-acknowledgment');

SET @procedures_handbook_ack_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'procedures-handbook-acknowledgment');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @procedures_handbook_ack_id AS a, 'employeeFirstName' AS b, 'Employee First Name' AS c, 'text' AS d, 1 AS e, 1 AS f) AS t
WHERE @procedures_handbook_ack_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @procedures_handbook_ack_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 @procedures_handbook_ack_id AS a, 'employeeLastName' AS b, 'Employee Last Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @procedures_handbook_ack_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @procedures_handbook_ack_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 @procedures_handbook_ack_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 0 AS e, 3 AS f) AS t
WHERE @procedures_handbook_ack_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @procedures_handbook_ack_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 @procedures_handbook_ack_id AS a, 'employeeSignature' AS b, 'Employee Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 4 AS f) AS t
WHERE @procedures_handbook_ack_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @procedures_handbook_ack_id AND field_key = 'employeeSignature');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @procedures_handbook_ack_id AS a, 'agreement' AS b, 'I acknowledge I have read and understood the Procedures/Handbook' AS c, 'checkbox' AS d, 1 AS e, 5 AS f) AS t
WHERE @procedures_handbook_ack_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @procedures_handbook_ack_id AND field_key = 'agreement');

-- ---- Verification of Mailing/Physical Address and Driver's License Acknowledgment ----
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, 'address-license-verification',
       'Verification of Mailing/Physical Address and Driver''s License Acknowledgment', '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 = 'address-license-verification');

SET @address_license_verification_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'address-license-verification');

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

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'addressStreet' AS b, 'Physical Address — Street' AS c, 'text' AS d, 0 AS e, 5 AS f) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'addressStreet');

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'mailingSameAsPhysical' AS b, 'Mailing Address Same as Physical Address?' AS c, 'select' AS d, 1 AS e, 9 AS f, '{"options": ["Same as physical address", "Different from physical address"]}' AS g) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'mailingSameAsPhysical');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'mailingCity' AS b, 'Mailing Address — City (if different)' AS c, 'text' AS d, 0 AS e, 11 AS f) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'mailingCity');

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'dlState' AS b, 'Driver''s License — State of Issuance' AS c, 'text' AS d, 1 AS e, 14 AS f) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'dlState');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'dlNumber' AS b, 'Driver''s License Number' AS c, 'text' AS d, 1 AS e, 15 AS f) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'dlNumber');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'dlAddressStreet' AS b, 'Address on Driver''s License — Street' AS c, 'text' AS d, 0 AS e, 16 AS f) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'dlAddressStreet');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'dlAddressCity' AS b, 'Address on Driver''s License — City' AS c, 'text' AS d, 0 AS e, 17 AS f) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'dlAddressCity');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'dlAddressState' AS b, 'Address on Driver''s License — State/Region/Province' AS c, 'text' AS d, 0 AS e, 18 AS f) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'dlAddressState');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'dlAddressZip' AS b, 'Address on Driver''s License — Postal / Zip Code' AS c, 'text' AS d, 0 AS e, 19 AS f) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'dlAddressZip');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'attachmentsChecklist' AS b, 'Attachments Checklist (comma-separated: Driver''s License Front & Back, Utility Bill/Lease if different)' AS c, 'text' AS d, 0 AS e, 21 AS f) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'attachmentsChecklist');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @address_license_verification_id AS a, 'verificationFile' AS b, 'Upload Driver''s License / Address Verification Document(s)' AS c, 'file' AS d, 0 AS e, 22 AS f) AS t
WHERE @address_license_verification_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @address_license_verification_id AND field_key = 'verificationFile');

-- ---- Home Health Aide Performance Evaluation ----
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, 'home-health-aide-performance-evaluation',
       'Home Health Aide Performance Evaluation', 'Thank you — the evaluation 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 = 'home-health-aide-performance-evaluation');

SET @performance_eval_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'home-health-aide-performance-evaluation');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @performance_eval_id AS a, 'location' AS b, 'Employee 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 @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_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 @performance_eval_id AS a, 'employeeFirstName' AS b, 'Employee First Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_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 @performance_eval_id AS a, 'employeeLastName' AS b, 'Employee Last Name' AS c, 'text' AS d, 1 AS e, 3 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_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 @performance_eval_id AS a, 'employeeEmail' AS b, 'Employee Email' AS c, 'email' AS d, 1 AS e, 4 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'employeeEmail');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'evalPeriodEnd' AS b, 'Evaluation Period End' AS c, 'date' AS d, 1 AS e, 6 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'evalPeriodEnd');

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'clientCareHeading' AS b, 'Client Care (total available points = 21)' AS c, 'heading' AS d, 0 AS e, 10 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'clientCareHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'clientCareCompassion' AS b, 'Demonstrates compassion and patience with clients (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 11 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'clientCareCompassion');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'clientCarePersonalCare' AS b, 'Provides personal care services according to the care plan (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 12 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'clientCarePersonalCare');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'clientCareEnvironment' AS b, 'Maintains client''s environment in a clean and safe manner (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 13 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'clientCareEnvironment');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'communicationHeading' AS b, 'Communication Skills (total available points = 21)' AS c, 'heading' AS d, 0 AS e, 14 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'communicationHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'commEffective' AS b, 'Communicates effectively with clients, families, and team members (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 15 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'commEffective');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'commReportsChanges' AS b, 'Reports changes in client''s condition promptly (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 16 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'commReportsChanges');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'commDocumentation' AS b, 'Documents are provided accurately and comprehensively (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 17 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'commDocumentation');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'dependabilityHeading' AS b, 'Dependability (total available points = 21)' AS c, 'heading' AS d, 0 AS e, 18 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'dependabilityHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'dependOnTime' AS b, 'Arrives on time for scheduled shifts (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 19 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'dependOnTime');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'dependReliable' AS b, 'Shows reliability in completing assigned tasks (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 20 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'dependReliable');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'dependPolicies' AS b, 'Follows agency policies and procedures (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 21 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'dependPolicies');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'professionalismHeading' AS b, 'Professionalism (total available points = 21)' AS c, 'heading' AS d, 0 AS e, 22 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'professionalismHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'profConfidentiality' AS b, 'Maintains client confidentiality (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 23 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'profConfidentiality');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'profRespect' AS b, 'Demonstrates respect for clients and their property (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 24 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'profRespect');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'profDressCode' AS b, 'Adheres to dress code and presents a professional image (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 25 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'profDressCode');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'teamworkHeading' AS b, 'Teamwork and Collaboration (total available points = 21)' AS c, 'heading' AS d, 0 AS e, 26 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'teamworkHeading');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'teamCooperative' AS b, 'Works cooperatively with other team members (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 27 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'teamCooperative');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'teamHelpsPeers' AS b, 'Offers help to peers when needed (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 28 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'teamHelpsPeers');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'teamParticipation' AS b, 'Participates in staff meetings and training sessions (1-7, 1=lowest, 7=highest)' AS c, 'number' AS d, 1 AS e, 29 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'teamParticipation');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'evaluatorComments' AS b, 'Evaluator Comments (opportunity for growth and professional development)' AS c, 'textarea' AS d, 1 AS e, 30 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'evaluatorComments');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @performance_eval_id AS a, 'overallRating' AS b, 'Overall Performance Rating (total possible score = 105)' AS c, 'select' AS d, 1 AS e, 31 AS f, '{"options": ["Outstanding 80-105", "Exceeds Expectations 60-79", "Meets Expectations 40-59", "Needs Improvement 20-39", "Unsatisfactory 1-19"]}' AS g) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'overallRating');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'evaluatorEmail' AS b, 'Evaluator Email' AS c, 'email' AS d, 1 AS e, 32 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'evaluatorEmail');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @performance_eval_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 1 AS e, 33 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_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 @performance_eval_id AS a, 'evaluatorSignature' AS b, 'Evaluator Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 34 AS f) AS t
WHERE @performance_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @performance_eval_id AND field_key = 'evaluatorSignature');

-- ---- Offer Letter of Employment ----
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, 'offer-letter-employment',
       'Offer Letter of Employment', 'Thank you — the offer letter 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 = 'offer-letter-employment');

SET @offer_letter_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'offer-letter-employment');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @offer_letter_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 @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_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 @offer_letter_id AS a, 'employeeFirstName' AS b, 'Employee Aide First Name' AS c, 'text' AS d, 0 AS e, 2 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_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 @offer_letter_id AS a, 'employeeLastName' AS b, 'Employee Aide Last Name' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_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 @offer_letter_id AS a, 'employeeEmail' AS b, 'Employee Email Address' AS c, 'email' AS d, 1 AS e, 4 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_id AND field_key = 'employeeEmail');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @offer_letter_id AS a, 'startDate' AS b, 'Start Date' AS c, 'date' AS d, 1 AS e, 6 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_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 @offer_letter_id AS a, 'payRateWeekday' AS b, 'Pay Rate (Weekday)' AS c, 'number' AS d, 1 AS e, 7 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_id AND field_key = 'payRateWeekday');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @offer_letter_id AS a, 'payRateWeekend' AS b, 'Pay Rate (Weekend)' AS c, 'number' AS d, 1 AS e, 8 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_id AND field_key = 'payRateWeekend');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @offer_letter_id AS a, 'payRateHoliday' AS b, 'Pay Rate (Holiday)' AS c, 'number' AS d, 1 AS e, 9 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_id AND field_key = 'payRateHoliday');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @offer_letter_id AS a, 'deadlineToSign' AS b, 'Deadline to Sign and Return Offer Letter' AS c, 'date' AS d, 1 AS e, 10 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_id AND field_key = 'deadlineToSign');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @offer_letter_id AS a, 'representativeInitials' AS b, 'Representative Initials' AS c, 'text' AS d, 1 AS e, 11 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_id AND field_key = 'representativeInitials');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @offer_letter_id AS a, 'hrFirstName' AS b, 'Contact Person/HR Representative — First Name' AS c, 'text' AS d, 1 AS e, 12 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_id AND field_key = 'hrFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @offer_letter_id AS a, 'hrLastName' AS b, 'Contact Person/HR Representative — Last Name' AS c, 'text' AS d, 1 AS e, 13 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_id AND field_key = 'hrLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @offer_letter_id AS a, 'hrJobTitle' AS b, 'HR Job Title' AS c, 'text' AS d, 0 AS e, 14 AS f) AS t
WHERE @offer_letter_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @offer_letter_id AND field_key = 'hrJobTitle');

-- ---- LarCare/SamariCare Services Handbook Acknowledgment ----
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, 'employee-handbook-acknowledgment',
       'LarCare/SamariCare Services Handbook Acknowledgment', 'Thank you — your acknowledgment 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 = 'employee-handbook-acknowledgment');

SET @employee_handbook_ack_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'employee-handbook-acknowledgment');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @employee_handbook_ack_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 @employee_handbook_ack_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @employee_handbook_ack_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 @employee_handbook_ack_id AS a, 'employeeFirstName' AS b, 'Employee First Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @employee_handbook_ack_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @employee_handbook_ack_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 @employee_handbook_ack_id AS a, 'employeeLastName' AS b, 'Employee Last Name' AS c, 'text' AS d, 1 AS e, 3 AS f) AS t
WHERE @employee_handbook_ack_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @employee_handbook_ack_id AND field_key = 'employeeLastName');

-- ---- Contractor W9 ----
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, 'contractor-w9',
       'Contractor W9', 'Thank you — your W9 information 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 = 'contractor-w9');

SET @contractor_w9_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'contractor-w9');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_w9_id AS a, 'location' AS b, 'Choose Reporting State' 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 @contractor_w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_w9_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 @contractor_w9_id AS a, 'firstName' AS b, 'First Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @contractor_w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_w9_id AND field_key = 'firstName');

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

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @contractor_w9_id AS a, 'entityType' AS b, 'Federal Tax Classification' AS c, 'select' AS d, 1 AS e, 6 AS f, '{"options": ["Individual"]}' AS g) AS t
WHERE @contractor_w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_w9_id AND field_key = 'entityType');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_w9_id AS a, 'addressStreet' AS b, 'Street Address' AS c, 'text' AS d, 1 AS e, 7 AS f) AS t
WHERE @contractor_w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_w9_id AND field_key = 'addressStreet');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_w9_id AS a, 'addressState' AS b, 'State / Region / Province' AS c, 'text' AS d, 1 AS e, 9 AS f) AS t
WHERE @contractor_w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_w9_id AND field_key = 'addressState');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_w9_id AS a, 'addressZip' AS b, 'Postal / Zip Code' AS c, 'text' AS d, 1 AS e, 10 AS f) AS t
WHERE @contractor_w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_w9_id AND field_key = 'addressZip');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_w9_id AS a, 'ssn' AS b, 'SSN' AS c, 'text' AS d, 1 AS e, 11 AS f) AS t
WHERE @contractor_w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_w9_id AND field_key = 'ssn');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contractor_w9_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 1 AS e, 12 AS f) AS t
WHERE @contractor_w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_w9_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 @contractor_w9_id AS a, 'signature' AS b, 'Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 13 AS f) AS t
WHERE @contractor_w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contractor_w9_id AND field_key = 'signature');

-- ---- Client Acknowledgement of Aide's Criminal Background History ----
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-ack-aide-background-history',
       'Client Acknowledgement of Aide''s Criminal Background History', '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 = 'client-ack-aide-background-history');

SET @client_ack_background_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'client-ack-aide-background-history');

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_ack_background_id AS a, 'caregiverFirstName' AS b, 'Caregiver First Name' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @client_ack_background_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_ack_background_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 @client_ack_background_id AS a, 'caregiverLastName' AS b, 'Caregiver Last Name' AS c, 'text' AS d, 0 AS e, 4 AS f) AS t
WHERE @client_ack_background_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_ack_background_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 @client_ack_background_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 0 AS e, 5 AS f) AS t
WHERE @client_ack_background_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_ack_background_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 @client_ack_background_id AS a, 'employeeSignature' AS b, 'Employee Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 6 AS f) AS t
WHERE @client_ack_background_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_ack_background_id AND field_key = 'employeeSignature');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @client_ack_background_id AS a, 'agreement' AS b, 'I confirm the above signature is authentic and electronically executed' AS c, 'checkbox' AS d, 1 AS e, 7 AS f) AS t
WHERE @client_ack_background_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @client_ack_background_id AND field_key = 'agreement');

-- ---- Twelve Strike Policy Acknowledgment ----
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, 'twelve-strike-policy',
       'Twelve Strike Policy Acknowledgment', '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 = 'twelve-strike-policy');

SET @twelve_strike_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'twelve-strike-policy');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @twelve_strike_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 @twelve_strike_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @twelve_strike_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 @twelve_strike_id AS a, 'employeeFirstName' AS b, 'Employee First Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @twelve_strike_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @twelve_strike_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 @twelve_strike_id AS a, 'employeeLastName' AS b, 'Employee Last Name' AS c, 'text' AS d, 1 AS e, 3 AS f) AS t
WHERE @twelve_strike_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @twelve_strike_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 @twelve_strike_id AS a, 'employeeSignature' AS b, 'Employee Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 4 AS f) AS t
WHERE @twelve_strike_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @twelve_strike_id AND field_key = 'employeeSignature');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @twelve_strike_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 0 AS e, 5 AS f) AS t
WHERE @twelve_strike_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @twelve_strike_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 @twelve_strike_id AS a, 'agreement' AS b, 'I have read and agree to the Twelve Strike Policy' AS c, 'checkbox' AS d, 1 AS e, 6 AS f) AS t
WHERE @twelve_strike_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @twelve_strike_id AND field_key = 'agreement');

-- ---- Competency Evaluation 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, 'competency-evaluation',
       'Competency Evaluation Form', 'Thank you — the evaluation 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 = 'competency-evaluation');

SET @competency_eval_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'competency-evaluation');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @competency_eval_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 @competency_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @competency_eval_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 @competency_eval_id AS a, 'contractorFirstName' AS b, 'Contractor First Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @competency_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @competency_eval_id AND field_key = 'contractorFirstName');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @competency_eval_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 0 AS e, 4 AS f) AS t
WHERE @competency_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @competency_eval_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 @competency_eval_id AS a, 'employeeSignature' AS b, 'Employee Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 5 AS f) AS t
WHERE @competency_eval_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @competency_eval_id AND field_key = 'employeeSignature');

-- ---- Photo/Video Disclaimer and Consent ----
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, 'photo-video-consent',
       'Photo/Video Disclaimer and Consent', 'Thank you — your consent 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 = 'photo-video-consent');

SET @photo_video_consent_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'photo-video-consent');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @photo_video_consent_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 @photo_video_consent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @photo_video_consent_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 @photo_video_consent_id AS a, 'firstName' AS b, 'First Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @photo_video_consent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @photo_video_consent_id AND field_key = 'firstName');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @photo_video_consent_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 0 AS e, 4 AS f) AS t
WHERE @photo_video_consent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @photo_video_consent_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 @photo_video_consent_id AS a, 'signature' AS b, 'Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 5 AS f) AS t
WHERE @photo_video_consent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @photo_video_consent_id AND field_key = 'signature');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @photo_video_consent_id AS a, 'agreement' AS b, 'I consent to the Photo/Video Disclaimer and Consent terms' AS c, 'checkbox' AS d, 1 AS e, 6 AS f) AS t
WHERE @photo_video_consent_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @photo_video_consent_id AND field_key = 'agreement');

-- ---- Substitute Aide Request ----
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, 'substitute-aide-request',
       'Substitute Aide Request', 'Thank you — the substitute 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 = 'substitute-aide-request');

SET @substitute_aide_request_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'substitute-aide-request');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @substitute_aide_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 @substitute_aide_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @substitute_aide_request_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 @substitute_aide_request_id AS a, 'contractorType' AS b, 'Contractor Aide/Nurse Type' AS c, 'select' AS d, 1 AS e, 2 AS f, '{"options": ["Nurse", "Aide"]}' AS g) AS t
WHERE @substitute_aide_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @substitute_aide_request_id AND field_key = 'contractorType');

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

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @substitute_aide_request_id AS a, 'fillingInForFirstName' AS b, 'Employee You Are Filling In For — First Name' AS c, 'text' AS d, 1 AS e, 5 AS f) AS t
WHERE @substitute_aide_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @substitute_aide_request_id AND field_key = 'fillingInForFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @substitute_aide_request_id AS a, 'fillingInForLastName' AS b, 'Employee You Are Filling In For — Last Name' AS c, 'text' AS d, 0 AS e, 6 AS f) AS t
WHERE @substitute_aide_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @substitute_aide_request_id AND field_key = 'fillingInForLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @substitute_aide_request_id AS a, 'patientFirstName' AS b, 'Patient Being Served Today — First Name' AS c, 'text' AS d, 0 AS e, 7 AS f) AS t
WHERE @substitute_aide_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @substitute_aide_request_id AND field_key = 'patientFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @substitute_aide_request_id AS a, 'patientLastName' AS b, 'Patient Being Served Today — Last Name' AS c, 'text' AS d, 0 AS e, 8 AS f) AS t
WHERE @substitute_aide_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @substitute_aide_request_id AND field_key = 'patientLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @substitute_aide_request_id AS a, 'agreedPayRate' AS b, 'Agreed Pay Rate for Hours Served (USD)' AS c, 'number' AS d, 1 AS e, 9 AS f) AS t
WHERE @substitute_aide_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @substitute_aide_request_id AND field_key = 'agreedPayRate');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @substitute_aide_request_id AS a, 'hoursToCover' AS b, 'How Many Hours Should Be Covered' AS c, 'number' AS d, 1 AS e, 10 AS f) AS t
WHERE @substitute_aide_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @substitute_aide_request_id AND field_key = 'hoursToCover');

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

-- ---- Call Out or Request Time Off ----
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, 'call-out-time-off-request',
       'Call Out or Request Time Off', 'Thank you — your 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 = 'call-out-time-off-request');

SET @call_out_request_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'call-out-time-off-request');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @call_out_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 @call_out_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @call_out_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 @call_out_request_id AS a, 'employeeFirstName' AS b, 'Aide or Employee First Name Requesting Time Off' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @call_out_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @call_out_request_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 @call_out_request_id AS a, 'employeeLastName' AS b, 'Aide or Employee Last Name Requesting Time Off' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @call_out_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @call_out_request_id AND field_key = 'employeeLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @call_out_request_id AS a, 'contractorType' AS b, 'Contractor Aide/Nurse Type' AS c, 'select' AS d, 1 AS e, 4 AS f, '{"options": ["Aide", "Nurse", "Admin or Operational"]}' AS g) AS t
WHERE @call_out_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @call_out_request_id AND field_key = 'contractorType');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @call_out_request_id AS a, 'dateOffFrom' AS b, 'Date Requested Off — From' AS c, 'date' AS d, 0 AS e, 6 AS f) AS t
WHERE @call_out_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @call_out_request_id AND field_key = 'dateOffFrom');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @call_out_request_id AS a, 'patientFirstName' AS b, 'Patient Name Being Serviced — First Name' AS c, 'text' AS d, 0 AS e, 7 AS f) AS t
WHERE @call_out_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @call_out_request_id AND field_key = 'patientFirstName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @call_out_request_id AS a, 'patientLastName' AS b, 'Patient Name Being Serviced — Last Name' AS c, 'text' AS d, 0 AS e, 8 AS f) AS t
WHERE @call_out_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @call_out_request_id AND field_key = 'patientLastName');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @call_out_request_id AS a, 'reason' AS b, 'Reason for Time Off' AS c, 'textarea' AS d, 1 AS e, 9 AS f) AS t
WHERE @call_out_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @call_out_request_id AND field_key = 'reason');

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

-- ---- Team Task and Work Orders ----
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, 'team-task-work-order',
       'Team Task and Work Orders', 'Thank you — your request has been submitted to the team.', 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 = 'team-task-work-order');

SET @team_task_work_order_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'team-task-work-order');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @team_task_work_order_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 @team_task_work_order_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @team_task_work_order_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 @team_task_work_order_id AS a, 'firstName' AS b, 'First Name' AS c, 'text' AS d, 0 AS e, 2 AS f) AS t
WHERE @team_task_work_order_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @team_task_work_order_id AND field_key = 'firstName');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @team_task_work_order_id AS a, 'contractorType' AS b, 'Contractor Aide/Nurse Type' AS c, 'select' AS d, 1 AS e, 4 AS f, '{"options": ["Aide", "Nurse", "Admin or Operational"]}' AS g) AS t
WHERE @team_task_work_order_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @team_task_work_order_id AND field_key = 'contractorType');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @team_task_work_order_id AS a, 'taskDescription' AS b, 'What Do You Need the Team to Do?' AS c, 'textarea' AS d, 1 AS e, 5 AS f) AS t
WHERE @team_task_work_order_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @team_task_work_order_id AND field_key = 'taskDescription');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @team_task_work_order_id AS a, 'attachmentFile' AS b, 'Upload File / Voice Note (if not able to type your message)' AS c, 'file' AS d, 0 AS e, 6 AS f) AS t
WHERE @team_task_work_order_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @team_task_work_order_id AND field_key = 'attachmentFile');

-- ---- Company Culture Awareness Standard Operating Agreement ----
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, 'company-culture-awareness-sop',
       'Company Culture Awareness Standard Operating Agreement', '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 = 'company-culture-awareness-sop');

SET @culture_awareness_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'company-culture-awareness-sop');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @culture_awareness_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 @culture_awareness_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @culture_awareness_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 @culture_awareness_id AS a, 'employeeFirstName' AS b, 'Employee First Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @culture_awareness_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @culture_awareness_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 @culture_awareness_id AS a, 'employeeLastName' AS b, 'Employee Last Name' AS c, 'text' AS d, 1 AS e, 3 AS f) AS t
WHERE @culture_awareness_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @culture_awareness_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 @culture_awareness_id AS a, 'email' AS b, 'Email' AS c, 'email' AS d, 1 AS e, 4 AS f) AS t
WHERE @culture_awareness_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @culture_awareness_id AND field_key = 'email');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @culture_awareness_id AS a, 'dateTime' AS b, 'Date-Time' AS c, 'date' AS d, 0 AS e, 5 AS f) AS t
WHERE @culture_awareness_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @culture_awareness_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 @culture_awareness_id AS a, 'signature' AS b, 'Signature — type your full legal name to sign' AS c, 'text' AS d, 1 AS e, 6 AS f) AS t
WHERE @culture_awareness_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @culture_awareness_id AND field_key = 'signature');

-- ---- Request for Veteran Care ----
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, 'request-veteran-care',
       'Request for Veteran Care', 'Thank you — the 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 = 'request-veteran-care');

SET @veteran_care_request_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'request-veteran-care');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order, config_json)
SELECT * FROM (SELECT @veteran_care_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 @veteran_care_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @veteran_care_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 @veteran_care_request_id AS a, 'employeeFirstName' AS b, 'Employee First Name' AS c, 'text' AS d, 0 AS e, 2 AS f) AS t
WHERE @veteran_care_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @veteran_care_request_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 @veteran_care_request_id AS a, 'employeeLastName' AS b, 'Employee Last Name' AS c, 'text' AS d, 0 AS e, 3 AS f) AS t
WHERE @veteran_care_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @veteran_care_request_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 @veteran_care_request_id AS a, 'date' AS b, 'Date' AS c, 'date' AS d, 1 AS e, 4 AS f) AS t
WHERE @veteran_care_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @veteran_care_request_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 @veteran_care_request_id AS a, 'patientFirstName' AS b, 'Patient First Name' AS c, 'text' AS d, 0 AS e, 5 AS f) AS t
WHERE @veteran_care_request_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @veteran_care_request_id AND field_key = 'patientFirstName');

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

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

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