INSERT INTO paises (nombre, codigo_iso2, codigo_iso3, created_at)
VALUES
('Colombia', 'CO', 'COL', NOW()),
('Mexico', 'MX', 'MEX', NOW()),
('Peru', 'PE', 'PER', NOW()),
('Ecuador', 'EC', 'ECU', NOW()),
('Chile', 'CL', 'CHL', NOW()),
('Argentina', 'AR', 'ARG', NOW()),
('Estados Unidos', 'US', 'USA', NOW()),
('Espana', 'ES', 'ESP', NOW())
ON DUPLICATE KEY UPDATE
    nombre = VALUES(nombre);

INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Bogota', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'CO'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Bogota'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Medellin', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'CO'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Medellin'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Cali', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'CO'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Cali'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Barranquilla', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'CO'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Barranquilla'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Cartagena', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'CO'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Cartagena'
  );

INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Ciudad de Mexico', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'MX'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Ciudad de Mexico'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Guadalajara', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'MX'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Guadalajara'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Monterrey', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'MX'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Monterrey'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Puebla', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'MX'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Puebla'
  );

INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Lima', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'PE'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Lima'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Arequipa', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'PE'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Arequipa'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Trujillo', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'PE'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Trujillo'
  );

INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Quito', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'EC'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Quito'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Guayaquil', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'EC'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Guayaquil'
  );

INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Santiago', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'CL'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Santiago'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Valparaiso', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'CL'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Valparaiso'
  );

INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Buenos Aires', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'AR'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Buenos Aires'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Cordoba', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'AR'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Cordoba'
  );

INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Miami', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'US'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Miami'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'New York', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'US'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'New York'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Los Angeles', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'US'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Los Angeles'
  );

INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Madrid', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'ES'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Madrid'
  );
INSERT INTO ciudades (pais_id, nombre, created_at)
SELECT p.id, 'Barcelona', NOW()
FROM paises p
WHERE p.codigo_iso2 = 'ES'
  AND NOT EXISTS (
      SELECT 1 FROM ciudades c WHERE c.pais_id = p.id AND c.nombre = 'Barcelona'
  );
