package merchantapp_config;

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

import com.jcraft.jsch.JSch;


public class DbConnection {
	String appid = null;
	String otp = null;
	private final static Logger LOGGER = Logger.getLogger(DbConnection.class.getName());
	
	public DbConnection() throws Exception {
		super();
		// TODO Auto-generated constructor stub

	}

	public String pullOtp()
	{
		try {
			 JSch jsch = new JSch(); 
			Class.forName("com.mysql.jdbc.Driver");

			System.out.println("Driver loaded");

//			Connection con = DriverManager.getConnection(
//					"jdbc:mysql://localhost:9090/cfwebbeta", "pratik",
//					"r<)SDv0hvXtzH");
//			Connection con = DriverManager.getConnection(
//					"jdbc:mysql://creditfairbeta-prod-new.ctduiw4dfr8l.ap-south-1.rds.amazonaws.com:3306/cfwebbeta", "pratik",
//					"r<)SDv0hvXtzH");
//			Connection con = DriverManager.getConnection(
//					"jdbc:mysql://localhost:9090/cfwebbeta", "pratik",
//					"r<)SDv0hvXtzH");
			Connection con = DriverManager.getConnection(
					"jdbc:mysql://cfwebbetanew1.cyc1x9kcl7ju.ap-south-1.rds.amazonaws.com:3306/cfwebbeta", "pratik",
					"m77UXldW5zZ_d");
			
			System.out.println("Connected to prod DB");

			Statement smt = con.createStatement();
			ResultSet rsotp = smt.executeQuery(
					"select otp from otp_manager where mobile_number = 7208239249 Order BY id DESC LIMIT 1");
			while (rsotp.next()) {

				otp = rsotp.getString("otp");
			}
			System.out.println(otp);

		}
		catch (Exception e) {
			e.printStackTrace();
		}
//		
		
//		try {
//			JSch jsch = new JSch(); 
//			Session session = jsch.getSession("pratik", "13.200.119.237", 22);
//            session.setPassword("cR(wyJK*tq8Xe9sk");
//      
//            java.util.Properties config = new java.util.Properties();
//            config.put("StrictHostKeyChecking", "no");
//            config.put("Compression", "yes");
//            config.put("ConnectionAttempts","2");
//             
//            session.setConfig(config);
//             
//            session.connect();  
//            System.out.println("Tunnel Connect");
//            StringBuilder url = new StringBuilder("jdbc:mysql://creditfairbeta-prod-new.ctduiw4dfr8l.ap-south-1.rds.amazonaws.com:");
//            url.append(3306).append ("/").append("cfwebbeta").append ("?user=").
//            append("pratik").append ("&password=").
//            append ("r<)SDv0hvXtzH");
//            Class.forName("com.mysql.jdbc.Driver");
//            System.out.println("Driver loaded");
//            System.out.println(url);
//            java.sql.Connection connection =
//                    java.sql.DriverManager.getConnection(url.toString());
//            System.out.println(connection);
// 
//            java.sql.DatabaseMetaData metadata = connection.getMetaData();
//		
//		}
//		
//		catch (Exception e) {
//			e.printStackTrace();
//		}
//		
		
	
	         
	        // 
//	        int assigned_port;   
//	        final int local_port=3309;
	         
	        // Remote host and port
//	        final int remote_port=3306;
//	        final String remote_host="13.200.119.237";
	         
//	        try {
//	            JSch jsch = new JSch(); 
	             
	            // Create SSH session.  Port 22 is your SSH port which
	            // is open in your firewall setup.
//	            Session session = jsch.getSession("pratik", remote_host, 22);
//	            session.setPassword("cR(wyJK*tq8Xe9sk");
//	 
	            // Additional SSH options.  See your ssh_config manual for
	            // more options.  Set options according to your requirements.
//	            java.util.Properties config = new java.util.Properties();
//	            config.put("StrictHostKeyChecking", "no");
//	            config.put("Compression", "yes");
//	            config.put("ConnectionAttempts","2");
	             
//	            session.setConfig(config);
	             
	            // Connect
//	            session.connect();     
//	            System.out.println("Successfully connect tunnel");
	             
	            // Create the tunnel through port forwarding.  
	            // This is basically instructing jsch session to send 
	            // data received from local_port in the local machine to 
	            // remote_port of the remote_host
	            // assigned_port is the port assigned by jsch for use,
	            // it may not always be the same as
	            // local_port.
	             
//	            assigned_port = session.setPortForwardingL(local_port, 
//	                    remote_host, remote_port);
	             
//	        } 
	        
//	        catch (JSchException e) {            
//	            LOGGER.log(Level.SEVERE, e.getMessage()); return remote_host;
//	        }
	         
//	        if (assigned_port == 0) {
//	            LOGGER.log(Level.SEVERE, "Port forwarding failed !"); 
//	            return remote_host;
//	        }
	 
	        // Database access credintials.  Make sure this user has
	        // "connect" access to this database;
	         
	        // these may be initialized somewhere else in your code.
//	        final String database_user="pratik";
//	        final String database_password="r<)SDv0hvXtzH";
//	        final String database = "cfwebbeta";
//	         
//	        // Build the  database connection URL.
//	        StringBuilder url =
//	                new StringBuilder("jdbc:mysql://creditfairbeta-prod-new.ctduiw4dfr8l.ap-south-1.rds.amazonaws.com:");
//	         
//	        // use assigned_port to establish database connection
//	        url.append(9091).append ("/").append(database).append ("?user=").
//	                append(database_user).append ("&password=").
//	                append (database_password);
//	        System.out.println(url);
//	         
//	        try {
//	            Class.forName(
//	                    "com.mysql.jdbc.Driver");
//	            
//	            System.out.println("Driver Loaded");
//	            // On Prod
//	            Connection con = DriverManager.getConnection(
//						"jdbc:mysql://localhost:9091/cfwebbeta", "pratik",
//						"r<)SDv0hvXtzH");
//	            System.out.println("Prod DB Connected");
//	            // On Stage
////	            Connection con = DriverManager.getConnection(
////						"jdbc:mysql://cfwebbetanew1.cyc1x9kcl7ju.ap-south-1.rds.amazonaws.com:3306/cfwebbeta", "pratik",
////						"m77UXldW5zZ_d");
//	            java.sql.Connection connection =
//	                    java.sql.DriverManager.getConnection(url.toString());
//	            System.out.println("Prod DB Connected");
//
////	            java.sql.DatabaseMetaData metadata = con.getMetaData();
//	 
//	        }
//	        catch (Exception e 
//	                
//	           ) {
//				e.printStackTrace();
//			}
		return otp;
		
	}
	
	

}