package dk.daoas.naermestepshop;

import geocode.GeoPoint;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Logger;


public class Database {
	private Logger logger = Logger.getLogger(Database.class.getName());
	
	private Connection conn;
	private PreparedStatement saveStmt;
	private int batchCount = 0;
	
	public Database(Properties props) throws SQLException,IOException {
		conn = getConnection(props);
		
		String sql = "INSERT INTO fulddaekning.shopafstande (id,postnr, daoshop,daoafstand,swipshop,swipafstand,glsshop,glsafstand) "+
				"VALUES (?,?,?,?,?,?,?,? )";

		saveStmt = conn.prepareStatement(sql);		

	}
	
	public List<Adresse> getAdresser() throws Exception {
		String sql = "SELECT id,postnr,latitude,longitude " +
					 "FROM fulddaekning.adressetabel " +
					 "WHERE latitude IS NOT NULL " +
					 "AND longitude IS NOT NULL "
					 //+ "LIMIT 250000"
					 ;
		List<Adresse> result = new ArrayList<Adresse>();
		

		try ( Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY) ){
			stmt.setFetchSize(Integer.MIN_VALUE);
			
			try  (ResultSet res = stmt.executeQuery(sql))  {
				while (res.next()) {
					double latitude = res.getDouble("latitude");
					double longitude = res.getDouble("longitude");
					
					Adresse addr = new Adresse(latitude,longitude);
					addr.id = res.getInt("id");
					addr.postnr = res.getShort("postnr");
					
					result.add(addr);
				}
			}
		}
		logger.info("getAdresser() : " + result.size());
		
		return result;
	}
	
	public List<GeoPoint> getShops(String kaede) throws Exception {
		

		String sql = "SELECT kaede,ktonr,navn,adresse,postnr,latitude,longitude " + 
				"FROM fulddaekning.shoppositioner " +
				"WHERE kaede = ? ";
		
		List<GeoPoint> result = new ArrayList<GeoPoint>();
		
		try (PreparedStatement stmt = conn.prepareStatement(sql) ) {
			
		
			stmt.setString(1, kaede);
			
			
			try (ResultSet res = stmt.executeQuery()) {
				//logger.info("Starting exec query done");
		
				while (res.next()) {
					double latitude = res.getDouble("latitude");
					double longitude = res.getDouble("longitude");
					
					Shop shop = new Shop(latitude,longitude);
					shop.kaede = kaede;
					shop.ktonr = res.getInt("ktonr");
					shop.adresse = res.getString("adresse");
					shop.postnr = res.getShort("postnr");
					
					result.add(shop);
				}
			}
		}
		logger.info("getShops(" + kaede + ") : " + result.size());

		
		return result;
	}
	

	public void resetResultTable() throws SQLException {
		logger.info("Truncating result table");
		String sql = "TRUNCATE TABLE fulddaekning.shopafstande";
		conn.createStatement().executeUpdate(sql);		
	}	
	
	public synchronized void gemResultat(Adresse orgAdresse, ShopResult dao, ShopResult swip, ShopResult gls) throws SQLException {

		saveStmt.setInt(1, orgAdresse.id);
		saveStmt.setShort(2, orgAdresse.postnr);
		saveStmt.setInt(3, dao.shop.ktonr);
		saveStmt.setDouble(4, dao.distance);
		saveStmt.setInt(5, swip.shop.ktonr);
		saveStmt.setDouble(6, swip.distance);
		saveStmt.setInt(7, gls.shop.ktonr);
		saveStmt.setDouble(8, gls.distance);

		saveStmt.addBatch();
		batchCount++;
		if (batchCount >= 5000) {
			saveStmt.executeBatch();
			batchCount = 0;
		}
	}
	
	public void saveBatch() throws SQLException{
		saveStmt.executeBatch();
		batchCount = 0;
	}
	

	
	public Connection getConnection(Properties props) throws SQLException, IOException {
		
		String db_host = props.getProperty("DB_HOST");
		String db_user = props.getProperty("DB_USER");
		String db_pass = props.getProperty("DB_PASS");


		

	    Connection conn = null;
	    Properties connectionProps = new Properties();
	    connectionProps.put("user", db_user);
	    connectionProps.put("password", db_pass);

	    //For debug output, tilføj denne til JDBC url'en: &profileSQL=true	    
	    conn = DriverManager.getConnection(
	                   "jdbc:mysql://" +
	                   db_host +
	                   ":3306/?rewriteBatchedStatements=true",
	                   connectionProps);
	    logger.info("Connected to database");
	    return conn;
	}	
}