from pprint import pformat

from lib_b2b.erp import ERP
from .errors import NotFoundError, ContainerCreationError
from py_aws_oracle_util import fetch, insert, execute
from datetime import datetime
from os import environ
import logging

logger = logging.getLogger(__name__)

class ContainerAttributes:
    def __init__(self, height, width, length, standard_weight, actual_weight, weight_um):
        self.height = height
        self.width = width
        self.length = length
        self.standard_weight = standard_weight
        self.actual_weight = actual_weight
        self.weight_um = weight_um

    def __repr__(self):
        return "<" + type(self).__name__ + "> " + pformat(vars(self), indent=2, width=1)

    def __str__(self):
        return pformat(vars(self), indent=2, width=1)


class ContainerContent:
    def __init__(self, ccn, so, soline, quantity, lot, item, revision, cus_po, cus_po_line, customer_edi_id, cus_item):
        self.ccn = ccn
        self.so = so
        self.soline = soline
        self.quantity = quantity
        self.lot = lot
        self.item = item
        self.revision = revision
        self.cus_po = cus_po
        self.cus_po_line = cus_po_line
        self.customer_edi_id = customer_edi_id
        self.cus_item = cus_item

    def get_b2b_order_id(self):
        return f"{self.customer_edi_id}-{self.cus_po}".strip()

    def __repr__(self):
        return "<" + type(self).__name__ + "> " + pformat(vars(self), indent=2, width=1)

    def __str__(self):
        return pformat(vars(self), indent=2, width=1)


class Container:
    @staticmethod
    def fetch(ccn, container_id):
        statement = """select cc.CCN,
                               cc.ID,
                               cc.ITEM,
                               cc.LOT,
                               cc.QUANTITY,
                               cc.SO,
                               cc.SO_LINE,
                               c.C_HEIGHT,
                               c.C_LENGTH,
                               c.C_WIDTH,
                               c.C_WEIGHT,
                               c.C_WEIGHT_ACTUAL,
                               l.EDI_CODE,
                               h.CUS_PO,
                               s.CUS_PO_LINE,
                               ci.CUS_ITEM,
                               i.WEIGHT_UM
                        from BMC_CONTAINER_CONTENTS cc
                              inner join BMC_CONTAINERS c on c.CCN = cc.CCN and c.ID = cc.ID
                              inner join SO_HDR h on h.SALES_CCN = cc.CCN and h.SO = cc.SO
                              inner join SO s on s.SALES_CCN = h.SALES_CCN and s.SO = h.SO and s.SO_LINE = cc.SO_LINE
                              inner join CUS_LOC l on l.CUSTOMER = h.CUSTOMER and l.CUS_LOC = h.CUS_BUY_LOC
                              inner join ITEM i on i.ITEM = s.ITEM and i.REVISION = s.REVISION
                              left outer join CUS_ITEM ci on ci.CCN = c.CCN and ci.CUSTOMER=h.CUSTOMER and ci.CUS_BUY_LOC = h.CUS_BUY_LOC and ci.ITEM = s.ITEM and ci.REVISION = s.REVISION
                        where cc.CCN = :ccn
                          and cc.ID = :container_id"""
        results = fetch(
            sql=statement,
            parameters={'ccn': ccn, 'container_id': container_id.strip()},
            config_path="/erp/tdb"
        )

        if results:
            _r = results[0]
            physical_attributes=ContainerAttributes(
                height=_r['C_HEIGHT'],
                width=_r['C_WIDTH'],
                length=_r['C_LENGTH'],
                standard_weight=_r['C_WEIGHT'],
                actual_weight=_r['C_WEIGHT_ACTUAL'],
                weight_um=_r['WEIGHT_UM']
            )
            container = Container(
                ccn=_r['CCN'],
                container_id=_r['ID'],
                customer_edi_id=_r['EDI_CODE'],
                attributes=physical_attributes,
                contents=[]
            )
            for _container_row in results:
                content_line = ContainerContent(
                    ccn=_container_row['CCN'],
                    so=_container_row['SO'].strip(),
                    soline=_container_row['SO_LINE'].strip(),
                    quantity=_container_row['QUANTITY'],
                    lot=_container_row['LOT'].strip(),
                    item=_container_row['ITEM'].strip(),
                    revision=' ',
                    cus_po=_container_row['CUS_PO'].strip(),
                    cus_po_line=_container_row['CUS_PO_LINE'].strip(),
                    customer_edi_id=_container_row['EDI_CODE'].strip(),
                    cus_item=_container_row['CUS_ITEM'].strip()
                )
                container.contents.append(content_line)
            return container
        else:
            raise NotFoundError(f"Unable to find container information for ccn:{ccn}, "
                                f"container_id:{container_id}")

    @staticmethod
    def create_for_order_line(ccn, sales_order, sales_order_line, inventory_id, force = False):
        """
        INSERT INTO GLOVIA710.BMC_CONTAINERS (CCN, ID, LOCATION, TYPE, CREATED, SO, SO_LINE, MAX_CASE_QTY, SHIP_STAT, C_HEIGHT, C_LENGTH, C_WIDTH, C_WEIGHT, C_WEIGHT_ACTUAL) VALUES ('402100', 'D1-539958-2', 'DSHIP', 'P', TO_DATE('2018-12-19 14:34:33', 'YYYY-MM-DD HH24:MI:SS'), '              132490', null, null, null, 45.0000, 19.0000, 19.0000, 92.0000, 91.2000);
        INSERT INTO GLOVIA710.BMC_CONTAINER_CONTENTS (CCN, ID, ITEM, LOT, QUANTITY, INSP_STAT, REVISION, SO, SO_LINE) VALUES ('402100', 'D1-539958-2', 'BIB-FLTB-GRY', ' ', 1.0000, '3', null, '              132490', '0001');
        INSERT INTO GLOVIA710.BMC_CONTAINER_CONTENTS (CCN, ID, ITEM, LOT, QUANTITY, INSP_STAT, REVISION, SO, SO_LINE) VALUES ('402100', 'D1-539958-2', 'BIB-PBO-QU', '               NFM-539958', 1.0000, '3', ' ', '              132490', '0002');

        :return:
        """
        from .profile import Profile
        from .inventory import InventoryItem

        _sales_order = str(sales_order).strip().rjust(20, ' ')
        _sales_order_line = str(sales_order_line).strip().rjust(4, '0')

        exists_sql = """
            select * from BMC_CONTAINERS where CCN=:ccn and ID=:inventory_id
        """
        results = fetch(sql=exists_sql, parameters={'ccn': ccn, 'inventory_id': inventory_id}, config_path="/erp/tdb")
        if results:
            if force:
                delete_sql = ["delete from BMC_CONTAINERS where CCN=:ccn and ID=:inventory_id",
                              "delete from BMC_CONTAINER_CONTENTS where CCN=:ccn and ID=:inventory_id"]
                logger.info(f"Deleting container records for ccn:{ccn}, inventory_id:{inventory_id}")
                for sql in delete_sql:
                    execute(sql=sql, parameters={'ccn': ccn, 'inventory_id': inventory_id}, config_path="/erp/tdb")
            else:
                raise ContainerCreationError(f"Unable to create container record for ccn:{ccn}, inventory_id:{inventory_id}. It already exists.")

        order = ERP.default().fetch_order_line(ccn=ccn,
                                               sales_order=_sales_order,
                                               sales_order_line=_sales_order_line)
        customer_edi_id = order.edi_code
        profile = Profile.profile_for(customer=customer_edi_id)
        inventory_item = InventoryItem.fetch(
            is_serialized=profile.fulfillment_config.is_serialized,
            ccn=ccn,
            inventory_no=inventory_id
        )

        data_dict={
            'CCN': ccn,
            'ID': inventory_id,
            'LOCATION': 'DSHIP',
            'TYPE': 'C',
            'CREATED': datetime.now(),
            'SO': _sales_order,
            'SO_LINE': _sales_order_line,
            'C_HEIGHT': inventory_item.height,
            'C_LENGTH': inventory_item.length,
            'C_WIDTH': inventory_item.width,
            'C_WEIGHT': inventory_item.standard_weight,
            'C_WEIGHT_ACTUAL': inventory_item.actual_weight
        }
        result = insert(schema='glovia710',
                        table='BMC_CONTAINERS',
                        parameters=data_dict,
                        config_path="/erp/tdb")
        logger.info(f"Inserted container record for order: {_sales_order},{_sales_order_line} into glovia710.BMC_CONTAINERS")
        print(data_dict)

        data_dict = {
            'CCN': ccn,
            'ID': inventory_id,
            'ITEM': inventory_item.item,
            'LOT': inventory_item.lot,
            'QUANTITY': 1,
            'INSP_STAT': 3,
            'REVISION': inventory_item.revision,
            'SO': _sales_order,
            'SO_LINE': _sales_order_line
        }
        result = insert(schema='glovia710',
                        table='BMC_CONTAINER_CONTENTS',
                        parameters=data_dict,
                        config_path="/erp/tdb")
        logger.info(f"Inserted container content record for order: {_sales_order},{_sales_order_line} into glovia710.BMC_CONTAINERS")
        print(data_dict)

        return Container.fetch(ccn, inventory_id)

    @staticmethod
    def save_tracking_number(ccn: str, container_id: str, tracking_number: str):
        """
        save the tracking number that was created for this container
        :param ccn: the ccn for the container
        :param container_id: the id of the container
        :param tracking_number: the tracking number for the containers shipment
        :return:
        """
        sql = """
            update BMC_CONTAINERS
            set TRACKING_NUM = :tracking_num
            where
                ccn = :ccn
                and id = :container_id
        """
        execute(sql=sql,
                parameters={'ccn': ccn, 'container_id': container_id, 'tracking_num': tracking_number},
                config_path="/erp/tdb")

    def __init__(self, ccn, container_id, customer_edi_id, attributes: ContainerAttributes, contents: [ContainerContent]):
        self.ccn = ccn
        self.container_id = container_id
        self.customer_edi_id = customer_edi_id
        self.attributes = attributes
        self.contents = contents

    def get_shipment_id(self):
        return self.container_id

    def __repr__(self):
        return "<" + type(self).__name__ + "> " + pformat(vars(self), indent=2, width=1)

    def __str__(self):
        return pformat(vars(self), indent=2, width=1)