#!/usr/lib64/linuxfabrik-monitoring-plugins/venv/bin/python
# -*- coding: utf-8; py-indent-offset: 4 -*-
#
# Author:  Linuxfabrik GmbH, Zurich, Switzerland
# Contact: info (at) linuxfabrik (dot) ch
#          https://www.linuxfabrik.ch/
# License: The Unlicense, see LICENSE file.

# https://github.com/Linuxfabrik/monitoring-plugins/blob/main/CONTRIBUTING.md

"""See the check's README for more details."""

import argparse
import sys

import lib.args
import lib.base
import lib.db_mysql
import lib.db_sqlite
import lib.human
import lib.txt
from lib.globals import STATE_OK, STATE_UNKNOWN, STATE_WARN

__author__ = 'Linuxfabrik GmbH, Zurich/Switzerland'
__version__ = '2026051003'

DESCRIPTION = """Checks the rate of joins executed without indexes in MySQL/MariaDB
(`Select_range_check + Select_full_join`). A high rate (more than 250 such joins per day,
matching MySQLTuner) indicates missing indexes and can severely impact query performance.
Alerts when the rate exceeds the threshold."""

DEFAULT_DEFAULTS_FILE = '/var/spool/icinga2/.my.cnf'
DEFAULT_DEFAULTS_GROUP = 'client'
DEFAULT_TIMEOUT = 3

SQLITE_DB = 'linuxfabrik-monitoring-plugins-mysql-joins.db'

# Above this `join_buffer_size`, mysqltuner stops recommending raising it - the
# upstream code hard-codes `if join_buffer_size < 4 * 1024 * 1024` for the "raise"
# branch, without further justification. We mirror the same threshold and use the
# *per-session* allocation argument (size × max_connections is reserved memory) to
# motivate it instead of guessing about diminishing returns above 4 MiB.
JOIN_BUFFER_SIZE_RECOMMEND_CEILING = 4 * 1024 * 1024


def parse_args():
    """Parse command line arguments using argparse."""
    parser = argparse.ArgumentParser(description=DESCRIPTION)

    parser.add_argument(
        '-V',
        '--version',
        action='version',
        version=f'%(prog)s: v{__version__} by {__author__}',
    )

    parser.add_argument(
        '--always-ok',
        help=lib.args.help('--always-ok'),
        dest='ALWAYS_OK',
        action='store_true',
        default=False,
    )

    parser.add_argument(
        '--defaults-file',
        help='MySQL/MariaDB cnf file to read user, host and password from. '
        'Example: `--defaults-file=/var/spool/icinga2/.my.cnf`. '
        'Default: %(default)s',
        dest='DEFAULTS_FILE',
        default=DEFAULT_DEFAULTS_FILE,
    )

    parser.add_argument(
        '--defaults-group',
        help=lib.args.help('--defaults-group') + ' Default: %(default)s',
        dest='DEFAULTS_GROUP',
        default=DEFAULT_DEFAULTS_GROUP,
    )

    parser.add_argument(
        '--timeout',
        help=lib.args.help('--timeout') + ' Default: %(default)s (seconds)',
        dest='TIMEOUT',
        type=int,
        default=DEFAULT_TIMEOUT,
    )

    args, _ = parser.parse_known_args()
    return args


def get_vars(conn):
    # Do not implement `get_all_vars()`, just fetch the ones we need for this check.
    sql = """
        show global variables
        where variable_name like 'join_buffer_size'
            ;
          """
    return lib.base.coe(lib.db_mysql.select(conn, sql))


def get_status(conn):
    # Do not implement `get_all_vars()`, just fetch the ones we need for this check.
    sql = """
        show global status
        where variable_name like 'Select_full_join'
            or variable_name like 'Select_range_check'
            or variable_name like 'Uptime'
            ;
          """
    return lib.base.coe(lib.db_mysql.select(conn, sql))


def main():
    """The main function. This is where the magic happens."""

    # logic taken from mysqltuner.pl:mysql_stats(), section "Joins", verified in
    # sync with MySQLTuner (the > 250 joins-per-day threshold and the
    # 4 MiB `join_buffer_size` ceiling above which mysqltuner stops recommending
    # to raise it are unchanged upstream since the original port).

    # parse the command line
    try:
        args = parse_args()
    except SystemExit:
        sys.exit(STATE_UNKNOWN)

    # fetch data
    mysql_connection = {
        'defaults_file': args.DEFAULTS_FILE,
        'defaults_group': args.DEFAULTS_GROUP,
        'timeout': args.TIMEOUT,
    }
    conn = lib.base.coe(lib.db_mysql.connect(mysql_connection))
    lib.base.coe(lib.db_mysql.check_privileges(conn))

    myvar = lib.db_mysql.lod2dict(get_vars(conn))
    mystat = lib.db_mysql.lod2dict(get_status(conn))
    lib.db_mysql.close(conn)

    # init some vars
    # cache int conversions
    select_full_join = int(mystat['Select_full_join'])
    select_range_check = int(mystat['Select_range_check'])
    joins_without_indexes = select_full_join + select_range_check
    join_buffer_size = int(myvar['join_buffer_size'])
    # Clamp Uptime to a minimum of 1 second so the per-day rate is well-defined on a
    # freshly booted server where `SHOW GLOBAL STATUS LIKE 'Uptime'` can legitimately
    # return 0 in the first second after startup.
    uptime = max(int(mystat.get('Uptime') or 0), 1)

    state = STATE_OK
    sections = []
    # All recommendations from all WARN paths land here and render once at the
    # end as a `Recommendations:\n* ...` bulleted block, regardless of which
    # combinations of WARN paths fire.
    recommendations = []

    # analyze data
    joins_per_day = round(joins_without_indexes / (uptime / 86400), 1)

    # build the message
    # Always show what was measured, even on OK - admins reading the service line
    # benefit from seeing the actual count and breakdown rather than a bare
    # "Everything is ok.". Component breakdown is also human-formatted for symmetry
    # with the per-day rate.
    facts = (
        f'{lib.human.number2human(joins_without_indexes)}'
        f' {lib.txt.pluralize("JOIN", joins_without_indexes)} without indexes'
        f' in {lib.human.seconds2human(uptime)} of uptime'
        f' (approx. {lib.human.number2human(joins_per_day)}/day;'
        f' `Select_range_check` = {lib.human.number2human(select_range_check)},'
        f' `Select_full_join` = {lib.human.number2human(select_full_join)})'
    )

    if joins_per_day > 250:
        state = lib.base.get_worst(state, STATE_WARN)
        sections.append(f'{facts}{lib.base.state2str(STATE_WARN, prefix=" ")}.')
        recommendations.append('Use JOINs with indexes wherever possible')
        if join_buffer_size < JOIN_BUFFER_SIZE_RECOMMEND_CEILING:
            recommendations.append(
                f'Otherwise raise `join_buffer_size`'
                f' (currently {lib.human.bytes2human(join_buffer_size)}). MySQLTuner'
                f' uses 4 MiB as its cutoff for this recommendation, above which'
                f' it stops suggesting further increases'
            )
        else:
            recommendations.append(
                f'`join_buffer_size` is already'
                f' {lib.human.bytes2human(join_buffer_size)} - above the 4 MiB'
                f' point at which MySQLTuner stops recommending to raise it, so the'
                f' bottleneck is more likely the query design (missing indexes)'
                f' than the buffer size'
            )
    else:
        sections.append(f'{facts}.')

    # Independent WARN: oversized `join_buffer_size`. mysqltuner does not flag this,
    # but the variable is allocated PER SESSION, so size × `max_connections` is
    # real reserved memory.
    if join_buffer_size > JOIN_BUFFER_SIZE_RECOMMEND_CEILING:
        state = lib.base.get_worst(state, STATE_WARN)
        sections.append(
            f'`join_buffer_size` ({lib.human.bytes2human(join_buffer_size)}) is'
            f' above MySQLTuner\'s 4 MiB cutoff, and the buffer is allocated per'
            f' session, so the cost is paid up to `max_connections` times the'
            f' buffer size{lib.base.state2str(STATE_WARN, prefix=" ")}.'
        )
        recommendations.append(
            f'Lower `join_buffer_size` (currently'
            f' {lib.human.bytes2human(join_buffer_size)}) closer to MySQLTuner\'s'
            f' 4 MiB cutoff to reduce per-session memory reservation'
        )

    if recommendations:
        sections.append(
            'Recommendations:\n' + '\n'.join(f'* {r}' for r in recommendations)
        )

    msg = '\n\n'.join(sections)

    # Per-CONTRIBUTING: counters as in-plugin per-second deltas, not `uom='c'`.
    rates = lib.db_sqlite.per_second_deltas(
        SQLITE_DB,
        'mysql-joins',
        {
            'select_full_join': select_full_join,
            'select_range_check': select_range_check,
            'joins_without_indexes': joins_without_indexes,
        },
    )

    perfdata = ''
    perfdata += lib.base.get_perfdata(
        'mysql_join_buffer_size',
        join_buffer_size,
        uom='B',
        _min=0,
    )
    if rates is not None:
        perfdata += lib.base.get_perfdata(
            'mysql_select_full_join_per_second',
            int(rates['select_full_join']),
            _min=0,
        )
        perfdata += lib.base.get_perfdata(
            'mysql_select_range_check_per_second',
            int(rates['select_range_check']),
            _min=0,
        )
        perfdata += lib.base.get_perfdata(
            'mysql_joins_without_indexes_per_second',
            int(rates['joins_without_indexes']),
            _min=0,
        )
    perfdata += lib.base.get_perfdata(
        'mysql_uptime',
        uptime,
        uom='s',
        _min=0,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_joins_without_indexes_per_day',
        joins_per_day,
        _min=0,
    )

    # over and out
    lib.base.oao(msg, state, perfdata, always_ok=args.ALWAYS_OK)


if __name__ == '__main__':
    try:
        main()
    except Exception:
        lib.base.cu()
