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


from datetime import datetime
import pandas as pd
import json
from io import StringIO


from .models import Project, ProjectItem
from management.models import Good, Vendor, StorageFacility
from stock.models import Stock, Reservation, InvoiceWritingOffMaterial, WritingOffMaterialsItem, Receipt, ReceiptItem
from custom_user.models import User


from .filters import ProjectFilter

from stock.views import get_next_number_invoice_write_off
from action_history.views import action_history
from utils.utils import split_string_into_name_and_units_of_measurement


def convert_date_format(input_date):
    input_date = input_date.strip()
    format_str = '%d.%m.%Y'
    try:
        date_object = datetime.strptime(input_date, format_str)
        formatted_date = date_object.strftime('%Y-%m-%d')
        return formatted_date
    except ValueError as e:
        return None

def process_project_items(project_id, project_item):
    try:
        project_items_delete = ProjectItem.objects.filter(project=project_id, is_write_off = False)
        project_items_delete.delete()      
        itm = project_item.groupby(['Товар', 'Одиниці']).agg({'Кількість': 'sum'}).reset_index()
        for _, row in itm.iterrows():

            good_name = row['Товар']
            unit_of_measurement = row['Одиниці']
            quantity = float(row['Кількість'])

            project_item = ProjectItem.objects.create(
                project=Project.objects.get(id=project_id),
                good=Good.objects.get(name=good_name, unit_of_measurement=unit_of_measurement),
                quantity = quantity,
                is_write_off = False,
            )
    except Exception as error:
        raise error


def project(request):
    if request.method == "GET":
        projects = Project.objects.all().order_by("-created_at")[:5]
        for item in projects:
                user = User.objects.get(email = item.created_by)  or 'System'
                item.user = user
        count_all_projects = Project.objects.all().count()
        return render(request, 'project/project.html', context={'projects': projects, "count_all_projects":count_all_projects})
    elif request.method == "POST":
        data = json.loads(request.body)
        project_name = data['data'].get("project_name")
        project = Project.objects.filter(name = project_name).values('id','name', 'customer', 'date_start', 'created_by')
        if project:
            project_item = project.first()
            user = User.objects.get(email = project_item.get('created_by'))
            project_item = {
                'project_id': project_item.get('id'),
                'name': project_item.get('name'),
                'customer': project_item.get('customer'),
                'date_start': project_item.get('date_start'),
                'created_by': project_item.get('created_by'),
                'user_first_name': user.first_name,
                'user_last_name': user.last_name,
            }

        

        return JsonResponse({
            'message': 'Success',
            'project_item':project_item
            }, safe=False)

def add_new_project(request):
    try:
        if request.method == "GET":
            return render(request, 'project/new_project.html')
        elif request.method == "POST":
            data = json.loads(request.body)
            name = data['data'].get("name")
            customer = data['data'].get("customer")
            date_start = data['data'].get("date_start")
            date_end = data['data'].get("date_end")
            description = data['data'].get("description")
            with transaction.atomic():
                project = Project.objects.create(
                    name=name,
                    customer=customer,
                    date_start=date_start,
                    date_end=date_end,
                    description=description,
                    created_by=request.user
                )
                table =  data['data'].get("table")
                project_item = pd.read_html(table)[0]
                process_project_items(project.id, project_item)
                action_history(request.user, f"Додано обьєкт {name}", "OK")
                return JsonResponse({'message': 'Success'})
    except Exception as error:
        action_history(request.user, f"Додано обьєкт {name}", {error})
        return JsonResponse({'message': 'Error', 'error': str(error)})



def project_detail(request, id):
    goods =  Good.objects.all()
    project = Project.objects.get(id=id)
    project_items = ProjectItem.objects.filter(project=project.id, is_write_off = False)
    
    project_price = 0

    write_of_items = None
    total_price_write_off = 0
    for item in project_items:
        stock_entries = Stock.objects.filter(good_id=item.good).order_by('date')
        
        remaining_quantity = item.quantity
        total_price = 0.0

        for stock_entry in stock_entries:
            if remaining_quantity <= 0:
                break
            
            if stock_entry.quantity >= remaining_quantity:
                total_price += remaining_quantity * stock_entry.price
                remaining_quantity = 0
            else:
                remaining_quantity -= stock_entry.quantity
        

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

        item.is_reservation = "False"
        item.is_write_of = "False"


        good = Good.objects.filter(name=item).first().id

        item.availability  = Stock.objects.filter(good_id=good).aggregate(Sum('actual_quantity'))['actual_quantity__sum'] or 0


        nestacha  = item.quantity - item.availability


        if nestacha > 0:
            item.nestacha = nestacha

        reserv = Reservation.objects.filter(good=good, quantity = item.quantity, project = id)

        if reserv:
            item.is_reservation = "True"

        
    invoice_write_off = InvoiceWritingOffMaterial.objects.filter(project = id).first()
    if invoice_write_off:
        write_of_items = WritingOffMaterialsItem.objects.filter(invoice = invoice_write_off)
    total_price_write_off = 0.0
    price_per_unit = WritingOffMaterialsItem.objects.filter(invoice = invoice_write_off)
    if price_per_unit:
        for item in price_per_unit:
            price = item.price * item.quantity
            total_price_write_off += price
    return render(request, 'project/project_detail.html', {'project': project, 
                                                        'project_items': project_items, 
                                                        'goods': goods, 
                                                        'project_price':project_price, 
                                                        'write_of_items':write_of_items, 
                                                        'total_price_write_off':total_price_write_off})


def project_update(request):
    try:
        if request.method == "POST":
            data = json.loads(request.body)
            name = data['data'].get("name")
            customer = data['data'].get("customer")
            date_start = data['data'].get("date_start")
            date_end = data['data'].get("date_end")
            description = data['data'].get("description")


            project_id = data['data'].get("project_id")
            project = get_object_or_404(Project, id=project_id)
            with transaction.atomic():
                # Оновлюємо значення проекту
                project.name = name
                project.customer = customer
                project.date_start = date_start
                project.date_end = date_end
                project.description = description
                project.save()

                table =  data['data'].get("table")
                project_item = pd.read_html(table)[0]
                process_project_items(project.id, project_item)
                action_history(request.user, f"Оновлено обьєкт {project.name}", "OK")
                return JsonResponse({'message': 'Success'})

    except Exception as error:
        action_history(request.user, f"Оновлено обьєкт {project.name}", {error})
        return JsonResponse({'message': 'Error', 'error': str(error)})

def project_delete(request):
    try:
        data = json.loads(request.body)
        project_id = data.get("project_id")
        with transaction.atomic():
            is_write_off = InvoiceWritingOffMaterial.objects.filter(project = project_id).first()
            if is_write_off:
                return JsonResponse({'message': 'Error', 'data': str('Ви не можете видалити обьєкт, на нього вже є списання')})
            reservation_items = Reservation.objects.filter(project = project_id)
            if reservation_items:
                for item in reservation_items:
                    item.delete()
            project = get_object_or_404(Project, id=project_id)
            project.delete()
            action_history(request.user, f"Видалення обьєкт {project.name}", "OK")
            return JsonResponse({'message': 'Success'})
    except Exception as error:
        action_history(request.user, f"Видалення обьєкт {project.name}", {error})
        return JsonResponse({'message': 'Error', 'data': str(error)})

def add_tmc_to_project(request):
    data = {}
    try:
        if request.method == "POST": 
            table = request.POST.get("table")
            project_id = request.POST.get("project_id")
            project_item = pd.read_html(table)[0]
            process_project_items(project_id, project_item)
            data["message"] = 'Success'
            return JsonResponse(data)
    except Exception as error:
        data["error"] = 'Помилка додавання ТМЦ'
        return JsonResponse(data)
    
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 get_price_for_good(id, quantity):
    stock_entries = Stock.objects.filter(good_id=id).order_by('date')
    remaining_quantity = quantity
    total_price = 0.0
    for stock_entry in stock_entries:
            if remaining_quantity <= 0:
                break
            
            if stock_entry.quantity >= remaining_quantity:
                # Якщо наявна достатня кількість
                total_price += remaining_quantity * stock_entry.price
                remaining_quantity = 0
            else:
                # Якщо наявна кількість менша, ніж потрібно
                total_price += stock_entry.quantity * stock_entry.price
                remaining_quantity -= stock_entry.quantity  
    return total_price

def export_project_items_to_excel(reguest, id):
    from io import BytesIO
    from django.http import HttpResponse

    project = get_object_or_404(Project, id=id)
    project_items = ProjectItem.objects.filter(project=project.id)
    data = {
        "Товар": [item.good.name for item in project_items],
        "Кількість": [item.quantity for item in project_items],
        "Одиниці": [item.good.unit_of_measurement for item in project_items],
        "Вартість": [get_price_for_good(item.good.id, item.quantity) for item in project_items],
    }

    # total_cost = sum(data["Вартість"])
    # data["Вартість проекту"] = [total_cost] * len(project_items)

    df = pd.DataFrame(data)
    # df.loc['Загалом']= df.sum({index (0), columns (1)})

    with BytesIO() as b:
        with pd.ExcelWriter(b) as writer:
            writer.book.formats[0].set_text_wrap()
            df.to_excel(writer, sheet_name="Data", index=False)
        filename = f"project.xlsx"
        res = HttpResponse(
            b.getvalue(),
            content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
        )
        res['Content-Disposition'] = f'attachment; filename={filename}'
        return res


def add_additional_purchases(request, id):
    if request.method == 'GET':
        project = Project.objects.get(id = id)
        goods = Good.objects.all().values("name", "unit_of_measurement")
        vendors = Vendor.objects.all().values("name")
        return render(request, "project/additional_purchases.html", context={"project":project, "goods":goods, "vendors":vendors})

def save_additional_purchases(request):
    try:
        if request.method == 'POST':
            data = json.loads(request.body)
            table =  data.get("table")
            project_id =  data.get("project_id")
            vendor = data.get("vendor")
            invoice = data.get("invoice")
            html_buffer = StringIO(table)
            items = pd.read_html(html_buffer)[0]
            with transaction.atomic():
                storage_facility = StorageFacility.objects.get(name = 'Склад_1')
                project = Project.objects.get(id=project_id)
                # Створюємо закупку 
                receipt = Receipt.objects.create(
                                    vendor = vendor, 
                                    created_by = request.user, 
                                    invoice=invoice, 
                                    storage_facility = storage_facility,
                                )
                for _, row in items.iterrows():
                    good_name = row['Товар']
                    unit_of_measurement = row['Одиниці']
                    quantity = float(row['Кількість'])
                    good_price = float(row['Вартість'])
                    utilized = float(row['Використанно'])

                    price = good_price / quantity
                    good = Good.objects.get(name=good_name, unit_of_measurement = unit_of_measurement)
                    receipt_item = ReceiptItem.objects.create(
                        receipt=Receipt.objects.get(id = receipt.id),
                        good = good,
                        quantity=quantity,
                        price=float(price),
                        total=good_price,
                        created_by = request.user
                    )
                    #  Додаємо на склад 
                    identifier_write_off = Stock.objects.create(
                        storage_facility_id=storage_facility,
                        good_id=good,
                        quantity=quantity,
                        actual_quantity=quantity,
                        price=price,
                        receipt = receipt
                    )
                    #  Додаємо до списаних в обьєкт 
                    is_invoice_present = InvoiceWritingOffMaterial.objects.filter(project=project_id).exists()
                    if is_invoice_present:
                        write_off_invoice = InvoiceWritingOffMaterial.objects.get(project=project)
                        if quantity > utilized:
                            price = price * utilized
                            item_for_write_off = WritingOffMaterialsItem.objects.create(
                                quantity = utilized,
                                good = good,
                                invoice = write_off_invoice,
                                price = price,
                                identifier_write_off = identifier_write_off.id
                            )
                            identifier_write_off.actual_quantity =  F('actual_quantity') - utilized
                            identifier_write_off.save()
                            write_off_invoice.total_sum = F('total_sum') + price
                            write_off_invoice.save()
                        else:
                            price = price * utilized
                            item_for_write_off = WritingOffMaterialsItem.objects.create(
                                quantity = quantity,
                                good = good,
                                invoice = write_off_invoice,
                                price = price,
                                identifier_write_off = identifier_write_off.id
                            )
                            identifier_write_off.actual_quantity =  F('actual_quantity') - quantity
                            identifier_write_off.save()
                            write_off_invoice.total_sum = F('total_sum') + price
                            write_off_invoice.save()

                    else:
                        invoice = InvoiceWritingOffMaterial.objects.create(
                            invoice = get_next_number_invoice_write_off(),
                            reason = project.name,
                            project = project,
                            total_sum = 0,
                            created_by = request.user.email
                        )
                        if quantity > utilized:
                            price = price * utilized
                            item_for_write_off = WritingOffMaterialsItem.objects.create(
                                quantity = utilized,
                                good = good,
                                invoice = invoice,
                                price = price,
                                identifier_write_off = identifier_write_off.id
                            )
                            identifier_write_off.actual_quantity =  F('actual_quantity') - utilized
                            identifier_write_off.save()
                            invoice.total_sum = F('total_sum') + price
                            invoice.save()
                        else:
                            price = price * utilized
                            item_for_write_off = WritingOffMaterialsItem.objects.create(
                                quantity = quantity,
                                good = good,
                                invoice = invoice,
                                price = price,
                                identifier_write_off = identifier_write_off.id
                            )
                            identifier_write_off.actual_quantity =  F('actual_quantity') - quantity
                            identifier_write_off.save()
                            invoice.total_sum = F('total_sum') + price
                            invoice.save()

                # ############################################
            action_history(request.user, f"Дозакупка на обьєкт {project.name} чек {invoice}", "OK")
        return JsonResponse({'message': 'Success'})
    except Exception as error:
        action_history(request.user, f"Дозакупка на обьєкт {project.name} чек {invoice}", {error})
        return JsonResponse({'status': 'error', 'message': str(error)}, status=500)

def return_from_project(request, id):
    try:

        if request.method == 'GET':
            project = Project.objects.get(id = id)
            is_write_off_invoice = InvoiceWritingOffMaterial.objects.get(project = project )
            goods = WritingOffMaterialsItem.objects.filter(invoice=is_write_off_invoice).values('good__name')
            return render(request, "project/return_from_project.html", context={"project":project, "goods":goods})
    except Exception as error:
        raise error

def return_to_stock_from_project(request):
    try:
        if request.method == 'POST':
            data = json.loads(request.body)
            identifier_write_off = data.get("identifier_write_off")
            quantity = data.get("quantity")
            project_id =  data.get("project_id")
            project = Project.objects.get(id = project_id )
            with transaction.atomic():
                
                remove_from_write_off = WritingOffMaterialsItem.objects.get(identifier_write_off = identifier_write_off)
                if remove_from_write_off.quantity < float(quantity):
                    return JsonResponse({'message': 'Error', 'data': str('Кількість повернення не може бути більше ніж наявна кількість')})
                remove_from_write_off.quantity = F('quantity') - quantity
                remove_from_write_off.save()
 

                add_to_stock = Stock.objects.get(id = identifier_write_off)
                add_to_stock.actual_quantity = F('actual_quantity') + quantity
                add_to_stock.save()

                items_for_delete = WritingOffMaterialsItem.objects.filter(quantity = 0)
                for item in items_for_delete:
                    item.delete()
                return JsonResponse({'message': 'Success'})


    except Exception as error:
        raise error