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;

public class DatabaseLayerImplementation implements DatabaseLayer {
	
	static boolean DEBUG = false;
	
	@Override
	public List<Address> getAllAdresses() throws SQLException {
		String debugFilter = DatabaseLayerImplementation.DEBUG ? " AND postnr = 8700 " : "";
		
		String sql = 
				"SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,upper(distributor) AS distributor,dbkbane,koreliste,rute,korelisteloerdag,ruteloerdag,korelistesoendag,rutesoendag,latitude,longitude "
				+ "FROM fulddaekning.adressetabel "
				+ "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>();
			DeduplicateHelper<String> husnrbogstavCache = new DeduplicateHelper<String>();
			DeduplicateHelper<String> distributorCache = new DeduplicateHelper<String>();
			DeduplicateHelper<String> korelisteCache = new DeduplicateHelper<String>();
			DeduplicateHelper<String> ruteCache = new DeduplicateHelper<String>();
	
			
			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.koreliste = korelisteCache.getInstance( res.getString(11) );
				a.rute = ruteCache.getInstance( res.getString(12) );
				a.korelisteLordag = korelisteCache.getInstance( res.getString(13) );
				a.ruteLordag = ruteCache.getInstance( res.getString(14) );				
				a.korelisteSondag = korelisteCache.getInstance( res.getString(15) );
				a.ruteSondag = ruteCache.getInstance( res.getString(16) );
				
				a.latitude = (float) res.getDouble(17);
				a.longitude = (float) res.getDouble(18);
				
				//a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn);
				
				
				//Ajourfør adresse objectets dækningstype
				AddressUtils.updateDaekningstype(a);
				
				list.add(a);
			}
			res.close();
			
			System.out.println("Loaded " + list.size() + " adresses");
			
			return list;
		}
	}
	
	@Override
	public List<AliasBean> getAliasList() throws SQLException {
		

		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("Loaded " + list.size() + " aliase beans");
			
			return list;
		}

	}
	
	@Override
	public List<ExtendedBean> getExtendedAdresslist() throws SQLException {
		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 " +
					"join odbc.transporttype t " + 
					"on t.Art = 'Transpost' " +
					"and ( (t.Type = 'Cykel' and a.Afstand < 1.001) or (t.Type = 'Scooter' and a.Afstand < 1.201) or (t.Type = 'Bil' and a.Afstand < 2.601) ) " +
					"and 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("Loaded " + list.size() + " extendedbeans");
			
			return list;
		}
	}
	
	@Override
	public Map<Short,HundredePctBean> get100PctList() throws SQLException {
		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>();
			
			while (res.next()) {
				
				
				HundredePctBean bean = new HundredePctBean();
				bean.postnr = (short) res.getInt(1);
				bean.distributor = distributorCache.getInstance(res.getString(2));
				bean.rute = res.getString(3);
				bean.koreliste = res.getString(4);
				bean.dbkBane = (short)res.getInt(5);			
							
				map.put(bean.postnr, bean);
			}
			
			res.close();
			
			System.out.println("Loaded " + map.size() + " 100pct beans");
			
			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.setString(11, omdelingsdag);
			stmt.setInt(12, getStatusInt(result.status) );
			
			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;
		}
	}	
	
}