#!/usr/bin/env python3
"""
根据音频文件名查询id和字幕
"""
import sqlite3
import os

db_path = "\\\\ll\\D\\xtrssvjj\\dpd.db"
audio_file = "\\\\ll\\d\\xtrssvjj\\失去对流量的掌控_这可能是雷军和小米汽车当下最焦虑的事情__小米__雷军__SU7__YU7.mp3"

conn = sqlite3.connect(db_path)
cursor = conn.cursor()

# 提取文件名（不含路径）
audio_name = os.path.basename(audio_file)

# 查询id
cursor.execute("SELECT id FROM ads WHERE name = ?", (audio_name,))
result = cursor.fetchone()

if result:
    adid = result[0]
    print(f"找到 adid: {adid}")
    
    # 查询字幕数量
    cursor.execute("SELECT COUNT(*) FROM srts WHERE adid = ?", (adid,))
    count = cursor.fetchone()[0]
    print(f"字幕数量: {count}句")
    
    # 查看前3句字幕
    cursor.execute("SELECT idx, cn, en FROM srts WHERE adid = ? ORDER BY idx LIMIT 3", (adid,))
    subs = cursor.fetchall()
    print("\n前3句字幕:")
    for idx, cn, en in subs:
        print(f"[{idx}] 中: {cn}")
        print(f"    英: {en}")
        print()
else:
    print("未找到对应的adid")

conn.close()