from goodnews.db import connect, init_db
from goodnews import queries


def _article(c, aid, *, when):
    c.execute(
        "INSERT INTO articles (id, source_id, canonical_url, title, url_hash, published_at) "
        "VALUES (?, 1, ?, ?, ?, ?)",
        (aid, f"http://s/{aid}", f"T{aid}", f"h{aid}", when),
    )
    c.execute(
        "INSERT INTO article_scores (article_id, accepted, constructive_score) VALUES (?, 1, 5)",
        (aid,),
    )


def test_latest_sorts_strictly_by_recency(tmp_path):
    c = connect(str(tmp_path / "t.db")); init_db(c)
    c.execute("INSERT INTO sources (id, name, feed_url) VALUES (1, 'S', 'http://s/f')")
    # Insert out of order; the dates are what should drive 'latest'.
    _article(c, 1, when="2026-03-01T00:00:00")
    _article(c, 2, when="2026-06-01T00:00:00")  # newest
    _article(c, 3, when="2026-01-01T00:00:00")  # oldest
    c.commit()

    latest = [a["id"] for a in queries.feed(c, sort="latest")]
    assert latest == [2, 1, 3]  # newest → oldest, regardless of insert order


def test_feed_source_id_filters_to_one_source(tmp_path):
    c = connect(str(tmp_path / "t.db")); init_db(c)
    c.execute("INSERT INTO sources (id,name,feed_url) VALUES (1,'A','http://a/f'),(2,'B','http://b/f')")
    for aid, sid in [(1, 1), (2, 2), (3, 1)]:
        c.execute("INSERT INTO articles (id,source_id,canonical_url,title,url_hash,published_at) "
                  "VALUES (?,?,?,?,?,'2026-01-01T00:00:00')", (aid, sid, f"u{aid}", f"T{aid}", f"h{aid}"))
        c.execute("INSERT INTO article_scores (article_id,accepted) VALUES (?,1)", (aid,))
    c.commit()
    ids = {a["id"] for a in queries.feed(c, source_id=1)}
    assert ids == {1, 3}  # only source A
    assert all(a["source_id"] == 1 for a in queries.feed(c, source_id=1))