# -*- coding: utf-8 -*-
import sys
sys.path.insert(0, '.')
from config.settings import AppConfig
from database.connection import DatabaseConnection
from database.operations import DatabaseOperations

config = AppConfig.load()
db_conn = DatabaseConnection(config.config['db']['dsn'])
db = DatabaseOperations(db_conn)

# 检查DWD层服务记录分布
print("=== DWD层服务记录分析 ===")
print()

# 1. 总体统计
sql1 = """
    SELECT 
        COUNT(*) as total_records,
        COUNT(DISTINCT tenant_member_id) as unique_members,
        COUNT(DISTINCT site_assistant_id) as unique_assistants,
        COUNT(DISTINCT (tenant_member_id, site_assistant_id)) as unique_pairs
    FROM billiards_dwd.dwd_assistant_service_log
    WHERE tenant_member_id > 0 AND is_delete = 0
"""
r = dict(db.query(sql1)[0])
print("总体统计:")
print(f"  总服务记录数: {r['total_records']}")
print(f"  唯一会员数: {r['unique_members']}")
print(f"  唯一助教数: {r['unique_assistants']}")
print(f"  唯一客户-助教对: {r['unique_pairs']}")

# 2. 助教服务会员数分布
print()
print("助教服务会员数分布 (Top 10):")
sql2 = """
    SELECT site_assistant_id, COUNT(DISTINCT tenant_member_id) as member_count
    FROM billiards_dwd.dwd_assistant_service_log
    WHERE tenant_member_id > 0 AND is_delete = 0
    GROUP BY site_assistant_id
    ORDER BY member_count DESC
    LIMIT 10
"""
for row in db.query(sql2):
    r = dict(row)
    print(f"  助教 {r['site_assistant_id']}: 服务 {r['member_count']} 个会员")

# 3. 每个客户-助教对的服务次数分布
print()
print("客户-助教对 服务次数分布 (Top 10):")
sql3 = """
    SELECT tenant_member_id, site_assistant_id, COUNT(*) as service_count
    FROM billiards_dwd.dwd_assistant_service_log
    WHERE tenant_member_id > 0 AND is_delete = 0
    GROUP BY tenant_member_id, site_assistant_id
    ORDER BY service_count DESC
    LIMIT 10
"""
for row in db.query(sql3):
    r = dict(row)
    print(f"  会员 {r['tenant_member_id']} - 助教 {r['site_assistant_id']}: {r['service_count']} 次服务")

# 4. 近60天的数据
print()
print("=== 近60天数据 ===")
sql4 = """
    SELECT 
        COUNT(*) as total_records,
        COUNT(DISTINCT tenant_member_id) as unique_members,
        COUNT(DISTINCT site_assistant_id) as unique_assistants,
        COUNT(DISTINCT (tenant_member_id, site_assistant_id)) as unique_pairs
    FROM billiards_dwd.dwd_assistant_service_log
    WHERE tenant_member_id > 0 AND is_delete = 0
      AND last_use_time >= NOW() - INTERVAL '60 days'
"""
r4 = dict(db.query(sql4)[0])
print(f"  总服务记录数: {r4['total_records']}")
print(f"  唯一会员数: {r4['unique_members']}")
print(f"  唯一助教数: {r4['unique_assistants']}")
print(f"  唯一客户-助教对: {r4['unique_pairs']}")

db_conn.close()