-- DentalCare v0.5.9.6 clinical integrity backfill.

-- A legacy manually accepted flag is not procedure-specific consent.
UPDATE `{{prefix}}treatment_plan_items`
SET consent_status='pending',updated_at=NOW()
WHERE consent_status='accepted'
  AND NOT EXISTS (
      SELECT 1 FROM `{{prefix}}consent_forms` c
      WHERE c.treatment_plan_item_id=`{{prefix}}treatment_plan_items`.id
        AND c.status='signed'
        AND c.form_hash IS NOT NULL
        AND c.signature_hash IS NOT NULL
  );

-- Credential verification is derived only from a current verified license
-- document and complete profile license information.
UPDATE `{{prefix}}dentist_profiles` dp
SET dp.credential_status=CASE
        WHEN NULLIF(TRIM(dp.license_number),'') IS NOT NULL
         AND dp.license_expiry IS NOT NULL
         AND dp.license_expiry>=CURDATE()
         AND EXISTS (
             SELECT 1 FROM `{{prefix}}dentist_documents` dd
             WHERE dd.dentist_user_id=dp.user_id
               AND dd.document_type='license'
               AND dd.verification_status='verified'
               AND (dd.expires_at IS NULL OR dd.expires_at>=CURDATE())
         ) THEN 'verified'
        ELSE 'pending'
    END,
    dp.credential_verified_at=CASE
        WHEN NULLIF(TRIM(dp.license_number),'') IS NOT NULL
         AND dp.license_expiry IS NOT NULL
         AND dp.license_expiry>=CURDATE()
         AND EXISTS (
             SELECT 1 FROM `{{prefix}}dentist_documents` dd
             WHERE dd.dentist_user_id=dp.user_id
               AND dd.document_type='license'
               AND dd.verification_status='verified'
               AND (dd.expires_at IS NULL OR dd.expires_at>=CURDATE())
         ) THEN COALESCE(dp.credential_verified_at,NOW())
        ELSE NULL
    END,
    dp.profile_status=CASE
        WHEN NULLIF(TRIM(dp.license_number),'') IS NOT NULL
         AND dp.license_expiry IS NOT NULL
         AND dp.license_expiry>=CURDATE()
         AND EXISTS (
             SELECT 1 FROM `{{prefix}}dentist_documents` dd
             WHERE dd.dentist_user_id=dp.user_id
               AND dd.document_type='license'
               AND dd.verification_status='verified'
               AND (dd.expires_at IS NULL OR dd.expires_at>=CURDATE())
         ) THEN 'active'
        ELSE 'draft'
    END,
    dp.credential_note=CASE
        WHEN NULLIF(TRIM(dp.license_number),'') IS NOT NULL
         AND dp.license_expiry IS NOT NULL
         AND dp.license_expiry>=CURDATE()
         AND EXISTS (
             SELECT 1 FROM `{{prefix}}dentist_documents` dd
             WHERE dd.dentist_user_id=dp.user_id
               AND dd.document_type='license'
               AND dd.verification_status='verified'
               AND (dd.expires_at IS NULL OR dd.expires_at>=CURDATE())
         ) THEN COALESCE(dp.credential_note,'Verified from an existing current professional-license document during v0.5.9.6 migration.')
        ELSE COALESCE(dp.credential_note,'Clinical access is pending verification of a current professional license.')
    END;

-- Do not preserve clinical grants for an unverified profile.
UPDATE `{{prefix}}dentist_clinical_privileges` dcp
JOIN `{{prefix}}dentist_profiles` dp ON dp.user_id=dcp.dentist_user_id
SET dcp.status='pending',dcp.updated_at=NOW()
WHERE dp.credential_status<>'verified'
  AND dcp.status='granted';
