import psycopg2
from psycopg2.extras import RealDictCursor

conn = psycopg2.connect('host=192.168.0.61 port=55432 dbname=njts_acct user=njts_app password=njts_app2025')
cur = conn.cursor(cursor_factory=RealDictCursor)

print("=" * 100)
print("【詳細的Entry鏈條追踪】")
print("=" * 100)

# 查詢所有test開頭的交易，按ID排序
cur.execute('''
SELECT 
  je.journal_entry_id,
  je.description,
  je.parent_entry_id,
  je.is_deleted,
  je.created_by,
  je.created_at,
  SUM(CASE WHEN jl.debit > 0 THEN jl.debit ELSE 0 END)::int as debit_total,
  SUM(CASE WHEN jl.credit > 0 THEN jl.credit ELSE 0 END)::int as credit_total
FROM journal_entries je
LEFT JOIN journal_lines jl ON je.journal_entry_id = jl.journal_entry_id
WHERE je.description ILIKE '%test%'
  AND je.journal_entry_id > 375
GROUP BY je.journal_entry_id, je.description, je.parent_entry_id, je.is_deleted, je.created_by, je.created_at
ORDER BY je.created_at
''')

rows = cur.fetchall()

print("\n【Entry按创建顺序显示】\n")
current_chain = []
for row in rows:
    status = "❌ 已삭除" if row['is_deleted'] else "✓ 活跃"
    parent = f"parent={row['parent_entry_id']}" if row['parent_entry_id'] else "parent=None"
    
    print(f"Entry#{row['journal_entry_id']}: {status} {parent}")
    print(f"  Description: {row['description']}")
    print(f"  Amount: 借{row['debit_total']:>5} | 贳{row['credit_total']:>5}")
    print()

print("=" * 100)
print("【Entry之間的关系分析】")
print("=" * 100)

# 构建Entry链
entry_map = {}
for row in rows:
    entry_map[row['journal_entry_id']] = {
        'desc': row['description'],
        'parent': row['parent_entry_id'],
        'deleted': row['is_deleted'],
        'debit': row['debit_total'],
        'credit': row['credit_total']
    }

# 追踪每个entry的链条
for entry_id in sorted(entry_map.keys()):
    chain = [entry_id]
    parent_id = entry_map[entry_id]['parent']
    
    while parent_id and parent_id in entry_map:
        chain.insert(0, parent_id)
        parent_id = entry_map[parent_id]['parent']
    
    if len(chain) > 1:  # 有链条关系
        chain_str = " → ".join([f"#{eid}" for eid in chain])
        
        print(f"\n链条: {chain_str}")
        for eid in chain:
            e = entry_map[eid]
            status = "❌" if e['deleted'] else "✓"
            print(f"  {status} Entry#{eid}: {e['desc'][:40]}")
            print(f"     借{e['debit']:>5} / 贳{e['credit']:>5}")

conn.close()