import sys
import pymysql
sys.path.append('/var/local/mode2/')
from functions.mwDB import get_settings


MYSQL_SETTINGS_PATH = "/var/tmp/waf_settings"
LIMIT = 2000000

def main():
    delete_log()
    delete_over_limit_log()

def delete_log():
    try:
        settings = get_settings(MYSQL_SETTINGS_PATH)
        connection = pymysql.connect(
            host='localhost',
            user=settings["mysql_user"],
            password=settings["mysql_password"],
            database='mod_security'
        )

        with connection.cursor() as cursor:
            sql = f'DELETE FROM audit_log WHERE access_date < DATE_SUB(NOW(),INTERVAL 1 YEAR)'
            cursor.execute(sql)
            connection.commit()

    except pymysql.Error as e:
        print("Error while connecting to MySQL", e)

    finally:
        if connection:
            connection.close()


def delete_over_limit_log():
    try:
        settings = get_settings(MYSQL_SETTINGS_PATH)
        connection = pymysql.connect(
            host='localhost',
            user=settings["mysql_user"],
            password=settings["mysql_password"],
            database='mod_security'
        )

        with connection.cursor() as cursor:
            sql = f'SELECT COUNT(*) - {LIMIT} FROM audit_log'
            cursor.execute(sql)
            cnt = cursor.fetchall()[0][0]

            if cnt <= 0:
                return

            sql = f'DELETE FROM audit_log '
            sql += f'WHERE '
            sql += f'	audit_id IN (SELECT audit_id FROM (SELECT audit_id FROM audit_log ORDER BY access_date ASC LIMIT {cnt}) audit_log)'
            cursor.execute(sql)
            connection.commit()

    except pymysql.Error as e:
        print("Error while connecting to MySQL", e)

    finally:
        if connection:
            connection.close()


if __name__ == "__main__":
    main()