from django.shortcuts import render
import json
from django.conf import settings
from django.shortcuts import render
from inventory.models import *
from expenses.models import *
from store.models import *
from company.models import *
from masters.models import *
from supplier.models import *
from django.db import IntegrityError, transaction
import hashlib
import os
from django.conf import settings
from datetime import datetime
from django.utils import timezone
from django.db.models import Max, F, Q,Sum
import base64
from django.shortcuts import render
from io import BytesIO
from django.conf import settings
from django.template.loader import get_template
from django.http import HttpResponse,HttpResponseRedirect,JsonResponse,FileResponse
from django.shortcuts import get_object_or_404
from django.templatetags.static import static
from xhtml2pdf import pisa
from num2words import num2words
from django.contrib.staticfiles import finders  
from common.utils import *
from django.db import connection
import openpyxl
from openpyxl.styles import Font
from django.http import HttpResponse
from django.core.files.storage import default_storage

#```````````````````````````````````````````````````````````**PURCHASE ORDER**````````````````````````````````````````````````````````````````````````````````

def purchase_order(request):
    if 'user_id' in request.session:
        user_type = request.session.get('user_type')
        branch_id = request.session.get('branch_id')
        is_ho = request.session.get('is_ho')
        if user_type == 'stores':
            branch = selectList(branch_table, order_by='name').exclude(id=branch_id)
            if is_ho == 1:
                supplier = selectList(supplier_table, order_by='name' )
            else:
                supplier = selectList(supplier_table, Q(is_global=1), order_by='name' )

            return render(request, 'purchase_order/details.html',{'supplier':supplier,'branch':branch})
    
        elif user_type == 'wholesale':
            branch = selectList(branch_table, {'is_ho':1},order_by='name').exclude(id=branch_id)          

            return render(request, 'internal_wholesale/purchase/order/details.html',{'branch':branch})
        
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")
    



def ajax_order_view(request):
    role_id = request.session.get('role_id')
    branch_id = request.session.get('branch_id')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name)
             
    has_access, error_message = check_user_access(role_id, 'purchase_order', "read")

    if not has_access:
        return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view purchase order details'})
    
    supplier_id = request.POST.get('supplier')
    supply_id = request.POST.get('branch')
    from_date = request.POST.get('from_date')
    to_date = request.POST.get('to_date')
    keyword = request.POST.get('keyword_search', '').strip()

    query = Q(status=1, branch_id=branch_id, current_fy = financial_year)

    if supplier_id:
        query &= Q(supplier_id=supplier_id)

    if supply_id:
        query &= Q(supply_branch_id=supply_id)


    if from_date and to_date:
        query &= Q(po_date__range=[from_date, to_date])

    if keyword:
        query &= Q(po_no__icontains=keyword)
       
    
    
    data = list(selectList(purchase_order_table, query).values())

    formatted = []
    for index, item in enumerate(data):       
       
        formatted.append({
            'id': index + 1,
            'action': '<button type="button" onclick="edit_data(\'{}\')" class="btn btn-outline-success btn-xs p-1"><i class="fas fa-edit"></i></button> \
                      <button type="button" onclick="delete_data(\'{}\')" class="btn btn-outline-danger btn-xs p-1"> <i class="fas fa-trash-alt"></i></button>'.format(item['id'], item['id']), 
            'po_no': item['po_no'] if item['po_no'] else '-', 
            'po_date': format_date_month_year(item['po_date']),
            'supplier': getItemNameById(supplier_table, item['supplier_id']) if item['supplier_id'] else '-', 
            'quantity': item['total_quantity'] if item['total_quantity'] else '-', 
            'amount': format_amount(item['total_amount']),            
            'remarks': item['remarks'] if item['remarks'] else '-', 
            'status': '<span class="badge text-bg-success">Active</span>' if item['is_active'] else '<span class="badge text-bg-danger">Inactive</span>'
        })


    return JsonResponse({'data': formatted})

from wholesale.models import *

def purchase_order_add(request):
    if 'user_id' in request.session:
        user_type = request.session.get('user_type')
        branch_id = request.session.get('branch_id')
        is_ho = request.session.get('is_ho')
        fyf_name = request.session.get('fyf')
        financial_year = calculate_financial_year(fyf_name)
        if user_type == 'stores':
            company = select_row(company_table, {'id': 1})  
            employee = employee_list(request)
            subcategory = selectList(sub_category_table, order_by='name' )
            branch = selectList(branch_table, order_by='name').exclude(id=branch_id)
            item = selectList(item_table )
            if is_ho == 1:
                supplier = selectList(supplier_table, order_by='name' )
            else:
                supplier = selectList(supplier_table, Q(is_global=1), order_by='name' )
            employee = employee_list(request)
            brand = selectList(brand_table, order_by='name' )
            generated_po_no = generate_serial_number(model=purchase_order_table,number_field='po_no',financial_year_field='current_fy',financial_year=financial_year,branch_id=branch_id,prefix="PO_")
            return render(request, 'purchase_order/add.html', {'employee':employee,'company': company,'supplier':supplier,'branch':branch,'item':item,'employee':employee,'generated_po_no':generated_po_no,'brand':brand,'subcategory':subcategory})
        
        elif user_type == 'wholesale':
            branch = selectList(branch_table, {'is_ho':1},order_by='name').exclude(id=branch_id)
            item = selectList(item_table )
            supplier = selectList(supplier_table, Q(is_global=1), order_by='name' )
            brand = selectList(brand_table, order_by='name' )
            generated_po_no = generate_serial_number(model=wholesale_po_table,number_field='po_no',financial_year_field='current_fy',financial_year=financial_year,branch_id=branch_id,prefix="PO_")
            return render(request, 'internal_wholesale/purchase/order/add.html', {'supplier':supplier,'branch':branch,'item':item,'generated_po_no':generated_po_no,'brand':brand})
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")
 

def add_purchase_order(request):
    if request.method == 'POST':
        try:
            company_id = request.session.get('company_id')
            branch_id = request.session.get('branch_id')
            user_id = request.session.get('user_id')
            fyf_name = request.session.get('fyf')
            financial_year = calculate_financial_year(fyf_name)
            role_id = request.session.get('role_id')
            date_times = timezone.localtime(timezone.now())
            
            # Permission check
            has_access = check_user_access(role_id, 'purchase_order', "create")
            if not has_access:
                return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to add purchase order details'})          

           
            po_date = request.POST.get('po_date')
            supplier_id = request.POST.get('supplier_id') or 0
            remarks = request.POST.get('description')
            employee_id = request.POST.get('employee_id') or user_id
            total_qty = request.POST.get('total_quantity')
            sub_total = request.POST.get('sub_total')
            grand_total = request.POST.get('grand_total')
            round_off = request.POST.get('round_off') or 0
            items_json = request.POST.get('items')
            items = json.loads(items_json)
            purchase_status = 'accepted'


            negative_check_fields = ['total_quantity', 'sub_total', 'grand_total']
            has_negative, error_message = has_negative_values(request.POST, negative_check_fields)

            if has_negative:
                return JsonResponse({'message': 'infos', 'error_message': error_message})
            
            if not supplier_id:
                return JsonResponse({'message': 'warning', 'error_message': 'Please select either a Supplier or a Supply Store.'})

            if not items:
                return JsonResponse({'message': 'warning', 'error_message': 'Please add at least one item to the order.'})


            generated_po_no = generate_serial_number(
                model=purchase_order_table,
                number_field='po_no',
                financial_year_field='current_fy',
                financial_year=financial_year,
                branch_id=branch_id,
                prefix="PO_"
            )


            with transaction.atomic():
                main_obj = purchase_order_table.objects.create(
                    company_id=company_id,
                    po_date=po_date,
                    time=date_times.strftime('%H:%M:%S'),
                    po_no=generated_po_no,
                    num_series=generate_num_series(purchase_order_table),
                    current_fy=financial_year,
                    branch_id=branch_id,
                    supplier_id=supplier_id,
                    remarks=remarks if remarks else 'Purchase Order requested',
                    total_quantity=total_qty,
                    sub_total= sub_total,
                    total_amount=grand_total,
                    roundoff=round_off,
                    po_status=purchase_status,
                    is_active=1,
                    status=1,
                    created_on=date_times,
                    updated_on=date_times,
                    created_by=user_id,
                    updated_by=user_id,
                    employee_id = employee_id
                )

                # Save the purchase order items
                for item in items:
                    child_purchase_order_table.objects.create(
                        company_id=company_id,
                        current_fy=financial_year,
                        branch_id=branch_id,
                        supplier_id=supplier_id,
                        tm_po_id=main_obj.id,
                        subcategory_id=item['subcategory_id'] or 0,
                        brand_id=item['brand_id'] or 0,
                        model_id=item['model_id'] or 0,
                        variant_id=item['variant_id'] or 0,
                        color_id=item['color_id'] or 0,
                        ean=item['ean_number'] or '',
                        hsn_code=item['hsn_code'] or '',
                        rate=item['rate'],
                        quantity=item['quantity'],                      
                        amount=item['amount'],
                        mrp=item['mrp'],
                        fsp=item['fsp'],
                        wsp=item['wsp'],
                        mop=item['mop'],
                        bop=item['bop'],
                        db_price=item['db_price'],
                        is_active=1,
                        status=1,
                        created_on=date_times,
                        updated_on=date_times,
                        created_by=user_id,
                        updated_by=user_id
                    )


           
                        
            return JsonResponse({'message': 'success'})

        except IntegrityError as e:
            return JsonResponse({'message': 'exception', 'error': 'Database error occurred: ' + str(e)}, status=500)
        except Exception as e:
            return JsonResponse({'message': 'exception', 'error': 'An error occurred: ' + str(e)}, status=500)




def purchase_order_edit(request):
    if 'user_id' in request.session:
        user_type = request.session.get('user_type')
        branch_id = request.session.get('branch_id')
        is_ho = request.session.get('is_ho')
        if user_type == 'stores':
            encoded_id = request.GET.get('id', None)
            decoded_id = decode_base64_id(encoded_id)
            if decoded_id:
                company = select_row(company_table, {'id': 1})  
                purchase_order = select_row(purchase_order_table, {'id':decoded_id})
                if is_ho == 1:
                    supplier = selectList(supplier_table, order_by='name' )
                else:
                    supplier = selectList(supplier_table, Q(is_global=1), order_by='name' )
                brand = selectList(brand_table, order_by='name')
                subcategory = selectList(sub_category_table, order_by='name')
                employee = employee_list(request)
            else:
                return HttpResponse("ID parameter is missing")
        elif user_type == 'wholesale':
            encoded_id = request.GET.get('id', None)
            decoded_id = decode_base64_id(encoded_id)
            if decoded_id:
                company = select_row(company_table, {'id': 1})  
                purchase_order = select_row(wholesale_po_table, {'id':decoded_id})
                if is_ho == 1:
                    supplier = selectList(supplier_table, order_by='name' )
                else:
                    supplier = selectList(supplier_table, Q(is_global=1), order_by='name' )
                brand = selectList(brand_table, order_by='name')
                subcategory = selectList(sub_category_table, order_by='name')
                employee = employee_list(request)
                branch = selectList(branch_table, order_by='name').exclude(id=branch_id)

            else:
                return HttpResponse("ID parameter is missing")
            
            return render(request, 'internal_wholesale/purchase/order/edit.html', {'employee':employee,'company': company,'material_request':purchase_order,'id':decoded_id,'supplier':supplier,'brand':brand,'subcategory':subcategory,'branch':branch})
        else:
            return HttpResponseRedirect("/")
    else:
        return HttpResponseRedirect("/")






def ajax_po_edit(request):  
    role_id = request.session.get('role_id')
    fyf_name = request.session.get('fyf')
    financial_year = calculate_financial_year(fyf_name)
    
    has_access, error_message = check_user_access(role_id, 'purchase_order', "read")
    if not has_access:
        return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to view the purchase details'})

    po_id = request.POST.get('po_id')
    data = list(selectList(child_purchase_order_table,{'tm_po_id':po_id,'current_fy':financial_year}).values())
    
   
    formatted = []
    for index, item in enumerate(data):
        formatted.append({
            'id': index + 1,
            'action': '<button type="button" onclick="edit_data(\'{}\')" class="btn btn-outline-success btn-xs p-1"><i class="fas fa-edit"></i></button> \
                    <button type="button" onclick="delete_data(\'{}\')" class="btn btn-outline-danger btn-xs p-1"> <i class="fas fa-trash-alt"></i></button>'.format(item['id'], item['id']),
            'ean': item['ean'] if item['ean'] else '-',  # ✅ With SKU
            'subcategory': getItemNameById(sub_category_table, item['subcategory_id']) if item['subcategory_id'] else '-',
            'brand': getItemNameById(brand_table, item['brand_id']) if item['brand_id'] else '-',
            'model': getItemNameById(model_table, item['model_id']) if item['model_id'] else '-',
            'variant': getItemNameById(variant_table, item['variant_id']) if item['variant_id'] else '-',
            'color': getItemNameById(color_table, item['color_id']) if item['color_id'] else '-',
            'rate': format_amount(item['rate']) if item['rate'] else '-',
            'qty': item['quantity'] if item['quantity'] else '-',
            'amt': format_amount(item['amount']) if item['amount'] else '-',
        })

    return JsonResponse({'data': formatted})




def edit_purchase_order(request):
    if request.method == "POST":
        data = child_purchase_order_table.objects.filter(id=request.POST.get('id'))    
    return JsonResponse(data.values()[0])

def update_purchase_order(request):
    if request.method == 'POST':
        try:
            role_id = request.session.get('role_id')
            has_access, error_message = check_user_access(role_id, 'purchase_order', "update")
            if not has_access:
                return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to update the purchase details'})

            data = json.loads(request.body)
            user_id = request.session.get('user_id')
            po_id = data.get('tm_id')
            subcategory_id = data.get('subcategory_id')
            brand_id = data.get('brand_id')
            model_id = data.get('model_id')
            variant_id = data.get('variant_id')
            color_id = data.get('color_id')
            ean_number = data.get('ean_number')
            quantity = int(data.get('quantity', 1))
            rate = float(data.get('rate'))
            edit_id = data.get('tx_id', 0)
            date_times = timezone.localtime(timezone.now())
            hsn_code = data.get('hsn_code')
            db_price = float(data.get('db_price'))
            fsp = float(data.get('fsp'))
            wsp = float(data.get('wsp'))
            mop = float(data.get('mop'))
            bop = float(data.get('bop'))
            mrp = float(data.get('mrp'))
            amount = quantity * rate

            negative_check_fields = ['rate', 'quantity', 'amount']
            has_negative, error_message = has_negative_values(request.POST, negative_check_fields)
            if has_negative:
                return JsonResponse({'message': 'infos', 'error_message': error_message})

           
            purchase = select_row(purchase_order_table, {'id': po_id})
            if not purchase:
                return JsonResponse({'success': False, 'message': 'Purchase Order not found.'})

            # if purchase.po_status == 'accepted' or purchase.is_accepted == 1:
            #     return JsonResponse({'info': False, 'message': 'Cannot update. The Purchase Order is already accepted.'})

            inward_exists = selectList(child_purchase_inward_table, {'po_id': po_id}).exists()
            if inward_exists:
                return JsonResponse({'info': False, 'message': 'Cannot update. The Purchase Order has already been inwarded.'})

            # ✅ Proceed with child table logic
            if edit_id and int(edit_id) != 0:
                item_row = child_purchase_order_table.objects.filter(id=edit_id, tm_po_id=po_id).first()
                if item_row:
                    item_row.subcategory_id = subcategory_id
                    item_row.ean = ean_number
                    item_row.brand_id = brand_id
                    item_row.model_id = model_id
                    item_row.variant_id = variant_id
                    item_row.color_id = color_id
                    item_row.quantity = quantity
                    item_row.rate = rate
                    item_row.hsn_code = hsn_code
                    item_row.db_price = db_price
                    item_row.fsp = fsp
                    item_row.wsp = wsp
                    item_row.mop = mop
                    item_row.bop = bop
                    item_row.mrp = mrp
                    item_row.ean = ean_number
                    item_row.amount = amount
                    item_row.updated_on = date_times
                    item_row.updated_by = user_id
                    item_row.save()
                    action = 'edited'
                else:
                    return JsonResponse({'success': False, 'message': 'Item not found for editing.'})
            else:
                existing_item = child_purchase_order_table.objects.filter(
                    tm_po_id=po_id, subcategory_id=subcategory_id, brand_id=brand_id, model_id=model_id, variant_id=variant_id, color_id=color_id, ean=ean_number, rate=rate
                ).first()

                if existing_item:
                    existing_item.quantity += quantity
                    existing_item.amount = existing_item.quantity * existing_item.rate
                    existing_item.updated_on = date_times
                    existing_item.updated_by = user_id
                    existing_item.save()
                    action = 'updated'
                else:
                    child_purchase_order_table.objects.create(
                        company_id=purchase.company_id,
                        current_fy=purchase.current_fy,
                        branch_id=purchase.branch_id,
                        supplier_id=purchase.supplier_id,
                        tm_po_id=po_id,
                        subcategory_id=subcategory_id,
                        brand_id=brand_id,
                        model_id=model_id,
                        variant_id=variant_id,
                        color_id=color_id,
                        ean=ean_number,
                        quantity=quantity,
                        rate=rate,
                        hsn_code=hsn_code,
                        db_price=db_price,
                        fsp=fsp,
                        wsp=wsp,
                        mop=mop,
                        bop=bop,
                        mrp=mrp,
                        amount=amount,
                        remarks='',
                        created_on=date_times,
                        updated_on=date_times,
                        created_by=user_id,
                        updated_by=user_id
                    )
                    action = 'added'

            # ✅ Update parent totals (tm_po)
            totals = child_purchase_order_table.objects.filter(tm_po_id=po_id).aggregate(
                total_qty=Sum('quantity'),
                total_amt=Sum('amount')
            )

            purchase.total_quantity = totals['total_qty'] or 0
            purchase.total_amount = totals['total_amt'] or 0
            purchase.updated_on = date_times
            purchase.updated_by = user_id
            purchase.save()

            return JsonResponse({'success': True, 'action': action})

        except Exception as e:
            return JsonResponse({'success': False, 'message': str(e)})

    return JsonResponse({'success': False, 'message': 'Invalid request'})




def delete_tx_po(request):
    if request.method == 'POST':
        data_id = request.POST.get('id')    
        role_id = request.session.get('role_id')
        has_access, error_message = check_user_access(role_id, 'purchase_order', "delete")
        if not has_access:
            return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to delete purchase details'})
        
   

        try:
            child_item = child_purchase_order_table.objects.filter(id=data_id, status=1).first()
            if not child_item:
                return JsonResponse({'message': 'no such data'})

            po_id = child_item.tm_po_id

            po = purchase_order_table.objects.filter(id=po_id, status=1).first()
            if not po:
                return JsonResponse({'message': 'no such data'})

            if po.po_status.lower() == 'accepted' or po.is_accepted == 1:
                return JsonResponse({'message': 'warning', 'error_message': 'Cannot delete. The Purchase Order has been accepted.'})

            inward_exists = child_purchase_inward_table.objects.filter(po_id=po_id, status=1).exists()
            if inward_exists:
                return JsonResponse({'message': 'warning', 'error_message': 'Cannot delete. The Purchase Order has already been inwarded.'})
            


            updated = child_purchase_order_table.objects.filter(id=data_id).update(status=0)


            if updated:
                update_summary(purchase_order_table, child_purchase_order_table, 'tm_po_id', po_id)
                return JsonResponse({'message': 'yes'})
            else:
                return JsonResponse({'message': 'no such data'})
        except Exception as e:
            return JsonResponse({'message': 'exception', 'error': str(e)})

    return JsonResponse({'message': 'Invalid request method'})




def delete_tm_po(request):
    if request.method == 'POST':
        data_id = request.POST.get('id')
        role_id = request.session.get('role_id')

        has_access, error_message = check_user_access(role_id, 'purchase_order', "delete")
        if not has_access:
            return JsonResponse({'message': 'permission', 'error_message': 'You do not have permission to delete purchase details'})

        try:
            po = purchase_order_table.objects.filter(id=data_id, status=1).first()
            if not po:
                return JsonResponse({'message': 'no such data'})

            if po.po_status.lower() == 'accepted' or po.is_accepted == 1:
                return JsonResponse({'message': 'warning', 'error_message': 'Cannot delete. The Purchase Order has been accepted.'})

            inward_exists = child_purchase_inward_table.objects.filter(po_id=data_id, status=1).exists()
            if inward_exists:
                return JsonResponse({'message': 'warning', 'error_message': 'Cannot delete. The Purchase Order has already been inwarded.'})

            updated = purchase_order_table.objects.filter(id=data_id).update(status=0)
            updated = child_purchase_order_table.objects.filter(tm_po_id=data_id).update(status=0)
            if updated:
                return JsonResponse({'message': 'yes'})
            else:
                return JsonResponse({'message': 'no such data'})
        except Exception as e:
            return JsonResponse({'message': 'exception', 'error': str(e)})

    return JsonResponse({'message': 'Invalid request method'})

def load_po_price_old(request):
    if request.method == 'POST':        
        subcategory_id = request.POST.get('subcategory_id')
        brand_id       = request.POST.get('brand_id')
        model_id       = request.POST.get('model_id')
        variant_id     = request.POST.get('variant_id')
        color_id       = request.POST.get('color_id')

       

        # Filter items only by inward_type
        items = item_table.objects.filter(
            sub_category_id=subcategory_id,
            brand_id=brand_id,
            model_id=model_id,
            variant_id=variant_id,
            color_id=color_id,
            is_active=1,
            status=1
        ).values('sku', 'ean', 'hsn_code', 'db_price','wholesale_price',
                'wholesale_amount',  'franchise_price', 'franchise_amount','mop_price','bop_price','mrp')

        return JsonResponse({'items': list(items)})

    return JsonResponse({'error': 'Invalid request'}, status=400)


def load_po_price(request):
    if request.method == 'POST':
        subcategory_id = request.POST.get('subcategory_id')
        brand_id       = request.POST.get('brand_id')
        model_id       = request.POST.get('model_id')
        variant_id     = request.POST.get('variant_id')
        color_id       = request.POST.get('color_id')
        branch_id      = request.session.get('branch_id')

        # --------------------------------------------
        # 1️⃣ GET LATEST INWARD PRICE (IF EXISTS)
        # --------------------------------------------
        inward_price = child_purchase_inward_table.objects.filter(
            subcategory_id=subcategory_id,
            brand_id=brand_id,
            model_id=model_id,
            variant_id=variant_id,
            color_id=color_id,
            branch_id=branch_id,
            status=1,
            is_active=1
        ).order_by('-created_on').values(
            'fsp', 'wsp', 'mop', 'bop', 'mrp', 'db_price'
        ).first()

        # --------------------------------------------
        # 2️⃣ GET ITEM MASTER DATA
        # --------------------------------------------
        item_qs = item_table.objects.filter(
            sub_category_id=subcategory_id,
            brand_id=brand_id,
            model_id=model_id,
            variant_id=variant_id,
            color_id=color_id,
            is_active=1,
            status=1
        ).values(
            'sku', 'ean', 'hsn_code',
            'db_price', 'mrp',
            'franchise_price', 'wholesale_price',
            'mop_price', 'bop_price',
            'wholesale_amount', 'franchise_amount',
        )

        items = []
        for item in item_qs:
            items.append({
                'sku'      : item['sku'],
                'ean'      : item['ean'],
                'hsn_code' : item['hsn_code'],

                # 🔥 Price priority: inward → item master
                'db_price' : inward_price['db_price'] if inward_price else item['db_price'],
                'mrp'      : inward_price['mrp'] if inward_price else item['mrp'],
                'fsp'      : inward_price['fsp'] if inward_price else item['franchise_amount'],
                'wsp'      : inward_price['wsp'] if inward_price else item['wholesale_amount'],
                'mop'      : inward_price['mop'] if inward_price else item['mop_price'],
                'bop'      : inward_price['bop'] if inward_price else item['bop_price'],
            })

        return JsonResponse({'items': items})

    return JsonResponse({'error': 'Invalid request'}, status=400)