"""FastAPI service exposing learning day data and personalized plans from PostgreSQL."""
from __future__ import annotations

import asyncio
import json
import math
import os
import secrets
import time
import urllib.parse
import urllib.request
from contextlib import asynccontextmanager
from typing import Any, Dict, List, Optional, Sequence
import logging

if os.name == "nt":  # ensure psycopg async connections use selector loop on Windows
    asyncio.set_event_loop_policy(asyncio.WindowsSelectorEventLoopPolicy())

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s [%(levelname)s] %(name)s: %(message)s",
)

from fastapi import BackgroundTasks, Depends, FastAPI, HTTPException, Query, Request
from fastapi.middleware.cors import CORSMiddleware
from fastapi.responses import JSONResponse
from pydantic import BaseModel, Field
from psycopg_pool import AsyncConnectionPool

from plan_content_worker import run_plan_enrichment

DATABASE_URL = os.getenv("DATABASE_URL", "postgresql://postgres:postgres@localhost:5432/learning")
PLAN_CONTENT_BACKEND = os.getenv("PLAN_CONTENT_BACKEND", os.getenv("LLM_BACKEND", "ollama"))
PLAN_CONTENT_OPENAI_MODEL = os.getenv("PLAN_CONTENT_OPENAI_MODEL", os.getenv("LLM_MODEL", "gpt-4o-mini"))
PLAN_CONTENT_OLLAMA_MODEL = os.getenv("PLAN_CONTENT_OLLAMA_MODEL", os.getenv("OLLAMA_MODEL", "llama3.1"))
PLAN_CONTENT_AUTO = os.getenv("PLAN_CONTENT_AUTO", "true").lower() in {"1", "true", "yes"}
GOOGLE_CLIENT_ID = os.getenv("GOOGLE_CLIENT_ID")
_cors_origins = os.getenv("CORS_ALLOW_ORIGINS", "http://localhost:3000")
ALLOWED_ORIGINS = [origin.strip() for origin in _cors_origins.split(",") if origin.strip()]

LOGGER = logging.getLogger("api_server")


class TopicModel(BaseModel):
    name: str
    chapter: Optional[str] = None
    summary: Optional[str] = None
    difficulty: Optional[str] = None
    prerequisites: Optional[List[str]] = None


class LearningDayModel(BaseModel):
    day: int
    title: str
    goal: Optional[str]
    estimated_minutes: Optional[int]
    topics: List[TopicModel]


class DayContentModel(BaseModel):
    overview: Optional[str]
    key_points: Optional[List[str]]
    flashcards: Optional[List[Dict[str, str]]]
    practice: Optional[Dict[str, Any]]
    reflection_prompt: Optional[str]


class DayDetailModel(BaseModel):
    day: LearningDayModel
    content: Optional[DayContentModel]


class BookModel(BaseModel):
    id: str
    slug: str
    title: str
    description: Optional[str]
    cover_url: Optional[str]
    default_days: Optional[int]


class BookDetailModel(BookModel):
    topics_preview: List[Dict[str, Any]] = []


class PlanDayModel(BaseModel):
    day: int
    title: str
    goal: Optional[str]
    estimated_minutes: Optional[int]
    topics: List[TopicModel]


class PlanModel(BaseModel):
    id: str
    book: BookModel
    total_days: int
    minutes_per_day: Optional[int]
    focus: Optional[str]
    days: List[PlanDayModel]
    enriched_days: int = 0
    is_enrichment_complete: bool = False
    template_plan_id: Optional[str] = None


class PlanDayDetailModel(BaseModel):
    day: PlanDayModel
    content: Optional[DayContentModel]


class PlanSummaryModel(BaseModel):
    id: str
    book_title: str
    book_slug: str
    total_days: int
    minutes_per_day: Optional[int]
    focus: Optional[str]
    template_plan_id: str
    enriched_days: int = 0
    is_enrichment_complete: bool = False


class PlanRequest(BaseModel):
    email: str = Field(..., description="Identifier for temporary auth")
    display_name: Optional[str] = None
    book_slug: str
    total_days: int = Field(..., ge=1, le=180)
    minutes_per_day: Optional[int] = Field(None, ge=15, le=240)
    focus: Optional[str] = Field(None, description="Optional focus hint (theory, balanced, hands-on)")


class GoogleAuthRequest(BaseModel):
    id_token: str = Field(..., description="Google OAuth ID token from the client")


class GoogleAuthResponse(BaseModel):
    user_id: str
    email: str
    display_name: Optional[str]


pool: AsyncConnectionPool | None = None


@asynccontextmanager
async def lifespan(app: FastAPI):
    global pool
    if os.name == "nt":
        asyncio.set_event_loop_policy(asyncio.WindowsSelectorEventLoopPolicy())
    pool = AsyncConnectionPool(
        conninfo=DATABASE_URL,
        max_size=10,
        kwargs={"prepare_threshold": 0},
        open=False,
    )
    await pool.open()
    try:
        yield
    finally:
        await pool.close()


def get_pool() -> AsyncConnectionPool:
    if pool is None:
        raise RuntimeError("Connection pool is not initialized")
    return pool


app = FastAPI(title="Learning Days API", lifespan=lifespan)

app.add_middleware(
    CORSMiddleware,
    allow_origins=ALLOWED_ORIGINS or ["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)


@app.get("/health")
async def health() -> Dict[str, str]:
    return {"status": "ok"}


@app.post("/auth/google", response_model=GoogleAuthResponse)
async def authenticate_with_google(
    payload: GoogleAuthRequest,
    connection_pool: AsyncConnectionPool = Depends(get_pool),
) -> GoogleAuthResponse:
    token_data = await _verify_google_id_token(payload.id_token)
    email = (token_data.get("email") or "").strip().lower()
    display_name = token_data.get("name") or token_data.get("given_name")
    if not email:
        raise HTTPException(status_code=400, detail="Google token missing email claim")
    async with connection_pool.connection() as conn:
        async with conn.cursor() as cur:
            user_id = await _upsert_user(cur, email, display_name)
        await conn.commit()
    LOGGER.info("Verified Google auth for %s", email)
    return GoogleAuthResponse(user_id=user_id, email=email, display_name=display_name)


@app.get("/days", response_model=List[LearningDayModel])
async def list_days(
    limit: int = Query(20, ge=1, le=100),
    offset: int = Query(0, ge=0),
    connection_pool: AsyncConnectionPool = Depends(get_pool),
) -> List[LearningDayModel]:
    query = """
        SELECT day, title, goal, estimated_minutes, topics
        FROM learning_days
        ORDER BY day
        LIMIT %s OFFSET %s
    """
    async with connection_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(query, (limit, offset))
            rows = await cur.fetchall()

    return [
        LearningDayModel(
            day=row[0],
            title=row[1],
            goal=row[2],
            estimated_minutes=row[3],
            topics=row[4] or [],
        )
        for row in rows
    ]


@app.get("/days/{day_id}", response_model=DayDetailModel)
async def get_day(
    day_id: int,
    connection_pool: AsyncConnectionPool = Depends(get_pool),
) -> DayDetailModel:
    async with connection_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                """
                SELECT day, title, goal, estimated_minutes, topics
                FROM learning_days
                WHERE day = %s
                """,
                (day_id,),
            )
            day_row = await cur.fetchone()
            if day_row is None:
                raise HTTPException(status_code=404, detail="Day not found")

            await cur.execute(
                """
                SELECT overview, key_points, flashcards, practice, reflection_prompt
                FROM day_content
                WHERE day = %s
                """,
                (day_id,),
            )
            content_row = await cur.fetchone()

    day_model = LearningDayModel(
        day=day_row[0],
        title=day_row[1],
        goal=day_row[2],
        estimated_minutes=day_row[3],
        topics=day_row[4] or [],
    )

    content_model = (
        DayContentModel(
            overview=content_row[0],
            key_points=content_row[1],
            flashcards=content_row[2],
            practice=content_row[3],
            reflection_prompt=content_row[4],
        )
        if content_row
        else None
    )

    return DayDetailModel(day=day_model, content=content_model)


@app.get("/days/{day_id}/content", response_model=DayContentModel)
async def get_day_content(
    day_id: int,
    connection_pool: AsyncConnectionPool = Depends(get_pool),
) -> DayContentModel:
    async with connection_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(
                """
                SELECT overview, key_points, flashcards, practice, reflection_prompt
                FROM day_content
                WHERE day = %s
                """,
                (day_id,),
            )
            row = await cur.fetchone()
            if row is None:
                raise HTTPException(status_code=404, detail="Content not found")
    return DayContentModel(
        overview=row[0],
        key_points=row[1],
        flashcards=row[2],
        practice=row[3],
        reflection_prompt=row[4],
    )


@app.get("/books", response_model=List[BookModel])
async def list_books(
    limit: int = Query(20, ge=1, le=100),
    offset: int = Query(0, ge=0),
    connection_pool: AsyncConnectionPool = Depends(get_pool),
) -> List[BookModel]:
    query = """
        SELECT id, slug, title, description, cover_url, default_days
        FROM books
        ORDER BY created_at DESC
        LIMIT %s OFFSET %s
    """
    async with connection_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(query, (limit, offset))
            rows = await cur.fetchall()
    return [
        BookModel(
            id=str(row[0]),
            slug=row[1],
            title=row[2],
            description=row[3],
            cover_url=row[4],
            default_days=row[5],
        )
        for row in rows
    ]


@app.get("/books/{slug}", response_model=BookDetailModel)
async def get_book_detail(
    slug: str,
    connection_pool: AsyncConnectionPool = Depends(get_pool),
) -> BookDetailModel:
    book_query = """
        SELECT id, slug, title, description, cover_url, default_days
        FROM books WHERE slug = %s
    """
    topic_query = """
        SELECT payload
        FROM book_topics
        WHERE book_id = %s
        ORDER BY chapter_index, topic_index
        LIMIT 12
    """
    async with connection_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(book_query, (slug,))
            book_row = await cur.fetchone()
            if not book_row:
                raise HTTPException(status_code=404, detail="Book not found")
            book_id = book_row[0]
            await cur.execute(topic_query, (book_id,))
            topic_rows = await cur.fetchall()

    preview = []
    for topic_row in topic_rows:
        payload = topic_row[0] or {}
        topic = payload.get("topic") or {}
        topic["chapter"] = payload.get("title")
        preview.append(topic)

    return BookDetailModel(
        id=str(book_row[0]),
        slug=book_row[1],
        title=book_row[2],
        description=book_row[3],
        cover_url=book_row[4],
        default_days=book_row[5],
        topics_preview=preview,
    )


@app.post("/plans", response_model=PlanModel, status_code=201)
async def create_plan(
    payload: PlanRequest,
    background_tasks: BackgroundTasks,
    connection_pool: AsyncConnectionPool = Depends(get_pool),
) -> PlanModel:
    plan_days: List[Dict[str, Any]] = []
    async with connection_pool.connection() as conn:
        async with conn.cursor() as cur:
            book_id, book_title = await _fetch_book(cur, payload.book_slug)
            user_id = await _upsert_user(cur, payload.email, payload.display_name)
            template_key = _build_template_key(
                book_id,
                payload.total_days,
                payload.minutes_per_day,
                payload.focus,
            )
            template_plan_id = await _find_template_plan_id(cur, template_key)
            template_created = False
            if template_plan_id:
                LOGGER.info("Reusing template plan %s for key %s", template_plan_id, template_key)
                plan_days = await _load_plan_day_payloads(cur, template_plan_id)
                if not plan_days:
                    raise HTTPException(status_code=500, detail="Template plan is missing day payloads")
            else:
                topic_payloads = await _fetch_topics(cur, book_id)
                if not topic_payloads:
                    raise HTTPException(status_code=400, detail="Book has no topics ingested yet")
                plan_days = _build_personalized_plan(
                    book_title,
                    topic_payloads,
                    payload.total_days,
                    payload.minutes_per_day,
                )
                template_plan_id = await _create_study_plan(
                    cur,
                    user_id,
                    book_id,
                    payload.total_days,
                    payload.minutes_per_day,
                    payload.focus,
                    is_template=True,
                    template_key=template_key,
                )
                await _persist_plan_days(cur, template_plan_id, plan_days)
                template_created = True
                LOGGER.info("Created template plan %s for key %s", template_plan_id, template_key)

            plan_id = await _create_study_plan(
                cur,
                user_id,
                book_id,
                payload.total_days,
                payload.minutes_per_day,
                payload.focus,
                template_parent_id=template_plan_id,
                template_key=template_key,
            )
            enriched_days = await _count_enriched_days(cur, template_plan_id)
        await conn.commit()

    book_model = BookModel(
        id=str(book_id),
        slug=payload.book_slug,
        title=book_title,
        description=None,
        cover_url=None,
        default_days=None,
    )
    enrichment_complete = bool(plan_days) and enriched_days >= len(plan_days)
    total_plan_days = len(plan_days)
    LOGGER.info(
        "Plan %s linked to template %s (enriched %s/%s days, auto=%s)",
        plan_id,
        template_plan_id,
        enriched_days,
        total_plan_days,
        PLAN_CONTENT_AUTO,
    )
    plan_model = PlanModel(
        id=str(plan_id),
        book=book_model,
        total_days=payload.total_days,
        minutes_per_day=payload.minutes_per_day,
        focus=payload.focus,
        days=[PlanDayModel(**day) for day in plan_days],
        enriched_days=enriched_days,
        is_enrichment_complete=enrichment_complete,
        template_plan_id=str(template_plan_id),
    )

    if PLAN_CONTENT_AUTO and not enrichment_complete:
        background_tasks.add_task(
            _auto_enrich_plan,
            str(template_plan_id),
        )
        if template_created:
            LOGGER.info("Scheduled enrichment worker for new template %s", template_plan_id)
        else:
            LOGGER.info(
                "Scheduled enrichment worker for template %s to finish pending days (%s/%s)",
                template_plan_id,
                enriched_days,
                total_plan_days,
            )
    elif not PLAN_CONTENT_AUTO:
        LOGGER.warning(
            "PLAN_CONTENT_AUTO disabled; template %s will remain at %s/%s days until manual run",
            template_plan_id,
            enriched_days,
            total_plan_days,
        )
    else:
        LOGGER.info(
            "Template %s already complete (%s/%s); skipping auto enrichment",
            template_plan_id,
            enriched_days,
            total_plan_days,
        )

    return plan_model


@app.get("/users/{email}/plans", response_model=List[PlanSummaryModel])
async def list_user_plans(
    email: str,
    connection_pool: AsyncConnectionPool = Depends(get_pool),
) -> List[PlanSummaryModel]:
    normalized_email = email.strip().lower()
    query = """
        SELECT sp.id, sp.total_days, sp.minutes_per_day, sp.focus, b.title, b.slug, sp.template_parent_id
        FROM study_plans sp
        JOIN users u ON sp.user_id = u.id
        JOIN books b ON sp.book_id = b.id
        WHERE u.email = %s
        ORDER BY sp.created_at DESC
    """
    summaries: List[PlanSummaryModel] = []
    async with connection_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(query, (normalized_email,))
            rows = await cur.fetchall()
            for row in rows:
                plan_id = str(row[0])
                template_plan_id = str(row[6]) if row[6] else plan_id
                enriched_days = await _count_enriched_days(cur, template_plan_id)
                total_days = int(row[1])
                summaries.append(
                    PlanSummaryModel(
                        id=plan_id,
                        book_title=row[4],
                        book_slug=row[5],
                        total_days=total_days,
                        minutes_per_day=row[2],
                        focus=row[3],
                        template_plan_id=template_plan_id,
                        enriched_days=enriched_days,
                        is_enrichment_complete=enriched_days >= total_days if total_days else False,
                    )
                )
    return summaries


@app.get("/plans/{plan_id}", response_model=PlanModel)
async def get_plan(
    plan_id: str,
    connection_pool: AsyncConnectionPool = Depends(get_pool),
) -> PlanModel:
    plan_query = """
        SELECT sp.id, sp.total_days, sp.minutes_per_day, sp.focus,
               b.id, b.slug, b.title, b.description, b.cover_url, b.default_days,
               sp.template_parent_id
        FROM study_plans sp
        JOIN books b ON sp.book_id = b.id
        WHERE sp.id = %s
    """
    async with connection_pool.connection() as conn:
        async with conn.cursor() as cur:
            await cur.execute(plan_query, (plan_id,))
            plan_row = await cur.fetchone()
            if not plan_row:
                raise HTTPException(status_code=404, detail="Plan not found")
            source_plan_id = plan_row[10] or plan_row[0]
            source_plan_id = str(source_plan_id)
            plan_payloads = await _load_plan_day_payloads(cur, source_plan_id)
            enriched_days = await _count_enriched_days(cur, source_plan_id)
            LOGGER.info(
                "Fetched plan %s (template=%s) showing %s/%s enriched days",
                plan_id,
                source_plan_id,
                enriched_days,
                len(plan_payloads),
            )

    book_model = BookModel(
        id=str(plan_row[4]),
        slug=plan_row[5],
        title=plan_row[6],
        description=plan_row[7],
        cover_url=plan_row[8],
        default_days=plan_row[9],
    )
    plan_days = [PlanDayModel(**payload) for payload in plan_payloads]
    LOGGER.info(
        "Plan %s served using template %s with %s/%s enriched days",
        plan_id,
        source_plan_id,
        enriched_days,
        len(plan_days),
    )
    return PlanModel(
        id=str(plan_row[0]),
        book=book_model,
        total_days=plan_row[1],
        minutes_per_day=plan_row[2],
        focus=plan_row[3],
        days=plan_days,
        enriched_days=enriched_days,
        is_enrichment_complete=enriched_days >= len(plan_days) if plan_days else False,
        template_plan_id=source_plan_id,
    )


@app.get("/plans/{plan_id}/days/{day_number}", response_model=PlanDayDetailModel)
async def get_plan_day(
    plan_id: str,
    day_number: int,
    connection_pool: AsyncConnectionPool = Depends(get_pool),
) -> PlanDayDetailModel:
    query = """
        SELECT pd.payload, pdc.content
        FROM plan_days pd
        LEFT JOIN plan_day_content pdc ON pdc.plan_day_id = pd.id
        WHERE pd.plan_id = %s AND pd.day_number = %s
    """
    async with connection_pool.connection() as conn:
        async with conn.cursor() as cur:
            source_plan_id = await _resolve_plan_source_id(cur, plan_id)
            LOGGER.info(
                "Plan day request %s day %s resolved to template %s",
                plan_id,
                day_number,
                source_plan_id,
            )
            await cur.execute(query, (source_plan_id, day_number))
            row = await cur.fetchone()
            if not row:
                raise HTTPException(status_code=404, detail="Plan day not found")

    payload, content_payload = row
    if not payload:
        raise HTTPException(status_code=404, detail="Plan day is missing payload")

    day_model = PlanDayModel(**payload)
    content_model = DayContentModel(**content_payload) if content_payload else None
    return PlanDayDetailModel(day=day_model, content=content_model)


@app.exception_handler(Exception)
async def handle_exceptions(request: Request, exc: Exception):
    return JSONResponse(status_code=500, content={"error": str(exc)})


async def _fetch_book(cur, slug: str) -> tuple[str, str]:
    await cur.execute("SELECT id, title FROM books WHERE slug = %s", (slug,))
    row = await cur.fetchone()
    if not row:
        raise HTTPException(status_code=404, detail="Book not found")
    return str(row[0]), row[1]


async def _fetch_topics(cur, book_id: str) -> List[Dict[str, Any]]:
    await cur.execute(
        """
        SELECT payload
        FROM book_topics
        WHERE book_id = %s
        ORDER BY chapter_index, topic_index
        """,
        (book_id,),
    )
    rows = await cur.fetchall()
    topics: List[Dict[str, Any]] = []
    for (payload,) in rows:
        topic_data = dict(payload.get("topic") or {}) if payload else {}
        topic_data["chapter"] = payload.get("title") if payload else None
        topics.append(topic_data)
    return topics


async def _upsert_user(cur, email: str, display_name: Optional[str]) -> str:
    await cur.execute(
        """
        INSERT INTO users (email, display_name)
        VALUES (%s, %s)
        ON CONFLICT (email)
        DO UPDATE SET display_name = COALESCE(EXCLUDED.display_name, users.display_name)
        RETURNING id;
        """,
        (email.lower(), display_name),
    )
    return str((await cur.fetchone())[0])


async def _verify_google_id_token(raw_token: str) -> Dict[str, Any]:
    if not raw_token:
        raise HTTPException(status_code=400, detail="Missing Google token")
    if not GOOGLE_CLIENT_ID:
        raise HTTPException(status_code=500, detail="GOOGLE_CLIENT_ID is not configured")

    def _fetch_payload() -> Dict[str, Any]:
        token_url = "https://oauth2.googleapis.com/tokeninfo?id_token=" + urllib.parse.quote(raw_token)
        with urllib.request.urlopen(token_url, timeout=10) as resp:  # nosec B310
            return json.loads(resp.read())

    loop = asyncio.get_running_loop()
    try:
        payload: Dict[str, Any] = await loop.run_in_executor(None, _fetch_payload)
    except Exception as exc:  # pragma: no cover - network path
        LOGGER.warning("Google token verification failed: %s", exc)
        raise HTTPException(status_code=400, detail="Invalid Google token") from exc

    audience = payload.get("aud")
    if audience != GOOGLE_CLIENT_ID:
        raise HTTPException(status_code=400, detail="Google token is not meant for this application")

    expires_at = payload.get("exp")
    if expires_at and int(expires_at) < int(time.time()) - 60:
        raise HTTPException(status_code=400, detail="Google token has expired")

    email = payload.get("email")
    if not email:
        raise HTTPException(status_code=400, detail="Google token missing email claim")

    return payload


async def _create_study_plan(
    cur,
    user_id: str,
    book_id: str,
    total_days: int,
    minutes_per_day: Optional[int],
    focus: Optional[str],
    *,
    is_template: bool = False,
    template_key: Optional[str] = None,
    template_parent_id: Optional[str] = None,
) -> str:
    await cur.execute(
        """
        INSERT INTO study_plans (
            user_id,
            book_id,
            total_days,
            minutes_per_day,
            focus,
            is_template,
            template_key,
            template_parent_id
        )
        VALUES (%s, %s, %s, %s, %s, %s, %s, %s)
        RETURNING id;
        """,
        (user_id, book_id, total_days, minutes_per_day, focus, is_template, template_key, template_parent_id),
    )
    return str((await cur.fetchone())[0])


def _build_personalized_plan(
    book_title: str,
    topics: Sequence[Dict[str, Any]],
    total_days: int,
    minutes_per_day: Optional[int],
) -> List[Dict[str, Any]]:
    if total_days <= 0:
        raise HTTPException(status_code=400, detail="total_days must be positive")
    if not topics:
        raise HTTPException(status_code=400, detail="Book topics missing")

    per_day = math.ceil(len(topics) / total_days)
    plan_days: List[Dict[str, Any]] = []
    for day_idx in range(total_days):
        start = day_idx * per_day
        end = min(len(topics), start + per_day)
        day_topics = topics[start:end] if start < len(topics) else []
        if not day_topics and plan_days:
            day_topics = plan_days[-1]["topics"]
        day_number = day_idx + 1
        goal = _summarize_topics(day_topics, book_title)
        estimated = minutes_per_day or max(30, len(day_topics) * 45)
        plan_days.append(
            {
                "day": day_number,
                "title": f"Day {day_number}",
                "goal": goal,
                "estimated_minutes": estimated,
                "topics": [_coerce_topic(topic) for topic in day_topics],
            }
        )
    return plan_days


def _summarize_topics(topics: Sequence[Dict[str, Any]], book_title: str) -> str:
    names = [topic.get("name") for topic in topics if topic.get("name")]
    if not names:
        return f"Review prior knowledge from {book_title}."
    if len(names) == 1:
        return f"Deep dive into {names[0]}."
    if len(names) == 2:
        return f"Connect {names[0]} with {names[1]}."
    return f"Progress through {', '.join(names[:-1])}, then wrap with {names[-1]}"


async def _persist_plan_days(cur, plan_id: str, plan_days: List[Dict[str, Any]]) -> None:
    await cur.execute("DELETE FROM plan_days WHERE plan_id = %s", (plan_id,))
    for day in plan_days:
        await cur.execute(
            """
            INSERT INTO plan_days (plan_id, day_number, payload)
            VALUES (%s, %s, %s::jsonb)
            ON CONFLICT (plan_id, day_number)
            DO UPDATE SET payload = EXCLUDED.payload;
            """,
            (plan_id, day.get("day"), json.dumps(day, ensure_ascii=False)),
        )


def _coerce_topic(topic_payload: Dict[str, Any]) -> Dict[str, Any]:
    return {
        "name": topic_payload.get("name") or "Untitled Topic",
        "chapter": topic_payload.get("chapter"),
        "summary": topic_payload.get("summary"),
        "difficulty": topic_payload.get("difficulty"),
        "prerequisites": topic_payload.get("prerequisites") or [],
    }


async def _count_enriched_days(cur, plan_id: str) -> int:
    await cur.execute(
        """
        SELECT COUNT(*)
        FROM plan_day_content pdc
        JOIN plan_days pd ON pd.id = pdc.plan_day_id
        WHERE pd.plan_id = %s
        """,
        (plan_id,),
    )
    row = await cur.fetchone()
    count = int(row[0]) if row and row[0] is not None else 0
    LOGGER.info("Counted %s enriched days for plan/template %s", count, plan_id)
    return count


def _build_template_key(
    book_id: str,
    total_days: int,
    minutes_per_day: Optional[int],
    focus: Optional[str],
) -> str:
    minutes_segment = minutes_per_day or 0
    focus_segment = (focus or "none").strip().lower() or "none"
    return f"{book_id}:{total_days}:{minutes_segment}:{focus_segment}"


async def _find_template_plan_id(cur, template_key: str) -> Optional[str]:
    if not template_key:
        return None
    await cur.execute(
        """
        SELECT id
        FROM study_plans
        WHERE template_key = %s AND is_template = TRUE
        LIMIT 1
        """,
        (template_key,),
    )
    row = await cur.fetchone()
    return str(row[0]) if row else None


async def _load_plan_day_payloads(cur, plan_id: str) -> List[Dict[str, Any]]:
    await cur.execute(
        """
        SELECT payload
        FROM plan_days
        WHERE plan_id = %s
        ORDER BY day_number
        """,
        (plan_id,),
    )
    rows = await cur.fetchall()
    return [row[0] for row in rows if row and row[0]]


async def _resolve_plan_source_id(cur, plan_id: str) -> str:
    await cur.execute(
        """
        SELECT template_parent_id
        FROM study_plans
        WHERE id = %s
        """,
        (plan_id,),
    )
    row = await cur.fetchone()
    if not row:
        raise HTTPException(status_code=404, detail="Plan not found")
    parent_id = row[0]
    return str(parent_id) if parent_id else plan_id


def _auto_enrich_plan(plan_id: str) -> None:
    LOGGER.info(
        "Auto enrichment starting for plan template %s (backend=%s, openai_model=%s, ollama_model=%s)",
        plan_id,
        PLAN_CONTENT_BACKEND,
        PLAN_CONTENT_OPENAI_MODEL,
        PLAN_CONTENT_OLLAMA_MODEL,
    )
    try:
        run_plan_enrichment(
            plan_id=plan_id,
            database_url=DATABASE_URL,
            llm_backend=PLAN_CONTENT_BACKEND,
            llm_model=PLAN_CONTENT_OPENAI_MODEL,
            ollama_model=PLAN_CONTENT_OLLAMA_MODEL,
        )
        LOGGER.info("Auto enrichment finished for plan template %s", plan_id)
    except Exception as exc:  # pragma: no cover
        LOGGER.exception("Auto enrichment failed for plan %s: %s", plan_id, exc)