package chatweb;
import java.sql.*;
import java.io.*;

public class manager {
    String sDBDriver = "org.gjt.mm.mysql.Driver";
    String sConnStr = "jdbc:mysql://localhost:3306/hoodb";
    String user = "root";
    String pass = "root";
    Connection connect = null;
    ResultSet rs = null;
    
    public manager() {
        try {
            Class.forName(sDBDriver).newInstance();
        } catch(Exception e) {
            System.err.println( "forName:"+e.getMessage());
        }
    }
    
    public ResultSet executeQuery(String sql) {//主要用于執行查詢語句
        rs = null;
        try {
            connect = DriverManager.getConnection(sConnStr,user,pass);
            Statement stmt = connect.createStatement();
            rs = stmt.executeQuery(sql);
        } catch(SQLException ex) {
            System.err.println("EXECUTEQUERY:"+ex.getMessage());
        }
        return rs;
    }
    
    public boolean executeOrder(String sql) {//執行一條SQL語句,執行異常時返回FALSE
        try {
            connect = DriverManager.getConnection(sConnStr,user,pass);
            Statement stmt = connect.createStatement();
            stmt.executeUpdate(sql);
        } catch(SQLException ex) {
            System.err.println("EXECUTEORDER:"+ex.getMessage());
            return false;
        }
        return true;
    }
    
    
    public long getBlocked(String username){//0和負數表示沒被禁言，正數表示被禁言
        ResultSet rs1=null;
        String strSQL="select * from block where username='"+username+"'";
        rs1=executeQuery(strSQL);
        try{
            if(rs1.next()){
                String strSQL2="select period_diff(freetime,now()) as res from block where username='"+username+"';";
                ResultSet rs2=executeQuery(strSQL2);
                if(rs2.next()){
                    long lefttime = rs2.getLong("res");
                    if(lefttime<=0){
                        free(username);
                        lefttime=0;
                    }
                    return lefttime;
                }
            }
        } catch(Exception e){
            System.err.println(e.toString());
            return -1;
        }
        return 0;
    }
    
    public boolean block(String username,int blocktime){//禁止說話
        String strSQL="insert into block (username,freetime) values('"+username+"',date_add(now(),interval "+blocktime+" minute));";
        if(getBlocked(username)>0){
            strSQL=new String("update block set freetime=date_add(now(),interval "+blocktime+" minute) where username='"+username+"';");
        }
        return executeOrder(strSQL);
    }
    
    public boolean free(String username){
        String strSQL="delete from block where username='"+username+"'";
        return executeOrder(strSQL);
    }
    
    public boolean deleteUser(String username){//刪除成員
        if(getBlocked(username)>0){
            free(username);
        }
        String strSQL="delete from user where username='"+username+"' and not grade=1";//不能刪除管理員
        return executeOrder(strSQL);
    }
    
    public int[] getCount(String table,int pagesize){//對信息進行分頁，i[0]表示記錄數，i[1]表示頁數
        rs=null;
        int i[];
        i=new int[2];
        String strSQL="select count(*) from `"+table+"`";
        rs=executeQuery(strSQL);
        try{
            if (rs.next()) {
                i[0]=rs.getInt(1);
            }
        } catch(Exception e){
        }
        i[1]=(i[0]/pagesize)+(i[0]%pagesize==0?0:1);
        return i;
    }
    
    public ResultSet getPage(String table,int currentpage,int pagesize){//到到指定頁內指定數目的記錄
        rs=null;
        String strSQL="SELECT * FROM `"+table+"` order by id desc LIMIT "+((currentpage-1)*pagesize)+","+pagesize;
        rs=executeQuery(strSQL);
        return rs;
    }
    
}