"""
賞与を給料手当から分離する処理:
1. 賞与の勘定科目 (821) を追加
2. JE:833 の給料手当行 (4,720,000) を
   給料手当 420,000 ＋ 賞与 4,300,000 に分離
"""
import psycopg2

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

print("=== 変更前の確認 ===")
cur.execute("""
    SELECT jl.journal_line_id, a.account_code, a.account_name, jl.debit, jl.credit, jl.line_description
    FROM journal_lines jl
    JOIN accounts a ON jl.account_id = a.account_id
    WHERE jl.journal_entry_id = 833
    ORDER BY jl.journal_line_id
""")
rows = cur.fetchall()
for r in rows:
    print(f"  line_id:{r[0]} {r[1]} {r[2]:<15} 借:{r[3] or 0:>12,.0f} 貸:{r[4] or 0:>12,.0f} {r[5] or ''}")

print()

# Step 1: 賞与 (821) の科目を追加（存在しない場合のみ）
cur.execute("SELECT account_id FROM accounts WHERE account_code = '821'")
existing = cur.fetchone()
if existing:
    print(f"科目 821 は既に存在します (ID:{existing[0]})")
    bonus_account_id = existing[0]
else:
    cur.execute("""
        INSERT INTO accounts (account_code, account_name, account_type)
        VALUES ('821', '賞与', 'expense')
        RETURNING account_id
    """)
    bonus_account_id = cur.fetchone()[0]
    print(f"科目 821 賞与 を追加しました (ID:{bonus_account_id})")

# Step 2: 給料手当行 (line_id:2162) の金額を 4,720,000→420,000 に変更
cur.execute("""
    UPDATE journal_lines
    SET debit = 420000,
        line_description = NULL
    WHERE journal_line_id = 2162
""")
print(f"給料手当行 (line_id:2162) を 4,720,000→420,000 に更新")

# Step 3: 賞与の新しい明細行を追加
cur.execute("""
    INSERT INTO journal_lines (journal_entry_id, account_id, debit, credit, line_description)
    VALUES (833, %s, 4300000, 0, '賞与')
    RETURNING journal_line_id
""", (bonus_account_id,))
new_line_id = cur.fetchone()[0]
print(f"賞与行 (line_id:{new_line_id}) を追加: 4,300,000 円")

# Step 4: 仕訳の摘要も更新
cur.execute("""
    UPDATE journal_entries
    SET description = '張翔鶴 10月給料・賞与'
    WHERE journal_entry_id = 833
""")
print("仕訳摘要を更新: '張翔鶴 10月給料 + ボーナス' → '張翔鶴 10月給料・賞与'")

conn.commit()

print()
print("=== 変更後の確認 ===")
cur.execute("""
    SELECT jl.journal_line_id, a.account_code, a.account_name, jl.debit, jl.credit, jl.line_description
    FROM journal_lines jl
    JOIN accounts a ON jl.account_id = a.account_id
    WHERE jl.journal_entry_id = 833
    ORDER BY jl.journal_line_id
""")
rows = cur.fetchall()
debit_total = 0
credit_total = 0
for r in rows:
    d = r[3] or 0
    c = r[4] or 0
    debit_total += d
    credit_total += c
    print(f"  line_id:{r[0]} {r[1]} {r[2]:<15} 借:{d:>12,.0f} 貸:{c:>12,.0f} {r[5] or ''}")
print(f"  {'合計':<30} 借:{debit_total:>12,.0f} 貸:{credit_total:>12,.0f}")
print(f"  貸借バランス: {'✓ OK' if debit_total == credit_total else '✗ NG！'}")

print()
print("=== 期間合計（給料手当・賞与）===")
cur.execute("""
    SELECT a.account_code, a.account_name, SUM(jl.debit) AS total
    FROM journal_lines jl
    JOIN accounts a ON jl.account_id = a.account_id
    JOIN journal_entries je ON jl.journal_entry_id = je.journal_entry_id
    WHERE je.is_latest = true AND je.is_deleted = false
        AND je.entry_date >= '2025-06-01' AND je.entry_date <= '2025-12-31'
        AND a.account_code IN ('812', '813', '821')
    GROUP BY a.account_code, a.account_name
    ORDER BY a.account_code
""")
rows = cur.fetchall()
for r in rows:
    print(f"  {r[0]} {r[1]:<12}: {r[2] or 0:>12,.0f} 円")

conn.close()
print()
print("完了しました。")