package jp.satomichan.nucalgen;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map.Entry;
import java.util.regex.Matcher;

import org.apache.commons.cli.CommandLine;
import org.apache.commons.cli.CommandLineParser;
import org.apache.commons.cli.DefaultParser;
import org.apache.commons.cli.Option;
import org.apache.commons.cli.Options;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellReference;

import jp.satomichan.nucalgen.addition.AcCell;
import jp.satomichan.nucalgen.addition.AcRow;
import jp.satomichan.nucalgen.addition.AdditionConfig;
import jp.satomichan.nucalgen.addition.AdditionUtil;

public class Nucalgen {
	//開始位置　最初の食品の、最初の栄養成分の位置 (E4セル)
	private static final int ROW_INDEX_START = 3;
	private static final int COL_INDEX_START = 4;

	public static void main(String[] args) {
		//コマンドライン・オプション読み込み
		Options options = new Options();
		options.addOption(Option.builder("s").required().hasArg().longOpt("std-food-comp-table").build());
		options.addOption(Option.builder("c").required().hasArg().longOpt("columns").build());
		options.addOption(Option.builder("o").required().hasArg().longOpt("output").build());
		options.addOption(Option.builder("l").required().hasArg().longOpt("lines").build());
		options.addOption(Option.builder("add").hasArgs().longOpt("addition").build());
		options.addOption(Option.builder("sheets").hasArgs().build());
		options.addOption(Option.builder("bright").hasArg().longOpt("bright-colored-vegetables-list").build());
		options.addOption(Option.builder("protect").longOpt("set-protect").build());
		options.addOption(Option.builder("processed").longOpt("use-processed-table").build());

		NamedAreaStore namedArea = NamedAreaStore.getInstance();

		try {

			//コマンドライン引数 解析
			CommandLineParser parser = new DefaultParser();
			CommandLine cmd = parser.parse(options, args);
			final String mextStdFoodCompTableFileName = cmd.getOptionValue("std-food-comp-table");
			final String columnsXmlFileName = cmd.getOptionValue("columns");
			final String outputXlsxFileName = cmd.getOptionValue("output");
			final int lines = Integer.parseInt(cmd.getOptionValue("lines"));

			//コンフィグ （列設定） 読み込み
			NutritionColumnHolder nch = new NutritionColumnHolder(columnsXmlFileName);

			//Book生成
			Workbook outputWorkbook = WorkbookFactory.create(new FileInputStream(mextStdFoodCompTableFileName));

			if(cmd.hasOption("use-processed-table") == false) {
				//成分表 変換
				MextStdFoodCompTable stdCompTable = new MextStdFoodCompTable(cmd.getOptionValue("bright-colored-vegetables-list"));
				stdCompTable.processInto(outputWorkbook);
			}


			//「栄養価計算」シート生成
			CellStylePool csPool = new CellStylePool(outputWorkbook);
			List<String> usedTableList = new ArrayList<String>();
			List<String> sheetNameList = new ArrayList<String>();
			
			
			String[] sheets = cmd.getOptionValues("sheets");
			if(sheets != null && sheets.length >= 2) {
				//表が複数
				for(int si = 0; si < sheets.length - 1; si++) {
					final String sheetName =  sheets[si];
					generateCalculationSheet(outputWorkbook, sheetName, namedArea, cmd.hasOption("set-protect"), cmd.getOptionValues("addition"), lines, nch, csPool, usedTableList);
					outputWorkbook.setSheetOrder(sheetName, si);
					sheetNameList.add(sheetName);
				}
				final String sumSheetName =  sheets[sheets.length - 1];
				generateSumSheet(outputWorkbook, sumSheetName, sheetNameList, namedArea, cmd.hasOption("set-protect"), cmd.getOptionValues("addition"), lines, nch, csPool, usedTableList);
				outputWorkbook.setSheetOrder(sumSheetName, sheets.length - 1);

			}else {
				//表がひとつ
				final String sheetName = (sheets != null) ? sheets[0] : "栄養価計算";
				generateCalculationSheet(outputWorkbook, sheetName, namedArea, cmd.hasOption("set-protect"), cmd.getOptionValues("addition"), lines, nch, csPool, usedTableList);
				outputWorkbook.setSheetOrder(sheetName, 0);
				sheetNameList.add(sheetName);
			}
			
			
			//未使用表シート削除
			for(int si = outputWorkbook.getNumberOfSheets() - 1 ; si >= 1 ; si--) {
				String sheetName = outputWorkbook.getSheetName(si);
				boolean used = false;
				for(String usedTable : usedTableList) {
					if(usedTable.equals(sheetName)) {
						used = true;
					}
				}
				if(!used) {
					outputWorkbook.removeSheetAt(si);
				}
			}
			
			//ブック出力
			FileOutputStream outputXlsxFile = new FileOutputStream(outputXlsxFileName);
			outputWorkbook.setActiveSheet(0);
			
			outputWorkbook.setSelectedTab(0);
			outputWorkbook.write(outputXlsxFile);
			outputWorkbook.close();

		} catch (Exception e) {
			// TODO 自動生成された catch ブロック
			e.printStackTrace();
		}
	}








	//「栄養価計算」シート生成
	private static void generateCalculationSheet(Workbook book, String sheetName, NamedAreaStore namedArea, boolean setProtect,
			String[] additionOptionValues, final int lines,
			NutritionColumnHolder nch, CellStylePool csPool, List<String> usedTableList) {

		
		Sheet calcSheet = book.createSheet(sheetName);

		if(setProtect) {
			calcSheet.protectSheet("");
		}
		calcSheet.setColumnWidth(COL_INDEX_START -2, 10240); //C「食品名」列
		calcSheet.addMergedRegion(new CellRangeAddress(ROW_INDEX_START -2, ROW_INDEX_START -1, 
				                   COL_INDEX_START -3, COL_INDEX_START -3));  //B2:B3「食品番号」セル


		//「タイトル」行
		Row titleRow = calcSheet.createRow(ROW_INDEX_START -2);
		titleRow.createCell(COL_INDEX_START -3).setCellValue("食品番号");
		titleRow.createCell(COL_INDEX_START -2).setCellValue("食品名");
		titleRow.createCell(COL_INDEX_START -1).setCellValue("摂取量");
		int colIndex = COL_INDEX_START;
		for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
			titleRow.createCell(colIndex).setCellValue(aColumn.getDispName());
			colIndex++;
		}

		//「単位」行
		Row unitRow = calcSheet.createRow(ROW_INDEX_START -1);
		unitRow.createCell(COL_INDEX_START -2).setCellValue("単位");
		unitRow.createCell(COL_INDEX_START -1).setCellValue("g");
		colIndex = COL_INDEX_START;
		for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
			unitRow.createCell(colIndex).setCellValue(aColumn.getUnit());
			colIndex++;
		}

		//「栄養計算」行
		int rowIndex = ROW_INDEX_START;
		for(int i = rowIndex; i < lines + ROW_INDEX_START; i++,rowIndex++) {
			Row thisRow = calcSheet.createRow(rowIndex);

			//「食品名」
			thisRow.createCell(COL_INDEX_START -3).setCellStyle(csPool.getCellStyle("00000", false));
			thisRow.createCell(COL_INDEX_START -2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) +
					                             ",成分表!$B$13:$BL$2500,3,FALSE),\"\")");
			thisRow.createCell(COL_INDEX_START -1).setCellStyle(csPool.getCellStyle("", false));

			colIndex = COL_INDEX_START;
			for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
				Cell thisCell = thisRow.createCell(colIndex);
				thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));

				if(aColumn.getFormula().length() >= 1) {
					//「計算式」列
					String formula = aColumn.getFormula();
					for(String aAlias : nch.getNutritionAliasList()) {
						formula = replaceFormula(formula, aAlias, rowIndex, COL_INDEX_START + nch.indexOf(aAlias));
					}
					thisCell.setCellFormula(formula);


				} else {
					//通常の栄養素の列
					String div100 = aColumn.isUseRawValue() ? "" :  "/ 100 * $D" + (rowIndex + 1);
					thisCell.setCellFormula("IFERROR(VLOOKUP($B" + (rowIndex + 1) + "," + aColumn.getTable() + 
							                "!$B$13:$BL$2500,MATCH(\"" + aColumn.getName() + "\"," + 
							                aColumn.getTable() + "!$B$12:$BL$12,0),FALSE) " + div100 + ",\"\")");
				}

				colIndex++;

				usedTableList.add(aColumn.getTable());
				usedTableList.add(sheetName);
			}

		}


		//摂取量 範囲を記憶
		namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1);


		//「合計」行
		Row sumRow = calcSheet.createRow(rowIndex);
		sumRow.createCell(COL_INDEX_START -3).setCellValue("合計");
		calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, COL_INDEX_START -3, COL_INDEX_START -2));
		
		//「摂取量」合計
		Cell intakeSumCell = sumRow.createCell(COL_INDEX_START -1);
		intakeSumCell.setCellFormula("SUM(" + namedArea.load("AREA_INTAKE") + ")");
		namedArea.save("SUM_INTAKE", intakeSumCell);
		
		colIndex = COL_INDEX_START;
		for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
			Cell thisCell = sumRow.createCell(colIndex);

			//範囲を記憶
			String areaName = aColumn.getAlias().length() >= 1 ? ("AREA_" + aColumn.getAlias()) : ("AREAID_" + aColumn.getName());
			namedArea.save(areaName, ROW_INDEX_START, colIndex, rowIndex -1, colIndex);

			if(aColumn.isUseSum()) {
				String sumName = aColumn.getAlias().length() >= 1 ? ("SUM_" + aColumn.getAlias()) : ("SUMID_" + aColumn.getName());
				namedArea.save(sumName, thisCell);
			}

			thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
			if(aColumn.isUseSum()) {
				thisCell.setCellFormula("SUM(" + 
			                            NamedAreaStore.getAreaString(ROW_INDEX_START, colIndex, rowIndex -1, colIndex) +
			                            ")");
			}
			colIndex++;
		}

		
		//「付加行」出力
		if(additionOptionValues != null) {
			for(String aAdditionFileName : additionOptionValues) {
				rowIndex += 2;
				rowIndex = generateAddition(aAdditionFileName, calcSheet, null, csPool, rowIndex, namedArea);
			}
		}
		
		calcSheet.setForceFormulaRecalculation(true);

	}


	
	
	
	//集計用シート生成
	private static void generateSumSheet(Workbook book, String sheetName, List<String> sheetNameList, NamedAreaStore namedArea, boolean setProtect,
			String[] additionOptionValues, final int lines,
			NutritionColumnHolder nch, CellStylePool csPool, List<String> usedTableList) {

		usedTableList.add(sheetName);
		Sheet sheet = book.createSheet(sheetName);

		if(setProtect) {
			sheet.protectSheet("");
		}
		
		//「表」
		sheet.setColumnWidth(COL_INDEX_START -2, 10240); //C
		sheet.addMergedRegion(new CellRangeAddress(ROW_INDEX_START -2, ROW_INDEX_START -1, 
				                   COL_INDEX_START -3, COL_INDEX_START -2));  //B2:C3「表」セル


		//「タイトル」行
		Row titleRow = sheet.createRow(ROW_INDEX_START -2);
		titleRow.createCell(COL_INDEX_START -3).setCellValue("表");
		titleRow.createCell(COL_INDEX_START -1).setCellValue("摂取量");
		int colIndex = COL_INDEX_START;
		for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
			titleRow.createCell(colIndex).setCellValue(aColumn.getDispName());
			colIndex++;
		}

		//「単位」行
		Row unitRow = sheet.createRow(ROW_INDEX_START -1);
		unitRow.createCell(COL_INDEX_START -2).setCellValue("単位");
		unitRow.createCell(COL_INDEX_START -1).setCellValue("g");
		colIndex = COL_INDEX_START;
		for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
			unitRow.createCell(colIndex).setCellValue(aColumn.getUnit());
			colIndex++;
		}

		//表ごとの小計
		int rowIndex = ROW_INDEX_START;
		for(String aSheetName : sheetNameList) {
			Row thisRow = sheet.createRow(rowIndex);

			sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 
	                   COL_INDEX_START -3, COL_INDEX_START -2));  //B:C「表」セル

			//「表」列
			thisRow.createCell(COL_INDEX_START -3).setCellValue(aSheetName);
			
			//「摂取量」列
			thisRow.createCell(COL_INDEX_START -1).setCellFormula(aSheetName + "!" + namedArea.load("SUM_INTAKE"));
			

			//「表ごとの小計」列
			colIndex = COL_INDEX_START;
			for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
				Cell thisCell = thisRow.createCell(colIndex);
				thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
				if(aColumn.isUseSum()) {
					String sumName = aColumn.getAlias().length() >= 1 ? ("SUM_" + aColumn.getAlias()) : ("SUMID_" + aColumn.getName());
					String formula = aSheetName + "!" + namedArea.load(sumName);
					thisCell.setCellFormula(formula);
				}

				colIndex++;

			}

			rowIndex++;
		}


		//摂取量 範囲を記憶
		namedArea.save("AREA_INTAKE", ROW_INDEX_START, COL_INDEX_START -1, rowIndex -1, COL_INDEX_START -1);

		//「合計」行
		Row sumRow = sheet.createRow(rowIndex);
		sumRow.createCell(COL_INDEX_START -3).setCellValue("合計");
		sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, COL_INDEX_START -3, COL_INDEX_START -2));

		Cell intakeSumCell = sumRow.createCell(COL_INDEX_START -1);
		intakeSumCell.setCellFormula("SUM(" + namedArea.load("AREA_INTAKE") + ")");

		
		colIndex = COL_INDEX_START;
		for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
			Cell thisCell = sumRow.createCell(colIndex);

			//範囲を記憶
			String areaName = aColumn.getAlias().length() >= 1 ? ("AREA_" + aColumn.getAlias()) : ("AREAID_" + aColumn.getName());
			namedArea.save(areaName, ROW_INDEX_START, colIndex, rowIndex -1, colIndex);

			if(aColumn.isUseSum()) {
				String sumName = aColumn.getAlias().length() >= 1 ? ("SUM_" + aColumn.getAlias()) : ("SUMID_" + aColumn.getName());
				namedArea.save(sumName, thisCell);
			}


			thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
			if(aColumn.isUseSum()) {
				thisCell.setCellFormula("SUM(" + 
			                            NamedAreaStore.getAreaString(ROW_INDEX_START, colIndex, rowIndex -1, colIndex) +
			                            ")");
			}
			colIndex++;
		}

		
		//「付加行」出力
		if(additionOptionValues != null) {
			for(String aAdditionFileName : additionOptionValues) {
				rowIndex += 2;
				rowIndex = generateAddition(aAdditionFileName, sheet, sheetNameList, csPool, rowIndex, namedArea);
			}
		}
		
		sheet.setForceFormulaRecalculation(true);

	}
	
	
	
	
	//「付加行」生成
	private static int generateAddition(String fileName, Sheet calcSheet, List<String> sheetNameList, CellStylePool csPool, int rowIndex,
			                              NamedAreaStore namedArea) {

		AdditionConfig ac = AdditionUtil.additionFileReader(new File(fileName));
		int cellCounter = 0;
		for(AcRow acRow : ac.getRows()) { //行ごとのループ
			Row thisRow = calcSheet.createRow(rowIndex);
			int colIndex = 0;
			
			for(AcCell acCell : acRow.getCells()) { //セルごとのループ
				Cell thisCell = thisRow.createCell(colIndex);
				
				cellCounter++;
				if(sheetNameList == null) {
					String areaName = "ADDITION_" + fileName + "_" + cellCounter;
					namedArea.save(areaName, rowIndex, colIndex);
				}
				
				//alias 「付加行」内の別名定義（制約：右方・下方のセルからしか参照できない）
				if(acCell.getAlias() != null) {
					namedArea.save(acCell.getAlias(), rowIndex, colIndex);
				}
				
				//formula 計算式
				if(acCell.getFormula() != null){
					String formula = acCell.getFormula();
					
					if(sheetNameList != null && formula.indexOf("AREA_") != -1) {
						//集計シート && 名前付き範囲 AREA_ が対象に含まれる
						List<String> sumTarget = new ArrayList<String>();
						for(String aSheetName : sheetNameList) {
							String areaName = "ADDITION_" + fileName + "_" + cellCounter;
							sumTarget.add(aSheetName + "!" + namedArea.load(areaName)); 
						}
						formula = "SUM(" + StringUtils.join(sumTarget, ",") + ")";
						
					}else {
						//それ以外
						for(Entry<String, String> keyValue : namedArea.entrySet()) {
							String k = keyValue.getKey();
							String v = keyValue.getValue();
						
							formula = replaceFormula(formula, k, v);
						}
					}
					thisCell.setCellFormula(formula);
				
				//value そのままの値
				} else if(acCell.getValue() != null) {
					thisCell.setCellValue(acCell.getValue());
				
				//formula でも value でもない
				} else {
					//
				}
			
				
				colIndex++;
			} //セルごとのループ
			
			rowIndex++;
		} //行ごとのループ
		


		return rowIndex - 1;
	}



	//計算式中の文字列（名前付き範囲）置換
	private static String replaceFormula(String formula, String target, String replacement) {
		formula = "<" + formula + ">";
		replacement = Matcher.quoteReplacement(replacement);
		formula = formula.replaceAll("([^A-Za-z0-9_])" + target + "([^A-Za-z0-9_])", "$1" + replacement + "$2");
		formula = formula.replaceAll("^<", "");
		formula = formula.replaceAll(">$", "");
		return formula;
	}
	
	//計算式中の 文字列（名前付き範囲）→セル座標 置換
	private static String replaceFormula(String formula, String target, int cellRow, int cellCol) {
		return replaceFormula(formula, target, new CellReference(cellRow, cellCol).formatAsString());
	}






}