package se.lil.om;

import java.sql.*;
import java.util.HashSet;
import java.util.Properties;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;
//import java.util.logging.Logger;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class OraMon {
	//private final static Logger LOGGER = Logger.getLogger(OraMon.class.getName());
	Connection conn = null;
	String conString = "jdbc:oracle:thin:@//hostname:1521/SID";
	String conUser = "system";
	String conPass = "passw0rd";
	String dbName = null;
	String serviceName = null;
	
	public static final int SYSSTAT = 1;
	public static final int OSSTAT = 2;
	
	Collector colSysStat = new Collector("colSysStat");
	Collector colOsStat = new Collector("colOsStat");
	
	int getDataCalls = 0;
	int getDataSucess = 0;
	
	boolean getPdbs = false;
	Collector colPdbSysStat = null;
	Collector colPdbCPU = null;
	HashSet<String> pdbSet = null;
	public static final String strCpuTime = " cputime";
	public static final String strNiwTime = " niwtime";
	public static final String strSeparator = " ";
	
	public int getNumPdbs() {
		if(pdbSet != null) return pdbSet.size();
		else return 0;
	}
	
	public String[] getPdbArray() {
		if(pdbSet != null) return (String[]) pdbSet.toArray();
		else return null;
	}
	
	public int getDataCalled() {
		return getDataCalls;
	}
	
	public int getDataSucceeded() {
		return getDataSucess;
	}
	
	public int getDataFailed() {
		return getDataCalls - getDataSucess;
	}
	
	public String getConString() {
		return this.conString;
	}
	
	public String getServiceName() {
		if(this.serviceName != null) return this.serviceName;
		if(this.conString != null) {
			if(this.conString.contains("SERVICE_NAME=")) {
				//TNS format
				Pattern pat = Pattern.compile("SERVICE_NAME=(\\w+)");
				Matcher m = pat.matcher(this.conString);
				if(m.find()) {
					this.serviceName = m.group(1);
				} else {
					this.serviceName = null;
				}
			} else {
				int last = this.conString.lastIndexOf('/');
				if(last >=0)
					this.serviceName = this.conString.substring(last +1);
				else {
					last = this.conString.lastIndexOf(':');
					this.serviceName = this.conString.substring(last +1);
				}
			}
		} else {
			this.serviceName = null;
		}
		return this.serviceName;
	}
	
	//NUM_CPUS
	int numCpus = 0;
	public long getNumberOfCPUs() throws Throwable {
		return numCpus;
	}
	
	//DB CPU
	LongDelta cpuTime = new LongDelta(this.getFriendlyName()+" cpuTime");
	LongDelta niwTime = new LongDelta(this.getFriendlyName()+" niwTime");
	public double getCPUPercent(boolean excludeNonIdleWaitTime, String pdbName) throws Throwable {
		double cpuPerSec;
		if(pdbName == null) cpuPerSec = cpuTime.getPerSecondValue(); 
		else cpuPerSec = colPdbCPU.getPerSecValue(pdbName + strCpuTime);
		
		double niwPerSec;
		if(pdbName == null) niwPerSec = niwTime.getPerSecondValue();
		else niwPerSec = colPdbCPU.getPerSecValue(pdbName + strNiwTime);
		
		if(excludeNonIdleWaitTime) {
			double newPerSec = cpuPerSec - (niwPerSec * 1000);
			if(newPerSec > 0) cpuPerSec = newPerSec; else cpuPerSec = 0;
		}
		double totSec = numCpus * 1000 * 1000;
		double percent = (cpuPerSec / totSec) * 100;
		if(percent > 100D) percent=100D;
		if(percent < 0D) percent=0D;
		return percent;
	}
	public double getCPUPercent(boolean excludeNonIdleWaitTime) throws Throwable {
		return getCPUPercent(excludeNonIdleWaitTime, null);
	}
	public double getCPUPercent() throws Throwable {
		return getCPUPercent(false);
	}
	public double getCPUPercent(String pdbName) throws Throwable {
		return getCPUPercent(false, pdbName);
	}
	
	public double getCPUAvailableSeconds() throws Throwable {
		return cpuTime.getSeconds()*numCpus;
	}
	public double getCPUAvailableSeconds(String pdbName) throws Throwable {
		if(pdbName != null)
			return colPdbCPU.getSeconds(pdbName + strCpuTime)*numCpus;
		else 
			return getCPUAvailableSeconds();
	}
	
	public long getCPURawValue() throws Throwable {
		return cpuTime.getCurrentValue();
	}
	public long getCPURawValue(String pdbName) throws Throwable {
		if(pdbName != null)
			return colPdbCPU.getCurrentValue(pdbName + strCpuTime);
		else
			return getCPURawValue();
	}
	
//	public long getCPURawDiff() throws Throwable {
//		return cpuTime.getDelta();
//	}
//	public long getCPURawDiff(String pdbName) throws Throwable {
//		if(pdbName != null)
//			return colPdbCPU.getDelta(pdbName + strCpuTime);
//		else
//			return getCPURawDiff();
//	}
	
	public double getCPUTimePerSecond() throws Throwable {
		return cpuTime.getPerSecondValue();
	}
	public double getCPUTimePerSecond(String pdbName) throws Throwable {
		if(pdbName != null)
			return colPdbCPU.getPerSecValue(pdbName + strCpuTime);
		else
			return getCPUTimePerSecond();
	}
	
	public double getPerSecondValue(String name) throws Throwable {
		return getPerSecondValue(name, SYSSTAT, null);
	}
	
	public double getPerSecondValue(String name, int table) throws Throwable {
		return getPerSecondValue(name, table, null);
	}
	
	public double getPerSecondValue(String name, String pdbName) throws Throwable {
		return getPerSecondValue(name, SYSSTAT, pdbName);
	}
	
	public double getPerSecondValue(String name, int table, String pdbName) throws Throwable {
		switch (table) {
		case OSSTAT:
			return colOsStat.getPerSecValue(name);
		case SYSSTAT:
			if(pdbName == null)
				return colSysStat.getPerSecValue(name);
			else
				return colPdbSysStat.getPerSecValue(pdbName + strSeparator + name);
		default:
			return colSysStat.getPerSecValue(name);
		}
	}
	
	public String getDBName() {
		// Returns the instance name unless we are a PDB, in that case the service name from the con string is returned instead.
		return this.dbName;
	}
	
	public String getFriendlyName() {
		return getFriendlyName(null);
	}
	
	public String getFriendlyName(String pdbName) {
		// Returns a friendly name of the database, either the service name from con string or the database name from the instance
		getServiceName();
		if(pdbName != null) {
			if(this.serviceName != null) return this.serviceName + ":" + pdbName;
			else return this.dbName + ":" + pdbName;
		} else {
			if(this.serviceName != null) return this.serviceName;
			else return this.dbName;
		}
	}
	
	public double getOsBusyPercent() throws Throwable {
		double idle = getPerSecondValue("IDLE_TIME", OSSTAT);
		double busy = getPerSecondValue("BUSY_TIME", OSSTAT);
		if(idle != 0 && busy != 0)
			return 100*(busy/(busy+idle));
		else {
			if(busy == 0)
				return 0;
			else
				return 100;
		}
	}
	
	// OS LOAD
	DoubleDelta osLoad = new DoubleDelta();
	
	public double getOsLoad() throws Throwable {
		return osLoad.getCurrentValue();
	}
	
	public double getOsLoadPerCPU() throws Throwable {
		double num = this.numCpus;
		double load = getOsLoad();
		return load / num;
	}
	
	public double getBufferCacheHitRatioPercent() throws Throwable {
		return getBufferCacheHitRatioPercent(null);
	}
	public double getBufferCacheHitRatioPercent(String pdbName) throws Throwable {
		double conGetsCache;
		double dbBlocksCache;
		double physReadsCache;
		
		if(pdbName == null) {
			conGetsCache = getPerSecondValue("consistent gets from cache");
			dbBlocksCache = getPerSecondValue("db block gets from cache");
			physReadsCache = getPerSecondValue("physical reads cache");
		} else {
			conGetsCache = colPdbSysStat.getPerSecValue(pdbName + strSeparator + "consistent gets from cache");
			dbBlocksCache = colPdbSysStat.getPerSecValue(pdbName + strSeparator + "db block gets from cache");
			physReadsCache = colPdbSysStat.getPerSecValue(pdbName + strSeparator + "physical reads cache");
		}
		
		if (conGetsCache + dbBlocksCache + physReadsCache == 0) {
			return 100;
		}
		if (conGetsCache + dbBlocksCache == 0 && physReadsCache > 0) {
			return 0;
		}
		double buffCacheHitRatio = 1 - (physReadsCache/(conGetsCache + dbBlocksCache));
		if(buffCacheHitRatio > 0)
			return buffCacheHitRatio * 100;
		else
			return 0;
		
	}
	
	public double getCacheHitRatioPercent() throws Throwable {
		return getCacheHitRatioPercent(null);
	}
	public double getCacheHitRatioPercent(String pdbName) throws Throwable {
		double conGets;
		double dbBlocks;
		double physReads;
		
		if(pdbName == null) {
			conGets = getPerSecondValue("consistent gets");
			dbBlocks = getPerSecondValue("db block gets");
			physReads = getPerSecondValue("physical reads");
		} else {
			conGets = colPdbSysStat.getPerSecValue(pdbName + strSeparator + "consistent gets");
			dbBlocks = colPdbSysStat.getPerSecValue(pdbName + strSeparator + "db block gets");
			physReads = colPdbSysStat.getPerSecValue(pdbName + strSeparator + "physical reads");
		}
		
		if (conGets + dbBlocks + physReads == 0) {
			return 100;
		}
		if (conGets + dbBlocks == 0 && physReads > 0) {
			return 0;
		}
		double cacheHitRatio = 1 - (physReads/(conGets + dbBlocks));
		if(cacheHitRatio > 0)
			return cacheHitRatio * 100;
		else
			return 0;
	}
	
	public double getLogicalReadsPerSecond() throws Throwable {
		return getLogicalReadsPerSecond(null);
	}
	public double getLogicalReadsPerSecond(String pdbName) throws Throwable {
		if(pdbName == null)
			return getPerSecondValue("consistent gets") + getPerSecondValue("db block gets");
		else 
			return colPdbSysStat.getPerSecValue(pdbName + strSeparator + "consistent gets") + colPdbSysStat.getPerSecValue(pdbName + strSeparator + "db block gets");
	}
	
	long lastRTns = 0;
	public long getLastRTms() {
		return lastRTns / (1000 * 1000);
	}
	
	long lastFetchTSns = 0;
	
	public long getAgeTs() {
		return (System.nanoTime() - lastFetchTSns)/(1000*1000*1000);
	}
	
	public boolean getData() throws Throwable {
		return getData(0, null);
	}
	
	public boolean getData(long ageTs) throws Throwable {
		return getData(ageTs, null);
	}
	
	Lock lock = new ReentrantLock();
	Throwable lastEx = null;
	
 	public boolean getData(long ageTs, String pdbName) throws Throwable {
 		//Check age (unless pdbName was set for the first time, in that case we need to discard the cached data)
 		if(getPdbs == false && pdbName != null) { 
 			getPdbs = true; 
 		}
 		else {
 			if ((System.nanoTime() - lastFetchTSns) < (ageTs * 1000 * 1000 * 1000)) return false;
 		}

 		// Start thread safe
 		// Try to get a lock
 		boolean haveLock = lock.tryLock();
		if( haveLock == false) {
			// We could not get the lock, someone else is updating. Wait for it to complete by waiting for a lock, then unlock and return.
			try {
				lock.lock();
			} finally {
				lock.unlock();
			}
			return false;
		}
		// We do have the lock. Do the rest in a try catch everything and if we catch anything, re throw the catch but always release the lock.
		Statement stmt = null;
		try {
	 		long startTSns = System.nanoTime();
	 		getDataCalls++;
	 		if(conn == null) open();
	 		if(conn.isClosed()) open();
	 		boolean stale = true;
	 		try {
	 			if (conn.isValid(20)) {
	 				stale = false;
	 			}
	 		} catch (SQLException e) {
	 			stale = true;
	 		}
	 		if(stale) {
	 			open();
	 		}
			stmt = conn.createStatement();
			ResultSet rset = null;
			
			//Get the database name once // Currently always gets the CDB name
			if(this.dbName == null) {
				rset = stmt.executeQuery("select value from V$SYSTEM_PARAMETER where name = 'db_name'");
				rset.next();
				this.dbName = rset.getString(1);
				rset.close(); 
			}
			
			// All other cases (CDB or an instance without containers) get the data from the database
			
			//Get the entire V_$OSSTAT table from DB
			colOsStat.update(stmt.executeQuery("select systimestamp, stat_name, value from V$OSSTAT"));
			
			//Set numCpus
			this.numCpus = (int) colOsStat.getCurrentValue("NUM_CPUS");
			
			// Get OS Load
			osLoad.update(stmt.executeQuery("select systimestamp, value from V$OSSTAT where stat_name='LOAD'"));
			
			// Get DB CPU use time
			rset = stmt.executeQuery("select systimestamp, s.value as NIWT, t.value as DBCPU from V$SYSSTAT s, V$SYS_TIME_MODEL t where s.name='non-idle wait time' and t.STAT_NAME='DB CPU'");
			rset.next();
			niwTime.update(rset.getBigDecimal(2), rset.getTimestamp(1));
			cpuTime.update(rset.getBigDecimal(3), rset.getTimestamp(1));
			rset.close();
	
			//Get the entire V_$SYSSTAT table from DB
			colSysStat.update(stmt.executeQuery("select systimestamp, name, value from V$SYSSTAT"));
			
			//Get PDB specific data if getPDB is set to true
			if(getPdbs) {
				//CPU
				if(colPdbCPU == null) colPdbCPU = new Collector("colPdbCPU");
				rset = stmt.executeQuery("select systimestamp, c.NAME, s.value as NIWT, t.value as DBCPU from V$CON_SYSSTAT s, V$CON_SYS_TIME_MODEL t, V$CONTAINERS c where s.CON_ID=t.CON_ID and s.CON_ID=c.CON_ID and s.name='non-idle wait time' and t.STAT_NAME='DB CPU'");
				while(rset.next()) {
					Timestamp ts = rset.getTimestamp(1);
					String name = rset.getString(2);
					colPdbCPU.updateValue(ts, name + strNiwTime, rset.getBigDecimal(3));
					colPdbCPU.updateValue(ts, name + strCpuTime, rset.getBigDecimal(4));
					if(pdbSet == null) pdbSet = new HashSet<String>();
					pdbSet.add(name);
				}
				rset.close();
				
				//SYSSTATS
				if(colPdbSysStat == null) colPdbSysStat = new Collector("colPdbSysStat");
				colPdbSysStat.update(stmt.executeQuery("select systimestamp, c.NAME || '" + strSeparator + "' || s.name, s.value from V$CON_SYSSTAT s, V$CONTAINERS c where s.CON_ID=c.CON_ID"));
			}
			
			stmt.close();
			
			getDataSucess++;
			lastFetchTSns = System.nanoTime();
			lastRTns = lastFetchTSns - startTSns;
			return true;
		} catch (Throwable e) {
			Throwable lastCause = e.getCause();
			Throwable thisCause = e;
			while(lastCause != null) { thisCause = lastCause; lastCause = thisCause.getCause(); }
			lastEx = thisCause;
			// Close the connection and statement to prevent memory leaks
			try {
				stmt.close();
				conn.close();
			} catch (Exception e2) {
				// Dont care
			}
			throw (e);
		} finally {
			lock.unlock();
		}
		// End thread safe
	}
 	
	public Throwable getLastEx() {
		return lastEx;
	}
	
	public OraMon() {
		// TODO Auto-generated constructor stub
	}
	
	public OraMon(String con, String user, String pass) {
		this.conString = con;
		this.conUser = user;
		this.conPass = pass;
	}
	
	public void open() throws Throwable {
		Class.forName ("oracle.jdbc.OracleDriver");
		Properties jdbcProps = new Properties();
		jdbcProps.put("user", conUser);
		jdbcProps.put("password", conPass);
		jdbcProps.put("v$session.program","LIL Oracle Monitor");
		this.conn = DriverManager.getConnection(conString, jdbcProps);
		
	}
	
	public void open(String con, String user, String pass) throws Throwable {
		Class.forName ("oracle.jdbc.OracleDriver");
		if(con != null) this.conString = con;
		if(user != null) this.conUser = user;
		if(pass != null) this.conPass = pass;
		Properties jdbcProps = new Properties();
		jdbcProps.put("user", conUser);
		jdbcProps.put("password", conPass);
		jdbcProps.put("v$session.program","LIL Oracle Monitor");
		this.conn = DriverManager.getConnection(conString, jdbcProps);
	}
	
	public void close() throws Throwable {
		if(conn != null) conn.close();
	}
}