package wrappers;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.Collections;
import java.util.Hashtable;
import java.util.List;
import java.util.UUID;

import javax.sql.DataSource;

public class JdbcInterface {
	// This is used by DS classes
    static Hashtable<String,DataSource> dst = null;
    
	boolean           DEBUG = false;
	boolean           CatisSchema = false;
	String            Errmsg = "No error";
	Connection        conn = null;
	DatabaseMetaData  dbmd = null;
	Statement         stmt = null;
	PreparedStatement pstmt = null;
    ResultSet         rs = null;
    ResultSetMetaData rsmd = null;
    
    // === Constructors/finalize  =========================================
    public JdbcInterface() {
    	this(false);
    } // end of default constructor

    public JdbcInterface(boolean b) {
    	DEBUG = b;
    } // end of constructor
    
    protected void SetErrmsg(Exception e) {
        if (DEBUG)
    		System.out.println(e.getMessage());
      	
        Errmsg = e.toString();
    } // end of SetErrmsg
      
    public String GetErrmsg() {
      String err = Errmsg;
      
      Errmsg = "No error";
      return err;
    } // end of GetErrmsg
    
    protected void CheckURL(String url, String vendor) throws Exception {
      if (url == null)
    	throw new Exception("URL cannot be null");
      
      String[] tk = url.split(":", 3);
      
      if (!tk[0].equals("jdbc") || tk[1] == null)
    	throw new Exception("Invalid URL");
      
      if (vendor != null && !tk[1].equals(vendor))
    	throw new Exception("Wrong URL for this wrapper");
    	  
      // Some drivers use Catalog as Schema
      CatisSchema = tk[1].equals("mysql") || tk[1].equals("mariadb");
    } // end of CatalogIsSchema

    public int JdbcConnect(String[] parms, int fsize, boolean scrollable) {
      int rc = 0;
      
      if (DEBUG)
      	System.out.println("In JdbcInterface: driver=" + parms[0]);
      
      try {
		if (DEBUG)
		  System.out.println("In try block");
			      
		if (parms[0] != null && !parms[0].isEmpty()) {
		  if (DEBUG)
			System.out.println("Loading class" + parms[0]);
		  
  		  Class.forName(parms[0]); //loads the driver
		} // endif driver
			
	    if (DEBUG)
		  System.out.println("URL=" + parms[1]);
	    
	    CheckURL(parms[1], null);
	    
	    // This is required for drivers using context class loaders
	    Thread.currentThread().setContextClassLoader(getClass().getClassLoader());
	      
    	if (parms[2] != null && !parms[2].isEmpty()) {
  	      if (DEBUG)
  	      	System.out.println("user=" + parms[2] + " pwd=" + parms[3]);
  	      
    	  conn = DriverManager.getConnection(parms[1], parms[2], parms[3]);
    	} else
    	  conn = DriverManager.getConnection(parms[1]);

	    if (DEBUG)
		  System.out.println("Connection " + conn.toString() + " established");
	    
	    // Get the data base meta data object
	    dbmd = conn.getMetaData();
	    
	    // Get a statement from the connection
	    stmt = GetStmt(fsize, scrollable);
	  } catch(ClassNotFoundException e) {
		SetErrmsg(e);
	    rc = -1; 
	  } catch (SQLException se) {
		SetErrmsg(se);
	    rc = -2; 
	  } catch( Exception e ) {
		SetErrmsg(e);
	    rc = -3; 
	  } // end try/catch
      
      return rc;
    } // end of JdbcConnect
    
    protected Statement GetStmt(int fsize, boolean scrollable) throws SQLException, Exception {
    	Statement stmt = null;
    	
    	if (scrollable)
    		stmt = conn.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE, java.sql.ResultSet.CONCUR_READ_ONLY);
    	else
    		stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, java.sql.ResultSet.CONCUR_READ_ONLY);

    	if (DEBUG)
    		System.out.println("Statement type = " + stmt.getResultSetType()
   							   + " concurrency = " + stmt.getResultSetConcurrency());
    			  
   	    if (DEBUG)   // Get the fetch size of a statement
   	    	System.out.println("Default fetch size = " + stmt.getFetchSize());

   	    if (fsize != 0) {
   	    	// Set the fetch size
   		    stmt.setFetchSize(fsize);
    		      
   			if (DEBUG)
   				System.out.println("New fetch size = " + stmt.getFetchSize());
    				      
   	    } // endif fsize

        return stmt;
    } // end of GetStmt
    
    
    public int CreatePrepStmt(String sql) {
    	int rc = 0;
    	
    	try {
    		pstmt = conn.prepareStatement(sql);
    	} catch (SQLException se) {
    		SetErrmsg(se);
    	    rc = -1; 
    	} catch (Exception e) {
    		SetErrmsg(e);
    	    rc = -2; 
    	} // end try/catch
    	
    	return rc;
    } // end of CreatePrepStmt
    
    public void SetStringParm(int i, String s) {
    	try {
    		pstmt.setString(i, s);
    	} catch (Exception e) {
    		SetErrmsg(e);
    	} // end try/catch
    	
    } // end of SetStringParm
    
    public void SetIntParm(int i, int n) {
    	try {
    		pstmt.setInt(i, n);
    	} catch (Exception e) {
    		SetErrmsg(e);
    	} // end try/catch
    	
    } // end of SetIntParm
    
    public void SetShortParm(int i, short n) {
    	try {
    		pstmt.setShort(i, n);
    	} catch (Exception e) {
    		SetErrmsg(e);
    	} // end try/catch
    	
    } // end of SetShortParm
    
    public void SetBigintParm(int i, long n) {
    	try {
    		pstmt.setLong(i, n);
    	} catch (Exception e) {
    		SetErrmsg(e);
    	} // end try/catch
    	
    } // end of SetBigintParm
    
    public void SetFloatParm(int i, float f) {
    	try {
    		pstmt.setFloat(i, f);
    	} catch (Exception e) {
    		SetErrmsg(e);
   	} // end try/catch
    	
    } // end of SetFloatParm
    
    public void SetDoubleParm(int i, double d) {
    	try {
    		pstmt.setDouble(i, d);
    	} catch (Exception e) {
    		SetErrmsg(e);
   	} // end try/catch
    	
    } // end of SetDoubleParm
    
    public void SetTimestampParm(int i, Timestamp t) {
    	try {
    		pstmt.setTimestamp(i, t);
    	} catch (Exception e) {
    		SetErrmsg(e);
    	} // end try/catch
    	
    } // end of SetTimestampParm
    
	public void SetUuidParm(int i, String s) {
		try {
			UUID uuid;

			if (s == null)
				uuid = null;
			else if (s.isEmpty())
				uuid = UUID.randomUUID();
			else
				uuid = UUID.fromString(s);

			pstmt.setObject(i, uuid);
		} catch (Exception e) {
			SetErrmsg(e);
		} // end try/catch

	} // end of SetUuidParm

    public int SetNullParm(int i, int typ) {
    	int rc = 0;
    	
    	try {
    		pstmt.setNull(i, typ);
    	} catch (Exception e) {
    		SetErrmsg(e);
    		rc = -1;
    	} // end try/catch
    	
    	return rc;
    } // end of SetNullParm
    
    public int ExecutePrep() {
        int n = -3;
        
        if (pstmt != null) try {
      	  n = pstmt.executeUpdate();
        } catch (SQLException se) {
    	  SetErrmsg(se);
  		  n = -1;
        } catch (Exception e) {
    	  SetErrmsg(e);
    	  n = -2;
        } //end try/catch

        return n;
    } // end of ExecutePrep
    
    public boolean ClosePrepStmt() {
    	boolean b = false;
    	
		if (pstmt != null) try {
    		pstmt.close();
    		pstmt = null;
    	} catch (SQLException se) {
    		SetErrmsg(se);
    	    b = true; 
    	} catch (Exception e) {
    		SetErrmsg(e);
    	    b = true; 
    	} // end try/catch
    	
    	return b;
    } // end of ClosePrepStmt 

    public int JdbcDisconnect() {
      int rc = 0;
      
      // Cancel pending statement
	  if (stmt != null)
		try {
		  if (DEBUG)
		    System.out.println("Cancelling statement");
		  
		  stmt.cancel();
		} catch(SQLException se) {
		  SetErrmsg(se);
		  rc += 1;
	    } // nothing more we can do
		      
	  // Close the statement and the connection
	  if (rs != null)
		try {
		  if (DEBUG)
			System.out.println("Closing result set");
		  
		  rs.close();
		} catch(SQLException se) {
		  SetErrmsg(se);
		  rc = 2;
	    } // nothing more we can do
		      
	  if (stmt != null)
		try {
		  if (DEBUG)
		    System.out.println("Closing statement");
		  
		  stmt.close();
		} catch(SQLException se) {
		  SetErrmsg(se);
		  rc += 4;
	    } // nothing more we can do
	  
	  ClosePrepStmt();
		      
      if (conn != null)
		try {
		  if (DEBUG)
		    System.out.println("Closing connection");
		  
		  conn.close();
	    } catch (SQLException se) {
		  SetErrmsg(se);
		  rc += 8;
	    } //end try/catch
	
      if (DEBUG)
    	System.out.println("All closed");
      
      return rc;
    } // end of JdbcDisconnect
    
    public int GetMaxValue(int n) {
      int m = 0;
      
      try {
        switch (n) {
        case 1:        // Max columns in table
    	  m = dbmd.getMaxColumnsInTable();
    	  break;
        case 2:        // Max catalog name length
    	  m = dbmd.getMaxCatalogNameLength();
    	  break;
        case 3:        // Max schema name length
    	  m = dbmd.getMaxSchemaNameLength();
    	  break;
        case 4:        // Max table name length
    	  m = dbmd.getMaxTableNameLength();
    	  break;
        case 5:        // Max column name length
    	  m = dbmd.getMaxColumnNameLength();
    	  break;
        } // endswitch n
      
      } catch(Exception e) {
  		SetErrmsg(e);
  		m = -1;
      } // end try/catch
      
      return m;
    } // end of GetMaxValue
    
    public String GetQuoteString() {
      String qs = null;
      
      try {
        qs = dbmd.getIdentifierQuoteString();
      } catch(SQLException se) {
    	SetErrmsg(se);  
      } // end try/catch
      
      return qs;
    } // end of GetQuoteString
    
    public int GetColumns(String[] parms) {
      int ncol = -1;
      
      try {
  		if (rs != null) rs.close();
  		
  		if (CatisSchema)
  	      rs = dbmd.getColumns(parms[1], null, parms[2], parms[3]);
  		else	
    	  rs = dbmd.getColumns(parms[0], parms[1], parms[2], parms[3]);
    	
		if (rs != null) {
		  rsmd = rs.getMetaData();
    	  ncol = rsmd.getColumnCount();
		} // endif rs
		
      } catch(SQLException se) {
  		SetErrmsg(se);
      } // end try/catch
      
      return ncol;
    } // end of GetColumns
    
    public int GetTables(String[] parms) {
        int ncol = -1;
        String[] typ = null;
        
        if (parms[3] != null) {
          typ = new String[1];
          typ[0] = parms[3];
        } // endif parms
        
        try {
    	  if (rs != null) rs.close();
    	  
    	  if (CatisSchema)
            rs = dbmd.getTables(parms[1], null, parms[2], typ);
    	  else
      	    rs = dbmd.getTables(parms[0], parms[1], parms[2], typ);
      	
  		  if (rs != null) {
  		    rsmd = rs.getMetaData();
      	    ncol = rsmd.getColumnCount();
  		  } // endif rs
  		
        } catch(SQLException se) {
    	  SetErrmsg(se);
        } // end try/catch
        
        return ncol;
      } // end of GetColumns
      
    public int Execute(String query) {
	      int n = 0;
	      
	      if (DEBUG)
			System.out.println("Executing '" + query + "'");
	    	
	      try {
	    	boolean b = stmt.execute(query);
	    	
	    	if (b == false) {
	    	  n = stmt.getUpdateCount();
	    	  if (rs != null) rs.close();
	    	} // endif b
	    	
	    	if (DEBUG)
			  System.out.println("Query '" + query + "' executed: n = " + n);
	    		
	      } catch (SQLException se) {
	  		SetErrmsg(se);
			n = -1;
	      } catch (Exception e) {
	  		SetErrmsg(e);
	      	n = -2;
	      } //end try/catch

	      return n;
	    } // end of Execute
    
    public int GetResult() {
    	int ncol = 0;
    	
    	try {
    		rs = stmt.getResultSet();
    		
    		if (rs != null) {
			  rsmd = rs.getMetaData();
	    	  ncol = rsmd.getColumnCount();
	    	
	    	  if (DEBUG)
			    System.out.println("Result set has " + rsmd.getColumnCount() + " column(s)");
	    	
    		} // endif rs
	    		
	    } catch (SQLException se) {
			SetErrmsg(se);
			ncol = -1;
	      } catch (Exception e) {
	  		SetErrmsg(e);
	    	ncol = -2;
	    } //end try/catch

	    return ncol;
    } // end of GetResult
	    
    public int ExecuteQuery(String query) {
      int ncol = 0;
      
      if (DEBUG)
		System.out.println("Executing query '" + query + "'");
    	
      try {
			if (rs != null)
				rs.close();
    	rs = stmt.executeQuery(query);
		rsmd = rs.getMetaData();
    	ncol = rsmd.getColumnCount();
    	
    	if (DEBUG) {
		  System.out.println("Query '" + query + "' executed successfully");
		  System.out.println("Result set has " + rsmd.getColumnCount() + " column(s)");
    	} // endif DEBUG
    		
      } catch (SQLException se) {
  		SetErrmsg(se);
		ncol = -1;
      } catch (Exception e) {
  		SetErrmsg(e);
  		ncol = -2;
      } //end try/catch

      return ncol;
    } // end of ExecuteQuery
    
    public int ExecuteUpdate(String query) {
        int n = 0;
        
        if (DEBUG)
  		  System.out.println("Executing update query '" + query + "'");
      	
        try {
      	  n = stmt.executeUpdate(query);
      	
      	  if (DEBUG)
  		    System.out.println("Update Query '" + query + "' executed: n = " + n);
      		
        } catch (SQLException se) {
    	  SetErrmsg(se);
  		  n = -1;
        } catch (Exception e) {
    	  SetErrmsg(e);
    	  n = -2;
        } //end try/catch

        return n;
    } // end of ExecuteUpdate
    
    public int ReadNext() {
	  if (rs != null) {
	    try {
	  	  return rs.next() ? 1 : 0;
		} catch (SQLException se) {
		  SetErrmsg(se);
		  return -1;
		} //end try/catch
	    	  
	  } else
	    return 0;
	      
	} // end of ReadNext
	      
    public boolean Fetch(int row) {
	  if (rs != null) {
	    try {
	  	  return rs.absolute(row);
		} catch (SQLException se) {
		  SetErrmsg(se);
		  return false;
		} //end try/catch
	    	  
	  } else
	    return false;
	      
	} // end of Fetch
	      
    public String ColumnName(int n) {
      if (rsmd == null) {
		System.out.println("No result metadata");
      } else try {
    	return rsmd.getColumnLabel(n);
      } catch (SQLException se) {
  		SetErrmsg(se);
      } //end try/catch
    	  
      return null;  
    } // end of ColumnName
    
    public int ColumnType(int n, String name) {
	  if (rsmd == null) {
		System.out.println("No result metadata");
	  } else try {
		if (n == 0)
		  n = rs.findColumn(name);
		
	    return rsmd.getColumnType(n);
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return 666;   // Not a type
	} // end of ColumnType
	    
    public String ColumnDesc(int n, int[] val) {
	  if (rsmd == null) {
		System.out.println("No result metadata");
		return null;
	  } else try {
		val[0] = rsmd.getColumnType(n);
		val[1] = rsmd.getPrecision(n);
		val[2] = rsmd.getScale(n);
		val[3] = rsmd.isNullable(n);
	    return rsmd.getColumnLabel(n);
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return null;  
	} // end of ColumnDesc
	    
    public String StringField(int n, String name) {
	  if (rs == null) {
		System.out.println("No result set");
	  } else try {
	    return (n > 0) ? rs.getString(n) : rs.getString(name);
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return null;  
	} // end of StringField
	    
    public int IntField(int n, String name) {
	  if (rs == null) {
		System.out.println("No result set");
	  } else try {
	    return (n > 0) ? rs.getInt(n) : rs.getInt(name);
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return 0;  
	} // end of IntField
	    
    public long BigintField(int n, String name) {
	  if (rs == null) {
		System.out.println("No result set");
	  } else try {
		BigDecimal bigDecimal = (n > 0) ? rs.getBigDecimal(n) : rs.getBigDecimal(name);
        return bigDecimal != null ? bigDecimal.longValue() : 0;
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return 0;  
	} // end of BiginttField
	    
    public double DoubleField(int n, String name) {
	  if (rs == null) {
		System.out.println("No result set");
	  } else try {
	    return (n > 0) ? rs.getDouble(n) : rs.getDouble(name);
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return 0.;  
	} // end of DoubleField
	    
    public float FloatField(int n, String name) {
	  if (rs == null) {
		System.out.println("No result set");
	  } else try {
	    return (n > 0) ? rs.getFloat(n) : rs.getFloat(name);
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return 0;  
	} // end of FloatField
	    
    public boolean BooleanField(int n, String name) {
	  if (rs == null) {
		System.out.println("No result set");
	  } else try {
	    return (n > 0) ? rs.getBoolean(n) : rs.getBoolean(name);
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return false;  
	} // end of BooleanField
	    
    public int DateField(int n, String name) {
	  if (rs == null) {
		System.out.println("No result set");
	  } else try {
	    Date d = (n > 0) ? rs.getDate(n) : rs.getDate(name);
	    return (d != null) ? (int)(d.getTime() / 1000) : 0;
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return 0;  
	} // end of DateField
	    
    public int TimeField(int n, String name) {
	  if (rs == null) {
		System.out.println("No result set");
	  } else try {
	    Time t = (n > 0) ? rs.getTime(n) : rs.getTime(name);
	    return (t != null) ? (int)(t.getTime() / 1000) : 0;
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return 0;  
	} // end of TimeField
	    
    public int TimestampField(int n, String name) {
	  if (rs == null) {
		System.out.println("No result set");
	  } else try {
	    Timestamp ts = (n > 0) ? rs.getTimestamp(n) : rs.getTimestamp(name);
	    return (ts != null) ? (int)(ts.getTime() / 1000) : 0;
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return 0;  
	} // end of TimestampField
    
  public Object ObjectField(int n, String name) {
	  if (rs == null) {
		System.out.println("No result set");
	  } else try {
	    return (n > 0) ? rs.getObject(n) : rs.getObject(name);
	  } catch (SQLException se) {
		SetErrmsg(se);
	  } //end try/catch
	    	  
	  return null;  
	} // end of ObjectField
	    
	public String UuidField(int n, String name) {
		Object job;

		if (rs == null) {
			System.out.println("No result set");
		} else
			try {
				job = (n > 0) ? rs.getObject(n) : rs.getObject(name);
				return job.toString();
			} catch (SQLException se) {
				SetErrmsg(se);
			} // end try/catch

		return null;
	} // end of UuidField

    public int GetDrivers(String[] s, int mxs) {
    	int n = 0;
    	List<Driver> drivers = Collections.list(DriverManager.getDrivers());
    	int size = Math.min(mxs, drivers.size());
		
    	for (int i = 0; i < size; i++) {
    		Driver driver = (Driver)drivers.get(i);

    		// Get name of driver
    		s[n++] = driver.getClass().getName();
	    
    		// Get version info
    		s[n++] = driver.getMajorVersion() + "." + driver.getMinorVersion();
    		s[n++] = driver.jdbcCompliant() ? "Yes" : "No";
    		s[n++] = driver.toString();
    	} // endfor i
    	
    	return size;
    } // end of GetDrivers
    
    /**
    * Adds the specified path to the java library path
    * from Fahd Shariff blog
    *
    * @param pathToAdd the path to add
        static public int addLibraryPath(String pathToAdd) {
		System.out.println("jpath = " + pathToAdd);

    	try {
    		Field usrPathsField = ClassLoader.class.getDeclaredField("usr_paths");
    		usrPathsField.setAccessible(true);

    		//get array of paths
    		String[] paths = (String[])usrPathsField.get(null);

    		//check if the path to add is already present
    		for (String path : paths) {
    			System.out.println("path = " + path);
    			
    			if (path.equals(pathToAdd))
    				return -5;
    			
    		} // endfor path

    		//add the new path
    		String[] newPaths = Arrays.copyOf(paths, paths.length + 1);
    		newPaths[paths.length] = pathToAdd;
    		usrPathsField.set(null, newPaths);
            System.setProperty("java.library.path",
            		System.getProperty("java.library.path") + File.pathSeparator + pathToAdd);
            Field fieldSysPath = ClassLoader.class.getDeclaredField("sys_paths");
            fieldSysPath.setAccessible(true);
            fieldSysPath.set(null, null);
    	} catch (Exception e) {
			SetErrmsg(e);
    		return -1;
    	} // end try/catch
    	
    	return 0;
    } // end of addLibraryPath
    */   
	    
} // end of class JdbcInterface