#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
循环处理 pub=1 的记录，执行双语视频制作流程
"""

import os
import sys
import sqlite3
import subprocess
import time


def get_db_connection():
    """
    获取数据库连接
    """
    import inspect
    func_name = inspect.currentframe().f_code.co_name
    line_no = inspect.currentframe().f_lineno
    
    try:
        conn = sqlite3.connect(r'\\ll\D\xtrssvjj\db\dpd.db', timeout=10.0)
        conn.row_factory = sqlite3.Row
        
        try:
            # 优化数据库参数，减少锁定
            conn.execute('PRAGMA synchronous=NORMAL')
            conn.execute('PRAGMA cache_size=-64000')
        except Exception as e:
            conn.close()
            raise
        
        print("111111111111111111111111111111")
        return conn
    except Exception as e:
        import traceback
        print(f"错误 [函数: {func_name}, 行号: {line_no}]: 连接数据库失败: {e}")
        traceback.print_exc()
        raise


def get_file_duration(file_path):
    """
    获取文件时长（秒）
    """
    try:
        cmd = [
            'ffprobe',
            '-v', 'quiet',
            '-show_entries', 'format=duration',
            '-of', 'default=noprint_wrappers=1:nokey=1',
            file_path
        ]
        result = subprocess.run(cmd, capture_output=True, text=True, shell=True)
        duration = float(result.stdout.strip())
        return duration
    except Exception as e:
        print(f"获取文件时长失败: {e}")
        return 0


def process_item(adid):
    """
    处理单个广告，执行双语视频制作流程
    """
    import datetime
    print(f"\n=== 开始处理 adid: {adid} ===")
    
    # 获取 adname
    print("正在获取 adname...")
    print(f"开始获取 adname，时间：{datetime.datetime.now()}")
    
    try:
        conn = get_db_connection()
        print("数据库连接成功")
        
        try:
            cursor = conn.execute('SELECT DISTINCT adname FROM srts WHERE adid = ? LIMIT 1', (adid,))
            print("执行查询成功")
            
            result = cursor.fetchone()
            print(f"查询结果: {result}")
            
            adname = result[0] if result else f"adid_{adid}"
            print(f"获取到 adname: {adname}")
        finally:
            print("2222222222222222222222222222222222222222222")
            conn.close()
            print("数据库连接已关闭")
    except Exception as e:
        print(f"获取 adname 失败: {e}")
        adname = f"adid_{adid}"
        print(f"使用默认 adname: {adname}")
    
    print(f"获取 adname 完成，时间：{datetime.datetime.now()}")
    
    print(f"\n=== 处理 adid: {adid}, name: {adname} ===")
    
    # 1. 截取中文音频并拼接双语音频
    print(f"正在进行：步骤1 - 截取中文音频并拼接双语音频")
    import datetime
    start_time = datetime.datetime.now()
    print(f"开始时间：{start_time}")
    
    # 直接执行命令，使用最简单的方式
    cmd = f"python joinbiliad.py {adid}"
    print(f"执行命令：{cmd}")
    print(f"开始执行命令，时间：{datetime.datetime.now()}")
    
    # 使用 capture_output=False，让输出直接显示在终端上
    result = subprocess.run(
        cmd, 
        cwd="c:\\dev\\xtrs\\vdcb\\srtsegjoin",
        shell=True,
        capture_output=False,
        text=True
    )
    
    print(f"命令执行完成，时间：{datetime.datetime.now()}")
    
    end_time = datetime.datetime.now()
    duration = (end_time - start_time).total_seconds()
    
    print(f"命令执行完成，返回码：{result.returncode}")
    print(f"执行时间：{duration:.2f}秒")
    
    returncode = result.returncode
    
    if returncode != 0:
        print(f"错误：双语音频制作失败")
        return False
    print(f"完成：步骤1 - 双语音频制作成功")
    
    # 2. 生成字幕图片
    print(f"正在进行：步骤2 - 生成字幕图片")
    cmd = f"python generate_subtitles.py {adid}"
    result = subprocess.run(
        cmd, 
        cwd="c:\\dev\\xtrs\\vdcb\\srtsegjoin",
        shell=True,
        capture_output=False,
        text=True
    )
    
    if result.returncode != 0:
        print(f"错误：字幕图片生成失败")
        return False
    print(f"完成：步骤2 - 字幕图片生成成功")
    
    # 3. 生成视频流
    print(f"正在进行：步骤3 - 生成视频流")
    cmd = f"python generate_bilingual_video.py {adid}"
    result = subprocess.run(
        cmd, 
        cwd="c:\\dev\\xtrs\\vdcb\\srtsegjoin",
        shell=True,
        capture_output=False,
        text=True
    )
    
    if result.returncode != 0:
        print(f"错误：视频流生成失败")
        return False
    print(f"完成：步骤3 - 视频流生成成功")
    
    # 4. 合并音视频
    print(f"正在进行：步骤4 - 合并音视频")
    cmd = f"python merge_audio_video.py {adid}"
    result = subprocess.run(
        cmd, 
        cwd="c:\\dev\\xtrs\\vdcb\\srtsegjoin",
        shell=True,
        capture_output=False,
        text=True
    )
    
    if result.returncode != 0:
        print(f"错误：音视频合并失败")
        return False
    print(f"完成：步骤4 - 音视频合并成功")
    
    # 5. 检查生成的视频文件
    output_file = f"{adname}_final_bilingual.mp4"
    output_path = os.path.join("c:\\dev\\xtrs\\vdcb\\srtsegjoin", output_file)
    
    if not os.path.exists(output_path):
        print(f"错误：最终视频文件不存在: {output_path}")
        return False
    
    # 检查视频时长
    video_duration = get_file_duration(output_path)
    if video_duration < 1:
        print(f"错误：视频时长过短: {video_duration}秒")
        return False
    
    print(f"成功：视频制作完成，时长: {video_duration:.2f}秒")
    return True
pub2wxvcflag=False
def getdestpubstate():
    if pub2wxvcflag:
        return 4
    return 3
def main():
    """
    主函数，循环处理 pub=1 和 pub=2 的记录
    """
    import datetime
    print("=== 双语视频制作循环开始 ===")
    
    while True:
        print(f"\n=== 开始新一轮检查，时间：{datetime.datetime.now()} ===")
        
        # 1. 查询 pub=2 的记录
        pub2_items = []
        try:
            conn = get_db_connection()
            try:
                cursor = conn.execute('SELECT rowid FROM ads WHERE pub = 2 ORDER BY rowid')
                pub2_items = cursor.fetchall()
                print(f"找到 {len(pub2_items)} 个 pub=2 的记录（中断的记录）")
            finally:
                conn.close()
                print("数据库连接已关闭")
        except Exception as e:
            print(f"查询 pub=2 记录失败: {e}")
            import traceback
            traceback.print_exc()
        
        # 处理 pub=2 的记录
        if pub2_items:
            print("先处理 pub=2 的记录...")
            for i, item in enumerate(pub2_items, 1):
                rowid = item[0]
                adid = rowid  # 使用 rowid 作为 adid
                print(f"\n[{i}/{len(pub2_items)}] 处理 pub=2 记录: rowid={rowid}, adid={adid}")
                
                # 执行制作流程
                success = process_item(adid)
                
                # 更新 pub 值
                try:
                    conn = get_db_connection()
                    try:
                        if success:
                            destpubstate=getdestpubstate()
                            print(f"制作成功，将 pub 值更新为 3 (已完成)...")
                            conn.execute('UPDATE ads SET pub = ? WHERE rowid = ?', (destpubstate,rowid,))
                            conn.commit()
                            print(f"更新成功：rowid={rowid}, pub=3")
                        else:
                            print(f"制作失败，将 pub 值重置为 1...")
                            conn.execute('UPDATE ads SET pub = 1 WHERE rowid = ?', (rowid,))
                            conn.commit()
                            print(f"更新成功：rowid={rowid}, pub=1")
                    finally:
                        conn.close()
                        print("数据库连接已关闭")
                except Exception as e:
                    print(f"更新 pub 值失败: {e}")
                    import traceback
                    traceback.print_exc()
        
        # 2. 查询 pub=1 的记录
        pub1_items = []
        try:
            conn = get_db_connection()
            try:
                cursor = conn.execute('SELECT rowid FROM ads WHERE pub = 1 ORDER BY rowid')
                pub1_items = cursor.fetchall()
                print(f"\n找到 {len(pub1_items)} 个 pub=1 的记录（新记录）")
            finally:
                conn.close()
                print("数据库连接已关闭")
        except Exception as e:
            print(f"查询 pub=1 记录失败: {e}")
            import traceback
            traceback.print_exc()
        
        # 处理 pub=1 的记录
        if pub1_items:
            print("处理 pub=1 的记录...")
            for i, item in enumerate(pub1_items, 1):
                rowid = item[0]
                adid = rowid  # 使用 rowid 作为 adid
                print(f"\n[{i}/{len(pub1_items)}] 处理 pub=1 记录: rowid={rowid}, adid={adid}")
                
                # 先更新 pub 值为 2
                try:
                    conn = get_db_connection()
                    try:
                        print("正在更新 pub 值为 2 (制作中)...")
                        conn.execute('UPDATE ads SET pub = 2 WHERE rowid = ?', (rowid,))
                        conn.commit()
                        print(f"更新成功：rowid={rowid}, pub=2")
                    finally:
                        conn.close()
                        print("数据库连接已关闭")
                except Exception as e:
                    print(f"更新 pub=2 失败: {e}")
                    import traceback
                    traceback.print_exc()
                    continue
                
                # 执行制作流程
                success = process_item(adid)
                
                # 更新 pub 值
                try:
                    conn = get_db_connection()
                    try:
                        if success:
                            # 查询 pub2wxcflag 的值，决定设置 pub=3 还是 pub=4
                            cursor = conn.execute('SELECT pub2wxcflag FROM ads WHERE rowid = ?', (rowid,))
                            result = cursor.fetchone()
                            pub2wxcflag = result[0] if result else 0
                            
                            target_pub = 4 if pub2wxcflag == 1 else 3
                            print(f"制作成功，pub2wxcflag={pub2wxcflag}，将 pub 值更新为 {target_pub}...")
                            conn.execute('UPDATE ads SET pub = ? WHERE rowid = ?', (target_pub, rowid))
                            conn.commit()
                            print(f"更新成功：rowid={rowid}, pub={target_pub}")
                        else:
                            print(f"制作失败，将 pub 值重置为 1...")
                            conn.execute('UPDATE ads SET pub = 1 WHERE rowid = ?', (rowid,))
                            conn.commit()
                            print(f"更新成功：rowid={rowid}, pub=1")
                    finally:
                        conn.close()
                        print("数据库连接已关闭")
                except Exception as e:
                    print(f"更新 pub 值失败: {e}")
                    import traceback
                    traceback.print_exc()
        
        # 处理完所有记录后，等待 10 秒
        print(f"\n处理完所有记录，等待 10 秒后重新检查... 时间：{datetime.datetime.now()}")
        time.sleep(10)


if __name__ == "__main__":
    main()