Skip to content

The OSINT investigator's workbench. One case, one folder, every tool. Local-first: your media and your investigations never leave your machine.

JavaScript
36.0 KB
Raw Blame
"""SQLite backend and JSON->SQLite converter (Step 3 of
docs/STORAGE_AND_PERFORMANCE.md).

The graph contract itself is covered by `tests/test_repository.py`, which runs
the same suite against both `Case` and `SqliteCase`. This file pins the
store-specific behaviour the contract can't reach: create/open, forward-compat
refusal, foreign-key enforcement, transaction rollback, and the atomic converter
that must never leave a half-built database or touch the legacy `case.json`.
"""

from __future__ import annotations

import sqlite3
import threading
from contextlib import closing

import pytest
from bigcase import build_big_case
import schema_rewind
from legacy_case import legacy_manifest_path, read_legacy_manifest, write_legacy_json_case

from azimut import layout
from azimut.sqlite_backend import SQLITE_SCHEMA, SqliteCase, convert_json_to_sqlite
from azimut.workspace import CaseError


def _entity(eid, **over):
    ent = {
        "id": eid,
        "type": "person",
        "label": eid,
        "attrs": {},
        "provenance": {"by": "user", "at": "2026-01-01T00:00:00Z", "status": "confirmed"},
    }
    ent.update(over)
    return ent


def _json_case(**over):
    data = {
        "azimut": {"schema": 2},
        "name": "Legacy",
        "created_at": "2026-01-01T00:00:00Z",
        "updated_at": "2026-01-02T00:00:00Z",
        "folders": [],
        "entities": [],
        "links": [],
    }
    data.update(over)
    return data


# -- create / open ---------------------------------------------------------


def test_create_then_open_roundtrips(tmp_path):
    store = SqliteCase.create(tmp_path / "case.db", name="Contract")
    e = store.add_entity("person", "Ada", {"handle": "@ada"}, by="user")

    reopened = SqliteCase.open(tmp_path / "case.db")
    assert reopened.get_entity(e["id"]) == e
    assert reopened.snapshot()["name"] == "Contract"


def test_create_refuses_existing_db(tmp_path):
    SqliteCase.create(tmp_path / "case.db", name="One")
    with pytest.raises(CaseError, match="already exists"):
        SqliteCase.create(tmp_path / "case.db", name="Two")


def test_open_missing_db_raises(tmp_path):
    with pytest.raises(CaseError, match="not found"):
        SqliteCase.open(tmp_path / "nope.db")


def test_open_refuses_newer_schema(tmp_path):
    db = tmp_path / "case.db"
    SqliteCase.create(db, name="From the future")
    with closing(sqlite3.connect(db)) as conn, conn:
        conn.execute("UPDATE meta SET value = ? WHERE key = 'schema_version'", ("99",))
    with pytest.raises(CaseError, match="newer Azimut"):
        SqliteCase.open(db)


# -- database behaviour ----------------------------------------------------


def test_foreign_keys_forbid_a_dangling_link(tmp_path):
    """A raw insert bypassing add_entity validation still can't dangle: the FK
    pragma is on for every connection."""
    db = tmp_path / "case.db"
    store = SqliteCase.create(db, name="FK")
    a = store.add_entity("person", "A", by="user")
    with store._connect() as conn:  # exercising the connection policy directly
        with pytest.raises(sqlite3.IntegrityError):
            conn.execute(
                "INSERT INTO links"
                "(id, from_id, to_id, type, prov_by, prov_at, prov_status)"
                " VALUES('l_x', ?, 'ghost', 'owns', 'user', '2026', 'confirmed')",
                (a["id"],),
            )


def test_write_rolls_back_on_error(tmp_path):
    store = SqliteCase.create(tmp_path / "case.db", name="Rollback")
    store.add_entity("person", "A", by="user")

    def boom(conn):
        conn.execute(
            "INSERT INTO entities(id, type, label, prov_by, prov_at)"
            " VALUES('e_x', 'person', 'B', 'user', '2026')"
        )
        raise RuntimeError("mid-transaction failure")

    with pytest.raises(RuntimeError):
        store._write(boom)  # exercising the transaction helper directly

    # the aborted insert left no trace
    assert store.get_entity("e_x") is None
    assert len(store.list_entities()) == 1


_SCHEMA_V1 = """
CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE schema_migrations (version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL);
CREATE TABLE entities (
    id TEXT PRIMARY KEY, type TEXT NOT NULL, label TEXT NOT NULL,
    attrs_json TEXT NOT NULL DEFAULT '{}',
    prov_by TEXT NOT NULL, prov_at TEXT NOT NULL,
    prov_status TEXT NOT NULL DEFAULT 'confirmed', prov_source TEXT
);
CREATE TABLE links (
    id TEXT PRIMARY KEY, from_id TEXT NOT NULL REFERENCES entities(id),
    to_id TEXT NOT NULL REFERENCES entities(id), type TEXT NOT NULL,
    prov_by TEXT NOT NULL, prov_at TEXT NOT NULL,
    prov_status TEXT NOT NULL DEFAULT 'confirmed', prov_source TEXT
);
CREATE TABLE folders (path TEXT PRIMARY KEY);
INSERT INTO meta(key, value) VALUES('schema_version', '1');
INSERT INTO entities(id, type, label, attrs_json, prov_by, prov_at)
    VALUES('e1', 'media', 'm', '{"folder": "Sources/Telegram"}', 'user', '2026');
INSERT INTO entities(id, type, label, attrs_json, prov_by, prov_at)
    VALUES('e2', 'media', 'loose', '{}', 'user', '2026');
"""


def test_open_upgrades_a_v1_db_through_every_migration(tmp_path):
    """A schema-1 case.db is upgraded on open through the whole chain: the folder
    column is added and backfilled (1->2), the jobs table is created (2->3),
    search/media browse indexes arrive in schema 4, the position flag in 5, the
    trash journal in 6, its recovery state in 7, link confidence in 8, the
    search-index rebuild in 9, the graph's hand-placed nodes, one arrangement
    per lens, in 10, what kind of tie an edge states in 11, entity photo
    galleries in 12, saved analysis views in 13, and the indexes the catalog
    orders the whole case by in 14. A second open applies nothing."""
    db = tmp_path / "case.db"
    with closing(sqlite3.connect(db)) as conn, conn:
        conn.executescript(_SCHEMA_V1)

    # runs 1 -> 2 -> ... -> the current schema in place. The media directory is
    # passed because the backend never guesses the case layout; it is empty
    # here, so the schema-4 backfill marks itself done with no rows.
    store = SqliteCase.open(db, media_dir=tmp_path / "media")

    # 1 -> 2: the folder column is backfilled and pages by folder.
    assert [e["id"] for e in store.page_entities(folder="Sources/Telegram")["items"]] == ["e1"]
    assert [e["id"] for e in store.page_entities(unfiled=True)["items"]] == ["e2"]
    # 2 -> 3: the durable jobs table exists and works.
    store.enqueue_job("thumbnail", key="media/x.jpg")
    assert store.count_jobs() == {"queued": 1}
    with closing(sqlite3.connect(db)) as conn, conn:
        assert conn.execute(
            "SELECT value FROM meta WHERE key='schema_version'"
        ).fetchone()[0] == str(SQLITE_SCHEMA)
        applied = {
            r[0] for r in conn.execute("SELECT version FROM schema_migrations").fetchall()
        }
        assert set(range(2, SQLITE_SCHEMA + 1)) <= applied
        columns = {
            row[1] for row in conn.execute("PRAGMA table_info(entities)").fetchall()
        }
        assert "search_text" in columns
        assert conn.execute(
            "SELECT value FROM meta WHERE key='media_index_ready'"
        ).fetchone()[0] == "1"
        # 5 -> 6: the trash journal is there, and empty.
        assert store.trash_summary() == {"groups": 0, "items": 0, "size_bytes": 0}
        # 6 -> 7: interrupted moves have a durable recovery state.
        trash_columns = {
            row[1] for row in conn.execute("PRAGMA table_info(trash)").fetchall()
        }
        assert "state" in trash_columns
        # 7 -> 8: an edge can carry how sure the analyst is.
        link_columns = {
            row[1] for row in conn.execute("PRAGMA table_info(links)").fetchall()
        }
        assert "confidence" in link_columns
        # 10 -> 11: an edge can say what kind of tie it states. Nothing to backfill —
        # every edge filed before this is unqualified, which is what a null says.
        assert "nature" in link_columns
        assert not conn.execute("SELECT 1 FROM links WHERE nature IS NOT NULL").fetchall()
        # 9 -> 10: the graph can remember where a node was dragged to, per lens, and
        # a case that never could reads as one nobody has arranged.
        assert store.graph_pins("all") == {}
        pin_columns = {
            row[1] for row in conn.execute("PRAGMA table_info(graph_pins)").fetchall()
        }
        assert "lens" in pin_columns
        # 11 -> 12: galleries hold private photos or existing Media references.
        image_columns = {
            row[1] for row in conn.execute("PRAGMA table_info(entity_images)").fetchall()
        }
        assert image_columns == {
            "entity_id",
            "image_id",
            "media_id",
            "path",
            "thumbnail",
            "title",
            "position",
            "is_primary",
        }
        # 12 -> 13: named Board/Graph readings are case-owned rows. The count lets
        # their bounded menu avoid parsing snapshot JSON.
        view_columns = {
            row[1] for row in conn.execute("PRAGMA table_info(analysis_views)").fetchall()
        }
        assert view_columns == {
            "id", "name", "mode", "surface", "spec_json", "snapshot_count",
            "created_at", "updated_at",
        }

    SqliteCase.open(db, media_dir=tmp_path / "media")  # idempotent — applies nothing
    with closing(sqlite3.connect(db)) as conn, conn:
        for version in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13):
            assert conn.execute(
                "SELECT COUNT(*) FROM schema_migrations WHERE version = ?", (version,)
            ).fetchone()[0] == 1


def _rewind_to_schema_7(db):
    schema_rewind.rewind(db, 7)


def test_a_real_schema_7_case_keeps_every_link_through_the_confidence_migration(tmp_path):
    """The upgrade people will actually run when they download the next release: a
    case built by the shipped code, migrated in place. Every edge survives with its
    id, type and provenance, and none of them acquires a rating — an existing link
    reads as *not assessed*, which is the truth about it.

    Written as a real case rather than a hand-authored schema-7 dump so it exercises
    the same `ALTER TABLE` the shipped binary will run, whatever the platform: the
    step is one SQL statement in one transaction, with no file moved or replaced, so
    it carries none of the Windows rename-not-delete hazards a file swap would.
    """
    db = tmp_path / "case.db"
    store = SqliteCase.create(db, name="Before the upgrade")
    a = store.add_entity("media", "clip.mp4", by="user")["id"]
    b = store.add_entity("place", "the quay", by="user")["id"]
    edge = store.add_link(a, b, "located-at", by="user")
    suggested = store.add_link(a, b, "depicts", by="enrich", status="suggested")
    _rewind_to_schema_7(db)

    reopened = SqliteCase.open(db, media_dir=tmp_path / "media")

    links = {link["id"]: link for link in reopened.list_links()}
    assert set(links) == {edge["id"], suggested["id"]}
    assert links[edge["id"]]["type"] == "located-at"
    assert links[edge["id"]]["provenance"]["status"] == "confirmed"
    # the enrichment suggestion is still awaiting review: the two axes are separate
    assert links[suggested["id"]]["provenance"]["status"] == "suggested"
    assert links[suggested["id"]]["provenance"]["by"] == "enrich"
    # absent, not null: no reader has to tell unevaluated from rated-as-nothing
    for link in links.values():
        assert "confidence" not in link


def test_the_confidence_migration_runs_on_a_case_with_no_links_at_all(tmp_path):
    """The other real shape: most cases hold entities and no relations yet. `ALTER
    TABLE` on an empty table is still a schema change, and the version has to move
    or every later open would retry it."""
    db = tmp_path / "case.db"
    store = SqliteCase.create(db, name="No relations")
    store.add_entity("media", "lonely.jpg", by="user")
    _rewind_to_schema_7(db)

    reopened = SqliteCase.open(db, media_dir=tmp_path / "media")

    assert reopened.list_links() == []
    with closing(sqlite3.connect(db)) as conn, conn:
        assert conn.execute(
            "SELECT value FROM meta WHERE key = 'schema_version'"
        ).fetchone()[0] == str(SQLITE_SCHEMA)


def _rewind_to_schema_8(db):
    schema_rewind.rewind(db, 8)


def test_a_case_search_reaches_the_fields_the_vocabulary_declares(tmp_path):
    """A vehicle is looked for by its plate and a claim by the words it quotes.

    Both were unfindable while the index stopped at the label, the type, the folder
    and the notes — the declared fields, the ones the registry went to the trouble
    of naming, were exactly what search could not see.
    """
    db = tmp_path / "case.db"
    store = SqliteCase.create(db, name="Declared fields")
    truck = store.add_entity(
        "vehicle", "Truck 12", {"plate": "AB-123-CD", "make": "Kamaz"}, by="user"
    )
    claim = store.add_entity(
        "claim", "Filmed at the quay", {"verbatim": "on the north jetty"}, by="user"
    )
    place = store.add_entity("place", "Quay 4", {"radius_m": 500}, by="user")

    assert [e["id"] for e in store.page_entities(query="AB-123")["items"]] == [truck["id"]]
    assert [e["id"] for e in store.page_entities(query="kamaz")["items"]] == [truck["id"]]
    assert [e["id"] for e in store.page_entities(query="north jetty")["items"]] == [claim["id"]]
    # a stored number is not text to match on: "500" against every radius in the
    # case would bury the rows that actually say 500
    assert store.page_entities(query="500")["items"] == []
    assert [e["id"] for e in store.page_entities(query="quay 4")["items"]] == [place["id"]]


def test_schema_9_rebuilds_the_index_for_rows_written_before_it(tmp_path):
    """The index is a stored column, so widening it only reaches later writes.

    Without the rebuild a case filed last week would go on answering searches from
    its label alone, which is indistinguishable from the bug being unfixed.
    """
    db = tmp_path / "case.db"
    store = SqliteCase.create(db, name="Old rows")
    truck = store.add_entity("vehicle", "Truck 12", {"plate": "AB-123-CD"}, by="user")
    with closing(sqlite3.connect(db)) as conn, conn:
        # exactly what schema 8 stored: label, type, folder, notes
        conn.execute("UPDATE entities SET search_text = 'truck 12\nvehicle'")
        conn.commit()
    assert SqliteCase.open(db, media_dir=tmp_path / "media").page_entities(
        query="AB-123"
    )["items"] == []

    _rewind_to_schema_8(db)
    reopened = SqliteCase.open(db, media_dir=tmp_path / "media")

    assert [e["id"] for e in reopened.page_entities(query="AB-123")["items"]] == [truck["id"]]


def test_a_search_finds_a_name_whatever_marks_it_was_written_with(tmp_path):
    """`Cafe` finds `Café`, and the Cyrillic, Arabic and Hebrew a reader types
    without its stress marks or vowels finds the label written with them. A mark
    that changes the letter, like a Devanagari vowel sign, still has to match."""
    store = SqliteCase.create(tmp_path / "case.db", name="Folded search")
    cafe = store.add_entity("place", "Café du Port", {}, by="user")
    kyiv = store.add_entity("place", "Ки́їв", {}, by="user")
    name = store.add_entity("person", "مُحَمَّد", {"aliases": "Мухаммед"}, by="user")
    peace = store.add_entity("organization", "שָׁלוֹם", {}, by="user")
    hindi = store.add_entity("organization", "हिंदी", {}, by="user")

    def found(query):
        return [e["id"] for e in store.page_entities(query=query)["items"]]

    assert found("cafe") == [cafe["id"]]
    assert found("CAFÉ") == [cafe["id"]]
    assert found("київ") == [kyiv["id"]]
    assert found("محمد") == [name["id"]]
    assert found("мухаммед") == [name["id"]]
    assert found("שלום") == [peace["id"]]
    assert found("हिंदी") == [hindi["id"]]
    assert found("हदी") == []

    store.upsert_media_item(
        {"path": "media/quai.jpg", "filename": "quai.jpg", "kind": "image",
         "added_at": "2026-01-01T00:00:00Z", "title": "Arrivée au quai"},
        entity_id="e_quai",
    )
    assert [i["path"] for i in store.page_media_items(q="arrivee")["items"]] == ["media/quai.jpg"]


def test_schema_19_folds_the_indexes_written_before_it(tmp_path):
    """0.3.1 stored its search text with every accent kept, so without the rebuild a
    name filed before the update would answer only its exact spelling."""
    db = tmp_path / "case.db"
    store = SqliteCase.create(db, name="Old accents")
    cafe = store.add_entity("place", "Café du Port", {}, by="user")
    store.upsert_media_item(
        {"path": "media/quai.jpg", "filename": "quai.jpg", "kind": "image",
         "added_at": "2026-01-01T00:00:00Z", "title": "Arrivée au quai"},
        entity_id="e_quai",
    )
    schema_rewind.rewind(db, 18)
    with closing(sqlite3.connect(db)) as conn:
        stored = conn.execute("SELECT search_text FROM entities WHERE id = ?", (cafe["id"],))
        assert stored.fetchone()[0].startswith("café")

    reopened = SqliteCase.open(db)
    assert [e["id"] for e in reopened.page_entities(query="cafe")["items"]] == [cafe["id"]]
    assert [i["path"] for i in reopened.page_media_items(q="arrivee")["items"]] == ["media/quai.jpg"]


def _zones(db) -> list[tuple[str, str | None, str | None]]:
    with closing(sqlite3.connect(db)) as conn:
        return conn.execute("SELECT raw, earliest, tz FROM temporal_items WHERE raw IS NOT NULL"
                            " AND category = 'statement'").fetchall()


def test_schema_19_reads_a_stated_day_in_its_zone(tmp_path):
    """0.3.1 had no zone column. The migration adds it and re-reads what the case
    holds, so a Claim stated in a zone lands on that zone's day on first open."""
    db = tmp_path / "case.db"
    store = SqliteCase.create(db, name="Zones")
    store.add_entity("claim", "Filmed", {"when": "2024-03-12", "when_zone": "Europe/Kyiv"}, by="user")
    schema_rewind.rewind(db, 18)

    SqliteCase.open(db)
    assert _zones(db) == [("2024-03-12", "2024-03-11T22:00:00.000000Z", "Europe/Kyiv")]


def test_a_case_from_0_3_0_with_a_dated_claim_opens(tmp_path):
    """0.3.0 shipped 17, whose projection has no zone column. 17 to 18 rebuilds the
    projection before 19 adds the column, so that rebuild must not write it into a
    table that lacks it: 0.3.2 did, and refused every such case."""
    db = tmp_path / "case.db"
    store = SqliteCase.create(db, name="From 0.3.0")
    store.add_entity("claim", "Filmed", {"when": "2024-03-12", "when_zone": "Europe/Kyiv"}, by="user")
    schema_rewind.rewind(db, 17)

    reopened = SqliteCase.open(db)
    assert _zones(db) == [("2024-03-12", "2024-03-11T22:00:00.000000Z", "Europe/Kyiv")]
    assert reopened.temporal_projection_status()["consistent"] is True


def test_a_development_19_without_the_zone_column_gains_it_on_open(tmp_path):
    """A case opened by an earlier cut of the unshipped 19 is stamped 19 already, so
    no migration runs; the open itself adds the column and rebuilds the rows."""
    db = tmp_path / "case.db"
    store = SqliteCase.create(db, name="Dev 19")
    store.add_entity("claim", "Filmed", {"when": "2024-03-12", "when_zone": "Asia/Tokyo"}, by="user")
    with closing(sqlite3.connect(db)) as conn, conn:
        schema_rewind.drop_temporal_zones(conn)

    reopened = SqliteCase.open(db)
    assert _zones(db) == [("2024-03-12", "2024-03-11T15:00:00.000000Z", "Asia/Tokyo")]
    assert reopened.temporal_projection_status()["consistent"] is True


# A schema-4 media index: the browse table as it shipped, before the position
# flag. `media_index_ready` is set because a real v4 case has already backfilled;
# without it, open would rescan the (absent) media folder and clear these rows.
_SCHEMA_V4_MEDIA = """
CREATE TABLE meta (key TEXT PRIMARY KEY, value TEXT NOT NULL);
CREATE TABLE schema_migrations (version INTEGER PRIMARY KEY, applied_at TEXT NOT NULL);
CREATE TABLE entities (
    id TEXT PRIMARY KEY, type TEXT NOT NULL, label TEXT NOT NULL,
    attrs_json TEXT NOT NULL DEFAULT '{}', folder TEXT,
    search_text TEXT NOT NULL DEFAULT '',
    prov_by TEXT NOT NULL, prov_at TEXT NOT NULL,
    prov_status TEXT NOT NULL DEFAULT 'confirmed', prov_source TEXT
);
CREATE TABLE links (
    id TEXT PRIMARY KEY, from_id TEXT NOT NULL REFERENCES entities(id),
    to_id TEXT NOT NULL REFERENCES entities(id), type TEXT NOT NULL,
    prov_by TEXT NOT NULL, prov_at TEXT NOT NULL,
    prov_status TEXT NOT NULL DEFAULT 'confirmed', prov_source TEXT
);
CREATE TABLE folders (path TEXT PRIMARY KEY);
CREATE TABLE jobs (
    id TEXT PRIMARY KEY, kind TEXT NOT NULL, job_key TEXT,
    state TEXT NOT NULL DEFAULT 'queued', attempts INTEGER NOT NULL DEFAULT 0,
    max_attempts INTEGER NOT NULL DEFAULT 3, payload_json TEXT NOT NULL DEFAULT '{}',
    error TEXT, created_at TEXT NOT NULL, updated_at TEXT NOT NULL
);
CREATE TABLE media_items (
    path TEXT PRIMARY KEY, entity_id TEXT UNIQUE, item_json TEXT NOT NULL,
    filename TEXT NOT NULL, kind TEXT NOT NULL, folder TEXT,
    name_sort TEXT NOT NULL, size INTEGER NOT NULL DEFAULT 0,
    added_at TEXT NOT NULL, search_text TEXT NOT NULL,
    source_type TEXT, source_op TEXT, imagery_mode TEXT
);
INSERT INTO meta(key, value) VALUES('schema_version', '4');
INSERT INTO meta(key, value) VALUES('media_index_ready', '1');
INSERT INTO media_items(path, item_json, filename, kind, name_sort, added_at, search_text)
    VALUES('media/photo.jpg',
           '{"path": "media/photo.jpg", "kind": "image", "gps": {"lat": 48.85, "lon": 2.29}}',
           'photo.jpg', 'image', 'photo.jpg', '2026-01-01T00:00:00Z', 'photo.jpg');
INSERT INTO media_items(path, item_json, filename, kind, name_sort, added_at, search_text)
    VALUES('media/plain.jpg', '{"path": "media/plain.jpg", "kind": "image"}',
           'plain.jpg', 'image', 'plain.jpg', '2026-01-02T00:00:00Z', 'plain.jpg');
"""


def test_open_backfills_the_position_flag_from_already_indexed_items(tmp_path):
    """4 -> 5 reads the sidecar JSON already in the row, so a case enriched before
    the column existed gains its GPS filter on open — no file scan, no re-enrich."""
    db = tmp_path / "case.db"
    with closing(sqlite3.connect(db)) as conn, conn:
        conn.executescript(_SCHEMA_V4_MEDIA)

    store = SqliteCase.open(db)

    page = store.page_media_items(gps=True)
    assert [item["path"] for item in page["items"]] == ["media/photo.jpg"]
    assert store.page_media_items()["facets"]["gps_count"] == 1


def test_open_without_a_media_dir_defers_the_backfill(tmp_path):
    """The backend is never told the case layout, so it must not invent one.

    An open with no media directory leaves the sidecar backfill undone and its
    marker unset, and a later open that knows where media live performs it.
    Anything else would silently index the wrong folder once the case tree
    moves.
    """
    db = tmp_path / "case.db"
    with closing(sqlite3.connect(db)) as conn, conn:
        conn.executescript(_SCHEMA_V1)  # predates the index, so it needs the backfill
    media_dir = tmp_path / "elsewhere"
    (media_dir / layout.META_DIR).mkdir(parents=True)
    (media_dir / "shot.png").write_bytes(b"x")
    (media_dir / layout.META_DIR / "shot.png.json").write_text(
        '{"filename": "shot.png", "kind": "image", "size": 1,'
        ' "added_at": "2026", "source": {"type": "upload"}}',
        encoding="utf-8",
    )

    SqliteCase.open(db)
    with closing(sqlite3.connect(db)) as conn, conn:
        assert conn.execute("SELECT value FROM meta WHERE key='media_index_ready'").fetchone() is None

    store = SqliteCase.open(db, media_dir=media_dir)
    assert [item["path"] for item in store.list_media_items()] == ["media/shot.png"]


def test_media_browse_index_pages_searches_and_counts_categories(tmp_path):
    store = SqliteCase.create(tmp_path / "case.db", name="Media index")
    store.upsert_media_item(
        {
            "path": "media/photo.png",
            "filename": "photo.png",
            "kind": "image",
            "size": 12,
            "added_at": "2026-01-01T00:00:00Z",
            "title": "Bridge",
            "notes": "river crossing",
            "folder": "Sources",
            "source": {"type": "upload"},
        },
        entity_id="e_photo",
    )
    store.upsert_media_item(
        {
            "path": "media/map.png",
            "filename": "map.png",
            "kind": "image",
            "size": 30,
            "added_at": "2026-01-02T00:00:00Z",
            "source": {"type": "satellite"},
        },
        entity_id="e_map",
    )
    store.upsert_media_item(
        {
            "path": "media/clip.mp4",
            "filename": "clip.mp4",
            "kind": "video",
            "size": 50,
            "added_at": "2026-01-03T00:00:00Z",
            "source": {"type": "download"},
        },
        entity_id="e_clip",
    )

    first = store.page_media_items(limit=2)
    assert [item["path"] for item in first["items"]] == [
        "media/clip.mp4",
        "media/map.png",
    ]
    assert first["next_cursor"] == "2"
    assert first["facets"]["category_counts"] == {
        "image": 1,
        "video": 1,
        "comparison": 0,
        "collage": 0,
        "satellite": 1,
        "upload": 1,
        "download": 1,
        "other": 0,
    }

    hit = store.page_media_items(q="bridge river", category="image")
    assert [item["path"] for item in hit["items"]] == ["media/photo.png"]
    assert hit["facets"]["folder_counts"] == {"Sources": 1}
    assert store.page_media_items(category="satellite")["total"] == 1
    assert store.media_items_by_paths(
        ["media/map.png", "media/photo.png", "media/missing.png"]
    ) == [
        store.page_media_items(category="satellite")["items"][0],
        store.page_media_items(q="bridge")["items"][0],
    ]


def test_concurrent_first_open_cannot_replace_registered_media_rows(tmp_path):
    db = tmp_path / "case.db"
    SqliteCase.create(db, name="Concurrent index")
    media_dir = tmp_path / "media"
    (media_dir / layout.META_DIR).mkdir(parents=True)
    for i in range(8):
        name = f"item-{i}.png"
        (media_dir / name).write_bytes(bytes([i]))
        (media_dir / layout.META_DIR / f"{name}.json").write_text(
            (
                '{"filename": "%s", "kind": "image", "size": 1,'
                ' "added_at": "2026", "source": {"type": "upload"}}'
            )
            % name,
            encoding="utf-8",
        )

    errors: list[BaseException] = []

    def open_and_register(i: int) -> None:
        try:
            store = SqliteCase.open(db, media_dir=media_dir)
            store.upsert_media_item(
                {
                    "path": f"media/live-{i}.png",
                    "filename": f"live-{i}.png",
                    "kind": "image",
                    "size": 1,
                    "added_at": "2026",
                    "source": {"type": "upload"},
                }
            )
        except BaseException as exc:  # pragma: no cover - asserted below
            errors.append(exc)

    workers = [threading.Thread(target=open_and_register, args=(i,)) for i in range(8)]
    for worker in workers:
        worker.start()
    for worker in workers:
        worker.join()

    assert errors == []
    assert len(SqliteCase.open(db, media_dir=media_dir).list_media_items()) == 16


def test_pagination_keys_on_rowid_so_a_deletion_does_not_skip(tmp_path):
    """The cursor keys on rowid, not an offset, so removing an already-seen row
    between page fetches never makes the next page skip a live entity."""
    store = SqliteCase.create(tmp_path / "case.db", name="Keyset")
    ids = [store.add_entity("person", f"P{i}", by="user")["id"] for i in range(4)]

    page1 = store.page_entities(limit=2)
    assert [e["id"] for e in page1["items"]] == ids[:2]

    store.remove_entity(ids[0])  # a row before the cursor disappears

    page2 = store.page_entities(limit=2, cursor=page1["next_cursor"])
    assert [e["id"] for e in page2["items"]] == ids[2:]  # nothing skipped


def test_an_ordered_page_keys_on_its_sort_and_never_ties_itself_into_a_loop(tmp_path):
    """A sort has to page on its own key *and* the rowid. Every one of these four
    entities was filed in the same second — which is what a bulk import looks like —
    so a cursor holding the date alone would hand back the same page forever."""
    store = SqliteCase.create(tmp_path / "case.db", name="Ordered")
    ids = [store.add_entity("person", f"P{i}", by="user")["id"] for i in range(4)]

    seen: list[str] = []
    cursor = None
    while True:
        page = store.page_entities(limit=2, cursor=cursor, order="-created")
        seen.extend(entity["id"] for entity in page["items"])
        cursor = page["next_cursor"]
        if cursor is None:
            break
    assert seen == list(reversed(ids))  # every row once, newest first


def test_an_ordered_cursor_survives_a_key_holding_its_own_separator(tmp_path):
    """The cursor is spelled `<rowid>:<key>`, and a label may hold a colon. The rowid
    cannot, so the key takes the whole of the rest and a name round-trips unharmed."""
    store = SqliteCase.create(tmp_path / "case.db", name="Colons")
    for label in ("aa: one", "bb: two", "cc: three"):
        store.add_entity("person", label, by="user")

    first = store.page_entities(limit=2, order="label")
    assert [entity["label"] for entity in first["items"]] == ["aa: one", "bb: two"]
    rest = store.page_entities(limit=2, cursor=first["next_cursor"], order="label")
    assert [entity["label"] for entity in rest["items"]] == ["cc: three"]


def test_an_ordering_the_store_does_not_have_is_refused(tmp_path):
    store = SqliteCase.create(tmp_path / "case.db", name="BadOrder")
    with pytest.raises(CaseError):
        store.page_entities(order="size")


# -- converter -------------------------------------------------------------


def test_convert_matches_the_source_graph(tmp_path):
    src = _json_case(
        folders=["Sources", "Sources/Telegram"],
        entities=[
            _entity("e_a", type="person", label="Ada"),
            _entity("e_b", type="account", label="@ada", attrs={"handle": "@ada"}),
            _entity("e_c", type="media", label="photo", attrs={"path": "media/x.jpg"}),
        ],
        links=[
            {
                "id": "l_1",
                "from": "e_a",
                "to": "e_b",
                "type": "owns",
                "provenance": {"by": "user", "at": "2026-01-01T00:00:00Z", "status": "confirmed"},
            }
        ],
    )

    report = convert_json_to_sqlite(src, tmp_path / "case.db")
    assert (report.entities, report.links, report.folders) == (3, 1, 2)
    assert report.integrity_ok and report.missing_endpoints == []

    store = SqliteCase.open(tmp_path / "case.db")
    assert {e["id"] for e in store.list_entities()} == {"e_a", "e_b", "e_c"}
    assert store.get_entity("e_b")["attrs"] == {"handle": "@ada"}
    assert store.list_folders() == ["Sources", "Sources/Telegram"]
    assert store.find_entity(attr="path", value="media/x.jpg")["id"] == "e_c"
    snap = store.snapshot()
    assert snap["name"] == "Legacy" and snap["created_at"] == "2026-01-01T00:00:00Z"


def test_convert_reports_and_drops_dangling_links(tmp_path):
    src = _json_case(
        entities=[_entity("e_a")],
        links=[
            {
                "id": "l_ghost",
                "from": "e_a",
                "to": "e_missing",
                "type": "owns",
                "provenance": {"by": "user", "at": "2026", "status": "confirmed"},
            }
        ],
    )

    report = convert_json_to_sqlite(src, tmp_path / "case.db")
    assert report.missing_endpoints == ["l_ghost"]
    assert report.integrity_ok  # a dropped edge is not an integrity failure

    store = SqliteCase.open(tmp_path / "case.db")
    assert store.list_links() == []
    assert store.get_entity("e_a") is not None  # the entity is never erased


def test_convert_rolls_back_and_leaves_no_db_on_failure(tmp_path):
    src = _json_case(entities=[_entity("e_dup"), _entity("e_dup")])  # duplicate primary key
    db = tmp_path / "case.db"

    with pytest.raises(sqlite3.IntegrityError):
        convert_json_to_sqlite(src, db)

    assert not db.exists()
    assert not (tmp_path / "case.db.tmp").exists()


def test_convert_writes_only_the_target_db(tmp_workspace, tmp_path):
    prov = {"by": "user", "at": "2026-01-01T00:00:00Z", "status": "confirmed"}
    case = write_legacy_json_case(
        "Live",
        entities=[{"id": "e_a", "type": "person", "label": "Ada", "attrs": {}, "provenance": prov}],
    )
    before = legacy_manifest_path(case).read_bytes()

    convert_json_to_sqlite(read_legacy_manifest(case), tmp_path / "case.db")

    # the converter only reads the graph; it never rewrites the source case.json
    assert legacy_manifest_path(case).read_bytes() == before
    assert (tmp_path / "case.db").exists()


def test_convert_a_large_case_is_intact(tmp_workspace, tmp_path):
    case, summary = build_big_case(
        name="Big", entities=300, links=400, media=100, notes=20, artifacts=30,
        write_media_files=False,
    )
    report = convert_json_to_sqlite(read_legacy_manifest(case), tmp_path / "case.db")

    assert report.integrity_ok
    assert report.entities == summary.entities
    store = SqliteCase.open(tmp_path / "case.db")
    assert len(store.list_entities()) == summary.entities
    # every surviving link resolves to real endpoints
    ids = {e["id"] for e in store.list_entities()}
    for link in store.list_links():
        assert link["from"] in ids and link["to"] in ids


def test_a_media_row_carries_the_entity_it_belongs_to(tmp_workspace):
    """The link is already a column. A caller that needs the entity behind a path
    reads it off the row instead of scanning the entities table for an attribute
    match — which is what enrichment does for every near-duplicate it finds."""
    from azimut.workspace import Case

    case = Case.create("Media rows")
    photo = case.add_entity("media", "Photo", attrs={"path": "media/photo.jpg"}, by="test")
    case.upsert_media_item(
        {"path": "media/photo.jpg", "filename": "photo.jpg", "kind": "image"},
        entity_id=photo["id"],
    )
    case.upsert_media_item({"path": "media/loose.jpg", "filename": "loose.jpg", "kind": "image"})

    by_path = {item["path"]: item for item in case.list_media_items()}
    assert by_path["media/photo.jpg"]["entity_id"] == photo["id"]
    # a row nothing filed says so rather than pointing at the wrong entity
    assert by_path["media/loose.jpg"]["entity_id"] is None
    assert case.media_items_by_paths(["media/photo.jpg"])[0]["entity_id"] == photo["id"]
    assert case.page_media_items()["items"][0]["entity_id"] in (photo["id"], None)


def test_the_index_says_how_each_file_came_into_the_case(tmp_workspace):
    """Read off the two indexed columns, keyed by entity, because the surfaces that
    draw one hold ids. A row recording no route is left out rather than mapped to an
    empty one: a caller has to be able to tell "came in by no stated route" from "the
    index has never seen this entity"."""
    from azimut.workspace import Case

    case = Case.create("Origins")
    frame = case.add_entity("media", "Frame", attrs={"path": "media/frame.png"}, by="inspect")
    photo = case.add_entity("media", "Photo", attrs={"path": "media/photo.jpg"}, by="test")
    mystery = case.add_entity("media", "Mystery", attrs={"path": "media/x.jpg"}, by="test")
    case.upsert_media_item(
        {
            "path": "media/frame.png",
            "filename": "frame.png",
            "kind": "image",
            "source": {"type": "inspect", "op": "frame", "from": "media/clip.mp4"},
        },
        entity_id=frame["id"],
    )
    case.upsert_media_item(
        {
            "path": "media/photo.jpg",
            "filename": "photo.jpg",
            "kind": "image",
            "source": {"type": "upload"},
        },
        entity_id=photo["id"],
    )
    case.upsert_media_item(
        {"path": "media/x.jpg", "filename": "x.jpg", "kind": "image"},
        entity_id=mystery["id"],
    )

    found = case.media_origins([frame["id"], photo["id"], mystery["id"], "e_nope"])
    assert found[frame["id"]] == {"type": "inspect", "op": "frame"}
    # An upload has a route and no act: nothing composed it, so there is no `op`.
    assert found[photo["id"]] == {"type": "upload"}
    assert mystery["id"] not in found and "e_nope" not in found
    assert case.media_origins([]) == {}