package com.gpc.tams.repository.rowmapper;

import com.gpc.tams.repository.customer.Customer;
import com.gpc.tams.repository.customer.CustomerAr;
import com.gpc.tams.repository.customer.CustomerDelivery;
import com.gpc.tams.repository.customer.CustomerChangeQueue;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.LinkedHashMap;
import java.util.Map;

public class CustomerChangeMapper implements GlobalRowMapper<Customer> {
    public static final CustomerChangeMapper INSTANCE = new CustomerChangeMapper();
    private static final StringBuffer QUERY = new StringBuffer();
    static {
       QUERY.append(" SELECT ")
            .append("    ISNULL(C.ID, -1) AS CUST_ID, ")
            .append("    CCQ.ID           AS QUEUE_ID, ")
            .append("    CCQ.ACTION, ")
            .append("    CCQ.TRIGGER_SOURCE, ")
            .append("    CCQ.VALIDATION_CD, ")
            .append("    CCQ.RETRY_COUNT, ")
            .append("    CCQ.CREATED_DATE CCQ_CREATED_DATE, ")
            .append("    CCQ.DELETED_VALUE, ")
            .append("    C.ID CUSTOMER_ID, ")
            .append("    C.GLOBAL_CUSTOMER_ID, ")
            .append("    C.CUSTOMER_NUM, ")
            .append("    C.LOC, ")
            .append("    C.PRIORITY_PLUS, ")
            .append("    C.ALTERNATE_CORE_CUSTOMER_ID, ")
            .append("    C.IBS_ACCOUNT, ")
            .append("    C.REF_NAPA_CUSTOMER_CATEGORY_ID, ")
            .append("    C.REF_CUSTOMER_TYPE_CD, ")
            .append("    C.ALPHA_KEY, ")
            .append("    C.ACTIVE_ACCOUNT, ")
            .append("    C.ACCOUNT_ESTABLISH_DATE, ")
            .append("    C.NAME, ")
            .append("    C.EMAIL, ")
            .append("    C.INVOICE_EMAIL_ADDRESS, ")
            .append("    C.PHONE, ")
            .append("    C.EXTENSION, ")
            .append("    C.FAX_NUMBER, ")
            .append("    C.WEB_ADDRESS, ")
            .append("    C.CHECK_FOR_MISC_CHG_EXEMPTIONS, ")
            .append("    C.EXTRA_INV_COPIES, ")
            .append("    C.MIN_AMT_FOR_PO, ")
            .append("    C.CUSTOMER_NOTE_ID, ")
            .append("    C.ALLOW_ONLY_CORE_SALES, ")
            .append("    C.ALLOW_SPECIAL_ORDERS, ")
            .append("    C.AUTHORIZED_BUYERS_ONLY, ")
            .append("    C.REF_BILLING_TYPE_CD, ")
            .append("    C.REF_LANGUAGE_CD, ")
            .append("    C.PRINT_BALANCE_ON_INV, ")
            .append("    C.PRINT_CATALOG_NOTES_ON_INVOICE, ")
            .append("    C.PRINT_MSDS_FOR_CUSTOMER, ")
            .append("    C.TRANSMIT_JSA_SALES, ")
            .append("    C.TRANSMIT_NATIONAL_SALES, ")
            .append("    C.CUSTOMER_TYPICALLY_PRESENT_DURING_INVOICING, ")
            .append("    C.USUAL_PRICE_MARKUP_PERCENT, ")
            .append("    C.USUAL_REF_PRICE_SHEET_CD, ")
            .append("    C.CPP_FLAG, ")
            .append("    C.SALESMAN_EMPLOYEE_ID, ")
            .append("    C.ALLOW_EMPLOYEE_ERASE_CORE, ")
            .append("    C.ALLOW_BACKORDERS, ")
            .append("    C.MONTHLY_SALES_POTENTIAL, ")
            .append("    C.DIRECT_SHIPMENT_ELIGIBLE, ")
            .append("    C.DNS_CUSTOMER, ")   
            .append("    C.LAST_MODIFIED_DATE CUSTOMER_LAST_MODIFIED_DATE, ")
            .append("    C.MODIFIED_BY_EMPLOYEE_ID CUSTOMER_MODIFIED_BY_EMPLOYEE_ID, ")            
            // CUSTOMER ADDRESS
            .append("    C.ADDRESS1, ")
            .append("    C.ADDRESS2, ")
            .append("    C.ADDRESS3, ")
            .append("    C.ADDRESS4, ")            
            .append("    C.CITY, ")
            .append("    C.POSTAL_CODE, ")
            .append("    C.REF_STATE_CD, ")
            .append("    C.REF_COUNTRY_CD, ")            
            // CUSTOMER TAX ATTRIBUTES
            .append("    C.DEFAULT_TAX_TABLE_ID, ")
            .append("    C.DELIVERED_TAX_TABLE_ID, ")
            .append("    C.PRIMARY_TAX_IDENTIFIER, ")
            .append("    C.SECONDARY_TAX_IDENTIFIER, ")
            .append("    C.TAX_EXEMPT_NUM_PRIMARY, ")
            .append("    C.TAX_EXEMPT_NUM_SECONDARY, ")
            .append("    C.TAX_EXEMPT_PRIMARY_EXP_DATE, ")
            .append("    C.TAX_EXEMPT_SECONDARY_EXP_DATE, ")
            .append("    C.TAX_STATUS_PRIMARY_REF_OPTION_CD, ")
            .append("    C.TAX_STATUS_SECONDARY_REF_OPTION_CD, ")   
            
            // CUSTOMER AR
            .append("    CAR.CUSTOMER_ID AS CUSTOMER_AR_CUSTOMER_ID, ")
            .append("    CAR.AR_TERMS_ID, ")
            .append("    CAR.LOC AS CUSTOMER_AR_LOCATION, ")            
            .append("    CAR.REF_STATEMENT_TYPE_ID, ")
            .append("    CAR.STATEMENT_CUSTOMER_ID, ")
            .append("    CAR.EXTRA_STATEMENT_COPIES, ")
            .append("    CAR.COMBINED_STATEMENT, ")
            .append("    CAR.CREDIT_LIMIT, ")
            .append("    CAR.STORE_RESPONSIBILITY, ")
            .append("    CAR.AGE_CUSTOMER, ")
            .append("    CAR.BAD_DEBT_ACCT, ")
            .append("    CAR.ACCT_SUMMARY_DAYS, ")
            .append("    CAR.INVOICE_COPY_DAYS, ")
            .append("    CAR.RETURNED_CHECK_COUNT, ")
            .append("    CAR.ACCEPT_CHECK_IF_PRIOR_BAD_CHECK, ")
            .append("    CAR.REF_STATEMENT_FORMAT_ID, ")
            .append("    CAR.STATEMENT_EMAIL_ADDRESS, ")
            .append("    CAR.LAST_COD_DATE, ")
            .append("    CAR.NUM_OF_TIMES_COD, ")  
            
            // CUSTOMER DELIVERY
            .append("    CD.CUSTOMER_ID AS DELIVERY_CUSTOMER_ID, ")
            .append("    CD.DELIVERY_PRIORITY_ID, ")
            .append("    CD.DELIVERY_REF_OPTION_CD, ")
            .append("    CD.DELIVERY_ROUTE_CD, ")
            .append("    CD.TRAVEL_TIME_CUSTOMER, ")
            .append("    CD.DISTANCE_TO_CUSTOMER, ")
            .append("    CD.DIRECTIONS, ")
            .append("    CD.MIN_AMT_FREE_DELIVERY, ")
            .append("    CD.DELIVERY_CHARGE ")
            .append("FROM ")
            .append("    CUSTOMER_CHANGE_QUEUE CCQ ")
            .append("LEFT OUTER JOIN ")
            .append("    CUSTOMER C ")
            .append("ON ")
            .append("    CCQ.LOC = C.LOC ")
            .append("AND CCQ.CUSTOMER_ID = C.ID ")
            .append("LEFT OUTER JOIN ")
            .append("    CUSTOMER_AR CAR ")
            .append("ON ")
            .append("    CAR.CUSTOMER_ID = C.ID ")
            .append("AND CAR.LOC = C.LOC ")
            .append("LEFT OUTER JOIN ")
            .append("    CUSTOMER_DELIVERY CD ")
            .append("ON ")
            .append("    CD.CUSTOMER_ID = C.ID ")
            .append("AND CD.LOC = C.LOC ")
            .append("WHERE ")
            .append("    CCQ.ID BETWEEN :lastSyncedQueueId AND :lastSyncedQueueId + :feedSize ")
            .append("AND CCQ.LOC = :location ")            
            .append("ORDER BY ")
            .append("    CCQ.ID, ")
            .append("    C.ID");
    }

    private CustomerChangeMapper() {
    }

    @Override
    public String getQuery() {
        return QUERY.toString();
    }

    @Override
    public Map<String, Integer> getParameters() {
        final Map<String, Integer> parameters = new LinkedHashMap<String, Integer>();
        parameters.put("lastSyncedQueueId", Types.INTEGER);
        parameters.put("feedSize", Types.INTEGER);
        parameters.put("location", Types.INTEGER);
        return parameters;
    }

    public Customer mapRow(ResultSet resultSet, int rowNum) throws SQLException{

         // CUSTOMER
        final Customer customer = new Customer();
        customer.setId(resultSet.getInt("CUST_ID"));
        customer.setGlobalCustomerId(resultSet.getString("GLOBAL_CUSTOMER_ID"));
        customer.setLocation(resultSet.getInt("LOC")); 
        customer.setCustomerNumber(resultSet.getInt("CUSTOMER_NUM"));
        customer.setPriorityPlus(resultSet.getInt("PRIORITY_PLUS"));
        if(resultSet.getObject("ALTERNATE_CORE_CUSTOMER_ID") instanceof Integer) {            
            customer.setAlternateCoreCustomerId((Integer)resultSet.getObject("ALTERNATE_CORE_CUSTOMER_ID"));
        }
        
        final String ibsAccount = resultSet.getString("IBS_ACCOUNT");
        customer.setIbsAccount(ibsAccount == null ? false : ibsAccount.equalsIgnoreCase("Y")); 
        if(resultSet.getObject("REF_NAPA_CUSTOMER_CATEGORY_ID") instanceof Integer) {            
            customer.setRefNapaCustomerCategoryId((Integer) resultSet.getObject("REF_NAPA_CUSTOMER_CATEGORY_ID"));
        }
        customer.setRefCustomerTypeCode(resultSet.getString("REF_CUSTOMER_TYPE_CD"));
        customer.setAlphaKey(resultSet.getString("ALPHA_KEY"));
        
        final String activeAccount = resultSet.getString("ACTIVE_ACCOUNT"); 
        customer.setActiveAccount(activeAccount == null ? false : activeAccount.equalsIgnoreCase("Y"));
        
        customer.setAccountEstablishDate(resultSet.getTimestamp("ACCOUNT_ESTABLISH_DATE"));
        customer.setName(resultSet.getString("NAME"));
        customer.setEmail(resultSet.getString("EMAIL"));
        customer.setInvoiceEmailAddress(resultSet.getString("INVOICE_EMAIL_ADDRESS"));
        customer.setPhone(resultSet.getString("PHONE"));
        customer.setExtension(resultSet.getString("EXTENSION"));
        customer.setFaxNumber(resultSet.getString("FAX_NUMBER"));
        customer.setWebAddress(resultSet.getString("WEB_ADDRESS"));
        
        final String checkForMiscChgExemptions = resultSet.getString("CHECK_FOR_MISC_CHG_EXEMPTIONS"); 
        customer.setCheckForMiscChangeExemptions(checkForMiscChgExemptions == null 
                ? false : checkForMiscChgExemptions.equalsIgnoreCase("Y"));
        if(resultSet.getObject("EXTRA_INV_COPIES") instanceof Integer) {            
            customer.setExtraInvoiceCopies((Integer)resultSet.getObject("EXTRA_INV_COPIES"));
        }
        if(resultSet.getObject("MIN_AMT_FOR_PO") instanceof Integer) {            
            customer.setMinAmountForPO((Integer) resultSet.getObject("MIN_AMT_FOR_PO"));
        }
        if(resultSet.getObject("CUSTOMER_NOTE_ID") instanceof Integer) {            
            customer.setCustomerNoteId((Integer) resultSet.getObject("CUSTOMER_NOTE_ID"));
        }
        customer.setAllowOnlyCoreSales(resultSet.getString("ALLOW_ONLY_CORE_SALES"));
                
        final String allowSpecialOrders = resultSet.getString("ALLOW_SPECIAL_ORDERS"); 
        customer.setAllowSpecialOrders(allowSpecialOrders == null ? false : allowSpecialOrders.equalsIgnoreCase("Y"));

        final String authorizedBuyersOnly = resultSet.getString("AUTHORIZED_BUYERS_ONLY"); 
        customer.setAuthorizedBuyersOnly(authorizedBuyersOnly == null ? false : authorizedBuyersOnly.equalsIgnoreCase("Y"));
        
        customer.setRefBillingTypeCode(resultSet.getString("REF_BILLING_TYPE_CD"));
        customer.setRefLanguageCode(resultSet.getString("REF_LANGUAGE_CD"));        
        
        final String printBalanceOnInvoice = resultSet.getString("PRINT_BALANCE_ON_INV"); 
        customer.setPrintBalanceOnInvoice(printBalanceOnInvoice == null 
                ? false : printBalanceOnInvoice.equalsIgnoreCase("Y"));
        
        final String printCatalogNotesOnInvoice = resultSet.getString("PRINT_CATALOG_NOTES_ON_INVOICE");
        customer.setPrintCatalogNotesOnInvoice(printCatalogNotesOnInvoice == null 
                ? false : printCatalogNotesOnInvoice.equalsIgnoreCase("Y"));
        
        final String printMsdsForCustomer = resultSet.getString("PRINT_MSDS_FOR_CUSTOMER");
        customer.setPrintMsdsForCustomer(printMsdsForCustomer == null 
                ? false : printMsdsForCustomer.equalsIgnoreCase("Y"));
        
        customer.setTransmitJsaSales(resultSet.getString("TRANSMIT_JSA_SALES"));
        customer.setTransmitNationalSales(resultSet.getString("TRANSMIT_NATIONAL_SALES"));
        
        final String customerTypicallyPresentDuringInvoicing = resultSet.getString("CUSTOMER_TYPICALLY_PRESENT_DURING_INVOICING");
        customer.setCustomerTypicallyPresentDuringInvoicing(customerTypicallyPresentDuringInvoicing == null 
                ? false : customerTypicallyPresentDuringInvoicing.equalsIgnoreCase("Y"));
               
        customer.setUsualPriceMarkupPercent(resultSet.getInt("USUAL_PRICE_MARKUP_PERCENT"));
        customer.setUsualRefPriceSheetCode(resultSet.getString("USUAL_REF_PRICE_SHEET_CD"));        
        customer.setCppFlag(resultSet.getInt("CPP_FLAG"));
        customer.setSalesmanEmployeeId(resultSet.getInt("SALESMAN_EMPLOYEE_ID"));
        
        final String allowEmployeeEraseCore = resultSet.getString("ALLOW_EMPLOYEE_ERASE_CORE");
        customer.setAllowEmployeeEraseCore(allowEmployeeEraseCore == null ? false : allowEmployeeEraseCore.equalsIgnoreCase("Y"));
        
        final String allowBackorders = resultSet.getString("ALLOW_BACKORDERS");
        customer.setAllowBackorders(allowBackorders == null ? false : allowBackorders.equalsIgnoreCase("Y"));
        
        customer.setMonthlySalesPotential(resultSet.getBigDecimal("MONTHLY_SALES_POTENTIAL"));
        
        final String directShipmentEligible = resultSet.getString("DIRECT_SHIPMENT_ELIGIBLE");
        customer.setDirectShipmentEligible(directShipmentEligible == null ? false : directShipmentEligible.equalsIgnoreCase("Y"));   
        customer.setLastModifiedDate(resultSet.getTimestamp("CUSTOMER_LAST_MODIFIED_DATE"));
        if (resultSet.getObject("DNS_CUSTOMER") instanceof Integer) {
            customer.setDnsCustomer((Integer) resultSet.getObject("DNS_CUSTOMER"));
        }
        if (resultSet.getObject("CUSTOMER_MODIFIED_BY_EMPLOYEE_ID") instanceof Integer) {
            customer.setModifiedByEmployeeId((Integer) resultSet.getObject("CUSTOMER_MODIFIED_BY_EMPLOYEE_ID"));
        }
        
        // CUSTOMER ADDRESS
        customer.setAddress1(resultSet.getString("ADDRESS1"));
        customer.setAddress2(resultSet.getString("ADDRESS2"));
        customer.setAddress3(resultSet.getString("ADDRESS3"));
        customer.setAddress4(resultSet.getString("ADDRESS4"));
        customer.setCity(resultSet.getString("CITY"));
        customer.setPostalCode(resultSet.getString("POSTAL_CODE"));
        customer.setStateCode(resultSet.getString("REF_STATE_CD"));
        customer.setCountryCode(resultSet.getString("REF_COUNTRY_CD"));
        
        // CUSTOMER TAX ATTRIBUTES
        if(resultSet.getObject("DEFAULT_TAX_TABLE_ID") instanceof Integer) {            
            customer.setDefaultTaxTableId((Integer)resultSet.getObject("DEFAULT_TAX_TABLE_ID"));
        }
        if (resultSet.getObject("DELIVERED_TAX_TABLE_ID") instanceof Integer) {
            customer.setDeliveredTaxTableId((Integer) resultSet.getObject("DELIVERED_TAX_TABLE_ID"));
        }
        customer.setPrimaryTaxNumber(resultSet.getString("PRIMARY_TAX_IDENTIFIER"));
        customer.setSecondaryTaxNumber(resultSet.getString("SECONDARY_TAX_IDENTIFIER"));
        customer.setTaxExemptNumPrimary(resultSet.getString("TAX_EXEMPT_NUM_PRIMARY"));
        customer.setTaxExemptNumSecondary(resultSet.getString("TAX_EXEMPT_NUM_SECONDARY"));
        customer.setTaxExemptPrimaryExpirationDate(resultSet.getTimestamp("TAX_EXEMPT_PRIMARY_EXP_DATE"));
        customer.setTaxExemptSecondaryExpirationDate(resultSet.getTimestamp("TAX_EXEMPT_SECONDARY_EXP_DATE"));
        customer.setTaxStatusPrimaryRefOptionCode(resultSet.getString("TAX_STATUS_PRIMARY_REF_OPTION_CD"));
        customer.setTaxStatusSecondaryRefOptionCode(resultSet.getString("TAX_STATUS_SECONDARY_REF_OPTION_CD"));
        
        // CUSTOMER_AR
        CustomerAr customerAr = null;        
        final Object customerARCustomerId = resultSet.getObject("CUSTOMER_AR_CUSTOMER_ID");
        if(customerARCustomerId instanceof Integer) {
            customerAr = new CustomerAr();
            customerAr.setArTermsId(resultSet.getInt("AR_TERMS_ID"));
            customerAr.setRefStatementTypeId((Integer)resultSet.getObject("REF_STATEMENT_TYPE_ID"));
            customerAr.setStatementCustomerId((Integer)resultSet.getObject("STATEMENT_CUSTOMER_ID"));
            customerAr.setLocation(resultSet.getInt("CUSTOMER_AR_LOCATION"));
            customerAr.setExtraStatementCopies((Integer)resultSet.getObject("EXTRA_STATEMENT_COPIES"));
            final String combinedStatement = resultSet.getString("COMBINED_STATEMENT");
            customerAr.setCombinedStatement(combinedStatement == null ? false : combinedStatement.equalsIgnoreCase("Y"));        
            customerAr.setCreditLimit((Integer)resultSet.getObject("CREDIT_LIMIT"));
            customerAr.setStoreResponsibility((Integer)resultSet.getObject("STORE_RESPONSIBILITY"));        
            final String ageCustomer = resultSet.getString("AGE_CUSTOMER");
            customerAr.setAgeCustomer(ageCustomer == null ? false : ageCustomer.equalsIgnoreCase("Y"));        
            final String badDebtAccount = resultSet.getString("BAD_DEBT_ACCT");
            customerAr.setBadDebtAccount(badDebtAccount == null ? false : badDebtAccount.equalsIgnoreCase("Y"));        
            customerAr.setAccountSummaryDays((Integer)resultSet.getObject("ACCT_SUMMARY_DAYS"));
            customerAr.setInvoiceCopyDays((Integer)resultSet.getObject("INVOICE_COPY_DAYS"));
            customerAr.setReturnedCheckCount((Integer)resultSet.getObject("RETURNED_CHECK_COUNT"));
            final String acceptCheckIfPriorBadCheck = resultSet.getString("ACCEPT_CHECK_IF_PRIOR_BAD_CHECK");
            customerAr.setAcceptCheckIfPriorBadCheck(acceptCheckIfPriorBadCheck == null 
                    ? false : acceptCheckIfPriorBadCheck.equalsIgnoreCase("Y"));
            customerAr.setRefStatementFormatId(resultSet.getByte("REF_STATEMENT_FORMAT_ID"));
            customerAr.setStatementEmailAddress(resultSet.getString("STATEMENT_EMAIL_ADDRESS"));
            customerAr.setLastCodDate(resultSet.getTimestamp("LAST_COD_DATE"));
            customerAr.setNumberOfTimesCod((Integer)resultSet.getInt("NUM_OF_TIMES_COD"));        
        }        
        customer.setCustomerAr(customerAr);
        
        // CUSTOMER_DELIVERY
        CustomerDelivery customerDelivery = null;               
        final Object deliveryCustomerId =  resultSet.getObject("DELIVERY_CUSTOMER_ID");
        if(deliveryCustomerId instanceof Integer) {            
            customerDelivery = new CustomerDelivery();
            customerDelivery.setDeliveryPriorityId((Integer)resultSet.getInt("DELIVERY_PRIORITY_ID"));
            customerDelivery.setDeliveryRefOptionCode(resultSet.getString("DELIVERY_REF_OPTION_CD"));
            customerDelivery.setDeliveryRouteCode(resultSet.getString("DELIVERY_ROUTE_CD"));
            customerDelivery.setTravelTimeCustomer((Integer)resultSet.getInt("TRAVEL_TIME_CUSTOMER"));
            customerDelivery.setDistanceToCustomer((Integer)resultSet.getInt("DISTANCE_TO_CUSTOMER"));
            customerDelivery.setDirections(resultSet.getString("DIRECTIONS"));
            customerDelivery.setMinAmountFreeDelivery(resultSet.getBigDecimal("MIN_AMT_FREE_DELIVERY"));
            customerDelivery.setDeliveryCharge(resultSet.getBigDecimal("DELIVERY_CHARGE"));
        }
        customer.setCustomerDelivery(customerDelivery);
        
        final CustomerChangeQueue queue = new CustomerChangeQueue();
        queue.setId(resultSet.getInt("QUEUE_ID"));
        queue.setCustomerId(customer.getId());
        queue.setAction(resultSet.getString("ACTION"));
        if(resultSet.getObject("RETRY_COUNT") instanceof Integer) {            
            queue.setRetryCount((Integer)resultSet.getObject("RETRY_COUNT"));
        }
        queue.setTriggerSource(resultSet.getString("TRIGGER_SOURCE"));
        queue.setValidationCd(resultSet.getString("VALIDATION_CD"));
        queue.setDeletedValue(resultSet.getString("DELETED_VALUE"));
        queue.setCreatedDate(resultSet.getTimestamp("CCQ_CREATED_DATE"));
        
        customer.setCustomerChangeQueue(queue);
        return customer;
    }
}