from fastapi.responses import FileResponse
from fastapi import HTTPException
from openpyxl import Workbook
from pathlib import Path
from typing import List, Optional
from openpyxl.styles import Font, Alignment
from openpyxl.utils import get_column_letter
from sqlalchemy import func
from app.api.reports.schema import Crew_report_by_status, Crew_report_by_type, crew_report
from app.models.main.vessel_list import TblVesselList
from app.models.main.company_list import TblCompanyList 
from app.models.main.seafarer import TblSeafarers
from app.models.main.seafarer_role import TblSeafarerRole
from app.models.main.enrollment import TblEnrollment
from app.models.main.enrolledseafarerinfo import TblEnrolledSeafarerInfo
from sqlalchemy.orm import Session
from reportlab.platypus import SimpleDocTemplate, Table,TableStyle,Paragraph, Image, Spacer
from reportlab.lib.pagesizes import A4,landscape
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib.enums import TA_CENTER
from reportlab.lib import colors
from reportlab.lib.units import inch

        
def generate_excel_report(title: str,subtitle: str,columns: List[str],rows: List[list],file_name: str):

    wb = Workbook()
    ws = wb.active
    ws.title = "Report"

    ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=len(columns))
    ws["A1"] = title
    ws["A1"].font = Font(bold=True, size=14)
    ws["A1"].alignment = Alignment(horizontal="center")

    ws.merge_cells(start_row=2, start_column=1, end_row=2, end_column=len(columns))
    ws["A2"] = subtitle
    ws["A2"].font = Font(bold=True, size=12)
    ws["A2"].alignment = Alignment(horizontal="center")


    ws.append(columns)
    header_row = 3
    for col in range(1, len(columns) + 1):
        cell = ws.cell(row=header_row, column=col)
        cell.font = Font(bold=True)
        cell.alignment = Alignment(horizontal="center", vertical="center")

    for row in rows:
        ws.append(row)

    for col in ws.columns:
        max_length = max(len(str(cell.value)) if cell.value else 0 for cell in col)
        ws.column_dimensions[get_column_letter(col[0].column)].width = max_length + 4

    ws.freeze_panes = "A4"

    report_path = Path(f"reports/{file_name}.xlsx")
    report_path.parent.mkdir(exist_ok=True)
    wb.save(report_path)

    return report_path

def generate_pdf_report(company_name: str,total: str,headers: list[str],rows: list[list],logo_path: str | Path | None = None):
    
    output_path = Path("reports/Crew_Report.pdf")
    output_path.parent.mkdir(parents=True, exist_ok=True)

    page_width, page_height = landscape(A4)

    doc = SimpleDocTemplate(
        str(output_path),
        pagesize=landscape(A4),
        leftMargin=30,
        rightMargin=30,
        topMargin=30,
        bottomMargin=30,
    )

    usable_width = page_width - doc.leftMargin - doc.rightMargin

    elements = []
    styles = getSampleStyleSheet()

    if logo_path and Path(logo_path).exists():
        logo = Image(str(logo_path), width=1.3 * inch, height=1.1 * inch)
    else:
        logo = Spacer(1, 1)

    title_style = ParagraphStyle(
        name="Title",
        fontSize=16,
        alignment=1,
        fontName="Helvetica-Bold",
    )

    subtitle_style = ParagraphStyle(
        name="Subtitle",
        fontSize=11,
        alignment=1,
    )

    title_block = [
        Paragraph(company_name, title_style),
        Spacer(1, 6),
        Paragraph(total, subtitle_style),
    ]

    header_table = Table(
        [[logo, title_block]],
        colWidths=[1.6 * inch, usable_width - 1.6 * inch],
    )

    header_table.setStyle(TableStyle([
        ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
        ("ALIGN", (1, 0), (1, 0), "CENTER"),
        ("BOTTOMPADDING", (0, 0), (-1, -1), 14),
    ]))

    elements.append(header_table)
    elements.append(Spacer(1, 16))

    table_data = [headers] + rows

    col_widths = [
        usable_width * 0.04,  # NO  
        usable_width * 0.18,  # Crew Name
        usable_width * 0.08,  # Crew ID
        usable_width * 0.14,  # Crew Role
        usable_width * 0.16,  # Last Accessed Date
        usable_width * 0.16,  # Vessel Name
        usable_width * 0.12,  # IMO
        usable_width * 0.14,  # Onboard Date
        ]

    table = Table(table_data, colWidths=col_widths, repeatRows=1)

    table.setStyle(TableStyle([
        # Header
        ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#cfe2ff")),
        ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
        ("ALIGN", (0, 0), (-1, 0), "CENTER"),
        ("VALIGN", (0, 0), (-1, 0), "MIDDLE"),

        # Body alignment
        ("ALIGN", (0, 1), (0, -1), "CENTER"),   # NO
        ("ALIGN", (2, 1), (2, -1), "CENTER"),   # Crew ID
        ("ALIGN", (6, 1), (6, -1), "CENTER"),   # IMO
        ("ALIGN", (7, 1), (7, -1), "CENTER"),   # Date
        ("ALIGN", (1, 1), (5, -1), "LEFT"),

        # Word wrap
        ("WORDWRAP", (0, 0), (-1, -1)),

        # Alternate row colors
        ("ROWBACKGROUNDS", (0, 1), (-1, -1),
        [colors.white, colors.HexColor("#e7f0ff")]),

        # Borders
        ("GRID", (0, 0), (-1, -1), 0.5, colors.black),

        # Padding
        ("TOPPADDING", (0, 0), (-1, -1), 6),
        ("BOTTOMPADDING", (0, 0), (-1, -1), 6),
    ]))


    elements.append(table)
    doc.build(elements)

    return output_path        

def generate_pdf_report2(company_name: str,total: str,headers: list[str],rows: list[list],logo_path: str | Path | None = None):
    
    output_path = Path("reports/Crew_Report.pdf")
    output_path.parent.mkdir(parents=True, exist_ok=True)

    page_width, page_height = landscape(A4)

    doc = SimpleDocTemplate(
        str(output_path),
        pagesize=landscape(A4),
        leftMargin=30,
        rightMargin=30,
        topMargin=30,
        bottomMargin=30,
    )

    usable_width = page_width - doc.leftMargin - doc.rightMargin

    elements = []
    styles = getSampleStyleSheet()

    if logo_path and Path(logo_path).exists():
        logo = Image(str(logo_path), width=1.3 * inch, height=1.1 * inch)
    else:
        logo = Spacer(1, 1)

    title_style = ParagraphStyle(
        name="Title",
        fontSize=16,
        alignment=1,
        fontName="Helvetica-Bold",
    )

    subtitle_style = ParagraphStyle(
        name="Subtitle",
        fontSize=11,
        alignment=1,
    )

    title_block = [
        Paragraph(company_name, title_style),
        Spacer(1, 6),
        Paragraph(total, subtitle_style),
    ]

    header_table = Table(
        [[logo, title_block]],
        colWidths=[1.6 * inch, usable_width - 1.6 * inch],
    )

    header_table.setStyle(TableStyle([
        ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),
        ("ALIGN", (1, 0), (1, 0), "CENTER"),
        ("BOTTOMPADDING", (0, 0), (-1, -1), 14),
    ]))

    elements.append(header_table)
    elements.append(Spacer(1, 16))

    table_data = [headers] + rows

    col_widths = [
        usable_width * 0.04,  
        usable_width * 0.18,  
        usable_width * 0.07,  
        usable_width * 0.12,  
        usable_width * 0.08,  
        usable_width * 0.15,  
        usable_width * 0.14,  
        usable_width * 0.10,  
        usable_width * 0.15,  
    ]

    table = Table(table_data, colWidths=col_widths, repeatRows=1)

    table.setStyle(TableStyle([
        # Header
        ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#cfe2ff")),
        ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
        ("ALIGN", (0, 0), (-1, 0), "CENTER"),
        ("VALIGN", (0, 0), (-1, 0), "MIDDLE"),

        # Body alignment
        ("ALIGN", (0, 1), (0, -1), "CENTER"),   # No
        ("ALIGN", (2, 1), (2, -1), "CENTER"),   # Crew Id
        ("ALIGN", (4, 1), (4, -1), "CENTER"),   # Status
        ("ALIGN", (7, 1), (7, -1), "CENTER"),   # IMO
        ("ALIGN", (8, 1), (8, -1), "CENTER"),   # Onboard Date

        ("ALIGN", (1, 1), (6, -1), "LEFT"),     # Text columns

        # Word wrap
        ("WORDWRAP", (0, 0), (-1, -1)),

        # Alternate row colors
        ("ROWBACKGROUNDS", (0, 1), (-1, -1),
            [colors.white, colors.HexColor("#e7f0ff")]),

        # Borders
        ("GRID", (0, 0), (-1, -1), 0.5, colors.black),

        # Padding
        ("TOPPADDING", (0, 0), (-1, -1), 6),
        ("BOTTOMPADDING", (0, 0), (-1, -1), 6),
    ]))


    elements.append(table)
    doc.build(elements)

    return output_path        


class ReportService:
    def __init__(self, db : Session, token = dict ):
        self.db = db
        self.token = token 
        
        
    async def get_all_vessal_by_company(self, company_id: int):
        get_all_data = self.db.query(TblVesselList).filter(TblVesselList.company_id == company_id).all()
        return get_all_data

    async def create_excel_report(self, company_id: int):
        get_all_data = self.db.query(TblVesselList).filter(TblVesselList.company_id == company_id).all()
        get_company_name = self.db.query(TblCompanyList).filter(TblCompanyList.company_id == company_id).first()
        
        title = f"Company Name : {get_company_name.company_name}"
        subtitle = f"Total Vessels : {len(get_all_data)}"
        
        headers = ["NO", "Vessel Name", "Vessel Type", "IMO"]
        rows = [
        [idx, vessel.vessel_name, vessel.vessel_type, vessel.imo]
        for idx, vessel in enumerate(get_all_data, start=1)
        ]
        
        excel_path = generate_excel_report(title, subtitle, headers, rows, "Vessel_Report")
        
        return FileResponse(
            path = excel_path,
            media_type = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            filename = "Vessel_Report.xlsx"
        )

    async def generate_vessel_pdf(self, company_id : int):
        get_all_data = self.db.query(TblVesselList).filter(TblVesselList.company_id == company_id).all()
        get_company_name = self.db.query(TblCompanyList).filter(TblCompanyList.company_id == company_id).first()
        file_path = Path("reports/Vessel_Report.pdf")
        file_path.parent.mkdir(exist_ok=True)

        doc = SimpleDocTemplate(
            str(file_path),
            pagesize=A4,
            rightMargin=30,
            leftMargin=30,
            topMargin=30,
            bottomMargin=30
        )

        styles = getSampleStyleSheet()
        elements = []

        logo = Image("app/static/marinerskills_logo.png", width=60, height=60)

        title_style = ParagraphStyle(
            "TitleStyle",
            parent=styles["Title"],
            alignment=TA_CENTER,
            fontSize=16,
            spaceAfter=6
        )

        subtitle_style = ParagraphStyle(
            "SubtitleStyle",
            parent=styles["Normal"],
            alignment=TA_CENTER,
            fontSize=11
        )

        header_table = Table(
            [[
                logo,
                Paragraph(f"<b>{get_company_name.company_name}</b>", title_style)
            ]],
            colWidths=[80, 400]
        )

        header_table.setStyle(TableStyle([
            ("VALIGN", (0, 0), (0, 0), "MIDDLE"),
            ("ALIGN", (1, 0), (1, 0), "CENTER"),
            ("BOX", (0, 0), (-1, -1), 0, colors.white)
        ]))

        elements.append(header_table)
        elements.append(Spacer(1, 8))
        elements.append(
            Paragraph(f"<b>Total Vessels:</b> {len(get_all_data)}", subtitle_style)
        )
        elements.append(Spacer(1, 20))

        table_data = [
            ["SNO", "VESSEL NAME", "VESSEL TYPE", "IMO NO"]
        ]

        for i, vessel in enumerate(get_all_data, start=1):
            table_data.append([
                i,
                vessel.vessel_name,
                vessel.vessel_type,
                vessel.imo
            ])

        table = Table(
            table_data,
            colWidths=[50, 170, 170, 120]
        )

       
        table.setStyle(TableStyle([
            
            ("BACKGROUND", (0, 0), (-1, 0), colors.HexColor("#cfe2ff")),
            ("FONTNAME", (0, 0), (-1, 0), "Helvetica-Bold"),
            ("ALIGN", (0, 0), (-1, 0), "CENTER"),
            
            ("GRID", (0, 0), (-1, -1), 0.8, colors.red),

            ("ALIGN", (0, 1), (0, -1), "CENTER"),
            ("ALIGN", (3, 1), (3, -1), "CENTER"),
            ("VALIGN", (0, 0), (-1, -1), "MIDDLE"),

            ("LEFTPADDING", (0, 0), (-1, -1), 6),
            ("RIGHTPADDING", (0, 0), (-1, -1), 6),
            ("TOPPADDING", (0, 0), (-1, -1), 6),
            ("BOTTOMPADDING", (0, 0), (-1, -1), 6),
        ]))

        for row in range(1, len(table_data)):
            if row % 2 == 0:
                table.setStyle([
                    ("BACKGROUND", (0, row), (-1, row), colors.HexColor("#e7f1ff"))
                ])

        elements.append(table)

        doc.build(elements)

        return FileResponse(
            file_path,
            media_type="application/pdf",
            filename="Vessel_Report.pdf"
        )
        
    async def get_all_vessel_by_company(self, company_id : int):
        vessel = self.db.query(TblVesselList).filter(TblVesselList.company_id == company_id).all()
        return vessel
        
    async def get_crew_by_data(self, company_id: int):
        
        last_accessed_subquery = (
            self.db.query(
                TblEnrollment.seafarer_id.label("seafarer_id"),
                func.max(TblEnrolledSeafarerInfo.last_accessed).label("last_accessed")
            )
            .join(
                TblEnrolledSeafarerInfo,
                TblEnrolledSeafarerInfo.enrollment_id == TblEnrollment.enrollment_id
            )
            .group_by(TblEnrollment.seafarer_id)
            .subquery()
        )

        query = (
            self.db.query(
                TblSeafarers.seafarer_id,
                TblSeafarers.first_name,
                TblSeafarers.middle_name,
                TblSeafarers.last_name,
                TblSeafarers.onboard_date,
                TblVesselList.vessel_id,
                TblVesselList.vessel_name,
                TblVesselList.imo,
                TblSeafarerRole.sf_role_name,
                last_accessed_subquery.c.last_accessed
            )
            .join(TblVesselList, TblVesselList.vessel_id == TblSeafarers.vessel_id)
            .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblSeafarers.sf_role_id)
            .outerjoin(
                last_accessed_subquery,
                last_accessed_subquery.c.seafarer_id == TblSeafarers.seafarer_id
            )
            .filter(TblVesselList.company_id == company_id)
        )

        results = query.order_by(
            TblVesselList.vessel_id
        ).all()

        if not results:
            raise HTTPException(
                status_code=404,
                detail="No crew found for selected vessels"
            )
        response = []
        for row in results:
            response.append({
                "crew_id": row.seafarer_id,
                "crew_name": " ".join(
                    filter(None, [row.first_name, row.middle_name, row.last_name])
                ),
                "crew_role": row.sf_role_name,
                "vessel_id" : row.vessel_id,
                "vessel_name": row.vessel_name,
                "imo": row.imo,
                "onboard_date": row.onboard_date,
                "last_accessed_date": row.last_accessed if row.last_accessed else "-"
            })

        return response
        
    async def get_all_vessel_name_by_company(self, company_id : int):
        vessel = self.db.query(TblVesselList).filter(TblVesselList.company_id == company_id).all()
        vessel_id_and_name = []
        for ves in vessel:
            vessel_id_and_name.append({
                "vessel_id" : ves.vessel_id,
                "vessel_name" : ves.vessel_name
            })
        return vessel_id_and_name
    
    async def crew_report_excel_by_vessel(self, request : crew_report):
        company = self.db.query(TblCompanyList).filter(TblCompanyList.company_id == request.company_id).first()
        
        last_accessed_subquery = (
            self.db.query(
                TblEnrollment.seafarer_id.label("seafarer_id"),
                func.max(TblEnrolledSeafarerInfo.last_accessed).label("last_accessed")
            )
            .join(
                TblEnrolledSeafarerInfo,
                TblEnrolledSeafarerInfo.enrollment_id == TblEnrollment.enrollment_id
            )
            .group_by(TblEnrollment.seafarer_id)
            .subquery()
        )
        query = (
            self.db.query(
                TblSeafarers.seafarer_id,
                TblSeafarers.first_name,
                TblSeafarers.middle_name,
                TblSeafarers.last_name,
                TblSeafarers.onboard_date,
                TblVesselList.vessel_name,
                TblVesselList.imo,
                TblSeafarerRole.sf_role_name,
                last_accessed_subquery.c.last_accessed 
            )
            .join(TblVesselList, TblVesselList.vessel_id == TblSeafarers.vessel_id)
            .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblSeafarers.sf_role_id)
            .outerjoin(
                last_accessed_subquery,
                last_accessed_subquery.c.seafarer_id == TblSeafarers.seafarer_id
            )
            .filter(TblVesselList.company_id == request.company_id)
        )
        if len(request.vessel_id) > 0:
            query = query.filter(TblSeafarers.vessel_id.in_(request.vessel_id))

        results = query.order_by(
            TblVesselList.vessel_id
        ).all()

        headers = ["NO", "Crew Name", "Crew Id", "Crew Role", "Last Accessed Date", "Vessel Name", "IMO", "Onboard Date"]

        title = f"Company Name : {company.company_name}"
        subtitle = f"Total Crew : {len(results)}"

        rows = []

        for idx, row in enumerate(results, start=1):
            full_name = " ".join(
                filter(None, [row.first_name, row.middle_name, row.last_name])
            )

            rows.append([
                idx,
                full_name,
                row.seafarer_id,
                row.sf_role_name,
                row.last_accessed if row.last_accessed else "-",
                row.vessel_name,
                row.imo,
                row.onboard_date
            ])

        excel_path = generate_excel_report(
            title=title,
            subtitle=subtitle,
            columns=headers,
            rows=rows,
            file_name="Crew_Report"
        )

        return FileResponse(
            path=excel_path,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            filename=f"{company.company_name}Crew_Report_vessel.xlsx"
        )
        
    async def crew_report_pdf_by_vessel(self, request : crew_report):
        
        company = self.db.query(TblCompanyList).filter(TblCompanyList.company_id == request.company_id).first()
        last_accessed_subquery = (
            self.db.query(
                TblEnrollment.seafarer_id.label("seafarer_id"),
                func.max(TblEnrolledSeafarerInfo.last_accessed).label("last_accessed")
            )
            .join(
                TblEnrolledSeafarerInfo,
                TblEnrolledSeafarerInfo.enrollment_id == TblEnrollment.enrollment_id
            )
            .group_by(TblEnrollment.seafarer_id)
            .subquery()
        )
        query = (
            self.db.query(
                TblSeafarers.seafarer_id,
                TblSeafarers.first_name,
                TblSeafarers.middle_name,
                TblSeafarers.last_name,
                TblSeafarers.onboard_date,
                TblVesselList.vessel_name,
                TblVesselList.imo,
                TblSeafarerRole.sf_role_name,
                last_accessed_subquery.c.last_accessed
            )
            .join(TblVesselList, TblVesselList.vessel_id == TblSeafarers.vessel_id)
            .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblSeafarers.sf_role_id)
            .outerjoin(
                last_accessed_subquery,
                last_accessed_subquery.c.seafarer_id == TblSeafarers.seafarer_id
            )
            .filter(TblVesselList.company_id == request.company_id)
        )
        if len(request.vessel_id) > 0:
            query = query.filter(TblSeafarers.vessel_id.in_(request.vessel_id))

        results = query.order_by(TblVesselList.vessel_id).all()

        headers = ["NO", "Crew Name", "Crew Id", "Crew Role", "Last Accessed Date", "Vessel Name", "IMO", "Onboard Date"]

        company_name = company.company_name
        total_crew = f"Total Crew : {len(results)}"

        rows = []

        for idx, row in enumerate(results, start=1):
            full_name = " ".join(
                filter(None, [row.first_name, row.middle_name, row.last_name])
            )

            rows.append([
                idx,
                full_name,
                row.seafarer_id,
                row.sf_role_name,
                row.last_accessed if row.last_accessed else "-",
                row.vessel_name,
                row.imo,
                row.onboard_date
            ])
        logo_path = "app/static/marinerskills_logo.png"
        pdf_file = generate_pdf_report(company_name,total_crew,headers,rows,logo_path)
        return FileResponse(
            pdf_file,
            media_type="application/pdf",
            filename=f"{company.company_name}Crew_Report_vessel.pdf"
        )
        
    async def get_all_seafarer_by_vesseltype(self, company_id: int):
        
        company = self.db.query(TblCompanyList).filter(TblCompanyList.company_id == company_id).first()
        if not company:
            raise HTTPException(status_code=404, detail="Company not found")

        last_accessed_subquery = (
            self.db.query(
                TblEnrollment.seafarer_id.label("seafarer_id"),
                func.max(TblEnrolledSeafarerInfo.last_accessed).label("last_accessed")
            )
            .join(
                TblEnrolledSeafarerInfo,
                TblEnrolledSeafarerInfo.enrollment_id == TblEnrollment.enrollment_id
            )
            .group_by(TblEnrollment.seafarer_id)
            .subquery()
        )
        
        query = (
            self.db.query(
                TblSeafarers.seafarer_id,
                TblSeafarers.first_name,
                TblSeafarers.middle_name,
                TblSeafarers.last_name,
                TblSeafarers.onboard_date,
                TblVesselList.vessel_id, 
                TblVesselList.vessel_name,
                TblVesselList.vessel_type,
                TblVesselList.imo,
                TblSeafarerRole.sf_role_name,
                last_accessed_subquery.c.last_accessed
            )
            .join(TblVesselList, TblVesselList.vessel_id == TblSeafarers.vessel_id)
            .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblSeafarers.sf_role_id)
            .outerjoin(
                last_accessed_subquery,
                last_accessed_subquery.c.seafarer_id == TblSeafarers.seafarer_id
            )
            .filter(TblVesselList.company_id == company_id)
        )
        
        results = query.order_by(TblVesselList.vessel_id).all()

        if not results:
            raise HTTPException(
                status_code=404,
                detail="No seafarers found for this vessel type"
            )

        # Response formatting
        data = []
        for idx, row in enumerate(results, start=1):
            full_name = " ".join(
                filter(None, [row.first_name, row.middle_name, row.last_name])
            )

            data.append({
                "no": idx,
                "crew_name": full_name,
                "crew_id": row.seafarer_id,
                "crew_role": row.sf_role_name,
                "last_accessed_date": row.last_accessed if row.last_accessed else "-",
                "vessel_id" : row.vessel_id, 
                "vessel_name" : row.vessel_name,
                "vessel_type": row.vessel_type,
                "imo": row.imo,
                "onboard_date": row.onboard_date
            })

        return data
    
    async def generate_seafarer_excel_by_vesseltype(self, request : Crew_report_by_type):
        
        company = self.db.query(TblCompanyList).filter(TblCompanyList.company_id == request.company_id).first()
        if not company:
            raise HTTPException(status_code=404, detail="Company not found")

        last_accessed_subquery = (
            self.db.query(
                TblEnrollment.seafarer_id.label("seafarer_id"),
                func.max(TblEnrolledSeafarerInfo.last_accessed).label("last_accessed")
            )
            .join(
                TblEnrolledSeafarerInfo,
                TblEnrolledSeafarerInfo.enrollment_id == TblEnrollment.enrollment_id
            )
            .group_by(TblEnrollment.seafarer_id)
            .subquery()
        )
        
        query = (
            self.db.query(
                TblSeafarers.seafarer_id,
                TblSeafarers.first_name,
                TblSeafarers.middle_name,
                TblSeafarers.last_name,
                TblSeafarers.onboard_date,
                TblVesselList.vessel_id, 
                TblVesselList.vessel_name,
                TblVesselList.vessel_type,
                TblVesselList.imo,
                TblSeafarerRole.sf_role_name,
                last_accessed_subquery.c.last_accessed
            )
            .join(TblVesselList, TblVesselList.vessel_id == TblSeafarers.vessel_id)
            .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblSeafarers.sf_role_id)
            .outerjoin(
                last_accessed_subquery,
                last_accessed_subquery.c.seafarer_id == TblSeafarers.seafarer_id
            )
            .filter(TblVesselList.company_id == request.company_id)
        )
        if request.vessel_type:
            query = query.filter(TblVesselList.vessel_type.in_(request.vessel_type))


        results = query.order_by(TblVesselList.vessel_id).all()

        if not results:
            raise HTTPException(
                status_code=404,
                detail="No seafarers found for this vessel type"
            )

        # Response formatting
        datas = []
        for idx, row in enumerate(results, start=1):
            full_name = " ".join(
                filter(None, [row.first_name, row.middle_name, row.last_name])
            )

            datas.append({
                "no": idx,
                "crew_name": full_name,
                "crew_id": row.seafarer_id,
                "crew_role": row.sf_role_name,
                "last_accessed_date": row.last_accessed if row.last_accessed else "-",
                "vessel_id" : row.vessel_id, 
                "vessel_name" : row.vessel_name,
                "vessel_type": row.vessel_type,
                "imo": row.imo,
                "onboard_date": row.onboard_date
            })

        
        title = f"Company Name : {company.company_name}"
        subtitle = f"Total Crew : {len(results)}"
        
        header = ["No", "Crew Name", "Crew Id", "Crew Role", "Last Accessed Date", "Vessel Name", "IMO", "Onboard Date"]
        
        data = []
        for row in datas:
            data.append([
                row["no"],row["crew_name"],row["crew_id"],row["crew_role"],row["last_accessed_date"],row["vessel_name"],row["imo"],row["onboard_date"]
            ])
        
        excel_path = generate_excel_report(
            title=title,
            subtitle=subtitle,
            columns=header,
            rows=data,
            file_name=f"{company.company_name}vessel_type_Crew_Report"
        )

        return FileResponse(
            path=excel_path,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            filename=f"{company.company_name}Crew_Report_vessel_type.xlsx"
            
        )
        
    async def generate_seafarer_pdf_by_vesseltype(self,request : Crew_report_by_type):
        company = self.db.query(TblCompanyList).filter(TblCompanyList.company_id == request.company_id).first()
        if not company:
            raise HTTPException(status_code=404, detail="Company not found")

        last_accessed_subquery = (
            self.db.query(
                TblEnrollment.seafarer_id.label("seafarer_id"),
                func.max(TblEnrolledSeafarerInfo.last_accessed).label("last_accessed")
            )
            .join(
                TblEnrolledSeafarerInfo,
                TblEnrolledSeafarerInfo.enrollment_id == TblEnrollment.enrollment_id
            )
            .group_by(TblEnrollment.seafarer_id)
            .subquery()
        )
        
        query = (
            self.db.query(
                TblSeafarers.seafarer_id,
                TblSeafarers.first_name,
                TblSeafarers.middle_name,
                TblSeafarers.last_name,
                TblSeafarers.onboard_date,
                TblVesselList.vessel_id, 
                TblVesselList.vessel_name,
                TblVesselList.vessel_type,
                TblVesselList.imo,
                TblSeafarerRole.sf_role_name,
                last_accessed_subquery.c.last_accessed
            )
            .join(TblVesselList, TblVesselList.vessel_id == TblSeafarers.vessel_id)
            .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblSeafarers.sf_role_id)
            .outerjoin(
                last_accessed_subquery,
                last_accessed_subquery.c.seafarer_id == TblSeafarers.seafarer_id
            )
            .filter(TblVesselList.company_id == request.company_id)
        )
        if request.vessel_type:
            query = query.filter(TblVesselList.vessel_type.in_(request.vessel_type))


        results = query.order_by(TblVesselList.vessel_id).all()

        if not results:
            raise HTTPException(
                status_code=404,
                detail="No seafarers found for this vessel type"
            )

        # Response formatting
        datas = []
        for idx, row in enumerate(results, start=1):
            full_name = " ".join(
                filter(None, [row.first_name, row.middle_name, row.last_name])
            )

            datas.append({
                "no": idx,
                "crew_name": full_name,
                "crew_id": row.seafarer_id,
                "crew_role": row.sf_role_name,
                "last_accessed_date": row.last_accessed if row.last_accessed else "-",
                "vessel_id" : row.vessel_id, 
                "vessel_name" : row.vessel_name,
                "vessel_type": row.vessel_type,
                "imo": row.imo,
                "onboard_date": row.onboard_date
            })
       
        company_name = company.company_name
        total_crew = f"Total Crew : {len(results)}"
        
        header = ["No", "Crew Name", "Crew Id", "Crew Role", "Last Accessed Date", "Vessel Name", "IMO", "Onboard Date"]
        
        data = []
        for row in datas:
            data.append([
                row["no"],row["crew_name"],row["crew_id"],row["crew_role"],row["last_accessed_date"],row["vessel_name"],row["imo"],row["onboard_date"]
            ])
        
        logo_path = "app/static/marinerskills_logo.png"
        pdf_file = generate_pdf_report(company_name,total_crew,header,data,logo_path)
        return FileResponse(
            pdf_file,
            media_type="application/pdf",
            filename=f"{company.company_name}Crew_Report_vessel_type.pdf"
        )
        
    async def get_all_vessel_type_by_company(self, company_id : int):
        vessel_types = (
            self.db.query(TblVesselList.vessel_type)
            .filter(TblVesselList.company_id == company_id)
            .distinct()
            .all()
        )

        return [
            {
                "vessel_id" : idx,
                "vessel_type_name": vt.vessel_type
            }
            for idx, vt in enumerate(vessel_types,start=1)
        ]
    
    async def get_all_seafarer_by_status(self, company_id: int, status : Optional[int] =  None):
        
        last_accessed_subquery = (
            self.db.query(
                TblEnrollment.seafarer_id.label("seafarer_id"),
                func.max(TblEnrolledSeafarerInfo.last_accessed).label("last_accessed")
            )
            .join(
                TblEnrolledSeafarerInfo,
                TblEnrolledSeafarerInfo.enrollment_id == TblEnrollment.enrollment_id
            )
            .group_by(TblEnrollment.seafarer_id)
            .subquery()
        )
        
        query = ( self.db.query(
            TblSeafarers.seafarer_id,
            TblSeafarers.first_name,
            TblSeafarers.middle_name,
            TblSeafarers.last_name,
            TblSeafarers.onboard_date,
            TblSeafarers.status,
            TblVesselList.vessel_name,
            TblVesselList.imo,
            TblSeafarerRole.sf_role_name,
            last_accessed_subquery.c.last_accessed     
        )
            .join(TblVesselList, TblVesselList.vessel_id == TblSeafarers.vessel_id)
            .join(TblSeafarerRole, TblSeafarerRole.sf_role_id == TblSeafarers.sf_role_id)
            .outerjoin(
                last_accessed_subquery,
                last_accessed_subquery.c.seafarer_id == TblSeafarers.seafarer_id
            )
            .filter(TblVesselList.company_id == company_id)
        )
        if status:
            if status == 1:
                status = "Active"
            elif status == 2:
                status = "Inactive"
            query = query.filter(TblSeafarers.status == status)
            
        results = query.order_by(TblVesselList.vessel_id).all()
        
        response = []
        for idx, row in enumerate(results, start=1):
            full_name = " ".join(filter(None, [row.first_name, row.middle_name, row.last_name]))
            
            response.append({
                "no":idx,
                "crew_name":full_name,
                "crew_id": row.seafarer_id,
                "crew_role":row.sf_role_name,
                "status":row.status,
                "last_accessed_date":row.last_accessed if row.last_accessed else "-",
                "vessel_name" : row.vessel_name,
                "imo":row.imo,
                "onboard_date":row.onboard_date        
            })
            
        return response
    
    async def generate_seafarer_excel_by_status(self, request : Crew_report_by_status):
        company_id = request.company_id
        status = request.status
        company = self.db.query(TblCompanyList).filter(TblCompanyList.company_id == company_id).first()
        if not company:
            raise HTTPException(status_code=404, detail="Company Not Found")
        
        rows = await self.get_all_seafarer_by_status(company_id, status)
        title = f"Company Name : {company.company_name}"
        subtitle = f"Total Crew : {len(rows)}"
        
        header = ["No", "Crew Name", "Crew Id", "Crew Role", "Status", "Last Accessed Date", "Vessel Name", "IMO", "Onboard Date"]
        
        data=[]
        for row in rows:
            data.append([
                row["no"],row["crew_name"],row["crew_id"],row["crew_role"],row["status"],row["last_accessed_date"],row["vessel_name"],row["imo"],row["onboard_date"]
            ])
            
        excel_path = generate_excel_report(title, subtitle, header, data, f"{company.company_name}_Seafarer_Status_Report")
        
        return FileResponse(
            path=excel_path,
            media_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
            filename=f"{company.company_name}_Seafarer_Status_Report.xlsx"
        )
        
    async def generate_seafarer_pdf_by_status(self, request : Crew_report_by_status):
        company_id = request.company_id
        status = request.status
        company = self.db.query(TblCompanyList).filter(TblCompanyList.company_id == company_id).first()
        if not company:
            raise HTTPException(status_code=404, detail="Company Not Found")
        
        rows = await self.get_all_seafarer_by_status(company_id, status)
        
        company_name = company.company_name
        total_crew = f"Total Crew : {len(rows)}"
        
        header = ["No", "Crew Name", "Crew Id", "Crew Role", "Status", "Last Accessed Date", "Vessel Name", "IMO", "Onboard Date"]
        
        data =[]
        for row in rows:
            data.append([
                row["no"],row["crew_name"],row["crew_id"],row["crew_role"],row["status"],row["last_accessed_date"],row["vessel_name"],row["imo"],row["onboard_date"]
            ])
            
        logo_path = "app/static/marinerskills_logo.png"
        pdf_file = generate_pdf_report2(company_name,total_crew,header,data,logo_path)
        
        return FileResponse(
            pdf_file,
            media_type="application/pdf",
            filename=f"{company.company_name}_Seafarer_Status_Report.pdf"
        )
        
    async def seafarer_status_dropdown(self):
        status = [{"status_id" : 1 , "status_resposnse": "Active"},{"status_id" :  2 , "status_resposnse": "Inactive"}]
        return status