#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
查找有srts数据的ads
"""

import sqlite3


def find_ads_with_srts():
    """
    查找有srts数据的ads
    """
    conn = sqlite3.connect('srtsegjoin/dpd.db')
    cur = conn.cursor()
    
    # 查询srts表中有哪些adid
    cur.execute("SELECT DISTINCT adid FROM srts")
    adids = cur.fetchall()
    print(f"srts表中有 {len(adids)} 个不同的adid")
    
    # 查询这些adid对应的ads数据
    print(f"\n有srts数据的ads:")
    for adid_row in adids:
        adid = adid_row[0]
        cur.execute("SELECT oid, name FROM ads WHERE oid=?", (adid,))
        ads_row = cur.fetchone()
        if ads_row:
            print(f"  oid={ads_row[0]}")
            print(f"    name={ads_row[1][:60]}...")
            
            # 查询srts数量
            cur.execute("SELECT COUNT(*) FROM srts WHERE adid=?", (adid,))
            srts_count = cur.fetchone()[0]
            print(f"    srts数量={srts_count}")
    
    conn.close()


if __name__ == "__main__":
    find_ads_with_srts()