package dk.daoas.daoadresseservice.db;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import dk.daoas.daoadresseservice.Address;
import dk.daoas.daoadresseservice.AddressUtils;
import dk.daoas.daoadresseservice.DaekningsType;

public class DatabaseLayer {
	
	public static List<Address> getAllAdresses() throws SQLException {
		
		String sql = "SELECT id,vejnavn,husnr,husnrbogstav,kommunekode,vejkode,postnr,gadeid,distributor,dbkbane,koreliste,rute "
				+ "FROM fulddaekning.adressetabel "
				+ "WHERE postnr BETWEEN 8700 and 8899 OR POSTNR=8300" //DEBUG only
				;
		
		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);
		
		List<Address> list = new ArrayList<Address>();
		
		while (res.next()) {
			Address a = new Address();
			a.id = res.getInt(1);
			a.vejnavn = res.getString(2);
			a.husnr = res.getInt(3);
			a.husnrbogstav = res.getString(4);
			a.kommunekode = res.getInt(5);
			a.vejkode = res.getInt(6);
			a.postnr = res.getInt(7);
			a.gadeid = res.getLong(8);
			a.distributor = res.getString(9);
			a.dbkBane = res.getInt(10);
			a.koreliste = res.getString(11);
			a.rute = res.getString(12);
			
			a.vasketVejnavn = AddressUtils.vaskVejnavn(a.vejnavn);
			
			if (a.rute != null && a.rute.length()> 0) {
				a.daekningsType = DaekningsType.DAEKNING_DIREKTE;
			} else {
				a.daekningsType = DaekningsType.DAEKNING_IKKEDAEKKET;
			}
			
			list.add(a);
		}
		res.close();
		stmt.close();
		conn.close();
		
		System.out.println("Loaded " + list.size() + " adresses");
		
		return list;
	}
}