package backend.dao;

import backend.model.Charts;
import backend.utils.DBConnectionPool;
import backend.utils.DBHelper;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ChartsDao {
    List<Charts> commonResultSet(ResultSet rs) throws SQLException {
        List<Charts> chartsPoList = new ArrayList<>();
        while(rs.next()){
            Charts charts = new Charts();
            charts.setUserid(rs.getInt("userid"));
            charts.setCount(rs.getInt("count"));
            charts.setScore(rs.getInt("score"));
            chartsPoList.add(charts);
        }
        return chartsPoList;
    }
    public List<Charts> findAll() {
        List<Charts> chartsPoList = new ArrayList<>();
        Connection conn = DBHelper.getInstance().getConnection();
        String sql = "select userid,count,score from charts";
        try {
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Charts charts = new Charts();
                charts.setUserid(rs.getInt("userid"));
                charts.setCount(rs.getInt("count"));
                charts.setScore(rs.getInt("score"));
                chartsPoList.add(charts);
            }
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            System.out.println("查询所有用户失败: " + e.getMessage());
        }
        return chartsPoList;
    }
    public Charts findById(int userid) {
        Connection conn = DBHelper.getInstance().getConnection();
        Charts chartsPo = null;
        if(conn==null)
            return null;
        String sql = "select userid,count,score from charts where userid = ?";
        try {
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1,userid);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                chartsPo = new Charts();
                chartsPo.setUserid(rs.getInt("userid"));
                chartsPo.setCount(rs.getInt("count"));
                chartsPo.setScore(rs.getInt("score"));

            }
            rs.close();
            stmt.close();
        } catch (SQLException e) {
            System.out.println("查询用户失败: " + e.getMessage());
        }
        return chartsPo;
    }
    public boolean insertCharts(Charts charts) {
        Connection conn = DBHelper.getInstance().getConnection();
        int row = 0;
        if(conn==null)
            return false;
        //查找是否已存在
        String findSql = "select * from charts where userid = ?";
        try{
            PreparedStatement stmt = conn.prepareStatement(findSql);
            stmt.setInt(1,charts.getUserid());
            ResultSet rs = stmt.executeQuery();
            if(rs.next()){
                return false;
            }
            rs.close();
        } catch (SQLException e) {
            System.out.println("查找用户失败: " + e.getMessage());
        }
        String sql = "insert into charts (userid,count,score) values (?,?,?)";
        try{
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1,charts.getUserid());
            stmt.setInt(2,charts.getCount());
            stmt.setInt(3,charts.getScore());
            row = stmt.executeUpdate();
            stmt.close();
            DBConnectionPool.releaseConnection(conn);
        } catch (SQLException e) {
            System.out.println("添加用户失败: " + e.getMessage());
        }
        return row > 0;
    }
    public boolean updateCharts(Charts charts) {
        Connection conn = DBHelper.getInstance().getConnection();
        Charts oldCharts = findById(charts.getUserid());
        int row = 0;
        if(conn==null)
            return false;
        String sql = "update charts set count = ?,score = ? where userid = ?";
        try{
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1,oldCharts.getCount()+1);
            stmt.setInt(2,oldCharts.getScore()+charts.getScore());
            stmt.setInt(3,charts.getUserid());
            row = stmt.executeUpdate();
            stmt.close();
        } catch (SQLException e) {
            System.out.println("更新用户失败: " + e.getMessage());
        }
        return row > 0;
    }
    public boolean deleteCharts(int userid) {
        Connection conn = DBHelper.getInstance().getConnection();
        String sql = "delete from charts where userid = ?";
        int row = 0;
        try{
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setInt(1,userid);
            row = stmt.executeUpdate() ;
        } catch (SQLException e) {
            System.out.println("删除用户失败: " + e.getMessage());
        }
        return row>0;
    }
    public List<Charts> sortByScoreUp() {
        List<Charts> chartsPoList = new ArrayList<>();
        List<Charts> list = findAll();
        for(int i = 0; i < list.size() - 1; i++) {
            for(int j = i + 1; j < list.size(); j++) {
                if(list.get(i).getScore() < list.get(j).getScore()) {
                    Charts temp = list.get(i);
                    list.set(i, list.get(j));
                    list.set(j, temp);
                }
            }
        }
        return list;
    }
    public List<Charts> sortByScoreDown() {
        List<Charts> chartsPoList = new ArrayList<>();
        List<Charts> list = findAll();
        for(int i = 0; i < list.size() - 1; i++) {
            for(int j = i + 1; j < list.size(); j++) {
                if(list.get(i).getScore() > list.get(j).getScore()) {
                    Charts temp = list.get(i);
                    list.set(i, list.get(j));
                    list.set(j, temp);
                }
            }
        }
        return list;
    }
}
