# -*- coding: utf-8 -*-
from django.shortcuts import render, get_object_or_404
from django.http import JsonResponse
from django.db import transaction
from django.db.models import Sum


import pandas as pd
from datetime import datetime
import json


from .models import Procurement, ProcurementItem
from management.models import Good, Vendor
from project.models import Project, ProjectItem
from stock.models import ReceiptItem, Receipt, Reservation, Stock


from .filters import ProcurementFilter
# Create your views here.

def convert_date_format(input_date):
    date_object = datetime.strptime(input_date, '%b %d, %Y')
    formatted_date = date_object.strftime('%Y-%m-%d')
    return formatted_date

def get_next_number():
    try:
        next_number = Procurement.objects.last()
        if not next_number:
            next_number = 1
        else:
            next_number = int(next_number.number) + 1
        return next_number
    
    except Exception as error:
        raise error

def process_procurement_items(procurement_id, procurement_item):
    try:
        procurement_items_for_delete = ProcurementItem.objects.filter(procurement_id = procurement_id)
        for item in procurement_items_for_delete:
            item.delete()
        for _, row in procurement_item.iterrows():
            good_name = row['Товар']
            quantity = row['Кількість']
            procurement_item = ProcurementItem.objects.create(
                                            procurement = Procurement.objects.get(id = procurement_id),
                                            good = Good.objects.get(name=good_name),
                                            quantity = quantity,
            )
    except Exception as error:
        raise error

def procurement(request):

    if request.method == 'POST':
            filter_value = request.POST.get('vendor__name', '')  # Замініть 'vendor__name' на ваше поле фільтрації
            filter_value_lower = filter_value.lower()
            procurements_all = Procurement.objects.all()
            procurements_filter = ProcurementFilter(request.POST, queryset = procurements_all)
            # procurements_filter.cleaned_data['vendor__name'] = filter_value_lower
            procurements = procurements_filter.qs
            return render (request, 'procurement/procurement.html', {"procurements":procurements})
    if request.method == 'GET':
        procurements = Procurement.objects.all()[:10]
        return render(request, 'procurement/procurement.html', {"procurements":procurements})

def procurement_create(request):
    if request.method == 'GET':
        goods = Good.objects.all()
        vendors = Vendor.objects.all()
        projects = Project.objects.all().order_by('created_at')
        next_number = get_next_number()
        return render(request, 'procurement/procurement_create.html', {"vendors": vendors, "goods":goods, "projects":projects, "next_number":next_number})
    if request.method == 'POST':
        project_name = request.POST.get("project_name")
        number = request.POST.get("number")
        vendor = request.POST.get("vendor")
        date = request.POST.get("date")
        comment = request.POST.get("comment")
        project = Project.objects.get(name = "tst_1 ")
        procurement = Procurement.objects.create(
                                            project = Project.objects.get(name = "tst_1 "),
                                            number = number, 
                                            vendor = Vendor.objects.get(name = vendor),
                                            created_by = request.user,
                                            created_at = date,
                                            comment = comment
                                        )
        table = request.POST.get("table")
        procurement_item = pd.read_html(table)[0]
        process_procurement_items(procurement.id, procurement_item)
        return JsonResponse({'message': 'Success'})

def procurement_detail(request,id):
    try:
        procurement = get_object_or_404(Procurement, id=id)
        procurement_item = ProcurementItem.objects.filter(procurement = procurement.id)
        goods = Good.objects.all()
        return render(request, 'procurement/procurement_detail.html', {'procurement': procurement, 'procurement_item':procurement_item, "goods":goods})
    except Exception as error:
        return JsonResponse({'message': 'error','data':str(error)})

def procurement_delete(request):
    try:
        data = json.loads(request.body)
        with transaction.atomic():
            procurement = Procurement.objects.get(id = data.get("data"))
            procurement_items = ProcurementItem.objects.filter(procurement_id = procurement.id)
            for item in procurement_items:
                item.delete()
            procurement.delete()
            return JsonResponse({'message': 'Success'})
    except Exception as error:
        return JsonResponse({'message': 'error','data':str(error)})

def add_tmc_to_procurement(request):
    data = {}
    try:
        if request.method == "POST":
            table = request.POST.get("table")
            procurement_id = request.POST.get("procurement_id")
            procurement_item = pd.read_html(table)[0]
            process_procurement_items(procurement_id, procurement_item)
            data["message"] = 'Success'
            return JsonResponse(data)
    except Exception as error:
        data["error"] = 'Помилка додавання ТМЦ'
        return JsonResponse(data)

    # with pd.ExcelWriter(f'{dir}/{name}.xlsx', engine='xlsxwriter') as writer:
    #     workbook = writer.book
    #     worksheet = workbook.add_worksheet('Result')
    #     writer.sheets['Result'] = worksheet

    #     COLUMN = 0
    #     row = 0

    #     for df in dataframes:
    #         worksheet.write_string(row, COLUMN, df.name)
    #         row += 1
    #         df.to_excel(writer, sheet_name='Result',
    #                     startrow=row, startcol=COLUMN)
    #         row += df.shape[0] + 2

def write_dataframes_to_excel_sheet(dataframes, dir, name):
    from io import BytesIO
    from django.http import HttpResponse

    with BytesIO() as b:
        # Знову створюємо ExcelWriter, але цього разу для запису в BytesIO
        with pd.ExcelWriter(b, engine='xlsxwriter') as writer:
            workbook = writer.book
            worksheet = workbook.add_worksheet('Result')
            writer.sheets['Result'] = worksheet

            COLUMN = 0
            row = 0

            for df in dataframes:
                worksheet.write_string(row, COLUMN, df.name)
                row += 1
                df.to_excel(writer, sheet_name='Result', startrow=row, startcol=COLUMN)
                row += df.shape[0] + 2
                
            res = HttpResponse(
                b.getvalue(),
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            res['Content-Disposition'] = f'attachment; filename={name}.xlsx'
            return res

    
def checkActualQuantityAndShortage(request):
    try:
        if request.method == "POST":
            data = json.loads(request.body)
            good_name = data.get("good_name")
            unit_of_measurement = data.get("unit_of_measurement")
            good_quantity = data.get("good_quantity").replace(',','.')


            good = Good.objects.get(name=good_name,unit_of_measurement=unit_of_measurement)

            reservation = Reservation.objects.filter(good=good).aggregate(Sum('quantity'))['quantity__sum']  or 0

            actual_quantity = Stock.objects.filter(good_id=good).aggregate(Sum('quantity'))['quantity__sum'] or 0

            actual_quantity = actual_quantity - reservation

            shortage_quantity = float(actual_quantity) - float(good_quantity)

            result = {'status': 'success', 'actual_quantity': actual_quantity, 'shortage_quantity':shortage_quantity}

            return JsonResponse(result)
    except json.JSONDecodeError as e:
        return JsonResponse({'status': 'error', 'message': 'Invalid JSON format'}, status=400)
    except Exception as e:
        return JsonResponse({'status': 'error', 'message': str(e)}, status=500)

def procurement_goods_from_project_to_excel(request, id):
    try:
        project = Project.objects.get(id = id)
        goods_in_project = ProjectItem.objects.filter(project = id)
        result_df = pd.DataFrame(columns=["Назва", "Кількість", "Одиниці", "Постачальник"])
        for item in goods_in_project:
            good = Good.objects.get(name=item)
            reservation = Reservation.objects.filter(good=good).aggregate(Sum('quantity'))['quantity__sum']  or 0
            actual_quantity = Stock.objects.filter(good_id=good).aggregate(Sum('quantity'))['quantity__sum'] or 0
            actual_quantity = actual_quantity - reservation
            shortage_quantity = float(actual_quantity) - float(item.quantity)
            last_vendor_name = 'Не знайдено'
            last_vendor = ReceiptItem.objects.filter(good = good).order_by('created_at').last()
            if last_vendor:
                last_vendor_name = last_vendor.receipt.vendor
            if shortage_quantity < 0:
                item_data = {
                    "Назва": item,
                    "Кількість": abs(shortage_quantity),
                    "Одиниці": good.unit_of_measurement,
                    "Постачальник": last_vendor_name,
                }
                result_df = pd.concat([result_df, pd.DataFrame([item_data])], ignore_index=True)
                
        dataframes = []
        vendors = result_df['Постачальник'].unique()
        for vendor in vendors:            
            title = vendor
            vendor_df = result_df[result_df['Постачальник'] == vendor]
            vendor_df.name = title
            dataframes.append(vendor_df)
        
        from io import BytesIO
        from django.http import HttpResponse

        with BytesIO() as b:
            with pd.ExcelWriter(b, engine='xlsxwriter') as writer:
                workbook = writer.book
                worksheet = workbook.add_worksheet(project.name)
                writer.sheets[project.name] = worksheet

                COLUMN = 0
                row = 0

                for df in dataframes:
                    worksheet.write_string(row, COLUMN, df.name)
                    row += 1
                    df.to_excel(writer, sheet_name=project.name, startrow=row, startcol=COLUMN)
                    row += df.shape[0] + 2
                
            res = HttpResponse(
                b.getvalue(),
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            res['Content-Disposition'] = f'attachment; filename={project.name}.xlsx'
            return res

    except Exception as error:
        raise error