"""FlowDeck — Board Kanban Notion-style + multi-vues + propriétés custom + AI keywords."""
from __future__ import annotations

import json
import logging

from fastapi import APIRouter, Request, HTTPException, Query
from fastapi.responses import HTMLResponse, JSONResponse

from app.db import get_conn
from app.services.gitea_client import gitea
from app.auth.session import SessionManager

logger = logging.getLogger(__name__)
router = APIRouter(tags=["board"], prefix="/board")

STATUS_COLORS = {"todo": "var(--gray)", "progress": "var(--blue)", "done": "var(--green)"}
STATUS_LABELS = {"todo": "To-do", "progress": "In progress", "done": "Complete"}

AI_KEYWORD_COLORS = [
    "#E03E3E", "#D9730D", "#9B72F0", "#0F7B6C", "#3399CC",
    "#E255A1", "#787774", "#6B4E3D", "#6374C4", "#5A9E4B",
]

# ── Core helpers ──

def _issue_column(issue: dict, columns: list[str], board_id: int) -> str:
    if issue.get("state") == "closed":
        return "Terminé" if "Terminé" in columns else columns[-1]
    for lbl in issue.get("labels", []):
        with get_conn() as conn:
            row = conn.execute(
                "SELECT column_name FROM col_mapping WHERE board_id=? AND gitea_label=?",
                (board_id, lbl["name"]),
            ).fetchone()
            if row and row["column_name"] in columns:
                return row["column_name"]
    return columns[0] if columns else "Backlog"


def _map_issue_to_card(issue: dict, owner: str = "", repo: str = "") -> dict:
    title = issue.get("title", "Untitled")
    status = "todo"
    if issue.get("state") == "closed":
        status = "done"
    labels = issue.get("labels", [])
    for lbl in labels:
        name = lbl.get("name", "").lower()
        if "progress" in name or "doing" in name:
            status = "progress"
        elif "done" in name or "complete" in name or "terminé" in name:
            status = "done"

    assignee = issue.get("assignee", {}) or {}
    assignee_name = assignee.get("login", "")
    tag = labels[0].get("name", "") if labels else ""
    tag_color = labels[0].get("color", "#787774") if labels else "#787774"
    if tag_color and not tag_color.startswith("#"):
        tag_color = f"#{tag_color}"

    icon_map = {
        "bug": "🐛", "feature": "✨", "enhancement": "⚡", "documentation": "📄",
        "design": "🎨", "testing": "🧪", "refactor": "🔧", "security": "🔒",
    }
    icon = "📄"
    for lbl in labels:
        for kw, emoji in icon_map.items():
            if kw in lbl.get("name", "").lower():
                icon = emoji
                break

    # Load custom property values
    props = {}
    if owner and repo:
        with get_conn() as conn:
            pvs = conn.execute("""
                SELECT pp.name, pp.prop_type, pv.value
                FROM property_values pv
                JOIN project_properties pp ON pp.id = pv.property_id
                WHERE pp.project_owner=? AND pp.project_name=? AND pv.gitea_issue_id=?
            """, (owner, repo, issue.get("number", 0))).fetchall()
            for pv in pvs:
                props[pv["name"]] = {"type": pv["prop_type"], "value": pv["value"]}

    # AI keywords from DB
    keywords = []
    if owner and repo:
        with get_conn() as conn:
            kw_rows = conn.execute(
                "SELECT keyword, color FROM ai_keywords WHERE project_owner=? AND project_name=? ORDER BY usage_count DESC",
                (owner, repo),
            ).fetchall()
            # Filter: show keywords matching this issue's labels
            label_names = {lbl.get("name", "").lower() for lbl in labels}
            for kw in kw_rows:
                if kw["keyword"].lower() in label_names or any(
                    kw["keyword"].lower() in lbl for lbl in label_names
                ):
                    keywords.append({"name": kw["keyword"], "color": kw["color"]})

    return {
        "id": str(issue.get("number", 0)),
        "title": title,
        "status": status,
        "status_color": STATUS_COLORS.get(status, "var(--gray)"),
        "status_label": STATUS_LABELS.get(status, "To-do"),
        "icon": icon,
        "assignee": assignee_name,
        "tag": tag if tag else None,
        "tag_color": tag_color,
        "due_date": issue.get("due_date", ""),
        "url": issue.get("html_url", ""),
        "keywords": keywords,
        "custom_props": props,
    }


def _build_page_tree(conn, parent_id: int | None, ws_key: str, depth: int = 0, max_depth: int = 3) -> list[dict]:
    """Build nested page tree recursively. max_depth prevents infinite recursion."""
    if depth >= max_depth:
        return []
    rows = conn.execute(
        "SELECT id, title, updated_at FROM pages WHERE workspace=? AND parent_id IS ? AND deleted_at IS NULL ORDER BY sort_order ASC, updated_at DESC",
        (ws_key, parent_id),
    ).fetchall()
    items = []
    for row in rows:
        children = _build_page_tree(conn, row["id"], ws_key, depth + 1, max_depth)
        items.append({
            "id": f"page/{row['id']}",
            "db_id": row["id"],
            "name": row["title"] or "New page",
            "icon": "📄",
            "url": f"/pages/{row['id']}",
            "active": False,
            "depth": depth,
            "has_children": len(children) > 0,
            "children": children,
        })
    return items


def _file_icon(name: str, content_format: str = "") -> str:
    """Map file extension to emoji icon."""
    import re
    # FlowDeck internal pages (no extension)
    if content_format and content_format != 'file':
        return '📝'
    n = name.lower()
    if re.search(r'\.(png|jpe?g|gif|webp|svg|bmp|ico)$', n): return '🖼️'
    if n.endswith('.pdf'): return '📕'
    if re.search(r'\.(md|markdown)$', n): return '📝'
    if n.endswith('.py'): return '🐍'
    if re.search(r'\.(js|jsx|ts|tsx)$', n): return '📜'
    if re.search(r'\.(html?|xml)$', n): return '🌐'
    if n.endswith('.css'): return '🎨'
    if n.endswith('.json'): return '📋'
    if n.endswith('.sql'): return '🗃️'
    if re.search(r'\.(sh|bash|zsh)$', n): return '💻'
    if n.endswith('.ps1'): return '⚡'
    if re.search(r'\.(rs|go|java|rb|php|c|cpp|h|swift|kt|scala|r)$', n): return '📜'
    if re.search(r'\.(txt|log)$', n): return '📄'
    if re.search(r'\.(zip|tar|gz|rar|7z)$', n): return '📦'
    return '📄'


def _load_workspace_pages(ws_cookie: str) -> list:
    """Load top-level pages with children for the active workspace."""
    if not ws_cookie:
        return []
    try:
        ws_id = int(ws_cookie)
        with get_conn() as conn:
            rows = conn.execute(
                "SELECT id, title, parent_section, content_format FROM pages WHERE workspace_id=? AND parent_id IS NULL AND deleted_at IS NULL ORDER BY created_at DESC",
                (ws_id,),
            ).fetchall()
        items = []
        for r in rows:
            is_folder = r["parent_section"] == "Workspace"
            title = r["title"] or "Untitled"
            sub_children = _load_children(r["id"])
            items.append({
                "db_id": r["id"], "name": title,
                "id": f"page/{r['id']}",
                "icon": "📁" if is_folder else _file_icon(title, r["content_format"]),
                "is_folder": is_folder,
                "child_count": len(sub_children),
                "children": sub_children,
            })
        return items
    except (ValueError, Exception):
        return []


def _load_children(parent_id: int) -> list:
    """Recursively load children of a page."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT id, title, parent_section, content_format FROM pages WHERE parent_id=? AND deleted_at IS NULL ORDER BY created_at",
            (parent_id,),
        ).fetchall()
    children = []
    for r in rows:
        is_folder = r["parent_section"] == "Workspace"
        title = r["title"] or "Untitled"
        sub_children = _load_children(r["id"])
        children.append({
            "db_id": r["id"], "name": title,
            "id": f"page/{r['id']}",
            "icon": "📁" if is_folder else _file_icon(title, r["content_format"]),
            "is_folder": is_folder,
            "child_count": len(sub_children),
            "children": sub_children,
        })
    return children


def _sidebar_data(request: Request, owner: str = "", repo: str = "") -> dict:
    user = SessionManager.decode_session(request.cookies.get("flowdeck_session", ""))
    ws_name = user.get("login", "Bruno") if user else "Bruno"
    ws_key = f"{owner}/{repo}" if owner and repo else ws_name  # workspace = project context

    # Active workspace name from cookie (for local workspace display)
    from app.routers.dashboard import WORKSPACE_COOKIE
    ws_cookie = request.cookies.get(WORKSPACE_COOKIE, "")
    active_ws_name = "Workspace"
    workspace_pages = []
    gitea_workspace = False
    gitea_owner = ""
    gitea_repo = ""
    has_active_workspace = False

    if ws_cookie and ws_cookie.startswith("gitea:"):
        # Gitea workspace: preserve context across pages
        parts = ws_cookie.split(":", 2)
        if len(parts) >= 3:
            gitea_owner = parts[1]
            gitea_repo = parts[2]
            active_ws_name = f"{gitea_owner}/{gitea_repo}"
            gitea_workspace = True
            has_active_workspace = True
            workspace_pages = []  # loaded client-side
            # Get local workspace ID for mirror
            if user:
                try:
                    with get_conn() as conn:
                        row = conn.execute(
                            "SELECT id FROM workspaces WHERE owner_id=? AND name=? AND settings_json LIKE ?",
                            (user["id"], f"{gitea_owner}/{gitea_repo}", "%gitea_repo%")
                        ).fetchone()
                        if row:
                            local_ws_id = row["id"]
                except Exception:
                    pass
    elif ws_cookie and user:
        try:
            wsi = int(ws_cookie)
            with get_conn() as conn:
                row = conn.execute(
                    "SELECT id, name, owner_id FROM workspaces WHERE id=? AND owner_id=?",
                    (wsi, user["id"])
                ).fetchone()
                if row:
                    active_ws_name = row["name"]
                    workspace_pages = _load_workspace_pages(ws_cookie)
                    has_active_workspace = True
        except (ValueError, Exception):
            pass
    local_ws_id = 0
    recent = []
    if owner and repo:
        view_map = {
            "Kanban board": "kanban", "Detailed board": "detailed",
            "Table view": "table", "Status overview": "status", "Team Load": "teamload",
        }
        first = True
        for label, view in view_map.items():
            indent = 0 if first else 1
            active = first
            recent.append({
                "id": f"{owner}/{repo}/{view}",
                "name": label, "icon": "📁" if first else "·",
                "url": f"/board/{owner}/{repo}?view={view}",
                "active": active, "indent": indent,
                "depth": indent, "has_children": False, "children": [],
            })
            first = False
    # Load pages as nested tree for this project workspace
    with get_conn() as conn:
        tree_pages = _build_page_tree(conn, None, ws_key)
        for p in tree_pages:
            recent.append(p)
    # Private pages: same as recent but filtered for page/ items (non-board views)
    private_items = [r for r in recent if r.get("active") or r["id"].startswith("page/")]

    # Load favorite pages from DB
    uid = user["id"] if user and user.get("id") else 1
    with get_conn() as conn:
        fav_rows = conn.execute(
            "SELECT p.id, p.title, p.workspace, p.updated_at FROM favorites f "
            "JOIN pages p ON p.id = f.page_id "
            "WHERE f.user_id=? ORDER BY f.position", (uid,)
        ).fetchall()
    favorites = []
    for r in fav_rows:
        favorites.append({
            "id": f"page/{r['id']}",
            "db_id": r["id"],
            "name": r["title"] or "New page",
            "icon": "📄",
            "url": f"/pages/{r['id']}",
            "active": False,
            "indent": 0,
            "depth": 0,
            "has_children": False,
            "children": [],
        })

    # Load shared pages (anyone with link or published)
    with get_conn() as conn:
        shared_rows = conn.execute(
            "SELECT id, title, workspace, share_mode, published FROM pages "
            "WHERE share_mode='anyone' OR published=1 ORDER BY updated_at DESC LIMIT 20"
        ).fetchall()
    shared_pages = []
    published_pages = []
    for r in shared_rows:
        page_entry = {
            "id": f"page/{r['id']}",
            "db_id": r["id"],
            "name": r["title"] or "New page",
            "icon": "🌐" if r["published"] else "🔗",
            "url": f"/pages/{r['id']}",
            "active": False,
            "indent": 0,
            "depth": 0,
            "has_children": False,
            "children": [],
        }
        if r["published"]:
            published_pages.append(page_entry)
        else:
            shared_pages.append(page_entry)

    # Auth method & OAuth badge data
    auth_method = "local"
    gitea_linked = False
    github_linked = False
    if user and user.get("id"):
        try:
            with get_conn() as conn:
                am_row = conn.execute("SELECT auth_method FROM users WHERE id=?", (user["id"],)).fetchone()
                if am_row and am_row["auth_method"]:
                    auth_method = am_row["auth_method"]
                tokens = conn.execute(
                    "SELECT provider FROM user_oauth_tokens WHERE user_id=?", (user["id"],)
                ).fetchall()
                for t in tokens:
                    if t["provider"] == "gitea":
                        gitea_linked = True
                    elif t["provider"] == "github":
                        github_linked = True
        except Exception:
            pass

    return {"workspace_name": ws_name, "workspace_initial": ws_name[0].upper() if ws_name else "B",
            "active_ws_name": active_ws_name,
            "workspace_key": f"{gitea_owner}/{gitea_repo}" if gitea_workspace else ws_key,
            "workspace_pages": workspace_pages,
            "gitea_workspace": gitea_workspace,
            "gitea_owner": gitea_owner,
            "gitea_repo": gitea_repo,
            "local_ws_id": local_ws_id,
            "current_page": repo or "Dashboard", "last_edited": "now",
            "recent_pages": recent, "private_pages": private_items,
            "favorite_pages": favorites, "shared_pages": shared_pages,
            "published_pages": published_pages,
            "user": user,
            "auth_method": auth_method,
            "gitea_linked": gitea_linked,
            "github_linked": github_linked,
            "has_active_workspace": has_active_workspace}


def _extract_ai_keywords(owner: str, repo: str, labels: list[dict], body: str = ""):
    """Extract and persist AI keywords from issue labels and body."""
    if not owner or not repo:
        return
    candidates = set()
    for lbl in labels:
        name = lbl.get("name", "").strip().lower()
        if name and len(name) > 1:
            candidates.add(name)
    # Simple extraction from body: single words > 3 chars
    import re
    for word in re.findall(r'\b[a-zA-Z]{4,}\b', body.lower()):
        if word not in ("this", "that", "with", "from", "have", "when", "will"):
            candidates.add(word)

    with get_conn() as conn:
        for kw in candidates:
            kw = kw[:30]
            existing = conn.execute(
                "SELECT id, usage_count FROM ai_keywords WHERE project_owner=? AND project_name=? AND keyword=?",
                (owner, repo, kw),
            ).fetchone()
            if existing:
                conn.execute("UPDATE ai_keywords SET usage_count=? WHERE id=?",
                             (existing["usage_count"] + 1, existing["id"]))
            else:
                color_idx = len(candidates) % len(AI_KEYWORD_COLORS)
                conn.execute(
                    "INSERT INTO ai_keywords (project_owner, project_name, keyword, color) VALUES (?,?,?,?)",
                    (owner, repo, kw, AI_KEYWORD_COLORS[color_idx]),
                )
        conn.commit()


def _get_project_properties(owner: str, repo: str) -> list[dict]:
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM project_properties WHERE project_owner=? AND project_name=? ORDER BY position",
            (owner, repo),
        ).fetchall()
    return [dict(r) for r in rows]


def _get_dynamic_groups(owner: str, repo: str) -> list[str]:
    """Return groups from Gitea labels/milestones or fallback to defaults."""
    try:
        labels = json.loads(
            json.dumps([lbl["name"] for lbl in asyncio_get_labels(owner, repo)[:5]])
        ) if False else []
    except Exception:
        labels = []
    return labels if labels else ["Design", "Engineering", "No Team"]


async def asyncio_get_labels(owner: str, repo: str):
    return await gitea.get_labels(owner, repo)


def _apply_filters(cards: list[dict], status_filter: str, filters: str) -> list[dict]:
    if status_filter:
        allowed = set(status_filter.split(","))
        cards = [c for c in cards if c["status"] in allowed]
    if filters:
        for f in filters.split(","):
            if ":" in f:
                prop, val = f.split(":", 1)
                val_lower = val.lower()
                if prop == "assignee":
                    cards = [c for c in cards if c.get("assignee", "").lower() == val_lower]
                elif prop == "tag":
                    cards = [c for c in cards if (c.get("tag") or "").lower() == val_lower]
                elif prop == "keyword":
                    cards = [c for c in cards if any(val_lower in kw.get("name", "").lower() for kw in c.get("keywords", []))]
    return cards


def _apply_sorts(cards: list[dict], sorts: str) -> list[dict]:
    if not sorts:
        return cards
    order = {"todo": 0, "progress": 1, "done": 2}
    for spec in reversed(sorts.split(",")):
        if ":" not in spec:
            continue
        field, direction = spec.split(":", 1)
        rev = direction == "desc"
        if field == "name":
            cards.sort(key=lambda c: c["title"].lower(), reverse=rev)
        elif field == "status":
            cards.sort(key=lambda c: order.get(c["status"], 0), reverse=rev)
        elif field == "assignee":
            cards.sort(key=lambda c: c.get("assignee", "").lower(), reverse=rev)
        elif field == "deadline":
            cards.sort(key=lambda c: c.get("due_date", ""), reverse=rev)
    return cards


# ═══════════ Library page ═══════════

@router.get("/library", response_class=HTMLResponse)
async def library_page(request: Request, owner: str = Query(default=""), repo: str = Query(default="")):
    """Library page avec onglets Recents, Favorites, Shared, Private — scopé au workspace."""
    from jinja2 import Environment, FileSystemLoader
    env = Environment(loader=FileSystemLoader("app/templates"))
    sidebar = _sidebar_data(request, owner, repo)
    # Load all pages for the workspace from DB
    ws_key = f"{owner}/{repo}" if owner and repo else ""
    with get_conn() as conn:
        if ws_key:
            rows = conn.execute(
                "SELECT id, title, workspace, updated_at FROM pages "
                "WHERE workspace=? ORDER BY updated_at DESC",
                (ws_key,),
            ).fetchall()
        else:
            rows = conn.execute(
                "SELECT id, title, workspace, updated_at FROM pages "
                "ORDER BY updated_at DESC",
            ).fetchall()
    all_pages = []
    for r in rows:
        page = dict(r)
        all_pages.append({
            "id": f"page/{page['id']}",
            "name": page["title"] or "Untitled",
            "icon": "📄",
            "url": f"/pages/{page['id']}",
            "created_by": "You",
            "source": page.get("workspace") or "🔒 Private",
            "last_edited": page.get("updated_at", "now"),
            "last_visited": page.get("updated_at", "now"),
        })
    sidebar["recent_pages"] = all_pages
    sidebar["favorite_pages"] = []
    sidebar["private_pages"] = [p for p in all_pages if p.get("source") == "🔒 Private"]
    sidebar["shared_pages"] = []
    template = env.get_template("library.html")
    return template.render(**sidebar)

# ═══════════ Favorites API ═══════════

@router.get("/api/favorites")
async def list_favorites(request: Request):
    """List favorited page IDs for the current user."""
    user = SessionManager.decode_session(request.cookies.get("flowdeck_session", ""))
    uid = user["id"] if user and user.get("id") else 1
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT page_id FROM favorites WHERE user_id=? ORDER BY position", (uid,)
        ).fetchall()
    return {"favorites": [r["page_id"] for r in rows]}


@router.post("/api/favorites/{page_id:int}")
async def add_favorite(request: Request, page_id: int):
    """Add a page to favorites."""
    user = SessionManager.decode_session(request.cookies.get("flowdeck_session", ""))
    uid = user["id"] if user and user.get("id") else 1
    with get_conn() as conn:
        existing = conn.execute(
            "SELECT id FROM favorites WHERE user_id=? AND page_id=?", (uid, page_id)
        ).fetchone()
        if not existing:
            pos = conn.execute(
                "SELECT COALESCE(MAX(position), -1) + 1 FROM favorites WHERE user_id=?", (uid,)
            ).fetchone()[0]
            conn.execute(
                "INSERT INTO favorites (user_id, page_id, position) VALUES (?,?,?)",
                (uid, page_id, pos),
            )
            conn.commit()
    return {"status": "added", "page_id": page_id}


@router.delete("/api/favorites/{page_id:int}")
async def remove_favorite(request: Request, page_id: int):
    """Remove a page from favorites."""
    user = SessionManager.decode_session(request.cookies.get("flowdeck_session", ""))
    uid = user["id"] if user and user.get("id") else 1
    with get_conn() as conn:
        conn.execute("DELETE FROM favorites WHERE user_id=? AND page_id=?", (uid, page_id))
        conn.commit()
    return {"status": "removed", "page_id": page_id}

# ═══════════ Share API ═══════════

@router.post("/api/share/{page_id:int}")
async def update_share(request: Request, page_id: int):
    """Save share settings for a page."""
    import json
    body = await request.json()
    mode = body.get("mode", "private")
    published = body.get("published", False)
    with get_conn() as conn:
        conn.execute(
            "UPDATE pages SET share_mode=?, published=? WHERE id=?",
            (mode, 1 if published else 0, page_id),
        )
        conn.commit()
    return {"status": "ok", "share_mode": mode, "published": published}


# ═══════════ Trash (must be before /{owner}/{repo} catch-all) ═══════════

@router.get("/api/trash")
async def list_trash(request: Request):
    with get_conn() as conn:
        rows = conn.execute("SELECT id, title, workspace, parent_id, deleted_at FROM pages WHERE deleted_at IS NOT NULL ORDER BY deleted_at DESC").fetchall()
    return [{"id": r["id"], "name": r["title"] or "Untitled", "icon": "📄", "path": r["workspace"] or "Private", "deleted_at": r["deleted_at"]} for r in rows]


@router.post("/api/trash/{page_id}/restore")
async def restore_page(request: Request, page_id: int):
    with get_conn() as conn:
        conn.execute("UPDATE pages SET deleted_at=NULL WHERE id=?", (page_id,))
        conn.commit()
    return {"status": "ok", "restored": page_id}


@router.delete("/api/trash/{page_id}")
async def permanent_delete(request: Request, page_id: int):
    with get_conn() as conn:
        conn.execute("UPDATE pages SET parent_id=NULL WHERE parent_id=?", (page_id,))
        conn.execute("DELETE FROM pages WHERE id=? AND deleted_at IS NOT NULL", (page_id,))
        conn.commit()
    return {"status": "ok", "deleted": page_id}


@router.get("/trash", response_class=HTMLResponse)
async def trash_page(request: Request):
    from jinja2 import Environment, FileSystemLoader
    env = Environment(loader=FileSystemLoader("app/templates"))
    template = env.get_template("trash.html")
    return template.render(**_sidebar_data(request))


# ═══════════ Board page ═══════════

@router.get("/{owner}/{repo}", response_class=HTMLResponse)
async def board(request: Request, owner: str, repo: str, view: str = Query(default="")):
    from jinja2 import Environment, FileSystemLoader
    env = Environment(loader=FileSystemLoader("app/templates"))
    sidebar = _sidebar_data(request, owner, repo)
    template = env.get_template("board.html")
    return template.render(request=request, owner=owner, repo=repo, groups=[],
                          initial_view=view, **sidebar)


# ═══════════ View fragments ═══════════

@router.get("/{owner}/{repo}/view/{view}", response_class=HTMLResponse)
async def board_view(
    request: Request, owner: str, repo: str, view: str,
    status: str = Query(default=""),
    filter: str = Query(default=""),
    sort: str = Query(default=""),
):
    try:
        issues = await gitea.get_issues(owner, repo, state="all")
        issues_only = [i for i in issues if not i.get("pull_request")]
        cards = [_map_issue_to_card(i, owner, repo) for i in issues_only]
        cards = _apply_filters(cards, status, filter)
        cards = _apply_sorts(cards, sort)
    except Exception as e:
        logger.error("Board view error: %s", e)
        cards = []

    from jinja2 import Environment, FileSystemLoader
    env = Environment(loader=FileSystemLoader("app/templates"))

    # Dynamic groups from Gitea labels (fallback to hardcoded)
    group_names = ["Design", "Engineering", "No Team"]
    groups = []
    for gname in group_names:
        gid = gname.lower().replace(" ", "-")
        gcards = cards
        groups.append({
            "id": gid, "name": gname,
            "counts": {
                "todo": len([c for c in gcards if c["status"] == "todo"]),
                "progress": len([c for c in gcards if c["status"] == "progress"]),
                "done": len([c for c in gcards if c["status"] == "done"]),
            },
            "cards": gcards,
        })

    ctx = {"owner": owner, "repo": repo, "groups": groups, "cards": cards}

    template_map = {
        "table": "table_view.html",
        "status": "status_overview.html",
        "teamload": "team_load.html",
        "detailed": "detailed_board.html",
    }

    if view == "table":
        grouped = {g["name"]: g["cards"] for g in groups}
        ctx["grouped_cards"] = grouped
    elif view == "status":
        counts = {"todo": 0, "progress": 0, "done": 0}
        for c in cards:
            if c["status"] in counts:
                counts[c["status"]] += 1
        ctx.update(status_data=counts, status_colors=STATUS_COLORS, status_labels=STATUS_LABELS)
    elif view == "teamload":
        members = {}
        for c in cards:
            name = c.get("assignee") or "Unassigned"
            if name not in members:
                members[name] = {"name": name, "initial": name[0].upper(),
                                 "todo": 0, "progress": 0, "complete": 0, "total": 0}
            sk = c["status"] if c["status"] in ("todo", "progress") else "complete"
            members[name][sk] += 1
            members[name]["total"] += 1
        ctx["team_data"] = list(members.values())
    elif view == "detailed":
        pass
    else:
        template_map["kanban"] = "board_fragment.html"

    template_name = template_map.get(view, "board_fragment.html")
    template = env.get_template(template_name)
    return template.render(**ctx)


# ═══════════ v0.9.0: Custom Properties API ═══════════

@router.get("/api/properties/{owner}/{repo}")
async def get_properties(owner: str, repo: str):
    return {"properties": _get_project_properties(owner, repo)}


@router.post("/api/properties/{owner}/{repo}")
async def create_property(owner: str, repo: str, name: str = Query(...),
                          prop_type: str = Query(default="select"),
                          options: str = Query(default="")):
    opts = json.dumps([o.strip() for o in options.split(",") if o.strip()])
    with get_conn() as conn:
        try:
            conn.execute(
                "INSERT INTO project_properties (project_owner, project_name, name, prop_type, options_json) VALUES (?,?,?,?,?)",
                (owner, repo, name, prop_type, opts),
            )
            conn.commit()
        except Exception as e:
            raise HTTPException(409, f"Property already exists: {e}")
    return {"status": "ok", "name": name, "type": prop_type}


@router.delete("/api/properties/{owner}/{repo}")
async def delete_property(owner: str, repo: str, name: str = Query(...)):
    with get_conn() as conn:
        conn.execute(
            "DELETE FROM project_properties WHERE project_owner=? AND project_name=? AND name=?",
            (owner, repo, name),
        )
        conn.commit()
    return {"status": "ok"}


@router.post("/api/properties/{owner}/{repo}/values")
async def set_property_value(owner: str, repo: str, issue_id: int = Query(...),
                              name: str = Query(...), value: str = Query(default="")):
    with get_conn() as conn:
        prop = conn.execute(
            "SELECT id FROM project_properties WHERE project_owner=? AND project_name=? AND name=?",
            (owner, repo, name),
        ).fetchone()
        if not prop:
            raise HTTPException(404, f"Property '{name}' not found")
        conn.execute(
            "INSERT OR REPLACE INTO property_values (property_id, gitea_issue_id, value) VALUES (?,?,?)",
            (prop["id"], issue_id, value),
        )
        conn.commit()
    return {"status": "ok"}


# ═══════════ v0.9.0: AI Keywords API ═══════════

@router.get("/api/ai-keywords/{owner}/{repo}")
async def get_ai_keywords(owner: str, repo: str):
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT keyword, color, usage_count FROM ai_keywords WHERE project_owner=? AND project_name=? ORDER BY usage_count DESC LIMIT 30",
            (owner, repo),
        ).fetchall()
    return {"keywords": [dict(r) for r in rows]}


@router.post("/api/ai-keywords/{owner}/{repo}/extract")
async def extract_ai_keywords(owner: str, repo: str):
    """Re-extract keywords from all issues in the repo."""
    try:
        issues = await gitea.get_issues(owner, repo, state="all")
        for issue in issues:
            if not issue.get("pull_request"):
                _extract_ai_keywords(owner, repo, issue.get("labels", []), issue.get("body", ""))
    except Exception as e:
        raise HTTPException(500, str(e))
    return {"status": "ok", "issues_scanned": len(issues)}


# ═══════════ Pages Markdown ═══════════

@router.post("/api/pages")
async def create_page(request: Request, title: str = Query(default=""),
                       section: str = Query(default="Private"),
                       project: str = Query(default=""),
                       parent_id: int = Query(default=0)):
    """Create a new Markdown page, optionally as a sub-page."""
    user = SessionManager.decode_session(request.cookies.get("flowdeck_session", ""))
    ws_key = project if project else (user.get("login", "Bruno") if user else "Bruno")
    page_title = title.strip() if title else "Untitled"
    try:
        with get_conn() as conn:
            # Compute next sort_order for this parent
            next_order = 0
            parent_val = parent_id if parent_id > 0 else None
            row = conn.execute(
                "SELECT COALESCE(MAX(sort_order), -1) + 1 FROM pages WHERE workspace=? AND parent_id IS ?",
                (ws_key, parent_val),
            ).fetchone()
            if row:
                next_order = row[0]
            cur = conn.execute(
                "INSERT INTO pages (workspace, title, parent_section, parent_id, sort_order) VALUES (?,?,?,?,?)",
                (ws_key, page_title, section, parent_val, next_order),
            )
            conn.commit()
            page_id = cur.lastrowid
        return {"status": "ok", "id": page_id, "title": page_title, "workspace": ws_key, "parent_id": parent_id}
    except Exception as e:
        logger.error("create_page failed: %s", e)
        from fastapi.responses import JSONResponse
        return JSONResponse({"error": "Failed to create page", "detail": str(e)}, status_code=500)


@router.get("/api/pages/{page_id}")
async def get_page(page_id: int):
    """Get a Markdown page."""
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM pages WHERE id=?", (page_id,)).fetchone()
        if not row:
            raise HTTPException(404, "Page not found")
    return dict(row)


@router.put("/api/pages/{page_id}")
async def update_page(request: Request, page_id: int, title: str = Query(default=""),
                       content: str = Query(default=""),
                       content_format: str = Query(default="")):
    """Update a page's title and/or content. Accepts JSON body for blocks."""
    with get_conn() as conn:
        if title:
            conn.execute("UPDATE pages SET title=?, updated_at=CURRENT_TIMESTAMP WHERE id=?", (title, page_id))
        if content:
            conn.execute("UPDATE pages SET content=?, updated_at=CURRENT_TIMESTAMP WHERE id=?", (content, page_id))
        if content_format:
            conn.execute("UPDATE pages SET content_format=?, updated_at=CURRENT_TIMESTAMP WHERE id=?", (content_format, page_id))
        conn.commit()
    return {"status": "ok"}


@router.post("/api/pages/{page_id}/blocks")
async def save_page_blocks(request: Request, page_id: int):
    """Save blocks JSON content (Notion-style block editor)."""
    try:
        body = await request.json()
    except Exception:
        raise HTTPException(400, "Invalid JSON body")
    blocks_json = json.dumps(body.get("blocks", []))
    title = body.get("title", "")
    with get_conn() as conn:
        if title:
            conn.execute("UPDATE pages SET title=?, updated_at=CURRENT_TIMESTAMP WHERE id=?", (title, page_id))
        conn.execute(
            "UPDATE pages SET content=?, content_format='blocks', updated_at=CURRENT_TIMESTAMP WHERE id=?",
            (blocks_json, page_id),
        )
        conn.commit()
    return {"status": "ok", "id": page_id}


@router.put("/api/pages/{page_id}/move")
async def move_page(request: Request, page_id: int):
    """Move a page to another workspace or reorder within tree.
    
    Body (JSON): { workspace_id?: int, parent_id?: int, new_order?: int }
    - workspace_id: move page to a different workspace
    - parent_id: change parent (0 = root level)
    - new_order: position among siblings (0 = append)
    """
    try:
        body = await request.json()
    except Exception:
        body = {}
    new_ws_id = body.get("workspace_id")
    new_parent_id = body.get("parent_id", 0)
    new_order = body.get("new_order", 0)
    
    with get_conn() as conn:
        row = conn.execute("SELECT id, workspace, workspace_id FROM pages WHERE id=?", (page_id,)).fetchone()
        if not row:
            raise HTTPException(404, "Page not found")
        
        if new_ws_id:
            # Move to a different workspace: get the workspace name
            ws_row = conn.execute("SELECT name FROM workspaces WHERE id=?", (new_ws_id,)).fetchone()
            if not ws_row:
                return JSONResponse({"status": "error", "detail": "Workspace not found"}, status_code=404)
            conn.execute(
                "UPDATE pages SET workspace_id=?, workspace=?, parent_id=NULL, updated_at=CURRENT_TIMESTAMP WHERE id=?",
                (new_ws_id, ws_row["name"], page_id),
            )
        else:
            # Reorder within same workspace
            conn.execute(
                "UPDATE pages SET parent_id=?, updated_at=CURRENT_TIMESTAMP WHERE id=?",
                (new_parent_id if new_parent_id > 0 else None, page_id),
            )
            conn.execute(
                "UPDATE pages SET sort_order=?, updated_at=CURRENT_TIMESTAMP WHERE id=?",
                (new_order, page_id),
            )
        
        conn.commit()
    return {"status": "ok", "id": page_id}


@router.delete("/api/pages/{page_id}")
async def delete_page(request: Request, page_id: int):
    """Move a page to trash (soft delete)."""
    with get_conn() as conn:
        row = conn.execute("SELECT id, title FROM pages WHERE id=? AND deleted_at IS NULL", (page_id,)).fetchone()
        if not row:
            raise HTTPException(404, "Page not found")
        import datetime
        conn.execute("UPDATE pages SET deleted_at=? WHERE id=?", (datetime.datetime.utcnow().isoformat(), page_id,))
        conn.commit()
    return {"status": "ok", "deleted": page_id, "title": row["title"]}


@router.get("/pages/{page_id}", response_class=HTMLResponse)
async def view_page(request: Request, page_id: int):
    """Render a page as HTML, or a file viewer for uploaded files."""
    from jinja2 import Environment, FileSystemLoader
    env = Environment(loader=FileSystemLoader("app/templates"))
    with get_conn() as conn:
        row = conn.execute("SELECT * FROM pages WHERE id=?", (page_id,)).fetchone()
        if not row:
            return HTMLResponse("<h2>Page not found</h2>", status_code=404)
    page = dict(row)

    ws = page.get("workspace", "")
    parts = ws.split("/") if "/" in ws else ["", ""]
    owner, repo = parts[0], parts[1] if len(parts) > 1 else ""
    sidebar = _sidebar_data(request, owner, repo)
    # Check if page is favorited
    user = SessionManager.decode_session(request.cookies.get("flowdeck_session", ""))
    uid = user["id"] if user and user.get("id") else 1
    with get_conn() as conn:
        fav = conn.execute(
            "SELECT id FROM favorites WHERE user_id=? AND page_id=?", (uid, page_id)
        ).fetchone()

    # Build page_data, including file metadata for uploaded files
    page_data = {"id": page_id, "title": page.get("title"), "content_format": page.get("content_format", "blocks"), "content": page.get("content", ""), "favorited": fav is not None, "share_mode": page.get("share_mode", "private"), "published": bool(page.get("published", 0))}

    # For file pages, extract file metadata and add to page_data
    if page.get("content_format") == "file":
        import json as _json
        try:
            meta = _json.loads(page.get("content", "{}"))
        except _json.JSONDecodeError:
            meta = {}
        file_path = meta.get("file_path", "").replace("\\", "/")
        mime_type = meta.get("mime_type", "application/octet-stream")
        file_size = meta.get("size", 0)
        # Build workspace_id from file_path
        fp_parts = file_path.split("/")
        ws_id = ""
        for p in fp_parts:
            if p.startswith("workspace_"):
                ws_id = p.replace("workspace_", "")
                break
        filename = fp_parts[-1] if fp_parts else page.get("title", "File")
        file_url = f"/api/files/{ws_id}/{filename}" if ws_id else ""
        page_data["file_url"] = file_url
        page_data["file_mime"] = mime_type
        page_data["file_size"] = file_size
        page_data["file_name"] = filename

    from app.routers.dashboard import _nav_breadcrumb
    with get_conn() as conn:
        nav_crumbs = _nav_breadcrumb(conn, page_id)
    ctx = {**sidebar, "page": page, "page_favorited": fav is not None,
           "page_share_mode": page.get("share_mode", "private"),
           "page_published": bool(page.get("published", 0)),
           "page_data": page_data,
           "breadcrumb_items": nav_crumbs,
           "nav_workspace_id": page.get("workspace_id") or 0,
           "nav_page_id": page_id}
    template = env.get_template("page_editor.html")
    response = template.render(**ctx)
    return HTMLResponse(content=response, headers={"Cache-Control": "no-store, max-age=0"})


@router.post("/api/sync/{owner}/{repo}")
async def sync_project(owner: str, repo: str):
    """Full bidirectional sync: fetch Gitea issues → update local DB."""
    try:
        issues = await gitea.get_issues(owner, repo, state="all")
        issues_only = [i for i in issues if not i.get("pull_request")]
        with get_conn() as conn:
            board = conn.execute(
                "SELECT id, columns_json FROM boards WHERE project_owner=? AND project_name=?",
                (owner, repo),
            ).fetchone()
            if board:
                board_id = board["id"]
                columns = json.loads(board["columns_json"])
                for issue in issues_only:
                    col = _issue_column(issue, columns, board_id)
                    conn.execute(
                        "INSERT OR REPLACE INTO cards (board_id, gitea_issue_id, column_name) VALUES (?,?,?)",
                        (board_id, issue["number"], col),
                    )
                    # Extract AI keywords from each issue
                    _extract_ai_keywords(owner, repo, issue.get("labels", []), issue.get("body", ""))
                conn.commit()
        return {"status": "ok", "issues_synced": len(issues_only)}
    except Exception as e:
        raise HTTPException(500, str(e))