"""Make commits and snapshots globally content-addressed — remove repo_id.

Commits and snapshots are content-addressed objects identified by their SHA-256
hash alone.  They do not belong to any repo.  Repo membership is tracked in
separate ref tables (musehub_commit_refs, musehub_snapshot_refs), exactly
mirroring how musehub_object_refs tracks object membership.

Changes:
  - Drop repo_id FK + composite indexes from musehub_commits
  - Drop repo_id FK + index from musehub_snapshots
  - Create musehub_commit_refs (repo_id, commit_id)
  - Create musehub_snapshot_refs (repo_id, snapshot_id)
  - Backfill both ref tables from existing rows
"""

from __future__ import annotations

import sqlalchemy as sa
from alembic import op

revision = "0058"
down_revision = "0057"
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ── 1. Create musehub_commit_refs ────────────────────────────────────────
    op.create_table(
        "musehub_commit_refs",
        sa.Column("repo_id",    sa.String(128), sa.ForeignKey("musehub_repos.repo_id",    ondelete="CASCADE"), primary_key=True),
        sa.Column("commit_id",  sa.String(128), sa.ForeignKey("musehub_commits.commit_id", ondelete="CASCADE"), primary_key=True),
        sa.Column("created_at", sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
    )
    op.create_index("ix_musehub_commit_refs_repo_id", "musehub_commit_refs", ["repo_id"])

    # ── 2. Create musehub_snapshot_refs ──────────────────────────────────────
    op.create_table(
        "musehub_snapshot_refs",
        sa.Column("repo_id",     sa.String(128), sa.ForeignKey("musehub_repos.repo_id",        ondelete="CASCADE"), primary_key=True),
        sa.Column("snapshot_id", sa.String(128), sa.ForeignKey("musehub_snapshots.snapshot_id", ondelete="CASCADE"), primary_key=True),
        sa.Column("created_at",  sa.DateTime(timezone=True), nullable=False, server_default=sa.func.now()),
    )
    op.create_index("ix_musehub_snapshot_refs_repo_id", "musehub_snapshot_refs", ["repo_id"])

    # ── 3. Backfill commit refs from existing musehub_commits rows ────────────
    op.execute(
        """
        INSERT INTO musehub_commit_refs (repo_id, commit_id, created_at)
        SELECT repo_id, commit_id, now()
        FROM musehub_commits
        ON CONFLICT DO NOTHING
        """
    )

    # ── 4. Backfill snapshot refs from existing musehub_snapshots rows ────────
    op.execute(
        """
        INSERT INTO musehub_snapshot_refs (repo_id, snapshot_id, created_at)
        SELECT repo_id, snapshot_id, now()
        FROM musehub_snapshots
        ON CONFLICT DO NOTHING
        """
    )

    # ── 5. Drop composite indexes on musehub_commits ─────────────────────────
    op.drop_index("ix_musehub_commits_repo_branch",    table_name="musehub_commits")
    op.drop_index("ix_musehub_commits_repo_timestamp", table_name="musehub_commits")

    # ── 6. Drop repo_id FK + column from musehub_commits ─────────────────────
    op.drop_constraint("musehub_commits_repo_id_fkey", "musehub_commits", type_="foreignkey")
    op.drop_index("ix_musehub_commits_repo_id", table_name="musehub_commits")
    op.drop_column("musehub_commits", "repo_id")

    # ── 7. Drop repo_id FK + column from musehub_snapshots ───────────────────
    op.drop_constraint("musehub_snapshots_repo_id_fkey", "musehub_snapshots", type_="foreignkey")
    op.drop_index("ix_musehub_snapshots_repo_id", table_name="musehub_snapshots")
    op.drop_column("musehub_snapshots", "repo_id")


def downgrade() -> None:
    # Re-add repo_id to musehub_snapshots (data loss — refs table is lost)
    op.add_column("musehub_snapshots", sa.Column("repo_id", sa.String(128), nullable=True))
    op.create_foreign_key(
        "musehub_snapshots_repo_id_fkey", "musehub_snapshots",
        "musehub_repos", ["repo_id"], ["repo_id"], ondelete="CASCADE",
    )
    op.create_index("ix_musehub_snapshots_repo_id", "musehub_snapshots", ["repo_id"])

    # Re-add repo_id to musehub_commits
    op.add_column("musehub_commits", sa.Column("repo_id", sa.String(128), nullable=True))
    op.create_foreign_key(
        "musehub_commits_repo_id_fkey", "musehub_commits",
        "musehub_repos", ["repo_id"], ["repo_id"], ondelete="CASCADE",
    )
    op.create_index("ix_musehub_commits_repo_id",       "musehub_commits", ["repo_id"])
    op.create_index("ix_musehub_commits_repo_branch",   "musehub_commits", ["repo_id", "branch"])
    op.create_index("ix_musehub_commits_repo_timestamp","musehub_commits", ["repo_id", "timestamp"])

    op.drop_index("ix_musehub_snapshot_refs_repo_id", table_name="musehub_snapshot_refs")
    op.drop_table("musehub_snapshot_refs")
    op.drop_index("ix_musehub_commit_refs_repo_id", table_name="musehub_commit_refs")
    op.drop_table("musehub_commit_refs")