-- DentalCare v0.9.1.1
-- Canonical current dental-safety records, preserved change timeline, and
-- role-scoped print activity, and cross-session completed-update visibility. The application updater performs the same work
-- through setup_apply_v0911() so it can remain repeat-safe across supported
-- MySQL/MariaDB shared-hosting versions.

ALTER TABLE `{{prefix}}patient_allergies`
  ADD COLUMN IF NOT EXISTS `canonical_key` CHAR(64) NULL AFTER `normalized_substance`;
ALTER TABLE `{{prefix}}patient_medications`
  ADD COLUMN IF NOT EXISTS `canonical_key` CHAR(64) NULL AFTER `normalized_medication`;
ALTER TABLE `{{prefix}}patient_conditions`
  ADD COLUMN IF NOT EXISTS `canonical_key` CHAR(64) NULL AFTER `normalized_condition`;

-- Historical duplicates are not deleted. Older duplicate current rows are
-- converted to superseded history; the newest row remains current.
UPDATE `{{prefix}}patient_allergies` older
JOIN `{{prefix}}patient_allergies` newer
  ON newer.patient_id=older.patient_id
 AND newer.normalized_substance=older.normalized_substance
 AND newer.is_current=1 AND newer.status='active' AND newer.id>older.id
SET older.is_current=0,older.status='superseded',older.canonical_key=NULL,older.updated_at=NOW()
WHERE older.is_current=1 AND older.status='active';

UPDATE `{{prefix}}patient_medications` older
JOIN `{{prefix}}patient_medications` newer
  ON newer.patient_id=older.patient_id
 AND newer.normalized_medication=older.normalized_medication
 AND newer.is_current=1 AND newer.status='active' AND newer.id>older.id
SET older.is_current=0,older.status='superseded',older.canonical_key=NULL,older.updated_at=NOW()
WHERE older.is_current=1 AND older.status='active';

UPDATE `{{prefix}}patient_conditions` older
JOIN `{{prefix}}patient_conditions` newer
  ON newer.patient_id=older.patient_id
 AND newer.normalized_condition=older.normalized_condition
 AND newer.is_current=1 AND newer.status='active' AND newer.id>older.id
SET older.is_current=0,older.status='superseded',older.canonical_key=NULL,older.updated_at=NOW()
WHERE older.is_current=1 AND older.status='active';

UPDATE `{{prefix}}patient_allergies`
SET canonical_key=CASE WHEN is_current=1 AND status='active' AND TRIM(normalized_substance)<>''
  THEN SHA2(CONCAT(patient_id,'|',normalized_substance),256) ELSE NULL END;
UPDATE `{{prefix}}patient_medications`
SET canonical_key=CASE WHEN is_current=1 AND status='active' AND TRIM(normalized_medication)<>''
  THEN SHA2(CONCAT(patient_id,'|',normalized_medication),256) ELSE NULL END;
UPDATE `{{prefix}}patient_conditions`
SET canonical_key=CASE WHEN is_current=1 AND status='active' AND TRIM(normalized_condition)<>''
  THEN SHA2(CONCAT(patient_id,'|',normalized_condition),256) ELSE NULL END;

ALTER TABLE `{{prefix}}patient_allergies`
  ADD UNIQUE INDEX IF NOT EXISTS `uq_patient_allergy_current_key` (`canonical_key`);
ALTER TABLE `{{prefix}}patient_medications`
  ADD UNIQUE INDEX IF NOT EXISTS `uq_patient_med_current_key` (`canonical_key`);
ALTER TABLE `{{prefix}}patient_conditions`
  ADD UNIQUE INDEX IF NOT EXISTS `uq_patient_condition_current_key` (`canonical_key`);
ALTER TABLE `{{prefix}}print_logs`
  ADD INDEX IF NOT EXISTS `idx_print_user_time` (`printed_by`,`printed_at`),
  ADD INDEX IF NOT EXISTS `idx_print_branch_time` (`branch_id`,`printed_at`);

-- Exact duplicate active safety alerts are also retained as resolved history.
UPDATE `{{prefix}}medical_alerts` older
JOIN `{{prefix}}medical_alerts` newer
  ON newer.patient_id=older.patient_id
 AND LOWER(TRIM(newer.alert_type))=LOWER(TRIM(older.alert_type))
 AND LOWER(TRIM(newer.description))=LOWER(TRIM(older.description))
 AND newer.is_active=1 AND newer.id>older.id
SET older.is_active=0,older.resolved_at=COALESCE(older.resolved_at,NOW())
WHERE older.is_active=1;
