package com.muyu.system.service.impl;
import java.text.SimpleDateFormat;
import java.util.Date;
import cn.hutool.core.util.RandomUtil;
import com.muyu.common.core.utils.DateUtils;
import com.muyu.common.system.domain.SysUser;
import com.muyu.system.service.DataBaseCreator;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.support.EncodedResource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.stereotype.Service;

import java.sql.*;

/**
 * 创建数据源业务实现层
 * @Author：weiran
 * @Package：com.muyu.system.service.impl
 * @Project：cloud-server-8
 * @name：DataBaseCreatorImpl
 * @Date：2024/9/26 20:40
 */
@Service
public class DataBaseCreatorImpl implements DataBaseCreator {


    private static final String DB_URL="jdbc:mysql://159.75.188.178:3306?useSSL=false";
    private static final String USER="root";
    private static final String PASS="bwie-8666";


    @Override
    public String createDatbase(SysUser user) {
// 获取当前时间的毫秒值
        long currentTimeMillis = System.currentTimeMillis();

// 使用当前时间的毫秒值创建一个Timestamp对象
        Timestamp currentTimeStamp = new Timestamp(currentTimeMillis);

        Connection conn=null;
        Statement statement=null;
        PreparedStatement preparedStatement=null;
        try {
            //连接数据库
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            statement = conn.createStatement();
            String datbaseName="company"+RandomUtil.randomNumbers(5);
            //创建数据库
            String sqlCreateDatabase ="CREATE DATABASE IF NOT EXISTS "+datbaseName;
            String sqlUseDatabase="USE "+datbaseName;

            statement.execute(sqlCreateDatabase);
            statement.execute(sqlUseDatabase);

            //加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            ClassPathResource rc = new ClassPathResource("saas/eight.sql");
            EncodedResource er = new EncodedResource(rc, "utf-8");
            ScriptUtils.executeSqlScript(conn, er);
            //准备插入数据的SQL语句
            String sqlInsertUser="INSERT INTO `sys_user` (`user_id`,`user_name`, `user_type`, `nick_name`, `email`, `phonenumber`, `sex`, `avatar`, `password`, `status`, `del_flag`,`firm_id`, `create_by`, `create_time`, `update_by`, `update_time`, `remark`) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
            preparedStatement = conn.prepareStatement(sqlInsertUser);
            // 设置参数值（这里假设SysUser类有相应的getter方法）
            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, user.getUserName());
            preparedStatement.setString(3,  "00");
            preparedStatement.setString(4, user.getNickName());
            preparedStatement.setString(5, user.getEmail());
            preparedStatement.setString(6, user.getPhonenumber());
            preparedStatement.setString(7, user.getSex());
            preparedStatement.setString(8, user.getAvatar());
            preparedStatement.setString(9, user.getPassword()); // 请确保密码已经加密
            preparedStatement.setString(10, user.getStatus());
            preparedStatement.setString(11, user.getDelFlag());
            preparedStatement.setInt(12, 0);
            preparedStatement.setString(13, user.getCreateBy());
            preparedStatement.setTimestamp(14, currentTimeStamp);;// 假设createTime是Date类型
            preparedStatement.setString(15, user.getUpdateBy());
            preparedStatement.setTimestamp(16, currentTimeStamp); // 假设updateTime是Date类型，如果为null则可能需要处理
            preparedStatement.setString(17, user.getRemark());

            //执行插入操作
            preparedStatement.executeUpdate();
            return datbaseName;
        }catch (Exception e){
            e.printStackTrace();
            throw new RuntimeException("数据库创建失败");
        }finally {
            try {
                if (preparedStatement!=null){
                    preparedStatement.close();
                }
                if (statement!=null){
                    statement.close();
                }
                if (conn!=null){
                    conn.close();
                }
            }catch (SQLException e) {
                throw new RuntimeException(e);
            }

        }
    }
}
