-- Relatório de Embarcadores

SELECT c.id,
    c.exhibition_name AS nome_fantasia,
    c.company_name AS razao_social,
    c.cnpj,
    c.date_joined AS data_cadastro,
    c.financial_email AS email_financeiro,
    c.phone AS telefone_empresa,
    c.blocked AS bloqueado,
    u.username AS email_usuario,
    up.name AS nome_usuario,
    up.phone AS telefone_usuario,

    first_order.order_id AS primeiro_pedido,
    first_order.date_added AS data_primeiro_pedido,
    first_order.origem AS primeira_origem,
    first_order.origem_estado AS primeira_origem_estado,
    first_order.destino AS primeiro_destino,
    first_order.destino_estado AS primeiro_destino_estado,
    last_order.order_id AS ultimo_pedido,
    last_order.date_added AS data_ultimo_pedido,
    qnt_pedidos.contagem AS qnt_pedidos

FROM accounts_company AS c

LEFT JOIN auth_user AS u ON c.admin_id = u.id
LEFT JOIN accounts_userprofile AS up ON u.id = up.user_id

LEFT JOIN (  -- Getting the first order
    SELECT
        o.id,
        o.order_id,
        o.shipper_id,
        origin_city.nome AS origem,
        origin_state.sigla AS origem_estado,
        destination_city.nome AS destino,
        destination_state.sigla AS destino_estado,
        MIN(date_added) AS date_added

    FROM orders_order AS o
    LEFT JOIN accounts_company AS dispatcher ON o.dispatcher_id = dispatcher.id
    LEFT JOIN ibge_municipio AS origin_city ON dispatcher.city_id = origin_city.codigo_ibge
    LEFT JOIN ibge_unidadefederativa AS origin_state ON origin_city.uf_id = origin_state.codigo_ibge
    LEFT JOIN accounts_company AS receiver ON o.receiver_id = receiver.id
    LEFT JOIN ibge_municipio AS destination_city ON receiver.city_id = destination_city.codigo_ibge
    LEFT JOIN ibge_unidadefederativa AS destination_state ON destination_city.uf_id = destination_state.codigo_ibge
    GROUP BY shipper_id
) AS first_order ON first_order.shipper_id = c.id

LEFT JOIN (  -- Getting the last order
    SELECT
        o.id,
        o.order_id,
        o.shipper_id,
        MAX(date_added) AS date_added

    FROM orders_order AS o
    GROUP BY shipper_id
) AS last_order ON last_order.shipper_id = c.id

LEFT JOIN (
    SELECT
        shipper_id,
        COUNT(*) as contagem
    FROM orders_order
    GROUP BY shipper_id
) AS qnt_pedidos ON qnt_pedidos.shipper_id = c.id


WHERE c.user_type = 'shipper'
    AND c.date_joined > '2019-06-01'
    AND c.admin_id IS NOT NULL