#!/usr/bin/python
import sys
import requests
import psycopg2
from datetime import datetime
import sendgrid
from sendgrid.helpers.mail import *
import base64

URL = "http://alerts.sinfini.com/api/v3/index.php"

batchSize = 50

#db = MySQLdb.connect("10.160.0.11","bcWeb","!bigc!ty2@16","bc7up")

try:
  #raise RuntimeError('this is the error message')
  connect_str = "dbname='staging32' user='bcdev2' host='localhost' password='!stAte2@19'"
  db = psycopg2.connect(connect_str)
  db.set_client_encoding('UTF8')
except Exception as err:
  sys.stderr.write('DBERROR: %s' % str(err))


sg = sendgrid.SendGridAPIClient(apikey='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')
print("Starting MsgEmail...", datetime.now().strftime("%Y-%m-%d %H:%M:%S"))
cursor1 = db.cursor()
qry1 = "SELECT id, fromId, toId, sub, body, msgtype, status, pdf_path FROM messages WHERE pdf_path is not null"
cursor1.execute(qry1)
for (id, fromId, toId, sub, body, msgtype, status, pdf_path) in cursor1:
    if body.strip():
      file_path  = 'rajkumar_certificate_of_excellence.pdf'
      with open(file_path, 'rb') as f:
         data = f.read()
      encoded = base64.b64encode(data).decode()
      attachment = Attachment()
      attachment.content = encoded
      attachment.type = "application/pdf"
      attachment.filename = "rajkumar_certificate_of_excellence.pdf"
      attachment.disposition = "attachment"
      attachment.content_id = "PDF Document file"
  
      from_email = Email("no-reply@bigcityexperience.com", "Classmate")
      to_email = Email(toId)
      subject = sub
      content = Content("text/html", body)
      mail = Mail(from_email, subject, to_email, content)
      mail.add_attachment(attachment)
      response = sg.client.mail.send.post(request_body=mail.get())
      # sending get request and saving the response as response object
      if response.status_code == 202:
        print("OK.")
        status = 5
        sentOn = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cursor2 = db.cursor()
        qry2 = "UPDATE messages SET status = %s, sentOn = %s WHERE id = %s"
        params2 = [status, sentOn, id]
        cursor2.execute(qry2, params2)
        cursor2.close()
        db.commit()
      else:
        print("Failed.", response.status_code)
    else:
        status = -1
        sentOn = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        cursor2 = db.cursor()
        qry2 = "UPDATE messages SET status = %s, sentOn = %s WHERE id = %s"
        params2 = [status, sentOn, id]
        cursor2.execute(qry2, params2)
        cursor2.close()
        db.commit()
print("...Finished MsgEmail.")
cursor1.close()
db.commit()