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

from app.models.main.vendor_metrics_priority_list import TblVendorMetricsPriorityList
from app.models.main.vendor_scorecard import TblVendorScorecard
from app.models.main.private_lablling import TblPrivateLablling
from app.models.main.category_captinship_planning import TblCategoryCaptinshipPlanning
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_5_report.schema import (
    VendorPriorityMatrix,
    VendorScorecard,
    PrivateLabelling,
    CategoryCaptainship,
    PrimaryResearch,
    DamageExpiry,
    Summary,
    Module5ReportPreview,
)
from app.dependency.authantication import JWTPayloadSchema


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

    async def _fetch_module5_data(self, group_id: int) -> Module5ReportPreview:
        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(TblVendorMetricsPriorityList).filter_by(group_id=group_id).first() or
            self.db.query(TblVendorScorecard).filter_by(group_id=group_id).first() or
            self.db.query(TblPrivateLablling).filter_by(group_id=group_id).first() or
            self.db.query(TblCategoryCaptinshipPlanning).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
            vendor_priority = self.db.query(TblVendorMetricsPriorityList).filter_by(group_id=group_id).all()
            vendor_scorecard = self.db.query(TblVendorScorecard).filter_by(group_id=group_id).all()
            private_labelling = self.db.query(TblPrivateLablling).filter_by(group_id=group_id).all()
            category_captainship = self.db.query(TblCategoryCaptinshipPlanning).filter_by(group_id=group_id).all()
            summary = self.db.query(TblSummary).filter_by(group_id=group_id).first()

            return Module5ReportPreview(
                group_id=group_id,
                primary_research=PrimaryResearch(
                    profiles_interviewed="N/A",
                    insights_gathered="N/A"
                ),
                vendor_priority_matrix=[VendorPriorityMatrix(**vp.__dict__) for vp in vendor_priority] or [VendorPriorityMatrix()],
                vendor_scorecard=[VendorScorecard(**vs.__dict__) for vs in vendor_scorecard] or [VendorScorecard()],
                damage_expiry=DamageExpiry(
                    negotiation_strategy="N/A",
                    recovery_percentage=None,
                    predictions="N/A"
                ),
                private_labelling=[PrivateLabelling(**pl.__dict__) for pl in private_labelling] or [PrivateLabelling()],
                category_captainship=[CategoryCaptainship(**cc.__dict__) for cc in category_captainship] or [CategoryCaptainship()],
                summary=Summary(**summary.__dict__) if summary else Summary(),
                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 Module5ReportPreview(
                    group_id=group_id,
                    primary_research=PrimaryResearch(
                        profiles_interviewed="N/A",
                        insights_gathered="N/A"
                    ),
                    vendor_priority_matrix=[VendorPriorityMatrix(group_id=group_id)],
                    vendor_scorecard=[VendorScorecard(group_id=group_id)],
                    damage_expiry=DamageExpiry(
                        negotiation_strategy="N/A",
                        recovery_percentage=0.0,
                        predictions="N/A"
                    ),
                    private_labelling=[PrivateLabelling(group_id=group_id)],
                    category_captainship=[CategoryCaptainship(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) -> Module5ReportPreview:
        return await self._fetch_module5_data(group_id)
    
    def _generate_pdf_report(self, data: Module5ReportPreview) -> 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 5: Vendor Management Report", styles['Title']))
        story.append(Spacer(1, 20))
        
        # Primary Research
        normal_style = styles['Normal']
        normal_style.fontSize = 8
        normal_style.leading = 10
        
        story.append(Paragraph("Primary Research", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        research_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Profiles Interviewed', normal_style), Paragraph(str(data.primary_research.profiles_interviewed or 'N/A'), normal_style)],
            [Paragraph('Insights Gathered', normal_style), Paragraph(str(data.primary_research.insights_gathered or 'N/A'), normal_style)]
        ]
        
        research_table = Table(research_data, colWidths=[160, 340])
        research_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(research_table)
        story.append(Spacer(1, 12))
        
        # Vendor Priority Matrix
        story.append(Paragraph("Vendor Priority Matrix", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        headers = [
            Paragraph('Vendor<br/>ID', normal_style),
            Paragraph('Priority', normal_style),
            Paragraph('Area', normal_style),
            Paragraph('Metrics', normal_style),
            Paragraph('Remarks', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        table_rows = [headers]
        for vendor in data.vendor_priority_matrix:
            row = [
                Paragraph(str(vendor.vendor_id or 0), normal_style),
                Paragraph(str(vendor.priority or 0), normal_style),
                Paragraph(vendor.area or 'N/A', normal_style),
                Paragraph(vendor.metrics or 'N/A', normal_style),
                Paragraph(vendor.remarks or 'N/A', normal_style),
                Paragraph(str(vendor.group_id or 0), normal_style)
            ]
            table_rows.append(row)
        
        column_widths = [40, 50, 100, 120, 140, 50]
        
        vendor_table = Table(table_rows, colWidths=column_widths, repeatRows=1)
        vendor_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(vendor_table)
        story.append(Spacer(1, 12))
        
        # Vendor Scorecard
        story.append(Paragraph("Vendor Scorecard", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        scorecard_headers = [
            Paragraph('Scorecard<br/>ID', normal_style),
            Paragraph('Metric', normal_style),
            Paragraph('Weightage', normal_style),
            Paragraph('Importance', normal_style),
            Paragraph('BATNA', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        scorecard_rows = [scorecard_headers]
        for scorecard in data.vendor_scorecard:
            row = [
                Paragraph(str(scorecard.vendor_scorecard_id or 0), normal_style),
                Paragraph(scorecard.metric or 'N/A', normal_style),
                Paragraph(str(scorecard.weightage or 0), normal_style),
                Paragraph(scorecard.importance or 'N/A', normal_style),
                Paragraph(scorecard.batna or 'N/A', normal_style),
                Paragraph(str(scorecard.group_id or 0), normal_style)
            ]
            scorecard_rows.append(row)
        
        scorecard_column_widths = [50, 100, 60, 80, 160, 50]
        
        scorecard_table = Table(scorecard_rows, colWidths=scorecard_column_widths, repeatRows=1)
        scorecard_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(scorecard_table)
        story.append(Spacer(1, 12))
        
        # Damage Expiry
        story.append(Paragraph("Damage Expiry", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        damage_data = [
            [Paragraph('<b>Field</b>', normal_style), Paragraph('<b>Value</b>', normal_style)],
            [Paragraph('Negotiation Strategy', normal_style), Paragraph(str(data.damage_expiry.negotiation_strategy or 'N/A'), normal_style)],
            [Paragraph('Recovery Percentage', normal_style), Paragraph(str(data.damage_expiry.recovery_percentage or 'null'), normal_style)],
            [Paragraph('Predictions', normal_style), Paragraph(str(data.damage_expiry.predictions or 'N/A'), normal_style)]
        ]
        
        damage_table = Table(damage_data, colWidths=[160, 340])
        damage_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(damage_table)
        story.append(Spacer(1, 12))
        
        # Private Labelling
        story.append(Paragraph("Private Labelling", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        private_headers = [
            Paragraph('Private<br/>ID', normal_style),
            Paragraph('Category Name', normal_style),
            Paragraph('Private Label', normal_style),
            Paragraph('Rational', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        private_rows = [private_headers]
        for private in data.private_labelling:
            row = [
                Paragraph(str(private.private_id or 0), normal_style),
                Paragraph(private.category_name or 'N/A', normal_style),
                Paragraph(private.private_label or 'N/A', normal_style),
                Paragraph(private.rational or 'N/A', normal_style),
                Paragraph(str(private.group_id or 0), normal_style)
            ]
            private_rows.append(row)
        
        private_column_widths = [50, 120, 80, 200, 50]
        
        private_table = Table(private_rows, colWidths=private_column_widths, repeatRows=1)
        private_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(private_table)
        story.append(Spacer(1, 12))
        
        # Category Captainship
        story.append(Paragraph("Category Captainship", styles['Heading2']))
        story.append(Spacer(1, 10))
        
        captainship_headers = [
            Paragraph('Category<br/>Plan ID', normal_style),
            Paragraph('Category Name', normal_style),
            Paragraph('Category<br/>Captainship', normal_style),
            Paragraph('Rational', normal_style),
            Paragraph('Group<br/>ID', normal_style)
        ]
        
        captainship_rows = [captainship_headers]
        for captainship in data.category_captainship:
            row = [
                Paragraph(str(captainship.category_plan_id or 0), normal_style),
                Paragraph(captainship.category_name or 'N/A', normal_style),
                Paragraph(captainship.category_captinship or 'N/A', normal_style),
                Paragraph(captainship.rational or 'N/A', normal_style),
                Paragraph(str(captainship.group_id or 0), normal_style)
            ]
            captainship_rows.append(row)
        
        captainship_column_widths = [50, 120, 80, 200, 50]
        
        captainship_table = Table(captainship_rows, colWidths=captainship_column_widths, repeatRows=1)
        captainship_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(captainship_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('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)],
            [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"module5_vendor_management_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_module5_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}_m5"
            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()