from fastapi import HTTPException, UploadFile
import pandas as pd
from sqlalchemy.orm import Session
from app.api.course_list.schemas import CourseFilterQuery, CourseListCreat, CourseListUpdate
from app.models.main.course_list import CourseListBase, TblCourseList
from app.models.main.full_courses import TblFullCourse
from app.utils.schemas_utils import CustomResponse
from sqlalchemy.exc import IntegrityError
import requests
class CourseListService:
    
    def __init__(self, db: Session, token: dict):
        self.db = db
        self.token = token
        
    async def upload_courses(self, file: UploadFile):
        if not file.filename or not (file.filename.lower().endswith((".xlsx", ".xls"))):
            raise HTTPException(status_code=400, detail="Only Excel files (.xlsx, .xls) are supported")
        try:
            df = pd.read_excel(file.file)
        except Exception as e:
            raise HTTPException(status_code=400, detail=f"Error reading Excel file: {e}")
        column_mapping = {
            "Course Name": "course_name",
            "Duration (Hrs)": "duration_hrs",
            "Operational Area": "operational_area",
            "Functional Area": "functional_area",
            "Target Audience": "target_audience",
            "Standards": "standards",
            "Validity Days": "validity_days"
        }
        for col in column_mapping.keys():
            if col not in df.columns:
                raise HTTPException(status_code=400, detail=f"Missing column in Excel: {col}")
        df = df.where(pd.notnull(df), None)
        for _, row in df.iterrows():
            course_name = row["Course Name"]
            full_course = self.db.query(TblFullCourse).filter(
                TblFullCourse.course == course_name
            ).first()
            full_course_id = full_course.full_course_id if full_course else None
            course_data = {
                "course_name": course_name,
                "duration_hrs": int(row["Duration (Hrs)"]) if pd.notna(row["Duration (Hrs)"]) else None,
                "operational_area": row["Operational Area"],
                "functional_area": row["Functional Area"],
                "target_audience": row["Target Audience"],
                "standards": row["Standards"],
                "validity_days": int(row["Validity Days"]) if pd.notna(row["Validity Days"]) else None,
                "full_course_id": full_course_id
            }
            existing_course = self.db.query(TblCourseList).filter(
                TblCourseList.course_name == course_name
            ).first()
            if existing_course:
                for key, value in course_data.items():
                    setattr(existing_course, key, value)
            else:
                new_course = TblCourseList(**course_data)
                self.db.add(new_course)
        self.db.commit()
        return CustomResponse(status="1", message="Courses uploaded/updated Successfully")
    
    async def get_all_full_courses(self):
        return self.db.query(TblFullCourse).all()
    
    async def get_all_courses(self):
        return self.db.query(TblCourseList).all()
    
    async def filter_courses(self, filters: CourseFilterQuery):
        query = self.db.query(TblCourseList)
        if filters.functional_area:
            query = query.filter(TblCourseList.functional_area == filters.functional_area)
        if filters.operational_area:
            query = query.filter(TblCourseList.operational_area == filters.operational_area)
        if filters.target_audience:
            query = query.filter(TblCourseList.target_audience == filters.target_audience)
        if filters.standards:
            query = query.filter(TblCourseList.standards == filters.standards)
        return query.all()
    
    async def create_course(self, request: CourseListCreat):
        created_course = CourseListBase.model_validate(request)
        if not created_course.course_name and created_course.full_course_id:
            full_course = self.db.query(TblFullCourse).filter(
                TblFullCourse.full_course_id == created_course.full_course_id
            ).first()
            if not full_course:
                raise HTTPException(status_code=400, detail="Invalid full_course_id")
            created_course.course_name = full_course.course
        try:
            TblCourseList.create(created_course, self.db)
            self.db.commit()
        except IntegrityError:
            self.db.rollback()
            raise HTTPException(
                status_code=400,
                detail=f"Course already exists for full_course_id {created_course.full_course_id}"
            )
        return CustomResponse(status="1", message="Course Created Successfully")
    
    def sync_full_course_list(self):
        LMS_BASE_URL = "https://lms.marinerskills.com/lmsmarinerskills"
        API_KEY = "6598E51A-4C41-416C-821F-A50EBBBE4E99"
        payload = {"Key": API_KEY}
        try:
            resp = requests.post(f"{LMS_BASE_URL}/FullCourseList", json=payload, timeout=10)
            resp.raise_for_status()
        except requests.RequestException as e:
            raise HTTPException(status_code=500, detail=f"LMS API error: {str(e)}")
        data = resp.json()
        results = []
        for item in data:
            course = TblFullCourse(**{
                "program": item.get("Program"),
                "batch": item.get("Batch"),
                "course": item.get("Course"),
                "batch_course_code": item.get("BatchCourseCode"),
            })

            self.db.merge(course)
            results.append(item)
        self.db.commit()
        return {"stored_courses": results}
    
    async def update_course(self, request: CourseListUpdate):
        company_base = CourseListBase.model_validate(request)
        company = TblCourseList.update(request.course_id, company_base, self.db)
        if not company:
            raise HTTPException(status_code=404, detail="Course not found")
        return CustomResponse(status="1", message="Course updated successfully")