import os
import re
import argparse
import time
import dataset
import pymysql
pymysql.install_as_MySQLdb()

from yaml import parse
import pysrt
import shutil
from datetime import datetime
from PIL import Image,ImageDraw,ImageFont
import gspread
import pandas as pd
import csv

def download_YT(URL=None):
    if URL is None:
        return "No URL"
    else:
        os.remove("test.mp3")
        cmd = "youtube-dl -x --audio-format mp3 -o 'test.mp3' " + URL
        os.system(cmd)
        return "download success"

def srt(sound_file="test.mp3"):
    cmd = "autosub -S zh-TW -D zh-TW " + sound_file
    os.system(cmd)
    sound_srt_file = sound_file.split('.')[0] + ".srt"
    return "to srt success"

def srt_into_db(url,srt_file="test.srt"):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/gnews?charset=utf8mb4')
    subs = pysrt.open(srt_file)
    dict_tmp ={'URL':"",'text':"",'start_time':"",'end_time':""}

    for context in subs:
        #print(context.start.minutes*60+context.start.seconds+ 0.001*context.start.milliseconds)
        dict_tmp['URL'] = url
        dict_tmp['text'] = context.text
        dict_tmp['start_time'] = context.start
        dict_tmp['end_time'] = context.end
        result = db['srt'].insert(dict(dict_tmp))

if __name__ == '__main__':
    parser = argparse.ArgumentParser(description='manual to this script')
    parser.add_argument('--url',type=str,default=None)
    args = parser.parse_args()
    URL = args.url
    #print(download_YT(URL))
    #print(srt())
    srt_into_db(URL)
    print("finish")