"""FlowDeck — Database templates (v5.3.0).

Defines the built-in (seeded) database templates, materializes a template's
schema into real ``collection_properties`` rows, and creates a collection from
a template. Templates are stored in ``database_templates`` (name, icon,
description, schema_json).
"""
from __future__ import annotations

import json

# ── Built-in templates ──
# Each schema entry: {"name", "type", "options"?: [{name, color}]}.
SEED_TEMPLATES: list[dict] = [
    {
        "name": "Project tracker",
        "icon": "🚀",
        "description": "Suivi de projets avec statut, priorité et échéances.",
        "schema": [
            {"name": "Title", "type": "title"},
            {"name": "Status", "type": "status", "options": [
                {"name": "Not started", "color": "gray"},
                {"name": "In progress", "color": "blue"},
                {"name": "Done", "color": "green"},
            ]},
            {"name": "Priority", "type": "select", "options": [
                {"name": "Low", "color": "gray"},
                {"name": "Medium", "color": "yellow"},
                {"name": "High", "color": "orange"},
                {"name": "Urgent", "color": "red"},
            ]},
            {"name": "Due date", "type": "date"},
            {"name": "Assignee", "type": "person"},
            {"name": "Tags", "type": "multi_select"},
        ],
    },
    {
        "name": "CRM / Contacts",
        "icon": "👥",
        "description": "Gestion des contacts et prospects.",
        "schema": [
            {"name": "Name", "type": "title"},
            {"name": "Email", "type": "email"},
            {"name": "Phone", "type": "phone"},
            {"name": "Company", "type": "text"},
            {"name": "Stage", "type": "status", "options": [
                {"name": "Lead", "color": "gray"},
                {"name": "Prospect", "color": "blue"},
                {"name": "Customer", "color": "green"},
            ]},
            {"name": "Tags", "type": "multi_select"},
        ],
    },
    {
        "name": "Task list",
        "icon": "✅",
        "description": "Liste de tâches simple avec assignation et échéance.",
        "schema": [
            {"name": "Task", "type": "title"},
            {"name": "Status", "type": "status", "options": [
                {"name": "To do", "color": "gray"},
                {"name": "In progress", "color": "blue"},
                {"name": "Done", "color": "green"},
            ]},
            {"name": "Priority", "type": "select", "options": [
                {"name": "Low", "color": "gray"},
                {"name": "Medium", "color": "yellow"},
                {"name": "High", "color": "red"},
            ]},
            {"name": "Due date", "type": "date"},
            {"name": "Assignee", "type": "person"},
        ],
    },
    {
        "name": "Content calendar",
        "icon": "📅",
        "description": "Planification de contenu et de publications.",
        "schema": [
            {"name": "Title", "type": "title"},
            {"name": "Type", "type": "select", "options": [
                {"name": "Article", "color": "blue"},
                {"name": "Video", "color": "orange"},
                {"name": "Social", "color": "green"},
                {"name": "Newsletter", "color": "purple"},
            ]},
            {"name": "Status", "type": "status", "options": [
                {"name": "Draft", "color": "gray"},
                {"name": "In review", "color": "yellow"},
                {"name": "Published", "color": "green"},
            ]},
            {"name": "Publish date", "type": "date"},
            {"name": "Category", "type": "select"},
        ],
    },
    {
        "name": "Meeting notes",
        "icon": "🗒️",
        "description": "Notes de réunion avec participants, agenda, notes et actions.",
        "schema": [
            {"name": "Title", "type": "title"},
            {"name": "Date", "type": "date"},
            {"name": "Attendees", "type": "person"},
            {"name": "Status", "type": "status", "options": [
                {"name": "Scheduled", "color": "gray"},
                {"name": "Done", "color": "green"},
            ]},
            {"name": "Agenda", "type": "text"},
            {"name": "Notes", "type": "text"},
            {"name": "Action items", "type": "multi_select"},
        ],
    },
    {
        "name": "Reading list",
        "icon": "📚",
        "description": "Articles, livres et ressources à lire.",
        "schema": [
            {"name": "Title", "type": "title"},
            {"name": "URL", "type": "url"},
            {"name": "Status", "type": "status", "options": [
                {"name": "To read", "color": "gray"},
                {"name": "Reading", "color": "blue"},
                {"name": "Done", "color": "green"},
            ]},
            {"name": "Notes", "type": "text"},
        ],
    },
]


def materialize_properties(conn, collection_id: int, schema: list) -> None:
    """Insert ``collection_properties`` rows from a template ``schema``.

    The ``title`` property is represented by ``collection_pages.title`` and is
    not created as a column. Rows are inserted in schema order.
    """
    position = 0
    for prop in schema:
        name = (prop.get("name") or "").strip()
        if not name:
            continue
        prop_type = prop.get("type", "text")
        if prop_type == "title":
            continue
        options = prop.get("options") or []
        # idempotency guard — skip if a same-named property already exists
        exists = conn.execute(
            "SELECT id FROM collection_properties WHERE collection_id=? AND name=?",
            (collection_id, name),
        ).fetchone()
        if exists:
            continue
        conn.execute(
            """INSERT INTO collection_properties
               (collection_id, name, prop_type, options_json, number_format, position)
               VALUES (?, ?, ?, ?, 'number', ?)""",
            (collection_id, name, prop_type, json.dumps(options), position),
        )
        position += 1


def create_from_template(
    conn,
    name: str,
    template: dict,
    *,
    parent_page_id: int | None = None,
    workspace_id: int | None = None,
) -> int:
    """Create a collection from a template (with properties + default view).

    ``template`` may be a DB row (sqlite Row) or a dict; it must expose
    ``icon``, ``description`` and ``schema_json`` (JSON-encoded schema).
    """
    icon = template.get("icon") if isinstance(template, dict) else template["icon"]
    description = template.get("description") if isinstance(template, dict) else template["description"]
    schema_json = template.get("schema_json") if isinstance(template, dict) else template["schema_json"]
    try:
        schema = json.loads(schema_json)
    except (json.JSONDecodeError, TypeError):
        schema = []

    cur = conn.execute(
        """INSERT INTO collections
           (name, description, icon, schema_json, is_inline, parent_page_id, workspace_id)
           VALUES (?, ?, ?, ?, 1, ?, ?)""",
        (name, description or "", icon or "📋", json.dumps(schema),
         parent_page_id, workspace_id),
    )
    collection_id = cur.lastrowid

    materialize_properties(conn, collection_id, schema)

    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": [],
        })),
    )
    return collection_id