-- TRAIL DPS 4.2.3.4 - Index Direction / Organisation certifiee
-- Compatible MySQL/MariaDB OVH sans CREATE INDEX IF NOT EXISTS.
-- Ces index accelerent les controles d'unicite et la validation 2FA Direction.

DELIMITER $$

DROP PROCEDURE IF EXISTS traildps_create_index_if_missing$$
CREATE PROCEDURE traildps_create_index_if_missing(IN p_table VARCHAR(128), IN p_index VARCHAR(128), IN p_columns TEXT)
BEGIN
  IF NOT EXISTS (
    SELECT 1
    FROM information_schema.STATISTICS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = p_table
      AND INDEX_NAME = p_index
  ) THEN
    SET @ddl = CONCAT('CREATE INDEX ', p_index, ' ON ', p_table, ' ', p_columns);
    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
  END IF;
END$$

DELIMITER ;

CALL traildps_create_index_if_missing(
  'tracking_bindings',
  'idx_bindings_direction_phone_scope',
  '(place_instance_id, event_remote_id, event_local_id, course_feed_role, organization_certified, certified_phone_hash, status)'
);

CALL traildps_create_index_if_missing(
  'tracking_device_activation_tokens',
  'idx_tokens_direction_phone_scope',
  '(place_instance_id, event_remote_id, event_local_id, course_feed_role, organization_certified, certified_phone_hash, status)'
);

DROP PROCEDURE IF EXISTS traildps_create_index_if_missing;
