Get the statuses for a user's active tv shows and sort them by next unwatched episode

user_status.sql

WITH episodes as (
  select

  _status.id, 
  user_id,
  show_id,
  episode->>'id' episode_id,
  _show.remote_show->>'SeriesName' series_name,
  episode->>'SeasonNumber' season_index,
  episode->>'EpisodeNumber' episode_index,

  episode->>'FirstAired' first_aired,

  to_timestamp(concat(episode->>'FirstAired', 
  replace(replace(_show.remote_show->>'Airs_Time', 'p.m',  'PM'), 'a.m', 'AM')
  ), 'YYYY-MM-DDHH24:MI AM') air_date

  from statuses _status

  join shows _show on _status.show_id = _show.id,
  jsonb_array_elements(_show.all_episode_data) episode

  where episode ->> 'id' != all(watched_ids)
  and episode->>'FirstAired' is not null
  and _status.active = true 
  and cast(episode->>'SeasonNumber' as integer) >= 1

  order by air_date ASC
)

SELECT

statuses.id,
statuses.user_id,
statuses.show_id,
statuses.watched_id tvdb_id,
statuses.active,

shows.remote_show->>'SeriesName' AS series_name,

(shows.all_banner_data -> 0) ->> 'BannerPath'::text AS banner,
('/uploads/image/file/' || image.id || '/' || image.file) fanart,

shows.remote_show->>'Status' AS status,

(
  select air_date from episodes episode
  where episode.show_id = shows.id
  and episode.user_id = statuses.user_id
  limit 1
) < CURRENT_TIMESTAMP  behind,

(
  select air_date from episodes episode
  where episode.show_id = shows.id
  and episode.user_id = statuses.user_id
  limit 1
) AS next_episode,

statuses.created_at,
statuses.updated_at

FROM statuses

JOIN shows ON statuses.show_id = shows.id
LEFT JOIN images image ON image.id = (
  SELECT images.id
  FROM images
  WHERE images.owner_type::text = 'Show'::text AND images.owner_id = shows.id AND images.source::text ~~ concat('%', (shows.all_banner_data -> 0) ->> 'BannerPath'::text)
  LIMIT 1
)
where user_id = 1 and active = true