package com.artfess.dataShare.util;

import com.artfess.base.util.StringUtil;
import com.artfess.dataShare.dataResource.app.model.BizAppCatalogsTableField;
import com.artfess.dataShare.dataResource.dw.model.BizDwdCatalogsTableField;
import com.artfess.dataShare.dataResource.ods.model.BizOdsField;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class DorisUtils {

    //创建表
    public static String getOdsTableSql(String tableName,String tableDesc,List<BizOdsField> fields){
        StringBuffer createSql=new StringBuffer();
        createSql.append("CREATE TABLE IF NOT EXISTS ").append(tableName);
        createSql.append("(\n");
        createSql.append(" `id_` VARCHAR(64) NOT NULL COMMENT \"主键id\",\n");
        String uniqueKey="id_";
        for(int i=0;i<fields.size();i++){
            BizOdsField field = fields.get(i);
            if("ID_".equalsIgnoreCase(field.getFieldName())){
                continue;
            }
            Integer attrLength=field.getAttrLength()==null ? 0 : field.getAttrLength();
            Integer decimalLen=field.getDecimalLen()==null ? 4 : field.getDecimalLen();
            String fieldSql="`"+field.getFieldName()+"` "+FieldUtil.getColumnType(field.getDataType(), attrLength, attrLength,decimalLen)+" NULL COMMENT \""+field.getDesc()+"\"" ;
            if(i==fields.size()-1) {
                createSql.append(" ").append(fieldSql);
            }else{
                createSql.append(" ").append(fieldSql).append(",\n");
            }
            if("1".equalsIgnoreCase(field.getIsPartition())){
                uniqueKey+=","+field.getFieldName();
            }
        }

        createSql.append(")\n");

        createSql.append(" ENGINE=OLAP\n");
        //createSql.append(" UNIQUE KEY(`id_`)\n");
        createSql.append(" UNIQUE KEY("+uniqueKey+") \n");
        if(StringUtil.isNotEmpty(tableDesc)) {
            createSql.append(" COMMENT  '" + tableDesc + "' \n");
        }
        createSql.append(" DISTRIBUTED BY HASH(`id_`) BUCKETS 16");
        createSql.append(" PROPERTIES\n");
        createSql.append("(\n");
        createSql.append("\"replication_num\" = \"3\"\n");
        createSql.append(")");
        return createSql.toString();
    }

    //创建表
    public static String createTable(JdbcTemplate template, String tableName,String tableDesc, List<BizOdsField> fields){
        String createSql=getOdsTableSql(tableName,tableDesc,fields);
        System.out.println(createSql);
        template.execute(createSql);
        return createSql;
    }

    //DWD物理里表语句
    public static String getDWDWLTableSql(String tableName,String tableDesc, List<BizDwdCatalogsTableField> fields){
        StringBuffer createSql=new StringBuffer();
        createSql.append("CREATE TABLE IF NOT EXISTS ").append(tableName);
        createSql.append("(\n");
        createSql.append(" `id_` VARCHAR(64) NOT NULL COMMENT \"主键id\",\n");
        for(int i=0;i<fields.size();i++){
            BizDwdCatalogsTableField field = fields.get(i);
            if("ID_".equalsIgnoreCase(field.getFieldCode())){
                continue;
            }
            Integer attrLength=field.getAttrLength()==null ? 0 : field.getAttrLength();
            Integer decimalLen=field.getDecimalLen()==null ? 4 : field.getDecimalLen();
            String fieldSql="`"+field.getFieldCode()+"` "+FieldUtil.getColumnType(field.getDataType(), attrLength, attrLength,decimalLen)+" NULL COMMENT \""+field.getDesc()+"\"" ;
            if(i==fields.size()-1) {
                createSql.append(" ").append(fieldSql);
            }else{
                createSql.append(" ").append(fieldSql).append(",\n");
            }
        }
        createSql.append(")\n");
        createSql.append(" ENGINE=OLAP\n");
      //  createSql.append(" UNIQUE KEY(`id_`)\n");
        if(StringUtil.isNotEmpty(tableDesc)) {
            createSql.append(" COMMENT  '" + tableDesc + "' \n");
        }
        createSql.append(" DISTRIBUTED BY HASH(`id_`) BUCKETS 16");
        createSql.append(" PROPERTIES\n");
        createSql.append("(\n");
        createSql.append("\"replication_num\" = \"3\"\n");
        createSql.append(")");
        return createSql.toString();
    }

    //创建物理表
    public static String createDwTable(JdbcTemplate template, String tableName,String tableDesc, List<BizDwdCatalogsTableField> fields){
        String createSql=getDWDWLTableSql(tableName,tableDesc,fields);
        System.out.println(createSql);
        template.execute(createSql);
        return createSql;
    }

    public static String getAPPTableSql(String tableName,String tableDesc, List<BizAppCatalogsTableField> fields){
        StringBuffer createSql=new StringBuffer();
        createSql.append("CREATE TABLE IF NOT EXISTS ").append(tableName);
        createSql.append("(\n");
        createSql.append(" `id_` VARCHAR(64) NOT NULL COMMENT \"主键id\",\n");
        for(int i=0;i<fields.size();i++){
            BizAppCatalogsTableField field = fields.get(i);
            if("ID_".equalsIgnoreCase(field.getFieldCode())){
                continue;
            }
            Integer attrLength=field.getAttrLength()==null ? 0 : field.getAttrLength();
            Integer decimalLen=field.getDecimalLen()==null ? 4 : field.getDecimalLen();
            String fieldSql="`"+field.getFieldCode()+"` "+FieldUtil.getColumnType(field.getDataType(), attrLength, attrLength,decimalLen)+" NULL COMMENT \""+field.getDesc()+"\"" ;
            if(i==fields.size()-1) {
                createSql.append(" ").append(fieldSql);
            }else{
                createSql.append(" ").append(fieldSql).append(",\n");
            }
        }
        createSql.append(")\n");
        createSql.append(" ENGINE=OLAP\n");
     //   createSql.append(" UNIQUE KEY(`id_`)\n");
        if(StringUtil.isNotEmpty(tableDesc)) {
            createSql.append(" COMMENT  '" + tableDesc + "' \n");
        }
        createSql.append(" DISTRIBUTED BY HASH(`id_`) BUCKETS 16");
        createSql.append(" PROPERTIES\n");
        createSql.append("(\n");
        createSql.append("\"replication_num\" = \"3\"\n");
        createSql.append(")");
        return createSql.toString();
    }

    //创建物理表
    public static String createAppTable(JdbcTemplate template, String tableName,String tableDesc, List<BizAppCatalogsTableField> fields){
        String createSql=getAPPTableSql(tableName,tableDesc,fields);
        template.execute(createSql);
        return createSql;
    }

//ALTER table sw_jyjh rename ods_sw_jyjh

/*    public static String getColumnType(String columnType, int charLen, int intLen, int decimalLen) {
        if ("varchar".equalsIgnoreCase(columnType)) {
            return "VARCHAR(" + charLen + ')';
        } else if ("小数".equalsIgnoreCase(columnType)) {
            return "DECIMAL(" + (intLen + decimalLen) + "," + decimalLen + ")";
        } else if ("date".equalsIgnoreCase(columnType)) {
            return "DATETIME";
        }else if ("number".equals(columnType)) {
            return "BIGINT(" + intLen + ")";
        } else if ("text".equalsIgnoreCase(columnType)) {
            return "TEXT";
        } else {
            return "";
        }
    }*/

    //创建dwd层动态的两张动态表
    public static void  createDwTable(JdbcTemplate template,String tableName,String tableDesc){
        //数据明细表--DWD层（动态表数据存储表结构）Doris行数据存储表
        String createDwdMainSql = queryDwdMainSql(tableName,tableDesc);
        template.execute(createDwdMainSql);

        String createDwdDetailSql = queryDwdDetailSql(tableName,tableDesc);
        template.execute(createDwdDetailSql);
    }

    public static String queryDwdMainSql(String tableName,String tableDesc){
        if (StringUtil.isEmpty(tableName)){
            return "";
        }
        //数据明细表--DWD层（动态表数据存储表结构）Doris行数据存储表
        StringBuffer createDwdMainSql=new StringBuffer("create table DWD_BIZ_"+tableName+"_DATA\n" +
                "(\n" +
                "   ID_                  VARCHAR(64) not null comment '主键ID',\n" +
                "   USER_ID_             VARCHAR(64) comment '个人ID',\n" +
                "   USER_NAME_           varchar(64) default NULL comment '个人姓名',\n" +
                "   USER_SEX_            varchar(6) default NULL comment '个人性别',\n" +
                "   USER_IDENTITY_CARD_  varchar(18) default NULL comment '身份证号',\n" +
                "   USER_PHONE_          varchar(18) default NULL comment '移动手机号',\n" +
                "   DATA_ID_             VARCHAR(32) comment '数据批次ID（同一条记录数据批次ID相同）',\n" +
                "   TABLE_CODE_          VARCHAR(32) comment '数据资源明细项编码',\n" +
                "   DATA_JSON_           VARCHAR(4000) comment '字段数据JSON组合串',\n" +
                "   FIELD_VALUE_TIME_    datetime comment '字段数据所属时间',\n" +
                "   VERSION              VARCHAR(16) comment '数据版本号（同一批数据版本号相同）',\n" +
                "   CREATE_BY_           VARCHAR(64) default NULL comment '创建人',\n" +
                "   CREATE_ORG_ID_       VARCHAR(64) default NULL comment '创建单位',\n" +
                "   CREATE_TIME_         datetime comment '创建时间',\n" +
                "   UPDATE_BY_           VARCHAR(64) default NULL comment '修改人员',\n" +
                "   UPDATE_ORG_ID_       VARCHAR(64) default NULL comment '修改单位',\n" +
                "   UPDATE_TIME_         datetime comment '修改时间',\n" +
                "   TENANT_ID_           VARCHAR(64) default NULL comment '租户ID'\n");
        createDwdMainSql.append(")\n");
        createDwdMainSql.append(" ENGINE=OLAP\n");
        createDwdMainSql.append(" UNIQUE KEY(`id_`)\n");
        if(StringUtil.isNotEmpty(tableDesc)) {
            createDwdMainSql.append(" COMMENT  '" + tableDesc + "' \n");
        }
        createDwdMainSql.append(" DISTRIBUTED BY HASH(`id_`) BUCKETS 16");
        createDwdMainSql.append(" PROPERTIES\n");
        createDwdMainSql.append("(\n");
        createDwdMainSql.append("\"replication_num\" = \"1\"\n");
        createDwdMainSql.append(")");
        return createDwdMainSql.toString();
    }

    public static String queryDwdDetailSql(String tableName,String tableDesc){
        if (StringUtil.isEmpty(tableName)){
            return "";
        }
        StringBuffer createDwdDetailSql=new StringBuffer("create table DWD_"+tableName+"_TABLE_RECORD\n" +
                "(\n" +
                "   ID_                  VARCHAR(64) not null comment '主键ID（采用递增即可）',\n" +
                "   USER_ID_             VARCHAR(64) comment '个人ID',\n" +
                "   USER_NAME_           varchar(64) default NULL comment '个人姓名',\n" +
                "   USER_SEX_            varchar(6) default NULL comment '个人性别',\n" +
                "   USER_IDENTITY_CARD_  varchar(18) default NULL comment '身份证号',\n" +
                "   USER_PHONE_          varchar(18) default NULL comment '移动手机号',\n" +
                "   DATA_ID_             VARCHAR(32) comment '数据批次ID（同一条记录数据批次ID相同）',\n" +
                "   TABLE_CODE_          VARCHAR(32) comment '数据资源明细项编码',\n" +
                "   FIELD_ID_            VARCHAR(32) default NULL comment '字段ID(所属字段定义表ID)',\n" +
                "   FIELD_CODE_          VARCHAR(32) comment '字段列名(所属字段定义表字段名)',\n" +
                "   FIELD_VALUE_         varchar(2000) default NULL comment '字段值Value',\n" +
                "   FIELD_UI_MODULE_     varchar(2) default NULL comment '字段控件（1手动输入2下拉组件3复选组件4单选组件5日期组件6数字组件）',\n" +
                "   FIELD_VALUE_TIME_    datetime comment '字段数据所属时间',\n" +
                "   VERSION              VARCHAR(16) comment '数据版本号（同一批数据版本号相同）',\n" +
                "   CREATE_BY_           VARCHAR(64) default NULL comment '创建人',\n" +
                "   CREATE_ORG_ID_       VARCHAR(64) default NULL comment '创建单位',\n" +
                "   CREATE_TIME_         datetime comment '创建时间',\n" +
                "   UPDATE_BY_           VARCHAR(64) default NULL comment '修改人员',\n" +
                "   UPDATE_ORG_ID_       VARCHAR(64) default NULL comment '修改单位',\n" +
                "   UPDATE_TIME_         datetime comment '修改时间',\n" +
                "   TENANT_ID_           VARCHAR(64) default NULL comment '租户ID'\n");
        createDwdDetailSql.append(")\n");
        createDwdDetailSql.append(" ENGINE=OLAP\n");
        createDwdDetailSql.append(" UNIQUE KEY(`id_`)\n");
        if(StringUtil.isNotEmpty(tableDesc)) {
            createDwdDetailSql.append(" COMMENT  '" + tableDesc + "' \n");
        }
        createDwdDetailSql.append(" DISTRIBUTED BY HASH(`id_`) BUCKETS 16");
        createDwdDetailSql.append(" PROPERTIES\n");
        createDwdDetailSql.append("(\n");
        createDwdDetailSql.append("\"replication_num\" = \"1\"\n");
        createDwdDetailSql.append(")");
        return createDwdDetailSql.toString();
    }

    public static String queryAppMainSql(String tableName,String tableDesc){
        if (StringUtil.isEmpty(tableName)){
            return "";
        }
        //数据明细表--DWD层（动态表数据存储表结构）Doris行数据存储表
        StringBuffer createDwdMainSql=new StringBuffer("create table APP_D_"+tableName+"\n" +
                "(\n" +
                "   ID_                  VARCHAR(64) not null comment '主键ID',\n" +
                "   USER_ID_             VARCHAR(64) comment '个人ID',\n" +
                "   USER_NAME_           varchar(64) default NULL comment '个人姓名',\n" +
                "   USER_SEX_            varchar(6) default NULL comment '个人性别',\n" +
                "   USER_IDENTITY_CARD_  varchar(18) default NULL comment '身份证号',\n" +
                "   USER_PHONE_          varchar(18) default NULL comment '移动手机号',\n" +
                "   DATA_ID_             VARCHAR(32) comment '数据批次ID（同一条记录数据批次ID相同）',\n" +
                "   TABLE_CODE_          VARCHAR(32) comment '数据资源明细项编码',\n" +
                "   DATA_JSON_           VARCHAR(4000) comment '字段数据JSON组合串',\n" +
                "   FIELD_VALUE_TIME_    datetime comment '字段数据所属时间',\n" +
                "   VERSION              VARCHAR(16) comment '数据版本号（同一批数据版本号相同）',\n" +
                "   CREATE_BY_           VARCHAR(64) default NULL comment '创建人',\n" +
                "   CREATE_ORG_ID_       VARCHAR(64) default NULL comment '创建单位',\n" +
                "   CREATE_TIME_         datetime comment '创建时间',\n" +
                "   UPDATE_BY_           VARCHAR(64) default NULL comment '修改人员',\n" +
                "   UPDATE_ORG_ID_       VARCHAR(64) default NULL comment '修改单位',\n" +
                "   UPDATE_TIME_         datetime comment '修改时间',\n" +
                "   TENANT_ID_           VARCHAR(64) default NULL comment '租户ID'\n");
        createDwdMainSql.append(")\n");

        createDwdMainSql.append(" ENGINE=OLAP\n");
        createDwdMainSql.append(" UNIQUE KEY(`id_`)\n");
        if(StringUtil.isNotEmpty(tableDesc)) {
            createDwdMainSql.append(" COMMENT '" + tableDesc + "' \n");
        }
        createDwdMainSql.append(" DISTRIBUTED BY HASH(`id_`) BUCKETS 16");
        createDwdMainSql.append(" PROPERTIES\n");
        createDwdMainSql.append("(\n");
        createDwdMainSql.append("\"replication_num\" = \"1\"\n");
        createDwdMainSql.append(")");
        return createDwdMainSql.toString();
    }

    public static String queryAppDetailSql(String tableName,String tableDesc){
        if (StringUtil.isEmpty(tableName)){
            return "";
        }
        StringBuffer createDwdDetailSql=new StringBuffer("create table APP_D_"+tableName+"_COLUMN\n" +
                "(\n" +
                "   ID_                  VARCHAR(64) not null comment '主键ID（采用递增即可）',\n" +
                "   USER_ID_             VARCHAR(64) comment '个人ID',\n" +
                "   USER_NAME_           varchar(64) default NULL comment '个人姓名',\n" +
                "   USER_SEX_            varchar(6) default NULL comment '个人性别',\n" +
                "   USER_IDENTITY_CARD_  varchar(18) default NULL comment '身份证号',\n" +
                "   USER_PHONE_          varchar(18) default NULL comment '移动手机号',\n" +
                "   DATA_ID_             VARCHAR(32) comment '数据批次ID（同一条记录数据批次ID相同）',\n" +
                "   TABLE_CODE_          VARCHAR(32) comment '数据资源明细项编码',\n" +
                "   FIELD_ID_            VARCHAR(32) default NULL comment '字段ID(所属字段定义表ID)',\n" +
                "   FIELD_CODE_          VARCHAR(32) comment '字段列名(所属字段定义表字段名)',\n" +
                "   FIELD_VALUE_         varchar(2000) default NULL comment '字段值Value',\n" +
                "   FIELD_UI_MODULE_     varchar(2) default NULL comment '字段控件（1手动输入2下拉组件3复选组件4单选组件5日期组件6数字组件）',\n" +
                "   FIELD_VALUE_TIME_    datetime comment '字段数据所属时间',\n" +
                "   VERSION              VARCHAR(16) comment '数据版本号（同一批数据版本号相同）',\n" +
                "   CREATE_BY_           VARCHAR(64) default NULL comment '创建人',\n" +
                "   CREATE_ORG_ID_       VARCHAR(64) default NULL comment '创建单位',\n" +
                "   CREATE_TIME_         datetime comment '创建时间',\n" +
                "   UPDATE_BY_           VARCHAR(64) default NULL comment '修改人员',\n" +
                "   UPDATE_ORG_ID_       VARCHAR(64) default NULL comment '修改单位',\n" +
                "   UPDATE_TIME_         datetime comment '修改时间',\n" +
                "   TENANT_ID_           VARCHAR(64) default NULL comment '租户ID'\n");
        createDwdDetailSql.append(")\n");
        createDwdDetailSql.append(" ENGINE=OLAP\n");
        createDwdDetailSql.append(" UNIQUE KEY(`id_`)\n");
        if(StringUtil.isNotEmpty(tableDesc)) {
            createDwdDetailSql.append(" COMMENT  '" + tableDesc + "' \n");
        }
        createDwdDetailSql.append(" DISTRIBUTED BY HASH(`id_`) BUCKETS 16");
        createDwdDetailSql.append(" PROPERTIES\n");
        createDwdDetailSql.append("(\n");
        createDwdDetailSql.append("\"replication_num\" = \"1\"\n");
        createDwdDetailSql.append(")");
        return createDwdDetailSql.toString();
    }
}