package com.eversec.kit.dev;

import com.arangodb.ArangoCollection;
import com.arangodb.ArangoCursor;
import com.arangodb.ArangoDB;
import com.arangodb.ArangoDatabase;
import com.eversec.common.FileKit;
import com.eversec.common.JdbcKit;
import com.eversec.common.Kv;
import com.eversec.kit.creator.ICreator;
import org.junit.Test;

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import static java.util.Arrays.asList;

/**
 * export table example.
 * - export table to word.
 * - export to other.
 * @author: liangxianyou at 2018/10/8 10:55.
 */
public class ExportTable {

    private static String rootPath = FileKit.rootPath(ExportTable.class);

    /**
     * export table to word
     * 数据库表结构导出到word示例.
     * @throws SQLException
     * @throws IOException
     */
    @Test
    public void demo() throws SQLException, IOException {
        //load table ddl; gxbii_dev 为database， 连接的数据库修改 JdbcKit
        //List<Table> tables = JdbcKit.tables("gxbii_dev");

        List<Table> tables = ImportTable.tables();


        tables.forEach(t -> {
            System.out.println(t.getName() + " " + t.getComment());
        });

        //export to word
        FileKit.tplRender(new File(rootPath + "/libs/tpl/wordTpl.xml"), new File("target/tables.docx"), Kv.of("tables", tables));
    }

    /**
     * 导出工控数据库表结构到word
     *
     * @throws SQLException
     * @throws IOException
     */
    @Test
    public void tableToWord() throws SQLException, IOException {

        List<Table> tables = new ArrayList<>();

        //load gxbii_dev
        JdbcKit.tables("gxbii_dev").stream().filter(t ->
                !t.getName().startsWith("ct_") &&
                        !t.getName().startsWith("iplocation_") &&
                        !t.getName().endsWith("_old") &&
                        !t.getName().startsWith("Sheet")
        ).forEach(t -> {
            tables.add(t);
        });

        //load gxbii_cmd
        JdbcKit.tables("gxbii_cmd").stream().filter(t ->
                t.getName().startsWith("cmd_")
        ).forEach(t -> {
            tables.add(t);
        });

        /*tables.forEach(t->{
            System.out.println(t.getName()+ " " +t.getComment());
        });*/

        //export
        FileKit.tplRender(new File(rootPath + "/libs/tpl/wordTpl.xml"), new File("target/tables.docx"), Kv.of("tables", tables));

    }

    /**
     * 通过mysql 表创建 javaBean示例
     */
   /* @Test
    public void tableToBean() {
        try {
            Table2BeanKit beanKit = new Table2BeanKit();
            List<Table> tables = JdbcKit.tables("gxbii_dev", "task_export");
            tables.forEach(x -> {
                String str = beanKit.createByTable(x);
                try {
                    beanKit.createFile(str, ICreator.getClazzName(x.getName()));
                } catch (IOException e) {
                    e.printStackTrace();
                }
            });
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
*/
    /**
     * 导出数据库结构到excel表
     */
    @Test
    public void tableToExcel() {

    }

    //====================ArangoDB===========================
    private static ArangoDB arangoDb = new ArangoDB.Builder().host("120.24.230.60", 8529).user("root").password("root").build();
    private static ArangoDatabase dbDev = arangoDb.db("db_dev");
    private static ArangoCollection sysCols = dbDev.collection("sys_cols");
    private static ArangoCollection metaCols = dbDev.collection("meta_cols");

    @Test
    public void t() {
        //
        String aql = "for d in meta_cols\n" +
                "    return d";

        ArangoCursor<MetaTable> cursor = dbDev.query(aql, null, null, MetaTable.class);

        while (cursor.hasNext()) {
            MetaTable col = cursor.next();

            System.out.println(col);
            //System.out.println(map);
        }

    }

    /**
     * 将mysql 数据库表结构导入到 arangodb
     *
     * @throws SQLException
     */
    @Test
    public void tableToArango() throws SQLException {
        List<Table> redbbs = JdbcKit.tables("gxbii_dev");

        redbbs.forEach(x -> {
            List columns = new ArrayList();
            x.getColumns().forEach(c -> {
                columns.add(Kv.of("name", c.getName()).set("label", c.getComment()).set("type", c.getType()));
            });

            Kv doc = Kv.of("_key", x.getName()).set("name", x.getName()).set("comment", x.getComment()).set("item", columns);

            Map document = sysCols.getDocument(x.getName(), Map.class);

            if (document != null) {
                sysCols.updateDocument(x.getName(), doc);
            } else {
                sysCols.insertDocument(doc);
            }
            //System.out.println(doc);
        });
    }

    @Test
    public void excelToArango() {
        ICreator.sheetNames().forEach(k -> {
            try {
                List<Map> list = ICreator.readExcelSheet(k);
                //tables.add(toTable(list));
                Map doc = toCols(list);
                doc.put("sysPlatId", 4264801);

                String _key = doc.get("name") + "";
                doc.put("_key", _key);

                Map document = metaCols.getDocument(_key, Map.class);

                System.out.println(doc);
                if (document != null) {
                    metaCols.updateDocument(_key, doc);
                } else {
                    //metaCols.insertDocument(doc);
                }

                //System.out.println(doc);
            } catch (IOException e) {
                e.printStackTrace();
            }
        });

    }

    /**
     * 组装元数据
     */
    private Map toCols(List<Map> list) {

        Kv col = Kv.of();

        list.remove(1);//list[0] head info
        Map rowHead = list.remove(0);

        String comment = ImportTable.getComment(rowHead);//list[1] comment,
        String tableName = ImportTable.getTableName(rowHead);
        col.set("name", tableName).set("comment", comment);

        //所有字段
        List<Kv> items = new ArrayList<>();

        //展示的字段
        List<String> shows = new ArrayList<>();

        //编辑的字段
        List<String> edits = new ArrayList<>();

        //查询过滤用字段
        List<Kv> filters = new ArrayList<>();


        list.forEach(x -> {
            String field = x.get("field") + "";

            Kv item = Kv.of();
            item.set("name", field);
            item.set("label", x.get("remark1"));
            item.set("remark", x.get("remark2"));
            item.set("type", x.get("cate"));
            item.set("inType", x.get("tag"));
            item.set("inExt", x.get("selects"));
            items.add(item);


            if ("1".equals(x.get("column") + "")) {
                shows.add(field);
            }

            if ("1".equals(x.get("edit") + "")) {
                edits.add(field);
            }

            if (x.get("filter") != null && !"".equals(x.get("filter") + "")) {
                String filter = x.get("filter") + "";
                filter = filter.replace("1", "EQUAL");
                filter = filter.replace("!1", "NOTEQUAL");
                filter = filter.replace("like", "LIKE");

                filters.add(Kv.of("name", field).set("filterType", asList(filter.split(","))));
            }

        });

        col.set("items", items);
        col.set("shows", shows);
        col.set("edits", edits);
        col.set("filters", filters);
        return col;
    }

    @Test
    public void loadTables() {

    }


}
