package tree;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Collection;

public class noteBookService {	

    public ArrayList getAllNotes(){

		ArrayList<Map> notes = new ArrayList<Map>();
		Connection connection = null;
		try{
			//建立連接
			connection= MySQLConnection.getInstance().getConnection();
			//創建數據庫操作對象
			Statement statement = connection.createStatement();

			String sql="SELECT * FROM notebook";	

			ResultSet rs = statement.executeQuery(sql);

			Map<String,Object> note;
			while (rs.next())
			{
				note = new HashMap<String,Object>();
				note.put("nid",rs.getInt("nid"));
				note.put("user_name",rs.getString("user_name"));
				note.put("user_email",rs.getString("user_email"));
				note.put("title",rs.getString("title"));
				note.put("date",rs.getString("date"));
				note.put("content",rs.getString("content"));
				note.put("reply",rs.getString("reply"));

				notes.add(note);
			}
			
		}catch(SQLException e){
            notes = null;
        }finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
		return notes;
	}
	
	public ArrayList addNote(Map note){

		String user_name = note.get("user_name").toString();
		
		String email = note.get("user_email").toString();
		String title = note.get("title").toString();
		String content = note.get("content").toString();		
		
		Connection connection = null;
		int result = 0;
		try{
			connection=MySQLConnection.getInstance().getConnection();
			//創建數據庫操作對象
			Statement statement = connection.createStatement();
			String sql = "INSERT INTO notebook(user_name,user_email,title,date,content) values ('" + user_name +"','"+ email +"','"+ title +"',now(),'"+ content+"')";

			result = statement.executeUpdate(sql);
		}catch(SQLException e){
			//
		}finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
		if(result == 0){
			return null;
		}else{
			return this.getAllNotes();
		}
	}
	public Boolean doLogin(String user,String pass){
		String sql = "SELECT * FROM ADMIN where user = '" + user +"' AND pass = '" + pass +"'";

		Connection connection= null;
		boolean result = false;
		try{
			connection= MySQLConnection.getInstance().getConnection();
		
			Statement statement = connection.createStatement();

			ResultSet rs = statement.executeQuery(sql);

			if (rs.next()){
				result =  true;
			}
			
		}catch(SQLException e){
			//
		}finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
		return result;
	}
	public ArrayList removeNote (int nid){
			
		String sql = "DELETE FROM notebook where nid = "+nid;
		Connection connection= null;
		int result = 0;
		try{
			connection=MySQLConnection.getInstance().getConnection();
		
			Statement statement = connection.createStatement();

			result = statement.executeUpdate(sql);
		
		}catch(SQLException e){
			//
		}finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }		

		if(result == 0){
			return null;
		}else{
			return this.getAllNotes();
		}
	}
	public ArrayList replyNote(int nid,String reply){

		String sql = "UPDATE notebook set reply = '" + reply +"' where nid = "+nid;
		Connection connection= null;
		int result = 0;
		try{
			connection=MySQLConnection.getInstance().getConnection();
			
			Statement statement = connection.createStatement();

			result = statement.executeUpdate(sql);

		}catch(SQLException e){
			//
		}finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }		
	
		if(result == 0){
			return null;
		}else{
			return this.getAllNotes();
		}
	}
	public boolean modifyAccount(String user,String oldpass,String newpass){
		String sql = "UPDATE admin set pass = '" + newpass +"' where user = '"+user+"' and pass ='" + oldpass +"'";
		Connection connection= null;
		int result = 0;
		try{
			connection=MySQLConnection.getInstance().getConnection();
			//創建數據庫操作對象
			Statement statement = connection.createStatement();

			result = statement.executeUpdate(sql);
		}catch(SQLException e){
			//
		}finally {
            try {
                connection.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }		

		if(result == 0){
			return false;
		}else{
			return true;
		}
	}

}