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

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

DESCRIPTION = """Checks user schemas in MySQL/MariaDB for two replication- and performance-relevant
defects: base tables with no index at all (mysqltuner's `mysql_tables()` check) and
InnoDB base tables without a user-defined `PRIMARY KEY`. The second case is a documented
hotspot for ROW-based replication: the replica has to materialise each row event against an
internal hidden 6-byte index, which can degrade to a full table scan per row event. Alerts
when either count crosses `--warning` / `--critical`. `--match` and `--ignore` narrow the
scan down to a single schema or table. Supports extended reporting via --lengthy."""

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

# Default thresholds: the first defect already triggers WARN, an avalanche
# (10+) escalates to CRIT. Nagios range form `N` = "OK range is 0 to N";
# values strictly above N trigger. So `0` alerts on the first occurrence
# and `9` alerts at the tenth.
DEFAULT_WARN = '0'
DEFAULT_CRIT = '9'

SYSTEM_SCHEMAS = ('information_schema', 'mysql', 'performance_schema', 'sys')

# How many bad tables to list inline without --lengthy. Above this the output
# stays short and the admin uses --lengthy or the perfdata to dig further.
LIST_LIMIT = 10


def parse_args():
    """Parse command line arguments using argparse."""
    parser = argparse.ArgumentParser(
        description=DESCRIPTION,
        epilog=lib.args.epilog(__file__),
        formatter_class=lib.args.HelpFormatter,
    )

    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(
        '-c',
        '--critical',
        help='CRIT threshold for the number of bad tables (per category: '
        'missing indexes / missing primary key). '
        'Supports Nagios ranges. '
        'Default: %(default)s',
        dest='CRITICAL',
        default=DEFAULT_CRIT,
    )

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

    # Append parameters must always use `default=None` instead of a list,
    # because argparse appends user values to the default list instead of
    # replacing it (see https://bugs.python.org/issue16399).
    # After parsing, assign the actual defaults in main() if the value is
    # still None.
    parser.add_argument(
        '--ignore',
        help='Ignore tables whose name matches this Python regular expression. '
        'Matched against the fully qualified table identifier `schema.table`, '
        'so one pattern can drop a whole schema or a single table. '
        'Case-sensitive by default; use `(?i)` for case-insensitive matching. '
        'Can be specified multiple times. '
        'Default: %(default)s. '
        'Example: `--ignore="^icinga_director\\."` to skip a schema whose tables '
        'are managed by the application. '
        'Example: `--ignore="\\.tmp_"` to skip temporary tables everywhere.',
        dest='IGNORE',
        action='append',
        default=None,
    )

    # Deprecated parameters: hidden from --help, still accepted so existing
    # service definitions keep working. Both are matched against the bare
    # schema resp. table name, which is what they did when MySQL evaluated
    # them via `NOT REGEXP`.
    parser.add_argument(
        '--ignore-schemas',
        help=argparse.SUPPRESS,
        dest='IGNORE_SCHEMAS',
        action='append',
        default=None,
    )

    parser.add_argument(
        '--ignore-tables',
        help=argparse.SUPPRESS,
        dest='IGNORE_TABLES',
        action='append',
        default=None,
    )

    parser.add_argument(
        '--lengthy',
        help=lib.args.help('--lengthy'),
        dest='LENGTHY',
        action='store_true',
        default=DEFAULT_LENGTHY,
    )

    parser.add_argument(
        '--match',
        help='Only check tables whose name matches this Python regular expression. '
        'Matched against the fully qualified table identifier `schema.table`. '
        'Case-sensitive by default; use `(?i)` for case-insensitive matching. '
        'Can be specified multiple times. '
        + lib.args.MATCH_IGNORE_PRECEDENCE
        + ' Default: %(default)s. '
        'Example: `--match="^shop\\."` to check the `shop` schema only. '
        'Example: `--match="^shop\\.orders$"` to check one table only.',
        dest='MATCH',
        action='append',
        default=None,
    )

    parser.add_argument(
        '--no-perfdata',
        help=lib.args.help('--no-perfdata'),
        dest='NO_PERFDATA',
        action='store_true',
        default=False,
    )

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

    parser.add_argument(
        '-w',
        '--warning',
        help='WARN threshold for the number of bad tables (per category: '
        'missing indexes / missing primary key). '
        'Supports Nagios ranges. '
        'Default: %(default)s',
        dest='WARNING',
        default=DEFAULT_WARN,
    )

    args, _ = parser.parse_known_args()
    return args


def get_tables_without_any_index(conn, excluded):
    # LEFT JOIN onto information_schema.statistics: a base table with no
    # entries in statistics has no index at all. Single query, replaces the
    # previous O(schemas * tables) round-trip storm.
    sql = f"""
        select t.TABLE_SCHEMA as table_schema,
            t.TABLE_NAME as table_name,
            t.ENGINE as engine
        from information_schema.tables t
        left join information_schema.statistics s
            on t.TABLE_SCHEMA = s.TABLE_SCHEMA
            and t.TABLE_NAME = s.TABLE_NAME
        where t.TABLE_SCHEMA not in ({excluded})
            and t.TABLE_TYPE = 'BASE TABLE'
            and s.INDEX_NAME is null
        order by t.TABLE_SCHEMA, t.TABLE_NAME
        ;
    """  # nosec B608
    return lib.base.coe(lib.db_mysql.select(conn, sql))


def get_innodb_tables_without_pk(conn, excluded):
    # `NOT EXISTS` against statistics filtered to `INDEX_NAME = 'PRIMARY'`
    # catches InnoDB base tables where the admin did not define a primary key.
    # InnoDB synthesises a hidden 6-byte clustered index in that case, but
    # ROW-based replication needs the user-defined PK to apply row events
    # efficiently; without it the replica falls back to a full table scan
    # per row event.
    sql = f"""
        select t.TABLE_SCHEMA as table_schema,
            t.TABLE_NAME as table_name
        from information_schema.tables t
        where t.TABLE_SCHEMA not in ({excluded})
            and t.TABLE_TYPE = 'BASE TABLE'
            and t.ENGINE = 'InnoDB'
            and not exists (
                select 1
                from information_schema.statistics s
                where s.TABLE_SCHEMA = t.TABLE_SCHEMA
                    and s.TABLE_NAME = t.TABLE_NAME
                    and s.INDEX_NAME = 'PRIMARY'
            )
        order by t.TABLE_SCHEMA, t.TABLE_NAME
        ;
    """  # nosec B608
    return lib.base.coe(lib.db_mysql.select(conn, sql))


def get_base_tables(conn, excluded):
    # Returns the names instead of a `count(*)`, because `--match` and
    # `--ignore` are Python regexes and are evaluated here, not by the
    # server. The scan is a once-a-day check and the result set is one
    # row per base table.
    sql = f"""
        select TABLE_SCHEMA as table_schema,
            TABLE_NAME as table_name
        from information_schema.tables
        where TABLE_SCHEMA not in ({excluded})
            and TABLE_TYPE = 'BASE TABLE'
        ;
    """  # nosec B608
    return lib.base.coe(lib.db_mysql.select(conn, sql))


def compile_filters(args):
    """Compile the filter regexes once. lib.txt.compile_regex() returns a
    (success, result) tuple per pattern and names the parameter in its error
    message, so an invalid pattern exits UNKNOWN via lib.base.coe().
    """
    return {
        key: [lib.base.coe(item) for item in lib.txt.compile_regex(patterns, key)]
        for key, patterns in (
            ('--ignore', args.IGNORE),
            ('--ignore-schemas', args.IGNORE_SCHEMAS),
            ('--ignore-tables', args.IGNORE_TABLES),
            ('--match', args.MATCH),
        )
    }


def keep_table(schema, table, filters):
    """Decide whether a table survives the filters.

    `--match` (include) is applied first, then `--ignore` (exclude), so a
    table hit by `--ignore` is dropped even if it also matches `--match`.
    Both are matched against the fully qualified `schema.table` name, so a
    single pattern addresses either level.

    The deprecated `--ignore-schemas` / `--ignore-tables` are matched
    against the bare schema resp. table name, which is what they did while
    MySQL evaluated them via `NOT REGEXP`.
    """
    if any(item.search(schema) for item in filters['--ignore-schemas']):
        return False
    if any(item.search(table) for item in filters['--ignore-tables']):
        return False
    identifier = f'{schema}.{table}'
    if filters['--match'] and not any(
        item.search(identifier) for item in filters['--match']
    ):
        return False
    return not any(item.search(identifier) for item in filters['--ignore'])


def filter_tables(rows, filters):
    """Drop the rows whose `table_schema` / `table_name` the filters
    exclude.
    """
    return [
        row
        for row in rows
        if keep_table(row['table_schema'] or '', row['table_name'] or '', filters)
    ]


def format_table_list(rows, lengthy):
    """Render the per-table bullet list, truncated unless --lengthy."""
    qualified = [f'`{row["table_schema"]}`.`{row["table_name"]}`' for row in rows]
    if lengthy or len(qualified) <= LIST_LIMIT:
        shown = qualified
        suffix = ''
    else:
        shown = qualified[:LIST_LIMIT]
        suffix = (
            f'\n* ... and {len(qualified) - LIST_LIMIT} more'
            f' (use `--lengthy` to see the full list)'
        )
    return '\n'.join(f'* {q}' for q in shown) + suffix


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

    # logic taken from mysqltuner.pl:mysql_tables(), verified in sync with
    # MySQLTuner for the "table has no index" check. The InnoDB
    # "no PRIMARY KEY" check is a Linuxfabrik addition motivated by the
    # well-documented ROW-based-replication performance hotspot and is not
    # part of mysqltuner.

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

    # set default values for append parameters that were not specified
    if args.IGNORE is None:
        args.IGNORE = []
    if args.IGNORE_SCHEMAS is None:
        args.IGNORE_SCHEMAS = []
    if args.IGNORE_TABLES is None:
        args.IGNORE_TABLES = []
    if args.MATCH is None:
        args.MATCH = []

    # 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, 'SELECT'))

    excluded = ', '.join(f"'{s}'" for s in SYSTEM_SCHEMAS)

    no_index_rows = get_tables_without_any_index(conn, excluded)
    no_pk_rows = get_innodb_tables_without_pk(conn, excluded)
    base_table_rows = get_base_tables(conn, excluded)

    lib.db_mysql.close(conn)

    # init some vars
    filters = compile_filters(args)
    # Filter before counting, so the thresholds, the perfdata and the listed
    # tables all describe the same set of tables.
    no_index_rows = filter_tables(no_index_rows, filters)
    no_pk_rows = filter_tables(no_pk_rows, filters)
    total_tables = len(filter_tables(base_table_rows, filters))
    state = STATE_OK
    sections = []
    # All recommendations from all WARN/CRIT paths land here and render once
    # at the end as a `Recommendations:\n* ...` bulleted block.
    recommendations = []
    perfdata = ''

    no_index_count = len(no_index_rows)
    no_pk_count = len(no_pk_rows)

    # analyze data
    no_index_state = lib.base.get_state(
        no_index_count,
        args.WARNING,
        args.CRITICAL,
        _operator='range',
    )
    no_pk_state = lib.base.get_state(
        no_pk_count,
        args.WARNING,
        args.CRITICAL,
        _operator='range',
    )
    state = lib.base.get_worst(state, no_index_state, no_pk_state)

    # build the message
    facts = [
        f'{total_tables} base'
        f' {lib.txt.pluralize("table", total_tables)} across user schemas'
    ]
    if state == STATE_OK:
        sections.append('Everything is ok. ' + '. '.join(facts) + '.')
    else:
        sections.append('. '.join(facts) + '.')

    if no_index_count > 0:
        sections.append(
            f'{no_index_count}'
            f' {lib.txt.pluralize("table", no_index_count)}'
            f' without any index'
            f'{lib.base.state2str(no_index_state, prefix=" ")}:\n'
            f'{format_table_list(no_index_rows, args.LENGTHY)}'
        )
        recommendations.append(
            'Add at least a `PRIMARY KEY` (or any secondary index) on each '
            'of the listed tables. A heap table without any index forces '
            'every query against it into a full table scan'
        )

    if no_pk_count > 0:
        sections.append(
            f'{no_pk_count} InnoDB'
            f' {lib.txt.pluralize("table", no_pk_count)}'
            f' without a user-defined `PRIMARY KEY`'
            f'{lib.base.state2str(no_pk_state, prefix=" ")}:\n'
            f'{format_table_list(no_pk_rows, args.LENGTHY)}'
        )
        recommendations.append(
            'Add a `PRIMARY KEY` to each of the listed InnoDB tables. Without '
            'a user-defined `PRIMARY KEY`, InnoDB falls back to a hidden '
            '6-byte clustered index that ROW-based replication cannot use '
            'efficiently (worst case: full table scan per row event on the '
            'replica). Symptoms: replication lag, high CPU on replicas '
            'during writes'
        )

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

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

    perfdata += lib.base.get_perfdata(
        'mysql_total_tables',
        total_tables,
        _min=0,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_tables_without_index',
        no_index_count,
        warn=args.WARNING,
        crit=args.CRITICAL,
        _min=0,
    )
    perfdata += lib.base.get_perfdata(
        'mysql_innodb_tables_without_primary_key',
        no_pk_count,
        warn=args.WARNING,
        crit=args.CRITICAL,
        _min=0,
    )

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


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