package cities.utils;

import csip.api.server.ServiceException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.logging.Level;
import m.utils.Layer;
import m.utils.LocalStringUtils;
import m.utils.PostgresqlGISDatabase;
import m.utils.RandomString;
import m.utils.Table;

/**
* Last Updated: 31-August-2016
* @author Tyler Wible
* @since 30-August-2016
*/
public class PostgresqlLocationsDatabase extends PostgresqlGISDatabase {
    
    public static final String DEFAULT_SCHEMA = "csip_wrap";
    
    public PostgresqlLocationsDatabase(String hostname, String port, String dbName, String username, String password) throws ServiceException {
        super(hostname, port, dbName, username, password);
    }
    
    public PostgresqlLocationsDatabase(Connection conn) throws Exception {
        super(conn);
    }
    
    public Table getLocations(String schemaName, String tableName, Layer one) throws Exception {
        Layer locationLayer = new Layer(schemaName, tableName, this);
        String table = "loc_" + getSessionID() + "_" + new RandomString(4).nextString();
        String schemaTable = getDefaultSchema() + ".\"" + table + "\"";
        Statement stmt = conn.createStatement();

        createSchema(getDefaultSchema());
        deleteTable(schemaTable);

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

        Table res = new Table(getDefaultSchema(), table, this);

        stmt.close();
        return res;
    }
    
    public Layer getLocation(String schemaName, String tableName, int oid) throws Exception {
        return getLocation(schemaName, tableName, "objectid", oid);
    }
    
    public Layer getLocation(String schemaName, String tableName, String indexName, int oid) throws Exception {
        Layer locationLayer = new Layer(schemaName, tableName, this);
        String lyr = "loc_" + getSessionID() + "_" + new RandomString(4).nextString();
        String schemaTable = getDefaultSchema() + ".\"" + lyr + "\"";

        createSchema(getDefaultSchema());
        deleteTable(schemaTable);

        ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
            getProperties(locationLayer, false, new ArrayList<>(Arrays.asList(new String[]{}))),
        }));
        String sql = "CREATE TABLE " + schemaTable + " AS " +
                "SELECT " + LocalStringUtils.join(props, ", ") + " " +
                "FROM " + locationLayer.getSchemaTable() +
                "WHERE " + indexName + " = ? ";
        PreparedStatement prepStmt = conn.prepareStatement(sql);
        prepStmt.setInt(1, oid);
        LOG.log(Level.INFO, sql);
        prepStmt.executeUpdate();

        Layer res = new Layer(getDefaultSchema(), lyr, this);

        prepStmt.close();
        return res;
    }
 
    public Layer getLocationString(String schemaName, String tableName, String indexName, String indexValue) throws Exception {
        Layer locationLayer = new Layer(schemaName, tableName, this);
        String lyr = "loc_" + getSessionID() + "_" + new RandomString(4).nextString();
        String schemaTable = getDefaultSchema() + ".\"" + lyr + "\"";

        createSchema(getDefaultSchema());
        deleteTable(schemaTable);

        ArrayList<String> props = new ArrayList<>(Arrays.asList(new String[]{
            getProperties(locationLayer, false, new ArrayList<>(Arrays.asList("the_geom"))),
        }));
        String sql = "CREATE TABLE " + schemaTable + " AS " +
                "SELECT ST_UNION(" + locationLayer.getSchemaTable() + ".the_geom) as the_geom, '" + indexValue + "' as " + indexName + " " + 
                "FROM " + locationLayer.getSchemaTable() + " " +
                "WHERE " + indexName + " = ? ";
        PreparedStatement prepStmt = conn.prepareStatement(sql);
        prepStmt.setString(1, indexValue);
        LOG.log(Level.INFO, sql);
        prepStmt.executeUpdate();

        Layer res = new Layer(getDefaultSchema(), lyr, this);

        prepStmt.close();
        return res;
    }
 
}