5. dynamic_report

import os
import io
import datetime
import pandas as pd
import pyarrow.parquet as pq
import plotly.express as px  # <-- IMPORT PLOTLY

from django.shortcuts import render
from django.http import HttpResponse
from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent.parent
dynamic_summary_folder = BASE_DIR / "datasets" / "dynamics" / "summary"
filename = 'summary.parquet'

GROUPING_COLUMNS = [
    'report_date', 'filial', 'tobo', 'department', 
    'passport', 'avto', 'kategoriya_npl_90', 
    'kategoriya_straxovka', 'sud'
]

SUM_COLUMNS = [
    'vidacha_tekushiy_summa', 'brutto_95_summa', 'brutto_summa', 
    'protsent_16309_summa', 'protsent_16377_summa', 'protsent_16379_summa', 
    'spisat_95413_summa', 'spisat_91501_summa', 'reserve_summa', 
    'pogashen_tekushiy_summa', 'straxovka_summa', 'straxovka_brutto_95_summa', 
    'npl_summa'
]

def dynamic_report(request):
    filepath = f"{dynamic_summary_folder}/{filename}"
    
    # Graceful fallback if file doesn't exist yet
    if not os.path.exists(filepath):
        return HttpResponse("Файл summary.parquet не найден. Пожалуйста, сгенерируйте данные.", status=404)
        
    df = pd.read_parquet(filepath) 
    df['report_date'] = pd.to_datetime(df['report_date'])
    
    filter_options = {
        'filials': sorted(df['filial'].dropna().unique()) if 'filial' in df.columns else [],
        'departments': sorted(df['department'].dropna().unique()) if 'department' in df.columns else [],
        'min_date': df['report_date'].min().strftime('%Y-%m-%d') if not df.empty else '',
        'max_date': df['report_date'].max().strftime('%Y-%m-%d') if not df.empty else '',
    }
    
    start_date = request.GET.get('start_date')
    end_date = request.GET.get('end_date')
    selected_filial = request.GET.get('filial')
    selected_dept = request.GET.get('department')
    
    selected_metrics = request.GET.getlist('metrics') or [SUM_COLUMNS[0]]
    selected_groupings = request.GET.getlist('groupings') or ['report_date', 'department']
    download_format = request.GET.get('download')

    # Filter
    filtered_df = df.copy()
    if start_date:
        filtered_df = filtered_df[filtered_df['report_date'] >= pd.to_datetime(start_date)]
    if end_date:
        filtered_df = filtered_df[filtered_df['report_date'] <= pd.to_datetime(end_date)]
    if selected_filial and selected_filial != 'All':
        filtered_df = filtered_df[filtered_df['filial'] == selected_filial]
    if selected_dept and selected_dept != 'All':
        filtered_df = filtered_df[filtered_df['department'] == selected_dept]

    # Grouping
    if not filtered_df.empty:
        final_df = filtered_df.groupby(selected_groupings)[selected_metrics].sum().reset_index()
        if 'report_date' in final_df.columns:
            final_df['report_date'] = final_df['report_date'].dt.strftime('%Y-%m-%d')
    else:
        final_df = pd.DataFrame(columns=selected_groupings + selected_metrics)

    # ==========================================
    # EXCEL DOWNLOAD
    # ==========================================
    if download_format == 'excel':
        buffer = io.BytesIO()
        with pd.ExcelWriter(buffer, engine='openpyxl') as writer:
            final_df.to_excel(writer, index=False, sheet_name='Dynamic Report')
        buffer.seek(0)
        response = HttpResponse(buffer.getvalue(), content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
        response['Content-Disposition'] = f'attachment; filename="Dynamic_Report_{datetime.datetime.now().strftime("%Y%m%d")}.xlsx"'
        return response

    # ==========================================
    # PLOTLY CHART GENERATION (Python side)
    # ==========================================
    charts = []
    
    if not final_df.empty and selected_metrics:
        # We will chart the FIRST selected metric for simplicity
        target_metric = selected_metrics[0]
        
        # CHART 1: Time Series Trend (If date is selected)
        if 'report_date' in selected_groupings:
            trend_df = final_df.groupby('report_date')[target_metric].sum().reset_index()
            fig1 = px.line(
                trend_df, x='report_date', y=target_metric, 
                title=f"Динамика: {target_metric}", 
                markers=True, 
                color_discrete_sequence=['#2980b9']
            )
            fig1.update_layout(template='plotly_white', margin=dict(l=20, r=20, t=50, b=20), paper_bgcolor='rgba(0,0,0,0)', plot_bgcolor='rgba(0,0,0,0)')
            # Only include plotly.js CDN on the very first chart to save loading time
            charts.append(fig1.to_html(full_html=False, include_plotlyjs='cdn'))

        # CHART 2: Categorical Distribution (e.g., Department, Filial, Passport)
        cat_groupings = [g for g in selected_groupings if g != 'report_date']
        if cat_groupings:
            # Pick the first category to chart
            cat_col = cat_groupings[0]
            dist_df = final_df.groupby(cat_col)[target_metric].sum().reset_index()
            dist_df = dist_df.sort_values(by=target_metric, ascending=False).head(10) # Top 10 to keep it clean
            
            fig2 = px.bar(
                dist_df, x=cat_col, y=target_metric, 
                title=f"Топ 10 {cat_col} по {target_metric}", 
                color=cat_col
            )
            fig2.update_layout(template='plotly_white', margin=dict(l=20, r=20, t=50, b=20), showlegend=False, paper_bgcolor='rgba(0,0,0,0)', plot_bgcolor='rgba(0,0,0,0)')
            
            # If Chart 1 already loaded the JS, set include_plotlyjs=False
            include_js = False if charts else 'cdn'
            charts.append(fig2.to_html(full_html=False, include_plotlyjs=include_js))

    # Standard flow
    report_data = final_df.to_dict(orient='records') if not final_df.empty else []
    
    context = {
        'filter_options': filter_options,
        'sum_columns': SUM_COLUMNS,
        'grouping_columns': GROUPING_COLUMNS,
        'selected_metrics': selected_metrics,
        'selected_groupings': selected_groupings,
        'start_date': start_date,
        'end_date': end_date,
        'selected_filial': selected_filial,
        'selected_dept': selected_dept,
        'headers': selected_groupings + selected_metrics,
        'report_data': report_data,
        'charts': charts # Pass generated HTML charts to template
    }
    return render(request, 'dynamic_report/flexible_report.html', context)

{% extends 'base.html' %}
{% load humanize %}

{% block content %}
<!-- PDF Generation Libraries -->
<script src="https://cdnjs.cloudflare.com/ajax/libs/html2canvas/1.4.1/html2canvas.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/jspdf/2.5.1/jspdf.umd.min.js"></script>

<style>
    body { background-color: #f4f7f6; }
    .filter-panel { background: white; padding: 25px; border-radius: 15px; box-shadow: 0 4px 15px rgba(0,0,0,0.03); }
    .dashboard-area { background: white; padding: 30px; border-radius: 15px; box-shadow: 0 4px 15px rgba(0,0,0,0.03); }
    .chart-container { border: 1px solid #edf2f7; border-radius: 10px; padding: 15px; margin-bottom: 30px; }
    .table-responsive { max-height: 500px; overflow-y: auto; }
    
    /* Custom Scrollbar for multiselects */
    select[multiple] { height: 180px; }
</style>

<div class="container-fluid py-4 px-4">
    <div class="row">
        
        <!-- ========================================== -->
        <!-- FILTER SIDEBAR                             -->
        <!-- ========================================== -->
        <div class="col-lg-3 mb-4">
            <div class="filter-panel position-sticky" style="top: 20px;">
                <h5 class="fw-bold mb-4"><i class="fas fa-filter text-primary me-2"></i> Параметры отчета</h5>
                
                <form id="reportForm" method="GET" action="">
                    
                    <!-- Dates -->
                    <div class="mb-3">
                        <label class="form-label fw-bold small">Начало периода</label>
                        <input type="date" name="start_date" class="form-control form-control-sm" value="{{ start_date|default:filter_options.min_date }}">
                    </div>
                    <div class="mb-3">
                        <label class="form-label fw-bold small">Конец периода</label>
                        <input type="date" name="end_date" class="form-control form-control-sm" value="{{ end_date|default:filter_options.max_date }}">
                    </div>
                    
                    <!-- Basic Filters -->
                    <div class="mb-3">
                        <label class="form-label fw-bold small">Филиал</label>
                        <select name="filial" class="form-select form-select-sm">
                            <option value="All">Все филиалы</option>
                            {% for f in filter_options.filials %}
                            <option value="{{ f }}" {% if selected_filial == f %}selected{% endif %}>{{ f }}</option>
                            {% endfor %}
                        </select>
                    </div>
                    
                    <div class="mb-4">
                        <label class="form-label fw-bold small">Департамент</label>
                        <select name="department" class="form-select form-select-sm">
                            <option value="All">Все департаменты</option>
                            {% for d in filter_options.departments %}
                            <option value="{{ d }}" {% if selected_dept == d %}selected{% endif %}>{{ d }}</option>
                            {% endfor %}
                        </select>
                    </div>

                    <hr>

                    <!-- Groupings (Multi) -->
                    <div class="mb-3">
                        <label class="form-label fw-bold small text-primary">Сгруппировать по:</label>
                        <select name="groupings" class="form-select form-select-sm" multiple>
                            {% for col in grouping_columns %}
                            <option value="{{ col }}" {% if col in selected_groupings %}selected{% endif %}>{{ col }}</option>
                            {% endfor %}
                        </select>
                        <small class="text-muted" style="font-size: 0.7rem;">(Удерживайте Ctrl/Cmd для выбора нескольких)</small>
                    </div>

                    <!-- Metrics (Multi) -->
                    <div class="mb-4">
                        <label class="form-label fw-bold small text-success">Метрики (Суммы):</label>
                        <select name="metrics" class="form-select form-select-sm" multiple>
                            {% for col in sum_columns %}
                            <option value="{{ col }}" {% if col in selected_metrics %}selected{% endif %}>{{ col }}</option>
                            {% endfor %}
                        </select>
                    </div>

                    <!-- Generate Button -->
                    <button type="submit" class="btn btn-primary w-100 fw-bold shadow-sm mb-2">
                        <i class="fas fa-sync me-1"></i> Сгенерировать
                    </button>
                    
                    <!-- Excel Download Button -->
                    <button type="submit" name="download" value="excel" class="btn btn-outline-success w-100 fw-bold shadow-sm">
                        <i class="fas fa-file-excel me-1"></i> Скачать Excel
                    </button>
                </form>
            </div>
        </div>

        <!-- ========================================== -->
        <!-- DASHBOARD AREA                             -->
        <!-- ========================================== -->
        <div class="col-lg-9">
            
            <div class="d-flex justify-content-between align-items-center mb-3">
                <h3 class="fw-bold m-0 text-dark">Аналитика Портфеля</h3>
                <button onclick="downloadPDF()" class="btn btn-dark shadow-sm rounded-pill px-4">
                    <i class="fas fa-file-pdf me-2"></i> Сохранить PDF
                </button>
            </div>

            <!-- Area we want to capture for PDF -->
            <div id="pdf-export-area" class="dashboard-area">
                
                <!-- PLOTLY CHARTS -->
                {% if charts %}
                    <div class="row mb-4">
                        {% for chart in charts %}
                        <!-- If there's 1 chart, it takes full width. If 2, they split 50/50 -->
                        <div class="{% if charts|length == 1 %}col-12{% else %}col-lg-6{% endif %}">
                            <div class="chart-container">
                                <!-- The python backend generated the full HTML/JS for this -->
                                {{ chart|safe }}
                            </div>
                        </div>
                        {% endfor %}
                    </div>
                {% endif %}

                <!-- DATA TABLE -->
                <h5 class="fw-bold mb-3 border-bottom pb-2">Табличные данные</h5>
                <div class="table-responsive">
                    <table class="table table-hover table-striped table-bordered align-middle text-center mb-0" style="font-size: 0.85rem;">
                        <thead class="table-dark sticky-top">
                            <tr>
                                {% for header in headers %}
                                    <th scope="col" class="text-uppercase">{{ header }}</th>
                                {% endfor %}
                            </tr>
                        </thead>
                        <tbody>
                            {% for row in report_data %}
                            <tr>
                                {% for header in headers %}
                                    <!-- Use Django lookup dynamically. If it's a number, format it -->
                                    <td class="{% if header in sum_columns %}font-monospace fw-bold{% endif %}">
                                        <!-- Note: Custom dictionary lookup hack using dot notation isn't fully supported natively in Django loops if keys are dynamic. 
                                             Instead of a custom template tag, we rely on the clean data generated in Python -->
                                        {% for key, value in row.items %}
                                            {% if key == header %}
                                                {% if value|slugify|length > 0 %}
                                                    {{ value }}
                                                {% else %}
                                                    0
                                                {% endif %}
                                            {% endif %}
                                        {% endfor %}
                                    </td>
                                {% endfor %}
                            </tr>
                            {% empty %}
                            <tr>
                                <td colspan="{{ headers|length }}" class="text-muted py-4">Нет данных по выбранным фильтрам</td>
                            </tr>
                            {% endfor %}
                        </tbody>
                    </table>
                </div>

            </div> <!-- /#pdf-export-area -->
        </div>

    </div>
</div>

<!-- ========================================== -->
<!-- JAVASCRIPT FOR PDF EXPORT                  -->
<!-- ========================================== -->
<script>
    function downloadPDF() {
        const targetElement = document.getElementById('pdf-export-area');
        
        // Disable scroll limits temporarily so we capture the WHOLE table
        const tableArea = document.querySelector('.table-responsive');
        const originalMaxHeight = tableArea.style.maxHeight;
        tableArea.style.maxHeight = 'none';
        
        html2canvas(targetElement, {
            scale: 2, // High resolution
            backgroundColor: "#ffffff",
        }).then(canvas => {
            const imgData = canvas.toDataURL('image/png');
            
            // Access jsPDF
            const { jsPDF } = window.jspdf;
            
            // Create A4 Landscape PDF
            const pdf = new jsPDF('l', 'mm', 'a4');
            const pdfWidth = pdf.internal.pageSize.getWidth();
            const pdfHeight = pdf.internal.pageSize.getHeight();
            
            const imgProps = pdf.getImageProperties(imgData);
            const imgHeight = (imgProps.height * pdfWidth) / imgProps.width;
            
            // If the content is taller than 1 page, just fit it to width and let height overflow (it will cut off naturally or scale down)
            pdf.addImage(imgData, 'PNG', 0, 0, pdfWidth, imgHeight);
            pdf.save('Analytics_Report.pdf');
            
            // Restore UI
            tableArea.style.maxHeight = originalMaxHeight;
        });
    }
</script>
{% endblock %}