"""PostgreSQL repository for durable anchor attempts and immutable A2 evidence."""

from __future__ import annotations

import json
from dataclasses import dataclass
from typing import Any, Optional

from cl.anchor.backend import AnchorAttestation, AnchorError
from cl.anchor.neo import NeoSubmissionReference, expected_anchored_root
from cl.anchor.recovery import (
    AnchorAttempt,
    DOMAIN_LEDGER_EVIDENCE,
    LedgerEvidence,
    LogicalAnchorIdentity,
)
from cl.canonical.hash_profile import (
    DEFAULT_PROFILE,
    HashProfileError,
    content_hash as hash_by_profile,
    resolve_profile,
)
from cl.canonical.jcs import canonical_bytes
from cl.store.postgres import normalize_db_text


class AnchorStorageConflict(RuntimeError):
    pass


@dataclass(frozen=True)
class AnchorOutboxEntry:
    outbox_id: int
    evidence_id: int
    logical_anchor_key: bytes
    payload_bytes: bytes
    delivery_attempts: int


@dataclass(frozen=True)
class AssuranceProfile:
    """Per-backend assurance validation (Phase 4 — anchor durability).

    The repository used to hardcode NEO A2 semantics (evidence type,
    `signature_authentic` assurance, fold re-derivation, 32-byte block
    hash), rejecting every other backend's attestation outright. A profile
    declares the binding rules each backend's evidence must satisfy; the
    NEO profile preserves the original asserts exactly."""

    evidence_type: str
    required_assurance_level: str
    root_binding: str            # "neo_fold" -> expected_anchored_root();
    #                              "package_root" -> opaque_root == package_root
    binds_target_height: bool    # height must equal reference.target_height
    requires_block_hash: bool    # 32-byte block hash mandatory


NEO_A2_PROFILE = AssuranceProfile(
    evidence_type="NEO_A2_ATTESTATION_V1",
    required_assurance_level="signature_authentic",
    root_binding="neo_fold",
    binds_target_height=True,
    requires_block_hash=True,
)

CHECKPOINT_PROFILE = AssuranceProfile(
    evidence_type="CHECKPOINT_SELF_ATTESTED_V1",
    required_assurance_level="self_attested",
    root_binding="package_root",
    binds_target_height=False,
    requires_block_hash=False,
)

NEOVA_SEGMENT_PROFILE = AssuranceProfile(
    evidence_type="NEOVA_SEGMENT_COMMITTED_V1",
    required_assurance_level="neova_segment_committed_verified_single_node",
    root_binding="package_root",
    binds_target_height=False,
    requires_block_hash=False,
)


class PostgresAnchorRepository:
    def __init__(self, connection: Any,
                 profile: AssuranceProfile = NEO_A2_PROFILE):
        self._profile = profile
        self._connection = connection

    @staticmethod
    def _reference_bytes(reference: NeoSubmissionReference) -> bytes:
        return canonical_bytes(reference.as_dict())

    @staticmethod
    def _reference(value: bytes) -> NeoSubmissionReference:
        try:
            decoded = json.loads(bytes(value).decode("utf-8"))
        except (UnicodeDecodeError, json.JSONDecodeError) as error:
            raise AnchorStorageConflict("persisted submission reference is malformed") from error
        return NeoSubmissionReference.from_dict(decoded)

    @classmethod
    def _attempt(cls, row: tuple) -> AnchorAttempt:
        return AnchorAttempt(
            logical_anchor_key=bytes(row[0]),
            identity=LogicalAnchorIdentity(
                normalize_db_text(row[1], "backend_id"),
                normalize_db_text(row[2], "network_id"),
                normalize_db_text(row[3], "submission_profile"),
                bytes(row[4]),
            ),
            submission_reference=cls._reference(row[5]),
            state=normalize_db_text(row[6], "anchor_attempt.state"),
            last_observed_level=normalize_db_text(
                row[7], "anchor_attempt.last_observed_level", optional=True
            ),
        )

    @staticmethod
    def _attempt_select() -> str:
        return """
            SELECT logical_anchor_key, backend_id, network_id, submission_profile, package_root,
                   submission_reference_bytes, state, last_observed_level
            FROM anchor_attempts
        """

    def prepare_attempt(
        self, identity: LogicalAnchorIdentity, reference: NeoSubmissionReference
    ) -> AnchorAttempt:
        logical_key = identity.logical_key()
        if reference.event_commitment_hash != identity.package_root:
            raise ValueError("submission reference must bind the package root")
        reference_bytes = self._reference_bytes(reference)
        try:
            with self._connection.cursor() as cursor:
                cursor.execute(
                    """
                    INSERT INTO anchor_attempts
                        (logical_anchor_key, backend_id, network_id, submission_profile, package_root,
                         submission_reference_bytes, state)
                    VALUES (%s, %s, %s, %s, %s, %s, 'PREPARED')
                    ON CONFLICT (logical_anchor_key) DO NOTHING
                    RETURNING logical_anchor_key, backend_id, network_id, submission_profile, package_root,
                              submission_reference_bytes, state, last_observed_level
                    """,
                    (logical_key, identity.backend_id, identity.network_id, identity.submission_profile,
                     identity.package_root, reference_bytes),
                )
                row = cursor.fetchone()
                if row is None:
                    cursor.execute(self._attempt_select() + " WHERE logical_anchor_key = %s", (logical_key,))
                    row = cursor.fetchone()
                if row is None:
                    raise AnchorStorageConflict("logical anchor conflict row disappeared")
                attempt = self._attempt(row)
                if attempt.identity != identity or attempt.submission_reference != reference:
                    raise AnchorStorageConflict("logical anchor key resolves to different stable inputs")
            self._connection.commit()
            return attempt
        except Exception:
            self._connection.rollback()
            raise

    def get_attempt(self, logical_anchor_key: bytes) -> AnchorAttempt:
        with self._connection.cursor() as cursor:
            cursor.execute(self._attempt_select() + " WHERE logical_anchor_key = %s", (logical_anchor_key,))
            row = cursor.fetchone()
        if row is None:
            raise KeyError("unknown logical anchor key")
        attempt = self._attempt(row)
        if attempt.identity.logical_key() != attempt.logical_anchor_key:
            raise AnchorStorageConflict("persisted logical anchor identity does not reproduce its key")
        if attempt.submission_reference.event_commitment_hash != attempt.identity.package_root:
            raise AnchorStorageConflict("persisted submission reference does not bind package root")
        return attempt

    def record_progress(self, logical_anchor_key: bytes, level: str) -> AnchorAttempt:
        if level not in ("A0Received", "A1Sequenced"):
            raise ValueError("only A0/A1 are durable progress states")
        try:
            with self._connection.cursor() as cursor:
                cursor.execute(
                    """
                    UPDATE anchor_attempts
                    SET state = CASE
                            WHEN state IN ('A2_COMMITTED', 'A1Sequenced') THEN state ELSE %s END,
                        last_observed_level = CASE
                            WHEN state IN ('A2_COMMITTED', 'A1Sequenced') THEN last_observed_level ELSE %s END,
                        updated_at = transaction_timestamp()
                    WHERE logical_anchor_key = %s
                    RETURNING logical_anchor_key, backend_id, network_id, submission_profile, package_root,
                              submission_reference_bytes, state, last_observed_level
                    """, (level, level, logical_anchor_key),
                )
                row = cursor.fetchone()
                if row is None:
                    raise KeyError("unknown logical anchor key")
            self._connection.commit()
            return self._attempt(row)
        except Exception:
            self._connection.rollback()
            raise

    def _evidence_payload(self, attempt: AnchorAttempt, attestation: AnchorAttestation) -> dict:
        return {
            "hash_profile_id": DEFAULT_PROFILE.id().hex(),
            "evidence_type": self._profile.evidence_type,
            "logical_anchor_key": attempt.logical_anchor_key.hex(),
            "backend_id": attempt.identity.backend_id,
            "network_id": attempt.identity.network_id,
            "submission_profile": attempt.identity.submission_profile,
            "package_root": attempt.identity.package_root.hex(),
            "submission_reference": attempt.submission_reference.as_dict(),
            "observed_evidence_root": attestation.opaque_root.hex(),
            "anchored_at_height": attestation.anchored_at_height,
            "finality_certificate_hash": attestation.finality_certificate_hash.hex(),
            "assurance_level": attestation.assurance_level,
            "block_hash": attestation.block_hash.hex(),
        }

    def _validated_payload(
        self, attempt: AnchorAttempt, attestation: AnchorAttestation
    ) -> tuple[bytes, bytes]:
        profile = self._profile
        if profile.binds_target_height and \
                attestation.anchored_at_height != attempt.submission_reference.target_height:
            raise AnchorError("A2 attestation height does not bind the submission reference")
        if profile.root_binding == "neo_fold":
            if attestation.opaque_root != expected_anchored_root(attempt.identity.package_root):
                raise AnchorError("A2 attestation root is not the independently re-derived NEO fold")
        else:
            if attestation.opaque_root != attempt.identity.package_root:
                raise AnchorError(
                    f"attestation root does not bind the package root under the "
                    f"{profile.evidence_type} profile")
        if len(attestation.finality_certificate_hash) != 32:
            raise AnchorError("A2 attestation finality certificate hash is malformed")
        if profile.requires_block_hash and len(attestation.block_hash) != 32:
            raise AnchorError("A2 attestation block hash is malformed")
        if attestation.assurance_level != profile.required_assurance_level:
            raise AnchorError(
                f"attestation does not carry {profile.required_assurance_level} assurance")
        payload = self._evidence_payload(attempt, attestation)
        payload_bytes = canonical_bytes(payload)
        evidence_hash = hash_by_profile(DEFAULT_PROFILE.id(), DOMAIN_LEDGER_EVIDENCE, payload)
        return payload_bytes, evidence_hash

    def commit_a2(self, attempt: AnchorAttempt, attestation: AnchorAttestation) -> LedgerEvidence:
        payload_bytes, evidence_hash = self._validated_payload(attempt, attestation)
        try:
            with self._connection.cursor() as cursor:
                cursor.execute(
                    self._attempt_select() + " WHERE logical_anchor_key = %s FOR UPDATE",
                    (attempt.logical_anchor_key,),
                )
                persisted_row = cursor.fetchone()
                if persisted_row is None or self._attempt(persisted_row) != attempt:
                    raise AnchorStorageConflict("A2 commit attempt differs from durable attempt state")
                cursor.execute(
                    """
                    INSERT INTO ledger_evidence
                        (logical_anchor_key, evidence_hash, payload_bytes)
                    VALUES (%s, %s, %s)
                    ON CONFLICT (logical_anchor_key) DO NOTHING
                    RETURNING evidence_id, logical_anchor_key, evidence_hash, payload_bytes
                    """, (attempt.logical_anchor_key, evidence_hash, payload_bytes),
                )
                row = cursor.fetchone()
                if row is None:
                    cursor.execute(
                        """
                        SELECT evidence_id, logical_anchor_key, evidence_hash, payload_bytes
                        FROM ledger_evidence WHERE logical_anchor_key = %s
                        """, (attempt.logical_anchor_key,),
                    )
                    row = cursor.fetchone()
                if row is None or bytes(row[2]) != evidence_hash or bytes(row[3]) != payload_bytes:
                    raise AnchorStorageConflict("existing A2 evidence differs from verified attestation")
                evidence_id = int(row[0])
                cursor.execute(
                    """
                    INSERT INTO anchor_event_outbox
                        (evidence_id, logical_anchor_key, payload_bytes)
                    VALUES (%s, %s, %s)
                    ON CONFLICT (evidence_id) DO NOTHING
                    RETURNING outbox_id
                    """, (evidence_id, attempt.logical_anchor_key, payload_bytes),
                )
                outbox_row = cursor.fetchone()
                if outbox_row is None:
                    cursor.execute(
                        """
                        SELECT logical_anchor_key, payload_bytes
                        FROM anchor_event_outbox WHERE evidence_id = %s
                        """, (evidence_id,),
                    )
                    outbox_row = cursor.fetchone()
                    if (
                        outbox_row is None
                        or bytes(outbox_row[0]) != attempt.logical_anchor_key
                        or bytes(outbox_row[1]) != payload_bytes
                    ):
                        raise AnchorStorageConflict("existing anchor outbox differs from immutable evidence")
                cursor.execute(
                    """
                    UPDATE anchor_attempts
                    SET state = 'A2_COMMITTED', last_observed_level = 'A2EvidenceFinal',
                        updated_at = transaction_timestamp()
                    WHERE logical_anchor_key = %s
                    """, (attempt.logical_anchor_key,),
                )
            # G14: evidence append + outbox insert (+ attempt projection) share this one PostgreSQL commit.
            self._connection.commit()
            return LedgerEvidence(evidence_id, attempt.logical_anchor_key, evidence_hash, payload_bytes, attestation)
        except Exception:
            self._connection.rollback()
            raise

    def _decode_evidence(self, row: tuple) -> LedgerEvidence:
        try:
            payload_bytes = bytes(row[3])
            payload = json.loads(payload_bytes.decode("utf-8"))
            if canonical_bytes(payload) != payload_bytes:
                raise ValueError("non-canonical evidence")
            attestation = AnchorAttestation(
                opaque_root=bytes.fromhex(payload["observed_evidence_root"]),
                anchored_at_height=int(payload["anchored_at_height"]),
                finality_certificate_hash=bytes.fromhex(payload["finality_certificate_hash"]),
                assurance_level=payload["assurance_level"],
                block_hash=bytes.fromhex(payload["block_hash"]),
            )
            # Read path: verify under the profile DECLARED IN the payload (Canon Lock v1) so we can read both
            # old sha3 and new sha256 records. An unknown/unregistered profile id fails closed — never guessed.
            declared_profile_id = bytes.fromhex(payload["hash_profile_id"])
            resolve_profile(declared_profile_id)  # fail-closed on an unknown id
            expected_hash = hash_by_profile(declared_profile_id, DOMAIN_LEDGER_EVIDENCE, payload)
        except (KeyError, TypeError, UnicodeDecodeError, ValueError, json.JSONDecodeError, HashProfileError) as error:
            raise AnchorStorageConflict("persisted A2 evidence is malformed") from error
        if expected_hash != bytes(row[2]):
            raise AnchorStorageConflict("persisted A2 evidence hash mismatch")
        if payload.get("logical_anchor_key") != bytes(row[1]).hex():
            raise AnchorStorageConflict("persisted A2 evidence key mismatch")
        try:
            if (
                payload["evidence_type"] != self._profile.evidence_type
                or payload["assurance_level"]
                != self._profile.required_assurance_level
            ):
                raise ValueError("wrong evidence profile")
            package_root = bytes.fromhex(payload["package_root"])
            reference = NeoSubmissionReference.from_dict(payload["submission_reference"])
            identity = LogicalAnchorIdentity(
                payload["backend_id"], payload["network_id"], payload["submission_profile"], package_root
            )
        except (KeyError, TypeError, ValueError) as error:
            raise AnchorStorageConflict("persisted A2 evidence binding is malformed") from error
        expected_root = (expected_anchored_root(package_root)
                         if self._profile.root_binding == "neo_fold"
                         else package_root)
        if (
            reference.event_commitment_hash != package_root
            or identity.logical_key() != bytes(row[1])
            or (self._profile.binds_target_height
                and reference.target_height != attestation.anchored_at_height)
            or attestation.opaque_root != expected_root
            or (self._profile.requires_block_hash
                and len(attestation.block_hash) != 32)
            or len(attestation.finality_certificate_hash) != 32
        ):
            raise AnchorStorageConflict("persisted A2 evidence does not reproduce its NEO binding")
        return LedgerEvidence(int(row[0]), bytes(row[1]), bytes(row[2]), payload_bytes, attestation)

    def get_evidence(self, logical_anchor_key: bytes) -> Optional[LedgerEvidence]:
        with self._connection.cursor() as cursor:
            cursor.execute(
                """
                SELECT evidence_id, logical_anchor_key, evidence_hash, payload_bytes
                FROM ledger_evidence WHERE logical_anchor_key = %s
                """, (logical_anchor_key,),
            )
            row = cursor.fetchone()
        return None if row is None else self._decode_evidence(row)

    def pending_anchor_events(self, limit: int = 100) -> tuple[AnchorOutboxEntry, ...]:
        if limit < 1 or limit > 1000:
            raise ValueError("outbox limit must be between 1 and 1000")
        with self._connection.cursor() as cursor:
            cursor.execute(
                """
                SELECT o.outbox_id, o.evidence_id, o.logical_anchor_key, o.payload_bytes,
                       o.delivery_attempts, e.logical_anchor_key, e.evidence_hash, e.payload_bytes
                FROM anchor_event_outbox o
                JOIN ledger_evidence e ON e.evidence_id = o.evidence_id
                WHERE o.acknowledged_at IS NULL ORDER BY o.outbox_id ASC LIMIT %s
                """, (limit,),
            )
            rows = cursor.fetchall()
        entries = []
        for row in rows:
            if bytes(row[3]) != bytes(row[7]) or bytes(row[2]) != bytes(row[5]):
                raise AnchorStorageConflict("anchor outbox payload differs from immutable evidence")
            self._decode_evidence((row[1], row[5], row[6], row[7]))
            entries.append(AnchorOutboxEntry(int(row[0]), int(row[1]), bytes(row[2]), bytes(row[3]), int(row[4])))
        return tuple(entries)

    def acknowledge_anchor_event(self, outbox_id: int) -> None:
        try:
            with self._connection.cursor() as cursor:
                cursor.execute(
                    """
                    UPDATE anchor_event_outbox
                    SET acknowledged_at = COALESCE(acknowledged_at, transaction_timestamp()),
                        delivery_attempts = delivery_attempts + CASE WHEN acknowledged_at IS NULL THEN 1 ELSE 0 END
                    WHERE outbox_id = %s RETURNING outbox_id
                    """, (outbox_id,),
                )
                if cursor.fetchone() is None:
                    raise KeyError("unknown anchor outbox id")
            self._connection.commit()
        except Exception:
            self._connection.rollback()
            raise
