-- Adds the 4 form definitions LarCare's /forms page already links to but that never had
-- a dim_web_forms row: Employment Application, Employment Direct Deposit, Employment W9,
-- and the Patient Satisfaction & Outcomes Survey. The generic public form endpoints
-- (GET/POST /api/public/web/forms/{formKey}) already handle any form_key present here —
-- no backend code change needed, this is purely data.
-- Idempotent (guarded by form_key existence per site), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/larcare_forms_seed.sql

USE db_abos_v0.1;

-- LarCare Services is the only site in this tenant today; resolve it by domain so this
-- migration doesn't hardcode an id that could differ across environments.
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);

-- ---- Employment 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, 'employment-application',
       'Employment Application', 'Thanks 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 = 'employment-application');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @employment_application_id AS a, 'firstName' AS b, 'First Name' AS c, 'text' AS d, 1 AS e, 1 AS f) AS t
WHERE @employment_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @employment_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 @employment_application_id AS a, 'lastName' AS b, 'Last Name' AS c, 'text' AS d, 1 AS e, 2 AS f) AS t
WHERE @employment_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @employment_application_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 @employment_application_id AS a, 'email' AS b, 'Email' AS c, 'email' AS d, 1 AS e, 3 AS f) AS t
WHERE @employment_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @employment_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 @employment_application_id AS a, 'phone' AS b, 'Phone' AS c, 'tel' AS d, 1 AS e, 4 AS f) AS t
WHERE @employment_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @employment_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 @employment_application_id AS a, 'positionAppliedFor' AS b, 'Position Applied For' AS c, 'text' AS d, 1 AS e, 5 AS f) AS t
WHERE @employment_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @employment_application_id AND field_key = 'positionAppliedFor');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @employment_application_id AS a, 'availableStartDate' AS b, 'Available Start Date' AS c, 'date' AS d, 1 AS e, 6 AS f) AS t
WHERE @employment_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @employment_application_id AND field_key = 'availableStartDate');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @employment_application_id AS a, 'message' AS b, 'Relevant Experience / Cover Message' AS c, 'textarea' AS d, 1 AS e, 7 AS f) AS t
WHERE @employment_application_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @employment_application_id AND field_key = 'message');

-- ---- Employment 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, 'employment-direct-deposit',
       'Employment Direct Deposit', 'Thanks — 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 = 'employment-direct-deposit');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @direct_deposit_id AS a, 'employeeName' AS b, 'Employee Name' AS c, 'text' AS d, 1 AS e, 1 AS f) AS t
WHERE @direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @direct_deposit_id AND field_key = 'employeeName');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @direct_deposit_id AS a, 'email' AS b, 'Email' AS c, 'email' AS d, 1 AS e, 2 AS f) AS t
WHERE @direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @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 @direct_deposit_id AS a, 'bankName' AS b, 'Bank Name' AS c, 'text' AS d, 1 AS e, 3 AS f) AS t
WHERE @direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @direct_deposit_id AND field_key = 'bankName');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @direct_deposit_id AS a, 'routingNumber' AS b, 'Routing Number' AS c, 'text' AS d, 1 AS e, 4 AS f) AS t
WHERE @direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @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 @direct_deposit_id AS a, 'accountNumber' AS b, 'Account Number' AS c, 'text' AS d, 1 AS e, 5 AS f) AS t
WHERE @direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @direct_deposit_id AND field_key = 'accountNumber');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @direct_deposit_id AS a, 'accountType' AS b, 'Account Type (Checking or Savings)' AS c, 'text' AS d, 1 AS e, 6 AS f) AS t
WHERE @direct_deposit_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @direct_deposit_id AND field_key = 'accountType');

-- ---- Employment 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, 'employment-w9',
       'Employment W9', 'Thanks — 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 = 'employment-w9');

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

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @w9_id AS a, 'legalName' AS b, 'Legal Name' AS c, 'text' AS d, 1 AS e, 1 AS f) AS t
WHERE @w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @w9_id AND field_key = 'legalName');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @w9_id AS a, 'businessName' AS b, 'Business Name (if applicable)' AS c, 'text' AS d, 0 AS e, 2 AS f) AS t
WHERE @w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @w9_id AND field_key = 'businessName');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @w9_id AS a, 'email' AS b, 'Email' AS c, 'email' AS d, 1 AS e, 3 AS f) AS t
WHERE @w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @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 @w9_id AS a, 'taxClassification' AS b, 'Federal Tax Classification' AS c, 'text' AS d, 1 AS e, 4 AS f) AS t
WHERE @w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @w9_id AND field_key = 'taxClassification');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @w9_id AS a, 'address' AS b, 'Address (Number, Street, City, State, ZIP)' AS c, 'textarea' AS d, 1 AS e, 5 AS f) AS t
WHERE @w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @w9_id AND field_key = 'address');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @w9_id AS a, 'taxIdNumber' AS b, 'SSN or EIN' AS c, 'text' AS d, 1 AS e, 6 AS f) AS t
WHERE @w9_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @w9_id AND field_key = 'taxIdNumber');

-- ---- Patient Satisfaction & Outcomes Survey ----
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, 'patient-satisfaction-survey',
       'Patient Satisfaction & Outcomes Survey', 'Thank you for your feedback — it helps us improve the care we provide.', 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 = 'patient-satisfaction-survey');

SET @survey_id = (SELECT id FROM dim_web_forms WHERE site_id = @larcare_site_id AND form_key = 'patient-satisfaction-survey');

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @survey_id AS a, 'patientName' AS b, 'Patient Name (optional)' AS c, 'text' AS d, 0 AS e, 1 AS f) AS t
WHERE @survey_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @survey_id AND field_key = 'patientName');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @survey_id AS a, 'careDate' AS b, 'Date of Care Received' AS c, 'date' AS d, 1 AS e, 2 AS f) AS t
WHERE @survey_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @survey_id AND field_key = 'careDate');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @survey_id AS a, 'satisfactionRating' AS b, 'Overall Satisfaction (1-5)' AS c, 'number' AS d, 1 AS e, 3 AS f) AS t
WHERE @survey_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @survey_id AND field_key = 'satisfactionRating');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @survey_id AS a, 'wouldRecommend' AS b, 'I would recommend LarCare to others' AS c, 'checkbox' AS d, 0 AS e, 4 AS f) AS t
WHERE @survey_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @survey_id AND field_key = 'wouldRecommend');
INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @survey_id AS a, 'comments' AS b, 'Additional Comments' AS c, 'textarea' AS d, 0 AS e, 5 AS f) AS t
WHERE @survey_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @survey_id AND field_key = 'comments');