"""Add symbol vitals + coupling tables; add message/commit_branch to history entries.

Pre-computes coupling and symbol vitals at push time so the symbol detail
page is a set of simple indexed reads instead of expensive aggregations.

New tables:
  musehub_symbol_vitals    — first_introduced, change_count, version_count, op breakdown
  musehub_symbol_coupling  — (address, co_address, shared_commits)

New columns on musehub_symbol_history_entries:
  message        — commit message (denormalized from musehub_commits)
  commit_branch  — branch the commit landed on

Revision ID: 0031
Revises: 0030
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "0031"
down_revision = "0030"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column(
        "musehub_symbol_history_entries",
        sa.Column("message", sa.Text, nullable=True),
    )
    op.add_column(
        "musehub_symbol_history_entries",
        sa.Column("commit_branch", sa.String(512), nullable=True),
    )

    op.create_table(
        "musehub_symbol_vitals",
        sa.Column("repo_id", sa.String(128), sa.ForeignKey("musehub_repos.repo_id", ondelete="CASCADE"), primary_key=True, nullable=False),
        sa.Column("address", sa.String(512), primary_key=True, nullable=False),
        sa.Column("first_introduced", sa.DateTime(timezone=True), nullable=True),
        sa.Column("change_count", sa.Integer, nullable=False, server_default="0"),
        sa.Column("version_count", sa.Integer, nullable=False, server_default="0"),
        sa.Column("op_add", sa.Integer, nullable=False, server_default="0"),
        sa.Column("op_modify", sa.Integer, nullable=False, server_default="0"),
        sa.Column("op_delete", sa.Integer, nullable=False, server_default="0"),
        sa.Column("op_move", sa.Integer, nullable=False, server_default="0"),
    )
    op.create_index("ix_symbol_vitals_repo", "musehub_symbol_vitals", ["repo_id"])

    op.create_table(
        "musehub_symbol_coupling",
        sa.Column("repo_id", sa.String(128), sa.ForeignKey("musehub_repos.repo_id", ondelete="CASCADE"), primary_key=True, nullable=False),
        sa.Column("address", sa.String(512), primary_key=True, nullable=False),
        sa.Column("co_address", sa.String(512), primary_key=True, nullable=False),
        sa.Column("shared_commits", sa.Integer, nullable=False, server_default="0"),
    )
    op.create_index("ix_symbol_coupling_repo_address", "musehub_symbol_coupling", ["repo_id", "address"])


def downgrade() -> None:
    from sqlalchemy import text
    op.execute(text("DROP INDEX IF EXISTS ix_symbol_coupling_repo_address"))
    op.execute(text("DROP TABLE IF EXISTS musehub_symbol_coupling"))
    op.execute(text("DROP INDEX IF EXISTS ix_symbol_vitals_repo"))
    op.execute(text("DROP TABLE IF EXISTS musehub_symbol_vitals"))
    op.execute(text("ALTER TABLE musehub_symbol_history_entries DROP COLUMN IF EXISTS commit_branch"))
    op.execute(text("ALTER TABLE musehub_symbol_history_entries DROP COLUMN IF EXISTS message"))