import sqlite3

# 获取数据库连接
def get_db_connection():
    conn = sqlite3.connect('./db/data.db')
    return conn

# 获取总时长
def get_total_duration():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT SUM(audio_duration) FROM split_sentences WHERE status="completed"')
    total_duration = cursor.fetchone()[0]  # 假设结果是以毫秒为单位
    conn.close()
    return total_duration if total_duration else 0

# 获取章节和节信息
def get_chapter_and_section_info():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT COUNT(DISTINCT chapter) FROM split_sentences WHERE is_chapter = 1')
    num_chapters = cursor.fetchone()[0]
    
    cursor.execute('SELECT COUNT(DISTINCT section), SUM(audio_duration) FROM split_sentences WHERE section IS NOT NULL')
    num_sections, total_section_duration = cursor.fetchone()
    
    avg_section_duration = total_section_duration / num_sections if num_sections > 0 else 0
    
    conn.close()
    return num_chapters, num_sections, avg_section_duration

# 时间选择器逻辑，根据总时长和章节信息选择不同的合并策略
def time_selector(total_duration, num_chapters, num_sections, avg_section_duration):
    if total_duration >= 2 * 3600 * 1000 and num_chapters > 0 and num_sections > 0 and avg_section_duration >= 30 * 60 * 1000:
        explanation = "总时长超过2小时，存在章和节，且节平均时间长度大于等于30分钟，执行A方案（按章节方案）。"
        return "A", explanation
    elif total_duration >= 2 * 3600 * 1000 and num_chapters > 0 and (num_sections == 0 or avg_section_duration < 30 * 60 * 1000):
        explanation = "总时长大于等于2小时，存在章，不存在节或节的平均长度小于30分钟，执行B方案（按章分切方案）。"
        return "B", explanation
    elif 55 * 60 * 1000 <= total_duration < 2 * 3600 * 1000:
        explanation = "总时长介于55分钟到2小时之间，执行C方案（多段分切方案）。"
        return "C", explanation
    elif total_duration < 55 * 60 * 1000:
        explanation = "总时长少于55分钟，执行D方案（全文合并方案）。"
        return "D", explanation
    else:
        explanation = "无效的时长，无法选择方案。"
        return "无效", explanation

# 封装的触发函数，返回策略和判断依据或报错信息
def trigger_time_selector():
    try:
        total_duration = get_total_duration()
        num_chapters, num_sections, avg_section_duration = get_chapter_and_section_info()
        
        selected_plan, explanation = time_selector(total_duration, num_chapters, num_sections, avg_section_duration)
        
        return selected_plan, explanation
    except Exception as e:
        return "错误", f"错误: {str(e)}"

# 示例调用
if __name__ == '__main__':
    plan, explanation = trigger_time_selector()
    print(f"选择的策略：{plan}")
    print(f"判断依据：{explanation}")