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
import urllib.parse
import uuid
from elasticsearch import Elasticsearch
from pandasticsearch import Select


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


app = Flask(__name__)

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



#Get info
elasticinfo = config_object["elastic"]
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'))








# 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):
    es = Elasticsearch(str(elasticinfo["live"]))
    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}")

    # 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()
    
    # update specified report table
    sql = """INSERT INTO new_specified_report (id, username, status, start_date, end_date, email, created_at) VALUES(%s, %s, %s, %s, %s, %s, %s)"""
    _id = uuid.uuid4()
    submit_date =  datetime.utcnow().strftime('%Y-%m-%d-%H:%M:%S')
    val = (_id, username, 'pending', start_date, end_date, email, submit_date)
    conn_3.execute(sql, val)


    # file storage path
    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
    i = 0        
    
    while i <= num_days:

        yesterday_date = format_ed_time - timedelta(days=i)
        yesterday = yesterday_date.strftime('%Y-%m-%d')    
        yesterday_start_date = parser.isoparse(yesterday)
        yesterday_end_date = yesterday_start_date + timedelta(days=1, microseconds=-1)
        yesterday_start_date = yesterday_start_date.isoformat()
        yesterday_end_date = yesterday_end_date.isoformat()
        logging.info(f"[yesterday_start_date:{yesterday_start_date}] [yesterday_end_date:{yesterday_end_date}]")

    
        # username only
        if username.strip() and not job_id.strip() and not message.strip():
            json_payload = {
                                "size": 10000,
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}},
                                    {"match": {"username.keyword":username}}]}
                                },
                                "sort": [
                                { "submit_date" :  "desc"}
                                ]   
                            }
            
            count_payload = {
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}},
                                    {"match": {"username.keyword":username}}]}
                                },   
                        }
            try:
                # get total count of doc match
                count = es.count(index="deywuro-logs-*", body=count_payload)['count']
                total += count
                log_res = es.search(index="deywuro-logs-*", body=json_payload)
                df_1 = Select.from_dict(log_res).to_pandas()
                df_1 = df_1.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                logging.info(f'count:{count}')

                if not len(log_res['hits']['hits']):
                    return "No results"

                if count <= 10000:
                    save_path = "{}/{}.csv".format(userpath, yesterday_date)
                    logging.info(f' records: {len(df_1)}')
                    df_1.to_csv(save_path, index=False)
                    
                else:
                    # paginate indices
                    j = 0       
                    while len(log_res['hits']['hits']):
                        try:
                            cursor = log_res['hits']['hits'][-1]['sort'][0]
                            json_payload = {
                                            "size": 10000,
                                            "query": {
                                                "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}},
                                                {"match": {"username.keyword":username}}]}
                                            },
                                            "search_after" : [cursor],
                                            "sort": [
                                            { "submit_date" :  "desc"}
                                            ]   
                                            }
                            log_res = es.search(index="deywuro-logs-*", body=json_payload)
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            

                            # chunk results 200k
                            if len(df_1) >= 200000:
                                save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                                df_1.to_csv(save_path, index=False)
                                j += 1
                            else:
                                df_1 = pd.concat([df_1, df_2], ignore_index=True)
                            
                        except:
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)                        
                            df_2.to_csv(save_path, index=True)
                            break
            except Exception as e:
                logging.info(f'failed with error {e}')
                return 'internal server error'




        # job_id only
        elif job_id.strip() and not username.strip() and not message.strip():
            json_payload = {
                                "size": 10000,
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}},
                                    {"match": {"job_id.keyword":job_id}}]}
                                },
                                "sort": [
                                { "submit_date" :  "desc"}
                                ]   
                            }
            
            count_payload = {
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}},
                                    {"match": {"job_id.keyword":job_id}}]}
                                },   
                        }
            try:
                count = es.count(index="deywuro-logs-*", body=count_payload)['count']
                total += count
                log_res = es.search(index="deywuro-logs-*", body=json_payload)
                df_1 = Select.from_dict(log_res).to_pandas()
                df_1 = df_1.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                logging.info(f'count:{count}')

                if not len(log_res['hits']['hits']):
                    return "No results"

                if count <= 10000:
                    save_path = "{}/{}.csv".format(userpath, yesterday_date)
                    df_1.to_csv(save_path, index=False)
                    
                else:
                    j = 0       
                    while len(log_res['hits']['hits']):
                        try:
                            cursor = log_res['hits']['hits'][-1]['sort'][0]
                            json_payload = {
                                            "size": 10000,
                                            "query": {
                                                "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}},
                                                {"match": {"job_id.keyword":job_id}}]}
                                            },
                                            "search_after" : [cursor],
                                            "sort": [
                                            { "submit_date" :  "desc"}
                                            ]   
                                            }
                            log_res = es.search(index="deywuro-logs-*", body=json_payload)
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            if len(df_1) >= 200000:
                                save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                                df_1.to_csv(save_path, index=False)
                                j += 1
                            else:
                                df_1 = pd.concat([df_1, df_2], ignore_index=True)
                            
                        except:
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)                        
                            df_2.to_csv(save_path, index=True)
                            break
                            break
            except Exception as e:
                logging.info(f'failed with error {e}')
                return 'internal server error'


                
                
        # message only
        elif message.strip() and not username.strip() and not job_id.strip():
            json_payload = {
                                "size": 10000,
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}},
                                    {"match": {"message.keyword":message}}]}
                                },
                                "sort": [
                                { "submit_date" :  "desc"}
                                ]   
                            }
            
            count_payload = {
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}},
                                    {"match": {"message.keyword":message}}]}
                                },   
                        }
            try:
                count = es.count(index="deywuro-logs-*", body=count_payload)['count']
                total += count
                log_res = es.search(index="deywuro-logs-*", body=json_payload)
                df_1 = Select.from_dict(log_res).to_pandas()
                df_1 = df_1.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                logging.info(f'count:{count}')

                if not len(log_res['hits']['hits']):
                    return "No results"

                if count <= 10000:
                    save_path = "{}/{}.csv".format(userpath, yesterday_date)
                    logging.info(f' records: {len(df_1)}')
                    df_1.to_csv(save_path, index=False)
                    
                else:
                    j = 0       
                    while len(log_res['hits']['hits']):
                        try:
                            cursor = log_res['hits']['hits'][-1]['sort'][0]
                            json_payload = {
                                            "size": 10000,
                                            "query": {
                                                "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}},
                                                {"match": {"message.keyword":message}}]}
                                            },
                                            "search_after" : [cursor],
                                            "sort": [
                                            { "submit_date" :  "desc"}
                                            ]   
                                            }
                            log_res = es.search(index="deywuro-logs-*", body=json_payload)
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            if len(df_1) >= 200000:
                                save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                                df_1.to_csv(save_path, index=False)
                                j += 1
                            else:
                                df_1 = pd.concat([df_1, df_2], ignore_index=True)
                            
                        except:
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)                        
                            df_2.to_csv(save_path, index=True)
                            break
            except Exception as e:
                logging.info(f'failed with error {e}')
                return 'internal server error'




        # username and job_id
        elif username.strip() and job_id.strip() and not message.strip():
            json_payload = {
                                "size": 10000,
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                    "must": [{"match": {"username.keyword": username}},{"match": {"job_id.keyword": job_id}}]
                                }},
                                "sort": [
                                { "submit_date" :  "desc"}
                                ]   
                            }
            
            count_payload = {
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                    "must": [{"match": {"username.keyword": username}},{"match": {"job_id.keyword": job_id}}]
                                }},   
                        }
            try:
                count = es.count(index="deywuro-logs-*", body=count_payload)['count']
                total += count
                log_res = es.search(index="deywuro-logs-*", body=json_payload)
                df_1 = Select.from_dict(log_res).to_pandas()
                df_1 = df_1.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                logging.info(f'count:{count}')

                if not len(log_res['hits']['hits']):
                    return "No results"

                if count <= 10000:
                    save_path = "{}/{}.csv".format(userpath, yesterday_date)
                    logging.info(f' records: {len(df_1)}')
                    df_1.to_csv(save_path, index=False)
                    
                else:
                    j = 0       
                    while len(log_res['hits']['hits']):
                        try:
                            cursor = log_res['hits']['hits'][-1]['sort'][0]
                            json_payload = {
                                            "size": 10000,
                                            "query": {
                                                "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                                "must": [{"match": {"username.keyword": username}},{"match": {"job_id.keyword": job_id}}]
                                            }},
                                            "search_after" : [cursor],
                                            "sort": [
                                            { "submit_date" :  "desc"}
                                            ]   
                                            }
                            log_res = es.search(index="deywuro-logs-*", body=json_payload)
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            if len(df_1) >= 200000:
                                save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                                df_1.to_csv(save_path, index=False)
                                j += 1
                            else:
                                df_1 = pd.concat([df_1, df_2], ignore_index=True)
                            
                        except:
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)                        
                            df_2.to_csv(save_path, index=True)
                            break
            except Exception as e:
                logging.info(f'failed with error {e}')
                return 'internal server error'




        # username and message
        elif username.strip() and message.strip() and not job_id.strip():
            json_payload = {
                                "size": 10000,
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                    "must": [{"match": {"username.keyword": username}},{"match": {"message.keyword": message}}]
                                }},
                                "sort": [
                                { "submit_date" :  "desc"}
                                ]   
                            }
            
            count_payload = {
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                    "must": [{"match": {"username.keyword": username}},{"match": {"message.keyword": message}}]
                                }},   
                        }
            try:
                count = es.count(index="deywuro-logs-*", body=count_payload)['count']
                total += count
                log_res = es.search(index="deywuro-logs-*", body=json_payload)
                df_1 = Select.from_dict(log_res).to_pandas()
                df_1 = df_1.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                logging.info(f'count:{count}')

                if not len(log_res['hits']['hits']):
                    return "No results"

                if count <= 10000:
                    save_path = "{}/{}.csv".format(userpath, yesterday_date)
                    logging.info(f' records: {len(df_1)}')
                    df_1.to_csv(save_path, index=False)
                    
                else:
                    j = 0       
                    while len(log_res['hits']['hits']):
                        try:
                            cursor = log_res['hits']['hits'][-1]['sort'][0]
                            json_payload = {
                                            "size": 10000,
                                            "query": {
                                                "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                                "must": [{"match": {"username.keyword": username}},{"match": {"message.keyword": message}}]
                                            }},
                                            "search_after" : [cursor],
                                            "sort": [
                                            { "submit_date" :  "desc"}
                                            ]   
                                            }
                            log_res = es.search(index="deywuro-logs-*", body=json_payload)
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            if len(df_1) >= 200000:
                                save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                                df_1.to_csv(save_path, index=False)
                                j += 1
                            else:
                                df_1 = pd.concat([df_1, df_2], ignore_index=True)
                            
                        except:
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)                        
                            df_2.to_csv(save_path, index=True)
                            break
            except Exception as e:
                logging.info(f'failed with error {e}')
                return 'internal server error'





        # job_id and message
        elif job_id.strip() and message.strip() and not username.strip():
            json_payload = {
                                "size": 10000,
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                    "must": [{"match": {"job_id.keyword": job_id}},{"match": {"message.keyword": message}}]
                                }},
                                "sort": [
                                { "submit_date" :  "desc"}
                                ]   
                            }
            
            count_payload = {
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                    "must": [{"match": {"job_id.keyword": job_id}},{"match": {"message.keyword": message}}]
                                }},   
                        }
            try:
                count = es.count(index="deywuro-logs-*", body=count_payload)['count']
                total += count
                log_res = es.search(index="deywuro-logs-*", body=json_payload)
                df_1 = Select.from_dict(log_res).to_pandas()
                df_1 = df_1.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                logging.info(f'count:{count}')

                if not len(log_res['hits']['hits']):
                    return "No results"

                if count <= 10000:
                    save_path = "{}/{}.csv".format(userpath, yesterday_date)
                    logging.info(f' records: {len(df_1)}')
                    df_1.to_csv(save_path, index=False)
                    
                else:
                    j = 0       
                    while len(log_res['hits']['hits']):
                        try:
                            cursor = log_res['hits']['hits'][-1]['sort'][0]
                            json_payload = {
                                            "size": 10000,
                                            "query": {
                                                "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                                "must": [{"match": {"job_id.keyword": job_id}},{"match": {"message.keyword": message}}]
                                            }},
                                            "search_after" : [cursor],
                                            "sort": [
                                            { "submit_date" :  "desc"}
                                            ]   
                                            }
                            log_res = es.search(index="deywuro-logs-*", body=json_payload)
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            if len(df_1) >= 200000:
                                save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                                df_1.to_csv(save_path, index=False)
                                j += 1
                            else:
                                df_1 = pd.concat([df_1, df_2], ignore_index=True)
                            
                        except:
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)                        
                            df_2.to_csv(save_path, index=True)
                            break
            except Exception as e:
                logging.info(f'failed with error {e}')
                return 'internal server error'







        # username and message and job_id
        elif username.strip() and message.strip() and job_id.strip():
            json_payload = {
                                "size": 10000,
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                    "must": [{"match": {"username.keyword": username}},{"match": {"message.keyword": message}},{"match": {"job_id.keyword": job_id}}]
                                }},
                                "sort": [
                                { "submit_date" :  "desc"}
                                ]   
                            }
            
            count_payload = {
                                "query": {
                                    "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                    "must": [{"match": {"username.keyword": username}},{"match": {"message.keyword": message}},{"match": {"job_id.keyword": job_id}}]
                                }},   
                        }
            try:
                count = es.count(index="deywuro-logs-*", body=count_payload)['count']
                total += count
                log_res = es.search(index="deywuro-logs-*", body=json_payload)
                df_1 = Select.from_dict(log_res).to_pandas()
                df_1 = df_1.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                logging.info(f'count:{count}')

                if not len(log_res['hits']['hits']):
                    return "No results"

                if count <= 10000:
                    save_path = "{}/{}.csv".format(userpath, yesterday_date)
                    logging.info(f' records: {len(df_1)}')
                    df_1.to_csv(save_path, index=False)
                    
                else:
                    j = 0       
                    while len(log_res['hits']['hits']):
                        try:
                            cursor = log_res['hits']['hits'][-1]['sort'][0]
                            json_payload = {
                                            "size": 10000,
                                            "query": {
                                                "bool":{"filter":[{"range":{"submit_date":{"gte":"{}".format(yesterday_start_date), "lte":"{}".format(yesterday_end_date)}}}],
                                                "must": [{"match": {"username.keyword": username}},{"match": {"message.keyword": message}},{"match": {"job_id.keyword": job_id}}]
                                            }},
                                            "search_after" : [cursor],
                                            "sort": [
                                            { "submit_date" :  "desc"}
                                            ]   
                                            }
                            log_res = es.search(index="deywuro-logs-*", body=json_payload)
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            if len(df_1) >= 200000:
                                save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)
                                df_1.to_csv(save_path, index=False)
                                j += 1
                            else:
                                df_1 = pd.concat([df_1, df_2], ignore_index=True)
                            
                        except:
                            df_2 = Select.from_dict(log_res).to_pandas()
                            df_2 = df_2.filter(['id', 'network', 'msisdn', 'sender', 'message', 'sms_count', 'status', 'submit_date', 'delivery_date'])
                            save_path = "{}/{}-part-{}.csv".format(userpath, yesterday_date, j)                        
                            df_2.to_csv(save_path, index=True)
                            break
            except Exception as e:
                logging.info(f'failed with error {e}')
                return 'internal server error'
        i += 1

    # 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 new_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 new_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 = 'new_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)
            
            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')