import requests
import cv2
import sys
import pandas
from model.sql_models import DB
from config.using_config import quchen_text, dm


def update_video_info():
    # 1.获取到所有的video_url
    sql = '''
            select * from video_info vi 
                where length (preview_url )>0;
            '''
    df = pandas.read_sql(sql=sql, con=db_qc.engine)
    print(df)
    for index, row in df.iterrows():

        # 2.获取video信息----大小,时长,格式
        # video_url = 'http://wxsnsdy.wxs.qq.com/131/20210/snssvpdownload/SH/reserved/ads_svp_video__0b53qybncaaciaadpfl53jqbrbqe2gdafuka.f0.mp4?dis_k=4a9de877e9ee8dffe7a12a55700c7c0e&dis_t=1618994896&m=f4ed07a998cc60ba25ec1c2425176ea8'
        video_url = row['preview_url']
        rsp = requests.get(video_url)
        with open('aa.mp4', 'wb') as f:
            f.write(rsp.content)
        video_size = len(rsp.content)
        cap = cv2.VideoCapture('aa.mp4')  # 视频流
        if cap.isOpened():
            rate = cap.get(5)
            frame_num = cap.get(7)
            print(frame_num, rate)
            duration = frame_num / rate
        print(video_size, duration)
        # byte_rate = video_size / 1024 * 8 / duration
        byte_rate = (video_size/(duration/8))
        print(byte_rate)

        # 3.进行存储
        sql = '''
        update video_info
        set size={},video_length={},type='mp4',byte_rate={}
        where video_id='{}'
        '''.format(video_size, duration, byte_rate, row['video_id'])
        db_qc.session.execute(sql)
        db_qc.session.commit()

def update_byte_rate():
    sql = '''
                select * from video_info vi 
                    where length (preview_url )>0 and not type
                '''
    df = pandas.read_sql(sql=sql, con=db_qc.engine)
    print(df)
    for index, row in df.iterrows():

        # 2.获取video信息----大小,时长,格式
        # video_url = 'http://wxsnsdy.wxs.qq.com/131/20210/snssvpdownload/SH/reserved/ads_svp_video__0b53qybncaaciaadpfl53jqbrbqe2gdafuka.f0.mp4?dis_k=4a9de877e9ee8dffe7a12a55700c7c0e&dis_t=1618994896&m=f4ed07a998cc60ba25ec1c2425176ea8'
        video_url = row['preview_url']
        video_size = row['size']
        duration = row['video_length']

        # byte_rate = video_size / 1024 * 8 / duration
        byte_rate = (video_size / (duration / 8))
        print(byte_rate)

        # 3.进行存储
        sql = '''
            update video_info
            set size={},video_length={},type='mp4',byte_rate={}
            where video_id='{}'
            '''.format(video_size, duration, byte_rate, row['video_id'])
        db_qc.session.execute(sql)
        db_qc.session.commit()


def update_video_info_pro():
    from model.sql_models import DB
    from config import using_config_test, using_config
    db_qc = DB(config=using_config.quchen_text)
    db_qc_test = DB(config=using_config_test.quchen_text)
    sql_get = '''
    select 
      video_id,size, video_length, byte_rate, video_meta_data,download_path
     from video_info
    where LENGTH (preview_url )>1 and download_path is not null;
    '''
    cursor=db_qc_test.session.execute(sql_get)
    id_dict={}
    for line in cursor.fetchall():
        video_id,size, video_length, byte_rate, video_meta_data, download_path=line
        id_dict[video_id]=(size, video_length, byte_rate, video_meta_data,download_path)


    for k,v in id_dict.items():
        video_id=k
        size, video_length, byte_rate, video_meta_data,download_path=v
        sql_update = '''
                         update video_info 
                         set size={}, video_length={},
                         type='mp4', byte_rate={}, video_meta_data='{}',
                         download_path='{}'
                         where 
                         video_id='{}'
                        '''.format(size, video_length, byte_rate, video_meta_data, download_path, video_id)
        db_qc.session.execute(sql_update)
    db_qc.session.commit()


if __name__ == '__main__':
    db_qc = DB(config=quchen_text)
    db_dm = DB(config=dm)
    # update_video_info()
    # update_byte_rate()

    update_video_info_pro()