#!/usr/bin/env python3
"""
FastAPI server for Italian vocabulary database.
Provides paginated access to 100k+ vocabulary entries.

Run with: uvicorn server:app --reload --port 5000
"""

from fastapi import FastAPI, Query, HTTPException
from fastapi.middleware.cors import CORSMiddleware
from pydantic import BaseModel
import sqlite3
from pathlib import Path
from typing import Optional

app = FastAPI(title="Italian Vocabulary API", version="1.0.0")

# Enable CORS for frontend access
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

DB_PATH = Path(__file__).parent / "italian.db"

class VocabItem(BaseModel):
    id: int
    italian: str
    english: str
    category: str
    notes: str

class PaginatedResponse(BaseModel):
    items: list[VocabItem]
    page: int
    per_page: int
    total: int
    total_pages: int

class CategoryCount(BaseModel):
    name: str
    count: int

class StatsResponse(BaseModel):
    total_entries: int
    categories: dict[str, int]

def get_db():
    """Get database connection."""
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

@app.get("/api/vocab", response_model=PaginatedResponse)
def get_vocab(
    page: int = Query(1, ge=1),
    per_page: int = Query(50, ge=1, le=200),
    category: Optional[str] = None,
    search: Optional[str] = None
):
    """
    Get paginated vocabulary entries.

    - **page**: Page number (default: 1)
    - **per_page**: Items per page (default: 50, max: 200)
    - **category**: Filter by category (optional)
    - **search**: Search term (optional, searches italian and english)
    """
    offset = (page - 1) * per_page
    search = search.strip() if search else None

    conn = get_db()
    cur = conn.cursor()

    # Filter: only words starting with a-z (skip symbols, suffixes like -abile)
    # Order: deterministic hash for variety (id * golden ratio prime mod large prime)
    base_filter = "v.italian GLOB '[a-z]*' AND length(v.italian) > 1"
    base_filter_no_alias = "italian GLOB '[a-z]*' AND length(italian) > 1"
    order_clause = "ORDER BY (v.id * 2654435761) % 2147483647"
    order_clause_no_alias = "ORDER BY (id * 2654435761) % 2147483647"

    # Build query based on filters
    if search:
        # Use FTS for search
        search_term = f'"{search}"*'  # Prefix match
        if category:
            cur.execute(f"""
                SELECT v.id, v.italian, v.english, v.category, v.notes
                FROM vocabulary v
                JOIN vocabulary_fts fts ON v.id = fts.rowid
                WHERE vocabulary_fts MATCH ? AND v.category = ? AND {base_filter}
                {order_clause}
                LIMIT ? OFFSET ?
            """, (search_term, category, per_page, offset))
        else:
            cur.execute(f"""
                SELECT v.id, v.italian, v.english, v.category, v.notes
                FROM vocabulary v
                JOIN vocabulary_fts fts ON v.id = fts.rowid
                WHERE vocabulary_fts MATCH ? AND {base_filter}
                {order_clause}
                LIMIT ? OFFSET ?
            """, (search_term, per_page, offset))
    else:
        if category:
            cur.execute(f"""
                SELECT id, italian, english, category, notes
                FROM vocabulary
                WHERE category = ? AND {base_filter_no_alias}
                {order_clause_no_alias}
                LIMIT ? OFFSET ?
            """, (category, per_page, offset))
        else:
            cur.execute(f"""
                SELECT id, italian, english, category, notes
                FROM vocabulary
                WHERE {base_filter_no_alias}
                {order_clause_no_alias}
                LIMIT ? OFFSET ?
            """, (per_page, offset))

    rows = cur.fetchall()
    items = [dict(row) for row in rows]

    # Get total count for pagination (with same filters)
    if search:
        search_term = f'"{search}"*'
        if category:
            cur.execute(f"""
                SELECT COUNT(*) FROM vocabulary v
                JOIN vocabulary_fts fts ON v.id = fts.rowid
                WHERE vocabulary_fts MATCH ? AND v.category = ? AND {base_filter}
            """, (search_term, category))
        else:
            cur.execute(f"""
                SELECT COUNT(*) FROM vocabulary v
                JOIN vocabulary_fts fts ON v.id = fts.rowid
                WHERE vocabulary_fts MATCH ? AND {base_filter}
            """, (search_term,))
    else:
        if category:
            cur.execute(f"SELECT COUNT(*) FROM vocabulary WHERE category = ? AND {base_filter_no_alias}", (category,))
        else:
            cur.execute(f"SELECT COUNT(*) FROM vocabulary WHERE {base_filter_no_alias}")

    total = cur.fetchone()[0]
    conn.close()

    return {
        'items': items,
        'page': page,
        'per_page': per_page,
        'total': total,
        'total_pages': (total + per_page - 1) // per_page
    }

@app.get("/api/vocab/random", response_model=VocabItem)
def get_random(category: Optional[str] = None):
    """Get a random vocabulary entry, optionally filtered by category."""
    conn = get_db()
    cur = conn.cursor()

    # Same filter as main endpoint
    base_filter = "italian GLOB '[a-z]*' AND length(italian) > 1"

    if category:
        cur.execute(f"""
            SELECT id, italian, english, category, notes
            FROM vocabulary
            WHERE category = ? AND {base_filter}
            ORDER BY RANDOM()
            LIMIT 1
        """, (category,))
    else:
        cur.execute(f"""
            SELECT id, italian, english, category, notes
            FROM vocabulary
            WHERE {base_filter}
            ORDER BY RANDOM()
            LIMIT 1
        """)

    row = cur.fetchone()
    conn.close()

    if row:
        return dict(row)
    raise HTTPException(status_code=404, detail="No entries found")

@app.get("/api/vocab/{id}", response_model=VocabItem)
def get_vocab_by_id(id: int):
    """Get a specific vocabulary entry by ID."""
    conn = get_db()
    cur = conn.cursor()
    cur.execute("SELECT id, italian, english, category, notes FROM vocabulary WHERE id = ?", (id,))
    row = cur.fetchone()
    conn.close()

    if row:
        return dict(row)
    raise HTTPException(status_code=404, detail="Not found")

@app.get("/api/categories", response_model=list[CategoryCount])
def get_categories():
    """Get list of categories with counts."""
    conn = get_db()
    cur = conn.cursor()
    cur.execute("""
        SELECT category, COUNT(*) as count
        FROM vocabulary
        GROUP BY category
        ORDER BY count DESC
    """)
    rows = cur.fetchall()
    conn.close()

    return [{'name': row['category'], 'count': row['count']} for row in rows]

@app.get("/api/stats", response_model=StatsResponse)
def get_stats():
    """Get database statistics."""
    conn = get_db()
    cur = conn.cursor()

    cur.execute("SELECT COUNT(*) FROM vocabulary")
    total = cur.fetchone()[0]

    cur.execute("""
        SELECT category, COUNT(*) as count
        FROM vocabulary
        GROUP BY category
        ORDER BY count DESC
    """)
    categories = {row['category']: row['count'] for row in cur.fetchall()}

    conn.close()

    return {
        'total_entries': total,
        'categories': categories
    }

if __name__ == '__main__':
    import uvicorn

    if not DB_PATH.exists():
        print(f"Database not found at {DB_PATH}")
        print("Run: python build_vocab_db.py")
        exit(1)

    print(f"Starting server with database: {DB_PATH}")
    uvicorn.run(app, host="0.0.0.0", port=5000)