/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package m.gis;

import com.vividsolutions.jts.algorithm.MinimumDiameter;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKTReader;
import com.vividsolutions.jts.io.WKTWriter;
import csip.ServiceException;
import java.awt.font.NumericShaper;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import org.codehaus.jettison.json.JSONArray;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

/**
 *
 * @author ktraff
 */
public class PostgresqlDatabase extends Database {

    public PostgresqlDatabase(String hostname, String port, String dbName, String username, String password) throws ServiceException {
        super(hostname, port, dbName, username, password);
        createSchema(SCHEMA);
    }

    @Override
    public void createSchema(String schemaName) throws ServiceException {
        try {
            Statement stmt = conn.createStatement();
            String sql = "CREATE SCHEMA IF NOT EXISTS " + schemaName;
            stmt.executeUpdate(sql);

            stmt.close();
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public Layer createLayer(String tableName, JSONObject layerJSON) throws ServiceException {
        Layer lyr = null;
        try {
            GeoJSONParser.ShapeType shapeType = GeoJSONParser.getShape(layerJSON);
            int srid = GeoJSONParser.getSridInt(layerJSON);
            String schemaTable = SCHEMA + ".\"" + tableName + "\"";
            Statement stmt = conn.createStatement();
            if (!exists(SCHEMA, tableName)) {
                String sql = "CREATE TABLE " + schemaTable + " (id serial)";
                LOG.log(Level.INFO, sql);
                stmt.executeUpdate(sql);

                sql = "SELECT AddGeometryColumn ('" + 
                        SCHEMA + "','" + tableName + "', 'the_geom', " + 
                        String.valueOf(srid) + ", '" + shapeType + "', 2);";
                LOG.log(Level.INFO, sql);
                stmt.executeQuery(sql);
            }
            lyr = new Layer(SCHEMA, tableName, layerJSON);

            stmt.close();
        } catch (JSONException|SQLException ex) {
            throw new csip.ServiceException(ex);
        }
        return lyr;
    }

    @Override
    public Feature uploadFeature(JSONObject featureJSON, Layer layer) throws csip.ServiceException {
        try {
            Statement stmt = conn.createStatement();

            String sql = "SELECT ST_GeomFromGeoJSON('" + featureJSON.getJSONObject("geometry").toString() + "') AS geojson";
            LOG.log(Level.INFO, sql);
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            String geom = rs.getString("geojson");

            // Create a mapping of ordered column values for insertion.
            ArrayList<String> cols = new ArrayList<>();
            ArrayList<Object> vals = new ArrayList();
            cols.add("the_geom");
            vals.add(geom);

            // Create column fields for all properties.
            if (!featureJSON.isNull("properties")) {
                ArrayList<String> columnNames = getColumns(SCHEMA, layer.table);
                JSONObject props = featureJSON.getJSONObject("properties");
                Iterator<?> keys = props.keys();
                while (keys.hasNext()) {
                    String key = (String)keys.next();
                    LOG.log(Level.INFO, "Found property " + key + ": " + props.get(key));
                    cols.add(key);
                    vals.add(props.get(key));
                    if (!columnNames.contains(key)) {
                        if (props.get(key) instanceof String) {
                            addColumn(SCHEMA, layer.table, key, DataType.STRING);
                        }
                        else if (props.get(key) instanceof Double || 
                                 props.get(key) instanceof Integer ||
                                 props.get(key) instanceof Number) {
                            addColumn(SCHEMA, layer.table, key, DataType.NUMERIC);
                        }
                    }
                }
            }
            else {
                LOG.log(Level.INFO, "Found no properties");
            }

            sql = "INSERT INTO " + layer.getSchemaTable() + " (" + LocalStringUtils.join(cols, ",") + ") " +
                  "VALUES ('" + LocalStringUtils.join(vals, "', '")  + "') RETURNING id";
            LOG.log(Level.INFO, sql);
            stmt.execute(sql);
            ResultSet lastUpdated = stmt.getResultSet();
            int id = -1;
            if (lastUpdated.next()) {
                id = lastUpdated.getInt(1);
            }
            LOG.log(Level.INFO, "Uploaded feature with id: " + id);

            stmt.close();
            return new Feature(layer, featureJSON, id);
        } catch (SQLException|JSONException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public int deleteTable(String tableName) throws ServiceException {
        try {
            Statement stmt = conn.createStatement();
            String sql = "DROP TABLE IF EXISTS " + tableName;
            LOG.log(Level.INFO, sql);
            int ret = stmt.executeUpdate(sql);

            stmt.close();
			return ret;
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public int deleteLayer(Layer lyr) throws ServiceException {
	    return deleteTable(lyr.getSchemaTable());
    }

    @Override
    public Layer intersect(Layer one, Layer two) throws ServiceException {
        try {
            String table = "intsn_" + SESSION_ID + "_" + new RandomString(4).nextString();
            String schemaTable = SCHEMA + ".\"" + table + "\"";
            Statement stmt = conn.createStatement();

	    deleteTable(schemaTable);

            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                "ST_Intersection(" + one.getSchemaTable() + ".the_geom, " + two.getSchemaTable() + ".the_geom) AS the_geom ",
                getProperties(one, true),
                getProperties(two, true),
            }));
            String sql = "CREATE TABLE " + schemaTable + " AS " + 
                    "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                    "FROM " + one.getSchemaTable() + ", " + two.getSchemaTable() + " " +
                    "WHERE " + one.getSchemaTable() + ".the_geom && " + two.getSchemaTable() + ".the_geom";
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);

            stmt.close();
            return new Layer(SCHEMA, table, null);
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public Layer union(Layer one, Layer two) throws ServiceException {
        try {
            String table = "union_" + SESSION_ID + "_" + new RandomString(4).nextString();
            String schemaTable = SCHEMA + ".\"" + table + "\"";
            Statement stmt = conn.createStatement();
			createSchema(SCHEMA);
			deleteTable(schemaTable);

            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                "ST_Union(" + one.getSchemaTable() + ".the_geom, " + two.getSchemaTable() + ".the_geom) AS the_geom ",
                getProperties(one, true),
                getProperties(two, true),
            }));
            String sql = "CREATE TABLE " + schemaTable + " AS " + 
                    "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                    "FROM " + one.getSchemaTable() + ", " + two.getSchemaTable();
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);

            stmt.close();
            return new Layer(SCHEMA, table, null);
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public Layer buffer(Layer lyr, int distance) throws ServiceException {
        try {
            String table = "buf_" + SESSION_ID + "_" + new RandomString(4).nextString();
            String schemaTable = SCHEMA + ".\"" + table + "\"";
            Statement stmt = conn.createStatement();
			createSchema(SCHEMA);
			deleteTable(schemaTable);

            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                "ST_Buffer(" + lyr.getSchemaTable() + ".the_geom::geography, " + distance + ")::geometry AS the_geom ",
                getProperties(lyr, true)
            }));
            String sql = "CREATE TABLE " + schemaTable + " AS " + 
                    "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                    "FROM " + lyr.getSchemaTable();
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);

            stmt.close();
            return new Layer(SCHEMA, table, null);
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public Layer minimumBoundingCircle(Layer lyr, int numSegsPerQtCirc) throws ServiceException {
        try {
            String table = "mbc_" + SESSION_ID + "_" + new RandomString(4).nextString();
            String schemaTable = SCHEMA + ".\"" + table + "\"";
            Statement stmt = conn.createStatement();
			createSchema(SCHEMA);
			deleteTable(schemaTable);

            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                "ST_MinimumBoundingCircle(ST_Collect(" + lyr.getSchemaTable() + ".the_geom), " + 
                    numSegsPerQtCirc + ") AS the_geom ",
                getProperties(lyr, true) 
            }));
            String sql = "CREATE TABLE " + schemaTable + " AS " + 
                    "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                    "FROM " + lyr.getSchemaTable();
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);

            stmt.close();
            return new Layer(SCHEMA, table, null);
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public Layer minimumBoundingRectangle(Layer lyr) throws ServiceException {
        try {
            String table = "mbr_" + SESSION_ID + "_" + new RandomString(4).nextString();
            String schemaTable = SCHEMA + ".\"" + table + "\"";
            Statement stmt = conn.createStatement();

			JTSAdapter adapter = new JTSAdapter(this);
			String wkt = adapter.minimumBoundingRectangle(lyr);
			LOG.log(Level.INFO, "Minimum Bounding Rectangle: " + wkt);

            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                "ST_GeomFromText('" + wkt + "') as the_geom " + 
                getProperties(lyr, true) 
            }));
            String sql = "CREATE TABLE " + schemaTable + " AS " + 
                    "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                    "FROM " + lyr.getSchemaTable();
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);

            stmt.close();
            return new Layer(SCHEMA, table, null);
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public Layer extent(Layer lyr) throws ServiceException {
        try {
            String table = "ext_" + SESSION_ID + "_" + new RandomString(4).nextString();
            String schemaTable = SCHEMA + ".\"" + table + "\"";
            Statement stmt = conn.createStatement();
			createSchema(SCHEMA);
			deleteTable(schemaTable);

            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                "ST_Extent(" + lyr.getSchemaTable() + ".the_geom)::geometry AS the_geom ",
                getProperties(lyr, true) 
            }));
            String sql = "CREATE TABLE " + schemaTable + " AS " + 
                    "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                    "FROM " + lyr.getSchemaTable();
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);

            stmt.close();
            return new Layer(SCHEMA, table, null);
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public Layer envelope(Layer lyr) throws ServiceException {
        try {
            String table = "env_" + SESSION_ID + "_" + new RandomString(4).nextString();
            String schemaTable = SCHEMA + ".\"" + table + "\"";
            Statement stmt = conn.createStatement();
			createSchema(SCHEMA);
			deleteTable(schemaTable);

            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                "ST_Envelope(ST_Collect(" + lyr.getSchemaTable() + ".the_geom)) AS the_geom ",
                getProperties(lyr, true) 
            }));
            String sql = "CREATE TABLE " + schemaTable + " AS " + 
                    "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                    "FROM " + lyr.getSchemaTable();
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);

            stmt.close();
            return new Layer(SCHEMA, table, null);
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public Layer area(Layer lyr) throws ServiceException {
        try {
            String table = "area_" + SESSION_ID + "_" + new RandomString(4).nextString();
            String schemaTable = SCHEMA + ".\"" + table + "\"";
            Statement stmt = conn.createStatement();
			createSchema(SCHEMA);
			deleteTable(schemaTable);

            ArrayList<String> colsExclude = new ArrayList<>(Arrays.asList(new String[]{"the_geom","id","calc_area"}));
            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                lyr.getSchemaTable() + ".the_geom ",
                "ST_Area(" + lyr.getSchemaTable() + ".the_geom::geography) AS calc_area ",
                getProperties(lyr, true, colsExclude) 
            }));
            String sql = "CREATE TABLE " + schemaTable + " AS " + 
                    "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                    "FROM " + lyr.getSchemaTable();
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);

            stmt.close();
            return new Layer(SCHEMA, table, null);
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public JSONObject toJSON(Layer lyr) throws ServiceException {
        try {
            Statement stmt = conn.createStatement();
            ArrayList<String> cols = getColumns(lyr.schema, lyr.table);
            JSONObject featureCollection = new JSONObject("{" +
                    "\"type\": \"FeatureCollection\"," +
                "}");
            JSONArray features = new JSONArray();

            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                "ST_AsGeoJSON(" + lyr.getSchemaTable() + ".the_geom) As geometry ",
                getProperties(lyr, false),
            }));
            String sql = "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                            "FROM " + lyr.getSchemaTable() + " ";
            LOG.log(Level.INFO, sql);
            ResultSet rs = stmt.executeQuery(sql);

            while (rs.next()) {
                JSONObject feat = new JSONObject();
                feat.put("type", "Feature");
                feat.put("geometry", new JSONObject(rs.getString("geometry")));
                JSONObject jsonProps = new JSONObject();
                for (String col : cols) {
                    if (!col.equals("the_geom")) {
                        jsonProps.put(col, rs.getObject(col));
                    }
                }
                feat.put("properties", jsonProps);
                features.put(feat);
            }

            stmt.close();
            featureCollection.put("features", features);
            return featureCollection;
        } catch (SQLException|JSONException ex) {
            throw new csip.ServiceException(ex);
        }
    }

	@Override
	public String toWKT(Layer lyr) throws ServiceException {
		String wkt = "";
		try {
            Statement stmt = conn.createStatement();

            ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
                "ST_AsText(" + lyr.getSchemaTable() + ".the_geom) As wkt "
            }));
            String sql = "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                            "FROM " + lyr.getSchemaTable() + " ";
            LOG.log(Level.INFO, sql);
            ResultSet rs = stmt.executeQuery(sql);
			if (rs.next()) {
				wkt = rs.getString("wkt");
			}
		} catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
		return wkt;
	}

    @Override
    public boolean exists(String schema, String tableName) throws ServiceException {
        try {
            Statement stmt = conn.createStatement();
            String sql = "SELECT EXISTS ( " +
                            "SELECT 1 " +
                            "FROM   pg_catalog.pg_class c " +
                            "JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace " +
                            "WHERE  n.nspname = '" + schema + "' " +
                            "AND    c.relname = '" + tableName + "' " +
                        ")";
            LOG.log(Level.INFO, sql);
            ResultSet rs = stmt.executeQuery(sql);
            rs.next();
            boolean exists = rs.getBoolean("exists");

            stmt.close();
            return exists;
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public void addColumn(String schema, String tableName, String columnName, DataType type) throws ServiceException {
        try {
            String sql = "ALTER TABLE " + schema + ".\"" + tableName + "\" ADD COLUMN " + columnName + " ";
            switch (type) {
                case NUMERIC:
                    sql += "numeric";
                    break;
                case STRING:
                    sql += "character varying";
                    break;
                default: 
                    sql += "character varying";
                    break;
            }

            Statement stmt = conn.createStatement();
            LOG.log(Level.INFO, sql);
            stmt.executeUpdate(sql);
            stmt.close();
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
    }

    @Override
    public ArrayList<String> getColumns(String schema, String tableName) throws ServiceException {
        ArrayList<String> cols = new ArrayList<>();
        try {
            Statement stmt = conn.createStatement();
            String sql = "SELECT attrelid::regclass, attnum, attname " +
                            "FROM   pg_attribute " +
                            "WHERE  attrelid = '" + schema + ".\"" + tableName + "\"'::regclass " +
                            "AND    attnum > 0 " +
                            "AND    NOT attisdropped " +
                            "ORDER  BY attnum";

            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                cols.add(rs.getString("attname"));
            }
            stmt.close();
        } catch (SQLException ex) {
            throw new csip.ServiceException(ex);
        }
        return cols;
    }

    public String getProperties(Layer lyr, boolean useTableSuffix) throws ServiceException {
        ArrayList<String> colsExclude = new ArrayList<>(Arrays.asList(new String[]{
            "the_geom",
            "id",
        }));
        return getProperties(lyr, useTableSuffix, colsExclude);
    }

    public String getProperties(Layer lyr, boolean useTableSuffix, ArrayList<String> colsExclude) throws ServiceException {
        ArrayList<String> props = new ArrayList<>();
        ArrayList<String> cols = getColumns(lyr.schema, lyr.table);
        for (String col : cols) {
            if (!colsExclude.contains(col)) {
                String prop = lyr.getSchemaTable() + "." + col + " AS " + col;
                if (useTableSuffix && hasSuffix(lyr)) {
                    prop += "_" + lyr.table.charAt(lyr.table.length() - 1);
                }
                props.add(prop);
            }
        }
        return LocalStringUtils.join(props, ",");
    }

    public boolean hasSuffix(Layer lyr) {
        return RegularExpression.matches("_[0-9]$", lyr.table);
    }

}

/**
 * Interfaces with the Java Topology Library
 * @author ktraff
 */
class JTSAdapter {

    protected final Logger LOG = Logger.getLogger(getClass().getSimpleName());

	Database db;

	public JTSAdapter(Database db) {
		this.db = db;
	}
	
	public String minimumBoundingRectangle(Layer lyr) throws ServiceException {
		try {
			String wkt = db.toWKT(lyr);
			LOG.log(Level.INFO, "WKT: " + wkt);
			Geometry geom = new WKTReader().read(wkt);
			MinimumDiameter helper = new MinimumDiameter(geom);
			return new WKTWriter().write(helper.getMinimumRectangle());
		} catch (ParseException ex) {
			throw new ServiceException(ex);
		}
	}
	
}