analytics_tab.py

import gradio as gr
import pandas as pd
from datetime import datetime
import plotly.graph_objects as go
from reportlab.lib.pagesizes import letter
from reportlab.platypus import SimpleDocTemplate, Table, TableStyle, Paragraph, Spacer
from reportlab.lib.styles import getSampleStyleSheet, ParagraphStyle
from reportlab.lib import colors
from reportlab.lib.units import inch
import os

class AnalyticsTab:
    def __init__(self, get_sheet_func, get_farmers_func, format_indian_currency_func, format_date_display_func):
        """
        Initialize the Analytics Tab
        
        Args:
            get_sheet_func: Function to get Google Sheet
            get_farmers_func: Function to get list of farmers
            format_indian_currency_func: Function to format currency in Indian style
            format_date_display_func: Function to format date display
        """
        self.get_sheet = get_sheet_func
        self.get_farmers = get_farmers_func
        self.format_indian_currency = format_indian_currency_func
        self.format_date_display = format_date_display_func
    
    def get_all_transactions(self, farmer_name=None):
        """Get all transactions without interest calculations"""
        try:
            spreadsheet = self.get_sheet()
            trans_sheet = spreadsheet.worksheet("Transactions")
            data = trans_sheet.get_all_values()
            
            if len(data) <= 1:
                return pd.DataFrame()
            
            df = pd.DataFrame(data[1:], columns=data[0])
            
            # Filter by farmer if specified
            if farmer_name and farmer_name.strip():
                df = df[df['Farmer Name'] == farmer_name.strip()]
            
            if df.empty:
                return pd.DataFrame()
            
            df['Amount'] = pd.to_numeric(df['Amount'], errors='coerce')
            df['Date'] = pd.to_datetime(df['Date'], errors='coerce')
            df = df.sort_values('Date')
            
            # Calculate running balance without interest
            df['Balance'] = df['Amount'].cumsum()
            
            return df
        except Exception as e:
            print(f"Error getting transactions: {str(e)}")
            return pd.DataFrame()
    
    def refresh_farmers(self):
        """Refresh farmer dropdown"""
        return gr.Dropdown(choices=self.get_farmers())
    
    def create_transactions_pdf(self, farmer_name, df):
        """Generate PDF report of transactions without interest"""
        try:
            # Create PDF with custom name
            if farmer_name and farmer_name.strip():
                pdf_filename = f"{farmer_name.replace(' ', '_')}_transactions_without_interest.pdf"
            else:
                pdf_filename = "all_transactions.pdf"
            
            # Remove old file if exists
            if os.path.exists(pdf_filename):
                os.remove(pdf_filename)
            
            doc = SimpleDocTemplate(pdf_filename, pagesize=letter, rightMargin=30, leftMargin=30, topMargin=30, bottomMargin=18)
            
            elements = []
            styles = getSampleStyleSheet()
            
            # Title
            title_style = ParagraphStyle(
                'CustomTitle',
                parent=styles['Heading1'],
                fontSize=24,
                textColor=colors.HexColor('#1f77b4'),
                spaceAfter=30,
                alignment=1
            )
            
            title_text = f"Transaction History: {farmer_name}" if farmer_name and farmer_name.strip() else "All Transactions History"
            elements.append(Paragraph(title_text, title_style))
            elements.append(Spacer(1, 12))
            
            # Summary statistics
            total_outgoing = abs(df[df['Amount'] < 0]['Amount'].sum())
            total_incoming = df[df['Amount'] > 0]['Amount'].sum()
            final_balance = df['Balance'].iloc[-1] if not df.empty else 0
            
            summary_data = [
                ['Metric', 'Amount (₹)'],
                ['Total Outgoing', self.format_indian_currency(total_outgoing)],
                ['Total Incoming', self.format_indian_currency(total_incoming)],
                ['Final Balance', self.format_indian_currency(final_balance)]
            ]
            
            summary_table = Table(summary_data, colWidths=[3*inch, 3*inch])
            summary_table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#1f77b4')),
                ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, 0), 14),
                ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
                ('BACKGROUND', (0, 1), (-1, -1), colors.beige),
                ('GRID', (0, 0), (-1, -1), 1, colors.black),
                ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
                ('FONTSIZE', (0, 1), (-1, -1), 12),
                ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.lightgrey]),
            ]))
            elements.append(summary_table)
            elements.append(Spacer(1, 20))
            
            # Transaction history title
            elements.append(Paragraph("Detailed Transaction History (Without Interest)", styles['Heading2']))
            elements.append(Spacer(1, 12))
            
            # Transaction table
            trans_data = [['Date', 'Farmer', 'Description', 'Bank Account', 'Amount (₹)', 'Balance (₹)']]
            
            for _, row in df.iterrows():
                trans_data.append([
                    self.format_date_display(str(row['Date'])[:10]),
                    str(row['Farmer Name']),
                    str(row['Type']),
                    str(row['Bank Account']),
                    self.format_indian_currency(row['Amount']),
                    self.format_indian_currency(row['Balance'])
                ])
            
            trans_table = Table(trans_data, colWidths=[0.9*inch, 1.2*inch, 1.5*inch, 1.2*inch, 1.1*inch, 1.1*inch])
            trans_table.setStyle(TableStyle([
                ('BACKGROUND', (0, 0), (-1, 0), colors.HexColor('#2ca02c')),
                ('TEXTCOLOR', (0, 0), (-1, 0), colors.whitesmoke),
                ('ALIGN', (0, 0), (-1, -1), 'CENTER'),
                ('FONTNAME', (0, 0), (-1, 0), 'Helvetica-Bold'),
                ('FONTSIZE', (0, 0), (-1, 0), 9),
                ('BOTTOMPADDING', (0, 0), (-1, 0), 12),
                ('GRID', (0, 0), (-1, -1), 1, colors.black),
                ('FONTNAME', (0, 1), (-1, -1), 'Helvetica'),
                ('FONTSIZE', (0, 1), (-1, -1), 7),
                ('ROWBACKGROUNDS', (0, 1), (-1, -1), [colors.white, colors.lightgrey]),
            ]))
            elements.append(trans_table)
            
            # Build PDF
            doc.build(elements)
            
            # Return absolute path for Gradio File component
            abs_path = os.path.abspath(pdf_filename)
            if os.path.exists(abs_path):
                file_size = os.path.getsize(abs_path)
                print(f"PDF created successfully: {abs_path}, Size: {file_size} bytes")
                return abs_path
            else:
                print(f"PDF file not found at: {abs_path}")
                return None
                
        except Exception as e:
            print(f"Error creating PDF: {str(e)}")
            import traceback
            traceback.print_exc()
            return None
    
    def get_transactions_display(self, farmer_name=None):
        """Get transactions formatted for display"""
        try:
            df = self.get_all_transactions(farmer_name)
            
            if df.empty:
                if farmer_name and farmer_name.strip():
                    return None, None, f"❌ No transactions found for farmer '{farmer_name}'"
                return None, None, "❌ No transactions found"
            
            # Generate PDF FIRST with original numeric df
            pdf_path = self.create_transactions_pdf(farmer_name, df)
            
            # Then create display dataframe
            display_df = df[['Date', 'Farmer Name', 'Type', 'Bank Account', 'Amount', 'Balance']].copy()
            display_df.columns = ['Date', 'Farmer', 'Description', 'Bank Account', 'Amount (₹)', 'Balance (₹)']
            display_df['Date'] = display_df['Date'].apply(lambda x: self.format_date_display(str(x)[:10]))
            display_df['Amount (₹)'] = display_df['Amount (₹)'].apply(self.format_indian_currency)
            display_df['Balance (₹)'] = display_df['Balance (₹)'].apply(self.format_indian_currency)
            
            farmer_text = f" for {farmer_name}" if farmer_name and farmer_name.strip() else ""
            
            # Debug print
            print(f"PDF Path being returned: {pdf_path}")
            print(f"PDF exists: {os.path.exists(pdf_path) if pdf_path else 'None'}")
            
            return display_df, pdf_path, f"✅ Showing {len(display_df)} transactions{farmer_text}"
        except Exception as e:
            import traceback
            traceback.print_exc()
            return None, None, f"❌ Error loading transactions: {str(e)}"
    
    def clear_transactions(self):
        """Clear transaction display"""
        return None, None, ""
    
    def get_bank_analytics(self, farmer_name=None, month=None, year=None):
        """Get bank-wise analytics with optional filters"""
        try:
            df = self.get_all_transactions(farmer_name)
            
            if df.empty:
                if farmer_name and farmer_name.strip():
                    return None, None, f"❌ No transactions found for farmer '{farmer_name}'"
                return None, None, "❌ No transactions found"
            
            # Apply date filters if provided
            if month and year:
                df = df[(df['Date'].dt.month == int(month)) & (df['Date'].dt.year == int(year))]
            elif year:
                df = df[df['Date'].dt.year == int(year)]
            
            if df.empty:
                return None, None, "❌ No transactions found for the selected period"
            
            # Calculate bank-wise statistics
            bank_stats = []
            banks = df['Bank Account'].unique()
            
            for bank in banks:
                bank_df = df[df['Bank Account'] == bank]
                outgoing = abs(bank_df[bank_df['Amount'] < 0]['Amount'].sum())
                incoming = bank_df[bank_df['Amount'] > 0]['Amount'].sum()
                net = incoming - outgoing
                
                bank_stats.append({
                    'Bank Account': bank,
                    'Outgoing': outgoing,
                    'Incoming': incoming,
                    'Net': net
                })
            
            stats_df = pd.DataFrame(bank_stats)
            
            # Create horizontal bar chart
            fig = go.Figure()
            
            fig.add_trace(go.Bar(
                name='Outgoing',
                y=stats_df['Bank Account'],
                x=stats_df['Outgoing'],
                orientation='h',
                marker_color='#e74c3c',
                text=stats_df['Outgoing'].apply(lambda x: f'₹{self.format_indian_currency(x)}'),
                textposition='auto'
            ))
            
            fig.add_trace(go.Bar(
                name='Incoming',
                y=stats_df['Bank Account'],
                x=stats_df['Incoming'],
                orientation='h',
                marker_color='#27ae60',
                text=stats_df['Incoming'].apply(lambda x: f'₹{self.format_indian_currency(x)}'),
                textposition='auto'
            ))
            
            filter_text = ""
            if farmer_name and farmer_name.strip():
                filter_text = f" - {farmer_name}"
            if month and year:
                filter_text += f" ({datetime(int(year), int(month), 1).strftime('%B %Y')})"
            elif year:
                filter_text += f" ({year})"
            
            fig.update_layout(
                title=f"Bank-wise Transaction Analytics{filter_text}",
                xaxis_title="Amount (₹)",
                yaxis_title="Bank Account",
                barmode='group',
                height=350,
                font=dict(size=11),
                showlegend=True,
                margin=dict(l=20, r=20, t=60, b=20)
            )
            
            # Create compact summary HTML with black text
            summary_html = f"""
            <div style="background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); padding: 20px; border-radius: 10px; margin: 10px 0;">
                <h3 style="color: white; text-align: center; margin: 0 0 15px 0; font-size: 20px;">📊 Bank-wise Analytics{filter_text}</h3>
            """
            
            for _, row in stats_df.iterrows():
                summary_html += f"""
                <div style="background-color: white; border-radius: 8px; padding: 15px; margin-bottom: 10px;">
                    <h4 style="color: #2c3e50; margin: 0 0 10px 0; font-size: 16px;">🏦 {row['Bank Account']}</h4>
                    <div style="display: grid; grid-template-columns: repeat(3, 1fr); gap: 10px; font-size: 14px;">
                        <div style="text-align: center;">
                            <div style="color: #7f8c8d; font-size: 12px;">Outgoing</div>
                            <div style="color: #e74c3c; font-weight: bold; font-size: 16px;">₹{self.format_indian_currency(row['Outgoing'])}</div>
                        </div>
                        <div style="text-align: center;">
                            <div style="color: #7f8c8d; font-size: 12px;">Incoming</div>
                            <div style="color: #27ae60; font-weight: bold; font-size: 16px;">₹{self.format_indian_currency(row['Incoming'])}</div>
                        </div>
                        <div style="text-align: center;">
                            <div style="color: #7f8c8d; font-size: 12px;">Net</div>
                            <div style="color: {'#27ae60' if row['Net'] >= 0 else '#e74c3c'}; font-weight: bold; font-size: 16px;">₹{self.format_indian_currency(abs(row['Net']))}</div>
                        </div>
                    </div>
                </div>
                """
            
            summary_html += "</div>"
            
            return fig, summary_html, "✅ Analytics generated successfully"
        
        except Exception as e:
            return None, None, f"❌ Error generating analytics: {str(e)}"
    
    def clear_analytics(self):
        """Clear analytics display"""
        return None, None, ""
    
    def get_daily_transactions(self, selected_date):
        """Get day-wise transaction details for all farmers"""
        try:
            if not selected_date:
                return None, "❌ Please select a date"
            
            df = self.get_all_transactions()
            
            if df.empty:
                return None, "❌ No transactions found"
            
            # Parse date in DD-MM-YY format
            try:
                # Try DD-MM-YY format
                parts = selected_date.strip().split('-')
                if len(parts) == 3:
                    day, month, year = parts
                    if len(year) == 2:
                        year = f"20{year}"
                    selected_dt = pd.to_datetime(f"{year}-{month.zfill(2)}-{day.zfill(2)}")
                else:
                    return None, "❌ Invalid date format. Please use DD-MM-YY"
            except:
                return None, "❌ Invalid date format. Please use DD-MM-YY"
            
            # Filter for selected date
            daily_df = df[df['Date'].dt.date == selected_dt.date()]
            
            if daily_df.empty:
                return None, f"❌ No transactions found for {self.format_date_display(selected_dt.strftime('%Y-%m-%d'))}"
            
            # Create summary table
            outgoing_total = abs(daily_df[daily_df['Amount'] < 0]['Amount'].sum())
            incoming_total = daily_df[daily_df['Amount'] > 0]['Amount'].sum()
            
            # Get outgoing details
            outgoing_details = []
            outgoing_df = daily_df[daily_df['Amount'] < 0]
            for _, row in outgoing_df.iterrows():
                outgoing_details.append({
                    'Farmer': row['Farmer Name'],
                    'Bank Account': row['Bank Account'],
                    'Amount': abs(row['Amount'])
                })
            
            # Get incoming details
            incoming_details = []
            incoming_df = daily_df[daily_df['Amount'] > 0]
            for _, row in incoming_df.iterrows():
                incoming_details.append({
                    'Farmer': row['Farmer Name'],
                    'Bank Account': row['Bank Account'],
                    'Amount': row['Amount']
                })
            
            # Create compact HTML summary with black text
            formatted_date = self.format_date_display(selected_dt.strftime('%Y-%m-%d'))
            summary_html = f"""
            <div style="background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%); padding: 20px; border-radius: 10px; margin: 10px 0;">
                <h3 style="color: white; text-align: center; margin: 0 0 15px 0; font-size: 20px;">📅 Daily Summary: {formatted_date}</h3>
                
                <div style="display: grid; grid-template-columns: 1fr 1fr; gap: 15px; margin-bottom: 15px;">
                    <div style="background-color: white; border-radius: 8px; padding: 15px; text-align: center;">
                        <div style="color: #e74c3c; font-size: 14px; font-weight: bold; margin-bottom: 5px;">💸 Outgoing</div>
                        <div style="font-size: 24px; font-weight: bold; color: #2c3e50; margin-bottom: 5px;">₹{self.format_indian_currency(outgoing_total)}</div>
                        <div style="color: #7f8c8d; font-size: 12px;">{len(outgoing_details)} transaction(s)</div>
                    </div>
                    
                    <div style="background-color: white; border-radius: 8px; padding: 15px; text-align: center;">
                        <div style="color: #27ae60; font-size: 14px; font-weight: bold; margin-bottom: 5px;">💰 Incoming</div>
                        <div style="font-size: 24px; font-weight: bold; color: #2c3e50; margin-bottom: 5px;">₹{self.format_indian_currency(incoming_total)}</div>
                        <div style="color: #7f8c8d; font-size: 12px;">{len(incoming_details)} transaction(s)</div>
                    </div>
                </div>
            """
            
            # Add outgoing details with black text
            if outgoing_details:
                summary_html += """
                <div style="background-color: white; border-radius: 8px; padding: 15px; margin-bottom: 10px;">
                    <h4 style="color: #e74c3c; margin: 0 0 10px 0; font-size: 16px;">📤 Outgoing Transactions</h4>
                    <table style="width: 100%; border-collapse: collapse; font-size: 13px;">
                        <tr style="background-color: #f8f9fa;">
                            <th style="padding: 8px; text-align: left; border-bottom: 2px solid #dee2e6; color: #2c3e50;">Farmer</th>
                            <th style="padding: 8px; text-align: left; border-bottom: 2px solid #dee2e6; color: #2c3e50;">Bank Account</th>
                            <th style="padding: 8px; text-align: right; border-bottom: 2px solid #dee2e6; color: #2c3e50;">Amount</th>
                        </tr>
                """
                for detail in outgoing_details:
                    summary_html += f"""
                        <tr>
                            <td style="padding: 8px; border-bottom: 1px solid #dee2e6; color: #2c3e50;">{detail['Farmer']}</td>
                            <td style="padding: 8px; border-bottom: 1px solid #dee2e6; color: #2c3e50;">{detail['Bank Account']}</td>
                            <td style="padding: 8px; border-bottom: 1px solid #dee2e6; text-align: right; color: #e74c3c; font-weight: bold;">₹{self.format_indian_currency(detail['Amount'])}</td>
                        </tr>
                    """
                summary_html += "</table></div>"
            
            # Add incoming details with black text
            if incoming_details:
                summary_html += """
                <div style="background-color: white; border-radius: 8px; padding: 15px;">
                    <h4 style="color: #27ae60; margin: 0 0 10px 0; font-size: 16px;">📥 Incoming Transactions</h4>
                    <table style="width: 100%; border-collapse: collapse; font-size: 13px;">
                        <tr style="background-color: #f8f9fa;">
                            <th style="padding: 8px; text-align: left; border-bottom: 2px solid #dee2e6; color: #2c3e50;">Farmer</th>
                            <th style="padding: 8px; text-align: left; border-bottom: 2px solid #dee2e6; color: #2c3e50;">Bank Account</th>
                            <th style="padding: 8px; text-align: right; border-bottom: 2px solid #dee2e6; color: #2c3e50;">Amount</th>
                        </tr>
                """
                for detail in incoming_details:
                    summary_html += f"""
                        <tr>
                            <td style="padding: 8px; border-bottom: 1px solid #dee2e6; color: #2c3e50;">{detail['Farmer']}</td>
                            <td style="padding: 8px; border-bottom: 1px solid #dee2e6; color: #2c3e50;">{detail['Bank Account']}</td>
                            <td style="padding: 8px; border-bottom: 1px solid #dee2e6; text-align: right; color: #27ae60; font-weight: bold;">₹{self.format_indian_currency(detail['Amount'])}</td>
                        </tr>
                    """
                summary_html += "</table></div>"
            
            summary_html += "</div>"
            
            return summary_html, f"✅ Found {len(daily_df)} transaction(s) for {formatted_date}"
        
        except Exception as e:
            import traceback
            traceback.print_exc()
            return None, f"❌ Error getting daily transactions: {str(e)}"
            
    def clear_daily(self):
        """Clear daily transactions display"""
        return None, ""
    
    def create_tab(self):
        """Create the Analytics tab interface"""
        with gr.Tab("📈 Analytics & Insights"):
            gr.Markdown("## Transaction Analytics Dashboard")
            
            # Section 1: All Transactions
            with gr.Accordion("📋 All Transactions (Without Interest)", open=False):
                gr.Markdown("*View complete transaction history for a specific farmer*")
                
                with gr.Row():
                    trans_farmer = gr.Dropdown(
                        label="Select Farmer (Optional - leave empty for all)",
                        choices=[],
                        allow_custom_value=True,
                        value=None,
                        scale=9
                    )
                    refresh_trans_farmer_btn = gr.Button("🔄", scale=1, size="sm")
                
                with gr.Row():
                    load_trans_btn = gr.Button("Load Transactions", variant="primary")
                    clear_trans_btn = gr.Button("Clear", variant="secondary")
                
                trans_status = gr.Textbox(label="Status", interactive=False, show_label=False)
                
                # PDF Download
                with gr.Row():
                    trans_pdf_download = gr.File(label="📄 Download Transactions PDF", interactive=False, visible=True)
                
                all_trans_df = gr.Dataframe(
                    label="Transaction History",
                    interactive=False,
                    wrap=True
                )
            
            gr.Markdown("---")
            
            # Section 2: Bank-wise Analytics
            with gr.Accordion("🏦 Bank-wise Analytics", open=False):
                gr.Markdown("*Analyze incoming and outgoing transactions by bank account*")
                
                with gr.Row():
                    analytics_farmer = gr.Dropdown(
                        label="Select Farmer (Optional - leave empty for all)",
                        choices=[],
                        allow_custom_value=True,
                        value=None,
                        scale=9
                    )
                    refresh_analytics_farmer_btn = gr.Button("🔄", scale=1, size="sm")
                
                with gr.Row():
                    month_filter = gr.Dropdown(
                        label="Month",
                        choices=["All"] + [str(i) for i in range(1, 13)],
                        value="All"
                    )
                    year_filter = gr.Dropdown(
                        label="Year",
                        choices=["All"] + [str(year) for year in range(2020, 2031)],
                        value="All"
                    )
                
                with gr.Row():
                    generate_analytics_btn = gr.Button("Generate Analytics", variant="primary")
                    clear_analytics_btn = gr.Button("Clear", variant="secondary")
                
                analytics_status = gr.Textbox(label="Status", interactive=False, show_label=False)
                analytics_summary = gr.HTML()
                analytics_chart = gr.Plot(label="Bank-wise Comparison")
            
            gr.Markdown("---")
            
            # Section 3: Daily Transaction Details
            with gr.Accordion("📅 Daily Transaction Details", open=False):
                gr.Markdown("*View all transactions for a specific date across all farmers*")
                
                daily_date = gr.Textbox(
                    label="Date (DD-MM-YY)",
                    placeholder="15-01-24"
                )
                
                with gr.Row():
                    get_daily_btn = gr.Button("Get Daily Details", variant="primary")
                    clear_daily_btn = gr.Button("Clear", variant="secondary")
                
                daily_status = gr.Textbox(label="Status", interactive=False, show_label=False)
                daily_summary = gr.HTML()
            
            # Event handlers
            refresh_trans_farmer_btn.click(
                fn=self.refresh_farmers,
                outputs=[trans_farmer]
            )
            
            refresh_analytics_farmer_btn.click(
                fn=self.refresh_farmers,
                outputs=[analytics_farmer]
            )
            
            load_trans_btn.click(
                fn=self.get_transactions_display,
                inputs=[trans_farmer],
                outputs=[all_trans_df, trans_pdf_download, trans_status]
            )
            
            clear_trans_btn.click(
                fn=self.clear_transactions,
                inputs=[],
                outputs=[all_trans_df, trans_pdf_download, trans_status]
            )
            
            generate_analytics_btn.click(
                fn=lambda f, m, y: self.get_bank_analytics(
                    f if f else None,
                    m if m != "All" else None,
                    y if y != "All" else None
                ),
                inputs=[analytics_farmer, month_filter, year_filter],
                outputs=[analytics_chart, analytics_summary, analytics_status]
            )
            
            clear_analytics_btn.click(
                fn=self.clear_analytics,
                inputs=[],
                outputs=[analytics_chart, analytics_summary, analytics_status]
            )
            
            get_daily_btn.click(
                fn=self.get_daily_transactions,
                inputs=[daily_date],
                outputs=[daily_summary, daily_status]
            )
            
            clear_daily_btn.click(
                fn=self.clear_daily,
                inputs=[],
                outputs=[daily_summary, daily_status]
            )