#!/usr/bin/env python3
"""导出 GSV=0 用户的 tel_encrypt 字段到 Excel"""
import os
import sys
sys.path.insert(0, '/root/.openclaw/workspace')

import psycopg2
import pandas as pd
from openpyxl import Workbook
from datetime import datetime

# 读取密码
secrets = {}
with open('/root/.openclaw/workspace/secrets.env') as f:
    for line in f:
        line = line.strip()
        if line and not line.startswith('#') and '=' in line:
            k, v = line.split('=', 1)
            secrets[k.strip()] = v.strip().strip('"').strip("'")

conn = psycopg2.connect(
    host='bj-postgres-16pob4sg.sql.tencentcdb.com',
    port=28591,
    user='ai_member',
    password=secrets['PG_ONLINE_PASSWORD'],
    dbname='vala_bi'
)

# 查询：所有 status=1 且未删除的账号中，GSV=0（没有有效订单）的用户
# GSV=0 定义：没有任何 order_status=3（已完成）的订单
# 包括：从未下单的用户 + 下单但全部退款的用户
sql = """
SELECT 
    a.tel_encrypt
FROM bi_vala_app_account a
LEFT JOIN (
    SELECT 
        account_id,
        COUNT(*) FILTER (WHERE order_status IN (3, 4)) AS total_orders,
        COUNT(*) FILTER (WHERE order_status = 3) AS completed_orders
    FROM bi_vala_order
    GROUP BY account_id
) uos ON uos.account_id = a.id
WHERE a.status = 1 
  AND a.deleted_at IS NULL
  AND (uos.completed_orders IS NULL OR uos.completed_orders = 0)
ORDER BY a.id
"""

print("执行查询...")
df = pd.read_sql(sql, conn)
conn.close()

print(f"查询完成，共 {len(df)} 条记录")

# 写入 Excel
output_dir = '/root/.openclaw/workspace/output'
os.makedirs(output_dir, exist_ok=True)
output_path = os.path.join(output_dir, f'端内注册未成交用户加密手机号_{datetime.now().strftime("%Y%m%d_%H%M%S")}.xlsx')

wb = Workbook()
ws = wb.active
ws.title = "GSV=0用户"

# 表头
headers = ['tel_encrypt']
ws.append(headers)

# 数据
for _, row in df.iterrows():
    ws.append([row['tel_encrypt']])

# 列宽
ws.column_dimensions['A'].width = 30

# 统计 sheet


wb.save(output_path)
print(f"已导出到: {output_path}, 共 {len(df)} 人")
