-- TRAIL DPS V4.2 - LOT 2 - Initial WEB tracking schema
-- Non destructive: creates only new tracking tables. Existing license tables are not modified.

CREATE TABLE IF NOT EXISTS tracking_devices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_uid VARCHAR(128) NOT NULL UNIQUE,
    device_label VARCHAR(255) NOT NULL DEFAULT '',
    actor_type ENUM('RESCUE','SECURITY','COURSE_STAFF','UNKNOWN') NOT NULL DEFAULT 'UNKNOWN',
    token_hash CHAR(64) NULL,
    status ENUM('active','disabled','revoked') NOT NULL DEFAULT 'active',
    last_seen_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_devices_actor_type (actor_type),
    INDEX idx_tracking_devices_status (status),
    INDEX idx_tracking_devices_last_seen_at (last_seen_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tracking_bindings (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    place_instance_id VARCHAR(128) NOT NULL,
    event_remote_id VARCHAR(128) NULL,
    event_local_id BIGINT UNSIGNED NOT NULL,
    run_id BIGINT UNSIGNED NULL,
    device_id BIGINT UNSIGNED NOT 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,
    valid_from DATETIME NULL,
    valid_to DATETIME NULL,
    status ENUM('active','inactive','revoked') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tracking_bindings_scope (place_instance_id, event_local_id, run_id),
    INDEX idx_tracking_bindings_device (device_id),
    INDEX idx_tracking_bindings_actor (actor_type),
    INDEX idx_tracking_bindings_status (status),
    CONSTRAINT fk_tracking_bindings_device FOREIGN KEY (device_id) REFERENCES tracking_devices(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tracking_positions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    device_id BIGINT UNSIGNED NOT NULL,
    binding_id BIGINT UNSIGNED NULL,
    place_instance_id VARCHAR(128) NOT NULL,
    event_remote_id VARCHAR(128) 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',
    latitude DECIMAL(10,7) NOT NULL,
    longitude DECIMAL(10,7) NOT NULL,
    accuracy DECIMAL(8,2) NULL,
    speed DECIMAL(8,2) NULL,
    heading DECIMAL(6,2) NULL,
    battery TINYINT UNSIGNED NULL,
    recorded_at_device DATETIME NULL,
    recorded_at_server DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    source VARCHAR(32) NOT NULL DEFAULT 'api',
    raw_payload_hash CHAR(64) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tracking_positions_scope_time (place_instance_id, event_local_id, run_id, recorded_at_server),
    INDEX idx_tracking_positions_device_time (device_id, recorded_at_server),
    INDEX idx_tracking_positions_actor_time (actor_type, recorded_at_server),
    UNIQUE KEY uq_tracking_positions_payload (raw_payload_hash),
    CONSTRAINT fk_tracking_positions_device FOREIGN KEY (device_id) REFERENCES tracking_devices(id) ON DELETE CASCADE,
    CONSTRAINT fk_tracking_positions_binding FOREIGN KEY (binding_id) REFERENCES tracking_bindings(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS runner_location_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    place_instance_id VARCHAR(128) NOT NULL,
    event_remote_id VARCHAR(128) NULL,
    event_local_id BIGINT UNSIGNED NOT NULL,
    run_id BIGINT UNSIGNED NULL,
    event_name VARCHAR(255) NOT NULL,
    phone VARCHAR(32) NOT NULL,
    token_hash CHAR(64) NOT NULL UNIQUE,
    status ENUM('REQUEST_CREATED','SMS_SENT','SMS_ERROR','OPENED','LOCATION_RECEIVED','EXPIRED','CANCELLED','INCIDENT_CREATED','ERROR') NOT NULL DEFAULT 'REQUEST_CREATED',
    expires_at DATETIME NOT NULL,
    opened_at DATETIME NULL,
    location_received_at DATETIME NULL,
    incident_created_at DATETIME NULL,
    latitude DECIMAL(10,7) NULL,
    longitude DECIMAL(10,7) NULL,
    accuracy DECIMAL(8,2) NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_runner_location_scope (place_instance_id, event_local_id, run_id),
    INDEX idx_runner_location_status (status),
    INDEX idx_runner_location_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS sms_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    request_id BIGINT UNSIGNED NULL,
    provider VARCHAR(64) NOT NULL,
    phone VARCHAR(32) NOT NULL,
    status VARCHAR(64) NOT NULL,
    provider_message_id VARCHAR(128) NULL,
    error_message TEXT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sms_logs_request (request_id),
    INDEX idx_sms_logs_status (status),
    CONSTRAINT fk_sms_logs_request FOREIGN KEY (request_id) REFERENCES runner_location_requests(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tracking_api_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scope VARCHAR(64) NOT NULL,
    level ENUM('debug','info','warning','error','critical') NOT NULL DEFAULT 'info',
    message VARCHAR(512) NOT NULL,
    context_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tracking_api_logs_scope_time (scope, created_at),
    INDEX idx_tracking_api_logs_level_time (level, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tracking_error_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    scope VARCHAR(64) NOT NULL,
    error_code VARCHAR(128) NULL,
    message TEXT NOT NULL,
    context_json JSON NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tracking_error_logs_scope_time (scope, created_at),
    INDEX idx_tracking_error_logs_code (error_code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
