package dk.daoas.daoadresseservice.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.HashMap;
import java.util.List;
import java.util.Map;

import dk.daoas.daoadresseservice.AddressUtils;
import dk.daoas.daoadresseservice.beans.Address;
import dk.daoas.daoadresseservice.beans.AliasBean;
import dk.daoas.daoadresseservice.beans.ExtendedBean;
import dk.daoas.daoadresseservice.beans.HundredePctBean;
import dk.daoas.daoadresseservice.beans.SearchResult;
import dk.daoas.daoadresseservice.util.DeduplicateHelper;
import dk.daoas.daoadresseservice.util.TimingHelper;

public class DatabaseLayerImplementation implements DatabaseLayer {
	
	public static final boolean DEBUG = true;
	
	@Override
	public List<Address> getAllAdresses() throws SQLException {
		TimingHelper timing = new TimingHelper();
		
		String debugFilter = DatabaseLayerImplementation.DEBUG ? " AND postnr = 8700 " : "";
		
		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_beta "
				+ "WHERE gadeid IS NOT NULL "
				+ debugFilter
				;

		
		try ( Connection conn = DBConnection.getConnection();		
				Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.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(16) );
				
				
				//Ajourfør adresse objectets dækningstype
				AddressUtils.updateDaekningstype(a);
				
				list.add(a);
			}
			res.close();
			
			
			System.out.println("DB Loaded " + list.size() + " adresses in " + timing.getElapsed() + "ms");
			
			return list;
		}
	}
	
	@Override
	public List<AliasBean> getAliasList() throws SQLException {
		TimingHelper timing = new TimingHelper();
		

		String sql = "SELECT postnr,vejnavn,aliasvejnavn " +
					"FROM bogleveringer.vejtabelprod " 
					;
		
		try ( Connection conn = DBConnection.getConnection();		
				Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
				) {
					
			stmt.setFetchSize(Integer.MIN_VALUE);
			
			ResultSet res = stmt.executeQuery(sql);
			
			DeduplicateHelper<String> vejCache = new DeduplicateHelper<String>();
			
			List<AliasBean> list = new ArrayList<AliasBean>( 5000); 
			while (res.next()) {		
				
				AliasBean ab = new AliasBean();
				ab.postnr = res.getShort(1);
				ab.vejnavn = vejCache.getInstance( res.getString(2) );
				ab.aliasVejnavn = vejCache.getInstance( res.getString(3) );
							
				list.add(ab);
			}
		
			res.close();
			
			System.out.println("DB Loaded " + list.size() + " aliase beans in " + timing.getElapsed() + "ms");
			
			return list;
		}

	}
	
	@Override
	public List<ExtendedBean> getExtendedAdresslist() throws SQLException {
		
		TimingHelper timing = new TimingHelper();
		
		String debugFilter1 = DatabaseLayerImplementation.DEBUG ? " WHERE orgPostnr = 8700 " : "";
		String debugFilter2 = DatabaseLayerImplementation.DEBUG ? " AND orgPostnr = 8700 " : "";
		
		
		String sql = "select orgid, a.id as targetid, afstand, LOWER(type) as type from fulddaekning.afstand_anden_rute a " +
					"LEFT join odbc.transporttype t " + 
					"ON (t.Rute = a.Rute) " + 
					debugFilter1 +
					
					"UNION ALL " +
					
					"SELECT orgid, a.id as targetid, afstand,'' as type FROM  fulddaekning.afstand_anden_rute_bk a " +
					"left join bogleveringer.postnummerdistributor d on d.PostNr = a.orgPostnr " + 
					"WHERE d.Distributor <> 10057 " +
					debugFilter2
					;
		
		try ( Connection conn = DBConnection.getConnection();		
				Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
				) {
			
		
			stmt.setFetchSize(Integer.MIN_VALUE);
			
			ResultSet res = stmt.executeQuery(sql);
			
			DeduplicateHelper<String> transportCache = new DeduplicateHelper<String>();
			
			List<ExtendedBean> list = new ArrayList<ExtendedBean>( 350000); //Initial capacity 350K
			while (res.next()) {		
				
				ExtendedBean eb = new ExtendedBean();
				eb.orgId = res.getInt(1);
				eb.targetId = res.getInt(2);
				eb.afstand = (float) res.getDouble(3);
				eb.transport = transportCache.getInstance(res.getString(4));
							
				list.add(eb);
			}
			
			res.close();
			
			System.out.println("DB Loaded " + list.size() + " extendedbeans in " + timing.getElapsed() + "ms");
			
			return list;
		}
	}
	
	@Override
	public Map<Short,HundredePctBean> get100PctList() throws SQLException {
		TimingHelper timing = new TimingHelper();
		
		String sql = "SELECT postnr,UPPER(distributor) as distributor,rute,koreliste,dbkbane " + 
					 "FROM bogleveringer.adresser_udenfor_daekning";
		
		try ( Connection conn = DBConnection.getConnection();		
				Statement stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);
				) {
			ResultSet res = stmt.executeQuery(sql);
	
			Map<Short, HundredePctBean> map = new HashMap<Short,HundredePctBean>();
			
			DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
			DeduplicateHelper<String> ruteCache = new DeduplicateHelper<String>();
			DeduplicateHelper<String> korelisteCache = new DeduplicateHelper<String>();
			
			while (res.next()) {
				
				
				HundredePctBean bean = new HundredePctBean();
				bean.postnr = (short) res.getInt(1);
				bean.distributor = distributorCache.getInstance(res.getString(2));
				bean.rute = ruteCache.getInstance( res.getString(3) );
				bean.koreliste = korelisteCache.getInstance(res.getString(4) );
				bean.dbkBane = (short)res.getInt(5);			
							
				map.put(bean.postnr, bean);
			}
			
			res.close();
			
			System.out.println("DB Loaded " + map.size() + " 100pct beans in " + timing.getElapsed() + "ms");
			
			return map;
		}
		
	}
	
	@Override
	public void saveRequestLog(String brugerid, String postnr, String adresse, String omdelingsdag, SearchResult result) throws SQLException {
		String setVar = "set sql_mode = 'NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ";
		
		String sql = "INSERT INTO logs.hentruteinformation (postnr,adresse,vejnavn,googlevejnavn,husnr,husnr_bogstav,etage,lejlighed,rest,brugerid,status, OmdelingDag, indlast) " +
					"VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW() )";
		

		
		try ( Connection conn = DBConnection.getConnection();
				Statement setStmt = conn.createStatement();				
				PreparedStatement stmt = conn.prepareStatement(sql);
			) {
			
			setStmt.execute(setVar);
			
			
			stmt.setInt( 1, safeInt(postnr) );
			stmt.setString( 2, adresse);
			stmt.setString( 3, result.splitResult.vej);
			stmt.setString( 4, coalesce(result.googleVej,result.osmVej) );
			stmt.setString( 5, nullify(result.splitResult.husnr) );
			stmt.setString( 6, result.splitResult.litra);
			stmt.setString( 7, result.splitResult.etage);
			stmt.setString( 8, result.splitResult.lejlighed);
			stmt.setString( 9, result.splitResult.resten);
			stmt.setString(10, brugerid);			
			stmt.setInt(11, getStatusInt(result.status) );
			stmt.setString(12, omdelingsdag);
			
			stmt.executeUpdate();
			
		}		
	}
	


	
	private static int safeInt(String str) {
		try {
			return Integer.parseInt( str );
		} catch (NumberFormatException e) {
			return 0;
		}
	}
	
	private static String nullify(String str) {
		if (str == null)
			return null;
		
		if (str.equals("")) {
			return null;
		} else {
			return str;
		}
	}
	
	private static String coalesce(String s1, String s2) {
		if (s1 != null)
			return s1;
		
		return s2;
	}

	public static int getStatusInt(SearchResult.Status status) {

		switch (status) {
		case ERROR_UNKNOWN_POSTAL:
			return 20;
		case ERROR_MISSING_HOUSENUMBER:
			return 21;
		case ERROR_POSTBOX:
			return 22;			
		case ERROR_UNKNOWN_STREETNAME:
			return 23;
		case ERROR_UNKNOWN_ADDRESSPOINT:
			return 24;
		case STATUS_NOT_COVERED:			
			return 25;
		case ERROR_INTERNAL: //			
			return 26;			
			
		case STATUS_OK:
			return 30;
			
		default:
			return 31;
		}
	}	
	
}