import os import re import unicodedata import pandas as pd import requests from fastapi import HTTPException from app.api.vessel.service import VesselService from app.api.company.service import CompanyService UPLOAD_DIR = "uploaded_files" OFFLINE_DASHBOARD_FILE = os.path.join(UPLOAD_DIR, "Offline dashboard.xlsx") def clean_text(value: str) -> str: """Remove Excel hidden characters (NBSP, odd unicode, double spaces).""" if not isinstance(value, str): value = str(value) value = unicodedata.normalize("NFKC", value) value = value.replace("\u00A0", " ") value = value.replace("\u2007", " ") value = value.replace("\u202F", " ") value = value.replace("\uFEFF", "") value = value.replace("\t", " ") value = value.replace("\n", " ") value = value.replace("\r", " ") value = re.sub(r"\s+", " ", value) return value.strip() def clean_sheet_name(name: str) -> str: name = clean_text(name) name = re.sub(r'[\\/*?\[\]:]', "_", name) return name[:31] class CourseService: def __init__(self, vessel_service: VesselService ): self.vessel_service = vessel_service self.company_service = CompanyService() def get_company_excel(self, company_id: int) -> str: """ Return dynamic offline file per company, fallback to template. """ path = os.path.join(UPLOAD_DIR, f"company_{company_id}.xlsx") if os.path.exists(path): return path return os.path.join(UPLOAD_DIR, "Offline dashboard.xlsx") def load_excel_auto_header(self, file_path: str, target_header: str = "Course Name") -> pd.DataFrame: if not os.path.exists(file_path): raise HTTPException(status_code=404, detail=f"Excel file not found: {file_path}") raw = pd.read_excel(file_path, header=None) header_row_idx = None for idx, row in raw.iterrows(): values = [str(v).strip() for v in row.values if v is not None] if target_header in values: header_row_idx = idx break if header_row_idx is None: raise HTTPException(status_code=400, detail=f"Could not detect header row containing '{target_header}'") if not isinstance(header_row_idx, int): raise HTTPException(status_code=400, detail="Detected header row index is not an integer") df = pd.read_excel(file_path, header=header_row_idx) df.columns = df.columns.astype(str).str.strip().str.replace("\n", " ").str.replace("\r", " ") return df def get_vessel_details_by_vessel_id(self, vessel_id: int) -> dict: """ Find vessel by vessel_id across all companies. Returns companyId, vesselType, vesselId, vesselName. """ for cid in range(1, 200): try: vessels = self.company_service.get_vessels_by_company(cid) for v in vessels: if str(v.VesselID) == str(vessel_id): return { "companyId": int(v.CompanyID), "vesselType": v.VesselType, "vesselId": int(v.VesselID), "vesselName": v.VesselName } except Exception: continue raise HTTPException(status_code=404, detail=f"Vessel {vessel_id} not found") def _find_matching_sheet_name(self, workbook_sheets: list, desired_sheet_name: str) -> str | None: desired_clean = clean_text(desired_sheet_name).lower() desired_clean2 = clean_sheet_name(desired_sheet_name).lower() for s in workbook_sheets: s_clean = clean_text(s).lower() s_clean2 = clean_sheet_name(s).lower() if ( s_clean == desired_clean or s_clean2 == desired_clean or s_clean == desired_clean2 or s_clean2 == desired_clean2 ): return s return None def sync_full_course_list(self, vessel_id: int): # Step 1: Fetch LMS course list 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() lms_raw = resp.json() except requests.RequestException as e: raise HTTPException(status_code=500, detail=f"LMS API error: {e}") # Parse LMS response if isinstance(lms_raw, dict) and isinstance(lms_raw.get("courses"), list): lms_courses = lms_raw["courses"] elif isinstance(lms_raw, list): lms_courses = lms_raw else: raise HTTPException(status_code=500, detail="Unexpected LMS response format") lms_lookup = {c.get("Course"): c for c in lms_courses if c.get("Course")} # Step 2: Get vessel details (companyId, vesselType) vessel_detail = self.get_vessel_details_by_vessel_id(vessel_id) company_id = vessel_detail["companyId"] vessel_type = vessel_detail["vesselType"] # Step 3: Get dynamic roles for this vessel vessel_role_info = self.vessel_service.get_roles_by_vessel(vessel_id) dynamic_roles_raw = vessel_role_info.get("roles", []) dynamic_roles = dynamic_roles_raw if isinstance(dynamic_roles_raw, list) else [str(dynamic_roles_raw)] clean_dynamic_roles = [clean_text(r) for r in dynamic_roles] # Step 4: Load company Excel (all sheets) offline_file = self.get_company_excel(company_id) try: wb = pd.read_excel(offline_file, sheet_name=None, header=None) except Exception as e: raise HTTPException(status_code=400, detail=f"Error reading Excel file: {offline_file} - {e}") if not wb: raise HTTPException(status_code=400, detail=f"Excel file is empty: {offline_file}") # --- find correct sheet name by loose matching --- actual_sheet_name = self._find_matching_sheet_name(list(wb.keys()), vessel_type) if actual_sheet_name is None: raise HTTPException( status_code=400, detail=f"No matching sheet found for vesselType '{vessel_type}'. Sheets: {list(wb.keys())}" ) df_raw = wb[actual_sheet_name] # Step 4b — Auto-detect correct header row ("Course Name") header_index = None for idx, row in df_raw.iterrows(): values = [clean_text(str(v)) for v in row.tolist()] if any(v.lower() == "course name" for v in values): header_index = idx break if header_index is None: raise HTTPException( status_code=400, detail=f"Could not find header row ('Course Name') in sheet '{actual_sheet_name}'." ) # Extract header row header_values = df_raw.iloc[header_index].tolist() clean_headers = [clean_text(str(h)) for h in header_values] # Build final DataFrame df = df_raw.iloc[header_index + 1:].copy() df.columns = clean_headers # Extra cleaning df.columns = df.columns.to_series().apply(lambda x: re.sub(r"\s+", " ", x).strip()) df = df.where(pd.notnull(df), None) # Step 5: Check if all required dynamic roles exist as columns missing_roles = [r for r in clean_dynamic_roles if r not in df.columns] if missing_roles: raise HTTPException( status_code=400, detail=f"Missing role columns: {missing_roles}. Sheet columns: {df.columns.tolist()}" ) # Step 6: Match CMS & LMS courses matched = [] unmatched = [] for _, row in df.iterrows(): cms_name = row.get("Course Name") if not cms_name: continue assigned_roles = [ orig for orig, clean_r in zip(dynamic_roles, clean_dynamic_roles) if row.get(clean_r) == "X" ] lms_rec = lms_lookup.get(cms_name) if assigned_roles and lms_rec: matched.append({ "lms_program": lms_rec.get("Program"), "lms_batch": lms_rec.get("Batch"), "lms_course": lms_rec.get("Course"), "batch_course_code": lms_rec.get("BatchCourseCode"), "cms_course_name": cms_name, "series": row.get("Series"), "functional_area": row.get("Functional Area"), "category_name": row.get("Category"), "roles": assigned_roles }) else: unmatched.append(cms_name) # Step 7: Return response return { "status": "1", "message": "Courses mapped successfully", "companyId": company_id, "vesselType": vessel_type, "dynamic_roles": dynamic_roles, "total_matched": len(matched), "total_unmatched": len(unmatched), "matched_courses": matched, "unmatched_courses": unmatched }