from fastapi import HTTPException
from sqlalchemy.orm import Session
from jinja2 import Environment, BaseLoader
import pandas as pd
import os
from datetime import datetime
from io import BytesIO
from reportlab.lib.pagesizes import A4
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle
from reportlab.lib.styles import getSampleStyleSheet
from reportlab.lib import colors

from app.models.main.categories_under_promotions import TblCategoriesUnderPromotions
from app.models.main.competitor_loyalty_program import TblCompetitorLoyalty
from app.models.main.competitor_pricing_analysis import TblCompetitorPricingAnalysis
from app.models.main.customer_engagement_strategies import TblCustomerEngagement
from app.models.main.promotion_competitor_analysis import TblPromotionCompetitorAnalysis
from app.models.main.recovery_of_promotional import TblRecoveryOfPromotional
from app.models.main.store_formate_pricing_strategies import TblStoreFormatepricingStrategies
from app.models.main.category_pricing_strategis import TblCategoryPricingStrategis
from app.models.main.define_your_promotions_strategy import TblDefineYourPromotions
from app.models.main.summary import TblSummary
from app.models.main.group import TblGroup
from app.api.summary.schema import SummaryCreate

from app.api.module_6_report.schema import (
    CompetitorPricingAnalysis,
    StoreFormatepricingStrategies,
    CategoryPricingStrategis,
    DefineYourPromotions,
    PromotionalCalendar,
    PromotionCompetitorAnalysis,
    RecoveryOfPromotional,
    CategoriesUnderPromotions,
    CustomerEngagement,
    CompetitorLoyalty,
    Summary,
    Module6ReportPreview,
)
from app.dependency.authantication import JWTPayloadSchema


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

    async def _fetch_module6_data(self, group_id: int) -> Module6ReportPreview:
        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(TblCompetitorPricingAnalysis).filter_by(group_id=group_id).first() or
            self.db.query(TblStoreFormatepricingStrategies).filter_by(group_id=group_id).first() or
            self.db.query(TblCategoryPricingStrategis).filter_by(group_id=group_id).first() or
            self.db.query(TblDefineYourPromotions).filter_by(group_id=group_id).first() or
            self.db.query(TblPromotionCompetitorAnalysis).filter_by(group_id=group_id).first() or
            self.db.query(TblRecoveryOfPromotional).filter_by(group_id=group_id).first() or
            self.db.query(TblCategoriesUnderPromotions).filter_by(group_id=group_id).first() or
            self.db.query(TblCustomerEngagement).filter_by(group_id=group_id).first() or
            self.db.query(TblCompetitorLoyalty).filter_by(group_id=group_id).first() or
            self.db.query(TblSummary).filter_by(group_id=group_id).first()
        )

        if module_data_exists:
            # Group ID available in module tables - fetch actual data
            competitor_pricing = self.db.query(TblCompetitorPricingAnalysis).filter_by(group_id=group_id).first()
            store_formats = self.db.query(TblStoreFormatepricingStrategies).filter_by(group_id=group_id).all()
            category_pricing = self.db.query(TblCategoryPricingStrategis).filter_by(group_id=group_id).all()
            define_promo = self.db.query(TblDefineYourPromotions).filter_by(group_id=group_id).first()
            promo_analysis = self.db.query(TblPromotionCompetitorAnalysis).filter_by(group_id=group_id).all()
            recovery = self.db.query(TblRecoveryOfPromotional).filter_by(group_id=group_id).first()
            categories_under = self.db.query(TblCategoriesUnderPromotions).filter_by(group_id=group_id).first()
            engagement = self.db.query(TblCustomerEngagement).filter_by(group_id=group_id).first()
            loyalty_program = self.db.query(TblCompetitorLoyalty).filter_by(group_id=group_id).first()
            summary = self.db.query(TblSummary).filter_by(group_id=group_id).first()

            return Module6ReportPreview(
                group_id=group_id,
                competitor_pricing_analysis=CompetitorPricingAnalysis.model_validate(competitor_pricing) if competitor_pricing else CompetitorPricingAnalysis(group_id=group_id),
                store_format_pricing_strategis=[StoreFormatepricingStrategies.model_validate(s) for s in store_formats] if store_formats else [StoreFormatepricingStrategies(group_id=group_id)],
                category_pricing_strategis=[CategoryPricingStrategis.model_validate(c) for c in category_pricing] if category_pricing else [CategoryPricingStrategis(group_id=group_id)],
                define_your_promotions_strategy=DefineYourPromotions.model_validate(define_promo) if define_promo else DefineYourPromotions(group_id=group_id),
                promotional_calendar=[PromotionalCalendar(group_id=group_id)],
                promotion_competitor_analysis=[
                    PromotionCompetitorAnalysis(
                        promotion_id=p.promotion_id,
                        competitor_efforts=str(p.competitor_efforts) if p.competitor_efforts else "N/A",
                        top_promotion_types=str(getattr(p, 'your_store_type', None)) if getattr(p, 'your_store_type', None) else "N/A",
                        top_categories_for_promotions=str(getattr(p, 'throughout_the_year', None)) if getattr(p, 'throughout_the_year', None) else "N/A",
                        spillover_categories="N/A",
                        group_id=p.group_id
                    ) for p in promo_analysis
                ] if promo_analysis else [PromotionCompetitorAnalysis(group_id=group_id)],
                recovery_of_promotional=RecoveryOfPromotional.model_validate(recovery) if recovery else RecoveryOfPromotional(group_id=group_id),
                categories_under_promotions=CategoriesUnderPromotions.model_validate(categories_under) if categories_under else CategoriesUnderPromotions(group_id=group_id),
                customer_engagement=CustomerEngagement.model_validate(engagement) if engagement else CustomerEngagement(group_id=group_id),
                competitor_loyalty=CompetitorLoyalty.model_validate(loyalty_program) if loyalty_program else CompetitorLoyalty(group_id=group_id),
                summary=Summary.model_validate(summary) if summary else Summary(group_id=group_id),
                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 Module6ReportPreview(
                    group_id=group_id,
                    competitor_pricing_analysis=CompetitorPricingAnalysis(group_id=group_id),
                    store_format_pricing_strategis=[StoreFormatepricingStrategies(group_id=group_id)],
                    category_pricing_strategis=[CategoryPricingStrategis(group_id=group_id)],
                    define_your_promotions_strategy=DefineYourPromotions(group_id=group_id),
                    promotional_calendar=[PromotionalCalendar(group_id=group_id)],
                    promotion_competitor_analysis=[PromotionCompetitorAnalysis(group_id=group_id)],
                    recovery_of_promotional=RecoveryOfPromotional(group_id=group_id),
                    categories_under_promotions=CategoriesUnderPromotions(group_id=group_id),
                    customer_engagement=CustomerEngagement(group_id=group_id),
                    competitor_loyalty=CompetitorLoyalty(group_id=group_id),
                    summary=Summary(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")

    async def get_report_preview(self, group_id: int) -> Module6ReportPreview:
        return await self._fetch_module6_data(group_id)

    def _generate_pdf_report(self, data: Module6ReportPreview) -> str:
        """Generate PDF report and return file path"""
        buffer = BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=A4, leftMargin=50, rightMargin=50, topMargin=50, bottomMargin=50)
        styles = getSampleStyleSheet()
        story = []
        
        # Title
        story.append(Paragraph("Module 6: Pricing, Promotions and Loyalty Report", styles['Title']))
        story.append(Spacer(1, 20))
        
        normal_style = styles['Normal']
        normal_style.fontSize = 8
        normal_style.leading = 10
        
        # Competitor Pricing Analysis
        story.append(Paragraph("Competitor Pricing Analysis", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        pricing_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Competitor ID', normal_style), Paragraph(str(data.competitor_pricing_analysis.competitor_id or 0), normal_style)],
            [Paragraph('Reference Competitor', normal_style), Paragraph(str(data.competitor_pricing_analysis.reference_competitor or 'N/A'), normal_style)],
            [Paragraph('Pricing Strategy', normal_style), Paragraph(str(data.competitor_pricing_analysis.pricing_strategy or 'N/A'), normal_style)],
            [Paragraph('Which Category Generates Most Income', normal_style), Paragraph(str(data.competitor_pricing_analysis.which_category_generates_the_most_income_for_the_store or 'N/A'), normal_style)],
            [Paragraph('Key Observation Around Pricing', normal_style), Paragraph(str(data.competitor_pricing_analysis.key_observation_around_pricing_in_the_store or 'N/A'), normal_style)],
            [Paragraph('Other Remarks', normal_style), Paragraph(str(data.competitor_pricing_analysis.other_remarks or 'N/A'), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.competitor_pricing_analysis.group_id or 0), normal_style)]
        ]
        
        pricing_table = Table(pricing_data, colWidths=[160, 340])
        pricing_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), 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(pricing_table)
        story.append(Spacer(1, 12))
        
        # Store Format Pricing Strategies
        story.append(Paragraph("Store Format Pricing Strategies", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        store_headers = [
            Paragraph('Store<br/>ID', normal_style),
            Paragraph('Store Format<br/>Type', normal_style),
            Paragraph('Pricing<br/>Strategies', normal_style),
            Paragraph('Rational', normal_style),
            Paragraph('Other<br/>Strategy', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        store_rows = [store_headers]
        for store in data.store_format_pricing_strategis:
            row = [
                Paragraph(str(store.store_id or 0), normal_style),
                Paragraph(store.store_format_type or 'N/A', normal_style),
                Paragraph(store.choose_a_pricing_strategies or 'N/A', normal_style),
                Paragraph(store.rational or 'N/A', normal_style),
                Paragraph(str(store.specify_other_strategy or 'N/A'), normal_style),
                Paragraph(str(store.group_id or 0), normal_style)
            ]
            store_rows.append(row)
        
        store_column_widths = [40, 80, 80, 150, 80, 40]
        
        store_table = Table(store_rows, colWidths=store_column_widths, repeatRows=1)
        store_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), 2),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 2),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT')
        ]))
        
        story.append(store_table)
        story.append(Spacer(1, 12))
        
        # Category Pricing Strategies
        story.append(Paragraph("Category Pricing Strategies", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        category_headers = [
            Paragraph('Category<br/>ID', normal_style),
            Paragraph('Category', normal_style),
            Paragraph('Pricing<br/>Strategies', normal_style),
            Paragraph('Other<br/>Strategy', normal_style),
            Paragraph('Rational', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        category_rows = [category_headers]
        for category in data.category_pricing_strategis:
            row = [
                Paragraph(str(category.category_id or 0), normal_style),
                Paragraph(category.category or 'N/A', normal_style),
                Paragraph(category.pricing_strategis or 'N/A', normal_style),
                Paragraph(str(category.specify_other_strategy or 'N/A'), normal_style),
                Paragraph(category.rational or 'N/A', normal_style),
                Paragraph(str(category.group_id or 0), normal_style)
            ]
            category_rows.append(row)
        
        category_column_widths = [40, 80, 80, 80, 150, 40]
        
        category_table = Table(category_rows, colWidths=category_column_widths, repeatRows=1)
        category_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), 2),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 2),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT')
        ]))
        
        story.append(category_table)
        story.append(Spacer(1, 12))
        
        # Define Your Promotions Strategy
        story.append(Paragraph("Define Your Promotions Strategy", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        promo_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Define ID', normal_style), Paragraph(str(data.define_your_promotions_strategy.define_id or 0), normal_style)],
            [Paragraph('Reference Competitor', normal_style), Paragraph(str(data.define_your_promotions_strategy.reference_competitor or 'N/A'), normal_style)],
            [Paragraph('Ongoing Key Promotions', normal_style), Paragraph(str(data.define_your_promotions_strategy.ongoing_key_promotions or 'N/A'), normal_style)],
            [Paragraph('Among Key Various Promotion', normal_style), Paragraph(str(data.define_your_promotions_strategy.amoung_key_variouse_promotion or 'N/A'), normal_style)],
            [Paragraph('What Are The Same Key', normal_style), Paragraph(str(data.define_your_promotions_strategy.what_are_the_same_key or 'N/A'), normal_style)],
            [Paragraph('Other Remark', normal_style), Paragraph(str(data.define_your_promotions_strategy.other_remark or 'N/A'), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.define_your_promotions_strategy.group_id or 0), normal_style)]
        ]
        
        promo_table = Table(promo_data, colWidths=[160, 340])
        promo_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), 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(promo_table)
        story.append(Spacer(1, 12))
        
        # Promotional Calendar
        story.append(Paragraph("Promotional Calendar", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        calendar_headers = [
            Paragraph('Calendar<br/>ID', normal_style),
            Paragraph('Month', normal_style),
            Paragraph('Category', normal_style),
            Paragraph('Type of<br/>Promotion', normal_style),
            Paragraph('Success<br/>Metric', normal_style),
            Paragraph('Objective', normal_style),
            Paragraph('Remarks', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        calendar_rows = [calendar_headers]
        for calendar in data.promotional_calendar:
            row = [
                Paragraph(str(calendar.calendar_id or 0), normal_style),
                Paragraph(calendar.month or 'N/A', normal_style),
                Paragraph(calendar.category or 'N/A', normal_style),
                Paragraph(calendar.type_of_promotion or 'N/A', normal_style),
                Paragraph(calendar.success_metric or 'N/A', normal_style),
                Paragraph(calendar.objective or 'N/A', normal_style),
                Paragraph(calendar.remarks or 'N/A', normal_style),
                Paragraph(str(calendar.group_id or 0), normal_style)
            ]
            calendar_rows.append(row)
        
        calendar_column_widths = [35, 50, 60, 60, 60, 80, 100, 35]
        
        calendar_table = Table(calendar_rows, colWidths=calendar_column_widths, repeatRows=1)
        calendar_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), 2),
            ('RIGHTPADDING', (0, 0), (-1, -1), 2),
            ('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(calendar_table)
        story.append(Spacer(1, 12))
        
        # Promotion Competitor Analysis
        story.append(Paragraph("Promotion Competitor Analysis", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        comp_analysis_headers = [
            Paragraph('Promotion<br/>ID', normal_style),
            Paragraph('Competitor<br/>Efforts', normal_style),
            Paragraph('Top Promotion<br/>Types', normal_style),
            Paragraph('Top Categories<br/>for Promotions', normal_style),
            Paragraph('Spillover<br/>Categories', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        comp_analysis_rows = [comp_analysis_headers]
        for comp in data.promotion_competitor_analysis:
            row = [
                Paragraph(str(comp.promotion_id or 0), normal_style),
                Paragraph(comp.competitor_efforts or 'N/A', normal_style),
                Paragraph(comp.top_promotion_types or 'N/A', normal_style),
                Paragraph(comp.top_categories_for_promotions or 'N/A', normal_style),
                Paragraph(comp.spillover_categories or 'N/A', normal_style),
                Paragraph(str(comp.group_id or 0), normal_style)
            ]
            comp_analysis_rows.append(row)
        
        comp_analysis_column_widths = [50, 90, 90, 90, 90, 50]
        
        comp_analysis_table = Table(comp_analysis_rows, colWidths=comp_analysis_column_widths, repeatRows=1)
        comp_analysis_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), 2),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 2),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT')
        ]))
        
        story.append(comp_analysis_table)
        story.append(Spacer(1, 12))
        
        # Recovery of Promotional
        story.append(Paragraph("Recovery of Promotional", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        recovery_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Recovery ID', normal_style), Paragraph(str(data.recovery_of_promotional.recovery_id or 0), normal_style)],
            [Paragraph('Based Understanding', normal_style), Paragraph(str(data.recovery_of_promotional.based_unterstanding or 'N/A'), normal_style)],
            [Paragraph('What Percentage', normal_style), Paragraph(str(data.recovery_of_promotional.what_percentage or 0), normal_style)],
            [Paragraph('What Category', normal_style), Paragraph(str(data.recovery_of_promotional.what_category or 'N/A'), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.recovery_of_promotional.group_id or 0), normal_style)]
        ]
        
        recovery_table = Table(recovery_data, colWidths=[160, 340])
        recovery_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), 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(recovery_table)
        story.append(Spacer(1, 12))
        
        # Categories Under Promotions
        story.append(Paragraph("Categories Under Promotions", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        categories_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Categories ID', normal_style), Paragraph(str(data.categories_under_promotions.categories_id or 0), normal_style)],
            [Paragraph('On Which Category', normal_style), Paragraph(str(data.categories_under_promotions.on_which_category or {}), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.categories_under_promotions.group_id or 0), normal_style)]
        ]
        
        categories_table = Table(categories_data, colWidths=[160, 340])
        categories_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), 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(categories_table)
        story.append(Spacer(1, 12))
        
        # Customer Engagement
        story.append(Paragraph("Customer Engagement", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        engagement_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Customer ID', normal_style), Paragraph(str(data.customer_engagement.customer_id or 0), normal_style)],
            [Paragraph('Promotions Submissions', normal_style), Paragraph(str(data.customer_engagement.promotions_submissions or 'N/A'), normal_style)],
            [Paragraph('Promotions Summarise', normal_style), Paragraph(str(data.customer_engagement.promotions_summarise or 'N/A'), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.customer_engagement.group_id or 0), normal_style)]
        ]
        
        engagement_table = Table(engagement_data, colWidths=[160, 340])
        engagement_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), 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(engagement_table)
        story.append(Spacer(1, 12))
        
        # Competitor Loyalty
        story.append(Paragraph("Competitor Loyalty", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        loyalty_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Competitor ID', normal_style), Paragraph(str(data.competitor_loyalty.competitor_id or 0), normal_style)],
            [Paragraph('Reference Competitor', normal_style), Paragraph(str(data.competitor_loyalty.reference_competitor or 'N/A'), normal_style)],
            [Paragraph('Do They Have Loyalty Program', normal_style), Paragraph(str(data.competitor_loyalty.do_they_have_a_loyalty_program or 'N/A'), normal_style)],
            [Paragraph('Loyalty Program Type', normal_style), Paragraph(str(data.competitor_loyalty.loyalty_program_type or 'N/A'), normal_style)],
            [Paragraph('Key Observations Loyalty', normal_style), Paragraph(str(data.competitor_loyalty.key_observations_loyalty or 'N/A'), normal_style)],
            [Paragraph('Is It Useful For Store', normal_style), Paragraph(str(data.competitor_loyalty.is_it_useful_for_store or 'N/A'), normal_style)],
            [Paragraph('Want Loyalty Program', normal_style), Paragraph(str(data.competitor_loyalty.want_loyalty_program or 'N/A'), normal_style)],
            [Paragraph('Describe Loyalty Program', normal_style), Paragraph(str(data.competitor_loyalty.describe_loyalty_program or 'N/A'), normal_style)],
            [Paragraph('Budget Allocated', normal_style), Paragraph(str(data.competitor_loyalty.budget_allocated or 0), normal_style)],
            [Paragraph('Benefits of Loyalty Program', normal_style), Paragraph(str(data.competitor_loyalty.benefits_of_loyalty_program or 'N/A'), normal_style)],
            [Paragraph('Perils of Loyalty Program', normal_style), Paragraph(str(data.competitor_loyalty.perils_of_loyalty_program or 'N/A'), normal_style)],
            [Paragraph('Difference From Competitor', normal_style), Paragraph(str(data.competitor_loyalty.difference_from_competitor or 'N/A'), normal_style)],
            [Paragraph('Other Notes', normal_style), Paragraph(str(data.competitor_loyalty.other_notes or 'N/A'), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.competitor_loyalty.group_id or 0), normal_style)]
        ]
        
        loyalty_table = Table(loyalty_data, colWidths=[160, 340])
        loyalty_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), 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(loyalty_table)
        story.append(Spacer(1, 12))
        
        # Summary
        story.append(Paragraph("Summary", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        summary_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Summary ID', normal_style), Paragraph(str(data.summary.summary_id or 0), normal_style)],
            [Paragraph('Promotions Submissions', normal_style), Paragraph(str(data.summary.promotions_submissions or 'N/A'), normal_style)],
            [Paragraph('Promotions Summarise', normal_style), Paragraph(str(data.summary.promotions_summarise or 'N/A'), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.summary.group_id or 0), normal_style)]
        ]
        
        summary_table = Table(summary_data, colWidths=[160, 340])
        summary_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), 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(summary_table)
        story.append(Spacer(1, 12))
        
        # 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"module6_pricing_promotions_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_module6_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}_m6"
            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()