# -*- coding: utf-8 -*-
"""环境盘点第二轮：列名订正后补查（票 02）"""
import sys, io
sys.stdout = io.TextIOWrapper(sys.stdout.buffer, encoding='utf-8')
import pymysql

CONF = dict(host='8.129.84.155', port=3306, user='root',
            password='Itc@123456', database='crm', charset='utf8mb4',
            autocommit=True, connect_timeout=10, cursorclass=pymysql.cursors.DictCursor)

def q(sql, args=None):
    with pymysql.connect(**CONF) as conn, conn.cursor() as cur:
        cur.execute(sql, args)
        return cur.fetchall()

def cols(t):
    return [r['Field'] for r in q(f'SHOW COLUMNS FROM {t}')]

# 1) 关键表列结构
for t in ['opportunity', 'sys_dept', 'sys_role', 'sys_user_role', 'dict_group', 'dict_item', 'customer_pending_notice']:
    print(f'== {t} 列 ==')
    print(' ', cols(t))

# 2) 用户全量
print('\n== crm_auth_user 全量 ==')
for r in q('SELECT id, username, account, dept_id, enabled, employment_status, title FROM crm_auth_user WHERE deleted=0 ORDER BY id'):
    print(f"  {r['id']} | {r['username']} | acct={r['account']} | dept={r['dept_id']} | enabled={r['enabled']} | emp={r['employment_status']} | title={r['title']}")

# 3) 部门树
print('\n== sys_dept 全量 ==')
dc = cols('sys_dept')
namecol = 'dept_name' if 'dept_name' in dc else ('name' if 'name' in dc else dc[1])
pcol = 'parent_id' if 'parent_id' in dc else dc[2]
print(f'  (namecol={namecol} parent={pcol})')
for r in q(f'SELECT id, {namecol} nm, {pcol} pid FROM sys_dept WHERE deleted=0 ORDER BY id'):
    print(f"  {r['id']} | {r['nm']} | parent={r['pid']}")

# 4) debug 用户角色
print('\n== debug 用户 739564171091247104 角色 ==')
rc = cols('sys_role')
codecol = 'role_code' if 'role_code' in rc else ('code' if 'code' in rc else rc[1])
nmcol = 'role_name' if 'role_name' in rc else ('name' if 'name' in rc else rc[2])
try:
    for r in q(f"SELECT ur.role_id, r.{codecol} cd, r.{nmcol} nm FROM sys_user_role ur JOIN sys_role r ON r.id=ur.role_id WHERE ur.user_id='739564171091247104'"):
        print(f"  role={r['role_id']} {r['cd']} {r['nm']}")
except Exception as e:
    print('  ERR:', str(e)[:120])
print('  sys_role 全量:')
for r in q(f'SELECT id, {codecol} cd, {nmcol} nm FROM sys_role WHERE deleted=0'):
    print(f"  {r['id']} | {r['cd']} | {r['nm']}")

# 5) 字典 24 组
print('\n== dict_group 24 组 ==')
gc = cols('dict_group')
print('  列:', gc)
gcode = 'group_code' if 'group_code' in gc else gc[1]
gname = 'group_name' if 'group_name' in gc else ('name' if 'name' in gc else gc[2])
rows = q(f'SELECT {gcode} gc, {gname} gn FROM dict_group WHERE deleted=0 ORDER BY {gcode}') if 'deleted' in gc else q(f'SELECT {gcode} gc, {gname} gn FROM dict_group ORDER BY {gcode}')
for r in rows:
    print(f"  {r['gc']} | {r['gn']}")
print('  dict_item 按 group 分布:')
try:
    for r in q(f"SELECT {gcode if 'group_code' in cols('dict_item') else 'group_code'} g, COUNT(*) c FROM dict_item GROUP BY g ORDER BY g"):
        print(f"  {r['g']}: {r['c']}")
except Exception as e:
    ic = cols('dict_item')
    print('  item 列:', ic, 'ERR:', str(e)[:80])

# 6) customer 存量明细（seed 前基线）
print('\n== customer 存量 5 条明细 ==')
for r in q("SELECT id, customer_no, customer_name, owner_user_id, owner_user_name_snapshot, owner_dept_id, archive_status, customer_stage, deleted FROM customer ORDER BY create_time"):
    print(f"  {r['id']} | {r['customer_no']} | {r['customer_name']} | owner={r['owner_user_id']}/{r['owner_user_name_snapshot']} dept={r['owner_dept_id']} | arch={r['archive_status']} stage={r['customer_stage']} del={r['deleted']}")

# 7) opportunity 名称列 + e2e 残留
print('\n== opportunity 存量 ==')
oc = cols('opportunity')
print('  列:', oc)
namecand = [c for c in oc if 'name' in c or 'title' in c]
print('  名称候选列:', namecand)
if namecand:
    n = namecand[0]
    print(f"  e2e-% 残留({n}):", q(f"SELECT COUNT(*) c FROM opportunity WHERE {n} LIKE 'e2e-%'")[0]['c'])
    for r in q(f'SELECT id, {n} nm FROM opportunity WHERE deleted=0 LIMIT 10'):
        print(f"  {r['id']} | {r['nm']}")
print('  opportunity_customer 明细(前10):')
for r in q('SELECT id, opportunity_id, customer_id, customer_role, delete_key, is_primary_intended FROM opportunity_customer ORDER BY id LIMIT 10'):
    print(f"  {r['id']} opp={r['opportunity_id']} cust={r['customer_id']} role={r['customer_role']} dk={r['delete_key']} prim={r['is_primary_intended']}")

# 8) 其他支撑数据
print('\n== 支撑数据 ==')
print('  sys_region:', q('SELECT COUNT(*) c FROM sys_region')[0]['c'])
print('  sys_menu:', q('SELECT COUNT(*) c FROM sys_menu')[0]['c'])
print('  sys_data_scope_module:', q('SELECT COUNT(*) c FROM sys_data_scope_module')[0]['c'])
try:
    for r in q('SELECT module_code, scope_types FROM sys_data_scope_module'):
        print(f"    {r['module_code']}: {r['scope_types']}")
except Exception as e:
    dsm = cols('sys_data_scope_module')
    print('  dsm 列:', dsm, 'ERR:', str(e)[:80])
print('  user_saved_view:', q('SELECT COUNT(*) c FROM user_saved_view')[0]['c'])
print('  user_column_preference:', q('SELECT COUNT(*) c FROM user_column_preference')[0]['c'])
print('  customer_follow:', q('SELECT COUNT(*) c FROM customer_follow')[0]['c'])
print('  customer_reminder_rule:', q('SELECT COUNT(*) c FROM customer_reminder_rule')[0]['c'])

print('\nDONE2')