#!/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-3bd775389ab74c37bc7ff86d6ccff54b'



def SendDynamic (v_user_uuid, v_user_email, v_user_name, v_articles, v_jobs):

    #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 = "Top news articles!"
    email = v_user_email
    unsubscribe_url = "https://app.gigminds.com/unsubscribe/"+ "0c47a1b3-43f9-11eb-9605-2a05dc56dc12" + "/" + v_user_uuid
    message.dynamic_template_data = {
        'subject': subject,
        'articles': v_articles,
        'jobs': v_jobs,
        'unsubscribe_url': unsubscribe_url,
        'email': email
    }
    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:

        articles_sql = ( " select 'Article' type, id, title, description, wp_media_url image_url, wp_post_url url "
                    " from articles "
                    " where wp_id is not null "
                    " and length(description) > 375 "
                    " and wp_post_url is not null "
                    " order by id desc "
                    " limit 0,5 " )

        cursor.execute(articles_sql)
        articles = cursor.fetchall()

        users_sql = (" select uuid, first_name, last_name, name, email, tenant_id, id, resume_id "
                     " from person "
                     " where person.email = 'rberi@cloudely.com' "
                     " limit 1; ") 

        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['email']) > 5):
                print ('Sending email to: ', user['email'])

                jobs_sql = ( " select j.id, j.title, j.city, j.state_province, j.country, j.company, j.summary, m.skill_score, m.title_score, m.overall_score, m.location_score "
                        " from job_person_matches m "
                        " inner join person p on m.person_id = p.id and m.resume_id = p.resume_id "
                        " inner join jobs j on m.job_id = j.id "
                        " where p.id = 3108 "
                        " and j.status = 'Active' "
                        " order by m.location_score desc, m.skill_score desc, m.title_score desc "
                        " limit 5; ")

                cursor.execute(jobs_sql)
                jobs = cursor.fetchall()

                SendDynamic(user['uuid'], user['email'], user['name'], articles, jobs)    
                #SendDynamic(user['tenant_id'], 40, 'rberi@cloudely.com', 'raj beri', 1)