"""Rename ``case*`` schema artifacts to ``investigation*``.

The IR domain noun is **investigation** — an active, AI-driven analysis
lifecycle. The legacy schema picked ``case`` (TheHive/SOAR convention,
where a case is a passive folder for human tasks) which never fit this
product. This migration aligns the schema with the domain.

Operations:
  * Rename tables: cases, case_runs, case_events, case_facts, case_iocs,
    case_assets, case_links, case_outbox → investigation* equivalents.
  * Rename FK column ``case_id`` → ``investigation_id`` on every table
    that references the (formerly cases) parent: alerts, case_events,
    case_facts, case_iocs, case_assets, notes, proposals, execution_log,
    case_outbox. The ``case_links`` table's ``from_case_id`` /
    ``to_case_id`` columns are renamed to ``from_investigation_id`` /
    ``to_investigation_id``.
  * Rename the ``case_events_seq`` sequence powering case_events.seq.
  * Rename indexes / unique constraints / check constraints / foreign-
    key constraint names that embed ``case`` so a fresh DB restored from
    schema-only dumps gets the same names as a migrated one.
  * Rebuild RLS policies on the renamed tables. Postgres rewrites the
    policy expressions during table rename, but the policy NAMES still
    embed the old table name (``cases_tenant_audience``, etc.) — drop +
    recreate so the convention stays ``<table>_tenant_audience``.

This is a metadata-only migration: no row rewrite. Foreign-key data
already references the old IDs; renaming the column and the parent
table preserves the same row pointers.

Audit-log entries written before this migration carry ``ir.case.*``
event_type strings — those are append-only history and are NOT
backfilled. New writes go in as ``ir.investigation.*``.
"""

from __future__ import annotations

from alembic import op
import sqlalchemy as sa


revision = "v1_0011_rename_case_to_investigation"
down_revision = "v1_0010_organizations_slug"
branch_labels = None
depends_on = None


# ----------------------------------------------------------------------------
# Schema artifact maps
# ----------------------------------------------------------------------------

# Parent table + every dependent that holds a case_id FK to it. The order
# doesn't matter for renames (Postgres doesn't validate column references
# against table NAMES — they're stored as object IDs internally).
TABLE_RENAMES: list[tuple[str, str]] = [
    ("cases", "investigations"),
    ("case_runs", "investigation_runs"),
    ("case_events", "investigation_events"),
    ("case_facts", "investigation_facts"),
    ("case_iocs", "investigation_iocs"),
    ("case_assets", "investigation_assets"),
    ("case_links", "investigation_links"),
    ("case_outbox", "investigation_outbox"),
]

# Tables (post-rename names) where the ``case_id`` column needs to
# become ``investigation_id``. ``case_links`` has TWO such columns
# (from_case_id / to_case_id) — handled separately.
COLUMN_RENAMES_CASE_ID: list[str] = [
    "alerts",
    "investigation_runs",
    "investigation_events",
    "investigation_facts",
    "investigation_iocs",
    "investigation_assets",
    "notes",
    "proposals",
    "execution_log",
    "investigation_outbox",
]

# Indexes (and unique-constraint-backing indexes — Postgres treats
# uniques as indexes for rename purposes). (old, new)
#
# Includes Postgres-auto-named primary-key indexes (``<table>_pkey``)
# and the auto-named unique-key index for case_outbox.idempotency_key.
# ALTER TABLE RENAME does NOT touch index names; they have to be
# renamed explicitly so a fresh DB matches a migrated one.
INDEX_RENAMES: list[tuple[str, str]] = [
    ("uq_cases_tenant_short_id", "uq_investigations_tenant_short_id"),
    ("ix_cases_tenant_status", "ix_investigations_tenant_status"),
    ("uq_case_runs_single_active", "uq_investigation_runs_single_active"),
    ("uq_case_events_idempotency", "uq_investigation_events_idempotency"),
    ("ix_case_events_case_seq", "ix_investigation_events_seq"),
    ("ix_case_assets_case", "ix_investigation_assets_investigation"),
    ("ix_case_links_from", "ix_investigation_links_from"),
    ("ix_case_links_to", "ix_investigation_links_to"),
    ("uq_case_links_pair_kind", "uq_investigation_links_pair_kind"),
    ("ix_notes_case", "ix_notes_investigation"),
    ("ix_proposals_case_status", "ix_proposals_investigation_status"),
    ("ix_case_outbox_claim", "ix_investigation_outbox_claim"),
    ("ix_execution_log_case_ts", "ix_execution_log_investigation_ts"),
    # Auto-generated by Postgres; survive ALTER TABLE RENAME with the
    # old name and need explicit renaming.
    ("cases_pkey", "investigations_pkey"),
    ("case_runs_pkey", "investigation_runs_pkey"),
    ("case_events_pkey", "investigation_events_pkey"),
    ("case_facts_pkey", "investigation_facts_pkey"),
    ("case_iocs_pkey", "investigation_iocs_pkey"),
    ("case_assets_pkey", "investigation_assets_pkey"),
    ("case_links_pkey", "investigation_links_pkey"),
    ("case_outbox_pkey", "investigation_outbox_pkey"),
    ("case_outbox_idempotency_key_key", "investigation_outbox_idempotency_key_key"),
    # From v1_0009_case_runs_lease.
    ("ix_case_runs_lease_expires_at", "ix_investigation_runs_lease_expires_at"),
]

# CHECK constraints — must be renamed via ALTER TABLE RENAME CONSTRAINT,
# not ALTER INDEX. (post-rename-table, old, new)
CHECK_CONSTRAINT_RENAMES: list[tuple[str, str, str]] = [
    ("investigation_events", "ck_case_events_visibility", "ck_investigation_events_visibility"),
    ("investigation_links", "ck_case_links_visibility", "ck_investigation_links_visibility"),
    ("investigation_links", "ck_case_links_distinct", "ck_investigation_links_distinct"),
]

# Tables that get an ``<table>_tenant_audience`` policy
# (cases-as-was-then plus its visibility-modeled siblings).
AUDIENCE_RLS_TABLES: list[str] = [
    "investigations",
    "investigation_events",
    "iocs",
    "notes",
    "proposals",
    "investigation_links",
]

# Tables that get the simpler ``<table>_tenant_isolation`` policy.
ISOLATION_RLS_TABLES: list[str] = [
    "investigation_runs",
    "investigation_facts",
    "investigation_iocs",
    "investigation_assets",
    "investigation_outbox",
    "execution_log",
    "tenant_policies",
]


# ----------------------------------------------------------------------------
# Policy SQL (kept in sync with v1_0003's _attach_rls_* helpers)
# ----------------------------------------------------------------------------


def _audience_policy_sql(table: str) -> str:
    return f"""
    CREATE POLICY {table}_tenant_audience ON {table}
        USING (
            (
                COALESCE(NULLIF(current_setting('app.current_tenant_id', true), ''), '') = ''
                AND current_setting('app.current_audience', true) = 'mssp'
            )
            OR (
                NOT (tenant_id IS DISTINCT FROM
                     NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
                AND (
                    visibility = ANY(ARRAY['customer_safe','system'])
                    OR current_setting('app.current_audience', true) = 'mssp'
                )
            )
        )
        WITH CHECK (
            (
                COALESCE(NULLIF(current_setting('app.current_tenant_id', true), ''), '') = ''
                AND current_setting('app.current_audience', true) = 'mssp'
            )
            OR NOT (tenant_id IS DISTINCT FROM
                    NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
        )
    """


def _isolation_policy_sql(table: str) -> str:
    return f"""
    CREATE POLICY {table}_tenant_isolation ON {table}
        USING (
            (
                COALESCE(NULLIF(current_setting('app.current_tenant_id', true), ''), '') = ''
                AND current_setting('app.current_audience', true) = 'mssp'
            )
            OR NOT (tenant_id IS DISTINCT FROM
                    NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
        )
        WITH CHECK (
            (
                COALESCE(NULLIF(current_setting('app.current_tenant_id', true), ''), '') = ''
                AND current_setting('app.current_audience', true) = 'mssp'
            )
            OR NOT (tenant_id IS DISTINCT FROM
                    NULLIF(current_setting('app.current_tenant_id', true), '')::uuid)
        )
    """


# Mapping from the post-rename table to the policy NAMES that exist on it
# at the start of this migration. Postgres rewrites the policy expressions
# during ``ALTER TABLE ... RENAME`` but keeps the policy *name* embedded
# with the old table name. We drop the old-named policy + recreate under
# the new convention.
OLD_POLICY_NAMES: dict[str, list[str]] = {
    "investigations": ["cases_tenant_audience"],
    "investigation_events": ["case_events_tenant_audience"],
    "investigation_links": ["case_links_tenant_audience"],
    "iocs": ["iocs_tenant_audience"],
    "notes": ["notes_tenant_audience"],
    "proposals": ["proposals_tenant_audience"],
    "investigation_runs": ["case_runs_tenant_isolation"],
    "investigation_facts": ["case_facts_tenant_isolation"],
    "investigation_iocs": ["case_iocs_tenant_isolation"],
    "investigation_assets": ["case_assets_tenant_isolation"],
    "investigation_outbox": ["case_outbox_tenant_isolation"],
    # execution_log + tenant_policies don't embed "case" in their policy
    # name — leave them.
}


# ----------------------------------------------------------------------------
# upgrade
# ----------------------------------------------------------------------------


def upgrade() -> None:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    existing_tables = set(inspector.get_table_names(schema="public"))

    # Fresh installs still traverse the legacy read-model migrations
    # that created a public ``investigations`` table long before the
    # V1 IR core introduced ``cases``/``case_*``. When both names are
    # present in the same upgrade run, the V1 rename would otherwise
    # die on ``cases -> investigations`` with DuplicateTable. Preserve
    # the legacy table by moving it out of the way first; the V1 IR
    # tables become the authoritative ``investigation*`` schema.
    if "cases" in existing_tables and "investigations" in existing_tables:
        op.execute(
            'ALTER TABLE "investigations" RENAME TO "legacy_investigations_read_model"'
        )
        op.execute(
            'ALTER INDEX IF EXISTS "investigations_pkey" '
            'RENAME TO "legacy_investigations_read_model_pkey"'
        )

    # Idempotent re-entry: if the canonical investigation names already
    # exist and the legacy case names do not, the rename work is done.
    existing_tables = set(sa.inspect(bind).get_table_names(schema="public"))
    if "investigations" in existing_tables and "cases" not in existing_tables:
        return

    # --- 1. Tables ---------------------------------------------------------
    for old, new in TABLE_RENAMES:
        op.execute(f'ALTER TABLE "{old}" RENAME TO "{new}"')

    # --- 2. Sequences ------------------------------------------------------
    # case_events.seq's nextval sequence
    op.execute('ALTER SEQUENCE "case_events_seq" RENAME TO "investigation_events_seq"')
    # The default expression on investigation_events.seq still references
    # the OLD sequence name baked into the column default. Postgres
    # follows OID, but we re-set the default to the new name so
    # pg_dump / schema introspection match.
    op.execute(
        "ALTER TABLE investigation_events "
        "ALTER COLUMN seq SET DEFAULT nextval('investigation_events_seq')"
    )
    # cases.short_id sequence (independent of case_events_seq).
    op.execute('ALTER SEQUENCE "cases_short_id_seq" RENAME TO "investigations_short_id_seq"')

    # --- 3. case_id columns -----------------------------------------------
    for tbl in COLUMN_RENAMES_CASE_ID:
        op.execute(
            f'ALTER TABLE "{tbl}" RENAME COLUMN "case_id" TO "investigation_id"'
        )
    # case_links has two FK columns to (former) cases.
    op.execute(
        'ALTER TABLE "investigation_links" RENAME COLUMN "from_case_id" TO "from_investigation_id"'
    )
    op.execute(
        'ALTER TABLE "investigation_links" RENAME COLUMN "to_case_id" TO "to_investigation_id"'
    )

    # --- 4. Indexes / constraints -----------------------------------------
    for old, new in INDEX_RENAMES:
        op.execute(f'ALTER INDEX IF EXISTS "{old}" RENAME TO "{new}"')
    for tbl, old, new in CHECK_CONSTRAINT_RENAMES:
        op.execute(
            f'ALTER TABLE "{tbl}" RENAME CONSTRAINT "{old}" TO "{new}"'
        )

    # --- 5. RLS policies ---------------------------------------------------
    for table, old_names in OLD_POLICY_NAMES.items():
        for old in old_names:
            op.execute(f'DROP POLICY IF EXISTS "{old}" ON "{table}"')
    for tbl in AUDIENCE_RLS_TABLES:
        op.execute(_audience_policy_sql(tbl))
    for tbl in ISOLATION_RLS_TABLES:
        # execution_log + tenant_policies didn't embed "case" — they
        # already have correct policy names from v1_0003. Skip them.
        if tbl in ("execution_log", "tenant_policies"):
            continue
        op.execute(_isolation_policy_sql(tbl))


# ----------------------------------------------------------------------------
# downgrade — symmetric reverse
# ----------------------------------------------------------------------------


def downgrade() -> None:
    # Strict reverse of upgrade: drop new policies (still on
    # new-named tables), rename indexes back, rename columns back,
    # rename tables back, recreate old policies.
    for tbl in AUDIENCE_RLS_TABLES:
        op.execute(f'DROP POLICY IF EXISTS "{tbl}_tenant_audience" ON "{tbl}"')
    for tbl in ISOLATION_RLS_TABLES:
        if tbl in ("execution_log", "tenant_policies"):
            continue
        op.execute(f'DROP POLICY IF EXISTS "{tbl}_tenant_isolation" ON "{tbl}"')

    # Indexes — flip rename direction.
    for old, new in INDEX_RENAMES:
        op.execute(f'ALTER INDEX IF EXISTS "{new}" RENAME TO "{old}"')
    for tbl, old, new in CHECK_CONSTRAINT_RENAMES:
        op.execute(
            f'ALTER TABLE "{tbl}" RENAME CONSTRAINT "{new}" TO "{old}"'
        )

    # Columns — flip rename direction.
    op.execute(
        'ALTER TABLE "investigation_links" RENAME COLUMN "to_investigation_id" TO "to_case_id"'
    )
    op.execute(
        'ALTER TABLE "investigation_links" RENAME COLUMN "from_investigation_id" TO "from_case_id"'
    )
    for tbl in COLUMN_RENAMES_CASE_ID:
        op.execute(
            f'ALTER TABLE "{tbl}" RENAME COLUMN "investigation_id" TO "case_id"'
        )

    # Sequence default — must reset to OLD name BEFORE the table
    # rename, since the column lives on (now-)investigation_events
    # which is about to become case_events again.
    op.execute(
        "ALTER TABLE investigation_events "
        "ALTER COLUMN seq SET DEFAULT nextval('case_events_seq')"
    )
    op.execute(
        'ALTER SEQUENCE "investigation_events_seq" RENAME TO "case_events_seq"'
    )
    op.execute(
        'ALTER SEQUENCE "investigations_short_id_seq" RENAME TO "cases_short_id_seq"'
    )

    # Tables back. Order doesn't matter for renames (Postgres tracks
    # dependencies by OID, not name) but we go reverse for clarity.
    for old, new in reversed(TABLE_RENAMES):
        op.execute(f'ALTER TABLE "{new}" RENAME TO "{old}"')

    # Recreate old-named policies on the now-old-named tables.
    for tbl in ["cases", "case_events", "case_links", "iocs", "notes", "proposals"]:
        op.execute(_audience_policy_sql(tbl))
    for tbl in ["case_runs", "case_facts", "case_iocs", "case_assets", "case_outbox"]:
        op.execute(_isolation_policy_sql(tbl))
