package frame.file.office.excel;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.transaction.Transaction;

import org.apache.commons.collections4.map.HashedMap;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFReader.SheetIterator;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import com.sun.mail.handlers.image_gif;
import com.sun.org.apache.bcel.internal.generic.NEW;

import frame.config.Configer;
import frame.data.Entity;
import frame.data.meta.EntityMeta;
import frame.file.AppendMode;
import frame.file.DeleteMode;
import frame.file.FileIO;
import frame.file.FileIOContext;
import frame.file.FileIOItem;
import frame.file.LoadError;
import frame.file.UploadResult;
import frame.file.office.FileIOTypeCode;
import frame.file.office.IOMappingItemRuntime;
import frame.persist.NamedSQL;
import frame.persist.SQLRunner;
import frame.upload.FileItem;
import frame.upload.FileUploadException;
import frame.upload.disk.DiskFileItemFactory;
import frame.upload.servlet.ServletFileUpload;

public class ExcelLoaders {
	
	private Logger logger;
	private UploadResult result;
	private HttpServletRequest request;
	private List<Entity> dataList;
    private static int MAX_CACHE_SIZE = 30 * 1024 * 1024;
	private static int MAX_FILE_SIZE = 100 * 1024 * 1024;
	private static int DEFAULT_UPLOAD_SIZE = 10000;
	
	public ExcelLoaders(HttpServletRequest request) {
		this.logger = LoggerFactory.getLogger(this.getClass());
		this.request = request;
		dataList = new ArrayList<Entity>();
	}

	public boolean load(UploadResult result, FileIO fileIO) throws Exception {
		this.result = result;
		boolean isSuccess = false;
		List<FileIOItem> importList = fileIO.getItemList(FileIOTypeCode.Import);
		
		for (FileIOItem fileIOItem : importList) {
			fileIO.execHandlers(result, "XXbefore");
			isSuccess = oneload(fileIOItem);
		}
		
		return isSuccess;
	}

	private boolean oneload(FileIOItem fileIOItem) throws Exception {
		//1. delete temp table data
		String toName = fileIOItem.getToName();
		NamedSQL namedSql = NamedSQL.getInstance("emptyTable");
		namedSql.setParam("tablename", toName);
		SQLRunner.execSQL(namedSql);
		
		List<FileItem> loadUploadFile = loadUploadFile(request);
        //2. import file
		boolean isEndFound = false;
		for (int i = 0; i < loadUploadFile.size(); i++) {
			FileItem fileItem = loadUploadFile.get(i);
			LoadError loadOneError = doLoadOneFile(result, fileItem, fileIOItem);
			
			if (loadOneError.equals(LoadError.SheetNotFound) && i < loadUploadFile.size() - 1) {
				isEndFound = false;
			}
			
			if ((i == loadUploadFile.size() - 1) && !isEndFound) {
				throw SheetNotFoundException(loadOneError.getMessage());
			}
			
			if (loadOneError.equals(LoadError.SQLRunError)) {
				throw SQLrunnerException(loadOneError.getMessage());
			}
			
		}
		
        return false;
	}
	
    private String transactionSheetName(String fromName) {
    	String sheetName = fromName;
    	int leftIndex = fromName.indexOf("[");
    	String sheetString = fromName.substring(0, leftIndex);
    	
    	if (sheetString.equalsIgnoreCase("*")) {
			sheetName = "rId";
		}
    	
    	int rightIndex = fromName.indexOf("]");
    	String noString = fromName.substring(leftIndex + 1, rightIndex);
    	
    	try {
    		Integer no = Integer.valueOf(noString);
    		sheetName += no;
		} catch (Exception e) {
			e.printStackTrace();
			throw new FileIOException("fileio 数据错误");
		}
    	
    	return sheetName;
	}

	private LoadError doLoadOneFile(UploadResult result, FileItem file, FileIOItem fileIOItem) {
    	 logger.info("正在导入文件: " + file.getName() + "...");
    	 LoadError loadError = null;
         //1. delete temp table data
         String toName = fileIOItem.getToName();
         NamedSQL namedSql = null;
		 try {
			namedSql = NamedSQL.getInstance("emptyTable");
			namedSql.setParam("tablename", toName);
			SQLRunner.execSQL(namedSql);
		 } catch (Exception e) {
			 e.printStackTrace();
			 loadError = LoadError.SQLRunError;
			 try {
				loadError.setMessage("SQL:" + namedSql.getSQLString());
			 } catch (Exception e1) {
				loadError.setMessage("SQL:" + namedSql.getName() + "-- not found");
				e1.printStackTrace();
			 }
			 return loadError;
		 }
         
         //2. load file
		 InputStream fileInputStream = null;
		 InputStream sheetInputStream = null;
         try {
        	 fileInputStream = file.getInputStream();
 			 OPCPackage pkg = OPCPackage.open(fileInputStream);  
 			 
 			 XSSFReader xssfReader = new XSSFReader(pkg);  
 			 SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();  
 	         StylesTable stylesTable = xssfReader.getStylesTable();  
 	         
 	         String fromName = fileIOItem.getFromName();
 	         String sheetName = FileIOItem.extractFileName(fromName) + FileIOItem.extractSheetName(fromName);
 	         sheetInputStream = xssfReader.getSheet(sheetName);  
 	         
 	         if (sheetInputStream.available() == 0) {
 	        	 //TODO 没找到本fileiotime对应的sheet  两个方向  多file中 后面file 找到了   还是说需要多个file都存在
 	        	 loadError = LoadError.SheetNotFound;
 	        	 loadError.setMessage("sheet :" + sheetName + " not match");
 	        	 return loadError;
			 }
 	         
 	         XMLReader parser = fetchSheetParser(sharedStringsTable, stylesTable);  
 	         InputSource sheetSource = new InputSource(sheetInputStream);
 	         parser.parse(sheetSource);
 	         
             fileIOItem.execHandlers(result);
         }
         catch (Exception e) {
         	logger.error(e.getMessage());
            result.fail("error_parseData", "文件解析错误： " + e.getMessage());
         }
         finally {
        	 if (fileInputStream != null) {
				try {
					fileInputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			 }
        	 if (sheetInputStream != null) {
        		 try {
        			 sheetInputStream.close();
				 } catch (IOException e) {
					 e.printStackTrace();
				 }
			}
         }
		return LoadError.Non;
	}
    
	private XMLReader fetchSheetParser(SharedStringsTable sharedStringsTable, StylesTable stylesTable) throws SAXException {
		 XMLReader parser =  XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");  
		 SheetHandler handler = new SheetHandler(sharedStringsTable, stylesTable){
			 	
				@Override
				protected void outputRow(String[] datas, int[] rowTypes, int rowIndex, FileIOItem fileIOItem) {
					Map<String, Integer> cloumnMap = null;
					if (rowIndex == 0) {
						cloumnMap = new HashedMap<String, Integer>();
					}
					else if (fileIOItem.getFromRowNo() > (rowIndex - 1)) {
						for(int i = 0; i < datas.length; i++) {
							String oneCloumn = datas[i];
							
							if(cloumnMap == null) {
								cloumnMap = new HashMap<String, Integer>();
							}
							
							cloumnMap.put(oneCloumn, i);
						}
					}
					else {
						//add one  row
						if (fileIOItem.getFromRowNo() + DEFAULT_UPLOAD_SIZE == rowIndex) {
							commit2TeslmpDb(dataList);
						}
						List<IOMappingItemRuntime> insertMappingRuntime = fileIOItem.getInsertMappingRuntime();
						EntityMeta toMeta = fileIOItem.getToMeta();
						Entity entity = new Entity(toMeta);
						
						for (IOMappingItemRuntime ioMappingItemRuntime : insertMappingRuntime) {
							String fromField = ioMappingItemRuntime.getFromField();
							String toField = ioMappingItemRuntime.getToField();
							if (cloumnMap == null) {
								throw new NoCloumnException("");
							}
							
							if(!cloumnMap.containsKey(fromField)) {
								throw new NoMatchFieldException(fromField);
							}
							
							Integer valueIdx = cloumnMap.get(fromField);
							String fieldValue = datas[valueIdx];
							entity.set(toField, fieldValue);
						}
						dataList.add(entity);
						
					}
					
				}
	        };
	        parser.setContentHandler(handler);  
	        
	        return parser;  
	}


	@SuppressWarnings("unchecked")
	private static List<FileItem> loadUploadFile(HttpServletRequest request) throws FileUploadException {
    	DiskFileItemFactory diskFileFactory = new DiskFileItemFactory();
		diskFileFactory.setSizeThreshold(MAX_CACHE_SIZE);
		diskFileFactory.setRepository(new File(Configer.getParam("repository")));

		ServletFileUpload fileUpload = new ServletFileUpload(diskFileFactory);
		fileUpload.setSizeMax(MAX_FILE_SIZE);
		return fileUpload.parseRequest(request);
	}
	
}