-- TRAIL DPS 4.2.3 - Fil de Course Direction Certifie ++
-- Migration non destructive : certification des roles Organisation/Direction depuis PLACE.

ALTER TABLE tracking_devices
  ADD COLUMN course_feed_role VARCHAR(32) NULL,
  ADD COLUMN course_feed_role_source VARCHAR(32) NULL,
  ADD COLUMN organization_certified TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN certified_phone_cipher TEXT NULL,
  ADD COLUMN certified_phone_hash CHAR(64) NULL,
  ADD COLUMN certified_phone_last4 VARCHAR(8) NULL,
  ADD COLUMN certified_label VARCHAR(80) NULL,
  ADD COLUMN direction_2fa_required TINYINT(1) NOT NULL DEFAULT 0;

ALTER TABLE tracking_bindings
  ADD COLUMN course_feed_role VARCHAR(32) NULL,
  ADD COLUMN course_feed_role_source VARCHAR(32) NULL,
  ADD COLUMN organization_certified TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN certified_phone_cipher TEXT NULL,
  ADD COLUMN certified_phone_hash CHAR(64) NULL,
  ADD COLUMN certified_phone_last4 VARCHAR(8) NULL,
  ADD COLUMN certified_label VARCHAR(80) NULL,
  ADD COLUMN direction_2fa_required TINYINT(1) NOT NULL DEFAULT 0;

ALTER TABLE tracking_device_activation_tokens
  ADD COLUMN course_feed_role VARCHAR(32) NULL,
  ADD COLUMN course_feed_role_source VARCHAR(32) NULL,
  ADD COLUMN organization_certified TINYINT(1) NOT NULL DEFAULT 0,
  ADD COLUMN certified_phone_cipher TEXT NULL,
  ADD COLUMN certified_phone_hash CHAR(64) NULL,
  ADD COLUMN certified_phone_last4 VARCHAR(8) NULL,
  ADD COLUMN certified_label VARCHAR(80) NULL,
  ADD COLUMN direction_2fa_required TINYINT(1) NOT NULL DEFAULT 0;

CREATE INDEX idx_tracking_bindings_course_feed_role
  ON tracking_bindings(place_instance_id, event_remote_id, event_local_id, course_feed_role, organization_certified);

CREATE INDEX idx_tracking_bindings_certified_phone
  ON tracking_bindings(certified_phone_hash, course_feed_role, organization_certified, status);
