"""データセットと版（`datasets`・`dataset_versions`・`dataset_members`・`dataset_member_annotations`）.

版とその構成は INSERT だけを行い，UPDATE・DELETE しない（スキーマのトリガでも拒否される．
SPEC_02「スキーマで強制する規則」）．そのため版の行は manifest の SHA-256 を計算してから
構成と一緒に INSERT する．
"""

import sqlite3

from pydantic import BaseModel, ConfigDict


class _Row(BaseModel):
    model_config = ConfigDict(frozen=True)


class MemberAssetRow(_Row):
    """版に入れる（入れた）ファイルと，その撮影・症例．

    Attributes:
        sha256: ファイルの SHA-256．
        ext: 拡張子．
        kind: ファイルの種類．
        capture_id: 撮影 ID（撮影にひも付かないファイルは None）．
        subject_id: 研究用の症例 ID（撮影が無ければ None）．
    """

    sha256: str
    ext: str
    kind: str
    capture_id: str | None
    subject_id: str | None


class AnnotationRow(_Row):
    """所見の行（版の評価に使う列だけ）．

    Attributes:
        annotation_id: 所見の ID．
        kind: 所見の種類．
        capture_id: 撮影に付く所見の撮影 ID．
        sha256: ファイルに付く所見のファイルの SHA-256．
        value: 値（JSON の文字列）．
    """

    annotation_id: int
    kind: str
    capture_id: str | None
    sha256: str | None
    value: str


class MemberAnnotationRow(_Row):
    """版の構成で，ファイルに対して採用した所見．"""

    member_sha256: str
    annotation: AnnotationRow


class DatasetRecord(_Row):
    """`datasets` の 1 行．"""

    name: str
    description: str | None
    created_at: str


class VersionRecord(_Row):
    """`dataset_versions` の 1 行．"""

    name: str
    version: int
    definition: str
    hub_commit: str
    manifest_sha256: str
    created_at: str
    note: str | None


class VersionSummary(_Row):
    """版の行と構成の件数．

    Attributes:
        record: 版の行．
        members: 構成のファイルの数．
        member_annotations: 採用した所見の数．
    """

    record: VersionRecord
    members: int
    member_annotations: int


_VERSION_COLUMNS = "name, version, definition, hub_commit, manifest_sha256, created_at, note"


def _version(row: tuple) -> VersionRecord:
    return VersionRecord(
        name=row[0],
        version=row[1],
        definition=row[2],
        hub_commit=row[3],
        manifest_sha256=row[4],
        created_at=row[5],
        note=row[6],
    )


def _annotation(row: tuple) -> AnnotationRow:
    return AnnotationRow(
        annotation_id=row[0], kind=row[1], capture_id=row[2], sha256=row[3], value=row[4]
    )


def kind_categories(conn: sqlite3.Connection) -> dict[str, str]:
    """種類名 → 分類（`asset`・`annotation`）．"""
    rows = conn.execute("SELECT kind, category FROM kinds ORDER BY kind")
    return {kind: category for kind, category in rows}


def list_assets_of_kind(conn: sqlite3.Connection, kind: str) -> list[MemberAssetRow]:
    """種類が一致するファイルと，その撮影・症例を SHA-256 の順に返す．"""
    rows = conn.execute(
        "SELECT a.sha256, a.ext, a.kind, a.capture_id, c.subject_id FROM assets AS a"
        " LEFT JOIN captures AS c ON c.capture_id = a.capture_id"
        " WHERE a.kind = ? ORDER BY a.sha256",
        (kind,),
    )
    return [
        MemberAssetRow(sha256=r[0], ext=r[1], kind=r[2], capture_id=r[3], subject_id=r[4])
        for r in rows
    ]


def list_valid_annotations(conn: sqlite3.Connection, kind: str) -> list[AnnotationRow]:
    """種類が一致する有効な所見（訂正されていないもの）を ID の順に返す．"""
    rows = conn.execute(
        "SELECT annotation_id, kind, capture_id, sha256, value FROM annotations"
        " WHERE kind = ? AND superseded_by IS NULL ORDER BY annotation_id",
        (kind,),
    )
    return [_annotation(r) for r in rows]


def get_dataset(conn: sqlite3.Connection, name: str) -> DatasetRecord | None:
    """データセットの行を引く（無ければ None）．"""
    row = conn.execute(
        "SELECT name, description, created_at FROM datasets WHERE name = ?", (name,)
    ).fetchone()
    if row is None:
        return None
    return DatasetRecord(name=row[0], description=row[1], created_at=row[2])


def list_datasets(conn: sqlite3.Connection) -> list[DatasetRecord]:
    """データセットの行を名前の順に返す．"""
    rows = conn.execute("SELECT name, description, created_at FROM datasets ORDER BY name")
    return [DatasetRecord(name=r[0], description=r[1], created_at=r[2]) for r in rows]


def insert_dataset(
    conn: sqlite3.Connection, name: str, description: str | None, created_at: str
) -> None:
    """データセットの行を追加する．"""
    conn.execute(
        "INSERT INTO datasets (name, description, created_at) VALUES (?, ?, ?)",
        (name, description, created_at),
    )


def get_version(conn: sqlite3.Connection, name: str, version: int) -> VersionRecord | None:
    """版の行を引く（無ければ None）．"""
    row = conn.execute(
        f"SELECT {_VERSION_COLUMNS} FROM dataset_versions WHERE name = ? AND version = ?",
        (name, version),
    ).fetchone()
    return None if row is None else _version(row)


def get_latest_version(conn: sqlite3.Connection, name: str) -> VersionRecord | None:
    """データセットの最新の版の行を引く（版が無ければ None）．"""
    row = conn.execute(
        f"SELECT {_VERSION_COLUMNS} FROM dataset_versions WHERE name = ?"
        " ORDER BY version DESC LIMIT 1",
        (name,),
    ).fetchone()
    return None if row is None else _version(row)


def list_versions(conn: sqlite3.Connection, name: str | None = None) -> list[VersionSummary]:
    """版の行と構成の件数を，名前・版の順に返す．

    Args:
        conn: 目録への接続．
        name: データセット名（省略時はすべてのデータセット）．

    Returns:
        版ごとの行と件数．
    """
    rows = conn.execute(
        "SELECT v.name, v.version, v.definition, v.hub_commit, v.manifest_sha256,"
        " v.created_at, v.note,"
        " (SELECT COUNT(*) FROM dataset_members AS m"
        "  WHERE m.name = v.name AND m.version = v.version),"
        " (SELECT COUNT(*) FROM dataset_member_annotations AS d"
        "  WHERE d.name = v.name AND d.version = v.version)"
        " FROM dataset_versions AS v"
        " WHERE ? IS NULL OR v.name = ?"
        " ORDER BY v.name, v.version",
        (name, name),
    )
    return [
        VersionSummary(record=_version(r[:7]), members=r[7], member_annotations=r[8]) for r in rows
    ]


def version_members(conn: sqlite3.Connection, name: str, version: int) -> list[MemberAssetRow]:
    """版の構成のファイルと，その撮影・症例を SHA-256 の順に返す．

    目録にファイルが無い構成の行は含めない（`check` が manifest の不一致として見つける）．
    """
    rows = conn.execute(
        "SELECT a.sha256, a.ext, a.kind, a.capture_id, c.subject_id"
        " FROM dataset_members AS m"
        " JOIN assets AS a ON a.sha256 = m.sha256"
        " LEFT JOIN captures AS c ON c.capture_id = a.capture_id"
        " WHERE m.name = ? AND m.version = ? ORDER BY a.sha256",
        (name, version),
    )
    return [
        MemberAssetRow(sha256=r[0], ext=r[1], kind=r[2], capture_id=r[3], subject_id=r[4])
        for r in rows
    ]


def version_member_annotations(
    conn: sqlite3.Connection, name: str, version: int
) -> list[MemberAnnotationRow]:
    """版の構成で採用した所見を，ファイルの SHA-256・所見の ID の順に返す．

    訂正された所見（`superseded_by` あり）も版を作った時点の値のまま返す．
    """
    rows = conn.execute(
        "SELECT d.sha256, a.annotation_id, a.kind, a.capture_id, a.sha256, a.value"
        " FROM dataset_member_annotations AS d"
        " JOIN annotations AS a ON a.annotation_id = d.annotation_id"
        " WHERE d.name = ? AND d.version = ? ORDER BY d.sha256, a.annotation_id",
        (name, version),
    )
    return [MemberAnnotationRow(member_sha256=r[0], annotation=_annotation(r[1:])) for r in rows]


def insert_version(
    conn: sqlite3.Connection,
    record: VersionRecord,
    members: list[str],
    member_annotations: list[tuple[str, int]],
) -> None:
    """版の行と構成を追加する（呼び出し側のトランザクションの中で呼ぶ）．

    Args:
        conn: 目録への接続．
        record: 版の行（manifest の SHA-256 を計算済み）．
        members: 構成のファイルの SHA-256．
        member_annotations: 採用した所見（ファイルの SHA-256, 所見の ID）．
    """
    conn.execute(
        f"INSERT INTO dataset_versions ({_VERSION_COLUMNS}) VALUES (?, ?, ?, ?, ?, ?, ?)",
        (
            record.name,
            record.version,
            record.definition,
            record.hub_commit,
            record.manifest_sha256,
            record.created_at,
            record.note,
        ),
    )
    conn.executemany(
        "INSERT INTO dataset_members (name, version, sha256) VALUES (?, ?, ?)",
        [(record.name, record.version, sha256) for sha256 in members],
    )
    conn.executemany(
        "INSERT INTO dataset_member_annotations (name, version, sha256, annotation_id)"
        " VALUES (?, ?, ?, ?)",
        [
            (record.name, record.version, sha256, annotation_id)
            for sha256, annotation_id in member_annotations
        ],
    )