"""目録の集計（読み取りのみ）."""

import sqlite3


def count_kinds(conn: sqlite3.Connection) -> dict[str, int]:
    """種類の定義の件数を分類（`asset`・`annotation`）ごとに数える．

    Args:
        conn: 目録への接続（マイグレーション適用済み）．

    Returns:
        分類 → 件数．
    """
    rows = conn.execute("SELECT category, COUNT(*) FROM kinds GROUP BY category ORDER BY category")
    return {category: count for category, count in rows}


def count_assets_by_kind(conn: sqlite3.Connection) -> dict[str, int]:
    """ファイルの数をファイルの種類ごとに数える（登録の無い種類は 0）．

    Args:
        conn: 目録への接続．

    Returns:
        種類名 → ファイル数（種類名の順）．
    """
    rows = conn.execute(
        "SELECT k.kind, COUNT(a.sha256) FROM kinds AS k"
        " LEFT JOIN assets AS a ON a.kind = k.kind"
        " WHERE k.category = 'asset' GROUP BY k.kind ORDER BY k.kind"
    )
    return {kind: count for kind, count in rows}


def count_annotation_values(conn: sqlite3.Connection) -> dict[str, dict[str, int]]:
    """有効な所見（訂正されていないもの）の数を，所見の種類・値（JSON の文字列）ごとに数える．

    Args:
        conn: 目録への接続．

    Returns:
        所見の種類名 → 値の JSON → 件数．
    """
    rows = conn.execute(
        "SELECT kind, value, COUNT(*) FROM annotations WHERE superseded_by IS NULL"
        " GROUP BY kind, value ORDER BY kind, value"
    )
    result: dict[str, dict[str, int]] = {}
    for kind, value, count in rows:
        result.setdefault(kind, {})[value] = count
    return result


def count_superseded_annotations(conn: sqlite3.Connection) -> int:
    """訂正された（`superseded_by` のある）所見の数．"""
    (count,) = conn.execute(
        "SELECT COUNT(*) FROM annotations WHERE superseded_by IS NOT NULL"
    ).fetchone()
    return count


def count_subjects(conn: sqlite3.Connection) -> int:
    """症例の数．"""
    (count,) = conn.execute("SELECT COUNT(*) FROM subjects").fetchone()
    return count


def count_captures(conn: sqlite3.Connection) -> int:
    """撮影の数．"""
    (count,) = conn.execute("SELECT COUNT(*) FROM captures").fetchone()
    return count


def count_subjects_with_multiple_captures(conn: sqlite3.Connection) -> int:
    """撮影を 2 つ以上持つ症例の数（同じ患者の複数枚がまとまっているかの目安）．"""
    (count,) = conn.execute(
        "SELECT COUNT(*) FROM"
        " (SELECT subject_id FROM captures GROUP BY subject_id HAVING COUNT(*) >= 2)"
    ).fetchone()
    return count


def count_assets_without_capture(conn: sqlite3.Connection) -> int:
    """撮影にひも付かない（`capture_id` が NULL の）ファイルの数．"""
    (count,) = conn.execute("SELECT COUNT(*) FROM assets WHERE capture_id IS NULL").fetchone()
    return count


def count_valid_annotations_by_target(conn: sqlite3.Connection, kind: str) -> tuple[int, int]:
    """種類が一致する有効な所見（訂正されていないもの）の数を，付いた先ごとに数える．

    Args:
        conn: 目録への接続．
        kind: 所見の種類．

    Returns:
        （撮影に付いたものの数, ファイルに付いたものの数）．
    """
    (on_captures, on_files) = conn.execute(
        "SELECT COALESCE(SUM(capture_id IS NOT NULL), 0), COALESCE(SUM(sha256 IS NOT NULL), 0)"
        " FROM annotations WHERE kind = ? AND superseded_by IS NULL",
        (kind,),
    ).fetchone()
    return on_captures, on_files


def list_asset_files(conn: sqlite3.Connection) -> list[tuple[str, str]]:
    """目録の全ファイルの（SHA-256, 拡張子）を SHA-256 の順に返す（点検用）．"""
    rows = conn.execute("SELECT sha256, ext FROM assets ORDER BY sha256")
    return [(sha256, ext) for sha256, ext in rows]


def count_dangling_annotations(conn: sqlite3.Connection) -> int:
    """参照先（撮影・ファイル・訂正後の所見）が目録に無い所見の数．

    外部キー制約で通常は 0 になる．制約を無効にした接続での書き込み等で壊れていないかを見る．
    """
    (count,) = conn.execute(
        "SELECT COUNT(*) FROM annotations AS a"
        " WHERE (a.capture_id IS NOT NULL"
        "        AND NOT EXISTS (SELECT 1 FROM captures AS c WHERE c.capture_id = a.capture_id))"
        "    OR (a.sha256 IS NOT NULL"
        "        AND NOT EXISTS (SELECT 1 FROM assets AS s WHERE s.sha256 = a.sha256))"
        "    OR (a.superseded_by IS NOT NULL"
        "        AND NOT EXISTS"
        "            (SELECT 1 FROM annotations AS n WHERE n.annotation_id = a.superseded_by))"
    ).fetchone()
    return count


def count_broken_dataset_members(conn: sqlite3.Connection) -> int:
    """凍結した版の構成のうち，参照先（ファイル・所見・構成のファイル）が欠けている行の数．"""
    (members,) = conn.execute(
        "SELECT COUNT(*) FROM dataset_members AS m"
        " WHERE NOT EXISTS (SELECT 1 FROM assets AS a WHERE a.sha256 = m.sha256)"
        "    OR NOT EXISTS (SELECT 1 FROM dataset_versions AS v"
        "                   WHERE v.name = m.name AND v.version = m.version)"
    ).fetchone()
    (member_annotations,) = conn.execute(
        "SELECT COUNT(*) FROM dataset_member_annotations AS d"
        " WHERE NOT EXISTS (SELECT 1 FROM annotations AS a WHERE a.annotation_id = d.annotation_id)"
        "    OR NOT EXISTS (SELECT 1 FROM dataset_members AS m"
        "                   WHERE m.name = d.name AND m.version = d.version"
        "                     AND m.sha256 = d.sha256)"
    ).fetchone()
    return members + member_annotations