package com.wmx.db2doc.controller;

import com.wmx.db2doc.util.WordTableUtils;
import org.apache.commons.collections4.ListUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.annotation.Resource;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
import java.io.IOException;
import java.net.URLEncoder;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.*;

/**
 * 数据库控制层
 *
 * @author wangMaoXiong
 * @version 1.0
 * @date 2020/12/30 14:16
 */
@Controller
public class DatabaseController {
    /**
     * Spring Boot 默认已经配置好了数据源，程序员可以直接 DI 注入然后使用即可
     */
    @Resource
    DataSource dataSource;

    @Resource
    private JdbcTemplate jdbcTemplate;

    /**
     * 跳转到数据库以及表信息展示页面
     * http://localhost:8317/db/toTableShow
     *
     * @return
     */
    @GetMapping("/db/toTableShow")
    public String toTableShow(Model model) {
        try {
            Connection connection = dataSource.getConnection();
            List<Map<String, Object>> allTable = this.findAllTable();

            model.addAttribute("driverVersion", connection.getMetaData().getDatabaseProductVersion());
            model.addAttribute("url", connection.getMetaData().getURL());
            model.addAttribute("userName", connection.getMetaData().getUserName());
            model.addAttribute("allTable", allTable);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return "tableShow";
    }

    /**
     * 将数据库表结构导出为 Word 文档
     *  http://localhost:8317/db/db2Doc
     *
     * @param tableNames
     * @return
     */
    @PostMapping("db/db2Doc")
    @ResponseBody
    public void db2Doc(@RequestParam String tableNames, HttpServletResponse response) {
        ServletOutputStream outputStream = null;
        try {
            //设置返回类型，必须对文件名称进行编码，否则中午容易乱码
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("Oracle数据库文档.docx", "UTF-8"));

            outputStream = response.getOutputStream();
            String[] headerTitle = {"序号", "名称", "类型", "长度", "描述", "是否可为空"};
            List<String> tableNameList = Arrays.asList(tableNames.split(","));
            System.out.println("导出表个数=" + tableNameList.size());

            List<Map<String, Object>> mapList = this.findTableByName(tableNameList);
            Map<String, List<Map<String, Object>>> list2Map = this.list2Map(mapList);

            XWPFDocument xwpfDocument = new XWPFDocument();
            WordTableUtils.addCustomHeadingStyle(xwpfDocument, "TS1", 0);
            int count = 1;
            for (Map.Entry<String, List<Map<String, Object>>> entry : list2Map.entrySet()) {
                String key = entry.getKey();
                //设置标题
                XWPFParagraph paragraph = xwpfDocument.createParagraph();
                paragraph.setStyle("TS1");
                XWPFRun xwpfRun = paragraph.createRun();
                xwpfRun.setBold(true);
                xwpfRun.setFontSize(22);
                xwpfRun.setText((count++) + "、" + key);

                //设置表格
                List<Map<String, Object>> value = entry.getValue();
                WordTableUtils.createSimpleTable(xwpfDocument, headerTitle, value);
                xwpfDocument.createParagraph();
            }
            xwpfDocument.write(outputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.flush();
                    outputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 根据表面查询字段信息
     *
     * @param tableName
     * @return
     */
    private List<Map<String, Object>> findTableByName(List<String> tableName) {
        List<Map<String, Object>> returnDataList = new ArrayList<>();
        //每50个表查询一次
        List<List<String>> partition = ListUtils.partition(tableName, 50);
        for (List<String> list : partition) {
            String join = "'" + StringUtils.join(list, "','") + "'";
            String querySql = "select t.TABLE_NAME || ' ' || (select a.COMMENTS from user_tab_comments a where t.TABLE_NAME=a.TABLE_NAME) as TABLE_NAME, t.COLUMN_ID,t.COLUMN_NAME, t.DATA_TYPE, " +
                    "t.DATA_LENGTH, u.COMMENTS, t.NULLABLE " +
                    " from User_Tab_Cols t,User_Col_Comments u where t.TABLE_NAME=u.TABLE_NAME and t.COLUMN_NAME=u.COLUMN_NAME " +
                    "and t.TABLE_NAME in(" + join + ") order by t.TABLE_NAME,t.COLUMN_ID";
            List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(querySql);
            returnDataList.addAll(queryForList);
        }
        return returnDataList;
    }

    /**
     * 查询可以导出word文档的表名
     * http://localhost:8317/db/findAllTable
     *
     * @return
     */
    private List<Map<String, Object>> findAllTable() {
        String querySql = "select distinct t.table_name from user_tab_cols t order by t.table_name";
        List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(querySql);
        return queryForList;
    }

    private Map<String, List<Map<String, Object>>> list2Map(List<Map<String, Object>> dataList) {
        Map<String, List<Map<String, Object>>> finalDataMap = new LinkedHashMap<>();
        for (Map<String, Object> map : dataList) {
            String table_name = (String) map.get("TABLE_NAME");
            map.remove("TABLE_NAME");
            if (finalDataMap.containsKey(table_name)) {
                finalDataMap.get(table_name).add(map);
            } else {
                List<Map<String, Object>> temp = new LinkedList<>();
                temp.add(map);
                finalDataMap.put(table_name, temp);
            }
        }
        return finalDataMap;
    }
}