#!/usr/bin/env python3
import argparse
import json
from collections import defaultdict

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment
from openpyxl.utils import get_column_letter

HEADER_FILL = PatternFill(start_color="305496", end_color="305496", fill_type="solid")
HEADER_FONT = Font(color="FFFFFF", bold=True)

COLUMNS = [
    ("date", "日付"),
    ("vendor", "取引先"),
    ("description", "内容"),
    ("amount_incl_tax", "税込金額"),
    ("amount_excl_tax", "税抜金額"),
    ("tax_rate", "税率"),
    ("registration_number", "登録番号"),
    ("category", "費目区分"),
    ("file_path", "元ファイル"),
    ("needs_review", "確認フラグ"),
]


def compute_excl_tax(amount_incl_tax, tax_rate):
    if amount_incl_tax is None or tax_rate is None:
        return None
    try:
        return round(amount_incl_tax / (1 + tax_rate / 100), 0)
    except ZeroDivisionError:
        return amount_incl_tax


def format_tax_rate(tax_rate):
    if tax_rate is None:
        return "不明"
    if tax_rate == 0:
        return "非課税"
    return f"{tax_rate}%"


def build_summary_sheet(ws, records):
    by_category = defaultdict(lambda: {"count": 0, "amount": 0})
    by_tax_rate = defaultdict(lambda: {"count": 0, "amount": 0})
    no_registration = 0

    for r in records:
        cat = r.get("category") or "未分類"
        amt = r.get("amount_incl_tax") or 0
        by_category[cat]["count"] += 1
        by_category[cat]["amount"] += amt

        rate_label = format_tax_rate(r.get("tax_rate"))
        by_tax_rate[rate_label]["count"] += 1
        by_tax_rate[rate_label]["amount"] += amt

        if not r.get("registration_number"):
            no_registration += 1

    row = 1
    ws.cell(row=row, column=1, value="費目区分別 小計").font = Font(bold=True)
    row += 1
    ws.cell(row=row, column=1, value="費目区分").font = HEADER_FONT
    ws.cell(row=row, column=1).fill = HEADER_FILL
    ws.cell(row=row, column=2, value="件数").font = HEADER_FONT
    ws.cell(row=row, column=2).fill = HEADER_FILL
    ws.cell(row=row, column=3, value="金額（税込）").font = HEADER_FONT
    ws.cell(row=row, column=3).fill = HEADER_FILL
    row += 1
    for cat, v in sorted(by_category.items(), key=lambda x: -x[1]["amount"]):
        ws.cell(row=row, column=1, value=cat)
        ws.cell(row=row, column=2, value=v["count"])
        ws.cell(row=row, column=3, value=v["amount"])
        row += 1

    row += 1
    ws.cell(row=row, column=1, value="税率別 小計").font = Font(bold=True)
    row += 1
    ws.cell(row=row, column=1, value="税率区分").font = HEADER_FONT
    ws.cell(row=row, column=1).fill = HEADER_FILL
    ws.cell(row=row, column=2, value="件数").font = HEADER_FONT
    ws.cell(row=row, column=2).fill = HEADER_FILL
    ws.cell(row=row, column=3, value="金額（税込）").font = HEADER_FONT
    ws.cell(row=row, column=3).fill = HEADER_FILL
    row += 1
    for rate_label, v in sorted(by_tax_rate.items(), key=lambda x: -x[1]["amount"]):
        ws.cell(row=row, column=1, value=rate_label)
        ws.cell(row=row, column=2, value=v["count"])
        ws.cell(row=row, column=3, value=v["amount"])
        row += 1

    row += 1
    ws.cell(row=row, column=1, value="登録番号なしの件数（インボイス対象外の可能性、要確認）").font = Font(bold=True)
    ws.cell(row=row, column=3, value=no_registration)

    for col in range(1, 4):
        ws.column_dimensions[get_column_letter(col)].width = 32


def build_detail_sheet(ws, records):
    for col_idx, (_, header) in enumerate(COLUMNS, start=1):
        cell = ws.cell(row=1, column=col_idx, value=header)
        cell.font = HEADER_FONT
        cell.fill = HEADER_FILL
        cell.alignment = Alignment(horizontal="center")

    for row_idx, r in enumerate(records, start=2):
        amount_incl = r.get("amount_incl_tax")
        tax_rate = r.get("tax_rate")
        amount_excl = compute_excl_tax(amount_incl, tax_rate)

        values = {
            "date": r.get("date", ""),
            "vendor": r.get("vendor", ""),
            "description": r.get("description", ""),
            "amount_incl_tax": amount_incl,
            "amount_excl_tax": amount_excl,
            "tax_rate": format_tax_rate(tax_rate),
            "registration_number": r.get("registration_number") or "なし",
            "category": r.get("category", "未分類"),
            "file_path": r.get("file_path", ""),
            "needs_review": "要確認" if r.get("needs_review") else "",
        }
        for col_idx, (key, _) in enumerate(COLUMNS, start=1):
            ws.cell(row=row_idx, column=col_idx, value=values[key])

    for col_idx, (key, _) in enumerate(COLUMNS, start=1):
        width = 14
        if key in ("description", "file_path", "vendor"):
            width = 30
        ws.column_dimensions[get_column_letter(col_idx)].width = width


def main():
    parser = argparse.ArgumentParser(description="経費データJSONから日本向けインボイス対応Excel台帳を生成する")
    parser.add_argument("--input", required=True, help="抽出済み経費データのJSONファイル")
    parser.add_argument("--output", required=True, help="出力するxlsxファイルパス")
    args = parser.parse_args()

    with open(args.input, "r", encoding="utf-8") as f:
        records = json.load(f)

    wb = Workbook()
    detail_ws = wb.active
    detail_ws.title = "経費一覧"
    build_detail_sheet(detail_ws, records)

    summary_ws = wb.create_sheet("集計")
    build_summary_sheet(summary_ws, records)

    wb.save(args.output)
    print(f"Saved: {args.output} ({len(records)} records)")


if __name__ == "__main__":
    main()