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
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()
        
        # 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 = [['Corporate ID', 'Role Category', 'Cost To Company', 'Y1', 'Y2', 'Y3', 'Y4', 'Y5', 'Group ID']]
        for corp in data.corporate_concept_office:
            corp_data.append([
                str(corp.corporate_id or 0),
                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),
                str(corp.group_id or 0)
            ])
        
        corp_table = Table(corp_data, colWidths=[50, 80, 80, 40, 40, 40, 40, 40, 50])
        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')
        ]))
        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 = [['Concept ID', 'Role Category', 'Cost To Company', 'Y1', 'Y2', 'Y3', 'Y4', 'Y5', 'Group ID']]
        for concept in data.concept_office:
            concept_data.append([
                str(concept.concept_id or 0),
                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),
                str(concept.group_id or 0)
            ])
        
        concept_table = Table(concept_data, colWidths=[50, 80, 80, 40, 40, 40, 40, 40, 50])
        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')
        ]))
        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
        
        story.append(Paragraph("Store Level Staff Needs", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        header_cells = [
            Paragraph('Store ID', normal_style),
            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),
            Paragraph('Group ID', normal_style)
        ]
        
        table_rows = [header_cells]
        for staff in data.store_level_staff_needs:
            row = [
                Paragraph(str(staff.store_id or 0), normal_style),
                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),
                Paragraph(str(staff.group_id or 0), normal_style)
            ]
            table_rows.append(row)
        
        column_widths = [50, 95, 70, 40, 95, 55, 55, 40]
        
        staff_table = Table(table_rows, colWidths=column_widths, 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), 3),
            ('RIGHTPADDING', (0, 0), (-1, -1), 3),
            ('TOPPADDING', (0, 0), (-1, -1), 3),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 3),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('ALIGN', (0, 0), (-1, -1), 'CENTER')
        ]))
        
        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>Competitor ID</b>', normal_style), 
             Paragraph(str(data.training_talent_management.competitor_id or 0), normal_style)],
            [Paragraph('<b>Name Of Reference Retailer</b>', normal_style), 
             Paragraph(data.training_talent_management.name_of_reference_retailer or 'null', normal_style)],
            [Paragraph('<b>Employee Management Activity</b>', normal_style), 
             Paragraph(data.training_talent_management.employee_management_activity or 'null', normal_style)],
            [Paragraph('<b>Policies Plan Activities</b>', normal_style), 
             Paragraph(data.training_talent_management.policeis_plan_activities or 'null', normal_style)],
            [Paragraph('<b>Group ID</b>', normal_style), 
             Paragraph(str(data.training_talent_management.group_id or 0), normal_style)]
        ]
        
        training_table = Table(training_data, colWidths=[180, 300])
        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), 4),
            ('RIGHTPADDING', (0, 0), (-1, -1), 4),
            ('TOPPADDING', (0, 0), (-1, -1), 3),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 3),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('VALIGN', (0, 0), (-1, -1), 'TOP')
        ]))
        
        story.append(training_table)
        story.append(Spacer(1, 12))
        
        # Utilities
        story.append(Paragraph("Utilities", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        header_cells = [
            Paragraph('Utility ID', normal_style),
            Paragraph('Utility Name', normal_style),
            Paragraph('Cost Per Month', normal_style),
            Paragraph('Key Usage Parameters', normal_style),
            Paragraph('Remark', normal_style),
            Paragraph('Store Format Type', normal_style),
            Paragraph('Group ID', normal_style)
        ]
        
        table_rows = [header_cells]
        for util in data.utilities:
            row = [
                Paragraph(str(util.utility_id or 0), normal_style),
                Paragraph(util.utility_name or 'null', normal_style),
                Paragraph(str(util.cost_per_month or 0), normal_style),
                Paragraph(util.key_usage_parameters or 'null', normal_style),
                Paragraph(util.remark or 'null', normal_style),
                Paragraph(util.store_format_type or 'null', normal_style),
                Paragraph(str(util.group_id or 0), normal_style)
            ]
            table_rows.append(row)
        
        column_widths = [40, 65, 70, 120, 120, 75, 40]
        
        util_table = Table(table_rows, colWidths=column_widths, 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), 3),
            ('RIGHTPADDING', (0, 0), (-1, -1), 3),
            ('TOPPADDING', (0, 0), (-1, -1), 3),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 3),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('ALIGN', (0, 0), (2, -1), 'CENTER'),
            ('ALIGN', (3, 0), (-2, -1), 'LEFT'),
            ('ALIGN', (-1, 0), (-1, -1), 'CENTER')
        ]))
        
        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 ID', normal_style),
            Paragraph('Operating Expenses Type', normal_style),
            Paragraph('Percentage Of Sales Per Year', normal_style),
            Paragraph('Remark', normal_style),
            Paragraph('Store Format Type', normal_style),
            Paragraph('Group ID', normal_style)
        ]
        
        table_rows = [header_cells]
        for op in data.operating_expenses:
            row = [
                Paragraph(str(op.operating_id or 0), normal_style),
                Paragraph(op.operating_expenses_type or 'null', normal_style),
                Paragraph(str(op.percentage_of_sales_per_year or 0), normal_style),
                Paragraph(op.remark or 'null', normal_style),
                Paragraph(op.store_format_type or 'null', normal_style),
                Paragraph(str(op.group_id or 0), normal_style)
            ]
            table_rows.append(row)
        
        column_widths = [55, 120, 80, 160, 75, 45]
        
        op_table = Table(table_rows, colWidths=column_widths, 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), 3),
            ('RIGHTPADDING', (0, 0), (-1, -1), 3),
            ('TOPPADDING', (0, 0), (-1, -1), 3),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 3),
            ('VALIGN', (0, 0), (-1, -1), 'MIDDLE'),
            ('ALIGN', (0, 0), (2, -1), 'CENTER'),
            ('ALIGN', (3, 0), (4, -1), 'LEFT'),
            ('ALIGN', (5, 0), (5, -1), 'CENTER')
        ]))
        
        story.append(op_table)
        story.append(Spacer(1, 12))
        
        # Summary
        story.append(Paragraph("Summary", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        summary_data = [
            ['Summary ID', str(data.summary.summary_id or 0)],
            ['Service Submissions', str(data.summary.service_submissions or 'null')],
            ['Service Summarise', str(data.summary.service_summarise or 'null')],
            ['Group ID', str(data.summary.group_id or 0)]
        ]
        
        summary_table = Table(summary_data, colWidths=[200, 300])
        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), 'LEFT'),
            ('VALIGN', (0, 0), (-1, -1), 'TOP')
        ]))
        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_services_utilities_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 filepath
    
    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()