-- TRAIL DPS V4.2 - LOT 6 - Activation mobile par token temporaire
-- Migration non destructive.
-- Objectif : permettre a PLACE de generer un token d'activation temporaire, scannable par QR code ou cliquable par SMS.

CREATE TABLE IF NOT EXISTS tracking_device_activation_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    activation_token_hash CHAR(64) NOT NULL UNIQUE,
    device_id BIGINT UNSIGNED NOT NULL,
    binding_id BIGINT UNSIGNED NOT NULL,
    server_url VARCHAR(255) NOT NULL,
    place_instance_id VARCHAR(128) NOT NULL,
    event_remote_id VARCHAR(128) NOT NULL,
    event_local_id BIGINT UNSIGNED NOT NULL,
    run_id BIGINT UNSIGNED NULL,
    actor_type ENUM('RESCUE','SECURITY','COURSE_STAFF','UNKNOWN') NOT NULL DEFAULT 'UNKNOWN',
    local_team_id BIGINT UNSIGNED NULL,
    local_staff_team_id BIGINT UNSIGNED NULL,
    event_name VARCHAR(255) NOT NULL DEFAULT '',
    actor_label VARCHAR(255) NOT NULL DEFAULT '',
    device_uid VARCHAR(128) NOT NULL,
    device_label VARCHAR(255) NOT NULL DEFAULT '',
    encrypted_device_token TEXT NOT NULL,
    status ENUM('pending','used','expired','revoked') NOT NULL DEFAULT 'pending',
    expires_at DATETIME NOT NULL,
    used_at DATETIME NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tracking_activation_status_expires (status, expires_at),
    INDEX idx_tracking_activation_scope (place_instance_id, event_remote_id, event_local_id, run_id),
    INDEX idx_tracking_activation_device (device_id),
    CONSTRAINT fk_tracking_activation_device FOREIGN KEY (device_id) REFERENCES tracking_devices(id) ON DELETE CASCADE,
    CONSTRAINT fk_tracking_activation_binding FOREIGN KEY (binding_id) REFERENCES tracking_bindings(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Indexes event_remote_id recommandes a ajouter une seule fois si absents :
-- ALTER TABLE tracking_bindings ADD INDEX idx_tracking_bindings_remote_scope (place_instance_id, event_remote_id, event_local_id, run_id);
-- ALTER TABLE tracking_positions ADD INDEX idx_tracking_positions_remote_scope_time (place_instance_id, event_remote_id, event_local_id, run_id, recorded_at_server);
