# coding=utf8
# the above tag defines encoding for this document and is for Python 2.x compatibility

import re
import sys
import pprint
import MySQLdb
import json
import datetime
from sys import stdin

debug = 0

def print_debug( match ):

        for groupNum in range(0, len(match.groups())):
                groupNum = groupNum + 1

                print ("Group {groupNum} found at {start}-{end}: {group}".format(groupNum = groupNum, start = match.start(groupNum), end = match.end(groupNum), group = match.group(groupNum)))

def parse_clientstr( record ):

        if debug >= 1:
                print("Clientstring = " + record["clientstr"])

        # AppleCoreMedia
        regex = "AppleCoreMedia.*?\((.*?);.*\)"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = match.group(1)
                record["client"] = 'AppleCoreMedia'
                return( record )

        # AntennaPod
        regex = "AntennaPod.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'AntennaPod'
                return( record )

        # Blubrry PowerPress
        regex = "Blubrry PowerPress.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'Blubrry PowerPress'
                return( record )

        # CastBox
        regex = "CastBox.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'CastBox'
                return( record )

        # Downcast
        regex = "Downcast.*?\((.*?);.*\)"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = match.group(1)
                record["client"] = 'Downcast'
                return( record )

        # Feedreader
        regex = "Feedreader.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'Feedreader'
                return( record )

        # iTMS
        regex = "iTMS.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'iTunes'
                return( record )

        # iTunes
        regex = "iTunes.*?\((.*?);.*\)"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = match.group(1)
                record["client"] = 'iTunes'
                return( record )

        # iTunes
        regex = "iTunes.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'iTunes'
                return( record )

        # itunesstored
        regex = "itunesstored.*?model/(.*?),.*\)"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = match.group(1)
                record["client"] = 'iTunes'
                return( record )

        # MixcloudPodcastImporter
        regex = "MixcloudPodcastImporter.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'Mixcloud'
                return( record )

        # Mozilla
        regex = "Mozilla.*?\((.*?);.*\)"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = match.group(1)
                record["client"] = 'Mozilla'
                return( record )

        # okhttp
        regex = "okhttp.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'Android Podcast App'
                return( record )

        # Overcast
        regex = "Overcast.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = "iOS";
                record["client"] = 'Overcast'
                return( record )

        # Player FM
        regex = "Player FM.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'Player FM'
                return( record )

        # Pocket Casts
        regex = "Pocket Casts.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'Pocket Casts'
                return( record )

        # PodcastAddict
        regex = "PodcastAddict.*?\(Linux; U; (.*?);.*\)"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = match.group(1)
                record["client"] = 'PodcastAddict'
                return( record )

        # Podkicker Pro
        regex = "Podkicker Pro.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'Podkicker Pro'
                return( record )

        # RSSRadio
        regex = "RSSRadio.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'RSSRadio'
                return( record )

        # stagefright
        regex = "stagefright.*?\(Linux;(.*?).*\)"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = match.group(1)
                record["client"] = 'stagefright'
                return( record )

        # Stitcher
        regex = "Stitcher/(.*?)"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = match.group(1)
                record["client"] = 'Stitcher'
                return( record )

        # StitcherBot
        regex = "StitcherBot.*"
        match = re.match(regex, record["clientstr"])
        if match != None:
                record["os"] = 'None'
                record["client"] = 'Stitcher'
                return( record )

        return( record )

def parse_logstr( source, logstr ):

        global Unmatched

        if debug >= 1:
                print( logstr )

        regex = r"(\d*\.\d*\.\d*\.\d*) - - \[(\d*)/(\w*)/(\d*):(\d*):(\d*):(\d*) \+(\d*)\] \"GET /roaringelephant/(RoaringElephant_Episode_\d*).mp3.*? HTTP/(.*)\" (\d*) (\d*) \".*\" \"(.*)\""

        match = re.match(regex, logstr)

        if match != None:

                if debug >= 1:
                        print_debug( match )

                #Group 1 found at 0-14: 188.221.70.222
                #Group 2 found at 20-22: 10
                #Group 3 found at 23-26: May
                #Group 4 found at 27-31: 2017
                #Group 5 found at 32-34: 07
                #Group 6 found at 35-37: 01
                #Group 7 found at 38-40: 47
                #Group 8 found at 42-46: 0200
                #Group 9 found at 70-96: RoaringElephant_Episode_42
                #Group 10 found at 106-109: 1.1
                #Group 11 found at 111-114: 206
                #Group 12 found at 115-118: 474
                #Group 13 found at 124-199: AppleCoreMedia/1.0.0.14E304 (iPhone; U; CPU OS 10_3_1 like Mac OS X; en_gb)

                months = {'Jan': 1,'Feb': 2, 'Mar': 3, 'Apr': 4, 'May': 5, 'Jun': 6, 'Jul':7, 'Aug': 8, 'Sep': 9, 'Oct': 10, 'Nov': 11, 'Dec': 12}

                dt = datetime.datetime( int(match.group(4)), months[match.group(3)], int(match.group(2)), int(match.group(5)), int(match.group(6)), int(match.group(7)) )

                epnr = match.group(9).split('_')[2]

                record = {
                        'source': source,
                        'ip': match.group(1),
                        'datetime': dt - datetime.timedelta( hours=int(match.group(8))/100 ),
                        'filename': match.group(9),
                        'episodenr': epnr,
                        'httpver': match.group(10),
                        'httpcode': match.group(11),
                        'size': match.group(12),
                        'clientstr': match.group(13),
                        'os': 'other',
                        'client': 'other'
                }

                if debug >= 2:
                        pp = pprint.PrettyPrinter(indent=4)
                        pp.pprint(record)

                return parse_clientstr( record )

        else:
                Unmatched = Unmatched + 1
                if debug >= 1:
                        print( "No match" )

                return None

def save( record ):

        global Added
        global Duplicates

        if record != None:

                placeholders = ', '.join(['%s'] * len(record))
                columns = ', '.join(record.keys())
                sql = "INSERT INTO %s ( %s ) VALUES ( %s )" % ('logs', columns, placeholders)

                try:
                        cursor.execute(sql, record.values())
                        Added = Added + 1
                except MySQLdb.IntegrityError:
                        Duplicates = Duplicates + 1
                        if debug >= 1:
                                print ('duplicate %s %s %s', record['datetime'], record['ip'], record['filename'])

                db.commit()


if __name__ == "__main__":

        Unmatched = 0
        Duplicates = 0
        Skipped = 0
        Added = 0
        Debugged = 0

        db = MySQLdb.connect(host="dbserver.anset.org",user="mediastats_user", passwd="2P37E51fMgtDUf599gG6",db="mediastats")
        cursor = db.cursor()

        if debug >= 9:
                debug_bad_str =  '''188221.70.222 - - [10/May/2017:07:01:47 +0200] "GET /roaringelephant/RoaringElephant_Episode_42.mp3 HTTP/1.1" 206 474 "-" "AppleCoreMedia/1.0.0.14E304 (iPhone; U; CPU OS 10_3_1 like Mac OS X; en_gb)"'''
                debug_good_str = '''188.221.70.222 - - [10/May/2017:07:01:47 +0200] "GET /roaringelephant/RoaringElephant_Episode_42.mp3 HTTP/1.1" 206 474 "-" "AppleCoreMedia/1.0.0.14E304 (iPhone; U; CPU OS 10_3_1 like Mac OS X; en_gb)"'''

                parse_logstr( "0", debug_bad_str)
                parse_logstr( "0", debug_good_str)
        else:

                sql = 'select max(datetime) from logs'
                cursor.execute(sql)
                result = cursor.fetchall()

                offset = result[0][0]
                offset = None

                for line in stdin:

                        if debug >= 1:
                                print("\n----------------------------------------------------------------------------------------------------------------------------------------")
                        record = parse_logstr('3', line)

                        if record != None:

                                if  debug >= 1:
                                        print("Found Client = " + record["client"])

                                if offset == None or record['datetime'] >= offset:
                                        if debug >= 1:
                                                print("Skipping save due to debug flag..")
                                                Debugged = Debugged + 1
                                        else:
                                                save( record )
                                else:
                                        Skipped = Skipped + 1
                                        if debug >= 1:
                                                print('skipping %s %s %s', record['datetime'], record['ip'], record['filename'])

        db.commit()
        cursor.close()
        db.close()

        print (str(datetime.datetime.now()) + " [SKIP: " + str(Unmatched) + "][DUPS: " + str(Duplicates) + "][ADDS: " + str(Added) + "][DBG: " + str(Debugged) + "][TOTP: " + str(Added+Duplicates+Skipped+Unmatched+Debugged) + "]")