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.lib import colors
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.platypus import SimpleDocTemplate, Paragraph, Spacer, Table, TableStyle, Image

from app.models.main.competitor_category_analyses import TblCompetitorCategoryAnalyses
from app.models.main.pre_selected_caregory import TblPreSelectedCategory
from app.models.main.gross_margin_contributions import TblGrossMarginContribution
from app.models.main.margin_and_sales_contribution_analysis import TblMarginandSalesContributionAnalysis
from app.models.main.summary import TblSummary, SummaryBase
from app.models.main.group import TblGroup
from app.models.main.store_formate import TblStoreFormat

from app.api.module_4_report.schema import (
    CompetitorCategoryAnalysis,
    PreSelectedCategory,
    GrossMarginContribution,
    MarginAndSalesContributionAnalysis,
    Summary,
    StoreFormat,
    CategoryRoleManagement,
    Module4ReportPreview,
)
from app.dependency.authantication import JWTPayloadSchema


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

    async def _fetch_module4_data(self, group_id: int) -> Module4ReportPreview:
        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(TblCompetitorCategoryAnalyses).filter_by(group_id=group_id).first() or
            self.db.query(TblPreSelectedCategory).filter_by(group_id=group_id).first() or
            self.db.query(TblGrossMarginContribution).filter_by(group_id=group_id).first() or
            self.db.query(TblMarginandSalesContributionAnalysis).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_analysis = self.db.query(TblCompetitorCategoryAnalyses).filter_by(group_id=group_id).first()
            pre_selected = self.db.query(TblPreSelectedCategory).filter_by(group_id=group_id).first()
            store_formats = self.db.query(TblStoreFormat).filter_by(group_id=group_id).all()
            gross_margins = self.db.query(TblGrossMarginContribution).filter_by(group_id=group_id).all()
            margin_sales = self.db.query(TblMarginandSalesContributionAnalysis).filter_by(group_id=group_id).first()
            summary = self.db.query(TblSummary).filter_by(group_id=group_id).first()

            return Module4ReportPreview(
                group_id=group_id,
                competitor_analysis=CompetitorCategoryAnalysis(**competitor_analysis.__dict__) if competitor_analysis else CompetitorCategoryAnalysis(),
                pre_selected_categories=PreSelectedCategory(**pre_selected.__dict__) if pre_selected else PreSelectedCategory(),
                store_formats=[StoreFormat(**sf.__dict__) for sf in store_formats] if store_formats else [StoreFormat()],
                category_role_management=[CategoryRoleManagement()],
                gross_margin_contributions=[GrossMarginContribution(**gm.__dict__) for gm in gross_margins] if gross_margins else [GrossMarginContribution()],
                margin_sales_analysis=MarginAndSalesContributionAnalysis(**margin_sales.__dict__) if margin_sales else MarginAndSalesContributionAnalysis(),
                summary=Summary(**summary.__dict__) if summary else Summary(),
                last_updated=pd.Timestamp.now().to_pydatetime()
            )
        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 Module4ReportPreview(
                    group_id=group_id,
                    competitor_analysis=CompetitorCategoryAnalysis(group_id=group_id),
                    pre_selected_categories=PreSelectedCategory(group_id=group_id),
                    store_formats=[StoreFormat(group_id=group_id)],
                    category_role_management=[CategoryRoleManagement(group_id=group_id)],
                    gross_margin_contributions=[GrossMarginContribution(group_id=group_id)],
                    margin_sales_analysis=MarginAndSalesContributionAnalysis(group_id=group_id),
                    summary=Summary(group_id=group_id),
                    last_updated=pd.Timestamp.now().to_pydatetime()
                )
            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) -> Module4ReportPreview:
        return await self._fetch_module4_data(group_id)
    
    def _generate_pdf_report(self, data: Module4ReportPreview) -> str:
        """Generate PDF report from Module4 data in table format"""
        buffer = BytesIO()
        doc = SimpleDocTemplate(buffer, pagesize=A4, leftMargin=30, rightMargin=30, topMargin=30, bottomMargin=30)
        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 = ParagraphStyle('Title', parent=styles['Heading1'], fontSize=14, spaceAfter=15, alignment=1, fontName='Helvetica-Bold')
        story.append(Paragraph("Module 4: Category Management Report", title_style))
        story.append(Spacer(1, 12))
        
        # Section style
        section_style = ParagraphStyle('Section', parent=styles['Heading2'], fontSize=11, spaceAfter=8, fontName='Helvetica-Bold')
        
        # Competitor Analysis
        story.append(Paragraph("Competitor Analysis", section_style))
        comp_data = [
            ['Field', 'Value'],
            ['Reference Competitor', data.competitor_analysis.reference_competitor or 'N/A'],
            ['Number of Categories Stocked', str(data.competitor_analysis.number_of_categories_stocked or 0)],
            ['Key Categories Stocked', data.competitor_analysis.key_categories_stocked or 'N/A'],
            ['Stocking Observations', data.competitor_analysis.stocking_observations or 'N/A'],
            ['Competitor Others Remark', data.competitor_analysis.competitor_others_remark or 'N/A'],
            ['Research Methodology', data.competitor_analysis.research_methodology or 'N/A'],
            ['Shopping Behavior', data.competitor_analysis.shopping_behavior or 'N/A'],
            ['Consumer Others Remark', data.competitor_analysis.consumer_others_remark or 'N/A']
        ]
        comp_table = Table(comp_data)
        comp_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),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('ALIGN', (0, 0), (-1, -1), 'LEFT'),
            ('WORDWRAP', (0, 0), (-1, -1), True),
            ('LEFTPADDING', (0, 0), (-1, -1), 6),
            ('RIGHTPADDING', (0, 0), (-1, -1), 6),
            ('TOPPADDING', (0, 0), (-1, -1), 6),
            ('BOTTOMPADDING', (0, 0), (-1, -1), 6)
        ]))
        story.append(comp_table)
        story.append(Spacer(1, 12))
        
        # Pre Selected Categories
        story.append(Paragraph("Pre Selected Categories", section_style))
        pre_data = [
            ['Field', 'Value'],
            ['Low Value', str(data.pre_selected_categories.low_value or 0)],
            ['Medium Value', str(data.pre_selected_categories.medium_value or 0)],
            ['High Value', str(data.pre_selected_categories.high_value or 0)]
        ]
        pre_table = Table(pre_data, colWidths=[100, 355])
        pre_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),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('LEFTPADDING', (0, 0), (-1, -1), 3),
            ('RIGHTPADDING', (0, 0), (-1, -1), 3)
        ]))
        story.append(pre_table)
        story.append(Spacer(1, 12))
        
        # Store Formats
        story.append(Paragraph("Store Formats", section_style))
        store_headers = ['Store Name', 'Store Format Type', 'Merchandise']
        store_data = [store_headers]
        for store in data.store_formats:
            store_data.append([
                store.store_name or 'N/A',
                store.store_format_type or 'N/A',
                store.merchandise or 'N/A'
            ])
        store_table = Table(store_data)
        store_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),
            ('VALIGN', (0, 0), (-1, -1), 'TOP'),
            ('LEFTPADDING', (0, 0), (-1, -1), 3),
            ('RIGHTPADDING', (0, 0), (-1, -1), 3)
        ]))
        story.append(store_table)
        story.append(Spacer(1, 12))
        
        # Category Role Management
        normal_style = styles['Normal']
        normal_style.fontSize = 8
        normal_style.leading = 10
        
        story.append(Paragraph("Category Role Management", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        header_cells = [
            Paragraph('Category Name', normal_style),
            Paragraph('Category Role', normal_style),
            Paragraph('Sales<br/>Contribution', normal_style),
            Paragraph('Margin<br/>Contribution', normal_style)
        ]
        
        table_rows = [header_cells]
        for role in data.category_role_management:
            row = [
                Paragraph(role.category_name or 'N/A', normal_style),
                Paragraph(role.category_role or 'N/A', normal_style),
                Paragraph(str(role.sales_contribution or 0), normal_style),
                Paragraph(str(role.margin_contribution or 0), normal_style)
            ]
            table_rows.append(row)
        
        # Updated column widths to fit within PDF page (~500pt total)
        column_widths = [130, 130, 120, 120]
        
        role_table = Table(table_rows, repeatRows=1)
        role_table.setStyle(TableStyle([
            ('GRID', (0, 0), (-1, -1), 0.5, 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(role_table)
        story.append(Spacer(1, 12))
        
        # Gross Margin Contributions
        normal_style = styles['Normal']
        normal_style.fontSize = 7
        normal_style.leading = 9
        
        story.append(Paragraph("Gross Margin Contributions", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        headers = [
            Paragraph('Contribution<br/>to Total Sales', normal_style),
            Paragraph('Contribution<br/>to Gross Margin', normal_style),
            Paragraph('Rationale', normal_style)
        ]
        
        table_rows = [headers]
        for gm in data.gross_margin_contributions:
            row = [
                Paragraph(str(gm.contribution_to_total_sales or 0), normal_style),
                Paragraph(str(gm.contribution_to_gross_margin or 0), normal_style),
                Paragraph(gm.rationale or 'N/A', normal_style)
            ]
            table_rows.append(row)
        
        # Fit the table to page width (Total ~520pt); rationale column gets widest
        column_widths = [100, 100, 320]
        
        margin_table = Table(table_rows, repeatRows=1)
        margin_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), 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(margin_table)
        story.append(Spacer(1, 12))
        
        # Margin Sales Analysis
        normal_style = styles['Normal']
        normal_style.fontSize = 8
        normal_style.leading = 10
        
        story.append(Paragraph("Margin Sales Analysis", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        analysis_data = [
            [Paragraph('Field', normal_style), Paragraph('Value', normal_style)],
            [Paragraph('Which category contributes more to sales', normal_style), Paragraph(data.margin_sales_analysis.which_category_do_you_think_contributes_more_to_sales or 'N/A', normal_style)],
            [Paragraph('Which category contributes more to gross margins', normal_style), Paragraph(data.margin_sales_analysis.which_category_do_you_think_contributes_more_to_gross_margins or 'N/A', normal_style)],
            [Paragraph('Which category represents your store', normal_style), Paragraph(data.margin_sales_analysis.which_category_do_you_think_represents_your_store or 'N/A', normal_style)]
        ]
        
        # Set column widths to fit page (total ~520pt standard)
        analysis_table = Table(analysis_data, colWidths=[170, 350])
        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), 4),
            ('RIGHTPADDING', (0, 0), (-1, -1), 4),
            ('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(analysis_table)
        story.append(Spacer(1, 12))
        
        # Summary
        normal_style = styles['Normal']
        normal_style.fontSize = 8
        normal_style.leading = 10
        
        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('Store Submissions', normal_style), Paragraph(str(data.summary.store_submissions or 'null'), normal_style)],
            [Paragraph('Store Summarise', normal_style), Paragraph(str(data.summary.store_summarise or 'null'), normal_style)],
            [Paragraph('Location Submissions', normal_style), Paragraph(str(data.summary.location_submissions or 'null'), normal_style)],
            [Paragraph('Location Summarise', normal_style), Paragraph(str(data.summary.location_summarise or 'null'), normal_style)],
            [Paragraph('Network Submissions', normal_style), Paragraph(str(data.summary.network_submissions or 'null'), normal_style)],
            [Paragraph('Network Summarise', normal_style), Paragraph(str(data.summary.network_summarise or 'null'), normal_style)],
            [Paragraph('Category Submissions', normal_style), Paragraph(str(data.summary.category_submissions or 'null'), normal_style)],
            [Paragraph('Category Summarise', normal_style), Paragraph(str(data.summary.category_summarise or 'null'), normal_style)],
            [Paragraph('Promotions Submissions', normal_style), Paragraph(str(data.summary.promotions_submissions or 'null'), normal_style)],
            [Paragraph('Promotions Summarise', normal_style), Paragraph(str(data.summary.promotions_summarise or 'null'), normal_style)],
            [Paragraph('Marketing Submissions', normal_style), Paragraph(str(data.summary.marketing_submissions or 'null'), normal_style)],
            [Paragraph('Marketing Summarise', normal_style), Paragraph(str(data.summary.marketing_summarise or 'null'), normal_style)],
            [Paragraph('Service Submissions', normal_style), Paragraph(str(data.summary.service_submissions or 'null'), normal_style)],
            [Paragraph('Service Summarise', normal_style), Paragraph(str(data.summary.service_summarise or 'null'), normal_style)],
            [Paragraph('Backend Submissions', normal_style), Paragraph(str(data.summary.backend_submissions or 'null'), normal_style)],
            [Paragraph('Backend Summarise', normal_style), Paragraph(str(data.summary.backend_summarise or 'null'), normal_style)],
            [Paragraph('Metrics Submissions', normal_style), Paragraph(str(data.summary.metrics_submissions or 'null'), normal_style)],
            [Paragraph('Metrics Summarise', normal_style), Paragraph(str(data.summary.metrics_summarise or 'null'), normal_style)]
        ]
        
        # Reduce total width to 500pt to fit within page margins
        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"module4_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_module4_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}_m4"
            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()