"""目録への接続（SPEC_02 冒頭: 外部キー制約を有効にする）."""

from pathlib import Path

import pytest

from tiasdata import catalog


def test_connect_enables_foreign_keys(tmp_path: Path) -> None:
    conn = catalog.connect(tmp_path / "db.sqlite", create=True)
    try:
        assert conn.execute("PRAGMA foreign_keys").fetchone()[0] == 1
    finally:
        conn.close()


def test_connect_enables_foreign_keys_on_every_connection(tmp_path: Path) -> None:
    path = tmp_path / "db.sqlite"
    catalog.connect(path, create=True).close()
    conn = catalog.connect(path)
    try:
        assert conn.execute("PRAGMA foreign_keys").fetchone()[0] == 1
    finally:
        conn.close()


def test_connect_with_create_makes_db_file(tmp_path: Path) -> None:
    path = tmp_path / "db.sqlite"
    catalog.connect(path, create=True).close()
    assert path.is_file()


def test_connect_without_create_refuses_missing_db(tmp_path: Path) -> None:
    path = tmp_path / "missing.sqlite"
    with pytest.raises(ValueError):
        catalog.connect(path)
    # 空の目録を誤って作らない
    assert not path.exists()


def test_connect_missing_db_message_does_not_contain_path(tmp_path: Path) -> None:
    path = tmp_path / "missing.sqlite"
    with pytest.raises(ValueError) as exc:
        catalog.connect(path)
    assert str(tmp_path) not in str(exc.value)
    assert tmp_path.as_posix() not in str(exc.value)


# ---------------------------------------------------------------------------
# write_transaction（SPEC_03: 登録は 1 つのトランザクション．
# error-handling.md: 制約違反は ValueError）
# ---------------------------------------------------------------------------
def _subject(conn, subject_id: str) -> None:
    conn.execute(
        "INSERT INTO subjects (subject_id, created_at) VALUES (?, '2026-10-06T00:00:00Z')",
        (subject_id,),
    )


def _subjects(path: Path) -> list[str]:
    conn = catalog.connect(path)
    try:
        return [s for (s,) in conn.execute("SELECT subject_id FROM subjects ORDER BY 1")]
    finally:
        conn.close()


@pytest.fixture
def migrated_path(tmp_path: Path) -> Path:
    path = tmp_path / "catalog.sqlite"
    conn = catalog.connect(path, create=True)
    try:
        catalog.apply_migrations(conn, "catalog")
    finally:
        conn.close()
    return path


def test_write_transaction_commits_on_success(migrated_path: Path) -> None:
    conn = catalog.connect(migrated_path)
    try:
        with catalog.write_transaction(conn):
            _subject(conn, "S000001")
            _subject(conn, "S000002")
    finally:
        conn.close()
    assert _subjects(migrated_path) == ["S000001", "S000002"]


def test_write_transaction_converts_integrity_error_and_rolls_back(migrated_path: Path) -> None:
    conn = catalog.connect(migrated_path)
    try:
        with pytest.raises(ValueError), catalog.write_transaction(conn):
            _subject(conn, "S000001")
            _subject(conn, "bad-id")  # CHECK 違反
        assert not conn.in_transaction
    finally:
        conn.close()
    assert _subjects(migrated_path) == []


def test_write_transaction_rolls_back_on_other_error(migrated_path: Path) -> None:
    conn = catalog.connect(migrated_path)
    try:
        with pytest.raises(RuntimeError, match="boom"), catalog.write_transaction(conn):
            _subject(conn, "S000001")
            raise RuntimeError("boom")
    finally:
        conn.close()
    assert _subjects(migrated_path) == []


def test_write_transaction_includes_attached_db(migrated_path: Path, tmp_path: Path) -> None:
    # 目録と ATTACH した対応表への書き込みは同じトランザクションで取り消される
    identity_path = tmp_path / "identity.sqlite"
    conn = catalog.connect(identity_path, create=True)
    try:
        catalog.apply_migrations(conn, "identity")
    finally:
        conn.close()

    conn = catalog.connect(migrated_path)
    try:
        conn.execute("ATTACH DATABASE ? AS identity", (str(identity_path),))
        with pytest.raises(ValueError), catalog.write_transaction(conn):
            conn.execute(
                "INSERT INTO identity.identity_map (patient_key, subject_id, created_at)"
                " VALUES ('990001', 'S000001', '2026-10-06T00:00:00Z')"
            )
            _subject(conn, "S000001")
            _subject(conn, "S000001")  # 主キーの重複
    finally:
        conn.close()
    conn = catalog.connect(identity_path)
    try:
        assert conn.execute("SELECT COUNT(*) FROM identity_map").fetchone()[0] == 0
    finally:
        conn.close()
    assert _subjects(migrated_path) == []


def test_require_latest_rejects_unmigrated_db(tmp_path: Path) -> None:
    conn = catalog.connect(tmp_path / "db.sqlite", create=True)
    try:
        with pytest.raises(ValueError):
            catalog.require_latest(conn, "catalog")
    finally:
        conn.close()


def test_require_latest_accepts_migrated_db(migrated_path: Path) -> None:
    conn = catalog.connect(migrated_path)
    try:
        catalog.require_latest(conn, "catalog")
    finally:
        conn.close()


def test_require_latest_rejects_newer_db(migrated_path: Path) -> None:
    conn = catalog.connect(migrated_path)
    try:
        conn.execute(f"PRAGMA user_version = {catalog.latest_version('catalog') + 1}")
        with pytest.raises(ValueError):
            catalog.require_latest(conn, "catalog")
    finally:
        conn.close()