import os, json
from zipfile import ZipFile
from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter
from openpyxl.worksheet.table import Table, TableStyleInfo

finance_dir = '/Users/qianliyun/Documents/aiwork/finance'
os.makedirs(finance_dir, exist_ok=True)
path = os.path.join(finance_dir, '云汉寻真_公司个人账隔离台账.xlsx')
today = '2026-05-15'
now = '2026-05-15 15:30:41 CST'
headers = ['记录ID','日期','入账/发生时间','账户归属','收支方向','交易类型','对方/主体','收入金额','支出金额','关联公司','用途/项目','发票状态','凭证状态','混同风险标记','后续核销/处理计划','备注','记录时间']
row = ['YXHZ-20260515-001', today, '今天/待补充银行流水时间', '个人账户收到公司相关款项', '收入', '报销款收款', '财务/云汉寻真公司', 8700, '', '云汉寻真', '样品采购；产品开发；采购等公司相关费用', '待给云汉寻真开足票', '待补充银行流水、发票、采购凭证', '是', '未来围绕样品采购、产品开发、采购等真实支出，逐笔留存凭证并给云汉寻真开票/报销；每笔支出单独登记并与本次8700元报销款核销，避免与个人消费混列。', '用户原话：今天财务给我打了8700的报销款，是云汉寻真公司的。未来我要给云汉寻真开足票，用于样品的采购，去慢慢的报销；还有产品的开发、采购这种，慢慢去报销。', now]

if os.path.exists(path):
    wb = load_workbook(path)
    ws = wb['流水台账'] if '流水台账' in wb.sheetnames else wb.active
    if ws.max_row < 1 or ws.cell(1,1).value != '记录ID':
        ws.insert_rows(1)
        for c, h in enumerate(headers, 1):
            ws.cell(1, c, h)
    existing_ids = [ws.cell(r, 1).value for r in range(2, ws.max_row + 1)]
    if row[0] not in existing_ids:
        ws.append(row)
else:
    wb = Workbook()
    ws = wb.active
    ws.title = '流水台账'
    ws.append(headers)
    ws.append(row)
    sm = wb.create_sheet('总览与提醒')
    summary_rows = [
        ['项目','内容'],
        ['台账用途','记录公司相关资金进入/流出个人账户的情况，辅助区分公司账与个人账，降低混同风险。'],
        ['当前已记录收入',8700],
        ['关联公司','云汉寻真'],
        ['待处理','给云汉寻真开足票；按样品采购、产品开发、采购等真实支出逐步报销/核销。'],
        ['建议','后续每笔公司相关支出都单独记录：日期、金额、付款账户、供应商、用途、发票号码/凭证链接，并标记与哪笔预收/报销款核销。'],
        ['非专业提示','本表为记账辅助，不替代会计或税务意见；开票和报销口径建议最终以财务/税务顾问意见为准。'],
    ]
    for r in summary_rows:
        sm.append(r)

# Rebuild/refresh table if possible.
ws = wb['流水台账']
try:
    ws._tables.clear()
except Exception:
    pass
try:
    tab = Table(displayName='流水台账表', ref=f'A1:Q{ws.max_row}')
    tab.tableStyleInfo = TableStyleInfo(name='TableStyleMedium2', showFirstColumn=False, showLastColumn=False, showRowStripes=True, showColumnStripes=False)
    ws.add_table(tab)
except Exception:
    ws.auto_filter.ref = f'A1:Q{ws.max_row}'

for sheet in wb.worksheets:
    sheet.freeze_panes = 'A2'
    for cell in sheet[1]:
        cell.font = Font(bold=True, color='FFFFFF')
        cell.fill = PatternFill('solid', fgColor='1F4E78')
        cell.alignment = Alignment(horizontal='center', vertical='center')
    for row_cells in sheet.iter_rows():
        for cell in row_cells:
            cell.alignment = Alignment(vertical='top', wrap_text=True)
    for col in range(1, sheet.max_column + 1):
        max_len = 0
        for cell in sheet[get_column_letter(col)]:
            val = '' if cell.value is None else str(cell.value)
            max_len = max(max_len, min(len(val), 60))
        sheet.column_dimensions[get_column_letter(col)].width = max(12, min(max_len + 2, 45))

for r in range(2, ws.max_row + 1):
    ws.cell(r, 8).number_format = '¥#,##0.00'
    ws.cell(r, 9).number_format = '¥#,##0.00'

if '总览与提醒' in wb.sheetnames:
    sm = wb['总览与提醒']
    for r in range(1, sm.max_row + 1):
        sm.cell(r, 1).font = Font(bold=True)
    if sm.max_row >= 3:
        sm.cell(3, 2).number_format = '¥#,##0.00'

wb.save(path)
with ZipFile(path) as z:
    bad = z.testzip()
    if bad:
        raise RuntimeError(f'bad zip member: {bad}')
wb2 = load_workbook(path, data_only=True)
ws2 = wb2['流水台账']
headers2 = [c.value for c in ws2[1]]
idx_income = headers2.index('收入金额') + 1
idx_company = headers2.index('关联公司') + 1
total = 0
rows = 0
for r in range(2, ws2.max_row + 1):
    if ws2.cell(r, idx_company).value == '云汉寻真':
        rows += 1
        total += float(ws2.cell(r, idx_income).value or 0)
print(json.dumps({'path': path, 'sheets': wb2.sheetnames, 'ledger_rows': ws2.max_row - 1, 'yunhan_rows': rows, 'yunhan_income_total': total}, ensure_ascii=False, indent=2))