from decimal import Decimal

from lib_b2b.address import Address
from lib_b2b.erp import ERPCustomer, ERPItem
from lib_b2b.errors import CustomerNotFoundError, NotFoundError, InvalidCustomerItemError, ItemPricingNotFound
from py_aws_oracle_util import fetch
from os import environ
import logging
from aws_xray_sdk.core import xray_recorder

logger = logging.getLogger(__name__)


class GloviaCustomer(ERPCustomer):
    @staticmethod
    @xray_recorder.capture()
    def fetch(customer_edi_id):
        statement = """select b.CCN, b.CUSTOMER, l.CUS_LOC, l.NAME, l.NAME2, ADDR1, ADDR2, CITY, COUNTRY, NAME, ZIP, 
                              STATE, EDI_CODE, ACK_CODE, BRANCH, b.CUS_AR_LOC, b.CUS_SHIP_LOC, FREIGHT_PAY, PRICE_CLASS,
                              SALES_REGION, SALES_TERR, SALES_TYPE, TERMS_CODE, b.AGC, b.LANGUAGE, DEL_TERM, SALES_REP, 
                              CREDIT_TERMS, b.CUS_CONTACT, s.MAS_LOC, s.CARRIER, b.CURRENCY, s.VAT_VTAX, a.VAT_EXEMPT, 
                              a.VAT_EXEMPT_REASON, a.VAT_REG, a.AR_CCN, a.CREDIT_HOLD, s.ETAX, s.UTAX, s.STAX, s.MAS_LOC
                        from CUS_LOC l
                        inner join CUS_BUY b on b.CUSTOMER = l.CUSTOMER and b.CUS_BUY_LOC = l.CUS_LOC
                        inner join AR_LOC a on a.AR_CCN = b.AR_CCN and a.CUSTOMER = b.CUSTOMER and a.CUS_AR_LOC = b.CUS_AR_LOC
                        inner join CUS_SHIP s on s.CCN = b.CCN and s.CUSTOMER = b.CUSTOMER and s.CUS_SHIP_LOC = b.CUS_SHIP_LOC
                        where l.EDI_CODE=:edi_code"""
        results = fetch(
            sql=statement,
            parameters={'edi_code': customer_edi_id},
            config_path="/erp/tdb"
        )
        if results:
            result = results[0]
            return GloviaCustomer(
                ccn=result.get('CCN'),
                customer=result.get('CUSTOMER'),
                buy_location=result.get('CUS_LOC'),
                address=Address(
                    address1=result.get('ADDR1'),
                    address2=result.get('ADDR2'),
                    city=result.get('CITY'),
                    state=result.get('STATE'),
                    postalCode=result.get('ZIP'),
                    country=result.get('COUNTRY'),
                    name=result.get('NAME'),
                    company=result.get('NAME2'),
                ),
                edi_code=result.get('EDI_CODE'),
                ack_code=result.get('ACK_CODE'),
                branch=result.get('BRANCH'),
                ar_location=result.get('CUS_AR_LOC'),
                ship_location=result.get('CUS_SHIP_LOC'),
                freight_pay=result.get('FREIGHT_PAY'),
                price_class=result.get('PRICE_CLASS'),
                sales_region=result.get('SALES_REGION'),
                sales_territory=result.get('SALES_TERR'),
                sales_type=result.get('SALES_TYPE'),
                terms_code=result.get('TERMS_CODE'),
                account_code=result.get('AGC'),
                language=result.get('LANGUAGE'),
                delivery_terms=result.get('DEL_TERMS'),
                sales_rep=result.get('SALES_REP'),
                credit_terms=result.get('CREDIT_TERMS'),
                credit_hold=result.get('CREDIT_HOLD'),
                sales_tax=bool(result.get('STAX').strip() if result.get('STAX') else None),
                use_tax=bool(result.get('UTAX').strip() if result.get('UTAX') else None),
                excise_tax=bool(result.get('ETAX').strip() if result.get('ETAX') else None),
                carrier=result.get('CARRIER'),
                currency=result.get('CURRENCY'),
                master_location=result.get('MAS_LOC')
            )
        else:
            raise CustomerNotFoundError(f"Unable to find customer {customer_edi_id} in Glovia.")

    def __init__(self, ccn: str, customer: str, buy_location: str,
                 address: Address, edi_code: str, ack_code: str, branch: str,
                 ar_location: str, ship_location: str, freight_pay: str, price_class: str,
                 sales_region: str, sales_territory: str, sales_type: str, terms_code: str,
                 account_code: str, language: str, delivery_terms: str, sales_rep: str,
                 credit_terms: str, credit_hold: str, sales_tax: bool, use_tax: bool,
                 excise_tax: bool, carrier: str, currency: str, master_location: str):
        super().__init__(ccn, customer, buy_location)
        self.currency = currency
        self.carrier = carrier
        self.excise_tax = excise_tax
        self.use_tax = use_tax
        self.sales_tax = sales_tax
        self.credit_hold = credit_hold
        self.credit_terms = credit_terms
        self.sales_rep = sales_rep
        self.delivery_terms = delivery_terms
        self.language = language
        self.account_code = account_code
        self.terms_code = terms_code
        self.sales_type = sales_type
        self.sales_territory = sales_territory
        self.sales_region = sales_region
        self.price_class = price_class
        self.freight_pay = freight_pay
        self.ship_location = ship_location
        self.ar_location = ar_location
        self.branch = branch
        self.ack_code = ack_code
        self.edi_code = edi_code
        self.address = address
        self.master_location = master_location

    def is_valid(self, customer_item_name: str, requires_pricing=False) -> bool:
        statement = """
                    select
                      1 as ITEM_COUNT, coalesce(icp.AMT, l.AMT, null) UNIT_PRICE
                    FROM CUS_ITEM ci
                      INNER JOIN CUS_BUY b on b.CUSTOMER = ci.CUSTOMER and b.CUS_BUY_LOC = ci.CUS_BUY_LOC and b.CCN = ci.CCN
                      INNER JOIN CUS_LOC c on c.CUSTOMER = b.CUSTOMER and c.CUS_LOC = b.CUS_BUY_LOC
                      inner join ITEM i on ci.ITEM = i.ITEM and ci.REVISION = i.REVISION
                      inner join ITEM_CCN ic on ic.CCN = ci.CCN and ic.ITEM = ci.ITEM and ic.REVISION = ci.REVISION
                      left outer join ICUS_PRC icp on icp.CCN = ci.CCN and icp.CUSTOMER = ci.CUSTOMER 
                            and icp.CUS_BUY_LOC = ci.CUS_BUY_LOC 
                            and icp.ITEM = ci.ITEM and icp.REVISION = ci.REVISION 
                            and sysdate between icp.ICUS_PRC_EFF_BEG_DATE and icp.EFF_END
                      left outer join LIST_PRC l on l.CCN = ci.CCN and l.ITEM = ci.ITEM and l.REVISION = ci.REVISION 
                            and sysdate between l.LIST_PRC_EFFBEG_DATE and l.EFFEND
                    WHERE
                      c.EDI_CODE=:edi_code
                      and ci.CUS_ITEM=:item_name
                """
        results = fetch(sql=statement,
                        parameters={'edi_code': self.edi_code, 'item_name': customer_item_name},
                        config_path="/erp/tdb")
        if results:
            if results[0]['ITEM_COUNT'] <= 0:
                raise InvalidCustomerItemError(f"{customer_item_name} is not valid for edi customer {self.edi_code}")
            if not results[0]['UNIT_PRICE'] and requires_pricing:
                raise ItemPricingNotFound(f"Pricing not found for {customer_item_name} for edi customer {self.edi_code}")
            else:
                return True
        raise InvalidCustomerItemError(f"{customer_item_name} is not valid for edi customer {self.edi_code}")

    def get_price(self, customer_item_name: str) -> Decimal:
        statement = """
                    select
                      1 as ITEM_COUNT, coalesce(icp.AMT, l.AMT, null) UNIT_PRICE
                    FROM CUS_ITEM ci
                      INNER JOIN CUS_BUY b on b.CUSTOMER = ci.CUSTOMER and b.CUS_BUY_LOC = ci.CUS_BUY_LOC and b.CCN = ci.CCN
                      INNER JOIN CUS_LOC c on c.CUSTOMER = b.CUSTOMER and c.CUS_LOC = b.CUS_BUY_LOC
                      inner join ITEM i on ci.ITEM = i.ITEM and ci.REVISION = i.REVISION
                      inner join ITEM_CCN ic on ic.CCN = ci.CCN and ic.ITEM = ci.ITEM and ic.REVISION = ci.REVISION
                      left outer join ICUS_PRC icp on icp.CCN = ci.CCN and icp.CUSTOMER = ci.CUSTOMER 
                            and icp.CUS_BUY_LOC = ci.CUS_BUY_LOC 
                            and icp.ITEM = ci.ITEM and icp.REVISION = ci.REVISION 
                            and sysdate between icp.ICUS_PRC_EFF_BEG_DATE and icp.EFF_END
                      left outer join LIST_PRC l on l.CCN = ci.CCN and l.ITEM = ci.ITEM and l.REVISION = ci.REVISION 
                            and sysdate between l.LIST_PRC_EFFBEG_DATE and l.EFFEND
                    WHERE
                      c.EDI_CODE=:edi_code
                      and ci.CUS_ITEM=:item_name
                """
        results = fetch(sql=statement,
                        parameters={'edi_code': self.edi_code, 'item_name': customer_item_name},
                        config_path="/erp/tdb")
        if results:
            if results[0]['ITEM_COUNT'] <= 0:
                raise InvalidCustomerItemError(f"{customer_item_name} is not valid for edi customer {self.edi_code}")
            if not results[0]['UNIT_PRICE']:
                raise ItemPricingNotFound(f"Pricing not found for {customer_item_name} for edi customer {self.edi_code}")
            else:
                return Decimal(str(results[0]['UNIT_PRICE']))
        raise InvalidCustomerItemError(f"{customer_item_name} is not valid for edi customer {self.edi_code}")

    def get_items(self) -> [ERPItem]:
        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, h.DESCRIPTION HPL, p.DESCRIPTION PC  
                    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
                    inner join HPL h on h.HPL = i.HPL
                    inner join PC p on p.PC = i.PC
                    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 
                    where
                      cl.EDI_CODE=:customer_edi_id"""
        results = fetch(
            sql=statement,
            parameters={'customer_edi_id': self.edi_code},
            config_path='/erp/tdb'
        )
        if results:
            items = []
            for result in results:
                items.append(ERPItem(
                    customer=self,
                    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')
                ))
            return items
        else:
            raise NotFoundError(f"Unable to find customer items for edi_code [{self.edi_code} in Glovia.")

    def get_inventory(self) -> [(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, h.DESCRIPTION HPL, p.DESCRIPTION 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 HPL h on h.HPL = i.HPL
                             inner join PC p on p.PC = i.PC
                             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
                    where cl.EDI_CODE=:customer_edi_id
                    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, h.DESCRIPTION, p.DESCRIPTION"""
        results = fetch(
            sql=statement,
            parameters={'customer_edi_id': self.edi_code},
            config_path='/erp/tdb'
        )
        if results:
            items = []
            for result in results:
                items.append(
                    (ERPItem(
                        customer=self,
                        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 edi_code [{self.edi_code} in Glovia.")

    def get_item(self, customer_item_name: str = None,
                 customer_item_revision: str = ' ',
                 item_name: str = None, revision: str = ' ') -> ERPItem:
        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, h.DESCRIPTION HPL, p.DESCRIPTION PC  
                            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
                            left outer join HPL h on h.HPL = i.HPL
                            left outer join PC p on p.PC = i.PC
                            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 
                            where
                                c.CCN=:ccn
                                and c.CUSTOMER=:customer
                                and c.CUS_BUY_LOC=:buy_loc
                                """
        if customer_item_name:
            statement += """
                and c.CUS_ITEM=:item
                and c.CUS_REV=:rev
            """
        else:
            statement += """
                and i.ITEM=:item
                and i.REVISION=:rev
            """

        if customer_item_name:
            _item_name = customer_item_name
            _item_revision = customer_item_revision or ' '
        else:
            _item_name = item_name
            _item_revision = revision or ' '

        results = fetch(
            sql=statement,
            parameters={'ccn': self.ccn,
                        'customer': self.customer,
                        'buy_loc': self.buy_location,
                        'item': _item_name,
                        'rev': _item_revision},
            config_path="/erp/tdb"
        )
        if results:
            result = results[0]
            return ERPItem(
                customer=self,
                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')
            )
        else:
            raise NotFoundError(f"Unable to find customer items [{_item_name}] for edi_code [{self.edi_code}] in Glovia.")