import os
import shutil
from fastapi import APIRouter, Depends, File, UploadFile
from fastapi.responses import FileResponse, JSONResponse
from sqlalchemy.orm import Session
from app.database.main.mysql import get_db
from app.dependency.authantication import JWTPayloadSchema, get_current_user
import pandas as pd
from app.models.main.student import TblStudent

excel_router = APIRouter()

@excel_router.get("/download-excel-sample1")
async def downloadExcel_Sample1():
    file_path = "files/Excel Sample 1.xlsx"
    return FileResponse(
        path=file_path,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        filename="Excel Sample 1.xlsx"
    )

@excel_router.get("/download-excel-sample2")
async def downloadExcel_Sample2():
    file_path = "files/Excel Sample 2.xlsx"
    return FileResponse(
        path=file_path,
        media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        filename="Excel Sample 2.xlsx"
    )

UPLOAD_DIR = "uploaded_files"
os.makedirs(UPLOAD_DIR, exist_ok=True)

# @excel_router.post("/upload-excel")
# async def upload_excel(file: UploadFile = File(...)):
#     file_location = os.path.join(UPLOAD_DIR, file.filename)
    
#     with open(file_location, "wb") as buffer:
#         shutil.copyfileobj(file.file, buffer)

#     return JSONResponse(content={"message": "File uploaded successfully!", "filename": file.filename})

# @excel_router.post("/upload-excel-sample1")
# async def upload_excel1(file: UploadFile = File(...)):
#     filename: str = file.filename or "uploaded_file.xlsx"
#     file_location: str = os.path.join(UPLOAD_DIR, filename)

#     with open(file_location, "wb") as buffer:
#         shutil.copyfileobj(file.file, buffer)

#     return JSONResponse(content={"message": "File uploaded successfully!", "filename": filename})

@excel_router.post("/upload-excel-sample2")
async def upload_excel2(file: UploadFile = File(...)):
    filename: str = file.filename or "uploaded_file.xlsx"
    file_location: str = os.path.join(UPLOAD_DIR, filename)

    with open(file_location, "wb") as buffer:
        shutil.copyfileobj(file.file, buffer)

    return JSONResponse(content={"message": "File uploaded successfully!", "filename": filename})


@excel_router.post("/upload-excel-sample1")
async def upload_excel1(
    file: UploadFile = File(...),
    db: Session = Depends(get_db),
    token: JWTPayloadSchema = Depends(get_current_user)
):
    # ✅ Step 1: Extract student_id from token
    student_id = getattr(token, "student_id", None)
    if student_id is None:
        return JSONResponse(status_code=400, content={"error": "student_id missing in token."})

    # ✅ Step 2: Get the student from DB
    student = db.query(TblStudent).filter(TblStudent.student_id == student_id).first()
    if not student:
        return JSONResponse(status_code=404, content={"error": "Student not found."})

    # ✅ Step 3: Get related group from student (assuming relationship exists)
    if not student.group:
        return JSONResponse(status_code=404, content={"error": "Group not assigned to this student."})
    
    group = student.group  

    # ✅ Step 4: Save the uploaded file
    filename = file.filename or "uploaded_file.xlsx"
    file_location = os.path.join(UPLOAD_DIR, filename)
    with open(file_location, "wb") as buffer:
        shutil.copyfileobj(file.file, buffer)

    # ✅ Step 5: Optionally parse Excel
    df = pd.read_excel(file_location)

    # ✅ Step 6: Update group with Excel file info
    group.sample_excel_one = filename
    db.commit()

    return JSONResponse(content={
        "message": "File uploaded and group updated successfully!",
        "group_id": group.group_id,
        "filename": filename
    })