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 app.models.main.network_plan import TblNetworkPlanning
from app.models.main.trading_months import TblTradingMonth
from app.models.main.pre_operating_expenses import TblPreOperatingExpense
from app.models.main.summary import TblSummary, SummaryBase
from app.models.main.group import TblGroup
# Module 1 tables for capital expenses
from app.models.main.civil import TblCivil
from app.models.main.info_tech import TblInfoTech
# Module 2 tables for rent and utilities
from app.models.main.rent_expenses import TblRentExpenses
from app.models.main.utility import TblUtility
from app.models.main.store_level_staff_needs import TblStoreLevelStaffNeeds

from app.api.module_3.schema import (
    NetworkPlanning,
    TradingMonths,
    PreOperatingExpenses,
    StoreInvestmentSummary,
    Module3Summary,
    Module3ReportPreview,
)
from app.dependency.authantication import JWTPayloadSchema


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

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

        # Check if group_id exists in module tables
        module_data_exists = (
            self.db.query(TblNetworkPlanning).filter_by(group_id=group_id).first() or
            self.db.query(TblTradingMonth).filter_by(group_id=group_id).first() or
            self.db.query(TblPreOperatingExpense).filter_by(group_id=group_id).first()
        )

        if module_data_exists:
            # Group ID available in module tables - fetch actual data
            network_records = self.db.query(TblNetworkPlanning).filter_by(group_id=group_id).all()
            trading_record = self.db.query(TblTradingMonth).filter_by(group_id=group_id).first()
            pre_operating_record = self.db.query(TblPreOperatingExpense).filter_by(group_id=group_id).first()
            summary_record = self.db.query(TblSummary).filter_by(group_id=group_id).first()
            
            # Fetch cross-module data for store investment
            civil_data = self.db.query(TblCivil).filter_by(group_id=group_id).all()
            it_data = self.db.query(TblInfoTech).filter_by(group_id=group_id).all()
            rent_data = self.db.query(TblRentExpenses).filter_by(group_id=group_id).all()
            utility_data = self.db.query(TblUtility).filter_by(group_id=group_id).all()
            staff_data = self.db.query(TblStoreLevelStaffNeeds).filter_by(group_id=group_id).all()

            # Create store investment summary
            store_investment = self._create_store_investment_summary(
                group_id, pre_operating_record, civil_data, it_data, rent_data, utility_data, staff_data
            )

            return Module3ReportPreview(
                group_id=group_id,
                network_planning=[NetworkPlanning(**n.__dict__) for n in network_records] or [NetworkPlanning()],
                trading_months=TradingMonths(**trading_record.__dict__) if trading_record else TradingMonths(),
                pre_operating_expenses=PreOperatingExpenses(**pre_operating_record.__dict__) if pre_operating_record else PreOperatingExpenses(),
                store_investment_summary=store_investment,
                summary=Module3Summary(**summary_record.__dict__) if summary_record else Module3Summary(),
                last_updated=pd.Timestamp.now()
            )
        else:
            # Check if group_id exists in tbl_group
            group_exists = self.db.query(TblGroup).filter_by(group_id=group_id).first()
            
            if group_exists:
                # Group ID exists in tbl_group but not in module tables - return N/A and 0 values
                return Module3ReportPreview(
                    group_id=group_id,
                    network_planning=[NetworkPlanning(group_id=group_id)],
                    trading_months=TradingMonths(group_id=group_id),
                    pre_operating_expenses=PreOperatingExpenses(group_id=group_id),
                    store_investment_summary=StoreInvestmentSummary(group_id=group_id),
                    summary=Module3Summary(group_id=group_id),
                    last_updated=pd.Timestamp.now()
                )
            else:
                # Group ID not found in database
                raise HTTPException(status_code=404, detail="Group ID not found in database")
    
    def _create_store_investment_summary(self, group_id: int, pre_operating_record, civil_data, it_data, rent_data, utility_data, staff_data) -> StoreInvestmentSummary:
        """Create store investment summary from cross-module data"""
        store_investment = StoreInvestmentSummary(group_id=group_id)
        
        def sum_for_format(data_list, fmt_letter, attr="total"):
            return sum(
                (getattr(item, attr, 0) or 0)
                for item in (data_list or [])
                if fmt_letter in str(getattr(item, "store_format_type", ""))
            )
        
        def sum_civil_for_format(data_list, fmt_letter):
            return sum(
                ((item.rate_per_sqft or 0) * (item.total_sqft or 0))
                for item in (data_list or [])
                if fmt_letter in str(getattr(item, "store_format_type", ""))
            )
        
        # Module 1 - Capital expenses
        store_investment.capital_expenses_a = sum_civil_for_format(civil_data, "A")
        store_investment.capital_expenses_b = sum_civil_for_format(civil_data, "B")
        store_investment.it_capital_expenses_a = sum_for_format(it_data, "A")
        store_investment.it_capital_expenses_b = sum_for_format(it_data, "B")
        
        # Module 2 - Rental data
        for rent in rent_data:
            if "A" in str(rent.store_format_type or ""):
                store_investment.rental_advance_a = rent.rental_advance_amount or 0
                store_investment.rent_per_month_a = rent.rent_per_month or 0
            elif "B" in str(rent.store_format_type or ""):
                store_investment.rental_advance_b = rent.rental_advance_amount or 0
                store_investment.rent_per_month_b = rent.rent_per_month or 0
        
        # Operating expenses - Utilities
        for utility in utility_data:
            if "A" in str(utility.store_format_type or ""):
                store_investment.utilities_a += utility.cost_per_month or 0
            elif "B" in str(utility.store_format_type or ""):
                store_investment.utilities_b += utility.cost_per_month or 0
        
        # Operating expenses - Staff wages
        for staff in staff_data:
            wage = (staff.average_salary or 0) * (staff.count or 0)
            if "A" in str(staff.store_formate_type or ""):
                store_investment.employee_wages_a += wage
            elif "B" in str(staff.store_formate_type or ""):
                store_investment.employee_wages_b += wage
        
        # Module 3 - Pre-operating data
        if pre_operating_record:
            store_investment.marketing_expenses_a = pre_operating_record.marketing_expenses_store_a or 0
            store_investment.marketing_expenses_b = pre_operating_record.marketing_expenses_store_b or 0
            store_investment.pre_operating_months_a = pre_operating_record.pre_operating_months_store_a or 0
            store_investment.pre_operating_months_b = pre_operating_record.pre_operating_months_store_b or 0
            
            # Apply calculated formulas
            store_investment.pre_operating_expenses_a = store_investment.calculated_pre_operating_expenses_a
            store_investment.pre_operating_expenses_b = store_investment.calculated_pre_operating_expenses_b
            store_investment.total_store_capital_expenses_a = store_investment.calculated_total_capital_expenses_a
            store_investment.total_store_capital_expenses_b = store_investment.calculated_total_capital_expenses_b
            store_investment.total_store_investment_a = store_investment.calculated_total_store_investment_a
            store_investment.total_store_investment_b = store_investment.calculated_total_store_investment_b
        
        return store_investment
    
    def _generate_pdf_report(self, data: Module3ReportPreview) -> str:
        """Generate PDF report from Module3 data"""
        buffer = BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=A4, leftMargin=50, rightMargin=50, topMargin=50, bottomMargin=50)
        styles = getSampleStyleSheet()
        story = []
        
        # Title style
        title_style = ParagraphStyle(
            'CustomTitle',
            parent=styles['Heading1'],
            fontSize=16,
            spaceAfter=20,
            alignment=1,
            fontName='Helvetica-Bold'
        )
        story.append(Paragraph("Module 3: Network Planning", title_style))
        story.append(Spacer(1, 20))
        
        # Section header style
        section_style = ParagraphStyle(
            'SectionHeader',
            parent=styles['Heading2'],
            fontSize=14,
            spaceAfter=10,
            fontName='Helvetica-Bold'
        )
        
        # Network Planning
        story.append(Paragraph("Network Planning", section_style))
        if data.network_planning:
            network_headers = ['Network ID', 'Location', 'State', 'Total HH', 'Target HH', 'Area', 'Action', 'Format A Y5', 'Format B Y5', 'Total Stores Y5', 'Group ID']
            network_rows = [network_headers]
            for network in data.network_planning:
                network_rows.append([
                    str(network.network_id or 0),
                    str(network.location or "N/A"),
                    str(network.state or "N/A"),
                    str(network.total_hh or 0),
                    str(network.target_hh or 0),
                    str(network.area or 0),
                    str(network.action or "N/A"),
                    str(network.format_a_y5 or 0),
                    str(network.format_b_y5 or 0),
                    str(network.total_store_count_y5 or 0),
                    str(network.group_id or 0)
                ])
            network_table = Table(network_rows)
            network_table.setStyle(TableStyle([
                ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, -1), 6),
                ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
                ('VALIGN', (0, 0), (-1, -1), 'TOP'),
                ('WORDWRAP', (0, 0), (-1, -1), True)
            ]))
            story.append(network_table)
        story.append(Spacer(1, 15))
        
        # Trading Months
        normal_style = styles['Normal']
        normal_style.fontSize = 7
        normal_style.leading = 9
        
        story.append(Paragraph("Trading Months", section_style))
        story.append(Spacer(1, 10))
        
        # Define headers with Paragraph for wrapping
        headers = [
            Paragraph('Trading<br/>ID', normal_style),
            Paragraph('Store A<br/>Trading<br/>Months', normal_style),
            Paragraph('Store B<br/>Trading<br/>Months', normal_style),
            Paragraph('Rationale Store A', normal_style),
            Paragraph('Rationale Store B', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        # Data row with Paragraph for wrapping
        row = [
            Paragraph(str(data.trading_months.trading_id or 0), normal_style),
            Paragraph(str(data.trading_months.store_a_trading_months or "N/A"), normal_style),
            Paragraph(str(data.trading_months.store_b_trading_months or "N/A"), normal_style),
            Paragraph(str(data.trading_months.rationale_for_store_a or "N/A"), normal_style),
            Paragraph(str(data.trading_months.rationale_for_store_b or "N/A"), normal_style),
            Paragraph(str(data.trading_months.group_id or 0), normal_style)
        ]
        
        table_data = [headers, row]
        column_widths = [40, 50, 50, 180, 180, 40]
        
        trading_table = Table(table_data, colWidths=column_widths, repeatRows=1)
        trading_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), 7),
            ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
            ('FONTSIZE', (0, 1), (-1, -1), 7),
            ('LEFTPADDING', (0, 0), (-1, -1), 4),
            ('RIGHTPADDING', (0, 0), (-1, -1), 4),
            ('TOPPADDING', (0, 0), (-1, -1), 3),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 3),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT')
        ]))
        
        story.append(trading_table)
        story.append(Spacer(1, 15))
        
        # Pre Operating Expenses
        pre_op_normal_style = styles['Normal']
        pre_op_normal_style.fontSize = 6.5
        pre_op_normal_style.leading = 8
        
        story.append(Paragraph("Pre Operating Expenses", section_style))
        story.append(Spacer(1, 10))
        
        headers = [
            Paragraph('Pre<br/>Operating<br/>ID', pre_op_normal_style),
            Paragraph('Pre<br/>Operating<br/>Months<br/>Store A', pre_op_normal_style),
            Paragraph('Pre<br/>Operating<br/>Months<br/>Store B', pre_op_normal_style),
            Paragraph('Marketing<br/>Expenses<br/>Store A', pre_op_normal_style),
            Paragraph('Marketing<br/>Expenses<br/>Store B', pre_op_normal_style),
            Paragraph('Accounting Benefits<br/>Store A', pre_op_normal_style),
            Paragraph('Accounting Benefits<br/>Store B', pre_op_normal_style),
            Paragraph('Other Remarks<br/>Store A', pre_op_normal_style),
            Paragraph('Other Remarks<br/>Store B', pre_op_normal_style),
            Paragraph('Group<br/>ID', pre_op_normal_style)
        ]
        
        row = [
            Paragraph(str(data.pre_operating_expenses.pre_operating_id or 0), pre_op_normal_style),
            Paragraph(str(data.pre_operating_expenses.pre_operating_months_store_a or "N/A"), pre_op_normal_style),
            Paragraph(str(data.pre_operating_expenses.pre_operating_months_store_b or "N/A"), pre_op_normal_style),
            Paragraph(str(data.pre_operating_expenses.marketing_expenses_store_a or "N/A"), pre_op_normal_style),
            Paragraph(str(data.pre_operating_expenses.marketing_expenses_store_b or "N/A"), pre_op_normal_style),
            Paragraph(str(data.pre_operating_expenses.accounting_benefits_store_a or "N/A"), pre_op_normal_style),
            Paragraph(str(data.pre_operating_expenses.accounting_benefits_store_b or "N/A"), pre_op_normal_style),
            Paragraph(str(data.pre_operating_expenses.other_remarks_store_a or "N/A"), pre_op_normal_style),
            Paragraph(str(data.pre_operating_expenses.other_remarks_store_b or "N/A"), pre_op_normal_style),
            Paragraph(str(data.pre_operating_expenses.group_id or 0), pre_op_normal_style)
        ]
        
        table_data = [headers, row]
        column_widths = [30, 45, 45, 55, 55, 90, 90, 65, 65, 30]
        
        pre_op_table = Table(table_data, colWidths=column_widths, repeatRows=1)
        pre_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), 6.5),
            ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
            ('FONTSIZE', (0, 1), (-1, -1), 6.5),
            ('LEFTPADDING', (0, 0), (-1, -1), 3),
            ('RIGHTPADDING', (0, 0), (-1, -1), 3),
            ('TOPPADDING', (0, 0), (-1, -1), 2),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 2),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT')
        ]))
        
        story.append(pre_op_table)
        story.append(Spacer(1, 15))
        
        # Store Investment Summary
        story.append(Paragraph("Store Investment Summary", section_style))
        investment_headers = ['Field', 'Store Format A', 'Store Format B']
        investment_rows = [investment_headers]
        investment_rows.append(['Capital Expenses A', str(data.store_investment_summary.capital_expenses_a or 0), str(data.store_investment_summary.capital_expenses_b or 0)])
        investment_rows.append(['IT Capital Expenses', str(data.store_investment_summary.it_capital_expenses_a or 0), str(data.store_investment_summary.it_capital_expenses_b or 0)])
        investment_rows.append(['Rental Advance', str(data.store_investment_summary.rental_advance_a or 0), str(data.store_investment_summary.rental_advance_b or 0)])
        investment_rows.append(['Rent Per Month', str(data.store_investment_summary.rent_per_month_a or 0), str(data.store_investment_summary.rent_per_month_b or 0)])
        investment_rows.append(['Utilities', str(data.store_investment_summary.utilities_a or 0), str(data.store_investment_summary.utilities_b or 0)])
        investment_rows.append(['Employee Wages', str(data.store_investment_summary.employee_wages_a or 0), str(data.store_investment_summary.employee_wages_b or 0)])
        investment_rows.append(['Marketing Expenses', str(data.store_investment_summary.marketing_expenses_a or 0), str(data.store_investment_summary.marketing_expenses_b or 0)])
        investment_rows.append(['Pre Operating Months', str(data.store_investment_summary.pre_operating_months_a or 0), str(data.store_investment_summary.pre_operating_months_b or 0)])
        investment_rows.append(['Total Store Investment', str(data.store_investment_summary.calculated_total_store_investment_a or 0), str(data.store_investment_summary.calculated_total_store_investment_b or 0)])
        
        investment_table = Table(investment_rows)
        investment_table.setStyle(TableStyle([
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 8),
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        story.append(investment_table)
        story.append(Spacer(1, 15))
        
        # Summary
        story.append(Paragraph("Summary", section_style))
        summary_data = [['Summary ID', 'Network Submissions', 'Network Summarise', 'Group ID']]
        summary_data.append([str(data.summary.summary_id or 0), str(data.summary.network_submissions or "N/A"), str(data.summary.network_summarise or "N/A"), str(data.summary.group_id or 0)])
        summary_table = Table(summary_data)
        summary_table.setStyle(TableStyle([
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
            ('FONTSIZE', (0, 0), (-1, -1), 8),
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        story.append(summary_table)
        
        # Last Updated
        story.append(Spacer(1, 10))
        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"module3_network_planning_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_module3_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}_m3"
            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()