package hunnu.sj.raise_money.DataBase;
import android.content.Context;

import androidx.core.content.ContextCompat;

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

import hunnu.sj.raise_money.News;
import hunnu.sj.raise_money.R;
import hunnu.sj.raise_money.User;

public class UserService {
    public DatabaseHelper db = null;
    public Connection conn = null;
    public UserService(){
        this.db = DatabaseHelper.getDb();
    }

    public int login(String username,String password){
        conn = db.getConnection();
        Statement sttmt = null;
        try{
            sttmt = conn.createStatement();
            String sql = "select * from user where username = '"+username+"'";
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs1 = ps.executeQuery();
            boolean flag1 = rs1.next();
            if(!flag1){
                sttmt.close();
                return 0;       //当用户名不存在时返回0
            }
            ResultSet rs2 = sttmt.executeQuery("select * from user where username = '"+username+"'&&password = '"+password+"'");
            boolean flag2 = rs2.next();
            if(!flag2){
                rs2.close();
                sttmt.close();
                return 1;   //当用户名存在，密码错误时返回1
            }
            sttmt.close();
            return 2;       //都正确时，返回2
        }catch (Exception e){
            e.printStackTrace();
        }
            return 0;
    }

    public boolean register(User user){
        conn = db.getConnection();//将用户的信息上传到数据库，上传成功返回true，失败false
        try{
            String sql = "insert into user(username,password,role) values('"+user.getName()+"','"+user.getPasd()+"','"+user.getRole()+"')";
            Statement sttmt = conn.createStatement();
            sttmt.executeUpdate(sql);
            sttmt.close();
            return true;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    public String getRole(String username){//从数据库获取用户的角色
        //SQLiteDatabase sdb=dbHelper.getReadableDatabase();
        String sql="select role from user where username = '" + username+"'";
        conn = db.getConnection();
        try{
            Statement sttmt = conn.createStatement();
            ResultSet rs = sttmt.executeQuery(sql);
            rs.next();
            String role = rs.getString(1);
            rs.close();
            sttmt.close();
            return role;
        }catch (SQLException e){
            e.printStackTrace();
        }
        return null;
    }

    public boolean UploadInfo(News news){//用户上传信息到数据库，服务器
        String sql = "insert into stu_info(name,descri) values('"+news.getTitle()+"','"+news.getDes() + "')";
        conn = db.getConnection();
        try{
            Statement sttmt = conn.createStatement();
            sttmt.executeUpdate(sql);
            sttmt.close();
            return true;
        }catch (SQLException e){
            e.printStackTrace();
        }
        return false;
    }

    public void getAllNews(Context context,ArrayList<News> list){
        ArrayList<News> mlist = list;
        String sql = "select * from stu_info";
        conn = db.getConnection();
        try{
            Statement sttmt = conn.createStatement();
            ResultSet rs = sttmt.executeQuery(sql);
            while (rs.next()){
                News news= new News();
                news.setTitle(rs.getNString("name"));
                news.setDes(rs.getNString("descri"));
                news.setHead_icon(ContextCompat.getDrawable(context, R.drawable.ic_launcher_background));
                list.add(news);
            }
        }catch(SQLException e){
            e.printStackTrace();
        }
    }

}
