ALTER TABLE `fe_sucursales`
  ADD COLUMN IF NOT EXISTS `consec_fee` int(11) NOT NULL DEFAULT 0 AFTER `consec_fec`;

ALTER TABLE `fe_sucursales`
  ADD COLUMN IF NOT EXISTS `consec_rep` int(11) NOT NULL DEFAULT 0 AFTER `consec_rce`;

UPDATE `fe_sucursales` fs
LEFT JOIN (
    SELECT
        `sucursal_id`,
        `cod_sucursal`,
        `cod_terminal`,
        MAX(CASE WHEN `tipo_documento` = '01' THEN CAST(RIGHT(`consecutivo`, 10) AS UNSIGNED) ELSE 0 END) AS max_fe,
        MAX(CASE WHEN `tipo_documento` = '02' THEN CAST(RIGHT(`consecutivo`, 10) AS UNSIGNED) ELSE 0 END) AS max_nd,
        MAX(CASE WHEN `tipo_documento` = '03' THEN CAST(RIGHT(`consecutivo`, 10) AS UNSIGNED) ELSE 0 END) AS max_nc,
        MAX(CASE WHEN `tipo_documento` = '04' THEN CAST(RIGHT(`consecutivo`, 10) AS UNSIGNED) ELSE 0 END) AS max_te,
        MAX(CASE WHEN `tipo_documento` = '08' THEN CAST(RIGHT(`consecutivo`, 10) AS UNSIGNED) ELSE 0 END) AS max_fec,
        MAX(CASE WHEN `tipo_documento` = '09' THEN CAST(RIGHT(`consecutivo`, 10) AS UNSIGNED) ELSE 0 END) AS max_fee
    FROM `fe_documentos`
    WHERE `consecutivo` IS NOT NULL
      AND `consecutivo` <> ''
    GROUP BY `sucursal_id`, `cod_sucursal`, `cod_terminal`
) d ON d.`sucursal_id` = fs.`id`
   AND d.`cod_sucursal` = fs.`cod_sucursal`
   AND d.`cod_terminal` = fs.`cod_terminal`
SET
    fs.`consec_fe` = GREATEST(fs.`consec_fe`, COALESCE(d.`max_fe`, 0)),
    fs.`consec_nd` = GREATEST(fs.`consec_nd`, COALESCE(d.`max_nd`, 0)),
    fs.`consec_nc` = GREATEST(fs.`consec_nc`, COALESCE(d.`max_nc`, 0)),
    fs.`consec_te` = GREATEST(fs.`consec_te`, COALESCE(d.`max_te`, 0)),
    fs.`consec_fec` = GREATEST(fs.`consec_fec`, COALESCE(d.`max_fec`, 0)),
    fs.`consec_fee` = GREATEST(fs.`consec_fee`, COALESCE(d.`max_fee`, 0));

UPDATE `fe_sucursales` fs
LEFT JOIN (
    SELECT
        `sucursal_id`,
        MAX(CASE WHEN `tipo_documento` = '05' THEN CAST(RIGHT(`numero_consecutivo_receptor`, 10) AS UNSIGNED) ELSE 0 END) AS max_cce,
        MAX(CASE WHEN `tipo_documento` = '06' THEN CAST(RIGHT(`numero_consecutivo_receptor`, 10) AS UNSIGNED) ELSE 0 END) AS max_cpce,
        MAX(CASE WHEN `tipo_documento` = '07' THEN CAST(RIGHT(`numero_consecutivo_receptor`, 10) AS UNSIGNED) ELSE 0 END) AS max_rce
    FROM `fe_mensajes_receptor`
    WHERE `numero_consecutivo_receptor` IS NOT NULL
      AND `numero_consecutivo_receptor` <> ''
    GROUP BY `sucursal_id`
) mr ON mr.`sucursal_id` = fs.`id`
SET
    fs.`consec_cce` = GREATEST(fs.`consec_cce`, COALESCE(mr.`max_cce`, 0)),
    fs.`consec_cpce` = GREATEST(fs.`consec_cpce`, COALESCE(mr.`max_cpce`, 0)),
    fs.`consec_rce` = GREATEST(fs.`consec_rce`, COALESCE(mr.`max_rce`, 0));
