#!/usr/bin/env python3
"""
分析会员卡结算情况，找出所有相关字段和正确的消费金额计算方式
"""

import os
import psycopg2
from datetime import datetime
from dotenv import load_dotenv

def main():
    # 加载环境变量
    load_dotenv()
    
    test_db_dsn = os.environ.get('TEST_DB_DSN')
    if not test_db_dsn:
        raise RuntimeError("TEST_DB_DSN 环境变量未设置")
    
    print("💳 会员卡结算分析")
    print("=" * 50)
    
    with psycopg2.connect(test_db_dsn) as conn:
        with conn.cursor() as cur:
            
            # 1. 查看所有金额相关字段
            print("\n💰 1. 所有金额相关字段分析")
            
            cur.execute("""
                SELECT column_name, data_type
                FROM information_schema.columns 
                WHERE table_schema = 'ods' AND table_name = 'settlement_records'
                AND (column_name LIKE '%amount%' OR column_name LIKE '%money%' OR column_name LIKE '%card%')
                ORDER BY column_name
            """)
            
            amount_fields = cur.fetchall()
            print("金额相关字段:")
            for field_name, data_type in amount_fields:
                print(f"  {field_name}: {data_type}")
            
            # 2. 分析零消费订单的各字段值
            print("\n🔍 2. 零消费订单字段值分析")
            
            cur.execute("""
                SELECT 
                    payamount,
                    balanceamount,
                    cardamount,
                    cashamount,
                    couponamount,
                    onlineamount,
                    pointamount,
                    refundamount,
                    roundingamount,
                    adjustamount,
                    couponsaleamount,
                    memberdiscountamount,
                    tablechargemoney,
                    goodsmoney,
                    realgoodsmoney,
                    servicemoney,
                    prepaymoney,
                    rechargecardamount,
                    giftcardamount,
                    COUNT(*) as record_count
                FROM ods.settlement_records
                WHERE payamount = 0
                AND paytime >= '2026-02-01'
                GROUP BY 
                    payamount, balanceamount, cardamount, cashamount, couponamount,
                    onlineamount, pointamount, refundamount, roundingamount, adjustamount,
                    couponsaleamount, memberdiscountamount, tablechargemoney, goodsmoney,
                    realgoodsmoney, servicemoney, prepaymoney, rechargecardamount, giftcardamount
                ORDER BY record_count DESC
                LIMIT 10
            """)
            
            zero_patterns = cur.fetchall()
            print("零消费订单的字段组合 (前10种模式):")
            for i, pattern in enumerate(zero_patterns):
                print(f"\n  模式 {i+1} ({pattern[-1]} 条记录):")
                field_names = [
                    'payamount', 'balanceamount', 'cardamount', 'cashamount', 'couponamount',
                    'onlineamount', 'pointamount', 'refundamount', 'roundingamount', 'adjustamount',
                    'couponsaleamount', 'memberdiscountamount', 'tablechargemoney', 'goodsmoney',
                    'realgoodsmoney', 'servicemoney', 'prepaymoney', 'rechargecardamount', 'giftcardamount'
                ]
                for j, field_name in enumerate(field_names):
                    value = pattern[j]
                    if value != 0:
                        print(f"    {field_name}: {value}")
            
            # 3. 分析正常消费订单的字段值
            print("\n💵 3. 正常消费订单字段值分析")
            
            cur.execute("""
                SELECT 
                    payamount,
                    balanceamount,
                    cardamount,
                    cashamount,
                    couponamount,
                    goodsmoney,
                    realgoodsmoney,
                    servicemoney,
                    tablechargemoney,
                    COUNT(*) as record_count
                FROM ods.settlement_records
                WHERE payamount > 0
                AND paytime >= '2026-02-01'
                GROUP BY 
                    payamount, balanceamount, cardamount, cashamount, couponamount,
                    goodsmoney, realgoodsmoney, servicemoney, tablechargemoney
                ORDER BY record_count DESC
                LIMIT 5
            """)
            
            normal_patterns = cur.fetchall()
            print("正常消费订单的字段组合 (前5种模式):")
            for i, pattern in enumerate(normal_patterns):
                print(f"\n  模式 {i+1} ({pattern[-1]} 条记录):")
                field_names = [
                    'payamount', 'balanceamount', 'cardamount', 'cashamount', 'couponamount',
                    'goodsmoney', 'realgoodsmoney', 'servicemoney', 'tablechargemoney'
                ]
                for j, field_name in enumerate(field_names):
                    value = pattern[j]
                    print(f"    {field_name}: {value}")
            
            # 4. 分析可能的消费金额计算公式
            print("\n🧮 4. 消费金额计算公式分析")
            
            # 测试不同的计算公式
            formulas = [
                ("payamount", "payamount"),
                ("现金+卡", "cashamount + cardamount"),
                ("现金+卡+余额", "cashamount + cardamount + balanceamount"),
                ("现金+卡+优惠券", "cashamount + cardamount + couponamount"),
                ("商品金额", "goodsmoney"),
                ("实际商品金额", "realgoodsmoney"),
                ("商品+服务", "goodsmoney + servicemoney"),
                ("商品+台费", "goodsmoney + tablechargemoney"),
                ("全部金额", "cashamount + cardamount + balanceamount + couponamount + onlineamount"),
                ("储值卡+现金+团购", "cardamount + cashamount + couponamount")
            ]
            
            for formula_name, formula_sql in formulas:
                cur.execute(f"""
                    SELECT 
                        COUNT(*) as total_records,
                        COUNT(CASE WHEN ({formula_sql}) > 0 THEN 1 END) as positive_records,
                        AVG({formula_sql}) as avg_amount,
                        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY {formula_sql}) as median_amount,
                        MIN({formula_sql}) as min_amount,
                        MAX({formula_sql}) as max_amount
                    FROM ods.settlement_records
                    WHERE paytime >= '2026-02-01'
                    AND memberid > 0  -- 只看会员订单
                """)
                
                result = cur.fetchone()
                total, positive, avg_amt, median_amt, min_amt, max_amt = result
                positive_rate = (positive / total) * 100 if total > 0 else 0
                
                print(f"\n  {formula_name}: {formula_sql}")
                print(f"    正数记录: {positive:,}/{total:,} ({positive_rate:.1f}%)")
                print(f"    平均值: {avg_amt:.2f}, 中位数: {median_amt:.2f}")
                print(f"    范围: [{min_amt:.2f}, {max_amt:.2f}]")
            
            # 5. 深入分析会员卡相关字段
            print("\n💳 5. 会员卡相关字段深度分析")
            
            card_fields = ['cardamount', 'balanceamount', 'rechargecardamount', 'giftcardamount']
            
            for field in card_fields:
                cur.execute(f"""
                    SELECT 
                        CASE 
                            WHEN {field} = 0 THEN '零值'
                            WHEN {field} > 0 AND {field} <= 50 THEN '小额(≤50)'
                            WHEN {field} > 50 AND {field} <= 200 THEN '中额(50-200)'
                            WHEN {field} > 200 THEN '大额(>200)'
                            WHEN {field} < 0 THEN '负值'
                        END as amount_range,
                        COUNT(*) as record_count,
                        AVG({field}) as avg_amount
                    FROM ods.settlement_records
                    WHERE paytime >= '2026-02-01'
                    GROUP BY 
                        CASE 
                            WHEN {field} = 0 THEN '零值'
                            WHEN {field} > 0 AND {field} <= 50 THEN '小额(≤50)'
                            WHEN {field} > 50 AND {field} <= 200 THEN '中额(50-200)'
                            WHEN {field} > 200 THEN '大额(>200)'
                            WHEN {field} < 0 THEN '负值'
                        END
                    ORDER BY record_count DESC
                """)
                
                field_stats = cur.fetchall()
                print(f"\n  {field} 分布:")
                for amount_range, count, avg_amt in field_stats:
                    print(f"    {amount_range}: {count:,} 条, 平均 {avg_amt:.2f}")
            
            # 6. 找出最可能的会员卡抵扣字段
            print("\n🎯 6. 推荐的会员卡抵扣字段")
            
            # 分析零消费但有其他金额的订单
            cur.execute("""
                SELECT 
                    'cardamount' as field_name,
                    COUNT(CASE WHEN payamount = 0 AND cardamount > 0 THEN 1 END) as zero_pay_positive_field,
                    COUNT(CASE WHEN payamount > 0 AND cardamount > 0 THEN 1 END) as positive_pay_positive_field,
                    AVG(CASE WHEN cardamount > 0 THEN cardamount END) as avg_when_positive
                FROM ods.settlement_records
                WHERE paytime >= '2026-02-01'
                
                UNION ALL
                
                SELECT 
                    'balanceamount' as field_name,
                    COUNT(CASE WHEN payamount = 0 AND balanceamount > 0 THEN 1 END),
                    COUNT(CASE WHEN payamount > 0 AND balanceamount > 0 THEN 1 END),
                    AVG(CASE WHEN balanceamount > 0 THEN balanceamount END)
                FROM ods.settlement_records
                WHERE paytime >= '2026-02-01'
                
                UNION ALL
                
                SELECT 
                    'couponamount' as field_name,
                    COUNT(CASE WHEN payamount = 0 AND couponamount > 0 THEN 1 END),
                    COUNT(CASE WHEN payamount > 0 AND couponamount > 0 THEN 1 END),
                    AVG(CASE WHEN couponamount > 0 THEN couponamount END)
                FROM ods.settlement_records
                WHERE paytime >= '2026-02-01'
            """)
            
            field_analysis = cur.fetchall()
            print("字段与零消费关联性分析:")
            for field_name, zero_pay_positive, positive_pay_positive, avg_positive in field_analysis:
                print(f"  {field_name}:")
                print(f"    零消费但该字段>0: {zero_pay_positive:,} 条")
                print(f"    正常消费且该字段>0: {positive_pay_positive:,} 条")
                if avg_positive is not None:
                    print(f"    该字段>0时平均值: {avg_positive:.2f}")
                else:
                    print(f"    该字段>0时平均值: 无数据")
            
            # 7. 提供具体的样本数据
            print("\n📋 7. 具体样本数据")
            
            cur.execute("""
                SELECT 
                    id,
                    paytime,
                    payamount,
                    cardamount,
                    balanceamount,
                    cashamount,
                    couponamount,
                    goodsmoney,
                    memberid
                FROM ods.settlement_records
                WHERE payamount = 0
                AND (cardamount > 0 OR balanceamount > 0 OR couponamount > 0)
                AND paytime >= '2026-02-01'
                ORDER BY paytime DESC
                LIMIT 10
            """)
            
            samples = cur.fetchall()
            print("零消费但有其他金额的样本 (前10条):")
            for sample in samples:
                oid, paytime, payamount, cardamount, balanceamount, cashamount, couponamount, goodsmoney, memberid = sample
                print(f"  ID: {oid}, 时间: {paytime.strftime('%m-%d %H:%M')}")
                print(f"    pay: {payamount}, card: {cardamount}, balance: {balanceamount}")
                print(f"    cash: {cashamount}, coupon: {couponamount}, goods: {goodsmoney}")

if __name__ == "__main__":
    main()