"""
Roadmap & Flow API — data from Postgres tables.
"""
import logging
import uuid
from datetime import datetime, timedelta, timezone
from typing import Optional

from fastapi import APIRouter, HTTPException
from psycopg_pool import AsyncConnectionPool
from pydantic import BaseModel

from config import CHECKPOINT_POSTGRES_URL

VN_TZ = timezone(timedelta(hours=7))
_pool: AsyncConnectionPool | None = None


async def _get_pool() -> AsyncConnectionPool:
    global _pool
    if _pool is None:
        _pool = AsyncConnectionPool(CHECKPOINT_POSTGRES_URL, min_size=1, max_size=5, open=False)
        await _pool.open()
    return _pool


def _now() -> datetime:
    return datetime.now(VN_TZ)

logger = logging.getLogger(__name__)
router = APIRouter(prefix="/api/dashboard", tags=["Roadmap & Flow"])


# ═══════════════════════════════════════════════════
#  ROADMAP API
# ═══════════════════════════════════════════════════

class RoadmapCreate(BaseModel):
    version: str
    title: str
    description: str = ""
    status: str = "planned"
    badge: str = "planned"
    target_date: str = ""
    sort_order: int = 0


class RoadmapUpdate(BaseModel):
    version: Optional[str] = None
    title: Optional[str] = None
    description: Optional[str] = None
    status: Optional[str] = None
    badge: Optional[str] = None
    target_date: Optional[str] = None
    sort_order: Optional[int] = None


async def _query(q: str, params: tuple = ()) -> list[dict]:
    pool = await _get_pool()
    async with pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(q, params)
            cols = [d[0] for d in cur.description] if cur.description else []
            rows = await cur.fetchall()
            return [dict(zip(cols, row)) for row in rows]


async def _execute(q: str, params: tuple = ()):
    pool = await _get_pool()
    async with pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(q, params)
        await conn.commit()


def _serialize(row: dict) -> dict:
    r = {**row}
    for k in ("created_at", "updated_at"):
        if k in r and r[k]:
            r[k] = r[k].isoformat()
    return r


@router.get("/roadmap")
async def list_roadmap():
    """Get all roadmap items grouped by version."""
    rows = await _query(
        "SELECT * FROM roadmap_items ORDER BY version DESC, sort_order ASC"
    )
    items = [_serialize(r) for r in rows]
    # Group by version
    grouped = {}
    for item in items:
        v = item["version"]
        if v not in grouped:
            grouped[v] = {"version": v, "badge": item.get("badge", "planned"), "target_date": item.get("target_date", ""), "items": []}
        grouped[v]["items"].append(item)
    return {"status": "success", "versions": list(grouped.values()), "total": len(items)}


@router.post("/roadmap")
async def create_roadmap_item(item: RoadmapCreate):
    """Add a new roadmap item."""
    rid = str(uuid.uuid4())[:8]
    now = _now()
    await _execute(
        """INSERT INTO roadmap_items (id, version, title, description, status, badge, target_date, sort_order, created_at, updated_at)
           VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)""",
        (rid, item.version, item.title, item.description, item.status, item.badge, item.target_date, item.sort_order, now, now),
    )
    return {"status": "success", "id": rid}


@router.put("/roadmap/{item_id}")
async def update_roadmap_item(item_id: str, update: RoadmapUpdate):
    """Update a roadmap item."""
    sets, vals = [], []
    for field in ("version", "title", "description", "status", "badge", "target_date", "sort_order"):
        val = getattr(update, field, None)
        if val is not None:
            sets.append(f"{field} = %s")
            vals.append(val)
    if not sets:
        raise HTTPException(status_code=400, detail="Nothing to update")
    sets.append("updated_at = %s")
    vals.append(_now())
    vals.append(item_id)
    await _execute(f"UPDATE roadmap_items SET {', '.join(sets)} WHERE id = %s", tuple(vals))
    return {"status": "success"}


@router.delete("/roadmap/{item_id}")
async def delete_roadmap_item(item_id: str):
    """Delete a roadmap item."""
    await _execute("DELETE FROM roadmap_items WHERE id = %s", (item_id,))
    return {"status": "success"}


# ═══════════════════════════════════════════════════
#  FLOW API
# ═══════════════════════════════════════════════════

class FlowCreate(BaseModel):
    title: str
    description: str = ""
    category: str = "general"
    parent_id: Optional[str] = None
    sort_order: int = 0
    icon: str = ""


class FlowUpdate(BaseModel):
    title: Optional[str] = None
    description: Optional[str] = None
    category: Optional[str] = None
    parent_id: Optional[str] = None
    sort_order: Optional[int] = None
    icon: Optional[str] = None
    status: Optional[str] = None


@router.get("/flow")
async def list_flow():
    """Get all flow items ordered by sort_order."""
    rows = await _query("SELECT * FROM flow_items ORDER BY sort_order ASC")
    items = [_serialize(r) for r in rows]
    return {"status": "success", "items": items, "total": len(items)}


@router.post("/flow")
async def create_flow_item(item: FlowCreate):
    """Add a new flow item."""
    fid = str(uuid.uuid4())[:8]
    now = _now()
    await _execute(
        """INSERT INTO flow_items (id, title, description, category, parent_id, sort_order, icon, created_at, updated_at)
           VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)""",
        (fid, item.title, item.description, item.category, item.parent_id, item.sort_order, item.icon, now, now),
    )
    return {"status": "success", "id": fid}


@router.put("/flow/{item_id}")
async def update_flow_item(item_id: str, update: FlowUpdate):
    """Update a flow item."""
    sets, vals = [], []
    for field in ("title", "description", "category", "parent_id", "sort_order", "icon", "status"):
        val = getattr(update, field, None)
        if val is not None:
            sets.append(f"{field} = %s")
            vals.append(val)
    if not sets:
        raise HTTPException(status_code=400, detail="Nothing to update")
    sets.append("updated_at = %s")
    vals.append(_now())
    vals.append(item_id)
    await _execute(f"UPDATE flow_items SET {', '.join(sets)} WHERE id = %s", tuple(vals))
    return {"status": "success"}


@router.delete("/flow/{item_id}")
async def delete_flow_item(item_id: str):
    """Delete a flow item."""
    await _execute("DELETE FROM flow_items WHERE id = %s", (item_id,))
    return {"status": "success"}
