"""SaaS multi-tenant schema and helpers (Postgres or SQLite via app.database)."""

from __future__ import annotations

import secrets
import time
import uuid
from datetime import datetime, timezone
from typing import Any

from app.database import get_connection, table_columns

# Short in-process caches — remote Postgres round-trips dominate chat latency.
_AGENT_CACHE: dict[str, tuple[float, dict[str, Any]]] = {}
_DOCS_CACHE: dict[str, tuple[float, list[dict[str, Any]]]] = {}
_CACHE_TTL_S = 30.0


def _now() -> str:
    return datetime.now(timezone.utc).isoformat()


def invalidate_agent_cache(agent_id: str | None = None) -> None:
    if agent_id:
        _AGENT_CACHE.pop(agent_id, None)
    else:
        _AGENT_CACHE.clear()


def invalidate_docs_cache(org_id: str | None = None, agent_id: str | None = None) -> None:
    if org_id and agent_id:
        _DOCS_CACHE.pop(f"{org_id}:{agent_id}", None)
        _DOCS_CACHE.pop(f"{org_id}:", None)
    elif org_id:
        prefix = f"{org_id}:"
        for key in list(_DOCS_CACHE):
            if key.startswith(prefix):
                _DOCS_CACHE.pop(key, None)
    else:
        _DOCS_CACHE.clear()
    # Statement job lists depend on the same docs
    try:
        from app.saas.kb_answer import _JOBS_LIST_CACHE
        from app.saas.knowledge_state import invalidate_knowledge_cache

        if org_id and agent_id:
            _JOBS_LIST_CACHE.pop(f"{org_id}:{agent_id}", None)
        elif not org_id:
            _JOBS_LIST_CACHE.clear()
        invalidate_knowledge_cache(org_id, agent_id)
    except Exception:
        pass


def _row_id(row: Any) -> str:
    return str(row["id"] if isinstance(row, dict) else row[0])


def init_saas_tables() -> None:
    with get_connection() as conn:
        conn.executescript(
            """
            -- saas_users (not "users") — avoids collision with chat-companion on shared Postgres
            CREATE TABLE IF NOT EXISTS saas_users (
                id            TEXT PRIMARY KEY,
                email         TEXT NOT NULL UNIQUE,
                password_hash TEXT NOT NULL,
                full_name     TEXT,
                created_at    TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS orgs (
                id            TEXT PRIMARY KEY,
                name          TEXT NOT NULL,
                slug          TEXT NOT NULL UNIQUE,
                plan          TEXT NOT NULL DEFAULT 'free',
                kb_mode       TEXT NOT NULL DEFAULT 'tenant',
                created_at    TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS memberships (
                id         TEXT PRIMARY KEY,
                user_id    TEXT NOT NULL REFERENCES saas_users(id),
                org_id     TEXT NOT NULL REFERENCES orgs(id),
                role       TEXT NOT NULL DEFAULT 'owner',
                created_at TEXT NOT NULL,
                UNIQUE(user_id, org_id)
            );

            CREATE TABLE IF NOT EXISTS kb_documents (
                id            TEXT PRIMARY KEY,
                org_id        TEXT NOT NULL REFERENCES orgs(id),
                filename      TEXT NOT NULL,
                content_type  TEXT,
                status        TEXT NOT NULL DEFAULT 'pending',
                chunk_count   INTEGER DEFAULT 0,
                error         TEXT,
                uploaded_by   TEXT REFERENCES saas_users(id),
                created_at    TEXT NOT NULL,
                updated_at    TEXT NOT NULL
            );

            CREATE TABLE IF NOT EXISTS api_keys (
                id          TEXT PRIMARY KEY,
                org_id      TEXT NOT NULL REFERENCES orgs(id),
                name        TEXT NOT NULL,
                key_prefix  TEXT NOT NULL,
                key_hash    TEXT NOT NULL,
                created_by  TEXT REFERENCES saas_users(id),
                created_at  TEXT NOT NULL,
                last_used   TEXT,
                revoked     INTEGER DEFAULT 0
            );

            CREATE TABLE IF NOT EXISTS agents (
                id            TEXT PRIMARY KEY,
                org_id        TEXT NOT NULL REFERENCES orgs(id),
                name          TEXT NOT NULL,
                description   TEXT,
                avatar_path   TEXT,
                kb_mode       TEXT NOT NULL DEFAULT 'tenant',
                welcome_message TEXT,
                is_main       INTEGER NOT NULL DEFAULT 0,
                created_by    TEXT REFERENCES saas_users(id),
                created_at    TEXT NOT NULL,
                updated_at    TEXT NOT NULL
            );

            CREATE INDEX IF NOT EXISTS idx_memberships_user ON memberships(user_id);
            CREATE INDEX IF NOT EXISTS idx_memberships_org ON memberships(org_id);
            CREATE INDEX IF NOT EXISTS idx_kb_docs_org ON kb_documents(org_id);
            CREATE INDEX IF NOT EXISTS idx_api_keys_org ON api_keys(org_id);
            CREATE INDEX IF NOT EXISTS idx_agents_org ON agents(org_id);
            """
        )
        # Migrate legacy local `users` → `saas_users` (Postgres `users` is chat-companion — skip)
        try:
            legacy = table_columns(conn, "users")
            if "full_name" in legacy and "email" in legacy:
                conn.execute(
                    """
                    INSERT INTO saas_users(id, email, password_hash, full_name, created_at)
                    SELECT id, email, password_hash, full_name, created_at FROM users
                    WHERE id NOT IN (SELECT id FROM saas_users)
                    """
                )
        except Exception:
            pass

        # Migrations for existing DBs
        cols = table_columns(conn, "kb_documents")
        if "agent_id" not in cols:
            conn.execute("ALTER TABLE kb_documents ADD COLUMN agent_id TEXT")
        conn.execute(
            "CREATE INDEX IF NOT EXISTS idx_kb_docs_agent ON kb_documents(agent_id)"
        )
        for col, decl in (
            ("progress_pct", "INTEGER DEFAULT 0"),
            ("progress_stage", "TEXT"),
            ("page_count", "INTEGER DEFAULT 0"),
            ("pages_processed", "INTEGER DEFAULT 0"),
            ("chunks_created", "INTEGER DEFAULT 0"),
            ("embeddings_done", "INTEGER DEFAULT 0"),
            ("vectors_stored", "INTEGER DEFAULT 0"),
        ):
            if col not in cols:
                conn.execute(f"ALTER TABLE kb_documents ADD COLUMN {col} {decl}")
                cols = table_columns(conn, "kb_documents")
        agent_cols = table_columns(conn, "agents")
        if "is_main" not in agent_cols:
            conn.execute("ALTER TABLE agents ADD COLUMN is_main INTEGER NOT NULL DEFAULT 0")
        # Promote earliest Alex (or earliest agent) to main per org
        org_ids = [_row_id(r) for r in conn.execute("SELECT id FROM orgs").fetchall()]
        for oid in org_ids:
            main = conn.execute(
                "SELECT id FROM agents WHERE org_id = ? AND is_main = 1 LIMIT 1",
                (oid,),
            ).fetchone()
            if main:
                continue
            alex = conn.execute(
                "SELECT id FROM agents WHERE org_id = ? AND lower(name) = 'alex' ORDER BY created_at ASC LIMIT 1",
                (oid,),
            ).fetchone()
            pick = alex or conn.execute(
                "SELECT id FROM agents WHERE org_id = ? ORDER BY created_at ASC LIMIT 1",
                (oid,),
            ).fetchone()
            if pick:
                conn.execute(
                    "UPDATE agents SET is_main = 1, name = 'Alex' WHERE id = ?",
                    (_row_id(pick),),
                )

    # Financial Document Intelligence durable schema
    from app.fdi.schema import init_fdi_tables

    init_fdi_tables()


def create_user_with_org(
    *,
    email: str,
    password_hash: str,
    full_name: str,
    org_name: str,
) -> dict[str, Any]:
    user_id = str(uuid.uuid4())
    org_id = str(uuid.uuid4())
    membership_id = str(uuid.uuid4())
    now = _now()
    slug_base = "".join(c.lower() if c.isalnum() else "-" for c in org_name).strip("-") or "org"
    slug = f"{slug_base[:40]}-{secrets.token_hex(3)}"

    with get_connection() as conn:
        conn.execute(
            "INSERT INTO saas_users(id, email, password_hash, full_name, created_at) VALUES (?,?,?,?,?)",
            (user_id, email.lower().strip(), password_hash, full_name.strip(), now),
        )
        conn.execute(
            "INSERT INTO orgs(id, name, slug, plan, kb_mode, created_at) VALUES (?,?,?,?,?,?)",
            (org_id, org_name.strip(), slug, "free", "tenant", now),
        )
        conn.execute(
            "INSERT INTO memberships(id, user_id, org_id, role, created_at) VALUES (?,?,?,?,?)",
            (membership_id, user_id, org_id, "owner", now),
        )

    # Main chatbot for every new workspace
    create_agent(
        org_id=org_id,
        name="Alex",
        description="Your main accounting and finance assistant",
        created_by=user_id,
        kb_mode="tenant",
        welcome_message="Hello! I'm Alex — your accounting assistant. Ask about spend, merchants, or upload documents in Knowledge.",
        is_main=True,
    )

    return {
        "user_id": user_id,
        "email": email.lower().strip(),
        "full_name": full_name.strip(),
        "org_id": org_id,
        "org_name": org_name.strip(),
        "org_slug": slug,
        "role": "owner",
        "kb_mode": "tenant",
        "plan": "free",
    }


def get_user_by_email(email: str) -> dict[str, Any] | None:
    with get_connection() as conn:
        row = conn.execute(
            "SELECT * FROM saas_users WHERE email = ?",
            (email.lower().strip(),),
        ).fetchone()
        return dict(row) if row else None


def update_user_password(user_id: str, password_hash: str, *, full_name: str | None = None) -> None:
    with get_connection() as conn:
        if full_name is not None:
            conn.execute(
                "UPDATE saas_users SET password_hash = ?, full_name = ? WHERE id = ?",
                (password_hash, full_name.strip(), user_id),
            )
        else:
            conn.execute(
                "UPDATE saas_users SET password_hash = ? WHERE id = ?",
                (password_hash, user_id),
            )


def ensure_admin_account(
    *,
    email: str = "admin@gmail.com",
    password: str = "Thetitan@1234",
    full_name: str = "Admin",
    org_name: str = "Admin Workspace",
) -> dict[str, Any]:
    """Create or refresh the demo admin login used by the studio."""
    from app.saas.security import hash_password

    existing = get_user_by_email(email)
    pwd_hash = hash_password(password)
    if existing:
        update_user_password(existing["id"], pwd_hash, full_name=full_name)
        orgs = list_user_orgs(existing["id"])
        if orgs:
            ensure_main_agent(orgs[0]["id"], created_by=existing["id"])
        return {"user_id": existing["id"], "email": email.lower().strip(), "created": False}
    created = create_user_with_org(
        email=email,
        password_hash=pwd_hash,
        full_name=full_name,
        org_name=org_name,
    )
    return {**created, "created": True}


def get_user_by_id(user_id: str) -> dict[str, Any] | None:
    with get_connection() as conn:
        row = conn.execute("SELECT * FROM saas_users WHERE id = ?", (user_id,)).fetchone()
        return dict(row) if row else None


def get_org(org_id: str) -> dict[str, Any] | None:
    with get_connection() as conn:
        row = conn.execute("SELECT * FROM orgs WHERE id = ?", (org_id,)).fetchone()
        return dict(row) if row else None


def get_membership(user_id: str, org_id: str) -> dict[str, Any] | None:
    with get_connection() as conn:
        row = conn.execute(
            "SELECT * FROM memberships WHERE user_id = ? AND org_id = ?",
            (user_id, org_id),
        ).fetchone()
        return dict(row) if row else None


def list_user_orgs(user_id: str) -> list[dict[str, Any]]:
    with get_connection() as conn:
        rows = conn.execute(
            """
            SELECT o.*, m.role
            FROM orgs o
            JOIN memberships m ON m.org_id = o.id
            WHERE m.user_id = ?
            ORDER BY o.created_at ASC
            """,
            (user_id,),
        ).fetchall()
        return [dict(r) for r in rows]


def set_org_kb_mode(org_id: str, kb_mode: str) -> None:
    if kb_mode not in {"platform", "tenant", "combined"}:
        raise ValueError("kb_mode must be platform, tenant, or combined")
    with get_connection() as conn:
        conn.execute("UPDATE orgs SET kb_mode = ? WHERE id = ?", (kb_mode, org_id))


def create_kb_document(
    *,
    org_id: str,
    filename: str,
    content_type: str | None,
    uploaded_by: str | None,
    agent_id: str | None = None,
) -> dict[str, Any]:
    doc_id = str(uuid.uuid4())
    now = _now()
    with get_connection() as conn:
        conn.execute(
            """
            INSERT INTO kb_documents(id, org_id, agent_id, filename, content_type, status, chunk_count, uploaded_by, created_at, updated_at)
            VALUES (?,?,?,?,?, 'pending', 0, ?, ?, ?)
            """,
            (doc_id, org_id, agent_id, filename, content_type, uploaded_by, now, now),
        )
    invalidate_docs_cache(org_id, agent_id)
    return get_kb_document(doc_id)  # type: ignore[return-value]


def get_kb_document(doc_id: str) -> dict[str, Any] | None:
    with get_connection() as conn:
        row = conn.execute("SELECT * FROM kb_documents WHERE id = ?", (doc_id,)).fetchone()
        return dict(row) if row else None


def list_kb_documents(org_id: str, agent_id: str | None = None) -> list[dict[str, Any]]:
    cache_key = f"{org_id}:{agent_id or ''}"
    hit = _DOCS_CACHE.get(cache_key)
    now = time.monotonic()
    if hit and (now - hit[0]) < _CACHE_TTL_S:
        return hit[1]

    with get_connection() as conn:
        if agent_id:
            rows = conn.execute(
                """
                SELECT * FROM kb_documents
                WHERE org_id = ? AND agent_id = ?
                ORDER BY created_at DESC
                """,
                (org_id, agent_id),
            ).fetchall()
        else:
            rows = conn.execute(
                """
                SELECT * FROM kb_documents
                WHERE org_id = ?
                ORDER BY created_at DESC
                """,
                (org_id,),
            ).fetchall()
        docs = [dict(r) for r in rows]
    _DOCS_CACHE[cache_key] = (now, docs)
    return docs


def update_kb_document(
    doc_id: str,
    *,
    status: str | None = None,
    chunk_count: int | None = None,
    error: str | None = None,
    progress_pct: int | None = None,
    progress_stage: str | None = None,
    page_count: int | None = None,
    pages_processed: int | None = None,
    chunks_created: int | None = None,
    embeddings_done: int | None = None,
    vectors_stored: int | None = None,
) -> None:
    fields: list[str] = ["updated_at = ?"]
    params: list[Any] = [_now()]
    mapping = {
        "status": status,
        "chunk_count": chunk_count,
        "error": error,
        "progress_pct": progress_pct,
        "progress_stage": progress_stage,
        "page_count": page_count,
        "pages_processed": pages_processed,
        "chunks_created": chunks_created,
        "embeddings_done": embeddings_done,
        "vectors_stored": vectors_stored,
    }
    for col, val in mapping.items():
        if val is not None:
            fields.append(f"{col} = ?")
            params.append(val)
    params.append(doc_id)
    with get_connection() as conn:
        conn.execute(f"UPDATE kb_documents SET {', '.join(fields)} WHERE id = ?", params)
    # Drop docs cache — status/progress changes often during ingest
    _DOCS_CACHE.clear()


def delete_kb_document(doc_id: str) -> None:
    with get_connection() as conn:
        conn.execute("DELETE FROM kb_documents WHERE id = ?", (doc_id,))
    _DOCS_CACHE.clear()


def count_ready_kb_chunks(org_id: str, agent_id: str | None = None) -> int:
    with get_connection() as conn:
        if agent_id:
            row = conn.execute(
                """
                SELECT COALESCE(SUM(chunk_count), 0) AS c
                FROM kb_documents
                WHERE org_id = ? AND agent_id = ? AND status = 'ready'
                """,
                (org_id, agent_id),
            ).fetchone()
        else:
            row = conn.execute(
                """
                SELECT COALESCE(SUM(chunk_count), 0) AS c
                FROM kb_documents
                WHERE org_id = ? AND status = 'ready'
                """,
                (org_id,),
            ).fetchone()
        return int(row["c"] if row else 0)


# ── Chatbot agents ───────────────────────────────────────────────────────────

def create_agent(
    *,
    org_id: str,
    name: str,
    description: str | None = None,
    created_by: str | None = None,
    kb_mode: str = "tenant",
    welcome_message: str | None = None,
    avatar_path: str | None = None,
    is_main: bool = False,
) -> dict[str, Any]:
    agent_id = str(uuid.uuid4())
    now = _now()
    if kb_mode not in {"platform", "tenant", "combined"}:
        kb_mode = "tenant"
    with get_connection() as conn:
        if is_main:
            conn.execute("UPDATE agents SET is_main = 0 WHERE org_id = ?", (org_id,))
        conn.execute(
            """
            INSERT INTO agents(id, org_id, name, description, avatar_path, kb_mode, welcome_message, is_main, created_by, created_at, updated_at)
            VALUES (?,?,?,?,?,?,?,?,?,?,?)
            """,
            (
                agent_id,
                org_id,
                name.strip(),
                (description or "").strip() or None,
                avatar_path,
                kb_mode,
                welcome_message
                or f"Hi, I'm {name.strip()}. Ask me anything or feed me documents to learn from.",
                1 if is_main else 0,
                created_by,
                now,
                now,
            ),
        )
    return get_agent(agent_id)  # type: ignore[return-value]


def ensure_main_agent(org_id: str, created_by: str | None = None) -> dict[str, Any]:
    """Guarantee Alex exists as the main chatbot for this org."""
    agents = list_agents(org_id)
    main = next((a for a in agents if a.get("is_main")), None)
    if main:
        # Keep branding fixed as Alex
        if (main.get("name") or "").strip().lower() != "alex":
            update_agent(main["id"], name="Alex")
            main = get_agent(main["id"]) or main
        return main
    alex = next((a for a in agents if (a.get("name") or "").lower() == "alex"), None)
    if alex:
        with get_connection() as conn:
            conn.execute("UPDATE agents SET is_main = 0 WHERE org_id = ?", (org_id,))
            conn.execute(
                "UPDATE agents SET is_main = 1, name = 'Alex' WHERE id = ?",
                (alex["id"],),
            )
        return get_agent(alex["id"])  # type: ignore[return-value]
    return create_agent(
        org_id=org_id,
        name="Alex",
        description="Your main accounting and finance assistant",
        created_by=created_by,
        kb_mode="tenant",
        welcome_message="Hello! I'm Alex — your accounting assistant. Ask about spend, merchants, or upload documents in Knowledge.",
        is_main=True,
    )


def list_extra_agents(org_id: str) -> list[dict[str, Any]]:
    """Non-main chatbots (legacy multi-agent leftovers)."""
    return [a for a in list_agents(org_id) if not a.get("is_main")]


def get_agent(agent_id: str) -> dict[str, Any] | None:
    hit = _AGENT_CACHE.get(agent_id)
    now = time.monotonic()
    if hit and (now - hit[0]) < _CACHE_TTL_S:
        return hit[1]
    with get_connection() as conn:
        row = conn.execute("SELECT * FROM agents WHERE id = ?", (agent_id,)).fetchone()
        agent = dict(row) if row else None
    if agent:
        _AGENT_CACHE[agent_id] = (now, agent)
    return agent


def list_agents(org_id: str) -> list[dict[str, Any]]:
    with get_connection() as conn:
        rows = conn.execute(
            """
            SELECT a.*,
              (SELECT COUNT(DISTINCT d.filename) FROM kb_documents d WHERE d.agent_id = a.id AND d.status = 'ready') AS ready_docs,
              (SELECT COALESCE(SUM(chunk_count),0) FROM kb_documents d WHERE d.agent_id = a.id AND d.status = 'ready') AS chunk_count
            FROM agents a
            WHERE a.org_id = ?
            ORDER BY a.is_main DESC, a.created_at ASC
            """,
            (org_id,),
        ).fetchall()
        return [dict(r) for r in rows]


def document_status_counts(org_id: str) -> dict[str, dict[str, int]]:
    """Map agent_id -> {ready, failed, processing, pending, total, chunks}."""
    with get_connection() as conn:
        rows = conn.execute(
            """
            SELECT
              agent_id,
              status,
              COUNT(*) AS n,
              COALESCE(SUM(chunk_count), 0) AS chunks
            FROM kb_documents
            WHERE org_id = ?
            GROUP BY agent_id, status
            """,
            (org_id,),
        ).fetchall()
    out: dict[str, dict[str, int]] = {}
    for row in rows:
        d = dict(row)
        aid = str(d.get("agent_id") or "")
        if not aid:
            continue
        bucket = out.setdefault(
            aid,
            {"ready": 0, "failed": 0, "processing": 0, "pending": 0, "total": 0, "chunks": 0},
        )
        status = str(d.get("status") or "pending")
        n = int(d.get("n") or 0)
        chunks = int(d.get("chunks") or 0)
        if status not in bucket:
            bucket[status] = 0
        bucket[status] = int(bucket.get(status) or 0) + n
        bucket["total"] += n
        if status == "ready":
            bucket["chunks"] += chunks
    return out


def document_daily_counts(org_id: str, *, days: int = 7) -> list[dict[str, int | str]]:
    """Daily document uploads for the last N days."""
    from datetime import date, timedelta

    days = max(1, min(int(days), 30))
    start = date.today() - timedelta(days=days - 1)
    cutoff = start.isoformat()
    with get_connection() as conn:
        rows = conn.execute(
            """
            SELECT SUBSTR(created_at, 1, 10) AS day, COUNT(*) AS n
            FROM kb_documents
            WHERE org_id = ? AND SUBSTR(created_at, 1, 10) >= ?
            GROUP BY SUBSTR(created_at, 1, 10)
            ORDER BY day ASC
            """,
            (org_id, cutoff),
        ).fetchall()
    by_day = {str(dict(r)["day"]): int(dict(r).get("n") or 0) for r in rows if dict(r).get("day")}
    return [
        {"date": (start + timedelta(days=i)).isoformat(), "count": by_day.get((start + timedelta(days=i)).isoformat(), 0)}
        for i in range(days)
    ]


def build_org_dashboard(org_id: str) -> dict[str, Any]:
    """Aggregate workspace stats for the dashboard UI."""
    from app import database as core_db

    agents = list_agents(org_id)
    doc_counts = document_status_counts(org_id)
    fb_by_agent = core_db.feedback_counts_by_agent(org_id)
    org_fb = core_db.feedback_rating_counts(org_id=org_id)

    agent_rows: list[dict[str, Any]] = []
    total_ready = 0
    total_failed = 0
    total_processing = 0
    total_docs = 0
    total_chunks = 0

    for a in agents:
        aid = str(a["id"])
        docs = doc_counts.get(
            aid,
            {"ready": 0, "failed": 0, "processing": 0, "pending": 0, "total": 0, "chunks": 0},
        )
        processing = int(docs.get("processing") or 0) + int(docs.get("pending") or 0)
        ready = int(docs.get("ready") or 0)
        failed = int(docs.get("failed") or 0)
        chunks = int(docs.get("chunks") or a.get("chunk_count") or 0)
        fb = fb_by_agent.get(aid, {"up": 0, "down": 0, "total": 0, "with_correction": 0})
        rated = int(fb["up"]) + int(fb["down"])
        satisfaction = round((100.0 * fb["up"] / rated), 1) if rated else None

        total_ready += ready
        total_failed += failed
        total_processing += processing
        total_docs += int(docs.get("total") or 0)
        total_chunks += chunks

        agent_rows.append(
            {
                "id": aid,
                "name": a.get("name") or "Assistant",
                "is_main": bool(a.get("is_main")),
                "kb_mode": a.get("kb_mode") or "tenant",
                "created_at": a.get("created_at"),
                "updated_at": a.get("updated_at"),
                "avatar_url": f"/saas/agents/{aid}/avatar" if a.get("avatar_path") else None,
                "docs": {
                    "ready": ready,
                    "failed": failed,
                    "processing": processing,
                    "total": int(docs.get("total") or 0),
                    "chunks": chunks,
                },
                "feedback": {
                    "up": int(fb["up"]),
                    "down": int(fb["down"]),
                    "total": int(fb["total"]),
                    "with_correction": int(fb["with_correction"]),
                    "satisfaction_pct": satisfaction,
                },
            }
        )

    org_rated = int(org_fb["up"]) + int(org_fb["down"])
    org_satisfaction = round((100.0 * org_fb["up"] / org_rated), 1) if org_rated else None

    feedback_timeline = core_db.feedback_daily_counts(org_id=org_id, days=7)
    docs_timeline = document_daily_counts(org_id, days=7)

    return {
        "updated_at": _now(),
        "summary": {
            "agents": len(agents),
            "custom_agents": sum(1 for a in agents if not a.get("is_main")),
            "docs_total": total_docs,
            "docs_ready": total_ready,
            "docs_failed": total_failed,
            "docs_processing": total_processing,
            "chunks": total_chunks,
            "feedback_up": int(org_fb["up"]),
            "feedback_down": int(org_fb["down"]),
            "feedback_total": int(org_fb["total"]),
            "feedback_corrections": int(org_fb["with_correction"]),
            "satisfaction_pct": org_satisfaction,
        },
        "charts": {
            "knowledge_status": {
                "ready": total_ready,
                "processing": total_processing,
                "failed": total_failed,
            },
            "feedback_split": {
                "up": int(org_fb["up"]),
                "down": int(org_fb["down"]),
            },
            "feedback_timeline": feedback_timeline,
            "docs_timeline": docs_timeline,
            "agents_by_chunks": [
                {"name": r["name"], "chunks": r["docs"]["chunks"], "ready": r["docs"]["ready"]}
                for r in agent_rows
            ],
            "agents_by_feedback": [
                {
                    "name": r["name"],
                    "up": r["feedback"]["up"],
                    "down": r["feedback"]["down"],
                }
                for r in agent_rows
            ],
        },
        "agents": agent_rows,
    }


def update_agent(
    agent_id: str,
    *,
    name: str | None = None,
    description: str | None = None,
    kb_mode: str | None = None,
    welcome_message: str | None = None,
    avatar_path: str | None = None,
) -> dict[str, Any] | None:
    agent = get_agent(agent_id)
    if not agent:
        return None
    fields = ["updated_at = ?"]
    params: list[Any] = [_now()]
    if name is not None:
        fields.append("name = ?")
        params.append(name.strip())
    if description is not None:
        fields.append("description = ?")
        params.append(description.strip())
    if kb_mode is not None:
        if kb_mode not in {"platform", "tenant", "combined"}:
            raise ValueError("invalid kb_mode")
        fields.append("kb_mode = ?")
        params.append(kb_mode)
    if welcome_message is not None:
        fields.append("welcome_message = ?")
        params.append(welcome_message.strip())
    if avatar_path is not None:
        fields.append("avatar_path = ?")
        params.append(avatar_path)
    params.append(agent_id)
    with get_connection() as conn:
        conn.execute(f"UPDATE agents SET {', '.join(fields)} WHERE id = ?", params)
    invalidate_agent_cache(agent_id)
    return get_agent(agent_id)


def delete_agent(agent_id: str) -> None:
    with get_connection() as conn:
        conn.execute("DELETE FROM kb_documents WHERE agent_id = ?", (agent_id,))
        conn.execute("DELETE FROM agents WHERE id = ?", (agent_id,))
    invalidate_agent_cache(agent_id)
    _DOCS_CACHE.clear()


def create_api_key(
    *,
    org_id: str,
    name: str,
    key_prefix: str,
    key_hash: str,
    created_by: str | None,
) -> dict[str, Any]:
    key_id = str(uuid.uuid4())
    now = _now()
    with get_connection() as conn:
        conn.execute(
            """
            INSERT INTO api_keys(id, org_id, name, key_prefix, key_hash, created_by, created_at, revoked)
            VALUES (?,?,?,?,?,?,?,0)
            """,
            (key_id, org_id, name, key_prefix, key_hash, created_by, now),
        )
        row = conn.execute("SELECT * FROM api_keys WHERE id = ?", (key_id,)).fetchone()
        return dict(row)


def list_api_keys(org_id: str) -> list[dict[str, Any]]:
    with get_connection() as conn:
        rows = conn.execute(
            """
            SELECT id, org_id, name, key_prefix, created_at, last_used, revoked
            FROM api_keys WHERE org_id = ? ORDER BY created_at DESC
            """,
            (org_id,),
        ).fetchall()
        return [dict(r) for r in rows]


def get_api_key_by_prefix(prefix: str) -> dict[str, Any] | None:
    with get_connection() as conn:
        row = conn.execute(
            "SELECT * FROM api_keys WHERE key_prefix = ? AND revoked = 0",
            (prefix,),
        ).fetchone()
        return dict(row) if row else None


def revoke_api_key(key_id: str, org_id: str) -> bool:
    with get_connection() as conn:
        cur = conn.execute(
            "UPDATE api_keys SET revoked = 1 WHERE id = ? AND org_id = ?",
            (key_id, org_id),
        )
        return cur.rowcount > 0


def touch_api_key(key_id: str) -> None:
    with get_connection() as conn:
        conn.execute("UPDATE api_keys SET last_used = ? WHERE id = ?", (_now(), key_id))