"""breakage: create musehub_intel_breakage_issues and musehub_intel_breakage_meta

``musehub_intel_breakage_issues`` stores one row per stale-import detected at
push time by ``BreakageProvider``.  The stable ``issue_id`` hash
(``blob_id(repo_id:file_path:symbol_name:issue_type)``) makes upserts
idempotent — re-running the provider on the same snapshot produces the same IDs.

``musehub_intel_breakage_meta`` holds aggregate stats (total, warning/error
counts, file count) so stat chips never require a COUNT query on every load.

Revision ID: 0017
Revises: 0016
"""

from alembic import op
import sqlalchemy as sa

revision = "0017"
down_revision = "0016"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.create_table(
        "musehub_intel_breakage_issues",
        sa.Column("issue_id", sa.String(128), primary_key=True),
        sa.Column(
            "repo_id",
            sa.String(128),
            sa.ForeignKey("musehub_repos.repo_id", ondelete="CASCADE"),
            nullable=False,
        ),
        sa.Column("file_path", sa.String(512), nullable=False),
        sa.Column("issue_type", sa.String(64), nullable=False),
        sa.Column("description", sa.Text, nullable=False),
        sa.Column("severity", sa.String(32), nullable=False, server_default="warning"),
        sa.Column("ref", sa.String(128), nullable=False),
    )
    op.create_index(
        "ix_intel_breakage_issues_repo",
        "musehub_intel_breakage_issues",
        ["repo_id"],
    )
    op.create_index(
        "ix_intel_breakage_issues_repo_type",
        "musehub_intel_breakage_issues",
        ["repo_id", "issue_type"],
    )

    op.create_table(
        "musehub_intel_breakage_meta",
        sa.Column(
            "repo_id",
            sa.String(128),
            sa.ForeignKey("musehub_repos.repo_id", ondelete="CASCADE"),
            primary_key=True,
        ),
        sa.Column("total_issues", sa.Integer, nullable=False, server_default="0"),
        sa.Column("warning_count", sa.Integer, nullable=False, server_default="0"),
        sa.Column("error_count", sa.Integer, nullable=False, server_default="0"),
        sa.Column("file_count", sa.Integer, nullable=False, server_default="0"),
        sa.Column("ref", sa.String(128), nullable=False),
    )


def downgrade() -> None:
    op.drop_index("ix_intel_breakage_issues_repo_type", table_name="musehub_intel_breakage_issues")
    op.drop_index("ix_intel_breakage_issues_repo", table_name="musehub_intel_breakage_issues")
    op.drop_table("musehub_intel_breakage_issues")
    op.drop_table("musehub_intel_breakage_meta")