kemono2/db/migrations/20230930_00_TNd34a-add-created-timestamp-to-favs.py

"""
Add timestamp to faves
"""

from yoyo import step

__depends__ = {"20230909_00_AUS5i6-new-index-for-post-get-prev-next"}

steps = [
    step("""ALTER TABLE public.account_post_favorite ADD COLUMN created_at timestamp null;"""),
    step("""ALTER TABLE public.account_post_favorite ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;"""),
    step("""ALTER TABLE public.account_artist_favorite ADD COLUMN created_at timestamp null;"""),
    step("""ALTER TABLE public.account_artist_favorite  ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;"""),
]


"""
run these by hand optionally

WITH RollingMaxDates AS (
  SELECT
    apf.id,
    MAX(p.added) OVER (ORDER BY apf.id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rolling_max_date
  FROM public.account_post_favorite apf
  LEFT JOIN public.posts p ON apf.service = p.service AND apf.post_id = p.id
),

RollingMaxDatesMinMaxIds AS (
  SELECT
    MIN(RollingMaxDates.id) AS min_id,
    MAX(RollingMaxDates.id) AS max_id,
    rolling_max_date
  FROM RollingMaxDates
  GROUP BY rolling_max_date
  ORDER BY min_id ASC
),

DurationPerIdPerRange AS (
  SELECT
    a.min_id AS first_id_for_date_range,
    a.max_id AS last_id_for_date_range,
    a.rolling_max_date AS first_rolling_max_date,
    MIN(b.rolling_max_date) AS last_rolling_max_date,
    (MIN(b.rolling_max_date) - a.rolling_max_date) / GREATEST(a.max_id - a.min_id, 1) AS duration_per_id
  FROM RollingMaxDatesMinMaxIds a
  LEFT JOIN RollingMaxDatesMinMaxIds b ON a.max_id < b.min_id
  GROUP BY a.min_id, a.max_id, a.rolling_max_date
  ORDER BY a.min_id
)

UPDATE public.account_post_favorite apf
SET created_at = data_to_insert.created_at
FROM (
	SELECT
	  apf.id,
	  dpipr.first_rolling_max_date + dpipr.duration_per_id * (apf.id - dpipr.first_id_for_date_range) AS created_at
	FROM public.account_post_favorite apf
	LEFT JOIN DurationPerIdPerRange dpipr ON apf.id >= dpipr.first_id_for_date_range AND apf.id <= dpipr.last_id_for_date_range
) as data_to_insert
WHERE apf.id = data_to_insert.id;



WITH RollingMaxDates AS (
  SELECT
    aaf.id,
    MAX(l.indexed) OVER (ORDER BY aaf.id ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rolling_max_date
  FROM public.account_artist_favorite aaf 
  LEFT JOIN public.lookup l  ON aaf.service = l.service AND aaf.artist_id = l.id 
),

RollingMaxDatesMinMaxIds AS (
  SELECT
    MIN(RollingMaxDates.id) AS min_id,
    MAX(RollingMaxDates.id) AS max_id,
    rolling_max_date
  FROM RollingMaxDates
  GROUP BY rolling_max_date
  ORDER BY min_id ASC
),

DurationPerIdPerRange AS (
  SELECT
    a.min_id AS first_id_for_date_range,
    a.max_id AS last_id_for_date_range,
    a.rolling_max_date AS first_rolling_max_date,
    MIN(b.rolling_max_date) AS last_rolling_max_date,
    (MIN(b.rolling_max_date) - a.rolling_max_date) / GREATEST(a.max_id - a.min_id, 1) AS duration_per_id
  FROM RollingMaxDatesMinMaxIds a
  LEFT JOIN RollingMaxDatesMinMaxIds b ON a.max_id < b.min_id
  GROUP BY a.min_id, a.max_id, a.rolling_max_date
  ORDER BY a.min_id
)


UPDATE public.account_artist_favorite aaf
SET created_at = data_to_insert.created_at
FROM (
	SELECT
	  aaf.id,
	  dpipr.first_rolling_max_date + dpipr.duration_per_id * (aaf.id - dpipr.first_id_for_date_range) AS created_at
	FROM public.account_artist_favorite  aaf
	LEFT JOIN DurationPerIdPerRange dpipr ON aaf.id >= dpipr.first_id_for_date_range AND aaf.id <= dpipr.last_id_for_date_range
) as data_to_insert
WHERE aaf.id = data_to_insert.id;



"""