package repositories.Control;

import repositories.model.Goods;
import repositories.util.DB;
import repositories.util.ScannerChoice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * Created by bonismo
 * 15/7/18 下午4:04
 * <p>
 * 数据库 Goods 表  数据操作
 */
public final class GoodsControl {
    private Connection connection = null;
    private PreparedStatement statement = null;
    ResultSet resultSet = null;

    /**
     * 添加商品信息 到数据库 Goods 商品表
     *
     * @param goods 商品模型类的实例
     * @return boolean
     */
    public boolean addGoods(Goods goods) {
        boolean flag = false;

        String sqlAdd = "INSERT INTO db_shopping_management.goods VALUES (NULL , ?, ?, ?);";

        connection = DB.getConnection();
        statement = null;

        try {
            if (connection != null) {
                statement = connection.prepareStatement(sqlAdd);
            } else {
                return false;
            }
            statement.setString(1, goods.getGoods_name());
            statement.setDouble(2, goods.getGoods_price());
            statement.setInt(3, goods.getGoods_num());

            int rs = statement.executeUpdate();

            if (rs > 0) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DB.close(null, statement, connection);
        }
        return flag;
    }

    /**
     * 商品信息更新   Goods 商品表
     *
     * @param key   更改商品的选项
     * @param goods 商品模型类的实例
     * @return boolean
     */
    public boolean updateGoods(int key, Goods goods) {
        boolean flag = false;
        connection = DB.getConnection();

        switch (key) {
            case 1: // key = 1，更改商品名称
                String updateName = "UPDATE db_shopping_management.goods SET goods_name = ? WHERE id = ?";
                try {
                    if (connection != null) {
                        statement = connection.prepareStatement(updateName);
                    } else {
                        return false;
                    }
                    statement.setString(1, goods.getGoods_name());
                    statement.setInt(2, goods.getId());

                    int rs = statement.executeUpdate();
                    if (rs > 0) {
                        flag = true;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DB.close(null, statement, connection);
                }
                break;
            case 2: // key = 2 ，更改商品价格
                String updatePrice = "UPDATE db_shopping_management.goods SET goods_price = ? WHERE id = ?";
                try {
                    if (connection != null) {
                        statement = connection.prepareStatement(updatePrice);
                    } else {
                        return false;
                    }
                    statement.setDouble(1, goods.getGoods_price());
                    statement.setInt(2, goods.getId());

                    int rs = statement.executeUpdate();
                    if (rs > 0) {
                        flag = true;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DB.close(null, statement, connection);
                }
                break;
            case 3: // key = 3 ，更改商品数量
                String updateNum = "UPDATE db_shopping_management.goods SET goods_num = ? WHERE id = ?";
                try {
                    statement = connection.prepareStatement(updateNum);
                    statement.setInt(1, goods.getGoods_num());
                    statement.setInt(2, goods.getId());

                    int rs = statement.executeUpdate();
                    if (rs > 0) {
                        flag = true;
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                } finally {
                    DB.close(null, statement, connection);
                }
                break;
            default:
                break;
        }
        return flag;
    }

    /**
     * 删除商品信息  Goods 商品表
     *
     * @param id 商品表的 主键 ID
     * @return boolean
     */
    public boolean deleteGoods(int id) {
        boolean flag = false;
        connection = DB.getConnection();
        String deleteId = "DELETE FROM db_shopping_management.goods WHERE id = ?";

        try {
            statement = connection.prepareStatement(deleteId);
            statement.setInt(1, id);
            int rs = statement.executeUpdate();
            if (rs > 0) {
                flag = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DB.close(null, statement, connection);
        }
        return flag;
    }

    /**
     * 查询 方法重构
     *
     * @param goodsArrayList 商品信息集合容器
     * @param query          Mysql 查询语句
     */
    public static void queryAll(ArrayList<Goods> goodsArrayList, String query) {
        Connection connection = DB.getConnection();
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            statement = connection.prepareStatement(query);
            resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String goods_name = resultSet.getString("goods_name");
                Double goods_price = resultSet.getDouble("goods_price");
                int goods_num = resultSet.getInt("goods_num");

                // 获取结果集，根据有参构造，传参
                Goods goods = new Goods(id, goods_name, goods_price, goods_num);
                // 将类信息添加到集合容器
                goodsArrayList.add(goods);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DB.close(resultSet, statement, connection);
        }
    }

    /**
     * 查询商品信息
     *
     * @param key 查询选项
     * @return ArrayList<Goods>
     */
    public ArrayList<Goods> queryGoods(int key) {
        ArrayList<Goods> goodsArrayList = new ArrayList<>();
        connection = DB.getConnection();
        switch (key) {
            case 1: // 查询商品数量，默认升序
                String selNum = "SELECT * FROM db_shopping_management.goods ORDER BY goods_num";
                queryAll(goodsArrayList, selNum);
                break;
            case 2: // 查询商品价格，默认升序
                String selPrice = "SELECT * FROM db_shopping_management.goods ORDER BY goods_price";
                queryAll(goodsArrayList, selPrice);
                break;
            case 3: // 查询商品名字
                String nameGet = ScannerChoice.scannerInfoString();
                nameGet = "%" + nameGet + "%";
                String sqlGoodsName = "SELECT * FROM db_shopping_management.goods WHERE goods.goods_name LIKE ?";
                connection = DB.getConnection();
                try {
                    statement = connection.prepareStatement(sqlGoodsName);
                    statement.setString(1, nameGet);

                } catch (SQLException e) {
                    e.printStackTrace();
                }
        }
        return goodsArrayList;
    }

    /**
     * 显示商品所有信息
     *
     * @return ArrayList<Goods>
     */
    public ArrayList<Goods> displayGoods() {
        ArrayList<Goods> goodsArrayList = new ArrayList<>();
        String query = "SELECT * FROM db_shopping_management.goods";
        queryAll(goodsArrayList, query);
        return goodsArrayList;
    }
}
