CREATE VIEW moorages_view AS (
 WITH logbook_counts AS (
         SELECT logbook._from_moorage_id AS moorage_id,
            count(*) AS entries
           FROM api.logbook
          WHERE ((logbook.vessel_id = current_setting('vessel.id'::text, true)) AND (logbook._from_moorage_id IS NOT NULL) AND (logbook.active = false))
          GROUP BY logbook._from_moorage_id
        UNION ALL
         SELECT logbook._to_moorage_id AS moorage_id,
            count(*) AS entries
           FROM api.logbook
          WHERE ((logbook.vessel_id = current_setting('vessel.id'::text, true)) AND (logbook._to_moorage_id IS NOT NULL) AND (logbook.active = false))
          GROUP BY logbook._to_moorage_id
        ), logbook_total AS (
         SELECT logbook_counts.moorage_id,
            sum(logbook_counts.entries) AS total_arrivals_departures
           FROM logbook_counts
          GROUP BY logbook_counts.moorage_id
        ), stays_agg AS (
         SELECT stays.moorage_id,
            sum(stays.duration) AS total_duration
           FROM api.stays
          WHERE ((stays.vessel_id = current_setting('vessel.id'::text, true)) AND (stays.active = false))
          GROUP BY stays.moorage_id
        )
 SELECT m.id,
    m.name AS moorage,
    sa.description AS default_stay,
    sa.stay_code AS default_stay_id,
    COALESCE(lt.total_arrivals_departures, (0)::numeric) AS arrivals_departures,
    COALESCE(st.total_duration, 'PT0S'::interval) AS total_duration
   FROM (((api.moorages m
     JOIN api.stays_at sa ON ((m.stay_code = sa.stay_code)))
     LEFT JOIN logbook_total lt ON ((lt.moorage_id = m.id)))
     LEFT JOIN stays_agg st ON ((st.moorage_id = m.id)))
  WHERE ((m.vessel_id = current_setting('vessel.id'::text, true)) AND (m.geog IS NOT NULL))
  ORDER BY COALESCE(st.total_duration, 'PT0S'::interval) DESC
)