#!/usr/bin/env python3
"""Date de PUBLICATION du Fil (decision de Sebastien, 18/10/2026) : le Fil est trie et pagine par published_at, distincte de created_at ; elle remonte quand une avancee automatique evolue
(« demarre » devient « termine », un evenement rejoint une publication groupee) ; modifier, aimer, commenter ne la changent pas ; curseur sans trou ni doublon, meme apres une remontee."""
import json, os, re, subprocess, sys
from urllib.parse import quote
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
from lib import ROOT, call, mk_user, ok, reset_rate_limits, sql, summary

def raw_setting(k, v): sql(f"INSERT INTO messaging_settings (setting_key, setting_value) VALUES ('{k}', '{json.dumps(v)}') ON DUPLICATE KEY UPDATE setting_value = '{json.dumps(v)}'")
def unset(k): sql(f"DELETE FROM messaging_settings WHERE setting_key = '{k}'")
def section(t): print('\n■ ' + t); reset_rate_limits()
def feed(who, qs=''): st, d, _ = call('GET', who, '/posts?tab=explorer&limit=50' + qs); assert st == 200, (st, d); return d
def ids(d): return [p['id'] for p in d['items']]
def walk(who, limit, qs=''):
    out, cur, pages = [], '', 0
    while pages < 60:
        st, d, _ = call('GET', who, f'/posts?tab=explorer&limit={limit}{qs}' + (f'&before={cur}' if cur else '')); assert st == 200, (st, d); out += ids(d); pages += 1
        if not d['has_more']: break
        cur = d['next_before']
    return out, pages
def col(pid, c): return sql(f"SELECT {c} FROM posts WHERE id = {pid}")
def mkpost(uid, theme, body, created, published=None, kind='member'):
    return int(sql(f"INSERT INTO posts (user_id, theme_id, body, visibility, created_at, published_at, kind) VALUES ('{uid}', {theme}, '{body}', 'all', '{created}', '{published or created}', '{kind}'); SELECT LAST_INSERT_ID()").split()[-1])      # même connexion : LAST_INSERT_ID est propre à la connexion
def wipe():
    p = "'fp\\_%'"
    sql(f"DELETE FROM mentions WHERE member_id LIKE {p}; DELETE FROM comments WHERE user_id LIKE {p} OR post_id IN (SELECT id FROM posts WHERE user_id LIKE {p})")
    for t in ('post_likes', 'post_saves', 'post_media', 'auto_post_refs'): sql(f"DELETE FROM {t} WHERE post_id IN (SELECT id FROM posts WHERE user_id LIKE {p})")
    sql(f"DELETE FROM post_likes WHERE user_id LIKE {p}; DELETE FROM post_saves WHERE user_id LIKE {p}; DELETE FROM posts WHERE user_id LIKE {p}; DELETE FROM auto_post_refs WHERE user_id LIKE {p}; DELETE FROM notifications WHERE user_id LIKE {p} OR actor_id LIKE {p}")
    sql(f"DELETE FROM actions WHERE user_id LIKE {p}; DELETE FROM notification_prefs WHERE user_id LIKE {p}; DELETE FROM member_follows WHERE follower_id LIKE {p} OR followed_id LIKE {p}; DELETE FROM cards WHERE id LIKE 'TZF-%'")
def php(code): return subprocess.run(['php', '-d', 'display_errors=0', '-r', '$_SERVER["REQUEST_METHOD"]="GET"; require "api/_posts.php"; ' + code], capture_output=True, text=True, cwd=ROOT).stdout.strip()

wipe(); sql("DELETE FROM users WHERE id LIKE 'fp\\_%'"); raw_setting('auto_posts_daily_cap', 3)
for uid, f, l in [('fp_a', 'Ana', 'Alt'), ('fp_b', 'Bob', 'Bel'), ('fp_c', 'Cleo', 'Col')]: mk_user(uid, 'client_essentiel', f, l)
sql("UPDATE users SET created_at = NOW() - INTERVAL 60 DAY WHERE id LIKE 'fp\\_%'")
sql("INSERT IGNORE INTO categories (id, slug, label, sort_order) VALUES ('tzcat', 'tzcat', 'Categorie de test', 1)")
for i in range(1, 6): sql(f"INSERT INTO cards (id, category_id, title, slug, objective, description, difficulty, is_derived) VALUES ('TZF-{i}', 'tzcat', 'Action {i}', 'tzf-{i}', '', '', 2, 0)")
THEME = int(sql("SELECT id FROM post_themes WHERE active = 1 AND system_key IS NULL ORDER BY id LIMIT 1"))
def optin(who, **kw): st, d, _ = call('PUT', who, '/auto-posts/preference', {'share': True, **kw}); assert st == 200, (st, d)
def take(who, c): return call('POST', who, '/my-actions', {'card_id': c, 'status': 'en_cours'})
def finish(who, c): return call('PUT', who, '/my-actions', {'card_id': c, 'status': 'validee'})

section("La colonne et ses règles")
cols = sql("SHOW COLUMNS FROM posts LIKE 'published_at'"); ok('datetime' in cols and 'NO' in cols, 'posts.published_at existe, jamais nulle')
st, d, _ = call('POST', 'fp_a', '/posts', {'body': 'Une publication écrite', 'theme_id': THEME, 'visibility': 'all'}); pid = d['post']['id']
ok(col(pid, 'published_at') == col(pid, 'created_at') and re.fullmatch(r'\d{4}-\d\d-\d\dT\d\d:\d\d:\d\dZ', d['post'].get('published_at', '')) and d['post']['created_at'] == d['post']['published_at'], 'une publication écrite : publiée = créée ; le Fil expose published_at (ISO, UTC) en plus de created_at')
sql(f"UPDATE posts SET created_at = created_at - INTERVAL 3 HOUR, published_at = published_at - INTERVAL 3 HOUR WHERE id = {pid}"); c0, p0 = col(pid, 'created_at'), col(pid, 'published_at')
call('PUT', 'fp_a', f'/posts/{pid}', {'body': 'Texte modifié'}); call('POST', 'fp_b', f'/posts/{pid}/like', {}); call('POST', 'fp_b', f'/posts/{pid}/comments', {'body': 'Joli'}); call('POST', 'fp_c', f'/posts/{pid}/save', {})
ok(col(pid, 'created_at') == c0 and col(pid, 'published_at') == p0, 'modifier, aimer, commenter et enregistrer ne changent NI la date de création NI la date de publication')

section("Une avancée qui évolue remonte en tête")
for c in ('TZF-1', 'TZF-2'): sql(f"DELETE FROM actions WHERE user_id = 'fp_a' AND card_id = '{c}'")
optin('fp_a'); st, d, _ = take('fp_a', 'TZF-1'); X = int(sql("SELECT id FROM posts WHERE user_id = 'fp_a' AND kind = 'auto' ORDER BY id DESC LIMIT 1"))
sql(f"UPDATE posts SET created_at = created_at - INTERVAL 5 HOUR, published_at = published_at - INTERVAL 5 HOUR WHERE id = {X}")
B = mkpost('fp_b', THEME, 'Plus récente', sql("SELECT NOW() - INTERVAL 2 HOUR")); C = mkpost('fp_c', THEME, 'Encore plus récente', sql("SELECT NOW() - INTERVAL 1 HOUR"))
o = ids(feed('fp_b')); ok(o.index(C) < o.index(B) < o.index(X) and o.index(X) > o.index(pid) - 99, 'avant la fin : la plus récente d\'abord, l\'avancée démarrée plus bas')
cx, px = col(X, 'created_at'), col(X, 'published_at'); finish('fp_a', 'TZF-1'); o = ids(feed('fp_b'))
ok(o[0] == X and o.index(C) < o.index(B), 'après « terminé » : l\'avancée REMONTE en tête du Fil, le reste de l\'ordre est inchangé')
ok(col(X, 'created_at') == cx and col(X, 'published_at') > px and col(X, 'auto_event') == 'action_finished', 'sa date de CRÉATION n\'a pas bougé ; sa date de PUBLICATION est celle de la fin')
P = call('GET', 'fp_b', f'/posts/{X}')[1]['post']; ok(P['created_at'] != P['published_at'] and P['published_at'] > P['created_at'], 'l\'API expose les deux dates, différentes')
finish('fp_a', 'TZF-1'); ok(col(X, 'published_at') >= px and ids(feed('fp_b'))[0] == X, 'terminer deux fois : rien de plus (pas de nouvelle remontée)')
st, d, _ = take('fp_a', 'TZF-2'); Y = int(sql("SELECT id FROM posts WHERE user_id = 'fp_a' AND kind = 'auto' AND id <> " + str(X) + " ORDER BY id DESC LIMIT 1")); ok(ids(feed('fp_b'))[0] == Y, 'une nouvelle avancée arrive en tête (date de publication = maintenant)')

section("Publication groupée : un événement de plus la fait remonter")
raw_setting('auto_posts_daily_cap', 1); mk_user('fp_g', 'client_essentiel', 'Gus', 'Gal'); optin('fp_g'); take('fp_g', 'TZF-1'); take('fp_g', 'TZF-2')
G = int(sql("SELECT id FROM posts WHERE user_id = 'fp_g' AND auto_event = 'group_started'")); sql(f"UPDATE posts SET published_at = published_at - INTERVAL 4 HOUR, created_at = created_at - INTERVAL 4 HOUR WHERE user_id = 'fp_g'"); cg, pg = col(G, 'created_at'), col(G, 'published_at')
mkpost('fp_c', THEME, 'Après', sql("SELECT NOW() - INTERVAL 30 MINUTE")); ok(ids(feed('fp_b')).index(G) > 0, 'le groupe est plus bas qu\'une publication récente'); take('fp_g', 'TZF-3'); ok(col(G, 'published_at') > pg and col(G, 'created_at') == cg and ids(feed('fp_b'))[0] == G, 'un événement de plus rejoint le groupe : il remonte en tête, sa date de création ne bouge pas')
raw_setting('auto_posts_daily_cap', 3)

section("Pagination par date de publication")
wipe(); sql("DELETE FROM users WHERE id = 'fp_g'"); mk_user('fp_g', 'client_essentiel', 'Gus', 'Gal'); sql("UPDATE users SET created_at = NOW() - INTERVAL 60 DAY WHERE id LIKE 'fp\\_%'")
for i in range(1, 6): sql(f"INSERT INTO cards (id, category_id, title, slug, objective, description, difficulty, is_derived) VALUES ('TZF-{i}', 'tzcat', 'Action {i}', 'tzf-{i}', '', '', 2, 0)")
base = "2026-06-01 10:00:00"; posts = [mkpost('fp_a' if i % 2 else 'fp_b', THEME, f'Publication {i}', sql(f"SELECT '{base}' + INTERVAL {i} MINUTE")) for i in range(1, 26)]
full = ids(feed('fp_c')); ok(full == sorted(posts, reverse=True) and len(full) == 25, '25 publications : du plus récemment publié au plus ancien')
w, pages = walk('fp_c', 7); ok(w == full and pages == 4, f'pages de 7 : la liste complète ({pages} pages), sans trou ni doublon')
old = posts[2]; sql(f"UPDATE posts SET published_at = '2026-06-01 11:00:00' WHERE id = {old}"); full2 = ids(feed('fp_c'))
ok(full2[0] == old and len(full2) == 25 and sorted(full2) == sorted(posts), 'une publication ANCIENNE remontée en tête (date de publication récente) : première du Fil, aucune ne disparaît')
w, pages = walk('fp_c', 7); ok(w == full2 and len(set(w)) == 25 and w.count(old) == 1, 'parcourir les pages APRÈS la remontée : la liste complète, la publication remontée une seule fois, aucun doublon ni trou')
st, d, _ = call('GET', 'fp_c', '/posts?tab=explorer&limit=7'); ok(bool(re.fullmatch(r'\d{14}-\d+', d['next_before'])) and d['next_before'].endswith('-' + str(d['items'][-1]['id'])) and d['next_before'].startswith(re.sub(r'\D', '', col(d['items'][-1]['id'], 'published_at'))), 'next_before : « AAAAMMJJhhmmss-id » de la dernière publication de la page')
same = "2026-06-01 12:00:00"; t3 = [mkpost('fp_a', THEME, f'Même seconde {i}', same) for i in range(3)]; full3 = ids(feed('fp_c'))
ok(full3[:3] == sorted(t3, reverse=True), 'trois publications de la MÊME seconde : départagées par l\'identifiant (le plus récent d\'abord)')
for lim in (1, 2, 3): w, _ = walk('fp_c', lim); ok(w == full3 and len(set(w)) == 28, f'limite {lim}, avec des dates égales à la frontière des pages : aucune perte, aucun doublon')

section("Curseurs : formats acceptés et refusés")
bad_ok = True
for raw in ('abc', '99999999999999-', '20261301', '-5', '0', '2026-06-01', '20260601100000', "1;DROP TABLE posts", '9' * 40, ''): st, d, _ = call('GET', 'fp_c', f'/posts?tab=explorer&limit=7&before={quote(raw)}'); bad_ok &= (st == 200 and ids(d) == full3[:7])
ok(bad_ok, 'un curseur invalide ou incomplet (10 formes dont une injection SQL) : 200, et la PREMIÈRE page (jamais d\'erreur, jamais de résultat étranger)')
mid = full3[10]; st, d, _ = call('GET', 'fp_c', f'/posts?tab=explorer&limit=50&before={mid}')
ok(st == 200 and ids(d) == full3[11:], 'ancien format : un identifiant seul reste compris (la suite du Fil derrière cette publication)')
sql(f"DELETE FROM posts WHERE id = {mid}"); st, d, _ = call('GET', 'fp_c', f'/posts?tab=explorer&limit=7&before={mid}'); ok(st == 200 and ids(d) == [i for i in full3 if i != mid][:7], 'ancien format : un identifiant disparu → première page')
cur = d['next_before']; st, d2, _ = call('GET', 'fp_c', f'/posts?tab=explorer&limit=7&before={cur}'); sql(f"DELETE FROM posts WHERE id = {d['items'][-1]['id']}"); st, d3, _ = call('GET', 'fp_c', f'/posts?tab=explorer&limit=7&before={cur}'); ok(st == 200 and ids(d3) == ids(d2), 'le curseur par date reste valable même si la publication qu\'il désigne est supprimée entre deux pages')
ok(php('echo json_encode(posts_cursor_parse(db(), "20261018094501-77"));') == '["2026-10-18 09:45:01",77]' and php('echo json_encode(posts_cursor_parse(db(), "zzz"));') == 'null' and php('echo posts_cursor_format(["published_at" => "2026-10-18 09:45:01", "id" => 77]);') == '20261018094501-77', 'analyse et formatage du curseur (aller-retour exact)')

section("Filtres et onglets avec la nouvelle pagination")
raw_setting('auto_posts_daily_cap', 5); optin('fp_a'); take('fp_a', 'TZF-1'); take('fp_a', 'TZF-2'); finish('fp_a', 'TZF-1')
w, _ = walk('fp_c', 4, '&auto=0'); ok(all(call('GET', 'fp_c', f'/posts/{i}')[1]['post']['kind'] == 'member' for i in w[:5]) and len(set(w)) == len(w) and not any(sql(f"SELECT kind FROM posts WHERE id = {i}") == 'auto' for i in w), 'auto=0 : les avancées sont masquées sur toutes les pages, sans doublon')
THA = int(sql("SELECT id FROM post_themes WHERE system_key = 'progress'")); w, _ = walk('fp_c', 1, f'&theme={THA}'); ok(len(w) == 2 and all(sql(f"SELECT kind FROM posts WHERE id = {i}") == 'auto' for i in w) and w == sorted(w, key=lambda i: (col(i, 'published_at'), i), reverse=True), 'le thème « Avancées » : seulement les avancées, pages d\'une, du plus récemment publié au plus ancien')
for i in posts[:6]: call('POST', 'fp_c', f'/posts/{i}/save', {})
out, cur = [], ''
for _ in range(10):
    st, d, _ = call('GET', 'fp_c', '/posts?tab=saved&limit=2' + (f'&before={cur}' if cur else '')); out += ids(d)
    if not d['has_more']: break
    cur = d['next_before']
exp = sorted(posts[:6], key=lambda i: (col(i, 'published_at'), i), reverse=True); ok(out == exp, 'onglet « Enregistrés » : même pagination, par date de publication')

section("Réglage du lecteur : masquer les avancées dans mon Fil")
def hide(who, v=True): st, d, _ = call('PUT', who, '/auto-posts/preference', {'hide_auto': v}); assert st == 200, (st, d); return d
kinds = lambda lst: [sql(f"SELECT kind FROM posts WHERE id = {i}") for i in lst]
w_all, _ = walk('fp_c', 4); n_auto = sum(1 for k in kinds(w_all) if k == 'auto'); ok(n_auto >= 2 and len(w_all) > n_auto, f'sans réglage : le Fil montre les avancées ({n_auto}) et les publications écrites')
w_b, _ = walk('fp_b', 4); hide('fp_c'); w_c, pages = walk('fp_c', 4)
ok(len(w_c) == len(w_all) - n_auto and 'auto' not in kinds(w_c) and len(set(w_c)) == len(w_c) and w_c == [i for i in w_all if i in w_c], f'réglage activé : plus aucune avancée ({n_auto} en moins), sur toutes les pages, sans doublon, l\'ordre des autres est inchangé')
w_b2, _ = walk('fp_b', 4); ok(w_b2 == w_b, 'le réglage est PERSONNEL : un autre créateur voit toujours les avancées')
st, d, _ = call('GET', 'fp_c', '/posts?tab=explorer&limit=50&auto=1'); ok(sum(1 for k in kinds(ids(d)) if k == 'auto') == n_auto, 'auto=1 (explicite) les montre quand même : le paramètre l\'emporte sur le réglage')
st, d, _ = call('GET', 'fp_b', '/posts?tab=explorer&limit=50&auto=0'); ok('auto' not in kinds(ids(d)), 'auto=0 (explicite) continue de fonctionner pour qui le demande')
THA2 = int(sql("SELECT id FROM post_themes WHERE system_key = 'progress'")); ok(walk('fp_c', 4, f'&theme={THA2}')[0] == [] and len(walk('fp_b', 4, f'&theme={THA2}')[0]) == n_auto, 'le thème « Avancées » est vide pour qui masque, plein pour les autres')
av = [i for i in w_all if sql(f"SELECT kind FROM posts WHERE id = {i}") == 'auto'][0]; call('POST', 'fp_c', f'/posts/{av}/save', {}); out, cur = [], ''
for _ in range(5):
    st, d, _ = call('GET', 'fp_c', '/posts?tab=saved&limit=2' + (f'&before={cur}' if cur else '')); out += ids(d)
    if not d['has_more']: break
    cur = d['next_before']
ok(av not in out, 'onglet « Enregistrés » : l\'avancée enregistrée n\'y figure pas non plus tant que le réglage est actif')
hide('fp_c', False); ok(len(walk('fp_c', 4)[0]) == len(w_all), 'réglage retiré : les avancées reviennent, toutes')
d = hide('fp_c'); ok(d['hide_auto'] is True and d['share'] is None, 'le réglage de lecture est indépendant de l\'accord de publier (fp_c n\'a jamais répondu)'); hide('fp_c', False)

wipe(); sql("DELETE FROM users WHERE id LIKE 'fp\\_%'"); sql("DELETE FROM categories WHERE id = 'tzcat'")
for k in ('auto_posts_daily_cap',): unset(k)
summary()