CREATE OR REPLACE VIEW public.unmapped_delivery_notes AS
WITH
     shipment_info AS (
         SELECT shipment_delivery_notes.delivery_note_id,
            max(shipment_status.slug) FILTER (WHERE shipment_status.slug = 'dissolved'::text) AS has_dissolved,
            count(*) FILTER (WHERE shipment_status.slug <> 'dissolved'::text) AS other_status_count
           FROM shipment_delivery_notes
             JOIN shipment_status ON shipment_delivery_notes.shipment_status_id = shipment_status.id
          GROUP BY shipment_delivery_notes.delivery_note_id
        )
SELECT delivery_notes.uuid AS delivery_note_uuid,
    delivery_notes.id AS delivery_note_id,
    delivery_notes.delivery_note_number,
    count(delivery_note_line_items.id) AS no_of_items,
    user_details.first_name AS customer_name,
    customers_delivery_addresses.delivery_location,
    delivery_notes.reference_number,
    delivery_notes.date_of_issue,
    customers_delivery_addresses.id AS customers_delivery_addresses_id,
    delivery_notes.company_id,
    delivery_notes.status_id,
	user_details.user_id AS customer_id
   FROM delivery_notes
     LEFT JOIN shipment_info ON delivery_notes.id = shipment_info.delivery_note_id
     JOIN customers_delivery_addresses ON customers_delivery_addresses.id = delivery_notes.customer_delivery_address_id
     JOIN users ON users.id = delivery_notes.customer_id
     JOIN user_details ON users.id = user_details.user_id
     LEFT JOIN delivery_note_line_items ON delivery_notes.id = delivery_note_line_items.delivery_note_id
  WHERE delivery_notes.company_id = 1 AND delivery_notes.status_id <> 3 AND (shipment_info.delivery_note_id IS NULL OR shipment_info.has_dissolved IS NOT NULL AND shipment_info.other_status_count = '0'::bigint)
  GROUP BY delivery_notes.id, delivery_notes.uuid, delivery_notes.delivery_note_number, customers_delivery_addresses.id, user_details.user_id, user_details.first_name;