import math
from typing import Any, Dict, Optional

from app.core.database import DatabaseManager
from app.core.observability import LogService


class LongArticleMCP:
    def __init__(self, pool: DatabaseManager, log_service: LogService):
        self.pool = pool
        self.log_service = log_service

    SORTABLE_FIELDS = {
        "read_cnt",
        "read_median_multiplier",
        "publish_time",
    }

    BASE_WHERE = "WHERE t1.status = 2"

    BASE_FROM = """
        FROM long_articles_decode_tasks t1
        JOIN ad_platform_accounts_daily_detail t2
        ON t1.wx_sn = t2.wx_sn
    """

    async def get_decode_response(self, params: Optional[Dict[str, Any]] = None):
        params = params or {}
        page = params.get("page", 1)
        page_size = params.get("page_size", 20)
        sort_by = params.get("sort_by")
        sort_order = params.get("sort_order", "asc")

        count_query = f"SELECT COUNT(*) AS total {self.BASE_FROM} {self.BASE_WHERE}"
        count_result = await self.pool.async_fetch(query=count_query)
        total = count_result[0]["total"] if count_result else 0
        total_pages = math.ceil(total / page_size) if total > 0 else 0

        data_query = f"""
            SELECT t2.gh_id,
                 t2.account_name,
                 t2.article_title,
                 t2.article_link,
                 t2.article_cover,
                 t2.article_text,
                 t2.read_cnt,
                 t2.read_median_multiplier,
                 from_unixtime(t2.publish_timestamp, '%%Y-%%m-%%d %%H:%%i:%%s') as publish_time,
                 t1.result
            {self.BASE_FROM}
            {self.BASE_WHERE}
        """

        if sort_by and sort_by in self.SORTABLE_FIELDS:
            direction = "ASC" if sort_order == "asc" else "DESC"
            order_column = "t2.publish_timestamp" if sort_by == "publish_time" else f"t2.{sort_by}"
            data_query += f" ORDER BY {order_column} {direction}"

        offset = (page - 1) * page_size
        data_query += " LIMIT %s OFFSET %s"
        rows = await self.pool.async_fetch(query=data_query, params=(page_size, offset))

        return {
            "total": total,
            "page": page,
            "page_size": page_size,
            "total_pages": total_pages,
            "items": rows,
        }