from __future__ import annotations

import re
import unittest
from pathlib import Path


PROJECT_ROOT = Path(__file__).resolve().parents[1]
UP_PATH = PROJECT_ROOT / "database" / "010_mcp_result_ingestion.sql"
DOWN_PATH = PROJECT_ROOT / "database" / "010_mcp_result_ingestion.down.sql"


class McpResultIngestionMigrationTests(unittest.TestCase):
    @classmethod
    def setUpClass(cls) -> None:
        cls.up = UP_PATH.read_text(encoding="utf-8")
        cls.down = DOWN_PATH.read_text(encoding="utf-8")
        cls.up_lower = cls.up.lower()
        cls.down_lower = cls.down.lower()

    def test_up_is_additive_target_guarded_and_requires_mvp_v1(self) -> None:
        self.assertIn("current_database() <> 'booking_test'", self.up)
        self.assertIn("to_regclass('ingestion.processing_runs')", self.up)
        self.assertIn("to_regclass('ingestion.processing_attempts')", self.up)
        self.assertIn("to_regclass('finance.daily_versions')", self.up)
        self.assertNotIn("drop schema", self.up_lower)
        self.assertNotIn("truncate ", self.up_lower)
        self.assertNotIn("delete from", self.up_lower)

    def test_up_creates_attempt_bound_direct_submission_ledger(self) -> None:
        for relation in (
            "ingestion.result_submission_grants",
            "ingestion.result_submissions",
        ):
            self.assertIn(f"create table {relation}", self.up_lower)
        self.assertNotIn("result_submission_batches", self.up_lower)

        self.assertIn(
            "foreign key (attempt_id, processing_run_id)",
            self.up_lower,
        )
        self.assertIn("unique (attempt_id)", self.up_lower)
        self.assertIn("interval '30 minutes'", self.up_lower)
        self.assertIn(
            "consumed_at between created_at and expires_at",
            self.up_lower,
        )
        self.assertNotIn("write_token_sha256", self.up_lower)
        self.assertNotIn("password", self.up_lower)
        self.assertNotIn("access_key", self.up_lower)

    def test_same_payload_hash_is_indexed_but_not_unique(self) -> None:
        self.assertIn(
            "create index result_submissions_payload_sha256_idx",
            self.up_lower,
        )
        self.assertNotRegex(
            self.up_lower,
            re.compile(r"unique\s*\([^)]*payload_sha256", re.DOTALL),
        )
        self.assertIn("same xml/result may be processed repeatedly", self.up_lower)

    def test_single_payload_is_bounded_counted_and_purged_at_terminal(self) -> None:
        self.assertIn("payload_byte_size between 2 and 3145728", self.up_lower)
        self.assertIn("payload_json ? 'records'", self.up_lower)
        self.assertIn(
            "jsonb_array_length(payload_json -> 'records') = record_count",
            self.up_lower,
        )
        self.assertIn("submission_status = 'received'", self.up_lower)
        self.assertIn("payload_json is not null", self.up_lower)
        self.assertIn("payload_json is null", self.up_lower)
        self.assertIn("payload_purged_at is not null", self.up_lower)
        self.assertIn("submission_status <> 'expired'", self.up_lower)
        self.assertIn("finished_at >= expires_at", self.up_lower)
        self.assertNotIn("expected_batch_count", self.up_lower)
        self.assertNotIn("received_batch_count", self.up_lower)

    def test_existing_artifact_contract_is_preserved_as_default(self) -> None:
        self.assertGreaterEqual(
            self.up_lower.count("default 'artifact_callback'"),
            2,
        )
        self.assertIn("result_delivery_mode = 'direct_mcp'", self.up_lower)
        self.assertIn("result_artifact_id is null", self.up_lower)
        self.assertIn("daily_report_artifact_id is null", self.up_lower)
        self.assertIn("daily_report_artifact_id is not null", self.up_lower)

    def test_down_refuses_data_and_restores_008_constraints(self) -> None:
        self.assertIn("current_database() <> 'booking_test'", self.down)
        self.assertIn(
            "refusing rollback: migration 010 direct MCP state exists",
            self.down,
        )
        self.assertIn("exists (select 1 from ingestion.result_submissions)", self.down_lower)
        self.assertNotIn("result_submission_batches", self.down_lower)
        self.assertLess(
            self.down_lower.index("drop table ingestion.result_submissions"),
            self.down_lower.index("drop table ingestion.result_submission_grants"),
        )
        self.assertIn("drop column result_delivery_mode", self.down_lower)
        self.assertIn(
            "add constraint processing_runs_terminal_shape check",
            self.down_lower,
        )
        self.assertIn(
            "add constraint daily_versions_status_shape check",
            self.down_lower,
        )


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