from os import environ
from datetime import datetime

from lib_b2b.erp import ERPOrderLine, ERP
from .package import Package
from .order_shipment_status import OrderShipmentStatus
from .carrier import CarrierIntegration
from .errors import OrderShipStatusError, OrderLineNotFoundError
from .inventory import InventoryItem, SerializedInventoryItem
from .fedex_ship_mgr_db import FSMDB
from .profile import Profile
from .fulfillment import Fulfillment
from .fulfillment_status import FulfillmentStatus
from .orders import Orders
from .util import group_by
import logging
from aws_xray_sdk.core import xray_recorder

log_level = logging.getLevelName(environ['LOG_LEVEL']) if 'LOG_LEVEL' in environ else logging.INFO
logging.basicConfig(format='%(asctime)s,%(msecs)d %(levelname)-8s [%(filename)s:%(lineno)d] %(message)s',
                    datefmt='%d-%m-%Y:%H:%M:%S',
                    level=log_level)
logger = logging.getLogger('lib-b2b-fsm')


# noinspection SqlResolve
class FedexShipManager(CarrierIntegration):

    def __init__(self, profile: Profile):
        super().__init__(profile)
        self.table_name = environ['shipment_table'] if 'shipment_table' in environ else 'shipment'
        self.config_path='/ecommerce/fsm'

    @staticmethod
    @xray_recorder.capture()
    def __get_shipment_info_for(fulfillment_list):
        if fulfillment_list:
            fulfillment_ids = [f['id'] for f in fulfillment_list]
            fulfillment_tuple = tuple(fulfillment_ids)
            stmt = f"""select
                                  s.SHIPMENT_ID,
                                  s.FULFILLMENT_ID,
                                  s.CUSTOMER_REF CONTAINER_ID,
                                  s.CUSTOMER_EDI_ID,
                                  s.CUS_PO,
                                  s.CUS_PO_LINE,
                                  s.BILLABLE_WEIGHT,
                                  s.DIMENSIONAL_WEIGHT,
                                  s.PACKAGE_SIZE,
                                  s.SHIP_SEQ,
                                  d.TOTAL_LIST_SURCHARGE,
                                  d.TOTAL_LIST_DISCOUNT,
                                  d.TOTAL_LIST_CHARGE,
                                  d.TOTAL_CUST_SURCHARGE,
                                  d.TOTAL_CUST_DISCOUNT,
                                  d.TOTAL_CUST_CHARGE,
                                  d.TRACKING_NUM,
                                  d.UPDATED_AT as SHIP_DATE,
                                  count(1) as QTY
                                from shipment s
                                  left outer join shipment_data d
                                    on d.SHIPMENT_ID = s.SHIPMENT_ID
                                        and d.VOID IS NULL
                                WHERE
                                  s.SHIPPED = 1
                                  AND s.FULFILLMENT_ID in %(fulfillment_ids)s
                                GROUP BY 
                                  s.SHIPMENT_ID,
                                  s.FULFILLMENT_ID,
                                  s.CUSTOMER_REF,
                                  s.CUSTOMER_EDI_ID,
                                  s.CUS_PO,
                                  s.CUS_PO_LINE,
                                  s.BILLABLE_WEIGHT,
                                  s.DIMENSIONAL_WEIGHT,
                                  s.PACKAGE_SIZE,
                                  s.SHIP_SEQ,
                                  d.TOTAL_LIST_SURCHARGE,
                                  d.TOTAL_LIST_DISCOUNT,
                                  d.TOTAL_LIST_CHARGE,
                                  d.TOTAL_CUST_SURCHARGE,
                                  d.TOTAL_CUST_DISCOUNT,
                                  d.TOTAL_CUST_CHARGE,
                                  d.TRACKING_NUM,
                                  d.UPDATED_AT"""
            results = FSMDB.fetch(sql=stmt, parameters={'fulfillment_ids': fulfillment_tuple}, config_path='/ecommerce/fsm')
            return results
        else:
            return None

    @staticmethod
    @xray_recorder.capture()
    def update_fulfillments():
        pending = Fulfillment.fetch_pending()
        logger.info(f"Found {len(pending)} pending fulfillments.")
        if pending:
            ship_data = FedexShipManager.__get_shipment_info_for(pending)
            if ship_data:
                fulfillment_idx = group_by(lambda x: x['FULFILLMENT_ID'], ship_data)
                for fulfillment_id in fulfillment_idx.keys():
                    # should always have one and only one record for a particular fulfillment id
                    records = fulfillment_idx[fulfillment_id]
                    record = records[0]
                    Fulfillment.update_for_shipment(fulfillment=fulfillment_id,
                                                    status=FulfillmentStatus.NOT_SENT,
                                                    tracking_numbers=[record['TRACKING_NUM']],
                                                    ship_dates=[record['SHIP_DATE'].isoformat()],
                                                    carrier_data=record)
                return list(fulfillment_idx.keys())
        return None

    @xray_recorder.capture()
    def __is_shipped_serial(self, order: ERPOrderLine, inventory_item: InventoryItem):
        stmt = f"""select
                              SHIPMENT_ID,
                              CUSTOMER_REF,
                              CCN,
                              SO,
                              SO_LINE,
                              SHIPPED
                            from {self.table_name}
                            WHERE
                              CCN=%(ccn)s
                              and SO=%(so)s
                              and SO_LINE=%(so_line)s
                              and CUSTOMER_REF=%(inventory_no)s"""
        params = {
            'ccn': order.sales_ccn,
            'so': order.sales_order,
            'so_line': order.sales_order_line,
            'inventory_no': inventory_item.inventory_no
        }
        result = FSMDB.fetch(sql=stmt,
                             parameters=params,
                             config_path=self.config_path,
                             one=True)
        if result is None:
            return False
        else:
            return result['SHIPPED'] == 1

    @xray_recorder.capture()
    def __is_shipped_lotted(self, order: ERPOrderLine):
        stmt = f"""select
                          SHIPMENT_ID,
                          CCN,
                          SO,
                          SO_LINE,
                          SUM(SHIPPED) SHIPPED_COUNT,
                          count(1) RECORD_COUNT
                        from {self.table_name}
                        WHERE
                          CCN=%(ccn)s
                          and trim(SO)=%(so)s
                          and trim(SO_LINE)=%(so_line)s
                        GROUP BY SHIPMENT_ID, CCN, SO, SO_LINE
                        """
        params = {'ccn': order.sales_ccn, 'so': order.sales_order, 'so_line': order.sales_order_line}
        result = FSMDB.fetch(sql=stmt, parameters=params, config_path=self.config_path, one=True)
        if result:
            required_qty = order.required_qty()
            if result['SHIPPED_COUNT'] >= required_qty:
                return True
            return False
        else:
            return False

    @xray_recorder.capture()
    def __is_shipped_seq(self, order: ERPOrderLine, inventory_item: InventoryItem, ship_sequence):
        stmt = f"""select
                          SHIPMENT_ID,
                          CCN,
                          SO,
                          SO_LINE,
                          SHIP_SEQ,
                          SHIPPED
                        from {self.table_name}
                        WHERE
                          CCN=%(ccn)s
                          and SO=%(so)s
                          and SO_LINE=%(so_line)s
                          and CUSTOMER_REF=%(inv_no)s
                          and SHIP_SEQ=%(seq)s
                          and SHIPPED=1
                        """
        params = {
            'ccn': order.sales_ccn.strip(),
            'so': order.sales_order,
            'so_line': order.sales_order_line,
            'inv_no': inventory_item.inventory_no.strip(),
            'seq': ship_sequence
        }
        result = FSMDB.fetch(sql=stmt, parameters=params, config_path=self.config_path)
        if result:
            return True
        else:
            return False

    @xray_recorder.capture()
    def is_shipped(self, order: ERPOrderLine, inventory_item: InventoryItem, ship_sequence=None):
        if ship_sequence:
            return self.__is_shipped_seq(order=order, inventory_item=inventory_item, ship_sequence=ship_sequence)
        else:
            if isinstance(inventory_item, SerializedInventoryItem):
                return self.__is_shipped_serial(order=order, inventory_item=inventory_item)
            else:
                return self.__is_shipped_lotted(order=order)

    @xray_recorder.capture()
    def __record_count_for_so(self, order: ERPOrderLine):
        stmt = f"""select
                          SHIPMENT_ID,
                          CCN,
                          SO,
                          SO_LINE,
                          sum(SHIPPED) SHIPPED_COUNT,
                          count(1) RECORD_COUNT
                        from {self.table_name}
                        WHERE
                          CCN=%(ccn)s
                          and trim(SO)=%(so)s
                          and trim(SO_LINE)=%(so_line)s
                        GROUP BY SHIPMENT_ID, CCN, SO, SO_LINE
                        """
        params = {'ccn': order.sales_ccn, 'so': order.sales_order, 'so_line': order.sales_order_line}
        result = FSMDB.fetch(sql=stmt, parameters=params, config_path=self.config_path, one=True)
        if result:
            return OrderShipmentStatus(order, result['RECORD_COUNT'], result['SHIPPED_COUNT'])
        else:
            return OrderShipmentStatus(order, 0, 0)

    @xray_recorder.capture()
    def __record_count_for_po(self, order: ERPOrderLine):
        stmt = f"""select
                          SHIPMENT_ID,
                          CUSTOMER_EDI_ID,
                          CUS_PO,
                          CUS_PO_LINE,
                          sum(SHIPPED) SHIPPED_COUNT,
                          count(1) RECORD_COUNT
                        from {self.table_name}
                        WHERE
                          CUSTOMER_EDI_ID=%(edi_code)s
                          and trim(CUS_PO)=%(po)s
                          and trim(CUS_PO_LINE)=%(po_line)s
                        GROUP BY SHIPMENT_ID, CUSTOMER_EDI_ID, CUS_PO, CUS_PO_LINE
                        """
        params = {'edi_code': order.edi_code, 'po': order.purchase_order, 'po_line': order.purchase_order_line}
        result = FSMDB.fetch(sql=stmt, parameters=params, config_path=self.config_path, one=True)
        if result:
            return OrderShipmentStatus(order, result['RECORD_COUNT'], result['SHIPPED_COUNT'])
        else:
            return OrderShipmentStatus(order, 0, 0)

    @xray_recorder.capture()
    def get_status(self, order: ERPOrderLine, inventory_item: InventoryItem):
        return self.__record_count_for_so(order)

    @xray_recorder.capture()
    def necessary_records_exist(self, order: ERPOrderLine, inventory_item: InventoryItem):
        record_count_by_so = self.__record_count_for_so(order).request_count
        record_count_by_po = self.__record_count_for_po(order).request_count
        if isinstance(inventory_item, SerializedInventoryItem):
            return record_count_by_po > 0 and record_count_by_so > 0
        else:
            return order.required_qty <= record_count_by_so and order.required_qty <= record_count_by_po

    @xray_recorder.capture()
    def list_shipments(self):
        return FSMDB.fetch(sql=f"SELECT * FROM {self.table_name}", config_path=self.config_path)

    @xray_recorder.capture()
    def get_last_ship_sequence(self, fulfillment):
        stmt = f"""select
                      max(SHIP_SEQ) LAST_SHIP_SEQ
                    from {self.table_name}
                    WHERE
                      FULFILLMENT_ID=%(fulfillment_id)s
                    GROUP BY FULFILLMENT_ID
                    """
        params = {'fulfillment_id': fulfillment['id']}
        result = FSMDB.fetch(sql=stmt, parameters=params, config_path=self.config_path, one=True)
        if result:
            return result['LAST_SHIP_SEQ']
        else:
            return 0

    @xray_recorder.capture()
    def get_shipment(self, shipment_id, ship_sequence):
        stmt = f"""select *
                    from {self.table_name}
                    where SHIPMENT_ID=%(sid)s
                    and SHIP_SEQ=%(ss)s
        """
        params = {'sid': shipment_id, 'ss': ship_sequence}
        result = FSMDB.fetch(sql=stmt, parameters=params, config_path=self.config_path)
        if result:
            return result
        else:
            return None

    @xray_recorder.capture()
    def remove_shipment(self, order: ERPOrderLine, inventory_item: InventoryItem, ship_sequence):
        if self.is_shipped(order=order, inventory_item=inventory_item, ship_sequence=ship_sequence):
            raise OrderShipStatusError(f"Sales order line already shipped, unable to "
                                       f"remove. [{str(order)}][{str(inventory_item)}][{ship_sequence}]")
        else:
            logger.info(f"Deleting shipments [{str(order)}, {str(inventory_item)}] - ship seq: {ship_sequence}")
            stmt = f"delete from {self.table_name} where CCN=%s and SO=%s " \
                   f"and SO_LINE=%s and CUSTOMER_REF=%s and SHIP_SEQ=%s"
            params = {'ccn': order.sales_ccn, 'so': order.sales_order,
                      'so_line': order.sales_order_line, 'inv_no': inventory_item.inventory_no.strip(),
                      'seq': ship_sequence}
            FSMDB.execute(sql=stmt, parameters=params, config_path=self.config_path)

    @xray_recorder.capture()
    def has_available_ship_capacity(self, order: ERPOrderLine, quantity: int):
        """
        Answers the question about whether the quantity plus existing unshipped requests
        will exceed the total ordered
        :param order:   the glovia order object
        :param quantity:    the quantity you want to ship
        :return:    boolean
        """
        # if qty greater than required, throw error
        record_count = self.__record_count_for_so(order)
        return quantity <= (order.order_qty - order.cancelled_qty - record_count.request_count)

    @xray_recorder.capture()
    def __fetch_candidates_for_update(self, order: ERPOrderLine, inventory_item: InventoryItem, quantity: int):
        """
        Provides a list of ship sequences for the given order and inventory_no that are available for modify
        :param order: the glovia order
        :param inventory_item: the inventory identifier object (lot or serial)
        :param quantity: the number of items we would like to find updates for
        :return: a list of shipment sequences that, in combination with the order info, make the shipment records unique
        """
        stmt = f"""select
                      SHIPMENT_ID,
                      CCN,
                      SO,
                      SO_LINE,
                      CUSTOMER_REF,
                      SHIP_SEQ
                    from {self.table_name}
                    WHERE
                      CCN=%s
                      and SO=%s
                      and SO_LINE=%s
                      and CUSTOMER_REF=%s
                      and SHIPPED=0
                    ORDER BY SHIP_SEQ DESC
                    """
        params = {'ccn': order.sales_ccn.strip(), 'so': order.sales_order,
                  'so_line': order.sales_order_line, 'inv_no': inventory_item.inventory_no.strip()}
        result = FSMDB.fetch(sql=stmt, parameters=params, config_path=self.config_path)
        if len(result) <= quantity:
            return result
        else:
            return result[0:quantity]

    @xray_recorder.capture()
    def has_updatable_ship_capacity(self, order: ERPOrderLine, inventory_item: InventoryItem, quantity: int):
        """
        Checks whether there are enough unshipped shipment requests to fulfill the ship request quantity with some number of updates
        :param order: the glovia order
        :param inventory_item: the inventory identifier object (lot or serial)
        :param quantity: the number of items we would like to find updates for
        :return: a list of shipment sequences that, in combination with the order info, make the shipment records unique
        """
        if not self.profile.fulfillment_config.shipment_request_update_allowed:
            return False
        candidates = self.__fetch_candidates_for_update(order=order, inventory_item=inventory_item, quantity=quantity)
        record_count = self.__record_count_for_so(order)
        current_capacity = order.order_qty - order.cancelled_qty - record_count.request_count
        unshipped_capacity = len(candidates)

        # if the are open slots, add them to the count of rows that we could modify and check against requested quantity
        return (current_capacity + unshipped_capacity) >= quantity

    @xray_recorder.capture()
    def create_shipment(self, fulfillment, inventory_no: str, package: Package):
                        # order: GloviaOrder, inventory_item: InventoryItem,
                        # package: Package, edi_order, quantity: int):

        # if qty greater than required, throw error
        # if not self.has_available_ship_capacity(order=order, quantity=quantity):
        #     if self.profile.fulfillment_config.shipment_request_update_allowed:
        #         if self.has_updatable_ship_capacity(order=order, inventory_item=inventory_item, quantity=quantity):
        #             candidates = self.__fetch_candidates_for_update(order=order, inventory_item=inventory_item, quantity=quantity)
        #             for candidate in candidates:
        #                 self.remove_shipment(order=order, inventory_item=inventory_item, ship_sequence=candidate['SHIP_SEQ'])
        #         else:
        #             raise InvalidQuantityError(f"The quantity {quantity} is greater than "
        #                                        f"the order's outstanding unshipped quantity.")
        #     else:
        #         raise InvalidQuantityError(f"The quantity {quantity} is greater than "
        #                                    f"what the the order's outstanding quantity of "
        #                                    f"{order.tot_ord_qty - order.tot_canc_qty - order.tot_ship_qty}")

        sender = self.profile.sender
        data_dicts = []

        # FIX - Customer Service has a tendancy to chop of the last number
        #       of a nine digit zip zode when entering into glovia.
        #       Glovia only allows 9 characters.  So, it will chop
        #       off the last digit.  So, in the interest of correcting the
        #       condition, we will take only the first 5 if it contains a dash.
        _fulfillment = Fulfillment.for_(fulfillment)
        edi_order = Orders.for_(_fulfillment['order_id'])
        for line in _fulfillment['line_items']:
            order_line = ERP.for_(edi_order.customer_edi_id).fetch_order_line_by_id(_fulfillment['order_id'], line['purchase_order_line'])
            if not order_line:
                raise OrderLineNotFoundError(f"Unable to find glovia order line for {line['line_id']}")
            postal_code = order_line.otst_post_cd
            if '-' in order_line.otst_post_cd and order_line.country in ['US', 'USA']:
                postal_code = order_line.otst_post_cd[0:5]

            data_dict = {
                'SHIPMENT_ID': order_line.get_shipment_id(),
                'FULFILLMENT_ID': _fulfillment['id'],
                'CUSTOMER_REF': inventory_no.strip(),
                'CUSTOMER_EDI_ID': order_line.edi_code.strip(),
                'CUS_PO': order_line.cus_po.strip(),
                'CUS_PO_LINE': order_line.cus_po_line.strip(),
                'CCN': order_line.sales_ccn.strip(),
                'SO': order_line.so.strip(),
                'SO_LINE': order_line.so_line.strip(),
                'SHIP_NAME': order_line.otst_name,
                'SHIP_COMPANY': order_line.otst_name2,
                'SHIP_ADDR1': order_line.otst_addr1,
                'SHIP_ADDR2': order_line.otst_addr2,
                'SHIP_CITY': order_line.otst_city,
                'SHIP_STATE': order_line.otst_state,
                'SHIP_POSTAL_CODE': postal_code,
                'SHIP_COUNTRY': 'US',
                'SHIP_PHONE': edi_order['shipTo']['phone'] if edi_order and 'phone' in edi_order['shipTo'] else None,
                'RET_NAME': None,
                'RET_COMPANY': order_line.name,
                'RET_ADDR1': sender.address1,
                'RET_ADDR2': None,
                'RET_CITY': sender.city,
                'RET_STATE': sender.state,
                'RET_POSTAL_CODE': sender.postalCode,
                'RET_COUNTRY': sender.country,
                'RET_PHONE': order_line.contact_phone,
                'SENDER_NAME': None,
                'SENDER_COMPANY': order_line.name,
                'SENDER_ADDR1': sender.address1,
                'SENDER_ADDR2': None,
                'SENDER_CITY': sender.city,
                'SENDER_STATE': sender.state,
                'SENDER_POSTAL_CODE': sender.zip,
                'SENDER_COUNTRY': sender.country,
                'SENDER_PHONE': order_line.contact_phone,
                'DEPT_CODE': self.profile.get_department(),
                'HEIGHT': round(package.size.height),
                'WIDTH': round(package.size.width),
                'LENGTH': round(package.size.length),
                'BILL_TRANS_TO': 1,
                'DELIVERY_INST': None,
                'PACKAGE_TYPE': 1,
                'SERVICE_TYPE': package.get_service_type().value,
                'WEIGHT': round(package.weight.amt),
                'WEIGHT_TYPE': self.profile.fulfillment_config.get_weight_type(package.weight.units).value,
                'RETURN_LABEL_TYPE': 1,
                'SHIPPED': 0,
                'BILLABLE_WEIGHT': package.billable_weight,
                'DIMENSIONAL_WEIGHT': round(package.dimensional_weight, 2),
                'PACKAGE_SIZE': package.size.get_total_size(),
                'CREATED_AT': datetime.now().isoformat(),
                'UPDATED_AT': datetime.now().isoformat(),
                'SHIP_SEQ': _fulfillment.get('subsequence', 0),
                'FDX_ACCT': self.profile.fulfillment_config.fedex_acct or '0748116'
            }
            data_dicts.append(data_dict)
            # INSERT INTO `shipment` (`SHIPMENT_ID`, `CUSTOMER_REF`, `SHIP_NAME`, `SHIP_COMPANY`, `SHIP_ADDR1`, `SHIP_ADDR2`, `SHIP_CITY`, `SHIP_STATE`, `SHIP_POSTAL_CODE`, `SHIP_COUNTRY`, `SHIP_PHONE`, `RET_NAME`, `RET_COMPANY`, `RET_ADDR1`, `RET_ADDR2`, `RET_CITY`, `RET_STATE`, `RET_POSTAL_CODE`, `RET_COUNTRY`, `RET_PHONE`, `DEPT_CODE`, `HEIGHT`, `WIDTH`, `LENGTH`, `BILL_TRANS_TO`, `DELIVERY_INST`, `PACKAGE_TYPE`, `SERVICE_TYPE`, `WEIGHT`, `WEIGHT_TYPE`, `RETURN_LABEL_TYPE`,  `SENDER_NAME`, `SENDER_COMPANY`, `SENDER_ADDR1`, `SENDER_ADDR2`, `SENDER_CITY`, `SENDER_STATE`, `SENDER_POSTAL_CODE`, `SENDER_COUNTRY`, `SENDER_PHONE`) VALUES ('402100-999991-0001', 'D1-497208-25', 'Wiley Coyote', 'Acme, Inc.', '1200 Road Runner Ln', ' ', 'Las Vegas', 'NV', '88901', 'US', '7045551212', 'Oscar the Grouch', 'Semame Seeds', '1 Downtown', '01234567890123456789012345678901234', 'Cupertino', 'CA', '94024-3333', 'US', '000-000-0000', 'BUILDING-D', 10, 2, 10, 1, 'Leave at nearest roadrunner', 1, 1, 123, 2, 1, 'Oscar the Grouch', 'Semame Seeds', '1 Downtown', '01234567890123456789012345678901234', 'Cupertino', 'CA', '94024-3333', 'US', '000-000-0000');
            values = ', '.join(list(map(lambda key: f"%({key})s", data_dict.keys())))
            stmt = "insert into ShipDB.`shipment` ({columns}) values ({values});".format(
                columns=",".join(data_dict.keys()),
                values=values)
            FSMDB.execute(sql=stmt, parameters=data_dict, config_path=self.config_path)
        return data_dicts