CREATE VIEW moorage_view AS (
 WITH stay_details AS (
         SELECT s.moorage_id,
            s.arrived,
            s.departed,
            s.duration,
            s.id AS stay_id,
            first_value(s.id) OVER (PARTITION BY s.moorage_id ORDER BY s.arrived) AS first_seen_id,
            first_value(s.id) OVER (PARTITION BY s.moorage_id ORDER BY s.departed DESC) AS last_seen_id
           FROM api.stays s
          WHERE ((s.vessel_id = current_setting('vessel.id'::text, true)) AND (s.active = false))
        ), stay_summary AS (
         SELECT stay_details.moorage_id,
            min(stay_details.arrived) AS first_seen,
            max(stay_details.departed) AS last_seen,
            sum(stay_details.duration) AS total_duration,
            count(*) AS stay_count,
            max(stay_details.first_seen_id) AS first_seen_id,
            max(stay_details.last_seen_id) AS last_seen_id
           FROM stay_details
          GROUP BY stay_details.moorage_id
        ), log_summary AS (
         SELECT logs.moorage_id,
            count(DISTINCT logs.id) AS log_count
           FROM ( SELECT l_1._from_moorage_id AS moorage_id,
                    l_1.id
                   FROM api.logbook l_1
                  WHERE ((l_1.vessel_id = current_setting('vessel.id'::text, true)) AND (l_1.active = false))
                UNION ALL
                 SELECT l_1._to_moorage_id AS moorage_id,
                    l_1.id
                   FROM api.logbook l_1
                  WHERE ((l_1.vessel_id = current_setting('vessel.id'::text, true)) AND (l_1.active = false))) logs
          GROUP BY logs.moorage_id
        )
 SELECT m.id,
    m.name,
    (ARRAY['Unknown'::text, 'Anchor'::text, 'Mooring Buoy'::text, 'Dock'::text])[m.stay_code] AS default_stay,
    m.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,
        CASE
            WHEN (jsonb_array_length((m.user_data -> 'images'::text)) > 0) THEN true
            ELSE false
        END AS has_images,
    (m.user_data -> 'images'::text) AS images
   FROM ((api.moorages m
     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
)