foodlinkk-command-center/cockpit/app/services/agent_approvals.py

"""Agent action approval queue — gate before executing sensitive queries."""
from __future__ import annotations

import json
from typing import Any, Optional

from app.db import execute, fetch_all, fetch_one


def _serialize(row: dict[str, Any] | None) -> dict[str, Any] | None:
    if not row:
        return None
    out = dict(row)
    for key, val in list(out.items()):
        if hasattr(val, "isoformat"):
            out[key] = val.isoformat()
    return out


def has_pending_request(agent_key: str, action_type: str | None = None) -> bool:
    clauses = ["agent_key = %s", "status = 'pending'", "created_at >= CURRENT_DATE"]
    params: list[Any] = [agent_key.strip().lower()]
    if action_type:
        clauses.append("action_type = %s")
        params.append(action_type)
    row = fetch_one(
        f"SELECT id FROM agent_action_requests WHERE {' AND '.join(clauses)} LIMIT 1",
        tuple(params),
    )
    return bool(row)


def create_request(
    agent_key: str,
    title: str,
    action_type: str = "query",
    query_payload: dict[str, Any] | None = None,
) -> dict[str, Any]:
    key = (agent_key or "").strip().lower()
    if not key or not title.strip():
        raise ValueError("agent_key and title are required")

    row = fetch_one(
        """
        INSERT INTO agent_action_requests (agent_key, action_type, title, query_payload, status)
        VALUES (%s, %s, %s, %s::jsonb, 'pending')
        RETURNING *
        """,
        (key, action_type, title.strip(), json.dumps(query_payload or {})),
    )
    req = _serialize(row) or {}

    try:
        execute(
            """
            INSERT INTO agent_events (agent_name, agent_type, event_type, title, body, status, channel, metadata)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s::jsonb)
            """,
            (
                key,
                "agent_request",
                "approval_request",
                title.strip(),
                f"Wacht op goedkeuring — {action_type}",
                "needs_approval",
                "agents",
                json.dumps({"request_id": req.get("id"), "action_type": action_type}),
            ),
        )
    except Exception:
        pass
    return req


def list_requests(status: Optional[str] = None, limit: int = 50) -> list[dict[str, Any]]:
    clauses, params = [], []
    if status:
        clauses.append("status = %s")
        params.append(status)
    where = (" WHERE " + " AND ".join(clauses)) if clauses else ""
    safe_limit = max(1, min(limit, 200))
    rows = fetch_all(
        f"SELECT * FROM agent_action_requests{where} ORDER BY created_at DESC LIMIT %s",
        tuple(params + [safe_limit]),
    )
    return [_serialize(r) for r in rows]


def get_request(request_id: int) -> dict[str, Any] | None:
    return _serialize(fetch_one("SELECT * FROM agent_action_requests WHERE id = %s", (request_id,)))


def approve_request(request_id: int, approved_by: str = "ceo") -> dict[str, Any]:
    row = fetch_one(
        """
        UPDATE agent_action_requests
        SET status = 'approved', approved_by = %s, reviewed_at = NOW()
        WHERE id = %s AND status = 'pending'
        RETURNING *
        """,
        (approved_by, request_id),
    )
    if not row:
        raise ValueError("Request not found or not pending")
    req = _serialize(row) or {}
    try:
        execute(
            """
            UPDATE agent_events SET status = 'approved'
            WHERE status = 'needs_approval'
              AND metadata->>'request_id' = %s
            """,
            (str(request_id),),
        )
    except Exception:
        pass
    return req


def reject_request(request_id: int, reason: str = "", rejected_by: str = "ceo") -> dict[str, Any]:
    row = fetch_one(
        """
        UPDATE agent_action_requests
        SET status = 'rejected', approved_by = %s, rejection_reason = %s, reviewed_at = NOW()
        WHERE id = %s AND status = 'pending'
        RETURNING *
        """,
        (rejected_by, (reason or "")[:500], request_id),
    )
    if not row:
        raise ValueError("Request not found or not pending")
    req = _serialize(row) or {}
    try:
        execute(
            """
            UPDATE agent_events SET status = 'rejected'
            WHERE status = 'needs_approval'
              AND metadata->>'request_id' = %s
            """,
            (str(request_id),),
        )
    except Exception:
        pass
    return req


def mark_executed(request_id: int, result: dict[str, Any] | None = None) -> dict[str, Any]:
    row = fetch_one(
        """
        UPDATE agent_action_requests
        SET status = 'executed', executed_at = NOW(), result = %s::jsonb
        WHERE id = %s AND status = 'approved'
        RETURNING *
        """,
        (json.dumps(result or {}), request_id),
    )
    if not row:
        raise ValueError("Request not approved or not found")
    req = _serialize(row) or {}

    try:
        from app.services import projects as project_svc

        payload = req.get("query_payload") or {}
        if isinstance(payload, str):
            import json as _json
            try:
                payload = _json.loads(payload)
            except Exception:
                payload = {}
        pid = payload.get("project_id")
        project_svc.register_agent_output(
            asset_type=str(req.get("action_type") or "agent_action"),
            title=req.get("title") or f"Agent actie #{request_id}",
            ref_id=str(request_id),
            payload={"result": result or {}, "action_type": req.get("action_type")},
            project_id=int(pid) if pid else None,
            source_agent=str(req.get("agent_key") or "agent"),
            created_by=str(req.get("approved_by") or "ceo"),
        )
    except Exception:
        pass

    return req


def require_approved(request_id: int) -> dict[str, Any]:
    req = get_request(request_id)
    if not req:
        raise PermissionError("Approval request not found")
    if req.get("status") != "approved":
        raise PermissionError(f"Request status is {req.get('status')}, approval required")
    return req


def _auto_approve_policy() -> dict[str, bool]:
    try:
        row = fetch_one("SELECT value FROM app_settings WHERE key = 'sysops_auto_approve'")
        if row and row.get("value"):
            val = row["value"]
            if isinstance(val, str):
                return json.loads(val)
            return dict(val)
    except Exception:
        pass
    return {"maintenance_scan": True, "config_backup": True}


def try_auto_approve_and_execute(request_id: int) -> dict[str, Any] | None:
    """Auto-approve low-risk SysOps requests when policy allows."""
    req = get_request(request_id)
    if not req or req.get("status") != "pending":
        return None
    key = str(req.get("agent_key") or "").lower()
    action = str(req.get("action_type") or "")
    policy = _auto_approve_policy()
    if key != "sysops" or not policy.get(action):
        return None
    approved = approve_request(request_id, approved_by="auto_policy")
    import os

    import httpx

    tools_url = os.getenv("TOOLS_API_URL", "http://tools-api:8700").rstrip("/")
    result_payload: dict[str, Any] = {}
    try:
        if action == "config_backup":
            with httpx.Client(timeout=180.0) as client:
                resp = client.post(f"{tools_url}/ops/backup/run", json={"approval_request_id": request_id})
            result_payload = resp.json() if resp.status_code < 500 else {"ok": False, "detail": resp.text}
        elif action == "maintenance_scan":
            with httpx.Client(timeout=120.0) as client:
                resp = client.post(f"{tools_url}/ops/maintenance/scan")
            result_payload = resp.json() if resp.status_code < 500 else {"ok": False, "detail": resp.text}
    except Exception as exc:
        result_payload = {"ok": False, "detail": str(exc)}
    executed = mark_executed(request_id, result_payload)
    return {"approved": approved, "executed": executed, "auto": True}