package JDBC;

import pojo.IcCard;
import pojo.ParkingSpot;
import pojo.User;
import pojo.Vehicle;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class UserJDBC {
    public UserJDBC() {
        super();
    }

    public Connection getConnection() throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/parkinglot", "root", "root");
//        return DriverManager.getConnection("jdbc:mysql://121.199.5.54:3306/parkinglot", "root", "anhengyu1");
    }

    public void templateMethod() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("");

        conn.close();
    }

    public List<User> selectAllUser() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT *\n" +
                "FROM userinformation");
        ResultSet rs = ps.executeQuery();
        List<User> userList = new ArrayList<>();
        while(rs.next()) {
            User u = new User(
                    rs.getInt("user_id"),
                    rs.getString("name"),
                    rs.getString("telephone"),
                    rs.getString("user_type"),
                    rs.getInt("parking_spot_id"),
                    rs.getString("address"),
                    rs.getString("gender")
            );
            userList.add(u);
        }
        conn.close();
        return userList;
    }

    public List<User> selectUserByNPA(String name, int parking_spot_id, String address) throws Exception {
        Connection conn = this.getConnection();
        String sql = "SELECT * FROM userinformation";
        StringBuilder whereClause = new StringBuilder(" WHERE ");

        if (name != null && !name.isEmpty()) {
            whereClause.append("name LIKE '%").append(name).append("%' AND ");
        }
        if (parking_spot_id!=0 && !String.valueOf(parking_spot_id).isEmpty()) {
            whereClause.append("parking_spot_id = '").append(parking_spot_id).append("' AND ");
        }
        if (address != null && !address.isEmpty()) {
            whereClause.append("address = '").append(address).append("' AND ");;
        }

        if (whereClause.toString().contains(" AND ")) {
            whereClause.delete(whereClause.lastIndexOf(" AND "), whereClause.length());
        }
        if(!" WHERE ".equals(whereClause.toString())) {
            sql += whereClause.toString();
        }
        System.out.println("sql:"+ sql);
        PreparedStatement ps = conn.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        List<User> userList = new ArrayList<>();
        while(rs.next()) {
            User u = new User(
                    rs.getInt("user_id"),
                    rs.getString("name"),
                    rs.getString("telephone"),
                    rs.getString("user_type"),
                    rs.getInt("parking_spot_id"),
                    rs.getString("address"),
                    rs.getString("gender")
            );
            userList.add(u);
        }
        conn.close();
        return userList;
    }
    public List<User> SortUserByUserId() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM userinformation ORDER BY user_id");
        ResultSet rs = ps.executeQuery();
        List<User> userList = new ArrayList<>();
        while(rs.next()) {
            User u = new User(
                    rs.getInt("user_id"),
                    rs.getString("name"),
                    rs.getString("telephone"),
                    rs.getString("user_type"),
                    rs.getInt("parking_spot_id"),
                    rs.getString("address"),
                    rs.getString("gender")
            );
            userList.add(u);
        }
        conn.close();
        return userList;
    }

    public List<User> SortUserByParkId() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM userinformation ORDER BY parking_spot_id");
        ResultSet rs = ps.executeQuery();
        List<User> userList = new ArrayList<>();
        while(rs.next()) {
            User u = new User(
                    rs.getInt("user_id"),
                    rs.getString("name"),
                    rs.getString("telephone"),
                    rs.getString("user_type"),
                    rs.getInt("parking_spot_id"),
                    rs.getString("address"),
                    rs.getString("gender")
            );
            userList.add(u);
        }
        conn.close();
        return userList;
    }

    public List<User> SortUserByName() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM userinformation ORDER BY name");
        ResultSet rs = ps.executeQuery();
        List<User> userList = new ArrayList<>();
        while(rs.next()) {
            User u = new User(
                    rs.getInt("user_id"),
                    rs.getString("name"),
                    rs.getString("telephone"),
                    rs.getString("user_type"),
                    rs.getInt("parking_spot_id"),
                    rs.getString("address"),
                    rs.getString("gender")
            );
            userList.add(u);
        }
        conn.close();
        return userList;
    }

    public Map<String, Integer> CntUserByTotalUser() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT COUNT(*) AS total_users FROM userinformation");
        ResultSet rs = ps.executeQuery();
        Map<String, Integer> totalUserMap = new HashMap<>();
        if (rs.next()) {
            int totalUsers = rs.getInt("total_users");
            totalUserMap.put("total_users", totalUsers);
        }
        conn.close();
        return totalUserMap;
    }


    public Map<String, Integer> CntUserByGender() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT gender, COUNT(*) AS gender_count FROM userinformation GROUP BY gender");
        ResultSet rs = ps.executeQuery();
        Map<String, Integer> genderCountMap = new HashMap<>();
        while (rs.next()) {
            String gender = rs.getString("gender");
            int count = rs.getInt("gender_count");
            genderCountMap.put(gender, count);
        }
        conn.close();
        return genderCountMap;
    }


    public Map<String, Integer> CntUserByUserType() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT user_type, COUNT(*) AS user_count FROM userinformation GROUP BY user_type");
        ResultSet rs = ps.executeQuery();
        Map<String, Integer> userTypeCountMap = new HashMap<>();
        while (rs.next()) {
            String userType = rs.getString("user_type");
            int count = rs.getInt("user_count");
            userTypeCountMap.put(userType, count);
        }
        conn.close();
        return userTypeCountMap;
    }

    public List<Vehicle> selectCarByUserId(int user_id) throws Exception {
        Connection conn = this.getConnection();
        String query = "SELECT v.car_license, v.user_id, v.brand, v.model " +
                "FROM vehicleinformation v " +
                "JOIN userinformation u ON v.user_id = u.user_id " +
                "WHERE u.user_id = ? " +
                "ORDER BY v.car_license";

        PreparedStatement ps = conn.prepareStatement(query);
        ps.setInt(1, user_id);
        ResultSet rs = ps.executeQuery();

        List<Vehicle> carlist = new ArrayList<>();
        while (rs.next()) {
            Vehicle car = new Vehicle(
                    rs.getString("car_license"),
                    rs.getInt("user_id"),
                    rs.getString("brand"),
                    rs.getString("model")
            );
            carlist.add(car);
        }
        conn.close();
        return carlist;
    }

    public List<ParkingSpot> selectParkByParkId(int parkingSpotId) throws Exception {
        Connection conn = this.getConnection();
        String query = "SELECT p.parking_spot_id, p.spot_type, p.occupancy_status, p.location, p.fee_standard " +
                "FROM userinformation u " +
                "LEFT JOIN parkingspotinformation p ON u.parking_spot_id = p.parking_spot_id " +
                "WHERE p.parking_spot_id = ?";

        PreparedStatement ps = conn.prepareStatement(query);
        ps.setInt(1,parkingSpotId);
        ResultSet rs = ps.executeQuery();

        List<ParkingSpot> parkingSpotList = new ArrayList<>();
        while (rs.next()) {
            ParkingSpot parkingSpot = new ParkingSpot(
                    rs.getInt("parking_spot_id"),
                    rs.getString("spot_type"),
                    rs.getByte("occupancy_status"),
                    rs.getString("location"),
                    rs.getDouble("fee_standard")
            );
            parkingSpotList.add(parkingSpot);
        }
        conn.close();
        return parkingSpotList;
    }

    public List<IcCard> selectICByUserId(int userId) throws Exception {
        Connection conn = this.getConnection();
        String query = "SELECT i.user_id, i.card_number, i.password, i.balance " +
                "FROM userinformation u " +
                "INNER JOIN icinformation i ON u.user_id = i.user_id " +
                "WHERE u.user_id = ?";

        PreparedStatement ps = conn.prepareStatement(query);
        ps.setInt(1, userId);
        ResultSet rs = ps.executeQuery();

        List<IcCard> icCardList = new ArrayList<>();
        while (rs.next()) {
            IcCard icCard = new IcCard(
                    rs.getInt("user_id"),
                    rs.getString("card_number"),
                    rs.getString("password"),
                    rs.getDouble("balance")
            );
            icCardList.add(icCard);
        }
        conn.close();
        return icCardList;
    }

    public List<User> updateUser(int userId, String name, String telephone, String userType, int parkingSpotId, String address, String gender) throws Exception {
        Connection conn = this.getConnection();
        String query = "UPDATE userinformation " +
                "SET name = ?, telephone = ?, user_type = ?, parking_spot_id = ?, address = ?, gender = ? " +
                "WHERE user_id = ?";

        PreparedStatement ps = conn.prepareStatement(query);
        ps.setString(1, name);
        ps.setString(2, telephone);
        ps.setString(3, userType);
        ps.setInt(4, parkingSpotId);
        ps.setString(5, address);
        ps.setString(6, gender);
        ps.setInt(7, userId);

        int updatedRows = ps.executeUpdate();
        conn.close();

        // 查询并返回更新后的用户信息列表
        if (updatedRows > 0) {
            return selectAllUser();
        } else {
            return new ArrayList<>(); // 如果更新失败，返回空列表
        }
    }

    public List<User> deleteUserById(int userId) throws Exception {
        Connection conn = this.getConnection();
        String[] tables = {"icinformation", "userinformation", "vehicleinformation"};

        for (String table : tables) {
            String query = "DELETE FROM " + table + " WHERE ";
            switch (table) {
                case "icinformation":
                    query += "user_id = ?";
                    break;
                case "userinformation":
                    query += "user_id = ?";
                    break;
                case "vehicleinformation":
                    query += "user_id = ?";
                    break;
                default:
                    break;
            }

            PreparedStatement ps = conn.prepareStatement(query);
            ps.setInt(1, userId);
            ps.executeUpdate();
        }

        conn.close();
        return selectAllUser();
    }

    public User getUserById(int id) throws Exception {

        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT *\n" +
                "FROM userinformation\n" +
                "WHERE user_id = ?\n"
        );
        ps.setInt(1, id);
        ResultSet rs = ps.executeQuery();
        User pr = null;
        if(rs.next()) {
            pr = new User(
                    rs.getInt("user_id"),
                    rs.getString("name"),
                    rs.getString("telephone"),
                    rs.getString("user_type"),
                    rs.getInt("parking_spot_id"),
                    rs.getString("address"),
                    rs.getString("gender")
            );
        }
        conn.close();
        return pr;
    }
    public int update_user_Info(User user) throws Exception {

        Connection conn = this.getConnection();
        PreparedStatement pstmt = null;
        String sql = "UPDATE userinformation SET name=?,gender=?, telephone=?, address=?WHERE user_id=?";
        pstmt = conn.prepareStatement(sql);

        // 设置参数
        pstmt.setString(1, user.getName());
        pstmt.setString(2, user.getGender());
        pstmt.setString(3, user.getTelephone());
        pstmt.setString(4, user.getAddress());
        pstmt.setInt(5, user.getUser_id());
        int count = pstmt.executeUpdate();
        conn.close();
        return count;

    }
}



