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, Image, PageBreak
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>

            <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 = []
        
        # Logo
        try:
            logo = Image("TS Logo.png", width=150, height=75)
            logo.hAlign = 'LEFT'
            story.append(logo)
            story.append(Spacer(1, 10))
        except:
            pass  # Continue without logo if file not found
        
        # Title
        title_style = styles['Title']
        title_style.alignment = 1  # Center alignment
        story.append(Paragraph("Module 7: Marketing &amp; Communications", title_style))
        story.append(Spacer(1, 20))
        
        normal_style = styles['Normal']
        normal_style.fontSize = 8
        normal_style.leading = 10
        
        def format_currency(amount):
            """Format currency with Rs. prefix"""
            if amount is None or amount == 0:
                return "Rs. 0"
            return f"Rs. {amount:,.0f}"
        
        # 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('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)]
        ]
        
        brand_table = Table(brand_data)
        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), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        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('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)]
        ]
        
        branding_table = Table(branding_data)
        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), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        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('Marketing Budget', normal_style), Paragraph(format_currency(data.marketing_and_communication.marketing_budget), normal_style)],
            [Paragraph('Rational', normal_style), Paragraph(str(data.marketing_and_communication.rational or 'N/A'), normal_style)]
        ]
        
        marketing_table = Table(marketing_data)
        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), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        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('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)
        ]
        
        integrated_rows = [integrated_headers]
        for integrated in data.integrated_marketing:
            row = [
                Paragraph(integrated.activity_name or 'N/A', normal_style),
                Paragraph(format_currency(integrated.budget_allocated) if integrated.budget_allocated else 'Rs. 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)
            ]
            integrated_rows.append(row)
        
        integrated_table = Table(integrated_rows, 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), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        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('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)]
        ]
        
        competitor_table = Table(competitor_data)
        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), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        story.append(competitor_table)
        story.append(Spacer(1, 12))
        
        # Optimising Assets - Start on new page
        story.append(PageBreak())
        story.append(Paragraph("Brand Journey", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        assets_headers = [
            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)
        ]
        
        assets_rows = [assets_headers]
        for asset in data.optimising_assets:
            row = [
                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)
            ]
            assets_rows.append(row)
        
        assets_table = Table(assets_rows, 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), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        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('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)]
        ]
        
        summary_table = Table(summary_data)
        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), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('WORDWRAP', (0, 0), (-1, -1), True)
        ]))
        
        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_complete_report_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 os.path.join("uploaded_files", filename)
    
    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()