PLEX MEDIA SERVER: Update the added_at timestamp for each series to the most recently added episode's timestamp for that series.DO NOT RUN THIS UNLESS YOU KNOW WHAT IT DOES, AND BACKUP FIRST. IT WILL ALTER YOUR PLEX DATABASE. IT IS MOSTLY UNTESTED.

update_plex_added_at.sql

BEGIN;

DROP TABLE IF EXISTS tmp_series_episodes;

-- a temp table containing each episode with its cooresponding series
CREATE TEMP TABLE tmp_series_episodes AS
     SELECT series.id series_id,
            series.title series_name,
            series.added_at series_added_at,
            episode.id episode_id,
            episode.title episode_title,
            CASE 
              WHEN episode.available_at IS NOT NULL AND episode.available_at < CURRENT_TIMESTAMP
              THEN episode.available_at
              WHEN episode.originally_available_at IS NOT NULL AND episode.originally_available_at < CURRENT_TIMESTAMP
              THEN episode.originally_available_at
              ELSE parts.created_at
            END episode_preferred_added_at
       FROM metadata_items episode
       JOIN metadata_items season
       JOIN metadata_items series
       JOIN media_parts parts
       JOIN media_items items
      WHERE episode.id = items.metadata_item_id
        AND items.id = parts.media_item_id
        AND season.id = episode.parent_id
        AND series.id = season.parent_id
        AND series.id is not null;

-- a temp table containing each series with its newest episode timestamp
DROP TABLE IF EXISTS tmp_series_with_newest_episode_at;

CREATE TEMP TABLE tmp_series_with_newest_episode_at AS
     SELECT series_id,
           series_name,
           MAX(episode_preferred_added_at) series_newest_episode_at
      FROM tmp_series_episodes
     WHERE episode_preferred_added_at IS NOT NULL
  GROUP BY series_id;
 
-- updates the series added_at to match the latest episodes created_at
DROP TABLE IF EXISTS tmp_series_ids_mismatched;

CREATE TEMP TABLE tmp_series_ids_mismatched AS
     SELECT metadata_items.id
       FROM metadata_items
       JOIN tmp_series_with_newest_episode_at
      WHERE tmp_series_with_newest_episode_at.series_id = metadata_items.id
        AND tmp_series_with_newest_episode_at.series_newest_episode_at <> metadata_items.added_at;

UPDATE metadata_items
   SET added_at = (
     SELECT series_newest_episode_at
       FROM tmp_series_with_newest_episode_at
      WHERE tmp_series_with_newest_episode_at.series_id = metadata_items.id
   )
 WHERE metadata_items.id IN (
   SELECT id
     FROM tmp_series_ids_mismatched
 );

-- updates episode's added_at to current
DROP TABLE IF EXISTS tmp_episode_ids_mismatched;

CREATE TEMP TABLE tmp_episode_ids_mismatched AS
     SELECT metadata_items.id
       FROM metadata_items
       JOIN tmp_series_episodes
      WHERE tmp_series_episodes.episode_id = metadata_items.id
        AND tmp_series_episodes.episode_preferred_added_at <> metadata_items.added_at;

UPDATE metadata_items
   SET added_at = (
     SELECT episode_preferred_added_at
       FROM tmp_series_episodes
      WHERE tmp_series_episodes.episode_id = metadata_items.id
   )
 WHERE metadata_items.id IN (
   SELECT id
     FROM tmp_episode_ids_mismatched
 );

DROP TABLE tmp_series_episodes;
DROP TABLE tmp_series_with_newest_episode_at;
DROP TABLE tmp_series_ids_mismatched;
DROP TABLE tmp_episode_ids_mismatched;

COMMIT;