package JDBC;

import pojo.ParkingRecord;
import pojo.Vehicle;

import java.sql.*;
import java.util.*;
import java.util.Date;

public class ParkingRecordJDBC {
    public ParkingRecordJDBC() {
        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");
    }

    public ParkingRecord selectLatestRecord(String car_lincense) throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT *\n" +
                "FROM parking_record_view\n" +
                "WHERE car_license = ?\n" +
                "ORDER BY start_time DESC\n" +
                "LIMIT 1;");
        ps.setString(1, car_lincense);
        ResultSet rs = ps.executeQuery();
        ParkingRecord pr = null;
        if(rs.next()) {
            pr = new ParkingRecord(
                rs.getInt("record_id"),
                rs.getInt("parking_spot_id"),
                rs.getTimestamp("start_time"),
                rs.getTimestamp("end_time"),
                rs.getDouble("parking_fee"),
                rs.getString("car_license"),
                    rs.getString("location")
            );
        }
        conn.close();
        return pr;
    }

    public void setEndTime(int record_id, Timestamp end_time) throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("UPDATE parkingrecord\n" +
                "SET end_time = ?\n" +
                "WHERE record_id = ?;");
        ps.setTimestamp(1, end_time);
        ps.setInt(2, record_id);
        ps.executeUpdate();
    }

    public void insertNewRecord(ParkingRecord newRecord) throws Exception {
        Connection conn = this.getConnection();
        String car_license = newRecord.getCar_license();
        PreparedStatement ps = conn.prepareStatement("select *\n" +
                "FROM (\n" +
                "    SELECT *\n" +
                "    FROM vehicleinformation\n" +
                "    WHERE car_license = ?\n" +
                ") vi\n" +
                "INNER JOIN userinformation ui ON vi.user_id = ui.user_id\n" +
                "INNER JOIN parkingspotinformation psi ON ui.parking_spot_id = psi.parking_spot_id;");
        ps.setString(1, car_license);
        ResultSet rs = ps.executeQuery();
        rs.next();
        PreparedStatement ps1 = conn.prepareStatement("INSERT INTO parkingrecord (parking_spot_id, start_time, car_license)\n" +
                "VALUES (?, ?, ?);");
        ps1.setInt(1, rs.getInt("parking_spot_id"));
        ps1.setTimestamp(2, newRecord.getStart_time());
        ps1.setString(3, car_license);
        ps1.executeUpdate();
        conn.close();
    }

    public List<ParkingRecord> selectAllRecords() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT * FROM parking_record_view ORDER BY start_time DESC");
        ResultSet rs = ps.executeQuery();
        List<ParkingRecord> prs = new ArrayList<>();
        while(rs.next()) {
            ParkingRecord pr = new ParkingRecord(
                    rs.getInt("record_id"),
                    rs.getInt("parking_spot_id"),
                    rs.getTimestamp("start_time"),
                    rs.getTimestamp("end_time"),
                    rs.getDouble("parking_fee"),
                    rs.getString("car_license"),
                    rs.getString("location")
            );
            prs.add(pr);
        }
        conn.close();
        return prs;
    }

    public List<ParkingRecord> selectRecordsByCase(String car_license, String location, Timestamp startTime, Timestamp endTime) throws Exception {
        Connection conn = this.getConnection();
        String sql = "SELECT * FROM parking_record_view";
        StringBuilder whereClause = new StringBuilder(" WHERE ");

        if (car_license != null && !car_license.isEmpty()) {
            whereClause.append("car_license = '").append(car_license).append("' AND ");
        }
        if (location != null && !location.isEmpty()) {
            whereClause.append("location = '").append(location).append("' AND ");
        }

        // 添加时间范围条件
        if (startTime != null && endTime != null) {
            whereClause.append("start_time >= ? AND end_time <= ? AND ");
        }

        if (whereClause.toString().contains(" AND ")) {
            whereClause.delete(whereClause.lastIndexOf(" AND "), whereClause.length());
        }
        if (!" WHERE ".equals(whereClause.toString())) {
            sql += whereClause.toString();
        }
        sql += " ORDER BY start_time DESC";
        PreparedStatement ps = conn.prepareStatement(sql);

        // 设置时间范围参数
        int parameterIndex = 1;
        if (startTime != null && endTime != null) {
            ps.setTimestamp(parameterIndex++, startTime);
            ps.setTimestamp(parameterIndex, endTime);
        }

        ResultSet rs = ps.executeQuery();
        List<ParkingRecord> prs = new ArrayList<>();
        while (rs.next()) {
            ParkingRecord pr = new ParkingRecord(
                    rs.getInt("record_id"),
                    rs.getInt("parking_spot_id"),
                    rs.getTimestamp("start_time"),
                    rs.getTimestamp("end_time"),
                    rs.getDouble("parking_fee"),
                    rs.getString("car_license"),
                    rs.getString("location")
            );
            prs.add(pr);
        }
        conn.close();
        return prs;
    }


    public Map<String, Double> selectTopCost() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT user_name, total_parking_fees\n" +
                "FROM userparkingtotalfees\n" +
                "ORDER BY total_parking_fees DESC\n" +
                "LIMIT 5;");
        ResultSet rs = ps.executeQuery();
        Map<String, Double> userParkingInfo = new LinkedHashMap<>();
        while (rs.next()) {
            String userName = rs.getString("user_name");
            double totalParkingFees = rs.getDouble("total_parking_fees");
            userParkingInfo.put(userName, totalParkingFees);
        }
        conn.close();
        return userParkingInfo;
    }

    public Map<Date, Double> selectMonthIncome() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT DATE(start_time) AS date, SUM(parking_fee) AS total_income\n" +
                "FROM parkingrecord\n" +
                "WHERE start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)\n" +
                "GROUP BY DATE(start_time)\n" +
                "ORDER BY DATE(start_time);");
        ResultSet rs = ps.executeQuery();
        Map<Date, Double> monthIncome = new LinkedHashMap<>();
        while(rs.next()) {
            Date date = rs.getDate("date");
            Double income = rs.getDouble("total_income");
            monthIncome.put(date, income);
        }
        conn.close();
        return monthIncome;
    }

    public List<ParkingRecord> GetAllRecordbySpot_Id(int spot_id) throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT *\n" +
                "FROM parkingrecord\n" +
                "WHERE parking_spot_id = ?\n" +
                "ORDER BY start_time DESC;"
        );
        ps.setInt(1, spot_id);
        ResultSet rs = ps.executeQuery();
        ParkingRecord pr = null;
        List<ParkingRecord> list=new ArrayList<ParkingRecord>();
        while(rs.next()) {
            pr = new ParkingRecord(
                    rs.getInt("record_id"),
                    rs.getInt("parking_spot_id"),
                    rs.getTimestamp("start_time"),
                    rs.getTimestamp("end_time"),
                    rs.getDouble("parking_fee"),
                    rs.getString("car_license")
            );
            list.add(pr);
        }
        conn.close();
        return list;
    }

    public int selectUserSum() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("select COUNT(*) from parkingspotinformation");
        ResultSet rs = ps.executeQuery();
        int peopleSum = 0;
        if(rs.next()) {
            peopleSum = rs.getInt(1);
        }
        conn.close();
        return peopleSum;
    }
    public int selectBusyNum() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT COUNT(*)\n" +
                "FROM parkingspotinformation psi\n" +
                "WHERE NOT EXISTS (\n" +
                "    SELECT *\n" +
                "    FROM parkingrecord pr\n" +
                "    WHERE pr.parking_spot_id = psi.parking_spot_id\n" +
                "      AND TIME(pr.start_time) BETWEEN '18:00:00' AND '21:00:00'\n" +
                ");");
        ResultSet rs = ps.executeQuery();
        int peopleBusy = 0;
        if(rs.next()) {
            peopleBusy = rs.getInt(1);
        }
        conn.close();
        return peopleBusy;
    }

    public Map<Integer, Double> selectHourMins() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT\n" +
                "    HOUR(pr.start_time) as Hour,\n" +
                "    AVG(TIMESTAMPDIFF(MINUTE, pr.start_time, pr.end_time)) as AverageParkingMinutes\n" +
                "FROM\n" +
                "    parkingrecord pr\n" +
                "WHERE\n" +
                "        YEARWEEK(pr.start_time, 1) = YEARWEEK(CURDATE(), 1)\n" +
                "GROUP BY\n" +
                "    Hour\n" +
                "ORDER BY\n" +
                "    Hour;");
        Map<Integer, Double> hourMins = new LinkedHashMap<>();
        ResultSet rs = ps.executeQuery();
        while(rs.next()) {
            Integer hour = rs.getInt("Hour");
            Double apm = rs.getDouble("AverageParkingMinutes");
            hourMins.put(hour, apm);
        }
        conn.close();
        return hourMins;
    }

    public Map<String, Double> selectLocationMins() throws Exception {
        Connection conn = this.getConnection();
        PreparedStatement ps = conn.prepareStatement("SELECT\n" +
                "    location AS parking_location,\n" +
                "    AVG(TIMESTAMPDIFF(MINUTE, start_time, end_time)) AS avg_usage_time_this_week\n" +
                "FROM\n" +
                "    parking_record_view\n" +
                "WHERE\n" +
                "        YEARWEEK(start_time, 1) = YEARWEEK(CURDATE(), 1)\n" +
                "GROUP BY\n" +
                "    location\n" +
                "ORDER BY\n" +
                "    location;");
        ResultSet rs = ps.executeQuery();
        Map<String, Double> locationMins = new LinkedHashMap<>();
        while(rs.next()) {
            String location = rs.getString(1);
            Double min = rs.getDouble(2);
            locationMins.put(location, min);
        }
        conn.close();
        return locationMins;
    }
}



