USE db_abos_v0.1;

-- Adds dim_web_forms.skip_crm_deal — internal/operational forms (staff HR paperwork, nurse
-- reports, compliance acknowledgments) shouldn't create a sales Opportunity/Project every time
-- someone submits one; only genuine new-business inquiries should. See CrmDealFromWebService
-- and PublicWebService.submitForm(). Guarded, safe to re-run.

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_web_forms' AND COLUMN_NAME = 'skip_crm_deal');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `dim_web_forms` ADD COLUMN `skip_crm_deal` tinyint(1) DEFAULT ''0''', 'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- Flags every LarCare form that is not a new-business inquiry: staff/contractor HR &
-- compliance paperwork, nurse/clinical ops reports, and existing-client legal/intake
-- paperwork. Left OFF (still creates an Opportunity) for the small set of forms that are
-- genuine new-inquiry entry points: contact, private-care-inquiry, request-veteran-care,
-- sms-consent, patient-satisfaction-survey.
--
-- Matches by form_key (not a specific tenant/site) so this applies correctly regardless of
-- which site/tenant a form_key was seeded under.
UPDATE dim_web_forms
SET skip_crm_deal = 1
WHERE form_key IN (
  -- Client/Patient — existing-client legal, rights, and intake paperwork, not new inquiries
  'patient-intake', 'new-patient-checklist', 'va-rights-to-complain', 'sc-rights-to-complain',
  'client-rights-va', 'personal-care-agreement', 'va-advance-directives', 'sc-advance-directives',
  'private-pay-invoice-request', 'client-non-solicitation-agreement', 'emergency-preparedness-plan',
  -- Caregivers/Contractors — HR, onboarding, and compliance paperwork
  'daily-tasksheet-report', 'contractor-application', 'contractor-direct-deposit',
  'contractor-health-assessment', 'criminal-background-check-affirmation', 'drug-test-clearance-policy',
  'tb-test', 'caregiver-conduct-non-solicitation', 'procedures-handbook-acknowledgment',
  'address-license-verification', 'home-health-aide-performance-evaluation', 'offer-letter-employment',
  'employee-handbook-acknowledgment', 'contractor-w9', 'client-ack-aide-background-history',
  'twelve-strike-policy', 'competency-evaluation', 'photo-video-consent', 'substitute-aide-request',
  'call-out-time-off-request', 'team-task-work-order', 'company-culture-awareness-sop',
  'employment-application', 'employment-direct-deposit', 'employment-w9',
  -- Nurses — clinical/operational reports, not sales inquiries
  'nurse-site-visit-report-invoice', 'in-home-site-visit-report', 'home-care-assessment',
  'home-health-nurse-performance-evaluation',
  -- Operations/HR/Training
  'background-check-result', 'orientation-training-tracking', 'skills-training-checkoff',
  'client-emergency-preparedness-document', 'apprenticeship-request'
);