package de.mpiwg.gazetteer.utils;

import java.io.IOException;
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.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;

import de.mpiwg.gazetteer.bo.DBEntry;
import de.mpiwg.gazetteer.bo.LGBranch;
import de.mpiwg.gazetteer.bo.LGFile;
import de.mpiwg.gazetteer.bo.LGFullTextSearchFile;
import de.mpiwg.gazetteer.bo.LGTopic;
import de.mpiwg.gazetteer.bo.LGTopicSectionRelation;
import de.mpiwg.gazetteer.db.DBBook;
import de.mpiwg.gazetteer.db.DBContents;
import de.mpiwg.gazetteer.db.DBCoordinatesBook;
import de.mpiwg.gazetteer.db.DBSection;
import de.mpiwg.gazetteer.db.DBSectionVersion;



public class DBService {

	private static Logger logger = Logger.getLogger(DBService.class);

	// JDBC driver name and database URL
	static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";


	//static final String DB_URL = "jdbc:mysql://localhost/";
	static final String DB_URL = "jdbc:mysql://194.94.134.66/";	// TODO: when move Gazetteer database to SBB, need to re-config this
																// replace the IP with SBB's host IP: 194.94.134.66

	private static String SECTIONS_TABLE = "sections_index";


	// TODO: for all connection to mysql, we need to new a connection once (don't close it) and check everytime is the connection is still available.
	// It probably will timeout. If that then new a new connection.


	private static DBService instance = null;

	public static DBService getInstance(){
		if(instance == null){
			instance = new DBService();
		}
		return instance;
	}

	private Map<String, DBBook> bookMap;

	private List<String> dynastyList;

	private Map<String, DBCoordinatesBook> coordinatesBookMap;

	public List<String> getDynastyList() throws SQLException{
		if(this.dynastyList == null){
			this.loadDynastyList();
		}
		return dynastyList;
	}

	private void loadDynastyList() throws SQLException{
		Long start = System.currentTimeMillis();
		Connection conn = null;
		Statement stmt = null;
		this.dynastyList = new ArrayList<String>();

		try {
			String query = "SELECT DISTINCT dynasty FROM books";
			logger.debug(query);
			conn = getNewConnection();
			stmt   = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			while(rs.next()){
				this.dynastyList.add(rs.getString("dynasty"));
			}
			rs.close();

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			conn.close();
		}

		long end = System.currentTimeMillis();
		logger.debug("Time execution loading Book Map [ms]: " + (end - start));
	}

	public List<DBBook> getBooks(){
		try {
			this.loadBookMap();
		} catch (SQLException e) {

			e.printStackTrace();
		}

		// getBookMap().values() returns a Collection. In this way, we can use List.
		return new ArrayList<DBBook>(getBookMap().values());
	}


	public DBCoordinatesBook getCoordinatesBook(String bookId) {
		return getCoordinatesBookMap().get(bookId);
	}

	private Map<String, DBCoordinatesBook> getCoordinatesBookMap() {
		if (coordinatesBookMap == null) {
			try {
				this.loadCoordinatesBookMap();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return this.coordinatesBookMap;
	}

	private void loadCoordinatesBookMap() throws SQLException{
		Long start = System.currentTimeMillis();
		Connection conn = null;
		Statement stmt = null;
		this.coordinatesBookMap = new HashMap<String, DBCoordinatesBook>();

		try {
			String query = "SELECT * FROM coordinates_books";
			logger.debug(query);
			conn = getNewConnection();
			stmt   = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			while(rs.next()){
				DBCoordinatesBook book = new DBCoordinatesBook(rs);
				this.coordinatesBookMap.put(book.getBooks_id(), book);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			conn.close();
		}

		long end = System.currentTimeMillis();
		logger.debug("Time execution loading Coordinates Book Map [ms]: " + (end - start));
	}

	public DBBook getBook(String id){
		return getBookMap().get(id);
	}

	public Map<String, DBBook> getBookMap(){
		if(bookMap == null){
			try {
				this.loadBookMap();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		return this.bookMap;
	}

	private void loadBookMap() throws SQLException{
		Long start = System.currentTimeMillis();
		Connection conn = null;
		Statement stmt = null;
		this.bookMap = new HashMap<String, DBBook>();

		try {
			String query = "SELECT * FROM books";
			logger.debug(query);
			conn = getNewConnection();
			stmt   = conn.createStatement();
			ResultSet rs = stmt.executeQuery(query);

			while(rs.next()){
				DBBook book = new DBBook(rs);

				this.bookMap.put(book.getId(), book);
			}
			rs.close();

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			conn.close();
		}

		long end = System.currentTimeMillis();
		logger.debug("Time execution loading Book Map [ms]: " + (end - start));
	}

	public static List<DBSection> searchBook(List<String> termList, String field) throws SQLException{
		Long start = System.currentTimeMillis();

		List<DBSection> list = new ArrayList<DBSection>();

		Connection conn = null;
		Statement stmt = null;

		//TODO this query should join the section table
		String query = "SELECT * FROM books WHERE ";
		for(int i=0; i<termList.size() ; i++){
			String term = termList.get(i);
			if(i>0){
				query += " OR ";
			}
			query += field + " like '%" + term + "%' ";
		}

		try {
			conn = getNewConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				DBBook book = new DBBook(rs);

				String sql = "SELECT * FROM "+ SECTIONS_TABLE +" WHERE " + "books_id = '" + book.getId() + "'";
				logger.debug(sql);
				stmt = conn.createStatement();
				ResultSet rs0 = stmt.executeQuery(sql);
				while(rs0.next()){
					DBSection section = new DBSection(rs0);
					section.setBook(book);
					list.add(section);
				}
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}

		long end = System.currentTimeMillis();
		logger.debug("Time execution serching Books [ms]: " + (end - start));

		return list;
	}

	/*
	public static List<DBBook> searchFullText0(List<String> termList) throws SQLException{
		Long start = System.currentTimeMillis();

		String condition = "";
		for(int i=0; i<termList.size() ; i++){
			String term = termList.get(i);
			if(i>0){
				condition += " OR ";
			}
			condition += " contents.content like '%" + term + "%' ";
		}


		String sql =
					"SELECT books.level1 AS level1, books.level2 AS level2, " +
                       "books.name AS name, books.line AS line,books.period AS period, books.dynasty AS dynasty, " +
                       "books.admin_type as admin_type, books.in_jibengujiku AS in_jibengujiku, " +
                       "books.start_year AS start_year, books.end_year AS end_year, " +
                       "books.id AS id, contents.line AS PAGE, contents.content AS CONTENT, " +
                       "books.volume AS volume, books.author AS author, books.edition AS edition " +
                       "FROM contents " +
                       "JOIN books ON contents.books_id = books.id " +
                       "WHERE " + condition + " " +
                       "ORDER BY contents.books_id, contents.line";


		Connection conn = null;
		Statement stmt = null;
		List<DBBook> resultSet = new ArrayList<DBBook>();
		// TODO new structure for the result of full text search?
		try {
			conn = getNewConnection();
			stmt = conn.createStatement();

			int count = 0;
			ResultSet rs = stmt.executeQuery(sql);
			while (rs.next()) {
				count++;
				DBBook book = new DBBook(rs);
				resultSet.add(book);
			}

			System.out.println("************************* Count " + count + " **********************************");

			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}

		long end = System.currentTimeMillis();
		logger.debug("Time execution serching Books [ms]: " + (end - start));

		return resultSet;
	}
	*/


	public DBSection getSectionByBookIdAndPage(String bookId, int page) throws SQLException {

		Connection conn = null;
		Statement stmt = null;

		DBSection section = null;

		String query = "SELECT * FROM "+ SECTIONS_TABLE +" WHERE books_id='" + bookId + "' " +
						"and start_page<=" + page + " and end_page>=" + page;

		try {
			Class.forName(JDBC_DRIVER);
			conn = getNewConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(query);

			while (rs.next()) {
				section = new DBSection(rs);
				DBBook book = getInstance().getBook(bookId);
				section.setBook(book);

			}
			rs.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}

		if (section == null) {
			// section_index table is not completely correct by TOC_edit task (done by human), so query finds null in section

			section = new DBSection();
			section.setBookId(bookId);
			DBBook book = getInstance().getBook(bookId);
			section.setBook(book);
		}

		return section;

	}


	public static List<DBContents> searchFullText_X(List<String> termList) throws SQLException{
		//Trying subquery to get both contents and sections in one large mysql query, but it performed worse here...

		Long start = System.currentTimeMillis();

		String condition = "(";
		for(int i=0; i<termList.size() ; i++){
			String term = termList.get(i);
			if(i>0){
				condition += " OR ";
			}

			condition += " content like '%" + term + "%' ";
		}
		condition += ")";


		String condition_content_in_book = " WHERE books_id=contents.books_id and start_page <= contents.line and end_page >=contents.line LIMIT 0,1";

		String sql = "SELECT contents.id, contents.books_id AS books_id, contents.line AS line, contents.content AS content, " +
						"books.name, books.dynasty, " +
						"(SELECT sections_index.id FROM "+ SECTIONS_TABLE + condition_content_in_book + ") AS section_id, " +
						"(SELECT sections_index.name FROM "+ SECTIONS_TABLE + condition_content_in_book + ") AS section_name, " +
						"(SELECT sections_index.start_page FROM "+ SECTIONS_TABLE + condition_content_in_book + ") AS start_page, " +
						"(SELECT sections_index.end_page FROM "+ SECTIONS_TABLE + condition_content_in_book + ") AS end_page " +

                    	"FROM contents JOIN books ON contents.books_id=books.id " +
                    	"WHERE " + condition;//+ "and (books_id='00204' or books_id='00219')";	// remove the books_id condition after debug

		//logger.debug("sql: " + sql);

		Connection conn = null;
		Statement stmt = null;
		List<DBContents> resultSet = new ArrayList<DBContents>();
		try {
			conn = getNewConnection();
			stmt = conn.createStatement();

			Integer count = 0;
			ResultSet rs = stmt.executeQuery(sql);

			// log execution time
			long tmp = System.currentTimeMillis();
			logger.debug("Time execution mysql query [ms]: " + (tmp - start));


			while (rs.next()) {

				count++;
				DBContents content = new DBContents(rs);
				content.setInx(count);	// local index for row

				String books_id = rs.getString("books_id");

				// set this.section by bookId and page
				DBSection section = new DBSection();	// some info are not correctly initial, since we update the sql query by query two table at once.

				section.setId(rs.getLong("section_id"));
				section.setName(rs.getString("section_name"));

				section.setBookId(books_id);
				section.setStart_page(rs.getInt("start_page"));
				section.setEnd_page(rs.getInt("end_page"));


				content.setSection(section);

				// set this.coordinatesBook by bookId
				/*
				DBCoordinatesBook coordinatesBook = new DBCoordinatesBook();

				coordinatesBook.setId(rs.getInt("cb_id"));
				coordinatesBook.setBooks_id(books_id);
				coordinatesBook.setPlace_name(rs.getString("place_name"));
				coordinatesBook.setX(rs.getString("x"));
				coordinatesBook.setY(rs.getString("y"));
				*/

				// set this.coordinatesBook by bookId
				content.setCoordinatesBook(DBService.getInstance().getCoordinatesBook(content.getBookId()));

				resultSet.add(content);


			}
			logger.debug("************************* Count " + count + " **********************************");
			// log execution time
			long tmp2 = System.currentTimeMillis();
			logger.debug("Time execution parsing to objects [ms]: " + (tmp2 - tmp));


			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		/*
		// setting section in each DBContents content
		for (DBContents content : resultSet) {
			// set this.section by bookId and page
			content.setSection(DBService.getInstance().getSectionByBookIdAndPage(content.getBookId(), content.getPage()));

			// set this.coordinatesBook by bookId
			content.setCoordinatesBook(DBService.getInstance().getCoordinatesBook(content.getBookId()));
		}
		*/


		long end = System.currentTimeMillis();
		logger.debug("Total Time execution full text search [ms]: " + (end - start));

		return resultSet;
	}

	public static List<DBContents> searchFullText(List<String> termList) throws SQLException{

		Long start = System.currentTimeMillis();

		String condition = "(";
		for(int i=0; i<termList.size() ; i++){
			String term = termList.get(i);
			if(i>0){
				condition += " OR ";
			}

			condition += " content like '%" + term + "%' ";
		}
		condition += ")";


		String sql = "SELECT contents.id, contents.books_id AS books_id, contents.line AS line, contents.content AS content " +
                    	"FROM contents " +
                    	"WHERE " + condition;//+ "and (books_id='00204' or books_id='00219')";	// remove the books_id condition after debug

		//logger.debug("sql: " + sql);

		long tmp = 0 , tmp2 = 0;

		Connection conn = null;
		Statement stmt = null;
		List<DBContents> resultSet = new ArrayList<DBContents>();
		try {
			conn = getNewConnection();
			stmt = conn.createStatement();

			Integer count = 0;
			ResultSet rs = stmt.executeQuery(sql);

			// log execution time
			tmp = System.currentTimeMillis();
			logger.debug("Time execution mysql query [ms]: " + (tmp - start));


			while (rs.next()) {
				count++;
				DBContents content = new DBContents(rs);
				content.setInx(count);	// local index for row
				resultSet.add(content);
			}
			logger.debug("************************* Count " + count + " **********************************");

			// log execution time
			tmp2 = System.currentTimeMillis();
			logger.debug("Time execution parsing to objects [ms]: " + (tmp2 - tmp));

			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}



		// setting section in each DBContents content
		for (DBContents content : resultSet) {
			// set this.section by bookId and page
			content.setSection(DBService.getInstance().getSectionByBookIdAndPage(content.getBookId(), content.getPage()));

			// set this.coordinatesBook by bookId
			content.setCoordinatesBook(DBService.getInstance().getCoordinatesBook(content.getBookId()));
		}


		long end = System.currentTimeMillis();
		logger.debug("Time execution setting sections [ms]: " + (end - tmp2));
		logger.debug("Total Time execution full text search [ms]: " + (end - start));

		return resultSet;
	}



	/**
	 * This methods search from a list of terms.
	 * Every term is considered a subsequence of whole section name.
	 *
	 * @param termList
	 * @return
	 * @throws SQLException
	 */
	public static List<DBSection> searchSection(List<String> termList) throws SQLException{

		Long start = System.currentTimeMillis();

		List<DBSection> list = new ArrayList<DBSection>();

		Connection conn = null;
		Statement stmt = null;

		String query = "SELECT * FROM "+ SECTIONS_TABLE +" WHERE ";
		for(int i=0; i<termList.size() ; i++){
			String term = termList.get(i);
			if(i>0){
				query += " OR ";
			}
			query += "name like '%" + term + "%' ";
		}

		try {
			Class.forName(JDBC_DRIVER);
			conn = getNewConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				DBSection section = new DBSection(rs);
				DBBook book = getInstance().getBook(section.getBookId());
				section.setBook(book);
				list.add(section);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}

		long end = System.currentTimeMillis();
		logger.debug("Time execution serching Sections [ms]: " + (end - start));

		return list;
	}

	public static List<String> suggestBookName(String term, int limit) throws SQLException{
		List<String> list = new ArrayList<String>();

		Connection conn = null;
		Statement stmt = null;

		String query = "SELECT name FROM books WHERE name like '" + term + "%' limit " + limit;

		try {
			Class.forName(JDBC_DRIVER);
			conn = getNewConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String name = rs.getString("name");
				if(!list.contains(name)){
					list.add(name);
				}
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return list;
	}

	public static List<String> suggestSectionName(String term, int limit) throws SQLException {
		List<String> list = new ArrayList<String>();

		Connection conn = null;
		Statement stmt = null;

		String query = "SELECT name FROM "+SECTIONS_TABLE+" WHERE name like '" + term + "%' limit " + limit;

		try {
			Class.forName(JDBC_DRIVER);
			conn = getNewConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				String name = rs.getString("name");
				if(!list.contains(name)){
					list.add(name);
				}
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return list;
	}


	public static List<String> suggestSectionId(String input, int limit) throws SQLException {
		List<String> list = new ArrayList<String>();

		Connection conn = null;
		Statement stmt = null;

		String query = "SELECT id FROM "+SECTIONS_TABLE+" WHERE id like '" + input + "%' limit " + limit;

		try {
			Class.forName(JDBC_DRIVER);
			conn = getNewConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {

				String id = rs.getString("id");
				list.add(id);

			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}



		return list;
	}


	public static DBSection getSectionWithContent(Long sectionId) throws SQLException {

		Connection conn = null;
		Statement stmt = null;
		DBSection section = null;

		String query = "SELECT * FROM "+SECTIONS_TABLE+" WHERE id = '" + sectionId + "'";

		try {
			Class.forName(JDBC_DRIVER);
			conn = getNewConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {

				section = new DBSection(rs);
				/*
				String bookId = rs.getString("books_id");
				int startPage = rs.getInt("start_page");
				int endPage = rs.getInt("end_page");
				String sectionName = rs.getString("name");

				response.setBookId(bookId);
				response.setName(sectionName);
				*/
				//System.out.println("bookId=" + bookId + ", startPage=" + startPage + ", endPage=" + endPage);
				//String content = getContent(conn, bookId, startPage, endPage);
				String content = getContent(conn, section);
				section.setText(content);

				//DBBook book = getBook0FromDB(conn, bookId);
				DBBook book = DBService.getInstance().getBook(section.getBookId());
				section.setBook(book);

			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return section;
	}

	public static DBSection getDeprecatedSectionWithContent(Long sectionId) throws SQLException {

		Connection conn = null;
		Statement stmt = null;
		DBSection section = null;

		String query = "SELECT * FROM sections_revisions WHERE sections_id = '" + sectionId + "'";

		try {
			Class.forName(JDBC_DRIVER);
			conn = getNewConnection();
			stmt = conn.createStatement();

			ResultSet rs = stmt.executeQuery(query);
			rs.next();
			//while (rs.next()) {

				section = new DBSection(rs);

				String content = getContent(conn, section);
				section.setText(content);

				DBBook book = DBService.getInstance().getBook(section.getBookId());
				section.setBook(book);

			//}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return section;
	}



	public static String fixToNewline(String orig){
	    char[] chars = orig.toCharArray();
	    StringBuilder sb = new StringBuilder(100);
	    for(char c : chars){
	        switch(c){
	            case '\r':
	            case '\f':
	                break;
	            case '\n':
	                sb.append("<br>");
	                break;
	            default:
	                sb.append(c);
	        }
	    }
	    return sb.toString();
	}

	//"SELECT `content`, `line`, `books_id` FROM `contents` WHERE `books_id`=\"%s\" AND `line`>=%d AND `line`<=%d
	//private static String getContent(Connection conn, String bookId, Integer startLine, Integer endLine) throws Exception{
	private static String getContent(Connection conn, DBSection section) throws Exception{
		String query = "SELECT content, line FROM contents WHERE books_id = '" + section.getBookId() + "' AND line >= '" + section.getStart_page() + "' AND line <= '" + section.getEnd_page() + "'";
		//logger.debug(query);

		Statement stmt   = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);

		StringBuilder sb = new StringBuilder();
		while(rs.next()){
			String line = rs.getString("line");
			String content = rs.getString("content");
			sb.append("【" + line + "】" + content + "\n");
		}
		rs.close();

		return sb.toString();
	}

	/*
	public static DBBook getBookFromDB(String id) throws SQLException{
		Connection conn = null;
		DBBook book = null;

		try {
			//Class.forName(JDBC_DRIVER);
			conn = getNewConnection();
			book = getBook0FromDB(conn, id);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return book;
	}*/
	/*
	private static DBBook getBook0FromDB(Connection conn, String id) throws SQLException{
		DBBook book = null;

		String query = "SELECT * FROM books WHERE id = '" + id + "'";
		logger.debug(query);

		Statement stmt   = conn.createStatement();
		ResultSet rs = stmt.executeQuery(query);

		if(rs.next()){
			book = new DBBook(rs);
		}

		return book;
	}*/

	/**
	 * This method removed all files for a particular fileId.
	 * The elimination includes the current version as well as the old versions.
	 * @param fileId
	 * @return
	 */
	protected static int deleteBranchFromDB(Long branchId){
		logger.info("Deleting Branch by branchId=" + branchId);

		int modifiedFiles;
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = null;

		try{

			  tx = session.beginTransaction();

				Query query = session.createQuery("delete LGBranch where id = :id");
				query.setLong("id", branchId);
				modifiedFiles = query.executeUpdate();

				Query query0 = session.createQuery("delete LGFile where branchId = :branchId");
				query0.setLong("branchId", branchId);
				modifiedFiles += query0.executeUpdate();

				tx.commit();

		}catch (Exception e) {

			 if (tx!=null) tx.rollback();

			 e.printStackTrace();

			 throw e;

		}finally {
			 session.close();
		}

		return modifiedFiles;
	}

	protected static int deleteFullTextSearchFileFromDB(Long fileId){
		int modifiedFiles = 0;

		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = null;

		try{

			  tx = session.beginTransaction();

				Query query0 = session.createQuery("delete LGFullTextSearchFile where id = :fileId");
				query0.setLong("fileId", fileId);
				modifiedFiles = query0.executeUpdate();

				tx.commit();
		}catch (Exception e) {
				if (tx!=null) tx.rollback();

				e.printStackTrace();

				throw e;
		}finally {
			session.close();
		}

		return modifiedFiles;
	}

	protected static int deleteFileFromDB(Long fileId){

		int modifiedFiles;
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = null;
    try{
         tx = session.beginTransaction();

					Query query0 = session.createQuery("delete LGFile where id = :fileId");
					query0.setLong("fileId", fileId);
					modifiedFiles = query0.executeUpdate();

					tx.commit();
			}catch (Exception e) {
					if (tx!=null) tx.rollback();

					e.printStackTrace();

					throw e;
			}finally {
				session.close();
			}

		return modifiedFiles;

	}

	protected static List<LGFile> getLGFileInDatasetFromDB(String datasetPersistentId) {
		List<LGFile> list = null;

		Session session = HibernateUtil.getSessionFactory().openSession();

		try{

			session.getTransaction().begin();
			Query query = session.createQuery("from LGFile where datasetPersistentId = :datasetPersistentId and fileIdInDv != NULL");
			query.setString("datasetPersistentId", datasetPersistentId);
			list = query.list();
			session.getTransaction().commit();

		}
		catch(Exception e){
			 throw e;
		}
		finally{

			if (session != null && session.isOpen()) {
        session.close();
      }
		}

		return list;
	}


	protected static List<LGFullTextSearchFile> getAllLGFullTextSearchFileFromDB() {

		List<LGFullTextSearchFile> list = null;

		Session session = HibernateUtil.getSessionFactory().openSession();

		try{

			session.getTransaction().begin();
			Query query = session.createQuery("from LGFullTextSearchFile");
			list = query.list();
			session.getTransaction().commit();

		}
		finally{

			if (session != null && session.isOpen()) {
        session.close();
      }
		}

		return list;

	}

	protected static  List<LGFile> getAllLGFileFromDB(){
		List<LGFile> list = null;

		Session session = HibernateUtil.getSessionFactory().openSession();

		try{

			session.getTransaction().begin();
			Query query = session.createQuery("from LGFile");
			list = query.list();
			session.getTransaction().commit();

		}
		catch(Exception e){
			 throw e;
		}
		finally{

			if (session != null && session.isOpen()) {
        session.close();
      }
		}

		return list;
	}

	protected static  List<LGFile> getCurrentLGFilesFromDB(){
		List<LGFile> list = null;

		Session session = HibernateUtil.getSessionFactory().openSession();

		try{

			session.getTransaction().begin();
			Query query = session.createQuery("from LGFile where lastVersion = :lastVersion");
			query.setBoolean("lastVersion", true);
			list = query.list();
			session.getTransaction().commit();

		}
		catch(Exception e){
			 throw e;
		}
		finally{

			if (session != null && session.isOpen()) {
        session.close();
      }
		}

		return list;
	}



	protected static List<LGBranch> getAllLGBranchFromDB(){
		List<LGBranch> list = null;

		Session session = HibernateUtil.getSessionFactory().openSession();

		try{

			session.getTransaction().begin();
			Query query = session.createQuery("from LGBranch");
			list = query.list();
			session.getTransaction().commit();

		}
		catch(Exception e){
			 throw e;
		}
		finally{

			if (session != null && session.isOpen()) {
        session.close();
      }
		}

		return list;
	}

	protected static void saveDBEntry(DBEntry entry, Date date){

		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = null;
    try{
         tx = session.beginTransaction();

				 saveDBEntry0(session, entry, date);

				tx.commit();
		}catch (Exception e) {
				if (tx!=null) tx.rollback();

				e.printStackTrace();

				throw e;
		}finally {
			session.close();
		}
	}

	public static void saveDBEntry0(Session session, DBEntry entry, Date date){
		entry.setLastChangeDate(date);
		if (entry.isPersistent()) {
			session.update(entry);
		} else {
			entry.setCreationDate(date);
			session.save(entry);
		}
		logger.info("saveDBEntry: " + entry.toString());
	}

	public static Connection getNewConnection() throws SQLException, IOException{

		return DriverManager.getConnection(
				DB_URL + PropertiesUtils.getPropValue("db_gazetter_name") + "?useUnicode=yes&characterEncoding=UTF-8",
				PropertiesUtils.getPropValue("db_gazetter_username"),
				PropertiesUtils.getPropValue("db_gazetter_password"));

	}


	public List<DBSectionVersion> getSectionVersionList() throws SQLException {
		List<DBSectionVersion> section_version = new ArrayList<DBSectionVersion>();

		Connection con = null;
		try{
			con = getNewConnection();
			Statement stmt = con.createStatement();
			// query in sections_versions table.
			// Each line is a current version (with largest version number) for a book.
			String query = "SELECT * FROM sections_versions s1 " +
						"JOIN ( " +
							"SELECT books_id, MAX(version) AS version " +
							"FROM sections_versions " +
							"GROUP BY books_id) AS s2 " +
							"ON s1.books_id = s2.books_id AND s1.version = s2.version";

			ResultSet rs = stmt.executeQuery(query);
			while(rs.next()){
				DBSectionVersion sv = new DBSectionVersion(rs);
				section_version.add(sv);

			}
			rs.close();

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
		// ----

		return section_version;
	}

	public void updateTocCorrection(String bookId, String finishedStatus) throws SQLException {
		Connection conn = null;
		Statement stmt = null;

		try {
			String query = "UPDATE books SET toc_correction='"+ finishedStatus + "' WHERE id='" + bookId + "'";
			conn = getNewConnection();
			stmt   = conn.createStatement();
			int rs = stmt.executeUpdate(query);

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			conn.close();
		}

		DBBook book = this.bookMap.get(bookId);
		book.setTocCorrection(finishedStatus);


	}

	public void updateComment(String bookId, String comment) throws SQLException {
		Connection conn = null;
		Statement stmt = null;

		try {
			String query = "UPDATE books SET comments='"+ comment + "' WHERE id='" + bookId + "'";
			conn = getNewConnection();
			stmt   = conn.createStatement();
			int rs = stmt.executeUpdate(query);

		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			conn.close();
		}

		DBBook book = this.bookMap.get(bookId);
		book.setComments(comment);


	}


	/* --- topic --- */
	protected static List<LGTopic> getAllLGTopicFromDB(){
		List<LGTopic> list = null;

		Session session = HibernateUtil.getSessionFactory().openSession();

		try{

			session.getTransaction().begin();
			Query query = session.createQuery("from LGTopic");
			list = query.list();
			session.getTransaction().commit();

		}
		catch(Exception e){
			 throw e;
		}
		finally{

			if (session != null && session.isOpen()) {
        session.close();
      }
		}

		return list;
	}

	protected static int deleteTopicFromDB(Long topicId){

		logger.info("Deleting topic by topicId=" + topicId);

		int modifiedTopic;

		Session session = HibernateUtil.getSessionFactory().openSession();

		Transaction tx = null;
		try{
				 tx = session.beginTransaction();
				// delete record in Topic table
				Query query = session.createQuery("delete LGTopic where id = :id");
				query.setLong("id", topicId);
				modifiedTopic = query.executeUpdate();


				// delete records in TopicSectionRelation table
				Query query0 = session.createQuery("delete LGTopicSectionRelation where topicId = :topicId");
				query0.setLong("topicId", topicId);
				modifiedTopic += query0.executeUpdate();

				// delete records in TopicTagRelation table
				Query query1 = session.createQuery("delete LGTopicTagRelation where topicId = :topicId");
				query1.setLong("topicId", topicId);
				modifiedTopic += query1.executeUpdate();

				tx.commit();
		}catch (Exception e) {
				if (tx!=null) tx.rollback();

				e.printStackTrace();

				throw e;
		}finally {
			session.close();
		}

		return modifiedTopic;
	}


	protected static List<LGTopicSectionRelation> getAllLGTopicSectionRelationFromDB(){

		List<LGTopicSectionRelation> list = null;

		Session session = HibernateUtil.getSessionFactory().openSession();

		try{

			session.getTransaction().begin();
			Query query = session.createQuery("from LGTopicSectionRelation");
			list = query.list();
			session.getTransaction().commit();

		}
		catch(Exception e){
			 session.getTransaction().rollback();
		}
		finally{

			if (session != null && session.isOpen()) {
        session.close();
      }
		}

		return list;
	}

	protected static int deleteTopicSectionRelationFromDB(Long relationId){

		int modifiedRelation;
		Session session = HibernateUtil.getSessionFactory().openSession();
		Transaction tx = null;
    try{
         tx = session.beginTransaction();


				Query query0 = session.createQuery("delete LGTopicSectionRelation where id = :relationId");
				query0.setLong("relationId", relationId);
				modifiedRelation = query0.executeUpdate();

				tx.commit();
		}catch (Exception e) {
				if (tx!=null) tx.rollback();

				e.printStackTrace();

				throw e;
		}finally {
			session.close();
		}

		return modifiedRelation;

	}




	/* --- end topic --- */



}