-- Esquema para puntos de interés (POIs) de ciudades europeas
-- Estos POIs se obtienen consultando ChatGPT para cada ciudad

USE trafikhub_web;

-- Tabla de puntos de interés por ciudad
CREATE TABLE IF NOT EXISTS pois_ciudades_europa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ciudad_id INT NOT NULL,
    nombre VARCHAR(255) NOT NULL,
    tipo VARCHAR(100) NOT NULL COMMENT 'tipo de POI: monumento, museo, parque, restaurante, etc.',
    descripcion TEXT COMMENT 'descripción del POI',
    categoria VARCHAR(100) COMMENT 'categoría general: turismo, cultura, gastronomía, naturaleza, etc.',
    coordenadas_lat DECIMAL(10, 8) NOT NULL,
    coordenadas_lng DECIMAL(11, 8) NOT NULL,
    direccion VARCHAR(500),
    importancia VARCHAR(50) DEFAULT 'media' COMMENT 'alta, media, baja',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (ciudad_id) REFERENCES ciudades_europa(id) ON DELETE CASCADE,
    INDEX idx_ciudad_id (ciudad_id),
    INDEX idx_tipo (tipo),
    INDEX idx_categoria (categoria),
    INDEX idx_importancia (importancia),
    INDEX idx_coordenadas (coordenadas_lat, coordenadas_lng)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla para controlar qué ciudades ya han sido procesadas
CREATE TABLE IF NOT EXISTS ciudades_pois_procesadas (
    ciudad_id INT NOT NULL,
    fecha_procesamiento TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    total_pois INT DEFAULT 0,
    estado ENUM('pendiente', 'procesando', 'completado', 'error') DEFAULT 'pendiente',
    mensaje_error TEXT,
    PRIMARY KEY (ciudad_id),
    FOREIGN KEY (ciudad_id) REFERENCES ciudades_europa(id) ON DELETE CASCADE,
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla para controlar qué ciudades ya han sido procesadas para restaurantes
CREATE TABLE IF NOT EXISTS ciudades_restaurantes_procesadas (
    ciudad_id INT NOT NULL,
    fecha_procesamiento TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    total_restaurantes INT DEFAULT 0,
    estado ENUM('pendiente', 'procesando', 'completado', 'error') DEFAULT 'pendiente',
    mensaje_error TEXT,
    PRIMARY KEY (ciudad_id),
    FOREIGN KEY (ciudad_id) REFERENCES ciudades_europa(id) ON DELETE CASCADE,
    INDEX idx_estado (estado)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vista útil: POIs con información completa de ciudad, provincia y país
CREATE OR REPLACE VIEW v_pois_completos AS
SELECT 
    poi.id as poi_id,
    poi.nombre as poi_nombre,
    poi.tipo as poi_tipo,
    poi.descripcion as poi_descripcion,
    poi.categoria as poi_categoria,
    poi.coordenadas_lat as poi_lat,
    poi.coordenadas_lng as poi_lng,
    poi.direccion as poi_direccion,
    poi.importancia as poi_importancia,
    c.id as ciudad_id,
    c.nombre as ciudad_nombre,
    c.poblacion as ciudad_poblacion,
    p.id as provincia_id,
    p.nombre as provincia_nombre,
    p.tipo as provincia_tipo,
    pa.id as pais_id,
    pa.codigo_iso as pais_codigo,
    pa.nombre as pais_nombre
FROM pois_ciudades_europa poi
JOIN ciudades_europa c ON poi.ciudad_id = c.id
JOIN provincias_europa p ON c.provincia_id = p.id
JOIN paises_europa pa ON p.pais_id = pa.id;

