/* $Id: Bingo.java,v 0.1 2002/12/29 18:27:25 remm Exp $
 *
 */
package Bingo;

import java.io.*;
import java.text.*;
//import java.util.*;
import java.sql.*;
import java.util.Date;
import java.util.logging.Logger;
import java.util.logging.Level;
import java.util.logging.LogManager;

/*
 * @author Nobuhiko Ido
 */

public class bingoDBAccess{

    protected static Logger logger = Logger.getLogger(bingoControl.class.getName());

    public static final int DB_RESULT_NO_PLAYER = 0;
    public static final int DB_RESULT_ACCESS_ERR = -1;

    private static boolean dbOn;
    private static Connection conn;

    public static void initDB(String dbDriver,
			      String dbUrl,
			      String dbUserName,
			      String dbPassword) {
	try{
	    Class.forName(dbDriver);
	    conn = DriverManager.getConnection(dbUrl,dbUserName,dbPassword);
	    dbOn = true;
	    logger.fine("#D#initDB:connection established. dbDriver="
			 +dbDriver+",dbUrl="+dbUrl+",dbUserName="+dbUserName);
	}catch(Exception e){
	    logger.warning("#E#initDB:Error connection dbDriver="
			 +dbDriver+",dbUrl="+dbUrl+",dbUserName="+dbUserName+",e="+e);
	    dbOn = false;
	}
    }

    public static int getPlayerId(String name, String password){
	if(dbOn){
	    String selectSQL = "select player_id from players where name='"
		+name+"' and passwd='"+password+"'";
	    logger.fine("#D#(DGP0)bingoDBAccess:getPlayerId:selectSQL="+selectSQL);
	    try{
		Statement stmt = conn.createStatement();
		ResultSet rslt = stmt.executeQuery(selectSQL);
		if(rslt.next()){
		    int playerId = rslt.getInt(1);
		    logger.fine("#D#(DGP1)bingoDBAccess:getPlayerId:DB both players are registered. name="+name+",password="+password+",playerId="+playerId);
		    return(playerId);
		}
		logger.fine("#D#(DGP2)bingoDBAccess:getPlayerId:DB either of players is not registed. name="+name+",password="+password);
		stmt.close();
		rslt.close();
		return(-1);
	    }catch(Exception e){
		logger.fine("#D#(DGP3)bingoDBAccess:getPlayerId:DB access error:name="+name+",e="+e);
		return(-1);
	    }
	}
	return(-1);
    }


    public static void insertGameRecord(int winnerPID, int looserPID, boolean draw){
	if(dbOn){
	    Date date = new Date();
	    String insertSQL = "insert into games (date,winner,looser,draw) "
		+"values('"+date+"','"+winnerPID+"','"+looserPID+"','"+draw+"')";
	    logger.fine("#D#(DGI0)bingoDBAccess:updateGameRecord:insertSQL="+insertSQL);
	    try{	    
		Statement stmt = conn.createStatement();
		stmt.execute(insertSQL);
		logger.fine("#D#(DGI1)bingoDBAccess:updateRecords:successful update:winnerPID="+winnerPID+",looserPID="+looserPID);
		stmt.close();
	    }catch(Exception e){
		logger.fine("#D#(DGI2)bingoDBAccess:updateRecords:DB access error:winnerPID="+winnerPID+",looserPID="+looserPID+",e="+e);
	    }
	}
    }


    public static String getGameRecords(int playerId){
	if(dbOn){
	    StringBuffer buffer = new StringBuffer();
	    String selectSQL = "select g.date,pw.player_id,pw.name,pl.name,draw "
		+"from games g,players pw,players pl "
		+"where (g.winner='"+playerId+
		"' or g.looser ='"+playerId+"')"
		+"and pw.player_id=g.winner and pl.player_id=g.looser";
	    logger.fine("#D#(DGR0)bingoDBAccess:getGameRecord:selectSQL="+selectSQL);
	    try{
		Statement stmt = conn.createStatement();
		ResultSet rslt = stmt.executeQuery(selectSQL);
		int nWin=0;int nLoose=0;int nDraw = 0;
		while(rslt.next()){
		    Date date = rslt.getDate(1);
		    int winnerPID = rslt.getInt(2);
		    String winner = rslt.getString(3);
		    String looser = rslt.getString(4);
		    boolean draw = rslt.getBoolean(5);
		    if(!draw){
			buffer.append(winner+" beat "+looser+" on "
				      +date.toString()+"\n");
			if(winnerPID == playerId){
			    nWin++;
			}else{
			    nLoose++;
			}
		    }else{
			buffer.append(winner+" drew a game with  "
				      +looser+" on "+date+"\n");
			nDraw++;
		    }
		}
		buffer.append("total: win="+nWin+",loose="+nLoose+",draw="
			      +nDraw+"\n");
		logger.fine("#D#(DGR1)bingoDBAccess:readGameRecords:records read:playerId="+playerId);
		stmt.close();
		rslt.close();
		return(buffer.toString());
	    }catch(Exception e){
		logger.fine("#D#(DGR2)bingoDBAccess:readGameRecords:DB access error:playerId="+playerId+",e="+e);
		return(null);
	    }
	}
	return(null);
    }

}