SELECT
    CAST(vp.id_punto AS SIGNED) AS id_punto,
    CAST(COALESCE(m.id_punto, vp.ref_punto_id) AS SIGNED) AS id_punto_ref,
    vp.posicion,
    COALESCE(m.nombre, '') AS nombre,
    COALESCE(m.contacto, '') AS contacto,
    COALESCE(m.telefono, '') AS telefono,
    COALESCE(m.direccion, '') AS direccion,
    CASE
        WHEN REPLACE(TRIM(COALESCE(m.latitud, '')), ',', '.') REGEXP '^(-|)[0-9]+(\\.[0-9]+|)$'
        THEN CAST(REPLACE(TRIM(m.latitud), ',', '.') AS DOUBLE)
        ELSE NULL
    END AS latitud,
    CASE
        WHEN REPLACE(TRIM(COALESCE(m.longitud, '')), ',', '.') REGEXP '^(-|)[0-9]+(\\.[0-9]+|)$'
        THEN CAST(REPLACE(TRIM(m.longitud), ',', '.') AS DOUBLE)
        ELSE NULL
    END AS longitud,
    COALESCE(
        vp.obs,
        COALESCE(m.observaciones, ''),
        ''
    ) AS obs,
    CASE
        WHEN vp.actualizado_automaticamente = 1 THEN NULL
        ELSE vp.id_estado_intermedio
    END AS id_estado_intermedio,
    CASE
        WHEN vp.actualizado_automaticamente = 1 THEN ''
        ELSE COALESCE(te.estado, '')
    END AS estado_intermedio,
    CASE
        WHEN vp.actualizado_automaticamente = 1 THEN ''
        ELSE COALESCE(te.estado_en, '')
    END AS estado_intermedio_en,
    CASE
        WHEN vp.actualizado_automaticamente = 1 THEN NULL
        ELSE vp.fecha_y_hora
    END AS fecha_y_hora,
    CASE
        WHEN vp.actualizado_automaticamente = 1 THEN NULL
        ELSE vp.ind_fallido
    END AS ind_fallido,
    CASE
        WHEN vp.actualizado_automaticamente = 1 THEN NULL
        ELSE vp.latitud_estado
    END AS latitud_estado,
    CASE
        WHEN vp.actualizado_automaticamente = 1 THEN NULL
        ELSE vp.longitud_estado
    END AS longitud_estado,
    CASE
        WHEN vp.actualizado_automaticamente = 1 THEN NULL
        ELSE vp.fecha_hora
    END AS fecha_hora,
    CASE
        WHEN vp.actualizado_automaticamente = 1 THEN NULL
        ELSE vp.fecha_hora
    END AS datetime,
    NULLIF(TRIM(v.observaciones_mercancia), '') AS observaciones_mercancia,
    NULLIF(TRIM(v.observaciones_cliente), '') AS observaciones_cliente
FROM (
    SELECT
        id_punto,
        id_viaje,
        posicion,
        id_estado_intermedio,
        DATE_FORMAT(fecha_y_hora, '%Y-%m-%d %H:%i:%s') AS fecha_y_hora,
        COALESCE(ind_fallido, 0) AS ind_fallido,
        latitud AS latitud_estado,
        longitud AS longitud_estado,
        fecha_foto,
        COALESCE(actualizado_automaticamente, 0) AS actualizado_automaticamente,
        NULLIF(TRIM(valor), '') AS valor_plain,
        CASE
            WHEN NULLIF(TRIM(SUBSTRING_INDEX(valor, ':|:', 1)), '') REGEXP '^[0-9]+$'
            THEN CAST(NULLIF(TRIM(SUBSTRING_INDEX(valor, ':|:', 1)), '') AS UNSIGNED)
            ELSE NULL
        END AS ref_punto_id,
        CASE
            WHEN NULLIF(TRIM(SUBSTRING_INDEX(valor, ':|:', 1)), '') REGEXP '^[0-9]+$'
            THEN NULLIF(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(valor, ':|:', 2), ':|:', -1)), '')
            ELSE NULLIF(TRIM(valor), '')
        END AS obs,
        CASE
            WHEN NULLIF(TRIM(SUBSTRING_INDEX(valor, ':|:', 1)), '') REGEXP '^[0-9]+$'
                AND NULLIF(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(valor, ':|:', 3), ':|:', -1)), '') REGEXP '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
                AND NULLIF(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(valor, ':|:', 4), ':|:', -1)), '') REGEXP '^([0-2][0-9]):([0-5][0-9])$'
            THEN CONCAT(
                NULLIF(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(valor, ':|:', 3), ':|:', -1)), ''),
                ' ',
                NULLIF(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(valor, ':|:', 4), ':|:', -1)), ''),
                ':00'
            )
            WHEN NULLIF(TRIM(SUBSTRING_INDEX(valor, ':|:', 1)), '') REGEXP '^[0-9]+$'
                AND NULLIF(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(valor, ':|:', 3), ':|:', -1)), '') REGEXP '^([0-9]{4})-([0-9]{2})-([0-9]{2})$'
                AND NULLIF(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(valor, ':|:', 4), ':|:', -1)), '') REGEXP '^([0-2][0-9]):([0-5][0-9]):([0-5][0-9])$'
            THEN CONCAT(
                NULLIF(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(valor, ':|:', 3), ':|:', -1)), ''),
                ' ',
                NULLIF(TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(valor, ':|:', 4), ':|:', -1)), '')
            )
            WHEN fecha_foto IS NOT NULL
            THEN DATE_FORMAT(fecha_foto, '%Y-%m-%d %H:%i:%s')
            ELSE NULL
        END AS fecha_hora
    FROM c_viajes_puntos
    WHERE id_viaje = ?
) vp
LEFT JOIN m_puntos_envio_recogida m
    ON m.id_punto = vp.ref_punto_id
LEFT JOIN t_viaje_estados te
    ON te.id_estado = vp.id_estado_intermedio
LEFT JOIN c_viajes v
    ON v.id_viaje = vp.id_viaje
ORDER BY vp.posicion ASC, vp.id_punto ASC;`
