CREATE VIEW moorage_view AS (
 WITH stay_summary AS (
         SELECT s.moorage_id,
            min(s.arrived) AS first_seen,
            max(s.departed) AS last_seen,
            sum(s.duration) AS total_duration,
            count(*) AS stay_count,
            min(s.id) FILTER (WHERE (s.arrived = ( SELECT min(stays.arrived) AS min
                   FROM api.stays
                  WHERE ((stays.moorage_id = s.moorage_id) AND (stays.vessel_id = current_setting('vessel.id'::text, true)) AND (stays.active = false))))) AS first_seen_id,
            max(s.id) FILTER (WHERE (s.departed = ( SELECT max(stays.departed) AS max
                   FROM api.stays
                  WHERE ((stays.moorage_id = s.moorage_id) AND (stays.vessel_id = current_setting('vessel.id'::text, true)) AND (stays.active = false))))) AS last_seen_id
           FROM api.stays s
          WHERE ((s.vessel_id = current_setting('vessel.id'::text, true)) AND (s.active = false))
          GROUP BY s.moorage_id, s.id, s.arrived, s.departed
        ), log_summary AS (
         SELECT logs.moorage_id,
            count(DISTINCT logs.id) AS log_count
           FROM ( SELECT logbook._from_moorage_id AS moorage_id,
                    logbook.id
                   FROM api.logbook
                  WHERE ((logbook.vessel_id = current_setting('vessel.id'::text, true)) AND (logbook.active = false) AND (logbook._from_moorage_id IS NOT NULL))
                UNION ALL
                 SELECT logbook._to_moorage_id AS moorage_id,
                    logbook.id
                   FROM api.logbook
                  WHERE ((logbook.vessel_id = current_setting('vessel.id'::text, true)) AND (logbook.active = false) AND (logbook._to_moorage_id IS NOT NULL))) logs
          GROUP BY logs.moorage_id
        )
 SELECT m.id,
    m.name,
    sa.description AS default_stay,
    sa.stay_code AS default_stay_id,
    m.notes,
    m.home_flag AS home,
    m.geog,
    m.latitude,
    m.longitude,
    COALESCE(l.log_count, (0)::bigint) AS logs_count,
    COALESCE(ss.stay_count, (0)::bigint) AS stays_count,
    COALESCE(ss.total_duration, 'PT0S'::interval) AS stays_sum_duration,
    ss.first_seen AS stay_first_seen,
    ss.last_seen AS stay_last_seen,
    ss.first_seen_id AS stay_first_seen_id,
    ss.last_seen_id AS stay_last_seen_id,
    (jsonb_array_length((m.user_data -> 'images'::text)) > 0) AS has_images,
    (m.user_data -> 'images'::text) AS images
   FROM (((api.moorages m
     JOIN api.stays_at sa ON ((m.stay_code = sa.stay_code)))
     LEFT JOIN stay_summary ss ON ((m.id = ss.moorage_id)))
     LEFT JOIN log_summary l ON ((m.id = l.moorage_id)))
  WHERE ((m.vessel_id = current_setting('vessel.id'::text, true)) AND (m.geog IS NOT NULL))
  ORDER BY ss.total_duration DESC
)