#!/usr/bin/env python3
# -*- 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.human
import lib.txt
from lib.globals import STATE_OK, STATE_UNKNOWN, STATE_WARN

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

DESCRIPTION = """Checks storage engine configuration in MySQL/MariaDB, including fragmented tables
that may benefit from optimization and tables using non-default or deprecated storage
engines.
Alerts on fragmented tables or non-default engine usage."""

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


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.
    # Without the GLOBAL modifier, SHOW VARIABLES displays the values that are used for
    # the current connection to MariaDB.
    sql = """
        show global variables
        where variable_name like 'innodb_file_per_table'
            ;
          """
    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:check_storage_engines(), v1.9.8
    # including variable names

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

    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_select_privileges(conn))

    myvar = lib.db_mysql.lod2dict(get_vars(conn))
    engines = lib.db_mysql.get_engines(conn)

    # init some vars
    msg = ''
    state = STATE_OK

    # MySQL 5.1.5+ servers can have table sizes calculated quickly from information schema
    enginestats = {}
    sql = """
        select ENGINE as engine,
            sum(DATA_LENGTH+INDEX_LENGTH) as size,
            count(ENGINE) as count,
            sum(DATA_LENGTH) as dsize,
            sum(INDEX_LENGTH) as isize
        from information_schema.tables
        where TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql')
            and ENGINE is not null
        group by ENGINE
        order by ENGINE asc;
    """
    templist = lib.base.coe(lib.db_mysql.select(conn, sql))
    for temp in templist:
        if not temp:
            continue
        engine = temp['engine']
        enginestats[engine] = temp.get('size', 0)

    # If the storage engine isn't being used, recommend it to be disabled
    if (
        enginestats.get('InnoDB') is None
        and engines.get('have_innodb', '')
        and engines['have_innodb'] == 'YES'
    ):

        # build the message
        msg += "* InnoDB is enabled but isn't being used. Add skip-innodb to MySQL configuration to disable InnoDB\n"
    if (
        enginestats.get('BerkeleyDB') is None
        and engines.get('have_bdb', '')
        and engines['have_bdb'] == 'YES'
    ):
        msg += "* BDB is enabled but isn't being used. Add skip-bdb to MySQL configuration to disable BDB\n"
    if (
        enginestats.get('ISAM') is None
        and engines.get('have_isam', '')
        and engines['have_isam'] == 'YES'
    ):
        msg += "* MYISAM is enabled but isn't being used. Add skip-isam to MySQL configuration to disable ISAM (MySQL 4.1.0+)\n"

    # Fragmented tables
    # not_innodb is a hardcoded SQL fragment chosen from two constants, not user input
    not_innodb = ''
    if not myvar.get('innodb_file_per_table', '') or myvar['innodb_file_per_table'] == 'OFF':
        not_innodb = 'and not ENGINE="InnoDB"'
    sql = (
        "select concat(concat(TABLE_SCHEMA, '.'),TABLE_NAME) as full_table_name, "  # nosec B608
        'cast(DATA_FREE as signed) as data_free '
        'from information_schema.tables '
        "where TABLE_SCHEMA not in ('information_schema', 'performance_schema', 'mysql') "
        'and DATA_LENGTH/1024/1024 > 100 '
        'and cast(DATA_FREE as signed)*100/(DATA_LENGTH+INDEX_LENGTH+cast(DATA_FREE as signed)) > 10 '
        "and not ENGINE='MEMORY' "
        f'{not_innodb}'
    )
    fragtables = lib.base.coe(lib.db_mysql.select(conn, sql))
    if len(fragtables) > 0:
        msg += f'* {len(fragtables)} fragmented {lib.txt.pluralize("table", len(fragtables))}\n'
        total_free = 0
        for fragtable in fragtables:
            table_schema, table_name = fragtable['full_table_name'].rsplit(
                '.', 1
            )  # my.schema.my_table
            data_free = fragtable.get('data_free', 0)
            total_free += data_free
            msg += (
                f'* OPTIMIZE TABLE `{table_schema}`.`{table_name}`;'
                f' -- can free {lib.human.bytes2human(data_free)}\n'
            )
        msg += (
            f'* Total freed space after all OPTIMIZE TABLEs:'
            f' {lib.human.bytes2human(total_free)}\n'
        )

    # Auto increments
    tblist = {}

    # Find the maximum integer
    sql = 'select ~0 as maxint;'
    maxint = lib.base.coe(lib.db_mysql.select(conn, sql, fetchone=True))

    sql = 'show databases;'
    dblist = lib.base.coe(lib.db_mysql.select(conn, sql))

    # Now we use a database list, and loop through it to get storage engine stats for tables
    for db in dblist:
        if db['Database'] == 'information_schema':
            continue
        sql = f'show table status from `{db["Database"]}`;'
        tblist = lib.base.coe(lib.db_mysql.select(conn, sql))
        for tbl in tblist:
            if not tbl['Auto_increment']:
                continue
            percent = tbl['Auto_increment'] / maxint['maxint'] * 100
            if percent >= 75:
                msg += (
                    f'* {db["Database"]}.{tbl["Name"]}'
                    f' has an autoincrement value near'
                    f' max capacity ({round(percent, 1)}%)\n'
                )

    lib.db_mysql.close(conn)

    if msg:
        state = STATE_WARN
        msg = 'There are warnings.\n\n' + msg
    else:
        msg = 'Everything is ok.'

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


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