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.brand_attribute_assessment import TblBrandAttributeAssessment
from app.models.main.branding_attributes import TblBrandingAttributes
from app.models.main.integrated_marketing import TblIntegratedMarketing
from app.models.main.marketing_competitor_analysis import TblMarketingCompetitor
from app.models.main.optimising_assets_for_revenue import TblOptimisingAssets
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_7_report.schema import (
    BrandAttributeAssessment,
    BrandingAttributes,
    MarketingAndCommunication,
    IntegratedMarketing,
    MarketingCompetitor,
    OptimisingAssets,
    Summary,
    Module7ReportPreview,
)
from app.dependency.authantication import JWTPayloadSchema


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

    async def _fetch_module7_data(self, group_id: int) -> Module7ReportPreview:
        """Fetch module 7 data from all related tables."""
        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(TblBrandAttributeAssessment).filter_by(group_id=group_id).first() or
            self.db.query(TblBrandingAttributes).filter_by(group_id=group_id).first() or
            self.db.query(TblIntegratedMarketing).filter_by(group_id=group_id).first() or
            self.db.query(TblMarketingCompetitor).filter_by(group_id=group_id).first() or
            self.db.query(TblOptimisingAssets).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
            brand_attribute_assessment = self.db.query(TblBrandAttributeAssessment).filter_by(group_id=group_id).first()
            branding_attributes = self.db.query(TblBrandingAttributes).filter_by(group_id=group_id).first()
            marketing_and_communication_row = self.db.query(TblIntegratedMarketing.marketing_budget, TblIntegratedMarketing.rational).filter_by(group_id=group_id).first()
            integrated_marketing = self.db.query(TblIntegratedMarketing).filter_by(group_id=group_id).all()
            marketing_competitor = self.db.query(TblMarketingCompetitor).filter_by(group_id=group_id).first()
            optimising_assets = self.db.query(TblOptimisingAssets).filter_by(group_id=group_id).all()
            summary = self.db.query(TblSummary).filter_by(group_id=group_id).first()

            # Brand Attribute Assessment with list to string conversion
            brand_assessment_data = BrandAttributeAssessment(group_id=group_id)
            if brand_attribute_assessment:
                brand_assessment_data = BrandAttributeAssessment(
                    brand_id=brand_attribute_assessment.brand_id,
                    define_research_methodology=brand_attribute_assessment.define_research_methodology,
                    name_top_10_attributes=', '.join(brand_attribute_assessment.name_top_10_attributes) if isinstance(brand_attribute_assessment.name_top_10_attributes, list) else str(brand_attribute_assessment.name_top_10_attributes or "N/A"),
                    remarks=brand_attribute_assessment.remarks,
                    group_id=brand_attribute_assessment.group_id
                )
            
            # Optimising Assets with list to string conversion
            optimising_assets_data = []
            for oa in optimising_assets:
                optimising_assets_data.append(OptimisingAssets(
                    optimising_id=oa.optimising_id,
                    space_resources_for_income=', '.join(oa.space_resources_for_income) if isinstance(oa.space_resources_for_income, list) else str(oa.space_resources_for_income or "N/A"),
                    non_space_resources_for_income=', '.join(oa.non_space_resources_for_income) if isinstance(oa.non_space_resources_for_income, list) else str(oa.non_space_resources_for_income or "N/A"),
                    percentage_of_sales=oa.percentage_of_sales,
                    predictions_around_changes=', '.join(oa.predictions_around_changes) if isinstance(oa.predictions_around_changes, list) else str(oa.predictions_around_changes or "N/A"),
                    changes_in_marketing_communication=oa.changes_in_marketing_communication,
                    group_id=oa.group_id
                ))
            
            return Module7ReportPreview(
                group_id=group_id,
                brand_attribute_assessment=brand_assessment_data,
                branding_attributes=BrandingAttributes(**branding_attributes.__dict__) if branding_attributes else BrandingAttributes(group_id=group_id),
                marketing_and_communication=MarketingAndCommunication(
                    marketing_budget=marketing_and_communication_row.marketing_budget if marketing_and_communication_row else 0.0,
                    rational=marketing_and_communication_row.rational if marketing_and_communication_row else "N/A",
                    group_id=group_id
                ),
                integrated_marketing=[IntegratedMarketing(**im.__dict__) for im in integrated_marketing] or [IntegratedMarketing(group_id=group_id)],
                marketing_competitor=MarketingCompetitor(**marketing_competitor.__dict__) if marketing_competitor else MarketingCompetitor(group_id=group_id),
                optimising_assets=optimising_assets_data or [OptimisingAssets(group_id=group_id)],
                summary=Summary(**summary.__dict__) 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 Module7ReportPreview(
                    group_id=group_id,
                    brand_attribute_assessment=BrandAttributeAssessment(group_id=group_id),
                    branding_attributes=BrandingAttributes(group_id=group_id),
                    marketing_and_communication=MarketingAndCommunication(group_id=group_id),
                    integrated_marketing=[IntegratedMarketing(group_id=group_id)],
                    marketing_competitor=MarketingCompetitor(group_id=group_id),
                    optimising_assets=[OptimisingAssets(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 _render_html(self, report: Module7ReportPreview) -> str:
        """Render Module 7 report as an HTML preview."""
        template = """
        <html>
        <head>
            <style>
                body { font-family: Arial, sans-serif; margin: 20px; }
                h1, h2 { color: #333; }
                table { width: 100%; border-collapse: collapse; margin-top: 15px; }
                th, td { border: 1px solid #ddd; padding: 8px; }
                th { background-color: #f5f5f5; text-align: left; }
                pre { background: #fafafa; padding: 10px; border: 1px solid #eee; }
            </style>
        </head>
        <body>
            <h1>Module 7 Report – Marketing & Communication</h1>
            <p><b>Group ID:</b> {{ report.group_id }}</p>
            <p><b>Last Updated:</b> {{ report.last_updated }}</p>

            <h2>Brand Attribute Assessment</h2>
            <table>
                <tr><th>Define Research Methodology</th><td>{{ report.brand_attribute_assessment.define_research_methodology }}</td></tr>
                <tr><th>Top 10 Attributes</th><td>{{ report.brand_attribute_assessment.name_top_10_attributes }}</td></tr>
                <tr><th>Remarks</th><td>{{ report.brand_attribute_assessment.remarks }}</td></tr>
            </table>

            <h2>Branding Attributes</h2>
            <table>
                <tr><th>Attributes</th><td>{{ report.branding_attributes.attributes }}</td></tr>
                <tr><th>Reference Retailer 1</th><td>{{ report.branding_attributes.reference_retailer_1 }}</td></tr>
                <tr><th>Reference Retailer 2</th><td>{{ report.branding_attributes.reference_retailer_2 }}</td></tr>
                <tr><th>Own Brand</th><td>{{ report.branding_attributes.own_brand }}</td></tr>
            </table>

            <h2>Marketing & Communication</h2>
            <table>
                <tr><th>Marketing Budget</th><td>{{ report.marketing_and_communication.marketing_budget }}</td></tr>
                <tr><th>Rationale</th><td>{{ report.marketing_and_communication.rational }}</td></tr>
            </table>

            <h2>Integrated Marketing Activities</h2>
            {% for im in report.integrated_marketing %}
            <table>
                <tr><th>Activity Name</th><td>{{ im.activity_name }}</td></tr>
                <tr><th>Budget Allocated</th><td>{{ im.budget_allocated }}</td></tr>
                <tr><th>Objective</th><td>{{ im.objective }}</td></tr>
                <tr><th>Measurement</th><td>{{ im.measurment }}</td></tr>
                <tr><th>Target Shopper Journey</th><td>{{ im.target_shopper_journey }}</td></tr>
                <tr><th>Actions</th><td>{{ im.actions }}</td></tr>
            </table>
            {% endfor %}

            <h2>Marketing Competitor Analysis</h2>
            <table>
                <tr><th>Reference Retailer</th><td>{{ report.marketing_competitor.name_of_the_refereance_retailer }}</td></tr>
                <tr><th>Marketing Communication</th><td>{{ report.marketing_competitor.marketing_communication }}</td></tr>
                <tr><th>Planned Activities</th><td>{{ report.marketing_competitor.plan_activities }}</td></tr>
            </table>

            <h2>Optimising Assets for Revenue</h2>
            {% for oa in report.optimising_assets %}
            <table>
                <tr><th>Space Resources for Income</th><td>{{ oa.space_resources_for_income }}</td></tr>
                <tr><th>Non-space Resources for Income</th><td>{{ oa.non_space_resources_for_income }}</td></tr>
                <tr><th>Percentage of Sales</th><td>{{ oa.percentage_of_sales }}</td></tr>
                <tr><th>Predictions Around Changes</th><td>{{ oa.predictions_around_changes }}</td></tr>
                <tr><th>Changes in Marketing Communication</th><td>{{ oa.changes_in_marketing_communication }}</td></tr>
            </table>
            {% endfor %}

            <h2>Summary</h2>
            <table>
                <tr><th>Marketing Submissions</th><td>{{ report.summary.marketing_submissions }}</td></tr>
                <tr><th>Summary</th><td>{{ report.summary.marketing_summarise }}</td></tr>
            </table>
        </body>
        </html>
        """
        env = Environment(loader=BaseLoader())
        return env.from_string(template).render(report=report)

    def _generate_pdf_report(self, data: Module7ReportPreview) -> 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 7: Marketing & Communications Report", styles['Title']))
        story.append(Spacer(1, 20))
        
        normal_style = styles['Normal']
        normal_style.fontSize = 8
        normal_style.leading = 10
        
        # Brand Attribute Assessment
        story.append(Paragraph("Brand Attribute Assessment", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        brand_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Brand ID', normal_style), Paragraph(str(data.brand_attribute_assessment.brand_id or 0), normal_style)],
            [Paragraph('Define Research Methodology', normal_style), Paragraph(str(data.brand_attribute_assessment.define_research_methodology or 'N/A'), normal_style)],
            [Paragraph('Name Top 10 Attributes', normal_style), Paragraph(str(data.brand_attribute_assessment.name_top_10_attributes or 'N/A'), normal_style)],
            [Paragraph('Remarks', normal_style), Paragraph(str(data.brand_attribute_assessment.remarks or 'N/A'), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.brand_attribute_assessment.group_id or 0), normal_style)]
        ]
        
        brand_table = Table(brand_data, colWidths=[160, 340])
        brand_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(brand_table)
        story.append(Spacer(1, 12))
        
        # Branding Attributes
        story.append(Paragraph("Branding Attributes", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        branding_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Brand ID', normal_style), Paragraph(str(data.branding_attributes.brand_id or 0), normal_style)],
            [Paragraph('Attributes', normal_style), Paragraph(str(data.branding_attributes.attributes or 'N/A'), normal_style)],
            [Paragraph('Reference Retailer 1', normal_style), Paragraph(str(data.branding_attributes.reference_retailer_1 or 'N/A'), normal_style)],
            [Paragraph('Reference Retailer 2', normal_style), Paragraph(str(data.branding_attributes.reference_retailer_2 or 'N/A'), normal_style)],
            [Paragraph('Own Brand', normal_style), Paragraph(str(data.branding_attributes.own_brand or 'N/A'), normal_style)],
            [Paragraph('Brand Positioning Statement', normal_style), Paragraph(str(data.branding_attributes.brand_positioning_statement or 'N/A'), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.branding_attributes.group_id or 0), normal_style)]
        ]
        
        branding_table = Table(branding_data, colWidths=[160, 340])
        branding_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(branding_table)
        story.append(Spacer(1, 12))
        
        # Marketing and Communication
        story.append(Paragraph("Marketing and Communication", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        marketing_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Integrated ID', normal_style), Paragraph(str(data.marketing_and_communication.integrated_id or 0), normal_style)],
            [Paragraph('Marketing Budget', normal_style), Paragraph(str(data.marketing_and_communication.marketing_budget or 0), normal_style)],
            [Paragraph('Rational', normal_style), Paragraph(str(data.marketing_and_communication.rational or 'N/A'), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.marketing_and_communication.group_id or 0), normal_style)]
        ]
        
        marketing_table = Table(marketing_data, colWidths=[160, 340])
        marketing_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(marketing_table)
        story.append(Spacer(1, 12))
        
        # Integrated Marketing
        story.append(Paragraph("Integrated Marketing", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        integrated_headers = [
            Paragraph('Integrated<br/>ID', normal_style),
            Paragraph('Activity<br/>Name', normal_style),
            Paragraph('Budget<br/>Allocated', normal_style),
            Paragraph('Objective', normal_style),
            Paragraph('Measurement', normal_style),
            Paragraph('Target Shopper<br/>Journey', normal_style),
            Paragraph('Actions', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        integrated_rows = [integrated_headers]
        for integrated in data.integrated_marketing:
            row = [
                Paragraph(str(integrated.integrated_id or 0), normal_style),
                Paragraph(integrated.activity_name or 'N/A', normal_style),
                Paragraph(str(integrated.budget_allocated or 0), normal_style),
                Paragraph(integrated.objective or 'N/A', normal_style),
                Paragraph(integrated.measurment or 'N/A', normal_style),
                Paragraph(integrated.target_shopper_journey or 'N/A', normal_style),
                Paragraph(integrated.actions or 'N/A', normal_style),
                Paragraph(str(integrated.group_id or 0), normal_style)
            ]
            integrated_rows.append(row)
        
        integrated_column_widths = [35, 70, 50, 80, 80, 70, 80, 35]
        
        integrated_table = Table(integrated_rows, colWidths=integrated_column_widths, repeatRows=1)
        integrated_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(integrated_table)
        story.append(Spacer(1, 12))
        
        # Marketing Competitor
        story.append(Paragraph("Marketing Competitor", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        competitor_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Competitor ID', normal_style), Paragraph(str(data.marketing_competitor.competitor_id or 0), normal_style)],
            [Paragraph('Name of Reference Retailer', normal_style), Paragraph(str(data.marketing_competitor.name_of_the_reference_retailer or 'N/A'), normal_style)],
            [Paragraph('Marketing Communication', normal_style), Paragraph(str(data.marketing_competitor.marketing_communication or 'N/A'), normal_style)],
            [Paragraph('Plan Activities', normal_style), Paragraph(str(data.marketing_competitor.plan_activities or 'N/A'), normal_style)],
            [Paragraph('Group ID', normal_style), Paragraph(str(data.marketing_competitor.group_id or 0), normal_style)]
        ]
        
        competitor_table = Table(competitor_data, colWidths=[160, 340])
        competitor_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(competitor_table)
        story.append(Spacer(1, 12))
        
        # Optimising Assets
        story.append(Paragraph("Optimising Assets", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        assets_headers = [
            Paragraph('Optimising<br/>ID', normal_style),
            Paragraph('Space Resources<br/>for Income', normal_style),
            Paragraph('Non-Space Resources<br/>for Income', normal_style),
            Paragraph('Percentage<br/>of Sales', normal_style),
            Paragraph('Predictions Around<br/>Changes', normal_style),
            Paragraph('Changes in Marketing<br/>Communication', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        assets_rows = [assets_headers]
        for asset in data.optimising_assets:
            row = [
                Paragraph(str(asset.optimising_id or 0), normal_style),
                Paragraph(asset.space_resources_for_income or 'N/A', normal_style),
                Paragraph(asset.non_space_resources_for_income or 'N/A', normal_style),
                Paragraph(str(asset.percentage_of_sales or 0), normal_style),
                Paragraph(asset.predictions_around_changes or 'N/A', normal_style),
                Paragraph(asset.changes_in_marketing_communication or 'N/A', normal_style),
                Paragraph(str(asset.group_id or 0), normal_style)
            ]
            assets_rows.append(row)
        
        assets_column_widths = [35, 80, 80, 50, 100, 100, 35]
        
        assets_table = Table(assets_rows, colWidths=assets_column_widths, repeatRows=1)
        assets_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(assets_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('Marketing Submissions', normal_style), Paragraph(str(data.summary.marketing_submissions or 'N/A'), normal_style)],
            [Paragraph('Marketing Summarise', normal_style), Paragraph(str(data.summary.marketing_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"module7_marketing_communications_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_module7_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}_m7"
            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()