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)
        # TODO:码率有问题之后重新计算

        # 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;
                '''
    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)
        # TODO:码率有问题之后重新计算

        # 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()


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