package com.onsemi.middleware.lite.DbCsv_to_Snowflake.test;

import static net.bytebuddy.matcher.ElementMatchers.named;

import java.io.StringReader;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.data.jpa.repository.query.JSqlParserQueryEnhancer;
import org.springframework.data.jpa.repository.query.QueryEnhancerFactory;
import org.springframework.test.context.junit4.SpringRunner;

import com.onsemi.middleware.junit.runtime.sb.JunitConfiguration;
import com.onsemi.middleware.junit.runtime.sb.UeEngineSkeletonSpringBoot;
import com.onsemi.middleware.messaging.InboundMessage;
import com.onsemi.middleware.ue.execeng.UEngine;
import com.onsemi.middleware.ue.execeng.UExecClass;

import net.bytebuddy.ByteBuddy;
import net.bytebuddy.agent.ByteBuddyAgent;
import net.bytebuddy.dynamic.loading.ClassReloadingStrategy;
import net.bytebuddy.implementation.FixedValue;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.parser.CCJSqlParserManager;
import net.sf.jsqlparser.statement.Statement;

@RunWith(SpringRunner.class)
@SpringBootTest(
		webEnvironment=SpringBootTest.WebEnvironment.NONE,
		classes = {UeEngineSkeletonSpringBoot.class}
)
public class GenericDbCsvSnowflakeTest {
	public static final String PAYLOAD = "<payload><batch_id>-3</batch_id></payload>";
	public static final String IFACE_NAME = "testKaravan";

	static {
		UeEngineSkeletonSpringBoot.setup();
		setupPayloadAndInterfaceName();
		UeEngineSkeletonSpringBoot.excludedInterfacePackageAutoconfigurationClassesFromJunitMainContext = "com.onsemi.middleware.lite.DbCsv_to_Snowflake";
	}

	private static void setupPayloadAndInterfaceName() {
		JunitConfiguration.interfaceName = IFACE_NAME;
		JunitConfiguration.payload = PAYLOAD;
		ByteBuddyAgent.install();
		new ByteBuddy()
				.redefine(QueryEnhancerFactory.class)
				.method(named("isJSqlParserInClassPath"))
				.intercept(FixedValue.value(false))
				.make()
				.load(JSqlParserQueryEnhancer.class.getClassLoader(), ClassReloadingStrategy.fromInstalledAgent());
	}
	@Autowired
	private UeEngineSkeletonSpringBoot skeletonSpringBoot;
	@Autowired
	private UEngine uEngine;

	@Test
	public void testInterfaceRunSb() {
		BeanFactory interfaceBeanFactory = skeletonSpringBoot.createSpringContext();
		interfaceBeanFactory.getBean(UExecClass.class)
							.process(uEngine, interfaceBeanFactory.getBean(InboundMessage.class));

	}

	public void testInterfaceRun() throws JSQLParserException {
		CCJSqlParserManager parseSql = new CCJSqlParserManager();
		String sql = "SELECT\n" + "     l.ordered_quantity   AS OPEN_QTY,\n"
				+ "     to_char (TO_DATE (l.attribute5, 'YYYY/MM/DD HH24:MI:SS'))   AS INITSCH_DLVRY_DT,\n"
				+ "     decode (l.flow_status_code, 'BOOKED', 'OP', 'AWAITING_SHIPPING', (CASE WHEN (SELECT COUNT (1) FROM wsh_delivery_details wdd WHERE l.line_id = wdd.source_line_id AND released_status = 'Y') > 0 THEN 'RL' ELSE 'SC' END) , 'CANCELLED', 'XX', 'FULFILLED', 'IN', 'SHIPPED', 'IN', 'CLOSED', 'IN')   AS DLVRY_STATUS_CD,\n"
				+ "     fu_lu.user_name   AS LAST_UPDT_SRCE_TXT,\n" + "     nvl (l.d_ext_attr2, l.creation_date)   AS CREATE_DTME,\n"
				+ "     l.SPLIT_FLAG   AS SPLIT_FLAG,\n" + "     l.promise_date   AS SCHED_DELIVERY_DT,\n" + "     l.actual_shipment_date   AS CURR_SHIP_DT,\n"
				+ "     to_char((CASE WHEN l.c_ext_attr15 IS NOT NULL THEN l.c_ext_attr15 WHEN l.c_ext_attr15 IS NULL AND l.source_name = 'Conversions - EDI - Quotes' THEN h.sales_document_name ELSE to_char (h.quote_number) END))   AS SALES_QUOTE_NUM,\n"
				+ "     l.item_number   AS MARKET_PN,\n"
				+ "     decode(h.transactional_curr_code, 'USD', 1, gdr.conversion_rate) * l.unit_selling_price   AS U_PRICE_CRRT_AMT,\n"
				+ "     l.ship_to_cust_site_location   AS CPO_CUST_CD,\n" + "     h.order_attribute1   AS SALES_ORDER_NUM,\n"
				+ "     nvl (l.c_ext_attr20, l.shipment_number)   AS SALES_DELIVERY_NUM,\n"
				+ "     trunc (to_date (l.attribute13, 'YYYY/MM/DD HH24:MI:SS'))   AS IRD_DT,\n"
				+ "     l.ordered_quantity * l.unit_selling_price   AS OPEN_AMT,\n" + "     l.schedule_ship_date   AS RESCH_SHIP_DT,\n"
				+ "     decode(h.transactional_curr_code, 'USD', 1, gdr.conversion_rate) * l.unit_selling_price   AS U_PRICE_CNSTNT_AMT,\n"
				+ "     l.earliest_acceptable_date   AS EARLIEST_SHIP_DT,\n" + "     l.freight_carrier_code   AS CARRIER_CD,\n"
				+ "     l.odm_end_corp   AS ODM_END_CORP_CD,\n" + "     l.pricelst_name   AS CUSPA_NUMBER,\n" + "     l.attribute6   AS DEMAND_LOTCLASS_CD,\n"
				+ "     l.promise_date   AS RESCH_DELIVERY_DT,\n" + "     l.attribute2   AS CUST_RECPT_BANK_CD,\n"
				+ "     l.unit_selling_price * l.ordered_quantity   AS OPEN_CNSTNTDLR_AMT,\n" + "     l.promise_date   AS PLANNED_DLVRY_DT,\n"
				+ "     l.customer_line_number   AS PO_ITEM_NUM,\n" + "     TO_DATE (wdd.attribute5, 'YYYY/MM/DD HH24:MI:SS')   AS LAST_PICK_ALLOC_DT,\n"
				+ "     l.ordered_quantity * l.unit_selling_price   AS TOTAL_AMT,\n" + "     l.attribute13   AS CUST_PULL_SGNL_TXT,\n"
				+ "     l.attribute12   AS MDV_MPQ_OVRID_CD,\n" + "     l.c_ext_attr8   AS EMBARGO_CD,\n"
				+ "     (CASE WHEN l.flow_status_code = 'CANCELLED' THEN (SELECT oer.reason_code FROM oe_reasons oer WHERE l.line_id = oer.entity_id AND oer.entity_code = 'LINE' ORDER BY oer.last_update_date DESC FETCH FIRST ROW ONLY) WHEN l.flow_status_code = 'BOOKED' THEN l.attribute7 ELSE NULL END)   AS DLVRY_STAT_RSN_CD,\n"
				+ "     l.c_ext_attr2   AS CUSTOMER_REF_NAM,\n" + "     l.attribute17   AS INIT_REQUESTED_QTY,\n"
				+ "     nvl (l.attribute19, 0)   AS RESCHEDULED_CNT,\n"
				+ "     decode(l.source_name, 'Conversions - Manual', 'O', 'Quotes', 'Q', 'Conversions - EDI - Quotes', 'C', 'Copy', 'O', 'Conversions - Quotes', 'Q', 'Conversions', 'Q', 'Online', 'O', 'E')   AS ORDR_DLVRY_SRC_TXT,\n"
				+ "     TO_DATE (l.attribute8, 'YYYY/MM/DD HH24:MI:SS')   AS INIT_REQUESTED_DT,\n" + "     l.line_number   AS SALES_ITEM_NUM,\n"
				+ "     l.coi   AS COI,\n" + "     l.pricelst_attribute2   AS U_PRICE_SOURCE_CD,\n"
				+ "     decode(h.transactional_curr_code, 'USD', 1, gdr.conversion_rate) * l.ordered_quantity * l.unit_selling_price   AS OPEN_CURRNTDLR_AMT,\n"
				+ "     h.header_id   AS ORDER_HEADER_ID,\n" + "     l.country_cd   AS COUNTRY_CD,\n" + "     l.nnco   AS NNCO,\n"
				+ "     l.pricelist_create_date   AS U_PRICE_STAT_DTME,\n" + "     l.attribute6   AS DEMAND_CLASS,\n" + "     l.attribute14   AS CITI_CD,\n"
				+ "     l.attribute1   AS DEMAND_BANK_CD,\n" + "     l.attribute20   AS OPO_FLAG,\n" + "     h.order_attribute10   AS ORDRPROC_REGION_CD,\n"
				+ "     decode (l.flow_status_code, 'SHIPPED', 'Y', 'CLOSED', 'Y')   AS SHIP_COMPLETE_FLG,\n"
				+ "     l.actual_shipment_date   AS LAST_SHIP_CNFRM_DT,\n" + "     wdd.attribute3   AS LAST_PICK_REF_NUM,\n"
				+ "     l.source_name   AS ORA_ORDER_DLVRY_SRC_CD,\n"
				+ "     decode (l.flow_status_code, 'SHIPPED', l.ordered_quantity, 'CLOSED', l.ordered_quantity)   AS SHIP_CONFIRMED_QTY,\n"
				+ "     l.request_date   AS REQUESTED_DT,\n"
				+ "     trunc (to_date (l.attribute13, 'YYYY/MM/DD HH24:MI:SS')) - TO_NUMBER (NVL (l.ATTRIBUTE10, 0))   AS IRSD_DT,\n"
				+ "     l.request_date - TO_NUMBER (l.attribute10)   AS REQUESTED_SHIP_DT,\n" + "     l.attribute16   AS RESCH_SHIP_RSN_CD,\n"
				+ "     l.d_ext_attr4   AS DS_RELEASE_DT,\n" + "     l.agreement_type   AS NNCO_AGREEMENT_TYPE,\n"
				+ "     wdd.picked_quantity   AS PICK_ALLOC_QTY,\n" + "     h.order_number   AS ORA_ORDER_NUM,\n"
				+ "     TO_DATE (l.attribute9, 'YYYY/MM/DD HH24:MI:SS')   AS BSSD_DT,\n" + "     l.cust_po_number   AS CUST_PO_NUM,\n"
				+ "     decode(h.transactional_curr_code, 'USD', 1, gdr.conversion_rate) * l.ordered_quantity * l.unit_selling_price   AS TOTAL_CRRTDLR_AMT,\n"
				+ "     fu_cu.user_name   AS CREATE_SOURCE_TXT,\n"
				+ "     decode (l.flow_status_code, 'BOOKED', NULL, to_char (TO_DATE (l.attribute18, 'YYYY/MM/DD HH24:MI:SS') , 'YYYY/MM/DD'))   AS OSSD_DT,\n"
				+ "     l.attribute15   AS PART_ID,\n" + "     l.customer_line_number   AS CUST_PO_ITEM_NUM,\n"
				+ "     l.unit_list_price   AS LIST_PRICE_AMT,\n" + "     l.shipment_number   AS ORA_SHIPMENT_NUM,\n"
				+ "     fu_lu.user_name   AS LAST_UPDATE_USERID,\n" + "     l.c_ext_attr11   AS CHNG_ORDER_SEQ_NUM,\n"
				+ "     h.sold_to_cust_site_location   AS SOLD_TO_CUST_CD,\n" + "     l.ship_to_cust_site_location   AS SHIP_TO_CUST_CD,\n"
				+ "     h.last_update_date   AS DLVRY_STATUS_DTME,\n" + "     l.unit_selling_price   AS U_PRICE_AMT,\n"
				+ "     l.customer_line_number   AS PO_DELIVERY_NUM,\n"
				+ "     decode (h.transactional_curr_code, 'USD', 1, gdr.conversion_rate) * l.unit_selling_price   AS U_PRICE_USD_AMT,\n"
				+ "     l.attribute3   AS END_CUST_CD,\n"
				+ "     decode (l.flow_status_code, 'SHIPPED', ordered_quantity * unit_selling_price, 'CLOSED', ordered_quantity * unit_selling_price)   AS SHIP_CFRM_CURT_AMT,\n"
				+ "     wdd.delivery_detail_id   AS DELIVERY_DTL_ID,\n"
				+ "     decode (l.flow_status_code, 'SHIPPED', ordered_quantity * unit_selling_price, 'CLOSED', ordered_quantity * unit_selling_price)   AS SHIP_CONFIRMED_AMT,\n"
				+ "     TO_NUMBER (l.attribute10)   AS SCHD_TRNST_DAY_CNT,\n" + "     l.bill_to_cust_site_attr5   AS PAYMENT_TERM_CD,\n"
				+ "     h.c_ext_attr4   AS SHIP_ATTN_OF_TXT,\n" + "     l.c_ext_attr20   AS SLSQTE_ITEM_NUM,\n"
				+ "     decode (l.flow_status_code, 'AWAITING_SHIPPING', (CASE WHEN (SELECT COUNT (1) FROM wsh_delivery_details wdd WHERE l.line_id = wdd.source_line_id AND released_status = 'Y') > 0 THEN 'PICKED' ELSE l.flow_status_code END) , l.flow_status_code)   AS ORA_DLVRY_STATUS_CD,\n"
				+ "     decode (l.item_identifier_type, 'CUST', l.ordered_item)   AS CUST_PART_ID,\n" + "     fu_cu.user_name   AS CREATE_USERID,\n"
				+ "     decode (l.flow_status_code, 'SHIPPED', ordered_quantity * unit_selling_price, 'CLOSED', ordered_quantity * unit_selling_price)   AS SHIP_CFRM_CNST_AMT,\n"
				+ "     h.c_ext_attr5   AS DROP_SHIP_FLG,\n" + "     xmio.category_code   AS PROD_CATEGORY_CD,\n" + "     1   AS ROE_INS_CURNCY_RTE,\n"
				+ "     l.c_ext_attr40   AS DS_RELEASED_BY,\n"
				+ "     decode(h.transactional_curr_code, 'USD', 1, gdr.conversion_rate) * l.ordered_quantity * l.unit_selling_price   AS TOTAL_CNSTNT_AMT,\n"
				+ "     h.last_update_date   AS LAST_IOOD_UPDT_DTME,\n" + "     l.schedule_ship_date   AS MRSD_DT,\n"
				+ "     nvl (l.d_ext_attr2, l.creation_date)   AS SALES_ITEM_DTME,\n" + "     l.ordered_quantity   AS DELIVERY_QTY,\n"
				+ "     h.last_update_date   AS LAST_UPDATE_DTME,\n" + "     l.line_id   AS ORDER_LINE_ID,\n"
				+ "     h.order_attribute10   AS MISC_CHG_REGION_CD,\n" + "     l.c_ext_attr2   AS CUST_SHIP_REF_NAME\n" + "FROM\n"
				+ "   WSH_DELIVERY_DETAILS wdd,\n" + "   FND_USER fu_lu,\n" + "   GL_DAILY_RATES gdr,\n" + "   XXON_MTL_ITEM_OVERFLOW xmio,\n"
				+ "   XXON_OM_ORDER_HEADER_DETAILS_V h,\n" + "   FND_USER fu_cu,\n" + "   XXON_OM_ORDER_LINE_DETAILS_V l\n" + "WHERE\n"
				+ "   ((l.flow_status_code IN ('CLOSED', 'CANCELLED') AND trunc (l.last_update_date) > (trunc (sysdate) - 7)) OR l.flow_status_code NOT IN ('CLOSED', 'CANCELLED', 'ENTERED'))\n"
				+ "   AND h.header_id = l.header_id\n" + "   AND l.line_id = wdd.source_line_id (+)\n" + "   AND l.header_id = wdd.source_header_id (+)\n"
				+ "   AND gdr.from_currency (+) = h.currency_code\n" + "   AND trunc (h.creation_date) < trunc (sysdate)\n"
				+ "   AND trunc (h.last_update_date) < trunc (sysdate)\n" + "   AND trunc (l.last_update_date) < trunc (sysdate)\n"
				+ "   AND gdr.to_currency (+) = 'USD'\n" + "   AND gdr.conversion_type (+) = 'Corporate'\n" + "   AND fu_lu.user_id = h.created_by\n"
				+ "   AND fu_cu.user_id = l.created_by\n" + "   AND gdr.conversion_date (+) = trunc (sysdate)\n"
				+ "   AND xmio.inventory_item_id (+) = l.inventory_item_id\n" + "   AND h.transaction_phase_code = 'F' and rownum <=10\n";
		final Statement parse = parseSql.parse(new StringReader(sql));
		parse.getClass();
	}
}

