"""normalized_symbol_intel_tables

Replaces three unbounded JSON blobs in musehub_intel_results with normalized
relational tables:

    musehub_symbol_history_entries  — one row per (repo_id, address, commit_id)
    musehub_symbol_intel            — one row per (repo_id, address)
    musehub_hash_occurrence_entries — one row per (content_id, repo_id, address)

code.symbol_history, code.per_symbol_intel, and code.hash_occurrence rows in
musehub_intel_results are deleted on upgrade — they will be rebuilt
incrementally on the next push to each repo.

Revision ID: 0003
Revises: 0002
Create Date: 2026-04-29 20:45:00.000000+00:00
"""
from __future__ import annotations

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

revision: str = '0003'
down_revision: Union[str, None] = '0002'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    op.create_table(
        'musehub_symbol_history_entries',
        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('commit_id', sa.String(128), primary_key=True, nullable=False),
        sa.Column('committed_at', sa.DateTime(timezone=True), nullable=False),
        sa.Column('author', sa.String(256), nullable=True),
        sa.Column('op', sa.String(32), nullable=False),
        sa.Column('content_id', sa.String(128), nullable=True),
    )
    op.create_index('ix_symbol_history_repo_address', 'musehub_symbol_history_entries', ['repo_id', 'address'])
    op.create_index('ix_symbol_history_repo_address_ts', 'musehub_symbol_history_entries', ['repo_id', 'address', 'committed_at'])

    op.create_table(
        'musehub_symbol_intel',
        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('churn', sa.Integer, nullable=False, server_default='0'),
        sa.Column('churn_30d', sa.Integer, nullable=False, server_default='0'),
        sa.Column('churn_90d', sa.Integer, nullable=False, server_default='0'),
        sa.Column('blast', sa.Integer, nullable=False, server_default='0'),
        sa.Column('blast_direct', sa.Integer, nullable=False, server_default='0'),
        sa.Column('blast_cross', sa.Integer, nullable=False, server_default='0'),
        sa.Column('blast_top', sa.ARRAY(sa.Text), nullable=False, server_default='{}'),
        sa.Column('last_changed', sa.DateTime(timezone=True), nullable=True),
        sa.Column('last_author', sa.String(256), nullable=True),
        sa.Column('author_count', sa.Integer, nullable=False, server_default='0'),
        sa.Column('gravity', sa.Float, nullable=False, server_default='0.0'),
        sa.Column('weekly', sa.ARRAY(sa.Integer), nullable=False, server_default='{}'),
    )
    op.create_index('ix_symbol_intel_repo_churn', 'musehub_symbol_intel', ['repo_id', 'churn'])
    op.create_index('ix_symbol_intel_repo_gravity', 'musehub_symbol_intel', ['repo_id', 'gravity'])

    op.create_table(
        'musehub_hash_occurrence_entries',
        sa.Column('content_id', sa.String(128), primary_key=True, nullable=False),
        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),
    )
    op.create_index('ix_hash_occurrence_repo_content', 'musehub_hash_occurrence_entries', ['repo_id', 'content_id'])

    # Remove stale blob types — will be rebuilt from normalized tables on next push.
    op.execute("""
        DELETE FROM musehub_intel_results
        WHERE intel_type IN (
            'code.symbol_history',
            'code.per_symbol_intel',
            'code.hash_occurrence'
        )
    """)


def downgrade() -> None:
    op.drop_table('musehub_hash_occurrence_entries')
    op.drop_table('musehub_symbol_intel')
    op.drop_table('musehub_symbol_history_entries')