public class CardInfo {
    private int number;
    private double balance;
	
	//此处用于初始化银行卡状态
    private String status = "可用";
    private int accountid;
    private int bankid;

    @Override
	
	//用于显示银行卡内数据
    public String toString() {
        StringBuilder stringBuilder = new StringBuilder();
        stringBuilder.append("卡号：").append(number);
        stringBuilder.append("\t余额：").append(balance);
        stringBuilder.append("\t状态：").append(status);
        stringBuilder.append("\t账户id：").append(accountid);
        stringBuilder.append("\t银行id：").append(bankid);
        return stringBuilder.toString();
    }

    public int getNumber() {
        return number;
    }

    public void setNumber(int number) {
        this.number = number;
    }

    public double getBalance() {
        return balance;
    }

    public void setBalance(double balance) {
        this.balance = balance;
    }

    public String getStatus() {
        return status;
    }

    public void setStatus(String status) {
        this.status = status;
    }

    public int getAccountid() {
        return accountid;
    }

    public void setAccountid(int accountid) {
        this.accountid = accountid;
    }

    public int getBankid() {
        return bankid;
    }

    public void setBankid(int bankid) {
        this.bankid = bankid;
    }
}

public class CardDAO {
    //增加、修改、查询

    //增加数据
    public void saveCardInfo() {
        CardInfo cardInfo = new CardInfo();
        Connection connection = DBUtils.getConnection();
        Statement statement = null;
        Scanner scanner = new Scanner(System.in);
        System.out.println("请设置卡号：");
        int number = scanner.nextInt();
        if (judge(number)) {
            try {
                statement = connection.createStatement();

                cardInfo.setNumber(number);

                System.out.println("请设置卡内初始余额：");
                cardInfo.setBalance(scanner.nextDouble());

                System.out.println("请设置银行卡所属账户ID：");
                cardInfo.setAccountid(scanner.nextInt());

                System.out.println("请设置银行卡所属银行ID：");
                cardInfo.setBankid(scanner.nextInt());


                String sql1 = "insert into t_bank_card values(%d,%f,'%s',%d,%d)";

            String sql2 = String.format(sql1, cardInfo.getNumber(), cardInfo.getBalance(),
                    cardInfo.getStatus(), cardInfo.getAccountid(), cardInfo.getBankid());

                System.out.println(sql2);

                statement.executeUpdate(sql2);
                scanner.close();
                DBUtils.close(statement, connection);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                scanner.close();
                DBUtils.close(statement, connection);
            }
        } else {
            System.out.println("该卡号已存在！");
        }
    }

    //修改数据(暂定,没啥用)
    public void updateCardInfo() {
        //todo
        CardInfo cardInfo = new CardInfo();
        Connection connection = DBUtils.getConnection();
        Statement statement = null;
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要修改的卡号：");
        int number = scanner.nextInt();
        if (judge(number) == false) {
            try {
                statement = connection.createStatement();

                System.out.println("请输入新的卡号：");
                cardInfo.setNumber(scanner.nextInt());

                System.out.println("请输入新的余额：");
                cardInfo.setBalance(scanner.nextDouble());

                String status = "不可用";

                System.out.println("请输入新的账户ID：");
                cardInfo.setAccountid(scanner.nextInt());

                System.out.println("请输入新的银行ID：");
                cardInfo.setBankid(scanner.nextInt());

                String sql1 = "update t_bank_card set " +
                        "card_number = %d,balance = %f,status = '%s',account_id = %d,bank_id = %d" +
                        "where card_number = " + number;
            String sql2 = String.format(sql1, cardInfo.getNumber(), cardInfo.getBalance(),
                    cardInfo.getStatus(), cardInfo.getAccountid(), cardInfo.getBankid());

                System.out.println(sql2);

                statement.executeUpdate(sql2);
                scanner.close();
                DBUtils.close(statement, connection);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                scanner.close();
                DBUtils.close(statement, connection);
            }
        } else {
            System.out.println("此卡号不存在！");
        }
    }

    //删除银行卡数据,改变银行卡状态为不可用
    public void deleteCardInfo() {
        Connection connection = DBUtils.getConnection();
        Statement statement = null;
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要删除的卡号：");
        int number = scanner.nextInt();
        if (judge(number) == false) {
            try {
                statement = connection.createStatement();

                String status="不可用";

                String sql1 = "update t_bank_card set " +
                        "status='%s'" + status;
            String sql2 = String.format(sql1, number);

                statement.executeUpdate(sql2);
                System.out.println(sql2);

                scanner.close();
                DBUtils.close(statement, connection);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                scanner.close();
                DBUtils.close(statement, connection);
            }
        } else {
            System.out.println("此卡号不存在！");
        }
    }

    //显示银行卡数据(暂定,没啥用)
    public void showCardInfo() {
        //todo
        Connection connection = DBUtils.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from t_bank_card");

            List<CardInfo> list = new ArrayList<>();
            while (resultSet.next()) {
                CardInfo cardInfo = new CardInfo();
                cardInfo.setNumber(resultSet.getInt("card_number"));
                cardInfo.setBalance(resultSet.getInt("balance"));
                cardInfo.setStatus((resultSet.getString("status")));
                cardInfo.setAccountid(resultSet.getInt("account_id"));
                cardInfo.setBankid(resultSet.getInt("bank_id"));
                list.add(cardInfo);
            }
            for (CardInfo cardInfo : list) {
                System.out.println(cardInfo);
            }
            DBUtils.close(resultSet, statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, statement, connection);
        }
    }

    //条件查询1：卡号查询（通过卡号找到银行卡信息）
    public static CardInfo findByNumber(int number) {
        CardInfo cardInfo = new CardInfo();
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("select * from T_BANK_CARD where card_number = " + number);
            if (resultSet == null) {
                System.out.println("空值");
            } else {
                while (resultSet.next()) {
                    cardInfo.setNumber(resultSet.getInt("card_number"));
                    cardInfo.setBalance(resultSet.getDouble("balance"));
                    cardInfo.setStatus(resultSet.getString("status"));
                    cardInfo.setAccountid(resultSet.getInt("account_id"));
                    cardInfo.setBankid(resultSet.getInt("bank_id"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, statement, connection);
        }
        return cardInfo;
    }

    //判断数据库是否存在数据,true代表无数据，false代表有数据
    public static boolean judge(int number) {
        boolean judgement = false;
        Connection connection = DBUtils.getConnection();
        Statement statement = null;
        ResultSet resultSet = null;
        try {
            int login = 0;
            statement = connection.createStatement();
            resultSet = statement.executeQuery(
                    "select count(1) from T_BANK_CARD where card_number=" + number);
            while (resultSet.next()) {
                login = resultSet.getInt("count(1)");
            }
            if (login == 0) {
                judgement = true;
            } else {
                judgement = false;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(resultSet, statement, connection);
            return judgement;
        }
    }

    //余额增加
    public void inputMoney(double money,int number) {
        Connection connection = DBUtils.getConnection();
        Statement statement = null;
        try {
            statement = connection.createStatement();
            String string = "UPDATE T_BANK_CARD SET balance = BALANCE + %f WHERE CARD_NUMBER = %d";
            String sql = String.format(string, money, number);
            statement.executeUpdate(sql);
            DBUtils.close(statement, connection);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(statement,connection);
        }
    }

    //余额减少
    public void outputMoney(double money, int number) {
        //先判断转出账户存在且余额足够
        if (!judge(number) && findByNumber(number).getBalance() >= money) {
            Connection connection = DBUtils.getConnection();
            Statement statement = null;
            try {
                statement = connection.createStatement();
                String string = "UPDATE T_BANK_CARD SET balance = BALANCE - %f WHERE CARD_NUMBER = %d";
                String sql = String.format(string, money, number);
                statement.executeUpdate(sql);
                DBUtils.close(statement, connection);
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                DBUtils.close(statement, connection);
            }
        } else if (judge(number)) {
            System.out.println("该账户不存在！");
        } else {
            System.out.println("账户余额不足！");
        }
    }
}