"""Add musehub_file_last_commits materialized table for per-file last-commit data.

Replaces the O(N_commits × manifest_blob) walk in get_file_last_commits with a
single indexed SQL read. Populated at push time by the push.file_last_commits job.

Revision ID: 0034
Revises: 0033
"""
from __future__ import annotations

from alembic import op
import sqlalchemy as sa

revision = "0034"
down_revision = "0033"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "musehub_file_last_commits",
        sa.Column("repo_id", sa.String(128), sa.ForeignKey("musehub_repos.repo_id", ondelete="CASCADE"), primary_key=True),
        sa.Column("branch", sa.String(255), primary_key=True),
        sa.Column("path", sa.Text, primary_key=True),
        sa.Column("commit_id", sa.String(128), nullable=False),
        sa.Column("commit_message", sa.Text, nullable=False, server_default=""),
        sa.Column("commit_author", sa.String(255), nullable=False, server_default=""),
        sa.Column("commit_timestamp", sa.DateTime(timezone=True), nullable=False),
        sa.Column("agent_id", sa.String(128), nullable=True),
        sa.Column("model_id", sa.String(128), nullable=True),
    )
    op.create_index(
        "ix_file_last_commits_repo_branch",
        "musehub_file_last_commits",
        ["repo_id", "branch"],
        if_not_exists=True,
    )


def downgrade() -> None:
    op.execute("DROP INDEX IF EXISTS ix_file_last_commits_repo_branch")
    bind = op.get_bind()
    if sa.inspect(bind).has_table("musehub_file_last_commits"):
        op.drop_table("musehub_file_last_commits")