package dk.thoerup.traininfoservice;

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;

public class StationDAO {
	private StationBean convertSingleRow(ResultSet res) throws SQLException {
		StationBean station = new StationBean();
		
		station.setId( res.getInt(1) );
		station.setName( res.getString(2) );
		station.setLatitude( res.getDouble(3) );
		station.setLongitude( res.getDouble(4) );
		station.setRegional( res.getString(5) );
		station.setStrain( res.getString(6) );
		station.setMetro( res.getString(7) );
		station.setAddress( res.getString(8) );
		station.setCalcdist( (int)res.getDouble(9) );
		
		return station;
	}
	
	private List<StationBean> convertResultset(ResultSet res) throws SQLException {
		List<StationBean> stations = new ArrayList<StationBean>();
		while (res.next()) {
			stations.add( convertSingleRow(res) );
		}
		return stations;
		
	}
	
	
	public StationBean getById(int id) throws SQLException {
		String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro,address,0.0 " +
		      "FROM trainstations WHERE id=" + id + " AND enabled=true";
		
		Connection conn = null;
		StationBean result;
		
		try {
			conn = DBConnection.getConnection();
		
			Statement stmt = conn.createStatement();
			ResultSet res = stmt.executeQuery(SQL);		
			res.next();
			result = convertSingleRow(res);
		} finally {
			if (conn != null)
				conn.close();
		}
		
		return result;
	}
	
	
	public List<StationBean> getByName(String name) throws SQLException {
		String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address, 0.0 " + 
		"FROM trainstations " + 
		"WHERE name ILIKE ? AND enabled = true " +
		"ORDER BY name ";

		
		List<StationBean> result;
		Connection conn = null;
		try {
			conn = DBConnection.getConnection();
			PreparedStatement stmt = conn.prepareStatement(SQL);
			
			stmt.setString(1, name + "%");
			
			ResultSet rs = stmt.executeQuery();
			result = convertResultset(rs);
			
		} finally {
			if (conn != null)
				conn.close();
		}
		return result;
	}
	 
	public List<StationBean> getByLocation(double latitude, double longitude) throws SQLException {
		String SQL = "SELECT * FROM ( "+
		"               SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog, stationcode_metro, address," + 
		"                     earth_distance( ll_to_earth(latitude,longitude), ll_to_earth(?,?))::int AS calcdist " + 
		"               FROM trainstations " +
		"               WHERE enabled = true " +
		"       ) AS trainstations2 " +
		"ORDER BY calcdist ASC " + 
		"LIMIT 4 ";
		List<StationBean> result;
		Connection conn = null;
		try {
			conn = DBConnection.getConnection();
			PreparedStatement stmt = conn.prepareStatement(SQL);
			stmt.setDouble(1, latitude);
			stmt.setDouble(2, longitude);
			ResultSet rs = stmt.executeQuery();
			result = convertResultset(rs);
			
		} finally {
			if (conn != null)
				conn.close();
		}
		return result;
	}
	 
	 public List<StationBean> getByList(String list) throws SQLException {
			String SQL = "SELECT id,name,latitude,longitude,stationcode_fjrn,stationcode_stog,stationcode_metro, address,0.0 " + 
			"FROM trainstations " + 
			"WHERE id IN " + list + " AND enabled = true " +
			"ORDER BY name ";
			
			Connection conn = null;
			List<StationBean> result;
			
			try {
				conn = DBConnection.getConnection();
				Statement stmt = conn.createStatement();
				ResultSet res = stmt.executeQuery(SQL);
				result = convertResultset(res);
			} finally {
				if (conn!= null)
					conn.close();
			}
			
			return result;
			
	 }
	 
}