#!/usr/bin/env python3
import os
from sendgrid.helpers.mail import Mail
from sendgrid import SendGridAPIClient
import json
import pymysql.cursors
import pymysql


db = pymysql.connect (host="cldy-hub-db-prod-do-user-1524670-0.a.db.ondigitalocean.com",
user="doadmin",
passwd="jmbly6e4obtma5z0",
db="defaultdb",
port=25060,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)


# from address we pass to our Mail object, edit with your name
FROM_EMAIL = ('no-reply@gigminds.com', 'Gigminds')

# update to your dynamic template id from the UI
TEMPLATE_ID = 'd-9bdc9536629140df96f799c634a31018'



def SendDynamic(v_tenant_id, v_user_id, v_user_email, v_user_name, v_count):
    with db.cursor() as cursor:
        sql = ( " select u.name assigned_to, t.name task_type, t.subject task_name, t.status, c.name created_by, datediff(now(), due_date) elapsed_days, DATE_FORMAT(t.due_date, '%m/%d/%Y') due_date, t.details, "
                " IF (t.job_application_id is not null, 'https://app.gigminds.com/my/applications/'||t.job_application_id||'?task=true', "
                " IF (t.job_id is not null, 'https://app.gigminds.com/my/jobs/'||t.job_id||'?task=true', "
                " IF (t.person_id is not null, 'https://app.gigminds.com/marketplace/people/view/'||t.person_id||'?task=true', null))) url "
                " from tasks t "
                " left join users u on u.id = t.user_id "
                " left join users c on c.id = t.created_by "
                " where t.due_date < (NOW() - INTERVAL 1 DAY) "
                " and t.status != 'Completed' "
                " and u.id = '" + str(v_user_id) + "'"
                " and t.tenant_id = '" + str(v_tenant_id) + "'"
                " order by elapsed_days desc; ")

        cursor.execute(sql)
        records = cursor.fetchall()

    #print (records)

    TO_EMAILS = [(v_user_email, v_user_name)]
    
    # create Mail object and populate
    message = Mail(
        from_email=FROM_EMAIL,
        to_emails=TO_EMAILS)
    # pass custom values for our HTML placeholders
    subject = "Gigminds: You have " + str(v_count) + " overdue tasks!"
    message.dynamic_template_data = {
        'subject': subject,
        'overdue': records
    }
    message.template_id = TEMPLATE_ID
    # create our sendgrid client object, pass it our key, then send and return our response objects
    try:
        sg = SendGridAPIClient('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')
        response = sg.send(message)
        code, body, headers = response.status_code, response.body, response.headers
        #print(f"Response code: {code}")
        #print(f"Response headers: {headers}")
        #print(f"Response body: {body}")
        #print("Dynamic Messages Sent!")
    except Exception as e:
        print("Error: {0}".format(e))
    return
#
# str(response.status_code)






if __name__ == "__main__":
    
    # list of emails and preheader names, update with yours        
    with db.cursor() as cursor:
        users_sql = (" select u.tenant_id tenant_id, u.id user_id, u.email user_email, u.name user_name, count(*) count "
                 " from tasks t "
                 " left join users u on u.id = t.user_id "
                 " where t.due_date < (NOW() - INTERVAL 1 DAY) "
                 " and t.status != 'Completed' "
                 " and t.tenant_id = 6 "
                 " group by u.tenant_id, u.id, u.email, u.name "
                 " having count(*) > 0 ") 

        cursor.execute(users_sql)
        users = cursor.fetchall()
        for user in users:
            #print (user['user_email'], user['user_name'])
            #if (user['user_email'] == 'mmellon@cloudely.com'):
            if (len(user['user_email']) > 5):
                print ('Sending email to: ', user['user_email'])
                SendDynamic(user['tenant_id'], user['user_id'], user['user_email'], user['user_name'], user['count'])    
                #SendDynamic(user['tenant_id'], 40, 'rberi@cloudely.com', 'raj beri', 1)