import db


def test_db_roundtrip(tmp_path):
    database_path = tmp_path / "test.db"
    database_url = f"sqlite+pysqlite:///{database_path}"
    db.configure_database(database_url)
    db.init_db()

    with db.session_scope() as session:
        user = db.ensure_user(session, "TestUser")
        db.create_score(
            session,
            user=user,
            image_id="image_1",
            score_value=777,
            similarity=0.42,
            text="Ein Testeintrag",
        )
        user_id = user.id

    with db.session_scope() as session:
        same_user = db.ensure_user(session, "testuser")
        assert same_user.id == user_id
        global_scores = db.get_global_top(session)
        assert len(global_scores) == 1
        assert global_scores[0].score == 777
        image_scores = db.get_image_top(session, "image_1")
        assert len(image_scores) == 1
        user_scores = db.get_user_recent(session, db.normalize_username("TESTUSER"))
        assert len(user_scores) == 1
        rows = db.scores_to_rows(global_scores)
        assert rows[0][0] == 1  # Rang
        assert rows[0][1] == "TestUser"
        assert rows[0][2] == "image_1"
        assert rows[0][3] == 777
        assert rows[0][4] == round(0.42, 4)
        assert "Ein Testeintrag" in rows[0][5]