package jp.ac.kyutech.mns.ist;

import java.awt.Color;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.TimeZone;

import javax.swing.JFrame;
import javax.swing.JOptionPane;

import jp.ac.kyutech.mns.ist.util.SingleSelector;

import com.mysql.jdbc.exceptions.jdbc4.CommunicationsException;

/**
 * MySQL接続クラス
 * @author miuramo
 *
 */
public class ATN_Mysql_DB {
	String user;
	String pass;
	String host = "localhost";
	String db;

	Connection con;
	Statement stmt;

	SimpleATN satn;
	//	static String nullorzero_with_is = "is NULL";
	//	static String nullorzero = "NULL";
	static String nullorzero_with_is = "= 0";
	static String nullorzero = "0";

	public ATN_Mysql_DB(SimpleATN _satn, String _user, String _pass, String _db){
		satn = _satn;
		user = _user;
		pass = _pass;
		db = _db;

		reconnect();
	}
	public void reconnect(){
		try {
			Class.forName("com.mysql.jdbc.Driver"); // MySQLの場合
			String url = "jdbc:mysql://" + host + "/"
			+ db + "?user="
			+ user + "&password="
			+ pass
			+ "&useUnicode=true&characterEncoding=SJIS";
			con = (Connection) DriverManager.getConnection(url);

			// ステートメントオブジェクトを生成
			stmt = (Statement) con.createStatement();
		} catch (CommunicationsException ex){
			satn.menutoolbar.disableDBMenus();
			satn.loadFromFile(null,2);
			return;
		} catch (Exception e) {
			satn.findDBName();
			e.printStackTrace();
		}
	}
	public void import_file(JFrame parent){
		String dbname = JOptionPane.showInputDialog(parent, "新規データベース名を入力してください");
		if (dbname == null) {
			JOptionPane.showMessageDialog(parent, "インポートをキャンセルしました");
			return;
		}
		//同一名のDBがないかチェック
		ArrayList<String> dbnames = get_db_list();
		boolean flag = false;
		for(String s: dbnames){
			if (s.equals(dbname)){
				flag = true;
			}
		}
		if (flag){
			int res = JOptionPane.showConfirmDialog(parent, "指定されたデータベースは存在します．テーブルを削除して，インポートを継続しますか？", "確認", JOptionPane.YES_NO_OPTION);
			if (res == JOptionPane.NO_OPTION) {
				JOptionPane.showMessageDialog(parent, "インポートをキャンセルしました");
				return;
			}
		}
		String path = satn.selectfile(false, ".sql");
		if (path == null) return;
		if (!flag) create_new_db(dbname);
		import_to_db(dbname, path);
	}
	public void create_new_db(String dbname){
		try {
			execute("CREATE DATABASE  `"+dbname+"` DEFAULT CHARACTER SET sjis COLLATE sjis_japanese_ci;");

			execute("GRANT ALL PRIVILEGES ON `"+dbname+"` . * TO 'atnuser'@'localhost' WITH GRANT OPTION ;");

		} catch (NullPointerException nullex){
		}
	}
	public void import_to_db(String dbname, String path){

		//			String backup_dbname = db;
		//			db = dbname;
		//			reconnect();
		//import
		String[] command = new String[]{
				"cmd",
				"/c",
				"C:\\xampp\\mysql\\bin\\mysql -u root -h localhost -proot --default-character-set=utf8 "+dbname+" < " + path};

		ProcessBuilder pb = new ProcessBuilder(command);
		Process p = null;
		try {
			p = pb.start();
		} catch (IOException e) {
			e.printStackTrace();
		}
		if (p != null)
			try {
				p.waitFor();
			} catch (InterruptedException e) {
				e.printStackTrace();
			}

		//				db = backup_dbname;
		//				reconnect();

	}

	public ArrayList<String> get_db_list(){
		ResultSet rs = null;
		ArrayList<String> dbnames = new ArrayList<String>();
		try {
			rs = query("show databases;");
		} catch (NullPointerException nullex){
			return dbnames;
		}
		try {
			while(rs.next()){
				dbnames.add(rs.getString("Database"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dbnames;
	}
	public void change_db(JFrame parent){
		ArrayList<String> dbnames = get_db_list();
		if (dbnames.size()>0){
			SingleSelector<String> sel = new SingleSelector<String>(dbnames, parent, "データベースを選択してください");
//			SingleTableSelector<String> sel = new SingleTableSelector<String>(dbnames, parent, "データベースを選択してください",
//					new String[]{"id","name","size","Start datetime","Buttons"},
//					new String[]{"id","name","dataamount","tstamp",""},
//					new String[]{"open"},
//					new int[]{30,200,60,200,200});
			sel.setVisible(true);
			if (sel.selectedObj == null) return;
			db = sel.selectedObj;
			reconnect();
			satn.removeAllNote(true);
			satn.openConnection();
		}
	}
	public ArrayList<DB_Lecture> get_db_lectures(boolean isadd_new){
		ArrayList<DB_Lecture> dblec = new ArrayList<DB_Lecture>();
		ResultSet rs = null;
		boolean no_zerolec = true;//講義名がついていない(LectureID=0)の筆記があれば，falseにする
		try {
			rs = query("SELECT count(notid), lectureID, tstamp from note group by lectureID order by lectureID desc;");
		}catch (NullPointerException nullex){
			return null;
		}
		//まずは0がないか探索（本当はもっと効率良く書きたいが，0を先頭にもってきたいため）
		int count = 0;
		try {
			while(rs.next()){
				int id = rs.getInt("lectureID");
				if (id==0) no_zerolec = false;
				count++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
//0がなければ，追加
//		if (count==0 || no_zerolec){
		if (isadd_new) dblec.add(new DB_Lecture(0, 0, "---", satn)); //新規データ取り込み
//		}
		try {
			rs.beforeFirst();//巻き戻し
			while(rs.next()){
				int c = rs.getInt("count(notid)");
				int id = rs.getInt("lectureID");
				String ts = rs.getString("tstamp");
				//				System.out.println(c+" "+id+" "+ts);
				dblec.add(new DB_Lecture(id, c, ts, satn));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		rs = query("SELECT lecid, lecname, session, classid, teacher, begin, end from lecture;");
		DateFormat df = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		df.setTimeZone(TimeZone.getTimeZone("JST"));
		try {
			while(rs.next()){
				int id = rs.getInt("lecid");
				String name = rs.getString("lecname");
				String ts = rs.getString("session");
				long b = rs.getLong("begin");
				Date db = new Date(b);
				long e = rs.getLong("end");
				Date de = new Date(e);

//				System.out.println(id+" "+name+" "+ts+" "+df.format(db)+" "+df.format(de));
				for(DB_Lecture dbl: dblec){
					if (dbl.id == id) dbl.setName(name);
					//TODO:とりあえず名前だけ
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return dblec;
	}
	public ResultSet query(String q){
		ResultSet rs = null;
		try {
			PreparedStatement stmt1 = con.prepareStatement(q);

			rs = (ResultSet) stmt1.executeQuery();
			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}
	public boolean execute(String q){
		try {
			PreparedStatement stmt1 = con.prepareStatement(q);

			return stmt1.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
	public ResultSet getLectures(){
		ResultSet rs = null;
		try {
			PreparedStatement stmt1 = con
			.prepareStatement("SELECT lecid, lecname, session, classid, teacher, begin, end from lecture;");

			rs = (ResultSet) stmt1.executeQuery();
			return rs;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	// 直接DBに格納する
	public void insertStroke(ShortStroke ss) {
		byte[] xy = ss.getXYBytes();
		byte[] pp = ss.getPseudoPPBytes();

		try {
			PreparedStatement stmt = con
			.prepareStatement("INSERT INTO note (penID, paperID, xy, penpress, gentime, pencolor, penwidth, lectureID) VALUES (?,?,?,?,?,?,?,?)");

			stmt.setInt(1, ss.penid);
			stmt.setInt(2, ss.paperid);
			stmt.setBytes(3, xy);
			stmt.setBytes(4, pp);
			stmt.setLong(5, ss.time);
			stmt.setInt(6, SimpleATN.theapp.drawToolbar.color.getRGB());
			stmt.setInt(7, SimpleATN.theapp.drawToolbar.width/2);
			int lecID = 0;
			if (satn.openingLecture == null) lecID = 0;
			else lecID = satn.openingLecture.id; 
			stmt.setInt(8, lecID);
			//			System.out.println(stmt.toString());
			stmt.execute();
			stmt.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	public int getLastInsertID() {
		ResultSet rs = null;
		int autoincid = -1;
		try {
			rs = (ResultSet) stmt.executeQuery("SELECT LAST_INSERT_ID()");
			if (rs.next()) {
				autoincid = rs.getInt(1);
			} else {
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return autoincid;
	}

	public boolean renameLecture(int lecid, String name){
		if (name == null) name = JOptionPane.showInputDialog(satn, "Input Lecture Name", "講義名を変更", JOptionPane.QUESTION_MESSAGE);
		if (name == null) {
			satn.showFadingMessage("「講義名を変更」をキャンセルしました", SimpleATN.messageColor, Color.black, 4, 0.9f);
			return false;
		}
		try {
			PreparedStatement stmt = con.prepareStatement("update lecture set lecname = ? where lecid = ?");
			stmt.setString(1, name);
			stmt.setInt(2, lecid);
			System.out.println(stmt.toString());
			stmt.execute();
			stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		satn.showFadingMessage("講義名を変更しました", SimpleATN.messageColor, Color.black, 3, 0.9f);
		return true;
	}
	public boolean fixAsNamedLecture(String name) {
		if (name == null) name = JOptionPane.showInputDialog(satn, "Input Lecture Name", "講義名をつけて保存", JOptionPane.QUESTION_MESSAGE);
		if (name == null) {
			satn.showFadingMessage("「講義名をつけて一時筆記を確定する」をキャンセルしました", SimpleATN.messageColor, Color.black, 4, 0.9f);
			return false;
		}
		int lastid = 0;
		try {
			PreparedStatement stmt = con.prepareStatement("INSERT INTO lecture (lecname, session, teacher) VALUES (?,?,?)");
			stmt.setString(1, name);
			stmt.setString(2, "");
			stmt.setString(3, "");
//			stmt.setString(4, "");
			System.out.println(stmt.toString());
			stmt.execute();
			stmt.close();
			// 作成したlectureID を取得
			lastid = getLastInsertID();

			// NULLだった筆記データにlectureIDを設定
			PreparedStatement stmt2 = con
			.prepareStatement("update `note` set lectureID = ? where lectureID "+nullorzero_with_is);
			stmt2.setInt(1, lastid);
			stmt2.execute();
			stmt2.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		satn.showFadingMessage("講義名をつけて一時筆記を確定しました．\n\n  講義番号"+String.valueOf(lastid)+" : "+name, SimpleATN.messageColor, Color.black, 3, 0.9f);
		return true;
	}
	public void deleteTemporaryDrawings() {

		ArrayList<DB_Lecture> dblec = get_db_lectures(false);

		if (dblec.size()==0) {
			JOptionPane.showMessageDialog(satn, "筆記がありませんでした");
			return;
		}
		DB_Lecture selectedDBLec = null;
		if (dblec.size()>0){
			SingleSelector<DB_Lecture> sel = new SingleSelector<DB_Lecture>(dblec, satn, "DBから完全に削除したい講義を選択してください");
			sel.setVisible(true);
			if (sel.selectedObj == null) return;
			selectedDBLec = sel.selectedObj;
		}

		//実際の削除
		int p = JOptionPane.showConfirmDialog(satn, "本当にDBの筆記を完全に削除してよろしいですか？","DB筆記の完全削除",JOptionPane.YES_NO_OPTION);
		if (p == JOptionPane.YES_OPTION) {
			String lecID = null;
			if (selectedDBLec.id > 0) lecID = "= "+selectedDBLec.id;
			else lecID = nullorzero_with_is;
			boolean result = false;
			try {
				PreparedStatement stmt0 = con.prepareStatement("delete from `note` where lectureID "+lecID);
				result = stmt0.execute();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			if (!result) {
				satn.showFadingMessage("DBの筆記を削除しました", SimpleATN.messageColor, Color.black, 3, 0.9f);
				if (selectedDBLec.id == satn.openingLecture.id){ //現在開いている講義だったら
					satn.removeAllNote(true);
				}
			}
			else satn.showFadingMessage("DBの筆記削除に失敗しました", Color.red.brighter(), Color.black, 4, 0.9f);
		} else {
			satn.showFadingMessage("DB筆記の削除をキャンセルしました", SimpleATN.messageColor, Color.black, 2, 0.9f);
		}
	}
	public void mergeLectures() {
		ArrayList<DB_Lecture> dblec = get_db_lectures(false);

		if (dblec.size()==0) {
			JOptionPane.showMessageDialog(satn, "筆記がありませんでした");
			return;
		}
		if (dblec.size()==1) {
			JOptionPane.showMessageDialog(satn, "講義が1つだけなので，マージできません");
			return;
		}
		JOptionPane.showMessageDialog(satn, "【筆記のマージ】\n\n最初の画面で，講義番号を修正する筆記（マージ元）を選択し，\nその後，修正後の講義番号（マージ先）を選択します．");
		DB_Lecture selectedDBLec1 = null;
		if (dblec.size()>1){
			SingleSelector<DB_Lecture> sel = new SingleSelector<DB_Lecture>(dblec, satn, "講義番号を修正する筆記（マージ元）を選択してください");
			sel.setVisible(true);
			if (sel.selectedObj == null) return;
			selectedDBLec1 = sel.selectedObj;
		}
		DB_Lecture selectedDBLec2 = null;
		if (dblec.size()>1){
			SingleSelector<DB_Lecture> sel = new SingleSelector<DB_Lecture>(dblec, satn, "修正後の講義番号（マージ先）を選択してください");
			sel.setVisible(true);
			if (sel.selectedObj == null) return;
			selectedDBLec2 = sel.selectedObj;
		}
		String fromLec = null;
		if (selectedDBLec1.id > 0) fromLec = "= "+selectedDBLec1.id;
		else fromLec = nullorzero_with_is;
		String toLec = null;
		if (selectedDBLec2.id > 0) toLec = String.valueOf(selectedDBLec2.id);
		else toLec = nullorzero;
		boolean result = false;
		try {
			PreparedStatement stmt0 = con.prepareStatement("update `note` set `lectureID` = "+toLec+" where lectureID "+fromLec);
			result = stmt0.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		if (!result) satn.showFadingMessage("筆記をマージしました", SimpleATN.messageColor, Color.black, 3, 0.9f);
		else satn.showFadingMessage("筆記マージに失敗しました", Color.red.brighter(), Color.black, 4, 0.9f);
	}
}
