package eu.dnetlib.espas.sosservice.jdbc;

import eu.dnetlib.espas.exception.OwsException;
import eu.dnetlib.espas.exception.OwsExceptionCode;
import eu.dnetlib.espas.jdbc.configuration.*;
import eu.dnetlib.espas.sosservice.*;
import org.apache.commons.dbcp.BasicDataSource;
import org.apache.log4j.Logger;
import org.apache.velocity.app.VelocityEngine;
import org.apache.velocity.runtime.RuntimeConstants;
import org.apache.velocity.runtime.resource.loader.ClasspathResourceLoader;
import org.springframework.transaction.annotation.Transactional;
import org.xml.sax.SAXException;

import javax.sql.DataSource;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.xpath.XPathExpressionException;
import java.io.IOException;
import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

@SuppressWarnings({"UnusedDeclaration", "unchecked", "UnusedAssignment", "ThrowFromFinallyBlock"})
@Transactional
public class JdbcSOSDataProvider implements SOSDataProvider {

    private static Logger logger = Logger.getLogger(JdbcSOSDataProvider.class);

    @SuppressWarnings({"unused", "FieldCanBeLocal"})
    private String timeZone = null;
    private SOSConfiguration jdbcSOSConfiguration = null;
    private String prefix = "jdbc.sos.";

    private VelocityEngine velocityEngine = null;

    public void init() {
        velocityEngine = new VelocityEngine();

        velocityEngine.addProperty(RuntimeConstants.RUNTIME_LOG_LOGSYSTEM_CLASS, "org.apache.velocity.runtime.log.Log4JLogChute");
        velocityEngine.addProperty("runtime.log.logsystem.log4j.logger", logger.getName());

        velocityEngine.addProperty(RuntimeConstants.RESOURCE_LOADER, "classpath");
        velocityEngine.addProperty("classpath.resource.loader.class", ClasspathResourceLoader.class.getName());
        velocityEngine.addProperty(RuntimeConstants.UBERSPECT_CLASSNAME, org.apache.velocity.util.introspection.SecureUberspector.class.getName());

        velocityEngine.init();
    }

    @Override
    public String getResultTemplate(Map<String, String> parameters) throws OwsException {
        Properties properties = jdbcSOSConfiguration.getProperties();
        SOSHelper helper = new SOSHelper();
        String resourceTemplate = null;
        boolean hasErrors = false;

        String observedProperty = parameters.get("observedproperty");
        String offering = parameters.get("offering");

        String[] processInfo = offering.split("\\/");

        try {
            if (processInfo.length > 0) {
                String process = processInfo[0];
                String namespace = processInfo[1];
                String localID = processInfo[2];
                String version = processInfo[3];
                String capabilityName = "";

                if (processInfo.length > 4)
                    capabilityName = processInfo[4];

                resourceTemplate = helper.getResourceTemplate(velocityEngine, properties, prefix, jdbcSOSConfiguration,
                        process, namespace, localID, version, observedProperty, capabilityName);
            } else throw new Exception("No parameters available. Check servlet for the parsed parameters.");
        } catch (XPathExpressionException e) {
            logger.error("XPathExpressionException in getResult", e);
            hasErrors = true;
        } catch (ParserConfigurationException e) {
            logger.error("ParserConfigurationException in getResult", e);
            hasErrors = true;
        } catch (IOException e) {
            logger.error("IOException in getResult", e);
            hasErrors = true;
        } catch (SAXException e) {
            logger.error("SAXException in getResult", e);
            hasErrors = true;
        } catch (Exception e) {
            logger.error("Exception in getResult", e);
            hasErrors = true;
        } finally {
            if (hasErrors) {
                hasErrors = false;
                throw new OwsException("Check Log file for errors", OwsExceptionCode.NO_APPLICABLE_CODE);
            }
        }

        return resourceTemplate;
    }

    @Override
    public String getResult(Map<String, String> parameters) throws OwsException {
        Properties properties = jdbcSOSConfiguration.getProperties();
        SOSHelper helper = new SOSHelper();

        String observedProperty = null;
        String offering = null;
        String resource = null;
        boolean hasErrors = false;
        String select = null;
        String from = null;
        String where = null;

        try {
            observedProperty = parameters.get("observedproperty");
            offering = parameters.get("offering");

            String[] processInfo = offering.split("\\/");

            if (processInfo.length > 0) {
                String process = processInfo[0];
                String namespace = processInfo[1];
                String localID = processInfo[2];
                String version = processInfo[3];
                String capabilityName = "";

                if (processInfo.length > 4)
                    capabilityName = processInfo[4];

                String service = properties.getProperty(prefix + "csw.service");
                String blockSeparator = properties.getProperty(prefix + "blockseparator");
                String tokenSeparator = properties.getProperty(prefix + "tokenseparator");

                String urlLabel;
                if (capabilityName.isEmpty())
                    urlLabel = helper.getObservedProperty(observedProperty);
                else
                    urlLabel = capabilityName;

                String dbName = properties.getProperty(prefix + "db.name");
                String dbType = properties.getProperty(prefix + "db.type");
                String dbClassname = properties.getProperty(prefix + "db.classname");
                String dbUrl = properties.getProperty(prefix + "db.url");
                String dbUsername = properties.getProperty(prefix + "db.username");
                String dbPassword = properties.getProperty(prefix + "db.password");

                Database database = new Database(dbName, dbType, dbClassname,
                        dbUrl, dbUsername, dbPassword);

                select = helper.retrieveProperty(urlLabel, localID, prefix, "select", properties);
                from = helper.retrieveProperty(urlLabel, localID, prefix, "from", properties);
                where = helper.retrieveProperty(urlLabel, localID, prefix, "where", properties);

                if (parameters.get("temporalfilter") != null) {
                    Set<Map<String, String>> temporalQueryParsedSet = helper.temporalQueryParser(parameters.get("temporalfilter"));
                    String startRegex = "(\\$\\{startdate\\})";
                    String endRegex = "(\\$\\{enddate\\})";
                    DateFormat utcFormat = new SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ss'Z'");
                    utcFormat.setTimeZone(TimeZone.getTimeZone("UTC"));

                    DateFormat localFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    TimeZone localTimeZone = TimeZone.getTimeZone(timeZone);
                    localFormat.setTimeZone(localTimeZone);

                    Iterator<Map<String, String>> iterator = temporalQueryParsedSet.iterator();
                    String statement;

                    if (where != null) {
                        while (iterator.hasNext()) {
                            Map<String, String> temporalQueryParsed = iterator.next();
                            for (String key : temporalQueryParsed.keySet()) {
                                Date date = utcFormat.parse(temporalQueryParsed.get(key));
                                if (key.contains("from")) {
                                    where = where.replaceFirst(startRegex, localFormat.format(date));
                                }
                                if (key.contains("to")) {
                                    where = where.replaceFirst(endRegex, localFormat.format(date));
                                }
                                if (key.contains("instant")) {
                                    where = where.replaceFirst(startRegex, localFormat.format(date));
                                    where = where.replaceFirst(endRegex, localFormat.format(date));
                                }
                            }
                        }
                        statement = "select " + select + " from " + from + " where " + where;
                    } else
                        statement = "select " + select + " from " + from;

                    String url = helper.CSWGetRecordByIdUrl(properties, prefix, process, namespace, localID, version);
                    Capability capability = helper.getProcessCapability(url, observedProperty, capabilityName);
                    resource = jdbcCreateXml(database, temporalQueryParsedSet, statement, tokenSeparator, blockSeparator, capability.getFillValue());
                }
            } else {
                throw new OwsException("No parameters available. Check servlet for the parsed parameters.", OwsExceptionCode.MISSING_PARAMETER);
            }
        } catch (ParseException e) {
            logger.error("Timezone format parse exception in getResult", e);
            hasErrors = true;
        } catch (SAXException e) {
            logger.error("SAXException", e);
        } catch (ParserConfigurationException e) {
            logger.error("ParserConfigurationException", e);
        } catch (IOException e) {
            logger.error("IOException", e);
        } finally {
            if (hasErrors) {
                hasErrors = false;
                if (select == null)
                    throw new OwsException("No entry for requested Offering", OwsExceptionCode.INVALID_PROPERTY_OFFERING_COMBINATION);

                throw new OwsException("Check Log file for errors", OwsExceptionCode.NO_APPLICABLE_CODE);
            }
        }

        return resource;
    }

    private String jdbcCreateXml(Database database, Set<Map<String, String>> temporalQueryParsedSet,
                                 String statement, String tokenSeparator, String blockSeparator, String fillValue) throws OwsException {

        Connection conn = null;


        StringBuilder resourceBuilder = new StringBuilder();
        SOSHelper helper = new SOSHelper();

        try {
            conn = this.getDatasource(database).getConnection();

            PreparedStatement stmt = conn.prepareStatement(statement);

            String xmlHeader = "<?xml version=\"1.0\" encoding=\"UTF-8\"?>\n" +
                    "<sos:GetResultResponse " +
                    "xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" " +
                    "xmlns:swe=\"http://www.opengis.net/swe/2.0\" " +
                    "xmlns:swes=\"http://www.opengis.net/swes/2.0\" " +
                    "xmlns:ows=\"http://www.opengis.net/ows/1.1\" " +
                    "xmlns:sos=\"http://www.opengis.net/sos/2.0\" " +
                    "xmlns:fes=\"http://www.opengis.net/fes/2.0\" " +
                    "xmlns:gml=\"http://www.opengis.net/gml/3.2\" " +
                    "xmlns:ogc=\"http://www.opengis.net/ogc\" " +
                    "xmlns:om=\"http://www.opengis.net/om/2.0\" " +
                    "xmlns:xlink=\"http://www.w3.org/1999/xlink\" " +
                    "xsi:schemaLocation=\"http://www.opengis.net/sos/2.0 http://schemas.opengis.net/sos/2.0/sos.xsd\">\n" +
                    "<sos:resultValues>";
            String xmlFooter = "</sos:resultValues>\n" +
                    "</sos:GetResultResponse>";

            final ResultSet rs = stmt.executeQuery();
            int resultCount = 0;

            while (rs.next()) {
                ResultSetMetaData metaData = rs.getMetaData();
                int count = metaData.getColumnCount() + 1;
                StringBuilder resultBuilder = new StringBuilder();
                String value = null;

                for (int i = 1; i < count; i++) {
                    Date date = null;
                    value = rs.getString(i);
                    if (value == null || value.equals(""))
                        value = fillValue;

                    try {
                        helper.appendValue(timeZone, resultBuilder, value);
                    } catch (NullPointerException e) {
                        break;
                    }
                    resultBuilder.append(tokenSeparator);
                }

                if (value != null) {
                    resultCount++;
                    resourceBuilder.append(resultBuilder.toString().replaceAll(tokenSeparator+"$", "")).append(blockSeparator);
                }
            }

            resourceBuilder.insert(0, resultCount + blockSeparator);
            resourceBuilder.insert(0, xmlHeader);
            resourceBuilder.append(xmlFooter);

            if (rs != null)
                rs.close();
            if (stmt != null)
                stmt.close();
        } catch (SQLException e) {
            logger.error("Error in database", e);
            throw new OwsException("Error connecting to the database", OwsExceptionCode.NO_APPLICABLE_CODE);
        } finally {
            try {
                if (conn != null)
                    conn.close();
            } catch (SQLException e) {
                logger.error("Error closing database", e);
                throw new OwsException("Error closing database", OwsExceptionCode.NO_APPLICABLE_CODE);
            }
        }
        return resourceBuilder.toString();
    }

    private Map<String, Date> spatialQueryParser(String temporalFilter) throws OwsException {
        return null;
    }

    public DataSource getDatasource(Database database) throws SQLException {
        if (database != null) {
            BasicDataSource datasource = new BasicDataSource();

            datasource.setUrl(database.getUrl());
            datasource.setDriverClassName(database.getClassName());
            datasource.setUsername(database.getUsername());
            datasource.setPassword(database.getPassword());
            datasource.setMaxIdle(10);
            datasource.setMaxActive(100);
            datasource.setMaxWait(10000);
            datasource.setValidationQuery(database.getValidationQuery());
            datasource.setTestOnBorrow(true);
            datasource.setTestWhileIdle(true);
            datasource.setTimeBetweenEvictionRunsMillis(1200000);
            datasource.setMinEvictableIdleTimeMillis(1800000);
            datasource.setNumTestsPerEvictionRun(5);
            datasource.setDefaultAutoCommit(false);

            return datasource;
        } else {
            logger.error("Cannot find database configuration");
            throw new SQLException("Cannot find database configuration");
        }
    }

    public String getTimeZone() {
        return timeZone;
    }

    public void setTimeZone(String timeZone) {
        this.timeZone = timeZone;
    }

    public SOSConfiguration getJdbcSOSConfiguration() {
        return jdbcSOSConfiguration;
    }

    public void setJdbcSOSConfiguration(SOSConfiguration jdbcSOSConfiguration) {
        this.jdbcSOSConfiguration = jdbcSOSConfiguration;
    }
}