#!/usr/bin/env python3
"""Check 57 gap orders - efficient approach"""
import subprocess, os, re

PGPASS = "LdfjdjL83h3h3^$&**YGG*"

# Gap data extracted from chat history
gaps = [
    # 3月 (20)
    (1,"3月","qX7oJ6","小龙","雪珂💗","3月6日",1999,"微信小店","","sales-adp-bj-jxl-0"),
    (2,"3月","qX7oJ6","小龙","Mars（燚）","3月7日",3598,"小红书-晚柠","","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    (3,"3月","qX7oJ6","小龙","薇薇","3月",1999,"小红书直购","13520306626","newmedia-dianpu-xhs-0-0"),
    (4,"3月","qX7oJ6","小龙","Yeah～^_^","3月20日",3598,"小红书-晚柠","","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    (5,"3月","qX7oJ6","小龙","TutuTu","3月",1999,"小红书直购","18107332677","newmedia-dianpu-xhs-0-0"),
    (6,"3月","qX7oJ6","小龙","EFFIE","3月25日",3598,"微信小店","","newmedia-dianpu-xhs-0-0"),
    (7,"3月","sSCT22","Tom","JeanneLᴇᴇ🦄","3月4日",3598,"微信小店","","newmedia-dianpu-wxxd-0-0"),
    (8,"3月","sSCT22","Tom","薇妮姐","3月8日",599,"端内","","app-active-h5-0-0"),
    (9,"3月","sSCT22","Tom","欣","3月7日",599,"端内","","app-active-h5-0-0"),
    (10,"3月","sSCT22","Bob","Mogu","3月6日",599,"端内","","app-active-h5-0-0"),
    (11,"3月","sSCT22","Bob","L. 一颗夹星糖🌟","3月12日",3598,"小红书-晚柠","","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    (12,"3月","sSCT22","Tom","阿雅呀","3月15日",3598,"小红书-晚柠","","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    (13,"3月","sSCT22","Tom","Anna","3月4日",1999,"抖音","","newmedia-dianpu-douyin-0-0"),
    (14,"3月","sSCT22","Bob","Echo Liang","3月15日",1999,"微信小店","","sales-adp-cd-zjf-0"),
    (15,"3月","sSCT22","Tom","幼兒園高材生🍼","3月23日",3598,"微信小店","13055770067","sales-adp-cd-yy-0"),
    (16,"3月","sSCT22","Tom","Nancy","3月26日",599,"端内","","app-active-h5-0-0"),
    (17,"3月","sSCT22","Tom","依米","3月30日",3598,"微信小店","","sales-adp-cd-zjf-0"),
    (18,"3月","sSCT22","Bob","zhouyun","3月6日",1999,"小红书-学霸老王","13588706769","newmedia-daren-xhs-学霸老王讲真话-0"),
    (19,"3月","sSCT22","Tom","💗小超人棒棒哒🍭","3月29日",3598,"小红书-晚柠","18630368296","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    (20,"3月","sSCT22","Bob","鹿","3月14日",3598,"小红书-晚柠","","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    # 4月 (15)
    (21,"4月","qX7oJ6","小龙","琳溪","4月19日",3598,"微信小店","","sales-adp-bj-jxl-0"),
    (22,"4月","qX7oJ6","小龙","小丽","4月26日",599,"端内","","app-active-h5-0-0"),
    (23,"4月","qX7oJ6","小龙","子曦","4月16日",3598,"晚柠-小红书","","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    (24,"4月","qX7oJ6","小龙","Gaᴗao","4月24日",3598,"微信小店","","sales-adp-bj-jxl-0"),
    (25,"4月","XqxgjP","吴迪","ཚེ་རིང་མཚོ彩让措","2026/4/26",599,"端内","","app-active-h5-0-0"),
    (26,"4月","XqxgjP","吴迪","莉筱雅","2026/4/27",1999,"晚柠-小红书","","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    (27,"4月","XqxgjP","吴迪","爱吃巧克力","2026/4/24",1999,"微信小店","","sales-adp-bj-wd-0"),
    (28,"4月","XqxgjP","吴迪","Aa~Jessie💝","2026/4/27",1999,"抖音","","newmedia-dianpu-douyin-0-0"),
    (29,"4月","XqxgjP","吴迪","🐷","2026/4/26",3598,"宣儿妈妈-小红书","","sales-adp-cd-yy-0"),
    (30,"4月","XqxgjP","吴迪","🐷","2026/4/26",3598,"宣儿妈妈-小红书","","sales-adp-cd-yy-0"),
    (31,"4月","sSCT22","Tom","静静是我🍃","4月8日",1999,"小红书-学霸老王","15975769851","partner-actives-0-0-0"),
    (32,"4月","sSCT22","Bob","胆大鬼","4月8日",3598,"小红书-学霸老王","15262255267","newmedia-daren-xhs-学霸老王讲真话-0"),
    (33,"4月","sSCT22","Tom","希Cissy-427","4月27日",3598,"微信小店","","sales-adp-cd-zjf-0"),
    (34,"4月","sSCT22","Tom","Kerry","4月9日",3598,"微信小店","18328334683","sales-adp-cd-yy-0"),
    (35,"4月","sSCT22","Tom","SHAN_Q_Q","4月1日",3598,"小红书-官店","","newmedia-dianpu-xhs-0-0"),
    # 5月 (22)
    (36,"5月","qX7oJ6","小龙","Sia","5月9日",599,"微信小店","","sales-adp-bj-jxl-0"),
    (37,"5月","XqxgjP","吴迪","璐","2026/5/9",1999,"微信小店","","sales-adp-bj-wd-0"),
    (38,"5月","XqxgjP","吴迪","^_^","2026/5/16",3598,"晚柠-小红书","","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    (39,"5月","XqxgjP","吴迪","🌸白色铃兰🌸","2026/5/17",3598,"抖音","","newmedia-dianpu-douyin-0-0"),
    (40,"5月","XqxgjP","吴迪","咔咔","2026/5/7",3598,"微信小店","","sales-adp-bj-wd-0"),
    (41,"5月","XqxgjP","吴迪","梦马","2026/5/21",3598,"小红书","","newmedia-dianpu-xhs-0-0"),
    (42,"5月","XqxgjP","吴迪","梦马","2026/5/21",3598,"小红书","","newmedia-dianpu-xhs-0-0"),
    (43,"5月","XqxgjP","吴迪","小西瓜","2026/5/30",1999,"微信小店","","sales-adp-bj-wd-0"),
    (44,"5月","XqxgjP","吴迪","璐","2026/5/9",1999,"微信小店","","sales-adp-bj-wd-0"),
    (45,"5月","sSCT22","Bob","Yuki-515","5月15日",3598,"小红书-晚柠","","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    (46,"5月","sSCT22","Tom","冬-515","5月15日",1999,"小红书-晚柠","","newmedia-daren-xhs-晚柠也是个妈妈了-0"),
    (47,"5月","sSCT22","Bob","微笑向暖","5月4日",1999,"微信小店","","sales-adp-bj-wd-0"),
    (48,"5月","sSCT22","Bob","cici-511","5月11日",1999,"微信小店","","sales-adp-cd-xsy-0"),
    (49,"5月","sSCT22","Tom","🐷-519","5月19日",1999,"小红书-宣儿妈妈","","newmedia-daren-xhs-宣儿妈妈%"),
    (50,"5月","sSCT22","Bob","妃-516","5月16日",1999,"微信小店","","sales-adp-cd-zjf-0"),
    (51,"5月","sSCT22","Tom","黄晔-516","5月16日",3598,"微信小店","","sales-adp-bj-wd-0"),
    (52,"5月","sSCT22","Tom","朵朵呀！","5月25日",1999,"微信小店","","sales-adp-cd-zjf-0"),
    (53,"5月","sSCT22","Tom","雷鸣-414","5月14日",599,"微信小店","","sales-adp-bj-wd-0"),
    (54,"5月","sSCT22","Tom","毛阿毛🐱-520","5月20日",1999,"小红书-官店","","newmedia-dianpu-xhs-0-0"),
    (55,"5月","sSCT22","Tom","____Miss_y-519","5月19日",599,"微信小店","","sales-adp-bj-wd-0"),
    (56,"5月","sSCT22","Tom","Mandy-526","5月26日",1999,"微信小店","","sales-adp-bj-wd-0"),
    (57,"5月","sSCT22","Bob","Yoki-529","5月29日",1999,"微信小店","","sales-adp-cd-zjf-0"),
]

# Parse date
def parse_date(d):
    d = d.replace("日","").replace("月","/").replace(" ","").strip()
    if "/" in d:
        parts = d.split("/")
        if len(parts) >= 2 and parts[1]:
            m = int(parts[0])
            day = int(parts[1])
            return f"2026-{m:02d}-{day:02d}"
    return None

# Encrypt phones
from scripts.phone_encrypt import encrypt_phone
phone_map = {}
for g in gaps:
    phone = g[7]
    if phone:
        phone_map[phone] = encrypt_phone(phone)

# Look up accounts by encrypted phone
if phone_map:
    phone_conds = " OR ".join([f"a.tel_encrypt LIKE '{v}%'" for v in phone_map.values()])
    sql = f"SELECT a.id, a.tel, a.tel_encrypt FROM bi_vala_app_account a WHERE a.status=1 AND ({phone_conds})"
    r = subprocess.run(["psql","-h","bj-postgres-16pob4sg.sql.tencentcdb.com","-p","28591","-U","ai_member","-d","vala_bi","-c",sql],
        env={**os.environ,"PGPASSWORD":PGPASS}, capture_output=True, text=True, timeout=15)
    # Build account_id -> tel mapping
    acct_tel = {}
    for line in r.stdout.strip().split("\n")[2:-1]:
        parts = [p.strip() for p in line.split("|")]
        if len(parts) >= 3:
            acct_tel[parts[0]] = parts[1]
else:
    acct_tel = {}

# Now query orders by key_from + date for each gap
results = []
for g in gaps:
    idx, month, src, sales, nick, date_str, amount, channel, phone, keyfrom = g
    d = parse_date(date_str)
    amt = amount * 100
    
    found = None
    # If we have phone match
    if phone and phone in phone_map:
        enc = phone_map[phone]
        # Find account_id
        sql2 = f"SELECT a.id FROM bi_vala_app_account a WHERE a.status=1 AND a.tel_encrypt LIKE '{enc}%'"
        r2 = subprocess.run(["psql","-h","bj-postgres-16pob4sg.sql.tencentcdb.com","-p","28591","-U","ai_member","-d","vala_bi","-c",sql2],
            env={**os.environ,"PGPASSWORD":PGPASS}, capture_output=True, text=True, timeout=10)
        acct_ids = []
        for line in r2.stdout.strip().split("\n")[2:-1]:
            p = line.split("|")[0].strip()
            if p.isdigit():
                acct_ids.append(p)
        if acct_ids:
            acct_list = ",".join(acct_ids)
            sql3 = f"SELECT o.id, o.trade_no, o.pay_amount_int, to_char(o.pay_success_date,'YYYY-MM-DD'), o.key_from, o.order_status, o.account_id FROM bi_vala_order o WHERE o.account_id IN ({acct_list}) AND o.pay_amount_int={amt} AND o.order_status IN (3,4) AND o.pay_success_date IS NOT NULL ORDER BY o.pay_success_date"
            r3 = subprocess.run(["psql","-h","bj-postgres-16pob4sg.sql.tencentcdb.com","-p","28591","-U","ai_member","-d","vala_bi","-c",sql3],
                env={**os.environ,"PGPASSWORD":PGPASS}, capture_output=True, text=True, timeout=10)
            for line in r3.stdout.strip().split("\n")[2:-1]:
                parts = [p.strip() for p in line.split("|")]
                if len(parts) >= 7:
                    found = {"order_id":parts[0],"trade_no":parts[1],"amount":int(parts[2]),"pay_date":parts[3],"key_from":parts[4],"status":parts[5],"account_id":parts[6]}
                    break
    
    # If no phone match, try keyfrom + date + amount
    if not found and d and keyfrom:
        if "%" in keyfrom:
            like = keyfrom.replace("%","")
            sql4 = f"SELECT o.id, o.trade_no, o.pay_amount_int, to_char(o.pay_success_date,'YYYY-MM-DD'), o.key_from, o.order_status, o.account_id, a.tel FROM bi_vala_order o JOIN bi_vala_app_account a ON o.account_id=a.id WHERE a.status=1 AND o.pay_success_date::date='{d}' AND o.pay_amount_int={amt} AND o.key_from LIKE '%{like}%' AND o.order_status IN (3,4) LIMIT 3"
        else:
            sql4 = f"SELECT o.id, o.trade_no, o.pay_amount_int, to_char(o.pay_success_date,'YYYY-MM-DD'), o.key_from, o.order_status, o.account_id, a.tel FROM bi_vala_order o JOIN bi_vala_app_account a ON o.account_id=a.id WHERE a.status=1 AND o.pay_success_date::date='{d}' AND o.pay_amount_int={amt} AND o.key_from='{keyfrom}' AND o.order_status IN (3,4) LIMIT 3"
        r4 = subprocess.run(["psql","-h","bj-postgres-16pob4sg.sql.tencentcdb.com","-p","28591","-U","ai_member","-d","vala_bi","-c",sql4],
            env={**os.environ,"PGPASSWORD":PGPASS}, capture_output=True, text=True, timeout=10)
        matches = []
        for line in r4.stdout.strip().split("\n")[2:-1]:
            parts = [p.strip() for p in line.split("|")]
            if len(parts) >= 8:
                matches.append({"order_id":parts[0],"trade_no":parts[1],"amount":int(parts[2]),"pay_date":parts[3],"key_from":parts[4],"status":parts[5],"account_id":parts[6],"tel":parts[7]})
        if matches:
            found = matches[0]
    
    # Check refunds
    gsv = 0
    if found:
        aid = found.get("account_id","")
        sql5 = f"SELECT SUM(r.refund_amount_int) FROM bi_refund_order r JOIN bi_vala_order o ON r.trade_no=o.trade_no WHERE o.account_id={aid} AND r.status=3"
        r5 = subprocess.run(["psql","-h","bj-postgres-16pob4sg.sql.tencentcdb.com","-p","28591","-U","ai_member","-d","vala_bi","-c",sql5],
            env={**os.environ,"PGPASSWORD":PGPASS}, capture_output=True, text=True, timeout=10)
        refund = 0
        for line in r5.stdout.strip().split("\n")[2:-1]:
            val = line.strip()
            if val and val.isdigit():
                refund = int(val)
        gsv = found["amount"] - refund
    
    status = "有" if found else "无"
    is_direct = keyfrom in ("newmedia-dianpu-xhs-0-0","newmedia-dianpu-douyin-0-0","newmedia-dianpu-wxxd-0-0","app-active-h5-0-0")
    should_enter = "是" if (found and gsv > 0 and not is_direct) else "否"
    
    print(f"{idx}. [{month}] {sales} {nick} | {date_str} ¥{amount} {channel} | {status} | GSV={gsv/100:.0f} | 应进Bot={should_enter}")
    if found:
        print(f"   order_id={found['order_id']} {found.get('pay_date','')} ¥{found['amount']/100:.0f} {found.get('key_from','')[:50]} status={found.get('status','')}")
