-- Sistema IoT y Realidad Aumentada
-- Database schema for IoT sensors and AR experiences

-- Dispositivos IoT en sitios turísticos
CREATE TABLE iot_devices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) UNIQUE NOT NULL,
    device_type ENUM('sensor_temperatura', 'sensor_humedad', 'contador_visitantes', 'camara_seguridad', 'beacon_bluetooth', 'estacion_meteorologica') NOT NULL,
    nombre VARCHAR(200) NOT NULL,
    ubicacion VARCHAR(500) NOT NULL,
    latitud DECIMAL(10, 8),
    longitud DECIMAL(11, 8),
    producto_id INT,
    activo BOOLEAN DEFAULT TRUE,
    configuracion JSON, -- Configuración específica del dispositivo
    ultima_comunicacion TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE SET NULL,
    INDEX idx_device_type (device_type),
    INDEX idx_ubicacion (latitud, longitud),
    INDEX idx_producto (producto_id)
);

-- Datos de sensores IoT
CREATE TABLE iot_sensor_data (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL,
    sensor_type VARCHAR(50) NOT NULL,
    valor DECIMAL(10, 4),
    unidad VARCHAR(20),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    metadatos JSON, -- Datos adicionales del sensor
    procesado BOOLEAN DEFAULT FALSE,
    INDEX idx_device_timestamp (device_id, timestamp),
    INDEX idx_sensor_type (sensor_type),
    INDEX idx_timestamp (timestamp),
    FOREIGN KEY (device_id) REFERENCES iot_devices(device_id) ON DELETE CASCADE
);

-- Alertas automáticas del sistema IoT
CREATE TABLE iot_alerts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100) NOT NULL,
    alert_type ENUM('temperatura_alta', 'humedad_extrema', 'capacidad_excedida', 'dispositivo_offline', 'mantenimiento_requerido', 'emergencia') NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL,
    message TEXT NOT NULL,
    valor_actual DECIMAL(10, 4),
    umbral_configurado DECIMAL(10, 4),
    estado ENUM('activa', 'resuelta', 'ignorada') DEFAULT 'activa',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    resolved_at TIMESTAMP NULL,
    resolved_by INT NULL,
    notas TEXT,
    FOREIGN KEY (device_id) REFERENCES iot_devices(device_id) ON DELETE CASCADE,
    FOREIGN KEY (resolved_by) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_estado (estado),
    INDEX idx_severity (severity),
    INDEX idx_created (created_at)
);

-- Experiencias de Realidad Aumentada
CREATE TABLE ar_experiences (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    producto_id INT,
    tipo_ar ENUM('marker_based', 'location_based', 'object_recognition', 'face_tracking') NOT NULL,
    contenido_3d_url VARCHAR(500), -- URL del modelo 3D
    audio_url VARCHAR(500), -- Narración o efectos de sonido
    video_url VARCHAR(500), -- Videos superpuestos
    imagenes JSON, -- Array de imágenes para la experiencia
    configuracion_ar JSON, -- Configuración técnica de AR
    latitud DECIMAL(10, 8),
    longitud DECIMAL(11, 8),
    radio_activacion DECIMAL(6, 2) DEFAULT 10.00, -- Radio en metros
    activo BOOLEAN DEFAULT TRUE,
    orden_experiencia INT DEFAULT 1,
    duracion_estimada INT, -- Duración en segundos
    requisitos_dispositivo JSON, -- Requisitos mínimos del dispositivo
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE CASCADE,
    INDEX idx_tipo_ar (tipo_ar),
    INDEX idx_ubicacion_ar (latitud, longitud),
    INDEX idx_producto_ar (producto_id)
);

-- Interacciones de usuarios con AR
CREATE TABLE ar_user_interactions (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    ar_experience_id INT NOT NULL,
    usuario_id INT NULL,
    session_id VARCHAR(100) NOT NULL,
    device_info JSON, -- Información del dispositivo
    latitud_usuario DECIMAL(10, 8),
    longitud_usuario DECIMAL(11, 8),
    tipo_interaccion ENUM('inicio', 'completado', 'abandonado', 'compartido', 'feedback') NOT NULL,
    duracion_segundos INT,
    calificacion TINYINT CHECK (calificacion BETWEEN 1 AND 5),
    feedback TEXT,
    screenshot_url VARCHAR(500),
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ar_experience_id) REFERENCES ar_experiences(id) ON DELETE CASCADE,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL,
    INDEX idx_experience_timestamp (ar_experience_id, timestamp),
    INDEX idx_usuario (usuario_id),
    INDEX idx_tipo_interaccion (tipo_interaccion)
);

-- Marcadores AR para reconocimiento
CREATE TABLE ar_markers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ar_experience_id INT NOT NULL,
    marker_type ENUM('qr_code', 'image_target', 'nft_marker', 'object_3d') NOT NULL,
    marker_data TEXT NOT NULL, -- Datos del marcador (imagen, código, etc.)
    marker_config JSON, -- Configuración específica del marcador
    nombre VARCHAR(200),
    descripcion TEXT,
    posicion_x DECIMAL(8, 4) DEFAULT 0,
    posicion_y DECIMAL(8, 4) DEFAULT 0,
    posicion_z DECIMAL(8, 4) DEFAULT 0,
    escala DECIMAL(4, 2) DEFAULT 1.0,
    rotacion JSON, -- Rotación en X, Y, Z
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (ar_experience_id) REFERENCES ar_experiences(id) ON DELETE CASCADE,
    INDEX idx_marker_type (marker_type),
    INDEX idx_experience_marker (ar_experience_id)
);

-- Contenido 3D para AR
CREATE TABLE ar_3d_content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    descripcion TEXT,
    archivo_3d_url VARCHAR(500) NOT NULL, -- URL del archivo .glb, .gltf, .obj
    texture_urls JSON, -- URLs de texturas
    animation_data JSON, -- Datos de animaciones
    tamaño_archivo INT, -- Tamaño en bytes
    tipo_archivo VARCHAR(20), -- glb, gltf, obj, fbx
    complejidad ENUM('baja', 'media', 'alta') DEFAULT 'media',
    requisitos_minimos JSON, -- RAM, GPU, etc.
    preview_image_url VARCHAR(500),
    categoria VARCHAR(100),
    tags JSON,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_categoria (categoria),
    INDEX idx_complejidad (complejidad),
    INDEX idx_tipo_archivo (tipo_archivo)
);

-- Relación entre experiencias AR y contenido 3D
CREATE TABLE ar_experience_content (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ar_experience_id INT NOT NULL,
    content_3d_id INT NOT NULL,
    posicion_x DECIMAL(8, 4) DEFAULT 0,
    posicion_y DECIMAL(8, 4) DEFAULT 0,
    posicion_z DECIMAL(8, 4) DEFAULT 0,
    escala DECIMAL(4, 2) DEFAULT 1.0,
    rotacion JSON,
    trigger_condition JSON, -- Condiciones para mostrar el contenido
    orden_aparicion INT DEFAULT 1,
    activo BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (ar_experience_id) REFERENCES ar_experiences(id) ON DELETE CASCADE,
    FOREIGN KEY (content_3d_id) REFERENCES ar_3d_content(id) ON DELETE CASCADE,
    UNIQUE KEY unique_experience_content (ar_experience_id, content_3d_id),
    INDEX idx_orden (orden_aparicion)
);

-- Métricas ambientales en tiempo real
CREATE TABLE environmental_metrics (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    producto_id INT,
    temperatura DECIMAL(4, 2), -- Celsius
    humedad DECIMAL(5, 2), -- Porcentaje
    presion_atmosferica DECIMAL(6, 2), -- hPa
    velocidad_viento DECIMAL(5, 2), -- km/h
    direccion_viento INT, -- Grados (0-360)
    precipitacion DECIMAL(5, 2), -- mm
    visibilidad DECIMAL(5, 2), -- km
    indice_uv DECIMAL(3, 1),
    calidad_aire JSON, -- PM2.5, PM10, CO2, etc.
    capacidad_actual INT, -- Visitantes actuales
    capacidad_maxima INT,
    estado_senderos JSON, -- Estado de cada sendero
    alertas_activas JSON,
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE CASCADE,
    INDEX idx_producto_timestamp (producto_id, timestamp),
    INDEX idx_timestamp (timestamp)
);

-- Configuración de umbrales para alertas
CREATE TABLE iot_alert_thresholds (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(100),
    producto_id INT,
    metric_name VARCHAR(100) NOT NULL,
    threshold_min DECIMAL(10, 4),
    threshold_max DECIMAL(10, 4),
    alert_type VARCHAR(100) NOT NULL,
    severity ENUM('low', 'medium', 'high', 'critical') NOT NULL,
    enabled BOOLEAN DEFAULT TRUE,
    notification_channels JSON, -- email, sms, push, webhook
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (device_id) REFERENCES iot_devices(device_id) ON DELETE CASCADE,
    FOREIGN KEY (producto_id) REFERENCES productos(id) ON DELETE CASCADE,
    INDEX idx_device_metric (device_id, metric_name),
    INDEX idx_enabled (enabled)
);

-- Reportes automatizados del sistema IoT
CREATE TABLE iot_reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(200) NOT NULL,
    tipo_reporte ENUM('diario', 'semanal', 'mensual', 'personalizado') NOT NULL,
    productos JSON, -- Array de product_ids
    devices JSON, -- Array de device_ids
    metricas JSON, -- Métricas a incluir
    formato_salida ENUM('pdf', 'excel', 'json', 'email') NOT NULL,
    destinatarios JSON, -- Emails de destinatarios
    configuracion JSON, -- Configuración específica del reporte
    ultimo_envio TIMESTAMP NULL,
    proximo_envio TIMESTAMP NULL,
    activo BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_tipo_reporte (tipo_reporte),
    INDEX idx_proximo_envio (proximo_envio),
    INDEX idx_activo (activo)
);

-- Historial de reportes generados
CREATE TABLE iot_report_history (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    report_id INT NOT NULL,
    periodo_inicio DATE NOT NULL,
    periodo_fin DATE NOT NULL,
    archivo_url VARCHAR(500),
    tamaño_archivo INT,
    estado ENUM('generando', 'completado', 'error') DEFAULT 'generando',
    error_message TEXT,
    estadisticas JSON, -- Estadísticas del reporte
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (report_id) REFERENCES iot_reports(id) ON DELETE CASCADE,
    INDEX idx_report_periodo (report_id, periodo_inicio, periodo_fin),
    INDEX idx_estado (estado)
);

-- Insertar dispositivos IoT demo para sitios arqueológicos mayas
INSERT INTO iot_devices (device_id, device_type, nombre, ubicacion, latitud, longitud, producto_id, configuracion) VALUES
('TEMP_TIKAL_001', 'sensor_temperatura', 'Sensor Temperatura Tikal Plaza Mayor', 'Plaza Mayor, Tikal', 17.2221, -89.6233, 1, '{"intervalo_lectura": 300, "umbral_alerta": 35}'),
('HUMID_TIKAL_001', 'sensor_humedad', 'Sensor Humedad Tikal Plaza Mayor', 'Plaza Mayor, Tikal', 17.2221, -89.6233, 1, '{"intervalo_lectura": 300, "umbral_min": 40, "umbral_max": 85}'),
('COUNT_TIKAL_001', 'contador_visitantes', 'Contador Visitantes Templo IV', 'Templo IV, Tikal', 17.2284, -89.6275, 1, '{"capacidad_maxima": 50, "reset_diario": true}'),
('BEACON_YAXHA_001', 'beacon_bluetooth', 'Beacon Bluetooth Yaxha Entrada', 'Entrada Principal, Yaxha', 17.0567, -89.3942, 2, '{"intervalo_beacon": 1000, "potencia_tx": -12}'),
('WEATHER_SEMUC_001', 'estacion_meteorologica', 'Estación Meteorológica Semuc Champey', 'Mirador Principal, Semuc Champey', 15.5400, -89.9800, 3, '{"sensores": ["temperatura", "humedad", "viento", "precipitacion"]}'),
('CAM_CARACOL_001', 'camara_seguridad', 'Cámara Seguridad Caracol', 'Plaza Principal, Caracol', 16.7611, -89.1167, 4, '{"resolucion": "1080p", "vision_nocturna": true, "deteccion_movimiento": true}');

-- Insertar experiencias AR para sitios arqueológicos
INSERT INTO ar_experiences (nombre, descripcion, producto_id, tipo_ar, contenido_3d_url, audio_url, latitud, longitud, radio_activacion, duracion_estimada, configuracion_ar) VALUES
('Reconstrucción Virtual Tikal', 'Experiencia AR que muestra cómo lucía Tikal en su época de esplendor', 1, 'location_based', '/assets/ar/tikal_reconstruction.glb', '/assets/audio/tikal_narration_es.mp3', 17.2221, -89.6233, 50.0, 300, '{"min_android": 7.0, "min_ios": 11.0, "arcore_required": true}'),
('Astronomía Maya en Yaxha', 'Visualización AR de las alineaciones astronómicas mayas', 2, 'location_based', '/assets/ar/maya_astronomy.glb', '/assets/audio/astronomy_narration.mp3', 17.0567, -89.3942, 30.0, 240, '{"tracking_type": "world", "light_estimation": true}'),
('Fauna Prehistórica Semuc Champey', 'Animales que habitaban la región hace milenios', 3, 'marker_based', '/assets/ar/prehistoric_animals.glb', '/assets/audio/nature_sounds.mp3', 15.5400, -89.9800, 20.0, 180, '{"marker_detection": "natural_features"}'),
('Jeroglíficos Interactivos Caracol', 'Traducción en tiempo real de jeroglíficos mayas', 4, 'object_recognition', '/assets/ar/hieroglyphs.glb', '/assets/audio/maya_language.mp3', 16.7611, -89.1167, 15.0, 420, '{"object_tracking": "extended", "occlusion": true}');

-- Insertar marcadores AR
INSERT INTO ar_markers (ar_experience_id, marker_type, marker_data, nombre, descripcion, marker_config) VALUES
(1, 'image_target', '/assets/markers/tikal_stela.jpg', 'Estela Maya Tikal', 'Marcador basado en estela maya original', '{"tracking_rating": 5, "scale": 1.0}'),
(2, 'qr_code', 'QR_YAXHA_ASTRO_001', 'QR Astronomía Yaxha', 'Código QR para activar experiencia astronómica', '{"error_correction": "M"}'),
(3, 'nft_marker', '/assets/markers/semuc_natural.jpg', 'Marcador Natural Semuc', 'Características naturales de las pozas', '{"tracking_type": "natural_features"}'),
(4, 'image_target', '/assets/markers/caracol_hieroglyph.jpg', 'Jeroglífico Caracol', 'Panel de jeroglíficos como marcador', '{"tracking_rating": 4, "augmentation_3d": true}');

-- Insertar contenido 3D
INSERT INTO ar_3d_content (nombre, descripcion, archivo_3d_url, tipo_archivo, complejidad, categoria, tags, preview_image_url) VALUES
('Templo Maya Completo', 'Reconstrucción 3D completa de templo maya', '/assets/3d/maya_temple_complete.glb', 'glb', 'alta', 'arqueologia', '["maya", "templo", "reconstruccion"]', '/assets/previews/maya_temple.jpg'),
('Calendario Maya 3D', 'Modelo interactivo del calendario maya', '/assets/3d/maya_calendar.glb', 'glb', 'media', 'astronomia', '["calendario", "astronomia", "maya"]', '/assets/previews/maya_calendar.jpg'),
('Jaguar Maya Animado', 'Jaguar con animaciones tradicionales mayas', '/assets/3d/jaguar_animated.glb', 'glb', 'media', 'fauna', '["jaguar", "animacion", "fauna"]', '/assets/previews/jaguar.jpg'),
('Escriba Maya', 'Personaje 3D de escriba maya con animaciones', '/assets/3d/maya_scribe.glb', 'glb', 'alta', 'personajes', '["escriba", "personaje", "maya"]', '/assets/previews/scribe.jpg');

-- Insertar relaciones experiencia-contenido
INSERT INTO ar_experience_content (ar_experience_id, content_3d_id, posicion_x, posicion_y, posicion_z, escala) VALUES
(1, 1, 0, 0, 0, 1.0),
(2, 2, 0, 1.5, 0, 0.8),
(3, 3, 2.0, 0, 0, 1.2),
(4, 4, -1.0, 0, 1.0, 0.9);

-- Insertar umbrales de alertas IoT
INSERT INTO iot_alert_thresholds (device_id, metric_name, threshold_min, threshold_max, alert_type, severity, notification_channels) VALUES
('TEMP_TIKAL_001', 'temperatura', -5, 40, 'temperatura_extrema', 'high', '["email", "push"]'),
('HUMID_TIKAL_001', 'humedad', 30, 90, 'humedad_extrema', 'medium', '["email"]'),
('COUNT_TIKAL_001', 'visitantes', NULL, 50, 'capacidad_excedida', 'high', '["email", "sms", "push"]'),
('WEATHER_SEMUC_001', 'viento', NULL, 60, 'viento_fuerte', 'high', '["email", "sms"]'),
('WEATHER_SEMUC_001', 'precipitacion', NULL, 50, 'lluvia_intensa', 'medium', '["push"]');

-- Insertar reportes automatizados
INSERT INTO iot_reports (nombre, tipo_reporte, productos, devices, metricas, formato_salida, destinatarios, configuracion) VALUES
('Reporte Diario Condiciones Tikal', 'diario', '[1]', '["TEMP_TIKAL_001", "HUMID_TIKAL_001", "COUNT_TIKAL_001"]', '["temperatura", "humedad", "visitantes"]', 'pdf', '["admin@mayanworldtravelagency.com", "operaciones@mayanworldtravelagency.com"]', '{"hora_envio": "08:00", "incluir_graficos": true}'),
('Reporte Semanal AR Analytics', 'semanal', '[1,2,3,4]', '[]', '["ar_interactions", "ar_ratings", "ar_completion"]', 'excel', '["marketing@mayanworldtravelagency.com"]', '{"dia_envio": "lunes", "incluir_comparativas": true}'),
('Reporte Mensual Capacidad', 'mensual', '[1,2,3,4,5]', '["COUNT_TIKAL_001", "BEACON_YAXHA_001"]', '["capacidad_promedio", "picos_visitantes", "tendencias"]', 'pdf', '["gerencia@mayanworldtravelagency.com"]', '{"dia_envio": 1, "incluir_proyecciones": true}');