package com.ximple.eofms.jobs;

import com.ximple.eofms.geoserver.config.XGeosDataConfig;
import com.ximple.eofms.geoserver.config.XGeosDataConfigMapping;
import com.ximple.eofms.util.PrintfFormat;
import it.geosolutions.geoserver.rest.GeoServerRESTManager;
import it.geosolutions.geoserver.rest.GeoServerRESTPublisher;
import it.geosolutions.geoserver.rest.GeoServerRESTReader;
import it.geosolutions.geoserver.rest.manager.GeoServerRESTStoreManager;
import org.apache.commons.collections.MultiMap;
import org.apache.commons.digester3.Digester;
import org.apache.commons.digester3.binder.DigesterLoader;
import org.apache.commons.digester3.xmlrules.FromXmlRulesModule;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.geotools.data.DataStore;
import org.geotools.data.edbgeo.PostgisDataStore;
import org.geotools.data.postgis.PostGISDialect;
import org.geotools.jdbc.JDBCDataStore;
import org.geotools.jdbc.SQLDialect;
import org.quartz.Job;
import org.quartz.JobExecutionContext;
import org.quartz.JobExecutionException;
import org.xml.sax.SAXException;

import javax.naming.ConfigurationException;
import javax.sql.DataSource;
import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

public class GeoserverResetConfigJob extends OracleConvertDgn2PostGISJob {
    final static Log logger = LogFactory.getLog(GeoserverResetConfigJob.class);

    private static final String SKIPCONFIGJOB = "SKIPCONFIGJOB";
    private static final String MASTERMODE = "MASTERMODE";
    private static final String EPSG = "EPSG:";
    private static final String DEFAULTNAMESPACE = "tpc";
    private static final String XGEOSDATACONFIG_PATH = "xgeosdataconfig.xml";
    private static final String XGEOSRULES_NAME = "DefaultXGeosDataConfigRules.xml";

    // private static final int MAGIC_BLOCKSIZE = (64 * 1024 * 1024) - (32 * 1024);

    private static final String QUERY_VIEWDEFSQL = "SELECT table_name, view_definition FROM information_schema.views " +
            "WHERE table_schema = ? AND table_name LIKE ";

    private static final String CREATE_VIEWSQL = "CREATE OR REPLACE VIEW \"%s\" AS SELECT * FROM \"%s\".\"%s\"";
    private static final String EXTRAWHERE_VIEWSQL = " WHERE \"%s\".level = %s AND \"%s\".symweight = %s";

    private static final String ALTER_VIEWSQL = "ALTER TABLE \"%s\" OWNER TO ";
    // private static final String GRANT_VIEWSQL = "GRANT SELECT ON TABLE \"%s\" TO public";
    private static final int SRSID_TWD97_ZONE119 = 3825;
    private static final int SRSID_TWD97_ZONE121 = 3826;

    private static XGeosDataConfigMapping xgeosDataConfigMapping = null;

    protected XGeosDataConfigMapping getConfigMapping() {
        if (xgeosDataConfigMapping == null) {
            final URL rulesURL = XGeosDataConfigMapping.class.getResource(XGEOSRULES_NAME);
            assert rulesURL != null;
            /*
            FromXmlRulesModule rules = new FromXmlRulesModule() {
                @Override
                protected void loadRules() {
                    this.loadXMLRules(rulesURL);
                }
            };

            Digester digester = DigesterLoader.newLoader(rules).newDigester();
            */

            /*
            File rootDir = GeoserverDataDirectory.getGeoserverDataDirectory();
            File xfmsConfigDir;

            try {
                xfmsConfigDir = GeoserverDataDirectory.findConfigDir(rootDir, "xdgnjobs");
            } catch (ConfigurationException cfe) {
                logger.warn("no xmark dir found, creating new one");
                //if for some bizarre reason we don't fine the dir, make a new one.
                xfmsConfigDir = new File(rootDir, "xdgnjobs");
            }

            File xfmsConfigFile = new File(xfmsConfigDir, XGEOSDATACONFIG_PATH);
            try {
                xgeosDataConfigMapping = (XGeosDataConfigMapping) digester.parse(xfmsConfigFile);
            } catch (IOException e) {
                logger.warn(e.getMessage(), e);
            } catch (SAXException e) {
                logger.warn(e.getMessage(), e);
            }
            */
        }
        return xgeosDataConfigMapping;
    }

    @Override
    public void execute(JobExecutionContext jobExecutionContext) throws JobExecutionException {
        super.execute(jobExecutionContext);
        GeoServerRESTReader reader;
        GeoServerRESTManager manager;
        GeoServerRESTPublisher publisher;
    }

    /**
     * 重新建立所有重新建立所有PostGIS中的資料庫視景
     *
     * @param executionContext 批次執行的關係
     * @param dataStore        Geoserver的資料儲存連結
     * @param ownerName        資料庫視景擁有者名稱
     */
    private void resetPostgisViewMapping(JobExecutionContext executionContext, SQLDialect dataStore, String ownerName) {
        assert executionContext != null;
        try {
            if (dataStore instanceof PostGISDialect) {
                PostGISDialect pgDataStore = (PostGISDialect) dataStore;
                // DataSource dataSource = pgDataStore.getDataSource();
                // Connection connection = dataSource.getConnection();
                Connection connection = null;
                String currentTargetSchema = retrieveCurrentSchemaName(connection,
                        DataReposVersionManager.VSSTATUS_READY);
                if (currentTargetSchema == null) {
                    logger.info("Cannot found schema that status is VSSTATUS_READY[" +
                            DataReposVersionManager.VSSTATUS_READY + "]");
                    return;
                }

                ArrayList<String> realTableNames = new ArrayList<String>();
                retrieveAllRealTableName(connection, currentTargetSchema, realTableNames);

                HashMap<String, String> viewDefs = retrieveViewDef(connection, "public", "fsc%");
                HashMap<String, String> tempViewDefs = retrieveViewDef(connection, "public", "indexshape%");
                viewDefs.putAll(tempViewDefs);
                tempViewDefs = viewDefs = retrieveViewDef(connection, "public", "lndtpc%");
                viewDefs.putAll(tempViewDefs);

                for (String tableName : realTableNames) {
                    resetPostgisDataView(connection, viewDefs, ownerName, currentTargetSchema, tableName);
                }

                resetExtraPostgisDataView(connection, ownerName, currentTargetSchema, realTableNames);

                updateCurrentRepositoryStatus(connection, currentTargetSchema,
                        DataReposVersionManager.VSSTATUS_LINKVIEW);

                // String[] featureNames = dataStore.getTypeNames();
                // logger.info("featureNames[] size = " + featureNames.length);
            }
        // } catch (IOException e) {
        //     logger.warn(e.getMessage(), e);
        } catch (SQLException e) {
            logger.warn(e.getMessage(), e);
        } finally {
            // if (dataStore != null) dataStore.dispose();
        }
    }

    private void retrieveAllRealTableName(Connection connection, String targetSchema,
                                          ArrayList<String> realTableNames) throws SQLException {
        ResultSet rsMeta = null;
        try {
            rsMeta = connection.getMetaData().getTables("", targetSchema, "fsc%", new String[]{"TABLE"});
            while (rsMeta.next()) {
                String tableName = rsMeta.getString(3);
                realTableNames.add(tableName);
            }
            rsMeta.close();
            rsMeta = null;

            rsMeta = connection.getMetaData().getTables("", targetSchema, "index%", new String[]{"TABLE"});
            while (rsMeta.next()) {
                String tableName = rsMeta.getString(3);
                realTableNames.add(tableName);
            }
            rsMeta.close();
            rsMeta = null;

            rsMeta = connection.getMetaData().getTables("", targetSchema, "lndtpc%", new String[]{"TABLE"});
            while (rsMeta.next()) {
                String tableName = rsMeta.getString(3);
                realTableNames.add(tableName);
            }
        } finally {
            if (rsMeta != null) rsMeta.close();
        }
    }

    private void resetPostgisDataView(Connection connection, HashMap<String, String> viewDefs,
                                      String ownerName, String schemaName, String tableName) throws SQLException {
        String[] splits = tableName.split("-");
        if (splits.length > 3) {
            // feature table

            StringBuilder viewBuilder = new StringBuilder();
            viewBuilder.append(splits[0]);
            viewBuilder.append('-');
            viewBuilder.append(splits[1]);
            viewBuilder.append('-');
            viewBuilder.append(splits[2]);
            viewBuilder.append(splits[3]);
            String viewName = viewBuilder.toString();
            if (viewDefs.containsKey(viewName)) {
                String viewDef = viewDefs.get(viewName);
                int pos = viewDef.indexOf("FROM");
                String subView = viewDef.substring(pos + 4);
                // String[] viewSources = subView.split("\\.");
                String[] viewSources = subView.split("(\\.\"|\")");
                if (!viewSources[0].equalsIgnoreCase(schemaName)) {
                    createOrReplaceView(connection, schemaName, tableName, viewName, ownerName);
                }
            } else {
                createOrReplaceView(connection, schemaName, tableName, viewName, ownerName);
            }

        } else {

            splits = tableName.split("_");
            if (splits.length > 0) {
                StringBuilder viewBuilder = new StringBuilder();
                viewBuilder.append(splits[0]);
                if (splits.length > 1) viewBuilder.append(splits[1]);
                if (splits.length > 2) viewBuilder.append(splits[2]);
                String viewName = viewBuilder.toString();
                if (viewDefs.containsKey(viewName)) {
                    String viewDef = viewDefs.get(viewName);
                    int pos = viewDef.indexOf("FROM");
                    String subView = viewDef.substring(pos + 4);
                    String[] viewSources = subView.split("(\\.\"|\")");
                    if (!viewSources[0].equalsIgnoreCase(schemaName)) {
                        createOrReplaceView(connection, schemaName, tableName, viewName, ownerName);
                    }
                } else {
                    createOrReplaceView(connection, schemaName, tableName, viewName, ownerName);
                }
            }
        }
    }

    private void resetExtraPostgisDataView(Connection connection, String ownerName, String currentSchema,
                                           ArrayList<String> realTableNames) {
        try {
            // ArrayList<String> extraViewNames = new ArrayList<String>();
            XGeosDataConfigMapping configMapping = getConfigMapping();
            MultiMap configMultiMap = configMapping.getMapping();
            for (Object key : configMultiMap.keySet()) {
                List values = (List) configMultiMap.get(key);
                for (Object value : values) {
                    XGeosDataConfig xgeosConfig = (XGeosDataConfig) value;
                    short tid = xgeosConfig.getFSC();
                    short cid = xgeosConfig.getCOMP();
                    StringBuilder sbTable = new StringBuilder("fsc-");
                    sbTable.append(tid).append("-c-");
                    sbTable.append(cid);
                    int index = realTableNames.indexOf(sbTable.toString());
                    if (index == -1) {
                        logger.debug("Cannot found-" + xgeosConfig.toString());
                        continue;
                    }
                    StringBuilder sbView = new StringBuilder("fsc-");
                    sbView.append(tid).append("-c");
                    sbView.append(cid).append("-l");
                    sbView.append(xgeosConfig.getLEV()).append("-w");
                    sbView.append(xgeosConfig.getWEIGHT());
                    // extraViewNames.add(sbView.toString());

                    createOrReplaceExtraView(connection, currentSchema, sbTable.toString(), sbView.toString(),
                            ownerName, xgeosConfig);
                }
            }
        } catch (SQLException e) {
            logger.warn(e.getMessage(), e);
        }
    }

    private HashMap<String, String> retrieveViewDef(Connection connection, String schemaName, String tablePattern) throws SQLException {
        PreparedStatement stmt = connection.prepareStatement(QUERY_VIEWDEFSQL + "'" + tablePattern + "'");
        stmt.setString(1, schemaName);
        // stmt.setString(2, tablePattern);
        HashMap<String, String> result = new HashMap<String, String>();
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            String tableName = rs.getString(1);
            String viewDef = rs.getString(2);
            result.put(tableName, viewDef);
        }
        rs.close();
        stmt.close();
        return result;
    }

    private void createOrReplaceView(Connection connection, String schemaName, String tableName, String viewName,
                                     String ownerName) throws SQLException {
        PrintfFormat pf = new PrintfFormat(CREATE_VIEWSQL);
        String sql = pf.sprintf(new Object[]{viewName, schemaName, tableName});
        Statement stmt = connection.createStatement();
        stmt.execute(sql);

        pf = new PrintfFormat(ALTER_VIEWSQL + ownerName);
        sql = pf.sprintf(viewName);
        stmt.execute(sql);
        stmt.close();
        connection.commit();
    }

    private void createOrReplaceExtraView(Connection connection, String schemaName, String tableName, String viewName,
                                          String ownerName, XGeosDataConfig xgeosConfig) throws SQLException {
        PrintfFormat pf = new PrintfFormat(CREATE_VIEWSQL);
        String sql = pf.sprintf(new Object[]{viewName, schemaName, tableName});

        PrintfFormat pfWhere = new PrintfFormat(EXTRAWHERE_VIEWSQL);
        sql += pfWhere.sprintf(new String[]{tableName, Short.toString(xgeosConfig.getLEV()),
                tableName, Short.toString(xgeosConfig.getWEIGHT())});

        Statement stmt = connection.createStatement();
        stmt.execute(sql);

        pf = new PrintfFormat(ALTER_VIEWSQL + ownerName);
        sql = pf.sprintf(viewName);
        stmt.execute(sql);
        stmt.close();
        connection.commit();
    }

    private Timestamp retrieveCurrentSchemaTimestamp(Connection connection, short status) throws SQLException {
        StringBuilder sbSQL = new StringBuilder("SELECT vstimestamp, vsschema, vsstatus FROM ");
        sbSQL.append(DataReposVersionManager.XGVERSIONTABLE_NAME);
        sbSQL.append(" WHERE vsstatus = ");
        sbSQL.append(status);
        sbSQL.append(" ORDER BY vsid");

        Timestamp result = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(sbSQL.toString());
            // get first result
            if (rs.next()) {
                result = rs.getTimestamp(1);
            }
            return result;
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
        }
    }

    private void updateCurrentRepositoryStatus(Connection connection, String schemaName, short newStatus)
            throws SQLException {
        StringBuilder sbSQL = new StringBuilder("UPDATE ");
        sbSQL.append(DataReposVersionManager.XGVERSIONTABLE_NAME).append(' ');
        sbSQL.append(" SET vsstatus = ");
        sbSQL.append(newStatus);
        sbSQL.append(", vstimestamp = CURRENT_TIMESTAMP WHERE vsschema = '");
        sbSQL.append(schemaName).append("'");

        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            stmt.executeUpdate(sbSQL.toString());
        } finally {
            if (stmt != null) stmt.close();
        }
    }

    /*
    private boolean checkCurrentRepositoryStatus(DataStore dataStore, short status) {
        try {
            if (dataStore instanceof PostgisDataStore) {
                PostgisDataStore pgDataStore = (PostgisDataStore) dataStore;
                DataSource dataSource = pgDataStore.getDataSource();
                Connection connection = dataSource.getConnection();
                return checkCurrentRepositoryStatus(connection, status);
            }
        } catch (SQLException e) {
            logger.warn(e.getMessage(), e);
        }
        return false;
    }
    */

    private boolean checkCurrentRepositoryStatus(Connection connection, short status) {
        try {
            return (retrieveCurrentSchemaName(connection, status) != null);
        } catch (SQLException e) {
            logger.warn(e.getMessage(), e);
            return false;
        }
    }

    private String retrieveCurrentSchemaName(Connection connection, short status) throws SQLException {
        StringBuilder sbSQL = new StringBuilder("SELECT vsschema, vstimestamp, vsstatus FROM ");
        sbSQL.append(DataReposVersionManager.XGVERSIONTABLE_NAME);
        sbSQL.append(" WHERE vsstatus = ");
        sbSQL.append(status);
        sbSQL.append(" ORDER BY vsid");

        String result = null;
        Statement stmt = null;
        ResultSet rs = null;

        try {
            stmt = connection.createStatement();
            rs = stmt.executeQuery(sbSQL.toString());
            // get first result
            if (rs.next()) {
                result = rs.getString(1);
            }
            return result;
        } finally {
            if (rs != null) rs.close();
            if (stmt != null) stmt.close();
        }
    }
}