"""Rebuild rel_relationships with the correct schema.

Revision ID: 097
Revises: 096
Create Date: 2026-05-01

═══════════════════════════════════════════════════════════════════════
The bug
═══════════════════════════════════════════════════════════════════════

Both prod and local TiDB carry a ``rel_relationships`` table whose
column names do NOT match the ORM model:

  Existing schema (wrong):
    id, from_entity_type, from_id, to_entity_type, to_id,
    relation_type, metadata, created_at

  ORM expects (per ``server/app/domains/relationships/models.py``):
    id, from_type, from_id, from_label, to_type, to_id, to_label,
    rel_type, attributes, source, weight, first_seen_at,
    last_seen_at, deleted_at, created_at, updated_at
    + uq_rel_edge unique constraint
    + idx_rel_from / idx_rel_to / idx_rel_type / idx_rel_last_seen

The mismatch causes the ``relationships.incremental_sync`` cron to fail
every 5 minutes in prod with::

    Unknown column 'rel_relationships.from_type' in 'where clause'

Migration 091 was supposed to create the table, but its
``if not _table_exists(bind, 'rel_relationships'):`` guard skipped the
create — the table was already there from an earlier
``Base.metadata.create_all()`` invocation in dev/CI that produced the
old shape (likely from a since-removed model definition or a migration
that has been deleted).

═══════════════════════════════════════════════════════════════════════
What this migration does
═══════════════════════════════════════════════════════════════════════

Detect the wrong-shape table and rebuild it. Specifically:

1. If ``rel_relationships`` exists AND has ``from_entity_type`` (the
   wrong-shape signature), DROP it and recreate from scratch with the
   correct DDL (matching migration 091's blueprint).
2. If the table doesn't exist at all, create it (defensive — same DDL).
3. If the table exists with the correct shape (``from_type`` present),
   no-op.

Data loss is fine: the table is a materialised cache, fully
regenerated by the ``relationships.full_sync`` cron at 04:30 UTC daily
and incrementally every 5 minutes. We verified row count = 0 in both
prod (2026-05-01) and local before writing this migration, so we are
not even discarding any cached edges.

═══════════════════════════════════════════════════════════════════════
Idempotency
═══════════════════════════════════════════════════════════════════════

Re-running ``alembic upgrade head`` is safe — the column-presence check
short-circuits to a no-op once the rebuild has run.
"""
from __future__ import annotations

import logging

import sqlalchemy as sa

from alembic import op

logger = logging.getLogger(__name__)

revision = "097"
down_revision = "096"
branch_labels = None
depends_on = None

_TABLE = "rel_relationships"
_WRONG_SIGNATURE_COLUMN = "from_entity_type"
_CORRECT_SIGNATURE_COLUMN = "from_type"


def _table_exists(bind, name: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.TABLES "
            "WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = :n"
        ),
        {"n": name},
    ).scalar() is not None


def _column_exists(bind, table: str, column: str) -> bool:
    return bind.execute(
        sa.text(
            "SELECT 1 FROM information_schema.COLUMNS "
            "WHERE TABLE_SCHEMA = DATABASE() "
            "AND TABLE_NAME = :t AND COLUMN_NAME = :c"
        ),
        {"t": table, "c": column},
    ).scalar() is not None


def _create_correct_table(bind) -> None:
    """Create ``rel_relationships`` with the schema the ORM expects.

    Mirrors the create_table block in migration 091; copied (not
    imported) so a future edit to that file can't drift this rebuild
    out from under prod.
    """
    bind.execute(sa.text(
        """
        CREATE TABLE rel_relationships (
            id CHAR(36) NOT NULL,
            from_type VARCHAR(32) NOT NULL,
            from_id VARCHAR(64) NOT NULL,
            from_label VARCHAR(255) NULL,
            to_type VARCHAR(32) NOT NULL,
            to_id VARCHAR(64) NOT NULL,
            to_label VARCHAR(255) NULL,
            rel_type VARCHAR(48) NOT NULL,
            attributes JSON NULL,
            source VARCHAR(48) NOT NULL DEFAULT 'sync',
            weight FLOAT NOT NULL DEFAULT 1.0,
            first_seen_at DATETIME(6) NOT NULL,
            last_seen_at DATETIME(6) NOT NULL,
            deleted_at DATETIME(6) NULL,
            created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
            updated_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)
                ON UPDATE CURRENT_TIMESTAMP(6),
            PRIMARY KEY (id),
            UNIQUE KEY uq_rel_edge (from_type, from_id, to_type, to_id, rel_type),
            KEY idx_rel_from (from_type, from_id),
            KEY idx_rel_to (to_type, to_id),
            KEY idx_rel_type (rel_type),
            KEY idx_rel_last_seen (last_seen_at)
        )
        """
    ))


def upgrade() -> None:
    bind = op.get_bind()

    if _table_exists(bind, _TABLE):
        if _column_exists(bind, _TABLE, _CORRECT_SIGNATURE_COLUMN):
            # Already correct — nothing to do.
            logger.info(
                "rel_relationships already has the correct schema; skipping",
            )
            return
        if _column_exists(bind, _TABLE, _WRONG_SIGNATURE_COLUMN):
            # Wrong shape — drop and recreate. Cache table, regenerated
            # by relationships.full_sync at 04:30 UTC daily and the
            # incremental sync every 5 minutes; row count was 0 in
            # prod + local at the time of writing.
            logger.warning(
                "rel_relationships has the wrong schema — dropping and "
                "rebuilding with correct columns",
            )
            bind.execute(sa.text(f"DROP TABLE {_TABLE}"))
            _create_correct_table(bind)
            return
        # Unknown shape — refuse to silently do anything destructive.
        # Surfacing the error means an operator looks at the table
        # before we touch it.
        raise RuntimeError(
            f"{_TABLE} exists but has neither the wrong-shape signature "
            f"({_WRONG_SIGNATURE_COLUMN!r}) nor the correct-shape "
            f"signature ({_CORRECT_SIGNATURE_COLUMN!r}); refusing to "
            "rebuild blind. Inspect the table manually."
        )

    # Table doesn't exist at all — create it.
    _create_correct_table(bind)


def downgrade() -> None:
    # Dropping the rebuilt table on downgrade would be destructive and
    # nothing useful is restored (the prior schema was the broken one
    # this migration exists to fix). Intentional no-op — production
    # downgrade-back-to-broken is not a path anyone wants.
    pass