import json

import aiosqlite
from fastapi import APIRouter, HTTPException, Query

from config import INFO_ENGINE_DB

router = APIRouter()


async def _db():
    return aiosqlite.connect(INFO_ENGINE_DB)


def _row_to_item(row):
    tags = []
    if row[5]:
        try:
            tags = json.loads(row[5])
        except Exception:
            tags = []

    return {
        "id": row[0],
        "source": row[1],
        "title": row[2],
        "summary": row[3],
        "url": row[4],
        "tags": tags,
        "published_at": row[6],
        "collected_at": row[7],
        "content_text": row[8],
        "content_html": row[9],
    }


@router.get("/items")
async def list_items(
    limit: int = Query(50, ge=1, le=200),
    offset: int = Query(0, ge=0),
    source: str = Query(""),
    tag: str = Query(""),
    since: str = Query(""),
):
    where = []
    params = []

    if source:
        where.append("source = ?")
        params.append(source)
    if tag:
        where.append("tags LIKE ?")
        params.append(f'%"{tag}"%')
    if since:
        where.append("collected_at >= ?")
        params.append(since)

    where_clause = f"WHERE {' AND '.join(where)}" if where else ""

    async with await _db() as db:
        count_cursor = await db.execute(
            f"SELECT COUNT(*) FROM info_items {where_clause}",
            tuple(params),
        )
        total = (await count_cursor.fetchone())[0]

        cursor = await db.execute(
            f"""
            SELECT id, source, title, summary, url, tags, published_at, collected_at, content_text, content_html
            FROM info_items
            {where_clause}
            ORDER BY COALESCE(published_at, collected_at) DESC, id DESC
            LIMIT ? OFFSET ?
            """,
            tuple(params + [limit, offset]),
        )
        rows = await cursor.fetchall()

    return {"items": [_row_to_item(row) for row in rows], "total": total}


@router.get("/items/{item_id}")
async def get_item(item_id: int):
    async with await _db() as db:
        cursor = await db.execute(
            """
            SELECT id, source, title, summary, url, tags, published_at, collected_at, content_text, content_html
            FROM info_items
            WHERE id = ?
            """,
            (item_id,),
        )
        row = await cursor.fetchone()

    if not row:
        raise HTTPException(404, "Item not found")

    return _row_to_item(row)


@router.get("/health")
async def health():
    async with await _db() as db:
        cursor = await db.execute("SELECT COUNT(*), MAX(collected_at) FROM info_items")
        row = await cursor.fetchone()

    return {
        "status": "ok",
        "item_count": row[0] if row else 0,
        "last_collect_at": row[1] if row else None,
    }