#!/usr/bin/env python3
# -*- coding: utf-8 -*-
# Copyright (C) 2020 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.

import datetime
from typing import Any, Mapping, NamedTuple, Optional

# Sample output:

# <<<mssql_jobs:sep(09)>>>
# MSSQLSERVER	{E86617B1-A9B2-4443-8916-312AB1F526DE}	syspolicy_purge_history	1	20200821	20000	5		0	0	0	1	2020-08-21 07:54:34

# Sample error:
# MSSQLSERVER ERROR: Incorrect syntax near the keyword 'LEFT'. (SQLState: 42000/NativeError: 156). Incorrect syntax near 'sjh'. (SQLState: 42000/NativeError: 102).

# Database documentation:
# https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/dbo-sysjobs-transact-sql?view=sql-server-ver15


factory_settings["mssql_jobs_defaults"] = {
    "consider_job_status": "ignore",
    "status_disabled_jobs": 0,
    "status_missing_jobs": 2,
}

DATETIME_FMT = "%Y-%m-%d %H:%M:%S"


class JobSpec(NamedTuple):
    last_run_duration: Optional[float]
    last_run_outcome: str
    last_run_datetime: str
    enabled: bool
    schedule_enabled: bool
    next_run_datetime: str
    last_outcome_message: str


def _calculate_seconds(raw_duration: str) -> Optional[float]:
    """Return the number of seconds from a string in HHMMSS format.
    The strings are of variable length, e.g. 124 = 1 minute 24 seconds.

    >>> _calculate_seconds('0')
    0.0

    >>> _caluclate_seconds('3'):
    3.0

    >>> _caluclate_seconds('124'):
    84.0

    >>> _calculate_seconds('12314'):
    4994.0

    """

    if not (length := len(raw_duration)):
        return None

    if length <= 2:
        return float(raw_duration)

    if length <= 4:
        raw_seconds = float(raw_duration[-2:])
        raw_minutes = float(raw_duration[:-2])
        return raw_seconds + raw_minutes * 60

    raw_seconds = float(raw_duration[-2:])
    raw_minutes = float(raw_duration[-4:-2])
    raw_hours = float(raw_duration[:-4])

    return raw_seconds + raw_minutes * 60 + raw_hours * 3600


def _format_to_datetime(raw_date: str, raw_time: str) -> str:
    """Return a valid datetime from date and time strings in YYYYMMDD and HHMMSS formats respectively.
    The date and/or time may be set to '0'. The time may be of variable length, e.g. 300 = 00:03:00.

    >>> _format_to_datetime('20200821', '132400')
    '2020-08-21 13:24:00'

    >>> _format_to_datetime('20200821', '300')
    '2020-08-21 00:03:00'

    >>> _format_to_datetime('20200821', '0')
    '2020-08-21 00:00:00'

    >>> _format_to_datetime('0', '0')
    'N/A'

    """
    if raw_date in ["0", ""]:
        return "N/A"

    source_datetime = datetime.datetime.strptime(
        raw_date + " " + raw_time.zfill(6),
        "%Y%m%d %H%M%S",
    )

    return datetime.datetime.strftime(source_datetime, DATETIME_FMT)


def parse_mssql_jobs(info):

    col_headers = [
        "job_id",
        "job_name",
        "job_enabled",
        "next_run_date",
        "next_run_time",
        "last_run_outcome",
        "last_outcome_message",
        "last_run_date",
        "last_run_time",
        "last_run_duration",
        "schedule_enabled",
        "server_current_time",
    ]
    outcome_translation = {
        "": "N/A",
        "0": "Fail",
        "1": "Succeed",
        "2": "Retry",
        "3": "Cancel",
        "4": "In progress",
        "5": "Unknown",
    }
    parsed = {}

    for line in iter(info):
        if len(line) == 1:
            continue
        # the job output may contain more than one line per job, depending on how often
        # it is scheduled. the user is only interested in the most immediate next run time
        if line[1] in parsed:
            continue
        job = dict(zip(col_headers, line))

        parsed[job["job_name"]] = JobSpec(
            last_run_duration=_calculate_seconds(job["last_run_duration"]),
            last_run_outcome=outcome_translation[job["last_run_outcome"]],
            last_run_datetime=_format_to_datetime(
                job["last_run_date"],
                job["last_run_time"],
            ),
            enabled=job["job_enabled"] not in ("", "0"),
            schedule_enabled=job["schedule_enabled"] not in ("", "0"),
            next_run_datetime=_format_to_datetime(
                job["next_run_date"],
                job["next_run_time"],
            ),
            last_outcome_message=job["last_outcome_message"],
        )

    return parsed


def discover_mssql_jobs(section: Mapping[str, JobSpec]):
    for job_name in section:
        yield job_name, {}


def check_mssql_jobs(item: str, params: Mapping[str, Any], section: Mapping[str, JobSpec]):

    if (job_specs := section.get(item)) is None:
        return params["status_missing_jobs"], "Job not found"

    status_mapping = {
        "Fail": 2,
        "Succeed": 0,
        "Retry": 0,
        "Cancel": 0,
        "In progress": 0,
        "Unknown": 3,
    }

    if job_specs.last_run_duration is not None:
        yield check_levels(
            value=job_specs.last_run_duration,
            dsname="database_job_duration",
            params=params.get("run_duration"),
            human_readable_func=get_age_human_readable,
            infoname="Last duration",
        )

    db_status = (
        status_mapping[job_specs.last_run_outcome]
        if params["consider_job_status"] == "consider"
        or (params["consider_job_status"] == "consider_if_enabled" and job_specs.enabled)
        else 0
    )

    yield db_status, "MSSQL status: %s" % job_specs.last_run_outcome

    yield 0, "Last run: %s" % job_specs.last_run_datetime

    status_disabled_jobs = params["status_disabled_jobs"]
    if not job_specs.enabled:
        yield status_disabled_jobs, "Job is disabled"
    elif not job_specs.schedule_enabled:
        yield status_disabled_jobs, "Schedule is disabled"
    else:
        yield 0, "Next run: %s" % job_specs.next_run_datetime

    yield 0, "\nOutcome message: %s" % job_specs.last_outcome_message


check_info["mssql_jobs"] = {
    "parse_function": parse_mssql_jobs,
    "inventory_function": discover_mssql_jobs,
    "check_function": check_mssql_jobs,
    "service_description": "MSSQL Job: %s",
    "group": "mssql_jobs",
    "has_perfdata": True,
    "default_levels_variable": "mssql_jobs_defaults",
}
