import smtplib
import traceback
import os
from email.mime.text import MIMEText
from email.mime.image import MIMEImage
from email.mime.multipart import MIMEMultipart
from email.message import EmailMessage
import codecs
import jinja2
import codecs
import os
import dataset
import time

def gen_email(email,clientid,campaign):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/yodb?charset=utf8mb4')

    fname=os.path.abspath(__file__)
    elmts=fname.split(os.path.sep)
    path2=os.path.sep.join(elmts[0:-1])
    keysdir=path2+os.path.sep
    print(keysdir)


    templateLoader = jinja2.FileSystemLoader(searchpath=keysdir)
    templateEnv = jinja2.Environment(loader=templateLoader)
    TEMPLATE_FILE = 'test.tmpl'
    template = templateEnv.get_template(TEMPLATE_FILE)


    #SELECT distinct ts_word FROM trending_searches ORDER BY ts_date DESC limit 21;
    cursor=db.query('SELECT distinct ts_word FROM trending_searches ORDER BY ts_date DESC limit 21;')
    display={}
    idx=1
    for c in cursor:
        display['a'+str(idx)]=c['ts_word']
        idx+=1
    display['client']=clientid
    display['email']=email
    display['campaign']=campaign

    outputText = template.render(display=display) 

    fw=codecs.open('c:/tmp/final.html','w','utf-8')
    fw.write(outputText)
    fw.close()



def send_email(campaign,email):
    gmail_user = 'edm@choozmo.com'
    gmail_password='wqdsyqwvppmubitv'

    sent_from = gmail_user
    #to = ['jared@choozmo.com','nina.huang@choozmo.com','ana@choozmo.com','ming@choozmo.com','mike@choozmo.com','andy@choozmo.com','hana@choozmo.com','stacy@choozmo.com','wen@choozmo.com','yukyo@choozmo.com','fxp87257@gmail.com','noodlesloves@gmail.com']
    to = [email]

    msg = MIMEMultipart()
    msg['Subject'] = '['+campaign+'] 趨勢日報 | ChoozMo '
    msg['From'] = 'edm@choozmo.com'
    msg['To'] = email

    msgAlternative = MIMEMultipart('alternative')
    msg.attach(msgAlternative)

    fr=codecs.open('c:/tmp/final.html','r','utf-8')
    content=fr.read()
    fr.close()

    text = MIMEText(content,'html','utf-8')

    msgAlternative.attach(text)
    try:
        server = smtplib.SMTP_SSL('smtp.gmail.com', 465)
        server.ehlo()
        server.login(gmail_user, gmail_password)
        server.sendmail(sent_from, to,  msg.as_string())
        server.close()
        print ('Email sent!')
    except:
        traceback.print_exc()
        print ('Something went wrong...')


db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/cmm_test?charset=utf8mb4')
#cursor=db.query("SELECT id,email FROM cmm_test.edm_list where ugroup='test';")
#cursor=db.query("SELECT id,email FROM cmm_test.edm_list where ugroup='choozmo';")
#cursor=db.query("SELECT id,email FROM cmm_test.edm_list where ugroup='g2';")
#cursor=db.query("SELECT id,email FROM cmm_test.edm_list where ugroup='zec';")
#cursor=db.query("SELECT id,email FROM cmm_test.edm_list where ugroup='core_cust';")
#cursor=db.query("SELECT id,email FROM cmm_test.edm_list where ugroup='g4';")
#cursor=db.query("SELECT id,email FROM cmm_test.edm_list where ugroup='g1';")
cursor=db.query("SELECT id,email FROM cmm_test.edm_list where ugroup='g3';")


for c in cursor:
    clientid=c['id']
    email=c['email']
    campaign='2021-06-13'
    gen_email(email,clientid,campaign)
    send_email(campaign,email)
    time.sleep(30)

#clientid='1'
#email='jeweiliang@gmail.com'