import re
from typing import List, Dict, Union, cast
import unicodedata
from fastapi import HTTPException
import requests
import os
import shutil
from fastapi import UploadFile
from fastapi.responses import FileResponse, StreamingResponse
import pandas as pd
from io import BytesIO
from app.api.course.service import UPLOAD_DIR
from app.api.company.service import CompanyService
class SeafarerService:
    def __init__(self):
        self.api_url = "https://cms.marinerskills.com/api/get-seafarer-based-on-vessel"
        self.security_key = "FslKvipEQ3hT2PfdZla00hp"
        self.company_service = CompanyService()

    def get_roles_by_vessel(self, vessel_id: int) -> Dict[str, Union[int, List[str]]]:
        """
        Calls the third-party APIs to retrieve seafarers for a vessel,
        then extracts all distinct Role values.
        """
        try:
            payload = {"VesselID": str(vessel_id), "SecurityKey": self.security_key}
            response = requests.post(self.api_url, json=payload, timeout=10)
            response.raise_for_status()
            data = response.json()
          
            if isinstance(data, list):
                seafarers = data
            elif isinstance(data, dict):
                if "data" in data and isinstance(data["data"], list):
                    seafarers = data["data"]
                else:
                    raise ValueError(f"Unexpected API response format: {data}")
            else:
                raise ValueError("Invalid API response")

         
            roles = [s.get("Role") for s in seafarers if s.get("Role")]
            if not roles:
                raise HTTPException(status_code=404, detail="No roles found for this vesselId")

            unique_roles = sorted(set(roles))

            return {"vesselId": vessel_id, "roles": unique_roles}

        except requests.RequestException as e:
            raise HTTPException(status_code=502, detail=f"External API error: {e}")
        except Exception as e:
            raise HTTPException(status_code=500, detail=str(e))
        
    def get_roles_by_company(self, company_id: int) -> Dict[str, Union[int, List[str]]]:
        """
        Calls third-party APIs to retrieve seafarers for a vessel,
        then extracts all distinct Role values.
        """
        try:
            payload = {"CompanyID": str(company_id), "SecurityKey": self.security_key}
            service = CompanyService()

            company_data = service.get_vessels_by_company(company_id)

            if not company_data:
                raise HTTPException(status_code=404, detail="No vessels found for this company")

            vessel_ids = [
                int(getattr(v, "VesselID", getattr(v, "vesselid", 0)))
                for v in company_data
                if getattr(v, "VesselID", getattr(v, "vesselid", None))
            ]

            all_roles: set[str] = set()

            for vessel_id in vessel_ids:
                try:
                    role_data = self.get_roles_by_vessel(vessel_id)
                    roles = role_data.get("roles", [])

                   
                    if isinstance(roles, list):
                        all_roles.update([str(r) for r in roles])
                    else:
                        # Ignore invalid types
                        print(f"Invalid roles type for vessel {vessel_id}: {type(roles)}")

                except Exception as e:
                    print(f"Error fetching roles for vessel {vessel_id}: {e}")

            if not all_roles:
                raise HTTPException(status_code=404, detail="No roles found for any vessels")

            # Convert set → list before returning
            return {"companyId": company_id, "roles": list(all_roles)}

        except Exception as e:
            raise HTTPException(status_code=500, detail=str(e))

    def generate_excel_old(self, vessel_id: int):
        """
        Generates an Excel file with static + dynamic role columns for a vessel.
        """
       
        role_data = self.get_roles_by_vessel(vessel_id)
        roles = cast(List[str], role_data["roles"])

        static_columns = ["Course Name", "Series", "Category", "Functional Area"]
        all_columns = static_columns + roles

        data = [
            {
                "Course Name": "MSSV-2001-Coping with Stress",
                "Series": "video-series",
                "Category": "Video Series for SIRE 2.0",
                "Functional Area": "SIRE Ch 3: Crew Management",
                **{role: "X" if role in ["Office - Senior Managers", "Other"] else "" for role in roles}
            },
            {
                "Course Name": "MSSV-2002-Managing Fatigue onboard",
                "Series": "video-series",
                "Category": "Video Series for SIRE 2.0",
                "Functional Area": "SIRE Ch 3: Crew Management",
                **{role: "X" for role in roles}
            }
        ]

        df = pd.DataFrame(data, columns=all_columns)
        output = BytesIO()

        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name="Courses")

        output.seek(0)

        headers = {
            "Content-Disposition": f"attachment; filename=vessel_{vessel_id}_courses.xlsx"
        }

        return StreamingResponse(
            output,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            headers=headers
        )
    
    def clean_sheet_name(self, name: str) -> str:
        import re
        # Replace illegal Excel characters with "_"
        name = re.sub(r'[\\/*?\[\]]', '_', name)
        return name[:31]

    def generate_excel(self, company_id: int):

         # 0️⃣ If company already has uploaded Excel → return that file instead of template
        uploaded_file_path = os.path.join(UPLOAD_DIR, f"company_{company_id}.xlsx")

        if os.path.exists(uploaded_file_path):
            return FileResponse(
                uploaded_file_path,
                media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                filename=f"company_{company_id}_courses.xlsx"
            )

        # 1️⃣ Fetch roles
        roles = self.company_service.get_roles(str(company_id))
        all_roles = [r.RoleName for r in roles]

        # 2️⃣ Fetch vessel types
        vessel_response = self.company_service.get_vessel_types(company_id)
        vessel_types = vessel_response.vessel_types

        static_columns = ["Course Name", "Series", "Category", "Functional Area"]
        all_columns = static_columns + all_roles

        template_data = [
            {
                "Course Name": "MSSV-2001-Coping with Stress",
                "Series": "video-series",
                "Category": "Video Series for SIRE 2.0",
                "Functional Area": "SIRE Ch 3: Crew Management",
                **{role: ("X" if role in ["Office - Senior Managers", "Other"] else "") for role in all_roles}
            },
            {
                "Course Name": "MSSV-2002-Managing Fatigue onboard",
                "Series": "video-series",
                "Category": "Video Series for SIRE 2.0",
                "Functional Area": "SIRE Ch 3: Crew Management",
                **{role: "X" for role in all_roles}
            }
        ]

        output = BytesIO()

        with pd.ExcelWriter(output, engine="openpyxl") as writer:

            for vessel_type in vessel_types:

                cleaned_name = self.clean_sheet_name(vessel_type)

                df = pd.DataFrame(template_data, columns=all_columns)

                df.to_excel(writer, index=False, sheet_name=cleaned_name)

        output.seek(0)

        headers = {
            "Content-Disposition": f"attachment; filename=company_{company_id}_courses.xlsx"
        }

        return StreamingResponse(
            output,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            headers=headers
        )

    async def upload_courses_by_vessel(self, vessel_id: int, file: UploadFile):
        """
        Upload Excel file and validate static + dynamic columns.
        Upload will be rejected if columns do not match exactly.
        File will not be saved if validation fails.
        """
        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")

        role_data = self.get_roles_by_vessel(vessel_id)
        dynamic_roles = cast(List[str], role_data["roles"])

        static_columns = ["Course Name", "Series", "Category", "Functional Area"]
        required_columns = static_columns + dynamic_roles

        os.makedirs(UPLOAD_DIR, exist_ok=True)
        temp_path = os.path.join(UPLOAD_DIR, "temp_upload.xlsx")

        # Save temporarily for validation
        try:
            with open(temp_path, "wb") as buffer:
                shutil.copyfileobj(file.file, buffer)
        except Exception as e:
            raise HTTPException(status_code=500, detail=f"Error saving temporary file: {e}")

        try:
            df_raw = pd.read_excel(temp_path, header=None)
        except Exception as e:
            # Remove temporary file if read fails
            if os.path.exists(temp_path):
                os.remove(temp_path)
            raise HTTPException(status_code=400, detail=f"Error reading Excel file: {e}")

        df_raw = df_raw.dropna(how="all")
        df_raw.columns = df_raw.iloc[0]
        df = df_raw.drop(df_raw.index[0])
        df.columns = (
            df.columns.astype(str)
            .str.strip("")
            .str.replace("\n", " ")
            .str.replace("\r", " ")
        )

        missing = [col for col in required_columns if col not in df.columns]
        extra = [col for col in df.columns if col not in required_columns]

        # If columns mismatch, reject upload and remove file
        if missing or extra:
            if os.path.exists(temp_path):
                os.remove(temp_path)
            return {
                "status": "-1",
                "message": (
                    f"Excel upload failed. Columns do not match expected template. "
                    f"Missing columns: {missing if missing else 'None'}, "
                    f"Extra columns: {extra if extra else 'None'}. "
                    f"Expected columns: {required_columns}"
                ),
            }

        # Validation passed → now save as final file
        final_path = os.path.join(UPLOAD_DIR, "Offline dashboard.xlsx")
        os.replace(temp_path, final_path)

        df = df.where(pd.notnull(df), None)

        return {
            "status": "1",
            "message": "Excel file validated successfully",
            "vesselId": vessel_id,
            "roles": dynamic_roles,
            "saved_file_path": final_path,
            "total_rows": len(df),
        }


    async def upload_courses_by_company(self, company_id: int, 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")

        #Fetch roles (must return ALL roles used in Excel)
        role_data = self.company_service.get_roles(company_id)
        dynamic_roles = [r.RoleName for r in role_data]

        #STATIC columns
        static_columns = ["Course Name", "Series", "Category", "Functional Area"]
        required_columns = static_columns + dynamic_roles

        #Save TEMP file
        os.makedirs(UPLOAD_DIR, exist_ok=True)
        temp_path = os.path.join(UPLOAD_DIR, "temp_upload.xlsx")

        try:
            with open(temp_path, "wb") as buffer:
                shutil.copyfileobj(file.file, buffer)
        except Exception as e:
            raise HTTPException(status_code=500, detail=f"Error saving temporary file: {e}")

        #Read ONLY FIRST SHEET
        try:
            df_raw = pd.read_excel(temp_path, sheet_name=0, header=None)
        except Exception as e:
            os.remove(temp_path)
            raise HTTPException(status_code=400, detail=f"Error reading Excel file: {e}")

        #Clean header + data
        df_raw = df_raw.dropna(how="all")
        df_raw.columns = df_raw.iloc[0]
        df = df_raw.drop(df_raw.index[0])

        df.columns = (
            df.columns.astype(str)
            .str.strip()
            .str.replace("\n", " ")
            .str.replace("\r", " ")
        )
        #Validate only FIRST sheet
        missing = [col for col in required_columns if col not in df.columns]
        extra = [col for col in df.columns if col not in required_columns]

        if missing or extra:
            os.remove(temp_path)
            return {
                "status": "-1",
                "message": (
                    f"Excel upload failed. Columns do not match expected template. "
                    f"Missing columns: {missing or 'None'}, "
                    f"Extra columns: {extra or 'None'}. "
                    f"Expected columns: {required_columns}"
                )
            }

        # 7️⃣ Save final file
        final_path = os.path.join(UPLOAD_DIR, f"company_{company_id}.xlsx")
        try:
            if os.path.exists(final_path):
                os.remove(final_path)
        except Exception as e:
            # If file is open in Excel
            raise HTTPException(
                status_code=500,
                detail="Cannot overwrite existing file. Please close the Excel file if it's open."
            )

        # Now move the new file safely
        shutil.move(temp_path, final_path)

        return {
            "status": "1",
            "message": "Excel file validated successfully",
            "companyId": company_id,
            "roles": dynamic_roles,
            "saved_file_path": final_path,
            "total_rows": len(df),
        }