ver=640
import shutil
import subprocess
import tempfile

from pydub import AudioSegment
import sqlite3
from urllib.parse import quote
import datetime
import json
import logging
import os
import threading
import time
import uuid
from flask import Flask, jsonify,send_file,request
import requests
from collections import deque
def trsstc(cnstr):
    try:
        url='http://localhost:8022/api/translate'
        url='http://localhost/api/translate'
        data={"source_lang":"zh","text": cnstr,"target_lang": "en"}
        resp=requests.post(url,json=data)
        retdata=resp.json()
        return retdata['result']
    except Exception as ex:
        print('xxx at trs',ex)
def writesrt_old(srtdata,srtf):
    try:
        with open(srtf,'w',encoding='utf-8')as f:
            json.dump(srtdata,f,ensure_ascii=False)
    except Exception as ex:
        print(ex)
def writetrser(srtf,stcidx,trser):
    try:
        data=readsrtdata(srtf)
        data[stcidx]['trser']=trser
        with open(srtf,'w',encoding='utf-8')as f:
            json.dump(data,f,ensure_ascii=False)
    except Exception as ex:
        print(ex)
def readsrt(srtf):
    try:
        with open(srtf,encoding='utf-8')as f:
            srtdata=json.load(f)
            for stcidx,stc in enumerate(srtdata):
                try:
                    enstr=stc['en']
                    continue
                except:
                    pass        
                try:
                    trser=stc['trser']
                    print('trser:',trser)
                    if trser!=uuid.getnode():
                       print('not me')
                       continue
                except:
                    stc['trser']=uuid.getnode()
                    writesrt(srtdata,srtf)
                    return -1
                cnstr=stc['text']
                enstr=trsstc(stc['text'])
                print(stcidx+1,len(srtdata),cnstr,enstr)
                stc['en']=enstr
                del stc['trser']
                writesrt(srtdata,srtf)
                return 1
            return 9
        maker=srtdata['maker']
        return maker==uuid.getnode()
    except FileNotFoundError as ex:
        print('尚无字幕')
        return True
    except TypeError as ex:
        print(ex)
        print('已译')
        return False
    except Exception as ex:
        print('xxx',ex)
def readsrtdata(srtf):
    try:
        with open(srtf,encoding='utf-8')as f:
            srtdata=json.load(f)
            if srtdata:
                return srtdata
    except Exception as ex:
        print('xxx readsrtdata:',ex)
    return []
def writesrt(trser,srtf,idxinsrtfle,enstr,idx):
    try:
        data=readsrtdata(srtf)
        if enstr=='':                   #空格作为已完成翻译的标志
            enstr=' '
        data[idxinsrtfle]['en']=enstr
        data[idxinsrtfle]['trser']=trser
        with open(srtf,'w',encoding='utf-8')as f:
            json.dump(data,f,ensure_ascii=False)
    except Exception as ex:
        print(ex)
def readsrt(srtf):
    try:
        stcidx=0
        while True:
            data=readsrtdata(srtf)
            if stcidx>len(data)-2:
                return 9
            stc=data[stcidx]
            try:
                enstr=stc['en']
                stcidx+=1
                continue
            except:
                pass
            try:
                trser=stc['trser']
            except Exception as ex:                
                trser=uuid.getnode()
                savetrserrt(srtf,stcidx,trser)
                return -1
            cnstr=stc['text']
            print(stcidx+1,len(data),cnstr)
            print('trser:',trser)
            if trser!=uuid.getnode():
                print('has trser,but not me('+str(uuid.getnode())+')')
                stcidx+=1                
                continue
            enstr=trsstc(stc['text'])
            print(enstr)
            writesrt(srtf,stcidx,enstr)
            return 1                    
        with open(srtf,encoding='utf-8')as f:
            srtdata=json.load(f)
            for stcidx,stc in enumerate(srtdata):
                try:
                    enstr=stc['en']
                    continue
                except:
                    pass        
                try:
                    trser=stc['trser']
                    print('trser:',trser)
                    if trser!=uuid.getnode():
                       print('not me')
                       continue
                except:
                    stc['trser']=uuid.getnode()
                    writesrt(srtdata,srtf)
                    return -1
                cnstr=stc['text']
                enstr=trsstc(stc['text'])
                print(stcidx+1,len(srtdata),cnstr,enstr)
                stc['en']=enstr
                del stc['trser']
                writesrt(srtdata,srtf)
                return 1
            return 9
        maker=srtdata['maker']
        return maker==uuid.getnode()
    except FileNotFoundError as ex:
        print('尚无字幕')
        return True
    except TypeError as ex:
        print(ex)
        print('已译')
        return False
    except Exception as ex:
        print('xxx',ex)
def get_files_by_type(path, ext):
    return [os.path.join(path, f) for f in os.listdir(path) if f.lower().endswith(ext)]
def loop():
    files=get_files_by_type(dir,".m4a")
    files=sorted(files, key=lambda x: os.path.getmtime(os.path.join(dir, x)), reverse=True)
    for f in files:
        srtf=f[:-3]+'srt'
        if os.path.getmtime(srtf)<(datetime.datetime.now()-datetime.timedelta(days=4)).timestamp():
            print('too old,skip:',srtf)
            return       
        print(srtf)
        while True:
            ret=readsrt(srtf)
            if ret==1:
                continue
            if ret!=-1:
                break
dir=rf'\\ll\c\wxvddata'
dir=r'''c:\wxvddata'''
def getstc2trs(trser):
    for idx,srt in enumerate(srts):
        try:
            if len(srt['en'])>0:
                continue
        except Exception as ex:
            print(srt)
        srtf=srt['srtf']
        srtf=srtf[:-3]+'srt'
        idxinsrtfile=srt['idx']
        cnstr=srt['cn']
        savedtrser=srt['trser']
        if savedtrser=="":
            print('new stc:',idx,trser,srt)
            srt['trser']=trser
            return srtf,idxinsrtfile,cnstr,idx
        if savedtrser==trser:
            print('my last working stc:',trser,idx,srt)
            return srtf,idxinsrtfile,cnstr,idx
    return '',-1,'',-1
app = Flask(__name__)
app.config['JSON_AS_ASCII'] = False # 禁用 ASCII 编码
app.json.ensure_ascii=False
lock = threading.Lock()
trsers=[("xb",22087386184820),("n",211365433288380),("de",216438562869218)]
# @app.route('/')
def rt_():
    files=get_files_by_type(dir,".m4a")
    files=sorted(files, key=lambda x: os.path.getmtime(os.path.join(dir, x)), reverse=True)
    htm=''
    htm+=str(tstidxinsrtfile+1)
    htm+=' '
    htm+=tstsrtf[16:-4]
    htm+='<br>'
    htm+=str(tstidx+1)
    htm+='/'
    htm+=str(len(srts))
    htm+=' '
    htm+=tstcnstr
    htm+='<br>'
    htm+='<a href=/play>'
    htm+='播放mp3'
    htm+='</a>'
    htm+=' '
    htm+='<a href=/reqstc/121212>'
    htm+='tst req srt'
    htm+='</a>'
    htm+=' '
    htm+=f'<a href=/savesrt/121212/{quote(tstsrtf,safe='')}/{tstidxinsrtfile}/{quote(tstcnstr,safe='')}/{tstidx}>'
    htm+='tst save srt'
    htm+='</a>'
    htm+=' '
    htm+=f'<a href=/resetsrt>'
    htm+='reest top ad'
    htm+='</a>'
    htm+=' '
    htm+=f'<a href=/lssrts>'
    htm+='list srts'
    htm+='</a>'

    htm+='<br>'
    for f in files:
        srtf=f[:-3]+'srt'
        fn=srtf[16:]
        try:
            if os.path.getmtime(srtf)<(datetime.datetime.now()-datetime.timedelta(days=4)).timestamp():
                print('too old,skip:',srtf)
                break
            mp3=srtf[16:-3]+'mp3'
            htm+=rf'<a href=/mp3/{mp3}>'
            htm+='播放mp3'
            htm+='</a>'
            htm+=' '
            htm+=rf'<a href=/cnstr/{fn}>'
            htm+='中文'
            htm+='</a>'
        except:
            htm+='(字幕生成中...)'
        htm+=' '
        htm+=rf'<a href=/srt/{fn}>'
        htm+=fn
        htm+='</a>'
        htm+='<br>'
    return htm
@app.route('/')
def rt():
    rs=readads()
    htm=''
    htm+=str(tstidxinsrtfile+1)
    htm+=' '
    htm+=tstsrtf[16:-4]
    htm+='<br>'
    htm+=str(tstidx+1)
    htm+='/'
    htm+=str(len(srts))
    htm+=' '
    htm+=tstcnstr
    htm+='<br>'
    htm+='<a href=/play>'
    htm+='播放mp3'
    htm+='</a>'
    htm+=' '
    htm+='<a href=/reqstc/121212>'
    htm+='tst req srt'
    htm+='</a>'
    htm+=' '
    htm+=f'<a href=/savesrt/121212/{quote(tstsrtf,safe='')}/{tstidxinsrtfile}/{quote(tstcnstr,safe='')}/{tstidx}>'
    htm+='tst save srt'
    htm+='</a>'
    htm+=' '
    htm+=f'<a href=/resetsrt>'
    htm+='reest top ad'
    htm+='</a>'
    htm+=' '
    htm+=f'<a href=/lssrts>'
    htm+='list srts'
    htm+='</a>'
    htm+='<br>'
    htm+='<a href=/trshis>trs history</a>'
    htm+=' '
    htm+='trs中: '    
    try:
        htm+='idx'+str(histrsads[-1][0])+' cnt:'+str(histrsads[-1][1])+'/tot:'+str(histrsads[-1][2])+' '+histrsads[-1][3]
    except Exception as ex:
        htm+=''
    htm+='<br>'
    htm+='<a href=/ttshis>tts history</a>'
    htm+=' '
    htm+='tts中: '    
    try:
        htm+='idx'+str(histtsads[-1][0])+' cnt:'+str(histtsads[-1][1])+'/tot:'+str(histtsads[-1][2])+' '+histtsads[-1][3]
    except Exception as ex:
        htm+=''
    htm+='<br>'
    htm+='<a href=/joinclipshis>join clips his</a>'
    htm+=' '
    try:
        htm+='idx'+str(histtsads[-1][0])+' cnt:'+str(histtsads[-1][1])+'/tot:'+str(histtsads[-1][2])+' '+histtsads[-1][3]
    except Exception as ex:
        htm+=''
    htm+='<br>'
    for r in rs:
        createdt=r[3][5:-3]
        htm+=createdt
        htm+=' '
        adid=r[0]
        f=r[1]
        srtf=f+'.srt'
        fn=srtf[16:]
        bili=r[16]
        if bili<1:
            htm+='无中字'
        else:
            mp3=f+'.mp3'
            htm+=rf'<a href=/mp3/{mp3}>'
            htm+='播放mp3'
            htm+='</a>'
            htm+=' '
            htm+=rf'<a href=/srt/{adid}>'
            htm+='字幕'
            htm+='</a>'
            htm+=' '
            htm+=rf'<a href=/cnstr/{adid}>'
            htm+='中字'
            htm+='</a>'
            htm+=' '
            if bili>1:
                htm+=rf'<a href=/en/{adid}>'
                htm+='英字</a>'
                htm+=' '
                htm+=rf'<a href=/reseten/{adid}>'
                htm+='重做英字</a>'
            else:
                htm+='无英字'
        htm+=' '
        htm+=rf'<a href=/resetcn/{adid}>'
        htm+='重做中字</a>'
        htm+=' '
        htm+=rf'<a href=/en/{adid}>'
        htm+='英字</a>'
        htm+=' '
        htm+=rf'<a href=/reseten/{adid}>'
        htm+='重做英字</a>'
        htm+=' '
        htm+=rf'<a href=/resettts/{adid}>'
        htm+='重合音频</a>'
        htm+=' '
        htm+=f
        htm+='<br>'
    return htm
@app.route('/resetsrt')
def resetrt():
    resetsrt()
    return ''
@app.route('/lssrts')
def lssrtrt():
    htm=''
    for srt in srts:
        htm+=srt['srtf']
        htm+=' '
        htm+=str(srt['idx'])
        htm+=' '
        htm+=str(srt['trser'])
        htm+=' '
        htm+=srt['cn']
        htm+=' '
        htm+=srt['en']
        htm+='<br>'
    return htm
@app.route('/play')
@app.route('/p')
def mp3listrt():
    m4adir=rf'\\ll\c\wxvddata'
    mp3dir=rf'\\ll\c\wxvddata\mp3'
    files=readbili3ads()
    htm=''
    htm+='''
<head>
    <meta name="viewport" content="width=device-width, initial-scale=2">
</head>
'''
    htm+='tts中: '    
    try:
        htm+='idx'+str(histtsads[-1][0])+' cnt:'+str(histtsads[-1][1])+'/tot:'+str(histtsads[-1][2])+' '+histtsads[-1][3]
    except Exception as ex:
        htm+=''
    htm+='<br>'
    for f in files:
        adname=f[1]
        mp3f=adname+'.mp3'
        htm+=' '
        htm+=rf'''
        <audio controls playbackRate=0.75  style="width:100%;height: 60px">
        <source src="/mp3/{mp3f}" type="audio/mpeg">
        您的浏览器不支持此音频格式。
        </audio>
'''
        htm+=rf'<a href=/mp3/{mp3f}>'
        htm+=adname
        htm+='</a>'
        htm+='<br>'
    return htm
@app.route('/ttshis')
def ttshisrt():
    htm=''
    for f in reversed(histtsads):
        adname=f[3]
        dt=f[4]
        ttser=f[5]
        htm+=dt
        htm+=' '
        htm+='idx:'
        htm+=rf'{f[0]}'
        htm+=' '
        htm+='cnt/tot:'
        htm+=rf'{f[1]}'
        htm+='/'
        htm+=rf'{f[2]}'
        htm+=rf' <font color="red">{ttser}</font>'
        htm+=' '
        htm+=rf'{adname}'
        htm+='<br>'
    return htm
@app.route('/trshis')
def trshisrt():
    htm=''
    for f in reversed(histrsads):
        # histrsads.append((adid,srtid,idx,r[0],enstr,trsername))
        nottrs=f[3]
        enstr=f[4]
        ttser=f[5]
        dt=f[6]
        htm+=dt+' '
        htm+='adid:'
        htm+=rf'{f[0]}'
        htm+=' '
        htm+=rf'srtid:{f[1]}'
        htm+=' '
        htm+=rf'idx:{f[2]}'
        htm+=' '
        htm+=rf'nottrs:{nottrs}'
        htm+=rf' <font color="red">{ttser}</font>'
        htm+=' '
        htm+=rf'{enstr}'
        htm+='<br>'
    return htm
@app.route('/joinclipshis')
def joinclipshisrt():
    htm=''
    for f in reversed(hisjoinsrtclips):
        # histrsads.append((adid,srtid,idx,r[0],enstr,trsername))
        htm+=rf'{f}'
        htm+='<br>'
    return htm
def save4trsend(adid,srtid,idx,enstr,trser,trsername):
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()    
    # sqlstr = rf'''select count(*) from srts where adid=?'''
    # re=cur.execute(sqlstr,(adid))
    # idx=re.fetchone[0]
    try:
        sqlstr = rf'''update srts set en=?,trser=?,dt=datetime('now','localtime') where rowid=?'''    
        sqlstr = rf'''update srts set en=?,trser=?,trsenddt=datetime('now','localtime') where rowid=?'''    
        re=cur.execute(sqlstr,(enstr,trser,srtid))
        re=conn.commit()
        sqlstr = rf'''select count(*) from srts where adid=? and en=""'''    
        re=cur.execute(sqlstr,(adid,))        
        r=re.fetchone()
        print('rrr end trs end,idx:',idx,'未译数:',r[0])
        now=str(datetime.datetime.now())[5:-7]
        histrsads.append((adid,srtid,idx,r[0],enstr,trsername,now))
        if r[0]==0:
            print('ad trs end')
            sqlstr = rf'''update srts set bili=2,bilidt=datetime('now','localtime'),adtrsenddt=datetime('now','localtime') where adid={adid}'''     #bili:1 中,2 英,3 tts
            re=cur.execute(sqlstr)            
            sqlstr = rf'''update ads set bili=2,bilidt=datetime('now','localtime'),trsenddt=datetime('now','localtime') where rowid={adid}'''     #bili:1 中,2 英,3 tts
            # re=cur.execute(sqlstr)
            # sqlstr = rf'''update ads set bili=2 where rowid={adid}'''
            re=cur.execute(sqlstr)          
            re=conn.commit()
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    # return 0
def combinedmp3ok(adname):
    mp3f=adname+'.mp3'
    try:
        totorgad=AudioSegment.from_file(mp3f)
        print('mp3 len(s):',int(len(totorgad)/1000))
        return len(totorgad)>0
    except Exception as ex:
        print('detecting mp3 file:',ex)
def createpub(org,mp3f,speed=0.8):
    target_dir = r"/xtrs/pub"
    dst= os.path.join(target_dir, os.path.basename(mp3f))
    cmd = [
        'ffmpeg', '-y', '-i', org,
        '-filter:a', f'atempo={speed}',
        dst
    ]    
    subprocess.run(cmd, check=True)
def jnadsegs(adid,srtdata,adname):
    now=str(datetime.datetime.now())[5:-7]
    hisjoinsrtclips.append(f'{now} {adid} {adname}')
    mp3f=adname+'.mp3'
    orgadf=adname+'.m4a'
    try:
        totorgad=AudioSegment.from_file(orgadf)
    except Exception as ex:
        print('xxx', ex)
        try:
            totorgad=AudioSegment.from_file(mp3f)
        except Exception as ex:
            print('xxx', ex)
            return
    slc=AudioSegment.silent(duration=400)
    dstad=AudioSegment.empty()
    startts=0
    clipcnt=0
    lastend=0    
    interval=400
    for idx,stc in enumerate(srtdata):
        # if idx==0:
        #     startts=0
        # else:
        #     startts=srtdata[idx-1][10]          #end timstamp
        evrclipt=AudioSegment.from_file(clipad)
        start=stc[9]
        if stc[9]-lastend<interval*2:
            start-=400
            
            dstad+=totorgad+slc+evrclipt

        if idx==len(srtdata)-1:
            cut_audio = totorgad[startts:]
        else:
            endts=srtdata[idx+1][9]       #start timestamp         
            cut_audio = totorgad[startts:endts]
        clipad=("%09d"%adid+"-%04d"%idx+'.mp3')
        try:
            # evrclipt=AudioSegment.from_file(clipad)
            dstad+=cut_audio + slc+evrclipt+slc
            clipcnt+=1
        except Exception as ex:
            print('warn:',ex)
            dstad+=cut_audio + slc
        startts=stc[10]          #end timstamp
        lastend=stc[10]
    now=str(datetime.datetime.now())[5:-7]
    hisjoinsrtclips.append(f'{now} {adid} {adname} clip cnt:{clipcnt}')
    target_dir = r"\\ll\d\wxvddata"
    target_file = os.path.join(target_dir, os.path.basename(mp3f))
    with tempfile.NamedTemporaryFile(suffix=".mp3", mode='wb', delete=False) as tmp:
        temp_path = tmp.name
        try:
            # pydub 导出到该路径（它会自动以二进制流处理）
            dstad.export(temp_path, format="mp3")            
            shutil.copy2(temp_path,target_file)            
        finally:
            pass
            # 确保逻辑执行完后，手动删除文件（模拟 delete=True）
            # if os.path.exists(temp_path):
            #     os.remove(temp_path)
            #     print("临时文件已清理")
    # with tempfile.NamedTemporaryFile(mode='w+', delete=True, suffix='.mp3') as temp:
    #     dstad.export(temp, format("mp3"))
    #     target_dir = r"\\ll\d\wxvddata"
    #     target_file = os.path.join(target_dir, os.path.basename(mp3f))
    #     shutil.copy2(temp,target_file)
    createpub(target_file,mp3f)
    delclipcnt=0
    for idx,stc in enumerate(srtdata):
        clipad=("%09d"%adid+"-%04d"%idx+'.mp3')
        try:
            os.remove(clipad)
            delclipcnt+=1
        except Exception as ex:
            print('xxx',ex)
    now=str(datetime.datetime.now())[5:-7]
    hisjoinsrtclips.append(f'{now} {adid} {adname} del clip cnt:{clipcnt}')
histrsads=deque(maxlen=1000)
histtsads=deque(maxlen=1000)
hisjoinsrtclips=deque(maxlen=2000)
def jnadsegsandupdatedb(adid,srtsdata,adname,ttser,srtid,totsrts):
    jnadsegs(adid,srtsdata,adname)
    try:
        conn = sqlite3.connect(dbfile)
        cur = conn.cursor()    
        # sqlstr = rf'''update srts set tts=1,ttser=?,ttsenddt=datetime('now','localtime') where rowid=?'''    
        # re=cur.execute(sqlstr,(ttser,srtid))
        sqlstr = rf'''update srts set bili=3,bilidt=datetime('now','localtime') where adid={adid}'''     #bili:1 中,2 英,3 tts
        re=cur.execute(sqlstr)            
        sqlstr = rf'''update ads set bili=3,bilidt=datetime('now','localtime'),tts=1,ttscnt={totsrts} where rowid={adid}'''     #bili:1 中,2 英,3 tts
        re=cur.execute(sqlstr)            
        re=conn.commit()
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
def jnadsegthd(adid,srtsdata,adname,ttser,srtid,totsrts):
    # 创建并启动后台线程
    thread = threading.Thread(
        target=jnadsegsandupdatedb,
        args=(adid,srtsdata,adname,ttser,srtid,totsrts)
    )
    thread.start()    
def updatettsinfo(srtid,ttser,adid,adname,totsrts,srtidx,ttsername='',dur=0):
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    # sqlstr = rf'''select count(*) from srts where adid=?'''
    # re=cur.execute(sqlstr,(adid))
    # idx=re.fetchone[0]
    # adid=0
    # adname=''
    srtsdata=[]
    try:
        sqlstr = rf'''select count(*) from srts where adid=? and ttsenddt>""'''    
        re=cur.execute(sqlstr,(adid,))        
        r=re.fetchone()
        ttscnt=r[0]+1
        now=str(datetime.datetime.now())[5:-7]
        histtsads.append((srtidx,ttscnt,totsrts,adname,now,ttsername,3))
        sqlstr = rf'''update ads set ttscnt=? where rowid=?'''    
        re=cur.execute(sqlstr,(ttscnt,adid,))
        print('ttt end tts end, srtidx/ttscnt/tot:',srtidx,ttscnt,totsrts,adname)
        sqlstr = rf'''update srts set tts=1,ttser=?,ttsenddt=datetime('now','localtime'),ttsdur=? where rowid=?'''    
        re=cur.execute(sqlstr,(ttser,dur,srtid))
        re=conn.commit()
        if ttscnt<totsrts:
            return
        sqlstr = rf'''select rowid,* from srts where adid=? order by idx'''
        conn.row_factory = sqlite3.Row    
        re=cur.execute(sqlstr,(adid,))        
        srtsdata=re.fetchall()        
    except Exception as ex:
        print('xxx',ex)
        return
    finally:
        cur.close()
        conn.close()
    print('all ttsed')
    now=str(datetime.datetime.now())[5:-7]
    # histtsads.append((adid,totsrts,adname,now))
    # sqlstr = rf'''select count(*) from srts where adid=?'''
    # re=cur.execute(sqlstr,(adid))
    # idx=re.fetchone[0]
    jnadsegthd(adid,srtsdata,adname,ttser,srtid,totsrts)
    return
    joinadsrtclip(adid,srtsdata,adname)
    try:
        conn = sqlite3.connect(dbfile)
        cur = conn.cursor()    
        sqlstr = rf'''update srts set tts=1,ttser=?,ttsenddt=datetime('now','localtime') where rowid=?'''    
        re=cur.execute(sqlstr,(ttser,srtid))
        sqlstr = rf'''update srts set bili=3,bilidt=datetime('now','localtime') where adid={adid}'''     #bili:1 中,2 英,3 tts
        re=cur.execute(sqlstr)            
        sqlstr = rf'''update ads set bili=3,bilidt=datetime('now','localtime'),tts=1,ttscnt={totsrts} where rowid={adid}'''     #bili:1 中,2 英,3 tts
        re=cur.execute(sqlstr)            
        re=conn.commit()
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    # return 0
@app.route('/readsrt/<srtf>/<int:trser>')
def readsrtrt(srtf,trser):
    with lock: # 自动加锁解锁，保证
        data=readsrtdata(srtf)
        return jsonify(data)
@app.route('/reqstc/<int:trser>')
def reqstcrt(trser):
    global tstidxinsrtfile,tstsrtf,tstcnstr,tstidx
    with lock: # 自动加锁解锁，保证
        srtf,idxinsrtfile,cnstr,tstidx=getstc2trs(trser)
        tstidxinsrtfile=idxinsrtfile
        tstsrtf=srtf
        tstcnstr=cnstr
        return jsonify(srtf,idxinsrtfile,cnstr,tstidx)
@app.route('/savetrser/<srtf>/<int:stcidx>/<int:trser>')
def savetrserrt(srtf,stcidx,trser):
    with lock: # 自动加锁解锁，保证
        writetrser(srtf,stcidx,trser)
        return 'v'
tstsrtf=''
tstidxinsrtfile=-1
tstcnstr=''
tstidx=-1
@app.route('/savesrt/<int:trser>/<int:adid>/<int:srtid>/<int:idx>/<path:enstr>/trsername')
@app.route('/savesrt4trs/<int:trser>/<int:adid>/<int:srtid>/<int:idx>/<path:enstr>/<trsername>')
def savert(trser,adid,srtid,enstr,idx,trsername):
    save4trsend(adid,srtid,idx,enstr,trser,trsername)
    return 'v'
@app.route('/resetcn/<int:adid>')
def resetcnrt(adid):
    resetcn(adid)
    return 'v'
@app.route('/reseten/<int:adid>')
def resetenrt(adid):
    reseten(adid)
    return 'v'
@app.route('/resettts/<int:adid>')
def resetttsrt(adid):
    resettts(adid)
    return 'v'
@app.route('/mp3/<srtf>')
def mp3rt(srtf):
    # return send_file(dir+'\\mp3\\'+srtf, mimetype='audio/mpeg')
    return send_file(srtf, mimetype='audio/mpeg')
    return send_file('mp3/'+srtf, mimetype='audio/mpeg')
@app.route('/srt/<srtf>')
def ensrtrt(srtf):
    htm=''
    srtf=dir+'\\'+srtf
    print(srtf)
    data=readsrtdata(srtf)
    for idx,stc in enumerate(data):
        htm+=str(idx+1)
        htm+='/'
        htm+=str(len(data))
        htm+=' '
        try:
            htm+=str(stc['trser'])
        except:
            pass
        htm+=' '
        try:
            htm+=stc['en']
        except:
            pass
        htm+='<br>'
    return htm
@app.route('/cnstr/<int:adid>')
def cnsrtrt(adid):
    htm=''
    data=readsrts(adid)
    for idx,stc in enumerate(data):
        htm+=str(idx+1)
        htm+='/'
        htm+=str(len(data))
        htm+=' '
        try:
            htm+=str(stc['start']/1000.0)
            htm+='-'
            htm+=str(stc['end']/1000.0)
            htm+=' '
            htm+=stc['cn']
        except Exception as ex:
            pass
        htm+='<br>'
    return htm
@app.route('/srt/<int:adid>')
def srtrt(adid):
    htm=''
    data=readsrts(adid)
    for idx,stc in enumerate(data):
        htm+=str(idx+1)
        htm+='/'
        htm+=str(len(data))
        htm+=' '
        try:
            htm+=str(stc['start']/1000.0)
            htm+='-'
            htm+=str(stc['end']/1000.0)
            htm+=' '
            htm+=stc['cn']
            htm+='<font color=blue>'            
            htm+=stc['en']
            htm+='</font>'
        except Exception as ex:
            pass
        htm+='<br>'
    return htm
@app.route('/en/<int:adid>')
def enrt(adid):
    htm=''
    data=readsrts(adid)
    for idx,stc in enumerate(data):
        htm+=str(idx+1)
        htm+='/'
        htm+=str(len(data))
        htm+=' '
        try:
            htm+=stc['trser']
            htm+=' '
            htm+=str(stc['start']/1000.0)
            htm+='-'
            htm+=str(stc['end']/1000.0)
            htm+=' '
            htm+=stc['en']
        except Exception as ex:
            pass
        htm+='<br>'
    return htm
@app.route('/dldsrts/<int:adid>')
def dldsrtsrt(adid):
    rs=readsrts(adid)
    data = [dict(row) for row in rs]
    return jsonify(data)
mlstnfiletime=0
srts=[]
def ccsrts(f,srts):
    srtf=f
    mp3f=f[:-3]+'mp3'
    if os.path.exists(mp3f):
        return
    srtf=f[:-3]+'srt'
    data=readsrtdata(srtf)
    if not isinstance(data,(list)):
        return
    if len(data)<1:
        return
    for idx,stc in enumerate(data):
        if 'en' not in stc:
            newsrt={'srtf':srtf,"idx":idx,'cn':stc['text'],'en':'',"trser":""}
            srts.append(newsrt)
def setmlstn():
    global mlstnfiletime
    mlstnfiletime=datetime.datetime.now().timestamp()
def freshfileinit():
    global srts
    setmlstn()
    myfiles=get_files_by_type(dir,".m4a")
    myfiles=sorted(myfiles, key=lambda x: os.path.getmtime(os.path.join(dir, x)), reverse=True)
    for f in myfiles:
        srtf=f
        mp3f=f[:-3]+'mp3'
        if os.path.exists(mp3f):
            continue        
 # 格式化输出
        date_time = datetime.datetime.fromtimestamp(os.path.getmtime(srtf))
        formatted_date_time = date_time.strftime("%Y-%m-%d %H:%M:%S")
        # print(f"最后修改时间: {formatted_date_time}")                   
        lastfilet=os.path.getmtime(f)
        try:
            if os.path.getmtime(f)<(datetime.datetime.now()-datetime.timedelta(days=7)).timestamp():
                print('too old,skip:',f)
                return
        except:
            continue
        date_time = datetime.datetime.fromtimestamp(os.path.getmtime(srtf))
        formatted_date_time = date_time.strftime("%Y-%m-%d %H:%M:%S")
        # print(f"最后修改时间: {formatted_date_time}")
        srtf=f[:-3]+'srt'
        data=readsrtdata(srtf)
        if not isinstance(data,(list)):
            continue
        if len(data)<1:
            continue
        enstrsok=True
        for idx,stc in enumerate(data):
            if 'en' not in stc:
                srt={'srtf':srtf,"idx":idx,'cn':stc['text'],'en':'',"trser":''}
                srts.append(srt)
def freshfileloop():
    global srts
    lastfilet=0
    lastmlstnfiletime=mlstnfiletime
    setmlstn()
    myfiles=get_files_by_type(dir,".m4a")
    myfiles=sorted(myfiles, key=lambda x: os.path.getmtime(os.path.join(dir, x)), reverse=True)
    for f in myfiles:
        srtf=f
        try:
            filetime=os.path.getmtime(srtf)
            os.path.getmtime(srtf)
            date_time = datetime.datetime.fromtimestamp(os.path.getmtime(srtf))
            # 格式化输出
            formatted_date_time = date_time.strftime("%Y-%m-%d %H:%M:%S")
            print(f"最后修改时间: {formatted_date_time}")
            lastfilet=os.path.getmtime(f)
            if lastfilet<=lastmlstnfiletime:
                return
                os.path.getmtime(srtf)
                date_time = datetime.datetime.fromtimestamp(os.path.getmtime(srtf))
                # 格式化输出
                formatted_date_time = date_time.strftime("%Y-%m-%d %H:%M:%S")
                print(f"最后修改时间: {formatted_date_time}")
                print('too old,skip:',srtf)
                files=get_files_by_type(dir,".m4a")
                files=sorted(files, key=lambda x: os.path.getmtime(os.path.join(dir, x)), reverse=True)
                return
        except Exception as ex:
            print('xxx',ex)
            continue
        srtf=f[:-3]+'srt'
        ccsrts(srtf,srts)
def expsrts():
    idle=True
    for srt in srts:
        try:
            if len(srt['en'])==0:                
                return
        except:
            pass
    if idle:
        for srt in srts:
            try:
                if len(srt['en'])>0:
                    print('expire:',srt)
                    del srt
            except:
                pass
def readsrtfilertn():
    freshfileinit()
    if len(srts)>0:
        # time.sleep(60)
        pass
    while True:
        expsrts()
        freshfileloop()
        time.sleep(60)       
def appendnewad(newad):
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()    
    # sqlstr = rf'''select count(*) from srts where adid=?'''
    # re=cur.execute(sqlstr,(adid))
    # idx=re.fetchone[0]
    try:
        sqlstr = rf'''insert into ads (name,createtime) values(?,?)'''    
        re=cur.execute(sqlstr,(newad[1],newad[3]))
        re=conn.commit()
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
def dldadfile(fname):
    url=rf'http://devv.cc:8888/play/{fname}'
    ret=requests.get(url)
    with open(fname,"wb") as code:
        code.write(ret.content) 
def readsrtfilertn():
    input()
    refad=readtopad()
    print(refad[3])
    refdt=refad[3]
    while True:
        newad=reqnewad(refdt)
        print(newad)
        if newad and newad!=[]:
            dldadfile(newad[1]+'.m4a')
            appendnewad(newad)
            refdt=newad[3]
            continue
        time.sleep(10)
def resetsrt():
    myfiles=get_files_by_type(dir,".m4a")
    myfiles=sorted(myfiles, key=lambda x: os.path.getmtime(os.path.join(dir, x)), reverse=True)
    for f in myfiles:
        mp3f=f[:-3]+'mp3'
        try:
            os.remove(mp3f)
        except Exception as ex:
            pass
        try:
            srtf=f[:-3]+'srt'
            srts=[]
            with open(srtf,'r',encoding='utf-8')as f:
                srts=json.load(f)
                for idx,srt in enumerate(srts):
                    print(idx,srt)
                    del srt['en']                
            with open(srtf,'w',encoding='utf-8')as f:
                json.dump(srts,f)
            break
        except Exception as ex:
            pass
dbfile="dpd.db"
def srtexists(cur,adid,srtidx):
    sqlstr = rf'''select count(*) from srts where adid=? and idx=?'''
    re=cur.execute(sqlstr,(adid,srtidx))
    r=re.fetchone()
    cnt=r[0]
    return cnt!=0
def savecnsrt0(adid,adname,srts):
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()    
    # sqlstr = rf'''select count(*) from srts where adid=?'''
    # re=cur.execute(sqlstr,(adid))
    # idx=re.fetchone[0]
    try:
        for idx,srt in enumerate(srts):
            cn=srt['text']
            start=srt['start']
            end=srt['end']
            if srtexists(cur,adid,idx):                
                sqlstr = rf'''update srts set adname=?,idx=?,cn=?,start=?,end=?,en="",ttsenddt=datetime('now','localtime') where adid=? and idx=?'''
                re=cur.execute(sqlstr,(adname,idx,cn,start,end,adid,idx))
            else:
                sqlstr = rf'''insert into srts (adid,adname,idx,cn,start,end,en,ttsenddt) values(?,?,?,?,?,?,"",datetime('now','localtime'))'''    
                re=cur.execute(sqlstr,(adid,adname,idx,cn,start,end))
        sqlstr = rf'''update ads set bili=1 where rowid={adid}'''
        re=cur.execute(sqlstr)
        re=conn.commit()
        return cur.lastrowid
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return 0

    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    sqlstr = rf'''update ads set playtime=datetime('now','localtime') where rowid=?'''
    try:
        re=cur.execute(sqlstr,(did,))
        re=conn.commit()
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
def savecnsrt(adid,adname,srts):
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()    
    # sqlstr = rf'''select count(*) from srts where adid=?'''
    # re=cur.execute(sqlstr,(adid))
    # idx=re.fetchone[0]
    try:
        for idx,srt in enumerate(srts):
            cn=srt[0]
            start=srt[1]
            end=srt[2]
            if srtexists(cur,adid,idx):                
                sqlstr = rf'''update srts set adname=?,idx=?,cn=?,start=?,end=?,en="",ttsenddt=datetime('now','localtime') where adid=? and idx=?'''
                re=cur.execute(sqlstr,(adname,idx,cn,start,end,adid,idx))
            else:
                sqlstr = rf'''insert into srts (adid,adname,idx,cn,start,end,en,ttsenddt) values(?,?,?,?,?,?,"",datetime('now','localtime'))'''    
                re=cur.execute(sqlstr,(adid,adname,idx,cn,start,end))
        sqlstr = rf'''update ads set bili=1 where rowid={adid}'''
        re=cur.execute(sqlstr)
        re=conn.commit()
        return cur.lastrowid
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return 0

    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    sqlstr = rf'''update ads set playtime=datetime('now','localtime') where rowid=?'''
    try:
        re=cur.execute(sqlstr,(did,))
        re=conn.commit()
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
def resetcn(adid):
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()    
    try:
        sqlstr = rf'''delete from srts where adid={adid}'''        
        re=cur.execute(sqlstr)
        sqlstr = rf'''update ads set bili=0 where rowid={adid}'''        
        re=cur.execute(sqlstr)
        re=conn.commit()
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
def reseten(adid):
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()    
    try:
        sqlstr = rf'''update srts set en="",trser="" where adid={adid}'''        
        re=cur.execute(sqlstr)
        sqlstr = rf'''update ads set bili=1 where rowid={adid} and bili>1'''        
        re=cur.execute(sqlstr)
        re=conn.commit()
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
def resettts(adid):
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()    
    try:
        sqlstr = rf'''update srts set tts=0,ttser="",ttsenddt="" where adid={adid}'''        
        re=cur.execute(sqlstr)
        sqlstr = rf'''update ads set bili=2 where rowid={adid} and bili>2'''        
        re=cur.execute(sqlstr)
        re=conn.commit()
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
def readsrts(addid):
    conn = sqlite3.connect(dbfile)
    conn.row_factory = sqlite3.Row    
    cur = conn.cursor()
    sqlstr = rf'''select rowid,* from srts where length(cn)>0 and en="" order by createdt desc'''
    sqlstr = rf'''select rowid,* from srts where adid=? order by idx'''
    try:
        re=cur.execute(sqlstr,(addid,))
        ret=re.fetchall()
        return ret
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
@app.route('/savecnsrts/<int:adid>/<path:adname>',methods=['POST'])
def savecnsrtsrt(adid,adname):
    srts=request.json
    savecnsrt(adid,adname,srts)
    return ''
    htm=''
    for srt in srts:
        htm+=srt['srtf']
        htm+=' '
        htm+=str(srt['idx'])
        htm+=' '
        htm+=str(srt['trser'])
        htm+=' '
        htm+=srt['cn']
        htm+=' '
        htm+=srt['en']
        htm+='<br>'
    return htm
def readnewads():
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    sqlstr = rf'''select rowid,* from ads where (bili=0 or bili is NULL) and datetime(createtime)>datetime('now', '-22 day') order by createtime desc'''
    try:
        re=cur.execute(sqlstr)
        ret=re.fetchall()
        return ret
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return []
def readbili3ads():
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    sqlstr = rf'''select rowid,* from ads where bili=3 order by bilidt desc'''
    try:
        re=cur.execute(sqlstr)
        ret=re.fetchall()
        return ret
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return []
def reqsrt4trs(trser,trsername):
    with lock:
        conn = sqlite3.connect(dbfile)
        cur = conn.cursor()
        sqlstr = rf'''select rowid,* from srts where en="" and (trser=="" or trser=={trser}) order by createdt desc LIMIT 1'''
        sqlstr = rf'''select rowid,* from srts where en="" and (trser=={trser} or trsstartdt<datetime('now','-5 minutes') or trser=="") order by createdt LIMIT 1'''
# ttsenddt>"" and strftime('%s','now','localtime') - strftime('%s',ttsstartdt)>300))
        # sqlstr = rf'''select rowid,* from srts where trsenddt=="" and (trsstartdt=="" or strftime('%s','now','localtime') - strftime('%s',trsstartdt)>300) order by createdt LIMIT 1'''
        sqlstr = rf'''select rowid,* from srts where en=="" and (trsstartdt=="" or strftime('%s','now','localtime') - strftime('%s',trsstartdt)>300) order by createdt LIMIT 1'''
        # print(sqlstr)
        try:
            re=cur.execute(sqlstr)
            ret=re.fetchone()
            if not ret:
                return ()
            if len(ret)>0:
                srtid=ret[0]
                sql='''update srts set trser=?,trsstartdt=datetime('now','localtime') where rowid=?'''
                re=cur.execute(sql,(trser,srtid))
                conn.commit()
                print('rrr start trs start,trser/srt:',trsername,ret)
                return ret
        except Exception as ex:
            print('无结果for trs start')
        except TypeError as ex:
            print('xxx',ex)
        finally:
            cur.close()
            conn.close()
        return ()
def readads():
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    sqlstr = rf'''select rowid,* from ads where datetime(createtime)>datetime('now', '-30 day') order by createtime desc'''
    try:
        re=cur.execute(sqlstr)
        ret=re.fetchall()
        return ret
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return []
def readad4tts():
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    sqlstr = rf'''select rowid,* from ads where bili==2 and datetime(createtime)>datetime('now', '-30 day') order by createtime desc limit 1'''
    try:
        re=cur.execute(sqlstr)
        ret=re.fetchone()
        return ret
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return ()
def readsrt4tts_old():
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()

    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 order by createdt limit 1'''
    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 and length(en)>1 order by createdt limit 1'''
    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 and length(en)>1 order by createdt limit 1'''
    sqlstr = rf'''select rowid,* from ads where trscnt>0 and trscnt<totsrts and (tts is NULL or tts<1) order by totsrts-trscnt limit 1'''
    sqlstr = rf'''select rowid,* from ads where trscnt>0 and ttscnt<totsrts and (tts is NULL or tts<1) order by totsrts-trscnt limit 1'''
    sqlstr = rf'''select rowid,* from ads where bili==2 order by totsrts-ttscnt limit 1'''
    try:
        re=cur.execute(sqlstr)
        ret=re.fetchone()
        adid=ret[0]
        sqlstr = rf'''select rowid,* from srts where adid=? and (tts is NULL or tts<1) limit 1'''
        re=cur.execute(sqlstr,(adid,))
        ret=re.fetchone()
        if ret:
            return ret[0],ret[1],ret[2],ret[3],ret[7]
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return ()
def reqsrt4tts():
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 order by createdt limit 1'''
    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 and length(en)>1 order by createdt limit 1'''
    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 and length(en)>1 and (strftime('%s',ttsstartdt) is null or strftime('%s', 'now') - strftime('%s',ttsstartdt)>300) order by trsenddt limit 1'''
    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 and length(en)>1 and (strftime('%s',ttsstartdt) is null or strftime('%s', 'now') - strftime('%s',ttsstartdt)>300) order by adtrsenddt limit 1'''
    sqlstr = rf'''select rowid,* from srts where adtrsenddt>"" and ((ttsenddt=="" or ttsenddt is null) and (strftime('%s',ttsstartdt) is null or strftime('%s','now','localtime') - strftime('%s',ttsstartdt)>300)) order by adtrsenddt limit 1'''
    # sqlstr = rf'''select rowid,* from ads where bili==2 order by totsrts-ttscnt limit 1'''
    try:
        re=cur.execute(sqlstr)
        ret=re.fetchone()
        # adid=ret[0]
        # sqlstr = rf'''select rowid,* from srts where adid=? and (tts is NULL or tts<1) limit 1'''
        # re=cur.execute(sqlstr,(adid,))
        # ret=re.fetchone()
        if ret:
            adid=ret[1]
            srtidx=ret[3]
            adname=ret[2]
            sqlstr = rf'''select count(*) from srts where adid=?'''
            totsrtsret=cur.execute(sqlstr,(adid,))
            totsrtsrec=totsrtsret.fetchone()
            totsrts=totsrtsrec[0]
            print('ttt start tts start(trs已完),idx/tot:',srtidx,'/',totsrts,adname)
            # return ret[0],ret[1],ret[2],ret[3],ret[7],totsrts
        else:
            sqlstr = rf'''select rowid,* from srts where trsenddt>"" and (ttsstartdt=="" or (ttsenddt>"" and strftime('%s', 'now','localtime') - strftime('%s',ttsstartdt)>300)) order by trsenddt limit 1'''
            ret=cur.execute(sqlstr)
            ret=ret.fetchone()
            if not ret:
                return ()
            adid=ret[1]
            srtidx=ret[3]
            adname=ret[2]
            sqlstr = rf'''select count(*) from srts where adid=?'''
            totsrtsret=cur.execute(sqlstr,(adid,))
            totsrtsrec=totsrtsret.fetchone()
            totsrts=totsrtsrec[0]
            print('ttt start tts start(trs未完),idx/tot:',srtidx,'/',totsrts,adname)
                # return ret[0],ret[1],ret[2],ret[3],ret[7],totsrts
        sqlstr = rf'''update srts set ttsstartdt=datetime('now','localtime') where rowid=?'''
        re=cur.execute(sqlstr,(ret[0],))
        conn.commit()
        return ret[0],ret[1],adname,srtidx,ret[7],totsrts
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return ()
def qrsrt4tts():
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 order by createdt limit 1'''
    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 and length(en)>1 order by createdt limit 1'''
    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 and length(en)>1 and (strftime('%s',ttsstartdt) is null or strftime('%s', 'now') - strftime('%s',ttsstartdt)>300) order by trsenddt limit 1'''
    sqlstr = rf'''select rowid,* from srts where tts is NULL or tts<1 and length(en)>1 and (strftime('%s',ttsstartdt) is null or strftime('%s', 'now') - strftime('%s',ttsstartdt)>300) order by adtrsenddt limit 1'''
    sqlstr = rf'''select rowid,* from srts where adtrsenddt>"" and ((ttsenddt=="" or ttsenddt is null) and (strftime('%s',ttsstartdt) is null or strftime('%s','now','localtime') - strftime('%s',ttsstartdt)>300)) order by adtrsenddt limit 1'''
    sqlstr = r'''
    select rowid, *
    from srts
    where adtrsenddt>""
    and (ttsenddt = "" or ttsenddt is null)
    and (
            ttsstartdt = ""
            or strftime('%s','now','localtime')-strftime('%s', ttsstartdt) > 300
        )
    order by adtrsenddt, adid, idx
    limit 1
    '''
    # sqlstr = '''
    # select rowid,ttsstartdt,ttsenddt,*
    # from srts
    # where adtrsenddt>""
    # order by adtrsenddt desc, adid, idx
    # '''
    # sqlstr = rf'''select rowid,* from ads where bili==2 order by totsrts-ttscnt limit 1'''
    try:
        re=cur.execute(sqlstr)
        ret=re.fetchone()
        # adid=ret[0]
        # sqlstr = rf'''select rowid,* from srts where adid=? and (tts is NULL or tts<1) limit 1'''
        # re=cur.execute(sqlstr,(adid,))
        # ret=re.fetchone()        
        if ret:
            adid=ret[1]
            srtidx=ret[3]
            adname=ret[2]
            sqlstr = rf'''select count(*) from srts where adid=?'''
            totsrtsret=cur.execute(sqlstr,(adid,))
            totsrtsrec=totsrtsret.fetchone()
            totsrts=totsrtsrec[0]
            print('ttt start tts start(trs已完),idx/tot:',srtidx,'/',totsrts,adname)
            # return ret[0],ret[1],ret[2],ret[3],ret[7],totsrts
        else:
            sqlstr = rf'''select rowid,* from srts where trsenddt>"" and (ttsstartdt=="" or (ttsenddt>"" and strftime('%s', 'now','localtime') - strftime('%s',ttsstartdt)>300)) order by trsenddt limit 1'''
            sqlstr='''
SELECT rowid, * 
FROM srts 
WHERE trsenddt > '' 
  AND IFNULL(ttsenddt, '') = ''  -- 核心前提：ttsenddt 必须为空（未完成）
  AND (
    IFNULL(ttsstartdt, '') = ''  -- 情况A：还没开始
    OR ttsstartdt < datetime('now', 'localtime', '-300 seconds') -- 情况B：开始了但超时
  )
order by trsenddt limit 1
'''
            ret=cur.execute(sqlstr)
            ret=ret.fetchone()
            if not ret:
                return ()
            adid=ret[1]
            srtidx=ret[3]
            adname=ret[2]
            sqlstr = rf'''select count(*) from srts where adid=?'''
            totsrtsret=cur.execute(sqlstr,(adid,))
            totsrtsrec=totsrtsret.fetchone()
            totsrts=totsrtsrec[0]
            print('ttt start tts start(trs未完),idx/tot:',srtidx,'/',totsrts,adname)
                # return ret[0],ret[1],ret[2],ret[3],ret[7],totsrts
        sqlstr = rf'''update srts set ttsstartdt=datetime('now','localtime') where rowid=?'''
        re=cur.execute(sqlstr,(ret[0],))
        conn.commit()
        return ret[0],ret[1],adname,srtidx,ret[7],totsrts
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return ()
def readtopad():
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    sqlstr = rf'''select rowid,* from ads order by createtime desc limit 1'''
    try:
        re=cur.execute(sqlstr)
        ret=re.fetchone()
        return ret
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return ()
@app.route('/readbili0ads')
def readbili0adsrt():
    ads=readnewads()
    return jsonify(ads)        
# @app.route('/dldad/<path:adf>')
@app.route('/reqsrt4trs/<trser>/<trsername>')
def reqsrt4trsrt(trser,trsername):
    ads=reqsrt4trs(trser,trsername)
    return jsonify(ads)        
@app.route('/dldad/<path:adf>')
def dldadfsrt(adf):
    if not os.path.exists(adf):
        return "File Not Found",404    
    return send_file(adf,mimetype='audio/mpeg')
    try:
        return send_file(adf+'.m4a',mimetype='audio/mpeg')
        # return send_file(adf,mimetype='audio/mpeg')
    except FileNotFoundError as ex:
        # print('无文件',adf)
        return ''
    except Exception as ex:
        print('xxx',ex)
        return ''
@app.route('/dldinfo4tts/<ttser>')
def readad4ttsrt(ttser):
    srt=readad4tts()
    return jsonify(srt)
@app.route('/reqsrt4tts/<ttser>')
def reqsrt4ttsrt(ttser):
    with lock:
        # srt=reqsrt4tts()
        srt=qrsrt4tts()
        return jsonify(srt)
@app.route('/postadclip/<srtid>/<ttser>/<int:adid>/<int:totsrts>/<int:srtidx>/<path:adname>',methods=['POST'])
@app.route('/postadclip/<srtid>/<ttser>/<ttsername>/<int:adid>/<int:totsrts>/<int:srtidx>/<path:adname>',methods=['POST'])
def postadcliprt(srtid,ttser,adid,adname,srtidx,totsrts,ttsername="???"):
    ret='v'
    try:
        f=request.files['file']
        if f:
            f.save("%09d"%adid+"-%04d"%srtidx+'.mp3')
    except Exception as ex:
        print('xxx',ex)
        ret='0'
    updatettsinfo(srtid,ttser,adid,adname,totsrts,srtidx,ttsername)
    return ret
def reqnewad(refdt):
    url=rf'http://devv.cc:8888/reqnewad/{quote(refdt)}'
    try:
        ret=requests.get(url)
        return ret.json()
    except Exception as ex:
        print('xxx',ex)
        time.sleep(10)
    return {}
def fixdb():
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    sqlstr = rf'''select rowid,* from ads order'''
    try:
        re=cur.execute(sqlstr)
        ret=re.fetchall()
        for r in ret:
            rid=r[0]
            adname=r[1]
            print(adname)
            sqlstr = rf'''select rowid,* from srts where adid=?'''
            re=cur.execute(sqlstr,(rid,))
            ret=re.fetchall()
            encnt=0
            for srt in ret:
                en=srt[7]
                if len(en)>1:
                    encnt+=1
            print('en/tot srts:',encnt,len(ret))
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    return ()
if __name__ == '__main__':
    # fixdb()
    from readsrtfilertnpy import readsrtfilertn
    # threading.Thread(target=readsrtfilertn).start()
    app.debug = False
    log = logging.getLogger('werkzeug')
    log.disabled = True
    # app.run('0.0.0.0',port=80,threaded=True)
    # app.run('0.0.0.0',port=8899,threaded=True)
    
    conn = sqlite3.connect(dbfile)
    cur = conn.cursor()
    # sqlstr = rf'''select count(*) from srts where adid=?'''
    # re=cur.execute(sqlstr,(adid))
    # idx=re.fetchone[0]
    # adid=0
    # adname=''
    adid=8397
    srtsdata=[]
    try:
        sqlstr = rf'''select rowid,* from srts where adid=? order by idx'''
        conn.row_factory = sqlite3.Row    
        re=cur.execute(sqlstr,(adid,))        
        srtsdata=re.fetchall()        
    except Exception as ex:
        print('xxx',ex)
    finally:
        cur.close()
        conn.close()
    print('all ttsed')
    now=str(datetime.datetime.now())[5:-7]
    # histtsads.append((adid,totsrts,adname,now))
    # sqlstr = rf'''select count(*) from srts where adid=?'''
    # re=cur.execute(sqlstr,(adid))
    # idx=re.fetchone[0]
    jnadsegthd(adid,srtsdata,adname,ttser,srtid,totsrts)