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 .container import Container
from .errors import OrderShipStatusError, OrderLineNotFoundError
from .fedex_ship_mgr_db import FSMDB
from .fulfillment import Fulfillment
from .fulfillment_status import FulfillmentStatus
from .util import group_by
from .orders import Orders
from .profile import Profile
import logging
from aws_xray_sdk.core import xray_recorder

logger = logging.getLogger(__name__)


# noinspection SqlResolve
class FedexShipManagerContainer:
    # TODO: Update Carrier Integration with container instead of order semantics, then make this extend CarrierIntegration again.
    def __init__(self, profile: Profile):
        # super().__init__(profile)
        self.profile = 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):
        fulfillment_ids = [f"'{f['id']}" for f in fulfillment_list]
        filter_str = ', '.join(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': filter_str}, config_path='/ecommerce/fsm')
        return results

    @staticmethod
    @xray_recorder.capture()
    def update_fulfillments():
        pending = Fulfillment.fetch_pending()
        logger.info(f"Found {len(pending)} pending fulfillments.")
        ship_data = FedexShipManagerContainer.__get_shipment_info_for(pending)
        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())

    @xray_recorder.capture()
    def is_shipped(self, container: Container):
        return self.__container_is_shipped(container=container)

    @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)
        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):
        return self.__record_count_for_so(order)

    @xray_recorder.capture()
    def necessary_records_exist(self, container: Container=None):
        return self.__record_exists_for_container(container=container)

    @xray_recorder.capture()
    def __container_is_shipped(self, container: Container):
        stmt = f"""select
                          SHIPMENT_ID,
                          CCN,
                          SO,
                          SO_LINE
                        from {self.table_name}
                        WHERE
                          SHIPMENT_ID=%(container_id)s
                          and CCN=%(ccn)s
                          and SHIPPED = 1
                        """
        params = {'container_id': container.container_id, 'ccn': container.ccn}
        result = FSMDB.fetch(sql=stmt, parameters=params, config_path=self.config_path)
        if result:
            return True
        return False

    @xray_recorder.capture()
    def __record_exists_for_container(self, container: Container):
        stmt = f"""select
                          SHIPMENT_ID,
                          CCN,
                          SO,
                          SO_LINE,
                          SHIPPED
                        from {self.table_name}
                        WHERE
                          SHIPMENT_ID=%(container_id)s
                          and CCN=%(ccn)s
                        """
        params = {'container_id': container.container_id, 'ccn': container.ccn}
        result = FSMDB.fetch(sql=stmt, parameters=params, config_path=self.config_path)
        if result:
            return True
        return False

    @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_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, container: Container):
        if self.is_shipped(container=container):
            raise OrderShipStatusError(f"Container is already shipped, unable to remove. [{container.container_id}]")
        else:
                logger.info(f"Deleting shipments [{container.container_id}.")
                stmt = f"delete from {self.table_name} where SHIPMENT_ID=%(container_id)s"
                params = {'container_id': container.container_id}
                FSMDB.execute(sql=stmt, parameters=params, config_path=self.config_path)

    @xray_recorder.capture()
    def create_shipment(self, fulfillment):
        sender = self.profile.sender
        _fulfillment = Fulfillment.for_(fulfillment)
        edi_order = Orders.for_(_fulfillment['order_id'])
        line = _fulfillment['line_items'][0]
        order = ERP.for_(edi_order.customer_edi_id).fetch_order_line_by_id(_fulfillment['order_id'], line['purchase_order_line'])
        if not order:
            raise OrderLineNotFoundError(f"Unable to find glovia order line for {line['line_id']}")
        _container = Container.fetch(ccn=order.sales_ccn, container_id=_fulfillment['container_id'])
        _package = Package.create_from(profile=self.profile, container=_container)

        # 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.
        postal_code = order.one_time_ship_to.postalCode
        if '-' in order.one_time_ship_to.postalCode and order.one_time_ship_to.country in ['US', 'USA']:
            postal_code = order.one_time_ship_to.postalCode[0:5]

        data_dict = {
            'SHIPMENT_ID': _container.get_shipment_id(),
            'FULFILLMENT_ID': _fulfillment['id'],
            'CUSTOMER_REF': _container.container_id,
            'CUSTOMER_EDI_ID': _container.customer_edi_id.strip(),
            'CUS_PO': order.purchase_order,
            'CUS_PO_LINE': order.purchase_order_line,
            'CCN': order.sales_ccn.strip(),
            'SO': order.sales_order,
            'SO_LINE': order.sales_order_line,
            'SHIP_NAME': order.one_time_ship_to.name,
            'SHIP_COMPANY': order.one_time_ship_to.company,
            'SHIP_ADDR1': order.one_time_ship_to.address1,
            'SHIP_ADDR2': order.one_time_ship_to.address2,
            'SHIP_CITY': order.one_time_ship_to.city,
            'SHIP_STATE': order.one_time_ship_to.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': sender.company,
            'RET_ADDR1': sender.address1,
            'RET_ADDR2': None,
            'RET_CITY': sender.city,
            'RET_STATE': sender.state,
            'RET_POSTAL_CODE': sender.zip,
            'RET_COUNTRY': sender.country,
            'RET_PHONE': sender.phone,
            'SENDER_NAME': sender.company,
            'SENDER_COMPANY': sender.company,
            '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': sender.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': 1,
            'FDX_ACCT': self.profile.fulfillment_config.fedex_acct or '0748116'
        }
        # 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_dict