package com.bcxin.file;

import com.bcxin.file.model.FileLog;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.*;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;
import java.util.stream.Collectors;

public class DataBaseUtil {

    Connection connection = null;
    private static final String connectionURL = "jdbc:mysql://114.115.153.118:3306/test_ftpser?useUnicode=true&characterEncoding=utf8&useCompression=true&serverTimezone=GMT%2B8&useSSL=false";

    public DataBaseUtil() throws ClassNotFoundException, IllegalAccessException, InstantiationException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
        connection = DriverManager.getConnection(connectionURL, "root", "Bcx#20202008");
    }

    public List<FileLog> readData2(List<FileLog> fileLogs) {
        ResultSet rs = null;
        PreparedStatement psmnt = null;
        List<FileLog> logList = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder("select fileUrl,md5,id from file_download_log_ex where md5 like 'none%'");
            System.out.println(sql.toString());
            psmnt = connection.prepareStatement(sql.toString());
            rs = psmnt.executeQuery();
            FileLog fileLog = null;
            Collection<String> list = new ArrayList<>();
            while (rs.next()) {
                fileLog = new FileLog();
                String id = rs.getString(3);
                String fileUrl = rs.getString(1);
                System.out.println("id-----------------"+id);
                System.out.println("fileUrl-------------"+fileUrl);
                String MD5fileUrl = MD5Util.encryptToMD5(fileUrl);
                System.out.println("MD5fileUrl-------------"+MD5fileUrl);
                StringBuilder sql2 = new StringBuilder("update file_download_log_ex set md5 = '");
                sql2.append(MD5fileUrl);
                sql2.append("' where id = '");
                sql2.append(id);
                sql2.append("';");
                String sql22 = sql2.toString();
                list.add(sql22);
                System.out.println("sql2------------"+sql2);
            }
            StringBuilder sqlL = new StringBuilder("");
            for (String s : list) {
                sqlL.append(s);
            }
            System.out.println(sqlL);
            int count = psmnt.executeUpdate(sqlL.toString());
            System.out.println("成功执行了："+count);


        } catch (Exception ex) {
            System.out.println("Found some error : " + ex);
        } finally {
            try {
                psmnt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return logList;
    }

    public List<FileLog> readData(List<FileLog> fileLogs) {
        ResultSet rs = null;
        PreparedStatement psmnt = null;
        List<FileLog> logList = new ArrayList<>();
        try {
            StringBuilder sql = new StringBuilder("select fileUrl,md5 from file_download_log_ex where md5 in (");
            for (FileLog fileLog : fileLogs) {
                //sql.append("'" + fileLog.getMd5() + "',");
                sql.append(fileLog.getSqlWhere() + ",");
            }
            sql.deleteCharAt(sql.length() - 1);
            sql.append(")");
            System.out.println(sql.toString());
            psmnt = connection.prepareStatement(sql.toString());
            rs = psmnt.executeQuery();

            FileLog fileLog = null;
            while (rs.next()) {
                fileLog = new FileLog();
                fileLog.setFileUrl(rs.getString(1));
                fileLog.setMd5(rs.getString(2));
                logList.add(fileLog);
            }
        } catch (Exception ex) {
            System.err.println(String.format("如下文件出现异常核查:%s; 将直接从系统下载附件:[%s] ", ex,
                    fileLogs.stream().map(ii -> ii.getFileUrl()).collect(Collectors.joining(";"))
            ));
        } finally {
            try {
                psmnt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return logList;
    }

    public int saveData(List<FileLog> logList){
        PreparedStatement psmnt = null;
        try {
            StringBuilder sql = new StringBuilder("insert into file_download_log_ex(createTime,fileUrl,md5) values");
            for (FileLog fileLog : logList) {
                sql.append("(now(),'" + fileLog.getFileUrl() + "','" + fileLog.getMd5() + "'),");
            }
            sql.deleteCharAt(sql.length() - 1);
            System.err.println(sql.toString());
            psmnt = connection.prepareStatement(sql.toString());
            return psmnt.executeUpdate();
        } catch (Exception ex) {
            System.err.println("Found some error : " + ex);
        } finally {
            try {
                psmnt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    public void close() {
        /*
        try {
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }

         */
    }
}
