-- Base de datos de países europeos, provincias y ciudades
-- Para uso en la plataforma de scraping

CREATE DATABASE IF NOT EXISTS trafikhub_web CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

USE trafikhub_web;

-- Tabla de países europeos
CREATE TABLE IF NOT EXISTS paises_europa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    codigo_iso VARCHAR(2) NOT NULL UNIQUE COMMENT 'Código ISO 3166-1 alpha-2',
    nombre VARCHAR(100) NOT NULL,
    nombre_en VARCHAR(100) NOT NULL,
    gentilicio VARCHAR(100),
    capital VARCHAR(100),
    idioma_principal VARCHAR(50) DEFAULT 'español',
    moneda VARCHAR(50),
    poblacion INT,
    superficie_km2 DECIMAL(10,2),
    zona_horaria VARCHAR(50) DEFAULT 'Europe/Madrid',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_codigo_iso (codigo_iso),
    INDEX idx_nombre (nombre)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de provincias/regiones
CREATE TABLE IF NOT EXISTS provincias_europa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    pais_id INT NOT NULL,
    codigo_provincia VARCHAR(10) COMMENT 'Código de provincia/región',
    nombre VARCHAR(100) NOT NULL,
    nombre_en VARCHAR(100),
    capital VARCHAR(100),
    poblacion INT,
    superficie_km2 DECIMAL(10,2),
    tipo ENUM('provincia', 'región', 'comunidad', 'departamento', 'condado', 'land', 'estado', 'cantón', 'distrito', 'comitado', 'voivodato', 'periferia', 'municipio', 'ciudad autónoma', 'capital') DEFAULT 'provincia',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (pais_id) REFERENCES paises_europa(id) ON DELETE CASCADE,
    INDEX idx_pais_id (pais_id),
    INDEX idx_nombre (nombre),
    INDEX idx_tipo (tipo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de ciudades
CREATE TABLE IF NOT EXISTS ciudades_europa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    provincia_id INT NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    nombre_alternativo VARCHAR(100),
    poblacion INT,
    codigo_postal_prefijo VARCHAR(20),
    es_capital BOOLEAN DEFAULT FALSE,
    coordenadas_lat DECIMAL(10,8),
    coordenadas_lng DECIMAL(11,8),
    codigo_ine VARCHAR(20) COMMENT 'Código INE o equivalente',
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    fecha_actualizacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (provincia_id) REFERENCES provincias_europa(id) ON DELETE CASCADE,
    INDEX idx_provincia_id (provincia_id),
    INDEX idx_nombre (nombre),
    INDEX idx_poblacion (poblacion DESC),
    INDEX idx_es_capital (es_capital)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Vista útil: Ciudades con su provincia y país
CREATE OR REPLACE VIEW v_ciudades_completas AS
SELECT 
    c.id as ciudad_id,
    c.nombre as ciudad_nombre,
    c.poblacion as ciudad_poblacion,
    c.es_capital as ciudad_capital,
    c.coordenadas_lat,
    c.coordenadas_lng,
    p.id as provincia_id,
    p.nombre as provincia_nombre,
    p.tipo as provincia_tipo,
    p.poblacion as provincia_poblacion,
    pa.id as pais_id,
    pa.codigo_iso as pais_codigo,
    pa.nombre as pais_nombre,
    pa.nombre_en as pais_nombre_en,
    pa.gentilicio as pais_gentilicio,
    pa.capital as pais_capital
FROM ciudades_europa c
JOIN provincias_europa p ON c.provincia_id = p.id
JOIN paises_europa pa ON p.pais_id = pa.id;

