package eu.dnetlib.msro.espas;

import org.apache.log4j.Logger;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.StringReader;
import java.net.MalformedURLException;
import java.net.URL;
import java.net.URLConnection;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by antleb on 8/28/14.
 */
public interface Test {
//	private Logger logger = Logger.getLogger(this.getClass());
//
//	private int batchSize = 1000;
//	private DataSource dataSource;
//
//	private final static String GET_LOCATION_FILES_QUERY = "select o.id as id, ores.linkage as url from observation o join observation_context oc on o.id = oc.observation and oc.role='http://ontology.espas-fp7.eu/relatedObservationRole/satelliteLocation' join observation lo on oc.relatedobservation=lo.id join result r on lo.result=r.id join result_onlineresource ror on r.id=ror.result join onlineresource ores on ror.onlineresource=ores.id where not exists (select 1 from location.observation_locationfile ol where ol.observation=o.id);";
//	private final static String SAVE_OBSERVATION_LOCATION = "insert into location.observation_locationfile (observation, file) values (?, ?)";
//
//	void foo() {
//		try {
//			Connection con = this.getConnection();
//			PreparedStatement stmt = con.prepareStatement(GET_LOCATION_FILES_QUERY);
//			ResultSet rs = stmt.executeQuery();
//
//			while (rs.next()) {
//				final String observationId = rs.getString("id");
//				final String url = rs.getString("url");
//				List<String[]> batch = new ArrayList<String[]>();
//
//				URLConnection urlConnection = new URL(url).openConnection();
//
//				InputStream is = urlConnection.getInputStream();
//				BufferedReader br = new BufferedReader(new InputStreamReader(is));
//				String crs = readCrs(br);
//				String line = br.readLine();
//
//				while (line != null) {
//					if (!line.startsWith("#") && !line.trim().isEmpty()) {
//						String[] segs = line.split("\\s+");
//
//						batch.add(segs);
//					}
//
//					if (batch.size() == batchSize) {
//						importBatch(batch, url, crs);
//
//						batch.clear();
//					}
//
//					line = br.readLine();
//				}
//
//				if (!batch.isEmpty()){
//					importBatch(batch, url, crs);
//
//					batch.clear();
//				}
//
//				PreparedStatement ostmt = con.prepareStatement(SAVE_OBSERVATION_LOCATION);
//
//				ostmt.setString(1, observationId);
//				ostmt.setString(2, url);
//				ostmt.executeUpdate();
//				ostmt.close();
//			}
//
//			rs.close();
//			stmt.close();
//			con.close();
//		} catch (Exception e) {
//			logger.error("Error saving location", e);
//		}
//	}
//
//	private void importBatch(List<String[]> batch, String locationUrl, String crs) throws SQLException, IOException {
//		CopyManager cp = new CopyManager((BaseConnection) this.getConnection());
//		StringBuilder sb = new StringBuilder();
//
//		for (String[] segs:batch) {
//			sb.append(locationUrl).append("\t");
//			sb.append(DateTimeFormat.forPattern("yyyy-MM-dd HH-mm-ss.SSSZZ").print(DateTime.parse(segs[0]))).append("\t");
//			sb.append(segs[1]).append("\t");
//			sb.append(segs[2]).append("\t");
//			sb.append(segs[3]).append("\t");
//			sb.append(crs).append("\n");
//		}
//
//		sb.append("\\.");
//
//		cp.copyIn("COPY location.location (locationUrl, date, x, y, z, crs) FROM stdin;", new StringReader(sb.toString()));
//	}
//
//
//	private static String readCrs(BufferedReader bufferedReader) throws IOException {
//		String line =  bufferedReader.readLine();
//
//		while (line != null && !line.startsWith("%crs"))
//			line = bufferedReader.readLine();
//
//		if (line != null)
//			return line.split("=")[1].trim();
//
//		else
//			return null;
//	}
//
//	private Connection getConnection() throws SQLException {
//		return dataSource.getConnection();
//	}
//
//	public DataSource getDataSource() {
//		return dataSource;
//	}
//
//	public void setDataSource(DataSource dataSource) {
//		this.dataSource = dataSource;
//	}
//
//	public int getBatchSize() {
//		return batchSize;
//	}
//
//	public void setBatchSize(int batchSize) {
//		this.batchSize = batchSize;
//	}
}