import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.sql.PreparedStatement;

public class SearchDAO {

	List<BookInformation> exactdata = new ArrayList<BookInformation> ();
	List<BookInformation> partialdata = new ArrayList<BookInformation> ();

	boolean resultfound = false;

	public void  Search(BookInformation data)  {

		final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
		final String DB_URL = "jdbc:mysql://localhost/book";

		//  Database credentials
		final String USER = "root";
		final String PASS = "1234";

		Connection conn = null;
		Statement stmt = null;
		try{
			//STEP 2: Register JDBC driver
			// Class.forName("com.mysql.jdbc.Driver");
			Class.forName("com.mysql.jdbc.Driver");
			//STEP 3: Open a connection
			System.out.println("Connecting to database...");
			conn = DriverManager.getConnection(DB_URL,USER,PASS);

			//STEP 4: Execute a query
			System.out.println("Creating statement...");
			//System.out.println("check"+data.Author);

			stmt = conn.createStatement();
			String sql;
//
//			sql = "SELECT bookinfo.book_id,bookinfo.Book_Title,bookinfo.Date_Issue, "
//					+ "group_concat(distinct(Author_Name)) as Author_Name,group_concat(distinct(Trans_Name)) as Trans_Name,genreinfo.Genre_Name "
//					+ "from bookinfo left join translatorinfo on bookinfo.Book_Id = translatorinfo.Book_Id "
//					+ "left join authorinfo on bookinfo.Book_Id = authorinfo.Book_Id "
//					+ "left join genreinfo on bookinfo.Book_Id = genreinfo.Book_Id "
//					+ "where Book_Status = 1 "
//					+ "group by bookinfo.Book_Id";

			//sql = "SELECT bookinfo.book_id from bookinfo where book_id = " + data.getId();

			sql = "SELECT bookinfo.book_id "
					+ "from bookinfo "
					+ "left join translatorinfo on bookinfo.Book_Id = translatorinfo.Book_Id "
					+ "left join authorinfo on bookinfo.Book_Id = authorinfo.Book_Id "
					+ "left join genreinfo on bookinfo.Book_Id = genreinfo.Book_Id "
					+ "where Book_Status = 1 " ;


			if(data.getTitle().trim().equals("") == false){

                        sql += " or bookinfo.book_name = '" + data.getTitle().trim() + "' " ;
                    }
			if(data.getDmy().trim().equals("") == false){

                sql += " or bookinfo.date_issue = '" + data.getDmy().trim() + "' " ;
            }
			if(data.getDonor().trim().equals("") == false){

                sql += " or bookinfo.donor_name = '" + data.getDonor().trim() + "' " ;
            }
			if(data.getGenre().trim().equals("") == false){

                sql += " or genreinfo.genre_name = '" + data.getGenre().trim() + "' " ;
            }

                    if(data.getauthorlist().size() >= 1){
                        sql += " or authorinfo.author_name IN (" ;
                        String tempitem = "";
                        for(String item : data.getauthorlist()){
                            tempitem += ",'" + item.trim() + "'";
                        }

                        tempitem = tempitem.substring(1);

                        sql += tempitem + ") " ;

                    }

                    if(data.gettranslatorlist().size() >= 1){
                        sql += " or translatorinfo.trans_name IN (" ;
                        String tempitem = "";
                        for(String item : data.gettranslatorlist()){
                            tempitem += ",'" + item.trim() + "'";
                        }

                        tempitem = tempitem.substring(1);

                        sql += tempitem + ") " ;

                    }





			ResultSet rs = stmt.executeQuery(sql);
			System.out.println(sql);

			List<String>BookID = new ArrayList<String>();
			while(rs.next()){
				BookID.add(rs.getString("book_id"));
			}

			String partialID = "";
			for(String item : BookID){
				partialID += " or bookinfo.Book_Id = " + item;

			}

			partialID = partialID.substring(3);


			sql = "SELECT bookinfo.book_id,bookinfo.Book_Title,bookinfo.Date_Issue, "
					+ "group_concat(distinct(Author_Name)) as Author_Name,group_concat(distinct(Trans_Name)) as Trans_Name,genreinfo.Genre_Name "
					+ "from bookinfo left join translatorinfo on bookinfo.Book_Id = translatorinfo.Book_Id "
					+ "left join authorinfo on bookinfo.Book_Id = authorinfo.Book_Id "
					+ "left join genreinfo on bookinfo.Book_Id = genreinfo.Book_Id "
					+ "where " + partialID
					+ " group by bookinfo.Book_Id";

			rs = stmt.executeQuery(sql);


			  while (rs.next()) {
				  BookInformation t = new BookInformation();
				  t.Id = rs.getString("Book_Id");
				  t.Title = rs.getString("Book_Title");
				  t.Author = rs.getString("Author_Name");
				  t.Translator = rs.getString("Trans_Name");
				  t.Genre = rs.getString("Genre_Name");
				  t.Dmy = rs.getString("Date_Issue");

				  String checkauthor = rs.getString("Author_Name");
				  List<String> authorlist = new ArrayList<String>();
				  if(checkauthor != null)
				  {
					  String [] authortemp = rs.getString("Author_Name").split(",");
					  for(String item: authortemp)
					  {
						  if(item.trim().equals("") == false)
						  {
							  authorlist.add(item);
						  }
					  }
				  }
				  String checktranslator = rs.getString("Trans_Name");
				  List<String> translatorlist = new ArrayList<String>();
				  if(checktranslator != null)
				  {
					  String [] translatortemp = rs.getString("Trans_Name").split(",");
					  for(String item: translatortemp)
					  {
						  if(item.trim().equals("") == false)
						  {
							  translatorlist.add(item);
						  }
					  }
				  }
				  t.authorlist = authorlist;
				  t.translatorlist = translatorlist;
//				  System.out.println(rs.getString("Book_Id"));
//				  System.out.println(rs.getString("Book_Title"));
//				  System.out.println(rs.getString("Author_Name"));
//				  System.out.println(rs.getString("Trans_Name"));
//				  System.out.println(rs.getString("Genre_Name"));
//				  System.out.println(rs.getString("Date_Issue"));
				  partialdata.add(t);
				  }


			  if(partialdata.size() >= 1 || exactdata.size() >= 1){
				  resultfound = true;
			  }

				  rs.close();



			stmt.close();
			conn.close();
		}catch(SQLException se){
			//Handle errors for JDBC
			se.printStackTrace();
		}catch(Exception e){
			//Handle errors for Class.forName
			e.printStackTrace();
		}finally{
			//finally block used to close resources
			try{
				if(stmt!=null)
					stmt.close();
			}catch(SQLException se2){
			}// nothing we can do
			try{
				if(conn!=null)
					conn.close();
			}catch(SQLException se){
				se.printStackTrace();
			}//end finally try
		}//end try


	}

}