# stuff to fix to move it into actual production
'''
1. Change app.config to FALSE
2. Take out all passwords and database names and all important variables into a separate, more secure file
3. Clear all print statements except generation and sent and errors
4. change the fetchmany(x) to fetchall()
''' 

# import statements
import sys
import traceback
import urllib
import mysql.connector
import xlsxwriter
import time
from datetime import date
import pandas as pd
import flask
import requests
from celery.app import task
from flask import request, send_from_directory, abort, Flask
from zipfile import ZipFile
import yagmail
import os
import logging
from celery import Celery
import json
import numpy as np

# to run the shell file, the command is sh run-redis.sh
# the variables
database = "hellio_archive"
emaildb = "hellio"
dbuser = "kwesi"
dbpaswd = "Ju3#95ghauqK0%L"
dbhost = "176.9.1.26"
port = 3117
DOWNLOAD_DIRECTORY = "/Users/paapakwesiquansah/PycharmProjects/pythonProject"
EMAIL_PASSWORD = 'thisistheemailpassword'
REPORT_EMAIL = "reports.npontu@gmail.com"

appCel = Celery('tasks', broker='localhost')
app = Flask(__name__)
app.config["DEBUG"] = True

#Setting Up Celery
def make_celery(app):
    celery = Celery(
        app.import_name,
        backend=app.config['result_backend'],
        broker=app.config['CELERY_BROKER_URL'],
        CELERY_ROUTES={"task_queue": {"queue": "task_queue_for_delay"}}
    )
    celery.conf.update(app.config)

class ContextTask(celery.Task):
        def _call_(self, *args, **kwargs):
            with app.app_context():
                return self.run(*args, **kwargs)

    celery.Task = ContextTask
    return celery

app.config.update(
    CELERY_BROKER_URL='amqp://localhost//',
    result_backend='rpc://'
)
celery = make_celery(app)
    
# build a dailyGenerator function
def dailyGeneratorUSERNAME(startDate, username, zipObj):
    # usernameORjobid shoudl replace username
    try:
        connection = mysql.connector.connect(host=dbhost, database=database, user=dbuser, password=dbpaswd, port=port)

        # monthYear - generate the string for the actualQueryMonth
        months = ['jan', 'feb', 'march', 'april', 'may', 'june', 'july', 'aug', 'sept', 'oct', 'nov', 'dec']
        monthName = int(startDate[2:4])
        monthYear = months[monthName - 1] + "_" + startDate[4:8]

        queryTemplate = startDate[4:8] + '-' + startDate[2:4] + '-' + startDate[0:2]
        actualQueryStart = queryTemplate + 'T00:00:00.00'  # '2021-04-25T00:00:00.00'
        actualQueryEnd = queryTemplate + 'T23:59:59.999'  # '2021-04-25T23:59:59.999'
        actualQueryMonth = monthYear + '_logs'  # month_year_logs

        # can use this to define exact users you want their data
        sql_select_Query = "SELECT * FROM " + actualQueryMonth + " WHERE username = '" + username + "' AND " \
                                                                                                    "submit_date " \
                                                                                                    "BETWEEN '" + \
                           actualQueryStart + "' AND '" + actualQueryEnd + "' "

        cursor = connection.cursor()
        cursor.execute(sql_select_Query)

        # get like all records for that day
        start = time.time()
        # this code to define how many records you want. fetchall() to get all, fetchmany(c) to get "c" number of records and fetchone() to get one
        records = cursor.fetchall()
        end = time.time()

        print("Time taken to get all records for the day is ", end - start, "and the number of rows is ", cursor.rowcount)

        nameOfExcel = username + '-report-' + startDate[0:2] + '-' + startDate[2:4] + '-' + startDate[4:8]
        # ('nationwideins-report-25-04-2021')

        excelName = nameOfExcel + '.xlsx'
        workbook = xlsxwriter.Workbook(excelName)
        worksheet = workbook.add_worksheet("Report for Day")

        # can use this to define exact columns you want in the file
        worksheet.write('A1', 'id')
        worksheet.write('B1', 'network')
        worksheet.write('C1', 'msisdn')
        worksheet.write('D1', 'sender')
        worksheet.write('E1', 'message')
        worksheet.write('F1', 'sms_count')
        worksheet.write('G1', 'status')
        worksheet.write('H1', 'submit_date')
        worksheet.write('I1', 'delivery_date')

        x = 0
        for row in records:
            aCellIndex = "A" + str(x + 2)
            worksheet.write(aCellIndex, row[0])

            bCellIndex = "B" + str(x + 2)
            worksheet.write(bCellIndex, row[6])

            cCellIndex = "C" + str(x + 2)
            worksheet.write(cCellIndex, row[5])

            dCellIndex = "D" + str(x + 2)
            worksheet.write(dCellIndex, row[7])

            eCellIndex = "E" + str(x + 2)
            worksheet.write(eCellIndex, row[9])

            fCellIndex = "F" + str(x + 2)
            worksheet.write(fCellIndex, row[10])

            gCellIndex = "G" + str(x + 2)
            worksheet.write(gCellIndex, row[13])

            # make the dates format well instead of nonsense floats
            hCellIndex = "H" + str(x + 2)
            timeH = row[11].strftime('%Y-%m-%d %H:%M:%S')
            worksheet.write(hCellIndex, timeH)
            
            if (row[12]):
                iCellIndex = "I" + str(x + 2)
                timeI = row[12].strftime('%Y-%m-%d %H:%M:%S')
                worksheet.write(iCellIndex, timeI)
            else:
                worksheet.write("Unavailable")

            x = x + 1

        workbook.close()

        if cursor.rowcount >= 1000000:
            # pass into function which will split and write into the zip file
            splitFunc(zipObj, excelName)
        else:
            zipObj.write(excelName)

        print("Excel File for Day", startDate, "generated")

        if os.path.exists(excelName):
            os.remove(excelName)
            print("Excel File for Day", startDate, "deleted")
        else:
            print("The file does not exist")

    except mysql.connector.Error as e:
        print("Error reading data from MySQL table", e)
    finally:
        connection = mysql.connector.connect(host=dbhost, database=database, user=dbuser, password=dbpaswd, port=port)
        cursor = connection.cursor()

        if connection.is_connected():
            connection.close()
            cursor.close()
            # print("MySQL connection is closed")


def splitFunc(zipObj, excelName):
    #splits the excel file and writes into the zip file
    chunksize = 999999
    df = pd.read_excel(excelName)

    i=1
    for chunk in np.split_array(df, len(df) // chunksize):
        newName = excelName + '_{:02d}.xlsx'.format(i)
        chunk.to_excel(newName , index=False)
        zipObj.write(newName)
        i=i+1


def dailyGeneratorJOBID(startDate, jobID, zipObj):
    # usernameORjobid shoudl replace username
    try:
        connection = mysql.connector.connect(host=dbhost, database=database, user=dbuser, password=dbpaswd, port=port)

        # monthYear - generate the string for the actualQueryMonth
        months = ['jan', 'feb', 'march', 'april', 'may', 'june', 'july', 'aug', 'sept', 'oct', 'nov', 'dec']
        monthName = int(startDate[2:4])
        monthYear = months[monthName - 1] + "_" + startDate[4:8]

        queryTemplate = startDate[4:8] + '-' + startDate[2:4] + '-' + startDate[0:2]
        actualQueryStart = queryTemplate + 'T00:00:00.00'  # '2021-04-25T00:00:00.00'
        actualQueryEnd = queryTemplate + 'T23:59:59.999'  # '2021-04-25T23:59:59.999'
        actualQueryMonth = monthYear + '_logs'  # month_year_logs

        # can use this to define exact users you want their data
        sql_select_Query = "SELECT * FROM " + actualQueryMonth + " WHERE username = '" + username + "' AND " \
                                                                                                    "submit_date " \
                                                                                                    "BETWEEN '" + \
                           actualQueryStart + "' AND '" + actualQueryEnd + "' "

        cursor = connection.cursor()
        cursor.execute(sql_select_Query)

        # get like all records for that day
        start = time.time()
        # this code to define how many records you want. fetchall() to get all, fetchmany(c) to get "c" number of records and fetchone() to get one
        records = cursor.fetchmany(10)
        end = time.time()

        # print("Time taken to get all records for the day is ", end - start, "and the number of rows is ",
        #      cursor.rowcount)

        nameOfExcel = username + '-report-' + startDate[0:2] + '-' + startDate[2:4] + '-' + startDate[4:8]
        # ('nationwideins-report-25-04-2021')

        excelName = nameOfExcel + '.xlsx'
        workbook = xlsxwriter.Workbook(excelName)
        worksheet = workbook.add_worksheet("Report for Day")

        # can use this to define exact columns you want in the file
        worksheet.write('A1', 'id')
        worksheet.write('B1', 'network')
        worksheet.write('C1', 'msisdn')
        worksheet.write('D1', 'sender')
        worksheet.write('E1', 'message')
        worksheet.write('F1', 'sms_count')
        worksheet.write('G1', 'status')
        worksheet.write('H1', 'submit_date')
        worksheet.write('I1', 'delivery_date')

        x = 0
        for row in records:
            aCellIndex = "A" + str(x + 2)
            worksheet.write(aCellIndex, row[0])

            bCellIndex = "B" + str(x + 2)
            worksheet.write(bCellIndex, row[6])

            cCellIndex = "C" + str(x + 2)
            worksheet.write(cCellIndex, row[5])

            dCellIndex = "D" + str(x + 2)
            worksheet.write(dCellIndex, row[7])

            eCellIndex = "E" + str(x + 2)
            worksheet.write(eCellIndex, row[9])

            fCellIndex = "F" + str(x + 2)
            worksheet.write(fCellIndex, row[10])

            gCellIndex = "G" + str(x + 2)
            worksheet.write(gCellIndex, row[13])

            # make the dates format well instead of nonsense floats
            hCellIndex = "H" + str(x + 2)
            timeH = row[11].strftime('%Y-%m-%d %H:%M:%S')
            worksheet.write(hCellIndex, timeH)

            iCellIndex = "I" + str(x + 2)
            timeI = row[12].strftime('%Y-%m-%d %H:%M:%S')
            worksheet.write(iCellIndex, timeI)

            x = x + 1

        workbook.close()
        zipObj.write(excelName)

        print("Excel File for Day", startDate, "generated")

        if os.path.exists(excelName):
            os.remove(excelName)
            print("Excel File for Day", startDate, "deleted")
        else:
            print("The file does not exist")

    except mysql.connector.Error as e:
        print("Error reading data from MySQL table", e)
    finally:
        connection = mysql.connector.connect(host=dbhost, database=database, user=dbuser, password=dbpaswd, port=port)
        cursor = connection.cursor()

        if connection.is_connected():
            connection.close()
            cursor.close()
            # print("MySQL connection is closed")


def checkJobIDExistence(passedJobID, newDate):
    # generate actualQueryMonth
    months = ['jan', 'feb', 'march', 'april', 'may', 'june', 'july', 'aug', 'sept', 'oct', 'nov', 'dec']
    monthName = int(newDate[2:4])
    monthYear = months[monthName - 1] + "_" + newDate[4:8]
    actualQueryMonth = monthYear + '_logs'  # month_2021_logs

    connection = mysql.connector.connect(host=dbhost, database=database, user=dbuser, password=dbpaswd, port=port)
    sqlQuery = "SELECT job_id, COUNT(*) FROM " + actualQueryMonth + " WHERE job_id = '" + passedJobID \
               + "' GROUP BY job_id"

    cursor = connection.cursor()
    cursor.execute(sqlQuery)

    cursor.fetchall()
    # gets the number of rows affected by the command executed
    row_count = cursor.rowcount

    if row_count == 0:
        return False
    else:
        return True


def checkUsernameExistence(passedUsername, newDate):
    # generate actualQueryMonth
    months = ['jan', 'feb', 'march', 'april', 'may', 'june', 'july', 'aug', 'sept', 'oct', 'nov', 'dec']
    monthName = int(newDate[2:4])
    monthYear = months[monthName - 1] + "_" + newDate[4:8]
    actualQueryMonth = monthYear + '_logs'  # month_2021_logs

    # check if a username exists
    connection = mysql.connector.connect(host=dbhost, database=database, user=dbuser, password=dbpaswd, port=port)
    sqlQuery = "SELECT username, COUNT(*) FROM " + actualQueryMonth + " WHERE username = '" + passedUsername \
               + "' GROUP BY username"

    cursor = connection.cursor()
    cursor.execute(sqlQuery)

    cursor.fetchall()
    # gets the number of rows affected by the command executed
    row_count = cursor.rowcount

    if row_count == 0:
        return False
    else:
        return True


def sendZipUsername(zipFileName, username):
    yagmail.register(REPORT_EMAIL, EMAIL_PASSWORD)
    filename = zipFileName

    userSMTP = yagmail.SMTP(REPORT_EMAIL)

    # this will be taken from the database
    connection = mysql.connector.connect(host=dbhost, database=emaildb, user=dbuser, password=dbpaswd, port=port)
    sqlQuery = "SELECT email FROM users WHERE username = '" + username + "'"

    cursor = connection.cursor()
    cursor.execute(sqlQuery)

    emailRecord = cursor.fetchall()
    row_count = cursor.rowcount

    if row_count == 0:
        return False
    else:
        receiver = emailRecord[0]
        print("The email would have been sent to {0}".format(receiver))

    # just so that the email doesn't actually go out, i am using a second re-assignment to force it to re-route to officialpaapa
    receiver = "officialpaapa@gmail.com"
    
    with open(zipFileName, "rb") as a_file:
        file_dict = {zipFileName: a_file}
        response = requests.post("https://api.anonfiles.com/upload", files=file_dict)

        #take the response and take the url to bit.ly
        urlParsed = response.json["data"]["file"]["url"]["full"]
  
        #it is self-contained code. the ourRes is the parsed response
        class UrlShortenTinyurl:
            URL = "http://tinyurl.com/api-create.php"

            def shorten(self, url_long):
                try:
                    url = self.URL + "?" + urllib.parse.urlencode({"url": url_long})
                    res = requests.get(url)
                    return res.text #res.text is the short url
                except Exception as e:
                    raise

        try:
            obj = UrlShortenTinyurl()
            shortURL = obj.shorten(urlParsed)
        except Exception as e:
            traceback.print_exc()
    
    # The body of the email
    body = 'Hello there, this is the report you requested for. <a href = "shortUrl" download = "zipFileName" >Click here to download your reports.</a>'

    userSMTP.send(
        to=receiver,
        # the subject of the email
        subject="Report Requested",
        contents=body,
        attachments=filename,
    )
    return True


def sendZipJobID(zipFileName, jobID):
    yagmail.register(REPORT_EMAIL, EMAIL_PASSWORD)
    filename = zipFileName

    userSMTP = yagmail.SMTP(REPORT_EMAIL)

    # this will be taken from the database
    connection = mysql.connector.connect(host=dbhost, database=emaildb, user=dbuser, password=dbpaswd, port=port)
    # USE THIS line to get the email from the job_id
    sqlQuery = "SELECT email FROM users WHERE username = '" + jobID + "'"

    cursor = connection.cursor()
    cursor.execute(sqlQuery)

    emailRecord = cursor.fetchall()
    row_count = cursor.rowcount

    if row_count == 0:
        return False
    else:
        receiver = emailRecord[0]
        print("The email would have been sent to {0}".format(receiver))

    # just so that the email doesn't actually go out, i am using a second re-assignment to force it to re-route to officialpaapa
    receiver = "officialpaapa@gmail.com"

    # The body of the email
    body = "Hello there, this is the report you requested for. <a download='filename'></a>"

    userSMTP.send(
        to=receiver,
        # the subject of the email
        subject="Report Requested",
        contents='<a href = zipFileName download = zipFileName >Click here to download your reports.</a>',
        attachments=filename,
    )
    return True


# Date Intervals from API
# http://127.0.0.1:5000/getReport
'''
POST REQUEST
{
    "StartDate": "xxxxxxxx",
    "EndDate": "xxxxxxxx",
    "Username": "xxx"
    "JobID" : "xxx"
}
'''


@app.route('/getReport', methods=['GET', 'POST'])
def api_id():
    json_data = flask.request.json
    data_passed = json.loads(json.dumps(json_data))

    if "StartDate" in data_passed:
        startDate = json_data["StartDate"]
        sdate = date(int(startDate[4:8]), int(startDate[2:4]), int(startDate[0:2]))  # start date
        print("The start date is", startDate)

        if "EndDate" not in data_passed:
            endDate = json_data["StartDate"]
            edate = date(int(endDate[4:8]), int(endDate[2:4]), int(endDate[0:2]))  # end date
            print("The end date is", endDate)

            res = pd.date_range(sdate, edate, freq='d').astype(str)
            newDatetest = res[0][8:10] + res[0][5:7] + res[0][0:4]

            if "Username" in data_passed:
                usernameVal = json_data["Username"]
                print("The username is", usernameVal)

                if checkUsernameExistence(usernameVal, newDatetest):
                    # it is true
                    zipFileName = usernameVal + '-report.zip'
                    zipObj = ZipFile(zipFileName, 'w')

                    task_queue.delay(res, zipObj, zipFileName, jobID=None, username=usernameVal)

            elif "JobID" in data_passed:
                jobIDVal = json_data["JobID"]

                if checkJobIDExistence(jobIDVal, newDatetest):
                    # it is true
                    zipFileName = jobIDVal + '-report.zip'
                    zipObj = ZipFile(zipFileName, 'w')

                    task_queue.delay(res, zipObj, zipFileName, jobID=jobIDVal, username=None)
                    return "<h1>Task Valid. Please check your email. You'll receive your report by email in less than 24 hours</h1>"
                else:
                    return "<h1>Username doesn't exist<h1>"

        elif "EndDate" in data_passed:
            endDate = json_data["EndDate"]
            edate = date(int(endDate[4:8]), int(endDate[2:4]), int(endDate[0:2]))  # end date
            print("The end date is", endDate)

            res = pd.date_range(sdate, edate, freq='d').astype(str)
            newDatetest = res[0][8:10] + res[0][5:7] + res[0][0:4]

            if "Username" in data_passed:
                usernameVal = json_data["Username"]
                print("The username is", usernameVal)

                if checkUsernameExistence(usernameVal, newDatetest):
                    # it is true
                    zipFileName = usernameVal + '-report.zip'
                    zipObj = ZipFile(zipFileName, 'w')

                    task_queue.delay(res, zipObj, zipFileName, jobID=None, username=usernameVal)
                    return "<h1>Task Valid. Please check your email. You'll receive your report by email in less than 24 hours</h1>"
                else:
                    return "<h1>Username doesn't exist<h1>"

            elif "JobID" in data_passed:
                jobIDVal = json_data["JobID"]

                if checkJobIDExistence(jobIDVal, newDatetest):
                    # it is true
                    zipFileName = jobIDVal + '-report.zip'
                    zipObj = ZipFile(zipFileName, 'w')

                    task_queue.delay(res, zipObj, zipFileName, jobID=jobIDVal, username=None)
                    return "<h1>Task Valid. Please check your email. You'll receive your report by email in less than 24 hours</h1>"
                else:
                    return "<h1>Job ID doesn't exist<h1>"
            else:
                return "<h1>Please check the Username or Job ID again. They don't seem to be valid.</h1>"

@celery.task(name='task_queue', queue='task_queue_for_delay')
def delayTaskOne(res, zipObj, zipFileName, jobID=None, username=None):
    if username != None:
        # if the usernames are given, generate the dates and use them to generate the reports.
        for x in range(len(res)):
            newDate = res[x][8:10] + res[x][5:7] + res[x][0:4]
            dailyGeneratorUSERNAME(newDate, username=username, zipObj=zipObj)

        # close the Zip File
        zipObj.close()
        if sendZipUsername(zipFileName, username) == True:
            print("Zip file sent")

            if os.path.exists(zipFileName):
                os.remove(zipFileName)
                print("Zip File deleted")
            else:
                print("The file does not exist")

    elif username == None and jobID != None:
        for x in range(len(res)):
            newDate = res[x][8:10] + res[x][5:7] + res[x][0:4]
            dailyGeneratorJOBID(newDate, jobID=jobID, zipObj=zipObj)
        # check the daily generator jobid function and the send zip function

        # close the Zip File
        zipObj.close()
        if sendZipJobID(zipFileName, jobID) == True:
            print("Zip file sent")

            if os.path.exists(zipFileName):
                os.remove(zipFileName)
                print("Zip File deleted")
            else:
                print("The file does not exist")


if __name__ == "__main__":
    print(appCel)
    app.run(host='127.0.0.1', port=8000)