import json
from flask import Flask, request, make_response, jsonify, send_file, abort
from flask_httpauth import HTTPBasicAuth
from celery import Celery
from itsdangerous import URLSafeTimedSerializer, SignatureExpired
import logging
from datetime import timedelta, datetime
import pandas as pd
from configparser import ConfigParser
from dateutil import parser
import zipfile
import os
import re
import sqlalchemy as db
import smtplib, ssl
from email.mime.text import MIMEText
import time
from apis.make_report_summary import plot_graph, convert2Pdf
import urllib.parse
import uuid

logging.basicConfig(filename='/var/www/html/logs/report_generation.log',
                    filemode='a',
                    format='%(asctime)s %(name)s %(levelname)s %(message)s',
                    level=logging.INFO)

# read config.ini file
config_object = ConfigParser()
config_object.read(".config.ini")

# get info
hellioinfo = config_object["live_hellio_wr"]
hellio_archinfo = config_object["hellio_archive"]
authinfo = config_object["auth"]
mailinfo = config_object["mail"]
tokeninfo = config_object["token"]
savepathinfo = config_object["save_path"]
sandbox_wr_info = config_object["sandbox_write"]
siteinfo = config_object['url']



app = Flask(__name__)



# token
s = URLSafeTimedSerializer(str(tokeninfo['secretkey'])) 

auth = HTTPBasicAuth()

USER_DATA = {
    str(authinfo["username"]): str(authinfo["password"])   
}

@auth.verify_password
def verify(username, password):
    if not (username and password):
        return False
    return USER_DATA.get(username) == password

# 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={"queue_report_update": {"queue": "queue_report_update_messenger"}}
    )
    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)


# download report
@app.route('/get-report/<token>/', methods=['GET'])
def getReport(token):
    try:
        save_zip_path = s.loads(token, max_age=604800)
        try:
            return send_file(save_zip_path, mimetype = 'zip', download_name=f"report-{os.path.basename(save_zip_path)}", as_attachment=True)  
        except FileNotFoundError:
            abort(404)   
    except SignatureExpired:
        return make_response(jsonify(code=500, error='token expired'))




# report summary
@app.route('/get/report-summary', methods=['POST', 'GET'])
@auth.login_required
def getReportSummary():

    # send mail function
    def sendMail(receiver:str, msg:str):
        SUBJECT = 'LOG REPORT'
        msg = msg
        TO = receiver
        FROM = mailinfo['mail_username']

        msg = MIMEText(msg)
        msg['Subject'] = SUBJECT
        msg['To'] = TO
        msg['From'] = FROM

        try:
            context = ssl.create_default_context()
            with smtplib.SMTP(str(mailinfo["mail_server"]), mailinfo["mail_port"]) as server:
                server.ehlo()  
                server.starttls(context=context)
                server.ehlo()  
                server.login(str(mailinfo["mail_username"]), str(mailinfo["mail_password"]))
                server.sendmail(FROM, TO, msg.as_string())
        except Exception as e:
            logging.info(f"Error in sending mail: {e}")
            return 'mail not sent'
        return 'mail sent'

    if request.method == 'POST':
        start_time = time.time()
        req = request.get_json()
        user_id = str(req['user_id'])
        username = str(req['search_username'])
        email = str(req['email']) 
        job_id = str(req['job_id'])
        message = str(req['message'])
        start_date = str(req['start_date'])
        end_date = str(req['end_date'])
        logging.info(f"Successful [job_id:{job_id}] [user_id:{user_id}] [username:{username}] [message:{message}] [start_date:{start_date}] [end_date={end_date}, [email={email}]")

        # format date from calendar
        st_time = parser.parse(start_date).isoformat()
        st_time = st_time.replace('T', " ")
        ed_time = parser.parse(end_date).isoformat()
        ed_time = ed_time.replace('T', " ")
        format_st_time  = datetime.strptime(st_time, '%Y-%m-%d %H:%M:%S')
        format_ed_time = datetime.strptime(ed_time, '%Y-%m-%d %H:%M:%S')
        days = abs(format_ed_time - format_st_time)
        num_days = days.days
        logging.info(f"number of days: {num_days}")
        
        # unique hellio_archive tables
        log_months = {
            'mar' : 'march',
            'apr' : 'april',
            'jun' : 'june',
            'jul' : 'july',
            'sep' : 'sept'
        }

        # connect to hellio db
        cnx_1 = db.create_engine('mysql+pymysql://' + str(hellioinfo["user"]) + ':' + urllib.parse.quote(str(hellioinfo["password"])) + '@' + str(hellioinfo['host']) + ':' + str(hellioinfo["port"]) + '/' + str(hellioinfo["db"]))
        conn_1 = cnx_1.connect().execution_options(stream_results=True)

        # connect to hellio_archive db
        cnx_2 = db.create_engine('mysql+pymysql://' + str(hellio_archinfo["user"]) + ':' + str(hellio_archinfo["password"]) + '@' + str(hellio_archinfo['host']) + ':' + str(hellio_archinfo["port"]) + '/' + str(hellio_archinfo["hl_arch_db"]))
        conn_2 = cnx_2.connect().execution_options(stream_results=True)


        i = 0
        try:
            while i <= num_days and i <= 93:

                yesterday_date = format_ed_time - timedelta(days=i)
                yesterday = yesterday_date.strftime('%Y-%m-%d')
                logging.info(f"yesterday : {yesterday}")
                yesterda_sql_str = str("'%%") + yesterday + str("%%'")
                message_sql_str = str("'%%") + message + str("%%'")

                # username only
                if username.strip() and not job_id.strip() and not message.strip():
    
                    status_df = pd.read_sql("""SELECT status, count(id) as count from logs WHERE username='{}' AND submit_date like {} GROUP BY status""".format(username, yesterda_sql_str), conn_1)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE username='{}' AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(username, yesterda_sql_str), conn_1)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from logs WHERE username='{}' AND submit_date like {} AND status='UNDELIV'""".format(username, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE username='{}' AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(username, yesterda_sql_str), conn_1)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from logs WHERE username='{}' AND submit_date like {} AND status='EXPIRED'""".format(username, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")

                    

                # message only
                elif message.strip() and not job_id.strip() and not username.strip():
                   
                    status_df = pd.read_sql("""SELECT status, count(id) as count from logs WHERE message like {} AND submit_date like {} GROUP BY status""".format(message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE message like {} AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from logs WHERE message like {} AND submit_date like {} AND status='UNDELIV'""".format(message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE message like {} AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from logs WHERE message like {} AND submit_date like {} AND status='EXPIRED'""".format(message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")

                    
    
                # job id only
                elif job_id.strip() and not username.strip() and not message.strip():

                    status_df = pd.read_sql("""SELECT status, count(id) as count from logs WHERE job_id='{}' AND submit_date like {} GROUP BY status""".format(job_id, yesterda_sql_str), conn_1)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE job_id='{}' AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(job_id, yesterda_sql_str), conn_1)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from logs WHERE job_id='{}' AND submit_date like {} AND status='UNDELIV'""".format(job_id, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE job_id='{}' AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(job_id, yesterda_sql_str), conn_1)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from logs WHERE job_id='{}' AND submit_date like {} AND status='EXPIRED'""".format(job_id, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")

                # username and message
                elif username.strip() and message.strip() and  not job_id.strip():
                  
                    status_df = pd.read_sql("""SELECT status, count(id) as count from logs WHERE username='{}' AND message like {} AND submit_date like {} GROUP BY status""".format(username, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE username='{}' AND message like {} AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(username, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from logs WHERE username='{}' AND message like {} AND submit_date like {} AND status='UNDELIV'""".format(username, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE username='{}' AND message like {} AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(username, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from logs WHERE username='{}' AND message like {} AND submit_date like {} AND status='EXPIRED'""".format(username, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")


                # username and job_id
                elif username.strip() and job_id.strip() and  not message.strip():
                    
                    status_df = pd.read_sql("""SELECT status, count(id) as count from logs WHERE username='{}' AND job_id='{}' AND submit_date like {} GROUP BY status""".format(username, job_id, yesterda_sql_str), conn_1)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE username='{}' AND job_id='{}' AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(username, job_id, yesterda_sql_str), conn_1)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from logs WHERE username='{}' AND job_id='{}' AND submit_date like {} AND status='UNDELIV'""".format(username, job_id, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE username='{}' AND job_id='{}'  AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(username, job_id, yesterda_sql_str), conn_1)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from logs WHERE username='{}' AND job_id='{}'  AND submit_date like {} AND status='EXPIRED'""".format(username, job_id, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")


                # message and job_id
                elif job_id.strip() and message.strip() and  not username.strip():
                   
                    status_df = pd.read_sql("""SELECT status, count(id) as count from logs WHERE job_id ='{}' AND message like {} AND submit_date like {} GROUP BY status""".format(job_id, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE job_id ='{}' AND message like {} AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(job_id, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from logs WHERE job_id ='{}' AND message like {}  AND submit_date like {} AND status='UNDELIV'""".format(job_id, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE job_id ='{}' AND message like {} AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(job_id, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from logs WHERE job_id ='{}' AND message like {} AND submit_date like {} AND status='EXPIRED'""".format(job_id, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")

                    
                
                # username, job_id and message
                elif username.strip() and job_id.strip() and message.strip():
                    
                    status_df = pd.read_sql("""SELECT status, count(id) as count from logs WHERE username='{}' AND job_id='{}' AND message like {} AND submit_date like {} GROUP BY status""".format(username, job_id, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE username='{}' AND job_id='{}' AND message like {} AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(username, job_id, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from logs WHERE username='{}' AND job_id='{}' AND message like {} AND submit_date like {} AND status='UNDELIV'""".format(username, job_id, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from logs WHERE username='{}' AND job_id='{}' AND message like {} AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(username, job_id, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from logs WHERE username='{}' AND job_id='{}' AND message like {} AND submit_date like {} AND status='EXPIRED'""".format(username, job_id, message_sql_str, yesterda_sql_str), conn_1)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")

                
                else:
                    logging.info("No search parameters")
                    return make_response(jsonify(code=404, error="No search parameters"), 200)
                i += 1



            while i <= num_days:

                # previous day
                yesterday_date = format_ed_time - timedelta(days=i)

                # get hellio_archive table names
                log_table = yesterday_date.strftime("%b_%Y_logs").lower()
                
                # find unique hellio_archive table names
                match_month = re.findall(r'([a-z]{3})_', log_table)
                logging.info(f"match month: {match_month}")
                
                # get unique hellio_archive table name
                if match_month[0] in log_months.keys():
                    log_month = log_table.replace(match_month[0],log_months.get(match_month[0]))
                else:
                    log_month = log_table
            
                logging.info(f"hellio_archive table : {log_month}")
                yesterday = yesterday_date.strftime('%Y-%m-%d')
                logging.info(f"yesterday : {yesterday}")

                yesterda_sql_str = str("'%%") + yesterday + str("%%'")

                message_sql_str = str("'%%") + message + str("%%'")


                # username only
                if username.strip() and not job_id.strip() and not message.strip():

                    status_df = pd.read_sql("""SELECT status, count(id) as count from {} WHERE username='{}' AND submit_date like {} GROUP BY status""".format(log_month, username, yesterda_sql_str), conn_2)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE username='{}' AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(log_month, username, yesterda_sql_str), conn_2)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from {} WHERE username='{}' AND submit_date like {} AND status='UNDELIV'""".format(log_month,username, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE username='{}' AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(log_month, username, yesterda_sql_str), conn_2)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from {} WHERE username='{}' AND submit_date like {} AND status='EXPIRED'""".format(log_month, username, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")


                # message only
                elif message.strip() and not job_id.strip() and not username.strip():
                   
                    status_df = pd.read_sql("""SELECT status, count(id) as count from {} WHERE message like {} AND submit_date like {} GROUP BY status""".format(log_month, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE message like {} AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(log_month, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from {} WHERE message like {} AND submit_date like {} AND status='UNDELIV'""".format(log_month, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE message like {} AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(log_month, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from {} WHERE message like {} AND submit_date like {} AND status='EXPIRED'""".format(log_month, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")

    
                # job id only
                elif job_id.strip() and not username.strip() and not message.strip():
                   
                    status_df = pd.read_sql("""SELECT status, count(id) as count from {} WHERE job_id='{}' AND submit_date like {} GROUP BY status""".format(log_month, job_id, yesterda_sql_str), conn_2)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE job_id='{}' AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(log_month, job_id, yesterda_sql_str), conn_2)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from {} WHERE job_id='{}' AND submit_date like {} AND status='UNDELIV'""".format(log_month, job_id, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE job_id='{}' AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(log_month, job_id, yesterda_sql_str), conn_2)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from {} WHERE job_id='{}' AND submit_date like {} AND status='EXPIRED'""".format(log_month, job_id, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")


                # username and message
                elif username.strip() and message.strip() and  not job_id.strip():
                   
                    status_df = pd.read_sql("""SELECT status, count(id) as count from {} WHERE username='{}' AND message like {} AND submit_date like {} GROUP BY status""".format(log_month, username, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE username='{}' AND message like {} AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(log_month, username, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from {} WHERE username='{}' AND message like {}  AND submit_date like {} AND status='UNDELIV'""".format(log_month,username, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE username='{}' AND message like {} AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(log_month, username, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from {} WHERE username='{}' AND message like {} AND submit_date like {} AND status='EXPIRED'""".format(log_month, username, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")


                # username and job_id
                elif username.strip() and job_id.strip() and  not message.strip():
                   
                    status_df = pd.read_sql("""SELECT status, count(id) as count from {} WHERE username='{}' AND job_id='{}' AND submit_date like {} GROUP BY status""".format(log_month, username, job_id, yesterda_sql_str), conn_2)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE username='{}' AND job_id='{}' AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(log_month, username, job_id, yesterda_sql_str), conn_2)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from {} WHERE username='{}' AND job_id='{}' AND submit_date like {} AND status='UNDELIV'""".format(log_month, username, job_id, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE username='{}' AND job_id='{}' AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(log_month, username, job_id, yesterda_sql_str), conn_2)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from {} WHERE username='{}' AND job_id='{}' AND submit_date like {} AND status='EXPIRED'""".format(log_month, username, job_id, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")

                
                # message and job_id
                elif job_id.strip() and message.strip() and  not username.strip():
                    
                    status_df = pd.read_sql("""SELECT status, count(id) as count from {} WHERE job_id ='{}' AND message like {} AND submit_date like {} GROUP BY status""".format(log_month, job_id, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE job_id ='{}' AND message like {} AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(log_month, job_id, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from {} WHERE job_id ='{}' AND message like {} AND submit_date like {} AND status='UNDELIV'""".format(log_month, job_id, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE job_id ='{}' AND message like {} AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(log_month, job_id, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from {} WHERE job_id ='{}' AND message like {} AND submit_date like {} AND status='EXPIRED'""".format(log_month, job_id, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")

                
                # username, job_id and message
                elif username.strip() and job_id.strip() and message.strip():
                    
                    status_df = pd.read_sql("""SELECT status, count(id) as count from {} WHERE username='{}' AND job_id='{}' AND message like {} AND submit_date like {} GROUP BY status""".format(log_month, username, job_id, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"status counts: {status_df.head(1)}")
                
                    network_undeliv_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE username='{}' AND job_id='{}' AND message like {} AND submit_date like {} AND status='UNDELIV' GROUP BY network""".format(log_month, username, job_id, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"network undelivered counts: {network_undeliv_df.head(1)}")
                
                    msisdn_undeliv_df = pd.read_sql(""" SELECT  msisdn from {} WHERE username='{}' AND job_id='{}' AND message like {}AND submit_date like {} AND status='UNDELIV'""".format(log_month, username, job_id, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn undelivered: {msisdn_undeliv_df.head(1)}")
                
                    network_exp_df = pd.read_sql("""SELECT  network, count(msisdn) as count from {} WHERE username='{}' AND job_id='{}' AND message like {} AND submit_date like {} AND status='EXPIRED' GROUP BY network""".format(log_month, username, job_id, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"network expired counts: {network_exp_df.head(1)}")

                    msisdn_exp_df = pd.read_sql(""" SELECT  msisdn from {} WHERE username='{}' AND job_id='{}' AND message like {} AND submit_date like {} AND status='EXPIRED'""".format(log_month, username, job_id, message_sql_str, yesterda_sql_str), conn_2)
                    logging.info(f"msisdn expired: {msisdn_exp_df.head(1)}")

                else:
                    logging.info("No search parameters")
                    return make_response(jsonify(code=404, error="No search parameters"), 200)
                    
                    
                i += 1
                
        except Exception as e:
            logging.info(e)
            conn_1.close()
            conn_2.close()
            return make_response(jsonify(code=500, error='Internal server error'), 200)


    

        # zip file in folder
        zip_time = datetime.utcnow().strftime('%H:%M:%S')
        logging.info(f"zip time: {zip_time}")
        save_zip_path = os.path.join(str(savepathinfo['save_zip_path']), "{}-{}.zip".format(zip_time, user_id))
        logging.info(f"save zip path: {save_zip_path}")
        xcel_filepath = str(userpath)
        xcel_files = os.listdir(xcel_filepath)

       
        end_time = time.time()
        logging.info(f"time elapsed: {time.strftime('%Hh%Mm%Ss', time.gmtime(end_time-start_time))}")
        
        return "successfully processed report"




  
           
  

# generate report
@app.route('/generate/report', methods=['POST', 'GET'])
@auth.login_required
def generateReport():
    if request.method == 'POST':
        req = request.get_json()
        user_id = str(req['user_id'])
        username = str(req['search_username'])
        email = str(req['email']) 
        job_id = str(req['job_id'])
        message = str(req['message'])
        start_date = str(req['start_date'])
        end_date = str(req['end_date'])
        try:
            queue_report_update.delay(job_id=job_id, user_id=user_id, username=username, message=message, start_date=start_date, end_date=end_date, email=email)
            logging.info(f"Successful [job_id:{job_id}] [user_id:{user_id}] [username:{username}] [message:{message}] [start_date:{start_date}] [end_date={end_date}] [email={email}]")
        except Exception as e:
            logging.info(f"error delay task : {e}")
            return make_response(jsonify(code=500, error="failed to queue process"), 200)
        return make_response(jsonify(code=200, message="processing reports"), 200)

    

@celery.task(name='queue_report_update', queue='queue_report_update_messenger')
def queue_report_update(job_id, user_id, username, message, start_date, end_date, email):
    start_time = time.time()
    logging.info(f"Querying database  [job_id:{job_id}] [user_id:{user_id}] [username:{username}] [message:{message}] [start_date:{start_date}] [end_date:{end_date}] [email:{email}]")
    
  
    def sendMail(receiver:str, msg:str):
        SUBJECT = 'LOG REPORT'
        msg = msg
        TO = receiver
        FROM = mailinfo['mail_username']
        cc = 'support@npontu.com'
        
        rcpt = cc + ',' + TO
        msg = MIMEText(msg)
        msg['Subject'] = SUBJECT
        msg['To'] = TO
        msg['From'] = FROM
        msg['Cc'] = cc

        try:
            context = ssl.create_default_context()
            with smtplib.SMTP(str(mailinfo["mail_server"]), mailinfo["mail_port"]) as server:
                server.ehlo()  
                server.starttls(context=context)
                server.ehlo()  
                server.login(str(mailinfo["mail_username"]), str(mailinfo["mail_password"]))
                server.sendmail(FROM, rcpt, msg.as_string())
        except Exception as e:
            logging.info(f"Error in sending mail: {e}")
            return 'mail not sent'
        return 'mail sent'


        
    
    # format date from calendar
    st_time = parser.parse(start_date).isoformat()
    st_time = st_time.replace('T', " ")
    ed_time = parser.parse(end_date).isoformat()
    ed_time = ed_time.replace('T', " ")
    format_st_time  = datetime.strptime(st_time, '%Y-%m-%d %H:%M:%S')
    format_ed_time = datetime.strptime(ed_time, '%Y-%m-%d %H:%M:%S')
    days = abs(format_ed_time - format_st_time)
    num_days = days.days
    logging.info(f"number of days: {num_days+1}")
    
    # unique hellio_archive tables
    log_months = {
        'mar' : 'march',
        'apr' : 'april',
        'jun' : 'june',
        'jul' : 'july',
        'sep' : 'sept'
    }

     # connect to  sandbox db
    cnx_3 = db.create_engine('mysql+pymysql://' + str(sandbox_wr_info["user"]) + ':' + urllib.parse.quote(str(sandbox_wr_info["password"])) + '@' + str(sandbox_wr_info['host']) + ':' + str(sandbox_wr_info["port"]) + '/' + str(sandbox_wr_info["db"]))
    conn_3 = cnx_3.connect()
    
    sql = """INSERT INTO specified_report (id, username, status, start_date, end_date, email, created_at) VALUES(%s, %s, %s, %s, %s, %s, %s)"""
    _id = uuid.uuid4()
    created_at =  datetime.utcnow().strftime('%Y-%m-%d-%H:%M:%S')
    val = (_id, username, 'pending', start_date, end_date, email, created_at)
    conn_3.execute(sql, val)
    
   


    i = 0
    try:
        
        sheets_path = str(savepathinfo["sheets_path"])
        user_path = username + '-' + datetime.utcnow().strftime('%Y-%m-%d-%H:%M:%S')
        userpath = os.path.join(sheets_path,user_path)
        os.mkdir(userpath)
        logging.info(f'user_directory:{userpath}')
        total = 0
        while i <= num_days and i <= 93:
            # connect to hellio db
            cnx_1 = db.create_engine('mysql+pymysql://' + str(hellioinfo["user"]) + ':' + urllib.parse.quote(str(hellioinfo["password"])) + '@' + str(hellioinfo['host']) + ':' + str(hellioinfo["port"]) + '/' + str(hellioinfo["db"]))
            conn_1 = cnx_1.connect().execution_options(stream_results=True)

            # connect to hellio_archive db
            cnx_2 = db.create_engine('mysql+pymysql://' + str(hellio_archinfo["user"]) + ':' + str(hellio_archinfo["password"]) + '@' + str(hellio_archinfo['host']) + ':' + str(hellio_archinfo["port"]) + '/' + str(hellio_archinfo["hl_arch_db"]))
            conn_2 = cnx_2.connect().execution_options(stream_results=True)

            yesterday_date = format_ed_time - timedelta(days=i)
            yesterday = yesterday_date.strftime('%Y-%m-%d')
            logging.info(f"yesterday : {yesterday}")
            yesterda_sql_str = str("'%%") + yesterday + str("%%'")
            message_sql_str = str("'%%") + message + str("%%'")

            # username only
            if username.strip() and not job_id.strip() and not message.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM logs WHERE username ='{}' AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(username, yesterda_sql_str), conn_1, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    


            # message only
            elif message.strip() and not job_id.strip() and not username.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM logs WHERE message like {} AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(message_sql_str, yesterda_sql_str), conn_1, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # job id only
            elif job_id.strip() and not username.strip() and not message.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM logs WHERE job_id ='{}' AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(job_id, yesterda_sql_str), conn_1, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # username and message
            elif username.strip() and message.strip() and  not job_id.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM logs WHERE username ='{}' AND message like {} AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(username, message_sql_str, yesterda_sql_str), conn_1, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # username and job_id
            elif username.strip() and job_id.strip() and  not message.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM logs WHERE username ='{}' AND job_id='{}' AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(username, job_id, yesterda_sql_str), conn_1, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # message and job_id
            elif job_id.strip() and message.strip() and  not username.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM logs WHERE job_id ='{}' AND message like {} AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(job_id, message_sql_str, yesterda_sql_str), conn_1, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # username, job_id and message
            elif username.strip() and job_id.strip() and message.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM logs WHERE username ='{}' AND job_id='{}' AND message like {} AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(username, job_id, message_sql_str, yesterda_sql_str), conn_1, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            else:
                logging.info("No search parameters")
                sql = """UPDATE specified_report SET status='{}' where id='{}' and username='{}'""".format('No results', _id, username)
                conn_1.close()
                conn_2.close()
                conn_3.close()
                return "No search parameters"
             
            i += 1



        while i <= num_days:
            # connect to hellio db
            cnx_1 = db.create_engine('mysql+pymysql://' + str(hellioinfo["user"]) + ':' + urllib.parse.quote(str(hellioinfo["password"])) + '@' + str(hellioinfo['host']) + ':' + str(hellioinfo["port"]) + '/' + str(hellioinfo["db"]))
            conn_1 = cnx_1.connect().execution_options(stream_results=True)

            # connect to hellio_archive db
            cnx_2 = db.create_engine('mysql+pymysql://' + str(hellio_archinfo["user"]) + ':' + str(hellio_archinfo["password"]) + '@' + str(hellio_archinfo['host']) + ':' + str(hellio_archinfo["port"]) + '/' + str(hellio_archinfo["hl_arch_db"]))
            conn_2 = cnx_2.connect().execution_options(stream_results=True)

            # previous day
            yesterday_date = format_ed_time - timedelta(days=i)

            # get hellio_archive table names
            log_table = yesterday_date.strftime("%b_%Y_logs").lower()
            
            # find unique hellio_archive table names
            match_month = re.findall(r'([a-z]{3})_', log_table)
            logging.info(f"match month: {match_month}")
            
            # get unique hellio_archive table name
            if match_month[0] in log_months.keys():
                log_month = log_table.replace(match_month[0],log_months.get(match_month[0]))
            else:
                log_month = log_table
        
            logging.info(f"hellio_archive table : {log_month}")
            yesterday = yesterday_date.strftime('%Y-%m-%d')
            logging.info(f"yesterday : {yesterday}")

            yesterda_sql_str = str("'%%") + yesterday + str("%%'")

            message_sql_str = str("'%%") + message + str("%%'")



            # username only
            if username.strip() and not job_id.strip() and not message.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM {} WHERE username ='{}' AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(log_month, username, yesterda_sql_str), conn_2, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # message only
            elif message.strip() and not job_id.strip() and not username.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM {} WHERE message like {} AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(log_month, message_sql_str, yesterda_sql_str), conn_2, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # job id only
            elif job_id.strip() and not username.strip() and not message.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM {} WHERE job_id ='{}' AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(log_month, job_id, yesterda_sql_str), conn_2, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # username and message
            elif username.strip() and message.strip() and  not job_id.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM {} WHERE username ='{}' AND message like {} AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(log_month, username, message_sql_str, yesterda_sql_str), conn_2, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # username and job_id
            elif username.strip() and job_id.strip() and  not message.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM {} WHERE username ='{}' AND job_id='{}' AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(log_month, username, job_id, yesterda_sql_str), conn_2, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # message and job_id
            elif job_id.strip() and message.strip() and  not username.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM {} WHERE job_id ='{}' AND message like {} AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(log_month, job_id, message_sql_str, yesterda_sql_str), conn_2, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()    

            # username, job_id and message
            elif username.strip() and job_id.strip() and message.strip():
                j = 1
                sql = """SELECT id, network, msisdn,sender,message,sms_count, status, submit_date, delivery_date FROM {} WHERE username ='{}' AND job_id='{}' AND message like {} AND submit_date like {}"""
                for chunk_dataframe in pd.read_sql(sql.format(log_month, username, job_id, message_sql_str, yesterda_sql_str), conn_2, chunksize=50000): 
                    if not len(chunk_dataframe):
                        continue
                    logging.info(f"{username}'s Report Data:{chunk_dataframe.head(1)}")
                    save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                    total += len(chunk_dataframe)
                    chunk_dataframe.to_csv(save_path, encoding='utf-8', index=False)
                    j += 1
                conn_1.close()
                conn_2.close()          
            else:           
                logging.info("No search parameters")
                sql = """UPDATE specified_report SET status='{}' where id='{}' and username='{}'""".format('No results', _id, username)
                conn_1.close()
                conn_2.close()
                return "No search parameters"
            i += 1
            
    except Exception as e:
        logging.info(f'failed with error {e}')
        conn_3.close()
        return 'Internal server error'


   

    # zip file in folder
    zip_time = datetime.utcnow().strftime('%Y-%m-%d %H:%M:%S')
    logging.info(f"zip time: {zip_time}")
    save_zip_path = os.path.join(str(savepathinfo['save_zip_path']), "{}-{}.zip".format(username, zip_time))
    logging.info(f"save zip path: {save_zip_path}")
    xcel_filepath = userpath
    logging.info(f'sheets filepath: {xcel_filepath}')
    xcel_files = os.listdir(xcel_filepath)

    if not len(xcel_files):
        logging.info('No results')
        sql = """UPDATE specified_report SET status='{}' where id='{}' and username='{}'""".format('No results', _id, username)
        conn_3.execute(sql)
        conn_3.close()
        return 'No results'

    with zipfile.ZipFile(save_zip_path, 'w') as zipObj: 
        for file in xcel_files:
            try:
                zipObj.write(os.path.join(userpath,file), os.path.relpath(os.path.join(userpath,file), str(savepathinfo['base_path'])))
            except Exception as e:
                logging.info(f"Error occured while zipping file: {e}")
       

    # encode filepath
    token = s.dumps(save_zip_path)
    link = "{}{}/".format(siteinfo['site_url'], token)
    msg = 'This is the download link for your report {}'.format(link)

    # send mail
    mail_response = sendMail(email, msg)
    logging.info(mail_response)
    end_time = time.time()
    logging.info(f"time elapsed: {time.strftime('%Hh%Mm%Ss', time.gmtime(end_time-start_time))}")
    logging.info(f"Total No of records for {username}: {total}")
    expires_at = (datetime.utcnow() + timedelta(days=7)).strftime('%Y-%m-%d %H:%M:%S')
    sql = """UPDATE specified_report SET status='{}', link='{}', total_records='{}', expires_at='{}' where id='{}' and username='{}'""".format('done', link, total, expires_at, _id, username)
    conn_3.execute(sql)
    conn_3.close()
       
    return "successfully processed report"






# db api
@app.route('/query/report', methods=['POST', 'GET'])
@auth.login_required
def queryReport():
    if request.method == 'POST':
        req = request.get_json()
        username = str(req['search_username'])
        table_name = 'specified_report'
        cnx_1 = db.create_engine('mysql+pymysql://' + str(sandbox_wr_info["user"]) + ':' + urllib.parse.quote(str(sandbox_wr_info["password"])) + '@' + str(sandbox_wr_info['host']) + ':' + str(sandbox_wr_info["port"]) + '/' + str(sandbox_wr_info["db"]))
        conn_1 = cnx_1.connect()

        try:
            sql = """SELECT id, username, email, status, link, total_records, start_date, end_date, expires_at from {} where username='{}' and created_at > now() - interval 7 day order by created_at desc"""
            query_df = pd.read_sql(sql.format(table_name, username), conn_1)
            query_df['start_date'] = pd.to_datetime(query_df['start_date'],unit='s')
            query_df['end_date'] = pd.to_datetime(query_df['end_date'],unit='s')
            if not len(query_df):
                return make_response(jsonify(code=404, result='No records found'), 200)

            result = query_df.to_json(orient='records')
            conn_1.close

            return make_response(jsonify(code=200, result=json.loads(result)), 200)
        except Exception as e:
            logging.info(f'failed with error {e}')
            conn_1.close()
            return make_response(jsonify(code=500, result='search failed'), 200)

            

        
if __name__ == '__main__':
    app.run(host='0.0.0.0')