from fastapi import APIRouter, Depends, Query, HTTPException, status
from sqlalchemy.orm import Session
from sqlalchemy import or_, desc, asc
from typing import Optional

from db.session import get_db
from db.models.course import Course
from db.schemas.course import CourseOut
from fastapi.encoders import jsonable_encoder
from fastapi.responses import JSONResponse

router = APIRouter(prefix="/courses", tags=["Courses"])


@router.get("/")
def list_courses(
    db: Session = Depends(get_db),
    search: Optional[str] = Query(None, description="Search keyword for topic, language, degree, or skills"),
    sort_by: str = Query("created_at", description="Field to sort by (e.g. created_at, topic)"),
    sort_order: str = Query("desc", description="Sorting order: asc or desc"),
    pageNo: int = Query(1, ge=1, description="Page number"),
    recordsPerPage: int = Query(10, ge=1, le=100, description="Items per page")
):
    """
    Get a paginated list of courses with optional search and sorting.
    """

    # ✅ Step 1: Base query (exclude deleted)
    db_query = db.query(Course)

    # ✅ Step 2: Apply search filter
    if search:
        search_pattern = f"%{search}%"
        db_query = db_query.filter(
            or_(
                Course.topic.ilike(search_pattern),
                Course.language.ilike(search_pattern),
                Course.degree.ilike(search_pattern),
                Course.skills.ilike(search_pattern),
            )
        )

    # ✅ Step 3: Count total records (before pagination)
    total_count = db_query.count()

    # ✅ Step 4: Apply sorting
    if hasattr(Course, sort_by):
        sort_column = getattr(Course, sort_by)
        db_query = db_query.order_by(asc(sort_column) if sort_order.lower() == "asc" else desc(sort_column))
    else:
        raise HTTPException(status_code=400, detail=f"Invalid sort field: {sort_by}")

    # ✅ Step 5: Pagination
    courses = db_query.offset((pageNo - 1) * recordsPerPage).limit(recordsPerPage).all()
    
    # ✅ Step 6: Handle no data
    if not courses:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="No courses found")

    # ✅ Step 7: Serialize output
    course_list = [CourseOut.from_orm(course) for course in courses]
    course_json = jsonable_encoder(course_list)

    # ✅ Step 8: Return response
    return JSONResponse(
        content={
            "status_code": status.HTTP_200_OK,
            "message": "Courses retrieved successfully",
            "total_count": total_count,
            "page": pageNo,
            "limit": recordsPerPage,
            "data": course_json,
        },
        status_code=status.HTTP_200_OK,
    )