#!/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
from lib.globals import STATE_OK, STATE_UNKNOWN, STATE_WARN

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

DESCRIPTION = """Checks index sizes, fragmentation, and consistent engine and collation usage across
all schemas in MySQL/MariaDB. Alerts on mixed storage engines or collations within a
single schema."""

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 (instead of specifying them on the command line). '
        'Example: `/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 main():
    """The main function. This is where the magic happens."""

    # logic taken from mysqltuner.pl:mysql_databases(), 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))

    # init some vars
    state = STATE_OK

    # SCHEMA_NAME and others must be uppercase due to MySQL 8+
    sql = """
        select SCHEMA_NAME
        from information_schema.schemata
        where SCHEMA_NAME not in (
            "mysql", "information_schema", "performance_schema", "sys"
        );
    """
    dblist = lib.base.coe(lib.db_mysql.select(conn, sql))

    results = {
        'index': '',
        'storengine': '',
        'collation': '',
        'tblengine': '',
        'colcharset': '',
        'colcollation': '',
    }

    # schema names come from MySQL itself (SHOW DATABASES), not from user input
    for schema in dblist:
        sql = f'''
            select TABLE_SCHEMA,
                sum(TABLE_ROWS) as sum_rows,
                sum(DATA_LENGTH) as sum_data,
                sum(INDEX_LENGTH) as sum_index,
                sum(DATA_LENGTH+INDEX_LENGTH) as sum_data_index,
                count(distinct ENGINE) as cnt_storengines,
                count(TABLE_NAME) as cnt_tables,
                count(distinct(TABLE_COLLATION)) as cnt_collations,
                count(distinct(ENGINE)) as cnt_tableengines
            from information_schema.tables
            where TABLE_SCHEMA="{schema['SCHEMA_NAME']}"
            group by TABLE_SCHEMA
            order by TABLE_SCHEMA;
        '''  # nosec B608
        dbinfo = lib.base.coe(lib.db_mysql.select(conn, sql, fetchone=True))
        if dbinfo is None:
            continue

        if (
            dbinfo['sum_data'] != 'NULL'
            and dbinfo['sum_index'] != 'NULL'
            and (
                dbinfo['sum_data'] > 10 * 1024 * 1024
                or dbinfo['sum_index'] > 10 * 1024 * 1024
            )
            and dbinfo['sum_data'] < dbinfo['sum_index']
        ):
            idx_h = lib.human.bytes2human(dbinfo['sum_index'])
            data_h = lib.human.bytes2human(dbinfo['sum_data'])
            results['index'] += f'{dbinfo["TABLE_SCHEMA"]} ({idx_h} / {data_h}), '

        if dbinfo['cnt_storengines'] != 1:
            results['storengine'] += (
                f'{dbinfo["TABLE_SCHEMA"]} ({dbinfo["cnt_storengines"]}x), '
            )

        if dbinfo['cnt_collations'] > 1:
            results['collation'] += (
                f'{dbinfo["TABLE_SCHEMA"]} ({dbinfo["cnt_collations"]}x), '
            )

        if dbinfo['cnt_tableengines'] > 1:
            results['tblengine'] += (
                f'{dbinfo["TABLE_SCHEMA"]} ({dbinfo["cnt_tableengines"]}x), '
            )

        sql = f'''
            select distinct(CHARACTER_SET_NAME)
            from information_schema.COLUMNS
            where CHARACTER_SET_NAME is not null
                and TABLE_SCHEMA = "{schema['SCHEMA_NAME']}";
        '''  # nosec B608
        distinct_column_charset = lib.base.coe(lib.db_mysql.select(conn, sql))
        if len(distinct_column_charset) > 1:
            results['colcharset'] += (
                f'{dbinfo["TABLE_SCHEMA"]} ({len(distinct_column_charset)}x), '
            )

        sql = f'''
            select distinct(COLLATION_NAME)
            from information_schema.COLUMNS
            where COLLATION_NAME is not null
                and TABLE_SCHEMA = "{schema['SCHEMA_NAME']}";
        '''  # nosec B608
        distinct_column_collation = lib.base.coe(lib.db_mysql.select(conn, sql))
        if len(distinct_column_collation) > 1:
            results['colcollation'] += (
                f'{dbinfo["TABLE_SCHEMA"]} ({len(distinct_column_collation)}x), '
            )

    lib.db_mysql.close(conn)

    msg = ''
    if results['index']:

        # build the message
        msg += f'* Index size is larger than data size: {results["index"][:-2]}\n'
    if results['storengine']:
        msg += (
            f'* Multi storage engines (use one storage engine for '
            f'all tables): {results["storengine"][:-2]}\n'
        )
    if results['collation']:
        msg += (
            f'* Multi collations (use one collation for all '
            f'tables): {results["collation"][:-2]}\n'
        )
    if results['tblengine']:
        msg += (
            f'* Multi table engines (use one engine for all '
            f'tables): {results["tblengine"][:-2]}\n'
        )
    if results['colcharset']:
        msg += (
            f'* Multi charsets for text-like cols (use one charset '
            f'for all cols if possible): '
            f'{results["colcharset"][:-2]}\n'
        )
    if results['colcollation']:
        msg += (
            f'* Multi collations for text-like cols (use one '
            f'charset for all cols if possible): '
            f'{results["colcollation"][:-2]}\n'
        )

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