package com.ruoyi.imports;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DateUtil;
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;

public class MyExcelDbImportUtils {

	public static void main(String[] args) {

		// 数据库定义文件
//		String filePath="C:\\workspace\\myworkspace2022\\jad-authing\\doc\\db20221120.xlsx";
//		String filePath = "E:\\workspace\\workspace2023\\bill-manager\\doc\\db\\数据库表设计-核算.xlsx";
		String filePath = "D:\\9fu\\业务\\云南省知识产权数据统计系统\\设计\\云南省知识产权数据统计系统表设计\\数据整理模块表设计-新.xlsx";
//		String filePath = "D:\\9fu\\业务\\云南省知识产权数据统计系统\\设计\\云南省知识产权数据统计系统表设计\\数据整理模块\\专利统计模块\\3-高价值专利统计模块表设计.xlsx";
		// 从excel读取内容
		List<List<String>> excelData = importExcel("ipc分类", filePath);//

		// 转换成表模型
		List<TableModel> tmList = toTableModelList(excelData);

		// 转换成建表语句
		List<String> sqlList = toSql(tmList);

		
		for (String sql : sqlList) {
			System.out.println(sql);
		}
		System.out.println("解析完成，一共" + tmList.size() + "张表");
//		for (TableModel tm : tmList) {
//			System.out.println(tm.name);
//		}
//		System.out.println("ok");

	}

	/**
	 * 转换成建表语句
	 * 
	 * @param tmList
	 * @return
	 */
	private static List<String> toSql(List<TableModel> tmList) {

		List<String> sqlList = new ArrayList<String>();

		for (TableModel tm : tmList) {
			sqlList.add(tm.toSql());
		}

		return sqlList;
	}

	/**
	 * 转换成表模型
	 * 
	 * @param excelData
	 * @return
	 */
	private static List<TableModel> toTableModelList(List<List<String>> excelData) {
		List<TableModel> tableList = new ArrayList<TableModel>();

		TableModel tm = null;
		for (int i = 0; i < excelData.size(); i++) {// 逐行循环
			List<String> cells = excelData.get(i);

			if (cells.size() < 2) {
				continue;
			}

			if ("表名".equals(cells.get(0))) {// 遇到一个表
				tm = new TableModel();
				tm.name = cells.get(1);
				if(tm.name!=null && tm.name.trim().endsWith("表")) {
					tm.name=tm.name.trim();
					tm.name=tm.name.substring(0,tm.name.length()-1);
				}
				
				tm.comment = cells.get(3);
				if(tm.comment!=null && tm.comment.trim().endsWith("表")) {
					tm.comment=tm.comment.trim();
					tm.comment=tm.comment.substring(0,tm.comment.length()-1);
				}
				
				
				tm.columns = new ArrayList<ColumnModel>();
				
				tableList.add(tm);
				continue;
			}

			if ("名称".equals(cells.get(0))) {
				continue;
			}

			if (cells.get(0) == null || "".equals(cells.get(0))) {
				continue;
			}
			ColumnModel cm = new ColumnModel();
			cm.name = cells.get(0).trim().toLowerCase();
			cm.type = cells.get(1);
			if (cells.size() >= 3) {
				cm.length = cells.get(2);
			}
			if (cells.size() >= 4) {
				cm.comment = cells.get(3);
			}
			
			if (cells.size() >= 5) {
				cm.defaultVal = cells.get(4);
			}
			tm.columns.add(cm);
		}

		return tableList;
	}

	/**
	 * 表模型定义
	 *
	 */
	private static class TableModel {
		String name;
		String comment;
		List<ColumnModel> columns;

		String toSql() {
			StringBuffer sb = new StringBuffer();
			sb.append("DROP TABLE IF EXISTS `" + name + "`;");
			sb.append("\n");
			sb.append("CREATE TABLE `" + name + "`  (");
			sb.append("\n");
			for (ColumnModel cm : columns) {
				if ("id".equals(cm.name)) {
					sb.append("`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',");
					sb.append("\n");
				} else {
					String type = cm.type;
					if ("date".equals(cm.type)) {
						type = "varchar";
					}
					sb.append("`").append(cm.name).append("` ").append(type);
					if (cm.length != null && !"".equals(cm.length.trim())) {
						sb.append("(").append(cm.length).append(")");
					} else if ("date".equals(cm.type)) {
						sb.append("(").append("10").append(")");
					}

					if (cm.defaultVal != null && !"".equals(cm.defaultVal.trim())) {
						sb.append(" default '").append(cm.defaultVal.trim()).append("'");
					}

					sb.append(" COMMENT ");
					if (cm.comment != null && !"".equals(cm.comment.trim())) {
						sb.append("'").append(cm.comment).append("'");
					} else {
						sb.append("'").append(cm.name).append("'");
					}
					sb.append(",");
					sb.append("\n");
				}
			}

			sb.append("PRIMARY KEY (`id`)");
			sb.append("\n");

//			 COMMENT = '用户信息表'
			String comment2 = (comment == null || "".equals(comment.trim())) ? name : comment;
			sb.append(")  COMMENT = '").append(comment2).append("' ;");
			sb.append("\n");
			return sb.toString();
		}
	}

	/**
	 * 列模型定义
	 *
	 */
	private static class ColumnModel {
		String name;
		String type;
		String length;
		String comment;
		String defaultVal;
	}

	/**
	 * 从excel中读内容
	 * 
	 * @param sheetName
	 * @param FilePath
	 * @return
	 */
	public static List<List<String>> importExcel(String sheetName, String FilePath) {
		List<List<String>> list = new ArrayList();
		InputStream in = null;
		Workbook wb = null;

		try {
			try {
				in = new FileInputStream(new File(FilePath));
				wb = WorkbookFactory.create(in);
			} catch (Exception e) {
				throw new RuntimeException("加载Workbook失败" + e.getMessage(), e);
			}
			Sheet sheet = StringUtils.isNotEmpty(sheetName) ? wb.getSheet(sheetName) : wb.getSheetAt(0);
			if (sheet == null) {
				throw new RuntimeException("文件sheet不存在");
			}
			boolean isXSSFWorkbook = !(wb instanceof HSSFWorkbook);
			if (!isXSSFWorkbook) {
				throw new RuntimeException("只支持导入2007及以上的excel文件");
			}

			// 获取后最后一行的下标
			int rows = sheet.getLastRowNum();
			if (rows <= 0) {
				return null;
			}
			for (int r = 0; r < rows; r++) {
				Row row = sheet.getRow(r);
				if (row == null) {
					continue;
				}
				List<String> cellList = new ArrayList<String>();
				list.add(cellList);
				for (int i = 0; i < row.getPhysicalNumberOfCells(); i++) {
					Cell cell = row.getCell(i);
					if (cell != null) {
						Object value = getCellValue(row, i);
						cellList.add(value == null ? "" : value.toString());
					} else {
						cellList.add("");
					}
				}
			}
		} finally {
			if (in != null) {
				try {
					in.close();
				} catch (IOException e) {
				}
				in = null;
			}
		}

		return list;
	}

	/**
	 * 获取单元格值
	 * 
	 * @param row
	 * @param column
	 * @return
	 */
	public static Object getCellValue(Row row, int column) {
		if (row == null) {
			return row;
		}
		Object val = "";
		try {
			Cell cell = row.getCell(column);
			if (cell != null) {
				if (cell.getCellType() == CellType.NUMERIC || cell.getCellType() == CellType.FORMULA) {
					val = cell.getNumericCellValue();
					if (DateUtil.isCellDateFormatted(cell)) {
						val = DateUtil.getJavaDate((Double) val); // POI Excel 日期格式转换
					} else {
						if ((Double) val % 1 != 0) {
							val = new BigDecimal(val.toString());
						} else {
							val = new DecimalFormat("0").format(val);
						}
					}
				} else if (cell.getCellType() == CellType.STRING) {
					val = cell.getStringCellValue();
				} else if (cell.getCellType() == CellType.BOOLEAN) {
					val = cell.getBooleanCellValue();
				} else if (cell.getCellType() == CellType.ERROR) {
					val = cell.getErrorCellValue();
				}

			}
		} catch (Exception e) {
			return val;
		}
		return val;
	}

}
