-- Sprint 7: solicitudes institucionales, mensajes, adjuntos y recordatorios.
-- Migración aditiva. No elimina datos ni modifica originales documentales.

ALTER TABLE comm_solicitudes
  ADD COLUMN IF NOT EXISTS id_institucion BIGINT UNSIGNED NULL AFTER id_fuente,
  ADD COLUMN IF NOT EXISTS id_plantilla BIGINT UNSIGNED NULL AFTER id_institucion,
  ADD COLUMN IF NOT EXISTS id_documento_solicitud BIGINT UNSIGNED NULL AFTER id_plantilla,
  ADD COLUMN IF NOT EXISTS destinatario_email VARCHAR(500) NULL AFTER destinatario_nombre,
  ADD COLUMN IF NOT EXISTS destinatario_telefono VARCHAR(120) NULL AFTER destinatario_email,
  ADD COLUMN IF NOT EXISTS prioridad ENUM('baja','media','alta','critica') NOT NULL DEFAULT 'media' AFTER referencia_interna,
  ADD COLUMN IF NOT EXISTS idioma CHAR(2) NOT NULL DEFAULT 'es' AFTER prioridad,
  ADD COLUMN IF NOT EXISTS fecha_limite DATE NULL AFTER programada_en,
  ADD COLUMN IF NOT EXISTS respuesta_esperada_en DATE NULL AFTER fecha_limite,
  ADD COLUMN IF NOT EXISTS proximo_seguimiento_en DATE NULL AFTER respuesta_esperada_en,
  ADD COLUMN IF NOT EXISTS ultimo_seguimiento_en DATETIME NULL AFTER proximo_seguimiento_en,
  ADD COLUMN IF NOT EXISTS cerrada_en DATETIME NULL AFTER respondida_en,
  ADD COLUMN IF NOT EXISTS instrucciones_internas TEXT NULL AFTER cerrada_en,
  MODIFY COLUMN estado ENUM('borrador','programada','enviada','entregada','respondida','sin_respuesta','fallida','cancelada','cerrada') NOT NULL DEFAULT 'borrador';

ALTER TABLE comm_recordatorios
  ADD COLUMN IF NOT EXISTS creado_por BIGINT UNSIGNED NULL AFTER nota,
  ADD COLUMN IF NOT EXISTS actualizado_en DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP AFTER creado_en;

ALTER TABLE comm_solicitudes
  ADD INDEX IF NOT EXISTS idx_comm_solicitud_seguimiento (id_empresa,id_caso,proximo_seguimiento_en,estado),
  ADD INDEX IF NOT EXISTS idx_comm_solicitud_documento_pendiente (id_empresa,id_documento_solicitud);

INSERT INTO comm_plantillas
(id_empresa,codigo,nombre,canal,idioma,asunto,cuerpo,variables_json,activo,creado_por,creado_en,actualizado_en)
VALUES
(NULL,'archivo_solicitud_inicial','Solicitud inicial a archivo','email','es',
 'Solicitud de información documental — {{persona}}',
 'Estimados señores:\n\nMe encuentro realizando una investigación genealógica e histórica sobre {{persona}}. Solicito respetuosamente su ayuda para localizar o confirmar la documentación descrita a continuación:\n\n{{objetivo}}\n\nDatos conocidos:\n{{datos_conocidos}}\n\nReferencia o signatura conocida:\n{{referencia}}\n\nAgradeceré que me indiquen si el documento se conserva, el procedimiento para obtener reproducción digital y cualquier tasa aplicable.\n\nAtentamente,\n{{solicitante}}',
 JSON_OBJECT('variables',JSON_ARRAY('persona','objetivo','datos_conocidos','referencia','solicitante')),1,NULL,NOW(),NOW()),
(NULL,'archivo_seguimiento','Seguimiento de solicitud','email','es',
 'Seguimiento — {{referencia}}',
 'Estimados señores:\n\nMe permito consultar el estado de la solicitud enviada el {{fecha_envio}}, relacionada con {{persona}} y la referencia {{referencia}}.\n\nComprendo los plazos de atención del archivo y agradeceré cualquier actualización o indicación adicional.\n\nAtentamente,\n{{solicitante}}',
 JSON_OBJECT('variables',JSON_ARRAY('fecha_envio','persona','referencia','solicitante')),1,NULL,NOW(),NOW()),
(NULL,'archivo_respuesta_agradecimiento','Agradecimiento por respuesta','email','es',
 'Agradecimiento — {{referencia}}',
 'Estimados señores:\n\nMuchas gracias por la respuesta y la documentación remitida en relación con {{referencia}}. La información será incorporada a una investigación familiar e histórica debidamente documentada.\n\nAtentamente,\n{{solicitante}}',
 JSON_OBJECT('variables',JSON_ARRAY('referencia','solicitante')),1,NULL,NOW(),NOW()),
(NULL,'archive_initial_request','Initial archive request','email','en',
 'Document request — {{person}}',
 'Dear Sir or Madam,\n\nI am conducting genealogical and historical research about {{person}}. I respectfully request assistance locating or confirming the following record:\n\n{{objective}}\n\nKnown details:\n{{known_details}}\n\nKnown reference or call number:\n{{reference}}\n\nPlease let me know whether the record is held, how to request a digital copy, and whether fees apply.\n\nSincerely,\n{{requester}}',
 JSON_OBJECT('variables',JSON_ARRAY('person','objective','known_details','reference','requester')),1,NULL,NOW(),NOW())
ON DUPLICATE KEY UPDATE
 nombre=VALUES(nombre),canal=VALUES(canal),asunto=VALUES(asunto),cuerpo=VALUES(cuerpo),variables_json=VALUES(variables_json),activo=1,actualizado_en=NOW();