CREATE DATABASE IF NOT EXISTS `mapa` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `mapa`;

-- Tabla: mensajes
DROP TABLE IF EXISTS `mensajes`;
CREATE TABLE `mensajes` (
  `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
  `texto` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `id_nodo` varchar(9) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `id_recep` varchar(9) CHARACTER SET ascii COLLATE ascii_general_ci NOT NULL,
  `canal` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `fecha` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_id_nodo` (`id_nodo`),
  KEY `idx_id_recep` (`id_recep`),
  KEY `idx_canal` (`canal`),
  KEY `idx_fecha` (`fecha`),
  KEY `idx_id_nodo_fecha` (`id_nodo`, `fecha`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: posiciones
DROP TABLE IF EXISTS `posiciones`;
CREATE TABLE `posiciones` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_nodo` varchar(9) COLLATE utf8mb4_unicode_ci NOT NULL,
  `nombre_corto` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `canal` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `latitud` decimal(10,7) NOT NULL,
  `longitud` decimal(10,7) NOT NULL,
  `altitud` smallint DEFAULT '0',
  `fecha_gps` timestamp NULL DEFAULT NULL,
  `fecha_recepcion` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `satelites` tinyint UNSIGNED DEFAULT '0',
  `velocidad` smallint DEFAULT '0',
  `estado` tinyint UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `idx_id_nodo` (`id_nodo`),
  KEY `idx_fecha_gps` (`fecha_gps`),
  KEY `idx_fecha_recepcion` (`fecha_recepcion`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla: telemetria
DROP TABLE IF EXISTS `telemetria`;
CREATE TABLE `telemetria` (
  `id` bigint UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_nodo` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bateria` decimal(5,2) DEFAULT NULL,
  `voltaje` decimal(5,2) DEFAULT NULL,
  `uso_canal` decimal(5,2) DEFAULT NULL,
  `transmision` decimal(5,2) DEFAULT NULL,
  `temperatura` decimal(5,2) DEFAULT NULL,
  `humedad` decimal(5,2) DEFAULT NULL,
  `presion` decimal(6,2) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_id_nodo` (`id_nodo`),
  KEY `idx_id_nodo` (`id_nodo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Tabla: usuarios
DROP TABLE IF EXISTS `usuarios`;
CREATE TABLE `usuarios` (
  `id_nodo` varchar(9) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `nombre` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `shortName` varchar(4) COLLATE utf8mb4_unicode_ci NOT NULL,
  `mac` varchar(17) COLLATE utf8mb4_unicode_ci NOT NULL,
  `modelo` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `rol` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `firmware` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id_nodo`),
  UNIQUE KEY `id_nodo_UNIQUE` (`id_nodo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;