"""RoadmapItem criteria / gate checklist service (AP1.4b)."""

from __future__ import annotations

from datetime import datetime, timezone
from typing import Any, Literal, Optional

from psycopg2.extras import RealDictCursor

from db import get_connection
from services.audit import log_audit
from services.decisions import create_decision

CriterionKind = Literal["manual", "evidence_required", "review_required", "metric"]
CriterionStatus = Literal["open", "satisfied", "waived", "deferred", "failed"]

CRITERION_KINDS = frozenset({"manual", "evidence_required", "review_required", "metric"})
CRITERION_STATUSES = frozenset({"open", "satisfied", "waived", "deferred", "failed"})
CLOSED_CRITERION_STATUSES = frozenset({"satisfied", "waived", "deferred"})
_TERMINAL_ITEM_STATUSES = frozenset({"reached", "moved", "discarded"})

_CRITERION_COLUMNS = """
    id, tenant_id, roadmap_item_id, sort_order, title, description,
    criterion_kind, verification_hint, status,
    waived_or_deferred_decision_id, satisfied_by_evidence_id, satisfied_at,
    created_at, updated_at
"""


def _serialize_row(row: dict[str, Any]) -> dict[str, Any]:
    result = dict(row)
    for key in (
        "id",
        "tenant_id",
        "roadmap_item_id",
        "waived_or_deferred_decision_id",
        "satisfied_by_evidence_id",
    ):
        if result.get(key):
            result[key] = str(result[key])
    for ts in ("satisfied_at", "created_at", "updated_at"):
        if result.get(ts):
            result[ts] = result[ts].isoformat()
    return result


def _validate_kind(kind: str) -> None:
    if kind not in CRITERION_KINDS:
        raise ValueError(f"Ungültiger criterion_kind: {kind}")


def _validate_status(status: str) -> None:
    if status not in CRITERION_STATUSES:
        raise ValueError(f"Ungültiger Kriterien-Status: {status}")


def _get_item_or_raise(*, tenant_id: str, item_id: str) -> dict[str, Any]:
    from services.roadmap import get_roadmap_item

    item = get_roadmap_item(tenant_id=tenant_id, item_id=item_id)
    if not item:
        raise ValueError("RoadmapItem nicht gefunden")
    return item


def _reopen_item_if_reached(
    cur,
    *,
    tenant_id: str,
    item: dict[str, Any],
    user_id: Optional[str],
    audit_reason: str,
) -> None:
    if item["status"] != "reached":
        return
    if item.get("item_type") == "maturity_stage":
        from services.progression_graph import enforce_single_active_work_gate_per_lane

        enforce_single_active_work_gate_per_lane(
            cur,
            tenant_id=tenant_id,
            initiative_id=str(item["initiative_id"]),
            keep_active_id=item["id"],
        )
    cur.execute(
        """
        UPDATE roadmap_items
        SET status = 'active', updated_at = NOW()
        WHERE id = %s AND tenant_id = %s
        """,
        (item["id"], tenant_id),
    )
    log_audit(
        "roadmap_item.reopened",
        user_id=user_id,
        tenant_id=tenant_id,
        details={
            "roadmap_item_id": item["id"],
            "reason": audit_reason,
            "from_status": "reached",
            "to_status": "active",
        },
    )


def create_default_criterion(
    cur,
    *,
    tenant_id: str,
    roadmap_item_id: str,
    title: str = "Gate allgemein",
    description: str = "",
    sort_order: int = 0,
) -> dict[str, Any]:
    cur.execute(
        f"""
        INSERT INTO roadmap_item_criteria (
            tenant_id, roadmap_item_id, sort_order, title, description, criterion_kind
        )
        VALUES (%s, %s, %s, %s, %s, 'evidence_required')
        RETURNING {_CRITERION_COLUMNS}
        """,
        (tenant_id, roadmap_item_id, sort_order, title, description or title),
    )
    return _serialize_row(dict(cur.fetchone()))


def list_criteria_for_item(*, tenant_id: str, item_id: str) -> list[dict[str, Any]]:
    _get_item_or_raise(tenant_id=tenant_id, item_id=item_id)
    conn = get_connection()
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(
                f"""
                SELECT {_CRITERION_COLUMNS}
                FROM roadmap_item_criteria
                WHERE tenant_id = %s AND roadmap_item_id = %s
                ORDER BY sort_order ASC, created_at ASC
                """,
                (tenant_id, item_id),
            )
            return [_serialize_row(dict(r)) for r in cur.fetchall()]
    finally:
        conn.close()


def get_criterion(*, tenant_id: str, criterion_id: str) -> Optional[dict[str, Any]]:
    conn = get_connection()
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(
                f"""
                SELECT {_CRITERION_COLUMNS}
                FROM roadmap_item_criteria
                WHERE id = %s AND tenant_id = %s
                """,
                (criterion_id, tenant_id),
            )
            row = cur.fetchone()
            return _serialize_row(dict(row)) if row else None
    finally:
        conn.close()


def create_criterion(
    *,
    tenant_id: str,
    item_id: str,
    title: str,
    description: str = "",
    criterion_kind: CriterionKind = "manual",
    verification_hint: str = "",
    sort_order: int = 0,
    user_id: Optional[str] = None,
) -> dict[str, Any]:
    title = title.strip()
    if not title:
        raise ValueError("Titel ist erforderlich")
    _validate_kind(criterion_kind)
    item = _get_item_or_raise(tenant_id=tenant_id, item_id=item_id)

    conn = get_connection()
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(
                f"""
                INSERT INTO roadmap_item_criteria (
                    tenant_id, roadmap_item_id, sort_order, title, description,
                    criterion_kind, verification_hint
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s)
                RETURNING {_CRITERION_COLUMNS}
                """,
                (
                    tenant_id,
                    item_id,
                    sort_order,
                    title,
                    description,
                    criterion_kind,
                    verification_hint,
                ),
            )
            row = _serialize_row(dict(cur.fetchone()))
            if item["status"] == "reached":
                _reopen_item_if_reached(
                    cur,
                    tenant_id=tenant_id,
                    item=item,
                    user_id=user_id,
                    audit_reason="criterion_created_on_reached_gate",
                )
        conn.commit()
    finally:
        conn.close()

    log_audit(
        "roadmap_item_criterion.created",
        user_id=user_id,
        tenant_id=tenant_id,
        details={"criterion_id": row["id"], "roadmap_item_id": item_id, "title": title},
    )
    return row


def update_criterion(
    *,
    tenant_id: str,
    criterion_id: str,
    user_id: Optional[str] = None,
    title: Optional[str] = None,
    description: Optional[str] = None,
    criterion_kind: Optional[CriterionKind] = None,
    verification_hint: Optional[str] = None,
    sort_order: Optional[int] = None,
    change_reason: str = "",
) -> Optional[dict[str, Any]]:
    existing = get_criterion(tenant_id=tenant_id, criterion_id=criterion_id)
    if not existing:
        return None
    item = _get_item_or_raise(tenant_id=tenant_id, item_id=existing["roadmap_item_id"])

    updates: list[str] = []
    params: list[Any] = []
    if title is not None:
        title = title.strip()
        if not title:
            raise ValueError("Titel ist erforderlich")
        updates.append("title = %s")
        params.append(title)
    if description is not None:
        updates.append("description = %s")
        params.append(description)
    if criterion_kind is not None:
        _validate_kind(criterion_kind)
        updates.append("criterion_kind = %s")
        params.append(criterion_kind)
    if verification_hint is not None:
        updates.append("verification_hint = %s")
        params.append(verification_hint)
    if sort_order is not None:
        updates.append("sort_order = %s")
        params.append(sort_order)
    if not updates:
        return existing

    if item["status"] == "reached" and not change_reason.strip():
        raise ValueError(
            "Änderung an Kriterien bei erreichtem Gate erfordert Begründung (change_reason)"
        )

    updates.append("updated_at = NOW()")
    params.extend([criterion_id, tenant_id])

    conn = get_connection()
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(
                f"""
                UPDATE roadmap_item_criteria
                SET {", ".join(updates)}
                WHERE id = %s AND tenant_id = %s
                RETURNING {_CRITERION_COLUMNS}
                """,
                params,
            )
            row = cur.fetchone()
            if not row:
                return None
            result = _serialize_row(dict(row))
            if item["status"] == "reached":
                _reopen_item_if_reached(
                    cur,
                    tenant_id=tenant_id,
                    item=item,
                    user_id=user_id,
                    audit_reason=change_reason.strip() or "criterion_updated_on_reached_gate",
                )
        conn.commit()
    finally:
        conn.close()

    log_audit(
        "roadmap_item_criterion.updated",
        user_id=user_id,
        tenant_id=tenant_id,
        details={
            "criterion_id": criterion_id,
            "roadmap_item_id": existing["roadmap_item_id"],
            "change_reason": change_reason.strip() or None,
        },
    )
    return result


def delete_criterion(
    *, tenant_id: str, criterion_id: str, user_id: Optional[str] = None
) -> bool:
    existing = get_criterion(tenant_id=tenant_id, criterion_id=criterion_id)
    if not existing:
        return False

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT COUNT(*) FROM roadmap_item_criteria
                WHERE tenant_id = %s AND roadmap_item_id = %s
                """,
                (tenant_id, existing["roadmap_item_id"]),
            )
            if cur.fetchone()[0] <= 1:
                raise ValueError("Letztes Kriterium eines Gates kann nicht gelöscht werden")

            cur.execute(
                """
                DELETE FROM roadmap_item_criteria
                WHERE id = %s AND tenant_id = %s
                RETURNING id
                """,
                (criterion_id, tenant_id),
            )
            deleted = cur.fetchone() is not None
        conn.commit()
    finally:
        conn.close()

    if deleted:
        log_audit(
            "roadmap_item_criterion.deleted",
            user_id=user_id,
            tenant_id=tenant_id,
            details={
                "criterion_id": criterion_id,
                "roadmap_item_id": existing["roadmap_item_id"],
            },
        )
    return deleted


def _accepted_evidence_for_item(cur, *, tenant_id: str, initiative_id: str, item_id: str):
    cur.execute(
        """
        SELECT id FROM evidence
        WHERE tenant_id = %s AND initiative_id = %s AND status = 'accepted'
          AND (roadmap_item_id = %s OR milestone_id = %s)
        ORDER BY updated_at DESC
        LIMIT 1
        """,
        (tenant_id, initiative_id, item_id, item_id),
    )
    row = cur.fetchone()
    return str(row["id"]) if row else None


def _completed_review_for_item(cur, *, tenant_id: str, initiative_id: str, item_id: str) -> bool:
    cur.execute(
        """
        SELECT 1 FROM reviews
        WHERE tenant_id = %s AND initiative_id = %s AND status = 'completed'
          AND (roadmap_item_id = %s OR milestone_id = %s)
        LIMIT 1
        """,
        (tenant_id, initiative_id, item_id, item_id),
    )
    return cur.fetchone() is not None


def satisfy_criterion(
    *,
    tenant_id: str,
    criterion_id: str,
    user_id: Optional[str] = None,
    evidence_id: Optional[str] = None,
) -> dict[str, Any]:
    existing = get_criterion(tenant_id=tenant_id, criterion_id=criterion_id)
    if not existing:
        raise ValueError("Kriterium nicht gefunden")
    item = _get_item_or_raise(tenant_id=tenant_id, item_id=existing["roadmap_item_id"])
    if item["status"] in _TERMINAL_ITEM_STATUSES and item["status"] != "reached":
        raise ValueError(f"Gate ist terminal: {item['status']}")

    conn = get_connection()
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            linked_evidence = evidence_id
            kind = existing["criterion_kind"]
            if kind == "evidence_required":
                if linked_evidence:
                    cur.execute(
                        """
                        SELECT id FROM evidence
                        WHERE id = %s AND tenant_id = %s AND status = 'accepted'
                          AND initiative_id = %s
                        """,
                        (linked_evidence, tenant_id, item["initiative_id"]),
                    )
                    if not cur.fetchone():
                        raise ValueError("Evidence nicht gefunden oder nicht akzeptiert")
                else:
                    linked_evidence = _accepted_evidence_for_item(
                        cur,
                        tenant_id=tenant_id,
                        initiative_id=item["initiative_id"],
                        item_id=item["id"],
                    )
                    if not linked_evidence:
                        raise ValueError(
                            "Evidence-Kriterium: akzeptierter Nachweis am Plan-Element erforderlich"
                        )
            elif kind == "review_required":
                if not _completed_review_for_item(
                    cur,
                    tenant_id=tenant_id,
                    initiative_id=item["initiative_id"],
                    item_id=item["id"],
                ):
                    raise ValueError(
                        "Review-Kriterium: abgeschlossenes Review am Plan-Element erforderlich"
                    )

            cur.execute(
                f"""
                UPDATE roadmap_item_criteria
                SET status = 'satisfied',
                    satisfied_by_evidence_id = %s,
                    satisfied_at = NOW(),
                    waived_or_deferred_decision_id = NULL,
                    updated_at = NOW()
                WHERE id = %s AND tenant_id = %s
                RETURNING {_CRITERION_COLUMNS}
                """,
                (linked_evidence, criterion_id, tenant_id),
            )
            result = _serialize_row(dict(cur.fetchone()))
        conn.commit()
    finally:
        conn.close()

    log_audit(
        "roadmap_item_criterion.satisfied",
        user_id=user_id,
        tenant_id=tenant_id,
        details={
            "criterion_id": criterion_id,
            "roadmap_item_id": existing["roadmap_item_id"],
            "evidence_id": linked_evidence,
        },
    )
    return result


def _waive_or_defer(
    *,
    tenant_id: str,
    criterion_id: str,
    action: Literal["waived", "deferred"],
    user_id: Optional[str],
    decision_title: str,
    decision_description: str,
) -> dict[str, Any]:
    existing = get_criterion(tenant_id=tenant_id, criterion_id=criterion_id)
    if not existing:
        raise ValueError("Kriterium nicht gefunden")
    item = _get_item_or_raise(tenant_id=tenant_id, item_id=existing["roadmap_item_id"])
    if item["status"] in _TERMINAL_ITEM_STATUSES and item["status"] != "reached":
        raise ValueError(f"Gate ist terminal: {item['status']}")

    decision_title = decision_title.strip()
    if not decision_title:
        raise ValueError("Decision-Titel (Begründung) ist erforderlich")

    prefix = f"criterion_{action}:"
    decision = create_decision(
        tenant_id=tenant_id,
        initiative_id=item["initiative_id"],
        title=f"{prefix}{criterion_id} — {decision_title}",
        description=decision_description,
        status="decided",
        user_id=user_id,
    )

    conn = get_connection()
    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(
                f"""
                UPDATE roadmap_item_criteria
                SET status = %s,
                    waived_or_deferred_decision_id = %s,
                    satisfied_by_evidence_id = NULL,
                    satisfied_at = NULL,
                    updated_at = NOW()
                WHERE id = %s AND tenant_id = %s
                RETURNING {_CRITERION_COLUMNS}
                """,
                (action, decision["id"], criterion_id, tenant_id),
            )
            result = _serialize_row(dict(cur.fetchone()))
        conn.commit()
    finally:
        conn.close()

    log_audit(
        f"roadmap_item_criterion.{action}",
        user_id=user_id,
        tenant_id=tenant_id,
        details={
            "criterion_id": criterion_id,
            "roadmap_item_id": existing["roadmap_item_id"],
            "decision_id": decision["id"],
        },
    )
    return result


def waive_criterion(
    *,
    tenant_id: str,
    criterion_id: str,
    user_id: Optional[str] = None,
    decision_title: str,
    decision_description: str = "",
) -> dict[str, Any]:
    return _waive_or_defer(
        tenant_id=tenant_id,
        criterion_id=criterion_id,
        action="waived",
        user_id=user_id,
        decision_title=decision_title,
        decision_description=decision_description,
    )


def defer_criterion(
    *,
    tenant_id: str,
    criterion_id: str,
    user_id: Optional[str] = None,
    decision_title: str,
    decision_description: str = "",
) -> dict[str, Any]:
    return _waive_or_defer(
        tenant_id=tenant_id,
        criterion_id=criterion_id,
        action="deferred",
        user_id=user_id,
        decision_title=decision_title,
        decision_description=decision_description,
    )


def criteria_progress_for_initiative(
    *, tenant_id: str, initiative_id: str
) -> dict[str, dict[str, int]]:
    from services.initiatives import get_initiative

    if not get_initiative(tenant_id=tenant_id, initiative_id=initiative_id):
        raise ValueError("Initiative nicht gefunden")

    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT
                    ri.id::text,
                    COUNT(c.id) AS total,
                    COUNT(c.id) FILTER (
                        WHERE c.status IN ('satisfied', 'waived', 'deferred')
                    ) AS closed
                FROM roadmap_items ri
                JOIN roadmaps r ON r.id = ri.roadmap_id AND r.tenant_id = ri.tenant_id
                LEFT JOIN roadmap_item_criteria c
                    ON c.roadmap_item_id = ri.id AND c.tenant_id = ri.tenant_id
                WHERE ri.tenant_id = %s AND r.initiative_id = %s
                GROUP BY ri.id
                """,
                (tenant_id, initiative_id),
            )
            return {
                row[0]: {
                    "total": row[1],
                    "closed": row[2],
                    "open": row[1] - row[2],
                }
                for row in cur.fetchall()
            }
    finally:
        conn.close()


def criteria_progress(*, tenant_id: str, item_id: str) -> dict[str, int]:
    conn = get_connection()
    try:
        with conn.cursor() as cur:
            cur.execute(
                """
                SELECT
                    COUNT(*) AS total,
                    COUNT(*) FILTER (
                        WHERE status IN ('satisfied', 'waived', 'deferred')
                    ) AS closed
                FROM roadmap_item_criteria
                WHERE tenant_id = %s AND roadmap_item_id = %s
                """,
                (tenant_id, item_id),
            )
            total, closed = cur.fetchone()
            return {"total": total, "closed": closed, "open": total - closed}
    finally:
        conn.close()


def prepare_criteria_for_verify(
    cur,
    *,
    tenant_id: str,
    initiative_id: str,
    item_id: str,
) -> tuple[bool, str]:
    """Auto-satisfy open evidence/review criteria wenn Nachweis da; prüft Gesamtstatus."""
    cur.execute(
        f"""
        SELECT {_CRITERION_COLUMNS}
        FROM roadmap_item_criteria
        WHERE tenant_id = %s AND roadmap_item_id = %s
        ORDER BY sort_order ASC, created_at ASC
        """,
        (tenant_id, item_id),
    )
    criteria = [dict(r) for r in cur.fetchall()]
    if not criteria:
        return True, "no_criteria"

    evidence_id = _accepted_evidence_for_item(
        cur, tenant_id=tenant_id, initiative_id=initiative_id, item_id=item_id
    )
    has_review = _completed_review_for_item(
        cur, tenant_id=tenant_id, initiative_id=initiative_id, item_id=item_id
    )

    for crit in criteria:
        status = crit["status"]
        if status == "failed":
            return False, f"Kriterium fehlgeschlagen: {crit['title']}"
        if status in CLOSED_CRITERION_STATUSES:
            if status in ("waived", "deferred") and not crit["waived_or_deferred_decision_id"]:
                return False, f"Kriterium ohne Decision: {crit['title']}"
            continue
        if status == "open":
            kind = crit["criterion_kind"]
            if kind == "evidence_required" and evidence_id:
                cur.execute(
                    """
                    UPDATE roadmap_item_criteria
                    SET status = 'satisfied',
                        satisfied_by_evidence_id = %s,
                        satisfied_at = NOW(),
                        updated_at = NOW()
                    WHERE id = %s AND tenant_id = %s
                    """,
                    (evidence_id, crit["id"], tenant_id),
                )
                continue
            if kind == "review_required" and has_review:
                cur.execute(
                    """
                    UPDATE roadmap_item_criteria
                    SET status = 'satisfied', satisfied_at = NOW(), updated_at = NOW()
                    WHERE id = %s AND tenant_id = %s
                    """,
                    (crit["id"], tenant_id),
                )
                continue
            if kind == "manual":
                return False, f"Manuelles Kriterium noch offen: {crit['title']}"
            return False, f"Kriterium noch offen: {crit['title']}"

    return True, "criteria_ready"