package com.cogix.vwf;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import java.sql.*;
import java.text.DateFormat;
import java.util.Vector;

/**
 * Prerequisite:
 * ViewsFlash 5 or higher
 *
 * Revised April 2008
 */

abstract class DatabaseExtension {
    protected dbStore db;
    protected DateFormat dfdate = Dirs.getDateFormat();
    protected DateFormat dftime = Dirs.getTimeFormat();

    protected int coltype;
    protected String coltypn;
    int typeblob = java.sql.Types.CLOB; //  2005: DB2
    int typevarchar = java.sql.Types.VARCHAR;   //  12: Oracle
    int typelongvarchar = java.sql.Types.LONGVARCHAR;   //  -1 MySql

    public String TableName;
    String OverridenTableName;
    String UnqualifiedTableName;

    public boolean bAnswerIsBlob;
    //  Returns whether answers are searchable; not so when BLOB has been set
    public boolean bFilterAnsweredOnly () {
        return bAnswerIsBlob;
    }

    boolean bDisableNativeDatabaseSave () {
        return false;
    }


    //	Will be written to the log with the class name
    String getVersion () {
        return "0.0";
    }

    void preinit (ServletConfig conf) {
        //  Do nothing in generic DB
    }

    public String TableName () {
        if ( this.TableName == null )  {
            UnqualifiedTableName = OverridenTableName != null ? OverridenTableName : "VWFDATA";
            TableName = Dirs.getdbStore ().getTableName ( UnqualifiedTableName);
        }
        return TableName;
    }
    //	Called at end of servlet initialization
    void init (ServletConfig conf) throws ServletException {
        db = Dirs.getdbStore();	//	must do this to use built-in database access
        if ( db == null )		//	Quit like this on serious error:
            throw new ServletException ("Database Extension requires using a database");
        Connection con = null;
        try {
            con = db.getConnection();
            TableName() ;   //  Primes UnqualifiedTableName
            if ( ObjectStorage.TableExists(con, UnqualifiedTableName ) == null )
                return;
            createTables () ;   //  SQL Statements are written just to the log, unfortunately
        } catch (SQLException e) {
            throw new ServletException (e.toString());
        } finally {
            db.freeConnection(con);
        }
    }

    void destroy () {
    }

    //	return true if tables aren't there
    boolean TablesNeedCreating () {
        return false;
    }

    //	Create the tables and return the SQL used if Ok, throw otherwise
    String createTables () throws SQLException {
        throw new SQLException ("No DatabaseExtension class provided. Use servlet parameter 'databaseextension= '   to disable. ");
    }

    //	Return SQL if Ok, or message if not Ok. Do not throw SQLException.
    String removeTables () {
        return "No extensible class provided";
    }

    //	This text describes an additional button on Save page; mandatory
    String getSavePageMessage () {
        return "Save in Normalized Database";
    }

    //	Save one entire survey response.
    //	Return null if Ok, or error message otherwise.
    String saveOneResponse ( String spotname, String pollname, String authenticateduserid, Vector items ) throws SQLException {
        return "No extensible class provided";
    }

    String saveOneResponse ( String spotname, String pollname, String authenticateduserid, Vector items, ThisCall thiscall) throws SQLException {
        return saveOneResponse ( spotname, pollname, authenticateduserid, items) ;
    }

    //	return null if Ok, msg otherwise
    abstract String getOneResponse ( PreparedStatement pstmt, String voteid, Vector fields, Vector build, boolean bUsePre, String spotname, String pollname );
/*    { return "getOneReponse Not implemented for this database";} */

    PreparedStatement getOneResponsePreparedStatement (Connection con,  String spotname, String pollname )  {
        //	select answer from vwfdata where VOTE_ORDINAL = NNN;
        try {
            String oneResponse = "SELECT QUESTIONNAME, ANSWER FROM " + db.getTableName ( "VWFDATA" )
                    + " WHERE SPOTNAME='" + spotname
                    + "' AND POLLNAME='" + ObjectStorage.storedpollid ( pollname ) + "'"
                    + "  AND VOTE_ORDINAL = ?";
            return con.prepareStatement ( oneResponse );
        } catch ( SQLException ignored) {
            return null;
        }
    }
    //	Remove all data associated with a given pollid. Called when questionnaire  removed
    //		or when [x] Remove Data is checked on Publish page.
    //	return null if Ok, msg otherwise
    String removeAllResponses ( String spotname, String pollname ) {
        return "No extensible class provided";
    }

    //	Rename and Move a place.  If newspotname is null, don't use it; same with newpollname.
    //	return null if Ok, msg otherwise
    String moveAllResponses ( String spotname, String newspotname, String pollname, String newpollname ) {
        return "Not implemented";
    }

    public String SQLgetvoteidsforDataGathering(dbStore db, String spotname, String pollname, String field, String id) {
        //  Note that all parameters must be checked for possible SQL injection before invoking this method
        String sqlgetvoteids;
        //  This is the query to use, produces the most recent entry for a given user.
        //  SELECT VOTE_ORDINAL from vwfdata where spotname='ccsv' and pollname='one' AND AUTHENTICATEDUSERID='2pQLyr0z9Y' AND ROWNUM <=1 order by TIME_STAMP
        sqlgetvoteids = "SELECT VOTE_ORDINAL, MIN (TIME_STAMP) FROM " + db.getTableName ("VWFDATA") + " WHERE " +
                "SPOTNAME='" + spotname +
                "' AND POLLNAME='" + ObjectStorage.storedpollid (pollname) +
                "' AND " + field + "='" + id + "' GROUP BY VOTE_ORDINAL";
        return sqlgetvoteids;
    }

    public String SQLgetvoteids(dbStore db, String spotname, String pollname, String filterwhereclause ) {
        //  Note that all parameters must be checked for possible SQL injection before invoking this method
        String sqlgetvoteids;
        sqlgetvoteids = "SELECT VOTE_ORDINAL, MIN (TIME_STAMP) FROM " + TableName() + //    db.getTableName ("VWFDATA") +
                " WHERE SPOTNAME='" + spotname +
                "' AND POLLNAME='" + ObjectStorage.storedpollid (pollname) + "'" +
                filterwhereclause +
                " GROUP BY VOTE_ORDINAL";
        if ( Dirs.bLoggingDB() ) {
            Dirs.WriteLog ("7788I","SQLgetvoteids using " + sqlgetvoteids );
        }
        return sqlgetvoteids;
    }

    public String SQLFilternotresponded(dbStore db, definition def, String filtid) {
        String xxx = "AND VOTE_ORDINAL NOT IN "
                + "( SELECT VOTE_ORDINAL FROM "
                + db.getTableName ("VWFDATA")
                + " WHERE SPOTNAME='" + def.spotname
                + "' AND POLLNAME='" + ObjectStorage.storedpollid (def.pollname) + "'"
                + " AND QUESTIONNAME='" + filtid + "' ) ";
        return xxx;
    }

    //  Overload when using CLOBs
    public String getFilterColumnName ( boolean bIsLongText ) {
        return "ANSWER";
    }

    public String  getOneValue ( String sqlquery ) throws SQLException {
        Connection con = null;
        Statement getoneresponse = null;
        ResultSet questions = null;
        try {
            con = db.getConnection();
            getoneresponse = con.createStatement();
            questions = getoneresponse.executeQuery(sqlquery);
            if ( questions.next() ) {
                return questions.getString(1);
            }
            return null;
        }
        finally {
            db.close(questions);
            db.close(getoneresponse);
            db.freeConnection (con);
        }
    }

    public int UpdateOneAnswer(String sqlupdatestmt, String value, boolean bShouldBeClob) throws SQLException {
        throw new SQLException("UpdateOneAnswer method not implemented" );
    }

    public int UpdateOneAnswerWithUnz (String sqlupdatestmt, String value, boolean bShouldBeClob, UpdateNormalized unz ) throws SQLException {
        return UpdateOneAnswer ( sqlupdatestmt, value, bShouldBeClob ); //  default handling doesn't need unz
    }

    void InsertOneAnswer(UpdateNormalized unz, String questionname, String content) throws SQLException {
         throw new SQLException("InsertOneAnswer method not implemented" );
    }

    int RemoveOneAnswer ( String sqlstmt ) throws SQLException {
        Connection con = null;
        Statement removeanswer = null;
        try {
            con = db.getConnection();
            removeanswer = con.createStatement();
            return removeanswer.executeUpdate (sqlstmt);
        }
        finally {
            db.close(removeanswer);
            db.freeConnection (con);
        }
    }

    public boolean bCanUpdateInPlace () {
        return false;
    }

    public String TrimToMaxVWFDATASize(String content) {
        return content;
    }

    boolean bIsTextField ( String pollid, String qname )  {
        definition def;
        try {
            def = Dirs.getDefinitionIfExists (pollid);
        } catch (vwfException e) {
            return false;
        }
        if ( def == null )
            return false;
        qdefinition qdef = def.findqdefinition(qname);
        return qdef != null && qdef.isLongText ();
    }

    public String updateOtherValues(UpdateNormalized updateNormalized, String updatesinglevalue) {
        return updatesinglevalue;       //  710 normal operation is nop
    }

    public String getOtherColumnsClause () {
        return  "";
    }

    public String LongTextAnswer ( String answer, ResultSet rs, Connection con ) {
        return answer;
    }
}