""" functions to fetch dta from other services"""

import os
import math
from rest_framework import status
from rest_framework.exceptions import ValidationError, NotFound
from . import models as purchase_models
from django.db import connections, connection

from django.core.cache import cache
from .services.erp_client import OdooAPIService


def get_packaged_product_data(product_id):
    """function to get product details"""
        
    with connection.cursor() as cursor:

        cursor.execute(
                    """SELECT Packaging from ProductMaster where Id=%s""", [
            product_id
        ])
        result = cursor.fetchone()
        packaging = result[0]


        cursor.execute(
                    """SELECT pkm.pack_tare_weight, pkm.nos_per_pallet, pkm.pallet_weight , pm.Packaging, 
                        pkm.packaged_product, pkm.dimension, pm.ArticleCode
                        from PackageMaster as pkm 
                        join ProductMaster as pm
                        on pkm.product_id=pm.Id
                        where pkm.product_id=%s""", [
            product_id
        ])
        product_data = cursor.fetchone()
    
    if product_data:
        product_dict = {
            'article_code': product_data[6],
            'packaging': packaging,
            'dimension': product_data[5],
        }
        return product_dict
    else :
        return {'packaging': packaging}

# def get_product_details(product_id):
#     """function to get product details"""
        
#     with connection.cursor() as cursor:

#         cursor.execute(
#                     """SELECT Packaging from ProductMaster where Id=%s""", [
#             product_id
#         ])
#         result = cursor.fetchone()
#         packaging = result[0]


#         cursor.execute(
#                     """SELECT pkm.pack_tare_weight, pkm.nos_per_pallet, pkm.pallet_weight , pm.Packaging, 
#                         pkm.packaged_product, pkm.dimension, pm.ArticleCode
#                         from PackageMaster as pkm 
#                         join ProductMaster as pm
#                         on pkm.product_id=pm.Id
#                         where pkm.product_id=%s""", [
#             product_id
#         ])
#         product_data = cursor.fetchone()
    
#     if product_data:
#         product_dict = {
#             'article_code': product_data[6],
#             'packaging': packaging,
#             'dimension': product_data[5],
#         }
#         return product_dict
#     else :
#         return {'packaging': packaging}
    

def get_product_data(product_id):
    """function to get product details"""
        
    with connection.cursor() as cursor:

        cursor.execute(
                    """SELECT ArticleCode, Packaging, NickName, EN_FullName, ProductCategory,  DU_FullName, ProductName 
                      from ProductMaster
                      where Id=%s""", [
            product_id
        ])
        result = cursor.fetchone()
    
    if result:
        product_dict = {
            'article_code': result[0],
            'packaging': result[1],
            'en_full_name': result[3],
            'product_category': result[4],
            'du_full_name': result[5],
            'product_name': result[6],
        }
        return product_dict
    else :
        return None
    
def get_product_data_from_article_code(article_code):
    """function to get product details"""
        
    with connection.cursor() as cursor:

        cursor.execute(
                    """SELECT Id, Packaging, NickName, EN_FullName, ProductCategory,  DU_FullName, ProductName 
                      from ProductMaster
                      where ArticleCode=%s""", [
            article_code
        ])
        result = cursor.fetchone()
    
    if result:
        product_dict = {
            'id': result[0],
            'packaging': result[1],
            'en_full_name': result[3],
            'product_category': result[4],
            'du_full_name': result[5],
            'product_name': result[6],
        }
        return product_dict
    else :
        return None
    

def get_product_data_from_exact(product_id):
    """function to get product details from Exact DB"""

    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT ArticleCode, ProductName, DU_FullName, IsADR from ProductMaster where Id=%s
            """, [
                product_id
            ])
            product_data = cursor.fetchone()
            
    except Exception as e:
        raise NotFound({
            "status_code": 500,
            "status": "error",
            "message": f"Product details not found: {str(e)}",
            "data": None
        })
    
    if product_data:
        article_code = product_data[0]
        response = {
                    'product_name': product_data[1].strip(),
                    'article_code': product_data[0].strip(), 
                    'un_code': product_data[2].strip() if product_data[2] and product_data[3] == 'ADR' else ''
                    }
        return response
    else:
        return None
    
def get_no_of_pallets(total_no_of_items, no_of_items_per_pallet):
    """
    Calculate number of pallets required.
    """
    if not no_of_items_per_pallet or no_of_items_per_pallet == 0:
        return None  # avoid division by zero
    return math.ceil(total_no_of_items / no_of_items_per_pallet)
    

def get_package_info(order_request, product_id=None):
    """function to get package info data if PackingDetail instance not created """

    new_product = True
    if not product_id:
        product_id = order_request.ProductId
        new_product = False

    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT pkm.pack_tare_weight, pkm.nos_per_pallet, pkm.pallet_weight , pm.Packaging, 
                        pkm.packaged_product, pkm.dimension, pkm.kg_per_packaging
                        from PackageMaster as pkm 
                        join ProductMaster as pm
                        on pkm.product_id=pm.Id
                        where pkm.product_id=%s
            """, [
                product_id
            ])
            print(product_id)
            packing_data = cursor.fetchone()
    except:
        raise NotFound({
            "status_code": 500,
            "status": "error",
            "message": f"Package details not found product_id {product_id}",
            "data": None
        })

    packing = purchase_models.PackingDetail.objects.filter(order_request=order_request).first()

    """ if its a not a new product and packing details exists , fetch existing saved 
    values for order request"""

    nos_per_pallet = packing_data[1] if packing_data else None
    
    if packing and not new_product:
        kg_per_packaging = packing.kg_per_packaging
        no_of_packings = packing.no_of_packings
        pack_tare_weight = packing.pack_tare_weight
        no_of_pallets = packing.no_of_pallet
        pallet_weight = packing.pallet_weight
    else:
        kg_per_packaging = order_request.kg_per_packaging
        no_of_packings = order_request.no_of_packings if order_request.no_of_packings else 0
        pack_tare_weight = packing_data[0] if packing_data else 0
        no_of_pallets = get_no_of_pallets(no_of_packings, nos_per_pallet)
        pallet_weight = packing_data[2] if packing_data else 0

    packaging = packing_data[3].upper() if packing_data else None
    product = packing_data[4] if packing_data else ''
    dimension = packing_data[5] if packing_data else ''      

    data = {
        'kg_per_packaging': kg_per_packaging,
        'no_of_packings': no_of_packings,
        'pack_tare_weight': pack_tare_weight,
        'nos_per_pallet': nos_per_pallet,
        'no_of_pallets': no_of_pallets,
        'pallet_weight': pallet_weight,
        'packaging': packaging,
        'product': product,
        'dimension': dimension
    }

    return data


def get_custom_tariff(order_request):
    """function to get custom tarif from exact"""

    product_id = order_request.ProductId

    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT ArticleCode, TariffNumber from ProductMaster where Id=%s
            """, [
                product_id
            ])
            product_data = cursor.fetchone()
            
    except Exception as e:
        raise NotFound({
            "status_code": 500,
            "status": "error",
            "message": f"Tariff details not found: {str(e)}",
            "data": None
        })
    
    if product_data:
        tariff_number = product_data[1]
        return tariff_number
    else:
        return ''


def get_quantity_summary(order_request):
    """function to retrieve quantity summary details"""
    packing_data = get_package_info(order_request)
    custom_tariff = get_custom_tariff(order_request)

    product_description = {
        'DRUMS': 'Drum + Product',
        'IBC': 'IBC + Product',
        'BAGS': 'Bag + Product '
    }

    if packing_data['packaging'] != 'BULK':
        pack_tare_weight = packing_data['pack_tare_weight'] 
        no_of_packings = packing_data['no_of_packings'] 
        kg_per_packaging = packing_data['kg_per_packaging']
        product_description = product_description.get(packing_data['packaging'], '')
        product_weight = f"{(kg_per_packaging + pack_tare_weight)} Kg"
        no_of_pallets = packing_data['no_of_pallets']
        pallet_weight = packing_data['pallet_weight']
        product_name = packing_data['product']

        net_weight = packing_data['kg_per_packaging'] * packing_data['no_of_packings']
        
        total_pack_tare_weight = (no_of_packings * pack_tare_weight)
        total_pallet_weight = (no_of_pallets * pallet_weight)
        total_tare_weight = (total_pack_tare_weight) + (total_pallet_weight)
        
        gross_weight = net_weight + total_tare_weight


    packagelist_data = {
        'packaging': packing_data['packaging'], 
        'product_name': product_name,
        'custom_tariff': custom_tariff,
        'no_of_packings': no_of_packings,
        'product_description': product_description,
        'product_weight': product_weight,
        'no_of_pallets': no_of_pallets,
        'pallet_weight': pallet_weight,
        'net_weight': net_weight,
        'pack_tare_weight': pack_tare_weight,
        'total_tare_weight': total_tare_weight, 
        'gross_weight': gross_weight,
    }

    return packagelist_data

def get_PackagingInfo(order_id):
    try:
        order_request = purchase_models.OrderRequests.objects.get(Id=order_id)
    except purchase_models.OrderRequests.ObjectDoesNotExist:
        raise ValidationError({
            "status_code": 404,
            "status": "error",
            "message": "Order request not found",
            "data": None
        }, status=status.HTTP_404_NOT_FOUND)
    
    product_id = order_request.ProductId
    product_data = get_product_data(product_id)
    
    if product_data['packaging']=='Bulk':
        return 'Bulk'
    
    package_data = get_quantity_summary(order_request)
    
    pallet_info = ''
    if package_data['no_of_pallets'] and package_data['no_of_pallets'] > 0:
        pallet_info = f'/ {package_data['no_of_pallets']} pallets'
    
    try:
        packaging = f"{package_data['no_of_packings']} {package_data['product_description']} {pallet_info} " 
        return packaging
    except:
        return package_data['packaging']


def get_user_data(user_id):
    """function to user_details"""
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT Username, FirstName, LastName   from UserMaster where Id=%s
            """, [
                user_id
            ])
            user_data = cursor.fetchone()
            
    except Exception as e:
        raise NotFound({
            "status_code": 500,
            "status": "error",
            "message": f"User details not found: {str(e)}",
            "data": None
        })
    if user_data:
        return {
            'user_name': user_data[0],
            'first_name': user_data[1],
            'last_name': user_data[2]}
    else:
        return None


def get_address_data(address_id):
    """function to user_details"""
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT Id, ExactERPAddressId,
                            CustomerName,
                            City,
                            AddressLine1,
                            AddressLine2,
                            AddressLine3,
                            PostalCode,
                            ParentShipTo,
                            HasOriginStatement,
                            Country
                  from AddressMaster where Id=%s
            """, [
                address_id
            ])
            user_data = cursor.fetchone()
            
    except Exception as e:
        raise NotFound({
            "status_code": 500,
            "status": "error",
            "message": f"User details not found: {str(e)}",
            "data": None
        })
    if user_data:
        return {
            'Id': user_data[0], 
            'exact_erp_address_id': user_data[1],
            'customer_name': user_data[2],
            'city': user_data[3],
            'addressline1': user_data[4],
            'addressline2': user_data[5],
            'addressline3': user_data[6],
            'postal_code': user_data[7],
            'parent_ship_to': user_data[8],
            'has_origin_statement': user_data[9],
            'country': user_data[10]
            }
    else:
        return None
    
def get_address_data_from_shipto(shipto):
    """function to get customer data from ship to code"""
    # try:
    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT Id, ExactERPAddressId, 
                       CustomerName, 
                       City, 
                       AddressLine1, 
                       AddressLine2, 
                       AddressLine3, 
                       PostalCode, 
                       ParentShipTo, 
                       CustomerNickName
                from AddressMaster where ExactERPAddressId=%s
        """, [
            shipto
        ])
        user_data = cursor.fetchone()
            
    # except Exception as e:
    #     raise NotFound({
    #         "status_code": 500,
    #         "status": "error",
    #         "message": f"User details not found: {str(e)}",
    #         "data": None
        # })
    if user_data:
        return {
            'Id': user_data[0], 
            'exact_erp_address_id': user_data[1],
            'customer_name': user_data[2],
            'city': user_data[3],
            'addressline1': user_data[4],
            'addressline2': user_data[5],
            'addressline3': user_data[6],
            'postal_code': user_data[7],
            'parent_shipto': user_data[8],
            'nickname': user_data[9],
            }
    else:
        return None
        

def get_loadpoint_email(load_point_id):
    """The contact address of a load point, from LoadPointMaster.Email.

    Used to address the third party warehouse request: loading at a site that is
    not ours has to be arranged with whoever runs it.

    Keyed on LoadPointId rather than the name, unlike get_loadpoint_data below -
    the id is what the order actually stores.
    """
    if not load_point_id:
        return ""
    try:
        with connection.cursor() as cursor:
            # No IsDeleted filter: the existing query on this table does not use
            # one either, and LoadPointMaster is managed = False, so the model's
            # columns are not proof the database has them.
            cursor.execute("""
                SELECT Email FROM LoadPointMaster
                WHERE LoadPointMasterID = %s
            """, [load_point_id])
            row = cursor.fetchone()
    except Exception as e:
        # Swallowed on purpose, but note the cost: a failed statement can leave
        # the connection unusable for the rest of the request, which would empty
        # every address on the order screen rather than just this one field.
        print(f"Load point email not found: {e}")
        return ""
    return (row[0] or "").strip() if row else ""


def get_loadpoint_data(order_request_id):
    """function to load point details from order no"""
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                select ors.ExactOrderNo, LP.LoadPointCity, LP.RequiredReferenceNumber
                           FROM Orders as ors 
                           JOIN  LoadPointMaster as LP 
                           ON ors.LoadPoint=LP.LoadPointName 
                           WHERE ors.OrderNo=%s
            """, [
                order_request_id
            ])
            load_point_data = cursor.fetchone()
            
    except Exception as e:
        raise NotFound({
            "status_code": 500,
            "status": "error",
            "message": f"Load point details not found: {str(e)}",
            "data": None
        })
    if load_point_data:
        return {
            'load_point_city': load_point_data[1],
            'required_reference_no': load_point_data[2],
            }
    else:
        return None
    
def get_incoterm_data(incoterm):
    """function to get incoterm details"""
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                select Code, Id, Remarks FROM Incoterms WHERE Code=%s
            """, [
                incoterm
            ])
            incoterm = cursor.fetchone()
            
    except Exception as e:
        raise NotFound({
            "status_code": 500,
            "status": "error",
            "message": f"Incoterm not found: {str(e)}",
            "data": None
        })
    if incoterm:
        return {
            'code': incoterm[0],
            'incoterm_id': incoterm[1],
            'remarks': incoterm[2]
            }
    else:
        return None

def get_incoterm_data_from_id(incoterm_id):
    """function to get incoterm details"""
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                select Code, Id, Remarks FROM Incoterms WHERE Id=%s
            """, [
                incoterm_id
            ])
            incoterm = cursor.fetchone()
            
    except Exception as e:
        raise NotFound({
            "status_code": 500,
            "status": "error",
            "message": f"Incoterm not found: {str(e)}",
            "data": None
        })
    if incoterm:
        return {
            'code': incoterm[0],
            'incoterm_id': incoterm[1],
            'remarks': incoterm[2]
            }
    else:
        return None
    

def get_transorter_data(transporter_id):
    """function to user_details"""
    try:
        with connection.cursor() as cursor:
            cursor.execute("""
                SELECT Id, Transporter
                  from TransportMaster where Id=%s
            """, [
                transporter_id

            ])
            user_data = cursor.fetchone()
            
    except Exception as e:
        raise NotFound({
            "status_code": 500,
            "status": "error",
            "message": f"Transporter details not found: {str(e)}",
            "data": None
        })
    if user_data:
        return {
            'Id': user_data[0], 
            'transporter_name': user_data[1],
            }
    else:
        return None

def get_transporter_data(transporter_id):
    """function to get product details"""
        
    with connection.cursor() as cursor:

        cursor.execute(
                    """SELECT Id, Transporter, Email, City
                      from TransportMaster
                      where Id=%s""", [
            transporter_id
        ])
        result = cursor.fetchone()
    
    if result:
        transport_dict = {
            'id': result[0],
            'transporter': result[1],
            'email': result[2],
            'city': result[3],
        }
        return transport_dict
    else :
        return None
    

def get_transporter_contact_data(transporter_id, product_category):
    """function to get transport contact details"""
        
    with connection.cursor() as cursor:

        cursor.execute(
                    """SELECT Id, TransporterId, ProductCategory, MailIds
                      from TransporterContacts
                      where TransporterId=%s and ProductCategory=%s""", [
            transporter_id, product_category
        ])
        result = cursor.fetchone()
    
    if result:
        transport_dict = {
            'id': result[0],
            'transporter': result[1],
            'product_category': result[2],
            'email': result[3],
        }
        return transport_dict
    else :
        return None
    

def get_odoo_customer_requirement_data(ship_to_code, article_code):
    """function to get customer requirements form odoo """

    # cache_key = f"odoo_{ship_to_code}_{article_code}"

    # data = cache.get(cache_key)
    # if data:
    #     print('data already exixsting')
    #     return data

    # ---- API CALL ----
    payload = {
        "ship_to_code": ship_to_code,
        "article_code": article_code
    }
    client = OdooAPIService()
    response = client.get_customer_requirements(payload)

    # ---- SAVE CACHE ----
    # cache.set(cache_key, response, timeout=300)  # 5 minutes

    return response


# The noun a carrier reads, keyed on ProductMaster.Packaging.
_PACKAGE_NOUN = {
    'DRUMS': ('drum', 'drums'),
    'IBC': ('IBC', 'IBCs'),
    'BAGS': ('bag', 'bags'),
}


def _format_pallet_size(dimension):
    """PackageMaster.dimension is "LxWxH" in millimetres, e.g. "1200x1000x1171".

    Returned in centimetres, which is the unit a transporter quotes a pallet in.
    Empty for the rows with no size on file - at present that is every drum
    product, so the line simply does not print for those.
    """
    if not dimension:
        return ''
    parts = [part.strip() for part in str(dimension).lower().split('x')]
    try:
        centimetres = [float(part) / 10 for part in parts if part]
    except ValueError:
        return ''
    if not centimetres:
        return ''
    return ' x '.join(
        ('%d cm' % value) if value == int(value) else ('%.1f cm' % value)
        for value in centimetres
    )


def _format_place(country_code, postal_code, city, country=None):
    """How a carrier writes a place: "SE-69133 Karlskoga, Sweden".

    Every part is optional. LoadPointMaster holds a postcode and country code
    for PVS only and no country name at all, so a third party loading station
    comes out as its city alone.
    """
    country_code = (country_code or '').strip()
    postal_code = (postal_code or '').strip()
    city = (city or '').strip()
    country = (country or '').strip()

    if country_code and postal_code:
        code = '%s-%s' % (country_code, postal_code)
    else:
        code = postal_code
    line = ' '.join(part for part in (code, city) if part)
    return ', '.join(part for part in (line, country) if part)


def get_transport_quote_details(order_request):
    """The shipment as a transporter needs it described in order to price it.

    Route, what is being moved, how it is packed and what it weighs. Everything
    is optional: a value that is not on file is returned empty and the line that
    would have carried it is left out of the mail rather than printed blank.
    """
    details = {
        'origin': '',
        'destination': '',
        # The ship-to address as it would be written on a label, one entry per
        # line: the carrier is being asked to deliver there, so the street and
        # the company name matter as much as the town.
        'destination_lines': [],
        'no_of_packings': None,
        'kg_per_packaging': None,
        'package_noun': '',
        'no_of_pallets': None,
        'pallet_size': '',
        'net_weight': None,
        'gross_weight': None,
        'product_name': '',
        'is_adr': False,
        'un_no': '',
        'hazard_class': '',
        'packing_group': '',
    }

    with connection.cursor() as cursor:
        cursor.execute("""
            SELECT PostalCode, LoadPointCity, CountryCode
            FROM LoadPointMaster WHERE LoadPointMasterID = %s
        """, [order_request.LoadPointId])
        row = cursor.fetchone()
        if row:
            details['origin'] = _format_place(row[2], row[0], row[1])

        cursor.execute("""
            SELECT CountryCode, PostalCode, City, Country, CustomerName,
                   AddressLine1, AddressLine2, AddressLine3
            FROM AddressMaster WHERE Id = %s
        """, [order_request.DeliverTo])
        row = cursor.fetchone()
        if row:
            details['destination'] = _format_place(row[0], row[1], row[2], row[3])
            street = [(part or '').strip() for part in row[5:8]]
            details['destination_lines'] = [
                line for line in (
                    [(row[4] or '').strip()]
                    + [part for part in street if part]
                    + [_format_place(row[0], row[1], row[2], row[3])]
                ) if line
            ]

        cursor.execute("""
            SELECT Packaging, ProductName, UNNo, HazardClass, PackingGroup, IsADR
            FROM ProductMaster WHERE Id = %s
        """, [order_request.ProductId])
        row = cursor.fetchone()
        packaging = ''
        if row:
            packaging = (row[0] or '').strip()
            details['product_name'] = (row[1] or '').strip()
            details['un_no'] = (row[2] or '').strip()
            details['hazard_class'] = (row[3] or '').strip()
            details['packing_group'] = (row[4] or '').strip()
            details['is_adr'] = str(row[5]).strip().lower() in ('1', 'true', 'yes')

        cursor.execute("""
            SELECT dimension, kg_per_packaging
            FROM PackageMaster WHERE product_id = %s
        """, [order_request.ProductId])
        row = cursor.fetchone()
        if row:
            details['pallet_size'] = _format_pallet_size(row[0])
            details['kg_per_packaging'] = row[1]

    # The counts and weights the load list already works out, rather than a
    # second implementation of the same arithmetic.
    try:
        summary = get_quantity_summary(order_request)
    except Exception as error:
        print(f"Quantity summary not available for the transport quote: {error}")
        return details

    no_of_packings = summary.get('no_of_packings')
    details['no_of_packings'] = no_of_packings
    details['no_of_pallets'] = summary.get('no_of_pallets')
    details['net_weight'] = summary.get('net_weight')
    details['gross_weight'] = summary.get('gross_weight')

    singular, plural = _PACKAGE_NOUN.get(packaging.upper(), (packaging, packaging))
    details['package_noun'] = singular if no_of_packings == 1 else plural

    return details