passport_change

import os
import io
import pandas as pd
import numpy as np
import oracledb
from django.shortcuts import render, redirect
from django.contrib import messages
from django.contrib.auth.decorators import login_required
from django.http import HttpResponse

from . import queries
from . import forms
from functions import common_functions


def _process_passport_data(df):
    """
    Helper function to process the passport dataframe.
    Keeps the code DRY and ensures consistency between cached and live data.
    """
    # 1. Safely handle names (preserving Russian characters)
    df['name'] = df['name'].fillna('Отсутствует').astype(str)
    
    # 2. Safely parse dates and format them
    # If a date fails to parse, it becomes NaT. We fill NaT with a fallback string 
    # so it doesn't get skipped in the string join later.
    df['date_change'] = pd.to_datetime(df['date_from'], errors='coerce')
    df['date_change'] = df['date_change'].dt.strftime('%d-%m-%Y').fillna('Дата неизвестна')

    # 3. Calculate changes
    change_check = df.groupby('dealid')['name'].nunique() > 1
    df['change'] = df['dealid'].map(change_check).astype(int)
    df['counter'] = 1

    # 4. Group by dealid
    df_grouped = df.groupby('dealid').agg({
        'date_change': lambda x: ', '.join(x),  # No dropna() needed since we filled missing values
        'name': lambda x: ', '.join(x),
        'counter': 'count'
    }).reset_index() 

    df_grouped["number_of_changes"] = df_grouped['counter'] - 1
    return df_grouped.to_dict(orient='records')


@login_required
def home(request):
    user_id = request.user.id

    if request.method == 'POST':
        start_date_form = forms.DateInputForm(request.POST, prefix='start')
        end_date_form = forms.DateInputForm(request.POST, prefix='end')

        if start_date_form.is_valid() and end_date_form.is_valid():
            start_date = start_date_form.cleaned_data['selected_date']
            end_date = end_date_form.cleaned_data['selected_date']

            raw_dir = "datasets/b2_changes/passport"
            os.makedirs(raw_dir, exist_ok=True)
            
            # Switched from .csv to .parquet
            filepath_passport = f"{raw_dir}/passport_from_{start_date}_to_{end_date}.parquet"
            request.session[f'b2_changes_passport_filepath_{user_id}'] = filepath_passport

            # ---------------------------------------------------------
            # CACHE CHECK
            # ---------------------------------------------------------
            if os.path.exists(filepath_passport):
                # Parquet retains datatypes and handles UTF-8 automatically
                df = pd.read_parquet(filepath_passport)
                
                records = _process_passport_data(df)
                request.session[f'b2_changes_processed_passport_data_{user_id}'] = records
                
                messages.success(request, "Данные за этот период уже были обработаны. (Loaded from Cache)")
                return redirect("b2_changes-result")

            # ---------------------------------------------------------
            # DATABASE QUERY
            # ---------------------------------------------------------
            try:
                connection = common_functions.get_oracle_connection()
                with connection.cursor() as cursor:
                    cursor.execute(queries.passport_change_query, {'start_date': start_date, 'end_date': end_date})
                    columns = [col[0] for col in cursor.description]
                    rows = cursor.fetchall()

                if not rows:
                    messages.error(request, "Нет данных для указанного периода.")
                    return redirect('b2_changes-home')

                df = pd.DataFrame(rows, columns=columns)
                df.columns = df.columns.astype(str).str.lower()

                # Save raw query result to Parquet immediately
                df.to_parquet(filepath_passport, index=False) 

                # Process data
                records = _process_passport_data(df)
                request.session[f'b2_changes_processed_passport_data_{user_id}'] = records

                return redirect('b2_changes-result')

            except oracledb.DatabaseError as e:
                messages.error(request, f"Ошибка базы данных: {str(e)}")
                return redirect('b2_changes-home')
            finally:
                if 'connection' in locals():
                    connection.close()

    else:
        start_date_form = forms.DateInputForm(prefix='start')
        end_date_form = forms.DateInputForm(prefix='end')

    context = {'start_date_form': start_date_form, 'end_date_form': end_date_form}
    return render(request, 'b2_changes/home.html', context)


@login_required
def result(request):
    user_id = request.user.id
    records = request.session.get(f'b2_changes_processed_passport_data_{user_id}')

    if not records:
        messages.warning(request, "Нет данных для отображения. Пожалуйста, выберите даты.")
        return redirect('b2_changes-home')

    return render(request, 'b2_changes/result.html', {'records': records})


@login_required
def download_excel(request):
    user_id = request.user.id
    filepath_passport = request.session.get(f'b2_changes_passport_filepath_{user_id}')
    passport_data = request.session.get(f'b2_changes_processed_passport_data_{user_id}')

    if not filepath_passport or not os.path.exists(filepath_passport):
        messages.error(request, 'Файл не найден. Пожалуйста, сформируйте отчет заново.')
        return redirect('b2_changes-home')

    if not passport_data:
        messages.error(request, 'Нет обработанных данных в сессии.')
        return redirect('b2_changes-home')

    # Read from Parquet (No need for encoding or sep parameters)
    df_passport = pd.read_parquet(filepath_passport)
    df_passport_pro = pd.DataFrame(passport_data)

    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine='xlsxwriter') as writer:
        df_passport.to_excel(writer, sheet_name='База', index=False)
        df_passport_pro.to_excel(writer, sheet_name='Свод', index=False)

        # Optional: Set column widths for readability
        for sheet in writer.sheets.values():
            sheet.set_column('A:Z', 18)

    buffer.seek(0)
    response = HttpResponse(
        buffer.read(), 
        content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
    )
    response['Content-Disposition'] = 'attachment; filename="passport_changes.xlsx"'
    return response