package jp.satomichan.nucalgen;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
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.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.AcCellType;
import jp.satomichan.nucalgen.addition.AcRow;
import jp.satomichan.nucalgen.addition.AdditionConfig;
import jp.satomichan.nucalgen.addition.AdditionUtil;

public class Nucalgen {
	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("groupsum").longOpt("with-group-sum").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());

		Map<String, String> namedAreaMap = new HashMap<String, String>();

		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);
			}


			//「栄養価計算」シート生成
			Sheet calcSheet = outputWorkbook.createSheet("栄養価計算");
			outputWorkbook.setSheetOrder("栄養価計算", 0);
			if(cmd.hasOption("set-protect")) {
				calcSheet.protectSheet("");
			}
			calcSheet.setColumnWidth(2, 10240);
			calcSheet.addMergedRegion(new CellRangeAddress(1, 2, 1, 1));

			CellStylePool csPool = new CellStylePool(outputWorkbook);

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

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

			//「栄養計算」行
			List<String> usedTableList = new ArrayList<String>();
			int rowIndex = 3;
			for(int i = rowIndex; i < lines + 3; i++,rowIndex++) {
				Row thisRow = calcSheet.createRow(rowIndex);

				//「食品名」
				thisRow.createCell(1).setCellStyle(csPool.getCellStyle("00000", false));
				thisRow.createCell(2).setCellFormula("IFERROR(VLOOKUP(B" + (rowIndex + 1) + ",成分表!$B$13:$BL$2500,3,FALSE),\"\")");
				thisRow.createCell(3).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()) {
							String cell = new CellReference(rowIndex, 4 + nch.indexOf(aAlias)).formatAsString();
							formula = formula.replaceAll("([^A-Za-z0-9_])" + aAlias + "([^A-Za-z0-9_])", "$1" + cell + "$2");
						}
						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());
				}

			}


			//摂取量 範囲を記憶
			String intakeArea = new CellReference(3, 3, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, 3, true, true).formatAsString();
			namedAreaMap.put("AREA_INTAKE", intakeArea);


			//「合計」行
			Row sumRow = calcSheet.createRow(rowIndex);
			sumRow.createCell(1).setCellValue("合計");
			calcSheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
			colIndex = COL_INDEX_START;
			for(NutritionColumn aColumn : nch.getNutritionColumnList()) {
				Cell thisCell = sumRow.createCell(colIndex);
				String sumTargetArea = new CellReference(3, colIndex, true, true).formatAsString() + ":" + new CellReference(rowIndex -1, colIndex, true, true).formatAsString();

				//範囲を記憶（alias あれば設定）
				if(aColumn.getAlias().length() > 0) {
					namedAreaMap.put("AREA_" + aColumn.getAlias(), sumTargetArea);

					if(aColumn.isUseSum()) {
						String sumArea = new CellReference(rowIndex, colIndex, true, true).formatAsString();
						namedAreaMap.put("SUM_" + aColumn.getAlias(), sumArea);
						//System.out.println("SUM_" + aColumn.getAlias() + " --- " +  sumArea);
					}
				}

				thisCell.setCellStyle(csPool.getCellStyle(aColumn.getFormat()));
				if(aColumn.isUseSum()) {
					thisCell.setCellFormula("SUM(" + sumTargetArea + ")");
				}
				colIndex++;
			}

			
			//「付加行」出力
			String[] additionOptionValues = cmd.getOptionValues("addition");
			if(additionOptionValues != null) {
				for(String aAdditionFileName : additionOptionValues) {
					rowIndex += 3;
					rowIndex = generateAddition(aAdditionFileName, calcSheet, csPool, rowIndex, namedAreaMap);
				}
			}

			//「食品群別摂取量」出力
			if(cmd.hasOption("with-group-sum")) {
				rowIndex += 3;
				rowIndex = generateGroupSum(calcSheet, csPool, rowIndex, namedAreaMap);
			}


			//未使用表シート削除
			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);
			calcSheet.setForceFormulaRecalculation(true);
			outputWorkbook.setSelectedTab(0);
			outputWorkbook.write(outputXlsxFile);
			outputWorkbook.close();

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


	 //群別摂取量
	private static int generateGroupSum(Sheet calcSheet, CellStylePool csPool, int rowIndex, Map<String,String> _namedAreaMap) {

		List<String> groupName = Arrays.asList("0", "穀類", "いも及びでん粉類", "砂糖及び甘味類", "豆類",
				"種実類", "野菜類", "果実類", "きのこ類", "藻類", "魚介類", "肉類", "卵類", "乳類",
				"油脂類", "菓子類", "し好飲料類", "調味料及び香辛料類", "調理加工食品類");



		Row groupRow = calcSheet.createRow(rowIndex);
		groupRow.createCell(1).setCellValue("食品群");
		groupRow.createCell(3).setCellValue("摂取量(g)");
		rowIndex++;

		for(int i = 1; i <= 18; i++,rowIndex++) {
			Row thisRow = calcSheet.createRow(rowIndex);
			thisRow.createCell(1).setCellValue(i);
			thisRow.createCell(2).setCellValue(groupName.get(i));
			Cell cCell = thisRow.createCell(3);
			cCell.setCellStyle(csPool.getCellStyle(""));
			//cCell.setCellFormula("SUMIF(AREA_GROUP, " + i + ", AREA_INTAKE)");
			cCell.setCellFormula("SUMIF(" + _namedAreaMap.get("AREA_GROUP") + ", " + i + ", " + _namedAreaMap.get("AREA_INTAKE") + ")");

			if(i == 6) {
				rowIndex++;
				thisRow = calcSheet.createRow(rowIndex);
				thisRow.createCell(2).setCellValue("うち　緑黄色野菜");
				Cell bcvCell = thisRow.createCell(3);
				bcvCell.setCellStyle(csPool.getCellStyle("0"));
				//bcvCell.setCellFormula("SUMIF(AREA_BRIGHT_COLORED_VEGETABLE, 1, AREA_INTAKE)");
				bcvCell.setCellFormula("SUMIF(" + _namedAreaMap.get("AREA_BRIGHT_COLORED_VEGETABLE") + ", 1, " + _namedAreaMap.get("AREA_INTAKE") + ")");
			}


		}

		return rowIndex;
	}


	//「付加行」生成
	private static int generateAddition(String fileName, Sheet calcSheet, CellStylePool csPool, int rowIndex,
																				Map<String,String> _namedAreaMap) {

		System.out.println("ADDITION : " + fileName);

		AdditionConfig ac = AdditionUtil.additionFileReader(new File(fileName));
		for(AcRow acRow : ac.getRows()) { //行ごとのループ
			Row thisRow = calcSheet.createRow(rowIndex);
			int colIndex = 0;
			
			for(AcCell acCell : acRow.getCells()) { //セルごとのループ
				Cell thisCell = thisRow.createCell(colIndex);
				
				//alias 「付加行」内の別名定義（制約：右方・下方のセルからしか参照できない）
				if(acCell.getAlias() != null) {
					_namedAreaMap.put(acCell.getAlias(), new CellReference(rowIndex, colIndex, true, true).formatAsString());
				}
				
				if(acCell.getCellType() == AcCellType.FORMULA) {
					String formula = "<" + acCell.getValue() + ">";
					for(Entry<String, String> keyValue : _namedAreaMap.entrySet()) {
						String k = keyValue.getKey();
						String v = keyValue.getValue();
						v = Matcher.quoteReplacement(v);
						formula = formula.replaceAll("([^A-Za-z0-9_])" + k + "([^A-Za-z0-9_])", "$1" + v + "$2");
						formula = formula.replaceAll("^<", "");
						formula = formula.replaceAll(">$", "");
					}
					thisCell.setCellFormula(formula);
				} else {
					thisCell.setCellValue(acCell.getValue());
				}
			
				
				colIndex++;
			} //セルごとのループ
			
			rowIndex++;
		} //行ごとのループ
		


		return rowIndex;
	}







}