package com.longruan.ark.core.magic.controller

import com.longruan.ark.core.db.DBTable
import com.longruan.ark.core.db.SqlPlus
import com.longruan.ark.core.exception.ArkException
import com.longruan.ark.core.magic.service.MetadataService
import com.longruan.ark.core.magic.trait.RuntimeTrait
import com.longruan.ark.core.model.Join
import com.longruan.ark.core.runtime.RuntimeTool
import com.longruan.ark.core.excel.ExcelBuilder
import com.longruan.ark.util.ListTool
import com.longruan.ark.util.StringTool
import groovy.json.JsonSlurper
import groovy.sql.GroovyRowResult
import groovy.util.logging.Slf4j
import io.vertx.core.eventbus.EventBus
import org.apache.poi.hssf.usermodel.*
import org.apache.poi.ss.usermodel.BorderStyle
import org.apache.poi.ss.usermodel.CellStyle
import org.apache.poi.ss.usermodel.HorizontalAlignment
import org.apache.poi.ss.usermodel.VerticalAlignment
import org.apache.poi.ss.util.CellRangeAddress
import org.apache.poi.ss.util.RegionUtil
import org.springframework.beans.factory.InitializingBean
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.http.HttpHeaders
import org.springframework.http.HttpStatus
import org.springframework.http.MediaType
import org.springframework.http.ResponseEntity
import org.springframework.web.bind.annotation.*
import org.springframework.web.multipart.MultipartFile

import javax.servlet.http.HttpServletRequest
import java.sql.Timestamp

@Slf4j
class GeneralController extends AuthController implements InitializingBean, RuntimeTrait {

    @Autowired
    public SqlPlus db

    @Autowired
    MetadataService metadataService

    @Autowired
    EventBus eventBus

    protected String moduleName

    String getMainTable() {
        return _mainTable
    }

    void setMainTable(String mainTable) {
        this._mainTable = mainTable
    }

    private String _mainTable

    String getShadowList() {
        return _shadowList
    }

    void setShadowList(String _shadowList) {
        this._shadowList = _shadowList
    }

    private String _shadowList

    String getShadowCard() {
        return _shadowCard
    }

    void setShadowCard(String shadowCard) {
        this._shadowCard = shadowCard
    }

    private String _shadowCard
    public String defaultSort
    public int defaultTreeMaxLevel

    List<String> getChildrenTables() {
        return _childrenTables
    }

    void setChildrenTables(List<String> childrenTables) {
        this._childrenTables = childrenTables
    }

    public List<String> _childrenTables = []
    public HashMap<String, String> foreignKeys = [:]   //如有必要，需要指定外键关联字段，比如UserController中的子表

    public boolean autoChildrenTableDelete = false
    public boolean autoBroadcast = true
    public boolean autoJoin = false

    public List<Join> joins = []

    static {
        GroovyRowResult.metaClass.getMap = {
            def map = [:]
            delegate.keySet().each {
                map[it] = delegate[it]
            }
            return map
        }
    }

    Join joinUserinfo(String selfColumn = 'id_at_auth_user__create') {
        return new Join("auth_userinfo", selfColumn)
                .addShowColumn("v_name")
    }

    @GetMapping("/info")
    def info() {
        [
                mainTable     : mainTable,
                shadowViewList: shadowList,
                shadowViewCard: shadowCard,
                childrenTables: childrenTables,
                joins         : joins
        ]
    }

    @PostMapping("/add")
    String add(@RequestBody def body) {
        if (body instanceof List) {
            throw new ArkException("添加接口，参数不接受数组")
        }

        if (!(body instanceof Map)) {
            body = body.getProperties()
        }

        if (body instanceof GroovyRowResult) {
            body = body.getMap()
        }

        metadataService.testModuleRules(apiAccess.id_at_app_module, body) //保存时验证数据校验规则

        String id

        if (body.id_at_me__parent == '') body.id_at_me__parent = null

        def user = RuntimeTool.runtimeUser

        db.withTransaction {
            buildDefaultValue(body, user)
            id = db.insertItem(body, mainTable)
            body.each { String k, v ->
                if (v instanceof List && v.size() > 0 && isChildTableIsExsits(k)) {//存在要保存的子表
                    def foreignKey = getForeignKey(k)
                    v.each {
                        it[foreignKey] = id
                        buildDefaultValue(it, user)
                    }

                    db.insertList(v, k)
                }
            }

        }

        broadcastChanged("insert", id)

        return id
    }

    def buildDefaultValue(def body, def user = null) {
        if (user == null) user = RuntimeTool.runtimeUser

        if (!body.id_at_auth_user__create) {
            body.id_at_auth_user__create = user.id
        }
        if (!body.id_at_auth_user__perm) {
            body.id_at_auth_user__perm = user.id
        }
        if (!body.id_at_orga_department__perm) {
            body.id_at_orga_department__perm = user.departmentID
        }
        if (!body.id_at_orga_organization__perm) {
            body.id_at_orga_organization__perm = user.organizationID
        }
        if (!body.v_code_at_areacode) {
            body.v_code_at_areacode = user.areacode
        }

        if (!body.containsKey("id_at_app_module") && mainTable != 'app_menu') {
            body.id_at_app_module = apiAccess?.id_at_app_module
        }
        if (!body.id_at_areacode) {
            body.id_at_areacode = user.areacodeID
        } else if (!body.v_code_at_areacode && db.tables[mainTable].contains("v_code_at_areacode")) {
            body.v_code_at_areacode = db.firstRow("select v_code from areacode where id = ?", body.id_at_areacode)?.v_code
        }

        if (!body.t_create) {
            body.t_create = new Date()
        }

        body.t_update = body.t_create
    }

    @PostMapping("/adds")
    List adds(@RequestBody def record) {
        if (!(record instanceof List)) {
            throw new ArkException("批量添加接口，参数只接受数组")
        }

        def id = []
        db.withTransaction {
            record.each {
                id << add(it)
            }
        }

        return id
    }

    @RequestMapping(value = "/edit/{id}", method = [RequestMethod.PUT, RequestMethod.POST])
    String edit(@PathVariable String id, @RequestBody def record) {

        if (!(record instanceof Map)) {
            record = record.getProperties()
        }

        if (record instanceof GroovyRowResult) {
            record = record.getMap()
        }


        record.id = id
        metadataService.testModuleRules(apiAccess.id_at_app_module, record) //保存时验证数据校验规则
        if (!record.id_at_auth_user__update) {
            record.id_at_auth_user__update = RuntimeTool.runtimeUser.id
        }
        record.t_update = new Date()
        db.withTransaction {
            db.updateItem(record, mainTable)
            record.each { String k, v ->
                if (v instanceof List && v.size() > 0 && isChildTableIsExsits(k)) {//存在要修改的子表
                    editChildTable(id, k, v, null, null)
                }
            }
        }

        broadcastChanged("edit", id)

    }

    @PostMapping("/edit")
    String edits(@RequestBody def list) {
        def ids = list.collect { it.id }
        checkMultiAction(ids)
        db.withTransaction {
            list.each {
                this.edit(it.id, it)
            }
        }
    }

    @RequestMapping(value = "/delete/{id}", method = [RequestMethod.GET, RequestMethod.DELETE])
    def delete(@PathVariable String id) {
        if (db.getTables().get(mainTable).contains("id_at_me__parent")
                && db.firstRow("select 1 from $mainTable where id_at_me__parent = ? limit 1".toString(), id)) {
            // 说明自己是棵树，则判断有下级节点，就不允许删除
            throw new ArkException("存在下级节点，不允许删除。")
        }
        def value
        db.withTransaction {
            value = db.deleteItemByID(id, mainTable)
            if (autoChildrenTableDelete) {
                childrenTables.each {
                    def foreignKey = getForeignKey(it)
                    db.executeUpdate("delete from $it where $foreignKey = ? ".toString(), id)
                }
            }
        }
        request.setAttribute("delete", value)

        broadcastChanged("delete", id)

        return value
    }

    @RequestMapping(value = "/delete", method = [RequestMethod.POST, RequestMethod.DELETE])
    def delete(HttpServletRequest request, @RequestBody def ids) {

        checkMultiAction(ids)
        def deleteList = []
        db.withTransaction {
            ids.each {
                deleteList << delete(it)
            }
        }

        request.setAttribute("delete", deleteList)
        return deleteList
    }

    def checkMultiAction(List ids, String action = null) {
        ids.each {
            authService.checkDataAuth(apiAccess.clone(
                    action: action,
                    dataID: it))
        }
    }

    @GetMapping("/reference/{id}")
    def reference(@PathVariable String id, @RequestParam(required = false) Boolean useShadowList, @RequestParam(required = false) Boolean showChindren) {
        view(id, useShadowList, showChindren)
    }

    @GetMapping("/view/{id}")
    def view(@PathVariable String id, @RequestParam(required = false) Boolean useShadowList, @RequestParam(required = false) Boolean showChindren) {
        def table = mainTable
        if (useShadowList == null) useShadowList = true
        def sql
        if (StringTool.isNotBlank(shadowCard)) {
            sql = "select * from $shadowCard where id = ?"
        } else if (useShadowList && StringTool.isNotBlank(shadowList)) {
            sql = "select * from $shadowList where id = ?"
        } else {
            sql = """
${buildSelectStarSql(table, joins)} where id = ?
"""
        }

        def mainRow = db.firstRow(sql.toString(), id)

        if (showChindren && childrenTables.size() > 0) {
            childrenTables.each {
                mainRow[it] = getChildTable(id, it, null, null, null, null, null, null, null, null, true)
            }
        }

        return mainRow
    }

    def tree(
            String treeRootID = null,
            Boolean showMeInTree = true,
            Integer treeMaxLevel = Integer.MAX_VALUE) {
        list(
                null, null, false, null, null,
                true,
                treeRootID,
                showMeInTree,
                false,
                null,
                treeMaxLevel
        )
    }

    def list(Integer page,
             Integer size,
             Boolean noPage,
             String condition,
             String sort,
             Boolean tree,
             String treeRootID,
             Boolean showMeInTree,
             Boolean isBoxTree,
             String treeLabelField,
             Integer treeMaxLevel,
             String query = null,
             String selectPlus = "",
             Boolean isSkipCount = false
    ) {

        page = page ?: 1
        size = size ?: 100

        db.checkSafeSqlSegment(selectPlus)

        def authCondition = ""
        if (request != null) {
            def api = apiAccess

            def _id_at_app_module = metadataService.getModuleIdByAlias(moduleName)

            if (_id_at_app_module) { // 有模块
                //说明该api是内部调用，因此要走module自己的权限
                if (api.module != moduleName) {
                    api = api.clone(
                            module: moduleName,
                            id_at_app_module: _id_at_app_module,
                            action: 'view'
                    )
                }
                authCondition = authService.buildCondition(api)
            }

        }

        def table = mainTable
        def isShadow = false
        if (StringTool.isNotBlank(shadowList)) {
            isShadow = true
        }

        sort = sort ?: defaultSort
        treeMaxLevel = treeMaxLevel ?: defaultTreeMaxLevel

        DBTable dbTable = db.tables.get(table)

        if (dbTable && StringTool.isBlank(sort) && dbTable.contains("n_order") && !isShadow) {
            sort = "n_order:d"
        }

        if (tree) {
            if (!isShadow && !dbTable.findColumn("id_at_me__parent")) {
                throw ArkException.notATree(table)
            }
            noPage = true
            if (StringTool.isBlank(sort)) {
                sort = null
            }

            treeMaxLevel = treeMaxLevel ?: Integer.MAX_VALUE
        }

        condition = condition ? " and " + db.getSafeSqlSegment(condition) : ""

        def sql

        if (isShadow) {
//            sql = new StringBuilder("select * $selectPlus from $shadowList where 1=1 $authCondition $condition")
//            sql = new StringBuilder("select * $selectPlus from $shadowList where 1=1 $authCondition $condition")
            sql = new StringBuilder("${buildSelectStarSql(shadowList, joins)} where 1=1 $authCondition $condition")
        }
//        else if (tree) {
//            sql = new StringBuilder("select * from  $table where 1=1 $authCondition $condition")
//        }
        else {
            sql = new StringBuilder("${buildSelectStarSql(table, joins)} where 1=1 $authCondition $condition")
        }

        def params = []
        List queryList
        List sortList

        if (StringTool.isNotBlank(query)) {
            queryList = new JsonSlurper().parseText(query).q
            sortList = new JsonSlurper().parseText(query).s
        }

        //控制查询条件
        buildQuery(queryList, sql, params)

        //控制排序
        if (ListTool.isNotBlank(sortList)) {
            sort = sortList.collect {
                it.f + ":" + it.o + ":" + it.n
            }.join(",")
        }

        if (StringTool.isNotBlank(sort)) {
            buildSort(sort, sql)
        }

        //控制分页
        if (!noPage) {//启用分页
            sql.append " offset ? limit ? "
            params << (page - 1) * size
            params << size
        }

        def rows
        try {
            rows = db.rows(sql.toString(), params)
        } catch (e) {
            throw ArkException.sqlSelectFail(e, sql.toString())
        }

        if (tree) {
            return buildTree(rows, treeRootID, showMeInTree, treeLabelField, treeMaxLevel, isBoxTree)
        } else {
            StringBuilder lengthSql
            def lengthParams = []
            if (isShadow) {
                lengthSql = new StringBuilder("select count(*) as num from $shadowList where 1=1 $authCondition $condition")
            } else {
                lengthSql = new StringBuilder("${buildSelectStarSql(table, joins, true)} where 1=1 $authCondition $condition")
            }

            buildQuery(queryList, lengthSql, lengthParams)

            def result = [
                    list: rows,
                    page: page,
                    size: size
            ]

            if (!isSkipCount) {
                if (lengthParams.size() > 0) {
                    result.length = db.firstRow(lengthSql.toString(), lengthParams).num
                } else {
                    result.length = db.firstRow(lengthSql.toString()).num
                }
            }


            if (noPage) {
                result.remove("page")
                result.remove("size")
            }
            return result
        }

    }

    List quickList(String condition) {
        list(null, null, true, condition, null, false, null, null, null, null, null).list
    }

    def buildQuery(List queryList, StringBuilder sql, List params) {
        if (ListTool.isBlank(queryList)) return

        List querySql = []
        queryList.each {
            String _sql = ""
            String field = it.f
            String symbol = it.op ?: '='
            def value = it.v
            if (!StringTool.isSafe(field)) throw ArkException.sqlUnSafe(field)

            _sql += " $field "
            if (value instanceof List && symbol == 'in') { //传入的是数组
                _sql += " in (${value.collect { "?" }.join(",")}) "
                params.addAll(value)
            } else if (symbol in ['=', '>', '<', '>=', '<=', '<>', '!=']) {

                def type = db.tables.get(mainTable)?.get(field)?.dataType

                if (type == 'varchar') {
                    _sql += " $symbol ? "
                } else if (value ==~ /\d{4}-\d{2}-\d{2}/) {
                    _sql += " $symbol ? ::date "
                } else if (value ==~ /\d{4}-\d{2}-\d{2} [\d:]+/) {
                    _sql += " $symbol ? ::timestamp "
                } else {
                    _sql += " $symbol ? "
                }

                params << value
            } else if (symbol == 'like') {
                _sql += " ::varchar like ? "
                params << value
            } else if (symbol == 'is') {
                _sql += " isnull "
            } else if (symbol == 'nn') {
                _sql += " is not null "
            } else {
                throw ArkException.sqlUnSafe(symbol)
            }

            querySql << _sql
        }
        if (querySql.size() > 0) {
            sql.append " and ( ${querySql.join(" and ")} )"
        }
    }

    /**
     * 标准查询
     * @param page 页码
     * @param size 分页大小
     * @param noPage 是否不分页
     * @param condition 查询条件
     * @param sort 排序字段，如 age:d,name,birthday:d,  :d代表降序（desc），默认升序，
     * 如果要指定null的位置，可以age:d:nf 则null在最前，age:d:nl 则null在最后，
     * 升序情况下可以通过age::nf指定null位置
     * @param tree 是否树状显示，树状显示时，分页相关参数无效
     * @param treeRootID 指定根节点
     * @param showMeInTree 对于指定的根节点是否显示（与treeRootID配合用）
     * @param isBoxTree 是否统一树行格式
     * @param treeLabelField 渲染label属性对应的字典，配合isBoxTree（为true时）使用
     * @param treeMaxLevel 显示最大的层级
     * @return
     */
    @GetMapping("/view")
    def viewList(@RequestParam(required = false) Integer page,
                 @RequestParam(required = false) Integer size,
                 @RequestParam(required = false) Boolean noPage,
                 @RequestParam(required = false) String condition,
                 @RequestParam(required = false) String sort,
                 @RequestParam(required = false) Boolean tree,
                 @RequestParam(required = false) String treeRootID,
                 @RequestParam(required = false) Boolean showMeInTree,
                 @RequestParam(required = false) Boolean isBoxTree,
                 @RequestParam(required = false) String treeLabelField,
                 @RequestParam(required = false) Integer treeMaxLevel,
                 @RequestParam(required = false) String query,
                 @RequestParam(required = false) Boolean isSkipCount
    ) {
        isSkipCount = isSkipCount ?: false
        this.list(page, size, noPage, condition, sort, tree, treeRootID, showMeInTree, isBoxTree, treeLabelField, treeMaxLevel, query, "", isSkipCount)
    }

    @PostMapping("/view")
    def viewListPost(@RequestBody body) {
        this.viewList(
                body.page as Integer,
                body.size as Integer,
                body.noPage as Boolean,
                body.condition as String,
                body.sort as String,
                body.tree as Boolean,
                body.treeRootID as String,
                body.showMeInTree as Boolean,
                body.isBoxTree as Boolean,
                body.treeLabelField as String,
                body.treeMaxLevel as Integer,
                body.query as String,
                body.isSkipCount as Boolean
        )
    }

    @GetMapping("/reference")
    def referenceList(@RequestParam(required = false) Integer page,
                      @RequestParam(required = false) Integer size,
                      @RequestParam(required = false) Boolean noPage,
                      @RequestParam(required = false) String condition,
                      @RequestParam(required = false) String sort,
                      @RequestParam(required = false) Boolean tree,
                      @RequestParam(required = false) String treeRootID,
                      @RequestParam(required = false) Boolean showMeInTree,
                      @RequestParam(required = false) Boolean isBoxTree,
                      @RequestParam(required = false) String treeLabelField,
                      @RequestParam(required = false) Integer treeMaxLevel,
                      @RequestParam(required = false) String query,
                      @RequestParam(required = false) Boolean isSkipCount
    ) {
        this.viewList(page, size, noPage, condition, sort, tree, treeRootID, showMeInTree, isBoxTree, treeLabelField, treeMaxLevel, query, isSkipCount)
    }

    @PostMapping("/reference")
    def referenceListPost(@RequestBody body) {
        this.viewListPost(body)
    }

    /**
     * 获取子表数据单条（1对1关系）
     * @param id
     * @param childTable
     * */
    @GetMapping(value = "/view/{id}/child/{childTable}")
    def getChildTableOnly(@PathVariable String id, @PathVariable String childTable, @RequestParam(required = false) Boolean plus) {
        checkChildTableIsExsits(childTable)
        def foreignKey = getForeignKey(childTable)

        def sql = "select * from ${childTable} where ${foreignKey} = ?"
        if (plus) {
            sql = "${buildSelectStarSql(childTable, [], false, true)} where ${foreignKey} = ?"
        }
        db.firstRow(sql.toString(), [id])
    }

    /**
     * 获取子表数据
     * @param id
     * @param childTable
     * @param record
     * @param showMeInTree 在tree里面是否显示自己，只有treeRootID有值时有效
     * */
    @RequestMapping(value = "/view/{id}/children/{childTable}", method = [RequestMethod.GET, RequestMethod.PUT])
    List getChildTable(@PathVariable String id, @PathVariable String childTable,
                       @RequestParam(required = false) String condition,
                       @RequestParam(required = false) String sort,
                       @RequestParam(required = false) Boolean tree,
                       @RequestParam(required = false) String treeRootID,
                       @RequestParam(required = false) Boolean showMeInTree,
                       @RequestParam(required = false) Boolean isBoxTree,
                       @RequestParam(required = false) String treeLabelField,
                       @RequestParam(required = false) Integer treeMaxLevel,
                       @RequestParam(required = false) Boolean plus) {
        checkChildTableIsExsits(childTable)

        condition = condition ? " and " + db.getSafeSqlSegment(condition) : ""

        def foreignKey = getForeignKey(childTable)

        def sql = new StringBuilder("select * from ${childTable} where ${foreignKey} = ? $condition")
        if (plus) {
            sql = new StringBuilder("${buildSelectStarSql(childTable, [], false, true)} where ${foreignKey} = ? $condition")
        }

        def params = [id]

        //控制排序
        if (sort) {
            buildSort(sort, sql)
        }


        def rows = db.rows(sql.toString(), params)

        if (tree) {
            treeMaxLevel = treeMaxLevel ?: Integer.MAX_VALUE
            return buildTree(rows, treeRootID, showMeInTree, treeLabelField, treeMaxLevel, isBoxTree)
        }

        return rows
    }


    /**
     * 修改子表，批量保存模式---动态增删改，单条模式--仅新增
     * @param id
     * @param childTable
     * @param record
     * @param skipDelete 为true时不会删除原有数据
     */
    @RequestMapping(value = "/edit/{id}/children/{childTable}", method = [RequestMethod.POST, RequestMethod.PUT])
    void editChildTable(@PathVariable String id,
                        @PathVariable String childTable,
                        @RequestBody def record,
                        @RequestParam(required = false) Boolean skipDelete,
                        @RequestParam(required = false) String condition
    ) {
        checkChildTableIsExsits(childTable)
        skipDelete = skipDelete ?: false

        def foreignKey = getForeignKey(childTable)
        if (record instanceof List) { // 子表批量保存
            condition = condition ? " and " + db.getSafeSqlSegment(condition) : ""
            List list = record
            def ids = list.findAll { it.id != null }.collect { it.id } << UUID.randomUUID().toString()
            // 加入一个随机uuid，用来给not in （） 占位
            def placeholder = ids.collect { "?" }.join(",")
            db.withTransaction {
                if (!skipDelete) { //跳过删除模式，不考虑删除问题
                    db.execute("delete from ${childTable} where $foreignKey = ? $condition and id not in (${placeholder})".toString(),
                            [id, ids].flatten())
                }


                list.each {
                    it[foreignKey] = id
                    if (it.id) {
                        if (!it.id_at_auth_user__update) {
                            it.id_at_auth_user__update = RuntimeTool.runtimeUser.id
                        }
                        it.t_update = new Date()
                    } else {
                        buildDefaultValue(it)
                    }
                    db.saveItem(it, childTable)
                }
            }
        } else {
            record[foreignKey] = id
            if (record.id) {
                if (!record.id_at_auth_user__update) {
                    record.id_at_auth_user__update = RuntimeTool.runtimeUser.id
                }
                record.t_update = new Date()
            } else {
                buildDefaultValue(record)
            }

            db.saveItem(record, childTable)
        }
    }

    /**
     * 查看子表数据(单条)
     */
    @RequestMapping(value = "/view/{id}/children/{childTable}/{childID}", method = [RequestMethod.GET, RequestMethod.PUT])
    def viewChildTable(@PathVariable String id, @PathVariable String childTable, @PathVariable String childID) {
        checkChildTableIsExsits(childTable)

        def foreignKey = getForeignKey(childTable)
        db.firstRow("${buildSelectStarSql(childTable)} where ${foreignKey} = ? and id = ?".toString(), [id, childID])
    }

    /**
     * 删除子表数据
     * @param id
     * @param childTable
     * @param record
     */
    @RequestMapping(value = "/edit/{id}/children/{childTable}/delete/{childID}", method = [RequestMethod.GET, RequestMethod.PUT])
    def deleteChildTable(@PathVariable String id, @PathVariable String childTable, @PathVariable String childID) {
        checkChildTableIsExsits(childTable)

        def foreignKey = getForeignKey(childTable)
        def value = db.firstRow("delete from ${childTable} where ${foreignKey} = ? and id = ?  returning *".toString(), [id, childID])
        request.setAttribute("delete", value)
        return value
    }

    /**
     * 批量删除子表数据
     * @param id
     * @param childTable
     * @param record
     */
    @RequestMapping(value = "/edit/{id}/children/{childTable}/delete", method = [RequestMethod.POST, RequestMethod.PUT])
    def deleteChildTables(@PathVariable String id, @PathVariable String childTable, @RequestBody def ids) {
        checkChildTableIsExsits(childTable)

        def foreignKey = getForeignKey(childTable)
        def value = db.rows("delete from ${childTable} where ${foreignKey} = ? and id  = any(?)  returning * ".toString(), [id, db.buildArray(ids)])
        request.setAttribute("delete", value)
        return value
    }

    /**
     * 批量删除子表数据
     * @param id
     * @param childTable
     * @param record
     */
    @RequestMapping(value = "/edit/{id}/children/{childTable}/truncate", method = [RequestMethod.GET, RequestMethod.PUT])
    def truncateChildTables(@PathVariable String id, @PathVariable String childTable) {
        checkChildTableIsExsits(childTable)

        def foreignKey = getForeignKey(childTable)
        db.executeUpdate("delete from ${childTable} where ${foreignKey} = ? ".toString(), id)
    }

    /**
     * 排序调整
     * @param id
     * @return
     */
    @RequestMapping(value = "/edit/{id}/sort", method = [RequestMethod.GET, RequestMethod.PUT])
    def sort(@PathVariable String id,
             @RequestParam(required = false) String id_at_me__prev,
             @RequestParam(required = false) String id_at_me__next,
             @RequestParam String id_at_me__parent
    ) {
        def table = mainTable
        def col = db.tables.get(table).get("n_order")
        if (!col) throw new ArkException("排序字段n_order不存在")

        def prev = 0
        def next = 0
        def now = 0

        if (id_at_me__prev) {
            prev = db.firstRow("select n_order from $table where id = ?;".toString(), id_at_me__prev).n_order
        }

        if (id_at_me__next) {
            next = db.firstRow("select n_order from $table where id = ?;".toString(), id_at_me__next).n_order
        }

        if (id_at_me__parent.isBlank()) {
            id_at_me__parent = null
        }

        if (next > prev) {
            now = (prev + next) / 2
        } else {
            now = prev + 2
        }

        def oldParent = db.firstRow("select id_at_me__parent from $table where  id = ?;".toString(), id).id_at_me__parent ?: null

        db.withTransaction {
            db.executeUpdate("update $table set n_order = ? , id_at_me__parent = ? where id = ?".toString(), [now, id_at_me__parent, id])
            //处理序号
            //当前层级排序
            db.executeUpdate(
                    """
                        with cte as ( select id,row_number() over (order by n_order) as  n_order
                                      from ${table} where coalesce(id_at_me__parent,'') ='${
                        id_at_me__parent == null ? "" : id_at_me__parent
                    }'
                                      order by n_order)
                        update ${table} set n_order = (
                                                          select cte.n_order
                                                          from cte where cte.id = ${table}.id
                                                      ) where coalesce(${table}.id_at_me__parent,'') ='${
                        id_at_me__parent == null ? "" : id_at_me__parent
                    }'
                    """.toString()
            )

            if (oldParent != id_at_me__parent) {
                //原层级排序
                oldParent = oldParent ?: ""
                db.executeUpdate(
                        """
                            with cte as ( select id,row_number() over (order by n_order) as  n_order
                                          from ${table} where coalesce(id_at_me__parent,'') ='$oldParent' 
                                          order by n_order)
                            update ${table} set n_order = (
                                                              select cte.n_order
                                                              from cte where cte.id = ${table}.id
                                                           ) where  coalesce(${table}.id_at_me__parent,'')='$oldParent'  
                        """.toString()
                )
            }
        }
    }


    static def buildTree(List list, String treeRootID, Boolean showMeInTree, String labelField, int maxLevel = Integer.MAX_VALUE, Boolean isBox = true,Boolean isShowNobody = true) {
        if (isBox == null) isBox = true

        def nobody = []

        list.each { x ->
            if (StringTool.isNotBlank(x.id_at_me__parent)
                    && list.find { it.id == x.id_at_me__parent } == null) { // 说明此节点的id_at_me__parent 不在list内
                nobody << x
            }
        }

        def group = list.groupBy { it.id_at_me__parent }

        if (StringTool.isBlank(labelField) && list.size() > 0) {
            def item = list[0] as GroovyRowResult
            if (item.containsKey("v_name"))
                labelField = "v_name"
            if (item.containsKey("v_value"))
                labelField = "v_value"
        }

        def buildChildren

        buildChildren = { String _treeRootID, int _level ->
            List roots
            if (_level > maxLevel) {
                roots = []
            } else if (_level == 1) {
                if (StringTool.isBlank(_treeRootID)) {
                    _treeRootID = null
                }

                if (showMeInTree) { //根节点是自己
                    def _me = list.find({ it.id == _treeRootID })
                    roots = _me != null ? [_me] : []
                } else {
                    roots = group[_treeRootID] ?: []
                }

                if (StringTool.isBlank(_treeRootID)) {
                    roots.addAll(group[""] ?: [])
                    if(isShowNobody){
                        roots.addAll(nobody)
                    }
                }
                if (roots.size() == 0
                        && (StringTool.isBlank(_treeRootID) || list.find { it.id == _treeRootID } == null)
                ) { //如果根节点数量为0，尝试寻找其他可以作为根节点的数据
                    group.each { k, v ->
                        if (!list.find { it.id == k }) { // 说明这个k，不在list中，也就是这个数据应该是根节点
                            roots.addAll(v)
                        }
                    }
                }
            } else {
                roots = group[_treeRootID] ?: []
            }


            return roots.sort {
                it.n_order
            }.collect {
                if (isBox) {
                    [id: it.id, label: it[labelField], data: it]
                } else {
                    it
                }
            }.each {
                def children = buildChildren(it.id, _level + 1)
                if (children?.size() > 0) {
                    it.children = children
                }
            }
        }


        return buildChildren(treeRootID, 1)


//        list.findAll {
//            if (level > maxLevel) {
//                return false
//            } else if ((it.id_at_me__parent ?: "") == treeRootID) {
//                return true
//            } else if (level == 1 && !list.find { _it -> it.id_at_me__parent == _it.id }) {
//                return true
//            } else {
//                return false
//            }
//        }


    }

    /**
     * excel 导出
     * @param page 页码
     * @param size 分页大小
     * @param noPage 是否不分页
     * @param condition 查询条件
     * @param sort 排序字段，如   age:d,name,birthday:d
     * @param tree 是否树状显示，树状显示时，分页相关参数无效
     * @param headList 目前包含表头信息
     * @return
     */
    @PostMapping("/export")
    def export(
            @RequestParam(required = false) Integer page,
            @RequestParam(required = false) Integer size,
            @RequestParam(required = false) Boolean noPage,
            @RequestParam(required = false) String condition,
            @RequestParam(required = false) String sort,
            @RequestParam(required = false) Boolean tree,
            @RequestParam(required = false) String treeRootID,
            @RequestParam(required = false) Boolean showMeInTree,
            @RequestParam(required = false) Boolean isBoxTree,
            @RequestParam(required = false) String treeLabelField,
            @RequestParam(required = false) Integer treeMaxLevel,
            @RequestParam(required = false) String query,
            @RequestBody def headList) {
        //获取数据
        List rows = list(page, size, noPage, condition, sort, tree, treeRootID, showMeInTree, isBoxTree, treeLabelField, treeMaxLevel, query).list
        //清除冗余字段
        List targeRows = new ArrayList()
        rows.eachWithIndex { it, index ->
            List row = new ArrayList()
            //只记录要显示的数据
            headList.each { kv ->
                //获取要显示的字段
                def fieldName = kv.values()[0]
                //记录对应的值
                def fieldValue = it.get(fieldName)
                if (fieldValue instanceof Timestamp) {
                    row.add(fieldValue.format("yyyy-MM-dd HH:mm:ss"))
                } else if (fieldValue instanceof Date) {
                    row.add(fieldValue.format("yyyy-MM-dd"))
                } else {
                    row.add(fieldValue)
                }
            }
            targeRows.add(row)
        }
        return buildExcel(targeRows, headList)
    }

    @PostMapping("/export2")
    def export2(@RequestBody body) {
        return export(
                body.page,
                body.size,
                body.noPage,
                body.condition,
                body.sort,
                body.tree,
                body.treeRootID,
                body.showMeInTree,
                body.isBoxTree,
                body.treeLabelField,
                body.treeMaxLevel,
                body.query,
                body.headList
        )

    }

    @PostMapping("/import")
    def imports(MultipartFile file) {
        new ExcelBuilder(file.inputStream).list()
    }

    /**
     * excel 生成
     * @param dataRows 数据
     * @param headList 表头
     */
    static def buildExcel(List dataRows, List headList = null, String title = null) {
        //创建HSSFWorkbook对象
        HSSFWorkbook wb = new HSSFWorkbook()
        //创建HSSFSheet对象
        HSSFSheet sheet = wb.createSheet("sheet0")

        def rowIndex = 0

        if (title) {
            def _row = sheet.createRow(rowIndex)
            CellRangeAddress cellAddresses = new CellRangeAddress(rowIndex, rowIndex, 0, headList.size() - 1)
            if (cellAddresses.toList().size() > 1) {
                sheet.addMergedRegion(cellAddresses)
            }
            def cell = _row.createCell(0)
            cell.setCellValue(title)
            HSSFCellStyle cellStyle = wb.createCellStyle()
            cellStyle.setAlignment(HorizontalAlignment.CENTER)
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER)
            cell.setCellStyle(cellStyle)
            rowIndex++
        }

        if (headList == null && dataRows.size() > 0) {
            Map first = dataRows[0]
            headList = first.keySet().collect { ["$it": it] }
        }


        //加入表头
        HSSFRow headRow = sheet.createRow(rowIndex)
        headList.eachWithIndex { it, i ->
            sheet.setColumnWidth(i, 256 * 25)
            //获取列名
            def colName = it.keySet()[0]
            HSSFCell cell = headRow.createCell(i)
            //设置单元格值
            cell.setCellValue(colName)
            //设置单元格样式
            CellStyle style = wb.createCellStyle()
            HSSFFont font = wb.createFont()
            font.setBold(true) //粗体显示
            style.setFont(font) //单元格样式
            style.setAlignment(HorizontalAlignment.CENTER) //居中
            cell.setCellStyle(style) //给cell这个单元格设置样式
        }

        def region = new CellRangeAddress(0, rowIndex, 0, headList.size() - 1)
        RegionUtil.setBorderBottom(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderLeft(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderRight(BorderStyle.THIN, region, sheet);
        RegionUtil.setBorderTop(BorderStyle.THIN, region, sheet);

        rowIndex++

        //加入数据
        dataRows.eachWithIndex { it, index ->
            HSSFRow _row = sheet.createRow(index + rowIndex);
            it.eachWithIndex { v, i ->
                if (v instanceof Map.Entry) v = v.value
                _row.createCell(i).setCellValue(v)
            }
        }
        //输出Excel文件
        ByteArrayOutputStream bos = new ByteArrayOutputStream()
        wb.write(bos)
        HttpHeaders httpHeaders = new HttpHeaders()
        String fileName = new String("${title ?: "export"}.xls".getBytes("UTF-8"), "iso-8859-1")
        httpHeaders.setContentDispositionFormData("attachment", fileName)
        httpHeaders.setContentType(MediaType.APPLICATION_OCTET_STREAM)
        ResponseEntity<byte[]> filebyte = new ResponseEntity<byte[]>(bos.toByteArray(), httpHeaders, HttpStatus.OK)
        try {
            bos.close()
        } catch (IOException e) {
            e.printStackTrace()
        } finally {
            bos.close()
        }
        return filebyte
    }

    /**
     * 检查子表是否存在
     * @param childTable
     */
    boolean checkChildTableIsExsits(String childTable) {
        if (!isChildTableIsExsits(childTable)) {
            throw ArkException.notHerChild(mainTable, childTable)
        }
        return true
    }

    /**
     * 检查子表是否存在
     * @param childTable
     */
    boolean isChildTableIsExsits(String childTable) {
        return (childTable in childrenTables)
    }

    /**
     *
     * @param dbTable
     * @param sort
     * @param sqlBuilder
     * @param params
     * @return
     */
    static def buildSort(String sort, StringBuilder sqlBuilder) {

        SqlPlus.checkSafeSqlSegment(sort)
        if (sort.startsWith('$$$')) {
            sqlBuilder.append(" order by " + sort.substring(3))
            return
        }

        def order = sort.split(",").collect {
            def str
            def arr = (it.split(":")) // [v_name, d]  [n_order]
            def field = arr[0]
            str = " ${field} "
            if (arr.length > 1) {
                if (arr[1] == 'd') {
                    str += " desc "
                } else {
                    str += " asc "
                }
            }

            if (arr.length > 2) {
                if (arr[2] == 'nf')
                    str += " nulls first "
                else if (arr[2] == 'nl')
                    str += " nulls last "
            }

            return str
        }.findAll { it != null }.join(",")

        if (StringTool.isNotBlank(order)) {
            sqlBuilder.append(" order by " + order)
        }

    }

    @Override
    void afterPropertiesSet() throws Exception {

//        eventBus.consumer(DatabaseCheckService.BUS_OK, {
//            db.checkTableExists(mainTable)
//            childrenTables.each {
//                db.checkTableExists(it)
//            }
//        })

        moduleName = getModuleName()
        metadataService.moduleMapTable[moduleName] = mainTable
        if (StringTool.isNotBlank(shadowList)) {
            metadataService.moduleMapShadow[moduleName] = shadowList
        }
        log.info("$moduleName is online, mainTable is $mainTable,childrenTable is ${childrenTables.toString()}")
    }

    protected String getModuleName() {
        String path = this.getClass().getAnnotation(RequestMapping.class).value()[0]
        return path.substring(path.lastIndexOf("/") + 1)
    }

    String getForeignKey(String childTable) {
        def foreignKey = foreignKeys[childTable]
        if (foreignKey == null) {
            foreignKey = "id_at_" + mainTable
        }
        return foreignKey
    }

    /**
     * 根据表名，在符合命名规则的情况下自动生成带json 的select全量字段查询，如：
     * with app_menu as (
     *     select app_menu.* ,
     *  app_menu_c7mNmV.v_name as v_name_at_me__parent ,
     *  app_menuscheme_DPo3N8.v_name as v_name_at_app_menuscheme ,
     *  app_module_L59noW.v_name as v_name_at_app_module
     *     from app_menu
     *  left join app_menu as app_menu_c7mNmV on app_menu_c7mNmV.id = app_menu.id_at_me__parent
     *  left join app_menuscheme as app_menuscheme_DPo3N8 on app_menuscheme_DPo3N8.id = app_menu.id_at_app_menuscheme
     *  left join app_module as app_module_L59noW on app_module_L59noW.id = app_menu.id_at_app_module
     * )
     * select * from app_menu
     * @param table
     * @return
     */
    String buildSelectStarSql(String table, List<Join> joins = [], boolean isCount = false, boolean plus = false) {

        def commonFields = ["v_name", "v_lable", "v_code", "v_username", "v_shortname", "v_value", "i_value"]
        def cols = db.tables[table].columns

        def leftJoin = []
        def xxAsyyList = []
        cols.each {
            def label = it.label
            def domain = it.domain
            if ((autoJoin || plus) && label.startsWith("id_at_")
//                    && !label.endsWith("__perm")
            ) {
                //如果是id_at_xxxx 开头，说明是关联了另外一张表的id
                def _tableWithSuffix = label.substring(6)
                def _tableName = _tableWithSuffix
                if (_tableWithSuffix.contains("__")) {
                    _tableName = _tableWithSuffix.substring(0, _tableWithSuffix.lastIndexOf("__"))
                }
                if (_tableName == "me") {
                    _tableName = table
                }

                def _table = db.tables[_tableName]
                if (_table) {
                    def _tableTempName = "${_tableName}_${StringTool.getRandomString(6)}"

                    def needJoin = false

                    _table.columns.each { _col ->
                        def _label = _col.label
                        if (_label in commonFields) {//关联表存在常用字段
                            def targetFieldAlias = "${_label}_at_${_tableWithSuffix}"
                            if (!cols.find { it.label == targetFieldAlias }) { //需要翻译的字段不存在于原表
                                needJoin = true
                                xxAsyyList << " ${_tableTempName}.${_label} as $targetFieldAlias "
                            }
                        }
                    }

                    if (needJoin) { //有字段需要关联
                        leftJoin << " left join ${_tableName} as ${_tableTempName} on ${_tableTempName}.id = ${table}.${label}  "
                    }
                }
            } else if (domain == 'dict' || label.startsWith("dict_")) { //说明是数据字典关联字段
                def dictType = label
                if (label.startsWith("dict_")) {
                    dictType = label.substring(5)
                }
                if (dictType.contains("__")) {
                    dictType = dictType.substring(0, dictType.lastIndexOf("__"))
                }
                def _dickTempName = "app_dict_${StringTool.getRandomString(6)}"

                ["v_value", "v_fullname", "v_shortname", "v_remark"].each {
                    def targetFieldAlias = "${it}_at_${label}"
                    xxAsyyList << " ${_dickTempName}.${it} as $targetFieldAlias "
                }

                leftJoin << " left join app_dict as ${_dickTempName} on ${_dickTempName}.id_at_app_dictcategory in ('$dictType','${dictType.toUpperCase()}') and ${_dickTempName}.v_code = ${table}.${label}::varchar "
            } else if (domain == 'dicts' || label.startsWith("dicts_")) {
                def dictType = label
                if (label.startsWith("dicts_")) {
                    dictType = label.substring(6)
                }
                if (dictType.contains("__")) {
                    dictType = dictType.substring(0, dictType.lastIndexOf("__"))
                }

                xxAsyyList << " translate_dicts('${dictType.toUpperCase()}',${table}.${label}) as v_value_at_${label} "
            } else if (domain == 'file' || label.startsWith("file_")) {
                xxAsyyList << " substring($table.$label from '\\.(.+)\$') as name_$label "
            } else if (domain == 'files' || label.startsWith("files_")) {
                xxAsyyList << """ (select ARRAY(select substring(UNNEST($table.$label) from '\\.(.+)\$'))) as name_$label  """
            }
        }

        joins.each {
            def _tableName = it.targetTable
            def _tableTempName = "${_tableName}_${StringTool.getRandomString(6)}"

            if (it.isShowAll()) {
                it.addShowColumns(db.tables[_tableName].columns.findAll { it.label != 'id' }.collect { it.label })
            }

            it.showColumns.each {
                def columnAlias = it.columnAlias
                if (!cols.find { it.label == columnAlias }) { //需要翻译的字段不存在于原表
                    xxAsyyList << " ${_tableTempName}.${it.column} as $columnAlias "
                }
            }

            leftJoin << " left join ${_tableName} as ${_tableTempName} on ${_tableTempName}.${it.targetColumn} = ${table}.${it.selfColumn}  "
        }


        def leftJoinStr = leftJoin.join("\n")
        def xxAsyyStr = (xxAsyyList.join(",\n"))
        if (xxAsyyStr.length() > 0) {
            xxAsyyStr = ",\n" + xxAsyyStr
        }


        def sql = """
select ${isCount ? "count(*) as num" : "*"} from (
    select ${table}.* ${xxAsyyStr}
    from ${table}
${leftJoinStr}
) ${table}
"""
        return sql
    }

    def broadcastChanged(String type, def data) {
        if (autoBroadcast) {
            eventBus.publish("data.changed.$mainTable".toString(), [
                    type: type,
                    data: data
            ])
        }
    }

}