from dateutil.tz import UTC

from lib_b2b.additional_charge import AdditionalCharge
from lib_b2b.erp import ERP, ERPOrderLine
from lib_b2b.glovia.tax import GloviaTaxCode
from lib_b2b.errors import OrderFlowFailure, OrderNotFoundError
from lib_b2b.order_line import OrderLine
from lib_b2b.util import ascii_safe_str
from dateutil.parser import isoparse
from datetime import datetime
from py_aws_oracle_util import insert, insert_many, execute, fetch
import logging
from os import environ
from lib_b2b.shipping import Shipping
import textwrap
import phonenumbers
from aws_xray_sdk.core import xray_recorder

logger = logging.getLogger(__name__)

NO_TAX_CODE = 'NONE'


class GloviaOrderFlowAction:
    """
    Provides functionality for inserting a new order into the Glovia EDI adpater tables.
    """
    def __init__(self, b2b_order):
        self.order = b2b_order
        erp = ERP.for_(self.order.customer_edi_id)
        self.glovia_customer = erp.fetch_customer(self.order['customer_edi_id'])
        if self.glovia_customer.sales_tax:
            self.tax_loc = erp.find_tax_location_code(customer_edi_id=self.order.customer_edi_id, ship_to=self.order.ship_to)
            if self.tax_loc == GloviaTaxCode.NO_TAX_CODE:
                self.order.record_warning(code="TAXLOC-1",
                                          msg=f"Unable to find tax location code for {ascii_safe_str(self.order, 'shipTo.postalCode')}. "
                                              f"Assuming nexus location and setting the tax location to 'NONE'.")
        else:
            self.tax_loc = 'None'
            self.order.chronicle(message=f"Glovia customer ({self.glovia_customer.customer}, "
                                         f"{self.glovia_customer.buy_location}) is not charged sales tax, setting tax "
                                         f"location to 'NONE'")

    def __get_promise_date(self):
        # Promise date should indicate the date we promise to ship the order, however...
        # change promised date to match required date (per Robert Lucas)
        return self.__get_required_date()

    def __get_required_date(self):
        # logic for date determination all moved to order.__assign_make_date
        return self.order.ship_date

    @xray_recorder.capture()
    def __order_exists(self):
        stmt = """
            select CCN, CUSTOMER, PO_NUM from A_KSNHDR where CCN=:ccn and CUSTOMER=:customer and PO_NUM=:po
            union all
            select CCN, CUSTOMER, PO_NUM from A_KSNLNE where CCN=:ccn and CUSTOMER=:customer and PO_NUM=:po
            union all
            select CCN, CUSTOMER, PO_NUM from A_KSNDEL where CCN=:ccn and CUSTOMER=:customer and PO_NUM=:po
            union all
            select CCN, CUSTOMER, PO_NUM from A_KSNHCM where CCN=:ccn and CUSTOMER=:customer and PO_NUM=:po
        """
        parameters = {
            'ccn': self.glovia_customer.ccn,
            'customer': self.glovia_customer.customer,
            'po': self.order['purchase_order']
        }
        results = fetch(sql=stmt,
                        parameters=parameters,
                        config_path="/erp/tdb")
        if results:
            if len(results) > 0:
                return True
        return False

    def __get_phone_number(self):
        customer_phone = ascii_safe_str(self.order, 'shipTo.phone')
        if customer_phone and len(customer_phone) > 24:
            _phone_number = phonenumbers.parse(customer_phone, None)
            customer_phone = phonenumbers.format_number(_phone_number, phonenumbers.PhoneNumberFormat.NATIONAL)
        if customer_phone and len(customer_phone) > 25:
            customer_phone = customer_phone[0:25]
        return customer_phone

    @xray_recorder.capture()
    def __create_order_header(self):
        """
        Create a glovia record in the A_KSNHDR table
        :return:
        """
        order_date_str = self.order.get('order_date', datetime.now().astimezone(UTC).isoformat())
        order_date = isoparse(order_date_str)
        required_date = self.__get_required_date()
        promised_date = self.__get_promise_date()

        data_dict = {
            'CCN': self.glovia_customer.ccn,
            'CUSTOMER': self.glovia_customer.customer,
            'CUS_BUY_LOC': self.glovia_customer.buy_location,
            'PUR_CCN': self.glovia_customer.ccn,
            'PO_NUM': self.order['purchase_order'],
            'PO_EDI_REVISION': self.order.get('purchase_order_revision', 1),
            'SO_HDR_BRANCH': self.glovia_customer.branch,
            'SO_HDR_CREDIT_TERMS': self.glovia_customer.credit_terms,
            'SO_HDR_CUSTOMER': self.glovia_customer.customer,
            'SO_HDR_CUS_AR_LOC': self.glovia_customer.ar_location,
            'SO_HDR_CUS_BUY_LOC': self.glovia_customer.buy_location,
            'SO_HDR_CUS_BUY_CONTACT': ' ',
            'SO_HDR_CUS_AGC': self.glovia_customer.account_code,
            'SO_HDR_CUS_SALES_TYPE': self.glovia_customer.sales_type,
            'SO_HDR_SALES_REGION': self.glovia_customer.sales_region,
            'SO_HDR_SALES_TERR': self.glovia_customer.sales_type,
            'SO_HDR_FREIGHT_PAY': self.glovia_customer.freight_pay,
            'SO_HDR_ORDER_DATE': order_date,
            'SO_HDR_ORDER_TAKER_NAME': 'B2B',
            'SO_HDR_PROJECT': ' ',
            'SO_HDR_PROMISE_DATE': promised_date,
            'SO_HDR_REQD_DATE': required_date,
            'SO_HDR_CCN': self.glovia_customer.ccn,
            'SO_HDR_CUS_PO': self.order['purchase_order'],
            'SO_HDR_CUS_PO_DATE': order_date,
            'SO_HDR_CUS_SHIP_LOC': self.glovia_customer.ship_location,
            'SO_HDR_EFF_DATE': order_date,
            'SO_HDR_ENTRY_DATE': order_date,
            'SO_HDR_MAS_LOC': self.glovia_customer.master_location,
            'SO_HDR_SALES_REP': self.glovia_customer.sales_rep,
            'SO_HDR_MISC1': ' ',
            'SO_HDR_MISC2': ' ',
            'SO_HDR_MISC3': ' ',
            'SO_HDR_OTST': 'Y',
            'SO_HDR_OTST_ADDR1': ascii_safe_str(self.order, 'shipTo.address1', max_length=35, default_value=' '),
            'SO_HDR_OTST_ADDR2': ascii_safe_str(self.order, 'shipTo.address2', max_length=35, default_value=' '),
            'SO_HDR_OTST_ADDR3': ' ',
            'SO_HDR_OTST_ADDR4': ' ',
            'SO_HDR_OTST_ADDR5': ' ',
            'SO_HDR_OTST_ADDR6': ' ',
            'SO_HDR_OTST_CITY': ascii_safe_str(self.order, 'shipTo.city'),
            'SO_HDR_OTST_CNTRY': ascii_safe_str(self.order, 'shipTo.country'),
            'SO_HDR_OTST_NAME': ascii_safe_str(self.order, 'shipTo.name'),
            'SO_HDR_OTST_POST_CD': Shipping.clean_postal_code(ascii_safe_str(self.order, 'shipTo.postalCode')),
            'SO_HDR_OTST_STATE': ascii_safe_str(self.order, 'shipTo.state'),
            'SO_HDR_PMT_TERMS': self.glovia_customer.terms_code,
            'SO_HDR_CARRIER': self.glovia_customer.carrier,
            'SO_HDR_PRICE_CLASS': self.glovia_customer.price_class,
            'SO_HDR_DEL_TERMS_POINT': self.glovia_customer.delivery_terms,
            'SO_HDR_DATE_PRINTED': None,
            'SO_HDR_TIME_PRINTED': ' ',
            'SO_HDR_DATE_CHANGED': None,
            'SO_HDR_TIME_CHANGED': ' ',
            'SO_HDR_CURRENCY': self.glovia_customer.currency,
            'SO_HDR_CUS_VTAX': ' ',
            'SO_HDR_VAT_EXEMPT_REASON': ' ',
            'SO_HDR_VAT_REG': ' ',
            'SO_HDR_OTST_NAME2': ' ',
            'SO_HDR_ERROR': 0,
            'SO_HDR_CUST_ERROR': ' ',
            'SO_HDR_ACCEPT': ' ',
            'SO_HDR_PRINT': ' ',
            'SO_HDR_JOB_REFERENCE': ' ',
            'SO_HDR_JOB_DESCRIPTION': ' ',
            'EDSO_HDR_NEW': ' ',
            'EDSO_LINE_ERROR': 0,
            'SO_HDR_CREATION_DATE': None,
            'SO_HDR_CREATION_TIME': ' ',
            'ACCEPT_': ' ',
            'AMEND': ' ',
            'BUYER': self.order['customer_edi_id'],
            'SELLER': self.order['customer_edi_id'],
            'LINE_PROCESSED': '\n',
            'SALES_CCN': self.glovia_customer.ccn,
            'SO': None,
            'SO_HDR_EXPORT': ' ',
            'SO_HDR_AR_CCN': '105000',
            'SO_HDR_DEST_CNTRY': ascii_safe_str(self.order, 'shipTo.country'),
            'SO_HDR_DISC_PCT': 0,
            'SO_HDR_ACK_CODE': '\n',
            'SO_HDR_REV': 0,
            'SO_HDR_CREDIT_HOLD': self.glovia_customer.credit_hold,
            'SO_HDR_CUS_ETAX': 'Y' if self.glovia_customer.excise_tax else ' ',
            'SO_HDR_CUS_STAX': 'Y' if self.glovia_customer.sales_tax else ' ',
            'SO_HDR_CUS_UTAX': 'Y' if self.glovia_customer.use_tax else ' ',
            'AMEND_VALID': ' ',
            'TYPE_': ' ',
            'DEL_DATE': 2,
            'CONTACT': ascii_safe_str(self.order, 'shipTo.name'),
            'CONTACT_PHONE': self.__get_phone_number(),
            'RESPONSE_AMEND': '\n',
            'DEL_': ' ',
            'HELD': ' ',
            'GUIH_ALPHA_01': self.order['id'],
            'GUIH_ALPHA_02': datetime.utcnow().isoformat()
        }
        result = insert(schema='glovia710',
                        table='A_KSNHDR',
                        parameters=data_dict,
                        config_path="/erp/tdb")
        logger.info(f"Inserted order record for order: {self.order['id']} into glovia710.A_KSNHDR")
        logger.debug(data_dict)

    @xray_recorder.capture()
    def __create_order_lines(self):
        """
        create order line entries in A_KSNLNE
        :return:
        """
        order_date = isoparse(self.order['order_date'] or datetime.now().astimezone(UTC).isoformat())
        lines = []
        for line in self.order.lines:
            from lib_b2b.glovia import Glovia
            line_item = Glovia().get_customer_item(customer_edi_id=self.order.customer_edi_id,
                                                   customer_item_name=line.product_id)

            total_discounts = sum(d.amount or 0 for d in line.discounts or [])
            total_parts_amt = line.amount

            data_dict = {
                'CCN': self.glovia_customer.ccn,
                'CUSTOMER': self.glovia_customer.customer,
                'CUS_BUY_LOC': self.glovia_customer.buy_location,
                'PUR_CCN': self.glovia_customer.ccn,
                'PO_NUM': self.order['purchase_order'],
                'PO_EDI_REVISION': self.order.get('purchase_order_revision', 1),
                'PO_LINE': line.purchase_order_line,
                'SO_LINE_AGC': line_item.accounting_code,
                'SO_LINE_CUS_SHIP_LOC': self.glovia_customer.ship_location,
                'SO_LINE_ITEM': line_item.item,
                'SO_LINE_MAS_LOC': self.glovia_customer.master_location,
                'SO_LINE_REVISION': line_item.item_revision,
                'SO_LINE_SELL_UM': line_item.selling_unit_of_measure,
                'SO_LINE_ADDED_DATE': order_date,
                'SO_LINE_CUS_PO': self.order['purchase_order'],
                'SO_LINE_CUS_PO_DATE': order_date,
                'SO_LINE_CUS_PO_LINE': line.purchase_order_line,
                'SO_LINE_EFF_DATE': order_date,
                'SO_LINE_EST_ORDER_DISC_AMT': total_discounts,
                'SO_LINE_EST_DISC_AMT': total_discounts,
                'SO_LINE_EST_PARTS_AMT': total_parts_amt,
                'SO_LINE_DEMAND_STREAM': ' ',
                'SO_LINE_DEMAND_STREAM_SOURCE': 'M',
                'SO_LINE_OTST': ' ',
                'SO_LINE_OTST_ADDR1': ascii_safe_str(self.order, 'shipTo.address1', max_length=35, default_value=' '),
                'SO_LINE_OTST_ADDR2': ascii_safe_str(self.order, 'shipTo.address2', max_length=35, default_value=' '),
                'SO_LINE_OTST_ADDR3': ' ',
                'SO_LINE_OTST_ADDR4': ' ',
                'SO_LINE_OTST_ADDR5': ' ',
                'SO_LINE_OTST_ADDR6': ' ',
                'SO_LINE_OTST_CITY': ascii_safe_str(self.order, 'shipTo.city'),
                'SO_LINE_OTST_CNTRY': ascii_safe_str(self.order, 'shipTo.country'),
                'SO_LINE_OTST_NAME': ascii_safe_str(self.order, 'shipTo.name'),
                'SO_LINE_OTST_POST_CD': Shipping.clean_postal_code(ascii_safe_str(self.order, 'shipTo.postalCode')),
                'SO_LINE_OTST_STATE': ascii_safe_str(self.order, 'shipTo.state'),
                'SO_LINE_SALES_REP': self.glovia_customer.sales_rep,
                'SO_LINE_UNIT_PRICE': line_item.unit_price,
                'SO_LINE_STOCK_UM': line_item.stocking_unit_of_measure,
                'SO_LINE_TOT_ORD_QTY': line.quantity,
                'SO_LINE_CCN': self.glovia_customer.ccn,
                'SO_LINE_STK': 'STK' if line_item.stock_item else 'NST',
                'SO_LINE_UM_SCALAR': 1,
                'SO_LINE_CARRIER': self.glovia_customer.carrier,
                'SO_LINE_ORIGIN_CNTRY': 'USA',
                'SO_LINE_DISPATCH_CNTRY': 'USA',
                'SO_LINE_ERROR': 0,
                'ACCEPT_': 'Y',
                'AMEND': 'N',
                'PROCESSED': ' ',
                'UNIT_PRICE_WAS': line_item.standard_cost,
                'SO_LINE_CUST_ITEM': line_item.customer_item_name,
                'SO_LINE_CUST_REV': line_item.customer_item_revision,
                'SO_LINE_UNIT_COST': line_item.standard_cost,
                'SO_LINE_ETAX': 'Y' if line_item.excise_tax else ' ',
                'SO_LINE_STAX': 'Y' if line_item.sales_tax else ' ',
                'SO_LINE_UTAX': 'Y' if line_item.use_tax else ' ',
                'SO_LINE_TAX_LOC_CODE': self.tax_loc,
                'SO_LINE_FORECAST_CONSUME': 'Y',
                'UM': line_item.selling_unit_of_measure,
                'PO_PRICE': line.unit_price or line_item.unit_price,
                'ITEM_WAS': line_item.item,
                'REVISION_WAS': line_item.item_revision,
                'PRICE_AMEND': 'N',
                'OTHER_AMEND': 'N',
                'CUST_ITEM_WAS': line_item.customer_item_name,
                'CUST_REV_WAS': line_item.customer_item_revision,
                'CUST_ITEM_IS': line_item.customer_item_name,
                'CUST_REV_IS': line_item.customer_item_revision,
                'ITEM_UPC_WAS': line_item.upc,
                'ITEM_UPC_IS': line_item.upc,
                'GUIL_ALPHA_01': self.order['id'],
                'GUIL_ALPHA_02': line.purchase_order_line
            }

            for idx, tax_line in enumerate(filter(lambda x: x.amount > 0, line.tax_lines or []), 1):
                if idx <= 6:
                    data_dict[f"SO_LINE_STAX_RATE{idx}"] = tax_line.rate

            lines.append(data_dict)
        result = insert_many(schema='glovia710',
                             table='A_KSNLNE',
                             parameter_list=lines,
                             config_path="/erp/tdb")
        logger.info(f"Inserted {len(result)} order line records for "
                    f"order: {self.order['id']} into glovia710.A_KSNLNE")
        logger.debug(data_dict)

    @xray_recorder.capture()
    def __create_order_deliveries(self):
        """
        Create order delivery records in A_KSNDEL
        :return:
        """
        required_date = self.__get_required_date()
        promised_date = self.__get_promise_date()
        deliveries = []
        for line in self.order.lines:
            data_dict = {
                'CCN': self.glovia_customer.ccn,
                'CUSTOMER': self.glovia_customer.customer,
                'CUS_BUY_LOC': self.glovia_customer.buy_location,
                'PUR_CCN': self.glovia_customer.ccn,
                'PO_NUM': self.order['purchase_order'],
                'PO_EDI_REVISION': self.order.get('purchase_order_revision', 1),
                'PO_LINE': line.purchase_order_line,
                'PO_DEL_NUM': '001',
                'SO_DEL_ORDERED_QTY':  line.quantity,
                'SO_DEL_SCHED_DATE': required_date,
                'SO_DEL_PROMISE_DATE': promised_date,
                'QTY_WAS': line.quantity,
                'DATE_WAS': required_date
            }
            deliveries.append(data_dict)
        result = insert_many(schema='glovia710',
                             table='A_KSNDEL',
                             parameter_list=deliveries,
                             config_path="/erp/tdb")
        logger.info(f"Inserted {len(result)} order delivery records for "
                    f"order: {self.order['id']} into glovia710.A_KSNDEL")
        logger.debug(deliveries)

    @xray_recorder.capture()
    def __create_order_header_comments(self):
        """
        Create order header comment records in A_KSNHCM
        :return:
        """
        notes = ascii_safe_str(self.order, 'note')
        if notes:
            comments = textwrap.wrap(notes, 70, break_long_words=False, max_lines=99)
            records = []
            for idx, comment_line in enumerate(comments):
                data_dict = {
                    'CCN': self.glovia_customer.ccn,
                    'CUSTOMER': self.glovia_customer.customer,
                    'CUS_BUY_LOC': self.glovia_customer.buy_location,
                    'PUR_CCN': self.glovia_customer.ccn,
                    'PO_NUM': self.order['purchase_order'],
                    'PO_EDI_REVISION': self.order.get('purchase_order_revision', 1),
                    'CMT_POS': 'T',
                    'HDR_CMNT_SEQ': str(idx+1).zfill(2),
                    'HDR_COMMENT': comment_line
                }
                records.append(data_dict)
            result = insert_many(schema='glovia710',
                                 table='A_KSNHCM',
                                 parameter_list=records,
                                 config_path="/erp/tdb")
            logger.info(f"Inserted {len(result)} comment lines for "
                        f"order: {self.order['id']} into glovia710.A_KSNHCM")
            logger.debug(records)

    @xray_recorder.capture('do_order_flow')
    def do(self):
        try:
            if self.__order_exists():
                # delete and re-insert
                logger.warning(f"Orders {self.order['id']} already exists in the adapter tables. Removing from adapter tables before write.")
                remove_action = GloviaOrderFlowRejectAction(b2b_order=self.order)
                remove_action.do()
            self.__create_order_header_comments()
            self.__create_order_deliveries()
            self.__create_order_lines()
            self.__create_order_header()

        except Exception as e:
            logger.exception(e)
            logger.fatal(f"Unable to write order {self.order['id']} to Glovia due to: {str(e)}")
            raise OrderFlowFailure(f"Unable to write order {self.order['id']} to Glovia due to: {str(e)}") from e


class GloviaOrderFlowRejectAction:
    """
    If the order status is READY, remove from the adapter tables.
    """
    def __init__(self, b2b_order):
        self.order = b2b_order
        from lib_b2b.glovia import GloviaCustomer
        self.glovia_customer = GloviaCustomer.fetch(self.order.customer_edi_id)

    @xray_recorder.capture()
    def __order_exists(self):
        stmt = """
            select CCN, CUSTOMER, PO_NUM from A_KSNHDR where CCN=:ccn and CUSTOMER=:customer and PO_NUM=:po
            union all
            select CCN, CUSTOMER, PO_NUM from A_KSNLNE where CCN=:ccn and CUSTOMER=:customer and PO_NUM=:po
            union all
            select CCN, CUSTOMER, PO_NUM from A_KSNDEL where CCN=:ccn and CUSTOMER=:customer and PO_NUM=:po
            union all
            select CCN, CUSTOMER, PO_NUM from A_KSNHCM where CCN=:ccn and CUSTOMER=:customer and PO_NUM=:po
        """
        parameters = {
            'ccn': self.glovia_customer.ccn,
            'customer': self.glovia_customer.customer,
            'po': self.order['purchase_order']
        }
        results = fetch(sql=stmt,
                        parameters=parameters,
                        config_path="/erp/tdb")
        if results:
            if len(results) > 0:
                return True
        return False

    @xray_recorder.capture()
    def __remove_order_header(self):
        """
        Remove a glovia record in the A_KSNHDR table
        :return:
        """
        del_stmt = """
            delete from A_KSNHDR 
            where
                CCN=:ccn
                and CUSTOMER=:customer
                and PO_NUM=:po 
        """
        parameters = {
            'ccn': self.glovia_customer.ccn,
            'customer': self.glovia_customer.customer,
            'po': self.order['purchase_order']
        }
        results = execute(sql=del_stmt,
                        parameters=parameters,
                        config_path="/erp/tdb")
        logger.info(f"Removed {results} for order {self.order['id']} from A_KSNHDR.")

    @xray_recorder.capture()
    def __remove_order_lines(self):
        """
        Remove a glovia record in the A_KSNLNE table
        :return:
        """
        del_stmt = """
                    delete from A_KSNLNE 
                    where
                        CCN=:ccn
                        and CUSTOMER=:customer
                        and PO_NUM=:po 
                """
        parameters = {
            'ccn': self.glovia_customer.ccn,
            'customer': self.glovia_customer.customer,
            'po': self.order['purchase_order']
        }
        results = execute(sql=del_stmt,
                          parameters=parameters,
                          config_path="/erp/tdb")
        logger.info(f"Removed {results} for order {self.order['id']} from A_KSNLNE.")

    @xray_recorder.capture()
    def __remove_order_deliveries(self):
        """
        Remove a glovia records in the A_KSNDEL table
        :return:
        """
        del_stmt = """
                    delete from A_KSNDEL 
                    where
                        CCN=:ccn
                        and CUSTOMER=:customer
                        and PO_NUM=:po 
                """
        parameters = {
            'ccn': self.glovia_customer.ccn,
            'customer': self.glovia_customer.customer,
            'po': self.order['purchase_order']
        }
        results = execute(sql=del_stmt,
                          parameters=parameters,
                          config_path="/erp/tdb")
        logger.info(f"Removed {results} for order {self.order['id']} from A_KSNDEL.")

    @xray_recorder.capture()
    def __remove_order_header_comments(self):
        """
        Remove a glovia records in the A_KSNHCM table
        :return:
        """
        del_stmt = """
                    delete from A_KSNHCM 
                    where
                        CCN=:ccn
                        and CUSTOMER=:customer
                        and PO_NUM=:po 
                """
        parameters = {
            'ccn': self.glovia_customer.ccn,
            'customer': self.glovia_customer.customer,
            'po': self.order['purchase_order']
        }
        results = execute(sql=del_stmt,
                          parameters=parameters,
                          config_path="/erp/tdb")
        logger.info(f"Removed {results} for order {self.order['id']} from A_KSNHCM.")

    @xray_recorder.capture('do_order_flow')
    def do(self):
        try:
            if self.__order_exists():
                self.__remove_order_header_comments()
                self.__remove_order_deliveries()
                self.__remove_order_lines()
                self.__remove_order_header()
            else:
                logger.warning(f"Orders {self.order['id']} is not in the adapter tables. Exiting without trying to remove.")
        except Exception as e:
            logger.exception(e)
            logger.fatal(f"Unable to remove order {self.order['id']} in Glovia due to: {str(e)}")
            raise OrderFlowFailure(f"Unable to remove order {self.order['id']} in Glovia due to: {str(e)}") from e


class GloviaOrderPostConvertUpdateAction:
    """
    Provides functionality to updating the sales order with the correct discounts and taxes after the EDI transaction has been converted to an SO.
    """
    def __init__(self, b2b_order):
        self.order = b2b_order
        erp = ERP.for_(self.order.customer_edi_id)
        self.glovia_customer = erp.fetch_customer(self.order['customer_edi_id'])
        self.erp_order = erp.fetch_order_by_id(self.order.order_id)
        self.erp_order_lines = erp.fetch_order_lines_by_id(self.order.order_id)
        self.has_drop_shipments = erp.has_drop_shipments(self.order.order_id)
        self.sales_ccn = self.erp_order.sales_ccn
        self.so = str(self.erp_order.sales_order).rjust(20, ' ')
        if self.glovia_customer.sales_tax:
            self.tax_loc = erp.find_tax_location_code(customer_edi_id=self.order.customer_edi_id, ship_to=self.order.ship_to)
            if self.tax_loc == GloviaTaxCode.NO_TAX_CODE:
                self.order.record_warning(code="TAXLOC-1",
                                          msg=f"Unable to find tax location code for {ascii_safe_str(self.order, 'shipTo.postalCode')}. "
                                              f"Assuming nexus location and setting the tax location to 'NONE'.")
        else:
            self.tax_loc = 'None'
            self.order.chronicle(message=f"Glovia customer ({self.glovia_customer.customer}, "
                                         f"{self.glovia_customer.buy_location}) is not charged sales tax, setting tax "
                                         f"location to 'NONE'")

    @xray_recorder.capture()
    def __order_exists(self):
        stmt = """
            select
                CCN, CUSTOMER, CUS_PO, SO
            from SO_HDR
            where
                CCN=:ccn
                and CUSTOMER=:customer
                and SO=:so
                and CUS_PO=:po
        """
        parameters = {
            'ccn': self.glovia_customer.ccn,
            'customer': self.glovia_customer.customer,
            'so': self.so.strip().rjust(20, ' '),
            'po': self.order['purchase_order']
        }
        results = fetch(sql=stmt,
                        parameters=parameters,
                        config_path="/erp/tdb")
        if results:
            if len(results) > 0:
                return True
        return False

    def __get_order_addl_charge_amt(self):
        """
        returns the total additional charge amount for the order
        :return: float
        """
        return self.order.totals.total_additional_charges

    def __get_order_addl_charge_tax_amt(self):
        """
        Get the taxes that are attributable to the additional charges on the order
        :return: float
        """
        return sum(tl.amount or 0 for ac in self.order.additional_charges or [] for tl in ac.tax_lines or [])

    def __get_order_parts_tax_amt(self):
        """
        Get the taxes that are attributable to the order lines on the order
        :return: float
        """
        return sum(l.amount or 0 for line in self.order.lines or [] for l in line.tax_lines or [])

    def __get_order_order_disc_amt(self):
        """
        returns the header level order discounts
        :return: float
        """
        # It doesn't look like Shopify allows true order level discounts.
        # Instead, it pre-applies them to the appropriate lines
        total_order_header_discount = 0
        # for d in self.order['discounts']:
        #     total_order_header_discount += d['amount']
        return total_order_header_discount

    def __get_order_parts_amt(self):
        """
        returns the total for the parts on the order without discount or tax
        :return: float
        """
        return self.order.totals.total_line_amount

    def __get_order_disc_amt(self):
        """
        returns the total of any line level discounts for the order
        :return: float
        """
        total_line_discounts = self.order.totals.total_discount - self.__get_order_order_disc_amt()
        return total_line_discounts

    def __get_order_backlog_amt(self):
        """
        returns the amount charged to the customer to add to the customer's account backlog
        :return: float
        """
        return self.order.totals.total_amount

    def __get_order_tax_amt(self):
        """
        returns the total taxes on the order
        :return: float
        """
        return self.order.totals.total_tax

    def __get_line_portion_of_order_discount(self):
        """
        allocates header level discount across order lines
        :return:
        """
        # This is not needed for Shopify
        header_discount_amt = 0
        for d in self.order.discounts or []:
            header_discount_amt += d.amount
        line_header_discount_portion = 0
        if header_discount_amt > 0:
            line_header_discount_portion = round(header_discount_amt / len(self.order.lines), 2)
        return line_header_discount_portion

    def __get_line_disc_amt(self, line):
        """
        Aggregates the discounts for the line and returns the total
        :param line: b2b order line object
        :return: float
        """
        return sum(d.amount or 0 for d in line.discounts or [])

    def __get_line_tax_amt(self, line):
        """
        Aggregates the taxes for the line and returns the total
        :param line: b2b order line object
        :return: float
        """
        return sum(t.amount or 0 for t in line.tax_lines or [])

    @xray_recorder.capture()
    def __update_header(self):
        """
        Update the estimated amounts in SO_HDR
        :return:
        """
        statement = """
            update SO_HDR
            set
                TOT_EST_ADDL_CHG_AMT=:addl_chg_amt,
                TOT_EST_DISC_AMT=:disc_amt,
                TOT_EST_PARTS_AMT=:parts_amt,
                TOT_EST_TAX_AMT=:est_tax_amt,
                TOT_EST_ORDER_DISC_AMT=:order_disc_amt,
                TOT_EST_BACKLOG_AMT=:backlog_amt,
                TOT_SHIP_TAX_AMT=:ship_tax_amt,
                BACKLOG_CHG=:backlog_amt,
                TOT_SHIP_ADDL_CHG_AMT=:addl_chg_amt,
                TOT_SHIP_BACKLOG_AMT=:ship_backlog_amt,
                TAX_LOC_CODE=:tax_loc,
                USE_SI=:use_si
            where
                SALES_CCN=:ccn
                and SO=:sales_order
        """
        parameters = {
                             'addl_chg_amt': self.__get_order_addl_charge_amt(),
                             'disc_amt': self.__get_order_disc_amt(),
                             'parts_amt': self.__get_order_parts_amt(),
                             'est_tax_amt': self.__get_order_tax_amt(),
                             'ship_tax_amt': self.__get_order_addl_charge_tax_amt(),
                             'order_disc_amt': self.__get_order_order_disc_amt(),
                             'backlog_amt': self.__get_order_backlog_amt(),
                             'ship_backlog_amt': self.__get_order_addl_charge_amt() + self.__get_order_addl_charge_tax_amt(),
                             'ccn': self.sales_ccn,
                             'sales_order': self.so,
                             'tax_loc': self.tax_loc,
                             'use_si': 'Y' if self.has_drop_shipments else ' '
                         }
        logger.debug(statement)
        logger.debug(parameters)
        result = execute(sql=statement,
                         parameters=parameters,
                         config_path="/erp/tdb")
        logger.info(f"Updated SO_HDR {self.so} for order {self.order['id']}.")
        logger.debug(parameters)
        logger.debug(result)

    @xray_recorder.capture()
    def __update_lines(self):
        """
        Update the all lines on the order
        :return:
        """
        for line in self.order.lines:
            self.__update_line(line)

    @xray_recorder.capture()
    def __update_line(self, line: OrderLine):
        """
        Update estimated discount, parts, backlog, and tax amounts along with the tax location for the line in SO
        :param line: b2b order line object
        :return:
        """
        line_item = self.glovia_customer.get_item(customer_item_name=line.product_id)
        statement = """
            update SO
            set 
                EST_DISC_AMT=:disc_amt,
                EST_PARTS_AMT=:parts_amt,
                EST_TAX_AMT=:tax_amt,
                EST_ORDER_DISC_AMT=:order_disc_amt,
                EST_BACKLOG_AMT=:backlog_amt,
                TAX_LOC_CODE=:tax_loc,
                PARTS_AMT_OVERRIDDEN='Y',
                OVRD_PARTS_AMT=:parts_amt,
                DISC_OVERRIDDEN_AMT=:disc_amt,
                DISC_OVERRIDDEN='Y',
                PRICE_OVERRIDDEN='Y',
                USE_SI=:use_si
            where
                SALES_CCN=:ccn
                and SO=:sales_order
                and CUS_PO_LINE=:cus_po_line
        """
        # It doesn't look like Shopify allows true order level discounts.
        # Instead, it pre-applies them to the appropriate lines
        line_header_discount_portion = 0
        line_disc_amt = self.__get_line_disc_amt(line) or 0
        line_parts_amt = line.amount or 0
        line_tax_amt = self.__get_line_tax_amt(line) or 0
        line_backlog_amt = line_parts_amt + line_tax_amt - line_disc_amt
        parameters = {
                         'disc_amt': line_disc_amt,
                         'parts_amt': line_parts_amt,
                         'tax_amt': line_tax_amt,
                         'order_disc_amt': line_header_discount_portion,
                         'backlog_amt': line_backlog_amt,
                         'tax_loc': self.tax_loc,
                         'ccn': self.sales_ccn,
                         'sales_order': self.so,
                         'cus_po_line': line.purchase_order_line,
                         'use_si': 'Y' if line_item.drop_ship_item else ' '
                     }
        logger.debug(parameters)
        result = execute(sql=statement,
                         parameters=parameters,
                         config_path="/erp/tdb")
        logger.info(f"Updated SO {self.so} for order {self.order['id']} po_line {line.purchase_order_line}.")
        logger.debug(parameters)
        logger.debug(result)

    @xray_recorder.capture()
    def __update_dropship_deliveries(self):
        """
        Update the all lines on the order
        :return:
        """
        for line in self.erp_order_lines:
            self.__update_dropship_delivery(line)

    @xray_recorder.capture()
    def __update_dropship_delivery(self, line: ERPOrderLine):
        """
        Update drop ship status for item.
        :param line: b2b order line object
        :return:
        """
        try:
            line_item = self.glovia_customer.get_item(item_name=line.item, revision=line.item_revision)
            if line.should_drop_ship:
                statement = """
                    update SO_DEL
                    set 
                        DROP_SHIP='Y',
                        VENDOR=:vendor,
                        PUR_LOC=:pur_loc,
                        USE_SI='Y'
                    where
                        SALES_CCN=:ccn
                        and SO=:sales_order
                        and SO_LINE=:so_line
                """
                parameters = {
                     'vendor': line_item.primary_vender,
                     'pur_loc': line_item.purchase_location,
                     'so_line': line.sales_order_line,
                     'ccn': self.sales_ccn,
                     'sales_order': self.so
                 }
                result = execute(sql=statement,
                                 parameters=parameters,
                                 config_path="/erp/tdb")
                logger.info(f"Updated SO_DEL deliveries for SO {self.so} for "
                            f"order {self.order.order_id} line {line.sales_order_line}.")
                logger.debug(parameters)
                logger.debug(result)
        except Exception as e:
            logger.exception(f"Unable to modify drop ship for order {self.order['id']}", e)

    @xray_recorder.capture()
    def __insert_header_text(self):
        """
        Inserts any order notes into SOHD_TXT so that the notes will show on the appropriate reports
        :return:
        """
        notes = self.order.get('note', None)
        if notes:
            _notes = notes.encode('ascii', errors="ignore").decode()
            comments = textwrap.wrap(_notes, 70, break_long_words=False, max_lines=99)
            # remove any existing text
            exists_stmt = "select count(1) row_count from SOHD_TXT where SALES_CCN=:ccn and SO=:so"
            exists_result = fetch(sql=exists_stmt,
                                  parameters={'ccn': self.sales_ccn, 'so': str(self.so).strip().rjust(20)},
                                  config_path="/erp/tdb")
            if exists_result:
                del_stmt = "delete from SOHD_TXT where SALES_CCN=:ccn and SO=:so"
                del_result = execute(sql=del_stmt,
                                     parameters={'ccn': self.sales_ccn, 'so': str(self.so).strip().rjust(20)},
                                     config_path="/erp/tdb")
            records = []
            for idx, comment_line in enumerate(comments):
                data_dict = {
                    'SALES_CCN': self.sales_ccn,
                    'SO': str(self.so).strip().rjust(20),
                    'SOHD_TXT_SEQ': str(idx + 1).zfill(3),
                    'TEXT': comment_line
                }
                records.append(data_dict)
            result = insert_many(schema='glovia710',
                                 table='SOHD_TXT',
                                 parameter_list=records,
                                 config_path="/erp/tdb")
            logger.info(f"Inserted {len(result)} header text lines for "
                        f"order: {self.order['id']} into glovia710.SOHD_TXT")
            logger.debug(records)

    @xray_recorder.capture()
    def __insert_additional_charges(self):
        """
        Inserts any additional charge records into the SO_EXP table.
        Notes:
            - Will ignore any additional charges whose amount is 0.
            - If the Shopify additional charge 'type' is Fees, then the additional charge code will be RCYC with an AGC of AGC5 and STAX='Y'
        :return:
        """
        if self.order.additional_charges:
            exists_stmt = "select count(1) row_count from SO_EXP where SALES_CCN=:ccn and SO=:so"
            exists_result = fetch(sql=exists_stmt, parameters={'ccn': self.sales_ccn, 'so': str(self.so).strip().rjust(20)}, config_path="/erp/tdb")
            if exists_result:
                # remove any existing additional charges for the order
                del_stmt = "delete from SO_EXP where SALES_CCN=:ccn and SO=:so"
                del_result = execute(sql=del_stmt,
                                     parameters={'ccn': self.sales_ccn, 'so': str(self.so).strip().rjust(20)},
                                     config_path="/erp/tdb")
        records = []
        for idx, ac in enumerate(filter(lambda x: x.amount > 0, self.order.additional_charges or [])):
            ac: AdditionalCharge = ac
            addl_chg_disc_amt = sum(d.amount or 0 for d in ac.discounts or [])
            addl_chg_tax_amt = sum(tl.amount or 0 for tl in ac.tax_lines or [])
            addl_chg_code = 'FRGT'
            addl_chg_agc = 'AGC3'
            addl_chg_tax_agc = 'TAGC'
            if ac.recycling:
                addl_chg_code = 'RCYC'
                addl_chg_agc = 'AGC5'
            order_date = self.order.order_date or datetime.now().astimezone(UTC)
            data_dict={
                'SALES_CCN': self.sales_ccn,
                'SO': str(self.so).strip().rjust(20),
                'SO_EXPENSE': str(idx + 1).zfill(4),
                'ADDL_CHG_AGC': addl_chg_agc,
                'ADDL_CHG_QTY': 1,
                'ADDED_DATE': order_date,
                'STAX': 'Y' if self.glovia_customer.sales_tax else ' ',
                'CONF_DATE': order_date,
                'CUS_PO': self.order['purchase_order'],
                'CUS_PO_DATE': order_date,
                'EST_ADDL_CHG_AMT': ac.amount,
                'SHIP_ADDL_CHG_AMT': ac.amount,
                'EST_DISC_AMT': addl_chg_disc_amt,
                'EST_TAX_AMT': addl_chg_tax_amt,
                'SHIP_TAX_AMT': addl_chg_tax_amt,
                'PRICE_OVERRIDDEN': 'Y',
                'SALES_REP': self.glovia_customer.sales_rep,
                'TAX_AGC': addl_chg_tax_agc,
                'TAX_LOC_CODE': self.tax_loc,
                'ADDL_CHG_CODE': addl_chg_code,
                'UNIT_COST': 0,
                'UNIT_PRICE': ac.amount,
                'UPDATE_SA': 'Y',
                # 'ADDL_CHG_AMT_OVERRIDDEN': 'Y',
                # 'OVRD_ADDL_CHG_AMT': ac['amount'],
                'CCN': self.sales_ccn,
                'PRINT_ACK': 'Y',
                'REV': 1,
                'EST_BACKLOG_AMT': ac.amount,
                'MAS_LOC': self.glovia_customer.master_location,
                'CURR_RATE_DATE': order_date
            }
            records.append(data_dict)

        result = insert_many(schema='glovia710',
                             table='SO_EXP',
                             parameter_list=records,
                             config_path="/erp/tdb")
        logger.info(f"Inserted {len(result)} additional charge lines for "
                    f"order: {self.order.order_id} into glovia710.SO_EXP")
        logger.debug(records)

    @xray_recorder.capture('do_update_order')
    def do(self):
        try:
            if self.__order_exists():
                self.__insert_additional_charges()
                self.__insert_header_text()
                self.__update_lines()
                self.__update_dropship_deliveries()
                self.__update_header()
            else:
                logger.error(f"Orders {self.so} for po {self.order.order_id} does not exist. Unable to modify.")
                raise OrderNotFoundError(f"Orders {self.so} for po {self.order.order_id} does not exist. Unable to modify.")
        except Exception as e:
            logger.exception(e)
            logger.fatal(f"Unable to modify glovia order {self.order.order_id} due to: {str(e)}")
            raise OrderFlowFailure(f"Unable to modify glovia order {self.order.order_id} due to: {str(e)}") from e