"""Recover standalone upgrade inputs from Catalog's existing durable facts.

Catalog remains the only operation/recovery owner. The installer must replay
the original idempotency key, not interpret a non-idle installation as absent.
"""
from __future__ import annotations

import hashlib
from typing import Mapping

from serviceification_d3_engine import (
    CatalogAuthority, DeployError, canonical_bytes, catalog_authority_tables_present,
    db_query_scalar, decode_hex_text, parse_catalog_authority_row,
    read_optional_catalog_authority,
)


INSTALLATION_STATES_SQL = """
SELECT State FROM _System_CatalogAppInstallation
WHERE AppId = 'io.nexuscrm.sfcrm' AND State <> 'REMOVED';
""".strip()

ACTIVE_AUTHORITY_DIAGNOSTIC_SQL = """
SELECT CONCAT_WS(' ',
  CONCAT('installation_release=', IF(i.ActiveReleaseDigest IS NULL, 'missing', 'present')),
  CONCAT('installation_operation=', IF(i.ActiveOperationId IS NULL, 'idle', 'busy')),
  CONCAT('installation_revision=', CAST(i.CatalogRevision AS CHAR)),
  CONCAT('org_state=', IF(s.OrgId IS NULL, 'missing', 'present')),
  CONCAT('org_operation=', IF(s.ActiveOperationId IS NULL, 'idle', 'busy')),
  CONCAT('org_revision=', COALESCE(CAST(s.CatalogRevision AS CHAR), 'missing')),
  CONCAT('org_security_epoch=', COALESCE(CAST(s.LastSecurityEpoch AS CHAR), 'missing')),
  CONCAT('active_pointer=', IF(a.ActivePublicationPresent = 1
    AND a.ActivePublicationDigest IS NOT NULL, 'present', 'missing')),
  CONCAT('publication=', IF(p.PublicationDigest IS NULL, 'missing', 'present')),
  CONCAT('publication_revision=', COALESCE(CAST(p.CatalogRevision AS CHAR), 'missing')),
  CONCAT('publication_security_epoch=', COALESCE(CAST(p.SecurityEpoch AS CHAR), 'missing')))
FROM _System_CatalogAppInstallation i
LEFT JOIN _System_CatalogOrgState s ON s.OrgId = i.OrgId
LEFT JOIN _System_CatalogPublicationActive a ON a.OrgId = i.OrgId
LEFT JOIN _System_CatalogPublication p
  ON p.OrgId = i.OrgId AND p.PublicationDigest = a.ActivePublicationDigest
WHERE i.AppId = 'io.nexuscrm.sfcrm' AND i.State = 'ACTIVE';
""".strip()

# Read the immutable publication at the *starting* revision, even if a later
# publication step already moved the active pointer. Multiple matching records
# are an ambiguity, never permission to choose one arbitrarily.
PENDING_UPGRADE_SQL = """
SELECT CONCAT_WS('\t',
  HEX(i.AppInstallationId), HEX(i.OrgId), i.ActiveReleaseDigest,
  CAST(i.CatalogRevision AS CHAR), HEX(s.RuntimeAudience),
  CAST(s.CatalogRevision AS CHAR), CAST(s.LastSecurityEpoch AS CHAR),
  p.PublicationDigest, CAST(p.SchemaGeneration AS CHAR),
  CAST(p.EngineGeneration AS CHAR), HEX(o.IdempotencyKey), HEX(o.TargetReleaseDigest))
FROM _System_CatalogAppInstallation i
JOIN _System_CatalogOrgState s ON s.OrgId = i.OrgId
JOIN _System_CatalogAppOperation o
  ON o.OperationId = i.ActiveOperationId AND o.AppInstallationId = i.AppInstallationId
  AND o.OrgId = i.OrgId AND o.AppId = i.AppId
JOIN _System_CatalogPublication p
  ON p.OrgId = i.OrgId AND p.CatalogRevision = i.CatalogRevision
  AND p.SecurityEpoch = s.LastSecurityEpoch
WHERE i.AppId = 'io.nexuscrm.sfcrm' AND i.State = 'UPGRADING'
  AND o.Kind = 'UPGRADE' AND o.State = 'UPGRADING'
  AND s.ActiveOperationId = i.ActiveOperationId
  AND i.CatalogRevision = s.CatalogRevision
  AND o.PriorReleaseDigest = i.ActiveReleaseDigest
  AND o.TargetReleaseDigest = i.CandidateReleaseDigest;
""".strip()


def operation_facts(operation: str, release_digest: str, l2_tree_digest: str, revision: int) -> dict:
    return {"operation": operation, "appReleaseDigest": release_digest,
            "l2TreeDigest": l2_tree_digest, "expectedCatalogRevision": revision}


def idempotency_key(environment: str, facts: Mapping) -> str:
    return f"{environment}-apprelease-" + hashlib.sha256(canonical_bytes(facts)).hexdigest()[:32]


def read_catalog_start(
    envv: Mapping[str, str], *, environment: str, operation: str,
    release_digest: str, l2_tree_digest: str,
    query=db_query_scalar, tables_present=catalog_authority_tables_present,
) -> CatalogAuthority | None:
    if not tables_present(envv):
        return None
    before = read_optional_catalog_authority(
        envv, query=query, tables_present=lambda _: True,
    )
    if before is not None:
        return before
    states = query(dict(envv), INSTALLATION_STATES_SQL, database=envv["TIDB_DATABASE"]).splitlines()
    if not states:
        return None
    if operation == "upgrade" and states == ["ACTIVE"]:
        details = query(
            dict(envv), ACTIVE_AUTHORITY_DIAGNOSTIC_SQL,
            database=envv["TIDB_DATABASE"],
        ).splitlines()
        if len(details) != 1:
            raise DeployError("active AppInstallation has ambiguous Catalog authority")
        raise DeployError(
            "active AppInstallation has no coherent current Catalog authority: "
            + details[0]
        )
    if operation != "upgrade" or states != ["UPGRADING"]:
        raise DeployError(f"existing AppInstallation is not ready for {operation}: {states}; not a fresh installation")
    rows = query(dict(envv), PENDING_UPGRADE_SQL, database=envv["TIDB_DATABASE"]).splitlines()
    if len(rows) != 1 or len(rows[0].split('\t')) != 12:
        raise DeployError("pending Catalog upgrade has no unambiguous starting authority")
    fields = rows[0].split('\t')
    before = parse_catalog_authority_row('\t'.join(fields[:10]))
    expected_key = idempotency_key(environment, operation_facts(
        operation, release_digest, l2_tree_digest, before.catalog_revision,
    ))
    if (decode_hex_text(fields[10], "upgrade idempotency key") != expected_key
            or decode_hex_text(fields[11], "upgrade target release") != release_digest):
        raise DeployError("pending Catalog upgrade belongs to different release inputs; replay the original inputs")
    return before