from fastapi import HTTPException
from sqlalchemy.orm import Session
import pandas as pd
import os
from datetime import datetime
from io import BytesIO
from reportlab.lib.pagesizes import A4
from reportlab.lib import colors
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle, Image, PageBreak
from reportlab.lib.units import inch

from app.models.main.corporate_concept_office import TblCorporateConceptOffice
from app.models.main.concept_office import TblConceptOffice
from app.models.main.store_level_staff_needs import TblStoreLevelStaffNeeds
from app.models.main.competitor_analysis_comparison import TblCompetitorAnalysisComparison
from app.models.main.utility import TblUtility
from app.models.main.operating_expenses import TblOperatingExpenses
from app.models.main.summary import TblSummary
from app.models.main.group import TblGroup

from app.api.module_8_report.schema import (
    CorporateConceptOffice,
    ConceptOffice,
    StoreLevelStaffNeeds,
    TrainingTalentManagement,
    Utility,
    OperatingExpenses,
    Summary,
    Module8ReportPreview,
)
from app.dependency.authantication import JWTPayloadSchema
from app.api.summary.schema import SummaryCreate


class Module8ReportService:
    def __init__(self, db: Session, token: JWTPayloadSchema):
        self.db = db
        self.token = token

    async def _fetch_module8_data(self, group_id: int) -> Module8ReportPreview:
        if not group_id:
            raise HTTPException(status_code=400, detail="group_id is required")

        # Check if group exists
        group_exists = self.db.query(TblGroup).filter_by(group_id=group_id).first()
        if not group_exists:
            raise HTTPException(status_code=404, detail="Group ID not found in database")

        def safe_str(v):
            return v if v not in [None, "", "null"] else "N/A"

        def safe_float(v):
            try:
                return float(v or 0)
            except:
                return 0.0

        def safe_int(v):
            try:
                return int(v or 0)
            except:
                return 0

        # --- Fetch all tables ---
        corporate_concept_office = self.db.query(TblCorporateConceptOffice).filter_by(group_id=group_id).all()
        concept_office = self.db.query(TblConceptOffice).filter_by(group_id=group_id).all()
        store_level_staff_raw = self.db.query(TblStoreLevelStaffNeeds).filter_by(group_id=group_id).all()
        training_talent = self.db.query(TblCompetitorAnalysisComparison).filter_by(group_id=group_id).first()
        utilities = self.db.query(TblUtility).filter_by(group_id=group_id).all()
        operating_expenses = self.db.query(TblOperatingExpenses).filter_by(group_id=group_id).all()
        summary = self.db.query(TblSummary).filter_by(group_id=group_id).first()
        
        # Group store level staff by designation
        staff_dict = {}
        for staff in store_level_staff_raw:
            designation = safe_str(staff.designation)
            if designation not in staff_dict:
                staff_dict[designation] = {
                    'store_id': safe_int(staff.store_id),
                    'designation': designation,
                    'average_salary': safe_float(staff.average_salary),
                    'count': 0,
                    'store_formate_type': 'Combined',
                    'format_a_count': 0,
                    'format_b_count': 0,
                    'group_id': safe_int(staff.group_id)
                }
            
            if staff.store_formate_type == "Store Format A":
                staff_dict[designation]['format_a_count'] = safe_int(staff.count)
            elif staff.store_formate_type == "Store Format B":
                staff_dict[designation]['format_b_count'] = safe_int(staff.count)
        
        store_level_staff = [StoreLevelStaffNeeds(**data) for data in staff_dict.values()]

        # --- Map data to schema objects ---
        return Module8ReportPreview(
            group_id=group_id,
            corporate_concept_office=[CorporateConceptOffice(**c.__dict__) for c in corporate_concept_office] or [CorporateConceptOffice()],
            concept_office=[ConceptOffice(**c.__dict__) for c in concept_office] or [ConceptOffice()],
            store_level_staff_needs=store_level_staff or [StoreLevelStaffNeeds()],
            training_talent_management=TrainingTalentManagement(**training_talent.__dict__) if training_talent else TrainingTalentManagement(),
            utilities=[Utility(**u.__dict__) for u in utilities] or [Utility()],
            operating_expenses=[OperatingExpenses(**o.__dict__) for o in operating_expenses] or [OperatingExpenses()],
            summary=Summary(**summary.__dict__) if summary else Summary(),
            last_updated=pd.Timestamp.now()
        )
    
    def _generate_pdf_report(self, data: Module8ReportPreview) -> str:
        """Generate PDF report and return file path"""
        buffer = BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=A4, topMargin=0.5*inch, bottomMargin=0.5*inch)
        story = []
        styles = getSampleStyleSheet()
        
        # Logo
        try:
            logo = Image("TS Logo.png", width=150, height=75)
            logo.hAlign = 'LEFT'
            story.append(logo)
            story.append(Spacer(1, 10))
        except:
            pass  # Continue without logo if file not found
        
        # Title
        story.append(Paragraph("<b>Module 8: Services & Utilities Report</b>", styles['Title']))
        story.append(Spacer(1, 12))
        
        # Corporate Concept Office
        story.append(Paragraph("Corporate Concept Office", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        corp_data = [['Role Category', 'Cost To Company', 'Y1', 'Y2', 'Y3', 'Y4', 'Y5']]
        for corp in data.corporate_concept_office:
            corp_data.append([
                corp.role_category or 'null',
                str(corp.cost_to_company or 0),
                str(corp.y1 or 0),
                str(corp.y2 or 0),
                str(corp.y3 or 0),
                str(corp.y4 or 0),
                str(corp.y5 or 0)
            ])
        
        corp_table = Table(corp_data)
        corp_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 8),
            ('ALIGN', (0, 0), (-1, -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),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        story.append(corp_table)
        story.append(Spacer(1, 12))
        
        # Concept Office
        story.append(Paragraph("Concept Office", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        concept_data = [['Role Category', 'Cost To Company', 'Y1', 'Y2', 'Y3', 'Y4', 'Y5']]
        for concept in data.concept_office:
            concept_data.append([
                concept.role_category or 'null',
                str(concept.cost_to_company or 0),
                str(concept.y1 or 0),
                str(concept.y2 or 0),
                str(concept.y3 or 0),
                str(concept.y4 or 0),
                str(concept.y5 or 0)
            ])
        
        concept_table = Table(concept_data)
        concept_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 8),
            ('ALIGN', (0, 0), (-1, -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),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        story.append(concept_table)
        story.append(Spacer(1, 12))
        
        # Store Level Staff Needs
        normal_style = styles['Normal']
        normal_style.fontSize = 8
        normal_style.leading = 10
        
        # Create centered paragraph style
        centered_style = ParagraphStyle(
            'CenteredStyle', parent=styles['Normal'],
            fontSize=8, leading=10, alignment=1
        )
        
        story.append(Paragraph("Store Level Staff Needs", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        header_cells = [
            Paragraph('Designation', normal_style),
            Paragraph('Average Salary', normal_style),
            Paragraph('Count', normal_style),
            Paragraph('Store Format Type', normal_style),
            Paragraph('Format A Count', normal_style),
            Paragraph('Format B Count', normal_style)
        ]
        
        table_rows = [header_cells]
        for staff in data.store_level_staff_needs:
            row = [
                Paragraph(staff.designation or 'null', normal_style),
                Paragraph(str(staff.average_salary or 0), normal_style),
                Paragraph(str(staff.count or 0), normal_style),
                Paragraph(staff.store_formate_type or 'null', normal_style),
                Paragraph(str(staff.format_a_count or 0), normal_style),
                Paragraph(str(staff.format_b_count or 0), normal_style)
            ]
            table_rows.append(row)
        
        staff_table = Table(table_rows, repeatRows=1)
        staff_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('BOX', (0, 0), (-1, -1), 1, colors.black),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 8),
            ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
            ('FONTSIZE', (0, 1), (-1, -1), 8),
            ('LEFTPADDING', (0, 0), (-1, -1), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        story.append(staff_table)
        story.append(Spacer(1, 12))
        
        # Training Talent Management
        story.append(Paragraph("Training Talent Management", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        training_data = [
            [Paragraph('<b>Name Of Reference Retailer</b>', centered_style), 
             Paragraph(data.training_talent_management.name_of_reference_retailer or 'null', centered_style)],
            [Paragraph('<b>Employee Management Activity</b>', centered_style), 
             Paragraph(data.training_talent_management.employee_management_activity or 'null', centered_style)],
            [Paragraph('<b>Policies Plan Activities</b>', centered_style), 
             Paragraph(data.training_talent_management.policeis_plan_activities or 'null', centered_style)]
        ]
        
        training_table = Table(training_data)
        training_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('BOX', (0, 0), (-1, -1), 1, colors.black),
            ('FONTNAME', (0, 0), (0, -1), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 8),
            ('LEFTPADDING', (0, 0), (-1, -1), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        story.append(training_table)
        story.append(Spacer(1, 12))
        
        # Utilities - Start on new page
        story.append(PageBreak())
        story.append(Paragraph("Utilities", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        header_cells = [
            Paragraph('Utility Name', centered_style),
            Paragraph('Cost Per Month', centered_style),
            Paragraph('Key Usage Parameters', centered_style),
            Paragraph('Remark', centered_style),
            Paragraph('Store Format Type', centered_style)
        ]
        
        table_rows = [header_cells]
        for util in data.utilities:
            row = [
                Paragraph(util.utility_name or 'null', centered_style),
                Paragraph(str(util.cost_per_month or 0), centered_style),
                Paragraph(util.key_usage_parameters or 'null', centered_style),
                Paragraph(util.remark or 'null', centered_style),
                Paragraph(util.store_format_type or 'null', centered_style)
            ]
            table_rows.append(row)
        
        util_table = Table(table_rows, repeatRows=1)
        util_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('BOX', (0, 0), (-1, -1), 1, colors.black),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 8),
            ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
            ('FONTSIZE', (0, 1), (-1, -1), 8),
            ('LEFTPADDING', (0, 0), (-1, -1), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        story.append(util_table)
        story.append(Spacer(1, 12))
        
        # Operating Expenses
        story.append(Paragraph("Operating Expenses", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        header_cells = [
            Paragraph('Operating Expenses Type', centered_style),
            Paragraph('Percentage Of Sales Per Year', centered_style),
            Paragraph('Remark', centered_style),
            Paragraph('Store Format Type', centered_style)
        ]
        
        table_rows = [header_cells]
        for op in data.operating_expenses:
            row = [
                Paragraph(op.operating_expenses_type or 'null', centered_style),
                Paragraph(str(op.percentage_of_sales_per_year or 0), centered_style),
                Paragraph(op.remark or 'null', centered_style),
                Paragraph(op.store_format_type or 'null', centered_style)
            ]
            table_rows.append(row)
        
        op_table = Table(table_rows, repeatRows=1)
        op_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('BOX', (0, 0), (-1, -1), 1, colors.black),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, 0), 8),
            ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
            ('FONTSIZE', (0, 1), (-1, -1), 8),
            ('LEFTPADDING', (0, 0), (-1, -1), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        story.append(op_table)
        story.append(Spacer(1, 12))
        
        # Summary
        story.append(Paragraph("Summary", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        summary_data = [
            [Paragraph('Service Submissions', centered_style), Paragraph(str(data.summary.service_submissions or 'null'), centered_style)],
            [Paragraph('Service Summarise', centered_style), Paragraph(str(data.summary.service_summarise or 'null'), centered_style)]
        ]
        
        summary_table = Table(summary_data)
        summary_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('FONTNAME', (0, 0), (0, -1), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 8),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('LEFTPADDING', (0, 0), (-1, -1), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        story.append(summary_table)
        story.append(Spacer(1, 12))
        
        # Last Updated
        story.append(Paragraph(f"Last Updated: {data.last_updated.strftime('%Y-%m-%d %H:%M:%S')}", styles['Normal']))
        
        doc.build(story)
        buffer.seek(0)
        
        # Save to specified directory
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        filename = f"module8_complete_report_group_{data.group_id}_{timestamp}.pdf"
        filepath = f"uploaded_files/{filename}"
        os.makedirs(os.path.dirname(filepath), exist_ok=True)
        
        with open(filepath, 'wb') as f:
            f.write(buffer.getvalue())
        
        return os.path.join("uploaded_files", filename)
    
    async def generate_and_save_pdf(self, group_id: int) -> str:
        """Generate PDF and save to database, return file path"""
        report_data = await self._fetch_module8_data(group_id)
        file_path = self._generate_pdf_report(report_data)
        self._save_file_path_to_db(group_id, file_path)
        return file_path
    
    def _save_file_path_to_db(self, group_id: int, file_path: str):
        """Save or update file path in summary table with group_id_mod"""
        try:
            group_id_mod = f"g{group_id}_m8"
            print(f"DEBUG: Looking for existing record with group_id_mod: {group_id_mod}")
            existing_summary = self.db.query(TblSummary).filter(TblSummary.group_id_mod == group_id_mod).first()
            
            if existing_summary:
                print(f"DEBUG: Found existing record, updating uploaded_files")
                existing_summary.uploaded_files = file_path
                self.db.commit()
                self.db.refresh(existing_summary)
                print(f"DEBUG: Updated record successfully")
            else:
                print(f"DEBUG: No existing record found, creating new one")
                new_summary = TblSummary(
                    group_id=group_id,
                    group_id_mod=group_id_mod,
                    uploaded_files=file_path
                )
                self.db.add(new_summary)
                self.db.commit()
                self.db.refresh(new_summary)
                print(f"DEBUG: Created new record with summary_id: {new_summary.summary_id}")
        except Exception as e:
            self.db.rollback()
            print(f"ERROR saving file path to database: {e}")
            import traceback
            traceback.print_exc()