package com.d2v.web.utils;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

public class SimpleDB {

    private static final Logger logger = LoggerFactory.getLogger(SimpleDB.class);

    private static Map<String, SimpleDB> dbList = new HashMap<String, SimpleDB>();

    /**
     * DB 추가
     * @param name DB 식별자(프로그램용)
     * @param simpleDB SimpleDB 객체
     */
    public static void addDatabase(String name,SimpleDB simpleDB)
    {
        dbList.put(name,simpleDB);
    }

    /**
     * DB 연결정보 추가
     * @param name DB 식별자(프로그램용)
     * @param url DBMS URL 또는 IP
     * @param port DBMS Port
     * @param dbName 사용할 DB 이름
     * @param username User명
     * @param passwd 비밀번호
     * @return SimpleDB 객체
     */
    public static SimpleDB addDatabase(String name, String url, String port, String dbName, String username, String passwd)
    {
        SimpleDB sDB = new SimpleDB(name,url,port,dbName,username,passwd);
        dbList.put(name,sDB);
        return sDB;
    }



    /**
     * 등록된 DB 가져오기
     * @param name 찾을 DB 식별자
     * @return 찾은 SimpleDB 객체, 실패시 Null
     */
    public static SimpleDB getDB(String name)
    {
        if(dbList.containsKey(name))
        {
            return dbList.get(name);
        }
        else return null;
    }


    private String name;
    private String url;
    private String port;
    private String dbName;
    private String username;
    private String passwd;

    List<Connection> conns;
    AtomicInteger connIndex= new AtomicInteger();
    int connMax=1;
    Connection connection;

    /**
     * 생성자
     * @param name DB 식별자(프로그램용)
     * @param url DBMS URL 또는 IP
     * @param port DBMS Port
     * @param dbName 사용할 DB 이름
     * @param username User명
     * @param passwd 비밀번호
     */
    public SimpleDB(String name, String url, String port, String dbName, String username, String passwd) {
        this.name = name;
        this.url = url;
        this.port = port;
        this.dbName = dbName;
        this.username = username;
        this.passwd = passwd;
    }

    public SimpleDB(String name, String url, String port, String dbName, String username, String passwd, int connMax) {
        this.name = name;
        this.url = url;
        this.port = port;
        this.dbName = dbName;
        this.username = username;
        this.passwd = passwd;
        this.connMax = connMax;
        connIndex.set(0);
    }

    /**
     * 연결 확인
     * @return 성공여부
     */
    public boolean test()
    {
        try{
            Connection conn = getConnection();
            if(conn!=null) {

                return true;
            }
            else return false;
        }
        catch (Exception e)
        {
            logger.error("Database 연결 실패 : "+e.getMessage());
            return false;
        }
    }

    public void disconnect() throws SQLException {
        if(connection!=null)
        {
            connection.close();
        }
    }

    public void resetConnection() {
        conns = new ArrayList<>();
    }

    /**
     * Connection 객체 가져오기
     * @return Connection 객체
     */
    public Connection getConnection()
    {
        if (connMax>1) {
            if(conns==null)
                conns = new ArrayList<>();

            if(conns.size() < connIndex.get()+1)
            {
                try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    //TODO Unicode를 입력받을 수 있게 하는게 좋을듯. 기본값은 UTF-8
                    connection = DriverManager.getConnection(
                            "jdbc:oracle:thin:@" + url + ":" + port + "/" + username + "?useUnicode=true&characterEncoding=UTF-8&autoReconnection=true",
                            dbName,
                            passwd);
                    conns.add(connection);
//                    Class.forName("com.mysql.jdbc.Driver");
//                    //TODO Unicode를 입력받을 수 있게 하는게 좋을듯. 기본값은 UTF-8
//                    connection = DriverManager.getConnection(
//                            "jdbc:mysql://" + url + ":" + port + "/" + dbName + "?useUnicode=true&characterEncoding=UTF-8&autoReconnection=true",
//                            username,
//                            passwd);
//                    conns.add(connection);

                    connIndex.getAndIncrement();

                    if(connIndex.get() >=connMax)
                    {
                        connIndex.set(0);
                    }
                    return connection;
                } catch (Exception e) {
                    logger.error("Database 연결에 실패하였습니다.");
                    e.printStackTrace();
                    return null;
                }
            }
            else
            {
                Connection cc = conns.get(connIndex.getAndIncrement());
                if(connIndex.get()==connMax) connIndex.set(0);
                return cc;
            }

        }else {
            if (connection == null) {
                try {
                    Class.forName("oracle.jdbc.driver.OracleDriver");
                    //TODO Unicode를 입력받을 수 있게 하는게 좋을듯. 기본값은 UTF-8
                    connection = DriverManager.getConnection(
                            "jdbc:oracle:thin:@" + url + ":" + port + "/" + username + "?useUnicode=true&characterEncoding=UTF-8&autoReconnection=true",
                            dbName,
                            passwd);
//                    Class.forName("com.mysql.jdbc.Driver");
//                    //TODO Unicode를 입력받을 수 있게 하는게 좋을듯. 기본값은 UTF-8
//                    connection = DriverManager.getConnection(
//                            "jdbc:mysql://" + url + ":" + port + "/" + dbName + "?useUnicode=true&characterEncoding=UTF-8&autoReconnection=true",
//                            username,
//                            passwd);
                    return connection;
                } catch (Exception e) {
                    logger.error("Database 연결에 실패하였습니다.");
                    e.printStackTrace();
                    return null;
                }
            } else return connection;
        }
    }


    public List<Map<String,Object>> getDataFromSQL(String sql)
    {
        List<Map<String,Object>> result = new ArrayList<Map<String, Object>>();

        Connection conn = getConnection();
        if(conn==null) return null;

        PreparedStatement preStmt = null;
        try {
            StringBuilder rsSql = new StringBuilder(sql);
            preStmt = conn.prepareStatement(rsSql.toString());
            ResultSet rs = preStmt.executeQuery();
            preStmt.clearParameters();
            ResultSetMetaData rsmd = rs.getMetaData();
            // 컬럼의 갯수
            int count = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> colList = new HashMap<String, Object>();

                // 컬럼의 시작은 1부터
                for(int i = 1; i <= count; i++){
                    /*
                    BIT: // BIT(-7)
                    TINYINT: // TINYINT(-6)
                    SMALLINT:// SMALLINT(5)
                    INTEGER: // INTEGER(4)
                    BIGINT: // BIGINT(-5)
                    FLOAT: // FLOAT(6)
                    REAL: // REAL(7)
                    DOUBLE: // DOUBLE(8)
                    NUMERIC: // NUMERIC(2)
                    DECIMAL: // DECIMAL(3)
                    CHAR: // CHAR(1)
                    VARCHAR: // VARCHAR(12)
                    LONGVARCHAR: // LONGVARCHAR(-1)
                    DATE: // DATE(91)
                    TIME: // TIME(92)
                    TIMESTAMP: // TIMESTAMP(93)
                    BINARY: // BINARY(-2)
                    VARBINARY: // VARBINARY(-3)
                    LONGVARBINARY: // LONGVARBINARY(-4)
                     */
                    // TYPE 를 구분하며 출력하기 위해 switch 를 사용한다.
                    switch(rsmd.getColumnType(i)){
                        case Types.BIT:
                            colList.put(rsmd.getColumnName(i), rs.getBoolean(i)); break;
                        case Types.BIGINT :
                            colList.put(rsmd.getColumnName(i), rs.getLong(i)); break;
                        case Types.NUMERIC :
                        case Types.INTEGER :
                            colList.put(rsmd.getColumnName(i), rs.getInt(i)); break;
                        case Types.FLOAT :
                            colList.put(rsmd.getColumnName(i), rs.getFloat(i)); break;
                        case Types.DOUBLE :
                            colList.put(rsmd.getColumnName(i),  rs.getDouble(i)); break;
                        case Types.DATE :
                            colList.put(rsmd.getColumnName(i),  rs.getDate(i)); break;
                        case Types.TIMESTAMP :
                            colList.put(rsmd.getColumnName(i),  rs.getTimestamp(i)); break;
                        case Types.CHAR :
                        default :
                            colList.put(rsmd.getColumnName(i),  rs.getString(i));
                    }
                }

                result.add(colList);
            }
            rs.close();
        }catch (Exception e)
        {
            logger.error(e.getMessage());
            e.printStackTrace();
        }finally {
            try {
                preStmt.close();

            } catch (SQLException e) {
                logger.error(e.getMessage());
                e.printStackTrace();
            }
        }

        return result;

    }


    /**
     * table에서 필요한 Column들의 데이터를 가져온다.
     * @param tableName 대상 Table명
     * @param columns 컬럼명 집합 (List)
     * @return 결과 집합 List(Map(컬럼명, 데이터))
     */
    public List<Map<String,Object>> getTableColumns(String tableName, List<String> columns)
    {
        return getTableColumns(tableName, columns,"");
    }

    /**
     * table에서 조건을 주어 필요한 Column들의 데이터를 가져온다.
     * @param tableName 대상 Table명
     * @param columns 컬럼명 집합 (List)
     * @param condition 조건 ( where , limit 등 )
     * @return 결과 집합 List(Map(컬럼명, 데이터))
     */
    public List<Map<String,Object>> getTableColumns(String tableName, List<String> columns,String condition)
    {
        List<Map<String,Object>> result = new ArrayList<Map<String, Object>>();

        Connection conn = getConnection();
        if(conn==null) return null;

        StringBuilder cols = new StringBuilder();
        if(columns==null) cols.append("*");
        else {
            for (String column : columns) {
                if (!cols.toString().equals("")) cols.append(", ");
                cols.append(column);
            }
        }

        PreparedStatement preStmt = null;
        try {
            StringBuilder rsSql = new StringBuilder("SELECT " + cols.toString() + " FROM " + dbName + "." + tableName + " "+ condition);
            preStmt = conn.prepareStatement(rsSql.toString());
            ResultSet rs = preStmt.executeQuery();
            preStmt.clearParameters();
            ResultSetMetaData rsmd = rs.getMetaData();
            // 컬럼의 갯수
            int count = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> colList = new HashMap<>();

                // 컬럼의 시작은 1부터
                for(int i = 1; i <= count; i++){
                    /*
                    BIT: // BIT(-7)
                    TINYINT: // TINYINT(-6)
                    SMALLINT:// SMALLINT(5)
                    INTEGER: // INTEGER(4)
                    BIGINT: // BIGINT(-5)
                    FLOAT: // FLOAT(6)
                    REAL: // REAL(7)
                    DOUBLE: // DOUBLE(8)
                    NUMERIC: // NUMERIC(2)
                    DECIMAL: // DECIMAL(3)
                    CHAR: // CHAR(1)
                    VARCHAR: // VARCHAR(12)
                    LONGVARCHAR: // LONGVARCHAR(-1)
                    DATE: // DATE(91)
                    TIME: // TIME(92)
                    TIMESTAMP: // TIMESTAMP(93)
                    BINARY: // BINARY(-2)
                    VARBINARY: // VARBINARY(-3)
                    LONGVARBINARY: // LONGVARBINARY(-4)
                     */
                    // TYPE 를 구분하며 출력하기 위해 switch 를 사용한다.
                    switch(rsmd.getColumnType(i)){
                        case Types.BIT:
                            colList.put(rsmd.getColumnName(i), rs.getBoolean(i)); break;
                        case Types.BIGINT :
                            colList.put(rsmd.getColumnName(i), rs.getLong(i)); break;
                        case Types.NUMERIC :
                        case Types.INTEGER :
                            colList.put(rsmd.getColumnName(i), rs.getInt(i)); break;
                        case Types.FLOAT :
                            colList.put(rsmd.getColumnName(i), rs.getFloat(i)); break;
                        case Types.DOUBLE :
                            colList.put(rsmd.getColumnName(i),  rs.getDouble(i)); break;
                        case Types.DATE :
                            colList.put(rsmd.getColumnName(i),  rs.getDate(i)); break;
                        case Types.TIMESTAMP :
                            colList.put(rsmd.getColumnName(i),  rs.getTimestamp(i)); break;
                        case Types.CHAR :
                        default :
                            colList.put(rsmd.getColumnName(i),  rs.getString(i));
                    }
                }

                result.add(colList);
            }
            rs.close();
        }catch (Exception e)
        {
            logger.error(e.getMessage());
            e.printStackTrace();
        }finally {
            try {
                preStmt.close();

            } catch (SQLException e) {
                logger.error(e.getMessage());
                e.printStackTrace();
            }
        }

        return result;

    }

    /**
     * 테이블의 데이터를 업데이트한다.
     * @param tableName 대상 테이블명
     * @param pk Primary Key Column명
     * @param colData 업데이트할 항목 및 데이터 ( Primary Key 포함 )
     * @return 성공여부
     */
    public boolean updateData(String tableName,String pk , Map<String,Object> colData)
    {
        Connection conn = getConnection();
        if(conn==null) return false;

        StringBuilder cols = new StringBuilder();
        for(String col : colData.keySet())
        {
            if(col.equals(pk)) continue;
            if(!cols.toString().equals("")) cols.append(", ");
            cols.append(col+" = ?");
        }

        PreparedStatement preStmt = null;
        try {
            //UPDATE `2017_heqm`.`worknet_crawling_recruit` SET `rec_detail_parsed`='test' WHERE  `id`=81;
            StringBuilder rsSql = new StringBuilder("UPDATE " + dbName + "." + tableName + " SET " + cols.toString() + " WHERE "+pk+"= ?");
            preStmt = conn.prepareStatement(rsSql.toString());
            int i=1;
            for(String col : colData.keySet())
            {
                if(col.equals(pk)) continue;

                if(colData.get(col) instanceof Integer) {
                    if(colData.get(col)==null)
                        preStmt.setNull(i++,Types.INTEGER);
                    else
                        preStmt.setInt(i++, (Integer) colData.get(col));
                }
                else if(colData.get(col) instanceof Long) {
                    if(colData.get(col)==null)
                        preStmt.setNull(i++,Types.BIGINT);
                    else
                        preStmt.setLong(i++, (Long) colData.get(col));
                }
                else{
                    if(colData.get(col)==null)
                        preStmt.setNull(i++,Types.VARCHAR);
                    else
                        preStmt.setString(i++,colData.get(col).toString());
                }
            }
            if(colData.get(pk) instanceof Integer) {
                if(colData.get(pk)==null)
                    preStmt.setNull(i,Types.INTEGER);
                else
                    preStmt.setInt(i, (Integer) colData.get(pk));
            }
            else if(colData.get(pk) instanceof Long) {
                if(colData.get(pk)==null)
                    preStmt.setNull(i,Types.BIGINT);
                else
                    preStmt.setLong(i, (Long) colData.get(pk));
            }
            else{
                if(colData.get(pk)==null)
                    preStmt.setNull(i,Types.VARCHAR);
                else
                    preStmt.setString(i,colData.get(pk).toString());
            }

            preStmt.executeUpdate();
            preStmt.clearParameters();
        }catch (Exception e)
        {
            logger.error(e.getMessage());
            e.printStackTrace();
        }finally {
            try {
                preStmt.close();

            } catch (SQLException e) {
                logger.error(e.getMessage());
                e.printStackTrace();
            }
        }

        return true;

    }

    /**
     * 테이블의 데이터를 삭제한다.
     * @param tableName 대상 테이블 명
     * @param condition 대상 조건
     * @return
     */
    public boolean deleteData(String tableName,String condition)
    {
        Connection conn = getConnection();
        if(conn==null) return false;


        PreparedStatement preStmt = null;
        try {
            //DELETE FROM `2017_heqm_task`.`analyze_ncs_resume_maching` WHERE  `idx`=24;
            StringBuilder rsSql = new StringBuilder("DELETE FROM " + dbName + "." + tableName + " WHERE "+condition);
            preStmt = conn.prepareStatement(rsSql.toString());
            preStmt.executeUpdate();
            preStmt.clearParameters();
        }catch (Exception e)
        {
            logger.error(e.getMessage());
            e.printStackTrace();
        }finally {
            try {
                preStmt.close();

            } catch (SQLException e) {
                logger.error(e.getMessage());
                e.printStackTrace();
            }
        }

        return true;
    }


    public boolean InsertData(String tableName,Map<String,Object> colData)
    {
        Connection conn = getConnection();
        if(conn==null) return false;

        StringBuilder cols = new StringBuilder();
        StringBuilder datas = new StringBuilder();
        for(String col : colData.keySet())
        {
            if(!cols.toString().equals("")){
                cols.append(", ");
                datas.append(", ");
            }
            cols.append("`"+col+"`");
            datas.append("?");
        }

        PreparedStatement preStmt = null;
        try {
            //INSERT INTO `2017_heqm_task`.`analyze_ncs_resume_maching` (`resume_id`, `ncs_id`, `checker`) VALUES ('3', '4', 'test2');
            StringBuilder rsSql = new StringBuilder("INSERT INTO " + dbName + "." + tableName + " ( " + cols.toString() + " ) VALUES ( "+datas.toString()+" )");
            preStmt = conn.prepareStatement(rsSql.toString());
            int i=1;
            for(String col : colData.keySet())
            {
                if(colData.get(col) instanceof Integer)
                    preStmt.setInt(i++,(Integer)colData.get(col));
                else preStmt.setString(i++,(String)colData.get(col));
            }
            preStmt.executeUpdate();
            preStmt.clearParameters();
        }catch (Exception e)
        {
            logger.error(e.getMessage());
            e.printStackTrace();
        }finally {
            try {
                preStmt.close();

            } catch (SQLException e) {
                logger.error(e.getMessage());
                e.printStackTrace();
            }
        }

        return true;
    }


    public boolean InsertDataList(String tableName,List<Map<String,Object>> colData)
    {
        boolean end = true;
        Connection conn = getConnection();
        if(conn==null) return false;
        if(colData.size()==0) return false;

        StringBuilder cols = new StringBuilder();
        StringBuilder datas = new StringBuilder();
        for(String col : colData.get(0).keySet())
        {
            if(!cols.toString().equals("")){
                cols.append(", ");
                datas.append(", ");
            }
            cols.append(col);
            datas.append("?");
        }

        PreparedStatement preStmt = null;
        try {
            //INSERT INTO `2017_heqm_task`.`analyze_ncs_resume_maching` (`resume_id`, `ncs_id`, `checker`) VALUES ('3', '4', 'test2');
            StringBuilder rsSql = new StringBuilder("INSERT INTO " + dbName + "." + tableName + "( " + cols.toString() + " ) VALUES ( "+datas.toString()+" )");
            preStmt = conn.prepareStatement(rsSql.toString());
            for(Map<String,Object> data : colData) {
                int i = 1;
                for (String col : data.keySet()) {
                    if (data.get(col) instanceof Integer)
                        preStmt.setInt(i++, (Integer) data.get(col));
                    else preStmt.setString(i++, (String) data.get(col));
                }

                preStmt.addBatch();
                preStmt.clearParameters();
            }
            preStmt.executeBatch();
            end = true;
        }catch (Exception e)
        {
            logger.error(e.getMessage());
            e.printStackTrace();
            end = false;
        }finally {
            try {
                preStmt.close();

            } catch (SQLException e) {
                logger.error(e.getMessage());
                e.printStackTrace();
            }
        }

        return end;
    }


    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getUrl() {
        return url;
    }

    public void setUrl(String url) {
        this.url = url;
    }

    public String getPort() {
        return port;
    }

    public void setPort(String port) {
        this.port = port;
    }

    public String getDbName() {
        return dbName;
    }

    public void setDbName(String dbName) {
        this.dbName = dbName;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPasswd() {
        return passwd;
    }

    public void setPasswd(String passwd) {
        this.passwd = passwd;
    }
}