"""Rewrite every stored reference list through the citation table."""
import json
from sqlalchemy import text
from app.database import SessionLocal
from app.services import citations

db = SessionLocal()
rows = db.execute(text(
    "select id, title, references_json from articles "
    "where references_json is not null and json_array_length(references_json) > 0"
)).fetchall()
changed = dropped = 0
for aid, title, refs in rows:
    if isinstance(refs, str):
        refs = json.loads(refs)
    fixed = citations.tidy(refs, limit=10)
    if fixed == refs:
        continue
    dropped += max(0, len(refs) - len(fixed))
    db.execute(text("update articles set references_json = cast(:r as json) where id = :i"),
               {"r": json.dumps(fixed), "i": aid})
    changed += 1
db.commit()
print(f"{len(rows)} articles with references, {changed} rewritten, {dropped} entries merged or dropped")
db.close()