import asyncio
from typing import Optional, List
from fastapi import APIRouter, Depends, HTTPException, Query
from pydantic import BaseModel, Field

from app.auth import require_user
from app.database import get_db
from app.tts import get_audio_hash, prewarm_card_audio

router = APIRouter(tags=["Cards & Collections"])


class CardCreateRequest(BaseModel):
    term: str = Field(..., min_length=1)
    translation: str = Field(..., min_length=1)
    context_sentence: Optional[str] = ""
    phonetic: Optional[str] = ""
    audio_url: Optional[str] = ""
    part_of_speech: Optional[str] = ""
    definition_en: Optional[str] = ""
    tags: Optional[str] = "general"
    notes: Optional[str] = ""
    collection: Optional[str] = "General"


class CardUpdateRequest(BaseModel):
    term: Optional[str] = None
    translation: Optional[str] = None
    context_sentence: Optional[str] = None
    phonetic: Optional[str] = None
    audio_url: Optional[str] = None
    part_of_speech: Optional[str] = None
    definition_en: Optional[str] = None
    tags: Optional[str] = None
    notes: Optional[str] = None
    collection: Optional[str] = None


class BatchActionRequest(BaseModel):
    card_ids: List[int] = Field(..., min_length=1)
    action: str = Field(..., pattern="^(set_collection|add_tags|delete)$")
    value: Optional[str] = ""


@router.get("/api/collections")
async def api_get_collections(user: dict = Depends(require_user)):
    """Returns all collections for the current user with card count."""
    with get_db() as conn:
        rows = conn.execute(
            """
            SELECT collection, COUNT(*) as count
            FROM vocabulary_cards
            WHERE user_id = ?
            GROUP BY collection
            ORDER BY count DESC, collection ASC
            """,
            (user["id"],),
        ).fetchall()
        return [{"name": r["collection"], "collection": r["collection"], "count": r["count"]} for r in rows]


@router.get("/api/cards/check-duplicate")
async def api_check_duplicate(term: str = Query(..., min_length=1), user: dict = Depends(require_user)):
    clean_term = term.strip().lower()
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM vocabulary_cards WHERE user_id = ? AND lower(trim(term)) = ?",
            (user["id"], clean_term),
        ).fetchone()
        if row:
            return {"exists": True, "card": dict(row)}
        return {"exists": False, "card": None}


@router.get("/api/cards")
async def api_get_cards(
    search: Optional[str] = None,
    tag: Optional[str] = None,
    collection: Optional[str] = None,
    srs_state: Optional[str] = None,
    limit: int = 100,
    offset: int = 0,
    user: dict = Depends(require_user),
):
    with get_db() as conn:
        query = """
            SELECT id, term, translation, context_sentence, phonetic, audio_url,
                   part_of_speech, definition_en, tags, notes, collection,
                   srs_reps, srs_interval, srs_ease, srs_next_review, srs_last_review, srs_state, error_count,
                   created_at, updated_at
            FROM vocabulary_cards
            WHERE user_id = ?
        """
        params = [user["id"]]

        if search:
            s_clean = f"%{search.strip()}%"
            query += " AND (term LIKE ? OR translation LIKE ? OR context_sentence LIKE ? OR notes LIKE ?)"
            params.extend([s_clean, s_clean, s_clean, s_clean])

        if tag:
            t_clean = f"%{tag.strip()}%"
            query += " AND tags LIKE ?"
            params.append(t_clean)

        if collection:
            query += " AND collection = ?"
            params.append(collection.strip())

        if srs_state:
            query += " AND srs_state = ?"
            params.append(srs_state.strip())

        # Total count before limit
        count_query = f"SELECT COUNT(*) FROM ({query})"
        total = conn.execute(count_query, params).fetchone()[0]

        query += " ORDER BY created_at DESC LIMIT ? OFFSET ?"
        params.extend([limit, offset])

        rows = conn.execute(query, params).fetchall()
        cards = []
        for r in rows:
            c = dict(r)
            if not c.get("audio_url"):
                c["audio_url"] = f"/api/audio/{get_audio_hash(c['term'], 'en', 1.0)}.wav"
            if c.get("context_sentence"):
                c["context_audio_url"] = f"/api/audio/{get_audio_hash(c['context_sentence'], 'en', 1.0)}.wav"
            cards.append(c)

        return {"total": total, "cards": cards}


@router.post("/api/cards")
async def api_create_card(card: CardCreateRequest, user: dict = Depends(require_user)):
    clean_term = card.term.strip()
    with get_db() as conn:
        existing = conn.execute(
            "SELECT * FROM vocabulary_cards WHERE user_id = ? AND lower(trim(term)) = lower(trim(?))",
            (user["id"], clean_term),
        ).fetchone()

        if existing:
            # Word already exists in library!
            existing_row = dict(existing)
            new_ctx = (card.context_sentence or "").strip()
            old_ctx = (existing_row.get("context_sentence") or "").strip()
            updated_ctx = old_ctx
            if new_ctx and new_ctx not in old_ctx:
                updated_ctx = f"{old_ctx}\n\n{new_ctx}" if old_ctx else new_ctx

            # Merge tags
            old_tags = set([t.strip() for t in (existing_row.get("tags") or "").split(",") if t.strip()])
            new_tags = [t.strip() for t in (card.tags or "").split(",") if t.strip()]
            for t in new_tags:
                old_tags.add(t)
            updated_tags = ", ".join(sorted(old_tags)) if old_tags else "general"

            conn.execute(
                """
                UPDATE vocabulary_cards
                SET context_sentence = ?, tags = ?, updated_at = CURRENT_TIMESTAMP
                WHERE id = ?
                """,
                (updated_ctx, updated_tags, existing_row["id"]),
            )
            updated_row = conn.execute("SELECT * FROM vocabulary_cards WHERE id = ?", (existing_row["id"],)).fetchone()
            res = dict(updated_row)
            if not res.get("audio_url"):
                res["audio_url"] = f"/api/audio/{get_audio_hash(res['term'], 'en', 1.0)}.wav"
            if res.get("context_sentence"):
                res["context_audio_url"] = f"/api/audio/{get_audio_hash(res['context_sentence'], 'en', 1.0)}.wav"
            res["already_exists"] = True

            # Pre-warm word and context audio in background
            asyncio.create_task(prewarm_card_audio(clean_term, card.context_sentence))
            return res

        audio_url_val = (card.audio_url or "").strip()
        if not audio_url_val:
            audio_url_val = f"/api/audio/{get_audio_hash(clean_term, 'en', 1.0)}.wav"

        cursor = conn.execute(
            """
            INSERT INTO vocabulary_cards (
                user_id, term, translation, context_sentence, phonetic, audio_url,
                part_of_speech, definition_en, tags, notes, collection,
                srs_reps, srs_interval, srs_ease, srs_state, error_count
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 0, 0, 2.5, 'new', 0)
            """,
            (
                user["id"],
                clean_term,
                card.translation.strip(),
                (card.context_sentence or "").strip(),
                (card.phonetic or "").strip(),
                audio_url_val,
                (card.part_of_speech or "").strip(),
                (card.definition_en or "").strip(),
                (card.tags or "general").strip(),
                (card.notes or "").strip(),
                (card.collection or "General").strip(),
            ),
        )
        new_id = cursor.lastrowid
        row = conn.execute("SELECT * FROM vocabulary_cards WHERE id = ?", (new_id,)).fetchone()
        res = dict(row)
        if not res.get("audio_url"):
            res["audio_url"] = f"/api/audio/{get_audio_hash(res['term'], 'en', 1.0)}.wav"
        if res.get("context_sentence"):
            res["context_audio_url"] = f"/api/audio/{get_audio_hash(res['context_sentence'], 'en', 1.0)}.wav"

        # Pre-warm word and context audio in background
        asyncio.create_task(prewarm_card_audio(clean_term, card.context_sentence))
        return res


@router.get("/api/cards/{card_id}")
async def api_get_card(card_id: int, user: dict = Depends(require_user)):
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM vocabulary_cards WHERE id = ? AND user_id = ?",
            (card_id, user["id"]),
        ).fetchone()
        if not row:
            raise HTTPException(status_code=404, detail="Card not found")
        return dict(row)


@router.put("/api/cards/{card_id}")
async def api_update_card(card_id: int, update: CardUpdateRequest, user: dict = Depends(require_user)):
    with get_db() as conn:
        row = conn.execute(
            "SELECT * FROM vocabulary_cards WHERE id = ? AND user_id = ?",
            (card_id, user["id"]),
        ).fetchone()
        if not row:
            raise HTTPException(status_code=404, detail="Card not found")
        current = dict(row)

        def pick(new_val, current_val):
            return new_val.strip() if new_val is not None else current_val

        conn.execute(
            """
            UPDATE vocabulary_cards
            SET term = ?, translation = ?, context_sentence = ?, phonetic = ?,
                audio_url = ?, part_of_speech = ?, definition_en = ?, tags = ?,
                notes = ?, collection = ?, updated_at = CURRENT_TIMESTAMP
            WHERE id = ? AND user_id = ?
            """,
            (
                pick(update.term, current["term"]),
                pick(update.translation, current["translation"]),
                pick(update.context_sentence, current.get("context_sentence", "")),
                pick(update.phonetic, current.get("phonetic", "")),
                pick(update.audio_url, current.get("audio_url", "")),
                pick(update.part_of_speech, current.get("part_of_speech", "")),
                pick(update.definition_en, current.get("definition_en", "")),
                pick(update.tags, current.get("tags", "")),
                pick(update.notes, current.get("notes", "")),
                pick(update.collection, current.get("collection", "General")),
                card_id,
                user["id"],
            ),
        )
        updated_row = conn.execute("SELECT * FROM vocabulary_cards WHERE id = ?", (card_id,)).fetchone()
        return dict(updated_row)


@router.delete("/api/cards/{card_id}")
async def api_delete_card(card_id: int, user: dict = Depends(require_user)):
    with get_db() as conn:
        cursor = conn.execute(
            "DELETE FROM vocabulary_cards WHERE id = ? AND user_id = ?",
            (card_id, user["id"]),
        )
        if cursor.rowcount == 0:
            raise HTTPException(status_code=404, detail="Card not found")
        return {"status": "ok", "deleted_id": card_id}


@router.post("/api/cards/batch")
async def api_batch_action(req: BatchActionRequest, user: dict = Depends(require_user)):
    if not req.card_ids:
        raise HTTPException(status_code=400, detail="No card IDs provided")

    with get_db() as conn:
        placeholders = ",".join("?" * len(req.card_ids))
        params_base = list(req.card_ids) + [user["id"]]

        if req.action == "delete":
            cursor = conn.execute(
                f"DELETE FROM vocabulary_cards WHERE id IN ({placeholders}) AND user_id = ?",
                params_base,
            )
            return {"status": "ok", "action": "delete", "affected": cursor.rowcount}

        elif req.action == "set_collection":
            collection_val = (req.value or "General").strip()
            cursor = conn.execute(
                f"UPDATE vocabulary_cards SET collection = ?, updated_at = CURRENT_TIMESTAMP WHERE id IN ({placeholders}) AND user_id = ?",
                [collection_val] + params_base,
            )
            return {"status": "ok", "action": "set_collection", "affected": cursor.rowcount}

        elif req.action == "add_tags":
            new_tags_raw = req.value or ""
            new_tags = [t.strip() for t in new_tags_raw.split(",") if t.strip()]
            if not new_tags:
                return {"status": "ok", "action": "add_tags", "affected": 0}

            rows = conn.execute(
                f"SELECT id, tags FROM vocabulary_cards WHERE id IN ({placeholders}) AND user_id = ?",
                params_base,
            ).fetchall()

            affected = 0
            for r in rows:
                existing = [t.strip() for t in (r["tags"] or "").split(",") if t.strip()]
                combined = existing + [t for t in new_tags if t not in existing]
                conn.execute(
                    "UPDATE vocabulary_cards SET tags = ?, updated_at = CURRENT_TIMESTAMP WHERE id = ?",
                    (", ".join(combined), r["id"]),
                )
                affected += 1
            return {"status": "ok", "action": "add_tags", "affected": affected}


@router.get("/api/tags")
async def api_get_tags(user: dict = Depends(require_user)):
    with get_db() as conn:
        rows = conn.execute(
            "SELECT tags FROM vocabulary_cards WHERE user_id = ?",
            (user["id"],),
        ).fetchall()
        tag_counts = {}
        for r in rows:
            if r["tags"]:
                for t in r["tags"].split(","):
                    clean = t.strip()
                    if clean:
                        tag_counts[clean] = tag_counts.get(clean, 0) + 1
        sorted_tags = sorted(
            [{"tag": k, "count": v} for k, v in tag_counts.items()],
            key=lambda x: x["count"],
            reverse=True,
        )
        return sorted_tags