package com.iplatform.generator;

import com.iplatform.generator.util.DataTypeUtils;
import com.iplatform.generator.util.SqlUtils;
import com.walker.db.DatabaseType;
import com.walker.jdbc.JdbcInspector;
import com.walker.jdbc.generator.util.GenPoUtils;
import com.walker.jdbc.service.BaseServiceImpl;
import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.zip.ZipOutputStream;

@Service
public class JdbcGeneratorServiceImpl extends BaseServiceImpl {

    protected final transient Logger logger = LoggerFactory.getLogger(this.getClass());

    /**
     * 适配老方法。
     * @param tableNamePrefix
     * @param mbbs
     * @return
     * @date 2022-11-26
     */
    public byte[] generatorPoCode(String tableNamePrefix, String mbbs){
        return this.generatorPoCode(tableNamePrefix, mbbs, false);
    }

    /**
     * 生成jdbc所需要的实体对象和映射对象。
     * @param tableNamePrefix
     * @param mbbs
     * @param isPrecision 是否精确匹配，如果true，则只会匹配到一张表记录。(2022-11-26添加)
     * @return
     * @date 2023-03-23 更新，把字段、表名改为驼峰式
     */
    public byte[] generatorPoCode(String tableNamePrefix, String mbbs, boolean isPrecision){
//        FileOutputStream fileOutputStream = new FileOutputStream(new File("d:/demo.zip"));
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        ZipOutputStream zip = new ZipOutputStream(outputStream);
        List<Map<String, Object>> tableList = null;
        if(isPrecision){
            tableList = this.getTableList(tableNamePrefix, true);
        } else {
            tableList = this.getTableList(tableNamePrefix);
        }
        for (Map<String, Object> table : tableList) {
            GenPoUtils.generatorCode(mbbs, table.get("table_name").toString()
                    , table.get("pk_name").toString(), table.get("pk_type").toString(), (List)table.get("columns"), zip);
        }
        IOUtils.closeQuietly(zip);
        return outputStream.toByteArray();
    }

    /**
     * 适配原来方法。
     * @param tableName
     * @return
     * @date 2022-11-26
     */
    public List<Map<String, Object>> getTableList(String tableName){
        return this.getTableList(tableName, false);
    }

    /**
     * 根据给定表明前缀，返回模糊查询的所有表结构集合（包含字段信息）。
     * @param tableName
     * @param isPrecision 是否精确匹配，如果true，则只会匹配到一张表记录。(2022-11-26添加)
     * @return
     */
    public List<Map<String, Object>> getTableList(String tableName, boolean isPrecision) {
        DatabaseType databaseType = JdbcInspector.getInstance().getPrimaryDatabaseType();
        logger.debug("databaseType = " + databaseType.toString());
//        if(!DataTypeUtils.isMysql(databaseType)){
        if(DataTypeUtils.isOracle(databaseType)){
            // oracle元数据中表名都是大写
            tableName = tableName.toUpperCase();
        } else {
            tableName = tableName.toLowerCase();
        }
        List<Map<String, Object>> resultList = new ArrayList<>();
        Map<String, Object> sqlMap = new HashMap<>();
        if(isPrecision){
            sqlMap.put("tablename", "%" + tableName);
        } else {
            sqlMap.put("tablename", tableName + "%");
        }
        String sql_table = "";
        String sql_pk = "";
        String sql_column = "";

//        if ("oracle".equalsIgnoreCase(databaseType.toString())) {
//        if (!DataTypeUtils.isMysql(databaseType)) {
        if (DataTypeUtils.isOracle(databaseType)) {
            sql_table = "select table_name from user_tables where table_name like :tablename order by table_name desc";
            sql_pk = "select  col.table_name,col.column_name from user_constraints con,user_cons_columns col where con.constraint_name=col.constraint_name and con.constraint_type='P' and col.table_name like :tablename ";
//            sql_column = "select table_name,column_name,data_type,data_precision,data_scale from user_tab_columns where table_name like :tablename order by table_name,column_id";
            sql_column = SqlUtils.ORACLE_QUERY_COLUMNS;
//            StringBuilder columnSql = new StringBuilder("select t1.*, ucc.constraint_name, uc.constraint_type from (");
//            columnSql.append("select c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.DATA_LENGTH, c.DATA_PRECISION, C.DATA_SCALE, c.NULLABLE, c.COLUMN_ID, s.COMMENTS ")
//                    .append("from user_tab_columns c, user_col_comments s ")
//                    .append("where c.Table_Name=? and c.TABLE_NAME = s.TABLE_NAME and c.COLUMN_NAME = s.COLUMN_NAME order by c.COLUMN_ID")
//                    .append(") t1 ")
//                    .append("left join user_cons_columns ucc on ucc.table_name=t1.Table_Name and ucc.column_name=t1.COLUMN_NAME ")
//                    .append("left join user_constraints uc on uc.constraint_name=ucc.constraint_name");
        } else if(DataTypeUtils.isMysql(databaseType)) {
            sql_table = "select table_name from information_schema.tables where table_schema=(SELECT DATABASE()) and table_name like :tablename order by table_name desc";
            sql_pk = "select table_name,column_name from INFORMATION_SCHEMA.Columns where column_key='PRI' AND table_schema=(SELECT DATABASE()) and table_name like :tablename";
            sql_column = "select table_name,column_name,data_type,NUMERIC_PRECISION data_precision,NUMERIC_SCALE data_scale, column_key from INFORMATION_SCHEMA.Columns where table_schema=(SELECT DATABASE()) and table_name like :tablename order by table_name,ordinal_position\n";

        } else if(databaseType == DatabaseType.POSTGRES){
            sql_table = "SELECT c.relname table_name from pg_class c, pg_namespace n  where c.relkind = 'r' and n.nspname = 'public' and c.relnamespace = n.oid and c.relname like :tablename";
            sql_column = SQL_PG_FIELDS;
            logger.debug("POSTGRES......");

        } else {
            throw new UnsupportedOperationException("（po生成）不支持的数据库类型：" + databaseType);
        }
        List<Map<String, Object>> table_list = this.dao.select(sql_table, sqlMap);
        List<Map<String, Object>> column_list = this.dao.select(sql_column, sqlMap);

        logger.debug("生成表结构（PO） = {}", table_list);

        for (Map<String, Object> table : table_list) {
            String table_name = table.get("table_name").toString();
            String pk_name = "id";
            String pk_type = "NUMBER";
            List<Map<String, Object>> column_temp = new ArrayList<>();

            String columnKey = null;
            for (Map<String, Object> column : column_list) {
                if(!column.get("table_name").toString().equalsIgnoreCase(table.get("table_name").toString())){
                    // 只有当前表字段才处理，老代码遍历所有表字段有问题。2022-09-20
                    continue;
                }
                // 2022-09-07 修改（后续有oracle需求还需要改）
                if(column.get("column_key") != null){
                    columnKey = column.get("column_key").toString();
                    if(DataTypeUtils.isPrimaryColumn(columnKey)){
                        // mysql主键字段是：PRI，oracle是：P，2023-03-03 修改
                        pk_name = column.get("column_name").toString().toLowerCase();
                        pk_type = column.get("data_type").toString().toLowerCase();
                        logger.info(table_name + " 找到主键:" + pk_name + ", " + pk_type);
                        continue;
                    }
                }

                if ((table_name.equalsIgnoreCase(column.get("table_name").toString())) && (!column.get("column_name").toString().equalsIgnoreCase(pk_name))) {
                    String type = column.get("data_type").toString().toLowerCase();
                    column.put("name", column.get("column_name").toString().toLowerCase());
                    if ((type.equalsIgnoreCase("number")) || (type.equalsIgnoreCase("decimal"))) {
                        int precision = Integer.valueOf(column.get("data_precision") == null ? "0" : column.get("data_precision").toString()).intValue();
                        int scale = Integer.valueOf(column.get("data_scale") == null ? "0" : column.get("data_scale").toString()).intValue();
                        type = DataTypeUtils.getType(type, precision, scale);
                    }
                    column.put("type", type);
                    column_temp.add(column);
                }
            }
            table.put("table_name", table_name.toLowerCase());
            table.put("pk_name", pk_name);
//            table.put("pk_type", pk_type);
            table.put("pk_type", DataTypeUtils.getType(pk_type, 0, 0));
            table.put("columns", column_temp);
            resultList.add(table);
        }
        return resultList;
    }

    private static final String SQL_PG_FIELDS = "SELECT\n" +
            "\tC.relname table_name,\n" +
            "\tA.attname AS column_name, A.attlen data_precision,\n" +
            "\tformat_type ( A.atttypid, A.atttypmod ) AS data_type,\n" +
            "\tcol_description ( A.attrelid, A.attnum ) AS COMMENT, 0 AS data_scale,\n" +
            "(CASE WHEN ( SELECT COUNT(*) FROM pg_constraint WHERE conrelid = a.attrelid AND conkey[1]= attnum AND contype = 'p' ) > 0 THEN\n" +
            "        'PRI' ELSE '' \n" +
            "        END ) AS column_key\n" +
            "FROM\n" +
            "\tpg_class AS C,\n" +
            "\tpg_attribute AS A,\n" +
            "\tpg_tables AS B\n" +
            "WHERE A.attrelid = C.oid\n" +
            "  and C.relname=B.tablename\n" +
            "  AND A.attnum > 0\n" +
            "  AND B.schemaname = 'public' and c.relname like :tablename";
}