SELECT
    p.id AS id_preventa,
    p.co_oficina,
    o.no_oficina,
    o.co_dan,
    o.no_dan,
    o.co_dt,
    o.no_dt,
    IF(po.co_oficina IS NULL,'No','Sí') AS piloto,
    p.nombre_gestor,
    p.telefono_gestor,
    p.email_gestor,
    IF(p.nombre IS NULL,'E','P') AS persona_empresa,
    p.nombre,
    p.apellidos,
    p.dni,
    p.representante_legal,
    p.dni_representante_legal,
    p.nombre_empresa,
    p.cif,
    p.telefono_1,
    p.telefono_2,
    p.email,
    p.direccion,
    p.localidad,
    p.provincia,
    p.codpos,
    p.centro_coste,
    p.id_oferta,
    IF(p.obras=1,'Si','No') AS obras,
    IF(p.luz=1,'Si','No') AS luz,
    IF(p.internet=1,'Si','No') AS internet,
    IF(p.mascotas=1,'Si','No') AS mascotas,
    IF(p.proactivo=1,'Si', IF(p.proactivo=0,'No','No procede')) AS cliente_proactivo,
    f.tipo_instalacion,
    f.nombre AS oferta,
    f.origen,
    f.orden_trabajo,
    p.id_motivo_compra,
    mc.motivo AS motivo_compra,
    p.id_subtipo,
    s.subtipo,
    p.observaciones_preventa,
    ll.id AS id_llamada,
    ll.tipo_llamada,
    ll.in_primera,
    ll.in_ultima,
    ll.id_user AS gestor_sd_id,
    u.name AS gestor_sd_name,
    u.email AS gestor_sd_email,
    ll.fe_seguimiento,
    DATE_FORMAT(ll.fe_seguimiento,'%d/%m/%Y %H:%i') AS fe_seguimiento_fmt,
    ll.fe_instalacion,
    ll.ho_instalacion,
    DATE_FORMAT(ll.fe_instalacion,'%d/%m/%Y') AS fe_instalacion_fmt,
    ll.prospecto,
    ll.pedido,
    ll.id_estado,
    e.estado,
    ll.id_sub_estado,
    e.estado_global,
    se.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,
    p.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,
    p.compara
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 subtipos AS s ON s.id = p.id_subtipo
          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
