package org.simantics.jdbc.variable;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.HashMap;
import java.util.Map;

import org.simantics.databoard.Bindings;
import org.simantics.databoard.binding.Binding;
import org.simantics.simulator.toolkit.StandardNodeManagerSupport;
import org.simantics.simulator.variable.exceptions.NodeManagerException;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.impossibl.postgres.api.jdbc.PGConnection;
import com.impossibl.postgres.jdbc.PGDataSource;

public class JDBCNodeManagerSupport implements StandardNodeManagerSupport<JDBCNode> {

    private static final Logger LOGGER = LoggerFactory.getLogger(JDBCNodeManagerSupport.class);
    @SuppressWarnings("unused")
    private String id; // this might have some use later in the future?
    private PGDataSource dataSource;
    private String channelName;

    public JDBCNodeManagerSupport(String id, PGDataSource dataSource, String channelName) {
        this.id = id;
        this.dataSource = dataSource;
        this.channelName = channelName;
    }

    @Override
    public Object getEngineValue(JDBCNode node) throws NodeManagerException {
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("Getting value for {}", node.getName());

        try (PGConnection connection = (PGConnection) dataSource.getConnection()) {
            // do get value
            PreparedStatement ps = connection.prepareStatement("SELECT value->'value' FROM simantics_table WHERE key IN ('" + node.getName() + "');");
            ResultSet rs = ps.executeQuery();
            if (!rs.next()) {
                if (LOGGER.isDebugEnabled()) {
                    LOGGER.debug("no value for query {}", ps.toString());
                }
                return null;
            }
            return rs.getObject(1);
        } catch (Exception e) {
            LOGGER.error("Failed to get value for {}", node.getName(), e);
            throw new NodeManagerException("Failed to get value for " + node.getName(), e);
        }
    }

    @Override
    public Binding getEngineBinding(JDBCNode node) throws NodeManagerException {
        return Bindings.OBJECT;
    }

    @Override
    public void setEngineValue(JDBCNode node, Object value) throws NodeManagerException {
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("Setting value for {} to {}", node.getName(), value);

        setValueImpl(node.getName(), value);
    }

    private void setValueImpl(String name, Object value) throws NodeManagerException {
        try (PGConnection connection = (PGConnection) dataSource.getConnection()) {
            // do set value
            PreparedStatement statement = connection.prepareStatement("INSERT INTO simantics_table VALUES (?, ?::JSON) ON CONFLICT (key) DO UPDATE SET value= ?::JSON");
            statement.setString(1, name);
            statement.setObject(2, "{\"value\": " + value.toString() + "}");
            statement.setObject(3, "{\"value\": " + value.toString() + "}");
            statement.executeUpdate();

            // notify others (including ourselves)
            doNotify(connection, name);
        } catch (Exception e) {
            LOGGER.error("Failed to set value for {} to {}", name, value, e);
            throw new NodeManagerException("Failed to set value for " + name + " to " + String.valueOf(value), e);
        }
    }

    private void doNotify(PGConnection connection, String name) throws SQLException {
        if (LOGGER.isDebugEnabled())
            LOGGER.debug("Notifying change {} to channel {}", name, this.channelName);
        Statement statement = connection.createStatement();
        String sql = "NOTIFY " + this.channelName + ", '" + name + "'";
        statement.execute(sql);
        statement.close();
    }

    @Override
    public String getName(JDBCNode node) {
        return node.getName();
    }

    @Override
    public Map<String, JDBCNode> getChildren(JDBCNode node) {
        return Collections.emptyMap();
    }

    @Override
    public Map<String, JDBCNode> getProperties(JDBCNode node) {
        HashMap<String, JDBCNode> properties = new HashMap<>();
        try (PGConnection connection = (PGConnection) dataSource.getConnection()) {
            Statement st = connection.createStatement();
            ResultSet executeQuery = st.executeQuery("SELECT key FROM simantics_table");
            while (executeQuery.next()) {
                String key = executeQuery.getString(1);
                properties.put(key, new JDBCNode(key));
            }
        } catch (Exception e) {
            LOGGER.error("Could not read properties", e);
        }

        return properties;
    }

}