import java.io.*;
import java.util.*;
import java.sql.*;

class DatabaseAccess {
	// ユーザ登録
	public boolean registUser(String name) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/businessim?useUnicode=true&characterEncoding=SJIS");
			
			Statement st = con.createStatement();
			ResultSet result = st.executeQuery("insert user (name,state) values ('" + name + "', '登録中')");
			result.close();
			st.close();
			
			con.close();
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
			return false;
		}
	}

	// メッセージ登録
	public void registMessage(String fromName, String toName, String message) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/businessim?useUnicode=true&characterEncoding=SJIS");
			
			Statement st = con.createStatement();
			ResultSet result = st.executeQuery("insert message (fromName, toName, message) values ('" + fromName + "', '" + toName + "', '" + message + "')");
			result.close();
			st.close();
			
			con.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 状態変更
	public void changeUserState(String name, String state) {
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/businessim?useUnicode=true&characterEncoding=SJIS");
			
			Statement st = con.createStatement();
			ResultSet result = st.executeQuery("update user set state='" + state + "' where name='" + name + "'");
			result.close();
			st.close();
			
			con.close();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	// ユーザ状態取得
	public String[][] getUserState() {
		try {
			ArrayList al = new ArrayList();

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/businessim?useUnicode=true&characterEncoding=SJIS");
			Statement st = con.createStatement();
			
			ResultSet result = st.executeQuery("select name, state from user");

			while (result.next()) {
				String name = result.getString(1);
				String state = result.getString(2);
				String[] user = new String[2];
				user[0] = name;
				user[1] = state;
				al.add(user);
			}
			result.close();
			st.close();
			con.close();

			String[][] res = new String[al.size()][];
			al.toArray(res);
			
			return res;
		} catch (Exception e) {
			e.printStackTrace();
			return new String[0][];
		}
	}
	
	// メッセージ取得
	public String[][] getUserMessage(String name) {
		try {
			ArrayList al = new ArrayList();

			Class.forName("com.mysql.jdbc.Driver").newInstance();
			Connection con = DriverManager.getConnection("jdbc:mysql://localhost/businessim?useUnicode=true&characterEncoding=SJIS");
			Statement st = con.createStatement();
			

			ResultSet result = st.executeQuery("select id, fromName, message from message where toName = '" + name + "'");

			int maxid = -1;
			while (result.next()) {
				int id = result.getInt(1);
				if (id > maxid)
					maxid = id;
				String fromName = result.getString(2);
				String state = result.getString(3);
				String[] user = new String[2];
				user[0] = fromName;
				user[1] = state;
				al.add(user);
			}
			result.close();
			
			result = st.executeQuery("delete from message where toName = '" + name + "' and id <= " + maxid);
			result.close();

			st.close();
			con.close();

			String[][] res = new String[al.size()][];
			al.toArray(res);
			
			return res;
		} catch (Exception e) {
			e.printStackTrace();
			return new String[0][];
		}
	}
}