"""Python/openpyxl monthly workbook building and recoverable publication."""

from __future__ import annotations

import hashlib
import json
import os
import shutil
import tempfile
import uuid
from dataclasses import dataclass
from datetime import date, datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
from typing import Any, Dict, Mapping, Optional

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

from arr_storage.store import ManagedObjectStore
from monthly_reports.contracts import KB_HEADER, RESULT_SCHEMA_VERSION, XLSX_MIME, ErrorCode, MonthlyReport
from monthly_reports.repository import (
    FileMetadata,
    ReportRepository,
    RepositoryError,
    ReservedReport,
)


class BuildError(RuntimeError):
    def __init__(self, code: str, safe_message: str):
        super().__init__(safe_message)
        self.code = code
        self.safe_message = safe_message


class PublicationError(RuntimeError):
    def __init__(self, code: str, safe_message: str):
        super().__init__(safe_message)
        self.code = code
        self.safe_message = safe_message


@dataclass(frozen=True)
class BuiltWorkbook:
    path: Path
    sha256: str
    byte_size: int
    summary: Mapping[str, Any]


@dataclass(frozen=True)
class PublicationOutcome:
    latest_path: Path
    latest_result_path: Path
    archive_path: Path
    result_path: Path
    artifact: FileMetadata
    result_json: FileMetadata


def sha256_file(path: Path) -> str:
    digest = hashlib.sha256()
    with path.open("rb") as handle:
        for chunk in iter(lambda: handle.read(1024 * 1024), b""):
            digest.update(chunk)
    return digest.hexdigest()


def _private_json(path: Path, payload: Mapping[str, Any]) -> None:
    path.parent.mkdir(parents=True, exist_ok=True, mode=0o700)
    descriptor = os.open(path, os.O_WRONLY | os.O_CREAT | os.O_TRUNC, 0o600)
    try:
        with os.fdopen(descriptor, "w", encoding="utf-8") as handle:
            json.dump(payload, handle, ensure_ascii=False, sort_keys=True, indent=2)
            handle.write("\n")
            handle.flush()
            os.fsync(handle.fileno())
    except Exception:
        try:
            os.close(descriptor)
        except OSError:
            pass
        raise


class OpenpyxlWorkbookBuilder:
    """Build and re-open a validated monthly workbook without external runtimes."""

    _INTEGER_HEADERS = frozenset({"NIGHTS", "ADULTS", "CHILDREN", "NO_OF_ROOMS"})
    _DECIMAL_HEADERS = frozenset({"RATE_AMOUNT", "REAL PRICE", "TOTAL PRICE", KB_HEADER})
    _DECIMAL_TOLERANCE = Decimal("0.000001")

    def __init__(self, *_legacy_args: Any, **_legacy_options: Any) -> None:
        # The ignored arguments keep older local wrappers import-compatible while
        # making the production builder entirely Python/openpyxl based.
        _ = (_legacy_args, _legacy_options)

    @staticmethod
    def _safe_text(value: Any) -> str:
        text = "" if value is None else str(value)
        return "'" + text if text[:1] in {"=", "+", "-", "@"} else text

    @staticmethod
    def _date_key(value: Any) -> str:
        if isinstance(value, datetime):
            return value.date().isoformat()
        if isinstance(value, date):
            return value.isoformat()
        if isinstance(value, str) and len(value) >= 10:
            return value[:10]
        return ""

    @staticmethod
    def _semantic_sha256(payload: Mapping[str, Any]) -> str:
        encoded = json.dumps(
            {"channels": payload["channels"]},
            ensure_ascii=False,
            separators=(",", ":"),
        ).encode("utf-8")
        return hashlib.sha256(encoded).hexdigest()

    @classmethod
    def _cell_value(cls, header: str, value: Any) -> Any:
        if header in {"ARRIVAL", "DEPARTURE"}:
            return date.fromisoformat(str(value))
        if header in cls._INTEGER_HEADERS:
            return int(value)
        if header in cls._DECIMAL_HEADERS:
            return float(Decimal(str(value)))
        return cls._safe_text(value)

    @classmethod
    def _decimal_matches(cls, actual: Any, expected: Any) -> bool:
        try:
            actual_decimal = Decimal(str(actual))
            expected_decimal = Decimal(str(expected))
        except (InvalidOperation, TypeError, ValueError):
            return False
        if not actual_decimal.is_finite() or not expected_decimal.is_finite():
            return False
        return abs(actual_decimal - expected_decimal) <= cls._DECIMAL_TOLERANCE

    @staticmethod
    def _style_sheet(worksheet: Any, headers: list[str]) -> None:
        worksheet.freeze_panes = "A2"
        worksheet.auto_filter.ref = f"A1:{get_column_letter(len(headers))}{max(1, worksheet.max_row)}"
        for column, header in enumerate(headers, start=1):
            cell = worksheet.cell(1, column)
            cell.font = Font(name="Arial", size=10, bold=True, color="000000")
            cell.fill = PatternFill("solid", fgColor="D9EAF7")
            cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True)
            worksheet.column_dimensions[get_column_letter(column)].width = min(
                34, max(12, len(header) + 2)
            )
        worksheet.row_dimensions[1].height = 28
        for row in worksheet.iter_rows(min_row=2):
            for cell in row:
                cell.font = Font(name="Arial", size=10, color="222222")
                cell.alignment = Alignment(vertical="center", wrap_text=True)
            worksheet.row_dimensions[row[0].row].height = 24
        for column in ("A", "B"):
            for cell in worksheet[column][1:]:
                cell.number_format = "yyyy-mm-dd"

    @classmethod
    def _write_sheet(cls, worksheet: Any, channel: Mapping[str, Any]) -> int:
        headers = [str(header) for header in channel["headers"]]
        worksheet.append(headers)
        total_column = headers.index("TOTAL PRICE") + 1
        for row in channel["rows"]:
            row_index = worksheet.max_row + 1
            values = [
                cls._cell_value(header, row.get(header, ""))
                for header in headers
            ]
            values[total_column - 1] = f"=R{row_index}*C{row_index}*G{row_index}"
            worksheet.append(values)
        cls._style_sheet(worksheet, headers)
        return len(channel["rows"])

    @classmethod
    def _validate_workbook(
        cls,
        output_path: Path,
        report: MonthlyReport,
        payload: Mapping[str, Any],
    ) -> int:
        workbook = load_workbook(output_path, data_only=False, read_only=True)
        try:
            expected_names = [channel.worksheet for channel in report.channels]
            expected_rows = [len(channel.rows) for channel in report.channels]
            if workbook.sheetnames != expected_names:
                raise ValueError("worksheet names do not match")
            formula_count = 0
            for channel, expected_row_count in zip(report.channels, expected_rows):
                worksheet = workbook[channel.worksheet]
                headers = list(channel.headers)
                row_iterator = worksheet.iter_rows(values_only=False)
                header_cells = next(row_iterator, None)
                if (
                    header_cells is None
                    or len(header_cells) != len(headers)
                    or [cell.value for cell in header_cells] != headers
                ):
                    raise ValueError("worksheet headers do not match")
                for row_index, expected_row in enumerate(channel.rows, start=2):
                    cells = next(row_iterator, None)
                    if cells is None or len(cells) != len(headers):
                        raise ValueError("worksheet dimensions do not match")
                    payload_row = expected_row.to_payload(channel.worksheet == "DY-AI-Easy-KB")
                    for header, cell in zip(headers, cells):
                        if header == "TOTAL PRICE":
                            expected_formula = f"=R{row_index}*C{row_index}*G{row_index}"
                            if cell.value != expected_formula or cell.data_type != "f":
                                raise ValueError("total price formula does not match")
                            formula_count += 1
                            continue
                        actual = cell.value
                        expected = payload_row[header]
                        if header in {"ARRIVAL", "DEPARTURE"}:
                            matches = cls._date_key(actual) == str(expected)
                        elif header in cls._INTEGER_HEADERS:
                            matches = actual == int(expected)
                        elif header in cls._DECIMAL_HEADERS:
                            matches = cls._decimal_matches(actual, expected)
                        else:
                            matches = ("" if actual is None else actual) == cls._safe_text(expected)
                        if not matches:
                            raise ValueError("worksheet values do not match")
                if next(row_iterator, None) is not None:
                    raise ValueError("worksheet dimensions do not match")
            if formula_count != report.row_count:
                raise ValueError("formula count does not match")
            if cls._semantic_sha256(payload) != cls._semantic_sha256(report.to_workbook_payload()):
                raise ValueError("semantic source differs")
            return formula_count
        finally:
            workbook.close()

    def build(self, report: MonthlyReport, work_dir: Path) -> BuiltWorkbook:
        work_dir.mkdir(parents=True, exist_ok=True, mode=0o700)
        os.chmod(work_dir, 0o700)
        output_path = work_dir / report.filename
        summary_path = work_dir / "workbook-summary.json"
        payload = report.to_workbook_payload()
        try:
            workbook = Workbook()
            for index, channel in enumerate(payload["channels"]):
                worksheet = workbook.active if index == 0 else workbook.create_sheet()
                worksheet.title = str(channel["worksheet"])
                self._write_sheet(worksheet, channel)
            workbook.save(output_path)
            workbook.close()
            os.chmod(output_path, 0o600)
            formula_count = self._validate_workbook(output_path, report, payload)
            expected_names = [channel.worksheet for channel in report.channels]
            expected_rows = [len(channel.rows) for channel in report.channels]
            summary = {
                "status": "success",
                "schema_version": payload["schema_version"],
                "report_year": report.report_year,
                "report_month": report.report_month,
                "as_of_date": report.as_of_date.isoformat(),
                "filename": report.filename,
                "sheet_names": expected_names,
                "row_counts": expected_rows,
                "formula_count": formula_count,
                "semantic_sha256": self._semantic_sha256(payload),
                "preview_count": len(expected_names),
                "byte_size": output_path.stat().st_size,
            }
            _private_json(summary_path, summary)
        except (OSError, ValueError, TypeError, InvalidOperation, KeyError):
            raise BuildError(
                ErrorCode.OUTPUT_VALIDATION_FAILED,
                "the monthly XLSX builder result could not be validated",
            ) from None
        return BuiltWorkbook(
            path=output_path,
            sha256=sha256_file(output_path),
            byte_size=output_path.stat().st_size,
            summary=summary,
        )

class AtomicReportPublisher:
    def __init__(
        self,
        project_root: Path,
        output_root: Path,
        *,
        object_store: Optional[ManagedObjectStore] = None,
        bucket_alias: str = "arr-private",
    ) -> None:
        self._project_root = project_root.resolve()
        self._output_root = output_root.resolve()
        self._object_store = object_store
        self._bucket_alias = bucket_alias
        try:
            self._output_root.relative_to(self._project_root)
        except ValueError:
            raise ValueError("output_root must be inside project_root") from None

    @staticmethod
    def _fsync_parent(path: Path) -> None:
        descriptor = os.open(path.parent, os.O_RDONLY)
        try:
            os.fsync(descriptor)
        finally:
            os.close(descriptor)

    @classmethod
    def _atomic_copy(cls, source: Path, target: Path) -> None:
        target.parent.mkdir(parents=True, exist_ok=True, mode=0o700)
        temporary = target.parent / f".{target.name}.tmp-{uuid.uuid4().hex}"
        try:
            shutil.copyfile(source, temporary)
            os.chmod(temporary, 0o600)
            with temporary.open("rb") as handle:
                os.fsync(handle.fileno())
            os.replace(temporary, target)
            cls._fsync_parent(target)
        finally:
            temporary.unlink(missing_ok=True)

    @classmethod
    def _atomic_json(cls, target: Path, payload: Mapping[str, Any]) -> None:
        target.parent.mkdir(parents=True, exist_ok=True, mode=0o700)
        temporary = target.parent / f".{target.name}.tmp-{uuid.uuid4().hex}"
        try:
            _private_json(temporary, payload)
            os.replace(temporary, target)
            cls._fsync_parent(target)
        finally:
            temporary.unlink(missing_ok=True)

    @classmethod
    def _install_once(cls, source: Path, target: Path, expected_sha256: str) -> bool:
        if target.exists():
            if sha256_file(target) != expected_sha256:
                raise PublicationError(
                    ErrorCode.PUBLISH_FAILED,
                    "an immutable monthly archive path contains different content",
                )
            return False
        cls._atomic_copy(source, target)
        return True

    def _storage_key(self, path: Path) -> str:
        try:
            return path.resolve().relative_to(self._project_root).as_posix()
        except ValueError:
            raise PublicationError(
                ErrorCode.PUBLISH_FAILED,
                "a monthly artifact escaped the controlled project root",
            ) from None

    @staticmethod
    def _success_result(
        report: MonthlyReport,
        reservation: ReservedReport,
        artifact_storage_key: str,
        artifact_sha256: str,
        semantic_sha256: str,
        storage_provider: str,
        bucket_alias: str,
    ) -> Dict[str, Any]:
        return {
            "schema_version": RESULT_SCHEMA_VERSION,
            "status": "success",
            "report_year": report.report_year,
            "report_month": report.report_month,
            "as_of_date": report.as_of_date.isoformat(),
            "report_version_id": reservation.report_version_id,
            "version_no": reservation.version_no,
            "artifact": {
                "filename": report.filename,
                "storage_key": artifact_storage_key,
                "sha256": artifact_sha256,
                "semantic_sha256": semantic_sha256,
                "storage_provider": storage_provider,
                "bucket_alias": bucket_alias,
            },
            "row_count": report.row_count,
            "channel_manifest": [
                {
                    "worksheet": worksheet,
                    "worksheet_order": order,
                    "row_count": row_count,
                }
                for worksheet, order, row_count in report.channel_manifest
            ],
            "daily_version_count": len(report.daily_versions),
            "warnings": [],
            "errors": [],
        }

    def publish(
        self,
        report: MonthlyReport,
        reservation: ReservedReport,
        built: BuiltWorkbook,
        repository: ReportRepository,
        work_dir: Path,
    ) -> PublicationOutcome:
        month_root = self._output_root / f"{report.report_year:04d}" / f"{report.report_month:02d}"
        version_root = month_root / "archive" / f"v{reservation.version_no:04d}"
        archive_path = version_root / report.filename
        result_path = version_root / "result.json"
        latest_path = month_root / "latest.xlsx"
        latest_result_path = month_root / "latest.result.json"
        workbook_backup = work_dir / "previous-latest.xlsx"
        result_backup = work_dir / "previous-latest.result.json"
        latest_existed = latest_path.is_file()
        latest_result_existed = latest_result_path.is_file()
        archive_created = False
        result_created = False
        latest_replaced = False
        latest_result_replaced = False

        try:
            month_root.mkdir(parents=True, exist_ok=True, mode=0o700)
            if latest_existed:
                shutil.copyfile(latest_path, workbook_backup)
                os.chmod(workbook_backup, 0o600)
            if latest_result_existed:
                shutil.copyfile(latest_result_path, result_backup)
                os.chmod(result_backup, 0o600)

            archive_created = self._install_once(built.path, archive_path, built.sha256)
            storage_provider = "local"
            bucket_alias = "arr-project-root"
            artifact_storage_key = self._storage_key(archive_path)
            if self._object_store is not None:
                uploaded_artifact = self._object_store.upload_committed(
                    job_id=f"monthly-report-{reservation.report_version_id}",
                    attempt_no=1,
                    role="monthly_report",
                    source=archive_path,
                    original_filename=report.filename,
                    expected_sha256=built.sha256,
                    expected_byte_size=built.byte_size,
                )
                storage_provider = "oss"
                bucket_alias = self._bucket_alias
                artifact_storage_key = uploaded_artifact.object_key
            result_payload = self._success_result(
                report,
                reservation,
                artifact_storage_key,
                built.sha256,
                str(built.summary.get("semantic_sha256", "")),
                storage_provider,
                bucket_alias,
            )
            if result_path.exists():
                expected = json.dumps(
                    result_payload,
                    ensure_ascii=False,
                    sort_keys=True,
                    indent=2,
                ) + "\n"
                if result_path.read_text(encoding="utf-8") != expected:
                    raise PublicationError(
                        ErrorCode.PUBLISH_FAILED,
                        "an immutable monthly result path contains different content",
                    )
            else:
                self._atomic_json(result_path, result_payload)
                result_created = True

            self._atomic_copy(built.path, latest_path)
            latest_replaced = True
            self._atomic_json(latest_result_path, result_payload)
            latest_result_replaced = True
            artifact = FileMetadata(
                file_kind="monthly_xlsx",
                original_filename=report.filename,
                storage_key=artifact_storage_key,
                sha256=built.sha256,
                byte_size=built.byte_size,
                mime_type=XLSX_MIME,
            )
            result_json = FileMetadata(
                file_kind="result_json",
                original_filename=result_path.name,
                storage_key=self._storage_key(result_path),
                sha256=sha256_file(result_path),
                byte_size=result_path.stat().st_size,
                mime_type="application/json",
                storage_provider=storage_provider,
                bucket_alias=bucket_alias,
            )
            if self._object_store is not None:
                uploaded_result = self._object_store.upload_committed(
                    job_id=f"monthly-report-{reservation.report_version_id}",
                    attempt_no=1,
                    role="result_json",
                    source=result_path,
                    original_filename=result_path.name,
                    expected_sha256=result_json.sha256,
                    expected_byte_size=result_json.byte_size,
                )
                result_json = FileMetadata(
                    file_kind="result_json",
                    original_filename=result_path.name,
                    storage_key=uploaded_result.object_key,
                    sha256=uploaded_result.sha256,
                    byte_size=uploaded_result.byte_size,
                    mime_type=uploaded_result.mime_type,
                    storage_provider=storage_provider,
                    bucket_alias=bucket_alias,
                )
            artifact = FileMetadata(
                file_kind="monthly_xlsx",
                original_filename=report.filename,
                storage_key=artifact_storage_key,
                sha256=built.sha256,
                byte_size=built.byte_size,
                mime_type=XLSX_MIME,
                storage_provider=storage_provider,
                bucket_alias=bucket_alias,
            )
            repository.activate_report(
                reservation,
                artifact,
                result_json,
                str(built.summary.get("semantic_sha256", "")),
            )
            return PublicationOutcome(
                latest_path=latest_path,
                latest_result_path=latest_result_path,
                archive_path=archive_path,
                result_path=result_path,
                artifact=artifact,
                result_json=result_json,
            )
        except Exception as error:
            if latest_result_replaced:
                if latest_result_existed and result_backup.is_file():
                    self._atomic_copy(result_backup, latest_result_path)
                else:
                    latest_result_path.unlink(missing_ok=True)
            if latest_replaced:
                if latest_existed and workbook_backup.is_file():
                    self._atomic_copy(workbook_backup, latest_path)
                else:
                    latest_path.unlink(missing_ok=True)
            if result_created:
                result_path.unlink(missing_ok=True)
            if archive_created:
                archive_path.unlink(missing_ok=True)
            code = getattr(error, "code", ErrorCode.PUBLISH_FAILED)
            safe_message = getattr(error, "safe_message", "monthly report publication failed")
            try:
                repository.mark_failed(reservation, code, safe_message)
            except Exception:
                pass
            if isinstance(error, PublicationError):
                raise
            if isinstance(error, RepositoryError):
                raise PublicationError(error.code, error.safe_message) from None
            raise PublicationError(
                ErrorCode.PUBLISH_FAILED,
                "monthly report publication failed",
            ) from None


def private_staging_directory(parent: Path):
    parent.mkdir(parents=True, exist_ok=True, mode=0o700)
    return tempfile.TemporaryDirectory(prefix="monthly-report-", dir=parent)
