"""FlowDeck — v5.14.0 Synced blocks: a block created once, displayed
and edited across multiple pages.

The ``synced_blocks`` table stores the source-of-truth content. Each
page that uses a synced block stores a reference in ``page_synced_blocks``.
Editing the source propagates to all referencing pages (via realtime
rooms or on next load).
"""
from __future__ import annotations

import json
import logging
import uuid

from app.db import get_conn

logger = logging.getLogger(__name__)


def block_id() -> str:
    return "sb" + uuid.uuid4().hex[:10]


# ── SyncedBlock CRUD ──────────────────────────────────────────────

def create_synced_block(workspace: str, title: str, content: list[dict],
                        created_by: int | None = None) -> int:
    """Create a new synced block and return its id."""
    content_json = json.dumps(content, ensure_ascii=False) if content else "[]"
    with get_conn() as conn:
        cur = conn.execute(
            """INSERT INTO synced_blocks (title, content, created_by, workspace)
               VALUES (?, ?, ?, ?)""",
            (title, content_json, created_by, workspace),
        )
        conn.commit()
        return cur.lastrowid


def get_synced_block(synced_id: int) -> dict | None:
    """Fetch a synced block by id."""
    with get_conn() as conn:
        row = conn.execute(
            "SELECT * FROM synced_blocks WHERE id=?", (synced_id,)
        ).fetchone()
    if not row:
        return None
    return dict(row)


def update_synced_block(synced_id: int, title: str,
                        content: list[dict]) -> None:
    """Update a synced block's title and content."""
    content_json = json.dumps(content, ensure_ascii=False)
    with get_conn() as conn:
        conn.execute(
            """UPDATE synced_blocks SET title=?, content=?,
               updated_at=CURRENT_TIMESTAMP WHERE id=?""",
            (title, content_json, synced_id),
        )
        conn.commit()


def delete_synced_block(synced_id: int) -> list[int]:
    """Delete a synced block (cascades via FK).

    v6.5.0: returns the page ids that referenced it so the caller can
    refresh/broadcast them (the FK cascade removes ``page_synced_blocks``).
    """
    page_ids = page_ids_for_synced(synced_id)
    with get_conn() as conn:
        conn.execute("DELETE FROM synced_blocks WHERE id=?", (synced_id,))
        conn.commit()
    return page_ids


def list_synced_blocks(workspace: str) -> list[dict]:
    """List all synced blocks in a workspace."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT * FROM synced_blocks WHERE workspace=? ORDER BY updated_at DESC",
            (workspace,),
        ).fetchall()
    return [dict(r) for r in rows]


# ── Page references ───────────────────────────────────────────────

def add_page_synced(page_id: int, synced_block_id: int,
                    block_index: int = 0) -> None:
    """Record that a page references a synced block at a given index."""
    with get_conn() as conn:
        conn.execute(
            """INSERT OR IGNORE INTO page_synced_blocks
               (page_id, synced_block_id, block_index)
               VALUES (?, ?, ?)""",
            (page_id, synced_block_id, block_index),
        )
        conn.commit()


def remove_page_synced(page_id: int, synced_block_id: int) -> None:
    """Remove a page's reference to a synced block."""
    with get_conn() as conn:
        conn.execute(
            "DELETE FROM page_synced_blocks WHERE page_id=? AND synced_block_id=?",
            (page_id, synced_block_id),
        )
        conn.commit()


def get_page_synced(page_id: int) -> list[dict]:
    """Get all synced block references for a page."""
    with get_conn() as conn:
        rows = conn.execute(
            """SELECT psb.*, sb.title, sb.content AS synced_content
               FROM page_synced_blocks psb
               JOIN synced_blocks sb ON sb.id=psb.synced_block_id
               WHERE psb.page_id=? ORDER BY psb.block_index""",
            (page_id,),
        ).fetchall()
    return [dict(r) for r in rows]


# ── Block resolution ──────────────────────────────────────────────

def resolve_synced_block(blocks: list[dict]) -> list[dict]:
    """Replace synced block references with actual content for rendering.

    A synced block in the block list looks like:
        {"type": "synced", "synced_id": 42, "content": "..."}
    This resolves it to the current content from the synced_blocks table.
    """
    for b in blocks:
        if b.get("type") == "synced" and b.get("synced_id"):
            sb = get_synced_block(b["synced_id"])
            if sb:
                try:
                    resolved = json.loads(sb["content"])
                    if isinstance(resolved, list):
                        b["_synced_content"] = resolved
                        b["_synced_title"] = sb.get("title", "")
                        b["_synced_id"] = sb["id"]
                        b["_synced_updated"] = sb.get("updated_at", "")
                        b.pop("_synced_deleted", None)
                except (json.JSONDecodeError, TypeError):
                    pass
            elif not b.get("_synced_deleted"):
                # v6.5.0: source deleted → instances render a clear state
                # instead of a stale cache / eternal "Loading…".
                b["_synced_deleted"] = True
                b.pop("_synced_content", None)
                b.pop("_synced_title", None)
        if isinstance(b.get("children"), list):
            resolve_synced_block(b["children"])
    return blocks


def page_ids_for_synced(synced_id: int) -> list[int]:
    """Pages currently referencing a synced block (before any cascade)."""
    with get_conn() as conn:
        rows = conn.execute(
            "SELECT page_id FROM page_synced_blocks WHERE synced_block_id=?",
            (synced_id,),
        ).fetchall()
    return [r["page_id"] for r in rows]


def resolve_content_json(content: str, content_format: str | None) -> str:
    """Resolve synced-block references inside a page's stored blocks JSON.

    Server-side freshness guarantee (v6.5.0): every read path passes through
    this so a stale cached ``_synced_content`` is replaced by the current
    source-of-truth content. Non-blocks content is returned unchanged.
    """
    if (content_format or "blocks") != "blocks" or not content:
        return content
    try:
        blocks = json.loads(content)
    except (json.JSONDecodeError, TypeError):
        return content
    if not isinstance(blocks, list):
        return content
    return json.dumps(resolve_synced_block(blocks), ensure_ascii=False)


def sync_synced_blocks_in_page(page_id: int) -> None:
    """Re-resolve all synced blocks in a page's content.

    Called after a synced block is updated — refreshes the referencing
    pages' stored content so they reflect the latest source (v6.5.0: this
    used to be dead code; it is now invoked by the update/delete routes,
    recursively covering blocks nested in columns/toggles).
    """
    refs = get_page_synced(page_id)
    if not refs:
        return
    with get_conn() as conn:
        row = conn.execute(
            "SELECT content, content_format FROM pages WHERE id=?",
            (page_id,),
        ).fetchone()
        if not row or row["content_format"] != "blocks":
            return
        try:
            blocks = json.loads(row["content"])
        except (json.JSONDecodeError, TypeError):
            return
        if not isinstance(blocks, list):
            return

    sources: dict[int, dict] = {r["synced_block_id"]: get_synced_block(r["synced_block_id"])
                                for r in refs}
    changed = False

    def _walk(bl: list[dict]) -> None:
        nonlocal changed
        for b in bl:
            if b.get("type") == "synced" and b.get("synced_id"):
                sid = b["synced_id"]
                if sid not in sources:
                    sources[sid] = get_synced_block(sid)
                sb = sources.get(sid)
                if sb:
                    try:
                        resolved = json.loads(sb["content"])
                    except (json.JSONDecodeError, TypeError):
                        continue
                    content_str = (json.dumps(resolved, ensure_ascii=False)
                                   if isinstance(resolved, list)
                                   else json.dumps(resolved))
                    title = sb.get("title", "")
                    if (b.get("content") != content_str
                            or b.get("_synced_content") != resolved
                            or b.get("_synced_title") != title
                            or b.get("_synced_deleted")):
                        b["content"] = content_str
                        b["_synced_content"] = resolved
                        b["_synced_title"] = title
                        b.pop("_synced_deleted", None)
                        changed = True
                elif not b.get("_synced_deleted"):
                    b["_synced_deleted"] = True
                    b.pop("_synced_content", None)
                    b.pop("_synced_title", None)
                    changed = True
            if isinstance(b.get("children"), list):
                _walk(b["children"])

    _walk(blocks)

    if changed:
        with get_conn() as conn:
            conn.execute(
                "UPDATE pages SET content=?, updated_at=CURRENT_TIMESTAMP WHERE id=?",
                (json.dumps(blocks, ensure_ascii=False), page_id),
            )
            conn.commit()


def mark_synced_block_deleted(synced_id: int, page_ids: list[int]) -> None:
    """Flag a deleted synced block's instances as ``_synced_deleted``.

    v6.5.0 — called after ``delete_synced_block()``: the FK cascade already
    removed ``page_synced_blocks`` refs, so ``sync_synced_blocks_in_page``
    cannot be used here; this rewrites the stored blocks directly so every
    surface (editor, offline sync, export) sees the deleted state.
    """
    for pid in page_ids:
        with get_conn() as conn:
            row = conn.execute(
                "SELECT content, content_format FROM pages WHERE id=?",
                (pid,),
            ).fetchone()
            if not row or row["content_format"] != "blocks":
                continue
            try:
                blocks = json.loads(row["content"])
            except (json.JSONDecodeError, TypeError):
                continue
            if not isinstance(blocks, list):
                continue
            changed = False

            def _walk(bl: list[dict]) -> None:
                nonlocal changed
                for b in bl:
                    if (b.get("type") == "synced"
                            and b.get("synced_id") == synced_id
                            and not b.get("_synced_deleted")):
                        b["_synced_deleted"] = True
                        b.pop("_synced_content", None)
                        b.pop("_synced_title", None)
                        changed = True
                    if isinstance(b.get("children"), list):
                        _walk(b["children"])

            _walk(blocks)
            if changed:
                conn.execute(
                    "UPDATE pages SET content=?, updated_at=CURRENT_TIMESTAMP WHERE id=?",
                    (json.dumps(blocks, ensure_ascii=False), pid),
                )
                conn.commit()


# ── Unsync: convert synced block to independent copy ──────────────