import sys
from pathlib import Path
import pandas as pd
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Alignment, Border, Side
from openpyxl.utils import get_column_letter

# Market_Wave 用：日別Excelを統合Excelへ上書き統合する道具
# 置き場所の想定：C:\Users\machi\Market_Wave\05_ツール

SCRIPT_DIR = Path(__file__).resolve().parent
BASE = SCRIPT_DIR.parent
EXCEL_DIR = BASE / "01_波Excel"
OUT_FILE = EXCEL_DIR / "all_inside_radar_summary.xlsx"

# 対象：日付付きファイルのみ。統合版 all_inside_radar_summary.xlsx は除外。
PATTERN = "all_inside_radar_summary_*.xlsx"


def autosize_excel(path: Path):
    wb = load_workbook(path)
    header_fill = PatternFill("solid", fgColor="1F4E78")
    header_font = Font(color="FFFFFF", bold=True)
    thin = Side(style="thin", color="D9E2F3")
    border = Border(left=thin, right=thin, top=thin, bottom=thin)

    for ws in wb.worksheets:
        if ws.max_row >= 1:
            ws.freeze_panes = "A2"
            ws.auto_filter.ref = ws.dimensions
            for cell in ws[1]:
                cell.fill = header_fill
                cell.font = header_font
                cell.alignment = Alignment(horizontal="center", vertical="center")
                cell.border = border
        for row in ws.iter_rows(min_row=2):
            for cell in row:
                cell.border = border
                cell.alignment = Alignment(vertical="center")
        for col in ws.columns:
            col_letter = get_column_letter(col[0].column)
            max_len = max(len(str(c.value)) if c.value is not None else 0 for c in col)
            ws.column_dimensions[col_letter].width = min(max(max_len + 2, 10), 32)
    wb.save(path)


def main():
    excel_dir = Path(sys.argv[1]) if len(sys.argv) >= 2 else EXCEL_DIR
    out_file = Path(sys.argv[2]) if len(sys.argv) >= 3 else excel_dir / "all_inside_radar_summary.xlsx"

    files = sorted(excel_dir.glob(PATTERN))
    files = [f for f in files if f.name != "all_inside_radar_summary.xlsx" and f.is_file()]

    if not files:
        print("対象の日別Excelが見つかりません")
        print(f"検索場所: {excel_dir}")
        print(f"検索形式: {PATTERN}")
        sys.exit(1)

    sheet_frames = {}

    for f in files:
        print(f"読み込み: {f.name}")
        try:
            book = pd.read_excel(f, sheet_name=None, engine="openpyxl")
        except Exception as e:
            print(f"  読み込み失敗: {f.name} / {e}")
            continue

        for sheet_name, df in book.items():
            if df.empty:
                continue
            df = df.copy()
            df["統合元ファイル"] = f.name
            sheet_frames.setdefault(sheet_name, []).append(df)

    if not sheet_frames:
        print("統合できるシートがありませんでした")
        sys.exit(1)

    with pd.ExcelWriter(out_file, engine="openpyxl") as writer:
        for sheet_name, frames in sheet_frames.items():
            merged = pd.concat(frames, ignore_index=True)

            # 同じ行が重複していたら削除。統合元ファイル列は除外して判定。
            key_cols = [c for c in merged.columns if c != "統合元ファイル"]
            if key_cols:
                merged = merged.drop_duplicates(subset=key_cols, keep="last")

            # Excelのシート名は31文字まで
            safe_sheet_name = sheet_name[:31]
            merged.to_excel(writer, index=False, sheet_name=safe_sheet_name)

    autosize_excel(out_file)

    print("保存完了")
    print(out_file)
    print(f"統合ファイル数: {len(files)}")
    for sheet_name, frames in sheet_frames.items():
        rows = sum(len(df) for df in frames)
        print(f"  {sheet_name}: {rows} 行")


if __name__ == "__main__":
    main()