package dk.daoas.adressevedligehold.db;

import java.sql.Connection;
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 dk.daoas.adressevedligehold.beans.Address;
import dk.daoas.adressevedligehold.beans.Address.AddressState;
import dk.daoas.adressevedligehold.tasks.TaskLogger;
import dk.daoas.adressevedligehold.util.DeduplicateHelper;
import dk.daoas.adressevedligehold.util.TimingHelper;

/* 
 *  
 */

public class DatabaseCoverageUpdate  {
	
	//static boolean DEBUG = false;
	
	private TaskLogger logger = TaskLogger.getInstance();
	
	
	public List<Address> getAllAdresses() throws SQLException {
		TimingHelper timing = new TimingHelper();
		
		//String debugFilter = DatabaseLayerImplementation.DEBUG ? " WHERE postnr >= 6000 " : "";
		
		String sql = 
				"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,latitude,longitude, "
				+ "rutema,korelistema,ruteti,korelisteti,ruteon,korelisteon,ruteto,korelisteto,rutefr,korelistefr,rutelo,korelistelo,ruteso,korelisteso "
				+ "FROM fulddaekning.adressetabel "
				//+ debugFilter
				;
		
		try ( Connection conn = DBConnection.getConnection();		
				Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
				) {
			stmt.setFetchSize(Integer.MIN_VALUE);
			ResultSet res = stmt.executeQuery(sql);
			
			List<Address> list = new ArrayList<Address>(2600000);//initial capacity 2.6 mio
			
			DeduplicateHelper<String> vejnavnCache = new DeduplicateHelper<String>(81920);
			DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
			DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
			DeduplicateHelper<String> dirigeringsCache = new DeduplicateHelper<String>(16*1024);			
	
			
			while (res.next()) {
				
				Address a = new Address();
				a.id = res.getInt(1);
				a.vejnavn = vejnavnCache.getInstance( res.getString(2) );
				a.husnr = (short) res.getInt(3);
				a.husnrbogstav = husnrbogstavCache.getInstance( res.getString(4) );
				a.kommunekode = (short) res.getInt(5);
				a.vejkode = (short)res.getInt(6);
				a.postnr = (short)res.getInt(7);
				a.gadeid = res.getInt(8);
				a.distributor = distributorCache.getInstance(res.getString(9));
				a.dbkBane = (short) res.getInt(10);
				a.latitude = (float) res.getDouble(11);
				a.longitude = (float) res.getDouble(12);
				
				a.ruteMandag = dirigeringsCache.getInstance( res.getString(13) );
				a.korelisteMandag = dirigeringsCache.getInstance( res.getString(14) );
				
				a.ruteTirsdag = dirigeringsCache.getInstance( res.getString(15) );
				a.korelisteTirsdag = dirigeringsCache.getInstance( res.getString(16) );				

				a.ruteOnsdag = dirigeringsCache.getInstance( res.getString(17) );
				a.korelisteOnsdag = dirigeringsCache.getInstance( res.getString(18) );
				
				a.ruteTorsdag = dirigeringsCache.getInstance( res.getString(19) );
				a.korelisteTorsdag = dirigeringsCache.getInstance( res.getString(20) );
				
				a.ruteFredag = dirigeringsCache.getInstance( res.getString(21) );
				a.korelisteFredag = dirigeringsCache.getInstance( res.getString(22) );
				
				a.ruteLordag = dirigeringsCache.getInstance( res.getString(23) );
				a.korelisteLordag = dirigeringsCache.getInstance( res.getString(24) );
				
				a.ruteSondag = dirigeringsCache.getInstance( res.getString(25) );
				a.korelisteSondag = dirigeringsCache.getInstance( res.getString(26) );


				list.add(a);
			}
			res.close();
			
			
			logger.info("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
			
			return list;
		}
	}
	
	public void saveNewAddresses(List<Address> addresses) {
		logger.info("DB::saveNewAddresses size:" + addresses.size() );
		
		String sql = "INSERT IGNORE INTO fulddaekning.adressetabel " +
					 "(vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,dbkbane,indlast,aendret,david,googlechecked," +
					 "RuteMa,KorelisteMa,RuteTi,KorelisteTi,RuteOn,KorelisteOn,RuteTo,KorelisteTo,RuteFr,KorelisteFr,RuteLo,KorelisteLo,RuteSo,KorelisteSo) " +
					 "VALUES " +
					 "(?,?,?,?,?,?,?,?, now(), now(), ?, 0, " +
					 "?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		
		
		try (Connection con = DBConnection.getConnection()) {

			
			try (PreparedStatement stmt = con.prepareStatement(sql)) {
				for (Address addr : addresses) {
					
					String david = String.format("%04d%03d%04d", addr.postnr, addr.kommunekode, addr.vejkode);
					
					stmt.setString(1, addr.vejnavn);
					stmt.setShort(2, addr.husnr);
					stmt.setString(3, addr.husnrbogstav);
					stmt.setShort(4, addr.kommunekode);
					stmt.setShort(5, addr.vejkode);
					stmt.setShort(6, addr.postnr);
					stmt.setInt(7, addr.gadeid);
					stmt.setShort(8, addr.dbkBane);
					stmt.setString(9, david);
					
					stmt.setString(10, addr.ruteMandag);
					stmt.setString(11, addr.korelisteMandag);
					stmt.setString(12, addr.ruteTirsdag);
					stmt.setString(13, addr.korelisteTirsdag);
					stmt.setString(14, addr.ruteOnsdag);
					stmt.setString(15, addr.korelisteOnsdag);
					stmt.setString(16, addr.ruteTorsdag);
					stmt.setString(17, addr.korelisteTorsdag);
					stmt.setString(18, addr.ruteFredag);
					stmt.setString(19, addr.korelisteFredag);
					stmt.setString(20, addr.ruteLordag);
					stmt.setString(21, addr.korelisteLordag);
					stmt.setString(22, addr.ruteSondag);
					stmt.setString(23, addr.korelisteSondag);

					
					stmt.execute();
					if (stmt.getUpdateCount() != 1) {
						logger.info("Error executing insert: " + addr);
						addr.state = AddressState.CREATE_FAIL;
					}
					
				}
			}
			
			//con.commit();
		} catch (SQLException e) {
			logger.warning("Error inserting addresses", e  );
		}
	}
	
	public void updateAddresses(List<Address> addresses) {
		logger.info("DB::UpdateAddresses size:" + addresses.size() );
		
		String sql = "UPDATE fulddaekning.adressetabel " +
					"SET " +
					"RuteMa=?, KorelisteMa=?, " +
					"RuteTi=?, KorelisteTi=?, " +
					"RuteOn=?, KorelisteOn=?, " +
					"RuteTo=?, KorelisteTo=?, " +
					"RuteFr=?, KorelisteFr=?, " +
					"RuteLo=?, KorelisteLo=?, " +
					"RuteSo=?, KorelisteSo=?, " +
				    "Distributor=?, " +
				    "DBKBane=?, " +
				    "Aendret=now() " +
					"WHERE id=? ";
					
					
		
		try (Connection con = DBConnection.getConnection()) {
			//con.setAutoCommit(false);
			
			try (PreparedStatement stmt = con.prepareStatement(sql)) {
				for (Address addr : addresses) {
					stmt.setString(1, addr.ruteMandag);
					stmt.setString(2, addr.korelisteMandag);
					stmt.setString(3, addr.ruteTirsdag);
					stmt.setString(4, addr.korelisteTirsdag);
					stmt.setString(5, addr.ruteOnsdag);
					stmt.setString(6, addr.korelisteOnsdag);
					stmt.setString(7, addr.ruteTorsdag);
					stmt.setString(8, addr.korelisteTorsdag);
					stmt.setString(9, addr.ruteFredag);
					stmt.setString(10, addr.korelisteFredag);
					stmt.setString(11, addr.ruteLordag);
					stmt.setString(12, addr.korelisteLordag);
					stmt.setString(13, addr.ruteSondag);
					stmt.setString(14, addr.korelisteSondag);

					stmt.setString(15, addr.distributor);
					stmt.setInt(16, addr.dbkBane);
					stmt.setInt(17, addr.id);

					
					stmt.execute();
					if (stmt.getUpdateCount() != 1) {
						logger.warning("Error executing update: " + addr);
					}
					
				}
			}
			
			//con.commit();
		} catch (SQLException e) {
			logger.warning("Error updating addresses", e );
		}
	}
	
	/*
	private static String nullify(String str) {
		if (str == null)
			return null;
		
		if (str.equals("")) {
			return null;
		} else {
			return str;
		}
	}


	/*
	private static int safeInt(String str) {
		try {
			return Integer.parseInt( str );
		} catch (NumberFormatException e) {
			return 0;
		}
	}
	

	
	private static String coalesce(String s1, String s2) {
		if (s1 != null)
			return s1;
		
		return s2;
	}

	*/
}