package ca.pencilneck.dbrb;

import java.sql.*;
import java.util.*;
import ca.pencilneck.dbrb.convert.Converter;
import ca.pencilneck.Debug;
import ca.pencilneck.util.ConsoleWrapper;
import ca.pencilneck.util.SimpleConsoleWrapper;
import org.jruby.runtime.builtin.IRubyObject;
import org.jruby.javasupport.JavaEmbedUtils;
import org.jruby.Ruby;
import org.jruby.RubyHash;

public class DBSession implements AutoCloseable
{
    
    protected static boolean defaultDebugMode = false;
    protected static boolean defaultVerboseMode = false;
    protected static ConsoleWrapper defaultConsole;
    
    static {
        defaultConsole = new SimpleConsoleWrapper();
    }

    public static void setDefaultConsole( ConsoleWrapper console ) {
        DBSession.defaultConsole = console;
    }
    
    public static void setDefaultDebugMode( boolean mode ) {
        DBSession.defaultDebugMode = mode;
    }
    
    public static boolean getDefaultDebugMode() {
        return DBSession.defaultDebugMode;
    }

    public static void setDefaultVerboseMode( boolean mode ) {
        DBSession.defaultVerboseMode = mode;
    }
    
    public static boolean getDefaultVerboseMode() {
        return DBSession.defaultVerboseMode;
    }
    
    
    protected HashMap<String, StatementContext> statementPool = null;
    
    public StatementContext getStatement( String sql ) throws SQLException { return this.getStatement( sql, null ); };
    
    public StatementContext getStatement( String sql, Map params ) throws SQLException {
        StatementContext stmt;
        if ( statementPool != null ) {
            stmt = statementPool.get( sql );
            if ( stmt != null ) {
                // Found it in the pool.
                stmt.replaceParameters( params );
                return stmt;
            }
        }
        stmt = new StatementContext( sql, params );
        if ( statementPool != null ) {
            statementPool.put( sql, stmt );
        }
        
        return stmt;
    }
    
    class StatementContext {
        
        protected PreparedStatement statement;
        protected String originalSQL;
        protected String parsedSQL;
        protected ArrayList<String> paramNamesFound;
            
        protected final static int PARSE_NORMAL = 0;
        protected final static int PARSE_SINGLEQ = 1;
        protected final static int PARSE_DOUBLEQ = 2;
        protected final static int PARSE_SLASHCMT = 3;
        protected final static int PARSE_DASHCMT = 4;
        protected final static char PARSE_NO_CHAR = '\uFFFF'; // Unicode 'zigamorph' guaranteed not a character
        
        public StatementContext( String sql ) throws SQLException { this( sql, null ); };
        
        public StatementContext( String sql, Map params ) throws SQLException {
            originalSQL = sql;
    
            if (params == null) {
                params = new HashMap<Object,Object>(1);
            }
    
            // Convert params to a String=>Object map so that the params can be looked up
            // by name embedded in the SQL. It isn't forced to be such at compile-time to support
            // RubyHashes that may have RubySymbols as keys
            Map<String,Object> marshalledparams = new HashMap<String, Object>( params.size() );
            for( Map.Entry e: (Set<Map.Entry>) params.entrySet() ) {
                marshalledparams.put( Converter.autoConvert( e.getKey() ).toString(), Converter.autoConvert( e.getValue() ) );
            }
    
            int parsemode = PARSE_NORMAL;
            char prevc = PARSE_NO_CHAR;
            char currc;
            int length = sql.length();
            StringBuilder parsedstatement = new StringBuilder();
            paramNamesFound = new ArrayList<String>();
            HashMap<Integer,Object> postedparams = new HashMap<Integer, Object>( params.size() );
            int highestparamindex = 0;
    
            for(int i=0;i<length;i++) {
                
                currc = sql.charAt(i);
    
                switch( parsemode ) {
                    case PARSE_SINGLEQ:
                        if ( (currc == '\'') && (prevc != '\\') ) { parsemode = PARSE_NORMAL; };                    
                        break;
                    case PARSE_DOUBLEQ:
                        if ( (currc == '"') && (prevc != '\\') ) { parsemode = PARSE_NORMAL; };
                        break;
                    case PARSE_SLASHCMT:
                        if ( (prevc == '*') && (currc == '/') ) { parsemode = PARSE_NORMAL; };
                        break;
                    case PARSE_DASHCMT:
                        if ( currc == '\n' ) { parsemode = PARSE_NORMAL; };
                        break;
                    default:
                        if ( currc == '\'' ) { parsemode = PARSE_SINGLEQ; break; };
                        if ( currc == '"'  ) { parsemode = PARSE_DOUBLEQ; break; };
                        if ( (prevc == '/') && (currc == '*') ) { parsemode = PARSE_SLASHCMT; break; };
                        if ( (prevc == '-') && (currc == '-') ) { parsemode = PARSE_DASHCMT; break; };
                        
                        if ( currc == ':' ) {
                            if ( Character.isJavaIdentifierPart( sql.charAt(i+1) ) ) {
                                int idstart = i+1;
                                
                                while( Character.isJavaIdentifierPart( sql.charAt(i+1) ) ) {
                                    i++;
                                } 
                                
                                paramNamesFound.add( sql.substring(idstart, i+1) );
                                                            
                                currc = '?';
                            }
                        };
                        
                }
                
                parsedstatement.append(currc);
                if (prevc == '\\') {
                    prevc = PARSE_NO_CHAR;
                } else {
                    prevc = currc;
                }
                           
            }
            
            parsedSQL = parsedstatement.toString();
            statement = dbconn.prepareStatement( parsedSQL );
            
            length = paramNamesFound.size();
            Object p;
            String paramname;
            for(int i=0; i<length; i++) {
                paramname = paramNamesFound.get( i );
                if (! marshalledparams.containsKey( paramname )) {
                    if ( debugMode ) {
                        console.warning( "Warning, no value provided for parameter '" + paramname + "'." );
                    } else {
                        throw new SQLException( "Parameter ':" + paramname + "' has no provided value for SQL statement '" + sql + "'" );
                    }
                }
                p = marshalledparams.get( paramname );
                statement.setObject( i+1, p );
                if ( debugMode || verboseMode ) {
                    postedparams.put(i+1, p);
                    highestparamindex = i+1; // monotonically increasing.
                }
            }
    
            
            if ( debugMode || verboseMode ) {
                console.system( "Prepared SQL statement: " );
                console.normal( parsedSQL );
                console.system( "Parameters as follows: " );
                for (int i=0; i<highestparamindex; i++) {
                    console.normal( "  #" + (i+1) + ": " + Debug.inspect( postedparams.get(i+1) ) );
                }
            }
    
        }

        public void replaceParameters( Map params ) throws SQLException {

            statement.clearParameters();            
            if ( params == null ) { return; }

            HashMap<Integer,Object> postedparams = new HashMap<Integer, Object>( params.size() );
            int highestparamindex = 0;
            
            // Convert params to a String=>Object map so that the params can be looked up
            // by name embedded in the SQL. It isn't forced to be such at compile-time to support
            // RubyHashes that may have RubySymbols as keys
            Map<String,Object> marshalledparams = new HashMap<String, Object>( params.size() );
            for( Map.Entry e: (Set<Map.Entry>) params.entrySet() ) {
                marshalledparams.put( Converter.autoConvert( e.getKey() ).toString(), Converter.autoConvert( e.getValue() ) );
            }

            int length = paramNamesFound.size();
            Object p;
            String paramname;
            for(int i=0; i<length; i++) {
                paramname = paramNamesFound.get( i );
                if (! marshalledparams.containsKey( paramname )) {
                    if ( debugMode ) {
                        console.warning( "Warning, no value provided for parameter '" + paramname + "'." );
                    } else {
                        throw new SQLException( "Parameter ':" + paramname + "' has no provided value for SQL statement '" + originalSQL + "'" );
                    }
                }
                p = marshalledparams.get( paramname );
                statement.setObject( i+1, p );
                if ( debugMode || verboseMode ) {
                    postedparams.put(i+1, p);
                    highestparamindex = i+1; // monotonically increasing.
                }
            }
            
            if ( debugMode || verboseMode ) {
                console.system( "Parameters changed as follows: " );
                for (int i=0; i<highestparamindex; i++) {
                    console.normal( "  #" + (i+1) + ": " + Debug.inspect( postedparams.get(i+1) ) );
                }
            }            
        }
        
        public int exec( ) throws SQLException {
            return statement.executeUpdate( );
        }
    
        public ResultSet query( ) throws SQLException {
            return statement.executeQuery( );
        }                
    }    
    
    protected java.sql.Connection dbconn;
    protected String url;
    protected boolean debugMode = false;
    protected boolean verboseMode = false;
    protected StatementContext lastContext;
    protected ConsoleWrapper console;
    
    public DBSession( String jdbcurl ) throws SQLException, ClassNotFoundException { this( jdbcurl, DBSession.defaultVerboseMode, DBSession.defaultDebugMode ); };
    public DBSession( String jdbcurl, boolean verbose ) throws SQLException, ClassNotFoundException { this( jdbcurl, verbose, DBSession.defaultDebugMode ); };
    
    public DBSession( String jdbcurl, boolean verbose, boolean debug ) throws SQLException, ClassNotFoundException {
        if (! jdbcurl.startsWith("jdbc:")) {
            jdbcurl = "jdbc:" + jdbcurl;
        }
        
        DynamicDriver.registerForURL( jdbcurl );
        dbconn = DriverManager.getConnection( jdbcurl );
        this.url = jdbcurl;
        this.verboseMode = verbose;
        this.debugMode = debug;
        this.console = DBSession.defaultConsole;
    }
    
    public static void show_param( Object p ) {
        defaultConsole.normal( Debug.inspect(p) );
    }

    public static RubyHash getCurrentResultValues( ResultSet rs, IRubyObject contextsource ) throws SQLException {
        Ruby runtime = contextsource.getRuntime();
        RubyHash rh = new RubyHash( runtime );
        
        ResultSetMetaData rsmd = rs.getMetaData();
        int ccthreshold = rsmd.getColumnCount() + 1;

        for (int i=1; i<ccthreshold; i++) {
            rh.put( JavaEmbedUtils.javaToRuby( runtime,rsmd.getColumnLabel(i) ), JavaEmbedUtils.javaToRuby( runtime, rs.getObject(i) ) );
        }
        
        return rh;
    }
    
    public void enableStatementPool() {
        if ( statementPool == null ) { statementPool = new HashMap<String, StatementContext>(); }
    }

    public void disableStatementPool() {
        statementPool = null;
    }
        
    public String getURL() {
        return url;
    }
    
    public void setConsole( ConsoleWrapper cw ) {
        this.console = cw;
    }

    public ConsoleWrapper getConsole( ) {
        return this.console;
    }
    
    public void close() throws SQLException {
        dbconn.close();
    }
    
    
    public int exec( String sql ) throws SQLException { return this.exec(sql, null); }

    public int exec( String sql, Map params ) throws SQLException {
        lastContext = getStatement( sql, params );
        if ( debugMode ) { return 0; }
        return lastContext.exec( );
    }

    public int exec_again( ) throws SQLException { return this.exec_again( null ); }

    public int exec_again( Map params ) throws SQLException {
        if (lastContext == null) { throw new SQLException( "Cannot exec_again with no previous SQL statement." ); }
        lastContext.replaceParameters( params );
        return lastContext.exec();
    }
    
    
    public ResultSet query( String sql ) throws SQLException { return this.query(sql, null); }
    
    public ResultSet query( String sql, Map params ) throws SQLException {
        lastContext = getStatement( sql, params );
        if ( debugMode ) { return null; }
        return lastContext.query( );
    }
    
    public ResultSet query_again( ) throws SQLException { return this.query_again( null ); }

    public ResultSet query_again( Map params ) throws SQLException {
        if (lastContext == null) { throw new SQLException( "Cannot query_again with no previous SQL statement." ); }
        lastContext.replaceParameters( params );
        return lastContext.query();
    }
    

    public Object query_scalar( String sql ) throws SQLException { return this.query(sql, null); }
    
    public Object query_scalar( String sql, Map params ) throws SQLException {
        ResultSet rs;
        
        lastContext = getStatement( sql, params );
        if ( debugMode ) { console.warning("Query not run in debug mode."); return null; }
        
        rs = lastContext.query( );
        return extract_scalar( rs );
        
    }
    
    public Object query_scalar_again( ) throws SQLException { return this.query_again( null ); }

    public Object query_scalar_again( Map params ) throws SQLException {
        ResultSet rs;
        
        if (lastContext == null) { throw new SQLException( "Cannot query_scalar_again with no previous SQL statement." ); }
        lastContext.replaceParameters( params );
        
        if ( debugMode ) { console.warning("Query not run in debug mode."); return null; }

        rs = lastContext.query( );
        return extract_scalar( rs );
    }
  
    protected Object extract_scalar( ResultSet rs ) throws SQLException {
        Object scalar;
        
        if (! rs.next() ) {
            rs.close();
            return null;
        }
        
        scalar = rs.getObject( 1 );
        rs.close();
        return scalar;
    }
  
      
    public void show_query( String sql ) throws SQLException { this.show_query(sql, null); }
    
    public void show_query( String sql, Map params ) throws SQLException {
        ResultSet rs;
        
        lastContext = getStatement( sql, params );
        if ( debugMode ) { console.warning("Query not run in debug mode."); return; }
        
        rs = lastContext.query( );
        
        show_results( rs );
    }

    public void show_query_again( ) throws SQLException { this.show_query_again(null); }
    
    public void show_query_again( Map params ) throws SQLException {
        ResultSet rs;
        
        if (lastContext == null) { throw new SQLException( "Cannot show_query_again with no previous SQL statement." ); }
        lastContext.replaceParameters( params );
        
        if ( debugMode ) { console.warning("Query not run in debug mode."); return; }
        
        rs = lastContext.query( );
        
        show_results( rs );
    }

    
    protected void show_results( ResultSet rs )  throws SQLException {
        ResultSetMetaData rsmd;
        StringBuilder sb = new StringBuilder();
        int printedrows = 0;
        boolean useseparator = false;
        Object v;

        rsmd = rs.getMetaData();
        int ccthreshold = rsmd.getColumnCount() + 1;

        console.system("↓↓ ↓↓ ↓↓ ↓↓ ↓↓ ↓↓ ↓↓ ↓↓ ↓↓ --");
        
        while (rs.next()) {
            if (useseparator) { console.system("-- -- -- -- -- -- -- -- -- -- --"); };
            for (int i=1; i<ccthreshold; i++) {
                sb.setLength(0);
                sb.append( rsmd.getColumnLabel(i) );
                sb.append( " = " );
                v = rs.getObject(i);
                if (v == null ) {
                    sb.append( "[null]" );                    
                } else {
                    sb.append( "'" );
                    sb.append( v.toString() );
                    sb.append( "'" );
                }
                console.normal( sb.toString() );
            }
            printedrows++;
            useseparator = true;
        }
        
        rs.close();

        if (printedrows < 1) {
            console.system("Query returned no rows.");            
        }        

        console.system("↑↑ ↑↑ ↑↑ ↑↑ ↑↑ ↑↑ ↑↑ ↑↑ ↑↑ --");
    }
    
    protected void finalize() throws Throwable {
        this.close();
    }    
    
}