"""c_task_list / c_task_get (03-C §3.2): read-only task queries for the
console (v_task_board, v_task_detail). Unowned by any Unit until now --
built here because U-025 is the first Unit that actually needs them.

c_task_get's output goes beyond 03-C's literal "task + artifact 列表 + bug_record
列表" wording: v_task_detail (02-V §2.6) also needs the task's work_graph_run(s)
(for the to-do tree built from graph_manifest, 01-M 1.3's "补充说明") and each
run's work_node_execution rows (status per to-do node, expandable
input/output/reflection_notes) -- 03-C's own wording undersells what the view
it feeds actually requires, so this returns the fuller shape rather than
forcing the frontend to make N more round trips.
"""

import json


def list_tasks(conn, project_id, status=None, org_node=None, keyword=None):
    query = (
        "SELECT task_id, goal_text, task_type, status, assigned_org_node, loop_round, "
        "created_at, updated_at FROM task WHERE project_id = ?"
    )
    params = [project_id]
    if status:
        query += " AND status = ?"
        params.append(status)
    if org_node:
        query += " AND assigned_org_node = ?"
        params.append(org_node)
    if keyword:
        query += " AND goal_text LIKE ?"
        params.append("%" + keyword + "%")
    query += " ORDER BY updated_at DESC"
    rows = conn.execute(query, params).fetchall()
    return [
        {
            "task_id": r[0], "goal_text": r[1], "task_type": r[2], "status": r[3],
            "assigned_org_node": r[4], "loop_round": r[5], "created_at": r[6], "updated_at": r[7],
        }
        for r in rows
    ]


class TaskNotFoundError(Exception):
    """NOT_FOUND -- 03-C §3.2: c_task_get on a nonexistent task_id."""


def get_task(conn, task_id):
    row = conn.execute(
        "SELECT task_id, project_id, goal_text, task_type, origin_ref, created_via, status, route, "
        "loop_round, blocked_reason, assigned_org_node, created_at, updated_at FROM task WHERE task_id = ?",
        (task_id,),
    ).fetchone()
    if row is None:
        raise TaskNotFoundError(task_id)

    task = {
        "task_id": row[0], "project_id": row[1], "goal_text": row[2], "task_type": row[3],
        "origin_ref": row[4], "created_via": row[5], "status": row[6], "route": row[7],
        "loop_round": row[8], "blocked_reason": row[9], "assigned_org_node": row[10],
        "created_at": row[11], "updated_at": row[12],
    }

    artifacts = conn.execute(
        "SELECT artifact_id, artifact_type, produced_by_run_id, file_path, git_commit_sha, "
        "validation_status, created_at FROM artifact WHERE task_id = ? ORDER BY created_at",
        (task_id,),
    ).fetchall()
    task["artifacts"] = [
        {
            "artifact_id": a[0], "artifact_type": a[1], "produced_by_run_id": a[2], "file_path": a[3],
            "git_commit_sha": a[4], "validation_status": a[5], "created_at": a[6],
        }
        for a in artifacts
    ]

    bugs = conn.execute(
        "SELECT bug_id, source, description, root_cause_category, suggested_route, status, repro_steps "
        "FROM bug_record WHERE task_id = ?",
        (task_id,),
    ).fetchall()
    task["bug_records"] = [
        {
            "bug_id": b[0], "source": b[1], "description": b[2], "root_cause_category": b[3],
            "suggested_route": b[4], "status": b[5], "repro_steps": b[6],
        }
        for b in bugs
    ]

    runs = conn.execute(
        "SELECT run_id, org_node, graph_manifest, status, started_at, completed_at "
        "FROM work_graph_run WHERE task_id = ? ORDER BY started_at",
        (task_id,),
    ).fetchall()
    task["work_graph_runs"] = [
        {
            "run_id": w[0], "org_node": w[1],
            "graph_manifest": json.loads(w[2]) if w[2] else None,
            "status": w[3], "started_at": w[4], "completed_at": w[5],
            "nodes": _work_nodes_for_run(conn, w[0]),
        }
        for w in runs
    ]
    return task


def _work_nodes_for_run(conn, run_id):
    rows = conn.execute(
        "SELECT node_exec_id, node_id, subagent_type, status, retry_count, input_payload, "
        "output_payload, reflection_notes, git_worktree_ref "
        "FROM work_node_execution WHERE run_id = ? ORDER BY node_exec_id",
        (run_id,),
    ).fetchall()
    return [
        {
            "node_exec_id": n[0], "node_id": n[1], "subagent_type": n[2], "status": n[3],
            "retry_count": n[4],
            "input_payload": json.loads(n[5]) if n[5] else None,
            "output_payload": json.loads(n[6]) if n[6] else None,
            "reflection_notes": n[7], "git_worktree_ref": n[8],
        }
        for n in rows
    ]