from _pydecimal import Decimal
from datetime import datetime

from dateutil.tz import UTC

from lib_b2b.erp import ERP, ERPCustomer, ERPOrderLine, ERPOrder, ERPItem, ERPTaxRate
from lib_b2b.address import Address
from lib_b2b.errors import OrderNotFoundError, ChangeFailedError, OrderCancelError, LineCancelError, \
    OrderLineNotFoundError, NotFoundError, OrderFlowFailure
from py_aws_oracle_util import execute, fetch

from lib_b2b.glovia.order_flow import GloviaOrderFlowRejectAction
from lib_b2b.order_status import OrderStatus
from .customer import GloviaCustomer
from .tax import GloviaTaxCode
from lib_b2b.orders import Orders
from lib_b2b.util import ascii_safe_str
from lib_b2b.shipping import Shipping
import logging
from os import environ
from aws_xray_sdk.core import xray_recorder

logger = logging.getLogger(__name__)


class Glovia(ERP):
    database_config_path = '/erp/tdb'

    def __init__(self) -> None:
        super().__init__()

    def update_address(self, ccn: str, sales_order: str, new_ship_address: Address, tax_location_code: str):
        """
        Update the address on an existing sales order
        :param ccn: organization
        :type ccn: str
        :param sales_order: order number
        :type sales_order: str
        :param new_ship_address: new address
        :type new_ship_address: Address
        :param tax_location_code: tax location for the new address
        :type tax_location_code: str
        :raises IntegrationError if unable to add the order into the erp
        :return: None
        :rtype: None
        """
        so_hdr_stmt = """
                        update SO_HDR
                        set OTST = :otst,
                            OTST_ADDR1 = :addr1,
                            OTST_ADDR2 = :addr2,
                            OTST_CITY = :city,
                            OTST_CNTRY = :country,
                            OTST_NAME = :name,
                            OTST_POST_CD = :postal_code,
                            OTST_STATE = :state,
                            TAX_LOC_CODE = :tax_loc
                        where
                            SALES_CCN=:ccn
                            and SO=:so
                    """
        so_stmt = """
                        update SO
                        set OTST = :otst,
                            OTST_ADDR1 = :addr1,
                            OTST_ADDR2 = :addr2,
                            OTST_CITY = :city,
                            OTST_CNTRY = :country,
                            OTST_NAME = :name,
                            OTST_POST_CD = :postal_code,
                            OTST_STATE = :state,
                            TAX_LOC_CODE = :tax_loc
                        where
                            SALES_CCN=:ccn
                            and SO=:so
                    """
        address_data = {
            'ccn': ccn,
            'so': sales_order.strip().rjust(20),
            'otst': 'Y',
            'addr1': ascii_safe_str(new_ship_address, 'address1', max_length=35, default_value=' '),
            'addr2': ascii_safe_str(new_ship_address, 'address2', max_length=35, default_value=' '),
            'city': ascii_safe_str(new_ship_address, 'city'),
            'country': ascii_safe_str(new_ship_address, 'country'),
            'name': ascii_safe_str(new_ship_address, 'name'),
            'postal_code': Shipping.clean_postal_code(ascii_safe_str(new_ship_address, 'postalCode')),
            'state': ascii_safe_str(new_ship_address, 'state'),
            'tax_loc': tax_location_code
        }

        execute(sql=so_hdr_stmt, parameters=address_data, config_path=Glovia.database_config_path)
        execute(sql=so_stmt, parameters=address_data, config_path=Glovia.database_config_path)

    def update_ship_date(self, ccn: str, sales_order: str, new_ship_date: datetime):
        """
        Update the ship date on an existing sales order
        :param ccn: organization
        :type ccn: str
        :param sales_order: order number
        :type sales_order: str
        :param new_ship_date: the new date that the order needs to be shipped
        :type new_ship_date: a datetime object
        :raises IntegrationError if unable to add the order into the erp
        :return: None
        :rtype: None
        """
        so_hdr_stmt = """
            update SO_HDR
            set PROMISE_DATE = :ship_date,
                REQD_DATE = :ship_date
            where
                SALES_CCN=:ccn
                and SO=:so
        """
        so_del_stmt = """
            update SO_DEL
            set PROMISE_DATE = :ship_date,
                REQD_DATE = :ship_date,
                SCHED_DATE = :ship_date
            where
                SALES_CCN=:ccn
                and SO=:so
        """
        # Ship date coming in as UTC and Glovia uses a timezone naive date for the local timezone :(
        _new_ship_date = new_ship_date.astimezone(tz=None)
        _data = {
            'ccn': ccn,
            'so': sales_order.strip().rjust(20),
            'ship_date': _new_ship_date
        }

        execute(sql=so_hdr_stmt, parameters=_data, config_path=Glovia.database_config_path)
        execute(sql=so_del_stmt, parameters=_data, config_path=Glovia.database_config_path)

    def fetch_order_line(self, ccn: str, sales_order: str, sales_order_line: str) -> ERPOrderLine:
        """
        Retrieve an ERPOrderLine object from the ERP system
        :param ccn: the organization identifier
        :type ccn: str
        :param sales_order: the sales order identifier
        :type sales_order: str
        :param sales_order_line: the sales order line identifier
        :type sales_order_line: str
        :return: an order line object
        :rtype: ERPOrderLine
        """
        statement = """SELECT c.CUSTOMER, c.CUS_LOC AS BUY_LOC, c.EDI_CODE, c.NAME, c.NAME2, c.SORT_NAME, c.ADDR1, 
                              c.ADDR2, c.ADDR3, c.ADDR4, c.ADDR5, c.ADDR6, c.CITY, c.STATE, c.ZIP, c.COUNTRY, 
                              c.EDI_CODE, c.EMAIL, h.SALES_CCN, h.SO, h.CUS_PO, h.OTST_NAME, h.OTST_NAME2, 
                              h.OTST_ADDR1, h.OTST_ADDR2, h.OTST_ADDR3, h.OTST_ADDR4, h.OTST_ADDR5, h.OTST_ADDR6, 
                              h.OTST_CITY, h.OTST_STATE, h.OTST_POST_CD, h.OTST_CNTRY, s.SO_LINE, s.CARRIER, 
                              s.CUS_PO_LINE AS CUS_PO_LINE, s.ITEM, s.REVISION, s.TOT_BO_QTY, s.TOT_CANC_QTY, 
                              s.TOT_COM_QTY, s.TOT_ORD_QTY, s.TOT_SHIP_QTY, i.ITEM, i.REVISION, i.DESCRIPTION, 
                              i.HEIGHT, i.WIDTH, i.LENGTH, i.WEIGHT AS STD_WEIGHT, i.WEIGHT_UM AS STD_WEIGHT_UM, 
                              n.CUS_CONTACT, n.NAME AS CONTACT_NAME, n.PHONE AS CONTACT_PHONE, d.DROP_SHIP, d.SI,
                              ic.DROP_SHIP SHOULD_DROP_SHIP
                        FROM so_hdr h
                          INNER JOIN so s ON h.sales_ccn = s.sales_ccn AND h.so = s.so
                          INNER JOIN so_del d on d.SALES_CCN = s.SALES_CCN and d.SO = s.SO and d.SO_LINE = s.SO_LINE
                          INNER JOIN cus_loc c ON h.customer = c.customer AND h.cus_buy_loc = c.cus_loc
                          INNER JOIN cus_buy b ON b.customer = c.customer AND b.cus_buy_loc = c.cus_loc
                          INNER JOIN item i ON i.item = s.item AND i.revision = s.revision
                          LEFT OUTER JOIN item_cus ic on ic.CCN = h.SALES_CCN and ic.CUSTOMER = h.CUSTOMER 
                                                          and ic.ITEM = s.ITEM and ic.REVISION = s.REVISION
                          LEFT OUTER JOIN cus_con n ON n.customer = b.customer AND n.cus_loc = b.cus_buy_loc AND n.cus_contact = b.cus_contact
                        WHERE
                          h.sales_ccn=:ccn
                          AND h.so=:so
                          AND s.so_line=:so_line"""
        results = fetch(
            sql=statement,
            parameters={'ccn': ccn, 'so': sales_order.strip().rjust(20), 'so_line': sales_order_line},
            config_path="/erp/tdb"
        )
        if results:
            result = results[0]
            return ERPOrderLine(
                customer=result.get('CUSTOMER'),
                customer_location=result.get('BUY_LOC'),
                edi_code=result.get('EDI_CODE'),
                sales_ccn=result.get('SALES_CCN'),
                sales_order=result.get('SO').strip() if result.get('SO') else None,
                sales_order_line=result.get('SO_LINE').strip() if result.get('SO_LINE') else None,
                purchase_order=result.get('CUS_PO').strip() if result.get('CUS_PO') else None,
                purchase_order_line=result.get('CUS_PO_LINE').strip() if result.get('CUS_PO_LINE') else None,
                item=result.get('ITEM').strip() if result.get('ITEM') else None,
                item_revision=result.get('REVISION').strip() if result.get('REVISION') else None,
                backorder_qty=int(result.get('TOT_BO_QTY')),
                committed_qty=int(result.get('TOT_COM_QTY')),
                cancelled_qty=int(result.get('TOT_CANC_QTY')),
                shipped_qty=int(result.get('TOT_SHIP_QTY')),
                order_qty=int(result.get('TOT_ORD_QTY')),
                one_time_ship_to=Address(
                    address1=result.get('OTST_ADDR1'),
                    address2=result.get('OTST_ADDR2'),
                    city=result.get('OTST_CITY'),
                    state=result.get('OTST_STATE'),
                    postalCode=result.get('OTST_POST_CD'),
                    country=result.get('OTST_CNTRY'),
                    name=result.get('OTST_NAME'),
                    company=result.get('OTST_NAME2'),
                    phone=result.get('CONTACT_PHONE')
                ),
                drop_ship=bool(result.get('DROP_SHIP').strip() if result.get('DROP_SHIP') else None),
                should_drop_ship=bool(result.get('SHOULD_DROP_SHIP').strip() if result.get('SHOULD_DROP_SHIP') else None),
                drop_ship_instructions_id=result.get('SI').strip() if result.get('SI') else None
            )

    def fetch_order_line_by_id(self, order_line_id: str) -> ERPOrderLine:
        """
        Retrieve an ERPOrderLine object based on the line id
        :param order_line_id: order line identifier
        :type order_line_id: str
        :return: the order line object
        :rtype: ERPOrderLine
        """
        statement = """SELECT c.CUSTOMER, c.CUS_LOC AS BUY_LOC, c.EDI_CODE, c.NAME, c.NAME2, c.SORT_NAME, 
                              c.ADDR1, c.ADDR2, c.ADDR3, c.ADDR4, c.ADDR5, c.ADDR6, c.CITY, c.STATE, c.ZIP, 
                              c.COUNTRY, c.EDI_CODE, c.EMAIL, h.SALES_CCN, h.SO, h.CUS_PO, h.OTST_NAME, 
                              h.OTST_NAME2, h.OTST_ADDR1, h.OTST_ADDR2, h.OTST_ADDR3, h.OTST_ADDR4, 
                              h.OTST_ADDR5, h.OTST_ADDR6, h.OTST_CITY, h.OTST_STATE, h.OTST_POST_CD, 
                              h.OTST_CNTRY, s.SO_LINE, s.CARRIER, s.CUS_PO_LINE AS CUS_PO_LINE, s.ITEM, 
                              s.REVISION, s.TOT_BO_QTY, s.TOT_CANC_QTY, s.TOT_COM_QTY, s.TOT_ORD_QTY, 
                              s.TOT_SHIP_QTY, i.ITEM, i.REVISION, i.DESCRIPTION, i.HEIGHT, i.WIDTH, 
                              i.LENGTH, i.WEIGHT AS STD_WEIGHT, i.WEIGHT_UM AS STD_WEIGHT_UM, n.CUS_CONTACT, 
                              n.NAME AS CONTACT_NAME, n.PHONE AS CONTACT_PHONE, d.DROP_SHIP, d.SI, 
                              ic.DROP_SHIP SHOULD_DROP_SHIP
                        FROM so_hdr h
                        INNER JOIN so s ON h.sales_ccn = s.sales_ccn AND h.so = s.so
                        INNER JOIN so_del d on d.SALES_CCN = s.SALES_CCN and d.SO = s.SO and d.SO_LINE = s.SO_LINE
                        INNER JOIN cus_loc c ON h.customer = c.customer AND h.cus_buy_loc = c.cus_loc
                        INNER JOIN cus_buy b ON b.customer = c.customer AND b.cus_buy_loc = c.cus_loc
                        INNER JOIN item i ON i.item = s.item AND i.revision = s.revision
                        LEFT OUTER JOIN item_cus ic on ic.CCN = h.SALES_CCN and ic.CUSTOMER = h.CUSTOMER 
                                                    and ic.ITEM = s.ITEM and ic.REVISION = s.REVISION
                        LEFT OUTER JOIN cus_con n ON n.customer = b.customer AND n.cus_loc = b.cus_buy_loc AND n.cus_contact = b.cus_contact
                        INNER JOIN so_gui g on g.SALES_CCN = s.SALES_CCN and g.SO = s.SO and g.SO_LINE = s.SO_LINE
                        WHERE g.ALPHA_01 || '-' || g.ALPHA_02 = :line_id
                        """
        results = fetch(
            sql=statement,
            parameters={'line_id': order_line_id},
            config_path=Glovia.database_config_path
        )
        if results:
            result = results[0]
            return ERPOrderLine(
                customer=result.get('CUSTOMER'),
                customer_location=result.get('BUY_LOC'),
                edi_code=result.get('EDI_CODE'),
                sales_ccn=result.get('SALES_CCN'),
                sales_order=result.get('SO').strip() if result.get('SO') else None,
                sales_order_line=result.get('SO_LINE').strip() if result.get('SO_LINE') else None,
                purchase_order=result.get('CUS_PO').strip() if result.get('CUS_PO') else None,
                purchase_order_line=result.get('CUS_PO_LINE').strip() if result.get('CUS_PO_LINE') else None,
                item=result.get('ITEM').strip() if result.get('ITEM') else None,
                item_revision=result.get('REVISION').strip() if result.get('REVISION') else None,
                backorder_qty=int(result.get('TOT_BO_QTY')),
                committed_qty=int(result.get('TOT_COM_QTY')),
                cancelled_qty=int(result.get('TOT_CANC_QTY')),
                shipped_qty=int(result.get('TOT_SHIP_QTY')),
                order_qty=int(result.get('TOT_ORD_QTY')),
                one_time_ship_to=Address(
                    address1=result.get('OTST_ADDR1'),
                    address2=result.get('OTST_ADDR2'),
                    city=result.get('OTST_CITY'),
                    state=result.get('OTST_STATE'),
                    postalCode=result.get('OTST_POST_CD'),
                    country=result.get('OTST_CNTRY'),
                    name=result.get('OTST_NAME'),
                    company=result.get('OTST_NAME2'),
                    phone=result.get('CONTACT_PHONE')
                ),
                drop_ship=bool(result.get('DROP_SHIP').strip() if result.get('DROP_SHIP') else None),
                should_drop_ship=bool(result.get('SHOULD_DROP_SHIP').strip() if result.get('SHOULD_DROP_SHIP') else None),
                drop_ship_instructions_id=result.get('SI').strip() if result.get('SI') else None
            )
        else:
            # Added to preserve backward compatibility with manually entered orders.
            fallback_query = """SELECT c.CUSTOMER, c.CUS_LOC AS BUY_LOC, c.EDI_CODE, c.NAME, c.NAME2, c.SORT_NAME, 
                                       c.ADDR1, c.ADDR2, c.ADDR3, c.ADDR4, c.ADDR5, c.ADDR6, c.CITY, c.STATE, c.ZIP, 
                                       c.COUNTRY, c.EDI_CODE, c.EMAIL, h.SALES_CCN, h.SO, h.CUS_PO, h.OTST_NAME, 
                                       h.OTST_NAME2, h.OTST_ADDR1, h.OTST_ADDR2, h.OTST_ADDR3, h.OTST_ADDR4, 
                                       h.OTST_ADDR5, h.OTST_ADDR6, h.OTST_CITY, h.OTST_STATE, h.OTST_POST_CD, 
                                       h.OTST_CNTRY, s.SO_LINE, s.CARRIER, s.CUS_PO_LINE AS CUS_PO_LINE, s.ITEM, 
                                       s.REVISION, s.TOT_BO_QTY, s.TOT_CANC_QTY, s.TOT_COM_QTY, s.TOT_ORD_QTY, 
                                       s.TOT_SHIP_QTY, i.ITEM, i.REVISION, i.DESCRIPTION, i.HEIGHT, i.WIDTH, i.LENGTH, 
                                       i.WEIGHT AS STD_WEIGHT, i.WEIGHT_UM AS STD_WEIGHT_UM, n.CUS_CONTACT, 
                                       n.NAME AS CONTACT_NAME, n.PHONE AS CONTACT_PHONE, d.DROP_SHIP, d.SI, 
                                       ic.DROP_SHIP SHOULD_DROP_SHIP,
                                       h.SALES_CCN || h.CUSTOMER || '-' || trim(h.CUS_PO) || '-' || trim(s.CUS_PO_LINE) ORDER_ID
                                FROM so_hdr h
                                  INNER JOIN so s ON h.sales_ccn = s.sales_ccn AND h.so = s.so
                                  INNER JOIN so_del d on d.SALES_CCN = s.SALES_CCN and d.SO = s.SO and d.SO_LINE = s.SO_LINE
                                  INNER JOIN cus_loc c ON h.customer = c.customer AND h.cus_buy_loc = c.cus_loc
                                  INNER JOIN cus_buy b ON b.customer = c.customer AND b.cus_buy_loc = c.cus_loc
                                  INNER JOIN item i ON i.item = s.item AND i.revision = s.revision
                                  LEFT OUTER JOIN item_cus ic on ic.CCN = h.SALES_CCN and ic.CUSTOMER = h.CUSTOMER 
                                                      and ic.ITEM = s.ITEM and ic.REVISION = s.REVISION
                                  LEFT OUTER JOIN cus_con n ON n.customer = b.customer AND n.cus_loc = b.cus_buy_loc AND n.cus_contact = b.cus_contact
                                WHERE
                                  h.SALES_CCN || h.CUSTOMER || '-' || trim(h.CUS_PO) || '-' || trim(s.CUS_PO_LINE)=:line_id"""
            results = fetch(
                sql=fallback_query,
                parameters={'line_id': order_line_id},
                config_path=Glovia.database_config_path
            )
            if results:
                result = results[0]
                return ERPOrderLine(
                    customer=result.get('CUSTOMER'),
                    customer_location=result.get('BUY_LOC'),
                    edi_code=result.get('EDI_CODE'),
                    sales_ccn=result.get('SALES_CCN'),
                    sales_order=result.get('SO').strip() if result.get('SO') else None,
                    sales_order_line=result.get('SO_LINE').strip() if result.get('SO_LINE') else None,
                    purchase_order=result.get('CUS_PO').strip() if result.get('CUS_PO') else None,
                    purchase_order_line=result.get('CUS_PO_LINE').strip() if result.get('CUS_PO_LINE') else None,
                    item=result.get('ITEM').strip() if result.get('ITEM') else None,
                    item_revision=result.get('REVISION').strip() if result.get('REVISION') else None,
                    backorder_qty=int(result.get('TOT_BO_QTY')),
                    committed_qty=int(result.get('TOT_COM_QTY')),
                    cancelled_qty=int(result.get('TOT_CANC_QTY')),
                    shipped_qty=int(result.get('TOT_SHIP_QTY')),
                    order_qty=int(result.get('TOT_ORD_QTY')),
                    one_time_ship_to=Address(
                        address1=result.get('OTST_ADDR1'),
                        address2=result.get('OTST_ADDR2'),
                        city=result.get('OTST_CITY'),
                        state=result.get('OTST_STATE'),
                        postalCode=result.get('OTST_POST_CD'),
                        country=result.get('OTST_CNTRY'),
                        name=result.get('OTST_NAME'),
                        company=result.get('OTST_NAME2'),
                        phone=result.get('CONTACT_PHONE')
                    ),
                    drop_ship=bool(result.get('DROP_SHIP').strip() if result.get('DROP_SHIP') else None),
                    should_drop_ship=bool(result.get('SHOULD_DROP_SHIP').strip() if result.get('SHOULD_DROP_SHIP') else None),
                    drop_ship_instructions_id=result.get('SI').strip() if result.get('SI') else None
                )
            else:
                raise OrderLineNotFoundError(f"Order line {order_line_id} not found in Glovia.")

    def fetch_order_by_id(self, order_id: str) -> ERPOrder:
        """
        Retrieve an ERPOrderLine object from the ERP system
        :param order_id: the order identifier
        :type order_id: str
        :return: an order header object
        :rtype: ERPOrder
        """
        statement = """select g.SALES_CCN, g.SO, h.ENTRY_DATE, h.ORDER_DATE, h.CUSTOMER, 
                              h.CUS_BUY_LOC, h.CUS_PO, h.EDI_SELLER as EDI_CODE, h.REQD_DATE, 
                              h.PROMISE_DATE
                       from SOH_GUI g
                       inner join SO_HDR h on h.SALES_CCN = g.SALES_CCN and h.SO = g.SO
                       where g.ALPHA_01=:order_id order by g.SO DESC
                       """

        results = fetch(
            sql=statement,
            parameters={'order_id': order_id},
            config_path=Glovia.database_config_path
        )
        if results:
            # Take the first one, that based on the sort, is the latest.
            result = results[0]
            return ERPOrder(
                customer=result.get('CUSTOMER'),
                customer_location=result.get('CUS_BUY_LOC'),
                edi_code=result.get('EDI_CODE'),
                sales_ccn=result.get('SALES_CCN'),
                sales_order=result.get('SO').strip() if result.get('SO') else None,
                entry_date=result.get('ENTRY_DATE'),
                order_date=result.get('ORDER_DATE'),
                purchase_order=result.get('CUS_PO').strip() if result.get('CUS_PO') else None,
                required_date=result.get('REQD_DATE'),
                promise_date=result.get('PROMISE_DATE')
            )
        else:
            raise OrderNotFoundError("Unable to find Glovia order")

    def has_drop_shipments(self, order_id: str) -> bool:
        """
        determines whether any of the lines on the order are drop shipments
        :param order_id: the order identifier
        :type order_id: str
        :return: whether the order contains any drop shipments
        :rtype: bool
        """
        statement = """select coalesce(sum(case when ic.DROP_SHIP = 'Y' then 1 else 0 end), 0) as DROP_SHIP_COUNT
                       from SO_GUI g
                       inner join SO s on s.SALES_CCN = g.SALES_CCN and s.SO = g.SO and s.SO_LINE = g.SO_LINE
                       inner join SO_HDR h on h.SALES_CCN = s.SALES_CCN and h.SO = s.SO
                       inner join ITEM_CUS ic on s.CCN = ic.CCN and s.ITEM = ic.ITEM and s.REVISION = ic.REVISION 
                                                 and h.CUSTOMER = ic.CUSTOMER and h.CUS_BUY_LOC = ic.CUS_BUY_LOC
                       where g.ALPHA_01=:order_id
                    """

        results = fetch(
            sql=statement,
            parameters={'order_id': order_id},
            config_path=Glovia.database_config_path
        )
        if results:
            return results[0]['DROP_SHIP_COUNT'] > 0
        else:
            raise OrderNotFoundError("Unable to find Glovia order")

    def cancellable(self, order_line_id: str, quantity: int = -1) -> bool:
        """
        determines if the particular line can be cancelled
        :param quantity: the quantity to cancel on the order line (-1 indicates all)
        :type quantity: int
        :param order_line_id: the order line identifier
        :type order_line_id: str
        :return: whether the order contains any drop shipments
        :rtype: bool
        :raise: LineCancelError
        """

        statement = """select s.TOT_ORD_QTY, s.TOT_SHIP_QTY, s.TOT_COM_QTY, s.TOT_CANC_QTY, 
                              (s.TOT_ORD_QTY - s.TOT_CANC_QTY - s.TOT_SHIP_QTY) as TOT_REMAINING_QTY, 
                              s.CANC_DATE, d.DROP_SHIP, d.SI, b.AR_DOC, d.SALES_CCN, d.SO, d.SO_LINE
                       from SO_GUI g
                       inner join SO s on s.SALES_CCN = g.SALES_CCN and s.SO = g.SO and s.SO_LINE = g.SO_LINE
                       inner join SO_HDR h on h.SALES_CCN = s.SALES_CCN and h.SO = s.SO
                       inner join SO_DEL d on d.SALES_CCN = s.SALES_CCN and d.SO = s.SO and d.SO_LINE = s.SO_LINE
                       left outer join BILL_SO b on b.SALES_CCN = d.SALES_CCN and b.DOCUMENT = d.SO and b.DOCUMENT_LINE = d.SO_LINE and b.SO_DELIVERY = d.SO_DELIVERY 
                       where g.ALPHA_01 || '-' || g.ALPHA_02 = :order_line_id
                    """

        results = fetch(
            sql=statement,
            parameters={'order_line_id': order_line_id},
            config_path=Glovia.database_config_path
        )
        if results:
            for result in results:
                if quantity == -1:
                    cancel_qty = result['TOT_ORD_QTY']
                else:
                    cancel_qty = quantity
                remaining_qty = result['TOT_REMAINING_QTY']
                if 'AR_DOC' in result and result['AR_DOC'] and result['AR_DOC'].strip():
                    raise LineCancelError(
                        line_id=order_line_id,
                        message=f"Unable to cancel line {order_line_id} because the line has already been billed. "
                                f"This may mean that you will need to issue a credit."
                    )
                if remaining_qty <= 0:
                    raise LineCancelError(line_id=order_line_id, message=f"Unable to cancel line {order_line_id} "
                                                                         f"because the line is already fulfilled or "
                                                                         f"cancelled.")
                if cancel_qty > remaining_qty:
                    raise LineCancelError(line_id=order_line_id,
                                          message=f"Unable to cancel line {order_line_id} because the quantity "
                                                  f"requested for cancellation exceeds the quantity remaining on "
                                                  f"the order line. This line will likely need to have shipments, "
                                                  f"packlists, and commitments reversed and then be "
                                                  f"manually cancelled.")
                has_assigned_shipping_instruction = bool(result['SI'].strip())
                if result['DROP_SHIP'] == 'Y' and has_assigned_shipping_instruction:
                    raise LineCancelError(line_id=order_line_id,
                                          message=f"Unable to cancel line {order_line_id} because it is a drop "
                                                  f"shipment for which shipping instructions already exist. This line "
                                                  f"will need to be manually cancelled. The drop ship vendor may need "
                                                  f"to be contacted.")
            return True
        else:
            raise OrderNotFoundError("Unable to find order line")

    def fetch_order_lines_by_id(self, order_id: str) -> ['ERPOrderLine']:
        """
        Retrieve ERPOrderLine objects from the ERP system
        :param order_id: the order identifier
        :type order_id: str
        :return: a list of ERPOrderLine objects
        :rtype: [ERPOrderLine]
        """
        statement = """SELECT c.CUSTOMER, c.CUS_LOC AS BUY_LOC, c.EDI_CODE, c.NAME, c.NAME2, c.SORT_NAME, 
                                      c.ADDR1, c.ADDR2, c.ADDR3, c.ADDR4, c.ADDR5, c.ADDR6, c.CITY, c.STATE, c.ZIP, 
                                      c.COUNTRY, c.EDI_CODE, c.EMAIL, h.SALES_CCN, h.SO, h.CUS_PO, h.OTST_NAME, 
                                      h.OTST_NAME2, h.OTST_ADDR1, h.OTST_ADDR2, h.OTST_ADDR3, h.OTST_ADDR4, 
                                      h.OTST_ADDR5, h.OTST_ADDR6, h.OTST_CITY, h.OTST_STATE, h.OTST_POST_CD, 
                                      h.OTST_CNTRY, s.SO_LINE, s.CARRIER, s.CUS_PO_LINE AS CUS_PO_LINE, s.ITEM, 
                                      s.REVISION, s.TOT_BO_QTY, s.TOT_CANC_QTY, s.TOT_COM_QTY, s.TOT_ORD_QTY, 
                                      s.TOT_SHIP_QTY, i.ITEM, i.REVISION, i.DESCRIPTION, i.HEIGHT, i.WIDTH, 
                                      i.LENGTH, i.WEIGHT AS STD_WEIGHT, i.WEIGHT_UM AS STD_WEIGHT_UM, n.CUS_CONTACT, 
                                      n.NAME AS CONTACT_NAME, n.PHONE AS CONTACT_PHONE, d.DROP_SHIP, d.SI, 
                                      ic.DROP_SHIP SHOULD_DROP_SHIP
                                FROM so_hdr h
                                INNER JOIN so s ON h.sales_ccn = s.sales_ccn AND h.so = s.so
                                INNER JOIN so_del d on d.SALES_CCN = s.SALES_CCN and d.SO = s.SO and d.SO_LINE = s.SO_LINE
                                LEFT OUTER JOIN item_cus ic on ic.CCN = h.SALES_CCN and ic.CUSTOMER = h.CUSTOMER 
                                                                and ic.ITEM = s.ITEM and ic.REVISION = s.REVISION
                                INNER JOIN cus_loc c ON h.customer = c.customer AND h.cus_buy_loc = c.cus_loc
                                INNER JOIN cus_buy b ON b.customer = c.customer AND b.cus_buy_loc = c.cus_loc
                                INNER JOIN item i ON i.item = s.item AND i.revision = s.revision
                                LEFT OUTER JOIN cus_con n ON n.customer = b.customer AND n.cus_loc = b.cus_buy_loc AND n.cus_contact = b.cus_contact
                                INNER JOIN soh_gui g on g.SALES_CCN = h.SALES_CCN and g.SO = h.SO
                                WHERE
                                  g.ALPHA_01=:order_id"""
        results = fetch(
            sql=statement,
            parameters={'order_id': order_id},
            config_path=Glovia.database_config_path
        )
        if results:
            lines = []
            for result in results:
                lines.append(
                    ERPOrderLine(
                        customer=result.get('CUSTOMER'),
                        customer_location=result.get('BUY_LOC'),
                        edi_code=result.get('EDI_CODE'),
                        sales_ccn=result.get('SALES_CCN'),
                        sales_order=result.get('SO').strip() if result.get('SO') else None,
                        sales_order_line=result.get('SO_LINE').strip() if result.get('SO_LINE') else None,
                        purchase_order=result.get('CUS_PO').strip() if result.get('CUS_PO') else None,
                        purchase_order_line=result.get('CUS_PO_LINE').strip() if result.get('CUS_PO_LINE') else None,
                        item=result.get('ITEM').strip() if result.get('ITEM') else None,
                        item_revision=result.get('REVISION').strip() if result.get('REVISION') else None,
                        backorder_qty=int(result.get('TOT_BO_QTY')),
                        committed_qty=int(result.get('TOT_COM_QTY')),
                        cancelled_qty=int(result.get('TOT_CANC_QTY')),
                        shipped_qty=int(result.get('TOT_SHIP_QTY')),
                        order_qty=int(result.get('TOT_ORD_QTY')),
                        one_time_ship_to=Address(
                            address1=result.get('OTST_ADDR1'),
                            address2=result.get('OTST_ADDR2'),
                            city=result.get('OTST_CITY'),
                            state=result.get('OTST_STATE'),
                            postalCode=result.get('OTST_POST_CD'),
                            country=result.get('OTST_CNTRY'),
                            name=result.get('OTST_NAME'),
                            company=result.get('OTST_NAME2'),
                            phone=result.get('CONTACT_PHONE')
                        ),
                        drop_ship=bool(result.get('DROP_SHIP').strip() if result.get('DROP_SHIP') else None),
                        should_drop_ship=bool(result.get('SHOULD_DROP_SHIP').strip() if result.get('SHOULD_DROP_SHIP') else None),
                        drop_ship_instructions_id=result.get('SI').strip() if result.get('SI') else None
                    )
                )
            return lines

    def fetch_customer(self, customer_edi_id: str) -> GloviaCustomer:
        """
        Retrieve the erp system customer record
        :param customer_edi_id: the unique edi id for the customer (buy location)
        :type customer_edi_id: str
        :return: the customer record
        :rtype: ERPCustomer
        """
        return GloviaCustomer.fetch(customer_edi_id)

    def reject(self, order_id: str):
        logger.info("Executing Glovia reject order flow action to remove records from adapter tables.")
        from .order_flow import GloviaOrderFlowRejectAction
        order = Orders.for_(order_id)
        action = GloviaOrderFlowRejectAction(b2b_order=order)
        action.do()

    @xray_recorder.capture()
    def add(self, order_id: str):
        from .order_flow import GloviaOrderFlowAction
        order = Orders.for_(order_id)
        logger.info("Executing Glovia order flow action.")
        action = GloviaOrderFlowAction(b2b_order=order)
        action.do()

    def accept(self, order_id: str):
        from .order_flow import GloviaOrderPostConvertUpdateAction
        order = Orders.for_(order_id)
        logger.info("Executing Glovia post order convert modify")
        action = GloviaOrderPostConvertUpdateAction(b2b_order=order)
        action.do()

    def cancel(self, order_id: str):
        # is the order just in the adapter tables or has the order been entered into glovia
        order = Orders.for_(order_id)
        if order.status < OrderStatus.ENTERED:
            # only need to remove the order from the adapter tables
            action = GloviaOrderFlowRejectAction(b2b_order=order)
            action.do()
            return
        else:
            try:
                # if the order is in Glovia as a sales order
                # Check to see if the lines can be cancelled
                order_lines = self.fetch_order_lines_by_id(order_id)
                if not order_lines:
                    logger.error(f"Lines not found in Glovia for order in status ENTERED. [{order_id}]")
                    return
                cancel_errors = []
                for line in order_lines:
                    try:
                        self.cancellable(order_line_id=line.order_line_id)
                    except LineCancelError as lce:
                        cancel_errors.append(lce)
                if cancel_errors:
                    # We are choosing to only cancel the whole order if all lines can be cancelled.
                    # We may revisit this and decide to cancel those lines which can be.
                    raise OrderCancelError(
                        order_id=order_id,
                        line_errors=cancel_errors,
                        message=f"Unable to cancel order {order_id}. You will need to "
                                f"manually cancel this order in Glovia."
                    )
                else:
                    # Cancel all of the lines and deliveries
                    for line in order_lines:
                        self.cancel_line(order_id, line.order_line_id)


                self.cancel_additional_charges(order_id)

                # Now modify the cancel reason on the header
                erp_order = self.fetch_order_by_id(order_id)
                _data = {
                    'ccn': erp_order.sales_ccn,
                    'sales_order': erp_order.sales_order.rjust(20),
                    'today': datetime.now()
                }
                so_hdr_stmt = """
                    update SO_HDR
                    set LAST_CANC_DATE = :today,
                        CANC_DATE = :today,
                        REASON = 'CUCN'
                    where 
                        SALES_CCN = :ccn
                        and SO = :sales_order
                """
                execute(sql=so_hdr_stmt, parameters=_data, config_path=Glovia.database_config_path)
                logger.info(f"Cancelled Glovia order {order_id} :: {erp_order.sales_order}")
                return True
            except Exception as e:
                logger.exception(e)
                raise OrderCancelError(
                    order_id=order_id,
                    line_errors=[],
                    message=f"Unable to cancel order {order_id}. You will need to manually cancel this order in Glovia."
                ) from e

    def cancel_line(self, order_id: str, order_line_id: str, quantity: int = -1):
        try:
            if self.cancellable(order_line_id=order_line_id, quantity=quantity):
                order_line = self.fetch_order_line_by_id(order_line_id=order_line_id)
                cancel_qty = quantity if quantity > -1 else order_line.required_qty
                _data = {
                    'ccn': order_line.sales_ccn,
                    'sales_order': order_line.sales_order.rjust(20),
                    'sales_order_line': order_line.sales_order_line.rjust(4, '0'),
                    'cancel_qty': cancel_qty,
                    'today': datetime.now()
                }

                delivery_stmt = """
                    update SO_DEL
                    set CANC_DATE = :today,
                        CANC_QTY = :cancel_qty,
                        REASON = 'CUCN'
                    where 
                        SALES_CCN = :ccn
                        and SO = :sales_order
                        and SO_LINE = :sales_order_line
                """
                execute(sql=delivery_stmt, parameters=_data, config_path=Glovia.database_config_path)
                so_stmt = """
                    update SO
                    set CANC_DATE = :today,
                        TOT_CANC_QTY = :cancel_qty,
                        REASON = 'CUCN'
                    where 
                        SALES_CCN = :ccn
                        and SO = :sales_order
                        and SO_LINE = :sales_order_line
                """
                execute(sql=so_stmt, parameters=_data, config_path=Glovia.database_config_path)
                logger.info(f"Canceled Glovia order line {order_line_id} :: {order_line.sales_order}-{order_line.sales_order_line}")
                return True
        except LineCancelError as lce:
            logger.warning(f"Unable to cancel line {order_line_id}", lce)
            raise lce
        except Exception as e:
            logger.exception(e)
            raise ChangeFailedError(f"Unable to cancel order line {order_line_id}.") from e

    def cancel_additional_charges(self, order_id: str):
        try:
            order = Orders.for_(order_id)
            erp_order = self.fetch_order_by_id(order_id)
            quantity = 1
            _data = {
                'ccn': erp_order.sales_ccn,
                'sales_order': erp_order.sales_order.rjust(20),
                'today': datetime.now(),
                'quantity': quantity,
            }
            so_exp_stmt = """
                update SO_EXP
                set CANC_DATE = :today,
                    CANC_QTY = :quantity,
                    REASON = 'CUCN'
                where 
                    SALES_CCN = :ccn
                    and SO = :sales_order
            """
            execute(sql=so_exp_stmt, parameters=_data, config_path=Glovia.database_config_path)
            logger.info(f"Cancelled Additional Charge Lines for Glovia order {order_id} :: {erp_order.sales_order}")
            return True
        except Exception as e:
            logger.exception(e)
            raise ChangeFailedError(f"Unable to cancel order additional charges {order_id}.") from e

    def find_tax_location_code(self, customer_edi_id: str, ship_to: Address) -> str:
        """
        Find the correct tax location code to use for updating the erp
        :param customer_edi_id:
        :type customer_edi_id:
        :param ship_to:
        :type ship_to:
        :return:
        :rtype:
        """
        return GloviaTaxCode.for_(customer_edi_id=customer_edi_id, ship_to=ship_to)

    def is_valid_item_for_customer(self, customer_edi_id: str, customer_item_name: str) -> bool:
        """
        Determine if the item is a valid item for the customer
        :param customer_edi_id: the customer number
        :type customer_edi_id: str
        :param customer_item_name: the customer defined item name
        :type customer_item_name: str
        :return: boolean represented if the item is valid
        :rtype: bool
        """
        _customer = self.fetch_customer(customer_edi_id)
        from lib_b2b.profile import Profile
        from lib_b2b.config import IntegrationType
        profile = Profile.profile_for(customer=customer_edi_id)
        requires_pricing = True if IntegrationType(profile.integration_type) is IntegrationType.STANDARD else False
        return _customer.is_valid(customer_item_name=customer_item_name, requires_pricing=requires_pricing)

    def get_customer_price(self, customer_edi_id: str, customer_item_name) -> Decimal:
        """
        Retrieve the customer specific item pricing
        :param customer_edi_id: the customer number
        :type customer_edi_id: str
        :param customer_item_name: the customer defined item name
        :type customer_item_name: str
        :return: the unit price in dollars
        :rtype: decimal.Decimal
        """
        _customer = self.fetch_customer(customer_edi_id)
        return _customer.get_price(customer_item_name)

    def get_customer_item(self, customer_edi_id: str,
                          customer_item_name: str = None, customer_item_revision: str = ' ',
                          item_name: str = None, revision: str = ' ') -> ERPItem:
        """
        Retrieve customer specific item information
        :param revision: the revision of the item
        :type revision: str
        :param item_name: the ERP item name
        :type item_name: str
        :param customer_edi_id: the customer number
        :type customer_edi_id: str
        :param customer_item_name: the customer defined item name
        :type customer_item_name: str
        :param customer_item_revision: the revision of the item
        :type customer_item_revision: str
        :return: the item information
        :rtype: ERPItem
        """
        if not customer_item_name or item_name:
            raise ValueError('customer_item_name or item_name are required parameters')
        _customer = self.fetch_customer(customer_edi_id)
        return _customer.get_item(customer_item_name=customer_item_name,
                                  customer_item_revision=customer_item_revision,
                                  item_name=item_name, revision=revision)

    def get_customer_items(self, customer_edi_id: str) -> ['ERPItem']:
        """
        Retrieve list of all defined items for the customer
        :param customer_edi_id: the customer number
        :type customer_edi_id: str
        :return: list of the item informationm
        :rtype: [ERPItem]
        """
        _customer = self.fetch_customer(customer_edi_id)
        return _customer.get_items()

    def get_customer_inventory(self, customer_edi_id: str) -> [('ERPItem', int)]:
        """
        Retrieve list of all defined items for the customer
        :param customer_edi_id: the customer number
        :type customer_edi_id: str
        :return: list of the item informationm
        :rtype: [ERPItem]
        """
        _customer = self.fetch_customer(customer_edi_id)
        return _customer.get_inventory()

    def get_inventory(self, ccn: str, locations: [str] = None) -> [('ERPItem', int)]:
        """
        Retrieve list of all defined items edi customers along with their on hand qty
        :param location: optional location parameter
        :type location: str
        :param ccn: ccn limitation
        :type ccn: str
        :return: list of the item informationm
        :rtype: [('ERPItem', int)]:
        """
        statement = """
                            select
                                   c.CCN, c.CUSTOMER, c.CUS_BUY_LOC, c.CUS_ITEM, c.CUS_REV, c.ITEM, c.REVISION, i.UPC,
                                   ic.AGC, ic.SELL_UM, ic.STOCK_UM, ic.STAX, ic.ETAX, ic.UTAX, ic.STD_COST,
                                   coalesce(icp.AMT, l.AMT, null) UNIT_PRICE, ic.VENDOR, ic.PUR_LOC, ics.DROP_SHIP,
                                   ic.STOCK, i.HEIGHT, i.WIDTH, i.LENGTH,
                                   i.WEIGHT, i.WEIGHT_UM, i.HPL, i.PC,coalesce(sum(d.OH_QTY), 0) OH_QTY
                            from CUS_ITEM c
                                     inner join CUS_BUY b on b.CUSTOMER = c.CUSTOMER and b.CUS_BUY_LOC = c.CUS_BUY_LOC and b.CCN = c.CCN
                                     inner join CUS_LOC cl on cl.CUSTOMER = b.CUSTOMER and cl.CUS_LOC = b.CUS_BUY_LOC
                                     inner join ITEM_CUS ics on ics.CCN = c.CCN and ics.ITEM = c.ITEM and ics.REVISION = c.REVISION and
                                                                ics.CUSTOMER = c.CUSTOMER and ics.CUS_BUY_LOC = c.CUS_BUY_LOC
                                     inner join ITEM i on c.ITEM = i.ITEM and c.REVISION = i.REVISION
                                     inner join ITEM_CCN ic on ic.CCN = c.CCN and ic.ITEM = c.ITEM and ic.REVISION = c.REVISION
                                     left outer join ICUS_PRC icp
                                                     on icp.CCN = c.CCN and icp.CUSTOMER = c.CUSTOMER and icp.CUS_BUY_LOC = c.CUS_BUY_LOC and
                                                        icp.ITEM = c.ITEM and icp.REVISION = c.REVISION and
                                                        sysdate between icp.ICUS_PRC_EFF_BEG_DATE and icp.EFF_END
                                     left outer join LIST_PRC l on l.CCN = c.CCN and l.ITEM = c.ITEM and l.REVISION = c.REVISION and
                                                                   sysdate between l.LIST_PRC_EFFBEG_DATE and l.EFFEND
                                     left outer join ITEM_DET d on d.ITEM = ic.ITEM and d.REVISION = ic.REVISION and d.CCN =ic.CCN
                            """
        location_str = None
        if locations:
            location_str = ", ".join("'{0}'".format(location) for location in locations)
            statement += f"""
                    where c.CCN = :ccn and d.LOCATION in ({location_str})
                    group by c.CCN, c.CUSTOMER, c.CUS_BUY_LOC, c.CUS_ITEM, c.CUS_REV, c.ITEM, c.REVISION, i.UPC,
                           ic.AGC, ic.SELL_UM, ic.STOCK_UM, ic.STAX, ic.ETAX, ic.UTAX, ic.STD_COST,
                           coalesce(icp.AMT, l.AMT, null), ic.VENDOR, ic.PUR_LOC, ics.DROP_SHIP,
                           ic.STOCK, i.HEIGHT, i.WIDTH, i.LENGTH,
                           i.WEIGHT, i.WEIGHT_UM, i.HPL, i.PC"""
            results = fetch(
                sql=statement,
                parameters={'ccn': ccn},
                config_path='/erp/tdb'
            )
        else:
            statement += """
                    where c.CCN = :ccn
                    group by c.CCN, c.CUSTOMER, c.CUS_BUY_LOC, c.CUS_ITEM, c.CUS_REV, c.ITEM, c.REVISION, i.UPC,
                           ic.AGC, ic.SELL_UM, ic.STOCK_UM, ic.STAX, ic.ETAX, ic.UTAX, ic.STD_COST,
                           coalesce(icp.AMT, l.AMT, null), ic.VENDOR, ic.PUR_LOC, ics.DROP_SHIP,
                           ic.STOCK, i.HEIGHT, i.WIDTH, i.LENGTH,
                           i.WEIGHT, i.WEIGHT_UM, i.HPL, i.PC"""
            results = fetch(
                sql=statement,
                parameters={'ccn': ccn},
                config_path='/erp/tdb'
            )
        if results:
            items = []
            for result in results:
                items.append(
                    (ERPItem(
                        customer=result.get('CUSTOMER'),
                        customer_item_name=result.get('CUS_ITEM'),
                        customer_item_revision=result.get('CUS_REV'),
                        item=result.get('ITEM'),
                        item_revision=result.get('REVISION'),
                        upc=result.get('UPC'),
                        accounting_code=result.get('AGC'),
                        selling_unit_of_measure=result.get('SELL_UM'),
                        stocking_unit_of_measure=result.get('STOCK_UM'),
                        unit_price=result.get('UNIT_PRICE'),
                        sales_tax=bool(result.get('STAX').strip() if result.get('STAX') else None),
                        excise_tax=bool(result.get('ETAX').strip() if result.get('ETAX') else None),
                        use_tax=bool(result.get('UTAX').strip() if result.get('UTAX') else None),
                        standard_cost=result.get('STD_COST'),
                        stock_item=bool(result.get('STOCK').strip() if result.get('STOCK') else None),
                        primary_vender=result.get('VENDOR'),
                        purchase_location=result.get('PUR_LOC'),
                        drop_ship_item=bool(result.get('DROP_SHIP').strip() if result.get('DROP_SHIP') else None),
                        height=result.get('HEIGHT'),
                        length=result.get('LENGTH'),
                        width=result.get('WIDTH'),
                        weight=result.get('WEIGHT'),
                        weight_unit_of_measure=result.get('WEIGHT_UM'),
                        product_line=result.get('HPL'),
                        product_code=result.get('PC')
                    ), result.get('OH_QTY', 0))
                )
            return items
        else:
            raise NotFoundError(f"Unable to find customer items for ccn and location [{ccn}, {location_str}] in Glovia.")

    def find_tax_rates(self, customer_edi_id: str, ship_to: Address) -> ['ERPTaxRate']:
        """
        Find the correct tax location code to use for updating the erp
        :param customer_edi_id:
        :type customer_edi_id:
        :param ship_to:
        :type ship_to:
        :return: list of tax rates that apply of the address, None is the customer is not taxable
        :rtype: ['ERPTaxRate']
        """
        return GloviaTaxCode.tax_rates_for(customer_edi_id=customer_edi_id, ship_to=ship_to)