import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.vividsolutions.jts.geom.Point;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKBReader;

import fr.ocelet.runtime.List;

public class FieldsPostgis {

	java.sql.Connection conn;

	public void connectBase(String baseUrl, String baseName, String login, String passwd) {
		try {
			Class.forName("org.postgresql.Driver");
			String url = "jdbc:postgresql://"+baseUrl+":5432/" + baseName;
			conn = DriverManager.getConnection(url, "postgres", "post.gres");
		} catch (ClassNotFoundException e) {
			System.out
					.println("Could not find the database driver. Maybe the postgresql_xxx_jdbc.jar your are using is outdated ..");
		} catch (SQLException e) {
			System.out.println("Failed to open connection on the database "
					+ baseName + " by user " + login
					+ ". Please check all names and password.");
		}
	}

	public List<Centroid> getCentroids(String tableName) {
		List<Centroid> lc = new List<Centroid>();
		try {
			Statement s = conn.createStatement();
			String query = "SELECT gid, st_ashexewkb(centroid(the_geom)) as c FROM "
					+ tableName;
			ResultSet r = s.executeQuery(query);

			WKBReader wkbReader = new WKBReader();

			while (r.next()) {
				Point pt = (Point) wkbReader.read(WKBReader.hexToBytes(r
						.getString(2)));
				lc.add(new Centroid(r.getInt(1), pt.getX(), pt.getY()));
			}
			s.close();
		} catch (SQLException e) {
			System.out.println("Failed to read the table " + tableName
					+ " from the database.");
		} catch (ParseException pe) {
			System.out.println("Could not parse the Point WKB value.");
		}

		return lc;
	}

	public void disconnectBase() {
		try {
			conn.close();
		} catch (SQLException e) {
			System.out.println("Failed at closing the database ... Oh well ..");
		}
	}

	public List<Neighblink> computeNeighb(String tableName, double distance) {
		List<Neighblink> nl = new List<Neighblink>();
		try {
			Statement s = conn.createStatement();
			String query = "SELECT S1.gid as c1, S2.gid as c2, st_area(st_intersection(st_buffer(S1.the_geom,"+distance+"),st_buffer(S2.the_geom,"+distance+"))) as inter "
					+ "FROM "
					+ tableName
					+ " as S1, "
					+ tableName
					+ " as S2 "
					+ "WHERE (S1.gid != S2.gid) AND (distance(S1.the_geom,S2.the_geom) < "
					+ distance + ")";
			ResultSet r = s.executeQuery(query);
			double max = 0;
			while (r.next()) {
				double inter = r.getDouble(3);
				if (inter > max)
					max = inter;
				nl.add(new Neighblink(r.getInt(1)-1, r.getInt(2)-1, inter));
			}
			s.close();
			for (int i = 0; i < nl.size(); i++) {
				Neighblink n = nl.get(i);
				n.coef = n.coef / max;
			}
		} catch (SQLException e) {
			System.out.println("Failed to read the table " + tableName
					+ " from the database.");
		}
		return nl;
	}

	public static void main(String[] args) {
		String baseUrl = "cir5456.teledetection.fr";
		String baseName = "ocltdata";
		String tableName = "brapan_rgr92";
		double distance = 10.0;
		FieldsPostgis fp = new FieldsPostgis();
		fp.connectBase(baseUrl, baseName, "postgres", "post.gres"); // pourrait être en
		// constructeur !
		List<Centroid> lc = fp.getCentroids(tableName);
		List<Neighblink> nl = fp.computeNeighb(tableName, distance);
		fp.disconnectBase();
		for (int i = 0; i < lc.size(); i++) {
			Centroid cc = lc.get(i);
			System.out.println(cc.id + ": " + cc.x + "," + cc.y);
		}
	}
}

/**
 * postgres lib : http://jdbc.postgresql.org/download.html postgis lib :
 * http://postgis.refractions.net/download/
 */
// * service connectBase(text baseName, text login, text passwd);
// * service getCentroids(); => ResultSet rc gid | point ---> construit entités
// :plot_<gid>
// * service computeNeighb(text tableName, real distance); => ResultSet rn et
// ResultSet rm=> maxSurf