/*
 * 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 huc.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;

/**
 *
 * @author ktraff
 */
public class PostgresqlHUCDatabase extends PostgresqlGISDatabase {
    
    public static final String DEFAULT_SCHEMA = "csip_wrap";
    
    public PostgresqlHUCDatabase(String hostname, String port, String dbName, String username, String password) throws ServiceException {
        super(hostname, port, dbName, username, password);
    }
    
    public PostgresqlHUCDatabase(Connection conn) throws Exception {
        super(conn);
    }
    
    public Table getHUCs(Layer one, int hucType, boolean excludeGeometry) throws Exception {
        Layer hucLayer = new Layer("public", "huc"+hucType, this);
        String table = "hucs_" + getSessionID() + "_" + new RandomString(4).nextString();
        String schemaTable = getDefaultSchema() + ".\"" + table + "\"";
        Statement stmt = conn.createStatement();
        Table res;

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

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

        if (excludeGeometry) {
            res = new Table(getDefaultSchema(), table, this);
        }
        else {
            res = new Layer(getDefaultSchema(), table, this);
        }

        stmt.close();
        return res;
    }
    
    public Layer getHUC(String id, int hucLevel) throws Exception {
        Layer hucLayer = new Layer("public", "huc"+hucLevel, this);
        String lyr = "huc_" + getSessionID() + "_" + new RandomString(4).nextString();
        String schemaTable = getDefaultSchema() + ".\"" + lyr + "\"";

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

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

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

        prepStmt.close();
        return res;
    }
 
}