-- Base de datos para tracking GPS
CREATE DATABASE IF NOT EXISTS navitrack_db;
USE navitrack_db;

-- Tabla para almacenar las ubicaciones de tracking
CREATE TABLE IF NOT EXISTS tracking_locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    lat DECIMAL(10, 8) NOT NULL,
    lng DECIMAL(11, 8) NOT NULL,
    timestamp BIGINT NOT NULL,
    duration_seconds INT DEFAULT 1,
    iterations INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_coordinates (lat, lng),
    INDEX idx_timestamp (timestamp)
);

-- Tabla para sesiones de tracking (opcional, para agrupar rutas)
CREATE TABLE IF NOT EXISTS tracking_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    session_id VARCHAR(50) UNIQUE NOT NULL,
    start_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP NULL,
    total_points INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);