"""SQLite CRUD round-trip tests."""
from backend.app.db import database as db
from backend.app.models import IndexStatus


def test_insert_and_get_video(tmp_data_root):
    db.insert_video("vid1", "movie.mp4", "/tmp/movie.mp4")
    row = db.get_video("vid1")
    assert row is not None
    assert row["filename"] == "movie.mp4"
    assert row["status"] == IndexStatus.PENDING.value


def test_update_status_and_duration(tmp_data_root):
    db.insert_video("vid1", "a.mp4", "/x")
    db.update_video_status("vid1", IndexStatus.INDEXED, duration=42.5)
    row = db.get_video("vid1")
    assert row["status"] == IndexStatus.INDEXED.value
    assert row["duration_seconds"] == 42.5


def test_segments_round_trip_and_cascade_delete(tmp_data_root):
    db.insert_video("v1", "a.mp4", "/x")
    db.insert_segments([
        ("s1", "v1", 0.0, 5.0, "hello"),
        ("s2", "v1", 5.0, 10.0, "world"),
    ])
    rows = db.get_segments_by_ids(["s1", "s2"])
    texts = sorted(r["text"] for r in rows)
    assert texts == ["hello", "world"]

    # listing should aggregate segment_count
    listed = db.list_videos()
    assert listed[0]["segment_count"] == 2

    # delete cascades to segments
    db.delete_video("v1")
    assert db.get_video("v1") is None
    assert db.get_segments_by_ids(["s1", "s2"]) == []


def test_get_segments_by_empty_list_returns_empty(tmp_data_root):
    assert db.get_segments_by_ids([]) == []