package com.vci.server.query.refquery.utils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;

import com.vci.corba.query.data.RefValue;
import com.vci.omd.constants.BusinessConstants;
import com.vci.omd.constants.LinkConstants;
import com.vci.server.base.utility.OmdHelper;
import com.vci.server.base.utility.ServerServiceProvider;
import com.vci.server.cache.OMCacheProvider;
import com.vci.server.query.refquery.objects.FromItem;
import com.vci.server.query.refquery.objects.MetaData;
import com.vci.server.query.refquery.objects.PLMRefQueryPath;
import com.vci.server.query.refquery.objects.Sequence;
import com.vci.corba.common.VCIError;
import com.vci.corba.omd.ltm.LinkType;

public class PLMRefSQLHelper {
	public static String Space = " ";//
	
	private static String generateEachOfPathToSubSQL(PLMRefQueryPath refpath,
			List<FromItem> allfromitems) throws Throwable {
		List<FromItem> fromitems = getEachOfPathFromItems(refpath, allfromitems);
		Collections.sort(fromitems, new java.util.Comparator<FromItem>() {
			@Override
			public int compare(FromItem o1, FromItem o2) {
				return o1.getLevel().compareTo(o2.getLevel());
			}

		});
		return recursion(refpath, fromitems, 0);
	}

	private static String recursion(PLMRefQueryPath refpath,
			List<FromItem> fromitems, int index) throws Throwable {
		StringBuffer subSQL = new StringBuffer();
		String priorAlias = "";
		String curAlias = "";
		FromItem leafItem = fromitems.get(fromitems.size() - 1);
		List<String> absList = getAbsList(leafItem);
		String key = refpath.getPath().path.substring(
				refpath.getPath().path.lastIndexOf(".") + 1,
				refpath.getPath().path.length());
		for (int m = index; m < fromitems.size(); m++) {
			int level = fromitems.get(m).getLevel();
			priorAlias = curAlias;
			if (level == 1) {
				priorAlias = fromitems.get(m).getAlias();
				curAlias = fromitems.get(m).getAlias();
				continue;
			}
			if (level >= 2) {
				curAlias = fromitems.get(m).getAlias();
				priorAlias = fromitems.get(m - 1).getAlias();
				// leaf node
				if ((refpath.getLevel() - 1) == fromitems.get(m).getLevel()) {
					boolean existed = false;
					for(String ab : absList) {
						if(ab.equalsIgnoreCase(key)) {
							existed = true;
							break;
						}
					}
					StringBuffer sb = new StringBuffer();
					sb.append("(");
					sb.append(" Select ");
					if(existed) {
						sb.append(fromitems.get(m).getAlias()).append(".")
						.append(key).append(" as ");
					}else{
						sb.append("'' as ");
					}
					sb.append(fromitems.get(m).getAlias()).append("_").append(key);
					sb.append(" From ");
					// 业务类型
					if (fromitems.get(m).getMeta().isType()) {
						String btmName = fromitems.get(m).getMeta().getRefType().name;
						String tableName = getBtmTypeViewName(btmName);
						sb.append(tableName);
					} else {
						sb.append(OmdHelper.getLTTableName(
										fromitems.get(m).getMeta().getRefLink().name));
					}
					sb.append(Space).append(fromitems.get(m).getAlias());
					sb.append(" Where ");
					sb.append(curAlias)
							.append(".oid ")
							.append("=")
							.append(priorAlias)
							.append(".")
							.append(fromitems.get(m).getMeta()
									.getAttributeDef().name);
					sb.append(") as ").append(fromitems.get(m).getAlias())
							.append("_").append(key);
					return sb.toString();

				}

				subSQL.append(" ( Select ");
				String sql = recursion(refpath, fromitems, m + 1);
				subSQL.append(sql);
				subSQL.append(" From ");
				// 业务类型
				if (fromitems.get(m).getMeta().isType()) {
					subSQL.append(OmdHelper.getBTTableName(
							fromitems.get(m).getMeta().getRefType().name));
				} else {
					subSQL.append(OmdHelper.getLTTableName(
									fromitems.get(m).getMeta().getRefLink().name));
				}
				subSQL.append(Space).append(fromitems.get(m).getAlias());
				subSQL.append(" Where ");
				subSQL.append(curAlias)
						.append(".oid ")
						.append("=")
						.append(priorAlias)
						.append(".")
						.append(fromitems.get(m).getMeta().getAttributeDef().name);
				subSQL.append(") as ").append(leafItem.getAlias()).append("_")
						.append(key);
				return subSQL.toString();
			}
		}
		return subSQL.toString();

	}
	
	public static List<FromItem> generateFromItemList(PLMRefQueryPath[] refpaths) {
		Sequence seq = new Sequence("a_0");
		List<FromItem> fromitems = generateFromItems(refpaths, seq);
		return fromitems;
	}

	public static String generateSQL(PLMRefQueryPath[] refpaths) {
		Sequence seq = new Sequence("a_0");
		List<FromItem> fromitems = generateFromItems(refpaths, seq);
		return generateSQL(refpaths, fromitems);
	}
	
	public static String generateSQL(PLMRefQueryPath[] refpaths, List<FromItem> fromitems) {
		String From = generateRootFrom(fromitems);
		String Where = generateRootWhere(fromitems);
		String Select = "";
		try {
			Select = generateRootSelect(refpaths, fromitems);
		} catch (Throwable e) {
			e.printStackTrace();
		}
		StringBuffer sb = new StringBuffer();
		sb.append(Select);
		sb.append(Space);
		sb.append(From);
		sb.append(Space).append(Where);

		String refSQL = sb.toString();
		return refSQL;
	}

	private static String generateRootFrom(List<FromItem> fromitems) {
		StringBuffer sb = new StringBuffer();
		sb.append(" From ");
		for (FromItem item : fromitems) {
			if (item.getLevel() == 1) {
				// 判断是业务对象还是link对象
				if (item.getMeta().isType()) {
					String btmName = item.getMeta().getRefType().name;
					String tableName = getBtmTypeViewName(btmName);
					sb.append(tableName);
					
				} else {
					sb.append(OmdHelper.getLTTableName(
							item.getMeta().getRefLink().name));
				}
				sb.append(Space + item.getAlias());
				sb.append(", ");
				break;
			}
		}
		return sb.toString().substring(0, sb.lastIndexOf(", "));
	}
	
	/**
	 * 获取业务类型对应的视图名称，如果视图不存在则返回表名
	 * @param btmName
	 * @return
	 */
	private static String getBtmTypeViewName(String btmName) {
		String[] childNames = null;
		try {
			childNames = ServerServiceProvider.getOMDService().getBTMService().getChildrenNames(btmName);
		} catch (VCIError e) {
			e.printStackTrace();
		}
		String tableName = "";
		if (childNames != null && childNames.length != 0) {
			tableName = OmdHelper.getBtViewName(btmName);
		} else {
			tableName = OmdHelper.getBTTableName(btmName);
		}
		return tableName;
	}

	public static String[] generateResultSetMetaData(PLMRefQueryPath[] refpaths) {
		Sequence seq = new Sequence("a_0");
		List<FromItem> fromitems = generateFromItems(refpaths, seq);
		String[] columNames = new String[refpaths.length + 1];
		FromItem first = null;
		for (FromItem fitem : fromitems) {
			if (fitem.getLevel() == 1) {
				first = fitem;
				break;
			}
		}

		StringBuffer sb = new StringBuffer();
		sb.append(first.getAlias()).append("_oid");

		columNames[0] = sb.toString().toUpperCase();

		for (int i = 0; i < refpaths.length; i++) {
			String path = refpaths[i].getPath().path;
			String key = path.substring(path.lastIndexOf(".") + 1,
					path.length());
			String alias = "";
			if (refpaths[i].getLevel() == 1) {
				alias = getFromItemAlias(fromitems, path);
				columNames[i + 1] = (alias + "_" + "OID").toUpperCase();
			} else {
				alias = getFromItemAlias(fromitems,
						path.substring(0, path.lastIndexOf(".")));
			}

			if (refpaths[i].getLevel() != 1) {
				columNames[i + 1] = (alias + "_" + key).toUpperCase();
			}

		}
		List<String> columns = new ArrayList<String>();
		for (int j = 0; j < columNames.length; j++) {
			if (!columns.contains(columNames[j]))
				columns.add(columNames[j]);
		}
		return columns.toArray(new String[columns.size()]);
	}

	private static String generateRootSelect(PLMRefQueryPath[] refpaths,
			List<FromItem> fromitems) throws Throwable {
		FromItem first = null;
		for (FromItem fitem : fromitems) {
			if (fitem.getLevel() == 1) {
				first = fitem;
				break;
			}
		}
		StringBuffer sb = new StringBuffer();
		sb.append("Select ");
		sb.append(first.getAlias()).append(".oid as ").append(first.getAlias())
				.append("_oid ");
		sb.append(", ");
		for (int i = 0; i < refpaths.length; i++) {
			String path = refpaths[i].getPath().path;
			String key = path.substring(path.lastIndexOf(".") + 1,
					path.length());
			String alias = "";
			if (refpaths[i].getLevel() == 1) {
				alias = getFromItemAlias(fromitems, path);
			} else {
				alias = getFromItemAlias(fromitems,
						path.substring(0, path.lastIndexOf(".")));
			}
			FromItem currentFitem = getFromItem(fromitems, alias);
			List<String> absList = getAbsList(currentFitem);
			if (currentFitem.getLevel() >= 2) {

				String subsql = generateEachOfPathToSubSQL(refpaths[i],
						fromitems);
				sb.append(subsql);
			} else {
				//参照的第一层不拼接SQL
				if (refpaths[i].getLevel() != 1) {
					boolean existed = false;
					for(String ab : absList) {
						if(ab.equalsIgnoreCase(key)) {
							existed = true;
							break;
						}
					}
					if(existed) {
						sb.append(alias).append(".").append(key).append(" as ");
					}else{
						sb.append("'' as ");
					}
					sb.append(alias).append("_").append(key);
				}
			}
			if (refpaths[i].getLevel() != 1) {
				refpaths[i].setSelectKey(alias + "_" + key);
			}
			//参照的第一层不拼接SQL
			if (refpaths[i].getLevel() != 1) {
				sb.append(", ");
			}
		}
		return sb.substring(0, sb.lastIndexOf(", "));
	}

	private static List<String> getAbsList(FromItem item) throws Throwable{
		List<String> absList = new ArrayList<String>();
		String[] LinkSysAbs = null;
		String[] BtmSysAbs = null;
		LinkSysAbs = LinkConstants.SELECT_CONSTANTS;
		BtmSysAbs = BusinessConstants.SELECT_CONSTANTS;
		List<String> linkSysAbsList = Arrays.asList(LinkSysAbs);
		List<String> BtmSysAbsList = Arrays.asList(BtmSysAbs);
		
		String name = null;
		String[] abs = null;
		if (item.getMeta().isType()) {
			name = item.getMeta().getRefType().name;
			//abs = ServerServiceProvider.getOMDService().getBTMService().getBtmApNameArray(name);
			abs = OMCacheProvider.getBTAttributes(name);
			absList.addAll(BtmSysAbsList);
		} else {
			name = item.getMeta().getRefLink().name;
			//LinkType linkType = ServerServiceProvider.getOMDService().getLinkTypeService().getLinkType(name);
			LinkType linkType = OMCacheProvider.getLinkType(name);
			abs = linkType.attributes;
			absList.addAll(linkSysAbsList);
		}
		absList.addAll(Arrays.asList(abs));
		
		return absList;
	}
	
	private static String getFromItemAlias(List<FromItem> fromitems,
			String indentifier) {
		String alias = "";
		for (FromItem fitem : fromitems) {
			if (fitem.getIdentifier().equals(indentifier)) {
				alias = fitem.getAlias();
				break;
			}
		}
		return alias;

	}

	private static FromItem getFromItem(List<FromItem> fromitems, String alias) {
		for (FromItem fitem : fromitems) {
			if (fitem.getAlias().equals(alias)) {
				return fitem;
				// break;
			}
		}
		return null;
	}

	private static String generateRootWhere(List<FromItem> fromitems) {
		StringBuffer sb = new StringBuffer();
		sb.append(" Where ");
		for (FromItem fItem : fromitems) {
			int level = fItem.getLevel();

			if (level == 1) {
				sb.append(fItem.getAlias()
						+ ".oid in "
						+ getSqlInConditon(fItem.getPath().getPath().values, "'"));
				// sb.append("order by instr('"
				// + getSqlInConditon(fItem.getPath().getPath().values, "")
				// + "'," + fItem.getAlias() + ".oid)");

			}

		}
		return sb.toString();
	}

	private static String getSqlInConditon(RefValue[] oids, String invertedComma) {
		StringBuilder in = new StringBuilder();
		String value = "";
		if (oids != null && oids.length > 0) {
			in.append("(");
			for (int i = 0; i < oids.length; i++) {
				in.append(invertedComma);
				in.append(oids[i].value);
				in.append(invertedComma);
				in.append(",");
			}
			value = in.substring(0, in.length() - 1);
			value += ")";
		}
		return value;
	}

	private static List<FromItem> getEachOfPathFromItems(
			PLMRefQueryPath refpath, List<FromItem> allFormItems) {
		List<FromItem> fromItems = new ArrayList<FromItem>();
		int i = refpath.getPath().path.split("\\.").length;
		for (int j = i - 2; j >= 1; j--) {
			String identifier = refpath.getlevel(j);
			for (FromItem f : allFormItems) {
				if (f.getIdentifier().equals(identifier)) {
					fromItems.add(f);
					break;
				}
			}
		}
		return fromItems;
	}

	private static List<FromItem> generateFromItems(PLMRefQueryPath[] refpaths,
			Sequence seq) {
		int maxPath = getMaxPathLevel(refpaths);
		List<FromItem> fromItems = new ArrayList<FromItem>();
		MetaData first = null;
		PLMRefQueryPath firstRefPath = null;
		for (PLMRefQueryPath refpathsindex : refpaths) {
			int level = refpathsindex.getPath().path.split("\\.").length - 1;
			if (level == 1) {
				firstRefPath = refpathsindex;
				break;
			}
		}
		for (int i = 0; i < refpaths.length; i++) {

			if (first == null) {
				first = refpaths[i].getLevel(1);
				FromItem item = new FromItem();

				item.setMeta(first);
				String alias = seq.getNextSequence();
				item.setAlias(alias);
				item.setLevel(1);
				item.setIdentifier(firstRefPath.getlevel(1));
				item.setPath(firstRefPath);
				fromItems.add(item);
			}
			int n = 2;
			while (n <= maxPath) {
				MetaData second = refpaths[i].getLevel(n);
				String cur = seq.getSequence();
				if (second != null) {
					FromItem item = new FromItem();
					item.setMeta(second);
					String alias = seq.getNextSequence();
					item.setAlias(alias);
					item.setLevel(n);
					item.setPath(refpaths[i]);
					item.setIdentifier(refpaths[i].getlevel(n));
					if (!fromItems.contains(item)) {
						fromItems.add(item);
					} else {
						seq.setSequence(cur);
					}
					n++;
				} else {
					break;
				}
			}
		}
		return fromItems;
	}

	private static int getMaxPathLevel(PLMRefQueryPath[] refPaths) {
		int max = 0;
		for (PLMRefQueryPath path : refPaths) {
			if (path.getLevel() > max) {
				max = path.getLevel();
			}
		}
		for (PLMRefQueryPath path : refPaths) {
			path.setMaxpath(max);
		}
		return max;

	}

	public static void main(String[] args) {
		Sequence seq = new Sequence("a_0");
		int i = 0;
		while (i < 260) {
			i++;
			//System.out.println(seq.getNextSequence());
		}

	}

}