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 de.mpiwg.gazetteer.bo.DBEntry;
import de.mpiwg.gazetteer.bo.LGBranch;
import de.mpiwg.gazetteer.bo.LGFile;
import de.mpiwg.gazetteer.db.DBBook;
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/";
	
	private static String SECTIONS_TABLE = "sections_index";

	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;
	
	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"));
			}
		} 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) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		// getBookMap().values() returns a Collection. In this way, we can use List.
		return new ArrayList<DBBook>(getBookMap().values());
	}
	
	public DBBook getBook(String id){
		return getBookMap().get(id);
	}
	
	private 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);
			}
		} 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> 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 += " 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>();
		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;
	}
	
	/**
	 * 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 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");
		}
		
		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().getCurrentSession();
		session.getTransaction().begin();
		
		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();
		
		session.getTransaction().commit();
		
		return modifiedFiles;
	}
	
	protected static int deleteFileFromDB(Long fileId){
		
		int modifiedFiles;
		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		session.getTransaction().begin();
		
		
		Query query0 = session.createQuery("delete LGFile where id = :fileId");
		query0.setLong("fileId", fileId);
		modifiedFiles = query0.executeUpdate();
		
		session.getTransaction().commit();
		
		return modifiedFiles;
		
	}
	
	protected static  List<LGFile> getAllLGFileFromDB(){
		List<LGFile> list = null;

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

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

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

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

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

		return list;
	}
	
	protected static void saveDBEntry(DBEntry entry, Date date){

		Session session = HibernateUtil.getSessionFactory().getCurrentSession();
		session.getTransaction().begin();

		saveDBEntry0(session, entry, date);

		session.getTransaction().commit();
	}

	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);
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
		// ----
		
		return section_version;
	}

	public void updateTocCorrection(String bookId, Integer 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.setToc_correction(finishedStatus);
	
		
	}
	
}