#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import sqlite3
import os

# 数据库路径
srts_db_path = r'\\ll\D\xtrssvjj\db\dpd.db'
author_db_path = r"\\ll\d\wxvddata\dpd.db"


def find_adid_by_adname(search_term):
    """
    从srts表查找adid
    """
    try:
        conn = sqlite3.connect(srts_db_path)
        cursor = conn.cursor()
        
        # 搜索包含关键词的adname
        cursor.execute("""
            SELECT DISTINCT adid, adname FROM srts 
            WHERE adname LIKE ? 
            ORDER BY adid
        """, (f"%{search_term}%",))
        
        results = cursor.fetchall()
        conn.close()
        
        return results
        
    except Exception as e:
        print(f"查找adid失败: {e}")
        return []


def get_author(adname):
    """
    从author数据库获取作者信息
    """
    try:
        conn = sqlite3.connect(author_db_path)
        cursor = conn.cursor()
        
        # 使用adname的前60个字符进行模糊匹配
        search_name = adname[:60]
        cursor.execute("SELECT author FROM ads WHERE name LIKE ? LIMIT 1", (f"%{search_name}%",))
        result = cursor.fetchone()
        conn.close()
        
        return result[0] if result else "未知"
        
    except Exception as e:
        print(f"获取作者信息失败: {e}")
        return "未知"


def generate_subtitle(author):
    """
    生成副标题
    """
    return f"译自@{author}"


def main():
    print("=== 查找 许知远_恶心到我了 的adid和副标题 ===")
    
    # 搜索关键词
    search_terms = ["许知远", "恶心到我了"]
    
    for term in search_terms:
        print(f"\n搜索关键词: {term}")
        results = find_adid_by_adname(term)
        
        if results:
            print(f"找到 {len(results)} 条记录:")
            for adid, adname in results:
                print(f"  adid: {adid}, adname: {adname}")
                
                # 获取作者信息
                author = get_author(adname)
                subtitle = generate_subtitle(author)
                print(f"  author: {author}")
                print(f"  副标题: {subtitle}")
        else:
            print("  未找到匹配记录")


if __name__ == "__main__":
    main()