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

import os
import sys
import sqlite3
import subprocess
import time
import toml
import glob

from add_cover_to_mp4a import add_cover_physical


def add_cover_to_mp4(adnamemain, mp4_dir='.', cover_dir=None):
    """
    给MP4视频添加封面图片

    参数:
        adnamemain: 文件主名，MP4文件名为 adnamemain.mp4
        mp4_dir: MP4文件所在目录，默认为当前目录
        cover_dir: 封面图片所在目录，默认为mp4_dir

    返回:
        True: 成功
        False: 失败
    """
    if cover_dir is None:
        cover_dir = mp4_dir

    mp4_path = os.path.join(mp4_dir, f"{adnamemain}.mp4")

    if not os.path.exists(mp4_path):
        print(f"错误：MP4文件不存在: {mp4_path}")
        return False

    # 查找封面图片：adnamemainxxx.jpg
    cover_pattern = os.path.join(cover_dir, f"{adnamemain}*.jpg")
    cover_files = glob.glob(cover_pattern)

    if not cover_files:
        print(f"警告：未找到封面图片，模式: {cover_pattern}")
        return False

    # 优先选择文件名最短的（最接近adnamemain的）
    cover_files.sort(key=lambda x: len(os.path.basename(x)))
    cover_path = cover_files[0]

    # 如果有完全匹配的，优先使用
    exact_cover = os.path.join(cover_dir, f"{adnamemain}.jpg")
    if os.path.exists(exact_cover):
        cover_path = exact_cover

    print(f"找到封面图片: {cover_path}")

    # 创建临时输出文件
    temp_output = os.path.join(mp4_dir, f"{adnamemain}_with_cover_temp.mp4")

    try:
        cmd = [
            'ffmpeg',
            '-i', cover_path,
            '-i', mp4_path,
            '-map', '0:v',
            '-map', '1:v',
            '-map', '1:a',
            '-c:v', 'copy',
            '-c:a', 'copy',
            '-disposition:0', 'attached_pic',
            '-y',
            temp_output
        ]

        print(f"执行命令: {' '.join(cmd)}")

        result = subprocess.run(
            cmd,
            capture_output=True,
            text=True,
            shell=True
        )

        if result.returncode != 0:
            print(f"错误：ffmpeg执行失败")
            print(f"stderr: {result.stderr}")
            return False

        # 替换原文件
        os.remove(mp4_path)
        os.rename(temp_output, mp4_path)

        print(f"成功：封面已添加到 {mp4_path}")
        return True

    except Exception as e:
        print(f"错误：添加封面失败: {e}")
        if os.path.exists(temp_output):
            os.remove(temp_output)
        return False


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=60.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

import shutil
from pathlib import Path

def publish_video(output_file, publishingdir):
    # 1. 确保目标目录存在（优雅点：不存在就自动创建）
    publish_path = Path(publishingdir)
    publish_path.mkdir(parents=True, exist_ok=True)
    
    # 2. 构建完整的目标路径
    # 保持原文件名，直接拼接到目标文件夹下
    dest_path = publish_path / Path(output_file).name
    
    try:
        # 3. 跨盘移动文件的工业级标准写法
        # 如果 dest_path 已存在，它会直接覆盖
        shutil.move(output_file, dest_path)
        print(f"✅ 发布成功：{dest_path}")
        return str(dest_path)
    except Exception as e:
        print(f"❌ 移动文件失败: {e}")
        return None

# 调用示例
# publishingdir = "D:/Final_Videos/Publish"
# publish_video(output_file, publishingdir)

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}"
    print(cmd)
    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"xxx 视频流生成失败")
        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. 添加视频封面
    # print(f"正在进行：步骤5 - 添加视频封面")
    # ret=add_cover_physical(adname)
    # if not ret:
    # # if not add_cover_to_mp4(adname, mp4_dir="c:\\dev\\xtrs\\vdcb\\srtsegjoin"):
    #     print(f"错误：视频封面添加失败")
    #     # return False
    # # print(f"完成：步骤5 - 视频封面添加成功")

    # 6. 检查生成的视频文件
    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}秒")
    publishingdir=f"mp4forpub"
    publish_video(output_file,publishingdir)    
    return True
def makebypub(pub=14):
    pub1_items = []
    try:
        conn = get_db_connection()
        try:
            cursor = conn.execute(f'SELECT rowid FROM ads WHERE pub ={pub} ORDER BY rowid')
            pub1_items = cursor.fetchall()
            print(f"\n找到 {len(pub1_items)} 个 pub=1 的记录（新记录）")
        finally:
            cursor.close()
            conn.close()
            print("数据库连接已关闭")
    except Exception as e:
        print(f"查询 pub=1 记录失败: {e}")
        import traceback
        traceback.print_exc()    
    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()
        except Exception as e:
            print(f"更新 pub 值失败: {e}")
            import traceback
            traceback.print_exc()
            continue            
        try:
            if success:
                target_pub = 100+pub
                conn.execute('UPDATE ads SET pub = ?,pubsetdt_of_v=datetime("now","localtime") WHERE rowid = ?', (target_pub, rowid))
                conn.commit()
                print(f"更新成功：rowid={rowid}, pub={target_pub}")
            else:
                print(f"制作失败，将 pub 值重置为 -2...")
                # conn.execute('UPDATE ads SET pub = 1 WHERE rowid = ?', (rowid,))
                conn.execute('UPDATE ads SET pub = -2 WHERE rowid = ?', (rowid,))
                conn.commit()
                print(f"更新成功：rowid={rowid}, pub=1")
        finally:
            conn.close()
            print("数据库连接已关闭")
def make():
    pub1_items = []
    try:
        conn = get_db_connection()
        try:
            cursor = conn.execute(f'SELECT rowid,pub,name FROM ads WHERE (bilidt>datetime("2026-05-24 07:00") and pub<1 and ttscnt>0 and ttscnt==totsrts) ORDER BY rowid')
            pub1_items = cursor.fetchall()
            print(f"\n找到 {len(pub1_items)} 个 待生成bili-vd的的记录")
        finally:
            cursor.close()
            conn.close()
            print("数据库连接已关闭")
    except Exception as e:
        print(f"查询 pub=1 记录失败: {e}")
        import traceback
        traceback.print_exc()    
    for i, item in enumerate(pub1_items, 1):
        rowid,pub,name=item
        adid = rowid  # 使用 rowid 作为 adid
        print(f"\n{i}/{len(pub1_items)} rowid={rowid},pub:{pub} {name}")        
        # 先更新 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()
        except Exception as e:
            print(f"更新 pub 值失败: {e}")
            import traceback
            traceback.print_exc()
            continue            
        try:
            if success:
                target_pub = 100+pub
                conn.execute('UPDATE ads SET pub = ?,pubsetdt_of_v=datetime("now","localtime") WHERE rowid = ?', (target_pub, rowid))
                conn.commit()
                print(f"更新成功：rowid={rowid}, pub={target_pub}")
            else:
                print(f"制作失败，将 pub 值重置为 -2...")
                # conn.execute('UPDATE ads SET pub = 1 WHERE rowid = ?', (rowid,))
                conn.execute('UPDATE ads SET pub = -2 WHERE rowid = ?', (rowid,))
                conn.commit()
                print(f"更新成功：rowid={rowid}, pub=1")
        finally:
            conn.close()
            print("数据库连接已关闭")
        break
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:
                cursor.close()
                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:
                            # 从配置文件读取全局 pub2wxcflag 设置
                            pub2wxcflag = 0
                            try:
                                with open('cfg.toml', 'r', encoding='utf-8') as f:
                                    config = toml.load(f)
                                    pub2wxcflag = config.get('global', {}).get('pub2wxcflag', 0)
                            except:
                                pass
                            
                            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()
        
        # 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:
                cursor.close()
                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 设置
                            pub2wxcflag = 0
                            try:
                                with open('cfg.toml', 'r', encoding='utf-8') as f:
                                    config = toml.load(f)
                                    pub2wxcflag = config.get('global', {}).get('pub2wxcflag', 0)
                            except:
                                pass
                            
                            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.execute('UPDATE ads SET pub = -2 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()
        makebypub()
        makebypub(24)
        make()
        # 处理完所有记录后，等待 10 秒
        print(f"\n处理完所有记录，等待 10 秒后重新检查... 时间：{datetime.datetime.now()}")
        time.sleep(10)
if __name__ == "__main__":
    main()