"""note_links.target_id — resolve [[links]] to a note, not to a string (M13 step 1)

Revision ID: 0023
Revises: 0022
Create Date: 2026-08-22

A wiki-link stored only as normalized TEXT means a note's name IS the edge: rename
the note and every inbound link stops matching. The old answer was to rewrite the
`[[Old Name]]` text inside every note that linked to it — workable while an explicit
title existed to hold still, untenable once a note's name is just its first body
line (M13).

`target_norm` stays: it is what an UNRESOLVED link carries, since linking to a note
that doesn't exist yet is a supported way to create one.

The backfill is safe to run bluntly because note_links is DERIVED data — every row
is recomputed from the source body on the next save regardless.
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

revision = "0023"
down_revision = "0022"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "note_links",
        sa.Column("target_id", postgresql.UUID(as_uuid=True), nullable=True),
    )
    op.create_foreign_key(
        "fk_note_links_target",
        "note_links",
        "notes",
        ["target_id"],
        ["id"],
        # A deleted target un-resolves its inbound links rather than deleting them:
        # the link text is still in the source's body, and it should read as pointing
        # at something that isn't there — which is also what lets it re-resolve if a
        # note of that name appears again.
        ondelete="SET NULL",
    )
    op.create_index("ix_note_links_target_id", "note_links", ["target_id"])

    # Resolve what can be resolved right now, scoped to the source's owner so a link
    # can never bind to another user's note.
    op.execute(
        """
        UPDATE note_links AS nl
           SET target_id = t.id
          FROM notes AS src, notes AS t
         WHERE nl.source_id = src.id
           AND t.owner_id = src.owner_id
           AND t.deleted_at IS NULL
           AND lower(btrim(t.display_title)) = nl.target_norm
           AND t.id <> src.id
        """
    )


def downgrade() -> None:
    op.drop_index("ix_note_links_target_id", table_name="note_links")
    op.drop_constraint("fk_note_links_target", "note_links", type_="foreignkey")
    op.drop_column("note_links", "target_id")