SELECT
    p.id                                   AS id_preventa,
    p.co_oficina                           AS co_oficina,
    o.no_oficina                           AS no_oficina,
    o.co_dan                               AS co_dan,
    o.no_dan                               AS no_dan,
    o.co_dt                                AS co_dt,
    o.no_dt                                AS no_dt,
    IF(po.co_oficina IS NULL,'No','Sí')    AS piloto,
    p.nombre_gestor                        AS nombre_gestor,
    p.telefono_gestor                      AS telefono_gestor,
    p.email_gestor                         AS email_gestor,
    p.nombre                               AS nombre,
    p.apellidos                            AS apellidos,
    p.dni                                  AS dni,
    p.telefono_1                           AS telefono_1,
    p.telefono_2                           AS telefono_2,
    p.email                                AS email,
    p.direccion_1                          AS direccion_1,
    p.localidad_1                          AS localidad_1,
    p.provincia_1                          AS provincia_1,
    p.codpos_1                             AS codpos_1,
    p.direccion_2                          AS direccion_2,
    p.localidad_2                          AS localidad_2,
    p.provincia_2                          AS provincia_2,
    p.codpos_2                             AS codpos_2,
    p.id_oferta                            AS id_oferta,
    IF(p.proactivo = 1,'Si',
       IF(p.proactivo = 0,'No','No procede'))          AS `cliente_proactivo`,
    f.nombre                               AS oferta,
    p.id_motivo_compra                     AS id_motivo_compra,
    mc.motivo                              AS motivo_compra,
    ll.id                                  AS id_llamada,
    ll.tipo_llamada                        AS tipo_llamada,
    ll.in_primera                          AS in_primera,
    ll.in_ultima                           AS in_ultima,
    ll.id_user                             AS gestor_sd_id,
    u.name                                 AS gestor_sd_name,
    u.email                                AS gestor_sd_email,
    ll.fe_seguimiento                      AS fe_seguimiento,
    DATE_FORMAT(ll.fe_seguimiento,'%d/%m/%Y %H:%i')    AS fe_seguimiento_fmt,
    ll.pedido                              AS pedido,
    ll.id_estado                           AS id_estado,
    e.estado                               AS estado,
    ll.id_sub_estado                       AS id_sub_estado,
    e.estado_global                        AS estado_global,
    se.subestado                           AS subestado,
    DATE_FORMAT(ll.ts_cambio_estado,'%d/%m/%Y %H:%i')  AS fe_cambio_estado,
    ll.observaciones                       AS observaciones_llamada,
    p.sent_to_orign                        AS sent_to_orign,
    p.orign_retries                        AS orign_retries,
    p.created_at                           AS preventa_created_at,
    p.updated_at                           AS preventa_updated_at,
    ll.created_at                          AS llamada_created_at,
    ll.updated_at                          AS llamada_updated_at,
    ll2.tipo_llamada                       AS primera_tipo_llamada,
    u2.id                                  AS primera_gestor_sd_id,
    u2.name                                AS primera_gestor_sd_name,
    u2.email                               AS primera_gestor_sd_email,
    lp.created_at                          AS fe_pedido,
    up.name                                AS gestor_pedido_name,
    up.email                               AS gestor_pedido_email
FROM  preventas               AS p
          LEFT JOIN llamadas            AS ll   ON ll.id_preventa = p.id  AND ll.in_ultima = TRUE
          LEFT JOIN oficinas            AS o    ON o.co_oficina = p.co_oficina
          LEFT JOIN piloto_oficinas     AS po   ON po.co_oficina = o.co_oficina
          LEFT JOIN ofertas             AS f    ON f.id = p.id_oferta
          LEFT JOIN motivo_compras      AS mc   ON mc.id = p.id_motivo_compra
          LEFT JOIN users               AS u    ON u.id = ll.id_user
          LEFT JOIN estados             AS e    ON e.id = ll.id_estado
          LEFT JOIN sub_estados         AS se   ON se.id = ll.id_sub_estado
          LEFT JOIN llamadas            AS ll2  ON ll2.id_preventa = p.id AND ll2.in_primera = TRUE
          LEFT JOIN users               AS u2   ON u2.id = ll2.id_user
          LEFT JOIN (
                        SELECT  id_preventa,
                                MIN(id) AS first_ll_id
                        FROM    llamadas
                        WHERE   pedido IS NOT NULL
                        GROUP BY id_preventa
                    )                       AS x   ON x.id_preventa = p.id
          LEFT JOIN llamadas      AS lp  ON lp.id = x.first_ll_id
          LEFT JOIN users         AS up  ON up.id = lp.id_user;
