-- View: public.shipment_timeline_view

DROP VIEW IF EXISTS shipment_timeline_view;


CREATE OR REPLACE VIEW public.shipment_timeline_view AS

SELECT 
    vth.id,
    vth.company_id,
    vth.shipment_id,
    vth.status_id,
    NULL::text AS zone_name,
    NULL::text AS zone_category,
    vth.trip_type AS state,
    vth.gps_timestamp AS date,
    concat(vth.latitude, ',', vth.longitude) AS latitude_longitude,
    vth.shipment_location_uid,
    cda.delivery_location
FROM vehicle_trip_histories vth
LEFT JOIN shipment_location sl 
    ON sl.shipment_location_uid = vth.shipment_location_uid
LEFT JOIN customers_delivery_addresses cda 
    ON cda.id = sl.customer_address_id

UNION ALL

SELECT 
    vgh.id,
    vgh.company_id,
    vgh.shipment_id,
    vgh.status_id,
    g.zone_name::text AS zone_name,
    g.category::text AS zone_category,
    vgh.event_type::text AS state,
    vgh.event_timestamp AS date,
    concat(vgh.latitude, ',', vgh.longitude) AS latitude_longitude,
    sl.shipment_location_uid,
    cda.delivery_location
FROM vehicle_geofence_history vgh
LEFT JOIN geofences g 
    ON g.id = vgh.geofence_id
LEFT JOIN shipment_location sl 
    ON sl.shipment_id = vgh.shipment_id
LEFT JOIN customers_delivery_addresses cda 
    ON cda.id = sl.customer_address_id;

ALTER TABLE public.shipment_timeline_view
    OWNER TO postgres;

    