-- =============================================================================
-- FIX Bug C — "Preinscripciones/Inscripciones duplicadas" (red de seguridad en BD)
-- App: ATOMS (PHP 7.4 / Zend1 / Smarty)
-- Objetivo: agregar los indices UNIQUE que faltan para que la BASE DE DATOS
--           impida por si misma los duplicados que hoy genera el reintento del
--           flujo publico de preinscripcion (ver reporte C). Cubre:
--             * preinscripto(documento)                        -> no dos personas con misma cedula
--             * preinscripcion(preinscripto_id, cursoinstancia_id) -> no dos preinscripciones
--                                                                      del mismo preinscripto a la misma instancia
--             * inscripcion(usuario_id, cursoinstancia_id)      -> no dos inscripciones del
--                                                                    mismo usuario a la misma instancia
--
-- Estructura relevante (ver sql/app.sql):
--   preinscripto : id, nombres, apellidos, documento (varchar(100) NULL), telefono, email, ...
--   preinscripcion: id, cursoinstancia_id (NULL), preinscripto_id (NULL), periodo_id, periodo, estado
--                   (OJO: esta tabla NO tiene usuario_id; el flujo publico usa preinscripto_id)
--   inscripcion  : id, usuario_id (NULL), cursoinstancia_id (NULL), rolmoodle_id, preinscripcion_id, estado, ...
--
-- -----------------------------------------------------------------------------
-- !!! IMPORTANTE — LEER ANTES DE EJECUTAR !!!
--   1. Este script NO se ejecuta automaticamente. LO EJECUTA EL USUARIO, por pasos.
--   2. HACER BACKUP de las tres tablas ANTES de tocar nada:
--        CREATE TABLE preinscripto_bkp_fixc   AS SELECT * FROM preinscripto;
--        CREATE TABLE preinscripcion_bkp_fixc AS SELECT * FROM preinscripcion;
--        CREATE TABLE inscripcion_bkp_fixc    AS SELECT * FROM inscripcion;
--   3. Correr PRIMERO los SELECT de DETECCION (PASO 1) para ver si hay duplicados.
--   4. Si hay duplicados, DEDUPLICAR (PASO 2) — las sentencias estan COMENTADAS a
--      proposito: revisar/ajustar y descomentar recien despues de mirar el PASO 1.
--      La estrategia conserva el MIN(id) de cada grupo (la fila mas antigua).
--   5. Solo cuando la DETECCION del PASO 1 devuelva 0 filas, correr los ALTER del
--      PASO 3. Si quedan duplicados, el ALTER ... ADD UNIQUE fallara (a proposito).
--
--   Sobre NULLs: en MySQL un indice UNIQUE PERMITE multiples filas con NULL en la
--   clave (los NULL se consideran distintos). Por eso:
--     - preinscripto.documento = NULL  -> no colisiona (pero documento = '' cadena
--       vacia SI colisiona: solo se permite una fila con ''). Revisar/normalizar ''.
--     - preinscripcion / inscripcion con preinscripto_id/usuario_id/cursoinstancia_id
--       en NULL -> no seran frenados por el UNIQUE. Es aceptable aqui (el UNIQUE
--       protege el caso normal, que es el que reintenta el flujo publico).
-- =============================================================================


-- =============================================================================
-- PASO 1 — DETECCION de duplicados actuales (SELECT read-only, seguros de correr)
-- =============================================================================

-- 1.a) preinscripto con el MISMO documento (posibles personas duplicadas)
--      Se ignoran documento NULL; se muestran tambien los '' (cadena vacia) porque
--      colisionarian al crear el UNIQUE.
SELECT documento, COUNT(*) AS repetidos, GROUP_CONCAT(id ORDER BY id) AS ids
FROM preinscripto
WHERE documento IS NOT NULL
GROUP BY documento
HAVING COUNT(*) > 1
ORDER BY repetidos DESC;

-- 1.b) preinscripcion duplicada por (preinscripto_id, cursoinstancia_id)
SELECT preinscripto_id, cursoinstancia_id, COUNT(*) AS repetidos, GROUP_CONCAT(id ORDER BY id) AS ids
FROM preinscripcion
WHERE preinscripto_id IS NOT NULL AND cursoinstancia_id IS NOT NULL
GROUP BY preinscripto_id, cursoinstancia_id
HAVING COUNT(*) > 1
ORDER BY repetidos DESC;

-- 1.c) inscripcion duplicada por (usuario_id, cursoinstancia_id)
SELECT usuario_id, cursoinstancia_id, COUNT(*) AS repetidos, GROUP_CONCAT(id ORDER BY id) AS ids
FROM inscripcion
WHERE usuario_id IS NOT NULL AND cursoinstancia_id IS NOT NULL
GROUP BY usuario_id, cursoinstancia_id
HAVING COUNT(*) > 1
ORDER BY repetidos DESC;


-- =============================================================================
-- PASO 2 — DEDUPLICACION (PLANTILLA — COMENTADA — revisar antes de ejecutar)
-- =============================================================================
-- Estrategia: conservar el MIN(id) de cada grupo y eliminar el resto.
-- ATENCION a las dependencias (hijos) antes de borrar filas padre:
--   * Borrar filas de `preinscripcion` puede dejar huerfanas filas de
--     `preinscripcion_adjunto` (preinscripcion_id) e `inscripcion` (preinscripcion_id).
--   * Borrar filas de `preinscripto` deja huerfanas filas de `preinscripcion`
--     (preinscripto_id): PRIMERO repuntar esas preinscripciones al id que se conserva
--     y RECIEN despues borrar el preinscripto duplicado.
-- Hacer BACKUP (PASO 0) y correr dentro de una transaccion para poder revertir.

-- 2.a) preinscripto duplicados por documento
--      (Opcional pero recomendado) repuntar hijos al id que se conserva antes de borrar:
--
-- UPDATE preinscripcion pr
-- JOIN (
--   SELECT MIN(id) AS keep_id, documento
--   FROM preinscripto
--   WHERE documento IS NOT NULL AND documento <> ''
--   GROUP BY documento
--   HAVING COUNT(*) > 1
-- ) k ON k.keep_id <> pr.preinscripto_id
-- JOIN preinscripto dup ON dup.id = pr.preinscripto_id AND dup.documento = k.documento
-- SET pr.preinscripto_id = k.keep_id;
--
-- DELETE p FROM preinscripto p
-- JOIN (
--   SELECT documento, MIN(id) AS keep_id
--   FROM preinscripto
--   WHERE documento IS NOT NULL AND documento <> ''
--   GROUP BY documento
--   HAVING COUNT(*) > 1
-- ) d ON p.documento = d.documento
-- WHERE p.id <> d.keep_id;

-- 2.b) preinscripcion duplicadas por (preinscripto_id, cursoinstancia_id)
--
-- DELETE p FROM preinscripcion p
-- JOIN (
--   SELECT preinscripto_id, cursoinstancia_id, MIN(id) AS keep_id
--   FROM preinscripcion
--   WHERE preinscripto_id IS NOT NULL AND cursoinstancia_id IS NOT NULL
--   GROUP BY preinscripto_id, cursoinstancia_id
--   HAVING COUNT(*) > 1
-- ) d ON p.preinscripto_id = d.preinscripto_id
--    AND p.cursoinstancia_id = d.cursoinstancia_id
-- WHERE p.id <> d.keep_id;

-- 2.c) inscripcion duplicadas por (usuario_id, cursoinstancia_id)
--
-- DELETE i FROM inscripcion i
-- JOIN (
--   SELECT usuario_id, cursoinstancia_id, MIN(id) AS keep_id
--   FROM inscripcion
--   WHERE usuario_id IS NOT NULL AND cursoinstancia_id IS NOT NULL
--   GROUP BY usuario_id, cursoinstancia_id
--   HAVING COUNT(*) > 1
-- ) d ON i.usuario_id = d.usuario_id
--    AND i.cursoinstancia_id = d.cursoinstancia_id
-- WHERE i.id <> d.keep_id;


-- =============================================================================
-- PASO 3 — INDICES UNIQUE (correr SOLO cuando el PASO 1 devuelva 0 filas)
-- =============================================================================
-- Red de seguridad a nivel BD: a partir de aca, ningun reintento ni condicion de
-- carrera del flujo publico puede insertar un duplicado (el segundo INSERT falla
-- con SQLSTATE 23000 - duplicate key).

-- 3.a) Evitar dos personas "distintas" con misma cedula
ALTER TABLE `preinscripto`
  ADD UNIQUE KEY `uq_preinscripto_documento` (`documento`);

-- 3.b) Evitar dos preinscripciones del mismo preinscripto a la misma instancia
ALTER TABLE `preinscripcion`
  ADD UNIQUE KEY `uq_preinscripcion_persona_instancia` (`preinscripto_id`, `cursoinstancia_id`);

-- 3.c) Evitar dos inscripciones del mismo usuario a la misma instancia
ALTER TABLE `inscripcion`
  ADD UNIQUE KEY `uq_inscripcion_usuario_instancia` (`usuario_id`, `cursoinstancia_id`);

-- =============================================================================
-- FIN. Verificacion post-ALTER (opcional): SHOW INDEX FROM `preinscripcion`;
-- =============================================================================