WITH episode_ AS (...), -- stale episodes
s_ AS (
SELECT
e.feed_id,
s.card_id,
MAX(e.created_at) AS last_created_at,
AVG(COALESCE(s.score::INT::REAL, 0.5)) AS _avg,
COUNT(s.score) AS _count
FROM score s
INNER JOIN episode e ON s.episode_id = e.episode_id
INNER JOIN feed f ON e.feed_id = f.feed_id
WHERE f.usr_id IS NOT NULL
GROUP BY e.feed_id, s.card_id
)
INSERT INTO episode (episode_id, feed_id, card_ids)
SELECT LEFT(MD5(RANDOM()::TEXT), 16), feed_id, ARRAY_AGG(card_id)
FROM (
SELECT feed_id, card_id
FROM (
SELECT
feed_id,
card_id,
per_episode,
ROW_NUMBER() OVER (
PARTITION BY feed_id
ORDER BY
COALESCE(s_.last_created_at, c.created_at, NOW())
+ f.every * RANDOM() * COALESCE(fd.chaos, 0.3)
+ f.every * COALESCE(s_._count, 7) ^ COALESCE(fd.deceleration, 0.7) * s_._avg ^ COALESCE(fd.feedback, 1.2)
+ f.every * LN(1 + c.bytes_b) * COALESCE(fd.ease, 1.0),
RANDOM()
) AS n
FROM episode_ e_
INNER JOIN feed f USING (feed_id)
INNER JOIN feed_deck fd USING (feed_id)
INNER JOIN card c USING (deck_id)
LEFT JOIN s_ ON s_.feed_id = e_.feed_id AND s_.card_id = c.card_id
WHERE NOT c.card_id = ANY(fd.hidden_card_ids)
) a
WHERE per_episode >= n
) b
GROUP BY feed_id;
