
"""
FortiMonitor Monitoring Agent Oracle Database Plugin
Copyright 2023 Fortinet, Inc. All Rights Reserved.
fm-ops@fortinet.com

To Configure:

The following configuration options need to be set under the [oracle] block in the
agent configuration file:

- oracle_home - ORACLE_HOME value that points to the local database installation
- oracle_sid - name of the database instance to be used for connections
- username - username for user that the agent should use when connecting to the database
- password - password for user that the agent should use when connecting to the database
- tns_listener_ip (optional) - IP address that the TNS listenener service is running on

The user that will be used by the agent needs the following permission grants to fully operate:

GRANT CREATE SESSION TO <USER> ;
GRANT SELECT ON dba_data_files TO <USER> ;
GRANT SELECT ON dba_segments TO <USER> ;
GRANT SELECT ON dba_free_space, v$resource_limit to <USER> ;
GRANT SELECT ON v_$resource_limit to <USER> ;
"""

import agent_util
import os
import os.path
import sys
import tempfile
from agent_util import float

import logging

logger = logging.getLogger(__name__)

tablespace_query = """select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2)
     as free_space from dba_free_space group by tablespace_name) a, (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
     from dba_data_files group by tablespace_name) b where a.tablespace_name(+)=b.tablespace_name;
"""
resource_query = """select resource_name, current_utilization, max_utilization from v$resource_limit;"""


def execute_query(sid, config, query):
    "Run an Oracle query via sqlplus and parse the results"

    # Generate a temporary file for the query script
    (file, filename) = tempfile.mkstemp()
    orig_filename = filename
    filename += ".sql"
    f = open(filename, 'w')
    f.write("set pages 1000;\nset linesize 1000;\n %s\nquit;" % query)
    f.close()

    # Build the SQL*PLUS command and call it
    global_vars = "LD_LIBRARY_PATH=%s/lib ORACLE_HOME=%s ORACLE_SID=%s " % (config['oracle_home'], config['oracle_home'], sid)
    command = os.path.join(config.get("oracle_home"), "bin", "sqlplus")
    command_format = ' -S %s/'
    command_arguments = (config.get('username'), config.get('password'), filename)
    command_format += '\\"%s\\"'

    if config.get("tnsname"):
        command_format += '@%s'
        command_arguments = (config.get('username'), config.get('password'), config.get('tnsname'), filename)
    elif config.get('rds_end_point'):
        command_format += '@//%s:1521/orcl'
        command_arguments = (config.get('username'), config.get('password'), config.get('rds_end_point'), filename)

    command_format += ' @%s'

    cmd = global_vars + command + command_format % command_arguments
    status, output = agent_util.execute_command(cmd, timeout=10)

    # Remove our temporary file
    os.remove(filename)
    os.remove(orig_filename)

    # Parse the output
    results = []
    lines = output.strip().split("\n")
    columns = lines[0].lower().split()
    for line in lines[2:]:
        line = line.strip()
        if not line: continue
        if line.endswith("rows selected."): continue
        values = line.split()
        results.append(dict(zip(columns, values)))

    return results


class OraclePlugin(agent_util.Plugin):
    textkey = "oracle"
    label = "Oracle"

    @classmethod
    def get_metadata(self, config):
        status = agent_util.SUPPORTED
        msg = None

        # Make sure they provided an oracle configuration block
        if not config:
            self.log.info("No oracle configuration block found")
            return {}

        # Check to see if Oracle block has been setup in the configuration file - if not, no op
        if not ("oracle_home" in config and "oracle_sid" in config and "username" in config and "password" in config):
            msg = "One or more Oracle configuration parameters missing from the agent config file"
            self.log.info(msg)
            status = agent_util.MISCONFIGURED

        # Make sure the sqlplus executable is installed and executable
        if status == agent_util.SUPPORTED and not os.path.exists(os.path.join(config.get("oracle_home"), "bin", "sqlplus")):
            msg = "Oracle sqlplus executable not found in directory specified in agent config file."
            self.log.info(msg)
            status = agent_util.MISCONFIGURED

        if status == agent_util.MISCONFIGURED:
            data = {
                "tnslistener": {
                    "label": "Whether TNS listener is active and rechable",
                    "options": None,
                    "status": status,
                    "error_message": msg
                }
            }
            return data

        data = {}
        sid_list = config['oracle_sid'].split(',')

        # Verify that the tnsping executable is present
        if os.path.exists(
                os.path.join(config.get("oracle_home"), "bin", "tnsping")):
            data['tnslistener'] = {
                "label": "Whether TNS listener is active and reachable",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": "",
            }

        # Gather up the available tablespaces that can be reported on
        tablespaces = {}
        for sid in sid_list:
            results = execute_query(sid, config, tablespace_query)
            tablespaces[sid] = [r["tablespace_name"] for r in results]
        if tablespaces:
            spaces = []
            for sid in tablespaces:
                for ts in tablespaces[sid]:
                    spaces.append("%s:%s" % (sid, ts))
            data['tablespace.size'] = {
                "label": "Size of tablespace in MB",
                "options": spaces,
                "status": agent_util.SUPPORTED,
                "error_message": "",
                "units": "MB"
            }
            data['tablespace.free'] = {
                "label": "Free space of tablespace in MB",
                "options": spaces,
                "status": agent_util.SUPPORTED,
                "error_message": "",
                "units": "MB"
            }
            data['tablespace.percent_free'] = {
                "label": "Percent of tablespace free",
                "options": spaces,
                "status": agent_util.SUPPORTED,
                "error_message": "",
                "units": "percent"
            }

        # Gather up the resource utilization metrics
        results = execute_query(sid_list[0], config, resource_query)
        resources = [r["resource_name"] for r in results]
        if results:
            data["resource.process.current"] = {
                "label": "Number of current processes",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.process.max"] = {
                "label": "Maximum number of processes",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }

            data["resource.session.current"] = {
                "label": "Number of current sessions",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.session.max"] = {
                "label": "Maximum number of sessions",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.enqueue_lock.current"] = {
                "label": "Number of current enqueue locks",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.enqueue_lock.max"] = {
                "label": "Maximum number of enqueue locks",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.enqueue_resource.current"] = {
                "label": "Number of current enqueue resources",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.enqueue_resource.max"] = {
                "label": "Maximum number of enqueue resources",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.ges_lock.current"] = {
                "label": "Number of current Global Enqueue Service locks",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.ges_lock.max"] = {
                "label": "Maximum number of Global Enqueue Service locks",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.ges_proc.current"] = {
                "label": "Number of current Global Enqueue Service processes ",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.ges_proc.max"] = {
                "label": "Maximum number of Global Enqueue Service processes",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.ges_resource.current"] = {
                "label": "Number of Global Enqueue Service resources",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.ges_resource.max"] = {
                "label": "Maximum number of Global Enqueue Service resources",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.max_shared_servers.current"] = {
                "label": "Current number of Maximum Shared Servers",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.max_shared_servers.max"] = {
                "label": "Maximum number of Maximum Shared Servers",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.transactions.current"] = {
                "label": "Current number of transactions",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }
            data["resource.transactions.max"] = {
                "label": "Maximum number of transactions",
                "options": sid_list,
                "status": agent_util.SUPPORTED,
                "error_message": ""
            }

        return data

    def check(self, textkey, data, config):

        if not data:
            msg = "Resource option is missing for metric type `%s`." % textkey
            self.log.info(msg)

        if textkey.startswith("resource."):
            sid = data
            junk, metric, kind = textkey.split('.')
            metric_mapping = {
                "process": "processes",
                "session": "sessions",
                "enqueue_lock": "enqueue_locks",
                "enqueue_resource": "enqueue_resources",
                "ges_lock": "ges_locks",
                "ges_proc": "ges_procs",
                "ges_resource": "ges_ress",
                "max_shared_servers": "max_shared_servers",
                "transactions": "transactions",
            }

            results = execute_query(sid, config, resource_query)
            for r in results:
                if r["resource_name"] == metric_mapping.get(metric, None):
                    if kind == "current":
                        return int(r["current_utilization"])
                    elif kind == "max":
                        return int(r["max_utilization"])

        elif textkey.startswith("tablespace"):
            sid, tablespace = data.split(":")
            results = execute_query(sid, config, tablespace_query)
            for r in results:
                if r["tablespace_name"] == tablespace:
                    if textkey.endswith(".size"):
                        return float(r["sizemb"])
                    elif textkey.endswith(".free"):
                        return float(r["freemb"])
                    elif textkey.endswith(".percent_free"):
                        if float(r["sizemb"]) == 0: return 0.
                        else:
                            return float(r["freemb"]) * 100. / float(r["sizemb"])

            # If we got here, the target tablespace wasn't found
            return 0

        elif textkey == "tnslistener":
            sid = data
            ip_address = config.get("tns_listener_ip", "127.0.0.1")
            cmd = "ORACLE_HOME=%s ORACLE_SID=%s " % (config['oracle_home'], sid)
            cmd += "%s %s" % (os.path.join(config.get("oracle_home"), "bin", "tnsping"), ip_address)
            status, output = agent_util.execute_command(cmd)
            if "OK" in output: return 1
            else: return 0

        else:
            msg = "Unknown metric type `%s` retuning `%s` as value." % (textkey, 0)
            self.log.info(msg)

            # Unknown metric type, return 0 by default
            return 0