"""11E2: version lead-lifecycle staff authorization; invalidate unverified installs.

Cloud upgrades run through PackInstallService on reinstallation, never in an
Alembic network call or startup repair. Records and custom entity schemas stay
in Cloud. Explicit conflicting catalog policies are not silently replaced.

Revision ID: 143
Revises: 142
"""
from copy import deepcopy
from datetime import datetime, timezone

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "143"
down_revision = "142"
branch_labels = None
depends_on = None

feature_packs = sa.table(
    "feature_packs", sa.column("slug", sa.String),
    sa.column("composition", postgresql.JSONB), sa.column("version", sa.String),
    sa.column("updated_at", sa.DateTime(timezone=True)),
)
installs = sa.table(
    "project_feature_packs", sa.column("pack_slug", sa.String),
    sa.column("status", sa.String), sa.column("pack_version", sa.String),
    sa.column("verified_at", sa.DateTime(timezone=True)),
    sa.column("updated_at", sa.DateTime(timezone=True)), sa.column("blocker", postgresql.JSONB),
)


def upgrade_composition(raw: dict) -> dict:
    from src.domain.feature_packs.crm_authorization import (
        CrmAuthorizationError, classify_policy, staff_policy,
    )
    from src.domain.feature_packs.normalize import stamp_composition

    composition = deepcopy(raw)
    leads = [e for e in composition.get("entities", []) if e.get("entity_key") == "lead"]
    if len(leads) != 1 or not isinstance(leads[0].get("schema"), dict):
        raise CrmAuthorizationError("lead", "catalog schema missing")
    state = classify_policy(leads[0]["schema"])
    if state == "historical_no_policy":
        leads[0]["schema"]["access_policy"] = staff_policy()
    elif state != "canonical_policy":
        raise CrmAuthorizationError("lead", state, conflict=True)
    return stamp_composition("lead-lifecycle", composition, implementation_version="1.1.0")


def upgrade() -> None:
    conn = op.get_bind()
    raw = conn.execute(sa.select(feature_packs.c.composition).where(
        feature_packs.c.slug == "lead-lifecycle"
    )).scalar_one()
    now = datetime.now(timezone.utc)
    conn.execute(sa.update(feature_packs).where(feature_packs.c.slug == "lead-lifecycle").values(
        composition=upgrade_composition(raw), version="1.1.0", updated_at=now,
    ))
    # Existing installs must not retain a ready signal before live verification.
    # Keep the old version until the normal installer actually verifies 1.1.0.
    conn.execute(sa.update(installs).where(
        installs.c.pack_slug == "lead-lifecycle",
        installs.c.pack_version != "1.1.0",
        installs.c.status != "uninstalled",
    ).values(status="blocked", verified_at=None, updated_at=now, blocker={
        "validation_code": "CRM_AUTHORIZATION_UNVERIFIED",
        "entity_key": "lead",
        "reason": "requires versioned staff authorization upgrade",
    }))


def downgrade() -> None:
    # Revocation must not be undone by downgrading catalog metadata. Data-safe.
    pass