package com.taover.repository.util;

import java.util.HashMap;
import java.util.Map;
import java.util.Stack;

import org.springframework.util.StringUtils;

import com.taover.repository.exception.SqlParsePagerException;

public class UtilsSql {
	
	public static String[] splitCoreSql(String coreSql) throws SqlParsePagerException {		
		//去除''内的信息
		int fromIndex = calcFromIndex(coreSql);
		if(fromIndex > -1) {
			return new String[] {coreSql.substring(0, fromIndex), coreSql.substring(fromIndex, coreSql.length())};
		}else {
			throw new SqlParsePagerException("未找到FROM子句"); 
		}
	}
		
	private static int calcFromIndex(String coreSql) {
		Stack<String> operStack = new Stack<String>();
		String coreSqlUpperCase = coreSql.toUpperCase();
		int sqlLen = coreSqlUpperCase.length();
		int currIndex = 0;
		while(currIndex < sqlLen) {
			String originContainBlank = subFirstStrEndWithBlank(coreSqlUpperCase, currIndex);
			String itemWithoutBlank = originContainBlank.trim();
			if("".equals(itemWithoutBlank)) {
				currIndex += originContainBlank.length();
				continue;
			}
			if(operStack.isEmpty() && "FROM".equals(itemWithoutBlank)) {
				return currIndex;
			}
			dealCoupleMark(itemWithoutBlank, operStack);			
			currIndex += originContainBlank.length();
		}
		return -1;
	}
	private static void dealCoupleMark(String itemWithoutBlank, Stack<String> operStack) {
		char preChar = ' ';
		for(int i=0; i<itemWithoutBlank.length(); ++i) {
			char currChar = itemWithoutBlank.charAt(i);
			if(preChar != '\\' && currChar == '\'') {
				if(operStack.isEmpty() || !operStack.peek().equals("'")) {
					operStack.push("'");
				}else if(operStack.peek().equals("'")) {
					operStack.pop();
				}
			}else if(currChar == '('){
				if(operStack.isEmpty() || !operStack.peek().equals("'")) {
					operStack.push("(");	
				}
			}else if(currChar == ')') {
				if(!operStack.isEmpty() && operStack.peek().equals("(")) {
					operStack.pop();
				}
			}
			preChar = currChar;
		}
	}

	private static String subFirstStrEndWithBlank(String coreSqlUpperCase, int startIndex) {		
		boolean startWithBlank = coreSqlUpperCase.charAt(startIndex)==' ';
		int endIndex = startIndex;
		int sqlLen = coreSqlUpperCase.length();
		char preChar = '0';
		for(int i=startIndex; i<sqlLen; ++i) {
			char currChar = coreSqlUpperCase.charAt(i);
			if(startWithBlank && currChar!=' ') {
				endIndex = i;
				break;
			}			
			if(!startWithBlank && currChar!=' ' && preChar==' ') {
				endIndex = i;
				break;
			}
			preChar = currChar;
			if(i == sqlLen-1) {
				endIndex = sqlLen;
			}
		}		
		return coreSqlUpperCase.substring(startIndex, endIndex);
	}

	/**
	 * 获取排序字符串
	 * @param sort
	 * @param order
	 * @param columnPreffix
	 * @return
	 */
	public static String getSortCondition(String sort, String order, String columnPreffix){		
		String underScoreSort = UtilsString.underscoreName(sort).toLowerCase();
		if(StringUtils.isEmpty(columnPreffix)){
			columnPreffix = "";
		}
		String sortCondition = " ";
		if(!StringUtils.isEmpty(underScoreSort)){
			sortCondition = " order by ";
			String[] sortArr = underScoreSort.split(",");
			if(StringUtils.isEmpty(order)){
				sortCondition += columnPreffix+sortArr[0]+" DESC ";
				for(int i=1; i<sortArr.length; ++i){
					sortCondition += ","+columnPreffix+sortArr[i]+" DESC ";	
				}					
			}else{
				String[] orderArr = order.split(",");
				sortCondition += sortArr[0]+" "+orderArr[0]+" ";
				for(int i=1; i<sortArr.length; ++i){
					if(i < orderArr.length){
						sortCondition = ","+columnPreffix+sortArr[i]+" "+orderArr[i]+" ";	
					}else{
						sortCondition = ","+columnPreffix+sortArr[i]+" DESC ";
					}						
				}				
			}			
		}
		return sortCondition;
	}
	
	/**
	 * 获取分页
	 * @param sort
	 * @param order
	 * @param columnPreffix
	 * @return
	 */
	public static String getLimitCondition(int page, int pageSize){
		String sql = "";
		if(page < -1){
			page = 0;
			if(pageSize < 1){
				pageSize = 0;
			}
			sql += " limit "+pageSize*(page-1)+","+pageSize;
		}else if(page == -1){
			sql = "";
		}else{
			if(pageSize < 1){
				pageSize = 0;
			}
			sql += " limit "+pageSize*(page-1)+","+pageSize;
		}
		return sql;
	}
	
	/**
	 * 创建分页返回
	 * @param page
	 * @param rows
	 * @param data
	 * @return
	 */
	public static Map<String, Object> createPage(int page, int size, int total, Object data){
		Map<String, Object> pageData = new HashMap<String, Object>();
		pageData.put("page", page);
		pageData.put("rows", data);
		pageData.put("size", size);
		pageData.put("total", total);
		return pageData;
	}
	
	public static void main(String[] args) {
		String[] testSql = new String[] {
			"\\",
			"select actionFrom as 'seelctsele\\'ctd',(select a as 'fromCActio' from t) from www where 223=1 ",
			"SELECT wxorder_order.refund_way refundWay,wxorder_order.refund_delivery_sn refundDeliverySn, (SELECT sum(wxorder_compensate.`ware_refund_money`)  from `wxorder_compensate`  where wxorder_compensate.`order_id`  = wxorder_order.id) as wareRefundMoney,(SELECT sum(wxorder_compensate.`refund_money`)  from `wxorder_compensate`  where wxorder_compensate.`order_id`  = wxorder_order.id) as refundMoney,  wxorder_order.operate_refund_time applyCompensateTime,wxorder_order.refund_instructions refundInstructions, wxorder_order.channel_id channelId, wxorder_channel.name channelName, wxorder_channel.platform_code platformCode,  wxorder_order.pre_control_status as refundPreStatus, wxorder_order.ware_id wareId, wxorder_ware.name wareName,  wxorder_order.id, wxorder_order.order_sn orderSn, wxorder_order.upload_sn uploadSn, wxorder_order.money_paid moneyPaid,  wxorder_order.consignee, wxorder_order.mobile, wxorder_order.province_name provinceName, wxorder_order.city_name cityName, wxorder_order.district_name districtName, wxorder_order.address,  wxorder_order.channel_remark channelRemark, wxorder_order.customer_remark customerRemark, wxorder_order.platform_customer_remark platformCustomerRemark,  wxorder_order.sender_name senderName, wxorder_order.sender_mobile senderMobile,  date_format(wxorder_order.create_time, '%Y-%m-%d %H:%i:%s') createTime, date_format(wxorder_order.real_delivery_time, '%Y-%m-%d %H:%i:%s') realDeliveryTime,  wxorder_order.progress_distribute progressDistribute, wxorder_order.progress_delivery progressDelivery, wxorder_order.control_status controlStatus,  wxorder_order.express_name expressName, wxorder_order.express_number expressNumber,wxorder_order.shipping_price shippingPrice, wxorder_order.customer_network_name ,(select count(*) from wxorder_compensate WHERE wxorder_compensate.order_id = wxorder_order.id) as compensateCountAll,(select count(*) from wxorder_compensate WHERE wxorder_compensate.order_id = wxorder_order.id and wxorder_compensate.progress_status = 1) as compensateCountDealed ,(select count(*) from wxorder_channel_refund_payment where wxorder_channel_refund_payment.order_id = wxorder_order.id) as channelRefundPaymentStatus,(select count(*) from wxorder_ware_refund_payment where wxorder_ware_refund_payment.order_id = wxorder_order.id) as wareRefundPaymentStatus  FROM wxorder_order wxorder_order  INNER JOIN wxorder_channel wxorder_channel ON wxorder_order.channel_id=wxorder_channel.id and wxorder_channel.tenant_id=54 INNER JOIN wxorder_ware wxorder_ware ON wxorder_order.ware_id=wxorder_ware.id and wxorder_ware.tenant_id=54 INNER JOIN wxorder_order_goods wxorder_order_goods ON wxorder_order.id=wxorder_order_goods.order_id and wxorder_order_goods.tenant_id=54 WHERE 1=1  AND wxorder_order.tenant_id='54' AND wxorder_order.control_status='4' GROUP BY wxorder_order.id",
			"select wdd, fromType from www where 223",
			"SELECT  excel_data.already_deal_line alreadyDealLine,  excel_data.channel_id channelId,  excel_data.channel_name channelName,  excel_data.contain_express_status containExpressStatus,  excel_data.create_time createTime,  excel_data.deal_control dealControl,  excel_data.deal_status dealStatus,  excel_data.excel_path excelPath,  excel_data.excel_title_index excelTitleIndex,  excel_data.exists_error existsError,  excel_data.file_id fileId,  excel_data.file_name fileName,  excel_data.group_name groupName,  excel_data.id,  excel_data.key_mapping keyMapping,  excel_data.message_ssid messageSsid,  excel_data.order_line orderLine,  excel_data.payload payload,  excel_data.progress_step progressStep,  excel_data.select_sheet_name selectSheetName,  excel_data.step_deal_time stepDealTime,  excel_data.tenant_id tenantId,  excel_data.update_time updateTime,  excel_data.xls_batch_no xlsBatchNo  FROM wxorder_excel_data excel_data  WHERE excel_data.tenant_id=16",
			"SELECT  excel_data.xls_batch_no as 'xlsBatchNo', (select now()) as 'temp'  FROM wxorder_excel_data excel_data  WHERE excel_data.tenant_id=16"
		};
		
		for(int i=0; i<testSql.length; ++i) {
			String item = testSql[i];
			System.out.println("================testSql["+i+"]===============");
			try {
				String[] data = splitCoreSql(item);
				System.out.println(data[0]);
				System.out.println(data[1]);
			} catch (Exception e) {
				System.out.println(e.getMessage());
			}				
		}
		
//		String testSub = " asdf asdf   asdfaf";
//		System.out.println(subFirstStrEndWithBlank(testSub, 5));
//		System.out.println(subFirstStrEndWithBlank(testSub, 6));
//		System.out.println(subFirstStrEndWithBlank(testSub, 13));
	}
}