import json
import os
import sqlite3
from datetime import UTC, datetime
from pathlib import Path

import pytest

from tenant_engine.sqlite_store import SQLiteTenantStore
from tenant_engine.transfer import _digest, _normalise, _parse_expectation, _transfer, _verify


def test_parse_expected_tenant_with_colon_identifier() -> None:
    assert _parse_expectation("tenant:trial:portalcheck=active:1") == (
        "tenant:trial:portalcheck",
        "active",
        1,
    )
    with pytest.raises(ValueError, match="LIFECYCLE"):
        _parse_expectation("tenant:trial:portalcheck")


def test_normalised_evidence_matches_sqlite_and_postgres_shapes() -> None:
    sqlite_rows = [
        {
            "seq": 1,
            "event_type": "tenant_created",
            "tenant_id": "t-1",
            "at": "2026-08-18T10:00:00+00:00",
            "payload": '{"grouping":"trial"}',
        }
    ]
    postgres_rows = [
        {
            "seq": 1,
            "event_type": "tenant_created",
            "tenant_id": "t-1",
            "at": datetime(2026, 8, 18, 10, tzinfo=UTC),
            "payload": {"grouping": "trial"},
        }
    ]

    expected = _normalise("events", sqlite_rows)
    assert expected == _normalise("events", postgres_rows)
    assert _digest(expected) == _digest(_normalise("events", postgres_rows))


def test_sqlite_transfer_preserves_every_table_and_legacy_null_timestamps(tmp_path) -> None:
    dsn = os.getenv("TENANT_ENGINE_TEST_DATABASE_URL", "").strip()
    if not dsn:
        pytest.skip("set TENANT_ENGINE_TEST_DATABASE_URL for transfer integration")

    import psycopg
    from psycopg.rows import dict_row

    sqlite_path = tmp_path / "tenant.db"
    sqlite_store = SQLiteTenantStore(str(sqlite_path))
    with sqlite_store._db:  # noqa: SLF001 - migration fixture uses the real schema
        sqlite_store._db.execute(  # noqa: SLF001
            """INSERT INTO tenants
               (tenant_id, identifier, grouping_name, lifecycle, version)
               VALUES (?, ?, ?, ?, ?)""",
            ("t-legacy", "tenant:trial:legacy", "trial", "active", 1),
        )
        sqlite_store._db.execute(  # noqa: SLF001
            "INSERT INTO grants VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (
                "grant-1",
                "t-legacy",
                "CUS",
                "manual_grant",
                None,
                "operator",
                "2026-08-18T10:00:00+00:00",
                "corr-1",
                None,
            ),
        )
        sqlite_store._db.execute(  # noqa: SLF001
            "INSERT INTO plans VALUES (?, ?, ?)",
            ("t-legacy", "silent-tier", "2026-08-18T10:01:00+00:00"),
        )
        sqlite_store._db.execute(  # noqa: SLF001
            "INSERT INTO events (seq, event_type, tenant_id, at, payload) VALUES (?, ?, ?, ?, ?)",
            (
                7,
                "tenant_created",
                "t-legacy",
                "2026-08-18T10:00:00+00:00",
                json.dumps({"grouping": "trial"}),
            ),
        )
        sqlite_store._db.execute(  # noqa: SLF001
            "INSERT INTO idempotency_receipts VALUES (?, ?, ?, ?, ?)",
            (
                "t-legacy",
                "idem-1",
                "sha256:request",
                json.dumps(
                    {
                        "tenant_id": "t-legacy",
                        "identifier": "tenant:trial:legacy",
                        "grouping_name": "trial",
                        "display_name": None,
                        "contact_email": None,
                        "lifecycle": "active",
                        "version": 1,
                        "created_at": None,
                        "updated_at": None,
                        "retired_at": None,
                        "reactivated_at": None,
                    }
                ),
                "2026-08-18T10:02:00+00:00",
            ),
        )
        sqlite_store._db.execute(  # noqa: SLF001
            "INSERT INTO guardrail_overrides VALUES (?, ?, ?, ?, ?, ?)",
            ("t-legacy", "monthly_spend", "money", "250", "EUR", "month"),
        )
        sqlite_store._db.execute(  # noqa: SLF001
            "INSERT INTO guardrail_changes VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)",
            (
                "change-1",
                "t-legacy",
                "monthly_spend",
                None,
                json.dumps(
                    {"kind": "money", "amount": "250", "currency": "EUR", "period": "month"}
                ),
                "operator",
                "initial ceiling",
                "corr-2",
                "2026-08-18T10:03:00+00:00",
            ),
        )
    sqlite_store._db.close()  # noqa: SLF001

    migration = Path(__file__).parents[1] / "migrations/postgres/0001_tenant_store.sql"
    source = sqlite3.connect(f"file:{sqlite_path}?mode=ro", uri=True)
    source.row_factory = sqlite3.Row
    try:
        with psycopg.connect(dsn, row_factory=dict_row, autocommit=True) as target:
            target.execute(migration.read_text(encoding="utf-8"))
            target.execute(
                """TRUNCATE guardrail_changes, guardrail_overrides, idempotency_receipts,
                   events, plans, grants, tenants RESTART IDENTITY CASCADE"""
            )
            with target.transaction():
                _transfer(source, target)
                evidence = _verify(source, target, [("t-legacy", "active", 1)])
            next_seq = target.execute(
                "INSERT INTO events (event_type, tenant_id, at, payload) "
                "VALUES ('after_transfer', 't-legacy', now(), '{}'::jsonb) RETURNING seq"
            ).fetchone()["seq"]
    finally:
        source.close()

    assert evidence["tables"]["events"]["rows"] == 1
    assert evidence["tables"]["guardrail_changes"]["rows"] == 1
    assert next_seq == 8