-- DentalCare v0.5.9.5
-- Backfill legacy patients that predate branch ownership. Prefer their
-- latest appointment or queue branch, then the first active clinic branch.
UPDATE `{{prefix}}patients` p
SET p.branch_id=COALESCE(
    (SELECT a.branch_id FROM `{{prefix}}appointments` a WHERE a.patient_id=p.id AND a.branch_id IS NOT NULL ORDER BY a.start_at DESC,a.id DESC LIMIT 1),
    (SELECT q.branch_id FROM `{{prefix}}queue_entries` q WHERE q.patient_id=p.id AND q.branch_id IS NOT NULL ORDER BY q.checked_in_at DESC,q.id DESC LIMIT 1),
    (SELECT b.id FROM `{{prefix}}branches` b WHERE b.is_active=1 ORDER BY b.id LIMIT 1)
)
WHERE p.branch_id IS NULL;

-- Existing medical-history records created by a Dentist remain verified.
-- All other legacy versions are retained but require dentist review.
UPDATE `{{prefix}}medical_histories` mh
LEFT JOIN `{{prefix}}users` u ON u.id=mh.created_by
LEFT JOIN `{{prefix}}roles` r ON r.id=u.role_id
SET mh.source_type=CASE WHEN r.code='dentist' THEN 'clinician_verified' ELSE 'legacy_record' END,
    mh.verification_status=CASE WHEN r.code='dentist' THEN 'verified' ELSE 'pending_review' END,
    mh.verified_by=CASE WHEN r.code='dentist' THEN mh.created_by ELSE NULL END,
    mh.verified_at=CASE WHEN r.code='dentist' THEN COALESCE(mh.confirmed_at,mh.created_at) ELSE NULL END,
    mh.confirmed_at=CASE WHEN r.code='dentist' THEN COALESCE(mh.confirmed_at,mh.created_at) ELSE NULL END,
    mh.verification_note=CASE
        WHEN r.code='dentist' THEN COALESCE(mh.verification_note,'Migrated as a legacy dentist-created clinical history.')
        ELSE COALESCE(mh.verification_note,'Legacy entry retained for dentist review during the v0.5.9.5 integrity update.')
    END
WHERE mh.source_type='legacy_record'
  AND mh.verification_note IS NULL;

-- Previous versions automatically granted authority to every guardian.
-- v0.5.9.5 removes that implicit authority and requires Administrator review.
UPDATE `{{prefix}}patient_guardians`
SET authorized_to_consent=0,
    authorized_to_receive_records=0,
    verification_status='pending',
    verification_method=NULL,
    verification_note=COALESCE(verification_note,'Authority reset by v0.5.9.5; identity and scope must be verified.'),
    verified_by=NULL,
    verified_at=NULL,
    revoked_at=NULL,
    updated_at=NOW()
WHERE verification_status<>'verified'
   OR verified_by IS NULL
   OR verified_at IS NULL;
