package com.ximple.eofms.jobs.context;

import java.io.IOException;
import java.sql.Connection;
import java.util.Properties;

import org.apache.commons.logging.Log;
import org.geotools.data.DataStore;
import org.geotools.data.Transaction;
import org.geotools.data.jdbc.JDBCUtils;
import org.geotools.data.oracle.OracleDataStore;

public abstract class AbstractOracleJobContext {
    /**
     * Table Prefiex
     */
    protected static final String TABLE_PREFIX = "GEO$";
    protected static final String UDT_SCHEMA = "SPATIALDB";
    /**
     * User Types
     */
    protected static final String UDT_RAWS = "CREATE OR REPLACE TYPE \"" + UDT_SCHEMA + "\".\"RAWS\" AS VARRAY (1048576) OF NUMBER(38)";
    protected static final String UDT_OFMID = "CREATE OR REPLACE TYPE  \"" + UDT_SCHEMA + "\".\"OFMID\" AS OBJECT ("
        + "\"CLSID\" NUMBER(5), \"OID\" NUMBER(10), \"STATUS\" NUMBER(5), \"COMPID\" NUMBER(3), "
        + "\"RULEID\" NUMBER(3), \"OCCID\" NUMBER(3))";
    protected static final String UDT_RAWSNAME = "RAWS";
    protected static final String UDT_OFMIDNAME = "OFMID";
    /**
     * Utility SQL
     */
    protected static final String TAB_CASCADEDROP = "DROP TABLE %s.%s CASCADE CONSTRAINTS";
    protected static final String TAB_DROP = "DROP TABLE %s.%s";
    protected static final String TAB_DELETE = "DELETE FROM %s.%s";
    /**
     * Table Schema
     */
    protected static final String TAB_RANGENODEINDEX_1 = "CREATE TABLE \"%s\".\"%s\"\n"
        + "   (    \"RNID\" INTEGER NOT NULL ENABLE,\n"
        + "    \"RPID\" INTEGER NOT NULL ENABLE,\n"
        + "    \"RNG_LOWX\" BINARY_DOUBLE NOT NULL ENABLE,\n"
        + "    \"RNG_LOWY\" BINARY_DOUBLE NOT NULL ENABLE,\n"
        + "    \"RNG_HIGHX\" BINARY_DOUBLE NOT NULL ENABLE,\n"
        + "    \"RNG_HIGHY\" BINARY_DOUBLE NOT NULL ENABLE,\n"
        + "    \"EXTENTS\" MDSYS.SDO_GEOMETRY,\n"
        + "    \"RNDESCR\" VARCHAR2(255), \n"
        + "    PRIMARY KEY ( \"RNID\" ) ENABLE )";
    protected static final String TAB_RANGENODEINDEX = "CREATE TABLE \"%s\".\"%s\"\n"
        + "   (    \"RNID\" INTEGER NOT NULL ENABLE,\n"
        + "    \"RPID\" INTEGER NOT NULL ENABLE,\n"
        + "    \"RNG_LOWX\" FLOAT NOT NULL ENABLE,\n"
        + "    \"RNG_LOWY\" FLOAT NOT NULL ENABLE,\n"
        + "    \"RNG_HIGHX\" FLOAT NOT NULL ENABLE,\n"
        + "    \"RNG_HIGHY\" FLOAT NOT NULL ENABLE,\n"
        + "    \"RNDESCR\" VARCHAR2(255), \n"
        + "    PRIMARY KEY ( \"RNID\" ) ENABLE )";
    protected static final String TAB_RANGENODESTORAGE = "CREATE TABLE \"%s\".\"%s\"\n"
        + "   (    \"RNID\" INTEGER NOT NULL ENABLE,\n"
        + "    \"LAYERID\" NUMBER(5,0) NOT NULL ENABLE,\n"
        + "    \"LASTUPDATE\" DATE NOT NULL ENABLE,\n"
        + "    \"SPACETABLE\" VARCHAR2(255)\n" + "   )";
    protected static final String TAB_ELEMENTINDEX_1 = "CREATE TABLE %s.%s (\n"
        + "    \"ELMNO\" INTEGER NOT NULL ENABLE,\n"
        + "    \"TYPE\" NUMBER(5) NOT NULL ENABLE,\n"
        + "    \"XLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n"
        + "    \"YLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n"
        + "    \"ZLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n"
        + "    \"XHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n"
        + "    \"YHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n"
        + "    \"ZHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n"
        + "    \"TAG_LUFID\" NUMBER(10) NOT NULL ENABLE,\n"
        + "    \"TAG_SFSC\" NUMBER(5) NOT NULL ENABLE,\n"
        + "    \"TAG_SSTAT\" NUMBER(5) NOT NULL ENABLE,\n"
        + "    \"TAG_BCOMPID\" NUMBER(3) NOT NULL ENABLE,\n"
        + "    \"TAG_BRULENO\" NUMBER(3) NOT NULL ENABLE,\n"
        + "    \"TAG_SOCCID\" NUMBER(5) NOT NULL ENABLE,\n"
        + "    \"SPACENAME\" VARCHAR2(255) NOT NULL ENABLE,\n"
        + "     PRIMARY KEY (\"ELMNO\") ENABLE\n"
        + "   )";
    protected static final String TAB_ELEMENTINDEX = "CREATE TABLE %s.%s (\n"
        + "    \"ELMNO\" INTEGER NOT NULL ENABLE,\n"
        + "    \"TYPE\" NUMBER(5) NOT NULL ENABLE,\n"
        + "    \"XLOW\" FLOAT NOT NULL ENABLE,\n"
        + "    \"YLOW\" FLOAT NOT NULL ENABLE,\n"
        + "    \"ZLOW\" FLOAT NOT NULL ENABLE,\n"
        + "    \"XHIGH\" FLOAT NOT NULL ENABLE,\n"
        + "    \"YHIGH\" FLOAT NOT NULL ENABLE,\n"
        + "    \"ZHIGH\" FLOAT NOT NULL ENABLE,\n"
        + "    \"TAG_LUFID\" NUMBER(10) NOT NULL ENABLE,\n"
        + "    \"TAG_SFSC\" NUMBER(5) NOT NULL ENABLE,\n"
        + "    \"TAG_SSTAT\" NUMBER(5) NOT NULL ENABLE,\n"
        + "    \"TAG_BCOMPID\" NUMBER(3) NOT NULL ENABLE,\n"
        + "    \"TAG_BRULENO\" NUMBER(3) NOT NULL ENABLE,\n"
        + "    \"TAG_SOCCID\" NUMBER(5) NOT NULL ENABLE,\n"
        + "    \"SPACENAME\" VARCHAR2(255) NOT NULL ENABLE,\n"
        + "     PRIMARY KEY (\"ELMNO\") ENABLE\n"
        + "   )";
    protected static final String TAB_IGDSSEED = "CREATE TABLE  %s.%s (\n"
        + "   \"ELMNO\" INTEGER NOT NULL ENABLE,\n"
        + "   \"SEEDELM\" \"" + UDT_SCHEMA
        + "\".\"RAWS\" NOT NULL ENABLE\n" + "   )";
    protected static final String TAB_STORAGE_1 = "CREATE TABLE %s.%s (\n" +
        "    \"ELMNO\" INTEGER NOT NULL ENABLE,\n" +
        "    \"XLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
        "    \"YLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
        "    \"XHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
        "    \"YHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
        "    \"EXTENTS\" MDSYS.SDO_GEOMETRY, \n" +
        "    \"PROPS\" INTEGER NOT NULL ENABLE,\n" +
        "    \"TAG_LUFID\" NUMBER(10) NOT NULL ENABLE,\n" +
        "    \"TAG_SFSC\" NUMBER(5) NOT NULL ENABLE,\n" +
        "    \"TAG_SSTAT\" NUMBER(5) NOT NULL ENABLE,\n" +
        "    \"TAG_BCOMPID\" NUMBER(3) NOT NULL ENABLE,\n" +
        "    \"TAG_BRULENO\" NUMBER(3) NOT NULL ENABLE,\n" +
        "    \"TAG_SOCCID\" NUMBER(5) NOT NULL ENABLE,\n" +
        "    \"LAYERID\" NUMBER(5) NOT NULL ENABLE,\n" +
        "    \"ELEMENT\" \"" + UDT_SCHEMA + "\".\"RAWS\" NOT NULL ENABLE, \n" +
        "    \"GEOM\" MDSYS.SDO_GEOMETRY \n" +
        "   )";
    protected static final String TAB_STORAGE = "CREATE TABLE %s.%s (\n" +
        "    \"ELMNO\" INTEGER NOT NULL ENABLE,\n" +
        "    \"XLOW\" FLOAT NOT NULL ENABLE,\n" +
        "    \"YLOW\" FLOAT NOT NULL ENABLE,\n" +
        "    \"XHIGH\" FLOAT NOT NULL ENABLE,\n" +
        "    \"YHIGH\" FLOAT NOT NULL ENABLE,\n" +
        "    \"PROPS\" INTEGER NOT NULL ENABLE,\n" +
        "    \"TAG_LUFID\" NUMBER(10) NOT NULL ENABLE,\n" +
        "    \"TAG_SFSC\" NUMBER(5) NOT NULL ENABLE,\n" +
        "    \"TAG_SSTAT\" NUMBER(5) NOT NULL ENABLE,\n" +
        "    \"TAG_BCOMPID\" NUMBER(3) NOT NULL ENABLE,\n" +
        "    \"TAG_BRULENO\" NUMBER(3) NOT NULL ENABLE,\n" +
        "    \"TAG_SOCCID\" NUMBER(5) NOT NULL ENABLE,\n" +
        "    \"LAYERID\" NUMBER(5) NOT NULL ENABLE,\n" +
        "    \"ELEMENT\" \"" + UDT_SCHEMA + "\".\"RAWS\" NOT NULL ENABLE \n" +
        "   )";
    protected static final String TAB_STORAGE2 = "CREATE TABLE %s.%s (\n" +
        "    \"ELMNO\" INTEGER NOT NULL ENABLE,\n" +
        "    \"XLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
        "    \"YLOW\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
        "    \"XHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
        "    \"YHIGH\" BINARY_DOUBLE NOT NULL ENABLE,\n" +
        "    \"EXTENTS\" MDSYS.SDO_GEOMETRY, \n" +
        "    \"PROPS\" INTEGER NOT NULL ENABLE,\n" +
        "    \"ID\" " + UDT_SCHEMA + ".OFMID NOT NULL ENABLE,\n" +
        "    \"LAYERID\" NUMBER(5) NOT NULL ENABLE,\n" +
        "    \"ELEMENT\" \"" + UDT_SCHEMA + "\".\"RAWS\" NOT NULL ENABLE \n" +
        "   )";
    /**
     * Trigger
     */
    protected static final String TRG_SPACENODE = "CREATE OR REPLACE TRIGGER \"%s\".\"%s\"\n"
        + "    AFTER DELETE OR INSERT OR UPDATE ON \"%s\".\"%s\"\n" + "    BEGIN\n"
        + "        UPDATE SPACENODES SET LASTUPDATE = SYSDATE\n" + "        WHERE SNID = \"%d\";\n"
        + "    END;";
    protected static final String TRG_ELMINDEX =
        "CREATE OR REPLACE TRIGGER \"%s\".\"%s\"\n"
            + "    AFTER INSERT OR UPDATE OR DELETE ON \"%s\".\"%s\"\n"
            + "    REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW\n"
            + "    BEGIN\n"
            + "        IF INSERTING THEN\n"
            + "            INSERT INTO \"%s\".\"%s\" (ELMNO, TYPE, XLOW, YLOW, XHIGH, YHIGH,\n"
            + "                UFID, FSC, COMPID, OCCID, SPACENAME)\n"
            + "            VALUES (SD$ELEMENTNUMBER_SEQ.NEXTVAL, :new.ELMTYPE, :new.XLOW, :new.YLOW, :new.XHIGH, :new.YHIGH,\n"
            + "                :new.UFID, :new.FSC, :new.COMPID, :new.OCCID, '%s');\n"
            + "        ELSIF DELETING THEN\n"
            + "            DELETE FROM \"%s\".\"%s\"\n"
            + "            WHERE \"%s\".UFID = :old.UFID AND\n"
            + "                \"%s\".FSC = :old.FSC AND\n"
            + "                \"%s\".COMPID = :old.COMPID AND\n"
            + "                \"%s\".OCCID = :old.OCCID;\n"
            + "        ELSE\n" + "            UPDATE \"%s\"\n"
            + "            SET XLOW = :new.XLOW, YLOW = :new.YLOW, XHIGH = :new.XHIGH, YHIGH = :new.YHIGH\n"
            + "            WHERE FSC = :new.FSC AND UFID = :new.UFID AND COMPID = :new.COMPID AND OCCID = :new.OCCID;\n"
            + "        END IF;\n" + "    END;";

    /**
     * copy connectivity to connectivity_webcheck sql
     */
    public static final String CONNECTIVITY_WEBCHECK_NAME = "CONNECTIVITY_WEBCHECK";
    public static final String TRUNCATE_CONNECTIVITY_WEBCHECK = "TRUNCATE TABLE BASEDB.CONNECTIVITY_WEBCHECK";
    public static final String CREATE_CONNECTIVITY_WEBCHECK = "CREATE TABLE BASEDB.CONNECTIVITY_WEBCHECK (\n" +
        " \"FSC\"      NUMBER(5) NOT NULL," +
        " \"UFID\"     NUMBER(10) NOT NULL," +
        " \"N1\"       NUMBER(10)," +
        " \"N2\"       NUMBER(10)," +
        " \"FDR1\"     NUMBER(5)," +
        " \"FDR2\"     NUMBER(5)," +
        " \"DIR\"      NUMBER(3)," +
        " \"OHUG\"     NUMBER(3)," +
        " \"OSTATUS\"  NUMBER(3)," +
        " \"PHASE\"    NUMBER(3)," +
        " \"X\"        NUMBER(10,3) NOT NULL," +
        " \"Y\"        NUMBER(10,3) NOT NULL" +
        ")";


    public static final String COPY_CONNECTIVITY_TO_WEBCHECK = "INSERT /*+ APPEND */ INTO BASEDB.CONNECTIVITY_WEBCHECK\n" +
        "(FSC, UFID, N1, N2, FDR1, FDR2, DIR, OHUG,OSTATUS, PHASE, X, Y)\n" +
        "SELECT FSC, UFID, N1, N2, FDR1, FDR2, DIR, OHUG, OSTATUS, PHASE, X, Y FROM BASEDB.CONNECTIVITY ";

    public static final String CREATE_CONNECTIVITY_WEBCHECK_INDEX_1 =
        "CREATE INDEX BASEDB.CONNECTIVITY_FDR1_WEB ON BASEDB.CONNECTIVITY_WEBCHECK (FDR1)";

    public static final String CREATE_CONNECTIVITY_WEBCHECK_INDEX_2 =
        "CREATE INDEX BASEDB.CONNECTIVITY_FSCUFID_INDEX_WEB ON BASEDB.CONNECTIVITY_WEBCHECK (FSC, UFID)";

    public static final String CREATE_CONNECTIVITY_WEBCHECK_INDEX_3 =
        "CREATE INDEX BASEDB.CONNECTIVITY_N1TON2_WEB ON BASEDB.CONNECTIVITY_WEBCHECK (N1, N2)";

    public static final String CREATE_CONNECTIVITY_WEBCHECK_INDEX_4 =
        "CREATE INDEX BASEDB.CONNECTIVITY_N1_INDEX_WEB ON BASEDB.CONNECTIVITY_WEBCHECK (N1)";

    public static final String CREATE_CONNECTIVITY_WEBCHECK_INDEX_5 =
        "CREATE INDEX BASEDB.CONNECTIVITY_N2TON1_WEB ON BASEDB.CONNECTIVITY_WEBCHECK (N2, N1)";

    public static final String CREATE_CONNECTIVITY_WEBCHECK_INDEX_6 =
        "CREATE INDEX BASEDB.CONNECTIVITY_N2_INDEX_WEB ON BASEDB.CONNECTIVITY_WEBCHECK (N2)";

    public static final String ALTER_CONNECTIVITY_WEBCHECK_1 =
        "ALTER TABLE BASEDB.CONNECTIVITY_WEBCHECK ADD (\n" +
            "  CONSTRAINT CONNECT_UNI_WEB UNIQUE (FSC,UFID))";

    public static final String ALTER_CONNECTIVITY_WEBCHECK_2 =
        "ALTER TABLE BASEDB.CONNECTIVITY_WEBCHECK ADD (\n" +
            "  CONSTRAINT CONN_N1N2_WEB UNIQUE (N1,N2))";


    public static final String GET_HIBERNATE_SEQUENCE=
        "SELECT BASEDB.HIBERNATE_SEQUENCE.NEXTVAL FROM DUAL";

    public static final String CREATE_HIBERNATE_SEQUENCE=
        "CREATE SEQUENCE BASEDB.HIBERNATE_SEQUENCE\n" +
        "  START WITH 1\n" +
        "  MAXVALUE 999999999999999999999999999\n" +
        "  MINVALUE 1\n" +
        "  NOCYCLE\n" +
        "  CACHE 20\n" +
        "  NOORDER";

    /**
     *
     */
    protected static final String TAB_ELEMENTSET_PREFIX = "ELMSET_";
    protected static final String STMT_CLEARCYCLEBIN = "PURGE RECYCLEBIN";
    protected static final String SMTM_GRANTOBJECTTYPE = "GRANT EXECUTE ANY TYPE TO \"" + UDT_SCHEMA + "\"";
    protected static final long TIMEOUT = Long.MAX_VALUE;

    /**
     * Encoding of URL path.
     */
    protected static final String ENCODING = "UTF-8";
    protected OracleDataStore sourceDataStore;

    protected String _dataPath;
    protected Properties properties;
    protected boolean _elementLogging;
    private Connection connection = null;
    private boolean profileMode;
    private boolean useTransform;

    private long _processTime;
    private long _updateTime;
    private long _processTimeStart;
    private long _updateTimeStart;

    public AbstractOracleJobContext(boolean profileMode, boolean useTransform) {
        this.profileMode = profileMode;
        this.useTransform = useTransform;
    }

    public OracleDataStore getSourceDataStore() {
        return sourceDataStore;
    }

    public void setSourceDataStore(DataStore sourceDataStore) {
        if ((sourceDataStore != null) && (sourceDataStore instanceof OracleDataStore)) {
            this.sourceDataStore = (OracleDataStore) sourceDataStore;
        } else {
            assert sourceDataStore != null;
            getLogger().warn("setSourceDataStore(datastore) need OracleDataStore but got " +
                sourceDataStore.getClass().getName());
        }
    }

    public Connection getOracleConnection() {
        if (connection != null) return connection;
        try {
            if (sourceDataStore != null) {
                connection = sourceDataStore.getConnection(Transaction.AUTO_COMMIT);
            }
        } catch (IOException e) {
            getLogger().warn(e.getMessage(), e);
        }

        return connection;
    }

    public void closeOracleConnection() {
        if (connection == null) {
            JDBCUtils.close(connection, Transaction.AUTO_COMMIT, null);
            connection = null;
        }
    }

    public void setDataPath(String dataPath) {
        _dataPath = dataPath;
    }

    public String getDataPath() {
        return _dataPath;
    }

    public boolean getElementLogging() {
        return _elementLogging;
    }

    public void setElementLogging(boolean elementLogging) {
        _elementLogging = elementLogging;
    }

    public abstract void startTransaction();

    public abstract void commitTransaction();

    public abstract void rollbackTransaction();

    protected abstract Log getLogger();

    public final boolean isProfileMode() {
        return profileMode;
    }

    public boolean isTransformed() {
        return useTransform;
    }

    public final void accumulateProcessTime() {
        _processTime += System.currentTimeMillis() - _processTimeStart;
    }

    public final void accumulateUpdateTime() {
        _updateTime += System.currentTimeMillis() - _updateTimeStart;
    }

    public final long getProcessTime() {
        return _processTime;
    }

    public final long getUpdateTime() {
        return _updateTime;
    }

    public final void markProcessTime() {
        _processTimeStart = System.currentTimeMillis();
    }

    public final void markUpdateTime() {
        _updateTimeStart = System.currentTimeMillis();
    }

    public final void resetUpdateTime() {
        _updateTime = 0;
    }

    public final void resetProcessTime() {
        _processTime = 0;
    }
}