from __future__ import annotations

from datetime import datetime, time
from decimal import Decimal
from pathlib import Path

import xlsxwriter

from .models import FundPortfolioReport, Severity, ValidationResult


NAVY = "#11233F"
BLUE = "#246BFE"
PALE_BLUE = "#EAF1FF"
TEAL = "#0F9D8A"
PALE_TEAL = "#E5F7F3"
AMBER = "#D98E04"
PALE_AMBER = "#FFF4D6"
RED = "#C83C3C"
PALE_RED = "#FDECEC"
SLATE = "#526173"
LIGHT = "#F5F7FA"
WHITE = "#FFFFFF"
BORDER = "#D9E0E8"


def _number(value: Decimal) -> float:
    return float(value)


def _as_datetime(value):
    return datetime.combine(value, time.min)


def _formats(workbook: xlsxwriter.Workbook) -> dict[str, xlsxwriter.format.Format]:
    return {
        "title": workbook.add_format(
            {
                "bold": True,
                "font_size": 18,
                "font_color": WHITE,
                "bg_color": NAVY,
                "align": "left",
                "valign": "vcenter",
            }
        ),
        "subtitle": workbook.add_format(
            {
                "font_size": 10,
                "font_color": SLATE,
                "bg_color": LIGHT,
                "align": "left",
                "valign": "vcenter",
            }
        ),
        "section": workbook.add_format(
            {
                "bold": True,
                "font_size": 11,
                "font_color": WHITE,
                "bg_color": BLUE,
                "align": "left",
                "valign": "vcenter",
            }
        ),
        "table_header": workbook.add_format(
            {
                "bold": True,
                "font_color": WHITE,
                "bg_color": NAVY,
                "border": 1,
                "border_color": NAVY,
                "align": "center",
                "valign": "vcenter",
                "text_wrap": True,
            }
        ),
        "label": workbook.add_format(
            {"bold": True, "font_color": NAVY, "bg_color": LIGHT}
        ),
        "text": workbook.add_format({"font_color": NAVY}),
        "text_border": workbook.add_format(
            {"font_color": NAVY, "border": 1, "border_color": BORDER}
        ),
        "wrapped_text": workbook.add_format(
            {
                "font_color": NAVY,
                "border": 1,
                "border_color": BORDER,
                "text_wrap": True,
                "valign": "top",
            }
        ),
        "integer": workbook.add_format(
            {"num_format": "#,##0", "border": 1, "border_color": BORDER}
        ),
        "decimal": workbook.add_format(
            {"num_format": "#,##0.00", "border": 1, "border_color": BORDER}
        ),
        "price": workbook.add_format(
            {"num_format": "#,##0.000000", "border": 1, "border_color": BORDER}
        ),
        "money": workbook.add_format(
            {
                "num_format": '#,##0.00;[Red]-#,##0.00',
                "border": 1,
                "border_color": BORDER,
            }
        ),
        "percent": workbook.add_format(
            {
                "num_format": "0.00%;[Red]-0.00%",
                "border": 1,
                "border_color": BORDER,
            }
        ),
        "date": workbook.add_format(
            {"num_format": "dd/mm/yyyy", "border": 1, "border_color": BORDER}
        ),
        "total_label": workbook.add_format(
            {
                "bold": True,
                "font_color": WHITE,
                "bg_color": NAVY,
                "border": 1,
                "border_color": NAVY,
            }
        ),
        "total_number": workbook.add_format(
            {
                "bold": True,
                "font_color": WHITE,
                "bg_color": NAVY,
                "num_format": "#,##0.00",
                "border": 1,
                "border_color": NAVY,
            }
        ),
        "total_percent": workbook.add_format(
            {
                "bold": True,
                "font_color": WHITE,
                "bg_color": NAVY,
                "num_format": "0.00%",
                "border": 1,
                "border_color": NAVY,
            }
        ),
        "declared_label": workbook.add_format(
            {
                "bold": True,
                "font_color": NAVY,
                "bg_color": PALE_BLUE,
                "border": 1,
                "border_color": BORDER,
            }
        ),
        "declared_number": workbook.add_format(
            {
                "bold": True,
                "font_color": NAVY,
                "bg_color": PALE_BLUE,
                "num_format": "#,##0.00",
                "border": 1,
                "border_color": BORDER,
            }
        ),
        "declared_percent": workbook.add_format(
            {
                "bold": True,
                "font_color": NAVY,
                "bg_color": PALE_BLUE,
                "num_format": "0.00%",
                "border": 1,
                "border_color": BORDER,
            }
        ),
        "status_ok": workbook.add_format(
            {"bold": True, "font_color": TEAL, "bg_color": PALE_TEAL}
        ),
        "status_warning": workbook.add_format(
            {"bold": True, "font_color": AMBER, "bg_color": PALE_AMBER}
        ),
        "status_error": workbook.add_format(
            {"bold": True, "font_color": RED, "bg_color": PALE_RED}
        ),
        "small": workbook.add_format({"font_size": 8, "font_color": SLATE}),
        "hash": workbook.add_format(
            {"font_name": "Consolas", "font_size": 8, "font_color": SLATE}
        ),
    }


def _status_format(formats, validation: ValidationResult):
    if validation.error_count:
        return formats["status_error"]
    if validation.warning_count:
        return formats["status_warning"]
    return formats["status_ok"]


def _add_title(
    sheet,
    title: str,
    subtitle: str,
    last_column: int,
    formats,
) -> None:
    sheet.merge_range(0, 0, 0, last_column, title, formats["title"])
    sheet.merge_range(1, 0, 1, last_column, subtitle, formats["subtitle"])
    sheet.set_row(0, 30)
    sheet.set_row(1, 22)
    sheet.hide_gridlines(2)


def _configure_print(sheet, *, repeat_header_row: int | None = None) -> None:
    sheet.set_landscape()
    sheet.set_paper(9)
    sheet.fit_to_pages(1, 0)
    sheet.center_horizontally()
    sheet.set_margins(left=0.25, right=0.25, top=0.45, bottom=0.45)
    sheet.set_header("&LFinReport Agent&C&F&R&P / &N", {"margin": 0.2})
    sheet.set_footer(
        "&LKaynak belgeye göre otomatik oluşturulmuştur.&R&D &T",
        {"margin": 0.2},
    )
    if repeat_header_row is not None:
        sheet.repeat_rows(repeat_header_row, repeat_header_row)


def _write_summary(workbook, report, validation, formats) -> None:
    sheet = workbook.add_worksheet("Özet")
    _add_title(
        sheet,
        "FinReport Agent · Doğrulanmış Portföy Özeti",
        f"{report.metadata.fund_name} · {report.metadata.period}",
        5,
        formats,
    )
    sheet.set_column("A:A", 25)
    sheet.set_column("B:B", 24)
    sheet.set_column("C:E", 20)
    sheet.set_column("F:F", 15)
    sheet.freeze_panes(8, 0)

    sheet.write("A3", "Doğrulama durumu", formats["label"])
    sheet.write("B3", validation.status, _status_format(formats, validation))
    sheet.write("A4", "Kaynak dosya", formats["label"])
    sheet.write("B4", report.metadata.source_filename, formats["text"])
    sheet.write("D3", "Fon kodu", formats["label"])
    sheet.write("E3", report.metadata.fund_code or "-", formats["text"])
    sheet.write("D4", "Dönem", formats["label"])
    sheet.write("E4", report.metadata.period, formats["text"])
    sheet.write("A6", "Portföy uzlaştırması", formats["section"])
    sheet.merge_range("B6:F6", "", formats["section"])

    headers = [
        "Kalem",
        "Muhasebesel Sınıf",
        "PDF Beyanı (TL)",
        "Detay Hesabı (TL)",
        "Fark (TL)",
        "FPD (%)",
    ]
    for column, header in enumerate(headers):
        sheet.write(7, column, header)

    totals = report.declared_totals
    equity_calc = sum(row.total_value for row in report.equities)
    repo_calc = sum(row.total_value for row in report.repos)
    collateral_calc = sum(row.total_value for row in report.cash_collaterals)
    rows = [
        (
            "Hisse Senetleri",
            "Portföye dahil",
            totals.equity_value,
            equity_calc,
            totals.equity_fpd_pct,
        ),
        (
            "T. Repo",
            "Portföye dahil",
            totals.repo_value,
            repo_calc,
            totals.repo_fpd_pct,
        ),
        (
            "VİOP Nakit Teminatı",
            "Portföye dahil",
            totals.cash_collateral_value,
            collateral_calc,
            totals.cash_collateral_fpd_pct,
        ),
        (
            "FON PORTFÖY DEĞERİ",
            "Uzlaştırılmış toplam",
            totals.fund_portfolio_value,
            equity_calc + repo_calc + collateral_calc,
            Decimal("1"),
        ),
        (
            "Futures Pozisyon Değeri",
            "Bilgi / maruziyet",
            totals.derivative_exposure,
            sum(row.total_value for row in report.derivatives),
            Decimal("0"),
        ),
    ]
    for row_index, (name, classification, declared, calculated, percentage) in enumerate(
        rows, start=8
    ):
        sheet.write(row_index, 0, name, formats["text_border"])
        sheet.write(row_index, 1, classification, formats["text_border"])
        sheet.write_number(row_index, 2, _number(declared), formats["money"])
        sheet.write_number(row_index, 3, _number(calculated), formats["money"])
        sheet.write_formula(
            row_index,
            4,
            f"=D{row_index + 1}-C{row_index + 1}",
            formats["money"],
            _number(calculated - declared),
        )
        sheet.write_number(row_index, 5, _number(percentage), formats["percent"])

    sheet.add_table(
        7,
        0,
        12,
        5,
        {
            "name": "PortfolioSummary",
            "style": "Table Style Medium 2",
            "columns": [
                {"header": header, "header_format": formats["table_header"]}
                for header in headers
            ],
        },
    )
    sheet.conditional_format(
        "E9:E13",
        {
            "type": "cell",
            "criteria": "not between",
            "minimum": -0.01,
            "maximum": 0.01,
            "format": formats["status_error"],
        },
    )
    sheet.write("A15", "Not", formats["label"])
    sheet.merge_range(
        "B15:F15",
        (
            "Futures pozisyon değeri maruziyettir; fon portföy değeri toplamına "
            "eklenmez. VİOP nakit teminatı portföy uzlaştırmasına dahildir."
        ),
        formats["subtitle"],
    )
    sheet.set_row(7, 28)
    _configure_print(sheet)


def _write_equities(workbook, report, formats) -> None:
    sheet = workbook.add_worksheet("Hisse Senetleri")
    _add_title(
        sheet,
        "Hisse Senetleri",
        f"{len(report.equities)} pozisyon · Kaynak koordinatları korunmuştur",
        13,
        formats,
    )
    headers = [
        "Kod",
        "İhraççı Kurum",
        "Döviz",
        "ISIN",
        "Nominal Değer",
        "Birim Alış Fiyatı",
        "Satın Alış Tarihi",
        "Borsa Sözleşme No",
        "Güncel Birim Fiyat",
        "Toplam Değer (TL)",
        "Grup (%)",
        "FPD (%)",
        "FTD (%)",
        "Kaynak Sayfa",
    ]
    header_row = 3
    for column, header in enumerate(headers):
        sheet.write(header_row, column, header)

    for row_index, row in enumerate(report.equities, start=header_row + 1):
        sheet.write(row_index, 0, row.code, formats["text_border"])
        issuer_format = (
            formats["wrapped_text"] if len(row.issuer) > 42 else formats["text_border"]
        )
        sheet.write(row_index, 1, row.issuer, issuer_format)
        if len(row.issuer) > 42:
            sheet.set_row(row_index, 30)
        sheet.write(row_index, 2, row.currency, formats["text_border"])
        sheet.write(row_index, 3, row.isin, formats["text_border"])
        sheet.write_number(row_index, 4, _number(row.nominal), formats["decimal"])
        sheet.write_number(row_index, 5, _number(row.purchase_price), formats["price"])
        sheet.write_datetime(
            row_index, 6, _as_datetime(row.purchase_date), formats["date"]
        )
        sheet.write(row_index, 7, row.contract_no or "", formats["text_border"])
        sheet.write_number(row_index, 8, _number(row.current_price), formats["price"])
        sheet.write_number(row_index, 9, _number(row.total_value), formats["money"])
        sheet.write_number(row_index, 10, _number(row.group_pct), formats["percent"])
        sheet.write_number(row_index, 11, _number(row.fpd_pct), formats["percent"])
        sheet.write_number(row_index, 12, _number(row.ftd_pct), formats["percent"])
        sheet.write_number(row_index, 13, row.source.page, formats["integer"])

    last_data_row = header_row + len(report.equities)
    sheet.add_table(
        header_row,
        0,
        last_data_row,
        len(headers) - 1,
        {
            "name": "EquityPositions",
            "style": "Table Style Medium 2",
            "columns": [
                {"header": header, "header_format": formats["table_header"]}
                for header in headers
            ],
        },
    )
    calculated_row = last_data_row + 1
    declared_row = last_data_row + 2
    sheet.write(calculated_row, 0, "HESAPLANAN TOPLAM", formats["total_label"])
    sheet.write_formula(
        calculated_row,
        4,
        f"=SUM(E{header_row + 2}:E{last_data_row + 1})",
        formats["total_number"],
        _number(sum(row.nominal for row in report.equities)),
    )
    sheet.write_formula(
        calculated_row,
        9,
        f"=SUM(J{header_row + 2}:J{last_data_row + 1})",
        formats["total_number"],
        _number(sum(row.total_value for row in report.equities)),
    )
    for column, attr in ((10, "group_pct"), (11, "fpd_pct"), (12, "ftd_pct")):
        sheet.write_formula(
            calculated_row,
            column,
            f"=SUM({xlsxwriter.utility.xl_col_to_name(column)}"
            f"{header_row + 2}:{xlsxwriter.utility.xl_col_to_name(column)}"
            f"{last_data_row + 1})",
            formats["total_percent"],
            _number(sum(getattr(row, attr) for row in report.equities)),
        )
    for column in range(1, len(headers)):
        if column not in {4, 9, 10, 11, 12}:
            sheet.write_blank(calculated_row, column, None, formats["total_label"])

    totals = report.declared_totals
    sheet.write(declared_row, 0, "PDF BEYAN TOPLAMI", formats["declared_label"])
    sheet.write_number(
        declared_row, 4, _number(totals.equity_nominal), formats["declared_number"]
    )
    sheet.write_number(
        declared_row, 9, _number(totals.equity_value), formats["declared_number"]
    )
    sheet.write_number(declared_row, 10, 1, formats["declared_percent"])
    sheet.write_number(
        declared_row, 11, _number(totals.equity_fpd_pct), formats["declared_percent"]
    )
    sheet.write_number(
        declared_row, 12, _number(totals.equity_ftd_pct), formats["declared_percent"]
    )
    for column in range(1, len(headers)):
        if column not in {4, 9, 10, 11, 12}:
            sheet.write_blank(declared_row, column, None, formats["declared_label"])

    sheet.freeze_panes(header_row + 1, 2)
    sheet.set_column("A:A", 12)
    sheet.set_column("B:B", 38)
    sheet.set_column("C:C", 9)
    sheet.set_column("D:D", 17)
    sheet.set_column("E:F", 17)
    sheet.set_column("G:G", 15)
    sheet.set_column("H:H", 20)
    sheet.set_column("I:J", 18)
    sheet.set_column("K:M", 12)
    sheet.set_column("N:N", 13)
    sheet.set_row(header_row, 34)
    _configure_print(sheet, repeat_header_row=header_row)


def _write_repos(workbook, report, formats) -> None:
    sheet = workbook.add_worksheet("T. Repo")
    _add_title(
        sheet,
        "Ters Repo Pozisyonları",
        f"{len(report.repos)} pozisyon",
        19,
        formats,
    )
    headers = [
        "Kod",
        "Döviz",
        "İhraççı",
        "Vade Tarihi",
        "Kalan Gün",
        "ISIN",
        "İç İskonto (%)",
        "Faiz Ödeme Sayısı",
        "Nominal Değer",
        "Birim Alış Fiyatı",
        "Satın Alış Tarihi",
        "Repo Teminatı",
        "Borsa Sözleşme No",
        "Net Dönüş",
        "Günlük Br Değer",
        "Toplam Değer (TL)",
        "Grup (%)",
        "FPD (%)",
        "FTD (%)",
        "Kaynak Sayfa",
    ]
    header_row = 3
    for column, header in enumerate(headers):
        sheet.write(header_row, column, header)
    for row_index, row in enumerate(report.repos, start=header_row + 1):
        values = [row.code, row.currency, row.issuer]
        for column, value in enumerate(values):
            sheet.write(row_index, column, value, formats["text_border"])
        sheet.write_datetime(
            row_index, 3, _as_datetime(row.maturity_date), formats["date"]
        )
        sheet.write_number(row_index, 4, row.days_to_maturity, formats["integer"])
        sheet.write(row_index, 5, row.isin, formats["text_border"])
        sheet.write_number(
            row_index, 6, _number(row.discount_rate / 100), formats["percent"]
        )
        sheet.write_number(
            row_index, 7, row.interest_payment_count, formats["integer"]
        )
        sheet.write_number(row_index, 8, _number(row.nominal), formats["money"])
        sheet.write_number(row_index, 9, _number(row.purchase_price), formats["price"])
        sheet.write_datetime(
            row_index, 10, _as_datetime(row.purchase_date), formats["date"]
        )
        sheet.write_number(
            row_index, 11, _number(row.collateral_amount), formats["money"]
        )
        sheet.write(row_index, 12, row.contract_no, formats["text_border"])
        sheet.write_number(row_index, 13, _number(row.net_return), formats["money"])
        sheet.write_number(row_index, 14, _number(row.daily_value), formats["price"])
        sheet.write_number(row_index, 15, _number(row.total_value), formats["money"])
        sheet.write_number(row_index, 16, _number(row.group_pct), formats["percent"])
        sheet.write_number(row_index, 17, _number(row.fpd_pct), formats["percent"])
        sheet.write_number(row_index, 18, _number(row.ftd_pct), formats["percent"])
        sheet.write_number(row_index, 19, row.source.page, formats["integer"])

    last_data_row = header_row + len(report.repos)
    sheet.add_table(
        header_row,
        0,
        last_data_row,
        len(headers) - 1,
        {
            "name": "RepoPositions",
            "style": "Table Style Medium 2",
            "columns": [
                {"header": header, "header_format": formats["table_header"]}
                for header in headers
            ],
        },
    )
    total_row = last_data_row + 1
    sheet.write(total_row, 0, "HESAPLANAN TOPLAM", formats["total_label"])
    sheet.write_formula(
        total_row,
        15,
        f"=SUM(P{header_row + 2}:P{last_data_row + 1})",
        formats["total_number"],
        _number(sum(row.total_value for row in report.repos)),
    )
    for column, attr in ((16, "group_pct"), (17, "fpd_pct"), (18, "ftd_pct")):
        letter = xlsxwriter.utility.xl_col_to_name(column)
        sheet.write_formula(
            total_row,
            column,
            f"=SUM({letter}{header_row + 2}:{letter}{last_data_row + 1})",
            formats["total_percent"],
            _number(sum(getattr(row, attr) for row in report.repos)),
        )
    for column in range(1, len(headers)):
        if column not in {15, 16, 17, 18}:
            sheet.write_blank(total_row, column, None, formats["total_label"])
    sheet.freeze_panes(header_row + 1, 2)
    sheet.set_column(0, 19, 15)
    sheet.set_column("C:C", 18)
    sheet.set_column("F:F", 17)
    sheet.set_column("M:M", 20)
    sheet.set_row(header_row, 34)
    _configure_print(sheet, repeat_header_row=header_row)


def _write_derivatives(workbook, report, formats) -> None:
    sheet = workbook.add_worksheet("Türev")
    _add_title(
        sheet,
        "Türev Pozisyonlar ve Nakit Teminatlar",
        "Maruziyet ile portföye dahil nakit teminatı ayrı sunulur",
        13,
        formats,
    )
    headers = [
        "Tür",
        "Kod",
        "Döviz",
        "Vade Tarihi",
        "Nominal",
        "Birim Alış Fiyatı",
        "Satın Alış Tarihi",
        "Borsa Sözleşme No",
        "Güncel Fiyat",
        "Pozisyon Değeri (TL)",
        "Grup (%)",
        "FPD (%)",
        "FTD (%)",
        "Kaynak Sayfa",
    ]
    header_row = 3
    for column, header in enumerate(headers):
        sheet.write(header_row, column, header)
    for row_index, row in enumerate(report.derivatives, start=header_row + 1):
        sheet.write(row_index, 0, row.position_type, formats["text_border"])
        sheet.write(row_index, 1, row.code, formats["text_border"])
        sheet.write(row_index, 2, row.currency, formats["text_border"])
        if row.maturity_date:
            sheet.write_datetime(
                row_index, 3, _as_datetime(row.maturity_date), formats["date"]
            )
        else:
            sheet.write(row_index, 3, "-", formats["text_border"])
        sheet.write_number(row_index, 4, _number(row.nominal), formats["decimal"])
        sheet.write_number(row_index, 5, _number(row.purchase_price), formats["price"])
        sheet.write_datetime(
            row_index, 6, _as_datetime(row.purchase_date), formats["date"]
        )
        sheet.write(row_index, 7, row.contract_no or "", formats["text_border"])
        sheet.write_number(row_index, 8, _number(row.current_price), formats["price"])
        sheet.write_number(row_index, 9, _number(row.total_value), formats["money"])
        sheet.write_number(row_index, 10, _number(row.group_pct), formats["percent"])
        sheet.write_number(row_index, 11, _number(row.fpd_pct), formats["percent"])
        sheet.write_number(row_index, 12, _number(row.ftd_pct), formats["percent"])
        sheet.write_number(row_index, 13, row.source.page, formats["integer"])

    last_derivative_row = header_row + len(report.derivatives)
    sheet.add_table(
        header_row,
        0,
        last_derivative_row,
        len(headers) - 1,
        {
            "name": "DerivativePositions",
            "style": "Table Style Medium 2",
            "columns": [
                {"header": header, "header_format": formats["table_header"]}
                for header in headers
            ],
        },
    )
    cash_header = last_derivative_row + 3
    sheet.merge_range(
        cash_header - 1,
        0,
        cash_header - 1,
        5,
        "Portföye Dahil Nakit Teminatlar",
        formats["section"],
    )
    cash_headers = [
        "Kalem",
        "Döviz",
        "Nominal",
        "Toplam Değer (TL)",
        "Grup (%)",
        "FPD (%)",
        "Kaynak Sayfa",
    ]
    for column, header in enumerate(cash_headers):
        sheet.write(cash_header, column, header)
    for row_index, row in enumerate(report.cash_collaterals, start=cash_header + 1):
        sheet.write(row_index, 0, row.name, formats["text_border"])
        sheet.write(row_index, 1, row.currency, formats["text_border"])
        sheet.write_number(row_index, 2, _number(row.nominal), formats["money"])
        sheet.write_number(row_index, 3, _number(row.total_value), formats["money"])
        sheet.write_number(row_index, 4, _number(row.group_pct), formats["percent"])
        sheet.write_number(row_index, 5, _number(row.fpd_pct), formats["percent"])
        sheet.write_number(row_index, 6, row.source.page, formats["integer"])
    if report.cash_collaterals:
        sheet.add_table(
            cash_header,
            0,
            cash_header + len(report.cash_collaterals),
            len(cash_headers) - 1,
            {
                "name": "CashCollaterals",
                "style": "Table Style Medium 4",
                "columns": [
                    {"header": header, "header_format": formats["table_header"]}
                    for header in cash_headers
                ],
            },
        )
    sheet.freeze_panes(header_row + 1, 2)
    sheet.set_column("A:B", 20)
    sheet.set_column("C:C", 10)
    sheet.set_column("D:J", 18)
    sheet.set_column("K:N", 13)
    sheet.set_row(header_row, 34)
    sheet.set_row(cash_header, 30)
    _configure_print(sheet, repeat_header_row=header_row)


def _write_findings(workbook, report, validation, formats) -> None:
    sheet = workbook.add_worksheet("Kontroller")
    _add_title(
        sheet,
        "Finansal Doğrulama ve Denetim İzi",
        (
            f"{validation.error_count} hata · {validation.warning_count} uyarı · "
            f"{validation.info_count} bilgi"
        ),
        8,
        formats,
    )
    sheet.write("A3", "Durum", formats["label"])
    sheet.write("B3", validation.status, _status_format(formats, validation))
    sheet.write("A4", "SHA-256", formats["label"])
    sheet.merge_range("B4:I4", report.metadata.source_sha256, formats["hash"])

    headers = [
        "Seviye",
        "Kontrol Kodu",
        "Açıklama",
        "Sayfa",
        "Kayıt",
        "Beklenen",
        "Gerçekleşen",
        "Çalışma Sayfası",
        "Sonuç",
    ]
    header_row = 6
    for column, header in enumerate(headers):
        sheet.write(header_row, column, header)
    for row_index, finding in enumerate(validation.findings, start=header_row + 1):
        severity_label = {
            Severity.ERROR: "Hata",
            Severity.WARNING: "Uyarı",
            Severity.INFO: "Bilgi",
        }[finding.severity]
        severity_format = {
            Severity.ERROR: formats["status_error"],
            Severity.WARNING: formats["status_warning"],
            Severity.INFO: formats["status_ok"],
        }[finding.severity]
        sheet.write(row_index, 0, severity_label, severity_format)
        sheet.write(row_index, 1, finding.code, formats["text_border"])
        sheet.write(row_index, 2, finding.message, formats["text_border"])
        sheet.write(row_index, 3, finding.page or "", formats["text_border"])
        sheet.write(row_index, 4, finding.record_key or "", formats["text_border"])
        sheet.write(row_index, 5, finding.expected or "", formats["text_border"])
        sheet.write(row_index, 6, finding.actual or "", formats["text_border"])
        sheet.write(row_index, 7, finding.sheet or "", formats["text_border"])
        result = "İncele" if finding.severity != Severity.INFO else "Bilgilendirme"
        sheet.write(row_index, 8, result, severity_format)

    last_row = header_row + max(1, len(validation.findings))
    if validation.findings:
        sheet.add_table(
            header_row,
            0,
            last_row,
            len(headers) - 1,
            {
                "name": "ValidationFindings",
                "style": "Table Style Medium 2",
                "columns": [
                    {"header": header, "header_format": formats["table_header"]}
                    for header in headers
                ],
            },
        )
    sheet.freeze_panes(header_row + 1, 0)
    sheet.set_column("A:B", 22)
    sheet.set_column("C:C", 72)
    sheet.set_column("D:E", 13)
    sheet.set_column("F:G", 22)
    sheet.set_column("H:I", 20)
    sheet.set_row(header_row + 1, 38)
    sheet.set_row(header_row, 30)
    _configure_print(sheet, repeat_header_row=header_row)


def _write_provenance(workbook, report, formats) -> None:
    sheet = workbook.add_worksheet("Kaynak İzleri")
    _add_title(
        sheet,
        "Hücre Kaynak İzleri",
        "Her kayıt PDF sayfası ve koordinat kutusuyla eşleştirilir",
        8,
        formats,
    )
    headers = [
        "Çalışma Sayfası",
        "Kayıt Türü",
        "Kayıt Anahtarı",
        "PDF Sayfası",
        "X0",
        "Y0",
        "X1",
        "Y1",
        "Çıkarım Yöntemi",
    ]
    header_row = 3
    for column, header in enumerate(headers):
        sheet.write(header_row, column, header)
    records = []
    records.extend(
        ("Hisse Senetleri", "Hisse", row.code, row.source)
        for row in report.equities
    )
    records.extend(("T. Repo", "Repo", row.code, row.source) for row in report.repos)
    records.extend(
        ("Türev", "Türev", row.code, row.source) for row in report.derivatives
    )
    records.extend(
        ("Türev", "Nakit Teminat", row.name, row.source)
        for row in report.cash_collaterals
    )
    for row_index, (sheet_name, record_type, key, source) in enumerate(
        records, start=header_row + 1
    ):
        sheet.write(row_index, 0, sheet_name, formats["text_border"])
        sheet.write(row_index, 1, record_type, formats["text_border"])
        sheet.write(row_index, 2, key, formats["text_border"])
        sheet.write_number(row_index, 3, source.page, formats["integer"])
        for offset, coordinate in enumerate(source.bbox):
            sheet.write_number(
                row_index, 4 + offset, coordinate, formats["decimal"]
            )
        sheet.write(row_index, 8, source.method.value, formats["text_border"])
    sheet.add_table(
        header_row,
        0,
        header_row + len(records),
        len(headers) - 1,
        {
            "name": "SourceProvenance",
            "style": "Table Style Medium 2",
            "columns": [
                {"header": header, "header_format": formats["table_header"]}
                for header in headers
            ],
        },
    )
    sheet.freeze_panes(header_row + 1, 0)
    sheet.set_column("A:C", 22)
    sheet.set_column("D:I", 15)
    sheet.set_row(header_row, 30)
    _configure_print(sheet, repeat_header_row=header_row)


def write_workbook(
    report: FundPortfolioReport,
    validation: ValidationResult,
    output_path: str | Path,
) -> Path:
    path = Path(output_path).expanduser().resolve()
    path.parent.mkdir(parents=True, exist_ok=True)
    workbook = xlsxwriter.Workbook(
        path,
        {
            "constant_memory": False,
            "nan_inf_to_errors": True,
        },
    )
    workbook.set_properties(
        {
            "title": f"{report.metadata.fund_code or ''} Fon Portföy Raporu",
            "subject": "FinReport Agent tarafından kaynak PDF'den oluşturuldu",
            "author": "FinReport Agent",
            "company": "FinReport Agent",
            "comments": (
                f"Kaynak SHA-256: {report.metadata.source_sha256}. "
                "Kontroller sayfasını inceleyin."
            ),
        }
    )
    formats = _formats(workbook)
    _write_summary(workbook, report, validation, formats)
    _write_equities(workbook, report, formats)
    _write_repos(workbook, report, formats)
    _write_derivatives(workbook, report, formats)
    _write_findings(workbook, report, validation, formats)
    _write_provenance(workbook, report, formats)
    workbook.close()
    return path