"""目録の登録（症例・撮影・ファイル・所見・由来）と，登録のための照会.

所見（`annotations`）は INSERT だけを行い，UPDATE・DELETE しない（スキーマのトリガでも拒否される）．
"""

import sqlite3
from datetime import UTC, datetime

from pydantic import BaseModel, ConfigDict

SUBJECT_PREFIX = "S"
CAPTURE_PREFIX = "C"
# 研究用 ID・撮影 ID は接頭辞 + 6 桁（SPEC_02「ID の書式」．スキーマの CHECK と同じ）
ID_DIGITS = 6
_MAX_ID_NUMBER = 10**ID_DIGITS - 1


class AssetRecord(BaseModel):
    """目録の `assets` の 1 行．"""

    model_config = ConfigDict(frozen=True)

    sha256: str
    kind: str
    capture_id: str | None
    ext: str
    size_bytes: int
    source: str
    source_name: str


class CaptureRecord(BaseModel):
    """目録の `captures` の 1 行．"""

    model_config = ConfigDict(frozen=True)

    capture_id: str
    subject_id: str
    captured_on: str | None
    device: str | None


def utc_timestamp(now: datetime | None = None) -> str:
    """目録に保存する日時の文字列（UTC の `YYYY-MM-DDTHH:MM:SSZ`．秒まで）を返す．

    Args:
        now: 日時（省略時は現在．タイムゾーンなしは UTC とみなす）．

    Returns:
        `2026-10-02T09:00:00Z` の形の文字列．
    """
    moment = now if now is not None else datetime.now(UTC)
    if moment.tzinfo is not None:
        moment = moment.astimezone(UTC)
    # スキーマの CHECK が小数秒を拒否するため秒までにする
    return moment.strftime("%Y-%m-%dT%H:%M:%SZ")


def format_id(prefix: str, number: int) -> str:
    """研究用 ID・撮影 ID の文字列を作る（例: `S000123`）．

    Args:
        prefix: `S` または `C`．
        number: 1 以上の番号．

    Returns:
        接頭辞 + 6 桁の ID．

    Raises:
        ValueError: 番号が 6 桁に収まらない．
    """
    if not 1 <= number <= _MAX_ID_NUMBER:
        raise ValueError(f"ID の番号が {ID_DIGITS} 桁に収まらない（{prefix}）")
    return f"{prefix}{number:0{ID_DIGITS}d}"


def parse_id_number(value: str | None) -> int:
    """目録・対応表にある研究用 ID・撮影 ID の番号を返す（`format_id` の逆．None なら 0）．"""
    # ID の書式はスキーマの CHECK で保証されているので，接頭辞を除けば整数になる
    return int(value[1:]) if value else 0


def max_subject_number(conn: sqlite3.Connection) -> int:
    """目録の症例の研究用 ID の最大の番号を返す（無ければ 0）．"""
    (value,) = conn.execute("SELECT MAX(subject_id) FROM subjects").fetchone()
    return parse_id_number(value)


def max_capture_number(conn: sqlite3.Connection) -> int:
    """目録の撮影 ID の最大の番号を返す（無ければ 0）．"""
    (value,) = conn.execute("SELECT MAX(capture_id) FROM captures").fetchone()
    return parse_id_number(value)


def subject_exists(conn: sqlite3.Connection, subject_id: str) -> bool:
    """症例が目録にあるか．"""
    row = conn.execute("SELECT 1 FROM subjects WHERE subject_id = ?", (subject_id,)).fetchone()
    return row is not None


def get_asset(conn: sqlite3.Connection, sha256: str) -> AssetRecord | None:
    """ファイルの行を SHA-256 で引く（無ければ None）．"""
    row = conn.execute(
        "SELECT sha256, kind, capture_id, ext, size_bytes, source, source_name"
        " FROM assets WHERE sha256 = ?",
        (sha256,),
    ).fetchone()
    if row is None:
        return None
    return AssetRecord(
        sha256=row[0],
        kind=row[1],
        capture_id=row[2],
        ext=row[3],
        size_bytes=row[4],
        source=row[5],
        source_name=row[6],
    )


def find_asset_sha256_by_source_name(
    conn: sqlite3.Connection, source: str, source_name: str
) -> list[str]:
    """取り込み元の名前と相対パスから，登録済みのファイルの SHA-256 を引く．"""
    rows = conn.execute(
        "SELECT sha256 FROM assets WHERE source = ? AND source_name = ? ORDER BY sha256",
        (source, source_name),
    )
    return [sha256 for (sha256,) in rows]


def get_capture(conn: sqlite3.Connection, capture_id: str) -> CaptureRecord | None:
    """撮影の行を引く（無ければ None）．"""
    row = conn.execute(
        "SELECT capture_id, subject_id, captured_on, device FROM captures WHERE capture_id = ?",
        (capture_id,),
    ).fetchone()
    if row is None:
        return None
    return CaptureRecord(capture_id=row[0], subject_id=row[1], captured_on=row[2], device=row[3])


def capture_annotation_values(
    conn: sqlite3.Connection, capture_id: str, kind: str, source: str
) -> list[str]:
    """撮影に付いた所見のうち，種類と由来が一致するものの値（JSON の文字列）を返す．

    訂正された行（`superseded_by` あり）も含める（取り込み元から読んだ元の値と照合するため）．
    """
    rows = conn.execute(
        "SELECT value FROM annotations"
        " WHERE capture_id = ? AND kind = ? AND source = ? ORDER BY annotation_id",
        (capture_id, kind, source),
    )
    return [value for (value,) in rows]


def file_annotation_values(
    conn: sqlite3.Connection, sha256: str, kind: str, source: str
) -> list[str]:
    """ファイルに付いた所見のうち，種類と由来が一致するものの値（JSON の文字列）を返す．

    訂正された行（`superseded_by` あり）も含める（取り込み元から読んだ元の値と照合するため）．
    """
    rows = conn.execute(
        "SELECT value FROM annotations"
        " WHERE sha256 = ? AND kind = ? AND source = ? ORDER BY annotation_id",
        (sha256, kind, source),
    )
    return [value for (value,) in rows]


def has_valid_annotation(
    conn: sqlite3.Connection,
    kind: str,
    *,
    capture_id: str | None = None,
    sha256: str | None = None,
) -> bool:
    """撮影またはファイルに，種類が一致する有効な所見（訂正されていないもの）があるか．

    Args:
        conn: 目録への接続．
        kind: 所見の種類．
        capture_id: 撮影 ID（撮影に付いた所見を見るとき）．
        sha256: ファイルの SHA-256（ファイルに付いた所見を見るとき）．

    Returns:
        有効な所見があれば True．

    Raises:
        RuntimeError: 撮影 ID と SHA-256 の両方を指定した，またはどちらも指定しなかった．
    """
    if (capture_id is None) == (sha256 is None):
        raise RuntimeError("capture_id と sha256 のどちらか一方だけを指定する")
    if capture_id is not None:
        row = conn.execute(
            "SELECT 1 FROM annotations"
            " WHERE capture_id = ? AND kind = ? AND superseded_by IS NULL LIMIT 1",
            (capture_id, kind),
        ).fetchone()
    else:
        row = conn.execute(
            "SELECT 1 FROM annotations"
            " WHERE sha256 = ? AND kind = ? AND superseded_by IS NULL LIMIT 1",
            (sha256, kind),
        ).fetchone()
    return row is not None


def insert_subject(conn: sqlite3.Connection, subject_id: str, created_at: str) -> None:
    """症例を登録する．"""
    conn.execute(
        "INSERT INTO subjects (subject_id, created_at) VALUES (?, ?)", (subject_id, created_at)
    )


def insert_capture(
    conn: sqlite3.Connection,
    capture_id: str,
    subject_id: str,
    *,
    captured_on: str | None,
    device: str | None,
) -> None:
    """撮影を登録する．"""
    conn.execute(
        "INSERT INTO captures (capture_id, subject_id, captured_on, device) VALUES (?, ?, ?, ?)",
        (capture_id, subject_id, captured_on, device),
    )


def insert_asset(conn: sqlite3.Connection, asset: AssetRecord, registered_at: str) -> None:
    """ファイルを登録する．"""
    conn.execute(
        "INSERT INTO assets"
        " (sha256, kind, capture_id, ext, size_bytes, source, source_name, registered_at)"
        " VALUES (?, ?, ?, ?, ?, ?, ?, ?)",
        (
            asset.sha256,
            asset.kind,
            asset.capture_id,
            asset.ext,
            asset.size_bytes,
            asset.source,
            asset.source_name,
            registered_at,
        ),
    )


def insert_capture_annotation(
    conn: sqlite3.Connection,
    *,
    kind: str,
    capture_id: str,
    value_json: str,
    source: str,
    created_at: str,
    annotator: str | None = None,
) -> int:
    """撮影に付く所見を追加する（値は `kinds` の型で検証済みの JSON の文字列）．

    Returns:
        追加した行の `annotation_id`．
    """
    cursor = conn.execute(
        "INSERT INTO annotations (kind, capture_id, value, annotator, source, created_at)"
        " VALUES (?, ?, ?, ?, ?, ?)",
        (kind, capture_id, value_json, annotator, source, created_at),
    )
    return int(cursor.lastrowid)


def insert_file_annotation(
    conn: sqlite3.Connection,
    *,
    kind: str,
    sha256: str,
    value_json: str,
    source: str,
    created_at: str,
    annotator: str | None = None,
) -> int:
    """ファイルに付く所見を追加する（値は `kinds` の型で検証済みの JSON の文字列）．

    撮影にひも付かないファイル（`assets.capture_id` が NULL）の所見に使う．

    Returns:
        追加した行の `annotation_id`．
    """
    cursor = conn.execute(
        "INSERT INTO annotations (kind, sha256, value, annotator, source, created_at)"
        " VALUES (?, ?, ?, ?, ?, ?)",
        (kind, sha256, value_json, annotator, source, created_at),
    )
    return int(cursor.lastrowid)


def insert_derivation(
    conn: sqlite3.Connection,
    *,
    tool: str,
    tool_version: str,
    mode: str | None,
    note: str | None,
    outputs: list[tuple[str, str]],
    inputs: list[tuple[str, str]] | None = None,
    performed_at: str | None = None,
) -> int:
    """由来（プログラム・版）と，その入力・出力のファイルを登録する．

    Args:
        conn: 目録への接続．
        tool: プログラム名．
        tool_version: プログラムの版（不明なら `unknown`）．
        mode: 実行モード・設定．
        note: 備考．
        outputs: 出力ファイル（SHA-256, 役割）．
        inputs: 入力ファイル（SHA-256, 役割）．省略時は入力なし．
        performed_at: 実行日時（不明なら None）．

    Returns:
        追加した行の `derivation_id`．
    """
    cursor = conn.execute(
        "INSERT INTO derivations (tool, tool_version, mode, performed_at, note)"
        " VALUES (?, ?, ?, ?, ?)",
        (tool, tool_version, mode, performed_at, note),
    )
    derivation_id = int(cursor.lastrowid)
    conn.executemany(
        "INSERT INTO derivation_inputs (derivation_id, sha256, role) VALUES (?, ?, ?)",
        [(derivation_id, sha256, role) for sha256, role in inputs or []],
    )
    conn.executemany(
        "INSERT INTO derivation_outputs (derivation_id, sha256, role) VALUES (?, ?, ?)",
        [(derivation_id, sha256, role) for sha256, role in outputs],
    )
    return derivation_id