-- TRAIL DPS 4.2.3.1 - Correctif Direction certifiee Fil de Course
-- A appliquer avec un utilisateur SQL disposant des droits ALTER/CREATE.
-- Le compte applicatif OVH ne doit plus tenter de creer/modifier le schema en runtime.

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

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

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

CREATE TABLE IF NOT EXISTS course_feed_direction_auth (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  channel_id BIGINT UNSIGNED NOT NULL,
  phone_hash CHAR(64) NOT NULL,
  code_hash VARCHAR(255) NOT NULL,
  status VARCHAR(16) NOT NULL DEFAULT 'PENDING',
  attempts INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  expires_at DATETIME NOT NULL,
  used_at DATETIME NULL,
  INDEX idx_direction_auth_lookup (channel_id, phone_hash, status, expires_at),
  INDEX idx_direction_auth_expiry (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE INDEX IF NOT EXISTS 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 IF NOT EXISTS idx_tracking_bindings_certified_phone
  ON tracking_bindings(certified_phone_hash, course_feed_role, organization_certified, status);
