/*
 * Copyright 2015, Yahoo Inc.
 * Copyrights licensed under the Apache License.
 * See the accompanying LICENSE file for terms.
 */
package com.yahoo.dba.perf.myperf.common;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/** 
 * JDBC utility functions
 * @author xrao
 *
 */
public class DBUtils {
  private static Logger logger = Logger.getLogger(DBUtils.class.getName());
  //private static final String INSTANCE_INFO_SQL="select INST_NUMBER, INST_NAME from v$active_instances";

  /**
   * Check if the given jdbc connection is still good
   * @param conn
   * @return
   */
  public static boolean checkConnection(Connection conn)
  {
    //String sql = "select sysdate from dual";
    String sql = "select now()";
    Statement stmt = null;
	ResultSet rs = null;
	try
	{
	  stmt = conn.createStatement();
	  stmt.setQueryTimeout(180);
	  rs = stmt.executeQuery(sql);
	  if(rs!=null && rs.next())
		return true;
	  return false;
	}catch(Exception ex)
	{			
	}finally
	{
	  close(rs);
	  close(stmt);
	}
	return false;
  }

  public static void close(Statement stmt)
  {
    if(stmt!=null)try{stmt.close();}catch(Exception ex){}
  }
  
  public static void close(ResultSet rs)
  {
    if(rs!=null)try{rs.close();}catch(Exception ex){}
  }

  public static void close(Connection conn)
  {
    if(conn!=null)try{conn.close();}catch(Exception ex){}
  }
	
  /**
   * Test is we can connect to db with given user name and password.
   * If testRWPermission is true, we will test if we can create/drop/insert/delete 
   * a test table (for metrics db).
   * @param dbinfo
   * @param username
   * @param password
   * @param testRWPermission
   * @return
   */
  public static String testConnection(DBInstanceInfo dbinfo, 
			String username, 
			String password,
			boolean testRWPermission)
  {
    String url = dbinfo.getConnectionString();
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	try
	{
	  logger.info("Test connection to ("+dbinfo+"): "+url);
	  DriverManager.setLoginTimeout(60);
	  conn = DriverManager.getConnection(url, username, password);
		//if(!dbinfo.isVirtualHost())//only update for non virtual host
		//  dbinfo.setInstance(getConnectionInstanceNumber(conn));
	  if(conn!=null)
	  {
	    dbinfo.setConnectionVerified(true);
		logger.info("Connection test succeeded to ("+dbinfo+")");
		if(testRWPermission)
		{
			stmt = conn.createStatement();
			//create table
			stmt.execute("create table mtr_test (id int auto_increment, v varchar(30), primary key(id))");
			stmt.execute("insert into mtr_test (v) values('123')");
			rs = stmt.executeQuery("select v from mtr_test");
			String testData = null;
			if(rs != null && rs.next())
			  testData = rs.getString(1);
			if(!"123".equals(testData))
				return "Failed insert/select test. Expect 123, got "+testData+".";
			rs.close(); rs = null;
			stmt.execute("delete from mtr_test where v='123'");
			stmt.execute("drop table mtr_test");
		}
	    return null;
      }
	  else
      {
		  logger.log(Level.SEVERE,"Connection test failed: reason null.");
	      return "Connection test failed: reason null.";    	  
      }
	  
	}catch(Exception ex)
	{
	  logger.log(Level.SEVERE,"Exception", ex);
      return "Connection test to "+ url+" failed: "+ex.getMessage();
	}finally
	{
		DBUtils.close(rs);
		DBUtils.close(stmt);
		DBUtils.close(conn);
	}
  }

  public static String testConnection(DBInstanceInfo dbinfo, 
			String username, 
			String password)
{
  String url = dbinfo.getConnectionString();
	Connection conn = null;
	Statement stmt = null;
	ResultSet rs = null;
	try
	{
	  logger.info("Test connection to ("+dbinfo+"): "+url);
	  DriverManager.setLoginTimeout(60);
	  conn = DriverManager.getConnection(url, username, password);
	  if(conn!=null)
	  {
	    dbinfo.setConnectionVerified(true);
		logger.info("Connection test succeeded to ("+dbinfo+")");
	    return null;
    }
	else
    {
		logger.log(Level.SEVERE,"Connection test failed: reason null.");
	    return "Connection test failed: reason null.";    	  
    }
	  
	}catch(Exception ex)
	{
	    logger.log(Level.SEVERE,"Exception", ex);
        return "Connection test to "+ url+" failed: "+ex.getMessage();
	}finally
	{
		DBUtils.close(rs);
		DBUtils.close(stmt);
		DBUtils.close(conn);
	}
  }

  public static boolean hasTable(Connection conn, String schemaName, String tableName)
  {
    ResultSet rs = null;
	try
	{
	  rs = conn.getMetaData().getTables(null, schemaName, tableName.toUpperCase(), null);
	  while(rs.next())
	  {
	    if(tableName.equalsIgnoreCase(rs.getString("TABLE_NAME")))return true;
	  }
	}catch(Exception ex)
	{			
	}
	finally
	{
	  if(rs!=null)try{rs.close();rs = null;}catch(Exception iex){}
	}
	return false;
  }
  
  public static DBCredential findDBCredential(MyPerfContext ctx, String dbid, AppUser appUser)
  {
    DBCredential cred = null;
	if(appUser==null)return null;
	try
	{		
	  //first, check if the user has his own credential
	  cred = ctx.getMetaDb().retrieveDBCredential(appUser.getName(), dbid);
	  if(cred!=null && !appUser.getName().equalsIgnoreCase(cred.getAppUser()))
	  {
	    logger.info(appUser.getName()+": get cred for "+cred.getUsername());
	  }
	}catch(Exception ex)
	{
	    logger.log(Level.WARNING, appUser.getName()+": failed to get cred for "+cred.getUsername());
	}
	return cred;
  }

}