#!/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)

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.name = 'Hemant Hoon' "
            " and t.tenant_id = 6 "
            " order by elapsed_days desc; ")

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

print (records)

# from address we pass to our Mail object, edit with your name
FROM_EMAIL = ('mmellon@cloudely.com', 'Mike Mellon')

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

# list of emails and preheader names, update with yours
TO_EMAILS = [('rberi@cloudely.com', 'Raj Beri')]


def SendDynamic():
    """ Send a dynamic email to a list of email addresses    

    :returns API response code
    :raises Exception e: raises an exception """
    
    # create Mail object and populate
    message = Mail(
        from_email=FROM_EMAIL,
        to_emails=TO_EMAILS)
    # pass custom values for our HTML placeholders
    message.dynamic_template_data = {
        'subject': 'SendGrid Development',
        'place': 'New York City',
        'event': 'Twilio Signal',
        '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__":
    SendDynamic()