USE documenta;
SET NAMES utf8mb4;

-- Documenta v1.24.1.0
-- Normaliza el catálogo a 18 servicios notariales, elimina toda la operación
-- transaccional de prueba y recrea exclusivamente las ofertas GesNot de la
-- Notaría Gaby Hernández Soto.

DROP TEMPORARY TABLE IF EXISTS tmp_catalogo_conservar;
CREATE TEMPORARY TABLE tmp_catalogo_conservar (
  codigo VARCHAR(80) NOT NULL PRIMARY KEY,
  nombre VARCHAR(180) NOT NULL,
  precio DECIMAL(14,2) NOT NULL,
  orden SMALLINT UNSIGNED NOT NULL
) ENGINE=MEMORY;

INSERT INTO tmp_catalogo_conservar (codigo,nombre,precio,orden) VALUES
 ('ZP-DJ-010','Declaración Cargos Públicos',4000,10),
 ('ZP-DJ-011','Declaración Carro de Arrastre',4000,20),
 ('DECLARACION_DE_EXPENSAS','Declaración de Expensas',4000,30),
 ('DECLARACION-JURADA-DE-RESIDENCIA','Declaración de Residencia',4000,40),
 ('DECLARACION-JURADA-DECLARACION-33','Declaración 33%',4000,50),
 ('DECLARACION_JURADA','Declaración Jurada',4000,60),
 ('DECLARACION_ESTADO_CIVIL','Declaración Estado Civil sin Testigos',4000,70),
 ('ZP-DJ-004','Declaración Ley de Alcoholes',4000,80),
 ('DECLARACION-JURADA-NO-PARENTEZCO','Declaración No Parentesco',4000,90),
 ('SUPERVIVENCIA','Declaración Supervivencia',4000,100),
 ('CARTA-RENUNCIA','Carta de Renuncia',4000,110),
 ('ZP-AUT-001','Autorización Domicilio Comercial',4500,120),
 ('ZP-ARR-001','Carta Poder de Arriendo',15000,130),
 ('CARTA-PODER-COBRAR-FINIQUITO','Carta Poder Cobrar Finiquito',11000,140),
 ('CARTA-PODER-MATRICULA-UNIVERSIDAD','Carta Poder Matrícula Universidad',4000,150),
 ('ZP-CP-004','Carta Poder Retiro de Patente',11000,160),
 ('ZP-CP-001','Carta Poder SII',7000,170),
 ('ZP-FIN-001','Finiquito',4500,180);

DROP PROCEDURE IF EXISTS sp_validar_limpieza_catalogo;
DELIMITER $$
CREATE PROCEDURE sp_validar_limpieza_catalogo()
BEGIN
  DECLARE encontrados INT DEFAULT 0;
  DECLARE gaby_count INT DEFAULT 0;
  DECLARE gesnot_count INT DEFAULT 0;
  DECLARE credencial_count INT DEFAULT 0;
  DECLARE tipo_archivo_count INT DEFAULT 0;
  DECLARE tipo_notaria_count INT DEFAULT 0;
  DECLARE estado_activo_count INT DEFAULT 0;

  SELECT COUNT(*) INTO encontrados
  FROM materias m
  JOIN tmp_catalogo_conservar k ON TRIM(m.codigo)=k.codigo;
  IF encontrados <> 18 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='PRECHECK: no se identificaron exactamente las 18 materias canónicas';
  END IF;

  SELECT COUNT(*) INTO gaby_count
  FROM notarias
  WHERE TRIM(nombre_fantasia) COLLATE utf8mb4_unicode_ci =
        'Notaría Gaby Hernández Soto' COLLATE utf8mb4_unicode_ci
    AND eliminado_en IS NULL;
  IF gaby_count <> 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='PRECHECK: no existe una única Notaría Gaby Hernández Soto';
  END IF;

  SELECT COUNT(*) INTO gesnot_count
  FROM proveedores_integracion WHERE codigo='GESNOT';
  IF gesnot_count <> 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='PRECHECK: proveedor GESNOT inexistente o duplicado';
  END IF;

  SELECT COUNT(*) INTO tipo_archivo_count
  FROM tipos_requisito WHERE codigo='ARCHIVO';
  IF tipo_archivo_count <> 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='PRECHECK: tipo de requisito ARCHIVO inexistente o duplicado';
  END IF;

  SELECT COUNT(*) INTO tipo_notaria_count
  FROM tipos_entidad WHERE codigo='NOTARIA' AND activo=TRUE;
  IF tipo_notaria_count <> 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='PRECHECK: capacidad NOTARIA inexistente o duplicada';
  END IF;

  SELECT COUNT(*) INTO estado_activo_count
  FROM estados_materia WHERE codigo='ACTIVA';
  IF estado_activo_count <> 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='PRECHECK: estado de materia ACTIVA inexistente o duplicado';
  END IF;

  SELECT COUNT(*) INTO credencial_count
  FROM notaria_credenciales nc
  JOIN notarias n ON n.id=nc.notaria_id
  JOIN proveedores_integracion pi ON pi.id=nc.proveedor_integracion_id
  WHERE TRIM(n.nombre_fantasia) COLLATE utf8mb4_unicode_ci =
        'Notaría Gaby Hernández Soto' COLLATE utf8mb4_unicode_ci
    AND pi.codigo='GESNOT'
    AND nc.activa=TRUE
    AND (nc.valida_hasta IS NULL OR nc.valida_hasta>UTC_TIMESTAMP(6));
  IF credencial_count < 1 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT='PRECHECK: Gaby Hernández Soto no tiene una credencial GesNot activa';
  END IF;
END$$
DELIMITER ;

DROP PROCEDURE IF EXISTS sp_validar_resultado_catalogo;
DELIMITER $$
CREATE PROCEDURE sp_validar_resultado_catalogo()
BEGIN
  IF @materias_finales <> 18 OR @nombres_distintos <> 18 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='POSTCHECK: el catálogo no quedó con 18 materias únicas';
  END IF;
  IF @ofertas_finales <> 18 OR @ofertas_gaby <> 18 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='POSTCHECK: no quedaron exactamente 18 ofertas exclusivas de Gaby';
  END IF;
  IF @ventas_finales <> 0 OR @solicitudes_finales <> 0 OR @pagos_finales <> 0 THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT='POSTCHECK: quedó operación transaccional residual';
  END IF;
END$$
DELIMITER ;

CALL sp_validar_limpieza_catalogo();

DROP TEMPORARY TABLE IF EXISTS tmp_materias_conservar;
CREATE TEMPORARY TABLE tmp_materias_conservar (
  id BIGINT UNSIGNED NOT NULL PRIMARY KEY,
  codigo VARCHAR(80) NOT NULL,
  nombre VARCHAR(180) NOT NULL,
  precio DECIMAL(14,2) NOT NULL,
  orden SMALLINT UNSIGNED NOT NULL
) ENGINE=MEMORY;

INSERT INTO tmp_materias_conservar (id,codigo,nombre,precio,orden)
SELECT m.id,k.codigo,k.nombre,k.precio,k.orden
FROM tmp_catalogo_conservar k
JOIN materias m ON TRIM(m.codigo)=k.codigo;

SET @gaby_id := (
  SELECT id FROM notarias
  WHERE TRIM(nombre_fantasia) COLLATE utf8mb4_unicode_ci =
        'Notaría Gaby Hernández Soto' COLLATE utf8mb4_unicode_ci
    AND eliminado_en IS NULL
  LIMIT 1
);
SET @gesnot_id := (
  SELECT id FROM proveedores_integracion WHERE codigo='GESNOT' LIMIT 1
);
SET @archivo_tipo_id := (
  SELECT id FROM tipos_requisito WHERE codigo='ARCHIVO' LIMIT 1
);
SET @notaria_tipo_id := (
  SELECT id FROM tipos_entidad WHERE codigo='NOTARIA' AND activo=TRUE LIMIT 1
);
SET @estado_activo_id := (
  SELECT id FROM estados_materia WHERE codigo='ACTIVA' LIMIT 1
);

START TRANSACTION;
SET FOREIGN_KEY_CHECKS=0;

-- La operación existente es íntegramente de prueba y depende de las ofertas
-- que serán reemplazadas. Se conserva sólo en el dump creado por el instalador.
UPDATE operaciones_b2b SET solicitud_id=NULL WHERE solicitud_id IS NOT NULL;

DELETE FROM documento_eventos;
DELETE FROM documento_aceptaciones;
DELETE FROM documento_accesos;
DELETE FROM documento_verificaciones;
DELETE FROM persona_notificaciones WHERE solicitud_id IS NOT NULL OR documento_id IS NOT NULL;
DELETE FROM documentos;

DELETE FROM solicitud_relaciones;
DELETE FROM solicitud_respuestas_entidad;
DELETE FROM solicitud_sla;
DELETE FROM solicitud_bandeja;
DELETE FROM solicitud_participantes;
DELETE FROM historial_estados_solicitud;
DELETE FROM integraciones_solicitud;
DELETE FROM solicitud_pagos;
DELETE FROM solicitudes;

DELETE FROM descargas_archivos;
DELETE FROM historial_estados_pago;
DELETE FROM intentos_integracion;
DELETE FROM comisiones_documentos;
DELETE FROM ordenes_gesnot;
DELETE FROM pagos;
DELETE FROM documentos_generados;
DELETE FROM archivos;
DELETE FROM respuestas_campos;
DELETE FROM comparecientes;
DELETE FROM consentimientos;
DELETE FROM historial_estados_venta;
DELETE FROM errores_sistema WHERE venta_id IS NOT NULL;
DELETE FROM notificaciones WHERE venta_id IS NOT NULL;
DELETE FROM ventas;

DELETE FROM entregas_correo_evento;
DELETE FROM eventos_outbox;
DELETE FROM claves_idempotencia;

DELETE FROM entidad_busqueda_indice;
DELETE FROM busquedas_publicas_auditoria;
DELETE FROM oferta_cobertura_comunas;
DELETE FROM oferta_cobertura_jurisdicciones;
DELETE FROM oferta_cobertura_cortes;
DELETE FROM oferta_conectores;
DELETE FROM directorio;

DELETE FROM configuraciones_servicio_entidad
WHERE materia_id NOT IN (SELECT id FROM tmp_materias_conservar);
DELETE FROM campos_materias
WHERE materia_id NOT IN (SELECT id FROM tmp_materias_conservar);
DELETE FROM requisitos_documentos
WHERE materia_id NOT IN (SELECT id FROM tmp_materias_conservar);
DELETE FROM plantillas_materias
WHERE materia_id NOT IN (SELECT id FROM tmp_materias_conservar);
DELETE FROM servicio_capacidades
WHERE materia_id NOT IN (SELECT id FROM tmp_materias_conservar);
DELETE FROM materias
WHERE id NOT IN (SELECT id FROM tmp_materias_conservar);

-- Nombres públicos exactos solicitados y estado activo.
UPDATE materias m
JOIN tmp_materias_conservar k ON k.id=m.id
SET m.nombre=k.nombre,
    m.estado_materia_id=@estado_activo_id,
    m.permite_adjuntos=TRUE,
    m.actualizado_en=UTC_TIMESTAMP(6);

-- Todos los servicios conservados son notariales.
UPDATE servicio_capacidades
SET activo=FALSE,es_principal=FALSE
WHERE materia_id IN (SELECT id FROM tmp_materias_conservar);

INSERT INTO servicio_capacidades
  (materia_id,tipo_entidad_id,es_principal,activo)
SELECT id,@notaria_tipo_id,TRUE,TRUE
FROM tmp_materias_conservar
ON DUPLICATE KEY UPDATE es_principal=TRUE,activo=TRUE;

-- Los requisitos pasan a ser globales y exactamente iguales a los informados.
DELETE FROM requisitos_documentos
WHERE materia_id IN (SELECT id FROM tmp_materias_conservar);

UPDATE configuraciones_servicio_entidad
SET requisitos_personalizados=FALSE,
    actualizado_en=UTC_TIMESTAMP(6)
WHERE materia_id IN (SELECT id FROM tmp_materias_conservar);

DROP TEMPORARY TABLE IF EXISTS tmp_requisitos;
CREATE TEMPORARY TABLE tmp_requisitos (
  materia_codigo VARCHAR(80) NOT NULL,
  codigo VARCHAR(80) NOT NULL,
  nombre VARCHAR(180) NOT NULL,
  descripcion VARCHAR(500) NOT NULL,
  cantidad_maxima SMALLINT UNSIGNED NOT NULL DEFAULT 1,
  orden SMALLINT UNSIGNED NOT NULL,
  PRIMARY KEY (materia_codigo,codigo)
) ENGINE=MEMORY;

INSERT INTO tmp_requisitos
  (materia_codigo,codigo,nombre,descripcion,cantidad_maxima,orden) VALUES
 ('ZP-DJ-010','cedula_identidad','Cédula de identidad','Imagen o PDF legible por ambos lados.',2,10),
 ('ZP-DJ-010','antecedentes_respaldo','Antecedentes de respaldo','Documentos que permitan respaldar la declaración, cuando corresponda.',10,20),

 ('ZP-DJ-011','cedula_identidad','Cédula de identidad vigente','Cédula de identidad vigente del o los comparecientes.',2,10),
 ('ZP-DJ-011','propiedad_carro','Propiedad del carro de arrastre','Factura, contrato u otro antecedente de propiedad del carro de arrastre.',5,20),
 ('ZP-DJ-011','antecedentes_tecnicos','Antecedentes técnicos','Antecedentes técnicos del carro de arrastre, incluyendo serie, peso y medidas.',5,30),
 ('ZP-DJ-011','antecedentes_municipales','Antecedentes municipales','Antecedentes requeridos por la municipalidad correspondiente.',10,40),

 ('DECLARACION_DE_EXPENSAS','cedula_identidad','Cédula de identidad vigente','Cédula de identidad vigente del o los comparecientes.',2,10),
 ('DECLARACION_DE_EXPENSAS','beneficiario_expensas','Antecedentes del beneficiario','Antecedentes de la persona beneficiaria de las expensas.',10,20),
 ('DECLARACION_DE_EXPENSAS','respaldos_economicos','Respaldos económicos','Respaldos económicos del sostenedor.',10,30),
 ('DECLARACION_DE_EXPENSAS','antecedentes_migratorios','Antecedentes migratorios','Antecedentes migratorios, si corresponde.',10,40),

 ('DECLARACION-JURADA-DE-RESIDENCIA','cedula_identidad','Cédula de identidad','Imagen o PDF legible por ambos lados.',2,10),
 ('DECLARACION-JURADA-DE-RESIDENCIA','antecedentes_respaldo','Antecedentes de respaldo','Documentos que permitan respaldar la declaración, cuando corresponda.',10,20),

 ('DECLARACION-JURADA-DECLARACION-33','cedula_identidad','Cédula de identidad','Imagen o PDF legible por ambos lados.',2,10),
 ('DECLARACION-JURADA-DECLARACION-33','antecedentes_respaldo','Antecedentes de respaldo','Documentos que permitan respaldar la declaración, cuando corresponda.',10,20),

 ('DECLARACION_JURADA','cedula_identidad','Cédula de identidad','Imagen o PDF legible por ambos lados.',2,10),
 ('DECLARACION_JURADA','antecedentes_respaldo','Antecedentes de respaldo','Documentos que permitan respaldar la declaración, cuando corresponda.',10,20),

 ('DECLARACION_ESTADO_CIVIL','cedula_identidad','Cédula de identidad','Imagen o PDF legible por ambos lados.',2,10),
 ('DECLARACION_ESTADO_CIVIL','antecedentes_respaldo','Antecedentes de respaldo','Documentos que permitan respaldar la declaración, cuando corresponda.',10,20),

 ('ZP-DJ-004','cedula_identidad','Cédula de identidad vigente','Cédula de identidad vigente del o los comparecientes.',2,10),
 ('ZP-DJ-004','establecimiento_patente','Establecimiento o patente de alcoholes','Antecedentes del establecimiento o patente de alcoholes.',10,20),
 ('ZP-DJ-004','antecedentes_municipales','Antecedentes municipales','Antecedentes requeridos por la municipalidad correspondiente.',10,30),

 ('DECLARACION-JURADA-NO-PARENTEZCO','cedula_identidad','Cédula de identidad vigente','Cédula de identidad vigente del o los comparecientes.',2,10),

 ('SUPERVIVENCIA','cedula_identidad','Cédula de identidad vigente','Cédula de identidad vigente del o los comparecientes.',2,10),
 ('SUPERVIVENCIA','establecimiento_patente','Establecimiento o patente de alcoholes','Antecedentes del establecimiento o patente de alcoholes.',10,20),
 ('SUPERVIVENCIA','antecedentes_municipales','Antecedentes municipales','Antecedentes requeridos por la municipalidad correspondiente.',10,30),

 ('CARTA-RENUNCIA','cedula_identidad','Cédula de identidad','Imagen o PDF legible por ambos lados.',2,10),

 ('ZP-AUT-001','cedula_identidad','Cédula de identidad vigente','Cédula de identidad vigente del o los comparecientes.',2,10),
 ('ZP-AUT-001','dominio_vigente','Certificado de dominio vigente','Certificado de dominio vigente del inmueble.',5,20),
 ('ZP-AUT-001','habilitacion_uso','Habilitación para el uso del inmueble','Contrato de arriendo, cesión o antecedente que habilite el uso del inmueble, si corresponde.',10,30),
 ('ZP-AUT-001','representacion_poder','Representación o poder','Antecedentes de representación o poder, si corresponde.',10,40),

 ('ZP-ARR-001','cedula_identidad','Cédula de identidad vigente','Cédula de identidad vigente del o los comparecientes.',2,10),
 ('ZP-ARR-001','dominio_vigente','Certificado de dominio vigente','Certificado de dominio vigente del inmueble.',5,20),
 ('ZP-ARR-001','representacion_poder','Representación o poder','Antecedentes de representación o poder, si corresponde.',10,30),

 ('CARTA-PODER-COBRAR-FINIQUITO','cedula_identidad','Cédula de identidad vigente','Cédula de identidad vigente del o los comparecientes.',2,10),
 ('CARTA-PODER-COBRAR-FINIQUITO','finiquito_original','Finiquito original','Copia del finiquito original.',5,20),
 ('CARTA-PODER-COBRAR-FINIQUITO','concepto_monto','Concepto y monto','Antecedentes del concepto y monto que se modifica o complementa.',10,30),

 ('CARTA-PODER-MATRICULA-UNIVERSIDAD','cedula_identidad','Cédula de identidad','Imagen o PDF legible por ambos lados.',2,10),

 ('ZP-CP-001','cedula_identidad','Cédula de identidad vigente','Cédula de identidad vigente del o los comparecientes.',2,10),
 ('ZP-CP-001','antecedentes_tramite','Antecedentes del trámite','Antecedentes del trámite que se realizará ante el SII u otra institución.',10,20),
 ('ZP-CP-001','facultades','Detalle de facultades','Minuta o detalle de las facultades que se otorgarán.',10,30),
 ('ZP-CP-001','formato_institucion','Formato de la institución','Formato o requisitos de la institución receptora, si corresponde.',10,40),

 ('ZP-FIN-001','cedula_identidad','Cédula de identidad','Imagen o PDF legible por ambos lados.',2,10),
 ('ZP-FIN-001','contrato_liquidacion','Contrato y última liquidación','Antecedentes necesarios para revisar el término laboral.',10,20);

INSERT INTO requisitos_documentos
  (materia_id,notaria_id,tipo_requisito_id,codigo,nombre,descripcion,requerido,
   cantidad_maxima,mime_permitidos,tamano_maximo_bytes,orden,activo)
SELECT k.id,NULL,@archivo_tipo_id,r.codigo,r.nombre,r.descripcion,TRUE,
       r.cantidad_maxima,'application/pdf,image/jpeg,image/png',10485760,r.orden,TRUE
FROM tmp_requisitos r
JOIN tmp_materias_conservar k ON k.codigo=r.materia_codigo;

-- Ofertas exclusivas de la Notaría Gaby Hernández Soto.
INSERT INTO directorio
  (notaria_id,materia_id,codigo_gesnot,gesnot_tipo_ot,gesnot_materia,precio,
   moneda,plazo_estimado_horas,activo,tipo_cobertura)
SELECT @gaby_id,k.id,k.nombre,2,k.nombre,k.precio,'CLP',NULL,TRUE,'NACIONAL'
FROM tmp_materias_conservar k;

INSERT INTO oferta_conectores
  (oferta_id,proveedor_integracion_id,evento,obligatorio,activo,configuracion_json)
SELECT d.id,@gesnot_id,'POST_PAGO',FALSE,TRUE,JSON_OBJECT('modo','OT')
FROM directorio d
WHERE d.notaria_id=@gaby_id;

-- Reconstruye el índice público con el catálogo definitivo.
CALL sp_refrescar_indice_busqueda_entidades();

-- Validaciones anteriores al COMMIT.
SET @materias_finales := (SELECT COUNT(*) FROM materias);
SET @ofertas_finales := (SELECT COUNT(*) FROM directorio);
SET @ofertas_gaby := (SELECT COUNT(*) FROM directorio WHERE notaria_id=@gaby_id);
SET @ventas_finales := (SELECT COUNT(*) FROM ventas);
SET @solicitudes_finales := (SELECT COUNT(*) FROM solicitudes);
SET @pagos_finales := (SELECT COUNT(*) FROM pagos);
SET @nombres_distintos := (SELECT COUNT(DISTINCT nombre) FROM materias);

CALL sp_validar_resultado_catalogo();

SET FOREIGN_KEY_CHECKS=1;
COMMIT;

DROP PROCEDURE IF EXISTS sp_validar_resultado_catalogo;
DROP PROCEDURE IF EXISTS sp_validar_limpieza_catalogo;

SELECT m.id,m.codigo,m.nombre,d.precio,d.gesnot_tipo_ot,d.gesnot_materia,
       n.nombre_fantasia AS entidad,d.activo
FROM materias m
JOIN directorio d ON d.materia_id=m.id
JOIN notarias n ON n.id=d.notaria_id
ORDER BY FIELD(m.codigo,
 'ZP-DJ-010','ZP-DJ-011','DECLARACION_DE_EXPENSAS',
 'DECLARACION-JURADA-DE-RESIDENCIA','DECLARACION-JURADA-DECLARACION-33',
 'DECLARACION_JURADA','DECLARACION_ESTADO_CIVIL','ZP-DJ-004',
 'DECLARACION-JURADA-NO-PARENTEZCO','SUPERVIVENCIA','CARTA-RENUNCIA',
 'ZP-AUT-001','ZP-ARR-001','CARTA-PODER-COBRAR-FINIQUITO',
 'CARTA-PODER-MATRICULA-UNIVERSIDAD','ZP-CP-004','ZP-CP-001','ZP-FIN-001');

SELECT
  (SELECT COUNT(*) FROM materias) AS materias,
  (SELECT COUNT(*) FROM directorio) AS ofertas,
  (SELECT COUNT(*) FROM requisitos_documentos) AS requisitos,
  (SELECT COUNT(*) FROM ventas) AS ventas,
  (SELECT COUNT(*) FROM solicitudes) AS solicitudes,
  (SELECT COUNT(*) FROM pagos) AS pagos;