HHH_Agent_List/api/main.py

from fastapi import FastAPI,Cookie, Depends, Query, status,File, UploadFile,Request,Response,HTTPException
from fastapi.templating import Jinja2Templates
from fastapi.responses import HTMLResponse, RedirectResponse, JSONResponse
from typing import List, Optional
from os.path import isfile, isdir, join
import threading
import os 
import requests
from bs4 import BeautifulSoup
import asyncio
import urllib.request
from fastapi.responses import FileResponse
from fastapi.middleware.cors import CORSMiddleware
import dataset
from datetime import datetime, timedelta
from fastapi.staticfiles import StaticFiles
import shutil
import io
import pymysql
pymysql.install_as_MySQLdb()

app = FastAPI()


app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],
    allow_credentials=True,
    allow_methods=["*"],
    allow_headers=["*"],
)

'''
資料表: agent_form
欄位:
cellphone (手機)
customer_note (備註)
'''

# 取得資料庫連線
def get_db_connection():
    # 測試機 DB
    db = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57-cluster.cluster-cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/stage?charset=utf8mb4')

    # 正式機 DB
    # db = dataset.connect('mysql://hhh7796hhh:lYmWsu^ujcA1@hhh-v57-cluster.cluster-cmab1ctkglka.ap-northeast-2.rds.amazonaws.com:3306/xoops?charset=utf8mb4')

    return db

# 檢查已登錄過的客戶手機號碼
@app.get("/check")
async def checkCellphone(cellphone: str = ''):
    temp_list = cellphone.split("\n")

    # 手機號碼清單
    cellphone_list = []

    # 避免換行空白問題
    for t in temp_list:
        if t:
            cellphone_list.append(t.strip())

    cellphone_list = "','".join(cellphone_list)

    print ('cellphone_list = ' + cellphone_list)
    
    # 取得資料庫連線
    db = get_db_connection()

    # 取得手機號碼資料
    q = "SELECT * \
        FROM agent_form \
        WHERE cellphone IN ('" + cellphone_list + "')"

    count = len(list(db.query(q)))

    if count == 0: # 無手機號碼資料
        print('無手機號碼資料資料')
        return {"count": 0, "msg": "無手機號碼資料"}
    else:
        # 既有手機號碼資料
        cellphone_list = []
        
        for r1 in db.query(q):
            print('手機號碼: ' + r1['cellphone'] + ' 資料已存在')
            cellphone_list.append(r1['cellphone'])
        
        return {"count": count, "cellphone": cellphone_list}

# checkCellphone('0975-516-501')


# 批次檢查已登錄過的客戶手機號碼

'''
# @app.get("/index2")
# async def index2():
#     return FileResponse('static/index2.html')

@app.get("/index_eng")
async def index2():
    return FileResponse('static/index_eng.html')

# home page
@app.get("/index", response_class=HTMLResponse)
async def get_home_page(request: Request, response: Response):
    return templates.TemplateResponse("index.html", {"request": request, "response": response})
@app.get("/", response_class=HTMLResponse)
async def get_home_page(request: Request, response: Response):
    return templates.TemplateResponse("index.html", {"request": request, "response": response})

@app.get("/make_video", response_class=HTMLResponse)
async def get_home_page(request: Request, response: Response, Authorize: AuthJWT = Depends()):
    try:
        Authorize.jwt_required()
    except Exception as e:
        print(e)
        return '請先登入帳號'
    current_user = Authorize.get_jwt_subject()
    return templates.TemplateResponse("make_video.html", {"request": request, "response": response})

@app.get("/make_video_long", response_class=HTMLResponse)
async def get_home_page(request: Request, response: Response, Authorize: AuthJWT = Depends()):
    try:
        Authorize.jwt_required()
    except Exception as e:
        print(e)
        return '請先登入帳號'
    current_user = Authorize.get_jwt_subject()
    return templates.TemplateResponse("make_video_long.html", {"request": request, "response": response})

@app.get("/make_video_slide", response_class=HTMLResponse)
async def make_video_slide(request: Request, response: Response, Authorize: AuthJWT = Depends()):
    try:
        Authorize.jwt_required()
    except Exception as e:
        print(e)
        return '請先登入帳號'
    current_user = Authorize.get_jwt_subject()
    return templates.TemplateResponse("make_video_slide.html", {"request": request, "response": response})

@app.get('/user_profile', response_class=HTMLResponse)
def protected(request: Request, Authorize: AuthJWT = Depends()):
    Authorize.jwt_required()
    current_user = Authorize.get_jwt_subject()
    return current_user


# login & register page
@app.get("/login", response_class=HTMLResponse)
async def get_login_and_register_page(request: Request):
    return templates.TemplateResponse("login.html", {"request": request})

@app.post("/login")
async def login_for_access_token(request: Request, form_data: OAuth2PasswordRequestForm = Depends(), Authorize: AuthJWT = Depends()):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    user = authenticate_user(form_data.username, form_data.password)
    if not user:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Incorrect username or password",
            headers={"WWW-Authenticate": "Bearer"},
        )
    access_token_expires = timedelta(minutes=ACCESS_TOKEN_EXPIRE_MINUTES)
    access_token = create_access_token(
        data={"sub": user.username}, expires_delta=access_token_expires
    )
    table = db['users']
    user.token = access_token
    table.update(dict(user), ['username'])
    access_token = Authorize.create_access_token(subject=user.username)
    refresh_token = Authorize.create_refresh_token(subject=user.username)
    Authorize.set_access_cookies(access_token)
    Authorize.set_refresh_cookies(refresh_token)
    #return templates.TemplateResponse("index.html", {"request": request, "msg": 'Login'})
    return {"access_token": access_token, "token_type": "bearer"}


@app.post("/token")
async def access_token(form_data: OAuth2PasswordRequestForm = Depends(), Authorize: AuthJWT = Depends()):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    user = authenticate_user(form_data.username, form_data.password)
    if not user:
        raise HTTPException(
            status_code=status.HTTP_401_UNAUTHORIZED,
            detail="Incorrect username or password",
            headers={"WWW-Authenticate": "Bearer"},
        )
    access_token_expires = timedelta(minutes=ACCESS_TOKEN_EXPIRE_MINUTES)
    access_token = create_access_token(
        data={"sub": user.username}, expires_delta=access_token_expires
    )
    return {"access_token": access_token, "token_type": "bearer"}


@app.post("/register")
async def register(request: Request):
    user = models.User(**await request.form())
    user_register(user)
    return templates.TemplateResponse("login.html", {'request': request,"success": True}, status_code=status.HTTP_302_FOUND)

@app.get('/user_profile', response_class=HTMLResponse)
def protected(request: Request, Authorize: AuthJWT = Depends()):
    Authorize.jwt_required()
    current_user = Authorize.get_jwt_subject()
    return current_user



@app.get('/logout')
def logout(request: Request, Authorize: AuthJWT = Depends()):
    Authorize.jwt_required()
    Authorize.unset_jwt_cookies()
    return {"msg": "Successfully logout"}

@app.get("/gen_avatar")
async def avatar():
    return FileResponse('static/gen_avatar.html')

@app.post("/swapFace")
async def swapFace(req:models.swap_req):
    if 'http' not in req.imgurl:
        req.imgurl= 'http://'+req.imgurl
    try:
        im = Image.open(requests.get(req.imgurl, stream=True).raw)
        im= im.convert("RGB")
    except:
        return {'msg':"無法辨別圖片網址"+req.imgurl}
    name_hash = str(time.time()).replace('.','')
    
    x = threading.Thread(target=gen_avatar, args=(name_hash,req.imgurl))
    x.start()
    return {'msg':'人物生成中，請稍候'}

@app.post("/uploadfile/")
async def create_upload_file(file: UploadFile = File(...)):
    img_name = str(time.time()).replace('.','')
    try:
        if file.content_type=='video/mp4':
            async with aiofiles.open(img_upload_folder+img_name+'.mp4', 'wb') as out_file:
                content = await file.read()
                await out_file.write(content) 
            return {"msg": 'www.choozmo.com:8168/'+tmp_img_sub_folder+img_name+'.mp4'}
        else:
            contents = await file.read()
            image = Image.open(io.BytesIO(contents))
            image= image.convert("RGB")
            image.save(img_upload_folder+img_name+'.jpg')
            return {"msg": 'www.choozmo.com:8168/'+tmp_img_sub_folder+img_name+'.jpg'}
    except Exception as e:
        logging.error(traceback.format_exc())
        return {'msg':'檔案無法使用'}

@app.post("/make_anchor_video_gSlide")
async def make_anchor_video_gSlide(req:models.gSlide_req,token: str = Depends(oauth2_scheme)):
    name, text_content, image_urls = gSlide.parse_slide_url(req.slide_url,eng=False)
    if len(image_urls) != len(text_content):
        return {'msg':'副標題數量、圖片(影片)數量以及台詞數量必須一致'}
    for idx in range(len(image_urls)):
        if 'http' not in image_urls[idx]:
            image_urls[idx] = 'http://'+image_urls[idx]
    if req.multiLang==0:
        for txt in text_content:
            if re.search('[a-zA-Z]', txt) !=None:
                print('語言錯誤')
                return {'msg':'輸入字串不能包含英文字!'}
    name_hash = str(time.time()).replace('.','')
    for imgu in image_urls:
        try:
            if get_url_type(imgu) =='video/mp4':
                r=requests.get(imgu)
            else:
                im = Image.open(requests.get(imgu, stream=True).raw)
                im= im.convert("RGB")
        except:
            return {'msg':"無法辨別圖片網址"+imgu}
    user_id = get_user_id(token)
    proto_req = models.request_normal()
    proto_req.text_content = text_content
    proto_req.name = name
    proto_req.image_urls = image_urls
    proto_req.avatar = req.avatar
    proto_req.multiLang = req.multiLang
    save_history(proto_req,name_hash,user_id)
    x = threading.Thread(target=gen_video_queue, args=(name_hash,name, text_content, image_urls,int(req.avatar),req.multiLang,user_id))
    x.start()
    return {"msg":"製作影片需要時間，請您耐心等候，成果會傳送至LINE群組中"} 

@app.post("/make_anchor_video_long")
async def make_anchor_video_long(req:models.request,token: str = Depends(oauth2_scheme)):
    if len(req.image_urls) != len(req.text_content):
        return {'msg':'副標題數量、圖片(影片)數量以及台詞數量必須一致'}
    for idx in range(len(req.image_urls)):
        if 'http' not in req.image_urls[idx]:
            req.image_urls[idx] = 'http://'+req.image_urls[idx]
    if req.multiLang==0:
        for txt in req.text_content:
            if re.search('[a-zA-Z]', txt) !=None:
                print('語言錯誤')
                return {'msg':'輸入字串不能包含英文字!'}
    name_hash = str(time.time()).replace('.','')
    for imgu in req.image_urls:
        try:
            if get_url_type(imgu) =='video/mp4':
                r=requests.get(imgu)
            else:
                im = Image.open(requests.get(imgu, stream=True).raw)
                im= im.convert("RGB")
        except:
            return {'msg':"無法辨別圖片網址"+imgu}
    user_id = get_user_id(token)
    save_history(req,name_hash,user_id)
    x = threading.Thread(target=gen_video_long_queue, args=(name_hash,req.name, req.text_content, req.image_urls,int(req.avatar),req.multiLang,user_id))
    x.start()
    return {"msg":"ok"} 

@app.post("/make_anchor_video")
async def make_anchor_video(req:models.request,token: str = Depends(oauth2_scheme)):
    if len(req.image_urls) != len(req.text_content):
        return {'msg':'副標題數量、圖片(影片)數量以及台詞數量必須一致'}
    for idx in range(len(req.image_urls)):
        if 'http' not in req.image_urls[idx]:
            req.image_urls[idx] = 'http://'+req.image_urls[idx]
    if req.multiLang==0:
        for txt in req.text_content:
            if re.search('[a-zA-Z]', txt) !=None:
                print('語言錯誤')
                return {'msg':'輸入字串不能包含英文字!'}
    name_hash = str(time.time()).replace('.','')
    for imgu in req.image_urls:
        try:
            if get_url_type(imgu) =='video/mp4':
                r=requests.get(imgu)
            else:
                im = Image.open(requests.get(imgu, stream=True).raw)
                im= im.convert("RGB")
        except:
            return {'msg':"無法辨別圖片網址"+imgu}
    user_id = get_user_id(token)
    save_history(req,name_hash,user_id)
    x = threading.Thread(target=gen_video_queue, args=(name_hash,req.name, req.text_content, req.image_urls,int(req.avatar),req.multiLang,user_id))
    x.start()
    return {'msg':'ok'}

@app.post("/make_anchor_video_eng")
async def make_anchor_video_eng(req:models.request_eng):
    if len(req.image_urls) != len(req.sub_titles) or len(req.sub_titles) != len(req.text_content):
        return {'msg':'副標題數量、圖片(影片)數量以及台詞數量必須一致'}
    for idx in range(len(req.image_urls)):
        if 'http' not in req.image_urls[idx]:
            req.image_urls[idx] = 'http://'+req.image_urls[idx]
    name_hash = str(time.time()).replace('.','')
    for imgu in req.image_urls:
        try:
            if get_url_type(imgu) =='video/mp4':
                r=requests.get(imgu)
            else:
                im = Image.open(requests.get(imgu, stream=True).raw)
                im= im.convert("RGB")
        except:
            return {'msg':"無法辨別圖片網址"+imgu}

    save_history(req,name_hash)
    x = threading.Thread(target=gen_video_queue_eng, args=(name_hash,req.name, req.text_content, req.image_urls,req.sub_titles,int(req.avatar)))
    x.start()
    return {"msg":"ok"} 

@app.get("/history_input")
async def history_input(request: Request, Authorize: AuthJWT = Depends()):
    Authorize.jwt_required()
    current_user = Authorize.get_jwt_subject()

    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    user_id = first(db.query('SELECT * FROM users where username="' + current_user +'"'))['id']
    statement = 'SELECT * FROM history_input WHERE user_id="'+str(user_id)+'" ORDER BY timestamp DESC LIMIT 50'

    logs = []
    for row in db.query(statement):
        logs.append({'id':row['id'],'name':row['name'],'text_content':row['text_content'].split(','),'link':row['link'],'image_urls':row['image_urls'].split(',')})
    return logs



@AuthJWT.load_config
def get_config():
    return models.Settings()

@app.exception_handler(AuthJWTException)
def authjwt_exception_handler(request: Request, exc: AuthJWTException):
    return JSONResponse(
        status_code=exc.status_code,
        content={"detail": exc.message}
    )

def get_user_id(token):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    credentials_exception = HTTPException(
        status_code=status.HTTP_401_UNAUTHORIZED,
        detail="Could not validate credentials",
        headers={"WWW-Authenticate": "Bearer"},
    )
    try:
        payload = jwt.decode(token, SECRET_KEY, algorithms=[ALGORITHM])
        username: str = payload.get("sub")
        if username is None:
            raise credentials_exception
        token_data = models.TokenData(username=username)
    except JWTError:
        raise credentials_exception
    user = get_user(username=token_data.username)
    if user is None:
        raise credentials_exception
    user_id = first(db.query('SELECT * FROM users where username="' + user.username+'"'))['id']
    return user_id

def check_user_exists(username):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    if int(next(iter(db.query('SELECT COUNT(*) FROM AI_anchor.users WHERE username = "'+username+'"')))['COUNT(*)']) > 0:
        return True
    else:
        return False

def get_user(username: str):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    if not check_user_exists(username):  # if user don't exist
        return False
    user_dict = next(
        iter(db.query('SELECT * FROM AI_anchor.users where username ="'+username+'"')))
    user = models.User(**user_dict)
    return user
    
def user_register(user):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    table = db['users']
    user.password = get_password_hash(user.password)
    table.insert(dict(user))

def get_password_hash(password):
    return pwd_context.hash(password)
def verify_password(plain_password, hashed_password):
    return pwd_context.verify(plain_password, hashed_password)
def authenticate_user(username: str, password: str):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    if not check_user_exists(username):  # if user don't exist
        return False
    user_dict = next(iter(db.query('SELECT * FROM AI_anchor.users where username ="'+username+'"')))
    user = models.User(**user_dict)
    if not verify_password(password, user.password):
        return False
    return user
def create_access_token(data: dict, expires_delta: Optional[timedelta] = None):
    to_encode = data.copy()
    if expires_delta:
        expire = datetime.utcnow() + expires_delta
    else:
        expire = datetime.utcnow() + timedelta(minutes=15)
    to_encode.update({"exp": expire})
    encoded_jwt = jwt.encode(to_encode, SECRET_KEY, algorithm=ALGORITHM)
    return encoded_jwt

def save_history(req,name_hash,user_id):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    log_table = db['history_input']
    txt_content_seperate_by_dot = ''
    for txt in req.text_content:
        txt_content_seperate_by_dot += txt+","
    txt_content_seperate_by_dot = txt_content_seperate_by_dot[:-1]
    img_urls_seperate_by_dot = ''
    for iurl in req.image_urls:
        img_urls_seperate_by_dot += iurl+","
    img_urls_seperate_by_dot = img_urls_seperate_by_dot[:-1]
    time_stamp = datetime.fromtimestamp(time.time())
    time_stamp = time_stamp.strftime("%Y-%m-%d %H:%M:%S")
    pk = log_table.insert({'name':req.name,'text_content':txt_content_seperate_by_dot,'image_urls':img_urls_seperate_by_dot
    ,'user_id':user_id,'link':'www.choozmo.com:8168/'+video_sub_folder+name_hash+'.mp4','timestamp':time_stamp})
    
def get_url_type(url):
    req = urllib.request.Request(url, method='HEAD', headers={'User-Agent': 'Mozilla/5.0'})
    r = urllib.request.urlopen(req)
    contentType = r.getheader('Content-Type')
    return contentType

def notify_line_user(msg, line_token):
    headers = {
            "Authorization": "Bearer " + line_token,
            "Content-Type": "application/x-www-form-urlencoded"
    }
    params = {"message": msg}   
    r = requests.post("https://notify-api.line.me/api/notify",headers=headers, params=params)

def notify_group(msg):
    glist=['WekCRfnAirSiSxALiD6gcm0B56EejsoK89zFbIaiZQD']
    for gid in glist:
        headers = {
                "Authorization": "Bearer " + gid,
                "Content-Type": "application/x-www-form-urlencoded"
        }
        params = {"message": msg}   
        r = requests.post("https://notify-api.line.me/api/notify",headers=headers, params=params)

def gen_video(name_hash,name,text_content, image_urls,avatar):
    c = rpyc.connect("localhost", 8858)
    c._config['sync_request_timeout'] = None
    remote_svc = c.root
    my_answer = remote_svc.call_video(name_hash,name,text_content, image_urls,avatar) # method call
    shutil.copy(tmp_video_dir+name_hash+'.mp4',video_dest+name_hash+'.mp4')
    os.remove(tmp_video_dir+name_hash+'.mp4')

def gen_video_eng(name_hash,name,text_content, image_urls,sub_titles,avatar):
    c = rpyc.connect("localhost", 8858)
    c._config['sync_request_timeout'] = None
    remote_svc = c.root
    my_answer = remote_svc.call_video_eng(name_hash,name,text_content, image_urls,sub_titles,avatar) # method call
    shutil.copy(tmp_video_dir+name_hash+'.mp4',video_dest+name_hash+'.mp4')
    os.remove(tmp_video_dir+name_hash+'.mp4')

def gen_video_long_queue(name_hash,name,text_content, image_urls,avatar,multiLang,user_id):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    time_stamp = datetime.fromtimestamp(time.time()).strftime("%Y-%m-%d %H:%M:%S")
    txt_content_seperate_by_dot = ''
    for txt in text_content:
        txt_content_seperate_by_dot += txt+","
    txt_content_seperate_by_dot = txt_content_seperate_by_dot[:-1]
    img_urls_seperate_by_dot = ''
    for iurl in image_urls:
        img_urls_seperate_by_dot += iurl+","
    img_urls_seperate_by_dot = img_urls_seperate_by_dot[:-1]
    
    db['video_queue'].insert({'name_hash':name_hash,'name':name,'text_content':txt_content_seperate_by_dot,'image_urls':img_urls_seperate_by_dot,'multiLang':multiLang,'avatar':avatar,'timestamp':time_stamp})
    while True:
        
        if first(db.query('SELECT * FROM video_queue_status'))['status'] == 1:#only one row in this table, which is the id 1 one
            print('another process running, leave loop')#1 means already running
            break
        if first(db.query('SELECT COUNT(1) FROM video_queue'))['COUNT(1)'] == 0:
            print('all finish, leave loop')
            break
        top1 = first(db.query('SELECT * FROM video_queue'))
        try:
        # if True:
            db.query('UPDATE video_queue_status SET status = 1;')
            c = rpyc.connect("localhost", 8858)
            c._config['sync_request_timeout'] = None
            remote_svc = c.root
            my_answer = remote_svc.call_video_gen(top1['name_hash'],top1['name'],top1['text_content'].split(','), top1['image_urls'].split(','),top1['multiLang'],top1['avatar']) # method call
            shutil.copy(tmp_video_dir+top1['name_hash']+'.mp4',video_dest+top1['name_hash']+'.mp4')
            os.remove(tmp_video_dir+top1['name_hash']+'.mp4')
            vid_duration = VideoFileClip(video_dest+top1['name_hash']+'.mp4').duration
            user_obj = first(db.query('SELECT * FROM users where id ="'+str(user_id)+'"'))
            line_token = user_obj['line_token']         # aa
            left_time = user_obj['left_time']
            email = user_obj['email']
            print('left_time is '+str(left_time))
            if left_time is None:
                left_time = 5*60
            if left_time < vid_duration:
                msg = '您本月額度剩下'+str(left_time)+'秒,此部影片有'+str(vid_duration)+'秒, 若要繼續產生影片請至 192.168.1.106:8887/confirm_add_value?name_hash='+name_hash+' 加值'
                print(msg)
                msg =msg.encode(encoding='utf-8')
                mailer.send(msg, email)
                #notify_line_user(msg, line_token)
                notify_group(name+"：帳號餘額不足，請至email查看詳細資訊")
            else:
                left_time = left_time - vid_duration
                db.query('UPDATE users SET left_time ='+str(left_time)+' WHERE id='+str(user_id)+';')
                notify_group(name+"的影片已經產生完成囉! www.choozmo.com:8168/"+video_sub_folder+name_hash+".mp4")
                #notify_line_user(name+"的影片已經產生完成囉! www.choozmo.com:8168/"+video_sub_folder+name_hash+".mp4", line_token)
        except Exception as e:
            logging.error(traceback.format_exc())
            print('video generation error')
            #notify_group('長影片錯誤-測試')
        db['video_queue'].delete(id=top1['id'])
        db.query('UPDATE video_queue_status SET status = 0')

def gen_video_queue(name_hash,name,text_content, image_urls,avatar,multiLang,user_id):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    time_stamp = datetime.fromtimestamp(time.time()).strftime("%Y-%m-%d %H:%M:%S")
    txt_content_seperate_by_dot = ''
    for txt in text_content:
        txt_content_seperate_by_dot += txt+","
    txt_content_seperate_by_dot = txt_content_seperate_by_dot[:-1]
    img_urls_seperate_by_dot = ''
    for iurl in image_urls:
        img_urls_seperate_by_dot += iurl+","
    img_urls_seperate_by_dot = img_urls_seperate_by_dot[:-1]
    
    db['video_queue'].insert({'name_hash':name_hash,'name':name,'text_content':txt_content_seperate_by_dot,'image_urls':img_urls_seperate_by_dot,'multiLang':multiLang,'avatar':avatar,'timestamp':time_stamp})
    while True:
        
        if first(db.query('SELECT * FROM video_queue_status'))['status'] == 1:#only one row in this table, which is the id 1 one
            print('another process running, leave loop')#1 means already running
            break
        if first(db.query('SELECT COUNT(1) FROM video_queue'))['COUNT(1)'] == 0:
            print('all finish, leave loop')
            break
        top1 = first(db.query('SELECT * FROM video_queue'))
        try:
        # if True:
            db.query('UPDATE video_queue_status SET status = 1;')
            c = rpyc.connect("localhost", 8858)
            c._config['sync_request_timeout'] = None
            remote_svc = c.root
            my_answer = remote_svc.call_video(top1['name_hash'],top1['name'],top1['text_content'].split(','), top1['image_urls'].split(','),top1['multiLang'],top1['avatar']) # method call
            shutil.copy(tmp_video_dir+top1['name_hash']+'.mp4',video_dest+top1['name_hash']+'.mp4')
            os.remove(tmp_video_dir+top1['name_hash']+'.mp4')
            vid_duration = VideoFileClip(video_dest+top1['name_hash']+'.mp4').duration
            user_obj = first(db.query('SELECT * FROM users where id ="'+str(user_id)+'"'))
            line_token = user_obj['line_token']         # aa
            left_time = user_obj['left_time']
            email = user_obj['email']
            print('left_time is '+str(left_time))
            if left_time is None:
                left_time = 5*60
            if left_time < vid_duration:
                msg = '您本月額度剩下'+str(left_time)+'秒,此部影片有'+str(vid_duration)+'秒, 若要繼續產生影片請至 192.168.1.106:8887/confirm_add_value?name_hash='+name_hash+' 加值'
                print(msg)
                msg =msg.encode(encoding='utf-8')
                mailer.send(msg, email)
                notify_line_user(msg, line_token)
            else:
                left_time = left_time - vid_duration
                db.query('UPDATE users SET left_time ='+str(left_time)+' WHERE id='+str(user_id)+';')
                notify_group(name+"的影片已經產生完成囉! www.choozmo.com:8168/"+video_sub_folder+name_hash+".mp4")
                #notify_line_user(name+"的影片已經產生完成囉! www.choozmo.com:8168/"+video_sub_folder+name_hash+".mp4", line_token)
        except Exception as e:
            logging.error(traceback.format_exc())
            print('video generation error')
            notify_group('影片錯誤')
        db['video_queue'].delete(id=top1['id'])
        db.query('UPDATE video_queue_status SET status = 0')

def gen_video_queue_eng(name_hash,name,text_content, image_urls,sub_titles,avatar):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    time_stamp = datetime.fromtimestamp(time.time()).strftime("%Y-%m-%d %H:%M:%S")
    txt_content_seperate_by_dot = ''
    for txt in text_content:
        txt_content_seperate_by_dot += txt+","
    txt_content_seperate_by_dot = txt_content_seperate_by_dot[:-1]
    img_urls_seperate_by_dot = ''
    for iurl in image_urls:
        img_urls_seperate_by_dot += iurl+","
    img_urls_seperate_by_dot = img_urls_seperate_by_dot[:-1]
    subtitles_seperate_by_dot = ''
    for sub in sub_titles:
        subtitles_seperate_by_dot += sub+","
    subtitles_seperate_by_dot = subtitles_seperate_by_dot[:-1]
    db['video_queue'].insert({'name_hash':name_hash,'name':name,'text_content':txt_content_seperate_by_dot,'image_urls':img_urls_seperate_by_dot,'subtitles':subtitles_seperate_by_dot,'avatar':avatar,'timestamp':time_stamp})
    while True:
        if first(db.query('SELECT * FROM video_queue_status'))['status'] == 1:#only one row in this table, which is the id 1 one
            print('another process running, leave loop')
            break
        if first(db.query('SELECT COUNT(1) FROM video_queue'))['COUNT(1)'] == 0:
            print('all finish, leave loop')
            break
        top1 = first(db.query('SELECT * FROM video_queue'))
        try:
            db.query('UPDATE video_queue_status SET status = 1;')
            c = rpyc.connect("localhost", 8858)
            c._config['sync_request_timeout'] = None
            remote_svc = c.root
            my_answer = remote_svc.call_video_eng(top1['name_hash'],top1['name'],top1['text_content'].split(','), top1['image_urls'].split(','),top1['subtitles'].split(','),top1['avatar']) # method call
            shutil.copy(tmp_video_dir+top1['name_hash']+'.mp4',video_dest+top1['name_hash']+'.mp4')
            os.remove(tmp_video_dir+top1['name_hash']+'.mp4')
        except:
            print('video generation error')
            notify_group('影片錯誤')
        db['video_queue'].delete(id=top1['id'])
        db.query('UPDATE video_queue_status SET status = 0')

def gen_avatar(name_hash, imgurl):
    db = dataset.connect('mysql://choozmo:pAssw0rd@db.ptt.cx:3306/AI_anchor?charset=utf8mb4')
    db['avatar_queue'].insert({'name_hash':name_hash,'imgurl':imgurl})
    while True:
        statement = 'SELECT * FROM avatar_service_status'#only one row in this table, which is the id 1 one
        status = -1
        for row in db.query(statement):
            status = row['status']
        if status == 1:
            print('leave process loop')
            break

        statement = 'SELECT * FROM avatar_queue'
        works = []
        for row in db.query(statement):
            works.append({'id':row['id'],'name_hash':row['name_hash'],'imgurl':row['imgurl']})
        if len(works)==0:
            print('leave process loop')
            break
        try:
            statement = 'UPDATE avatar_service_status SET status = 1 WHERE id=1;'
            db.query(statement)
            name_hash = works[0]['name_hash']
            imgurl = works[0]['imgurl']
            c = rpyc.connect("localhost", 8868)
            c._config['sync_request_timeout'] = None
            remote_svc = c.root
            my_answer = remote_svc.call_avatar(name_hash,imgurl) # method call
            shutil.copy(tmp_avatar_dir+name_hash+'.mp4',avatar_dest+name_hash+'.mp4')
            os.remove(tmp_avatar_dir+name_hash+'.mp4')
            
        except:
            print('gen error')
            notify_group('無法辨識人臉')
        db['avatar_queue'].delete(id=works[0]['id'])
        statement = 'UPDATE avatar_service_status SET status = 0 WHERE id=1;'  #only one row in this table, which id 1 one
        db.query(statement)
'''