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

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

from add_cover_to_mp4a import add_cover_physical
from urllib.parse import quote
from contextlib import closing
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=160.0)
        conn.row_factory = sqlite3.Row
        
        try:
            conn.execute("PRAGMA journal_mode=WAL;")            
            # 优化数据库参数，减少锁定
            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_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
        return conn
        try:
            # 优化数据库参数，减少锁定
            # conn.execute('PRAGMA synchronous=NORMAL')
            # conn.execute('PRAGMA cache_size=-64000')
            return conn
        except Exception as e:
            conn.close()
    except Exception as e:
        import traceback
        print(f"错误 [函数: {func_name}, 行号: {line_no}]: 连接数据库失败: {e}")
        traceback.print_exc()
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,adname="",author=''):
    # """
    # 处理单个广告，执行双语视频制作流程
    # """
    # 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
    # )
    from joinbiliad import extract_and_combine
    ret=extract_and_combine(adid,adname)
    print(f"命令执行完成，时间：{datetime.datetime.now()}")
    
    end_time = datetime.datetime.now()
    duration = (end_time - start_time).total_seconds()
    
    print(f"命令执行完成，返回值：{ret}")
    print(f"执行时间：{duration:.2f}秒")
    
    # returncode = result.returncode    
    # if returncode != 0:
    #     print(f"错误：双语音频制作失败")
    #     return False
    # print(f"完成：步骤1 - 双语音频制作成功")
    if not ret:
        print(r"错误：ffprobe xxx/_cn_audio/*.mp3 句段时间及双语音频制作失败")
        return False
    print(f"完成：步骤1 - 双语音频制作成功")
    return True
    # 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
    # )
    subs=reqsubs(adid)
    from generate_subtitles import generate_subtitle_images
    ret=generate_subtitle_images(adid,adname,author,subs)
    if not ret:
        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
    # )
    from generate_bilingual_video import make_bili_vd
    ret=make_bili_vd(adid,adname)
    if not ret:
        print(f"xxx 视频流生成失败")
        return False
    print(f"完成：步骤3 - 视频流生成成功")    
    # 4. 合并音视频
    print(f"正在进行：步骤4 - 合并音、视频")
    # cmd = f"python merge_audio_video.py {adid}"
    cmd = [sys.executable,"-W","ignore::UserWarning:.*debugpy.*","merge_audio_video.py", str(adid)]
    # result = subprocess.run(
    #     cmd, 
    #     cwd="c:\\dev\\xtrs\\vdcb\\srtsegjoin",
    #     shell=False,
    #     capture_output=False,
    #     text=True
    # )
    
    # if result.returncode != 0:
    #     print(f"错误：音视频合并失败")
    #     return False
    from merge_audio_video import mergeadvd
    ret=mergeadvd(adid,adname)
    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"xxxx 错误：最终视频文件不存在: {output_path}")
        return False
    
    # 检查视频时长
    video_duration = get_file_duration(output_path)
    if video_duration < 1:
        print(f"xxxx 错误：视频时长过短: {video_duration}秒")
        return False
    
    print(f"vvv 成功：视频制作完成，时长: {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 = 4
                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 make1():
    try:
        conn = get_db_connection()
        try:
            cursor = conn.execute(f'SELECT rowid,pub,name FROM ads WHERE (bilidt>datetime("2026-05-23 07:00") and pub>-1 and pub<3 and ttscnt>0 and ttscnt==totsrts) ORDER BY pub desc,rowid')
            item = cursor.fetchone()
            if not item:
                return
        finally:
            cursor.close()
            conn.close()
            print("数据库连接已关闭")
    except Exception as e:
        print(f"查询 pub=1 记录失败: {e}")
        import traceback
        traceback.print_exc()    
        return
    rowid,pub,name=item
    adid = rowid  # 使用 rowid 作为 adid
    print(f"rowid={rowid},pub:{pub} {name}")        
    upldlog("< start: "+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()
        return    
    # 执行制作流程
    success = process_item(adid,name)
    
    # 更新 pub 值
    try:
        conn = get_db_connection()
    except Exception as e:
        print(f"更新 pub 值失败: {e}")
        import traceback
        traceback.print_exc()
        return
    try:
        if success:
            target_pub = 4
            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}")
            upldlog('> end   : '+name)
            return True
        else:
            print(f"制作失败，将 pub 值重置为 -1...")
            # conn.execute('UPDATE ads SET pub = 1 WHERE rowid = ?', (rowid,))
            conn.execute('UPDATE ads SET pub = -1 WHERE rowid = ?', (rowid,))
            conn.commit()
    finally:
        conn.close()
        print("数据库连接已关闭")        
def makeit(adid=0):
    if adid==0:
        return
    try:
        conn = get_db_connection()
        try:
            cursor = conn.execute(f'SELECT pub,name FROM ads WHERE rowid={adid}')
            item = cursor.fetchone()
            if not item:
                return
        finally:
            cursor.close()
            conn.close()
            print("数据库连接已关闭")
    except Exception as e:
        print(f"查询 pub=1 记录失败: {e}")
        import traceback
        traceback.print_exc()    
        return
    pub,name=item
    print(f"rowid={adid},pub:{pub} {name}")        
    # 先更新 pub 值为 2
    try:
        conn = get_db_connection()
        try:
            print("正在更新 pub 值为 2 (制作中)...")
            conn.execute('UPDATE ads SET pub = 2 WHERE rowid = ?', (adid,))
            conn.commit()
            print(f"更新成功：rowid={adid}, pub=2")
        finally:
            conn.close()
            print("数据库连接已关闭")
    except Exception as e:
        print(f"更新 pub=2 失败: {e}")
        import traceback
        traceback.print_exc()
        return    
    # 执行制作流程
    success = process_item(adid)
    
    # 更新 pub 值
    try:
        conn = get_db_connection()
    except Exception as e:
        print(f"更新 pub 值失败: {e}")
        import traceback
        traceback.print_exc()
        return
    try:
        if success:
            target_pub = 4
            conn.execute('UPDATE ads SET pub = ?,pubsetdt_of_v=datetime("now","localtime") WHERE rowid = ?', (target_pub, adid))
            conn.commit()
            print(f"更新成功：rowid={adid}, pub={target_pub}")
            return True
        else:
            print(f"制作失败，将 pub 值重置为 -1...")
            # conn.execute('UPDATE ads SET pub = 1 WHERE rowid = ?', (rowid,))
            conn.execute('UPDATE ads SET pub = -1 WHERE rowid = ?', (adid,))
            conn.commit()
    finally:
        conn.close()
        print("数据库连接已关闭")
def upldlog(txt):
    nowstr=str(datetime.datetime.now())[6:-7]
    url='http://ll:899/api/appendmakebilivdlog/'+quote(nowstr+' '+txt)
    resp=requests.get(url)    
def makenc():
    print(f"\n=== 开始新一轮检查，时间：{datetime.datetime.now()} ===")    
    # 1. 查询 pub=2 的记录
    pub2_items = []
    try:
        conn = get_db_connection()
        try:
            cursor = conn.execute('SELECT rowid,name 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,adname = item
            adid = rowid  # 使用 rowid 作为 adid
            print(f"\n[{i}/{len(pub2_items)}] 处理 pub=2 记录: rowid={rowid}, adid={adid}")
            upldlog("< start: "+adname)
            # 执行制作流程
            success = process_item(adid,adname)
            
            # 更新 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}")
                        upldlog('> end   : '+adname)
                    else:
                        print(f"制作失败，将 pub 值重置为 1...")
                        conn.execute('UPDATE ads SET pub = 1 WHERE rowid = ?', (rowid,))
                        conn.commit()
                finally:
                    conn.close()
                    print("数据库连接已关闭")
            except Exception as e:
                print(f"更新 pub 值失败: {e}")
                import traceback
                traceback.print_exc()
def makeold():    
    # 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()
def req():
    conn = get_db_connection()
    cursor=None
    try:
        cursor = conn.execute(f'SELECT rowid,pub,name FROM ads WHERE (bilidt>datetime("2026-05-24 07:00") and pub>-1 and pub<3 and ttscnt>0 and ttscnt==totsrts) ORDER BY pub desc,rowid')
        item = cursor.fetchone()
        return item
    except Exception as ex:
        print('xxx',ex)
    finally:
        if cursor:
            cursor.close()
        conn.close()
        print("数据库连接已关闭")
def req():
    url='http://ll:899/api/req4makebilivd'
    resp=requests.get(url)
    return resp.json()
def querypub(rowid):        
    conn = get_db_connection()
    try:
        # 1. 使用 with 管理 cursor，无论是否报错、是否有数据，离开 with 块时 cursor 都会自动安全关闭
        with closing(conn.execute('SELECT name, pub FROM ads WHERE rowid = ?', (rowid,))) as cursor:
            item = cursor.fetchone()
            if item:
                name, pub = item
                print(pub,name)
                upldlog(f"{pub} {name}")
                return item
    except Exception as ex:
        print('xxx',ex)
    finally:
        # 2. 无论 try 块里发生什么，数据库连接一定会被关闭
        conn.close()
        print("数据库连接已关闭")
def setmakingstate(rowid):   
    # 先更新 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")
            return True
        finally:
            conn.close()
            print("数据库连接已关闭")
    except Exception as e:
        print(f"更新 pub=2 失败: {e}")
        import traceback
        traceback.print_exc()
        return
def setpub(adid,name,success):    
    # 更新 pub 值
    try:
        conn = get_db_connection()
    except Exception as e:
        print(f"更新 pub 值失败: {e}")
        import traceback
        traceback.print_exc()
        return
    try:
        if success:
            target_pub = 4
            conn.execute('UPDATE ads SET pub = ?,pubsetdt_of_v=datetime("now","localtime") WHERE rowid = ?', (target_pub, adid))
            conn.commit()
            print(f"更新成功：rowid={adid}, pub={target_pub}")
            upldlog('> end  : '+name)           
            return True
        else:
            print(f"制作失败，将 pub 值重置为 -1...")
            # conn.execute('UPDATE ads SET pub = 1 WHERE rowid = ?', (rowid,))
            conn.execute('UPDATE ads SET pub = -1 WHERE rowid = ?', (adid,))
            conn.commit()
    finally:
        conn.close()
        print("数据库连接已关闭")        
def setpub(adid,newpub):
    url=f'http://ll:899/api/setpub/{adid}/{newpub}'
    resp=requests.get(url)
    return resp.text==''
def setbilivdmadedt(adid):
    url=f'http://ll:899/api/setbilivdmadedt/{adid}'
    resp=requests.get(url)
    return resp.text==''
def reqsubs(adid):
    url=f'http://ll:899/api/reqsubs/{adid}'
    resp=requests.get(url)
    return resp.json()
def main():
    """
    主函数，循环处理 pub=1 和 pub=2 的记录
    """
    import datetime
    # makenc()
    print("=== 双语视频制作循环开始 ===")
    while True: 
        # ret=makeit(282179)        
        # makeold()
        # makebypub()
        # makebypub(24)
        try:
            item=req()
            if not item:
                print('no item to make,or db conn err,try later...')
                time.sleep(10)
                continue
            rowid,pub,name,author=item
            print(f"rowid={rowid},pub:{pub} {name}")
            upldlog("< start: pub="+str(pub)+" "+name)
            ret=setpub(rowid,2)
            if not ret:
                time.sleep(10)
                continue
            success=process_item(rowid,name,author)
            if not success:
                print('xxxxxxxxxxxxxxxxx')
                setpub(rowid,-1)
                time.sleep(10)
                continue
            # ret=setpub(rowid,4)
            ret=setbilivdmadedt(rowid)
            upldlog('> end   : '+str(ret)+'    '+name)
            # for idx in range(11):
            #     querypub(rowid)
            #     time.sleep(5)
            if ret:
                continue        
        except Exception as ex:
            print(f"xxx {str(datetime.datetime.now())[6:-7]} ex:{ex}")
        print(f"xxx {str(datetime.datetime.now())[6:-7]} 有错，等待 10 秒后重做...")
        time.sleep(10)
if __name__ == "__main__":
    main()