from datetime import datetime
from sqlalchemy.orm import Session
from db.models.university import University
from db.schemas.university import UniversityBase 
from sqlalchemy.exc import IntegrityError
from fastapi import HTTPException
from fastapi import Depends, HTTPException, status
from sqlalchemy.orm import Session
from sqlalchemy import func, asc, desc, or_
from fastapi.responses import JSONResponse
from fastapi.encoders import jsonable_encoder
from typing import Optional
from db.session import get_db
def get_university_code(name: str, id: int) -> str:
    """
    Generate a unique university code based on university name and ID.
    Example: UNI-DEL-20251028-001
    """
    prefix = "UNI"
    # Take first three uppercase letters of university name (if available)
    name_part = ''.join(filter(str.isalnum, name.upper()))[:3]
    date_part = datetime.now().strftime("%Y%m%d")
    return f"{prefix}-{name_part}-{date_part}-{id:03d}"

def create_university(db: Session, uni: UniversityBase):
    # Create initial university record (without code)
    db_uni = University(**uni.dict())
    db.add(db_uni)
    try:
        db.commit()
        db.refresh(db_uni)  # Get ID from DB

        # Now generate the institution_code using name and id
        db_uni.institution_code = get_university_code(db_uni.name, db_uni.id)
        db.commit()
        db.refresh(db_uni)

        return {"institution_code": db_uni.institution_code}

    except IntegrityError:
        db.rollback()
        raise HTTPException(status_code=400, detail="Email already exists")

def get_universities_dropdown(db: Session):
    return db.query(University).all()



async def get_universities(
    pageNo: int = 1,
    recordsPerPage: int = 10,
    search: Optional[str] = None,
    sort_by: str = "id",
    sort_order: str = "asc",
    db: Session = Depends(get_db)
):
    """
    Retrieve paginated, searchable, and sortable list of universities.
    """

    # ✅ Base query
    db_query = db.query(University)

    # ✅ Searching
    if search:
        db_query = db_query.filter(
            or_(
                University.name.ilike(f"%{search}%"),
                University.institution_code.ilike(f"%{search}%"),
            )
        )

    # ✅ Sorting
    if hasattr(University, sort_by):
        sort_column = getattr(University, sort_by)
        db_query = db_query.order_by(asc(sort_column) if sort_order == "asc" else desc(sort_column))
    else:
        db_query = db_query.order_by(asc(University.id))  # fallback

    # ✅ Pagination logic
    offset = (pageNo - 1) * recordsPerPage
    universities = db_query.offset(offset).limit(recordsPerPage).all()

    if not universities:
        raise HTTPException(status_code=status.HTTP_404_NOT_FOUND, detail="No universities found")

    # ✅ Total count
    total_universities = db.query(func.count(University.id)).scalar()

    # ✅ Prepare response data
    universities_list = [
        {
            "id": uni.id,
            "name": uni.name,
            "institution_code": uni.institution_code,
            "email": uni.email,
            "phone": uni.phone,
            "address": uni.address,
            "created_at": uni.created_at,
            "updated_at": uni.updated_at
        }
        for uni in universities
    ]

    # ✅ Response
    response_data = jsonable_encoder({
        "universities": universities_list,
        "total_count": total_universities,
        "page_no": pageNo,
        "records_per_page": recordsPerPage,
        "total_pages": (total_universities + recordsPerPage - 1) // recordsPerPage,
        "status_code": status.HTTP_200_OK,
        "message": "Universities retrieved successfully"
    })

    return JSONResponse(content=response_data, status_code=status.HTTP_200_OK)

def get_university(db: Session, university_id: int):
    return db.query(University).filter(University.id == university_id).first()

def update_university(db: Session, university_id: int, uni_update: UniversityBase):
    db_uni = db.query(University).filter(University.id == university_id).first()
    if db_uni:
        for key, value in uni_update.dict(exclude_unset=True).items():
            setattr(db_uni, key, value)
        db.commit()
        db.refresh(db_uni)
    return db_uni

def delete_university(db: Session, university_id: int):
    db_uni = db.query(University).filter(University.id == university_id).first()
    if db_uni:
        db.delete(db_uni)
        db.commit()
    return db_uni