#author : @thematrixsimulation

from flask import Flask, request, make_response, jsonify
from flask_httpauth import HTTPBasicAuth
import logging
from datetime import datetime, timedelta
import os
from configparser import ConfigParser
from dateutil import parser, tz
from flask_cors import CORS
import sqlalchemy as db
import urllib.parse
import pandas as pd
from dateutil import parser
from telegram_alert import send_telegram_message
import shutil


app = Flask(__name__)

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

CORS(app)

#Get info
dbinfo = config_object["mysql_database"]
authinfo = config_object["auth"]
compressed_path = config_object['path']['compressed_path']
original_path = config_object['path']['original_path']
base_path = config_object['path']['base_path']
log_file = os.path.join(config_object['logs']['base_log'], 'app.log')
auth = HTTPBasicAuth()


logging.basicConfig(filename=log_file,
                    filemode='a',
                    format='%(asctime)s %(name)s %(levelname)s %(message)s',
                    level=logging.INFO)

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



def convertMillis(millis):
    """Convert milliseconds to hours, minutes and seconds"""
    millis = int(millis)
    seconds=(millis/1000)%60
    seconds = int(seconds)
    minutes=(millis/(1000*60))%60
    minutes = int(minutes)
    hours=(millis/(1000*60*60))%24

    return "%dh:%dm:%ds" % (hours, minutes, seconds)



@app.route('/get/campaign/', methods=['GET'])
@auth.login_required
def campaignNameSearch():
    if request.method == 'GET':
        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()

        df = pd.read_sql('select media_name as campaign_name, sum(runtime) as total_runtime from mediashare_media where compressed_file is not null group by media_name', conn)
        try:
            df['clean_name'] = df['campaign_name'].map(lambda x : x[:-15])
            df['created_at'] = df['campaign_name'].map(lambda x : parser.parse(x[-14:]).strftime('%Y-%m-%d'))
            df['total_runtime'] = df['total_runtime'].apply(convertMillis)
            results = df.to_dict(orient='records')
            conn.close()

        except Exception as e:
            logging.info(f'failed with error {e}')
            conn.close()
            send_telegram_message(f'failed to retrieve campaign names')
            return make_response(jsonify(code=500, error='Failed to retrieve campaign names'))

        return make_response(jsonify(code=200, results=results), 200)




@app.route('/get/campaign/content/', methods=['POST'])
@auth.login_required
def campaignContent():
    if request.method == 'POST':
        req = request.get_json()
        campaign_name = req['campaign_name']

        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()

        try:
            df = pd.read_sql("select id, media_name as campaign_name, media_type, compressed_file as file, runtime from mediashare_media where media_name='{}'".format(campaign_name), conn)
            results = df.to_dict(orient='records')
            conn.close()
        
        except Exception as e:
            logging.info(f'failed with error {e}')
            send_telegram_message(f'failed to retrieve campaign content of {campaign_name}')
            conn.close()
            return make_response(jsonify(code=500, error='Failed to retrieve campaign contents'))
            
        return make_response(jsonify(code=200, results=results), 200)



@app.route('/schedule/campaign/', methods=['POST'])
@auth.login_required
def scheduleCampaign():
    if request.method == 'POST':
        req = request.get_json()
        pay_load  = req['pay_load']
        logging.info(f"payload: {pay_load}")
        now = datetime.utcnow()
        now_date = datetime.utcnow().date()
        pay_load['created_at'] = now_date

        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()
       
        try:
            for i in range(len(pay_load['branch'])):
                if pay_load['rotation_type'] == 'fixed':
                    if parser.parse(pay_load['start_date']) <= now:
                        pay_load['status'] = 1
                        pay_load['runs'] = 0
                        sql = """INSERT INTO mediashare_campaign(campaign_name, branch, start_date, end_date, rotation_type, status, runs, created_at) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"""
                        values = (pay_load['campaign_name'], pay_load['branch'][i], pay_load['start_date'], pay_load['end_date'], pay_load['rotation_type'], pay_load['status'], pay_load['runs'], pay_load['created_at'])
                        conn.execute(sql, values)
                        conn.close()


                elif pay_load['rotation_type'] == 'daily':
                    if parser.parse(pay_load['start_time']) <= now:
                        pay_load['status'] = 1
                        pay_load['runs'] = 0
                        pay_load['start_date'] = parser.parse(pay_load['start_time'])
                        sql = """INSERT INTO mediashare_campaign(campaign_name, branch,  start_date, start_time, end_time, rotation_type, status, runs, created_at) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s)"""
                        values = (pay_load['campaign_name'], pay_load['branch'][i], pay_load['start_date'], pay_load['start_time'], pay_load['end_time'], pay_load['rotation_type'], pay_load['status'], pay_load['runs'], pay_load['created_at'])
                        conn.execute(sql, values)
                        conn.close()

                        
                elif pay_load['rotation_type'] == 'weekly':
                    if parser.parse(pay_load['start_time']) <= now:
                        pay_load['status'] = 1
                        pay_load['runs'] = 0
                        pay_load['start_date'] = parser.parse(pay_load['start_time'])
                        pay_load['days_week'] = ','.join(pay_load['days_week'])
                        sql = """INSERT INTO mediashare_campaign(campaign_name, branch,  start_date, start_time, end_time, rotation_type, status, days_week, runs, created_at) VALUES(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
                        values = (pay_load['campaign_name'], pay_load['branch'][i], pay_load['start_date'], pay_load['start_time'], pay_load['end_time'], pay_load['rotation_type'], pay_load['status'], pay_load['days_week'], pay_load['runs'], pay_load['created_at'])
                        conn.execute(sql, values)
                        conn.close()

                else:
                    pass

        except Exception as e:
            logging.info(f'failed with error {e} while scheduling campaign')
            send_telegram_message(f"failed to schedule campaign for {pay_load['branch']}")
            conn.close()
            return make_response(jsonify(code=500, error='Failed to schedule campaign'))

        return make_response(jsonify(code=200, message='campaign scheduled'), 200)



@app.route('/delete/campaign/', methods=['POST'])
@auth.login_required
def deleteCampaign():
    if request.method == 'POST':
        req = request.get_json()
        campaign_name = req['campaign_name']
        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()

        try:
            sql_1 = "delete from mediashare_media where media_name='{}'".format(campaign_name)
            sql_2 = "delete from mediashare_campaign where campaign_name='{}'".format(campaign_name)
            conn.execute(sql_1)
            conn.execute(sql_2)
            if os.path.exists(os.path.join(compressed_path, campaign_name)):
                shutil.rmtree(os.path.join(compressed_path, campaign_name))
            else:
                logging.info(f'failed with error{e} while deleting campaign')
                send_telegram_message(f'failed with error{e} while deleting campaign')
                return make_response(jsonify(code=500, error='failed to delete campaign'), 200)
        except Exception as e:
            logging.info(f'failed with error{e} while deleting campaign')
            send_telegram_message(f'failed with error{e} while deleting campaign')
            return make_response(jsonify(code=500, error='failed to delete campaign'), 200)
        conn.close()
        return make_response(jsonify(code=200, error='deleted campaign'), 200)


@app.route('/delete/file/', methods=['POST'])
@auth.login_required
def deleteFile():
    if request.method == 'POST':
        req = request.get_json()
        campaign_name = req['campaign_name']
        file_name = req['file_name']
        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()

        try:
            sql = "delete from mediashare_media where media_name='{}' and compressed_file='{}'".format(campaign_name, file_name)
            conn.execute(sql)
            if os.path.exists(os.path.join(base_path, file_name)):
                shutil.rmtree(os.path.join(base_path, file_name))
            else:
                logging.info(f'failed with error{e} while deleting file')
                send_telegram_message(f'failed with error{e} while deleting file')
                return make_response(jsonify(code=500, error='failed to delete file'), 200)
        except Exception as e:
            logging.info(f'failed with error{e} while deleting file')
            send_telegram_message(f'failed with error{e} while deleting file')
            return make_response(jsonify(code=500, error='failed to delete file'), 200)
        conn.close()
        return make_response(jsonify(code=200, error='deleted file'), 200)





@app.route('/campaign/schedule/', methods=['GET'])
@auth.login_required
def campaignSchedule():
    if request.method == 'GET':
        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()

        try:
            df = pd.read_sql("select * from mediashare_campaign order by created_at desc", conn)
            results = df.to_dict(orient='records')

        except Exception as e:
            logging.info(f'failed with error {e} while campaign schedule')
            send_telegram_message(f"failed to retrieve campaign schedule")
            conn.close()
            return make_response(jsonify(code=500, error='Failed to retrieve campaign schedule'))

        return make_response(jsonify(code=200, results=results), 200)



@app.route('/unassigned/branches/', methods=['GET'])
@auth.login_required
def unassignedBranches():
    if request.method == 'GET':
        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()

        try:
            df_1 = pd.read_sql("select username, branch_name from mediashare_accounts where is_superuser=0", conn)
            df_2 = pd.read_sql("select distinct(branch) as branch_name from mediashare_campaign", conn)
            merged_df = pd.merge(df_1, df_2, on='branch_name', how='left', indicator=True)
            merged_df = merged_df[merged_df['_merge']=='left_only']
            merged_df = merged_df[['branch_name', 'username']]
            results = merged_df.to_dict(orient='records')

        except Exception as e:
            logging.info(f'failed with error {e} while retrieving branches without campaigns')
            send_telegram_message(f"failed with error {e} retrieving branches without campaigns")
            conn.close()
            return make_response(jsonify(code=500, error='Failed to retrieve branches without campaigns'))

        return make_response(jsonify(code=200, results=results), 200)



@app.route('/unassigned/campaigns/', methods=['GET'])
@auth.login_required
def unassignedCampaigns():
    if request.method == 'GET':
        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()

        try:
            df_1 = pd.read_sql("select distinct(media_name) as media_name from mediashare_media", conn)
            df_2 = pd.read_sql("select distinct(campaign_name) as media_name from mediashare_campaign", conn)
            merged_df = pd.merge(df_1, df_2, on='media_name', how='left', indicator=True)
            merged_df = merged_df[merged_df['_merge']=='left_only']
            merged_df = merged_df[['media_name']]
            results = merged_df.to_dict(orient='records')

        except Exception as e:
            logging.info(f'failed with error {e} while retrieving unassigned campaigns')
            send_telegram_message(f"failed with error {e} while retrieving unassigned campaigns")
            conn.close()
            return make_response(jsonify(code=500, error='Failed to retrieve unassigned campaigns'))

        return make_response(jsonify(code=200, results=results), 200)



    
@app.route('/campaign/view/', methods=['POST'])
# @auth.login_required
def campaignView():

    if request.method == 'POST':
        req =request.get_json()
        branch = req['branch']
        logging.info(f'branch:{branch}')

        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()

        df = pd.read_sql("select mediashare_media.media_name as campaign_name, mediashare_media.media_type, mediashare_media.runtime, mediashare_media.compressed_file as file from mediashare_media inner join mediashare_campaign on mediashare_media.media_name=mediashare_campaign.campaign_name where mediashare_campaign.branch='{}' and mediashare_campaign.status=1".format(branch), conn)
        if df.empty:
            conn.close()
            return make_response(jsonify(code=404, error='No active campaigns'))
            
        else:
            results = df.to_dict(orient='records')
            
            for item in results:
                try:
                    if item['media_type'] == 'video':
                        item['tags'] = """<video id="myVideo" class="embed-responsive emved-responsive-16by9" width="100%" height="auto" controls="controls" autoplay loop><source src=/media/{} type="video/mp4"></video>""".format(item['file'])
                            
                    elif item['media_type'] == 'audio':
                        item['tags'] = """<audio id="myVideo" class="embed-responsive emved-responsive-16by9" width="100%" height="auto" controls="controls" autoplay loop><source src=/media/{} type="audio/mpeg"></audio>""".format(item['file'])

                    
                    else:
                        item['tags'] = """<img src=/media/{} width="100%", height="auto">""".format(item['file'])

                except Exception as e:
                    logging.info(f'failed with error {e} while retrieving active campaigns for branch {branch}')
                    send_telegram_message(f'failed with error {e} while retrieving active campaigns for branch {branch}')
                    return make_response(jsonify(code=500, error='Failed to retrieve active campaigns'))

            
            logging.info(results)
            conn.close()
            return make_response(jsonify(code=200, results=results))


        

        
@app.route('/kill/switch/', methods=['POST'])
@auth.login_required
def killSwitch():
    if request.method == 'POST':
        req = request.get_json()
        campaign_name = req['campaign_name']
        kill_command = req['kill_command']

        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()

        try:
            # activate campaign
            if kill_command:
                sql = "update mediashare_campaign set status=0, kill_switch=1 where campaign_name='{}'".format(campaign_name)
                conn.execute(sql)
            
            # deactivate campaign
            else:
                sql = "update mediashare_campaign set status=1, kill_switch=0 where campaign_name='{}'".format(campaign_name)
                conn.execute(sql)
        
        
        except Exception as e:
            conn.close()
            if kill_command:
                logging.info(f'failed with error {e} while deactivating campaign {campaign_name}')
                send_telegram_message(f'failed with error {e} while deactivating campaign {campaign_name}')
                return make_response(jsonify(code=500, error='Failed to deactivate campaign'))

            else:
                logging.info(f'failed with error {e} while activating campaign {campaign_name}')
                send_telegram_message(f'failed with error {e} while activating campaign {campaign_name}')
                return make_response(jsonify(code=500, error='Failed to activate campaign'))
        conn.close()   
        return make_response(jsonify(code=200, message='success'), 200)
        

            
@app.route('/analytics', methods=['GET'])
# @auth.login_required
def analytics():          
    if request.method == 'GET':
        cnx = db.create_engine('mysql+pymysql://' + str(dbinfo["username"]) + ':' + urllib.parse.quote(str(dbinfo["password"])) + '@' + str(dbinfo['host']) + ':' + str(dbinfo["port"]) + '/' + str(dbinfo["db"]))
        conn = cnx.connect()     
        try:
            total_branches_df = pd.read_sql('select count(branch_id) as count from mediashare_accounts where is_superuser=0', conn)
            if total_branches_df.empty:
                total_branches = 0
            else:
                total_branches = int(total_branches_df['count'].values[0])
                logging.info(f'total_branches: {total_branches}')

            total_campaigns_df = pd.read_sql('select count(id) as count from mediashare_campaign', conn)
            if total_campaigns_df.empty:
                total_campaigns = 0
            else:
                total_campaigns = int(total_campaigns_df['count'].values[0])
            logging.info(f'total_campaigns: {total_campaigns}')


            total_active_df = pd.read_sql('select count(id) as count from mediashare_campaign where status=1', conn)
            if total_active_df.empty:
                total_active_campaigns = 0
            else:
                total_active_campaigns = int(total_active_df['count'].values[0])
            logging.info(f'total_active: {total_active_campaigns}')

            total_inactive_df = pd.read_sql('select count(id) as count from mediashare_campaign where status=0', conn)
            if total_inactive_df.empty:
                total_inactive_campaigns = 0
            else:
                total_inactive_campaigns = int(total_inactive_df['count'].values[0])
            logging.info(f'total_inactive: {total_inactive_campaigns}')


            played_campaigns_df = pd.read_sql('select campaign_name, count(id) as count from mediashare_campaign group by campaign_name', conn)
            if played_campaigns_df.empty:
                played_campaigns_count = []
                played_campaigns_label = []
            else:
                played_campaigns_label = played_campaigns_df['campaign_name'].tolist()
                played_campaigns_count = played_campaigns_df['count'].tolist()

            campaign_breakdown_df = pd.read_sql('select rotation_type, count(id) as count from mediashare_campaign group by rotation_type', conn)
            if campaign_breakdown_df.empty:
               campaign_breakdown_label = []
               campaign_breakdown_count = []
            else:
                campaign_breakdown_label = campaign_breakdown_df['rotation_type'].tolist()
                campaign_breakdown_count = campaign_breakdown_df['count'].tolist()

            file_breakdown_df = pd.read_sql('select media_type, count(id) as count from mediashare_media group by media_type', conn)
            if file_breakdown_df.empty:
                file_breakdown_label = []
                file_breakdown_count = []

            else:
                file_breakdown_label = file_breakdown_df['media_type'].tolist()
                file_breakdown_count = file_breakdown_df['count'].tolist()
        except Exception as e:
            logging.info(f'failed with error {e} while generating analytics')
            send_telegram_message(f'failed with error {e} while generating analytics')
            conn.close()
            return make_response(jsonify(code=500, error='Failed to generate analytics'))
        conn.close()
        return make_response(jsonify(code=200, 
                                     total_branches=total_branches, total_campaigns=total_campaigns, 
                                     total_active_campaigns=total_active_campaigns, total_inactive_campaigns=total_inactive_campaigns, 
                                     played_campaigns_count=played_campaigns_count, played_campaigns_label=played_campaigns_label,
                                     campaign_breakdown_count=campaign_breakdown_count, campaign_breakdown_label=campaign_breakdown_label,
                                     file_breakdown_count=file_breakdown_count, file_breakdown_label=file_breakdown_label), 200)
         



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