#!/usr/bin/env python
# coding=utf-8
"""
分析 opengid_base_data 样例数据
"""
import pandas as pd
from pathlib import Path

# 找到最新的输出文件
task_dir = Path(__file__).parent
output_dir = task_dir / "output"
csv_files = list(output_dir.glob("*.csv"))
if not csv_files:
    print("没有找到数据文件，请先运行 query.sql")
    exit(1)

latest_file = max(csv_files, key=lambda x: x.stat().st_mtime)
df = pd.read_csv(latest_file)

# 输出结果收集
lines = []


def log(text=""):
    print(text)
    lines.append(text)


log(f"分析文件: {latest_file.name}")
log()

# 基本信息
log("=" * 60)
log("基本信息")
log("=" * 60)
log(f"字段数: {len(df.columns)}")
log(f"记录数: {len(df)}")
log(f"渠道数: {df['channel'].nunique()}")
log()

# 渠道列表
log("=" * 60)
log("渠道列表")
log("=" * 60)
channels = df['channel'].unique()
for ch in channels:
    count = len(df[df['channel'] == ch])
    log(f"  {ch}: {count} 条")
log()

# 统计每个渠道的有效字段（非空率 > 50%）
channel_fields = {}
for channel in channels:
    ch_df = df[df['channel'] == channel]
    valid_fields = set()
    for col in ch_df.columns:
        if col in ['channel', 'rn', 'dt']:
            continue
        non_null_rate = ch_df[col].notna().sum() / len(ch_df)
        if non_null_rate >= 0.5:  # 非空率 >= 50% 认为是有效字段
            valid_fields.add(col)
    channel_fields[channel] = valid_fields

# 公共字段（所有渠道都有的）
all_channels_fields = list(channel_fields.values())
common_fields = set.intersection(*all_channels_fields) if all_channels_fields else set()

log("=" * 60)
log(f"公共字段（所有渠道都有，共 {len(common_fields)} 个）")
log("=" * 60)
for field in sorted(common_fields):
    log(f"  {field}")
log()

# 每个渠道独有的字段
log("=" * 60)
log("各渠道独有字段")
log("=" * 60)
for channel in channels:
    unique_fields = channel_fields[channel] - common_fields
    # 检查是否真的独有（其他渠道都没有）
    truly_unique = set()
    for field in unique_fields:
        is_unique = True
        for other_ch in channels:
            if other_ch != channel and field in channel_fields[other_ch]:
                is_unique = False
                break
        if is_unique:
            truly_unique.add(field)

    if truly_unique:
        log(f"\n【{channel}】独有字段 ({len(truly_unique)} 个):")
        for field in sorted(truly_unique):
            log(f"  {field}")
log()

# 每个渠道的完整字段列表
log("=" * 60)
log("各渠道完整字段列表（非空率 >= 50%）")
log("=" * 60)

for channel in channels:
    ch_df = df[df['channel'] == channel]
    log(f"\n【{channel}】({len(ch_df)} 条)")
    log("-" * 50)

    # 统计每个字段
    field_stats = []
    for col in ch_df.columns:
        if col in ['channel', 'rn', 'dt']:
            continue
        non_null = ch_df[col].notna().sum()
        rate = non_null / len(ch_df)
        field_stats.append((col, rate, non_null))

    # 按非空率排序
    field_stats.sort(key=lambda x: (-x[1], x[0]))

    # 完整字段
    full_fields = [(c, r, n) for c, r, n in field_stats if r == 1.0]
    if full_fields:
        log(f"\n  完整字段 ({len(full_fields)} 个):")
        for col, rate, n in full_fields:
            log(f"    {col}")

    # 部分有值
    partial_fields = [(c, r, n) for c, r, n in field_stats if 0 < r < 1.0]
    if partial_fields:
        log(f"\n  部分有值 ({len(partial_fields)} 个):")
        for col, rate, n in partial_fields:
            log(f"    {col}: {rate:.0%} ({n}/{len(ch_df)})")

    # 全空字段
    empty_fields = [(c, r, n) for c, r, n in field_stats if r == 0]
    if empty_fields:
        log(f"\n  全空字段 ({len(empty_fields)} 个):")
        for col, rate, n in empty_fields:
            log(f"    {col}")

# 输出推荐取数字段
log()
log("=" * 60)
log("各渠道推荐取数字段（非空率 >= 50%）")
log("=" * 60)
for channel in channels:
    fields = sorted(channel_fields[channel])
    log(f"\n【{channel}】({len(fields)} 个字段):")
    log(f"  {', '.join(fields)}")

# 保存分析结果
result_file = output_dir / f"{latest_file.stem}_分析.txt"
with open(result_file, 'w', encoding='utf-8') as f:
    f.write("\n".join(lines))

log()
log(f"分析结果已保存到: {result_file}")
