# -*- coding: utf-8 -*-

import MySQLdb as mdb # pip install MySQL-python
import smtplib
import logging
from time import gmtime, strftime

loglevel=logging.DEBUG
# loglevel=logging.INFO

logging.basicConfig(format='%(asctime)s [Przypominajka-%(levelname)s] %(message)s',filename='przypomiajka.log',level=loglevel)

class Mys:
    """ Simple MySQL class to handle select2dict and execute queries """
    def __init__(self, db_server, db_user, db_pass, db_name):
        logging.debug("Connecting to database %s on server: %s as %s", db_name, db_server, db_user)
        self.con = mdb.connect(db_server, db_user, db_pass, db_name)

    def execute(self,query):
        try:
            #~ con = mdb.connect('localhost','s4ros','.ZXCasd!@#.','s4ros')
            logging.debug("Executing SQL query: '%s'",query)
            cur = self.con.cursor()
            cur.execute(query)
            return True
        except mdb.Error, e:
            print "Error %d: %s" % (e.args[0],e.args[1])
            logging.error("Can't execute SQL query!")
            return False
        self.con.close()

    def select2dict(self,query):
        try:
            #~ con = mdb.connect('localhost','s4ros','.ZXCasd!@#.','s4ros')
            cur = self.con.cursor(mdb.cursors.DictCursor)
            logging.debug("select2dict from SQL query: '%s'",query)
            cur.execute(query)
            rows = cur.fetchall()
            cols = cur.description
            return rows
        except mdb.Error, e:
            print "Error %d: %s" % (e.args[0],e.args[1])
            logging.error("Can't select2dict SQL query!")
            return False
        self.con.close()

# test
if __name__ == '__main__':
    logging.info("Started new session of makova-reminder-cron job!")

    db_server = 'sql.second.vdl.pl'
    db_user = 'kwg13_makova'
    db_pass = '11roT2yUBV2wTByF4LSX'
    db_name = db_user

    smtp_server = "makovapanienka.pl"
    smtp_port = 587
    smtp_user = "przypominajka@makovapanienka.pl"
    smtp_passwd = "RGDjOJkt"

    logging.info("Creating connection to Database")
    m = Mys(db_server, db_user, db_pass, db_name)
    logging.info("Select all reminder data for ... good date.")
    a = m.select2dict('SELECT * from reminder where event_date = DATE(CURDATE() + INTERVAL 3 DAY)')

## pobralim wszystkie dane z przypominajki na dzien dzisiejszy..
## czas skomponowac maila
    for event in a:
        # making a connection to SMTP server
        logging.info("Connecting to SMTP %s:%s server",smtp_server,smtp_port)
        server = smtplib.SMTP(smtp_server, smtp_port)
        logging.debug("Sending EHLO")
        server.ehlo()
        logging.debug("Starting TLS communication")
        server.starttls()
        logging.debug("Sending second EHLO")
        server.ehlo()
        logging.info("Logging into SMTP server as %s",smtp_user)
        server.login(smtp_user, smtp_passwd)

        # gather all neccessary data from 'reminder' table
        mail=dict()
        mail['to'] = event['email']
        logging.debug("E-MAIL TO: %s",mail['to'])
        mail['subject'] = 'Przypominajka: %s' % event['event_name']
        logging.debug("E-MAIL SUBJECT: %s",mail['subject'])
        mail['content'] = event['content']
        logging.debug("E-MAIL CONTENT: %s",mail['content'])

        # building email message with HEADERS and CONTENT
        header='To:'+mail['to']+'\n'+'From:"Makowa Panienka"<'+smtp_user+'>\n'+'Subject:'+mail['subject']+'\n'
        msg = header+'\n'+mail['content']+'\n\n'

        # sending email
        logging.info("Sending an email to: %s",mail['to'])
        server.sendmail(smtp_user, mail['to'], msg)

        # closing SMTP connection
        logging.info("Closing connection to SMTP server.")
        server.close()
        # print 'done!'
    logging.info("Shutting down makova-reminder-cron job.")
    exit(0)