-- Definir nombres y apellidos
SET @nombres := 'Carlos,Luis,Juan,Pedro,Jose,Marco,Jorge,Andres,Raul,Victor,David,Fernando,Oscar,Cesar,Alfredo';
SET @apellidos := 'Perez,Gonzalez,Ramirez,Flores,Rojas,Castillo,Sanchez,Mendoza,Vargas,Reyes,Silva,Ortega,Lopez,Diaz,Chavez';
SET @id := 0;

INSERT INTO persona (
    Nombre,
    Apellido,
    NombreCompleto,
    IdTipoDoc,
    NumDoc,
    Correo,
    RutaImagen,
    Direccion,
    Estado,
    FechaRegistro,
    FechaModifica
)
SELECT
    t.nombre,
    t.apellido,
    CONCAT(t.nombre, ' ', t.apellido),
    2,
    LPAD(FLOOR(RAND() * 90000000) + 10000000, 8, '0'),
    CONCAT(LOWER(LEFT(t.nombre, 1)), LOWER(t.apellido), t.id, '@gmail.com'),
    NULL,
    CONCAT('Av. ', CASE FLOOR(RAND() * 5)
                       WHEN 0 THEN 'Los Heroes'
                       WHEN 1 THEN 'Las Palmas'
                       WHEN 2 THEN 'La Esperanza'
                       WHEN 3 THEN 'La Libertad'
                       ELSE 'El Sol' END,
           ' #', FLOOR(RAND() * 9000) + 100),
    'Activo',
    NOW(),
    NOW()
FROM (
         SELECT (@id := @id + 1) AS id,
                SUBSTRING_INDEX(SUBSTRING_INDEX(@nombres, ',', FLOOR(1 + (RAND() * 15))), ',', -1) AS nombre,
                SUBSTRING_INDEX(SUBSTRING_INDEX(@apellidos, ',', FLOOR(1 + (RAND() * 15))), ',', -1) AS apellido
         FROM (
                  SELECT 1 FROM
                               (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
                                UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) a,
                               (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
                                UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) b,
                               (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
                                UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) c,
                               (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
                                UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) d,
                               (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
                                UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) e
              ) base
             LIMIT 8000
     ) t;




-- Obtener distritos de Loreto con numeración
DROP TEMPORARY TABLE IF EXISTS tmp_distritos_loreto;

CREATE TEMPORARY TABLE tmp_distritos_loreto AS
SELECT d.idDistrito, (@row := @row + 1) AS row_num
FROM distrito d
         JOIN provincia p ON d.idProvincia = p.idProvincia
         JOIN departamentos dep ON p.idDepartamento = dep.idDepartamento
         JOIN (SELECT @row := 0) r
WHERE dep.idDepartamento = 15;

SELECT COUNT(*) INTO @total_distritos FROM tmp_distritos_loreto;
SET @i := 0;
UPDATE persona p
    JOIN (
    SELECT IdPersona, (@i := @i + 1) AS orden
    FROM persona
    WHERE IdDistrito IS NULL -- o quita esta condición para actualizar todos
    ORDER BY IdPersona ASC
    LIMIT 2013
    ) ultimos ON p.IdPersona = ultimos.IdPersona
    JOIN tmp_distritos_loreto d ON d.row_num = (ultimos.orden % @total_distritos) + 1
    SET p.IdDistrito = d.idDistrito;






-- Crear tabla temporal para los distritos de todos los departamentos (sin excluir ninguno)
DROP TEMPORARY TABLE IF EXISTS tmp_distritos_otros;
CREATE TEMPORARY TABLE tmp_distritos_otros AS
SELECT d.idDistrito, (@row := @row + 1) AS row_num
FROM distrito d
         JOIN provincia p ON d.idProvincia = p.idProvincia
         JOIN departamentos dep ON p.idDepartamento = dep.idDepartamento
         JOIN (SELECT @row := 0) r;

SELECT COUNT(*) INTO @total_distritos_otros FROM tmp_distritos_otros;

SET @i2 := 0;
UPDATE persona p
    JOIN (
    SELECT idPersona, (@i2 := @i2 + 1) AS orden
    FROM persona
    WHERE IdPersona BETWEEN 2014 AND 9347
    ORDER BY IdPersona
    ) ultimos ON p.IdPersona = ultimos.idPersona
    JOIN tmp_distritos_otros d ON d.row_num = (ultimos.orden % @total_distritos_otros) + 1
    SET p.IdDistrito = d.idDistrito;



-- -- Crear tabla temporal para los distritos de todos los departamentos excepto el 15 con numeración
-- DROP TEMPORARY TABLE IF EXISTS tmp_distritos_otros;
-- CREATE TEMPORARY TABLE tmp_distritos_otros AS
-- SELECT d.idDistrito, (@row := @row + 1) AS row_num
-- FROM distrito d
--          JOIN provincia p ON d.idProvincia = p.idProvincia
--          JOIN departamentos dep ON p.idDepartamento = dep.idDepartamento
--          JOIN (SELECT @row := 0) r
-- WHERE dep.idDepartamento <> 15;
--
-- -- Contar total distritos de otros departamentos
-- SELECT COUNT(*) INTO @total_distritos_otros FROM tmp_distritos_otros;
--
-- SET @i2 := 0;
-- UPDATE persona p
--     JOIN (
--     SELECT idPersona, (@i2 := @i2 + 1) AS orden
--     FROM persona
--     WHERE (IdDistrito IS NULL OR IdDistrito <> 15)
--     AND IdPersona BETWEEN 34002 AND 41120
--     ORDER BY IdPersona
--     ) ultimos ON p.IdPersona = ultimos.idPersona
--     JOIN tmp_distritos_otros d ON d.row_num = (ultimos.orden % @total_distritos_otros) + 1
--     SET p.IdDistrito = d.idDistrito;