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,
    p.nombre_gestor AS nombre_gestor,
    p.telefono_gestor AS telefono_gestor,
    p.email_gestor AS email_gestor,
    IF(p.nombre IS NULL, 'E', 'P') AS persona_empresa,
    p.nombre AS nombre,
    p.apellidos AS apellidos,
    p.dni AS dni,
    p.representante_legal AS representante_legal,
    p.dni_representante_legal AS dni_representante_legal,
    p.nombre_empresa AS nombre_empresa,
    p.cif AS cif,
    p.telefono_1 AS telefono_1,
    p.telefono_2 AS telefono_2,
    p.email AS email,
    p.direccion AS direccion,
    p.localidad AS localidad,
    p.provincia AS provincia,
    p.codpos AS codpos,
    p.centro_coste AS centro_coste,
    p.id_oferta AS 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',
    f.tipo_instalacion AS tipo_instalacion,
    f.nombre AS oferta,
    f.origen AS origen,
    f.orden_trabajo AS orden_trabajo,
    p.id_motivo_compra AS id_motivo_compra,
    mc.motivo AS motivo_compra,
    p.id_subtipo AS id_subtipo,
    s.subtipo AS subtipo,
    p.observaciones_preventa AS observaciones_preventa,
    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.fe_instalacion AS fe_instalacion,
    ll.ho_instalacion AS ho_instalacion,
    DATE_FORMAT(ll.fe_instalacion, '%d/%m/%Y') AS fe_instalacion_fmt,
    ll.prospecto AS prospecto,
    ll.pedido AS pedido,
    ll.id_estado AS id_estado,
    e.estado AS estado,
    ll.id_sub_estado,
    se.subestado,
    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,
    (select created_at from llamadas where id_preventa = p.id and pedido is not null order by id limit 1) as fe_pedido
FROM preventas p
         LEFT JOIN llamadas ll ON p.id = ll.id_preventa AND ll.in_ultima = true
         LEFT JOIN oficinas o ON p.co_oficina = o.co_oficina
         LEFT JOIN ofertas f ON f.id = p.id_oferta
         LEFT JOIN motivo_compras mc ON mc.id = p.id_motivo_compra
         LEFT JOIN subtipos s ON s.id = p.id_subtipo
         LEFT JOIN users u ON ll.id_user = u.id
         LEFT JOIN estados e ON ll.id_estado = e.id
         LEFT JOIN sub_estados se ON ll.id_sub_estado = se.id
         LEFT JOIN llamadas ll2 ON ll2.id_preventa = p.id AND ll2.in_primera = true
         LEFT JOIN users u2 ON u2.id = ll2.id_user
