package com.izouma.awesomeadmin.util;

import com.izouma.awesomeadmin.model.DataSourceInfo;
import com.izouma.awesomeadmin.model.TableField;
import com.izouma.codegenerator.GeneratorTool;
import org.apache.commons.lang.StringUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 数据源方法
 */
public class DatabaseUtil {


    /**
     * 加载数据库所有表
     *
     * @param dataSourceInfo
     * @return
     */
    public static List<String> loadDatabaseTables(DataSourceInfo dataSourceInfo) {


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

        String sql = "show tables";

        String DRIVER = "com.mysql.jdbc.Driver";

        PropertiesFileLoader propertiesFileLoader = PropertiesFileLoader.getInstance();

        String url = propertiesFileLoader.getProperties("properties/jdbc.properties", "jdbc.url");
        String user = propertiesFileLoader.getProperties("properties/jdbc.properties", "jdbc.username");
        String password = propertiesFileLoader.getProperties("properties/jdbc.properties", "jdbc.password");

        if (!"dataSource".equals(dataSourceInfo.getCode())) {
            if ("Mysql".equals(dataSourceInfo.getDatabaseType())) {
                url = "jdbc:mysql://" + dataSourceInfo.getUrl() + "/" + dataSourceInfo.getDatabaseName();
                user = dataSourceInfo.getUsername();
                password = dataSourceInfo.getPassword();
            } else if ("SqlServer".equals(dataSourceInfo.getDatabaseType())) {
                DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                url = "jdbc:sqlserver://" + dataSourceInfo.getUrl() + ";DatabaseName=" + dataSourceInfo.getDatabaseName();
                user = dataSourceInfo.getUsername();
                password = dataSourceInfo.getPassword();
                sql = "select * from sys.tables order by name";
            }

        }


        Connection conn = JDBC.connectDB(DRIVER, url, user, password);
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {

                //系统表不显示
                if (!rs.getString(1).startsWith("ACT_")
                        && !rs.getString(1).startsWith("act_")
                        && !rs.getString(1).startsWith("sys_")
                        && !rs.getString(1).startsWith("SYS_")) {

                    tables.add(rs.getString(1));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return tables;
    }

    /**
     * 加载表字段
     *
     * @param dataSourceInfo
     * @return
     */
    public static List<TableField> loadTableFields(DataSourceInfo dataSourceInfo) {


        List<TableField> tableFields = new ArrayList<>();


        String DRIVER = "com.mysql.jdbc.Driver";

        PropertiesFileLoader propertiesFileLoader = PropertiesFileLoader.getInstance();

        String databaseName = propertiesFileLoader.getProperties("properties/jdbc.properties", "jdbc.databaseName");
        String sql = "select * from information_schema.columns where  table_schema ='" + databaseName
                + "' and table_name = '" + dataSourceInfo.getTableName() + "' ;";

        String url = propertiesFileLoader.getProperties("properties/jdbc.properties", "jdbc.url");
        String user = propertiesFileLoader.getProperties("properties/jdbc.properties", "jdbc.username");
        String password = propertiesFileLoader.getProperties("properties/jdbc.properties", "jdbc.password");

        if (!StringUtils.isNotEmpty(dataSourceInfo.getCode())) {
            dataSourceInfo.setDatabaseType("Mysql");
            dataSourceInfo.setCode("dataSource");
        }

        if (!"dataSource".equals(dataSourceInfo.getCode())) {
            if ("Mysql".equals(dataSourceInfo.getDatabaseType())) {
                url = "jdbc:mysql://" + dataSourceInfo.getUrl() + "/" + dataSourceInfo.getDatabaseName();
                user = dataSourceInfo.getUsername();
                password = dataSourceInfo.getPassword();
                sql = "select * from information_schema.columns where  table_schema ='" + dataSourceInfo.getDatabaseName()
                        + "' and table_name = '" + dataSourceInfo.getTableName() + "' ;";
            } else if ("SqlServer".equals(dataSourceInfo.getDatabaseType())) {
                DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
                url = "jdbc:sqlserver://" + dataSourceInfo.getUrl() + ";DatabaseName=" + dataSourceInfo.getDatabaseName();
                user = dataSourceInfo.getUsername();
                password = dataSourceInfo.getPassword();
                sql = "sp_columns " + dataSourceInfo.getTableName() + ";";
            }

        }


        Connection conn = JDBC.connectDB(DRIVER, url, user, password);
        try {
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                TableField tableField = new TableField();

                if ("Mysql".equals(dataSourceInfo.getDatabaseType())) {
                    tableField.setName(rs.getString("COLUMN_NAME"));
                    tableField.setDefaultValue(rs.getString("COLUMN_DEFAULT"));
                    tableField.setJdbcType(rs.getString("DATA_TYPE"));
                    tableField.setLength(rs.getInt("CHARACTER_MAXIMUM_LENGTH"));
                    tableField.setNotNull("NO".equals(rs.getString("IS_NULLABLE")) ? true : false);
                    tableField.setPrimaryKey("PRI".equals(rs.getString("COLUMN_KEY")) ? true : false);
                    tableField.setAutoIncrease("auto_increment".equals(rs.getString("EXTRA")) ? true : false);
                    tableField.setRemark(StringUtils.isNotEmpty(rs.getString("COLUMN_COMMENT")) ? rs.getString("COLUMN_COMMENT") : rs.getString("COLUMN_NAME"));
                    tableField.setShowInForm(true);
                    tableField.setShowInList(true);
                    tableField.setSortable(true);
                    tableField.setSearchable("varchar".equalsIgnoreCase(tableField.getJdbcType()) ? true : false);
                    tableField.setFormType("singleLineText");

                } else if ("SqlServer".equals(dataSourceInfo.getDatabaseType())) {
                    tableField.setName(rs.getString("COLUMN_NAME"));
                    tableField.setJdbcType(GeneratorTool.SqlServerJdbcType2Mysql(rs.getString("TYPE_NAME")));
                    tableField.setLength(rs.getInt("PRECISION"));
                    tableField.setNotNull("NO".equals(rs.getString("IS_NULLABLE")) ? true : false);
                    tableField.setRemark(rs.getString("COLUMN_NAME"));
                    tableField.setShowInForm(true);
                    tableField.setShowInList(true);
                    tableField.setSortable(true);
                    tableField.setSearchable("varchar".equalsIgnoreCase(tableField.getJdbcType()) ? true : false);
                    tableField.setFormType("singleLineText");
                }


                tableFields.add(tableField);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return tableFields;
    }


}
