# routes/latestcomments.py
from fastapi import APIRouter, Request, Depends, Query
from fastapi.responses import RedirectResponse
from fastapi.templating import Jinja2Templates
from starlette.status import HTTP_302_FOUND
from utils.auth import get_current_user
from utils.pingdata import open_mysql, get_mysql_creds, get_pingconf_vars

PingConf = get_pingconf_vars()
mysql_creds = get_mysql_creds(PingConf)

router = APIRouter()
templates = Jinja2Templates(directory="templates")


@router.get("/latestcomments")
async def latest_comments(
    request: Request,
    user=Depends(get_current_user),
    ToID: str = Query(None),
    FromID: str = Query(None),
):
    # Require login
    if not user:
        return RedirectResponse(url="/signin", status_code=HTTP_302_FOUND)

    # Normalize query params
    to_id = int(ToID) if ToID and ToID.isdigit() else None
    from_id = int(FromID) if FromID and FromID.isdigit() else None

    # Resolve ToID to a user name for populating the To field
    to_name = None
    if to_id:
        try:
            with open_mysql(mysql_creds) as conn:
                cursor = conn.cursor(dictionary=True)
                cursor.execute("SELECT Name FROM ID WHERE IDNum = %s", (to_id,))
                row = cursor.fetchone()
                if row:
                    to_name = row["Name"]
                cursor.close()
        except Exception:
            to_name = None

    # Base SQL for fetching comments
    sql = """
        SELECT
            c.ArtNum,
            c.ComNum,
            a.Title AS ArticleTitle,
            c.PostDate,
            u_from.Name AS FromUser,
            c.ReplyToHandleStr AS ToUser
        FROM Comments c
        JOIN Articles a ON c.ArtNum = a.ArtNum
        JOIN ID u_from ON c.HandleID = u_from.IDNum
        WHERE a.Deleted = 0
    """

    # Apply filters if provided
    params = []
    if to_id:
        sql += " AND EXISTS (SELECT 1 FROM Ping p WHERE p.GlobComNum = c.GlobComNum AND p.ToHandleID = %s)"
        params.append(to_id)

    if from_id:
        sql += " AND u_from.IDNum = %s"
        params.append(from_id)

    sql += " ORDER BY c.PostDate DESC LIMIT 50"

    # Fetch comments
    comments = []
    cursor = None
    try:
        with open_mysql(mysql_creds) as conn:
            cursor = conn.cursor(dictionary=True)
            cursor.execute(sql, params)
            comments = cursor.fetchall()

        for row in comments:
            # Display ComNum or Article
            row['DisplayComNum'] = "(Article)" if row['ComNum'] == 0 else f"(#{row['ComNum']})"

            # Parse ToUsers from ReplyToHandleStr
            reply_str = row.get('ToUser')
            if reply_str:
                row['ToUsers'] = [s.strip() for s in reply_str.split(',') if s.strip()]
                if not row['ToUsers']:
                    row['ToUsers'] = ""
            else:
                row['ToUsers'] = ""

    finally:
        if cursor:
            cursor.close()

    return templates.TemplateResponse(
        "latestcomments.html",
        {
            "request": request,
            "user": user,
            "comments": comments,
            "to_id": to_id,
            "from_id": from_id,
            "to_name": to_name,
        }
    )