#!/usr/bin/env python3
# -*- coding: utf-8 -*-

##############################################################################
# myth-delete-missing
#
# Delete database entries for recordings where there is no recording file.
# Optionally set the recording to re-record if there is an existing recording
# rule for it.  Optionally create a "record one episode" recording rule if
# there is no existing recording rule.  SQL can be used to select which
# recordings to look at to see if the recording file is missing.
#
# Author: J S Worthington
#
##############################################################################

import argparse
import logging
import logging.handlers
import MythTV
import os
import sys
import MythTV.services_api.send as api
import MythTV.services_api.utilities as util

try:
    import requests
except ImportError:
    exit('Install python3-requests')

extra_help = '''
Notes:

1) This program has only been tested on MythTV v32.
2) Warning: Beta software - please test with care.  If you have problems,
   please email me or post on the MythTV users mailing list.  Take a copy
   of all console output (especially any tracebacks), and a copy of the
   myth-delete-missing.py.log file.
3) Due to lack of time I have not yet got the 'drc' action working - the code
   to add a 'record once' recording rule does not work yet.  So until it does
   any recording table entries without matching recording files that also do
   not have a matching record table entry will not be deleted.

How to use:

Run this program first with only the 'l' (list) action (or no action, which
defaults to 'l').  Check the output to see what deleting would be done and
make sure that it will be doing what you want.  If there are any recordings
that will not be handled as you would like, exclude them by using some SQL
with the -s or -f option, and try the 'l' action again to check the results.

When you are ready to actually try doing the deletes, DO A FULL DATABASE
BACKUP FIRST (using mythconverg_backup.pl), and make sure you know where to find
the backup file.  Choose a time when mythbackend will not be busy for a long
enough time for you to do a backup, try the deletes, and then if necessary
restore the database using mythconverg_restore.pl.  You can use my gaps program
to find a large enough gap:

http://www.jsw.gen.nz/mythtv/gaps

On Ubuntu, a restore command looks something like this:

/usr/share/mythtv/mythconverg_restore.pl --drop_database --create_database --directory=/usr/lib/mythtv/db_backups --filename=<name of backup file>

Some example SQL phrases to use with the -s or -f options:

   Exclude specific recordings by recordedid:
      where not recordedid in (12345, 78901, 19007)

   Only delete one specific recording:
      where recordedid=77863

   Only delete where the title matches a search string:
      where title like '%who do you think you are%'

   Only delete recordings earlier than 2018-17-06 UTC:
      where starttime<'2018-17-06 00:00'

   Only delete recordings earlier than 2018-17-06 MST
      where starttime<convert_tz('2018-17-06 00:00', 'MST', 'UTC')

   Limit the number of recordings scanned:
      limit 10

If you have problems with quotes in your SQL using the -s option, use -f instead.

Dates and times in the mythconverg database are in UTC, so you need to use UTC
times in your SQL, or convert times to UTC.  Dates and times displayed by the
program are converted to local time for your convenience, so you can not just
copy them, and would need to convert the local time to UTC to use it in SQL.

The recordedid values reference rows in the recorded table (the list of recordings).
The recordid values reference rows in the record table (the list of recording rules).

'''


##############################################################################
# Version 0.1 2023-01-11
#   Initial version.  Only list action works.
# Version 0.2 2023-01-21
#   Added delete ('d') and delete and re-record ('dr') actions.
#   The 'drc' action is not working yet.
#   Lots more help.
#   Fixed help formatting.
##############################################################################

VERSION = '0.2'
PROGRAM_NAME = os.path.basename(sys.argv[0])


##############################################################################
# Configuration
##############################################################################

# Default logging level.  Log level used for output to the log file.
LOGGING_LEVEL = 'debug'
#LOGGING_LEVEL = 'info'

# Default console logging level.  Logging level used for output to the console.
CONSOLE_LEVEL = 'info'


##############################################################################
# Convert a loglevel string to a loglevel value.
#
# No error checking is done as the 'level' value is assumed to have been
# created using argparse from a valid list of levels.
##############################################################################

def _str_to_loglevel(level):
    return {
        'none':     logging.NOTSET,
        'debug':    logging.DEBUG,
        'info':     logging.INFO,
        'warning':  logging.WARNING,
        'error':    logging.ERROR,
        'critical': logging.CRITICAL
        }[level]


##############################################################################
# Main
##############################################################################

class SmartFormatter(argparse.RawDescriptionHelpFormatter):
    def _split_lines(self, text, width):
        if text.startswith('R|'):
            return text[2:].splitlines(False)  
        return argparse.RawDescriptionHelpFormatter._split_lines(self, text, width)

parser = argparse.ArgumentParser(
    description='Delete MythTV database entries for recordings with missing files and optionally re-record (Version: ' + VERSION + ')',
    formatter_class=lambda prog: SmartFormatter(prog, max_help_position=15, width=80),
    epilog = extra_help
    )
sql_group = parser.add_mutually_exclusive_group()
parser.add_argument('-b', '--batch', dest='batch_mode', default=False, action='store_true',
    help='Batch mode - do not ask before doing any action')
parser.add_argument('-c', '--consolelevel', default=CONSOLE_LEVEL, choices=['debug', 'info', 'warning', 'error', 'critical'],
    help='Sets the console logging level (default: ' + CONSOLE_LEVEL + ')')
parser.add_argument('--host', default='localhost', dest='host', action='store',
    help='Set the host address of the MythTV backend server.  Default=localhost.  IP address or DNS name.  IPv6 allowed.')
parser.add_argument('-l', '--loglevel', default=LOGGING_LEVEL, choices=['none', 'debug', 'info', 'warning', 'error', 'critical'],
    help = 'Sets the logging level in the log file (default: ' + LOGGING_LEVEL + ')')
parser.add_argument('--port', default='6544', dest='port', action='store',
    help='Set the port number on the MythTV backend server.  Default=6544')
sql_group.add_argument('-s', '--sql', default='', dest='sql', action='store',
    help='SQL to select recordings.  Default=\'\'.  Will be concatenated on to this: \'select recordedid from recorded \'.')
sql_group.add_argument('-f', '--sql-file', default='', dest='sql_file', action='store',
    help='File to read containing the SQL to select recordings.  The file must always start with \'select recordedid from recorded \'.')
parser.add_argument('-v', '-V', '--version', dest='display_version', default=False, action='store_true',
    help='Display the program name and version and exit')
parser.add_argument('action', nargs='?', default='l',
    choices=['list', 'l', 'delete-only', 'd', 'delete-and-re-record', 'dr', 'delete-and-re-record-create-rules', 'drc'],
    help='R|l, list (default)\n'
        '    List recordings with missing files - do not delete or re-record.\n'
        'd, delete-only\n'
        '    Delete the database entries for missing files, but do not re-record.\n'
        'dr, delete-and-re-record\n'
        '    Delete the databse entries for missing files and set the programmes to re-record.\n'
        '    Does not create recording rules if one does not already exist.\n'
        'drc, delete-and-re-record-create-rules\n'
        '    Delete the database entries for missing files and set the programmes to re-record\n'
        '    if an existing recording rule is found.  If there is no existing recording rule, a\n'
        '    \'Record one episode\' recording rule will be created for the programme.\n'
    )


args = parser.parse_args()

if args.display_version:
    print(PROGRAM_NAME + ' version ' + VERSION)
    exit(0)

loglevel = _str_to_loglevel(args.loglevel)
consolelevel = _str_to_loglevel(args.consolelevel)

# Initialise logging.
logger = logging.getLogger(PROGRAM_NAME)
logger.setLevel(min(loglevel, consolelevel))

if loglevel != logging.NOTSET:
    # Log file for all output messages.
    logfile = PROGRAM_NAME + '.log'

    try:
        fh = logging.FileHandler(
            logfile,
            mode='w'
            )
    except IOError as e:
        if e.errno == 13:
            print('Exception opening log file ' + logfile + ' (errno 13: permission denied)')
        else:
            raise
        exit(1)

    fh_formatter = logging.Formatter(
        '%(asctime)s %(levelname)s %(message)s',
        datefmt='%Y-%m-%d %H:%M:%S'
        )
    fh.setFormatter(fh_formatter)
    logger.addHandler(fh)
    fh.setLevel(loglevel)

ch = logging.StreamHandler(stream=sys.stdout)
ch_formatter = logging.Formatter(
    '%(message)s'
    )
ch.setFormatter(ch_formatter)
logger.addHandler(ch)
ch.setLevel(consolelevel)

logger.debug(f'{args=}')
logger.debug(f'{loglevel=}')
logger.debug(f'{consolelevel=}')
logger.debug(f'{args.host=}')
logger.debug(f'{args.sql=}')

if args.action == 'list':
    args.action = 'l'
elif args.action == 'delete-only':
    args.action = 'd'
elif args.action == 'delete-and-re-record':
    args.action = 'dr'
elif args.action == 'delete-and-re-record-create-rules':
    args.action = 'drc'


# Initialise the Services API connection to mythbackend.
backend = api.Send(host=args.host, port=args.port)

# Initialise the UTCOffset so that the conversion to local time works.
util.get_utc_offset(backend)

# Initialise the Python bindings for direct access to the database.
db = MythTV.MythDB(DBHostName=args.host)
dbc = db.cursor()

try:
    endpoint='Dvr/GetRecordSchedule'
    rest="Template='Default'"
    default_rule = backend.send(endpoint=endpoint, rest=rest)
except RuntimeError as e:
    logger.debug(f'{endpoint=} {rest=} Exception {e=}')
    logger.critical(f'Unable to connect to {args.host}:{args.port}, aborting')
    exit(2)

if default_rule.get('RecRule') == None:
    logger.critical('Error getting Default template rule, aborting')
    exit(2)
default_rule = default_rule.get('RecRule')
logger.debug(f'{default_rule=}')

if args.sql == '':
    if args.sql_file == '':
        sql = 'select recordedid from recorded order by recordedid'
    else:
        with open(args.sql_file, 'r') as file:
            sql = file.read().replace('\n', ' ')
else:
    sql = 'select recordedid from recorded ' + args.sql
logger.debug(f'{sql=}')
rows = dbc.execute(sql)
logger.debug(f'{rows=}')

recording_list = []

while rows != 0:
    recordedid = dbc.fetchone()[0]
    #logger.debug(f'{recordedid=}')

    url = f'http://{args.host}:{args.port}/Content/GetRecording?Recordedid={recordedid}'
    try:
        r = requests.head(url)
    except requests.exceptions.ConnectionError:
        logger.critical(f'Unable to connect to {args.host}:{args.port}, aborting')
        exit(2)
    if r.status_code == 404:
        logger.info(f'Recording file not found for recordedid {recordedid}')
        recording_list.append(recordedid)
    elif r.status_code == 200:
        logger.debug(f'Recording file found for recordedid {recordedid}')
    else:
        logger.critical(f'Unhandled HTTP response {r.status_code} received for url {url}, aborting')
        exit(2)
    rows -= 1

deleting_list = []
for recordedid in recording_list:
    logger.debug(f'Processing recordedid {recordedid}')
    sql = f'select chanid, title, starttime, recgroup, recordid from recorded where recordedid={recordedid}'
    rows = dbc.execute(sql)
    if rows != 1:
        logger.critical(f'Error reading recording data for recordedid {recordedid}')
        exit(2)
    chanid, title, starttime, recgroup, recordid = dbc.fetchone()
    starttime = starttime.isoformat()
    if starttime[10:11] == 'T':
        starttime = starttime[0:10] + ' ' + starttime[11:]
    if recgroup == 'LiveTV':
        logger.info(f'LiveTV: {recordedid=} {chanid=} {title=} starttime={util.utc_to_local(starttime)} {recordid=}')
        # Only delete LiveTV recordings with no recording file - they can not be re-recorded.
        deleting_list.append([recordedid, chanid, title, starttime, recordid, 'd'])
    else:
        logger.info(f'{recordedid=} {chanid=} {title=} starttime={util.utc_to_local(starttime)} {recordid=}')
        rows = dbc.execute(f'select count(*) from record where recordid={recordid}')
        if rows != 1:
            logger.critical('Database error finding recording rule, aborting')
            exit(2)
        if dbc.fetchone()[0] == 0 and args.action == 'drc':
            # No recording rule found, need to create a new one.
            # Do not add these recordings to the deleting_list until the 'drc' action is working.
            #deleting_list.append([recordedid, chanid, title, starttime, recordid, 'drc'])
            logger.info(f'{recordedid=} {chanid=} {title=} starttime={util.utc_to_local(starttime)} {recordid=} will not be deleted as drc not working yet.')
        else:
            deleting_list.append([recordedid, chanid, title, starttime, recordid, args.action[0:2]])

if args.action == 'l':
    exit(0)

if args.batch_mode == False:
    logger.debug('Asking permission to proceed')
    while True:
        reply = input('Proceed with deletions (y/N) ? ')
        if len(reply) != 1:
            continue
        if not reply in ['y', 'Y', 'n', 'N']:
            continue
        if reply in ['n', 'N']:
            exit(0)
        break

logger.info('Proceeding with deletes')
for recordedid, chanid, title, starttime, recordid, action in deleting_list:
    if action == 'drc':
        # Create a record once recording rule.
        rule = default_rule
        params_not_sent = ('AverageDelay', 'CallSign', 'Id', 'LastDeleted', 'NextRecording',
            'StartTime', 'EndTime')
        for param in params_not_sent:
            try:
                del rule[param]
            except KeyError as e:
                logger.debug(f'KeyError: {e=}')
                #pass
        rule['Type'] = 'Record One'
        #rule['Type'] = 'Single Record'
        rule['Title'] = title
        rule['ParentId'] = 0
        rule['MakeOverride'] = 'false'
        response = backend.send(endpoint='Dvr/AddRecordSchedule', postdata=rule, opts={'wrmi': True})
        if response != {'bool': 'true'}:
            logger.critical(f'AddRecordSchedule failed for {recordedid=} {title=}: {response=}, aborting')
            exit(2)
        logger.info(f'Created record once rule: {title=}')
    postdata = {'RecordedId': recordedid, 'ForceDelete': True}
    if action[0:2] == 'dr':
        postdata['AllowRerecord'] = True
    try:
        response = backend.send(endpoint='Dvr/DeleteRecording', postdata=postdata, opts={'wrmi': True})
    except RuntimeError as e:
        logger.info(f'Exception while trying to delete recording {recordedid=}')
        logger.debug(f'Exception while trying to delete recording: {e=}')
        exit(2)
    if response != {'bool': 'true'}:
        logger.critical(f'DeleteRecording failed {response=}, aborting')
        exit(2)
    logger.info(f'Deleted: {recordedid=} {chanid=} {title=} starttime={util.utc_to_local(starttime)} {recordid=} {action=}')