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.adapting_supply_chain import TblAdaptingSupplyChain
from app.models.main.category_wish_inventory import TblCategoryWishInventory
from app.models.main.cash_conversion_cycle import TblCashConversionCycle
from app.models.main.shrinkage_areas_reasons import TblShrinkageAreasReasons
from app.models.main.comparative_analysis import TblComparativeAnalysis
from app.models.main.online_pre_selected_service_level import TblOnlinePreSelectService
from app.models.main.sales import TblSales
from app.models.main.digital_asset_acquisition import TblDigitalAsset
from app.models.main.recurring_operating_expenses import TblRecurringOperating
from app.models.main.summary import TblSummary
from app.models.main.group import TblGroup

from app.api.module_9_report.schema import (
    SupplyChainAdaptation,
    CategoryInventory,
    CashConversionCycle,
    ShrinkageData,
    ComparativeAnalysis,
    OnlinePreSelectService,
    OnlineSales,
    DigitalAsset,
    RecurringOperating,
    Summary,
    Module9ReportPreview,
)
from app.dependency.authantication import JWTPayloadSchema
from app.api.summary.schema import SummaryCreate


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

    async def get_report_preview(self, group_id: int) -> Module9ReportPreview:
        """Get Module 9 report preview data"""
        return await self._fetch_module9_data(group_id)

    async def _fetch_module9_data(self, group_id: int) -> Module9ReportPreview:
        if not group_id:
            raise HTTPException(status_code=400, detail="group_id is required")
        
        # Check if group exists in tbl_group
        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
        adapting_supply = self.db.query(TblAdaptingSupplyChain).filter_by(group_id=group_id).all()
        category_inventory = self.db.query(TblCategoryWishInventory).filter_by(group_id=group_id).all()
        cash_conversion = self.db.query(TblCashConversionCycle).filter_by(group_id=group_id).first()
        shrinkage = self.db.query(TblShrinkageAreasReasons).filter_by(group_id=group_id).first()
        comparative_analysis = self.db.query(TblComparativeAnalysis).filter_by(group_id=group_id).all()
        online_pre_select = self.db.query(TblOnlinePreSelectService).filter_by(group_id=group_id).all()
        sales = self.db.query(TblSales).filter_by(group_id=group_id).first()
        digital_asset = self.db.query(TblDigitalAsset).filter_by(group_id=group_id).all()
        recurring_operating = self.db.query(TblRecurringOperating).filter_by(group_id=group_id).all()
        summary = self.db.query(TblSummary).filter(
            TblSummary.group_id == group_id,
            TblSummary.backend_submissions.isnot(None)
        ).first()

        # Map supply chain adaptation data
        supply_chain_adaptations = []
        for item in adapting_supply:
            categories_str = ""
            if hasattr(item, 'what_are_some_categories') and item.what_are_some_categories:
                if isinstance(item.what_are_some_categories, list):
                    categories_str = ", ".join(item.what_are_some_categories)
                else:
                    categories_str = str(item.what_are_some_categories)
            
            supply_chain_adaptations.append(SupplyChainAdaptation(
                adapting_id=item.adapting_id or 0,
                what_are_some_categories=safe_str(categories_str),
                remarks_about_these_categories=safe_str(item.remarks_about_these_categories),
                do_you_think_you_need=safe_str(item.do_you_think_you_need),
                at_which_point_and_why=safe_str(item.at_which_point_and_why),
                group_id=item.group_id or 0
            ))

        # Map category inventory data
        category_inventories = []
        for item in category_inventory:
            category_inventories.append(CategoryInventory(
                category_wish_id=item.category_wish_id or 0,
                inventory=safe_int(item.inventory),
                credit_period=safe_int(item.credit_period),
                group_id=item.group_id or 0
            ))

        # Map cash conversion cycle
        cash_conv = None
        if cash_conversion:
            cash_conv = CashConversionCycle(
                cash_id=cash_conversion.cash_id or 0,
                inventory_purchase_timing=safe_str(cash_conversion.inventory_purchase_timing),
                sales_to_cash_gap=safe_str(cash_conversion.sales_to_cash_gap),
                cash_flow_challenges=safe_str(cash_conversion.cash_flow_challenges),
                group_id=cash_conversion.group_id or 0
            )

        # Map shrinkage data
        shrinkage_data = None
        if shrinkage:
            shrinkage_data = ShrinkageData(
                shrinkage_id=shrinkage.shrinkage_id or 0,
                shrinkage=safe_float(shrinkage.shrinkage),
                shrinkage_do_you_envision=safe_str(shrinkage.shrinkage_do_you_envision),
                shrinkage_in_your_stores=safe_str(shrinkage.shrinkage_in_your_stores),
                group_id=shrinkage.group_id or 0
            )

        # Map comparative analysis data
        comparative_analyses = []
        for item in comparative_analysis:
            comparative_analyses.append(ComparativeAnalysis(
                comparative_id=item.comparative_id or 0,
                attributes=safe_str(item.attributes),
                own_brand_physical_store=safe_str(item.own_brand_physical_store),
                own_brand_online_store=safe_str(item.own_brand_online_store),
                group_id=item.group_id or 0
            ))

        # Map online pre select service data
        online_pre_services = []
        for item in online_pre_select:
            online_pre_services.append(OnlinePreSelectService(
                online_id=item.online_id or 0,
                type=safe_str(item.type),
                check_box=safe_str(item.check_box),
                group_id=item.group_id or 0
            ))

        # Map online sales data
        online_sales = None
        if sales:
            online_sales = OnlineSales(
                sales_id=sales.sales_id or 0,
                average_traffic_expected=safe_int(sales.average_traffic_expected),
                average_remark=safe_str(sales.average_remark),
                number_of_transaction_per_month=safe_int(sales.number_of_transaction_per_month),
                number_remark=safe_str(sales.number_remark),
                average_transaction_value=safe_float(sales.average_transaction_value),
                transaction_remark=safe_str(sales.transaction_remark),
                cogs=safe_float(sales.cogs),
                cogs_remark=safe_str(sales.cogs_remark),
                group_id=sales.group_id or 0
            )

        # Map digital asset data
        digital_assets = []
        for item in digital_asset:
            digital_assets.append(DigitalAsset(
                digital_id=item.digital_id or 0,
                item=safe_str(item.item),
                estimate_cost=safe_float(item.estimate_cost),
                remark=safe_str(item.remark),
                group_id=item.group_id or 0
            ))

        # Map recurring operating data
        recurring_operatings = []
        for item in recurring_operating:
            recurring_operatings.append(RecurringOperating(
                recurring_id=item.recurring_id or 0,
                expenses_type=safe_str(item.expenses_type),
                percentage_of__sales=safe_float(item.percentage_of__sales),
                remarks=safe_str(item.remarks),
                group_id=item.group_id or 0
            ))

        # Map summary data
        summary_data = None
        if summary:
            summary_data = Summary(
                summary_id=summary.summary_id or 0,
                backend_submissions=safe_str(summary.backend_submissions),
                backend_summarise=safe_str(summary.backend_summarise),
                group_id=summary.group_id or 0
            )

        return Module9ReportPreview(
            group_id=group_id,
            supply_chain_adaptation=supply_chain_adaptations or [SupplyChainAdaptation()],
            category_inventory=category_inventories or [CategoryInventory()],
            cash_conversion_cycle=cash_conv,
            shrinkage_data=shrinkage_data,
            comparative_analysis=comparative_analyses or [ComparativeAnalysis()],
            online_pre_select_service=online_pre_services or [OnlinePreSelectService()],
            online_sales=online_sales,
            digital_asset=digital_assets or [DigitalAsset()],
            recurring_operating=recurring_operatings or [RecurringOperating()],
            summary=summary_data,
            last_updated=pd.Timestamp.now()
        )
    
    def _generate_pdf_report(self, data: Module9ReportPreview) -> 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()
        normal_style = styles['Normal']
        normal_style.fontSize = 8
        normal_style.leading = 10
        
        # Title
        story.append(Paragraph("<b>Module 9: Backend & Supply Chain Report</b>", styles['Title']))
        story.append(Spacer(1, 12))
        
        # Supply Chain Adaptation
        story.append(Paragraph("Supply Chain Adaptation", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        header_cells = [
            Paragraph('Adapting ID', normal_style),
            Paragraph('Categories', normal_style),
            Paragraph('Remarks About Categories', normal_style),
            Paragraph('Do You Think You Need', normal_style),
            Paragraph('At Which Point And Why', normal_style),
            Paragraph('Group ID', normal_style)
        ]
        
        table_rows = [header_cells]
        for item in data.supply_chain_adaptation:
            row = [
                Paragraph(str(item.adapting_id or 0), normal_style),
                Paragraph(item.what_are_some_categories or 'null', normal_style),
                Paragraph(item.remarks_about_these_categories or 'null', normal_style),
                Paragraph(item.do_you_think_you_need or 'null', normal_style),
                Paragraph(item.at_which_point_and_why or 'null', normal_style),
                Paragraph(str(item.group_id or 0), normal_style)
            ]
            table_rows.append(row)
        
        supply_table = Table(table_rows, colWidths=[50, 80, 100, 80, 120, 50], repeatRows=1)
        supply_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), 'LEFT')
        ]))
        
        story.append(supply_table)
        story.append(Spacer(1, 12))
        
        # Category Inventory
        story.append(Paragraph("Category Inventory", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        cat_data = [['Category Wish ID', 'Inventory', 'Credit Period', 'Group ID']]
        for item in data.category_inventory:
            cat_data.append([
                str(item.category_wish_id or 0),
                str(item.inventory or 0),
                str(item.credit_period or 0),
                str(item.group_id or 0)
            ])
        
        cat_table = Table(cat_data, colWidths=[120, 120, 120, 120])
        cat_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(cat_table)
        story.append(Spacer(1, 12))
        
        # Cash Conversion Cycle
        story.append(Paragraph("Cash Conversion Cycle", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        if data.cash_conversion_cycle:
            cash_data = [
                [Paragraph('<b>Cash ID</b>', normal_style), Paragraph(str(data.cash_conversion_cycle.cash_id or 0), normal_style)],
                [Paragraph('<b>Inventory Purchase Timing</b>', normal_style), Paragraph(data.cash_conversion_cycle.inventory_purchase_timing or 'null', normal_style)],
                [Paragraph('<b>Sales To Cash Gap</b>', normal_style), Paragraph(data.cash_conversion_cycle.sales_to_cash_gap or 'null', normal_style)],
                [Paragraph('<b>Cash Flow Challenges</b>', normal_style), Paragraph(data.cash_conversion_cycle.cash_flow_challenges or 'null', normal_style)],
                [Paragraph('<b>Group ID</b>', normal_style), Paragraph(str(data.cash_conversion_cycle.group_id or 0), normal_style)]
            ]
        else:
            cash_data = [
                [Paragraph('<b>Cash ID</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Inventory Purchase Timing</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Sales To Cash Gap</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Cash Flow Challenges</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Group ID</b>', normal_style), Paragraph('null', normal_style)]
            ]
        
        cash_table = Table(cash_data, colWidths=[180, 300])
        cash_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('BOX', (0, 0), (-1, -1), 1, colors.black),
            ('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(cash_table)
        story.append(Spacer(1, 12))
        
        # Shrinkage Data
        story.append(Paragraph("Shrinkage Data", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        if data.shrinkage_data:
            shrink_data = [
                [Paragraph('<b>Shrinkage ID</b>', normal_style), Paragraph(str(data.shrinkage_data.shrinkage_id or 0), normal_style)],
                [Paragraph('<b>Shrinkage</b>', normal_style), Paragraph(str(data.shrinkage_data.shrinkage or 0), normal_style)],
                [Paragraph('<b>Shrinkage Do You Envision</b>', normal_style), Paragraph(data.shrinkage_data.shrinkage_do_you_envision or 'null', normal_style)],
                [Paragraph('<b>Shrinkage In Your Stores</b>', normal_style), Paragraph(data.shrinkage_data.shrinkage_in_your_stores or 'null', normal_style)],
                [Paragraph('<b>Group ID</b>', normal_style), Paragraph(str(data.shrinkage_data.group_id or 0), normal_style)]
            ]
        else:
            shrink_data = [
                [Paragraph('<b>Shrinkage ID</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Shrinkage</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Shrinkage Do You Envision</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Shrinkage In Your Stores</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Group ID</b>', normal_style), Paragraph('null', normal_style)]
            ]
        
        shrink_table = Table(shrink_data, colWidths=[180, 300])
        shrink_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('BOX', (0, 0), (-1, -1), 1, colors.black),
            ('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(shrink_table)
        story.append(Spacer(1, 12))
        
        # Comparative Analysis
        story.append(Paragraph("Comparative Analysis", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        comp_header = [
            Paragraph('Comparative ID', normal_style),
            Paragraph('Attributes', normal_style),
            Paragraph('Own Brand Physical Store', normal_style),
            Paragraph('Own Brand Online Store', normal_style),
            Paragraph('Group ID', normal_style)
        ]
        
        comp_rows = [comp_header]
        for item in data.comparative_analysis:
            row = [
                Paragraph(str(item.comparative_id or 0), normal_style),
                Paragraph(item.attributes or 'null', normal_style),
                Paragraph(item.own_brand_physical_store or 'null', normal_style),
                Paragraph(item.own_brand_online_store or 'null', normal_style),
                Paragraph(str(item.group_id or 0), normal_style)
            ]
            comp_rows.append(row)
        
        comp_table = Table(comp_rows, colWidths=[60, 100, 120, 120, 50], repeatRows=1)
        comp_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), 'LEFT')
        ]))
        story.append(comp_table)
        story.append(Spacer(1, 12))
        
        # Online Pre Select Service
        story.append(Paragraph("Online Pre Select Service", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        online_header = [
            Paragraph('Online ID', normal_style),
            Paragraph('Type', normal_style),
            Paragraph('Check Box', normal_style),
            Paragraph('Group ID', normal_style)
        ]
        
        online_rows = [online_header]
        for item in data.online_pre_select_service:
            row = [
                Paragraph(str(item.online_id or 0), normal_style),
                Paragraph(item.type or 'null', normal_style),
                Paragraph(item.check_box or 'null', normal_style),
                Paragraph(str(item.group_id or 0), normal_style)
            ]
            online_rows.append(row)
        
        online_table = Table(online_rows, colWidths=[70, 120, 200, 70], repeatRows=1)
        online_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), 'LEFT')
        ]))
        story.append(online_table)
        story.append(Spacer(1, 12))
        
        # Online Sales
        story.append(Paragraph("Online Sales", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        if data.online_sales:
            sales_data = [
                [Paragraph('<b>Sales ID</b>', normal_style), Paragraph(str(data.online_sales.sales_id or 0), normal_style)],
                [Paragraph('<b>Average Traffic Expected</b>', normal_style), Paragraph(str(data.online_sales.average_traffic_expected or 0), normal_style)],
                [Paragraph('<b>Average Remark</b>', normal_style), Paragraph(data.online_sales.average_remark or 'null', normal_style)],
                [Paragraph('<b>Number Of Transaction Per Month</b>', normal_style), Paragraph(str(data.online_sales.number_of_transaction_per_month or 0), normal_style)],
                [Paragraph('<b>Number Remark</b>', normal_style), Paragraph(data.online_sales.number_remark or 'null', normal_style)],
                [Paragraph('<b>Average Transaction Value</b>', normal_style), Paragraph(str(data.online_sales.average_transaction_value or 0), normal_style)],
                [Paragraph('<b>Transaction Remark</b>', normal_style), Paragraph(data.online_sales.transaction_remark or 'null', normal_style)],
                [Paragraph('<b>COGS</b>', normal_style), Paragraph(str(data.online_sales.cogs or 0), normal_style)],
                [Paragraph('<b>COGS Remark</b>', normal_style), Paragraph(data.online_sales.cogs_remark or 'null', normal_style)],
                [Paragraph('<b>Group ID</b>', normal_style), Paragraph(str(data.online_sales.group_id or 0), normal_style)]
            ]
        else:
            sales_data = [
                [Paragraph('<b>Sales ID</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Average Traffic Expected</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Average Remark</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Number Of Transaction Per Month</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Number Remark</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Average Transaction Value</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Transaction Remark</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>COGS</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>COGS Remark</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Group ID</b>', normal_style), Paragraph('null', normal_style)]
            ]
        
        sales_table = Table(sales_data, colWidths=[180, 300])
        sales_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('BOX', (0, 0), (-1, -1), 1, colors.black),
            ('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(sales_table)
        story.append(Spacer(1, 12))
        
        # Digital Asset
        story.append(Paragraph("Digital Asset", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        digital_header = [
            Paragraph('Digital ID', normal_style),
            Paragraph('Item', normal_style),
            Paragraph('Estimate Cost', normal_style),
            Paragraph('Remark', normal_style),
            Paragraph('Group ID', normal_style)
        ]
        
        digital_rows = [digital_header]
        for item in data.digital_asset:
            row = [
                Paragraph(str(item.digital_id or 0), normal_style),
                Paragraph(item.item or 'null', normal_style),
                Paragraph(str(item.estimate_cost or 0), normal_style),
                Paragraph(item.remark or 'null', normal_style),
                Paragraph(str(item.group_id or 0), normal_style)
            ]
            digital_rows.append(row)
        
        digital_table = Table(digital_rows, colWidths=[60, 120, 80, 150, 50], repeatRows=1)
        digital_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), 'LEFT')
        ]))
        story.append(digital_table)
        story.append(Spacer(1, 12))
        
        # Recurring Operating
        story.append(Paragraph("Recurring Operating", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        recurring_header = [
            Paragraph('Recurring ID', normal_style),
            Paragraph('Expenses Type', normal_style),
            Paragraph('Percentage Of Sales', normal_style),
            Paragraph('Remarks', normal_style),
            Paragraph('Group ID', normal_style)
        ]
        
        recurring_rows = [recurring_header]
        for item in data.recurring_operating:
            row = [
                Paragraph(str(item.recurring_id or 0), normal_style),
                Paragraph(item.expenses_type or 'null', normal_style),
                Paragraph(str(item.percentage_of__sales or 0), normal_style),
                Paragraph(item.remarks or 'null', normal_style),
                Paragraph(str(item.group_id or 0), normal_style)
            ]
            recurring_rows.append(row)
        
        recurring_table = Table(recurring_rows, colWidths=[60, 120, 80, 150, 50], repeatRows=1)
        recurring_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), 'LEFT')
        ]))
        story.append(recurring_table)
        story.append(Spacer(1, 12))
        
        # Summary
        story.append(Paragraph("Summary", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        if data.summary:
            summary_data = [
                [Paragraph('<b>Summary ID</b>', normal_style), Paragraph(str(data.summary.summary_id or 0), normal_style)],
                [Paragraph('<b>Backend Submissions</b>', normal_style), Paragraph(str(data.summary.backend_submissions or 'null'), normal_style)],
                [Paragraph('<b>Backend Summarise</b>', normal_style), Paragraph(str(data.summary.backend_summarise or 'null'), normal_style)],
                [Paragraph('<b>Group ID</b>', normal_style), Paragraph(str(data.summary.group_id or 0), normal_style)]
            ]
        else:
            summary_data = [
                [Paragraph('<b>Summary ID</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Backend Submissions</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Backend Summarise</b>', normal_style), Paragraph('null', normal_style)],
                [Paragraph('<b>Group ID</b>', normal_style), Paragraph('null', normal_style)]
            ]
        
        summary_table = Table(summary_data, colWidths=[180, 300])
        summary_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, colors.black),
            ('BOX', (0, 0), (-1, -1), 1, colors.black),
            ('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(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"module9_backend_supply_chain_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_module9_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}_m9"
            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()