"""FlowDeck — Workspace, Comments, Favorites, History, Templates (v2.0.0)."""
from __future__ import annotations

import csv
import html
import io
import json
import logging
import sqlite3

from fastapi import APIRouter, Body, Depends, HTTPException, Request
from fastapi.responses import HTMLResponse, StreamingResponse

from app.auth.session import SessionManager
from app.db import get_conn
from app.services.automations import fire_event, run_event_sync

logger = logging.getLogger(__name__)
ROLES = ["admin", "editor", "commenter", "viewer"]


def _require_session(request: Request) -> dict:
    """A3/A4 — session obligatoire sur TOUT le router `/workspace`.

    Avant, ces routes s'exécutaient sans session : `GET /workspace/collections/
    {id}/export/csv` renvoyait le contenu réel d'une collection à un visiteur
    anonyme, et les POST créaient des données au nom de l'utilisateur 1.
    La seule route publique (`/workspace/public/...`) vit désormais dans
    ``public_router``, sans dépendance.
    """
    user = SessionManager.decode_session(request.cookies.get("flowdeck_session", ""))
    if not user or not user.get("id"):
        raise HTTPException(401, "Authentication required")
    return user


router = APIRouter(tags=["workspace"], prefix="/workspace", dependencies=[Depends(_require_session)])
# Route de partage public : AUCUNE dépendance d'authentification.
public_router = APIRouter(tags=["workspace-public"], prefix="/workspace")


def _current_user(request: Request) -> dict:
    """Session obligatoire — plus de repli « admin implicite » (A3/A4)."""
    return _require_session(request)


def _require_admin(request: Request) -> dict:
    """A15 : webhooks sortants = admin — le serveur POSTe le contenu des pages."""
    user = SessionManager.decode_session(request.cookies.get("flowdeck_session", ""))
    if not user or not user.get("id"):
        raise HTTPException(401, "Authentication required")
    if not user.get("is_admin"):
        raise HTTPException(403, "Admin only")
    return user


def _require_ws_admin(request: Request, ws_id: int) -> None:
    """A5 — CRUD des membres : session obligatoire + rôle admin de l'espace
    (ou admin global). Un anonymous ne peut plus s'ajouter lui-même ni se
    promouvoir admin."""
    user = SessionManager.decode_session(request.cookies.get("flowdeck_session", ""))
    if not user or not user.get("id"):
        raise HTTPException(401, "Authentication required")
    with get_conn() as conn:
        if conn.execute("SELECT 1 FROM users WHERE id=? AND is_admin=1", (user["id"],)).fetchone():
            return
        row = conn.execute(
            "SELECT role FROM workspace_members WHERE workspace_id=? AND user_id=?",
            (ws_id, user["id"]),
        ).fetchone()
    if not row or row["role"] != "admin":
        raise HTTPException(403, "Workspace admin role required")


# ── Workspaces ──

@router.post("")
def create_workspace(request: Request, body: dict = Body(default={})):
    name = body.get("name", "Default Workspace")
    user = _current_user(request)
    uid = user.get("id", 1)

    with get_conn() as conn:
        uid_ensured = conn.execute("SELECT id FROM users WHERE id=?", (uid,)).fetchone()
        if not uid_ensured:
            conn.execute("INSERT OR IGNORE INTO users (id, login, full_name, is_admin) VALUES (?,?,?,1)",
                         (uid, user.get("login", "admin"), user.get("full_name", "Admin")))
        cur = conn.execute("INSERT INTO workspaces (name, owner_id) VALUES (?,?)", (name, uid))
        ws_id = cur.lastrowid
        conn.execute("INSERT OR IGNORE INTO workspace_members (workspace_id, user_id, role) VALUES (?,?,?)",
                     (ws_id, uid, "admin"))
        conn.commit()
    return {"id": ws_id, "name": name, "status": "created"}


# ── Members ──

@router.get("/{ws_id}/members")
def list_members(request: Request, ws_id: int):
    if not SessionManager.decode_session(request.cookies.get("flowdeck_session", "")):
        raise HTTPException(401, "Authentication required")
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT wm.*, u.login, u.full_name, u.avatar_url FROM workspace_members wm JOIN users u ON wm.user_id=u.id WHERE wm.workspace_id=?",
            (ws_id,),
        ).fetchall()
    return {"members": [dict(r) for r in rows]}


@router.post("/{ws_id}/members")
def add_member(request: Request, ws_id: int, body: dict = Body(default={})):
    _require_ws_admin(request, ws_id)
    user_id = body.get("user_id")
    role = body.get("role", "editor")
    if role not in ROLES:
        raise HTTPException(400, f"Invalid role: {role}")
    with get_conn() as conn:
        conn.execute("INSERT OR IGNORE INTO users (id, login, full_name, is_admin) VALUES (?,?,?,0)",
                     (user_id, f"user_{user_id}", f"User {user_id}"))
        conn.execute("INSERT INTO workspace_members (workspace_id, user_id, role) VALUES (?,?,?)",
                     (ws_id, user_id, role))
        conn.commit()
    return {"status": "added"}


@router.put("/{ws_id}/members/{user_id}")
def update_member_role(request: Request, ws_id: int, user_id: int, body: dict = Body(default={})):
    _require_ws_admin(request, ws_id)
    role = body.get("role", "editor")
    if role not in ROLES:
        raise HTTPException(400, f"Invalid role: {role}")
    with get_conn() as conn:
        conn.execute("UPDATE workspace_members SET role=? WHERE workspace_id=? AND user_id=?",
                     (role, ws_id, user_id))
        conn.commit()
    return {"status": "updated"}


@router.delete("/{ws_id}/members/{user_id}")
def remove_member(request: Request, ws_id: int, user_id: int):
    _require_ws_admin(request, ws_id)
    with get_conn() as conn:
        conn.execute("DELETE FROM workspace_members WHERE workspace_id=? AND user_id=?", (ws_id, user_id))
        conn.commit()
    return {"status": "removed"}


# ── Comments ──

@router.get("/pages/{page_id}/comments")
def list_comments(request: Request, page_id: int):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT c.*, u.login, u.avatar_url FROM comments c JOIN users u ON c.user_id=u.id WHERE c.page_id=? ORDER BY c.created_at",
            (page_id,),
        ).fetchall()
    return {"comments": [dict(r) for r in rows]}


@router.post("/pages/{page_id}/comments")
def add_comment(request: Request, page_id: int, body: dict = Body(default={})):
    b = body.get("body", "").strip()
    if not b:
        raise HTTPException(400, "body required")
    user = _current_user(request)
    uid = user.get("id", 1)
    parent_id = body.get("parent_id")
    with get_conn() as conn:
        conn.execute("INSERT OR IGNORE INTO users (id, login, full_name, is_admin) VALUES (?,?,?,1)",
                     (uid, user.get("login", "admin"), user.get("full_name", "Admin")))
        cur = conn.execute("INSERT INTO comments (page_id, user_id, body, parent_id) VALUES (?,?,?,?)",
                           (page_id, uid, b, parent_id))
        conn.commit()
    try:
        run_event_sync(fire_event("comment.added", {"comment_id": cur.lastrowid, "page_id": page_id, "user_id": uid}))
    except Exception:
        logger.exception("add_comment")
    return {"id": cur.lastrowid, "status": "created"}


@router.put("/comments/{comment_id}")
def update_comment(request: Request, comment_id: int, body: dict = Body(default={})):
    b = body.get("body")
    resolved = body.get("resolved")
    with get_conn() as conn:
        row = conn.execute("SELECT page_id, resolved FROM comments WHERE id=?", (comment_id,)).fetchone()
        if b is not None:
            conn.execute("UPDATE comments SET body=?, updated_at=CURRENT_TIMESTAMP WHERE id=?", (b, comment_id))
        if resolved is not None:
            conn.execute("UPDATE comments SET resolved=? WHERE id=?", (int(resolved), comment_id))
        conn.commit()
    if resolved and row and not int(row["resolved"] or 0):
        try:
            run_event_sync(fire_event("comment.resolved", {"comment_id": comment_id, "page_id": row["page_id"]}))
        except Exception:
            logger.exception("update_comment")
    return {"status": "updated"}


# ── Page History ──

@router.get("/pages/{page_id}/history")
def page_history(request: Request, page_id: int):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM page_history WHERE page_id=? ORDER BY created_at DESC LIMIT 50",
            (page_id,),
        ).fetchall()
    return {"history": [dict(r) for r in rows]}


@router.post("/pages/{page_id}/history")
def record_history(request: Request, page_id: int, body: dict = Body(default={})):
    user = _current_user(request)
    uid = user.get("id", 1)
    with get_conn() as conn:
        conn.execute("INSERT OR IGNORE INTO users (id, login, is_admin) VALUES (?,?,1)", (uid, user.get("login", "admin")))
        conn.execute(
            "INSERT INTO page_history (page_id, user_id, change_type, snapshot_json) VALUES (?,?,?,?)",
            (page_id, uid, body.get("change_type", "updated"), json.dumps(body.get("snapshot", {}))),
        )
        conn.commit()
    return {"status": "recorded"}


# ── Favorites ──

@router.get("/favorites")
def list_favorites(request: Request):
    """List the caller's favorites.

    v7.46.0 — aligne sur le schéma v2.2.0 (``app/db.py``) : la table
    ``favorites`` n'a plus de colonne ``collection_id`` et ``page_id``
    référence ``pages(id)``. L'ancienne requête (jointure ``collection_pages``)
    levait ``sqlite3.OperationalError: no such column: f.collection_id`` → 500.
    """
    user = _require_session(request)
    uid = user["id"]
    with get_conn() as conn:
        rows = conn.execute(
            """SELECT f.*, p.title AS page_title
               FROM favorites f
               LEFT JOIN pages p ON f.page_id=p.id
               WHERE f.user_id=? ORDER BY f.position""",
            (uid,),
        ).fetchall()
    return {"favorites": [dict(r) for r in rows]}



@router.post("/favorites")
def add_favorite(request: Request, body: dict = Body(default={})):
    user = _require_session(request)
    uid = user["id"]
    page_id = body.get("page_id")
    if not page_id:
        raise HTTPException(400, "page_id required")
    with get_conn() as conn:
        conn.execute(
            "INSERT OR IGNORE INTO favorites (user_id, page_id) VALUES (?,?)",
            (uid, page_id),
        )
        conn.commit()
    try:
        run_event_sync(fire_event("favorite.added", {"page_id": page_id, "user_id": uid}))
    except Exception:
        logger.exception("add_favorite")
    return {"status": "favorited"}



@router.delete("/favorites/{fav_id}")
def remove_favorite(request: Request, fav_id: int):
    user = _require_session(request)
    with get_conn() as conn:
        cur = conn.execute(
            "DELETE FROM favorites WHERE id=? AND user_id=?", (fav_id, user["id"])
        )
        conn.commit()
        if cur.rowcount == 0:
            raise HTTPException(404, "Favorite not found")
    return {"status": "removed"}


# ── Templates ──

@router.get("/templates/database")
def list_db_templates(request: Request):
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM database_templates ORDER BY name").fetchall()
    return {"templates": [dict(r) for r in rows]}


@router.post("/templates/database")
def create_db_template(request: Request, body: dict = Body(default={})):
    cur = None
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO database_templates (name, description, icon, schema_json) VALUES (?,?,?,?)",
            (body.get("name", "Template"), body.get("description", ""),
             body.get("icon", "📋"), json.dumps(body.get("schema", []))),
        )
        conn.commit()
    return {"id": cur.lastrowid, "status": "created"}


@router.post("/templates/database/{tid}/apply")
def apply_db_template(request: Request, tid: int, body: dict = Body(default={})):
    from app.services.db_templates import create_from_template
    name = body.get("name", "New Database")
    with get_conn() as conn:
        tmpl = conn.execute("SELECT * FROM database_templates WHERE id=?", (tid,)).fetchone()
        if not tmpl:
            raise HTTPException(404, "Template not found")
        collection_id = create_from_template(conn, name, dict(tmpl))
        conn.commit()
    return {"collection_id": collection_id, "name": name, "status": "created"}


@router.get("/collections/{collection_id}/templates/page")
def list_page_templates(request: Request, collection_id: int):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM page_templates WHERE collection_id=? ORDER BY name", (collection_id,)
        ).fetchall()
    return {"templates": [dict(r) for r in rows]}


@router.post("/collections/{collection_id}/templates/page")
def create_page_template(request: Request, collection_id: int, body: dict = Body(default={})):
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO page_templates (collection_id, name, property_values_json) VALUES (?,?,?)",
            (collection_id, body.get("name", "Default"), json.dumps(body.get("properties", {}))),
        )
        conn.commit()
    return {"id": cur.lastrowid, "status": "created"}


@router.post("/collections/{collection_id}/templates/page/{tid}/apply")
def apply_page_template(request: Request, collection_id: int, tid: int, body: dict = Body(default={})):
    with get_conn() as conn:
        tmpl = conn.execute("SELECT * FROM page_templates WHERE id=? AND collection_id=?", (tid, collection_id)).fetchone()
        if not tmpl:
            raise HTTPException(404, "Template not found")
        max_pos = conn.execute(
            "SELECT COALESCE(MAX(position), -1) + 1 FROM collection_pages WHERE collection_id=?", (collection_id,)
        ).fetchone()[0]
        cur = conn.execute(
            "INSERT INTO collection_pages (collection_id, title, position, property_values_json) VALUES (?,?,?,?)",
            (collection_id, body.get("title", "New Page"), max_pos, tmpl["property_values_json"]),
        )
        conn.commit()
    run_event_sync(fire_event("page.created", {
        "page_id": cur.lastrowid,
        "collection_id": collection_id,
        "title": body.get("title", "New Page"),
        "properties": json.loads(tmpl["property_values_json"]) if tmpl["property_values_json"] else {},
    }))
    return {"id": cur.lastrowid, "status": "created"}


@router.put("/collections/{collection_id}/templates/page/{tid}")
def update_page_template(request: Request, collection_id: int, tid: int, body: dict = Body(default={})):
    """Update a page template — name, properties, content, recurrence."""
    with get_conn() as conn:
        tmpl = conn.execute(
            "SELECT * FROM page_templates WHERE id=? AND collection_id=?", (tid, collection_id)
        ).fetchone()
        if not tmpl:
            raise HTTPException(404, "Template not found")

        name = body.get("name", tmpl["name"])
        tmpl_dict = dict(tmpl)
        description = body.get("description", tmpl_dict.get("description", ""))
        property_values_json = json.dumps(body.get("properties", json.loads(tmpl["property_values_json"])))
        content_json = json.dumps(body.get("content", json.loads(tmpl_dict.get("content_json", "[]"))))
        is_recurring = int(body.get("is_recurring", tmpl_dict.get("is_recurring", 0)))
        recurrence_rule = body.get("recurrence_rule", tmpl_dict.get("recurrence_rule", ""))

        conn.execute(
            """UPDATE page_templates SET name=?, description=?, property_values_json=?,
               content_json=?, is_recurring=?, recurrence_rule=? WHERE id=?""",
            (name, description, property_values_json, content_json, is_recurring, recurrence_rule, tid),
        )
        conn.commit()
    return {"id": tid, "status": "updated"}


@router.delete("/collections/{collection_id}/templates/page/{tid}")
def delete_page_template(request: Request, collection_id: int, tid: int):
    """Delete a page template."""
    with get_conn() as conn:
        tmpl = conn.execute(
            "SELECT * FROM page_templates WHERE id=? AND collection_id=?", (tid, collection_id)
        ).fetchone()
        if not tmpl:
            raise HTTPException(404, "Template not found")
        conn.execute("DELETE FROM page_templates WHERE id=?", (tid,))
        conn.commit()
    return {"id": tid, "status": "deleted"}


# ── v4.2.0: Dashboards ──

@router.get("/collections/{collection_id}/dashboards")
def list_dashboards(request: Request, collection_id: int):
    """List all dashboards for a collection."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM collection_dashboards WHERE collection_id=? ORDER BY name", (collection_id,)
        ).fetchall()
    return {"dashboards": [dict(r) for r in rows]}


@router.post("/collections/{collection_id}/dashboards")
def create_dashboard(request: Request, collection_id: int, body: dict = Body(default={})):
    """Create a new dashboard for a collection."""
    name = body.get("name", "Dashboard").strip()
    layout = json.dumps(body.get("layout", {"columns": 1, "widgets": []}))

    with get_conn() as conn:
        coll = conn.execute("SELECT id FROM collections WHERE id=?", (collection_id,)).fetchone()
        if not coll:
            raise HTTPException(404, "Collection not found")
        cur = conn.execute(
            "INSERT INTO collection_dashboards (collection_id, name, layout_json) VALUES (?,?,?)",
            (collection_id, name, layout),
        )
        conn.commit()
    return {"id": cur.lastrowid, "name": name, "status": "created"}


@router.put("/collections/{collection_id}/dashboards/{did}")
def update_dashboard(request: Request, collection_id: int, did: int, body: dict = Body(default={})):
    """Update a dashboard — name or layout (widgets grid)."""
    with get_conn() as conn:
        dash = conn.execute(
            "SELECT * FROM collection_dashboards WHERE id=? AND collection_id=?", (did, collection_id)
        ).fetchone()
        if not dash:
            raise HTTPException(404, "Dashboard not found")

        name = body.get("name", dash["name"])
        layout = json.dumps(body.get("layout", json.loads(dash["layout_json"])))

        conn.execute(
            "UPDATE collection_dashboards SET name=?, layout_json=?, updated_at=CURRENT_TIMESTAMP WHERE id=?",
            (name, layout, did),
        )
        conn.commit()
    return {"id": did, "status": "updated"}


@router.delete("/collections/{collection_id}/dashboards/{did}")
def delete_dashboard(request: Request, collection_id: int, did: int):
    """Delete a dashboard."""
    with get_conn() as conn:
        dash = conn.execute(
            "SELECT * FROM collection_dashboards WHERE id=? AND collection_id=?", (did, collection_id)
        ).fetchone()
        if not dash:
            raise HTTPException(404, "Dashboard not found")
        conn.execute("DELETE FROM collection_dashboards WHERE id=?", (did,))
        conn.commit()
    return {"id": did, "status": "deleted"}


# ── v4.5.0: Sprints ──

@router.get("/collections/{collection_id}/sprints")
def list_sprints(request: Request, collection_id: int):
    """List all sprints for a collection."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM sprints WHERE collection_id=? ORDER BY start_date DESC", (collection_id,)
        ).fetchall()
        sprints = []
        for r in rows:
            s = dict(r)
            # Count pages in sprint
            count = conn.execute(
                "SELECT COUNT(*) as cnt FROM sprint_pages WHERE sprint_id=?", (r["id"],)
            ).fetchone()["cnt"]
            s["page_count"] = count
            sprints.append(s)
    return {"sprints": sprints}


@router.post("/collections/{collection_id}/sprints")
def create_sprint(request: Request, collection_id: int, body: dict = Body(default={})):
    """Create a new sprint."""
    name = body.get("name", "").strip()
    start_date = body.get("start_date", "")
    end_date = body.get("end_date", "")
    if not name or not start_date or not end_date:
        raise HTTPException(400, "name, start_date, end_date are required")

    goal = body.get("goal", "")
    status = body.get("status", "planning")
    auto_complete = int(body.get("auto_complete", 1))

    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO sprints (collection_id, name, start_date, end_date, goal, status, auto_complete) VALUES (?,?,?,?,?,?,?)",
            (collection_id, name, start_date, end_date, goal, status, auto_complete),
        )
        conn.commit()
    try:
        run_event_sync(fire_event("sprint.created", {"sprint_id": cur.lastrowid, "collection_id": collection_id, "name": name}))
    except Exception:
        logger.exception("create_sprint")
    return {"id": cur.lastrowid, "name": name, "status": "created"}


@router.put("/collections/{collection_id}/sprints/{sid}")
def update_sprint(request: Request, collection_id: int, sid: int, body: dict = Body(default={})):
    """Update a sprint."""
    with get_conn() as conn:
        sprint = conn.execute(
            "SELECT * FROM sprints WHERE id=? AND collection_id=?", (sid, collection_id)
        ).fetchone()
        if not sprint:
            raise HTTPException(404, "Sprint not found")

        name = body.get("name", sprint["name"])
        start_date = body.get("start_date", sprint["start_date"])
        end_date = body.get("end_date", sprint["end_date"])
        goal = body.get("goal", sprint["goal"])
        status = body.get("status", sprint["status"])
        auto_complete = int(body.get("auto_complete", sprint["auto_complete"]))

        conn.execute(
            "UPDATE sprints SET name=?, start_date=?, end_date=?, goal=?, status=?, auto_complete=? WHERE id=?",
            (name, start_date, end_date, goal, status, auto_complete, sid),
        )
        conn.commit()
    try:
        run_event_sync(fire_event("sprint.updated", {"sprint_id": sid, "collection_id": collection_id, "name": name, "status": status}))
    except Exception:
        logger.exception("update_sprint")
    return {"id": sid, "status": "updated"}


@router.delete("/collections/{collection_id}/sprints/{sid}")
def delete_sprint(request: Request, collection_id: int, sid: int):
    """Delete a sprint."""
    with get_conn() as conn:
        sprint = conn.execute(
            "SELECT * FROM sprints WHERE id=? AND collection_id=?", (sid, collection_id)
        ).fetchone()
        if not sprint:
            raise HTTPException(404, "Sprint not found")
        conn.execute("DELETE FROM sprints WHERE id=?", (sid,))
        conn.commit()
    return {"id": sid, "status": "deleted"}


@router.post("/collections/{collection_id}/sprints/{sid}/assign")
def assign_page_to_sprint(request: Request, collection_id: int, sid: int, body: dict = Body(default={})):
    """Assign a page to a sprint."""
    page_id = body.get("page_id")
    if not page_id:
        raise HTTPException(400, "page_id is required")

    velocity_points = body.get("velocity_points", 1)
    status_at_start = body.get("status_at_start", "")

    with get_conn() as conn:
        sprint = conn.execute("SELECT id FROM sprints WHERE id=?", (sid,)).fetchone()
        if not sprint:
            raise HTTPException(404, "Sprint not found")
        page = conn.execute("SELECT id FROM collection_pages WHERE id=?", (page_id,)).fetchone()
        if not page:
            raise HTTPException(404, "Page not found")

        try:
            conn.execute(
                "INSERT INTO sprint_pages (sprint_id, page_id, status_at_start, velocity_points) VALUES (?,?,?,?)",
                (sid, page_id, status_at_start, velocity_points),
            )
            conn.commit()
        except sqlite3.IntegrityError:
            raise HTTPException(409, "Page already assigned to this sprint") from None
    return {"sprint_id": sid, "page_id": page_id, "status": "assigned"}


@router.delete("/collections/{collection_id}/sprints/{sid}/assign/{page_id}")
def remove_page_from_sprint(request: Request, collection_id: int, sid: int, page_id: int):
    """Remove a page from a sprint."""
    with get_conn() as conn:
        existing = conn.execute(
            "SELECT * FROM sprint_pages WHERE sprint_id=? AND page_id=?", (sid, page_id)
        ).fetchone()
        if not existing:
            raise HTTPException(404, "Assignment not found")
        conn.execute("DELETE FROM sprint_pages WHERE sprint_id=? AND page_id=?", (sid, page_id))
        conn.commit()
    return {"sprint_id": sid, "page_id": page_id, "status": "removed"}


@router.get("/collections/{collection_id}/sprints/burndown/{sid}")
def sprint_burndown(request: Request, collection_id: int, sid: int):
    """Calculate burndown data for a sprint."""
    with get_conn() as conn:
        sprint = conn.execute(
            "SELECT * FROM sprints WHERE id=? AND collection_id=?", (sid, collection_id)
        ).fetchone()
        if not sprint:
            raise HTTPException(404, "Sprint not found")

        pages = conn.execute(
            """SELECT sp.velocity_points, cp.property_values_json
               FROM sprint_pages sp JOIN collection_pages cp ON sp.page_id=cp.id
               WHERE sp.sprint_id=?""", (sid,)
        ).fetchall()

    total_points = sum(p["velocity_points"] for p in pages)
    completed = 0
    for p in pages:
        props = json.loads(p["property_values_json"])
        for v in props.values():
            if isinstance(v, str) and v.lower() in ("done", "complete", "completed", "terminé"):
                completed += p["velocity_points"]
                break

    from datetime import date
    today = date.today()
    start = date.fromisoformat(sprint["start_date"]) if sprint["start_date"] else today
    end = date.fromisoformat(sprint["end_date"]) if sprint["end_date"] else today
    total_days = max((end - start).days, 1)
    elapsed = max((today - start).days, 0)
    ideal_burn = total_points - (total_points * elapsed / total_days)

    return {
        "sprint": sprint["name"],
        "total_points": total_points,
        "completed_points": completed,
        "remaining_points": total_points - completed,
        "ideal_remaining": round(ideal_burn, 1),
        "start_date": sprint["start_date"],
        "end_date": sprint["end_date"],
        "days_elapsed": elapsed,
        "days_total": total_days,
    }


# ── CSV Import/Export ──

@router.post("/collections/{collection_id}/import/csv")
def import_csv(request: Request, collection_id: int, body: dict = Body(default={})):
    csv_data = body.get("csv", "")
    if not csv_data:
        raise HTTPException(400, "csv field required")

    reader = csv.DictReader(io.StringIO(csv_data))
    rows_imported = 0
    with get_conn() as conn:
        max_pos = conn.execute(
            "SELECT COALESCE(MAX(position), -1) FROM collection_pages WHERE collection_id=?", (collection_id,)
        ).fetchone()[0]
        for row in reader:
            title = row.get("title", row.get("Title", row.get("Name", "Imported")))
            props = {k: v for k, v in row.items() if k.lower() != "title"}
            max_pos += 1
            conn.execute(
                "INSERT INTO collection_pages (collection_id, title, position, property_values_json) VALUES (?,?,?,?)",
                (collection_id, title, max_pos, json.dumps(props)),
            )
            rows_imported += 1
        conn.commit()
    return {"imported": rows_imported}


@router.get("/collections/{collection_id}/export/csv")
def export_csv(request: Request, collection_id: int):
    with get_conn() as conn:
        pages = conn.execute(
            "SELECT * FROM collection_pages WHERE collection_id=? ORDER BY position", (collection_id,)
        ).fetchall()

    # Collect all property keys
    all_keys = set()
    rows = []
    for p in pages:
        props = json.loads(p["property_values_json"])
        all_keys.update(props.keys())
        rows.append({"title": p["title"], **props})

    output = io.StringIO()
    fieldnames = ["title"] + sorted(all_keys)
    writer = csv.DictWriter(output, fieldnames=fieldnames)
    writer.writeheader()
    writer.writerows(rows)

    return StreamingResponse(
        iter([output.getvalue()]),
        media_type="text/csv",
        headers={"Content-Disposition": "attachment; filename=export.csv"},
    )


# ── Webhooks Outbound Management ──

@router.get("/webhooks")
def list_webhooks(request: Request):
    _require_admin(request)
    with get_conn() as conn:
        rows = conn.execute("SELECT * FROM webhook_subscriptions ORDER BY created_at DESC").fetchall()
    return {"webhooks": [dict(r) for r in rows]}


@router.post("/webhooks")
def create_webhook(request: Request, body: dict = Body(default={})):
    _require_admin(request)
    url = body.get("url", "").strip()
    event = body.get("event", "page.created")
    secret = body.get("secret", "")
    if not url:
        raise HTTPException(400, "url required")
    # A15 : SSRF — le scheduler POSTe le contenu des pages vers cette URL.
    from urllib.parse import urlparse

    from app.services.importers.url_fetch import _is_public_host
    parsed = urlparse(url)
    if parsed.scheme not in ("http", "https") or not parsed.hostname or not _is_public_host(parsed.hostname):
        raise HTTPException(400, f"url non autorisée: {parsed.hostname}")
    with get_conn() as conn:
        cur = conn.execute(
            "INSERT INTO webhook_subscriptions (url, event, secret) VALUES (?,?,?)",
            (url, event, secret),
        )
        conn.commit()
    return {"id": cur.lastrowid, "url": url, "event": event, "status": "registered"}


@router.delete("/webhooks/{wh_id}")
def delete_webhook(request: Request, wh_id: int):
    _require_admin(request)
    with get_conn() as conn:
        conn.execute("DELETE FROM webhook_subscriptions WHERE id=?", (wh_id,))
        conn.commit()
    return {"status": "deleted"}


# ── Public Sharing ──

@public_router.get("/public/{collection_id}")
def public_view(request: Request, collection_id: int):
    """Simple public read-only view — no auth required.

    A18 : les bases ``restricted``/``private`` (``permission_type``) restent
    masquées (404) et toute interpolation part dans ``html.escape`` (XSS stocké
    sur le titre de la base ou d'une ligne).
    """
    with get_conn() as conn:
        coll = conn.execute("SELECT * FROM collections WHERE id=?", (collection_id,)).fetchone()
        if not coll:
            raise HTTPException(404, "Collection not found")
        ptype = coll["permission_type"] if "permission_type" in coll.keys() else "inherit"
        if ptype in ("restricted", "private"):
            # 404 explicite : le handler global transformerait un HTTPException(404)
            # en redirection 302 → login pour un chemin HTML.
            return HTMLResponse(
                "<!DOCTYPE html><html><head><meta charset=\"utf-8\"><title>404</title></head>"
                "<body><h1>404 — Not found</h1></body></html>",
                status_code=404,
            )
        pages = conn.execute(
            "SELECT id, title, icon, property_values_json FROM collection_pages WHERE collection_id=? ORDER BY position",
            (collection_id,),
        ).fetchall()

    esc = html.escape
    name = esc(str(coll["name"] or ""))
    icon = esc(str(coll["icon"] or ""))
    items = "".join(
        f"<li>{esc(str(p['icon'] or ''))} <b>{esc(str(p['title'] or ''))}</b></li>"
        for p in pages
    )
    return HTMLResponse(f"""<!DOCTYPE html>
<html><head><meta charset="utf-8"><title>{name} — FlowDeck Public</title>
<style>body{{font-family:system-ui;background:#191919;color:#fff;padding:20px}}
h1{{font-size:24px}} li{{padding:8px;border-bottom:1px solid #333}}</style></head>
<body><h1>{icon} {name}</h1><ul>{items}</ul><p>{len(pages)} items</p></body></html>""")