#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
查询韩寒创业简史 下集的字幕数据
"""

import sqlite3

db_path = r'\\ll\D\xtrssvjj\db\dpd.db'
adid = 8434

try:
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    print(f"查询adid={adid}的字幕数据\n")
    
    # 查询前5句字幕
    cursor.execute("SELECT idx, start, end, text FROM srts WHERE adid = ? AND idx < 5 ORDER BY idx", (adid,))
    subtitles = cursor.fetchall()
    
    print("前5句字幕数据:")
    print("-" * 80)
    for idx, start_ms, end_ms, text in subtitles:
        print(f"idx={idx:02d}:  start={start_ms:6d}ms  end={end_ms:6d}ms  duration={end_ms-start_ms:6d}ms")
        print(f"         text={text[:50]}..." if len(text) > 50 else f"         text={text}")
        print()
    
    # 计算seg003的截取参数
    if len(subtitles) >= 4:
        print("=" * 80)
        print("seg003的截取过程:")
        print("=" * 80)
        
        # idx=0, 1, 2, 3的字幕数据
        idx0, start0, end0, text0 = subtitles[0]
        idx1, start1, end1, text1 = subtitles[1]
        idx2, start2, end2, text2 = subtitles[2]
        idx3, start3, end3, text3 = subtitles[3]
        
        print(f"\nidx=2 (前一句):")
        print(f"  start={start2:6d}ms")
        print(f"  end  ={end2:6d}ms")
        
        print(f"\nidx=3 (当前句):")
        print(f"  start={start3:6d}ms")
        print(f"  end  ={end3:6d}ms")
        
        print(f"\nidx=4 (下一句):")
        cursor.execute("SELECT idx, start, end FROM srts WHERE adid = ? AND idx = 4", (adid,))
        idx4_data = cursor.fetchone()
        if idx4_data:
            idx4, start4, end4 = idx4_data
            print(f"  start={start4:6d}ms")
            print(f"  end  ={end4:6d}ms")
        
        print(f"\n计算seg003的截取时间:")
        # 开始时间 = (前一句.end + 当前句.start) // 2
        start_time = (end2 + start3) // 2
        print(f"  start_time = (idx2.end({end2}) + idx3.start({start3})) // 2 = {start_time}ms")
        
        # 结束时间 = (当前句.end + 下一句.start) // 2
        if idx4_data:
            end_time = (end3 + start4) // 2
            print(f"  end_time   = (idx3.end({end3}) + idx4.start({start4})) // 2 = {end_time}ms")
            
            # 前gap和后gap
            prev_gap = start3 - end2
            next_gap = start4 - end3
            print(f"  prev_gap   = idx3.start({start3}) - idx2.end({end2}) = {prev_gap}ms {'(<800ms, 需要修正)' if prev_gap < 800 else '(>=800ms, 不需要修正)'}")
            print(f"  next_gap   = idx4.start({start4}) - idx3.end({end3}) = {next_gap}ms {'(<800ms, 需要修正)' if next_gap < 800 else '(>=800ms, 不需要修正)'}")
            
            # 音频片段时长
            audio_segment_duration = end_time - start_time
            subtitle_duration = end3 - start3
            print(f"\n音频片段时长: {audio_segment_duration}ms")
            print(f"字幕时长:     {subtitle_duration}ms")
    
    conn.close()
    
except Exception as e:
    print(f"错误: {e}")