-- Adds the "Upload Personal Care Agreement" file field to LarCare's existing Contact Form,
-- closing the gap vs the original WordPress site's contact-us-2 form. Backend support for a
-- multipart submission with a file part already exists generically (PublicWebController's
-- consumes=MULTIPART_FORM_DATA_VALUE overload + AttachableEntityType.WEB_FORM_SUBMISSION) —
-- this migration only adds the field definition, no schema change needed.
-- Idempotent (guarded by field_key existence), additive only.
-- Deploy: mysql -u root -p db_abos_v0.1 < db/larcare_contact_form_file_field.sql

USE db_abos_v0.1;

SET @contact_form_id = (
  SELECT f.id FROM dim_web_forms f
  JOIN dim_web_sites s ON f.site_id = s.id
  WHERE s.primary_domain = 'larcareservices.com' AND f.form_key = 'contact'
  LIMIT 1
);

INSERT INTO dim_web_form_fields (form_id, field_key, label, field_type, is_required, sort_order)
SELECT * FROM (SELECT @contact_form_id AS a, 'personalCareAgreement' AS b, 'Upload Personal Care Agreement (optional)' AS c, 'file' AS d, 0 AS e, 5 AS f) AS t
WHERE @contact_form_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM dim_web_form_fields WHERE form_id = @contact_form_id AND field_key = 'personalCareAgreement');