#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# Copyright (C) 2019 tribe29 GmbH - License: GNU General Public License v2
# This file is part of Checkmk (https://checkmk.com). It is subject to the terms and
# conditions defined in the file COPYING, which is part of this source code package.

# In cooperation with Thorsten Bruhns from OPITZ Consulting

# <<oracle_performance:sep(124)>>>
# TUX12C|DB CPU|64
# TUX12C|DB time|86

# server-linux-oracle-12:
# <<<oracle_performance:sep(124)>>>
# ENLT1|sys_time_model|DB CPU|223408
# ENLT1|sys_time_model|DB time|630525
# ENLT1|buffer_pool_statistics|DEFAULT|207456769|194044148|3075188333|126417048|10935918|0|419514
# ENLT1|librarycache|SQL AREA|84972008|84406451|196493113|193867707|791310|39140
# ENLT1|librarycache|TABLE/PROCEDURE|13196582|12937687|120405491|118546232|869542|0
# ENLT1|librarycache|BODY|8682469|8666221|11047659|11025730|3932|0
# ENLT1|librarycache|TRIGGER|21238|19599|21238|19599|0|0
# ENLT1|librarycache|INDEX|192359|171580|173880|112887|22742|0
# ENLT1|librarycache|CLUSTER|287523|284618|297990|294967|118|0
# ENLT1|librarycache|DIRECTORY|647|118|1297|232|0|0
# ENLT1|librarycache|QUEUE|6916850|6916397|14069290|14068271|367|0
# ENLT1|librarycache|APP CONTEXT|32|15|63|35|11|0
# ENLT1|librarycache|RULESET|2|1|15|9|4|0
# ENLT1|librarycache|SUBSCRIPTION|63|59|123|84|31|0
# ENLT1|librarycache|LOCATION|388|277|388|277|0|0
# ENLT1|librarycache|TRANSFORMATION|3452154|3451741|3452154|3451741|0|0
# ENLT1|librarycache|USER AGENT|24|15|12|2|1|0
# ENLT1|librarycache|TEMPORARY TABLE|45298|33939|45298|0|33939|0
# ENLT1|librarycache|TEMPORARY INDEX|18399|3046|18399|0|3046|0
# ENLT1|librarycache|EDITION|4054576|4054369|7846832|7846023|366|0

import cmk.utils.oracle_constants as oracle_constants

oracle_performance_discovery = []


def _get_item_data(item, parsed):
    data = parsed.get(item)

    # In case of missing information we assume that the login into
    # the database has failed and we simply skip this check. It won't
    # switch to UNKNOWN, but will get stale.
    if not data:
        raise MKCounterWrapped("Login into database failed")

    return data


#
# ORACLE Performance Main Check
#


def check_oracle_performance(item, params, parsed):
    discovery_params = host_extra_conf_merged(host_name(), oracle_performance_discovery)
    data = _get_item_data(item, parsed)

    now = time.time()
    perfdata = []
    infotexts = []

    if discovery_params.get("dbtime") is None:
        for result in _check_oracle_db_time(item, data, now, {}):
            yield result

    if discovery_params.get("memory") is None:
        # old agents deliver not the needed data...
        sga_info = data.get("SGA_info")
        if sga_info:
            for sga_field in oracle_constants.oracle_sga_fields:
                if sga_field.name not in sga_info:
                    continue
                value = sga_info[sga_field.name]
                yield 0, "%s: %s" % (sga_field.name, get_bytes_human_readable(value))
                perfdata.append((sga_field.metric, value))

    # PDB is <SID>.<PDB>
    # ignore more perf-data for PDBs except CDBROOT!
    if "." in item and ".CDB$ROOT" not in item:

        # PDB does not support more performance data at the moment...
        infotexts.append("limited performance data for PDBSEED and non CDBROOT")
        yield 0, ", ".join(infotexts), perfdata
        return

    if "buffer_pool_statistics" in data and "DEFAULT" in data["buffer_pool_statistics"]:
        buffer_pool_stats = data["buffer_pool_statistics"]
        (
            db_block_gets,
            db_block_change,
            consistent_gets,
            physical_reads,
            physical_writes,
            free_buffer_wait,
            buffer_busy_wait,
        ) = buffer_pool_stats["DEFAULT"]

        for what, val in [
            ("oracle_db_block_gets", db_block_gets),
            ("oracle_db_block_change", db_block_change),
            ("oracle_consistent_gets", consistent_gets),
            ("oracle_physical_reads", physical_reads),
            ("oracle_physical_writes", physical_writes),
            ("oracle_free_buffer_wait", free_buffer_wait),
            ("oracle_buffer_busy_wait", buffer_busy_wait),
        ]:
            rate = get_rate("oracle_perf.%s.buffer_pool_statistics.%s" % (item, what), now, val)
            perfdata.append((what, rate))

        if db_block_gets + consistent_gets > 0:
            hit_ratio = (
                1 - (float(physical_reads) / (float(db_block_gets) + float(consistent_gets)))
            ) * 100
            yield 0, "Buffer hit ratio: %.1f%%" % hit_ratio, [
                ("oracle_buffer_hit_ratio", hit_ratio)
            ]

    if "librarycache" in data:
        pins_sum = 0
        pin_hits_sum = 0
        for what, vals in data["librarycache"].items():
            _gets, _gethits, pins, pin_hits, _reloads, _invalidations = vals
            pins_sum += pins
            pin_hits_sum += pin_hits

        for what, val in [("oracle_pins_sum", pins_sum), ("oracle_pin_hits_sum", pin_hits_sum)]:
            rate = get_rate("oracle_perf.%s.librarycache.%s" % (item, what), now, val)
            perfdata.append((what, rate))

        if pins_sum > 0:
            pin_ratio = float(pin_hits_sum) / pins_sum * 100
            yield 0, "Library cache hit ratio: %.1f%%" % pin_ratio, [
                ("oracle_library_cache_hit_ratio", pin_ratio)
            ]

    yield 0, ", ".join(sorted(infotexts)), sorted(perfdata)
    return


check_info["oracle_performance"] = {
    # section is already migrated!
    "inventory_function": discover(),
    "check_function": check_oracle_performance,
    "service_description": "ORA %s Performance",
    "has_perfdata": True,
    "group": "oracle_performance",
}

# ======================================
# ORACLE PERFORMANCE SUBCHECKS
# ======================================


def discover_oracle_performance_subcheck(subcheck_settings_name):
    """Subchecks are activated optionally via discovery configuration"""

    def inventory_func(parsed):
        settings = host_extra_conf_merged(host_name(), oracle_performance_discovery)
        subcheck_settings = settings.get(subcheck_settings_name)
        if subcheck_settings is not None:
            for sid in parsed:
                yield sid, {}

    return inventory_func


def _get_subcheck_params(full_params, subcheck_name):
    params_list = full_params.get(subcheck_name, [])
    return {p[0]: p[1] for p in params_list}


#
# ORACLE Performance DB-Time
#


def _check_oracle_db_time(item, item_data, now, params):
    def get_db_time_rate(perfvar, val):
        return get_rate("oracle_perf.%s.sys_time_model.%s" % (item, perfvar), now, val)

    # old agents deliver no data for sys_time_model!
    sys_time_model = item_data.get("sys_time_model")
    if sys_time_model is None:
        return []

    # sys_time_model: only DB_CPU and DB_Time!
    cpu_time = sys_time_model.get("DB CPU")
    db_time = sys_time_model.get("DB time")
    # db_time is the sum of cpu time and wait time (non-idle)
    wait_time = db_time - cpu_time

    cpu_time_rate = get_db_time_rate("oracle_db_cpu", cpu_time)
    db_time_rate = get_db_time_rate("oracle_db_time", db_time)
    wait_time_rate = get_db_time_rate("oracle_db_wait_time", wait_time)

    results = []

    for metric, infoname, rate in [
        ("oracle_db_time", "DB Time", db_time_rate),
        ("oracle_db_cpu", "DB CPU", cpu_time_rate),
        ("oracle_db_wait_time", "DB Non-Idle Wait", wait_time_rate),
    ]:
        metric_params = params.get(metric, (None, None))
        results.append(check_levels(rate, metric, metric_params, unit="1/s", infoname=infoname))

    return results


def check_oracle_performance_dbtime(item, params, parsed):
    params = _get_subcheck_params(params, "dbtime")
    data = _get_item_data(item, parsed)
    now = time.time()
    for result in _check_oracle_db_time(item, data, now, params):
        yield result


check_info["oracle_performance.dbtime"] = {
    "inventory_function": discover_oracle_performance_subcheck("dbtime"),
    "check_function": check_oracle_performance_dbtime,
    "service_description": "ORA %s Performance DB-Time",
    "has_perfdata": True,
    "group": "oracle_performance",
}

#
# ORACLE Performance Memory
#


def _check_oracle_memory_info(data, params, sticky_fields, fields):
    results = []

    for ga_field in fields:
        value = data.get(ga_field.name)
        if value is None:
            continue

        metric_params = params.get(ga_field.metric, (None, None))
        state, infotext, perf = check_levels(
            value, ga_field.metric, metric_params, human_readable_func=get_bytes_human_readable
        )
        if ga_field.name in sticky_fields:
            results.append((state, "%s: %s" % (ga_field.name, infotext), perf))
        else:
            results.append((0, "", perf))
    return results


def check_oracle_performance_memory(item, params, parsed):
    params = _get_subcheck_params(params, "memory")
    data = _get_item_data(item, parsed)
    sga_info = data.get("SGA_info", {})

    # sticky_results_sga, results_sga, perfdata_sga = _check_oracle_memory_info(
    for result in _check_oracle_memory_info(
        sga_info, params, ["Maximum SGA Size"], oracle_constants.oracle_sga_fields
    ):
        yield result

    # prepare PGA_info values. They come as two element array [x, 'bytes']
    pga_info = {field: value[0] for field, value in data.get("PGA_info", {}).items()}
    for result in _check_oracle_memory_info(
        pga_info, params, ["total PGA allocated"], oracle_constants.oracle_pga_fields
    ):
        yield result


check_info["oracle_performance.memory"] = {
    "inventory_function": discover_oracle_performance_subcheck("memory"),
    "check_function": check_oracle_performance_memory,
    "service_description": "ORA %s Performance Memory",
    "has_perfdata": True,
    "group": "oracle_performance",
}

#
# ORACLE Performance IOStat Bytes + IOs
#


def _check_oracle_performance_iostat_file(item, params, data, unit, io_fields):
    now = time.time()
    totals = [0] * len(io_fields)
    error_results = []

    iostat_info = data.get("iostat_file", {})
    for iofile in oracle_constants.oracle_iofiles:
        waitdata = iostat_info.get(iofile.name)
        if not waitdata:
            continue
        for i, field in enumerate(io_fields):
            data_index, metric_suffix, field_name = field
            metric_name = "oracle_ios_f_%s_%s" % (iofile.id, metric_suffix)

            rate = get_rate(
                "oracle_perf.%s.iostat_file.%s" % (item, metric_name),
                now,
                waitdata[data_index],
                onwrap=ZERO,
            )

            totals[i] += rate

            metric_params = params.get(metric_name, (None, None))
            state, info, perf = check_levels(
                rate, metric_name, metric_params, infoname=iofile.name + " " + field_name, unit=unit
            )
            if state > 0:
                error_results.append((state, info, perf))
            else:
                yield 0, "", perf

    # Output totals
    for i, field in enumerate(io_fields):
        _data_index, metric_suffix, field_name = field
        total = totals[i]

        if unit == "bytes/s":
            total_readable = get_bytes_human_readable(total)
            total_output = "%s/s" % total_readable
        else:
            total_readable = total
            total_output = "%.2f %s" % (total_readable, unit)

        yield 0, "%s: %s" % (field_name, total_output), [
            ("oracle_ios_f_total_%s" % metric_suffix, total)
        ]

    # Output errors
    for result in error_results:
        yield result


def check_oracle_performance_iostat_bytes(item, params, parsed):
    params = _get_subcheck_params(params, "iostat_bytes")
    data = _get_item_data(item, parsed)
    return _check_oracle_performance_iostat_file(
        item,
        params,
        data,
        "bytes/s",
        [
            (8, "s_rb", "Small Read"),
            (9, "l_rb", "Large Read"),
            (10, "s_wb", "Small Write"),
            (11, "l_wb", "Large Write"),
        ],
    )


check_info["oracle_performance.iostat_bytes"] = {
    "inventory_function": discover_oracle_performance_subcheck("iostat_bytes"),
    "check_function": check_oracle_performance_iostat_bytes,
    "service_description": "ORA %s Performance IO Stats Bytes",
    "has_perfdata": True,
    "group": "oracle_performance",
}


def check_oracle_performance_iostat_ios(item, params, parsed):
    params = _get_subcheck_params(params, "iostat_ios")
    data = _get_item_data(item, parsed)
    return _check_oracle_performance_iostat_file(
        item,
        params,
        data,
        "1/s",
        [
            (0, "s_r", "Small Reads"),
            (1, "l_r", "Large Reads"),
            (2, "s_w", "Small Writes"),
            (3, "l_w", "Large Writes"),
        ],
    )


check_info["oracle_performance.iostat_ios"] = {
    "inventory_function": discover_oracle_performance_subcheck("iostat_ios"),
    "check_function": check_oracle_performance_iostat_ios,
    "service_description": "ORA %s Performance IO Stats Requests",
    "has_perfdata": True,
    "group": "oracle_performance",
}

#
# ORACLE Performance Waitclasses
#


def check_oracle_performance_waitclasses(item, params, parsed):
    params = _get_subcheck_params(params, "waitclasses")
    data = _get_item_data(item, parsed)
    now = time.time()
    error_results = []

    total_waited_sum = 0
    total_waited_sum_fg = 0

    # sys_wait_class -> wait_class
    waitclass_info = data.get("sys_wait_class", {})
    for waitclass in oracle_constants.oracle_waitclasses:
        waitdata = waitclass_info.get(waitclass.name)
        if not waitdata:
            continue
        metric_start = "oracle_wait_class_%s" % waitclass.id
        for data_index, metric_suffix, infotext_suffix in [
            (1, "waited", "wait class"),
            (3, "waited_fg", "wait class (FG)"),
        ]:
            metric_name = "%s_%s" % (metric_start, metric_suffix)
            rate = (
                get_rate(
                    "oracle_perf.%s.sys_wait_class.%s" % (item, metric_name),
                    now,
                    waitdata[data_index],
                )
                / 100
            )

            if metric_suffix == "waited":
                total_waited_sum += rate
            else:
                total_waited_sum_fg += rate

            metric_params = params.get(metric_name, (None, None))
            state, info, perf = check_levels(
                rate,
                metric_name,
                metric_params,
                infoname="%s %s" % (waitclass.name, infotext_suffix),
                unit="1/s",
            )
            if state > 0:
                error_results.append((state, info, perf))
            else:
                yield 0, "", perf

    # Output totals
    for infoname, total_value, total_metric in [
        ("Total waited", total_waited_sum, "oracle_wait_class_total"),
        ("Total waited (FG)", total_waited_sum_fg, "oracle_wait_class_total_fg"),
    ]:
        metric_params = params.get(total_metric, (None, None))
        yield check_levels(total_value, total_metric, metric_params, infoname=infoname, unit="1/s")

    # Output errors
    for result in error_results:
        yield result


check_info["oracle_performance.waitclasses"] = {
    "inventory_function": discover_oracle_performance_subcheck("waitclasses"),
    "check_function": check_oracle_performance_waitclasses,
    "service_description": "ORA %s Performance System Wait",
    "has_perfdata": True,
    "group": "oracle_performance",
}
