-- TRAIL DPS 4.2 - Partage sécurisé position coureur vers équipe RESCUE
-- Migration MySQL/MariaDB non destructive.
-- Prérequis : sauvegarde complète de la base synoluidpstrack avant exécution.

CREATE TABLE IF NOT EXISTS rescue_location_share_links (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  place_instance_id VARCHAR(128) NOT NULL,
  event_remote_id VARCHAR(128) NOT NULL,
  event_local_id BIGINT UNSIGNED NULL,
  run_id BIGINT UNSIGNED NULL,
  runner_request_id BIGINT UNSIGNED NOT NULL,
  incident_remote_id BIGINT UNSIGNED NULL,
  local_incident_id BIGINT UNSIGNED NULL,
  rescue_binding_id BIGINT UNSIGNED NULL,
  rescue_device_id BIGINT UNSIGNED NULL,
  rescue_team_label VARCHAR(255) NULL,
  phone VARCHAR(32) NOT NULL,
  token_hash CHAR(64) NOT NULL,
  status ENUM('CREATED','SMS_SENT','SMS_ERROR','OPENED','EXPIRED','CANCELLED','ERROR') NOT NULL DEFAULT 'CREATED',
  expires_at DATETIME NOT NULL,
  opened_at DATETIME NULL,
  last_opened_at DATETIME NULL,
  open_count INT NOT NULL DEFAULT 0,
  latitude DECIMAL(10,7) NOT NULL,
  longitude DECIMAL(10,7) NOT NULL,
  accuracy DECIMAL(8,2) NULL,
  recorded_at_device DATETIME NULL,
  recorded_at_server DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uniq_rescue_share_token (token_hash),
  KEY idx_rescue_share_event (event_remote_id, place_instance_id),
  KEY idx_rescue_share_request (runner_request_id),
  KEY idx_rescue_share_expires (expires_at),
  KEY idx_rescue_share_status (status),
  KEY idx_rescue_share_phone (phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET @db_name := DATABASE();

SET @sql := IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sms_logs' AND COLUMN_NAME = 'scope') = 0,
  'ALTER TABLE sms_logs ADD COLUMN scope VARCHAR(64) NULL AFTER error_message',
  'SELECT "sms_logs.scope already exists" AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sms_logs' AND COLUMN_NAME = 'related_share_id') = 0,
  'ALTER TABLE sms_logs ADD COLUMN related_share_id BIGINT UNSIGNED NULL AFTER scope',
  'SELECT "sms_logs.related_share_id already exists" AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @sql := IF(
  (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = @db_name AND TABLE_NAME = 'sms_logs' AND INDEX_NAME = 'idx_sms_logs_scope_share') = 0,
  'CREATE INDEX idx_sms_logs_scope_share ON sms_logs(scope, related_share_id)',
  'SELECT "idx_sms_logs_scope_share already exists" AS info'
);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
