#!/usr/bin/env python3
"""
Comprehensive Dashboard Analytics Enhancement Script
This script enhances the dashboard with better charts, analytics, and styling
"""

import os
import django
import sys

# Setup Django environment
os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'branch_system.settings')
django.setup()

from django.db.models import Sum, Count, Q, F, Avg
from django.utils import timezone
from datetime import datetime, timedelta
from loans.models import Loan, Repayment
from users.models import CustomUser
from decimal import Decimal

def generate_enhanced_dashboard_data(branch_id=None):
    """Generate comprehensive dashboard data with enhanced analytics"""
    
    today = timezone.now().date()
    current_month_start = today.replace(day=1)
    last_month_start = (current_month_start - timedelta(days=1)).replace(day=1)
    
    # Base querysets with branch filtering
    loans_qs = Loan.objects.all()
    repayments_qs = Repayment.objects.all()
    users_qs = CustomUser.objects.filter(role='borrower')
    
    if branch_id:
        loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        repayments_qs = repayments_qs.filter(loan__borrower__branch_id=branch_id)
        users_qs = users_qs.filter(branch_id=branch_id)
    
    # Enhanced Loan Performance Analytics
    loan_performance = {
        'total_loans': loans_qs.count(),
        'active_loans': loans_qs.filter(status='active').count(),
        'paid_loans': loans_qs.filter(status='paid').count(),
        'defaulted_loans': loans_qs.filter(status='defaulted').count(),
        'total_disbursed': loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0,
        'total_collected': repayments_qs.aggregate(total=Sum('amount'))['total'] or 0,
    }
    
    # Calculate performance metrics
    loan_performance['outstanding_amount'] = loan_performance['total_disbursed'] - loan_performance['total_collected']
    loan_performance['collection_rate'] = (
        (loan_performance['total_collected'] / loan_performance['total_disbursed'] * 100) 
        if loan_performance['total_disbursed'] > 0 else 0
    )
    loan_performance['default_rate'] = (
        (loan_performance['defaulted_loans'] / loan_performance['total_loans'] * 100)
        if loan_performance['total_loans'] > 0 else 0
    )
    
    # Enhanced Loan Distribution Analytics
    loan_distribution = loans_qs.values('status').annotate(
        count=Count('id'),
        total_amount=Sum('principal_amount')
    ).order_by('status')
    
    distribution_data = {
        'labels': [],
        'counts': [],
        'amounts': [],
        'colors': []
    }
    
    color_map = {
        'active': '#10B981',      # Green
        'paid': '#3B82F6',        # Blue  
        'defaulted': '#EF4444',   # Red
        'rolled_over': '#F59E0B', # Yellow
        'pending': '#8B5CF6'      # Purple
    }
    
    for item in loan_distribution:
        distribution_data['labels'].append(item['status'].title())
        distribution_data['counts'].append(item['count'])
        distribution_data['amounts'].append(float(item['total_amount'] or 0))
        distribution_data['colors'].append(color_map.get(item['status'], '#6B7280'))
    
    # Enhanced Client Growth Analytics
    client_growth_data = []
    monthly_labels = []
    
    for i in range(12):  # Last 12 months
        month_start = current_month_start - timedelta(days=30*i)
        month_end = (month_start + timedelta(days=32)).replace(day=1)
        
        new_clients = users_qs.filter(
            date_joined__gte=month_start,
            date_joined__lt=month_end
        ).count()
        
        total_clients = users_qs.filter(date_joined__lt=month_end).count()
        
        client_growth_data.insert(0, {
            'month': month_start.strftime('%b %Y'),
            'new_clients': new_clients,
            'total_clients': total_clients
        })
        monthly_labels.insert(0, month_start.strftime('%b %Y'))
    
    # Monthly Performance Trends
    monthly_performance = []
    
    for i in range(12):
        month_start = current_month_start - timedelta(days=30*i)
        month_end = (month_start + timedelta(days=32)).replace(day=1)
        
        month_loans = loans_qs.filter(
            created_at__gte=month_start,
            created_at__lt=month_end
        )
        
        month_repayments = repayments_qs.filter(
            payment_date__gte=month_start,
            payment_date__lt=month_end
        )
        
        disbursed = month_loans.aggregate(total=Sum('principal_amount'))['total'] or 0
        collected = month_repayments.aggregate(total=Sum('amount'))['total'] or 0
        
        monthly_performance.insert(0, {
            'month': month_start.strftime('%b %Y'),
            'disbursed': float(disbursed),
            'collected': float(collected),
            'net_flow': float(disbursed - collected),
            'loans_count': month_loans.count()
        })
    
    # Portfolio Quality Analytics
    overdue_loans = loans_qs.filter(
        status='active',
        due_date__lt=today
    )
    
    portfolio_quality = {
        'total_portfolio': loan_performance['total_disbursed'],
        'overdue_count': overdue_loans.count(),
        'overdue_amount': overdue_loans.aggregate(total=Sum('principal_amount'))['total'] or 0,
        'current_loans': loans_qs.filter(status='active', due_date__gte=today).count(),
    }
    
    portfolio_quality['overdue_rate'] = (
        (portfolio_quality['overdue_count'] / loan_performance['active_loans'] * 100)
        if loan_performance['active_loans'] > 0 else 0
    )
    
    # Risk Analytics
    risk_categories = {
        'low_risk': overdue_loans.filter(
            due_date__gte=today - timedelta(days=30)
        ).count(),
        'medium_risk': overdue_loans.filter(
            due_date__lt=today - timedelta(days=30),
            due_date__gte=today - timedelta(days=90)
        ).count(),
        'high_risk': overdue_loans.filter(
            due_date__lt=today - timedelta(days=90)
        ).count(),
    }
    
    return {
        'loan_performance': loan_performance,
        'loan_distribution': distribution_data,
        'client_growth': client_growth_data,
        'monthly_performance': monthly_performance,
        'portfolio_quality': portfolio_quality,
        'risk_categories': risk_categories,
        'monthly_labels': monthly_labels,
        'generated_at': timezone.now().isoformat(),
    }

def create_enhanced_dashboard_view():
    """Create enhanced dashboard view code"""
    
    view_code = '''
@login_required
def enhanced_dashboard(request):
    """Enhanced dashboard with comprehensive analytics"""
    selected_branch_id = request.session.get('selected_branch_id')
    
    try:
        # Generate comprehensive dashboard data
        dashboard_data = generate_enhanced_dashboard_data(branch_id=selected_branch_id)
        
        # Add real-time metrics
        today = timezone.now().date()
        
        # Today's metrics
        todays_loans = Loan.objects.filter(created_at__date=today)
        todays_repayments = Repayment.objects.filter(payment_date__date=today)
        
        if selected_branch_id:
            todays_loans = todays_loans.filter(borrower__branch_id=selected_branch_id)
            todays_repayments = todays_repayments.filter(loan__borrower__branch_id=selected_branch_id)
        
        real_time_metrics = {
            'todays_disbursements': todays_loans.aggregate(total=Sum('principal_amount'))['total'] or 0,
            'todays_collections': todays_repayments.aggregate(total=Sum('amount'))['total'] or 0,
            'todays_loan_count': todays_loans.count(),
            'todays_repayment_count': todays_repayments.count(),
        }
        
        context = {
            'dashboard_data': dashboard_data,
            'real_time_metrics': real_time_metrics,
            'selected_branch_id': selected_branch_id,
            'today': today,
        }
        
        return render(request, 'loans/enhanced_dashboard.html', context)
        
    except Exception as e:
        # Fallback to basic dashboard
        messages.warning(request, f'Enhanced analytics temporarily unavailable: {str(e)}')
        return redirect('dashboard')
'''
    
    return view_code

def create_enhanced_dashboard_template():
    """Create enhanced dashboard template"""
    
    template_code = '''{% extends 'base.html' %}
{% load static %}
{% load humanize %}

{% block title %}Enhanced Dashboard - HAVEN GRAZURI ADVANCE{% endblock %}

{% block extra_css %}
<link rel="stylesheet" href="{% static 'css/reports-enhanced.css' %}">
<style>
.dashboard-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(300px, 1fr));
    gap: 1.5rem;
    margin: 2rem 0;
}

.metric-card {
    background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
    color: white;
    padding: 1.5rem;
    border-radius: 15px;
    box-shadow: 0 10px 25px rgba(102, 126, 234, 0.3);
    transition: all 0.3s ease;
}

.metric-card:hover {
    transform: translateY(-5px);
    box-shadow: 0 15px 35px rgba(102, 126, 234, 0.4);
}

.metric-value {
    font-size: 2.5rem;
    font-weight: 700;
    margin-bottom: 0.5rem;
}

.metric-label {
    font-size: 1rem;
    opacity: 0.9;
}

.chart-grid {
    display: grid;
    grid-template-columns: 2fr 1fr;
    gap: 2rem;
    margin: 2rem 0;
}

@media (max-width: 1024px) {
    .chart-grid {
        grid-template-columns: 1fr;
    }
}
</style>
{% endblock %}

{% block content %}
<div class="report-container">
    <div class="container mx-auto px-4 sm:px-6 lg:px-8 py-6 fade-in">
        
        <!-- Enhanced Header -->
        <div class="report-card slide-up">
            <div class="report-header">
                <div class="flex flex-col sm:flex-row justify-between items-start sm:items-center gap-4">
                    <div>
                        <h1 class="report-title">Enhanced Analytics Dashboard</h1>
                        <p class="report-subtitle">Comprehensive business intelligence and performance metrics</p>
                    </div>
                    <div class="export-buttons">
                        <button onclick="refreshDashboard()" class="action-button secondary">
                            <i class="fas fa-sync-alt"></i> Refresh
                        </button>
                        <a href="{% url 'loans:analytics_dashboard' %}" class="action-button primary">
                            <i class="fas fa-chart-line"></i> Advanced Analytics
                        </a>
                    </div>
                </div>
            </div>
            
            <!-- Real-time Metrics -->
            <div class="dashboard-grid">
                <div class="metric-card">
                    <div class="metric-value">{{ real_time_metrics.todays_loan_count }}</div>
                    <div class="metric-label">Today's Loans</div>
                </div>
                <div class="metric-card">
                    <div class="metric-value">KES {{ real_time_metrics.todays_disbursements|floatformat:0|intcomma }}</div>
                    <div class="metric-label">Today's Disbursements</div>
                </div>
                <div class="metric-card">
                    <div class="metric-value">KES {{ real_time_metrics.todays_collections|floatformat:0|intcomma }}</div>
                    <div class="metric-label">Today's Collections</div>
                </div>
                <div class="metric-card">
                    <div class="metric-value">{{ dashboard_data.loan_performance.collection_rate|floatformat:1 }}%</div>
                    <div class="metric-label">Collection Rate</div>
                </div>
            </div>
        </div>
        
        <!-- Performance Summary -->
        <div class="summary-grid slide-up">
            <div class="summary-card">
                <div class="summary-card-content">
                    <div class="summary-icon blue">
                        <i class="fas fa-chart-line"></i>
                    </div>
                    <div class="summary-details">
                        <h3>Total Portfolio</h3>
                        <p>KES {{ dashboard_data.loan_performance.total_disbursed|floatformat:0|intcomma }}</p>
                    </div>
                </div>
            </div>
            
            <div class="summary-card">
                <div class="summary-card-content">
                    <div class="summary-icon green">
                        <i class="fas fa-money-bill-wave"></i>
                    </div>
                    <div class="summary-details">
                        <h3>Active Loans</h3>
                        <p>{{ dashboard_data.loan_performance.active_loans }}</p>
                    </div>
                </div>
            </div>
            
            <div class="summary-card">
                <div class="summary-card-content">
                    <div class="summary-icon purple">
                        <i class="fas fa-percentage"></i>
                    </div>
                    <div class="summary-details">
                        <h3>Default Rate</h3>
                        <p>{{ dashboard_data.loan_performance.default_rate|floatformat:1 }}%</p>
                    </div>
                </div>
            </div>
            
            <div class="summary-card">
                <div class="summary-card-content">
                    <div class="summary-icon yellow">
                        <i class="fas fa-exclamation-triangle"></i>
                    </div>
                    <div class="summary-details">
                        <h3>Overdue Rate</h3>
                        <p>{{ dashboard_data.portfolio_quality.overdue_rate|floatformat:1 }}%</p>
                    </div>
                </div>
            </div>
        </div>
        
        <!-- Enhanced Charts -->
        <div class="chart-grid slide-up">
            <!-- Performance Trend Chart -->
            <div class="chart-container">
                <div class="chart-header">
                    <h3 class="chart-title">Monthly Performance Trend</h3>
                    <div class="flex gap-2">
                        <button onclick="updatePerformanceChart('6m')" class="action-button secondary" id="perf6m">6M</button>
                        <button onclick="updatePerformanceChart('12m')" class="action-button primary" id="perf12m">12M</button>
                    </div>
                </div>
                <div class="chart-wrapper">
                    <canvas id="performanceChart"></canvas>
                </div>
            </div>
            
            <!-- Loan Distribution Chart -->
            <div class="chart-container">
                <div class="chart-header">
                    <h3 class="chart-title">Loan Status Distribution</h3>
                </div>
                <div class="chart-wrapper">
                    <canvas id="distributionChart"></canvas>
                </div>
            </div>
        </div>
        
        <!-- Client Growth Chart -->
        <div class="chart-container slide-up">
            <div class="chart-header">
                <h3 class="chart-title">Client Growth Trend</h3>
            </div>
            <div class="chart-wrapper">
                <canvas id="clientGrowthChart"></canvas>
            </div>
        </div>
        
        <!-- Risk Analysis -->
        <div class="summary-grid slide-up">
            <div class="summary-card">
                <div class="summary-card-content">
                    <div class="summary-icon green">
                        <i class="fas fa-shield-alt"></i>
                    </div>
                    <div class="summary-details">
                        <h3>Low Risk Loans</h3>
                        <p>{{ dashboard_data.risk_categories.low_risk }}</p>
                    </div>
                </div>
            </div>
            
            <div class="summary-card">
                <div class="summary-card-content">
                    <div class="summary-icon yellow">
                        <i class="fas fa-exclamation-circle"></i>
                    </div>
                    <div class="summary-details">
                        <h3>Medium Risk Loans</h3>
                        <p>{{ dashboard_data.risk_categories.medium_risk }}</p>
                    </div>
                </div>
            </div>
            
            <div class="summary-card">
                <div class="summary-card-content">
                    <div class="summary-icon red">
                        <i class="fas fa-exclamation-triangle"></i>
                    </div>
                    <div class="summary-details">
                        <h3>High Risk Loans</h3>
                        <p>{{ dashboard_data.risk_categories.high_risk }}</p>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>

<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
<script>
// Dashboard data from Django
const dashboardData = {{ dashboard_data|safe }};

// Initialize charts when page loads
document.addEventListener('DOMContentLoaded', function() {
    initializePerformanceChart();
    initializeDistributionChart();
    initializeClientGrowthChart();
});

function initializePerformanceChart() {
    const ctx = document.getElementById('performanceChart').getContext('2d');
    
    new Chart(ctx, {
        type: 'line',
        data: {
            labels: dashboardData.monthly_labels,
            datasets: [
                {
                    label: 'Disbursements',
                    data: dashboardData.monthly_performance.map(d => d.disbursed),
                    borderColor: '#667eea',
                    backgroundColor: 'rgba(102, 126, 234, 0.1)',
                    tension: 0.4,
                    fill: true
                },
                {
                    label: 'Collections',
                    data: dashboardData.monthly_performance.map(d => d.collected),
                    borderColor: '#10b981',
                    backgroundColor: 'rgba(16, 185, 129, 0.1)',
                    tension: 0.4,
                    fill: true
                }
            ]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: { position: 'top' }
            },
            scales: {
                y: {
                    beginAtZero: true,
                    ticks: {
                        callback: function(value) {
                            return 'KES ' + value.toLocaleString();
                        }
                    }
                }
            }
        }
    });
}

function initializeDistributionChart() {
    const ctx = document.getElementById('distributionChart').getContext('2d');
    
    new Chart(ctx, {
        type: 'doughnut',
        data: {
            labels: dashboardData.loan_distribution.labels,
            datasets: [{
                data: dashboardData.loan_distribution.counts,
                backgroundColor: dashboardData.loan_distribution.colors,
                borderWidth: 2,
                borderColor: '#ffffff'
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: { position: 'bottom' }
            }
        }
    });
}

function initializeClientGrowthChart() {
    const ctx = document.getElementById('clientGrowthChart').getContext('2d');
    
    new Chart(ctx, {
        type: 'bar',
        data: {
            labels: dashboardData.client_growth.map(d => d.month),
            datasets: [{
                label: 'New Clients',
                data: dashboardData.client_growth.map(d => d.new_clients),
                backgroundColor: 'rgba(46, 204, 113, 0.8)',
                borderColor: '#2ecc71',
                borderWidth: 2
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            plugins: {
                legend: { position: 'top' }
            },
            scales: {
                y: {
                    beginAtZero: true,
                    ticks: { stepSize: 1 }
                }
            }
        }
    });
}

function refreshDashboard() {
    location.reload();
}

function updatePerformanceChart(period) {
    // Update chart based on period
    document.querySelectorAll('#perf6m, #perf12m').forEach(btn => {
        btn.classList.remove('primary');
        btn.classList.add('secondary');
    });
    
    document.getElementById('perf' + period).classList.remove('secondary');
    document.getElementById('perf' + period).classList.add('primary');
    
    // Here you would fetch new data and update the chart
    // For now, we'll just show the current data
}
</script>
{% endblock %}'''
    
    return template_code

def main():
    """Main function to enhance dashboard analytics"""
    
    print("🚀 Enhancing Dashboard Analytics...")
    
    # Create the enhanced dashboard data function
    print("📊 Creating enhanced analytics functions...")
    
    # Add the function to loans/views.py
    enhanced_function = '''
def generate_enhanced_dashboard_data(branch_id=None):
    """Generate comprehensive dashboard data with enhanced analytics"""
    
    today = timezone.now().date()
    current_month_start = today.replace(day=1)
    
    # Base querysets with branch filtering
    loans_qs = Loan.objects.all()
    repayments_qs = Repayment.objects.all()
    users_qs = CustomUser.objects.filter(role='borrower')
    
    if branch_id:
        loans_qs = loans_qs.filter(borrower__branch_id=branch_id)
        repayments_qs = repayments_qs.filter(loan__borrower__branch_id=branch_id)
        users_qs = users_qs.filter(branch_id=branch_id)
    
    # Enhanced Loan Performance Analytics
    loan_performance = {
        'total_loans': loans_qs.count(),
        'active_loans': loans_qs.filter(status='active').count(),
        'paid_loans': loans_qs.filter(status='paid').count(),
        'defaulted_loans': loans_qs.filter(status='defaulted').count(),
        'total_disbursed': loans_qs.aggregate(total=Sum('principal_amount'))['total'] or 0,
        'total_collected': repayments_qs.aggregate(total=Sum('amount'))['total'] or 0,
    }
    
    # Calculate performance metrics
    loan_performance['outstanding_amount'] = loan_performance['total_disbursed'] - loan_performance['total_collected']
    loan_performance['collection_rate'] = (
        (loan_performance['total_collected'] / loan_performance['total_disbursed'] * 100) 
        if loan_performance['total_disbursed'] > 0 else 0
    )
    loan_performance['default_rate'] = (
        (loan_performance['defaulted_loans'] / loan_performance['total_loans'] * 100)
        if loan_performance['total_loans'] > 0 else 0
    )
    
    # Enhanced Loan Distribution Analytics
    loan_distribution = loans_qs.values('status').annotate(
        count=Count('id'),
        total_amount=Sum('principal_amount')
    ).order_by('status')
    
    distribution_data = {
        'labels': [],
        'counts': [],
        'amounts': [],
        'colors': []
    }
    
    color_map = {
        'active': '#10B981',      # Green
        'paid': '#3B82F6',        # Blue  
        'defaulted': '#EF4444',   # Red
        'rolled_over': '#F59E0B', # Yellow
        'pending': '#8B5CF6'      # Purple
    }
    
    for item in loan_distribution:
        distribution_data['labels'].append(item['status'].title())
        distribution_data['counts'].append(item['count'])
        distribution_data['amounts'].append(float(item['total_amount'] or 0))
        distribution_data['colors'].append(color_map.get(item['status'], '#6B7280'))
    
    # Monthly Performance Trends
    monthly_performance = []
    monthly_labels = []
    
    for i in range(12):
        month_start = current_month_start - timedelta(days=30*i)
        month_end = (month_start + timedelta(days=32)).replace(day=1)
        
        month_loans = loans_qs.filter(
            created_at__gte=month_start,
            created_at__lt=month_end
        )
        
        month_repayments = repayments_qs.filter(
            payment_date__gte=month_start,
            payment_date__lt=month_end
        )
        
        disbursed = month_loans.aggregate(total=Sum('principal_amount'))['total'] or 0
        collected = month_repayments.aggregate(total=Sum('amount'))['total'] or 0
        
        monthly_performance.insert(0, {
            'month': month_start.strftime('%b %Y'),
            'disbursed': float(disbursed),
            'collected': float(collected),
            'net_flow': float(disbursed - collected),
            'loans_count': month_loans.count()
        })
        monthly_labels.insert(0, month_start.strftime('%b %Y'))
    
    # Client Growth Analytics
    client_growth_data = []
    
    for i in range(12):
        month_start = current_month_start - timedelta(days=30*i)
        month_end = (month_start + timedelta(days=32)).replace(day=1)
        
        new_clients = users_qs.filter(
            date_joined__gte=month_start,
            date_joined__lt=month_end
        ).count()
        
        client_growth_data.insert(0, {
            'month': month_start.strftime('%b %Y'),
            'new_clients': new_clients
        })
    
    # Portfolio Quality Analytics
    overdue_loans = loans_qs.filter(
        status='active',
        due_date__lt=today
    )
    
    portfolio_quality = {
        'total_portfolio': loan_performance['total_disbursed'],
        'overdue_count': overdue_loans.count(),
        'overdue_amount': overdue_loans.aggregate(total=Sum('principal_amount'))['total'] or 0,
        'current_loans': loans_qs.filter(status='active', due_date__gte=today).count(),
    }
    
    portfolio_quality['overdue_rate'] = (
        (portfolio_quality['overdue_count'] / loan_performance['active_loans'] * 100)
        if loan_performance['active_loans'] > 0 else 0
    )
    
    # Risk Analytics
    risk_categories = {
        'low_risk': overdue_loans.filter(
            due_date__gte=today - timedelta(days=30)
        ).count(),
        'medium_risk': overdue_loans.filter(
            due_date__lt=today - timedelta(days=30),
            due_date__gte=today - timedelta(days=90)
        ).count(),
        'high_risk': overdue_loans.filter(
            due_date__lt=today - timedelta(days=90)
        ).count(),
    }
    
    return {
        'loan_performance': loan_performance,
        'loan_distribution': distribution_data,
        'client_growth': client_growth_data,
        'monthly_performance': monthly_performance,
        'portfolio_quality': portfolio_quality,
        'risk_categories': risk_categories,
        'monthly_labels': monthly_labels,
        'generated_at': timezone.now().isoformat(),
    }
'''
    
    # Write the enhanced template
    template_content = create_enhanced_dashboard_template()
    
    with open('templates/loans/enhanced_dashboard.html', 'w', encoding='utf-8') as f:
        f.write(template_content)
    
    print("✅ Enhanced dashboard template created!")
    
    # Create enhanced dashboard view
    view_content = create_enhanced_dashboard_view()
    
    print("📝 Enhanced dashboard view code generated!")
    print("🎯 Next steps:")
    print("1. Add the enhanced function to loans/views.py")
    print("2. Add the enhanced dashboard view to loans/views.py") 
    print("3. Add URL pattern for enhanced dashboard")
    print("4. Test the enhanced analytics")
    
    print("\n🚀 Dashboard Analytics Enhancement Complete!")
    
    return {
        'enhanced_function': enhanced_function,
        'enhanced_view': view_content,
        'template_created': True
    }

if __name__ == '__main__':
    main()