package com.eversec.common;

import com.eversec.kit.dev.Table;
import com.jfinal.kit.Kv;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * JdbcKit.
 * 未做连接关闭，只可做为本地main运行测试使用,此工具类不可用于线上使用
 * @author: liangxianyou at 2018/10/8 10:39.
 */
public class JdbcKit {
    private static String url;
    private static String user;
    private static String pwd;

    private static String showTableSql = "SELECT TABLE_NAME,TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%s'"; //查询所有[表名、表备注]，查询时%s需换为对应的数据库
    private static String showTableInfo = "show full columns from %s"; //查询%s表的 完整信息，查询时%s需换为对应的表名

    private static Connection connection;

    static {
        try {
            url = PropKit.getProperty("jdbc.url", "");
            user = PropKit.getProperty("jdbc.user", "");
            pwd = PropKit.getProperty("jdbc.pwd", "");
            connection = DriverManager.getConnection(url, user, pwd);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /*
    #show full columns from sys_userrecord;
    #show tables;
    #SELECT TABLE_NAME,TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE="BASE TABLE" #TABLE_SCHEMA like 'redbbs_dev'
    */

    @Deprecated
    public List _tables(String database) throws SQLException {
        //String showTables = "SELECT TABLE_NAME,TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + database + "'";

        ResultSet rs = executeQuery(String.format(showTableSql, database));

        List tables = new ArrayList();

        while (rs.next()) {
            String tableName = rs.getString("table_name");
            String tableComment = rs.getString("table_comment");

            Kv table = Kv.by("tableName", tableName).set("comment", tableComment);

            ResultSet columns = executeQuery(String.format(showTableInfo, database+ "." +tableName));
            List fields = new ArrayList();
            while (columns.next()) {
                fields.add(Kv.by("name", columns.getString("field"))
                        .set("type", columns.getString("type"))
                        .set("comment", columns.getString("comment"))
                );
            }

            table.set("fields", fields);
            tables.add(table);
        }
        return tables;
    }

    /**
     * 查询数据库中所有的表
     * @param database
     * @return
     */
    public static List<Table> tables(String database, String ... tables) throws SQLException {

        List<Table> list = new ArrayList<>();
        ResultSet rs = null;
        if (tables != null && tables.length > 0) {
            StringBuilder buf = new StringBuilder();
            for (String s : tables) {
                buf.append("'").append(s).append("',");
            }
            buf.deleteCharAt(buf.length() - 1);
            rs = executeQuery(String.format("SELECT TABLE_NAME,TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '%s' and table_name in (%s)", database, buf.toString()));
        } else {
            rs = executeQuery(String.format(showTableSql, database));
        }

        while (rs.next()) {
            Table table = new Table(rs.getString("table_name"), rs.getString("table_comment"));

            List<Table.Column> columns = new ArrayList<>();
            ResultSet rsColumns = executeQuery(String.format(showTableInfo, database+ "." +table.getName()));
            while (rsColumns.next()) {
                Table.Column column = new Table.Column(rsColumns.getString("field"), rsColumns.getString("type"),
                        !rsColumns.getBoolean("null"), rsColumns.getString("comment"));
                columns.add(column);

            }

            table.setColumns(columns);
            list.add(table);
        }

        return list;
    }




    //通过sql 查询数据
    private static ResultSet executeQuery(String sql) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(sql);
        return ps.executeQuery();

        /*try(
                PreparedStatement ps = connection.prepareStatement(sql);
                ResultSet resultSet = ps.executeQuery()
        ) { return resultSet; }*/
    }
}
