package org.jabref.shared;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

import org.jabref.shared.listener.OracleNotificationListener;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStatement;
import oracle.jdbc.dcn.DatabaseChangeRegistration;

/**
 * Processes all incoming or outgoing bib data to Oracle database and manages its structure.
 */
public class OracleProcessor extends DBMSProcessor {

    private OracleConnection oracleConnection;

    private OracleNotificationListener listener;

    private DatabaseChangeRegistration databaseChangeRegistration;


    public OracleProcessor(DBMSConnection connection) {
        super(connection);
    }

    /**
     * Creates and sets up the needed tables and columns according to the database type.
     *
     * @throws SQLException
     */
    @Override
    public void setUp() throws SQLException {
        connection.createStatement().executeUpdate(
                "CREATE TABLE \"ENTRY\" (" +
                "\"SHARED_ID\" NUMBER NOT NULL, " +
                "\"TYPE\" VARCHAR2(255) NULL, " +
                "\"VERSION\" NUMBER DEFAULT 1, " +
                "CONSTRAINT \"ENTRY_PK\" PRIMARY KEY (\"SHARED_ID\"))");

        connection.createStatement().executeUpdate("CREATE SEQUENCE \"ENTRY_SEQ\"");

        connection.createStatement().executeUpdate("CREATE TRIGGER \"ENTRY_T\" BEFORE INSERT ON \"ENTRY\" " +
                "FOR EACH ROW BEGIN SELECT \"ENTRY_SEQ\".NEXTVAL INTO :NEW.shared_id FROM DUAL; END;");

        connection.createStatement().executeUpdate(
                "CREATE TABLE \"FIELD\" (" +
                "\"ENTRY_SHARED_ID\" NUMBER NOT NULL, " +
                "\"NAME\" VARCHAR2(255) NOT NULL, " +
                "\"VALUE\" CLOB NULL, " +
                "CONSTRAINT \"ENTRY_SHARED_ID_FK\" FOREIGN KEY (\"ENTRY_SHARED_ID\") " +
                "REFERENCES \"ENTRY\"(\"SHARED_ID\") ON DELETE CASCADE)");

        connection.createStatement().executeUpdate(
                "CREATE TABLE \"METADATA\" (" +
                "\"KEY\"  VARCHAR2(255) NULL," +
                "\"VALUE\"  CLOB NOT NULL)");
    }

    @Override
    String escape(String expression) {
        return "\"" + expression + "\"";
    }

    @Override
    public void startNotificationListener(DBMSSynchronizer dbmsSynchronizer) {

        this.listener = new OracleNotificationListener(dbmsSynchronizer);

        try {
            oracleConnection = (OracleConnection) connection;

            Properties properties = new Properties();
            properties.setProperty(OracleConnection.DCN_NOTIFY_ROWIDS, "true");
            properties.setProperty(OracleConnection.DCN_QUERY_CHANGE_NOTIFICATION, "true");

            databaseChangeRegistration = oracleConnection.registerDatabaseChangeNotification(properties);
            databaseChangeRegistration.addListener(listener);

            try (Statement statement = oracleConnection.createStatement()) {
                ((OracleStatement) statement).setDatabaseChangeRegistration(databaseChangeRegistration);
                StringBuilder selectQuery = new StringBuilder()
                        .append("SELECT 1 FROM ")
                        .append(escape("ENTRY"))
                        .append(", ")
                        .append(escape("METADATA"));
                // this execution registers all tables mentioned in selectQuery
                statement.executeQuery(selectQuery.toString());
            }

        } catch (SQLException e) {
            LOGGER.error("SQL Error: ", e);
        }

    }

    @Override
    public void stopNotificationListener() {
        try {
            oracleConnection.unregisterDatabaseChangeNotification(databaseChangeRegistration);
            oracleConnection.close();
        } catch (SQLException e) {
            LOGGER.error("SQL Error: ", e);
        }
    }

    @Override
    public void notifyClients() {
        // Do nothing because Oracle triggers notifications automatically.
    }
}