# Diseño Técnico: Explosión Multinivel de Recetas y Pre-elaborados

Este documento detalla los cambios de base de datos, reglas de cálculo y vistas SQL necesarias en `pastelerias` (MariaDB) para obtener los ingredientes reales de cualquier receta, incluyendo de forma recursiva los ingredientes de sus pre-elaborados y los pre-elaborados de pre-elaborados (N niveles).

---

## 1. Diagnóstico de la situación actual

Tras revisar el esquema en `sql/tablas/` y los módulos Delphi (`frm_RecetasEditor.pas`, `frm_PlanificacionMasasProduccion.pas`), se detectan cuatro puntos críticos que impiden hacer una vista recursiva exacta sin ajustes previos:

1. **Ambigüedad en `ob_procesos_ingredientes.id_materia_prima`**:
   - Actualmente `id_materia_prima` guarda tanto IDs de `ge_materias_primas` como IDs de `ge_articulos` (por ejemplo en `frm_RecetasEditor.pas` se hace `LEFT JOIN` contra ambas tablas).
   - Al ser ambas tablas `INT AUTO_INCREMENT`, existe riesgo de colisión de IDs y no hay forma explícita de distinguir si una línea es:
     - Una materia prima pura (`ge_materias_primas`).
     - Un artículo comprado sin receta (`ge_articulos`).
     - Un pre-elaborado fabricado por otra receta (`ob_recetas`).
2. **Falta de rendimiento neto explícito por receta**:
   - Para escalar los ingredientes de un pre-elaborado hace falta conocer cuánto producto neto genera 1 ejecución de su receta base.
   - Si un pre-elaborado tiene mermas por cocción, reducción u horneado, la suma bruta de sus ingredientes no coincide con el peso final disponible.
3. **Unidades heterogéneas (`g`, `kg`, `ml`, `l`, `ud`)**:
   - `ob_procesos_ingredientes.unidad` es texto libre. Para sumar y dividir proporciones entre niveles de receta es imprescindible normalizar a una unidad base común (gramos `g` / mililitros `ml`).
4. **Posible duplicidad en la relación Artículo ↔ Receta**:
   - Un artículo puede aparecer en `ob_recetas_productos` o en `ob_recetas_procesos.id_articulo_resultado`. Si un artículo pre-elaborado tuviera más de una receta activa vinculada, la explosión multiplicaría sus ingredientes.

---

## 2. Cambios necesarios en la Base de Datos

### 2.1. Tabla `ob_recetas`
Añadir control explícito de pre-elaborado y rendimiento neto de la receta base:

```sql
ALTER TABLE `ob_recetas`
  ADD COLUMN `es_preelaborado` TINYINT(1) NOT NULL DEFAULT 0
    COMMENT '1 = Receta intermedia usada como ingrediente en otras recetas'
    AFTER `es_masa_base`,
  ADD COLUMN `rendimiento_base` DECIMAL(12,4) DEFAULT NULL
    COMMENT 'Cantidad neta producida por 1 receta base (si NULL se calcula por suma de ingredientes)'
    AFTER `es_preelaborado`,
  ADD COLUMN `unidad_rendimiento` VARCHAR(10) NOT NULL DEFAULT 'g'
    COMMENT 'Unidad del rendimiento_base (g, kg, ml, l, ud)'
    AFTER `rendimiento_base`,
  ADD COLUMN `porcentaje_merma` DECIMAL(5,2) NOT NULL DEFAULT 0.00
    COMMENT 'Merma estimada (%) al calcular rendimiento desde ingredientes'
    AFTER `unidad_rendimiento`;
```

> **Nota**: Hay que actualizar los triggers `trg_ob_recetas_after_insert`, `trg_ob_recetas_after_update` y `trg_ob_recetas_after_delete` para incluir estos nuevos campos en el `JSON_OBJECT` de `ge_auditoria_cambios`.

### 2.2. Tabla `ob_procesos_ingredientes`
Añadir discriminador de origen y referencia directa opcional a subreceta:

```sql
ALTER TABLE `ob_procesos_ingredientes`
  ADD COLUMN `tipo_origen` ENUM('MP', 'ART', 'REC') NOT NULL DEFAULT 'MP'
    COMMENT 'MP = ge_materias_primas, ART = ge_articulos, REC = ob_recetas'
    AFTER `proceso_id`,
  ADD COLUMN `id_subreceta` INT(11) DEFAULT NULL
    COMMENT 'Referencia directa a ob_recetas(id) cuando el ingrediente es un pre-elaborado'
    AFTER `id_materia_prima`,
  ADD KEY `idx_proc_ing_tipo_mp` (`tipo_origen`, `id_materia_prima`),
  ADD KEY `fk_proc_ing_subreceta` (`id_subreceta`),
  ADD CONSTRAINT `fk_proc_ing_subreceta`
    FOREIGN KEY (`id_subreceta`) REFERENCES `ob_recetas` (`id`)
    ON UPDATE CASCADE ON DELETE RESTRICT;
```

> **Nota**: Actualizar igualmente los 3 triggers `trg_ob_procesos_ingredientes_after_*` para auditar `tipo_origen` e `id_subreceta`.

### 2.3. Tabla auxiliar de conversión de unidades (`ge_unidades_conversion`)
Evita hardcodear `CASE` repetidos en múltiples vistas/consultas y asegura cálculos consistentes:

```sql
CREATE TABLE IF NOT EXISTS `ge_unidades_conversion` (
  `unidad` VARCHAR(10) NOT NULL,
  `unidad_canonica` VARCHAR(10) NOT NULL COMMENT 'g, ml, ud',
  `factor_a_canonica` DECIMAL(12,6) NOT NULL COMMENT 'Multiplicador para pasar a unidad canónica',
  PRIMARY KEY (`unidad`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

INSERT INTO `ge_unidades_conversion` (`unidad`, `unidad_canonica`, `factor_a_canonica`) VALUES
('g',      'g',  1.000000),
('gr',     'g',  1.000000),
('gramos', 'g',  1.000000),
('kg',     'g',  1000.000000),
('kilo',   'g',  1000.000000),
('kilos',  'g',  1000.000000),
('mg',     'g',  0.001000),
('ml',     'ml', 1.000000),
('cc',     'ml', 1.000000),
('l',      'ml', 1000.000000),
('lt',     'ml', 1000.000000),
('litro',  'ml', 1000.000000),
('litros', 'ml', 1000.000000),
('ud',     'ud', 1.000000),
('uds',    'ud', 1.000000),
('unidad', 'ud', 1.000000)
ON DUPLICATE KEY UPDATE
  `unidad_canonica` = VALUES(`unidad_canonica`),
  `factor_a_canonica` = VALUES(`factor_a_canonica`);
```

### 2.4. Migración de datos existentes (antes de activar las vistas)
1. **Clasificar `tipo_origen` en `ob_procesos_ingredientes`**:
   - Si `id_materia_prima` existe en `ge_materias_primas` (y no es exclusivamente un artículo): `tipo_origen = 'MP'`.
   - Si `id_materia_prima` existe en `ge_articulos` (revisar manualmente cualquier ID que exista en ambas tablas): `tipo_origen = 'ART'`.
2. **Rellenar `id_subreceta`** para los ingredientes que apunten a un artículo producido por una receta activa en `ob_recetas_productos` (o `ob_recetas_procesos.id_articulo_resultado`). Cuando `id_subreceta IS NOT NULL`, `tipo_origen` puede marcarse como `'REC'` (o mantenerse `'ART'` con `id_subreceta` informado; la vista usará `id_subreceta IS NOT NULL` como disparador de recursión).
3. **Marcar `es_preelaborado = 1`** en `ob_recetas` para todas las recetas usadas como `id_subreceta` (además de masas madre/masas base si participan como ingredientes).

---

## 3. Fórmulas de cálculo exacto

1. **Cantidad canónica de cada línea de ingrediente**:
   $$\text{cant\_canonica} = \text{cantidad\_base} \times \text{COALESCE}(\text{uc.factor\_a\_canonica}, 1)$$

2. **Rendimiento efectivo de una receta ($R$)**:
   - Si `ob_recetas.rendimiento_base` está informado (> 0): se convierte a unidad canónica usando `ge_unidades_conversion`.
   - Si es `NULL`: se calcula como la suma de `cant_canonica` de todos sus ingredientes aplicando la merma:
     $$\text{rendimiento\_efectivo} = \left(\sum \text{cant\_canonica}\right) \times \left(1 - \frac{\text{porcentaje\_merma}}{100}\right)$$

3. **Factor de escala acumulado en el árbol**:
   - **Nivel 0 (receta raíz)**: $\text{factor}_0 = 1$
   - **Nivel $k$ (al expandir un pre-elaborado $S$ usado con cantidad $Q$ dentro de un padre con factor $\text{factor}_{k-1}$)**:
     $$\text{factor}_k = \text{factor}_{k-1} \times \frac{Q_{\text{canónica}}}{\text{rendimiento\_efectivo}(S)}$$

4. **Protección contra referencias circulares**:
   - Cada paso recursivo concatena el ID de receta en `ruta_recetas` (ej. `'12,4,9'`) y exige `FIND_IN_SET(subreceta_id, ruta_recetas) = 0` más un límite de seguridad `nivel < 15`.

---

## 4. Arquitectura de Vistas SQL propuestas

Se proponen **3 vistas encadenadas** (limpias, legibles y reutilizables desde cualquier módulo):

### 4.1. `vw_recetas_rendimiento`
Devuelve el rendimiento neto canónico de cada receta para no repetir el cálculo dentro del CTE recursivo:

```sql
CREATE OR REPLACE VIEW `vw_recetas_rendimiento` AS
SELECT
  r.id AS receta_id,
  r.descripcion,
  r.es_masa_madre,
  r.es_masa_base,
  r.es_preelaborado,
  COALESCE(uc_r.unidad_canonica, 'g') AS unidad_canonica,
  CASE
    WHEN r.rendimiento_base IS NOT NULL AND r.rendimiento_base > 0 THEN
      r.rendimiento_base * COALESCE(uc_r.factor_a_canonica, 1)
    ELSE
      COALESCE(SUM(pi.cantidad_base * COALESCE(uc_i.factor_a_canonica, 1)), 0)
      * (1 - (COALESCE(r.porcentaje_merma, 0) / 100))
  END AS rendimiento_canonico
FROM ob_recetas r
LEFT JOIN ge_unidades_conversion uc_r
  ON LOWER(TRIM(r.unidad_rendimiento)) = uc_r.unidad
LEFT JOIN ob_recetas_procesos rp
  ON rp.receta_id = r.id
LEFT JOIN ob_procesos_ingredientes pi
  ON pi.proceso_id = rp.id
LEFT JOIN ge_unidades_conversion uc_i
  ON LOWER(TRIM(pi.unidad)) = uc_i.unidad
GROUP BY
  r.id, r.descripcion, r.es_masa_madre, r.es_masa_base, r.es_preelaborado,
  uc_r.unidad_canonica, r.rendimiento_base, uc_r.factor_a_canonica, r.porcentaje_merma;
```

### 4.2. `vw_recetas_ingredientes_arbol`
Explosión recursiva completa (muestra tanto nodos intermedios de pre-elaborados como hojas finales):

```sql
CREATE OR REPLACE VIEW `vw_recetas_ingredientes_arbol` AS
WITH RECURSIVE cte_explosion AS (
  -- Nivel 0: ingredientes directos de cada receta activa
  SELECT
    r.id AS receta_raiz_id,
    r.descripcion AS receta_raiz_desc,
    0 AS nivel,
    CAST(r.id AS CHAR(500)) AS ruta_recetas,
    r.id AS receta_origen_id,
    r.descripcion AS receta_origen_desc,
    rp.id AS proceso_id,
    rp.orden AS proceso_orden,
    rp.nombre AS proceso_nombre,
    pi.id AS linea_ingrediente_id,
    pi.tipo_origen,
    pi.id_materia_prima,
    pi.id_subreceta,
    CAST(1.00000000 AS DECIMAL(18,8)) AS factor_escala,
    CAST(pi.cantidad_base * COALESCE(uc.factor_a_canonica, 1) AS DECIMAL(18,6)) AS cantidad_canonica,
    COALESCE(uc.unidad_canonica, LOWER(TRIM(pi.unidad))) AS unidad_canonica,
    CASE WHEN pi.id_subreceta IS NULL THEN 1 ELSE 0 END AS es_hoja
  FROM ob_recetas r
  JOIN ob_recetas_procesos rp ON rp.receta_id = r.id
  JOIN ob_procesos_ingredientes pi ON pi.proceso_id = rp.id
  LEFT JOIN ge_unidades_conversion uc ON LOWER(TRIM(pi.unidad)) = uc.unidad
  WHERE r.activo = 1

  UNION ALL

  -- Niveles N: expandir ingredientes que son pre-elaborados (id_subreceta IS NOT NULL)
  SELECT
    c.receta_raiz_id,
    c.receta_raiz_desc,
    c.nivel + 1 AS nivel,
    CONCAT(c.ruta_recetas, ',', sub_r.id) AS ruta_recetas,
    sub_r.id AS receta_origen_id,
    sub_r.descripcion AS receta_origen_desc,
    sub_rp.id AS proceso_id,
    sub_rp.orden AS proceso_orden,
    sub_rp.nombre AS proceso_nombre,
    sub_pi.id AS linea_ingrediente_id,
    sub_pi.tipo_origen,
    sub_pi.id_materia_prima,
    sub_pi.id_subreceta,
    CAST(
      c.factor_escala * (c.cantidad_canonica / NULLIF(rend.rendimiento_canonico, 0))
      AS DECIMAL(18,8)
    ) AS factor_escala,
    CAST(
      (c.cantidad_canonica / NULLIF(rend.rendimiento_canonico, 0))
      * (sub_pi.cantidad_base * COALESCE(uc_sub.factor_a_canonica, 1))
      AS DECIMAL(18,6)
    ) AS cantidad_canonica,
    COALESCE(uc_sub.unidad_canonica, LOWER(TRIM(sub_pi.unidad))) AS unidad_canonica,
    CASE WHEN sub_pi.id_subreceta IS NULL THEN 1 ELSE 0 END AS es_hoja
  FROM cte_explosion c
  JOIN ob_recetas sub_r ON sub_r.id = c.id_subreceta AND sub_r.activo = 1
  JOIN vw_recetas_rendimiento rend ON rend.receta_id = sub_r.id
  JOIN ob_recetas_procesos sub_rp ON sub_rp.receta_id = sub_r.id
  JOIN ob_procesos_ingredientes sub_pi ON sub_pi.proceso_id = sub_rp.id
  LEFT JOIN ge_unidades_conversion uc_sub ON LOWER(TRIM(sub_pi.unidad)) = uc_sub.unidad
  WHERE c.es_hoja = 0
    AND c.nivel < 15
    AND FIND_IN_SET(sub_r.id, c.ruta_recetas) = 0
)
SELECT
  e.*,
  CASE e.tipo_origen
    WHEN 'MP'  THEN mp.descripcion
    WHEN 'ART' THEN a.descripcion
    WHEN 'REC' THEN sr.descripcion
    ELSE COALESCE(mp.descripcion, a.descripcion, sr.descripcion)
  END AS ingrediente_descripcion
FROM cte_explosion e
LEFT JOIN ge_materias_primas mp ON e.tipo_origen = 'MP' AND mp.id = e.id_materia_prima
LEFT JOIN ge_articulos a        ON e.tipo_origen = 'ART' AND a.id = e.id_materia_prima
LEFT JOIN ob_recetas sr         ON sr.id = e.id_subreceta;
```

### 4.3. `vw_recetas_ingredientes_netos`
Vista consolidada de **ingredientes puros finales** (solo hojas, sumados por ingrediente y unidad) lista para compras, costes, alérgenos y planificación:

```sql
CREATE OR REPLACE VIEW `vw_recetas_ingredientes_netos` AS
SELECT
  receta_raiz_id,
  receta_raiz_desc,
  tipo_origen,
  id_materia_prima,
  ingrediente_descripcion,
  unidad_canonica,
  SUM(cantidad_canonica) AS cantidad_total_canonica
FROM vw_recetas_ingredientes_arbol
WHERE es_hoja = 1
GROUP BY
  receta_raiz_id,
  receta_raiz_desc,
  tipo_origen,
  id_materia_prima,
  ingrediente_descripcion,
  unidad_canonica;
```

---

## 5. Impacto y adaptaciones en Delphi

1. **`frm_RecetasEditor.pas`**:
   - Cargar y guardar `tipo_origen`, `id_subreceta` y `unidad` en `ob_procesos_ingredientes` (actualmente inserta `'g'` fijo en la línea 2040).
   - Añadir controles en cabecera para `es_preelaborado`, `rendimiento_base`, `unidad_rendimiento` y `porcentaje_merma` de `ob_recetas`.
   - Validar en interfaz que una receta no se añada a sí misma (y opcionalmente validar ciclos al guardar).
   - Opcional: añadir pestaña de consulta rápida *"Desglose total de materias primas"* alimentada por `vw_recetas_ingredientes_netos`.
2. **`frm_PlanificacionMasasProduccion.pas`**:
   - Sustituir o simplificar los cálculos manuales de Masa Madre y subrecetas usando `vw_recetas_ingredientes_arbol` / `vw_recetas_ingredientes_netos`.
3. **Costes, Compras, Etiquetado / Alérgenos y FastReport (`.fr3`)**:
   - Cualquier informe o pantalla de escandallo puede consultar directamente `vw_recetas_ingredientes_netos WHERE receta_raiz_id = :id` sin reescribir lógica recursiva.
4. **Reglas del proyecto a respetar en la implementación Delphi**:
   - Campos `TINYINT(1)` / `INT(1)` como `es_preelaborado` o `es_hoja` se leerán con `.AsInteger <> 0` (nunca `.AsBoolean` en queries dinámicas).
   - Controles de filtro auxiliares en UI llevarán `Tag = 999`.
   - Parámetros de texto con `SetSafeString` / `SetSafeMemo`.

