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.List;

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.bo.SearchRulesFile;
import de.mpiwg.gazetteer.bo.Sequence;
import de.mpiwg.gazetteer.db.DBBook;
import de.mpiwg.gazetteer.db.DBSection;
import de.mpiwg.web.SessionBean;

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/";

	/**
	 * 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 name, id, books_id FROM sections WHERE ";
		for(int i=0; i<termList.size() ; i++){
			String term = termList.get(i);
			if(i>0){
				query += " OR ";
			}
			query += "name like '%" + term + "%' ";
		}
		//query += " limit 50";

		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 = getBook0(conn, section.getBookId());
				//section.setBook(book);
				list.add(section);
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		
		long end = System.currentTimeMillis();
		System.out.println("Time execution [ms]: " + (end - start));
		
		return list;
	}
	
	public static List<String> suggestSectionName(String term) throws SQLException {
		List<String> list = new ArrayList<String>();
		
		Connection conn = null;
		Statement stmt = null;
		
		String query = "SELECT name FROM sections WHERE name like '" + term + "%' limit 50";

		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) throws SQLException {
		List<String> list = new ArrayList<String>();
		
		Connection conn = null;
		Statement stmt = null;
		
		String query = "SELECT id FROM sections WHERE id like '" + input + "%' limit 50";

		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 response = new DBSection(sectionId);
		
		String query = "SELECT * FROM sections WHERE id = '" + sectionId + "'";
		
		try {
			Class.forName(JDBC_DRIVER);
			conn = getNewConnection();
			stmt = conn.createStatement();
			
			ResultSet rs = stmt.executeQuery(query);
			while (rs.next()) {
				
				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);
				response.setText(content);
				
				DBBook book = getBook0(conn, bookId);
				response.setBook(book);
				
			}
			rs.close();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return response;
	}
	
	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{
		
		String query = "SELECT content, line FROM contents WHERE books_id = '" + bookId + "' AND line >= '" + startLine + "' AND line <= '" + endLine + "'";
		
		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 getBook(String id) throws SQLException{
		Connection conn = null;
		DBBook book = null;
	
		try {
			Class.forName(JDBC_DRIVER);

			conn = getNewConnection();
			book = getBook0(conn, id);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			conn.close();
		}
		return book;
	}
	
	private static DBBook getBook0(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  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"));
	}
	
}