from __future__ import annotations

import os
import unittest
from contextlib import contextmanager
from datetime import date
from decimal import Decimal
from typing import Any, Callable, List, Optional, Sequence, Tuple
from unittest.mock import patch

from company_reports.contracts import BatchSnapshot, ErrorCode
from company_reports.core import build_company_report
from company_reports.repository import (
    CURRENT_PARSE_SQL,
    ROOM_ITEMS_SQL,
    DatabaseConfig,
    FileMetadata,
    PostgresReportRepository,
    RepositoryError,
    ReservedReport,
)


Handler = Callable[[str, Optional[Sequence[Any]]], Tuple[List[Tuple[Any, ...]], int]]


class FakeCursor:
    def __init__(self, handler: Handler):
        self.handler = handler
        self.calls: List[Tuple[str, Optional[Sequence[Any]]]] = []
        self.many_calls: List[Tuple[str, Sequence[Sequence[Any]]]] = []
        self._rows: List[Tuple[Any, ...]] = []
        self.rowcount = 0

    def __enter__(self) -> "FakeCursor":
        return self

    def __exit__(self, *_args: Any) -> None:
        return None

    def execute(self, sql: str, params: Optional[Sequence[Any]] = None) -> None:
        normalized = " ".join(sql.split())
        self.calls.append((normalized, params))
        self._rows, self.rowcount = self.handler(normalized, params)

    def executemany(self, sql: str, params: Sequence[Sequence[Any]]) -> None:
        normalized = " ".join(sql.split())
        self.many_calls.append((normalized, params))
        self._rows, self.rowcount = self.handler(normalized, params)

    def fetchall(self) -> List[Tuple[Any, ...]]:
        rows = list(self._rows)
        self._rows = []
        return rows

    def fetchone(self) -> Tuple[Any, ...]:
        row = self._rows[0]
        self._rows = self._rows[1:]
        return row


class FakeConnection:
    def __init__(self, handler: Handler):
        self.cursor_instance = FakeCursor(handler)
        self.closed = False

    @contextmanager
    def transaction(self):
        yield

    def cursor(self) -> FakeCursor:
        return self.cursor_instance

    def close(self) -> None:
        self.closed = True


class CompanyReportRepositoryTests(unittest.TestCase):
    def test_database_config_accepts_company_or_arr_dsn(self):
        with patch.dict(
            os.environ,
            {"ARR_DATABASE_URL": "postgresql://arr"},
            clear=True,
        ):
            self.assertEqual(
                DatabaseConfig.from_environment().dsn,
                "postgresql://arr",
            )
        with patch.dict(os.environ, {}, clear=True):
            with self.assertRaises(RepositoryError) as caught:
                DatabaseConfig.from_environment()
        self.assertEqual(caught.exception.code, ErrorCode.REQUEST_INVALID)

    def test_file_metadata_rejects_absolute_or_parent_paths(self):
        for storage_key in ("/tmp/SYN.xlsx", "outputs/../SYN.xlsx"):
            with self.subTest(storage_key=storage_key):
                metadata = FileMetadata(
                    file_kind="company_ten_day_xlsx",
                    original_filename="SYN.xlsx",
                    storage_key=storage_key,
                    sha256="a" * 64,
                    byte_size=1,
                    mime_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
                )
                with self.assertRaises(RepositoryError) as caught:
                    metadata.validate()
                self.assertEqual(caught.exception.code, ErrorCode.PUBLISH_FAILED)

    def test_snapshot_reads_booking_data_by_group_code_only(self):
        source_row = (
            date(2026, 6, 30),
            101,
            501,
            "QBD",
            "QBD",
            "SYN-COMPANY",
            "SYN-BLOCK-A",
            "SYN-GROUP-A",
            " syn-group-a ",
            "SYN-RM2",
            date(2026, 6, 30),
            date(2026, 7, 2),
            2,
            Decimal("900"),
            "matched",
        )
        room_row = (
            "SYN-GROUP-A",
            "SYN-SUP-TWN",
            2,
        )

        def handler(sql: str, _params: Optional[Sequence[Any]]):
            if "FROM finance.v_active_daily_facts" in sql:
                return [source_row], 1
            if "FROM booking.v_current_room_items" in sql:
                return [("SYN-GROUP-A", 701)], 1
            if "FROM booking.v_group_room_item_summary" in sql:
                return [room_row], 1
            return [], 0

        connection = FakeConnection(handler)
        repository = PostgresReportRepository(
            DatabaseConfig("postgresql://synthetic"),
            connect=lambda _dsn: connection,
        )

        snapshot = repository.load_snapshot(2026, 7, date(2026, 7, 10))

        self.assertTrue(connection.closed)
        self.assertEqual(len(snapshot.facts), 1)
        self.assertEqual(snapshot.facts[0].business_date, date(2026, 6, 30))
        self.assertEqual(snapshot.group_parse_versions, {"SYN-GROUP-A": 701})
        self.assertEqual(snapshot.room_items[0].quantity, 2)
        booking_calls = [
            (sql, params)
            for sql, params in connection.cursor_instance.calls
            if "booking.v_current_room_items" in sql
            or "booking.v_group_room_item_summary" in sql
        ]
        self.assertEqual(len(booking_calls), 2)
        self.assertTrue(all(params == (["SYN-GROUP-A"],) for _sql, params in booking_calls))
        self.assertNotIn("arrival", CURRENT_PARSE_SQL.lower())
        self.assertNotIn("departure", CURRENT_PARSE_SQL.lower())
        room_where = ROOM_ITEMS_SQL.lower().split("where", 1)[1]
        self.assertNotIn("arrival", room_where)
        self.assertNotIn("departure", room_where)

    def test_reserve_and_activate_do_not_persist_generated_report_rows(self):
        report = build_company_report(
            "HanaTour",
            2026,
            7,
            date(2026, 7, 10),
            BatchSnapshot((), (), (), {}),
        )

        reserve_connection = FakeConnection(lambda _sql, _params: ([], 0))
        repository = PostgresReportRepository(
            DatabaseConfig("postgresql://synthetic"),
            connect=lambda _dsn: reserve_connection,
        )
        reservation = repository.reserve_report(report)
        self.assertGreater(reservation.report_version_id, 0)
        self.assertEqual(reservation.report_version_id, reservation.version_no)
        self.assertEqual(reservation.company, "HanaTour")
        self.assertFalse(reserve_connection.closed)

        activate_connection = FakeConnection(lambda _sql, _params: ([], 0))
        repository = PostgresReportRepository(
            DatabaseConfig("postgresql://synthetic"),
            connect=lambda _dsn: activate_connection,
        )
        artifact = FileMetadata(
            file_kind="company_ten_day_xlsx",
            original_filename=report.filename,
            storage_key="outputs/company_reports/2026/07/archive/v0003/" + report.filename,
            sha256="a" * 64,
            byte_size=100,
            mime_type="application/vnd.openxmlformats-officedocument.spreadsheetml.sheet",
        )
        result_json = FileMetadata(
            file_kind="result_json",
            original_filename="HanaTour-v0003.result.json",
            storage_key="outputs/company_reports/2026/07/archive/v0003/HanaTour-v0003.result.json",
            sha256="b" * 64,
            byte_size=50,
            mime_type="application/json",
        )

        repository.activate_report(reservation, artifact, result_json)

        self.assertFalse(activate_connection.closed)
        executed = " ".join(sql for sql, _params in activate_connection.cursor_instance.calls)
        self.assertNotIn("finance.report_versions", executed)
        self.assertNotIn("booking.file_objects", executed)


if __name__ == "__main__":
    unittest.main()
