"""
    程式用途
        AI主播寄送郵件。
    參考
        Gmail SMTP: https://kb.synology.com/zh-tw/SRM/tutorial/How_to_use_Gmail_SMTP_server_to_send_emails_for_SRM
"""
import dataset
import smtplib
from email.mime.multipart import MIMEMultipart      # email內容載體
from email.mime.text import MIMEText                # 用於製作文字內文
from email.mime.base import MIMEBase                # 用於承載附檔
from email import encoders                          # 用於附檔編碼
import datetime
import ssl


def get_db(db_name, table_name):
    db = dataset.connect(f'mysql://choozmo:pAssw0rd@db.ptt.cx:3306/{db_name}?charset=utf8mb4')
    user_table = db[table_name]
    return user_table


def mail_to_users(user_id: int, subject: str, contents: str):
    """
        1. 透過id查詢DB, 找出使用者email、username
        2. 訊息制定(如: Aaron, 您的影片於2021/7/12 14:00 "ChoozMo測試影片" 完成了，請前往官網: "url")下載。
        3. 傳送。
            - 根據server不同，可以區分。
            - 先用Gmail。(可以動就好)
    """
    # 連線DB得到使用者郵件、姓名
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    rows = db.query(f'SELECT * FROM users WHERE id={user_id}')
    for row in rows:
        email = row['email']
        name = row['username']
        print(name, email)

    # 寄件者使用的Gmail帳戶資訊
    time_now = datetime.datetime.utcnow() + datetime.timedelta(hours=8)
    with open('password.txt') as f:
        gmail_user, gmail_password = f.readlines()
    # gmail_user = 'aaron@choozmo.com'
    # gmail_password = 'Choozmo1015'
    from_address = gmail_user
    # 設定信件內容與收件人資訊
    to_address = email  if type(email) == type([]) else [email]

    # 開始組合信件內容
    mail = MIMEMultipart()
    mail['From'] = from_address
    mail['To'] = ', '.join(to_address)
    mail['Subject'] = subject
    # 將信件內文加到email中
    mail.attach(MIMEText(contents))
    print(to_address)  

    # 設定smtp伺服器並寄發信件    
    smtpserver = smtplib.SMTP_SSL("smtp.gmail.com", 465)
    smtpserver.ehlo()
    smtpserver.login(gmail_user, gmail_password)
    smtpserver.sendmail(from_address, to_address, mail.as_string())
    smtpserver.quit()


if __name__ == '__main__':
    mail_to_users(4, 'subject', 'contents')