#!/usr/bin/env python3
"""Convert an Apple Numbers document to an Excel .xlsx workbook.

The default mapping is one Numbers table per Excel worksheet.  This avoids the
layout ambiguity caused by Numbers allowing several independently positioned
tables on one sheet.

Requirements:
    python -m pip install numbers-parser openpyxl

Examples:
    python numbers2xlsx.py sample.numbers
    python numbers2xlsx.py sample.numbers converted.xlsx
    python numbers2xlsx.py sample.numbers -o converted.xlsx --overwrite
    python numbers2xlsx.py sample.numbers --formulas comments
    python numbers2xlsx.py sample.numbers --formulas formulas
    python numbers2xlsx.py sample.numbers --formatted-values
"""

from __future__ import annotations

import argparse
import math
import os
import re
import sys
import tempfile
from dataclasses import dataclass, field
from datetime import date, datetime, time, timedelta, timezone
from decimal import Decimal
from pathlib import Path
from typing import Any, Iterable

try:
    from numbers_parser import Document
except ImportError as exc:  # pragma: no cover - exercised only without dependency
    raise SystemExit(
        "numbers-parser がインストールされていません。\n"
        "  python -m pip install numbers-parser openpyxl"
    ) from exc

try:
    from openpyxl import Workbook, load_workbook
    from openpyxl.cell.cell import ILLEGAL_CHARACTERS_RE
    from openpyxl.comments import Comment
    from openpyxl.styles import Alignment as XLAlignment
    from openpyxl.styles import Border as XLBorder
    from openpyxl.styles import Font, PatternFill, Side
    from openpyxl.utils import get_column_letter
except ImportError as exc:  # pragma: no cover - exercised only without dependency
    raise SystemExit(
        "openpyxl がインストールされていません。\n"
        "  python -m pip install openpyxl"
    ) from exc


INVALID_SHEET_CHARS_RE = re.compile(r"[\\/*?:\[\]]")
MAX_SHEET_TITLE = 31
MAX_CELL_TEXT = 32767


@dataclass
class ConversionStats:
    source_sheets: int = 0
    source_tables: int = 0
    output_sheets: int = 0
    cells_written: int = 0
    formulas_found: int = 0
    formulas_written: int = 0
    formula_comments: int = 0
    merged_ranges: int = 0
    styles_applied: int = 0
    hyperlinks_written: int = 0
    warnings: list[str] = field(default_factory=list)

    def warn(self, message: str, limit: int = 50) -> None:
        """Record a warning while preventing unbounded console output."""
        if len(self.warnings) < limit:
            self.warnings.append(message)
        elif len(self.warnings) == limit:
            self.warnings.append("以降の警告は省略しました。")


class SheetNameAllocator:
    """Create valid, unique Excel worksheet names."""

    def __init__(self) -> None:
        self._used: set[str] = set()

    @staticmethod
    def _clean(name: str) -> str:
        name = INVALID_SHEET_CHARS_RE.sub("_", str(name or "")).strip()
        name = name.strip("'")
        return name or "Sheet"

    def allocate(self, requested: str) -> str:
        base = self._clean(requested)[:MAX_SHEET_TITLE]
        candidate = base
        index = 2
        while candidate.casefold() in self._used:
            suffix = f"_{index}"
            candidate = f"{base[: MAX_SHEET_TITLE - len(suffix)]}{suffix}"
            index += 1
        self._used.add(candidate.casefold())
        return candidate


def rgb_to_argb(color: Any) -> str | None:
    """Convert numbers-parser RGB-like data to openpyxl ARGB."""
    if color is None:
        return None
    try:
        r, g, b = int(color.r), int(color.g), int(color.b)
    except (AttributeError, TypeError, ValueError):
        try:
            r, g, b = (int(component) for component in color[:3])
        except (TypeError, ValueError, IndexError):
            return None
    r, g, b = (min(255, max(0, component)) for component in (r, g, b))
    return f"FF{r:02X}{g:02X}{b:02X}"


def enum_name(value: Any) -> str:
    """Return a stable lowercase name for enum-like values."""
    name = getattr(value, "name", None)
    if name:
        return str(name).lower()
    return str(value).split(".")[-1].lower()


def make_font(style: Any) -> Font:
    underline = "single" if bool(getattr(style, "underline", False)) else None
    return Font(
        name=getattr(style, "font_name", None) or None,
        size=float(getattr(style, "font_size", 11.0) or 11.0),
        bold=bool(getattr(style, "bold", False)),
        italic=bool(getattr(style, "italic", False)),
        strike=bool(getattr(style, "strikethrough", False)),
        underline=underline,
        color=rgb_to_argb(getattr(style, "font_color", None)),
    )


def make_fill(style: Any) -> PatternFill:
    background = getattr(style, "bg_color", None)
    if isinstance(background, list):
        background = background[0] if background else None
    color = rgb_to_argb(background)
    if color is None:
        return PatternFill(fill_type=None)
    return PatternFill(fill_type="solid", fgColor=color, bgColor=color)


def make_alignment(style: Any) -> XLAlignment:
    source = getattr(style, "alignment", None)
    horizontal = enum_name(getattr(source, "horizontal", "auto"))
    vertical = enum_name(getattr(source, "vertical", "top"))

    horizontal_map = {
        "auto": None,
        "left": "left",
        "center": "center",
        "right": "right",
        "justify": "justify",
        "justified": "justify",
    }
    vertical_map = {
        "top": "top",
        "middle": "center",
        "center": "center",
        "bottom": "bottom",
    }
    return XLAlignment(
        horizontal=horizontal_map.get(horizontal),
        vertical=vertical_map.get(vertical, "top"),
        wrap_text=bool(getattr(style, "text_wrap", True)),
    )


def border_style(source_side: Any) -> str | None:
    if source_side is None:
        return None
    raw_style = getattr(source_side, "style", None)
    width = float(getattr(source_side, "width", 0.35) or 0.35)

    # numbers-parser currently stores solid/dashes/dots as 0/1/2.
    if raw_style == 1 or enum_name(raw_style) in {"dashes", "dashed"}:
        return "mediumDashed" if width > 1.5 else "dashed"
    if raw_style == 2 or enum_name(raw_style) in {"dots", "dotted"}:
        return "dotted"
    if width <= 0.35:
        return "hair"
    if width <= 1.0:
        return "thin"
    if width <= 2.0:
        return "medium"
    return "thick"


def make_side(source_side: Any) -> Side:
    if source_side is None:
        return Side(style=None)
    return Side(
        style=border_style(source_side),
        color=rgb_to_argb(getattr(source_side, "color", None)) or "FF000000",
    )


def make_border(source_border: Any) -> XLBorder:
    if source_border is None:
        return XLBorder()
    return XLBorder(
        left=make_side(getattr(source_border, "left", None)),
        right=make_side(getattr(source_border, "right", None)),
        top=make_side(getattr(source_border, "top", None)),
        bottom=make_side(getattr(source_border, "bottom", None)),
    )


def sanitize_text(value: str, stats: ConversionStats, location: str) -> str:
    cleaned = ILLEGAL_CHARACTERS_RE.sub("", value)
    if cleaned != value:
        stats.warn(f"{location}: Excelで使用できない制御文字を削除しました。")
    if len(cleaned) > MAX_CELL_TEXT:
        stats.warn(f"{location}: 文字列をExcelの上限 {MAX_CELL_TEXT} 文字に切り詰めました。")
        cleaned = cleaned[:MAX_CELL_TEXT]
    return cleaned


def normalize_value(value: Any, stats: ConversionStats, location: str) -> Any:
    """Convert a Numbers cell value into an openpyxl-compatible value."""
    if isinstance(value, datetime):
        if value.tzinfo is not None:
            stats.warn(f"{location}: タイムゾーン付き日時をUTCのタイムゾーンなし日時へ変換しました。")
            return value.astimezone(timezone.utc).replace(tzinfo=None)
        return value
    if value is None or isinstance(value, (bool, int, date, time, timedelta)):
        return value
    if isinstance(value, Decimal):
        return float(value)
    if isinstance(value, float):
        if math.isnan(value):
            return "NaN"
        if math.isinf(value):
            return "Infinity" if value > 0 else "-Infinity"
        return value
    if isinstance(value, str):
        return sanitize_text(value, stats, location)
    return sanitize_text(str(value), stats, location)


def normalize_formula(formula: str, stats: ConversionStats, location: str) -> str:
    formula = sanitize_text(formula.strip(), stats, location)
    return formula if formula.startswith("=") else f"={formula}"


def apply_source_style(xl_cell: Any, source_cell: Any, stats: ConversionStats) -> None:
    """Apply supported Numbers cell style and border attributes."""
    try:
        style = source_cell.style
        if style is not None:
            xl_cell.font = make_font(style)
            xl_cell.fill = make_fill(style)
            xl_cell.alignment = make_alignment(style)
        border = source_cell.border
        if border is not None:
            xl_cell.border = make_border(border)
        stats.styles_applied += 1
    except Exception as exc:  # Style conversion must not abort the data conversion.
        stats.warn(f"{xl_cell.coordinate}: 書式を一部変換できませんでした ({exc})")


def add_hyperlink(xl_cell: Any, source_cell: Any, stats: ConversionStats) -> None:
    """Preserve a simple, single hyperlink when possible."""
    try:
        hyperlinks = getattr(source_cell, "hyperlinks", None)
        if not hyperlinks or len(hyperlinks) != 1:
            return
        _text, url = hyperlinks[0]
        if not url:
            return
        xl_cell.hyperlink = str(url)
        stats.hyperlinks_written += 1
    except Exception:
        # Rich text may contain several partial-cell links; Excel cannot represent
        # those through a normal cell hyperlink without rebuilding rich text.
        return


def table_dimensions(table: Any) -> tuple[int, int]:
    rows = table.rows(values_only=False)
    return len(rows), max((len(row) for row in rows), default=0)


def convert_table(
    table: Any,
    worksheet: Any,
    stats: ConversionStats,
    *,
    formula_mode: str,
    formatted_values: bool,
    copy_styles: bool,
    copy_merges: bool,
    freeze_headers: bool,
) -> None:
    source_rows = table.rows(values_only=False)
    row_count = len(source_rows)
    col_count = max((len(row) for row in source_rows), default=0)

    for row_index, source_row in enumerate(source_rows, start=1):
        for col_index, source_cell in enumerate(source_row, start=1):
            location = f"{worksheet.title}!{get_column_letter(col_index)}{row_index}"
            xl_cell = worksheet.cell(row=row_index, column=col_index)

            is_formula = bool(getattr(source_cell, "is_formula", False))
            formula = getattr(source_cell, "formula", None) if is_formula else None
            if is_formula:
                stats.formulas_found += 1

            if formula_mode == "formulas" and formula:
                xl_cell.value = normalize_formula(formula, stats, location)
                stats.formulas_written += 1
            else:
                raw_value = (
                    getattr(source_cell, "formatted_value", None)
                    if formatted_values and getattr(source_cell, "value", None) is not None
                    else getattr(source_cell, "value", None)
                )
                xl_cell.value = normalize_value(raw_value, stats, location)
                if formula_mode == "comments" and formula:
                    xl_cell.comment = Comment(
                        f"Original Numbers formula:\n{formula}",
                        "numbers2xlsx",
                    )
                    stats.formula_comments += 1

            if copy_styles:
                apply_source_style(xl_cell, source_cell, stats)
            add_hyperlink(xl_cell, source_cell, stats)
            stats.cells_written += 1

    # Numbers and Excel both express row height in points.
    for row_index in range(row_count):
        try:
            height = table.row_height(row_index)
            if height and float(height) > 0:
                worksheet.row_dimensions[row_index + 1].height = float(height)
        except Exception as exc:
            stats.warn(f"{worksheet.title}: {row_index + 1}行目の高さを変換できませんでした ({exc})")

    # Numbers returns points; Excel column width is approximately character units.
    # 1 point ~= 1.333 pixels and a typical character is about 7 pixels.
    for col_index in range(col_count):
        try:
            width_points = float(table.col_width(col_index))
            excel_width = max(0.1, min(255.0, width_points * 1.333333 / 7.0))
            worksheet.column_dimensions[get_column_letter(col_index + 1)].width = excel_width
        except Exception as exc:
            stats.warn(
                f"{worksheet.title}: {get_column_letter(col_index + 1)}列の幅を変換できませんでした ({exc})"
            )

    if copy_merges:
        for merge_range in getattr(table, "merge_ranges", []) or []:
            try:
                worksheet.merge_cells(str(merge_range))
                stats.merged_ranges += 1
            except Exception as exc:
                stats.warn(f"{worksheet.title}: 結合範囲 {merge_range} を変換できませんでした ({exc})")

    if freeze_headers:
        header_rows = int(getattr(table, "num_header_rows", 0) or 0)
        header_cols = int(getattr(table, "num_header_cols", 0) or 0)
        if header_rows > 0 or header_cols > 0:
            worksheet.freeze_panes = worksheet.cell(
                row=min(row_count + 1, header_rows + 1),
                column=min(max(1, col_count + 1), header_cols + 1),
            )

    worksheet.sheet_view.showGridLines = True
    worksheet.page_setup.fitToWidth = 1
    worksheet.sheet_properties.pageSetUpPr.fitToPage = True
    worksheet.print_options.horizontalCentered = False


def preferred_sheet_name(sheet: Any, table: Any) -> str:
    tables = list(sheet.tables)
    if len(tables) == 1:
        return str(sheet.name)
    return f"{sheet.name} - {table.name}"


def build_workbook(
    input_path: Path,
    stats: ConversionStats,
    *,
    formula_mode: str,
    formatted_values: bool,
    copy_styles: bool,
    copy_merges: bool,
    freeze_headers: bool,
) -> Workbook:
    document = Document(input_path)
    workbook = Workbook()
    workbook.remove(workbook.active)
    workbook.properties.title = input_path.stem
    workbook.properties.subject = "Converted from Apple Numbers"
    workbook.properties.creator = "numbers2xlsx.py"

    allocator = SheetNameAllocator()
    stats.source_sheets = len(document.sheets)

    for source_sheet in document.sheets:
        tables = list(source_sheet.tables)
        if not tables:
            title = allocator.allocate(str(source_sheet.name))
            worksheet = workbook.create_sheet(title)
            worksheet["A1"] = "(Numbersシート内に表がありません)"
            stats.output_sheets += 1
            continue

        for table in tables:
            stats.source_tables += 1
            requested = preferred_sheet_name(source_sheet, table)
            title = allocator.allocate(requested)
            worksheet = workbook.create_sheet(title)
            convert_table(
                table,
                worksheet,
                stats,
                formula_mode=formula_mode,
                formatted_values=formatted_values,
                copy_styles=copy_styles,
                copy_merges=copy_merges,
                freeze_headers=freeze_headers,
            )
            stats.output_sheets += 1

    if not workbook.worksheets:
        workbook.create_sheet("Sheet")
        stats.output_sheets = 1
    return workbook


def atomic_save(workbook: Workbook, output_path: Path) -> None:
    output_path.parent.mkdir(parents=True, exist_ok=True)
    fd, temporary_name = tempfile.mkstemp(
        prefix=f".{output_path.stem}.", suffix=".xlsx", dir=output_path.parent
    )
    os.close(fd)
    temporary_path = Path(temporary_name)
    try:
        workbook.save(temporary_path)
        # Re-open once so malformed output is detected before replacing the target.
        check = load_workbook(temporary_path, read_only=True, data_only=False)
        check.close()
        os.replace(temporary_path, output_path)
    finally:
        temporary_path.unlink(missing_ok=True)


def resolve_output(input_path: Path, positional: Path | None, option: Path | None) -> Path:
    if positional is not None and option is not None:
        raise ValueError("出力先は第2引数または -o/--output のどちらか一方で指定してください。")
    output = option or positional or input_path.with_suffix(".xlsx")
    if output.suffix.lower() != ".xlsx":
        output = output.with_suffix(".xlsx")
    return output


def parse_args(argv: Iterable[str] | None = None) -> argparse.Namespace:
    parser = argparse.ArgumentParser(
        description="Apple Numbers (.numbers) の各表をExcel (.xlsx)へ変換します。",
        formatter_class=argparse.RawDescriptionHelpFormatter,
        epilog=(
            "既定では、Numbersの1表をExcelの1ワークシートに変換します。\n"
            "数式はNumbersに保存された計算結果を使用します。"
        ),
    )
    parser.add_argument("input", type=Path, help="入力 .numbers ファイル")
    parser.add_argument("output_positional", nargs="?", type=Path, help="出力 .xlsx ファイル")
    parser.add_argument("-o", "--output", type=Path, help="出力 .xlsx ファイル")
    parser.add_argument(
        "--formulas",
        choices=("values", "comments", "formulas"),
        default="values",
        help=(
            "数式セルの扱い: values=計算結果のみ (既定), "
            "comments=計算結果+コメントに元数式, formulas=数式をExcelへ書く"
        ),
    )
    parser.add_argument(
        "--formatted-values",
        action="store_true",
        help="数値や日時をNumbersの表示文字列として書き出す（データ型は文字列になる）",
    )
    parser.add_argument("--no-styles", action="store_true", help="セルの基本書式を変換しない")
    parser.add_argument("--no-merges", action="store_true", help="結合セルを変換しない")
    parser.add_argument("--no-freeze", action="store_true", help="ヘッダー行・列を固定しない")
    parser.add_argument("--overwrite", action="store_true", help="既存の出力ファイルを上書きする")
    return parser.parse_args(argv)


def main(argv: Iterable[str] | None = None) -> int:
    args = parse_args(argv)
    input_path: Path = args.input.expanduser().resolve()

    if not input_path.exists():
        print(f"Error: 入力ファイルが見つかりません: {input_path}", file=sys.stderr)
        return 2
    if input_path.suffix.lower() != ".numbers":
        print(f"Warning: 拡張子が .numbers ではありません: {input_path}", file=sys.stderr)

    try:
        output_path = resolve_output(input_path, args.output_positional, args.output)
        output_path = output_path.expanduser().resolve()
    except ValueError as exc:
        print(f"Error: {exc}", file=sys.stderr)
        return 2

    if input_path == output_path:
        print("Error: 入力と出力に同じパスは指定できません。", file=sys.stderr)
        return 2
    if output_path.exists() and not args.overwrite:
        print(
            f"Error: 出力ファイルは既に存在します: {output_path}\n"
            "上書きする場合は --overwrite を指定してください。",
            file=sys.stderr,
        )
        return 2
    if args.formatted_values and args.formulas == "formulas":
        print(
            "Error: --formatted-values と --formulas formulas は同時に指定できません。",
            file=sys.stderr,
        )
        return 2

    stats = ConversionStats()
    try:
        workbook = build_workbook(
            input_path,
            stats,
            formula_mode=args.formulas,
            formatted_values=args.formatted_values,
            copy_styles=not args.no_styles,
            copy_merges=not args.no_merges,
            freeze_headers=not args.no_freeze,
        )
        atomic_save(workbook, output_path)
    except Exception as exc:
        print(f"Error: 変換に失敗しました: {exc}", file=sys.stderr)
        return 1

    print(f"Converted: {input_path}")
    print(f"Output   : {output_path}")
    print(
        "Summary  : "
        f"{stats.source_sheets} source sheet(s), "
        f"{stats.source_tables} table(s), "
        f"{stats.output_sheets} Excel worksheet(s), "
        f"{stats.cells_written} cell(s)"
    )
    if stats.formulas_found:
        print(
            "Formulas : "
            f"found={stats.formulas_found}, "
            f"written={stats.formulas_written}, "
            f"comments={stats.formula_comments}"
        )
    if stats.merged_ranges:
        print(f"Merges   : {stats.merged_ranges}")
    if stats.warnings:
        print("Warnings :", file=sys.stderr)
        for warning in stats.warnings:
            print(f"  - {warning}", file=sys.stderr)

    return 0


if __name__ == "__main__":
    raise SystemExit(main())