"""FlowDeck — Templates unifiés façon Notion (registre + instanciation unique).

Phases 0-1 du cahier des charges ``docs/architecture-templates-notion-flowdeck.md`` :
le registre ``templates`` (migration 48) catalogue les trois silos legacy
(``database_templates``, ``page_templates``, ``page_global_templates``) sans
les détruire, et ``TemplateService.instantiate()`` est l'UNIQUE chemin de
création depuis un template (UI, API v2, agent, scheduler — P4).

Conventions anti-dérive de schéma : les INSERT sur ``pages`` /
``collection_pages`` / ``collections`` n'utilisent que les colonnes qui
existent réellement (``PRAGMA table_info``), pour rester compatibles avec
toutes les bases (anciennes comme futures).
"""
from __future__ import annotations

import json
import logging
import re
import sqlite3
import uuid
from datetime import UTC, datetime, timedelta
from typing import Any

logger = logging.getLogger(__name__)

KINDS = ("page", "row", "database", "blocks")
SCOPES = ("personal", "workspace", "teamspace", "system")
RUN_SOURCES = ("ui", "api", "agent", "automation", "recurrence")

_VAR_RE = re.compile(r"\{\{\s*([\w:+\-.]+)\s*\}\}")

# Présence des tables du registre ( False tant que les migrations 48/49 ne
# sont pas appliquées — le service se dégrade alors vers les silos legacy).
_registry_ready: bool | None = None


def _now_iso() -> str:
    return datetime.now(UTC).replace(tzinfo=None).isoformat()


def _new_id(prefix: str) -> str:
    return f"{prefix}_{uuid.uuid4().hex[:12]}"


def _cols(conn: sqlite3.Connection, table: str) -> set[str]:
    try:
        return {r[1] for r in conn.execute(f"PRAGMA table_info({table})").fetchall()}
    except Exception:
        return set()


def _table_exists(conn: sqlite3.Connection, table: str) -> bool:
    try:
        return bool(
            conn.execute(
                "SELECT 1 FROM sqlite_master WHERE type='table' AND name=?", (table,)
            ).fetchone()
        )
    except Exception:
        return False


def registry_ready(conn: sqlite3.Connection) -> bool:
    """True si les migrations 48/49 sont appliquées."""
    global _registry_ready
    if _registry_ready is None:
        _registry_ready = _table_exists(conn, "templates") and _table_exists(
            conn, "template_runs"
        )
    return _registry_ready


def reset_registry_cache() -> None:
    global _registry_ready
    _registry_ready = None


def _actor_id(actor: dict | None) -> int | None:
    if not actor:
        return None
    for key in ("id", "user_id", "owner_id"):
        try:
            if actor.get(key) is not None:
                return int(actor[key])
        except (TypeError, ValueError):
            continue
    return None


def _actor_name(conn: sqlite3.Connection, actor: dict | None) -> str:
    if actor and actor.get("login"):
        return str(actor["login"])
    if actor and actor.get("full_name"):
        return str(actor["full_name"])
    uid = _actor_id(actor)
    if uid:
        try:
            row = conn.execute(
                "SELECT full_name, login FROM users WHERE id=?", (uid,)
            ).fetchone()
            if row:
                return row["full_name"] or row["login"] or f"user-{uid}"
        except Exception:
            pass
    return "Moi"


def _actor_workspace_id(conn: sqlite3.Connection, actor: dict | None) -> int:
    """Workspace de travail de l'acteur (défaut : son premier workspace)."""
    if actor and actor.get("workspace_id"):
        try:
            return int(actor["workspace_id"])
        except (TypeError, ValueError):
            pass
    uid = _actor_id(actor)
    try:
        if uid:
            row = conn.execute(
                "SELECT id FROM workspaces WHERE owner_id=? ORDER BY id LIMIT 1",
                (uid,),
            ).fetchone()
            if row:
                return int(row["id"])
        row = conn.execute("SELECT id FROM workspaces ORDER BY id LIMIT 1").fetchone()
        if row:
            return int(row["id"])
    except Exception:
        pass
    return 1


def _valid_workspace(
    conn: sqlite3.Connection, actor: dict | None, *candidates: Any
) -> int | None:
    """Premier candidat existant, sinon workspace garanti (ou None).

    Un template peut référencer un workspace disparu (import, base
    restaurée) : utiliser son id tel quel casserait la FK de ``pages``.
    """
    for candidate in candidates:
        try:
            cid = int(candidate)  # type: ignore[arg-type]
        except (TypeError, ValueError):
            continue
        try:
            if conn.execute(
                "SELECT 1 FROM workspaces WHERE id=?", (cid,)
            ).fetchone():
                return cid
        except Exception:
            return cid
    ensured = _ensure_workspace(conn, actor)
    if ensured is not None:
        return ensured
    for candidate in candidates:
        try:
            return int(candidate)  # type: ignore[arg-type]
        except (TypeError, ValueError):
            continue
    return None


def _ensure_workspace(conn: sqlite3.Connection, actor: dict | None) -> int | None:
    """Retourne un workspace_id valide (FK) — crée « Mon espace » si besoin.

    Sans cela, toute création échouerait par ``FOREIGN KEY constraint``
    sur une base fraîche sans workspace (tests, nouvelle instance).
    Retourne ``None`` s'il n'existe encore aucun utilisateur (boot avant le
    seed admin) : les pages-supports sont alors créées sans workspace
    (colonne nullable), le registre garde un workspace_id numérique
    (sans FK) en attendant la première connexion.
    """
    ws_id = _actor_workspace_id(conn, actor)
    try:
        if conn.execute(
            "SELECT 1 FROM workspaces WHERE id=?", (ws_id,)
        ).fetchone():
            return ws_id
    except Exception:
        return ws_id
    uid = _actor_id(actor)
    if uid is None:
        try:
            first = conn.execute(
                "SELECT id FROM users ORDER BY id LIMIT 1"
            ).fetchone()
            uid = int(first["id"]) if first else None
        except Exception:
            uid = None
    if uid is None:
        return None
    try:
        cur = conn.execute(
            "INSERT INTO workspaces (name, owner_id, settings_json) VALUES (?,?,?)",
            ("Mon espace", uid, "{}"),
        )
        return int(cur.lastrowid)
    except Exception:
        logger.exception("création workspace templates")
        return ws_id


def _workspace_name(conn: sqlite3.Connection, workspace_id: int) -> str:
    try:
        row = conn.execute(
            "SELECT name FROM workspaces WHERE id=?", (workspace_id,)
        ).fetchone()
        if row and row["name"]:
            return str(row["name"])
    except Exception:
        pass
    return ""


# ── Variables dynamiques (§8.4) ─────────────────────────────────────────────

def _shift_date(base: datetime, spec: str) -> datetime:
    """``+7d`` / ``-1w`` / ``+1mo`` / ``+2y``."""
    m = re.fullmatch(r"([+-]?\d+)\s*(d|w|mo|y)", spec.strip())
    if not m:
        return base
    n, unit = int(m.group(1)), m.group(2)
    if unit == "d":
        return base + timedelta(days=n)
    if unit == "w":
        return base + timedelta(weeks=n)
    if unit == "mo":
        return base + timedelta(days=30 * n)
    return base + timedelta(days=365 * n)


def _system_variables(
    conn: sqlite3.Connection,
    actor: dict | None,
    *,
    title: str = "",
    collection_name: str = "",
    now: datetime | None = None,
) -> dict[str, Any]:
    moment = now or datetime.now()
    ws_id = _actor_workspace_id(conn, actor)
    me = _actor_name(conn, actor)
    return {
        "today": moment.strftime("%Y-%m-%d"),
        "now": moment.strftime("%Y-%m-%d %H:%M"),
        "me": me,
        "title": title,
        "workspace": _workspace_name(conn, ws_id),
        "collection": collection_name,
    }


def _resolve_string(
    text: str, values: dict[str, Any], *, strict: bool = True
) -> tuple[str, list[str]]:
    """Résout les ``{{tokens}}`` dans un texte. Retourne (texte, avertissements).

    En mode strict, un token inconnu lève ``ValueError`` (échec avant écriture).
    """
    warnings: list[str] = []

    def _sub(match: re.Match) -> str:
        token = match.group(1)
        if token.startswith("date:"):
            base = datetime.now()
            shifted = _shift_date(base, token[5:])
            return shifted.strftime("%Y-%m-%d")
        if token in values and values[token] not in (None, ""):
            value = values[token]
            if isinstance(value, list):
                return ", ".join(str(v) for v in value)
            return str(value)
        if strict:
            raise ValueError(f"variable de template non résolue : {{{{{token}}}}}")
        warnings.append(f"Variable non résolue ignorée : {token}")
        return match.group(0)

    try:
        return _VAR_RE.sub(_sub, text), warnings
    except ValueError:
        raise


def _resolve_value(
    value: Any, values: dict[str, Any], *, strict: bool = True
) -> tuple[Any, list[str]]:
    """Résout récursivement (une référence exacte ``{{nom}}`` garde son type)."""
    warnings: list[str] = []
    if isinstance(value, str):
        exact = re.fullmatch(r"\{\{\s*([\w:+\-.]+)\s*\}\}", value.strip())
        if exact and exact.group(1) in values:
            return values[exact.group(1)], warnings
        return _resolve_string(value, values, strict=strict)
    if isinstance(value, list):
        out = []
        for item in value:
            resolved, warns = _resolve_value(item, values, strict=strict)
            warnings.extend(warns)
            out.append(resolved)
        return out, warnings
    if isinstance(value, dict):
        out_d = {}
        for key, item in value.items():
            resolved, warns = _resolve_value(item, values, strict=strict)
            warnings.extend(warns)
            out_d[key] = resolved
        return out_d, warnings
    return value, warnings


def _declared_variables(template_row: sqlite3.Row) -> list[dict]:
    try:
        variables = json.loads(template_row["variables_json"] or "[]")
        return variables if isinstance(variables, list) else []
    except (json.JSONDecodeError, TypeError):
        return []


def _manifest(template_row: sqlite3.Row) -> dict:
    try:
        manifest = json.loads(template_row["manifest_json"] or "{}")
        return manifest if isinstance(manifest, dict) else {}
    except (json.JSONDecodeError, TypeError):
        return {}


# ── Blocs ──────────────────────────────────────────────────────────────────

def _fresh_block_ids(blocks: list) -> list:
    """Copie profonde des blocs avec de nouveaux identifiants (§8.3 pas 5).

    Les blocs synchronisés sont matérialisés en blocs ordinaires : éditer la
    copie ne doit jamais modifier la source.
    """
    try:
        clone = json.loads(json.dumps(blocks))
    except (TypeError, ValueError):
        return []
    for block in clone:
        if isinstance(block, dict):
            block["id"] = uuid.uuid4().hex[:8]
            block.pop("synced_block_id", None)
            if block.get("type") == "synced":
                block["type"] = "paragraph"
            children = block.get("children")
            if isinstance(children, list):
                block["children"] = _fresh_block_ids(children)
    return clone


def _read_holder_blocks(
    conn: sqlite3.Connection, template_row: sqlite3.Row
) -> tuple[list, str]:
    """Blocs de la page-support (ou du silo legacy pour les adaptateurs)."""
    holder_id = template_row["content_page_id"]
    if holder_id:
        try:
            row = conn.execute(
                "SELECT content, content_format FROM pages WHERE id=?", (holder_id,)
            ).fetchone()
            if row:
                if (row["content_format"] or "") == "blocks":
                    try:
                        blocks = json.loads(row["content"] or "[]")
                        return (blocks if isinstance(blocks, list) else [], "")
                    except (json.JSONDecodeError, TypeError):
                        return [], "Contenu du template illisible (JSON corrompu)"
                if row["content"]:
                    return [
                        {
                            "id": uuid.uuid4().hex[:8],
                            "type": "paragraph",
                            "content": row["content"],
                        }
                    ], ""
        except Exception as exc:
            logger.exception("lecture page-support template %s", template_row["id"])
            return [], f"Contenu du template illisible : {exc}"
    # Adaptateurs legacy : le registre catalogue, le contenu reste au silo.
    source_kind = template_row["source_kind"]
    source_id = template_row["source_id"]
    try:
        if source_kind == "legacy_global" and source_id and _table_exists(
            conn, "page_global_templates"
        ):
            row = conn.execute(
                "SELECT blocks_json FROM page_global_templates WHERE id=?",
                (int(source_id),),
            ).fetchone()
            if row:
                blocks = json.loads(row["blocks_json"] or "[]")
                return (blocks if isinstance(blocks, list) else [], "")
        if source_kind == "legacy_page" and source_id and _table_exists(
            conn, "page_templates"
        ):
            row = conn.execute(
                "SELECT content_json FROM page_templates WHERE id=?",
                (int(source_id),),
            ).fetchone()
            if row and row["content_json"]:
                blocks = json.loads(row["content_json"])
                return (blocks if isinstance(blocks, list) else [], "")
    except (ValueError, json.JSONDecodeError, TypeError):
        pass
    except Exception:
        logger.exception("lecture adaptateur legacy %s/%s", source_kind, source_id)
    return [], ""


# ── Permissions (§14, best-effort) ──────────────────────────────────────────

def _is_teamspace_member(conn: sqlite3.Connection, teamspace_id: int, user_id: int) -> bool:
    try:
        if not _table_exists(conn, "teamspace_members"):
            return True
        row = conn.execute(
            "SELECT private FROM teamspaces WHERE id=?", (teamspace_id,)
        ).fetchone() if _table_exists(conn, "teamspaces") else None
        if not row or not row["private"]:
            return True
        return bool(
            conn.execute(
                "SELECT 1 FROM teamspace_members WHERE teamspace_id=? AND user_id=?",
                (teamspace_id, user_id),
            ).fetchone()
        )
    except Exception:
        return True


def template_visible(
    conn: sqlite3.Connection, template_row: sqlite3.Row, actor: dict | None
) -> bool:
    """Un template soft-deleted/archivé n'est jamais visible en sélecteur."""
    if template_row["deleted_at"] or template_row["archived_at"]:
        return False
    scope = template_row["scope"]
    if scope == "system":
        return True
    uid = _actor_id(actor)
    if scope == "personal":
        return bool(uid) and int(template_row["owner_id"] or 0) == uid
    if scope == "teamspace" and template_row["teamspace_id"]:
        if not uid:
            return False
        try:
            return _is_teamspace_member(conn, int(template_row["teamspace_id"]), uid)
        except (TypeError, ValueError):
            return False
    # workspace (+ repli historique) : visible par tout utilisateur connecté.
    return True


def _row_to_dto(
    conn: sqlite3.Connection, row: sqlite3.Row, *, usage: int = 0
) -> dict:
    recurrence = None
    try:
        if _table_exists(conn, "template_recurrences"):
            rec = conn.execute(
                "SELECT rrule, timezone, enabled, next_run_at FROM template_recurrences WHERE template_id=?",
                (row["id"],),
            ).fetchone()
            if rec:
                recurrence = dict(rec)
    except Exception:
        recurrence = None
    return {
        "id": row["id"],
        "kind": row["kind"],
        "name": row["name"],
        "description": row["description"] or "",
        "icon": row["icon"] or "📄",
        "category": row["category"] or "",
        "scope": row["scope"],
        "teamspace_id": row["teamspace_id"],
        "target_collection_id": row["target_collection_id"],
        "is_default": bool(row["is_default"]),
        "is_system": bool(row["is_system"]),
        "position": row["position"] or 0,
        "variables": _declared_variables(row),
        "manifest": _manifest(row),
        "recurrence": recurrence,
        "usage_count": usage,
        "updated_at": row["updated_at"] or "",
    }


class TemplateService:
    """Registre unifié + instanciation unique (§8)."""

    # ── Lecture ──────────────────────────────────────────────────────────

    @staticmethod
    def list_templates(
        conn: sqlite3.Connection,
        actor: dict | None,
        *,
        kind: str | None = None,
        scope: str | None = None,
        target_collection_id: int | None = None,
        query: str | None = None,
        include_archived: bool = False,
        workspace_id: int | None = None,
    ) -> list[dict]:
        if not registry_ready(conn):
            return TemplateService._list_legacy(conn, actor, kind=kind, query=query)
        ws_id = workspace_id or _actor_workspace_id(conn, actor)
        sql = (
            "SELECT t.*, COALESCE((SELECT COUNT(*) FROM template_runs r "
            "WHERE r.template_id=t.id AND r.status='ok'), 0) AS usage_count "
            "FROM templates t WHERE t.deleted_at IS NULL"
        )
        params: list[Any] = []
        if not include_archived:
            sql += " AND t.archived_at IS NULL"
        # Portée : personnel de l'acteur + workspace/système (+ teamspace).
        # Les templates d'autres workspaces restent invisibles (P6).
        sql += " AND (t.scope IN ('workspace','system') OR t.workspace_id=? OR t.owner_id=?)"
        uid = _actor_id(actor)
        params += [ws_id, uid or -1]
        if kind and kind in KINDS:
            sql += " AND t.kind=?"
            params.append(kind)
        if scope and scope in SCOPES:
            sql += " AND t.scope=?"
            params.append(scope)
        if target_collection_id is not None:
            sql += " AND t.target_collection_id=?"
            params.append(target_collection_id)
        if query:
            sql += " AND (t.name LIKE ? OR t.description LIKE ? OR t.category LIKE ?)"
            like = f"%{query}%"
            params += [like, like, like]
        sql += " ORDER BY t.is_system DESC, t.position, t.updated_at DESC"
        out = []
        for row in conn.execute(sql, params).fetchall():
            if not template_visible(conn, row, actor):
                continue
            out.append(_row_to_dto(conn, row, usage=row["usage_count"]))
        return out

    @staticmethod
    def _list_legacy(
        conn: sqlite3.Connection,
        actor: dict | None,
        *,
        kind: str | None = None,
        query: str | None = None,
    ) -> list[dict]:
        """Avant la migration 48 : expose les silos tels quels (dégradation)."""
        out: list[dict] = []
        uid = _actor_id(actor)
        if kind in (None, "database") and _table_exists(conn, "database_templates"):
            for row in conn.execute(
                "SELECT * FROM database_templates ORDER BY name"
            ).fetchall():
                if query and query.lower() not in (row["name"] or "").lower():
                    continue
                out.append(
                    {
                        "id": f"legacy_database:{row['id']}",
                        "kind": "database",
                        "name": row["name"],
                        "description": row["description"] or "",
                        "icon": row["icon"] if "icon" in row.keys() else "📋",
                        "category": "",
                        "scope": "system",
                        "teamspace_id": None,
                        "target_collection_id": None,
                        "is_default": False,
                        "is_system": True,
                        "position": 0,
                        "variables": [],
                        "manifest": {},
                        "recurrence": None,
                        "usage_count": 0,
                        "updated_at": "",
                    }
                )
        if kind in (None, "blocks") and _table_exists(conn, "page_global_templates"):
            for row in conn.execute(
                "SELECT * FROM page_global_templates WHERE created_by IS NULL OR created_by=? ORDER BY name",
                (uid,),
            ).fetchall():
                if query and query.lower() not in (row["name"] or "").lower():
                    continue
                out.append(
                    {
                        "id": f"legacy_global:{row['id']}",
                        "kind": "blocks",
                        "name": row["name"],
                        "description": row["description"] or "",
                        "icon": row["icon"] or "📄",
                        "category": "",
                        "scope": "personal",
                        "teamspace_id": None,
                        "target_collection_id": None,
                        "is_default": False,
                        "is_system": False,
                        "position": 0,
                        "variables": [],
                        "manifest": {},
                        "recurrence": None,
                        "usage_count": 0,
                        "updated_at": "",
                    }
                )
        return out

    @staticmethod
    def get_template(
        conn: sqlite3.Connection, actor: dict | None, template_id: str
    ) -> sqlite3.Row:
        if not registry_ready(conn) or ":" in str(template_id):
            raise LookupError("Template introuvable")
        row = conn.execute(
            "SELECT * FROM templates WHERE id=? AND deleted_at IS NULL", (template_id,)
        ).fetchone()
        if not row or not template_visible(conn, row, actor):
            raise LookupError("Template introuvable")
        return row

    # ── Écriture ─────────────────────────────────────────────────────────

    @staticmethod
    def create_template(
        conn: sqlite3.Connection, actor: dict | None, spec: dict
    ) -> dict:
        kind = spec.get("kind") or "page"
        if kind not in KINDS:
            raise ValueError(f"genre inconnu : {kind!r}")
        uid = _actor_id(actor)
        _ws = _valid_workspace(conn, actor, spec.get("workspace_id"))
        ws_id = _ws if _ws is not None else 1
        scope = spec.get("scope") or "personal"
        if scope not in SCOPES:
            raise ValueError(f"portée inconnue : {scope!r}")
        if scope == "system" and not (actor and actor.get("is_admin")):
            raise PermissionError("Seul un admin peut créer un template système")
        if scope == "personal" and not uid:
            raise PermissionError("Connexion requise pour un template personnel")
        target_collection_id = spec.get("target_collection_id")
        if kind == "row" and not target_collection_id:
            raise ValueError("un template de ligne exige target_collection_id")
        now = _now_iso()
        tid = _new_id("tpl")
        # Page-support : le contenu vit dans une page ordinaire masquée (§9.3).
        holder_id = None
        if kind in ("page", "row", "blocks"):
            holder_id = TemplateService._create_holder(
                conn,
                actor,
                ws_id,
                title=spec.get("name") or "Sans titre",
                blocks=spec.get("blocks") or [],
                content_format=spec.get("content_format") or "blocks",
            )
        manifest = dict(spec.get("manifest") or {})
        manifest.setdefault("format", "flowdeck-template")
        manifest.setdefault("version", 1)
        if spec.get("title_template"):
            manifest["title_template"] = spec["title_template"]
        if kind == "row" and spec.get("property_defaults"):
            manifest["property_defaults"] = spec["property_defaults"]
        conn.execute(
            """INSERT INTO templates (id, workspace_id, teamspace_id, kind, name,
                description, icon, cover_url, category, scope, owner_id,
                target_collection_id, content_page_id, source_kind, source_id,
                manifest_json, variables_json, is_default, is_system, position,
                created_by, created_at, updated_at)
               VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
            (
                tid,
                ws_id,
                spec.get("teamspace_id"),
                kind,
                (spec.get("name") or "Sans titre").strip(),
                spec.get("description") or "",
                spec.get("icon") or "📄",
                spec.get("cover_url") or "",
                spec.get("category") or "",
                scope,
                uid if scope == "personal" else spec.get("owner_id"),
                target_collection_id,
                holder_id,
                "native",
                "",
                json.dumps(manifest),
                json.dumps(spec.get("variables") or []),
                0,
                1 if scope == "system" else 0,
                int(spec.get("position") or 0),
                uid,
                now,
                now,
            ),
        )
        TemplateService._emit("template.created", {"template_id": tid, "kind": kind})
        row = conn.execute("SELECT * FROM templates WHERE id=?", (tid,)).fetchone()
        return _row_to_dto(conn, row)

    @staticmethod
    def _create_holder(
        conn: sqlite3.Connection,
        actor: dict | None,
        workspace_id: int | None,
        *,
        title: str,
        blocks: list,
        content_format: str = "blocks",
    ) -> int | None:
        """Crée la page-support masquée (jamais listée : voir _holder_filters)."""
        cols = _cols(conn, "pages")
        ws_name = (_workspace_name(conn, workspace_id) if workspace_id else "") or "Private"
        try:
            uid = _actor_id(actor)
            data: dict[str, Any] = {}
            if "workspace" in cols:
                data["workspace"] = ws_name
            if "workspace_id" in cols and workspace_id is not None:
                data["workspace_id"] = workspace_id
            if "title" in cols:
                data["title"] = f"🧩 {title}"
            if "content" in cols:
                data["content"] = (
                    json.dumps(blocks or []) if content_format == "blocks" else ""
                )
            if "content_format" in cols:
                data["content_format"] = content_format
            if "parent_section" in cols:
                data["parent_section"] = "Private"
            if "search_excluded" in cols:
                data["search_excluded"] = 1
            if not data:
                return None
            keys = ", ".join(data.keys())
            placeholders = ", ".join("?" for _ in data)
            cur = conn.execute(
                f"INSERT INTO pages ({keys}) VALUES ({placeholders})",
                tuple(data.values()),
            )
            void = uid  # traçabilité future (réservé)
            return int(cur.lastrowid)
        except Exception:
            logger.exception("création page-support template")
            return None

    @staticmethod
    def update_template(
        conn: sqlite3.Connection, actor: dict | None, template_id: str, patch: dict
    ) -> dict:
        row = TemplateService.get_template(conn, actor, template_id)
        TemplateService._require_write(conn, actor, row)
        if row["is_system"]:
            raise PermissionError("Template système : dupliquez-le pour le modifier")
        updates: dict[str, Any] = {}
        for field in (
            "name",
            "description",
            "icon",
            "cover_url",
            "category",
            "scope",
            "teamspace_id",
            "position",
        ):
            if field in patch:
                updates[field] = patch[field]
        if "scope" in updates and updates["scope"] not in SCOPES:
            raise ValueError("portée inconnue")
        if "property_defaults" in patch and row["kind"] == "row":
            manifest = _manifest(row)
            manifest["property_defaults"] = patch["property_defaults"] or {}
            updates["manifest_json"] = json.dumps(manifest)
        if "title_template" in patch:
            manifest = _manifest(row)
            manifest["title_template"] = patch["title_template"] or ""
            updates["manifest_json"] = json.dumps(manifest)
        if "variables" in patch:
            updates["variables_json"] = json.dumps(patch["variables"] or [])
        if "manifest" in patch and isinstance(patch["manifest"], dict):
            merged = _manifest(row)
            merged.update(patch["manifest"])
            updates["manifest_json"] = json.dumps(merged)
        if updates:
            updates["updated_at"] = _now_iso()
            sets = ", ".join(f"{k}=?" for k in updates)
            conn.execute(
                f"UPDATE templates SET {sets} WHERE id=?",
                (*updates.values(), template_id),
            )
        TemplateService._emit("template.updated", {"template_id": template_id})
        return _row_to_dto(
            conn,
            conn.execute(
                "SELECT * FROM templates WHERE id=?", (template_id,)
            ).fetchone(),
        )

    @staticmethod
    def duplicate_template(
        conn: sqlite3.Connection, actor: dict | None, template_id: str, spec: dict | None = None
    ) -> dict:
        row = TemplateService.get_template(conn, actor, template_id)
        spec = spec or {}
        uid = _actor_id(actor)
        blocks, _warn = _read_holder_blocks(conn, row)
        manifest = _manifest(row)
        dto = TemplateService.create_template(
            conn,
            actor,
            {
                "kind": row["kind"],
                "name": spec.get("name") or f"{row['name']} (copie)",
                "description": row["description"],
                "icon": row["icon"],
                "category": row["category"],
                "scope": spec.get("scope") or ("personal" if uid else "workspace"),
                "teamspace_id": spec.get("teamspace_id") or row["teamspace_id"],
                "workspace_id": spec.get("workspace_id") or row["workspace_id"],
                "target_collection_id": row["target_collection_id"],
                "blocks": blocks,
                "manifest": manifest,
                "variables": _declared_variables(row),
                "title_template": manifest.get("title_template", ""),
            },
        )
        return dto

    @staticmethod
    def archive_template(conn, actor, template_id: str, *, archived: bool = True) -> dict:
        row = TemplateService.get_template(conn, actor, template_id)
        TemplateService._require_write(conn, actor, row)
        now = _now_iso() if archived else None
        conn.execute(
            "UPDATE templates SET archived_at=?, updated_at=? WHERE id=?",
            (now, _now_iso(), template_id),
        )
        return _row_to_dto(
            conn,
            conn.execute("SELECT * FROM templates WHERE id=?", (template_id,)).fetchone(),
        )

    @staticmethod
    def delete_template(conn, actor, template_id: str) -> dict:
        """Soft-delete (corbeille unifiée) ; la page-support suit."""
        row = TemplateService.get_template(conn, actor, template_id)
        TemplateService._require_write(conn, actor, row)
        now = _now_iso()
        conn.execute(
            "UPDATE templates SET deleted_at=?, updated_at=? WHERE id=?",
            (now, now, template_id),
        )
        if row["content_page_id"]:
            try:
                cols = _cols(conn, "pages")
                if "deleted_at" in cols:
                    conn.execute(
                        "UPDATE pages SET deleted_at=? WHERE id=?",
                        (now, row["content_page_id"]),
                    )
            except Exception:
                logger.exception("soft-delete page-support %s", row["content_page_id"])
        TemplateService._emit("template.deleted", {"template_id": template_id})
        return {"id": template_id, "status": "deleted"}

    @staticmethod
    def set_default(conn, actor, template_id: str) -> dict:
        """Défaut exclusif par base, en une transaction (§11.1)."""
        row = TemplateService.get_template(conn, actor, template_id)
        if row["kind"] != "row" or not row["target_collection_id"]:
            raise ValueError("seul un template de ligne a un défaut")
        TemplateService._require_write(conn, actor, row)
        conn.execute(
            "UPDATE templates SET is_default=0 WHERE target_collection_id=? AND is_default=1",
            (row["target_collection_id"],),
        )
        conn.execute(
            "UPDATE templates SET is_default=1, updated_at=? WHERE id=?",
            (_now_iso(), template_id),
        )
        return _row_to_dto(
            conn,
            conn.execute("SELECT * FROM templates WHERE id=?", (template_id,)).fetchone(),
        )

    @staticmethod
    def unset_default(conn, actor, template_id: str) -> dict:
        row = TemplateService.get_template(conn, actor, template_id)
        TemplateService._require_write(conn, actor, row)
        conn.execute(
            "UPDATE templates SET is_default=0, updated_at=? WHERE id=?",
            (_now_iso(), template_id),
        )
        return _row_to_dto(
            conn,
            conn.execute("SELECT * FROM templates WHERE id=?", (template_id,)).fetchone(),
        )

    @staticmethod
    def set_recurrence(
        conn, actor, template_id: str, spec: dict | None
    ) -> dict | None:
        row = TemplateService.get_template(conn, actor, template_id)
        if row["kind"] != "row":
            raise ValueError("seuls les templates de ligne sont récurrents")
        TemplateService._require_write(conn, actor, row)
        if not spec or not spec.get("rrule"):
            conn.execute(
                "DELETE FROM template_recurrences WHERE template_id=?", (template_id,)
            )
            return None
        now = _now_iso()
        next_run = _compute_next_run(
            spec["rrule"], spec.get("timezone") or "", start=now
        )
        conn.execute(
            """INSERT INTO template_recurrences
               (template_id, rrule, timezone, enabled, next_run_at, updated_at)
               VALUES (?,?,?,?,?,?)
               ON CONFLICT(template_id) DO UPDATE SET
                 rrule=excluded.rrule, timezone=excluded.timezone,
                 enabled=excluded.enabled, next_run_at=excluded.next_run_at,
                 updated_at=excluded.updated_at""",
            (
                template_id,
                spec["rrule"],
                spec.get("timezone") or "",
                1 if spec.get("enabled", True) else 0,
                next_run,
                now,
            ),
        )
        rec = conn.execute(
            "SELECT rrule, timezone, enabled, next_run_at FROM template_recurrences WHERE template_id=?",
            (template_id,),
        ).fetchone()
        return dict(rec) if rec else None

    @staticmethod
    def _require_write(conn, actor, row: sqlite3.Row) -> None:
        uid = _actor_id(actor)
        if actor and actor.get("is_admin"):
            return
        if row["scope"] == "system":
            raise PermissionError("Template système en lecture seule")
        if row["scope"] == "personal":
            if not uid or int(row["owner_id"] or 0) != uid:
                raise PermissionError("Template personnel d'un autre utilisateur")
            return
        # workspace/teamspace : il faut être connecté ; l'édition du défaut et
        # de la récurrence d'un template de ligne exige l'édition de la base.
        if not uid:
            raise PermissionError("Connexion requise")
        if row["kind"] == "row" and row["target_collection_id"]:
            try:
                if not conn.execute(
                    "SELECT id FROM collections WHERE id=?", (row["target_collection_id"],)
                ).fetchone():
                    raise PermissionError("Base cible introuvable")
            except PermissionError:
                raise
            except Exception:
                pass

    # ── Aperçu ───────────────────────────────────────────────────────────

    @staticmethod
    def preview(
        conn,
        actor: dict | None,
        template_id: str,
        variables: dict | None = None,
        *,
        title: str = "",
    ) -> dict:
        row = TemplateService.get_template(conn, actor, template_id)
        # L'aperçu ne bloque JAMAIS : les variables requises manquantes sont
        # montrées telles quelles ({{nom}}), seule l'instanciation exige.
        resolved, warnings = TemplateService._resolve_all(
            conn, actor, row, variables or {}, title=title, strict=False
        )
        blocks, warn = _read_holder_blocks(conn, row)
        if warn:
            warnings.append(warn)
        preview_blocks = []
        for block in _fresh_block_ids(blocks)[:12]:
            if isinstance(block, dict) and isinstance(block.get("content"), str):
                try:
                    text, _w = _resolve_string(block["content"], resolved, strict=False)
                    block = dict(block)
                    block["content"] = text
                except ValueError:
                    pass
            preview_blocks.append(block)
        return {
            "template_id": template_id,
            "title": resolved.get("__title__", row["name"]),
            "property_defaults": resolved.get("__properties__", {}),
            "blocks_preview": preview_blocks,
            "variables": _declared_variables(row),
            "warnings": warnings,
        }

    @staticmethod
    def _resolve_all(
        conn,
        actor: dict | None,
        row: sqlite3.Row,
        provided: dict,
        *,
        title: str = "",
        collection_name: str = "",
        strict: bool = True,
    ) -> tuple[dict, list[str]]:
        """Résout système puis déclarées.

        En mode strict (instanciation) : erreur AVANT toute écriture si une
        variable requise manque ou si un token survit (§8.3 pas 3, §8.4).
        En mode aperçu : jamais d'erreur — les tokens non résolus restent
        visibles tels quels dans l'aperçu.
        """
        warnings: list[str] = []
        manifest = _manifest(row)
        values = _system_variables(
            conn, actor, title=title or (provided.get("title") or ""), collection_name=collection_name
        )
        for var in _declared_variables(row):
            name = var.get("name")
            if not name:
                continue
            if name == "title":
                # « title » est une variable SYSTÈME réservée : jamais requise.
                # Une valeur fournie prime, sinon la résolution retombe sur le
                # titre de la page cible ou le nom du template (§8.4).
                if provided.get("title") not in (None, ""):
                    values["title"] = provided["title"]
                continue
            if name in provided and provided[name] not in (None, ""):
                values[name] = provided[name]
            elif var.get("default") not in (None, ""):
                values[name] = var["default"]
            elif var.get("required") and strict:
                raise ValueError(f"variable requise manquante : {name}")
            elif name not in values:
                values[name] = ""
        # Titre : title_template > titre fourni > nom du template.
        title_tpl = manifest.get("title_template") or "{{title}}"
        try:
            resolved_title, _w = _resolve_string(
                title_tpl if "{{" in title_tpl else title_tpl,
                {**values, "title": values.get("title") or title or row["name"]},
                strict=strict,
            )
        except ValueError as exc:
            raise ValueError(str(exc)) from None
        values["__title__"] = resolved_title or title or row["name"]
        # Propriétés par défaut (références typées conservées si exactes).
        raw_defaults = manifest.get("property_defaults") or {}
        # Adaptateur legacy : les valeurs vivent dans le silo d'origine.
        if not raw_defaults and row["source_kind"] == "legacy_page" and row["source_id"]:
            try:
                legacy = conn.execute(
                    "SELECT property_values_json FROM page_templates WHERE id=?",
                    (int(row["source_id"]),),
                ).fetchone()
                if legacy and legacy["property_values_json"]:
                    raw_defaults = json.loads(legacy["property_values_json"]) or {}
            except (ValueError, json.JSONDecodeError, TypeError):
                raw_defaults = {}
            except Exception:
                raw_defaults = {}
        resolved_props, warns = _resolve_value(raw_defaults, values, strict=strict)
        warnings.extend(warns)
        values["__properties__"] = (
            resolved_props if isinstance(resolved_props, dict) else {}
        )
        # Garde-fou §8.4 (instanciation uniquement) : aucun {{token}} ne
        # survit dans le titre/propriétés. En aperçu, les tokens restants
        # sont affichés tels quels pour guider la saisie.
        leftovers = _VAR_RE.findall(
            json.dumps(
                {"t": values["__title__"], "p": values["__properties__"]},
                default=str,
            )
        )
        if leftovers and strict:
            raise ValueError(
                "variables non résolues : " + ", ".join(sorted(set(leftovers)))
            )
        if leftovers:
            warnings.append(
                "À compléter : " + ", ".join(sorted(set(leftovers)))
            )
        return values, warnings

    # ── Instanciation unique (§8.3, P4) ──────────────────────────────────

    @staticmethod
    def instantiate(
        conn,
        actor: dict | None,
        template_id: str,
        variables: dict | None = None,
        context: dict | None = None,
        *,
        idempotency_key: str | None = None,
        run_source: str = "ui",
    ) -> dict:
        if run_source not in RUN_SOURCES:
            run_source = "ui"
        # Idempotence : rejouer la même clé ne crée jamais deux objets.
        if idempotency_key and registry_ready(conn):
            existing = conn.execute(
                "SELECT * FROM template_runs WHERE idempotency_key=?",
                (idempotency_key,),
            ).fetchone()
            if existing and existing["status"] == "ok":
                return {
                    "template_id": template_id,
                    "created_type": existing["created_type"],
                    "created_id": existing["created_id"],
                    "url": TemplateService._url_for(
                        existing["created_type"], existing["created_id"]
                    ),
                    "warnings": json.loads(existing["warnings_json"] or "[]"),
                    "run_id": existing["id"],
                    "replayed": True,
                }
        # Registre vide + id legacy → instanciation directe du silo.
        if not registry_ready(conn) or ":" in str(template_id):
            return TemplateService._instantiate_legacy(
                conn, actor, str(template_id), variables or {}, context or {},
                idempotency_key=idempotency_key, run_source=run_source,
            )
        row = TemplateService.get_template(conn, actor, template_id)
        context = context or {}
        target = (context.get("target") or {}).get("type") or "new_page"
        uid = _actor_id(actor)
        _ws = _valid_workspace(conn, actor, context.get("workspace_id"), row["workspace_id"])
        ws_id = _ws if _ws is not None else 1
        title_hint = (context.get("target") or {}).get("title") or (variables or {}).get("title", "")
        collection_name = ""
        target_collection = None
        if row["kind"] == "row":
            cid = context.get("collection_id") or row["target_collection_id"]
            target_collection = None
            if cid:
                target_collection = conn.execute(
                    "SELECT * FROM collections WHERE id=?", (cid,)
                ).fetchone()
            if target_collection is None:
                # Presets de ligne : la base cible est résolue par nom dans le
                # workspace (cf. manifest.target_collection_name), jamais par
                # un id externe qui ne voyagerait pas entre instances.
                hint_name = _manifest(row).get("target_collection_name")
                if hint_name:
                    target_collection = conn.execute(
                        "SELECT * FROM collections WHERE name=? "
                        "AND (workspace_id=? OR workspace_id IS NULL) "
                        "ORDER BY id LIMIT 1",
                        (hint_name, ws_id),
                    ).fetchone()
            if target_collection is None:
                hint_name = _manifest(row).get("target_collection_name") or ""
                raise ValueError(
                    "Ce template de ligne doit être appliqué dans une base"
                    + (f" (« {hint_name} »)" if hint_name else "")
                    + " — créez-la d'abord ou ouvrez-la."
                )
            collection_name = target_collection["name"] or ""
        resolved, warnings = TemplateService._resolve_all(
            conn, actor, row, variables or {}, title=title_hint, collection_name=collection_name
        )
        created_type, created_id = "", ""
        try:
            if row["kind"] == "page":
                created_id = str(
                    TemplateService._create_page(
                        conn, actor, ws_id, resolved, row,
                        parent_id=(context.get("target") or {}).get("page_id")
                        or context.get("parent_id"),
                        update_page_id=(context.get("target") or {}).get("page_id")
                        if target == "current_page" else None,
                        mode=(context.get("target") or {}).get("mode") or "fill",
                    )
                )
                created_type = "page"
            elif row["kind"] == "row":
                assert target_collection is not None
                created_id = str(
                    TemplateService._create_row(
                        conn, actor, target_collection, resolved, row,
                        view_hints=context.get("view_hints") or {},
                    )
                )
                created_type = "row"
            elif row["kind"] == "database":
                try:
                    _ppid = context.get("parent_page_id")
                    parent_pid = int(_ppid) if _ppid not in (None, "", 0, "0") else None
                except (TypeError, ValueError):
                    parent_pid = None
                created_id = str(
                    TemplateService._create_database(
                        conn, actor, ws_id, resolved, row,
                        parent_page_id=parent_pid,
                    )
                )
                created_type = "collection"
            else:  # blocks → insertion côté client ; le run journalise l'usage.
                created_type = "insertion"
                created_id = ""
            # Les créateurs ajoutent leurs avertissements (propriétés
            # disparues, mentions dégradées) dans resolved["__warnings__"].
            warnings = list(resolved.get("__warnings__") or warnings)
            run_id = TemplateService._log_run(
                conn, row["id"], run_source, uid, created_type, created_id,
                warnings, idempotency_key=idempotency_key,
                occurrence_key=context.get("occurrence_key"),
            )
            conn.commit()
        except Exception as exc:
            try:
                conn.rollback()
            except Exception:
                pass
            try:
                TemplateService._log_run(
                    conn, row["id"], run_source, uid, "", "",
                    warnings, error=str(exc),
                    idempotency_key=(idempotency_key + ":err") if idempotency_key else None,
                    occurrence_key=context.get("occurrence_key"),
                )
                conn.commit()
            except Exception:
                pass
            raise
        TemplateService._emit(
            "template.applied",
            {"template_id": row["id"], "created_type": created_type, "created_id": created_id},
        )
        result: dict[str, Any] = {
            "template_id": row["id"],
            "created_type": created_type,
            "created_id": created_id,
            "url": TemplateService._url_for(created_type, created_id),
            "warnings": warnings,
            "run_id": run_id,
        }
        if row["kind"] == "blocks":
            blocks, warn = _read_holder_blocks(conn, row)
            if warn:
                warnings.append(warn)
            fresh = _fresh_block_ids(blocks)
            resolved_blocks = []
            for block in fresh:
                if isinstance(block, dict) and isinstance(block.get("content"), str):
                    try:
                        text, _w = _resolve_string(block["content"], resolved, strict=False)
                        block = dict(block)
                        block["content"] = text
                    except ValueError:
                        pass
                resolved_blocks.append(block)
            result["blocks"] = resolved_blocks
            result["warnings"] = warnings
        # Page courante remplie en place : renvoyer le contenu à jour pour
        # que le front se rafraîchisse sans rechargement (T1-5).
        if row["kind"] == "page" and (context.get("target") or {}).get("page_id"):
            try:
                current = conn.execute(
                    "SELECT content, content_format, title FROM pages WHERE id=?",
                    (int((context.get("target") or {})["page_id"]),),
                ).fetchone()
                if current:
                    if (current["content_format"] or "") == "blocks":
                        result["blocks"] = json.loads(current["content"] or "[]")
                    result["title"] = current["title"] or resolved.get("__title__")
            except (ValueError, TypeError, json.JSONDecodeError):
                pass
            except Exception:
                logger.exception("relecture page après instanciation")
        return result

    # ── Briques de création ──────────────────────────────────────────────

    @staticmethod
    def _create_page(
        conn, actor, workspace_id: int, resolved: dict, row: sqlite3.Row,
        *, parent_id: int | None = None, update_page_id: int | None = None,
        mode: str = "fill",
    ) -> int:
        blocks, warn = _read_holder_blocks(conn, row)
        warnings_holder = resolved.setdefault("__warnings__", [])
        if warn:
            warnings_holder.append(warn)
        fresh = _fresh_block_ids(blocks)
        out_blocks = []
        for block in fresh:
            if isinstance(block, dict) and isinstance(block.get("content"), str):
                try:
                    text, _w = _resolve_string(block["content"], resolved, strict=False)
                    block = dict(block)
                    block["content"] = text
                except ValueError:
                    pass
            out_blocks.append(block)
        title = resolved.get("__title__") or row["name"]
        cols = _cols(conn, "pages")
        ws_name = (_workspace_name(conn, workspace_id) if workspace_id else "") or "Private"
        # Application à la page courante : ajout (défaut non destructif) ou
        # remplacement explicite (confirmation côté UI, T3-5).
        if update_page_id:
            current = conn.execute(
                "SELECT * FROM pages WHERE id=?", (update_page_id,)
            ).fetchone()
            if not current:
                raise LookupError("Page cible introuvable")
            if mode == "replace":
                if "title" in cols:
                    conn.execute(
                        "UPDATE pages SET title=? WHERE id=?", (title, update_page_id)
                    )
                if "content" in cols:
                    conn.execute(
                        "UPDATE pages SET content=? WHERE id=?",
                        (json.dumps(out_blocks), update_page_id),
                    )
            else:
                # Remplir la page courante : on ajuste aussi le titre selon le
                # template (résolu avec {{title}} = sujet fourni, sinon titre de
                # la page, sinon nom du template) — c'est le comportement
                # attendu « le titre suit le template choisi ».
                if "title" in cols and title:
                    conn.execute(
                        "UPDATE pages SET title=? WHERE id=?", (title, update_page_id)
                    )
                if "content" in cols:
                    try:
                        existing = (
                            json.loads(current["content"] or "[]")
                            if (current["content_format"] or "") == "blocks"
                            else []
                        )
                    except (json.JSONDecodeError, TypeError):
                        existing = []
                    merged = (existing if isinstance(existing, list) else []) + out_blocks
                    conn.execute(
                        "UPDATE pages SET content=? WHERE id=?",
                        (json.dumps(merged), update_page_id),
                    )
            return int(update_page_id)
        data: dict[str, Any] = {}
        if "workspace" in cols:
            data["workspace"] = ws_name
        if "workspace_id" in cols and workspace_id is not None:
            data["workspace_id"] = workspace_id
        if "title" in cols:
            data["title"] = title
        if "content" in cols:
            data["content"] = json.dumps(out_blocks)
        if "content_format" in cols:
            data["content_format"] = "blocks"
        if "parent_section" in cols:
            data["parent_section"] = "Private"
        if "parent_id" in cols and parent_id:
            data["parent_id"] = parent_id
        if "page_icon" in cols and row["icon"]:
            data["page_icon"] = row["icon"]
        if "cover_url" in cols and row["cover_url"]:
            data["cover_url"] = row["cover_url"]
        manifest = _manifest(row)
        if "full_width" in cols and manifest.get("full_width"):
            data["full_width"] = 1
        if "font_small" in cols and manifest.get("font_small"):
            data["font_small"] = 1
        if "collection_row_id" in cols:
            data["collection_row_id"] = None
        try:
            sort_col = "sort_order" in cols
            if sort_col:
                if parent_id:
                    nxt = conn.execute(
                        "SELECT COALESCE(MAX(sort_order), -1)+1 FROM pages WHERE parent_id=?",
                        (parent_id,),
                    ).fetchone()[0]
                else:
                    nxt = conn.execute(
                        "SELECT COALESCE(MAX(sort_order), -1)+1 FROM pages WHERE parent_id IS NULL"
                    ).fetchone()[0]
                data["sort_order"] = nxt if nxt is not None else 0
        except Exception:
            pass
        keys = ", ".join(data.keys())
        placeholders = ", ".join("?" for _ in data)
        cur = conn.execute(
            f"INSERT INTO pages ({keys}) VALUES ({placeholders})", tuple(data.values())
        )
        return int(cur.lastrowid)

    @staticmethod
    def _create_row(
        conn, actor, collection: sqlite3.Row, resolved: dict, row: sqlite3.Row,
        *, view_hints: dict | None = None,
    ) -> int:
        view_hints = view_hints or {}
        props = {
            p["name"]: dict(p)
            for p in conn.execute(
                "SELECT * FROM collection_properties WHERE collection_id=?",
                (collection["id"],),
            ).fetchall()
        }
        try:
            from app.services.property_types import validate_property_value
        except Exception:
            validate_property_value = None  # type: ignore
        warnings: list[str] = resolved.setdefault("__warnings__", [])
        values: dict[str, Any] = {}
        for name, raw in (resolved.get("__properties__") or {}).items():
            target = props.get(name)
            if target is None:
                # Compat : les anciens templates stockent parfois l'id.
                by_id = next(
                    (p for p in props.values() if str(p["id"]) == str(name)), None
                )
                if by_id is None:
                    warnings.append(f"Propriété « {name} » introuvable : ignorée")
                    continue
                target = by_id
            options = []
            try:
                options = json.loads(target.get("options_json") or "[]") or []
            except (json.JSONDecodeError, TypeError):
                options = []
            if validate_property_value is not None:
                try:
                    ok, err = validate_property_value(
                        target.get("prop_type") or "text", raw, options or None
                    )
                except Exception:
                    ok, err = True, ""
                if not ok:
                    warnings.append(f"Propriété « {target['name']} » : {err} — ignorée")
                    continue
            values[str(target["id"])] = raw
        # Les hints de vue priment sur le défaut (§7.3 : regroupement, date).
        for hint_key in ("group_property", "date_property"):
            hint = view_hints.get(hint_key)
            if isinstance(hint, dict) and hint.get("property") and hint.get("value") not in (None, ""):
                target = props.get(hint["property"])
                if target is not None:
                    values[str(target["id"])] = hint["value"]
                    warnings.append(
                        f"« {target['name']} » imposée par la vue : {hint['value']}"
                    )
        cp_cols = _cols(conn, "collection_pages")
        insert: dict[str, Any] = {}
        if "collection_id" in cp_cols:
            insert["collection_id"] = collection["id"]
        if "title" in cp_cols:
            insert["title"] = resolved.get("__title__") or row["name"]
        if "position" in cp_cols:
            try:
                insert["position"] = conn.execute(
                    "SELECT COALESCE(MAX(position), -1)+1 FROM collection_pages WHERE collection_id=?",
                    (collection["id"],),
                ).fetchone()[0]
            except Exception:
                insert["position"] = 0
        if "property_values_json" in cp_cols:
            insert["property_values_json"] = json.dumps(values)
        keys = ", ".join(insert.keys())
        placeholders = ", ".join("?" for _ in insert)
        cur = conn.execute(
            f"INSERT INTO collection_pages ({keys}) VALUES ({placeholders})",
            tuple(insert.values()),
        )
        row_id = int(cur.lastrowid)
        # Page-ombre : le corps du template devient le contenu de la ligne.
        blocks, warn = _read_holder_blocks(conn, row)
        if warn:
            warnings.append(warn)
        if blocks and "collection_row_id" in _cols(conn, "pages"):
            fresh = _fresh_block_ids(blocks)
            for block in fresh:
                if isinstance(block, dict) and isinstance(block.get("content"), str):
                    try:
                        text, _w = _resolve_string(block["content"], resolved, strict=False)
                        block["content"] = text
                    except ValueError:
                        pass
            try:
                page_data: dict[str, Any] = {"collection_row_id": row_id}
                pcols = _cols(conn, "pages")
                if "title" in pcols:
                    page_data["title"] = resolved.get("__title__") or row["name"]
                if "content" in pcols:
                    page_data["content"] = json.dumps(fresh)
                if "content_format" in pcols:
                    page_data["content_format"] = "blocks"
                if "workspace" in pcols:
                    page_data["workspace"] = "Private"
                if "search_excluded" in pcols:
                    page_data["search_excluded"] = 0
                keys = ", ".join(page_data.keys())
                placeholders = ", ".join("?" for _ in page_data)
                conn.execute(
                    f"INSERT INTO pages ({keys}) VALUES ({placeholders})",
                    tuple(page_data.values()),
                )
            except Exception:
                logger.exception("création page-ombre ligne %s", row_id)
                warnings.append("Corps du template non reporté sur la page de ligne")
        resolved["__warnings__"] = warnings
        return row_id

    @staticmethod
    def _create_database(
        conn, actor, workspace_id: int, resolved: dict, row: sqlite3.Row,
        *, parent_page_id: int | None = None,
    ) -> int:
        """Crée une collection depuis le template.

        Avec ``parent_page_id`` : base INLINE attachée au document courant
        (même patron que la pilule « Database ») — le front insère ensuite
        le bloc embed qui l'affiche en place, sans navigation.
        """
        manifest = _manifest(row)
        # Adaptateur legacy : le schéma vit dans database_templates.
        schema = (manifest.get("collection") or {}).get("schema")
        if schema is None and row["source_kind"] == "legacy_database" and row["source_id"]:
            try:
                legacy = conn.execute(
                    "SELECT * FROM database_templates WHERE id=?",
                    (int(row["source_id"]),),
                ).fetchone()
                if not legacy:
                    raise LookupError("Template de base d'origine introuvable")
                schema = json.loads(legacy["schema_json"] or "[]")
                name = resolved.get("__title__") or legacy["name"]
                icon = row["icon"] or (legacy["icon"] if "icon" in legacy.keys() else "📋")
                description = row["description"] or (legacy["description"] or "")
            except (ValueError, json.JSONDecodeError, TypeError) as exc:
                raise ValueError(f"schéma du template illisible : {exc}") from None
        else:
            schema = schema or []
            name = resolved.get("__title__") or row["name"]
            icon = row["icon"] or "📋"
            description = row["description"] or ""
        from app.services.db_templates import create_from_template

        title = name
        uid = _actor_id(actor)
        ccols = _cols(conn, "collections")
        # create_from_template() historique : on prépare la collection ici
        # pour maîtriser workspace_id / created_by sur tous les schémas.
        insert_cols = ["name", "description", "icon", "schema_json"]
        insert_vals: list[Any] = [title, description, icon, json.dumps(schema)]
        if "workspace_id" in ccols:
            insert_cols.append("workspace_id")
            insert_vals.append(workspace_id)
        if parent_page_id and "is_inline" in ccols:
            insert_cols.append("is_inline")
            insert_vals.append(1)
        if parent_page_id and "parent_page_id" in ccols:
            insert_cols.append("parent_page_id")
            insert_vals.append(parent_page_id)
        cur = conn.execute(
            f"INSERT INTO collections ({', '.join(insert_cols)}) "
            f"VALUES ({', '.join('?' for _ in insert_cols)})",
            tuple(insert_vals),
        )
        collection_id = int(cur.lastrowid)
        try:
            if "created_by" in ccols and uid:
                conn.execute(
                    "UPDATE collections SET created_by=? WHERE id=?", (uid, collection_id)
                )
        except Exception:
            pass
        from app.services.db_templates import materialize_properties

        materialize_properties(conn, collection_id, schema)
        try:
            if _table_exists(conn, "collection_views"):
                conn.execute(
                    "INSERT INTO collection_views (collection_id, name, view_type, config_json) VALUES (?,?,?,?)",
                    (collection_id, "Default View", "table", json.dumps({
                        "visible_properties": ["Title"], "sorts": [], "filters": [],
                    })),
                )
        except Exception:
            logger.exception("vue par défaut base depuis template %s", row["id"])
        # Lignes d'exemple (optionnelles, seed_rows = références de templates).
        for seed in (manifest.get("collection") or {}).get("seed_rows") or []:
            try:
                TemplateService.instantiate(
                    conn, actor, seed.get("template_ref", ""),
                    seed.get("variables") or {},
                    {"collection_id": collection_id},
                    run_source="automation",
                )
            except Exception:
                logger.exception("seed row template base %s", row["id"])
        void = create_from_template  # compat : le helper historique reste l'API bas niveau
        return collection_id

    @staticmethod
    def _instantiate_legacy(
        conn, actor, template_id: str, variables: dict, context: dict,
        *, idempotency_key: str | None, run_source: str,
    ) -> dict:
        """Avant la migration 48 : instancie directement depuis les silos."""
        uid = _actor_id(actor)
        if template_id.startswith("legacy_database:"):
            tid = int(template_id.split(":", 1)[1])
            legacy = conn.execute(
                "SELECT * FROM database_templates WHERE id=?", (tid,)
            ).fetchone()
            if not legacy:
                raise LookupError("Template introuvable")
            from app.services.db_templates import create_from_template

            _ws = _valid_workspace(conn, actor, context.get("workspace_id"))
            ws_id = _ws if _ws is not None else 1
            cid = create_from_template(
                conn,
                (variables.get("title") or legacy["name"]),
                dict(legacy),
                workspace_id=ws_id,
            )
            conn.commit()
            return {
                "template_id": template_id, "created_type": "collection",
                "created_id": str(cid), "url": f"/db/{cid}",
                "warnings": [], "run_id": None,
            }
        if template_id.startswith("legacy_global:"):
            tid = int(template_id.split(":", 1)[1])
            row = conn.execute(
                "SELECT * FROM page_global_templates WHERE id=?", (tid,)
            ).fetchone()
            if not row:
                raise LookupError("Template introuvable")
            try:
                blocks = json.loads(row["blocks_json"] or "[]")
            except (json.JSONDecodeError, TypeError):
                blocks = []
            conn.commit()
            return {
                "template_id": template_id, "created_type": "insertion",
                "created_id": "", "url": "",
                "blocks": _fresh_block_ids(blocks if isinstance(blocks, list) else []),
                "warnings": [], "run_id": None,
            }
        raise LookupError("Template introuvable")

    # ── Journal ──────────────────────────────────────────────────────────

    @staticmethod
    def _log_run(
        conn, template_id: str, run_source: str, actor_id: int | None,
        created_type: str, created_id: str, warnings: list[str],
        *, error: str = "", idempotency_key: str | None = None,
        occurrence_key: str | None = None,
    ) -> str | None:
        if not registry_ready(conn):
            return None
        # Les templates legacy (":") n'ont pas de ligne au registre : pas de run.
        if ":" in str(template_id):
            return None
        run_id = _new_id("trun")
        try:
            conn.execute(
                """INSERT INTO template_runs (id, template_id, run_source, actor_id,
                    occurrence_key, created_type, created_id, status, warnings_json,
                    error, idempotency_key, created_at)
                   VALUES (?,?,?,?,?,?,?,?,?,?,?,?)""",
                (
                    run_id, template_id, run_source, actor_id, occurrence_key,
                    created_type, str(created_id or ""),
                    "error" if error else "ok", json.dumps(warnings or []),
                    error or "", idempotency_key, _now_iso(),
                ),
            )
            return run_id
        except sqlite3.IntegrityError:
            # Dédup récurrence/idempotence : le run existe déjà, on le renvoie.
            logger.info("run template dédupliqué (%s)", occurrence_key or idempotency_key)
            try:
                key_col = "occurrence_key" if occurrence_key else "idempotency_key"
                key_val = occurrence_key or idempotency_key
                existing = conn.execute(
                    f"SELECT id FROM template_runs WHERE {key_col}=?", (key_val,)
                ).fetchone()
                return existing["id"] if existing else None
            except Exception:
                return None

    @staticmethod
    def mark_undone(conn, actor, run_id: str) -> dict:
        """Annule une instanciation (toast « Annuler ») : soft-delete + statut."""
        if not registry_ready(conn):
            raise LookupError("Journal indisponible")
        run = conn.execute(
            "SELECT * FROM template_runs WHERE id=?", (run_id,)
        ).fetchone()
        if not run or run["status"] != "ok":
            raise LookupError("Run introuvable ou déjà annulé")
        ctype, cid = run["created_type"], run["created_id"]
        try:
            if ctype == "page" and cid:
                cols = _cols(conn, "pages")
                if "deleted_at" in cols:
                    conn.execute(
                        "UPDATE pages SET deleted_at=? WHERE id=?", (_now_iso(), int(cid))
                    )
                else:
                    conn.execute("DELETE FROM pages WHERE id=?", (int(cid),))
            elif ctype == "row" and cid:
                conn.execute(
                    "DELETE FROM collection_pages WHERE id=?", (int(cid),)
                )
            elif ctype == "collection" and cid:
                conn.execute("DELETE FROM collections WHERE id=?", (int(cid),))
        except (ValueError, TypeError):
            pass
        conn.execute("UPDATE template_runs SET status='undone' WHERE id=?", (run_id,))
        conn.commit()
        return {"run_id": run_id, "status": "undone"}

    # ── Backfill (P8) ────────────────────────────────────────────────────

    @staticmethod
    def backfill_registry(conn: sqlite3.Connection) -> dict[str, int]:
        """Catalogue les silos legacy vers le registre — idempotent (rejouable).

        Clé d'idempotence : ``source_kind + source_id``. Ne déplace aucun
        contenu : l'instanciation lit le silo d'origine tant que le template
        n'est pas édité (convergence à la première édition).
        """
        counts = {"legacy_database": 0, "legacy_page": 0, "legacy_global": 0}
        if not registry_ready(conn):
            return counts
        now = _now_iso()
        if _table_exists(conn, "database_templates"):
            for r in conn.execute("SELECT * FROM database_templates").fetchall():
                exists = conn.execute(
                    "SELECT 1 FROM templates WHERE source_kind='legacy_database' AND source_id=?",
                    (str(r["id"]),),
                ).fetchone()
                if exists:
                    continue
                conn.execute(
                    """INSERT INTO templates (id, workspace_id, kind, name, description,
                        icon, category, scope, source_kind, source_id, manifest_json,
                        is_system, created_at, updated_at)
                       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
                    (
                        _new_id("tpl"), 1, "database", r["name"],
                        r["description"] or "",
                        r["icon"] if "icon" in r.keys() else "📋",
                        "Base", "system", "legacy_database", str(r["id"]),
                        json.dumps({"format": "flowdeck-template", "version": 1}),
                        1, now, now,
                    ),
                )
                counts["legacy_database"] += 1
        if _table_exists(conn, "page_templates"):
            for r in conn.execute("SELECT * FROM page_templates").fetchall():
                exists = conn.execute(
                    "SELECT 1 FROM templates WHERE source_kind='legacy_page' AND source_id=?",
                    (str(r["id"]),),
                ).fetchone()
                if exists:
                    continue
                manifest: dict[str, Any] = {
                    "format": "flowdeck-template", "version": 1,
                }
                try:
                    manifest["property_defaults"] = json.loads(
                        r["property_values_json"] or "{}"
                    )
                except (json.JSONDecodeError, TypeError):
                    manifest["property_defaults"] = {}
                keys = r.keys() if hasattr(r, "keys") else []
                tid = _new_id("tpl")
                conn.execute(
                    """INSERT INTO templates (id, workspace_id, kind, name, description,
                        scope, target_collection_id, source_kind, source_id,
                        manifest_json, created_at, updated_at)
                       VALUES (?,?,?,?,?,?,?,?,?,?,?,?)""",
                    (
                        tid, 1, "row", r["name"] or "Sans titre",
                        (r["description"] if "description" in keys else "") or "",
                        "workspace", r["collection_id"],
                        "legacy_page", str(r["id"]), json.dumps(manifest), now, now,
                    ),
                )
                counts["legacy_page"] += 1
                # Récurrence legacy → template_recurrences (transcription RRULE).
                try:
                    if (
                        "is_recurring" in keys and r["is_recurring"]
                        and "recurrence_rule" in keys and r["recurrence_rule"]
                    ):
                        conn.execute(
                            """INSERT OR IGNORE INTO template_recurrences
                               (template_id, rrule, timezone, enabled, next_run_at, updated_at)
                               VALUES (?,?,?,?,?,?)""",
                            (
                                tid, r["recurrence_rule"], "", 1,
                                _compute_next_run(r["recurrence_rule"], "", start=now),
                                now,
                            ),
                        )
                except Exception:
                    logger.exception("transcription récurrence legacy %s", r["id"])
        if _table_exists(conn, "page_global_templates"):
            for r in conn.execute("SELECT * FROM page_global_templates").fetchall():
                exists = conn.execute(
                    "SELECT 1 FROM templates WHERE source_kind='legacy_global' AND source_id=?",
                    (str(r["id"]),),
                ).fetchone()
                if exists:
                    continue
                conn.execute(
                    """INSERT INTO templates (id, workspace_id, kind, name, description,
                        icon, scope, owner_id, source_kind, source_id, manifest_json,
                        created_at, updated_at)
                       VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)""",
                    (
                        _new_id("tpl"), 1, "blocks", r["name"],
                        r["description"] or "", r["icon"] or "📄", "personal",
                        r["created_by"] if "created_by" in r.keys() else None,
                        "legacy_global", str(r["id"]),
                        json.dumps({"format": "flowdeck-template", "version": 1}),
                        now, now,
                    ),
                )
                counts["legacy_global"] += 1
        conn.commit()
        logger.info("backfill registre templates : %s", counts)
        return counts

    # ── Presets système (§12) ────────────────────────────────────────────

    @staticmethod
    def ensure_system_presets(conn: sqlite3.Connection) -> int:
        """Installe / rafraîchit les presets fournis (idempotent).

        Idempotence par (scope='system', kind, name). Un bump de
        ``PRESET_VERSION`` rafraîchit les presets système existants dont les
        types de blocs ou le manifeste ont été corrigés entre deux versions
        (les presets système ne sont jamais édités en place par l'utilisateur).
        """
        if not registry_ready(conn):
            return 0
        created = 0
        holder_ws = _ensure_workspace(conn, None)
        for preset in SYSTEM_PRESETS:
            existing = conn.execute(
                "SELECT id, content_page_id, manifest_json FROM templates "
                "WHERE scope='system' AND kind=? AND name=? AND deleted_at IS NULL",
                (preset["kind"], preset["name"]),
            ).fetchone()
            if existing is not None:
                try:
                    cur_manifest = json.loads(existing["manifest_json"] or "{}")
                except (json.JSONDecodeError, TypeError):
                    cur_manifest = {}
                if cur_manifest.get("preset_version") == PRESET_VERSION:
                    continue
                TemplateService._refresh_system_preset(conn, preset, existing, holder_ws)
                continue
            now = _now_iso()
            tid = _new_id("tpl")
            holder_id = None
            if preset["kind"] in ("page", "row", "blocks"):
                holder_id = TemplateService._create_holder(
                    conn, None, holder_ws, title=preset["name"],
                    blocks=preset.get("blocks") or [],
                )
            manifest = {"format": "flowdeck-template", "version": 1,
                        "preset_version": PRESET_VERSION}
            if preset.get("manifest"):
                manifest.update(preset["manifest"])
            if preset.get("title_template"):
                manifest["title_template"] = preset["title_template"]
            if preset.get("property_defaults"):
                manifest["property_defaults"] = preset["property_defaults"]
            conn.execute(
                """INSERT INTO templates (id, workspace_id, kind, name, description,
                    icon, category, scope, content_page_id, source_kind, manifest_json,
                    variables_json, is_system, created_at, updated_at)
                   VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
                (
                    tid, 1, preset["kind"], preset["name"],
                    preset.get("description") or "", preset.get("icon") or "📄",
                    preset.get("category") or "", "system", holder_id, "native",
                    json.dumps(manifest),
                    json.dumps(preset.get("variables") or []),
                    1, now, now,
                ),
            )
            created += 1
        if created:
            conn.commit()
        return created

    @staticmethod
    def _refresh_system_preset(conn, preset: dict, existing: sqlite3.Row,
                              holder_ws: int | None) -> None:
        """Met à jour un preset système existant (blocs + manifeste)."""
        manifest = {"format": "flowdeck-template", "version": 1,
                    "preset_version": PRESET_VERSION}
        if preset.get("manifest"):
            manifest.update(preset["manifest"])
        if preset.get("title_template"):
            manifest["title_template"] = preset["title_template"]
        if preset.get("property_defaults"):
            manifest["property_defaults"] = preset["property_defaults"]
        holder_id = existing["content_page_id"]
        if preset["kind"] in ("page", "row", "blocks"):
            blocks = preset.get("blocks") or []
            if holder_id:
                cols = _cols(conn, "pages")
                if "content" in cols:
                    conn.execute(
                        "UPDATE pages SET content=? WHERE id=?",
                        (json.dumps(blocks), holder_id),
                    )
            else:
                holder_id = TemplateService._create_holder(
                    conn, None, holder_ws, title=preset["name"], blocks=blocks,
                )
        conn.execute(
            "UPDATE templates SET description=?, icon=?, category=?, "
            "content_page_id=?, manifest_json=?, variables_json=?, updated_at=? "
            "WHERE id=?",
            (
                preset.get("description") or "", preset.get("icon") or "📄",
                preset.get("category") or "", holder_id, json.dumps(manifest),
                json.dumps(preset.get("variables") or []), _now_iso(),
                existing["id"],
            ),
        )

    # ── Récurrences dues (§11.2) ─────────────────────────────────────────

    @staticmethod
    def run_due_recurrences(
        conn: sqlite3.Connection, *, now_iso: str | None = None
    ) -> list[dict]:
        """Exécute les récurrences dues — dédupliqué par ``occurrence_key``."""
        out: list[dict] = []
        if not registry_ready(conn):
            return out
        now_iso = now_iso or _now_iso()
        due = conn.execute(
            """SELECT t.*, r.rrule, r.timezone, r.enabled, r.next_run_at FROM templates t
               JOIN template_recurrences r ON r.template_id=t.id
               WHERE r.enabled=1 AND (r.next_run_at IS NULL OR r.next_run_at<=?)
                 AND t.deleted_at IS NULL AND t.archived_at IS NULL
                 AND t.kind='row'""",
            (now_iso,),
        ).fetchall()
        for row in due:
            occurrence = (row["next_run_at"] or now_iso)[:10]
            occurrence_key = f"{row['id']}@{occurrence}"
            # Base archivée/supprimée → la récurrence se désactive elle-même.
            target_ok = True
            try:
                if row["target_collection_id"] and not conn.execute(
                    "SELECT 1 FROM collections WHERE id=?", (row["target_collection_id"],)
                ).fetchone():
                    target_ok = False
            except Exception:
                target_ok = True
            if not target_ok:
                conn.execute(
                    "UPDATE template_recurrences SET enabled=0, updated_at=? WHERE template_id=?",
                    (_now_iso(), row["id"]),
                )
                TemplateService._log_run(
                    conn, row["id"], "recurrence", None, "", "",
                    [f"Base cible disparue : récurrence désactivée ({occurrence})"],
                    occurrence_key=occurrence_key,
                )
                continue
            already = conn.execute(
                "SELECT 1 FROM template_runs WHERE occurrence_key=?", (occurrence_key,)
            ).fetchone()
            if already:
                pass
            else:
                try:
                    TemplateService.instantiate(
                        conn, {"id": row["created_by"], "login": ""},
                        row["id"], {},
                        {"collection_id": row["target_collection_id"],
                         "occurrence_key": occurrence_key},
                        run_source="recurrence",
                    )
                    out.append({"template_id": row["id"], "occurrence": occurrence, "status": "ok"})
                except Exception as exc:
                    logger.exception("récurrence template %s", row["id"])
                    TemplateService._emit(
                        "template.recurrence_failed",
                        {"template_id": row["id"], "error": str(exc)},
                    )
                    out.append({"template_id": row["id"], "occurrence": occurrence,
                                "status": "error", "error": str(exc)})
            try:
                nxt = _compute_next_run(
                    row["rrule"], row["timezone"] or "",
                    start=(row["next_run_at"] or now_iso),
                )
                conn.execute(
                    "UPDATE template_recurrences SET next_run_at=?, last_run_at=?, updated_at=? WHERE template_id=?",
                    (nxt, now_iso, _now_iso(), row["id"]),
                )
            except Exception:
                logger.exception("recalcul next_run %s", row["id"])
        try:
            conn.commit()
        except Exception:
            pass
        return out

    # ── Divers ───────────────────────────────────────────────────────────

    @staticmethod
    def _url_for(created_type: str, created_id: str) -> str:
        if created_type == "page" and created_id:
            return f"/pages/{created_id}"
        if created_type == "row" and created_id:
            return f"/db/row/{created_id}"
        if created_type == "collection" and created_id:
            return f"/db/{created_id}"
        return ""

    @staticmethod
    def _emit(event: str, payload: dict) -> None:
        try:
            from app.services.automations import fire_event, run_event_sync

            run_event_sync(fire_event(event, payload))
        except Exception:
            pass


# ── RRULE minimal (sous-ensemble de recurrence.py) ─────────────────────────

def _compute_next_run(rrule: str, timezone: str, *, start: str) -> str:
    """Prochaine occurrence ISO après ``start`` (DAILY/WEEKLY/MONTHLY + INTERVAL)."""
    try:
        base = datetime.fromisoformat(str(start)[:19])
    except (ValueError, TypeError):
        base = datetime.now()
    parts: dict[str, str] = {}
    for chunk in (rrule or "").split(";"):
        if "=" in chunk:
            key, _, value = chunk.partition("=")
            parts[key.strip().upper()] = value.strip().upper()
    freq = parts.get("FREQ", "DAILY")
    try:
        interval = max(1, int(parts.get("INTERVAL", "1")))
    except ValueError:
        interval = 1
    if freq == "WEEKLY":
        byday = parts.get("BYDAY", "")
        if byday:
            order = ["MO", "TU", "WE", "TH", "FR", "SA", "SU"]
            wanted = [d for d in byday.split(",") if d in order]
            if wanted:
                for delta in range(1, 8 * max(1, interval) + 1):
                    candidate = base + timedelta(days=delta)
                    if order[candidate.weekday()] in wanted:
                        return candidate.replace(
                            hour=9, minute=0, second=0, microsecond=0
                        ).isoformat()
        return (base + timedelta(weeks=interval)).isoformat()
    if freq == "MONTHLY":
        return (base + timedelta(days=30 * interval)).isoformat()
    if freq == "YEARLY":
        return (base + timedelta(days=365 * interval)).isoformat()
    return (base + timedelta(days=interval)).isoformat()


# ── Galerie système (§12) ──────────────────────────────────────────────────
# Types de blocs réels de l'éditeur : paragraph, heading_1..4, bulleted_list,
# numbered_list, to_do, toggle, quote, callout, divider, code…

def _para(text: str = "") -> dict:
    return {"id": uuid.uuid4().hex[:8], "type": "paragraph", "content": text}


def _h1(text: str) -> dict:
    return {"id": uuid.uuid4().hex[:8], "type": "heading_1", "content": text}


def _h2(text: str) -> dict:
    return {"id": uuid.uuid4().hex[:8], "type": "heading_2", "content": text}


def _h3(text: str) -> dict:
    return {"id": uuid.uuid4().hex[:8], "type": "heading_3", "content": text}


def _todo(text: str = "") -> dict:
    return {"id": uuid.uuid4().hex[:8], "type": "to_do", "content": text, "checked": False}


def _bullet(text: str = "") -> dict:
    return {"id": uuid.uuid4().hex[:8], "type": "bulleted_list", "content": text}


def _num(text: str = "") -> dict:
    return {"id": uuid.uuid4().hex[:8], "type": "numbered_list", "content": text}


def _quote(text: str = "") -> dict:
    return {"id": uuid.uuid4().hex[:8], "type": "quote", "content": text}


def _callout(text: str = "", icon: str = "💡") -> dict:
    return {"id": uuid.uuid4().hex[:8], "type": "callout", "content": text, "icon": icon}


def _divider() -> dict:
    return {"id": uuid.uuid4().hex[:8], "type": "divider", "content": ""}


def _toggle(text: str = "") -> dict:
    return {
        "id": uuid.uuid4().hex[:8], "type": "toggle", "content": text, "expanded": True,
        "children": [{"id": uuid.uuid4().hex[:8], "type": "paragraph", "content": ""}],
    }


def _db_preset(name, icon, category, description, schema):
    """Template de base système : le schéma vit dans manifest.collection.schema."""
    return {
        "kind": "database", "name": name, "icon": icon, "category": category,
        "description": description,
        "manifest": {"collection": {"name": name, "icon": icon, "schema": schema}},
    }


def _status(name, options):
    return {"name": name, "type": "status",
            "options": [{"name": o, "color": c} for o, c in options]}


def _select(name, options):
    return {"name": name, "type": "select",
            "options": [{"name": o, "color": c} for o, c in options]}


# Incrémenter pour rafraîchir les presets système déjà installés (contenu ou
# types de blocs corrigés entre deux versions de Flowdeck).
PRESET_VERSION = 3


SYSTEM_PRESETS: list[dict] = [
    # ── Pages ──────────────────────────────────────────────────────────────
    {
        "kind": "page", "name": "Note de réunion", "icon": "🗒️",
        "category": "Réunion",
        "description": "Ordre du jour, notes et actions — datée du jour.",
        "title_template": "Réunion du {{today}} — {{title}}",
        "variables": [
            {"name": "title", "label": "Sujet", "type": "text", "required": False, "default": ""},
        ],
        "blocks": [
            _h2("Ordre du jour"), _bullet("…"), _bullet("…"),
            _h2("Notes"), _para("…"),
            _h2("Actions"), _todo("Action — {{me}}"),
        ],
    },
    {
        "kind": "page", "name": "Compte rendu client", "icon": "🤝",
        "category": "Réunion",
        "description": "Participants, décisions et prochaines étapes côté client.",
        "title_template": "CR — {{title}} ({{today}})",
        "variables": [{"name": "title", "label": "Client", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Participants"), _bullet("…"),
            _h2("Points discutés"), _para("…"),
            _h2("Décisions"), _bullet("…"),
            _h2("Prochaines étapes"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "1:1", "icon": "💬",
        "category": "Réunion",
        "description": "Trame d'entretien individuel hebdomadaire.",
        "title_template": "1:1 du {{today}} — {{title}}",
        "variables": [
            {"name": "title", "label": "Participant", "type": "text", "required": False, "default": ""},
        ],
        "blocks": [
            _h2("Points positifs"), _para("…"),
            _h2("Difficultés"), _para("…"),
            _h2("Actions"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Revue hebdo", "icon": "📄",
        "category": "Personnel",
        "description": "Fait, en cours, blocages de la semaine.",
        "title_template": "Revue du {{today}}",
        "blocks": [_h2("Fait"), _todo("…"), _h2("En cours"), _todo("…"), _h2("Blocages"), _para("…")],
    },
    {
        "kind": "page", "name": "Journal quotidien", "icon": "📓",
        "category": "Personnel",
        "description": "Page datée du jour, créée en un clic.",
        "title_template": "Journal — {{today}}",
        "blocks": [_h2("Aujourd'hui"), _para("…"), _h2("Demain"), _para("…")],
    },
    {
        "kind": "page", "name": "Journal de gratitude", "icon": "🙏",
        "category": "Personnel",
        "description": "Trois gratitudes et une intention du jour.",
        "title_template": "Gratitude — {{today}}",
        "blocks": [_h2("Je suis reconnaissant pour"), _bullet("…"), _bullet("…"), _bullet("…"),
                   _h2("Intention du jour"), _para("…")],
    },
    {
        "kind": "page", "name": "Spécification produit", "icon": "📐",
        "category": "Projet",
        "description": "Contexte, objectifs, périmètre et critères d'acceptation.",
        "title_template": "Spéc — {{title}}",
        "variables": [{"name": "title", "label": "Fonctionnalité", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Contexte"), _para("…"),
            _h2("Objectifs"), _bullet("…"),
            _h2("Hors périmètre"), _para("…"),
            _h2("Critères d'acceptation"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Plan de projet", "icon": "🗺️",
        "category": "Projet",
        "description": "Objectifs, jalons, risques et équipe.",
        "title_template": "Plan — {{title}}",
        "variables": [{"name": "title", "label": "Projet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Objectif"), _para("…"),
            _h2("Jalons"), _todo("M1"), _todo("M2"),
            _h2("Risques"), _bullet("…"),
            _h2("Équipe"), _para("…"),
        ],
    },
    {
        "kind": "page", "name": "Rétrospective", "icon": "🔁",
        "category": "Équipe",
        "description": "Ce qui a marché, à améliorer, actions.",
        "title_template": "Rétro — {{title}} ({{today}})",
        "variables": [{"name": "title", "label": "Période / équipe", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("👍 Ce qui a bien marché"), _bullet("…"),
            _h2("👎 À améliorer"), _bullet("…"),
            _h2("🎯 Actions"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Post-mortem incident", "icon": "🚨",
        "category": "Équipe",
        "description": "Chronologie, impact, cause racine, actions correctives.",
        "title_template": "Post-mortem — {{title}}",
        "variables": [{"name": "title", "label": "Incident", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Résumé"), _para("…"),
            _h2("Impact"), _para("…"),
            _h2("Chronologie"), _num("…"),
            _h2("Cause racine"), _para("…"),
            _h2("Actions correctives"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Objectifs OKR", "icon": "🎯",
        "category": "Projet",
        "description": "Objectifs et résultats clés du trimestre.",
        "title_template": "OKR — {{title}}",
        "variables": [{"name": "title", "label": "Trimestre / équipe", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Objectif 1"), _para("…"),
            _h3("Résultats clés"), _bullet("KR1 — 0%"), _bullet("KR2 — 0%"),
            _h2("Objectif 2"), _para("…"),
        ],
    },
    {
        "kind": "page", "name": "Décision (ADR)", "icon": "🏛️",
        "category": "Projet",
        "description": "Contexte, décision, conséquences — mini ADR.",
        "title_template": "ADR — {{title}}",
        "variables": [{"name": "title", "label": "Sujet", "type": "text", "required": False, "default": ""}],
        "blocks": [_h2("Contexte"), _para("…"), _h2("Décision"), _para("…"),
                   _h2("Alternatives"), _bullet("…"), _h2("Conséquences"), _para("…")],
    },
    {
        "kind": "page", "name": "Procédure (SOP)", "icon": "📋",
        "category": "Opérations",
        "description": "Marche à suivre pas à pas pour une tâche récurrente.",
        "title_template": "SOP — {{title}}",
        "variables": [{"name": "title", "label": "Tâche", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Objectif"), _para("…"),
            _h2("Prérequis"), _bullet("…"),
            _h2("Étapes"), _num("…"), _num("…"),
            _h2("Vérification"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Onboarding nouvel arrivant", "icon": "🚀",
        "category": "Équipe",
        "description": "Checklist des premiers jours d'un nouveau membre.",
        "title_template": "Onboarding — {{title}}",
        "variables": [{"name": "title", "label": "Prénom", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Jour 1"), _todo("Accès et comptes"), _todo("Rencontre de l'équipe"),
            _h2("Semaine 1"), _todo("Lire la doc"), _todo("Premier ticket"),
            _h2("30 jours"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Notes de cours", "icon": "🎓",
        "category": "Personnel",
        "description": "Trame de prise de notes structurée.",
        "title_template": "Cours — {{title}} ({{today}})",
        "variables": [{"name": "title", "label": "Sujet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Idées clés"), _bullet("…"),
            _h2("Détails"), _para("…"),
            _h2("Questions"), _bullet("…"),
            _h2("À retenir"), _callout("…", "📌"),
        ],
    },
    {
        "kind": "page", "name": "Fiche de lecture", "icon": "📚",
        "category": "Personnel",
        "description": "Résumé, citations et notes d'un livre.",
        "title_template": "Lecture — {{title}}",
        "variables": [{"name": "title", "label": "Titre du livre", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Résumé"), _para("…"),
            _h2("Citations"), _quote("…"),
            _h2("Mes notes"), _para("…"),
        ],
    },
    {
        "kind": "page", "name": "Recette de cuisine", "icon": "🍳",
        "category": "Personnel",
        "description": "Ingrédients, étapes et temps de préparation.",
        "title_template": "Recette — {{title}}",
        "variables": [{"name": "title", "label": "Plat", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Ingrédients"), _bullet("…"),
            _h2("Préparation"), _num("…"), _num("…"),
            _h2("Astuces"), _para("…"),
        ],
    },
    {
        "kind": "page", "name": "Itinéraire de voyage", "icon": "✈️",
        "category": "Personnel",
        "description": "Programme jour par jour, réservations et budget.",
        "title_template": "Voyage — {{title}}",
        "variables": [{"name": "title", "label": "Destination", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Réservations"), _todo("Transport"), _todo("Hébergement"),
            _h2("Programme"), _h3("Jour 1"), _bullet("…"),
            _h2("Budget"), _para("…"),
        ],
    },
    {
        "kind": "page", "name": "Fiche prospect", "icon": "🧭",
        "category": "Vente",
        "description": "Qualification, besoins, objections et prochain contact.",
        "title_template": "Prospect — {{title}}",
        "variables": [{"name": "title", "label": "Société", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Contacts"), _bullet("…"),
            _h2("Besoins"), _para("…"),
            _h2("Objections"), _bullet("…"),
            _h2("Prochaine action"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Communiqué de projet", "icon": "📣",
        "category": "Projet",
        "description": "Annonce d'avancement à partager avec les parties prenantes.",
        "title_template": "Communiqué — {{title}} ({{today}})",
        "variables": [{"name": "title", "label": "Projet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _callout("Statut : 🟢 On track", "📣"),
            _h2("Avancement"), _para("…"),
            _h2("Points de blocage"), _para("…"),
            _h2("Prochaines étapes"), _bullet("…"),
        ],
    },
    # ── Blocs ──────────────────────────────────────────────────────────────
    {
        "kind": "blocks", "name": "Sommaire + actions", "icon": "▦",
        "category": "Blocs",
        "description": "Un sommaire et une liste d'actions à insérer au curseur.",
        "blocks": [_h2("Sommaire"), _para("…"), _h2("Actions"), _todo("…")],
    },
    {
        "kind": "blocks", "name": "Questions de réunion", "icon": "❓",
        "category": "Blocs",
        "description": "Liste de questions à aborder en réunion.",
        "blocks": [_h3("Questions"), _bullet("…"), _bullet("…"), _bullet("…")],
    },
    {
        "kind": "blocks", "name": "Matrice d'Eisenhower", "icon": "🧮",
        "category": "Blocs",
        "description": "Priorisation urgent/important.",
        "blocks": [
            _h3("1 · Urgent & important"), _todo("…"),
            _h3("2 · Important, pas urgent"), _todo("…"),
            _h3("3 · Urgent, pas important"), _todo("…"),
            _h3("4 · Ni urgent ni important"), _bullet("…"),
        ],
    },
    {
        "kind": "blocks", "name": "FAQ", "icon": "💬",
        "category": "Blocs",
        "description": "Questions fréquentes avec réponses repliables.",
        "blocks": [_h3("Questions fréquentes"),
                   _toggle("Question 1 ?"), _toggle("Question 2 ?"), _toggle("Question 3 ?")],
    },
    {
        "kind": "blocks", "name": "Citation + source", "icon": "❝",
        "category": "Blocs",
        "description": "Une citation mise en avant avec sa source.",
        "blocks": [_quote("…"), _para("— Source")],
    },
    {
        "kind": "blocks", "name": "Checklist de démarrage", "icon": "✅",
        "category": "Blocs",
        "description": "Quelques cases à cocher pour lancer une activité.",
        "blocks": [_h3("Checklist de démarrage"), _todo("…"), _todo("…"), _todo("…"), _todo("…")],
    },
    # ── Bases de données ───────────────────────────────────────────────────
    _db_preset("Tâches", "✅", "Base", "Liste de tâches avec statut, priorité et échéance.", [
        {"name": "Tâche", "type": "title"},
        _status("Statut", [("À faire", "gray"), ("En cours", "blue"), ("Terminé", "green")]),
        _select("Priorité", [("Basse", "gray"), ("Moyenne", "yellow"), ("Haute", "red")]),
        {"name": "Échéance", "type": "date"},
        {"name": "Assigné à", "type": "person"},
    ]),
    _db_preset("Gestion de projet", "🚀", "Base", "Suivi de projets avec avancement et échéances.", [
        {"name": "Projet", "type": "title"},
        _status("Statut", [("Pas commencé", "gray"), ("En cours", "blue"), ("Terminé", "green")]),
        _select("Priorité", [("P1", "red"), ("P2", "orange"), ("P3", "gray")]),
        {"name": "Échéance", "type": "date"},
        {"name": "Responsable", "type": "person"},
        {"name": "Étiquettes", "type": "multi_select"},
    ]),
    _db_preset("Suivi de bugs", "🐞", "Base", "Rapport et suivi des anomalies.", [
        {"name": "Titre", "type": "title"},
        _select("Sévérité", [("Bloquante", "red"), ("Majeure", "orange"), ("Mineure", "gray")]),
        _status("Statut", [("Nouveau", "gray"), ("En cours", "blue"), ("Résolu", "green")]),
        {"name": "Version", "type": "text"},
        {"name": "Reproductible", "type": "checkbox"},
    ]),
    _db_preset("Contenu éditorial", "📝", "Base", "Planification des articles et publications.", [
        {"name": "Titre", "type": "title"},
        _select("Type", [("Article", "blue"), ("Vidéo", "orange"), ("Newsletter", "purple")]),
        _status("Statut", [("Idée", "gray"), ("Brouillon", "yellow"), ("Publié", "green")]),
        {"name": "Date de publication", "type": "date"},
        {"name": "Auteur", "type": "person"},
    ]),
    _db_preset("Habitudes", "🔁", "Base", "Suivi quotidien d'habitudes.", [
        {"name": "Habitude", "type": "title"},
        _select("Fréquence", [("Quotidien", "green"), ("Hebdo", "blue"), ("Mensuel", "gray")]),
        {"name": "Fait aujourd'hui", "type": "checkbox"},
        {"name": "Série", "type": "number"},
    ]),
    _db_preset("Dépenses", "💳", "Base", "Suivi des dépenses par catégorie.", [
        {"name": "Libellé", "type": "title"},
        {"name": "Montant", "type": "number"},
        _select("Catégorie", [("Alimentation", "green"), ("Transport", "blue"),
                              ("Logement", "orange"), ("Loisirs", "purple"), ("Autre", "gray")]),
        {"name": "Date", "type": "date"},
    ]),
    _db_preset("Inventaire", "📦", "Base", "Stock d'objets ou de produits.", [
        {"name": "Article", "type": "title"},
        {"name": "Quantité", "type": "number"},
        {"name": "Prix", "type": "number"},
        {"name": "Fournisseur", "type": "text"},
    ]),
    _db_preset("Bibliothèque de liens", "🔗", "Base", "Articles, vidéos et ressources à lire.", [
        {"name": "Titre", "type": "title"},
        {"name": "URL", "type": "url"},
        _status("Statut", [("À lire", "gray"), ("En cours", "blue"), ("Lu", "green")]),
        {"name": "Étiquettes", "type": "multi_select"},
    ]),
    _db_preset("Objectifs", "🎯", "Base", "Objectifs avec progression et échéance.", [
        {"name": "Objectif", "type": "title"},
        _status("Statut", [("À définir", "gray"), ("En cours", "blue"), ("Atteint", "green")]),
        {"name": "Progression", "type": "number"},
        {"name": "Échéance", "type": "date"},
    ]),
    _db_preset("Annuaire d'équipe", "👥", "Base", "Contacts et rôles de l'équipe.", [
        {"name": "Nom", "type": "title"},
        {"name": "Email", "type": "email"},
        {"name": "Téléphone", "type": "phone"},
        {"name": "Rôle", "type": "text"},
        {"name": "Équipe", "type": "select"},
    ]),
    # ── Lignes (templates de ligne — nécessitent la base cible) ─────────────
    {
        "kind": "row", "name": "Tâche", "icon": "✅", "category": "Lignes",
        "description": "Ligne de tâche pré-remplie (base « Tâches »).",
        "manifest": {"target_collection_name": "Tâches",
                     "property_defaults": {"Statut": "À faire", "Priorité": "Moyenne"}},
        "title_template": "{{title}}",
        "blocks": [_h3("Description"), _para("…"), _h3("Sous-tâches"), _todo("…")],
    },
    {
        "kind": "row", "name": "Bug", "icon": "🐞", "category": "Lignes",
        "description": "Ligne de bug pré-remplie (base « Suivi de bugs »).",
        "manifest": {"target_collection_name": "Suivi de bugs",
                     "property_defaults": {"Statut": "Nouveau", "Sévérité": "Mineure"}},
        "title_template": "Bug — {{title}}",
        "blocks": [_h2("Reproduction"), _num("…"), _h2("Attendu"), _para("…"),
                   _h2("Observé"), _para("…")],
    },
    {
        "kind": "row", "name": "Article", "icon": "📝", "category": "Lignes",
        "description": "Ligne d'article pré-remplie (base « Contenu éditorial »).",
        "manifest": {"target_collection_name": "Contenu éditorial",
                     "property_defaults": {"Statut": "Idée", "Type": "Article"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Angle"), _para("…"), _h2("Plan"), _bullet("…"), _h2("Brouillon"), _para("…")],
    },
    {
        "kind": "row", "name": "Dépense", "icon": "💳", "category": "Lignes",
        "description": "Ligne de dépense pré-remplie (base « Dépenses »).",
        "manifest": {"target_collection_name": "Dépenses",
                     "property_defaults": {"Catégorie": "Autre"}},
        "title_template": "{{title}}",
        "blocks": [],
    },
    {
        "kind": "row", "name": "Habitude", "icon": "🔁", "category": "Lignes",
        "description": "Ligne d'habitude pré-remplie (base « Habitudes »).",
        "manifest": {"target_collection_name": "Habitudes",
                     "property_defaults": {"Fréquence": "Quotidien"}},
        "title_template": "{{title}}",
        "blocks": [_para("Pourquoi cette habitude ?"), _todo("Déclencheur"), _todo("Récompense")],
    },

    # ── Pages — Productivité & réunions ────────────────────────────────────
    {
        "kind": "page", "name": "Semainier", "icon": "🗓️", "category": "Productivité",
        "description": "Planifier la semaine : priorités, rendez-vous, tâches du jour.",
        "title_template": "Semaine du {{today}}",
        "blocks": [
            _h2("Objectifs de la semaine"), _todo("…"), _todo("…"), _todo("…"),
            _h2("Rendez-vous"), _bullet("…"),
            _h3("Lundi"), _todo(""), _h3("Mardi"), _todo(""), _h3("Mercredi"), _todo(""),
            _h3("Jeudi"), _todo(""), _h3("Vendredi"), _todo(""),
            _h2("En attente / à déléguer"), _bullet("…"),
        ],
    },
    {
        "kind": "page", "name": "Daily standup", "icon": "☀️", "category": "Réunion",
        "description": "Point quotidien asynchrone : hier, aujourd'hui, blocages.",
        "title_template": "Standup — {{today}}",
        "blocks": [
            _h3("Hier"), _bullet("…"),
            _h3("Aujourd'hui"), _todo("…"),
            _h3("Blocages"), _callout("…", "🚧"),
        ],
    },
    {
        "kind": "page", "name": "Agenda de réunion", "icon": "📋", "category": "Réunion",
        "description": "Ordre du jour minuté, décisions et actions.",
        "title_template": "Agenda — {{title}} ({{today}})",
        "variables": [{"name": "title", "label": "Sujet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Participants"), _bullet("…"),
            _h2("Ordre du jour"), _num("Point 1 — 10 min"), _num("Point 2 — 10 min"),
            _h2("Décisions"), _bullet("…"),
            _h2("Actions"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Compte rendu de sprint", "icon": "🏁", "category": "Réunion",
        "description": "Sprint review : démo, vélocité, engagements du prochain sprint.",
        "title_template": "Sprint review — {{title}}",
        "variables": [{"name": "title", "label": "Sprint", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Objectif du sprint"), _para("…"),
            _h2("Livré"), _todo("…"),
            _h2("Non livré"), _bullet("…"),
            _h2("Vélocité"), _para("…"),
            _h2("Engagements prochain sprint"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Kickoff de projet", "icon": "🚦", "category": "Projet",
        "description": "Lancement : contexte, objectifs, périmètre, rôles et jalons.",
        "title_template": "Kickoff — {{title}}",
        "variables": [{"name": "title", "label": "Projet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Contexte"), _para("…"),
            _h2("Objectifs"), _bullet("…"),
            _h2("Périmètre"), _para("…"),
            _h2("Rôles"), _bullet("…"),
            _h2("Jalons"), _todo("M1"), _todo("M2"),
        ],
    },
    {
        "kind": "page", "name": "Registre des risques", "icon": "⚠️", "category": "Projet",
        "description": "Risques, probabilité, impact, mitigation et responsable.",
        "title_template": "Risques — {{title}}",
        "variables": [{"name": "title", "label": "Projet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Risque 1"), _bullet("Probabilité : —"), _bullet("Impact : —"),
            _bullet("Mitigation : —"), _bullet("Responsable : {{me}}"),
            _h2("Risque 2"), _bullet("Probabilité : —"), _bullet("Impact : —"),
        ],
    },
    {
        "kind": "page", "name": "Leçons apprises", "icon": "🧠", "category": "Projet",
        "description": "Bilan de fin de projet : réussites, échecs, à reproduire.",
        "title_template": "Leçons apprises — {{title}}",
        "variables": [{"name": "title", "label": "Projet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Ce qui a bien marché"), _bullet("…"),
            _h2("Ce qui a moins bien marché"), _bullet("…"),
            _h2("À reproduire"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Note de cadrage", "icon": "🧭", "category": "Projet",
        "description": "Périmètre, contraintes, hypothèses et livrables attendus.",
        "title_template": "Cadrage — {{title}}",
        "variables": [{"name": "title", "label": "Sujet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Périmètre"), _para("…"),
            _h2("Contraintes"), _bullet("…"),
            _h2("Hypothèses"), _bullet("…"),
            _h2("Livrables"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Plan de communication", "icon": "📡", "category": "Projet",
        "description": "Qui communique quoi, à qui, par quel canal et à quelle fréquence.",
        "title_template": "Communication — {{title}}",
        "variables": [{"name": "title", "label": "Projet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Audiences"), _bullet("…"),
            _h2("Messages clés"), _bullet("…"),
            _h2("Canaux & fréquence"), _bullet("Hebdo — réunion"), _bullet("Mensuel — note"),
            _h2("Responsables"), _para("…"),
        ],
    },
    # ── Pages — Travail (dev, produit, vente, marketing, RH, ops) ───────────
    {
        "kind": "page", "name": "Runbook d'incident", "icon": "🚑", "category": "Opérations",
        "description": "Procédure pas à pas de gestion d'incident.",
        "title_template": "Runbook — {{title}}",
        "variables": [{"name": "title", "label": "Incident / service", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Détection"), _num("Alerte reçue"), _num("Confirmer l'impact"),
            _h2("Mitigation"), _num("…"), _num("…"),
            _h2("Communication"), _bullet("Statut à l'équipe"),
            _h2("Post-mortem"), _todo("Rédiger le post-mortem"),
        ],
    },
    {
        "kind": "page", "name": "Checklist de code review", "icon": "👀", "category": "Dev",
        "description": "Points à vérifier avant de valider une revue de code.",
        "title_template": "Review — {{title}}",
        "variables": [{"name": "title", "label": "PR / branche", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Fonctionnel"), _todo("Le comportement correspond au besoin"), _todo("Cas limites gérés"),
            _h2("Qualité"), _todo("Lisibilité"), _todo("Tests"), _todo("Pas de secret / debug oublié"),
            _h2("Performance & sécurité"), _todo("Requêtes raisonnables"), _todo("Entrées validées"),
        ],
    },
    {
        "kind": "page", "name": "Documentation d'API", "icon": "🔌", "category": "Dev",
        "description": "Description d'un endpoint : route, params, réponses, erreurs.",
        "title_template": "API — {{title}}",
        "variables": [{"name": "title", "label": "Endpoint", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Endpoint"), _para("GET /api/…"),
            _h2("Paramètres"), _bullet("…"),
            _h2("Réponse"), _callout("200 OK — …", "✅"),
            _h2("Erreurs"), _bullet("400 — requête invalide"), _bullet("404 — introuvable"),
            _h2("Exemple"), _para("…"),
        ],
    },
    {
        "kind": "page", "name": "Doc d'architecture", "icon": "🏗️", "category": "Dev",
        "description": "Vue d'ensemble : composants, flux, décisions structurantes.",
        "title_template": "Architecture — {{title}}",
        "variables": [{"name": "title", "label": "Système", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Contexte"), _para("…"),
            _h2("Composants"), _bullet("…"),
            _h2("Flux de données"), _para("…"),
            _h2("Décisions clés"), _bullet("…"),
        ],
    },
    {
        "kind": "page", "name": "Plan de test / QA", "icon": "🧪", "category": "Dev",
        "description": "Périmètre de test, cas, environnements et critères de sortie.",
        "title_template": "Plan de test — {{title}}",
        "variables": [{"name": "title", "label": "Version", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Périmètre"), _para("…"),
            _h2("Cas de test"), _todo("…"), _todo("…"),
            _h2("Environnements"), _bullet("…"),
            _h2("Critères de sortie"), _callout("0 bug bloquant", "🚦"),
        ],
    },
    {
        "kind": "page", "name": "Changelog", "icon": "📜", "category": "Produit",
        "description": "Notes de version : nouveautés, corrections, ruptures.",
        "title_template": "Changelog — {{today}}",
        "blocks": [
            _h2("Nouveautés"), _bullet("…"),
            _h2("Améliorations"), _bullet("…"),
            _h2("Corrections"), _bullet("…"),
            _h2("Ruptures"), _callout("Aucune", "⚠️"),
        ],
    },
    {
        "kind": "page", "name": "User persona", "icon": "🧑‍💼", "category": "Produit",
        "description": "Profil utilisateur : objectifs, frustrations, contexte.",
        "title_template": "Persona — {{title}}",
        "variables": [{"name": "title", "label": "Nom du persona", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Profil"), _bullet("Âge / rôle : —"), _bullet("Contexte : —"),
            _h2("Objectifs"), _bullet("…"),
            _h2("Frustrations"), _bullet("…"),
            _h2("Citations"), _quote("…"),
        ],
    },
    {
        "kind": "page", "name": "Guide d'entretien utilisateur", "icon": "🎤", "category": "Produit",
        "description": "Trame d'entretien de recherche utilisateur.",
        "title_template": "Entretien — {{title}} ({{today}})",
        "variables": [{"name": "title", "label": "Participant", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Contexte"), _bullet("Objectif — …"),
            _h2("Questions"), _num("…"), _num("…"), _num("…"),
            _h2("Notes"), _para("…"),
            _h2("Synthèse"), _para("…"),
        ],
    },
    {
        "kind": "page", "name": "Roadmap produit", "icon": "🛣️", "category": "Produit",
        "description": "Vision et jalons par trimestre.",
        "title_template": "Roadmap — {{title}}",
        "variables": [{"name": "title", "label": "Produit", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Vision"), _para("…"),
            _h3("Trimestre en cours"), _todo("…"),
            _h3("Prochain trimestre"), _todo("…"),
            _h3("Plus tard"), _bullet("…"),
        ],
    },
    {
        "kind": "page", "name": "Business Model Canvas", "icon": "🧩", "category": "Stratégie",
        "description": "Les 9 blocs du business model sur une page.",
        "title_template": "BMC — {{title}}",
        "variables": [{"name": "title", "label": "Projet / entreprise", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h3("Segments de clientèle"), _bullet("…"),
            _h3("Proposition de valeur"), _bullet("…"),
            _h3("Canaux"), _bullet("…"),
            _h3("Relations clients"), _bullet("…"),
            _h3("Sources de revenus"), _bullet("…"),
            _h3("Ressources clés"), _bullet("…"),
            _h3("Activités clés"), _bullet("…"),
            _h3("Partenaires clés"), _bullet("…"),
            _h3("Structure de coûts"), _bullet("…"),
        ],
    },
    {
        "kind": "page", "name": "Analyse SWOT", "icon": "📊", "category": "Stratégie",
        "description": "Forces, faiblesses, opportunités, menaces.",
        "title_template": "SWOT — {{title}}",
        "variables": [{"name": "title", "label": "Sujet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h3("Forces (Strengths)"), _bullet("…"),
            _h3("Faiblesses (Weaknesses)"), _bullet("…"),
            _h3("Opportunités (Opportunities)"), _bullet("…"),
            _h3("Menaces (Threats)"), _bullet("…"),
        ],
    },
    {
        "kind": "page", "name": "Matrice RACI", "icon": "🔲", "category": "Projet",
        "description": "Qui est Responsable, Approbateur, Consulté, Informé.",
        "title_template": "RACI — {{title}}",
        "variables": [{"name": "title", "label": "Projet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Légende"), _bullet("R = Réalise · A = Approuve · C = Consulté · I = Informé"),
            _h2("Activités"), _bullet("Activité 1 — R:… A:… C:… I:…"), _bullet("Activité 2 — R:… A:… C:… I:…"),
        ],
    },
    {
        "kind": "page", "name": "Proposition commerciale", "icon": "🤝", "category": "Vente",
        "description": "Besoin, solution, tarif et planning pour un client.",
        "title_template": "Proposition — {{title}}",
        "variables": [{"name": "title", "label": "Client", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Contexte & besoin"), _para("…"),
            _h2("Solution proposée"), _bullet("…"),
            _h2("Tarif"), _para("…"),
            _h2("Planning"), _bullet("…"),
            _h2("Prochaines étapes"), _todo("Signature / bon pour accord"),
        ],
    },
    {
        "kind": "page", "name": "Facture", "icon": "🧾", "category": "Vente",
        "description": "Modèle de facture simple (client, lignes, total, échéance).",
        "title_template": "Facture — {{title}} ({{today}})",
        "variables": [{"name": "title", "label": "Client", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Émetteur"), _para("Nom\nAdresse\nSIRET"),
            _h2("Client"), _para("…"),
            _h2("Lignes"), _bullet("Prestation — Qté — Prix"),
            _h2("Total"), _callout("Total : … €", "💰"),
            _h2("Échéance"), _para("Paiement à 30 jours"),
        ],
    },
    {
        "kind": "page", "name": "Plan marketing", "icon": "📣", "category": "Marketing",
        "description": "Objectifs, cibles, canaux, budget et calendrier.",
        "title_template": "Plan marketing — {{title}}",
        "variables": [{"name": "title", "label": "Produit / campagne", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Objectifs"), _bullet("…"),
            _h2("Cibles"), _bullet("…"),
            _h2("Canaux"), _bullet("…"),
            _h2("Budget"), _para("…"),
            _h2("Calendrier"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Brief créatif", "icon": "🎨", "category": "Marketing",
        "description": "Cadre d'un projet créatif : objectif, ton, contraintes, livrables.",
        "title_template": "Brief — {{title}}",
        "variables": [{"name": "title", "label": "Projet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Objectif"), _para("…"),
            _h2("Public cible"), _para("…"),
            _h2("Ton & style"), _bullet("…"),
            _h2("Contraintes"), _bullet("…"),
            _h2("Livrables"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Script d'appel", "icon": "📞", "category": "Vente",
        "description": "Trame d'appel de prospection ou de découverte.",
        "title_template": "Script d'appel — {{title}}",
        "variables": [{"name": "title", "label": "Cible", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Ouverture"), _para("Bonjour, …"),
            _h2("Qualification"), _num("Quel est votre besoin ?"), _num("Quel est votre délai ?"),
            _h2("Objections fréquentes"), _bullet("Trop cher → …"), _bullet("Pas le temps → …"),
            _h2("Prochaine étape"), _todo("Envoyer un récapitulatif"),
        ],
    },
    {
        "kind": "page", "name": "Rapport de performance", "icon": "📈", "category": "Opérations",
        "description": "Rapport hebdo de KPI et points saillants.",
        "title_template": "Rapport — semaine du {{today}}",
        "blocks": [
            _h2("Chiffres clés"), _bullet("KPI 1 : —"), _bullet("KPI 2 : —"),
            _h2("Faits marquants"), _bullet("…"),
            _h2("Points de vigilance"), _bullet("…"),
            _h2("Actions"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Fiche de poste", "icon": "🧑‍💻", "category": "RH",
        "description": "Mission, responsabilités, compétences et profil recherché.",
        "title_template": "Poste — {{title}}",
        "variables": [{"name": "title", "label": "Intitulé", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Mission"), _para("…"),
            _h2("Responsabilités"), _bullet("…"),
            _h2("Compétences requises"), _bullet("…"),
            _h2("Profil"), _para("…"),
        ],
    },
    {
        "kind": "page", "name": "Guide d'entretien d'embauche", "icon": "🎯", "category": "RH",
        "description": "Grille d'évaluation et questions d'entretien.",
        "title_template": "Entretien recrutement — {{title}}",
        "variables": [{"name": "title", "label": "Poste", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Candidat"), _bullet("Nom : —"), _bullet("Poste : —"),
            _h2("Questions techniques"), _num("…"), _num("…"),
            _h2("Évaluation"), _bullet("Technique : /5"), _bullet("Culture : /5"),
            _h2("Décision"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Politique interne", "icon": "📕", "category": "RH",
        "description": "Règles internes (congés, télétravail, notes de frais…).",
        "title_template": "Politique — {{title}}",
        "variables": [{"name": "title", "label": "Sujet", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Objet"), _para("…"),
            _h2("Règles"), _num("…"), _num("…"),
            _h2("Cas particuliers"), _bullet("…"),
            _h2("Référent"), _para("{{me}}"),
        ],
    },
    {
        "kind": "page", "name": "Guide d'installation", "icon": "🛠️", "category": "Opérations",
        "description": "Installation pas à pas d'un outil ou service.",
        "title_template": "Installation — {{title}}",
        "variables": [{"name": "title", "label": "Outil", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Prérequis"), _bullet("…"),
            _h2("Étapes"), _num("…"), _num("…"),
            _h2("Vérification"), _todo("…"),
            _h2("Dépannage"), _bullet("…"),
        ],
    },
    # ── Pages — Vie personnelle ─────────────────────────────────────────────
    {
        "kind": "page", "name": "Plan de repas", "icon": "🍽️", "category": "Personnel",
        "description": "Menus de la semaine et liste de courses associée.",
        "title_template": "Repas — semaine du {{today}}",
        "blocks": [
            _h3("Lundi"), _bullet(""), _h3("Mardi"), _bullet(""), _h3("Mercredi"), _bullet(""),
            _h3("Jeudi"), _bullet(""), _h3("Vendredi"), _bullet(""), _h3("Week-end"), _bullet(""),
            _h2("Courses"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Liste de courses", "icon": "🛒", "category": "Personnel",
        "description": "Liste de courses par rayon.",
        "title_template": "Courses — {{today}}",
        "blocks": [
            _h3("Fruits & légumes"), _todo(""), _h3("Épicerie"), _todo(""),
            _h3("Frais"), _todo(""), _h3("Entretien"), _todo(""),
        ],
    },
    {
        "kind": "page", "name": "Checklist de ménage", "icon": "🧽", "category": "Personnel",
        "description": "Tâches d'entretien de la maison, par fréquence.",
        "title_template": "Ménage — {{today}}",
        "blocks": [
            _h3("Tous les jours"), _todo(""), _h3("Chaque semaine"), _todo(""), _h3("Chaque mois"), _todo(""),
        ],
    },
    {
        "kind": "page", "name": "Suivi d'entraînement", "icon": "🏋️", "category": "Personnel",
        "description": "Programme et journal d'entraînement sportif.",
        "title_template": "Entraînement — semaine du {{today}}",
        "blocks": [
            _h2("Objectif"), _para("…"),
            _h3("Lundi"), _bullet(""), _h3("Mercredi"), _bullet(""), _h3("Vendredi"), _bullet(""),
            _h2("Journal"), _para("…"),
        ],
    },
    {
        "kind": "page", "name": "Plan de carrière", "icon": "🪜", "category": "Personnel",
        "description": "Objectifs professionnels à moyen et long terme.",
        "title_template": "Carrière — {{title}}",
        "variables": [{"name": "title", "label": "Horizon", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h2("Où je veux être"), _para("…"),
            _h2("Compétences à développer"), _bullet("…"),
            _h2("Étapes"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Rétrospective personnelle", "icon": "🪞", "category": "Personnel",
        "description": "Bilan personnel : continuer, arrêter, commencer.",
        "title_template": "Rétro perso — {{today}}",
        "blocks": [
            _h2("Continuer"), _bullet("…"),
            _h2("Arrêter"), _bullet("…"),
            _h2("Commencer"), _todo("…"),
        ],
    },
    {
        "kind": "page", "name": "Checklist de valise", "icon": "🧳", "category": "Personnel",
        "description": "Quoi emporter, par catégorie, pour un voyage.",
        "title_template": "Valise — {{title}}",
        "variables": [{"name": "title", "label": "Destination", "type": "text", "required": False, "default": ""}],
        "blocks": [
            _h3("Documents"), _todo("Passeport"), _todo("Billets"),
            _h3("Vêtements"), _todo(""), _h3("Toilette"), _todo(""), _h3("Électronique"), _todo(""),
        ],
    },
    {
        "kind": "page", "name": "Boîte à idées", "icon": "💡", "category": "Personnel",
        "description": "Vrac d'idées à trier plus tard.",
        "title_template": "Idées — {{today}}",
        "blocks": [_h2("Idées en vrac"), _bullet("…"), _bullet("…"), _h2("À explorer"), _todo("…")],
    },
    # ── Blocs ──────────────────────────────────────────────────────────────
    {
        "kind": "blocks", "name": "Étapes numérotées", "icon": "🔢", "category": "Blocs",
        "description": "Une procédure en étapes numérotées.",
        "blocks": [_h3("Étapes"), _num("Étape 1"), _num("Étape 2"), _num("Étape 3")],
    },
    {
        "kind": "blocks", "name": "Avantages / Inconvénients", "icon": "⚖️", "category": "Blocs",
        "description": "Comparaison en deux volets.",
        "blocks": [_h3("✅ Avantages"), _bullet("…"), _h3("❌ Inconvénients"), _bullet("…")],
    },
    {
        "kind": "blocks", "name": "Mise en garde + Astuce", "icon": "⚠️", "category": "Blocs",
        "description": "Un avertissement suivi d'une astuce.",
        "blocks": [_callout("⚠️ Attention : …", "⚠️"), _callout("💡 Astuce : …", "💡")],
    },
    {
        "kind": "blocks", "name": "Définition", "icon": "📖", "category": "Blocs",
        "description": "Un encadré de définition / concept clé.",
        "blocks": [_callout("**Terme** — définition…", "📖")],
    },
    {
        "kind": "blocks", "name": "Ressources / liens", "icon": "🔗", "category": "Blocs",
        "description": "Une liste de ressources et liens utiles.",
        "blocks": [_h3("Ressources"), _bullet("…"), _bullet("…"), _bullet("…")],
    },
    {
        "kind": "blocks", "name": "Contacts clés", "icon": "📇", "category": "Blocs",
        "description": "Un bloc de contacts avec coordonnées.",
        "blocks": [_h3("Contacts clés"), _bullet("Nom — rôle — email"), _bullet("Nom — rôle — email")],
    },
    {
        "kind": "blocks", "name": "Présentation courte", "icon": "🙋", "category": "Blocs",
        "description": "Une bio / présentation en quelques lignes.",
        "blocks": [_h3("À propos"), _para("Nom\nRôle\nQuelques mots…"), _divider()],
    },
    {
        "kind": "blocks", "name": "Comparatif d'options", "icon": "🅰️", "category": "Blocs",
        "description": "Comparer deux options côte à côte.",
        "blocks": [_h3("Option A"), _bullet("…"), _h3("Option B"), _bullet("…"),
                   _h3("Recommandation"), _callout("…", "👉")],
    },
    {
        "kind": "blocks", "name": "Signature d'email", "icon": "✉️", "category": "Blocs",
        "description": "Bloc de signature à réutiliser.",
        "blocks": [_divider(), _para("Prénom Nom — Rôle\nEmail · Téléphone · Site"), _quote("« Une phrase de signature. »")],
    },
    # ── Bases de données ───────────────────────────────────────────────────
    _db_preset("Pipeline de vente", "💰", "Base", "Opportunités commerciales et avancement des deals.", [
        {"name": "Opportunité", "type": "title"},
        _status("Étape", [("Nouveau", "gray"), ("Qualifié", "blue"), ("Proposition", "yellow"), ("Gagné", "green"), ("Perdu", "red")]),
        {"name": "Montant", "type": "number"},
        {"name": "Contact", "type": "text"},
        {"name": "Probabilité", "type": "number"},
        {"name": "Clôture", "type": "date"},
    ]),
    _db_preset("Devis / Factures", "🧾", "Base", "Suivi des devis et factures.", [
        {"name": "Numéro", "type": "title"},
        _select("Type", [("Devis", "blue"), ("Facture", "purple")]),
        {"name": "Client", "type": "text"},
        {"name": "Montant", "type": "number"},
        _status("Statut", [("Brouillon", "gray"), ("Envoyé", "yellow"), ("Payé", "green")]),
        {"name": "Date", "type": "date"},
    ]),
    _db_preset("Réunions", "🗓️", "Base", "Journal des réunions et comptes rendus.", [
        {"name": "Réunion", "type": "title"},
        {"name": "Date", "type": "date"},
        {"name": "Participants", "type": "person"},
        _status("Statut", [("Prévue", "gray"), ("Faite", "green"), ("Annulée", "red")]),
        {"name": "Compte rendu", "type": "text"},
    ]),
    _db_preset("Registre des décisions", "🏛️", "Base", "Journal des décisions (ADR log).", [
        {"name": "Décision", "type": "title"},
        _status("Statut", [("Proposée", "gray"), ("Acceptée", "green"), ("Remplacée", "orange")]),
        {"name": "Responsable", "type": "person"},
        {"name": "Date", "type": "date"},
        {"name": "Contexte", "type": "text"},
    ]),
    _db_preset("Backlog / Idées", "💡", "Base", "Idées et demandes à prioriser.", [
        {"name": "Idée", "type": "title"},
        _status("Statut", [("Idée", "gray"), ("À évaluer", "yellow"), ("Retenue", "green")]),
        _select("Catégorie", [("Produit", "blue"), ("Technique", "purple"), ("Autre", "gray")]),
        {"name": "Votes", "type": "number"},
        {"name": "Description", "type": "text"},
    ]),
    _db_preset("Sprints", "🏃", "Base", "Planification des sprints.", [
        {"name": "Sprint", "type": "title"},
        {"name": "Début", "type": "date"},
        {"name": "Fin", "type": "date"},
        _status("Statut", [("À venir", "gray"), ("En cours", "blue"), ("Terminé", "green")]),
        {"name": "Objectif", "type": "text"},
    ]),
    _db_preset("Roadmap / Fonctionnalités", "🛣️", "Base", "Fonctionnalités planifiées par version.", [
        {"name": "Fonctionnalité", "type": "title"},
        _status("Statut", [("Idée", "gray"), ("Planifiée", "blue"), ("Livrée", "green")]),
        _select("Priorité", [("Haute", "red"), ("Moyenne", "yellow"), ("Basse", "gray")]),
        {"name": "Version", "type": "text"},
        {"name": "Échéance", "type": "date"},
    ]),
    _db_preset("Tests QA", "🧪", "Base", "Cas de test et résultats.", [
        {"name": "Cas de test", "type": "title"},
        _status("Statut", [("À faire", "gray"), ("OK", "green"), ("Échec", "red")]),
        _select("Priorité", [("Haute", "red"), ("Moyenne", "yellow"), ("Basse", "gray")]),
        {"name": "Étape", "type": "text"},
        _select("Résultat", [("À tester", "gray"), ("Passé", "green"), ("Échoué", "red")]),
    ]),
    _db_preset("Risques", "⚠️", "Base", "Registre des risques projet.", [
        {"name": "Risque", "type": "title"},
        _select("Probabilité", [("Faible", "gray"), ("Moyenne", "yellow"), ("Élevée", "red")]),
        _select("Impact", [("Faible", "gray"), ("Moyen", "yellow"), ("Fort", "red")]),
        _status("Statut", [("Ouvert", "red"), ("Surveillé", "yellow"), ("Clos", "green")]),
        {"name": "Mitigation", "type": "text"},
    ]),
    _db_preset("Candidatures", "📮", "Base", "Suivi des candidatures et entretiens.", [
        {"name": "Poste", "type": "title"},
        {"name": "Entreprise", "type": "text"},
        _status("Statut", [("À postuler", "gray"), ("Postulé", "blue"), ("Entretien", "yellow"), ("Offre", "green"), ("Refusé", "red")]),
        {"name": "Date", "type": "date"},
        {"name": "Lien", "type": "url"},
    ]),
    _db_preset("Formations & cours", "🎓", "Base", "Suivi des formations et cours.", [
        {"name": "Formation", "type": "title"},
        {"name": "Plateforme", "type": "text"},
        _status("Statut", [("À commencer", "gray"), ("En cours", "blue"), ("Terminée", "green")]),
        {"name": "Durée (h)", "type": "number"},
        {"name": "Date", "type": "date"},
    ]),
    _db_preset("Compétences", "🧠", "Base", "Matrice de compétences et niveaux.", [
        {"name": "Compétence", "type": "title"},
        _select("Niveau", [("Débutant", "gray"), ("Intermédiaire", "blue"), ("Avancé", "green"), ("Expert", "purple")]),
        _select("Catégorie", [("Technique", "blue"), ("Produit", "purple"), ("Humain", "green")]),
        {"name": "Dernière pratique", "type": "date"},
    ]),
    _db_preset("Abonnements", "🔁", "Base", "Tracker d'abonnements et renouvellements.", [
        {"name": "Abonnement", "type": "title"},
        {"name": "Coût", "type": "number"},
        _select("Fréquence", [("Mensuel", "blue"), ("Annuel", "purple"), ("Hebdo", "gray")]),
        {"name": "Renouvellement", "type": "date"},
        _status("Statut", [("Actif", "green"), ("Résilié", "red")]),
    ]),
    _db_preset("Budget / Revenus", "💰", "Base", "Revenus et dépenses, toutes catégories.", [
        {"name": "Libellé", "type": "title"},
        {"name": "Montant", "type": "number"},
        _select("Type", [("Revenu", "green"), ("Dépense", "red")]),
        _select("Catégorie", [("Salaire", "green"), ("Fixe", "orange"), ("Variable", "blue"), ("Autre", "gray")]),
        {"name": "Date", "type": "date"},
    ]),
    _db_preset("Dépenses partagées", "🤝", "Base", "Partage de frais et remboursements.", [
        {"name": "Objet", "type": "title"},
        {"name": "Montant", "type": "number"},
        {"name": "Payé par", "type": "person"},
        {"name": "Remboursé", "type": "checkbox"},
        {"name": "Date", "type": "date"},
    ]),
    _db_preset("Campagnes marketing", "📣", "Base", "Suivi des campagnes marketing.", [
        {"name": "Campagne", "type": "title"},
        _select("Canal", [("Email", "blue"), ("Réseaux", "purple"), ("Ads", "orange"), ("SEO", "green")]),
        _status("Statut", [("Idée", "gray"), ("En cours", "blue"), ("Terminée", "green")]),
        {"name": "Budget", "type": "number"},
        {"name": "Dates", "type": "date"},
    ]),
    _db_preset("Publications réseaux sociaux", "📱", "Base", "Planning des publications.", [
        {"name": "Titre", "type": "title"},
        _select("Plateforme", [("LinkedIn", "blue"), ("X", "gray"), ("Instagram", "purple"), ("YouTube", "red"), ("TikTok", "orange")]),
        _status("Statut", [("Idée", "gray"), ("Rédigé", "yellow"), ("Publié", "green")]),
        {"name": "Date", "type": "date"},
        {"name": "URL", "type": "url"},
    ]),
    _db_preset("KPI / Métriques", "📈", "Base", "Suivi des indicateurs clés.", [
        {"name": "Indicateur", "type": "title"},
        {"name": "Valeur", "type": "number"},
        {"name": "Cible", "type": "number"},
        {"name": "Période", "type": "date"},
        _status("Statut", [("Atteint", "green"), ("En cours", "yellow"), ("À risque", "red")]),
    ]),
    _db_preset("Événements", "🎉", "Base", "Organisation d'événements.", [
        {"name": "Événement", "type": "title"},
        {"name": "Date", "type": "date"},
        {"name": "Lieu", "type": "text"},
        _status("Statut", [("Prévu", "gray"), ("Confirmé", "blue"), ("Terminé", "green")]),
        {"name": "Responsable", "type": "person"},
    ]),
    _db_preset("Recettes", "🍳", "Base", "Carnet de recettes.", [
        {"name": "Recette", "type": "title"},
        _select("Catégorie", [("Entrée", "green"), ("Plat", "orange"), ("Dessert", "purple"), ("Boisson", "blue")]),
        {"name": "Temps (min)", "type": "number"},
        {"name": "Personnes", "type": "number"},
        {"name": "URL", "type": "url"},
    ]),
    _db_preset("Films & séries", "🎬", "Base", "Wishlist et suivi de visionnage.", [
        {"name": "Titre", "type": "title"},
        _select("Type", [("Film", "blue"), ("Série", "purple"), ("Doc", "green")]),
        _status("Statut", [("À voir", "gray"), ("En cours", "yellow"), ("Vu", "green")]),
        {"name": "Note", "type": "number"},
    ]),
    _db_preset("Voyages", "✈️", "Base", "Projets de voyage et itinéraires.", [
        {"name": "Voyage", "type": "title"},
        {"name": "Destination", "type": "text"},
        {"name": "Dates", "type": "date"},
        _status("Statut", [("Idée", "gray"), ("Réservé", "blue"), ("Fait", "green")]),
        {"name": "Budget", "type": "number"},
    ]),
    _db_preset("Fournisseurs", "🏭", "Base", "Carnet de fournisseurs et contacts.", [
        {"name": "Fournisseur", "type": "title"},
        {"name": "Contact", "type": "text"},
        {"name": "Email", "type": "email"},
        {"name": "Téléphone", "type": "phone"},
        _select("Catégorie", [("Matières", "orange"), ("Services", "blue"), ("Logistique", "purple"), ("Autre", "gray")]),
    ]),
    _db_preset("Matériel / Équipement", "🖥️", "Base", "Inventaire du matériel et de l'équipement.", [
        {"name": "Équipement", "type": "title"},
        {"name": "Quantité", "type": "number"},
        _status("État", [("Neuf", "green"), ("Bon", "blue"), ("Usé", "yellow"), ("HS", "red")]),
        {"name": "Prix", "type": "number"},
        {"name": "Date d'achat", "type": "date"},
    ]),
    _db_preset("Rendez-vous santé", "🩺", "Base", "Suivi des rendez-vous médicaux.", [
        {"name": "Sujet", "type": "title"},
        {"name": "Date", "type": "date"},
        {"name": "Praticien", "type": "text"},
        {"name": "Lieu", "type": "text"},
        {"name": "Notes", "type": "text"},
    ]),
    _db_preset("Entraînements", "🏋️", "Base", "Journal des séances de sport.", [
        {"name": "Séance", "type": "title"},
        {"name": "Date", "type": "date"},
        {"name": "Durée (min)", "type": "number"},
        _select("Type", [("Cardio", "blue"), ("Muscu", "orange"), ("Souplesse", "green"), ("Sport", "purple")]),
        _select("Intensité", [("Faible", "gray"), ("Moyenne", "yellow"), ("Élevée", "red")]),
    ]),
    # ── Lignes (templates de ligne — nécessitent la base cible) ─────────────
    {
        "kind": "row", "name": "Client", "icon": "👤", "category": "Lignes",
        "description": "Ligne de contact client (base « CRM / Contacts »).",
        "manifest": {"target_collection_name": "CRM / Contacts"},
        "title_template": "{{title}}",
        "blocks": [_h3("Coordonnées"), _para("Email / téléphone"), _h3("Contexte"), _para("…"),
                   _h3("Historique"), _bullet("…")],
    },
    {
        "kind": "row", "name": "Opportunité", "icon": "💰", "category": "Lignes",
        "description": "Ligne d'opportunité commerciale (base « Pipeline de vente »).",
        "manifest": {"target_collection_name": "Pipeline de vente",
                     "property_defaults": {"Étape": "Nouveau", "Probabilité": 20}},
        "title_template": "{{title}}",
        "blocks": [_h2("Besoin"), _para("…"), _h2("Décideur"), _bullet("…"),
                   _h2("Prochaines étapes"), _todo("…")],
    },
    {
        "kind": "row", "name": "Devis", "icon": "🧾", "category": "Lignes",
        "description": "Ligne de devis (base « Devis / Factures »).",
        "manifest": {"target_collection_name": "Devis / Factures",
                     "property_defaults": {"Type": "Devis", "Statut": "Brouillon"}},
        "title_template": "Devis — {{title}}",
        "blocks": [_h2("Prestations"), _bullet("…"), _h2("Total"), _para("…"),
                   _h2("Conditions"), _para("Validité 30 jours")],
    },
    {
        "kind": "row", "name": "Facture", "icon": "🧾", "category": "Lignes",
        "description": "Ligne de facture (base « Devis / Factures »).",
        "manifest": {"target_collection_name": "Devis / Factures",
                     "property_defaults": {"Type": "Facture", "Statut": "Envoyé"}},
        "title_template": "Facture — {{title}}",
        "blocks": [_h2("Détail"), _bullet("…"), _h2("Total"), _para("…"),
                   _h2("Échéance"), _para("Paiement à 30 jours")],
    },
    {
        "kind": "row", "name": "Réunion", "icon": "🗓️", "category": "Lignes",
        "description": "Ligne de réunion avec compte rendu (base « Réunions »).",
        "manifest": {"target_collection_name": "Réunions",
                     "property_defaults": {"Statut": "Prévue"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Ordre du jour"), _bullet("…"), _h2("Décisions"), _bullet("…"),
                   _h2("Actions"), _todo("…")],
    },
    {
        "kind": "row", "name": "Décision", "icon": "🏛️", "category": "Lignes",
        "description": "Ligne de décision (base « Registre des décisions »).",
        "manifest": {"target_collection_name": "Registre des décisions",
                     "property_defaults": {"Statut": "Acceptée"}},
        "title_template": "ADR — {{title}}",
        "blocks": [_h2("Contexte"), _para("…"), _h2("Décision"), _para("…"),
                   _h2("Conséquences"), _para("…")],
    },
    {
        "kind": "row", "name": "Risque", "icon": "⚠️", "category": "Lignes",
        "description": "Ligne de risque (base « Risques »).",
        "manifest": {"target_collection_name": "Risques",
                     "property_defaults": {"Probabilité": "Moyenne", "Impact": "Moyen", "Statut": "Ouvert"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Description"), _para("…"), _h2("Mitigation"), _para("…")],
    },
    {
        "kind": "row", "name": "Idée", "icon": "💡", "category": "Lignes",
        "description": "Ligne d'idée / backlog (base « Backlog / Idées »).",
        "manifest": {"target_collection_name": "Backlog / Idées",
                     "property_defaults": {"Statut": "Idée"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Problème"), _para("…"), _h2("Proposition"), _para("…"),
                   _h2("Bénéfices"), _bullet("…")],
    },
    {
        "kind": "row", "name": "Fonctionnalité", "icon": "🛣️", "category": "Lignes",
        "description": "Ligne de fonctionnalité roadmap (base « Roadmap / Fonctionnalités »).",
        "manifest": {"target_collection_name": "Roadmap / Fonctionnalités",
                     "property_defaults": {"Statut": "Idée", "Priorité": "Moyenne"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Objectif"), _para("…"), _h2("Critères d'acceptation"), _todo("…")],
    },
    {
        "kind": "row", "name": "Cas de test", "icon": "🧪", "category": "Lignes",
        "description": "Ligne de cas de test QA (base « Tests QA »).",
        "manifest": {"target_collection_name": "Tests QA",
                     "property_defaults": {"Statut": "À faire", "Résultat": "À tester"}},
        "title_template": "Test — {{title}}",
        "blocks": [_h2("Préconditions"), _bullet("…"), _h2("Étapes"), _num("…"),
                   _h2("Résultat attendu"), _para("…")],
    },
    {
        "kind": "row", "name": "Vidéo", "icon": "📱", "category": "Lignes",
        "description": "Ligne de publication vidéo (base « Publications réseaux sociaux »).",
        "manifest": {"target_collection_name": "Publications réseaux sociaux",
                     "property_defaults": {"Plateforme": "YouTube", "Statut": "Idée"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Angle"), _para("…"), _h2("Plan"), _bullet("…"), _h2("Script"), _para("…")],
    },
    {
        "kind": "row", "name": "Événement", "icon": "🎉", "category": "Lignes",
        "description": "Ligne d'événement (base « Événements »).",
        "manifest": {"target_collection_name": "Événements", "property_defaults": {"Statut": "Prévu"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Programme"), _bullet("…"), _h2("Logistique"), _todo("…"),
                   _h2("Bilan"), _para("…")],
    },
    {
        "kind": "row", "name": "Voyage", "icon": "✈️", "category": "Lignes",
        "description": "Ligne de voyage (base « Voyages »).",
        "manifest": {"target_collection_name": "Voyages", "property_defaults": {"Statut": "Idée"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Réservations"), _todo("Transport"), _todo("Hébergement"),
                   _h2("Programme"), _bullet("…")],
    },
    {
        "kind": "row", "name": "Recette", "icon": "🍳", "category": "Lignes",
        "description": "Ligne de recette (base « Recettes »).",
        "manifest": {"target_collection_name": "Recettes"},
        "title_template": "{{title}}",
        "blocks": [_h2("Ingrédients"), _bullet("…"), _h2("Préparation"), _num("…")],
    },
    {
        "kind": "row", "name": "Séance d'entraînement", "icon": "🏋️", "category": "Lignes",
        "description": "Ligne de séance de sport (base « Entraînements »).",
        "manifest": {"target_collection_name": "Entraînements",
                     "property_defaults": {"Type": "Muscu", "Intensité": "Moyenne"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Échauffement"), _todo("…"), _h2("Exercices"), _bullet("…"),
                   _h2("Étirements"), _todo("…")],
    },
    {
        "kind": "row", "name": "Candidature", "icon": "📮", "category": "Lignes",
        "description": "Ligne de candidature (base « Candidatures »).",
        "manifest": {"target_collection_name": "Candidatures",
                     "property_defaults": {"Statut": "À postuler"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Offre"), _para("…"), _h2("Points forts"), _bullet("…"),
                   _h2("Questions à poser"), _bullet("…")],
    },
    {
        "kind": "row", "name": "Formation", "icon": "🎓", "category": "Lignes",
        "description": "Ligne de formation (base « Formations & cours »).",
        "manifest": {"target_collection_name": "Formations & cours",
                     "property_defaults": {"Statut": "À commencer"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Objectifs"), _bullet("…"), _h2("Notes"), _para("…"),
                   _h2("À retenir"), _callout("…", "📌")],
    },
    {
        "kind": "row", "name": "Fournisseur", "icon": "🏭", "category": "Lignes",
        "description": "Ligne de fournisseur (base « Fournisseurs »).",
        "manifest": {"target_collection_name": "Fournisseurs"},
        "title_template": "{{title}}",
        "blocks": [_h2("Coordonnées"), _para("…"), _h2("Conditions"), _bullet("…")],
    },
    {
        "kind": "row", "name": "Abonnement", "icon": "🔁", "category": "Lignes",
        "description": "Ligne d'abonnement (base « Abonnements »).",
        "manifest": {"target_collection_name": "Abonnements",
                     "property_defaults": {"Fréquence": "Mensuel"}},
        "title_template": "{{title}}",
        "blocks": [_h2("Détails"), _bullet("Coût — …"), _h2("Utilité"), _para("…")],
    },
    {
        "kind": "row", "name": "Indicateur", "icon": "📈", "category": "Lignes",
        "description": "Ligne de KPI (base « KPI / Métriques »).",
        "manifest": {"target_collection_name": "KPI / Métriques"},
        "title_template": "{{title}}",
        "blocks": [_h2("Définition"), _para("…"), _h2("Source"), _bullet("…")],
    },
]



def ensure_registry(conn: sqlite3.Connection) -> dict[str, int]:
    """Garantit registre + backfill + presets (boot, idempotent, non bloquant)."""
    stats: dict[str, int] = {"backfill": {}, "presets": 0}
    try:
        if not registry_ready(conn):
            return stats
        stats["backfill"] = TemplateService.backfill_registry(conn)
        stats["presets"] = TemplateService.ensure_system_presets(conn)
    except Exception:
        logger.exception("ensure_registry templates (dégradation : silos legacy)")
    return stats


async def template_recurrence_scheduler(interval_seconds: int = 60):
    """Boucle de fond : instancie les templates récurrents dus (§11.2).

    Même patron que ``reminder_scheduler`` (scan 60 s, dédup par
    ``occurrence_key``) : un redémarrage entre deux passages ne crée jamais
    de doublon.
    """
    import asyncio

    await asyncio.sleep(10)
    while True:
        try:
            from app.db import get_conn as _get_conn

            with _get_conn() as conn:
                fired = TemplateService.run_due_recurrences(conn)
            if fired:
                logger.info("Templates: %d récurrence(s) exécutée(s)", len(fired))
        except Exception as exc:  # pragma: no cover - défensif uniquement
            logger.warning("Template recurrence scan failed: %s", exc)
        await asyncio.sleep(max(30, int(interval_seconds)))