"""refactor evaluations to eval_runs and trace_scores

Revision ID: 572b122d002a
Revises: e6b8a7360199
Create Date: 2026-03-02 15:26:16.767601
"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = '572b122d002a'
down_revision: Union[str, None] = 'e6b8a7360199'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Design notes:
    # - trace_scores.value is VARCHAR(255) storing all score types as strings.
    #   data_type column drives interpretation: NUMERIC -> cast to float,
    #   BOOLEAN -> "true"/"false", CATEGORICAL -> freeform.
    #   Trade-off: simpler schema vs. CAST overhead on analytics queries.
    #   At current scale the unified approach is preferred for simplicity.
    # - eval_run_id uses ON DELETE SET NULL so scores survive run deletion.
    # - Indexes target the primary access patterns: per-trace lookup,
    #   per-project+metric listing, analytics by date range.
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('eval_runs',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('project_id', sa.UUID(), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=True),
    sa.Column('target_type', sa.String(length=20), nullable=False),
    sa.Column('metric_names', postgresql.ARRAY(sa.String(length=255)), nullable=False),
    sa.Column('filters', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.Column('sampling_rate', sa.Float(), nullable=False),
    sa.Column('model', sa.String(length=255), nullable=True),
    sa.Column('status', sa.Enum('PENDING', 'RUNNING', 'COMPLETED', 'FAILED', name='evaluation_status', native_enum=False, length=20), nullable=False),
    sa.Column('total_traces', sa.Integer(), nullable=False),
    sa.Column('evaluated_count', sa.Integer(), nullable=False),
    sa.Column('error_message', sa.Text(), nullable=True),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('completed_at', sa.DateTime(timezone=True), nullable=True),
    sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_eval_runs_project', 'eval_runs', ['project_id', 'created_at'], unique=False)
    op.create_table('trace_scores',
    sa.Column('id', sa.UUID(), nullable=False),
    sa.Column('trace_id', sa.UUID(), nullable=False),
    sa.Column('project_id', sa.UUID(), nullable=False),
    sa.Column('name', sa.String(length=255), nullable=False),
    sa.Column('data_type', sa.Enum('NUMERIC', 'BOOLEAN', 'CATEGORICAL', name='score_data_type', native_enum=False, length=20), nullable=False),
    sa.Column('value', sa.String(length=255), nullable=False),
    sa.Column('source', sa.Enum('AUTOMATED', 'ANNOTATION', 'PROGRAMMATIC', name='score_source', native_enum=False, length=20), nullable=False),
    sa.Column('eval_run_id', sa.UUID(), nullable=True),
    sa.Column('author_user_id', sa.String(length=255), nullable=True),
    sa.Column('reason', sa.Text(), nullable=True),
    sa.Column('environment', sa.String(length=255), nullable=True),
    sa.Column('config_id', sa.String(length=255), nullable=True),
    sa.Column('metadata', postgresql.JSONB(astext_type=sa.Text()), nullable=False),
    sa.Column('created_at', sa.DateTime(timezone=True), nullable=False),
    sa.Column('updated_at', sa.DateTime(timezone=True), nullable=False),
    sa.ForeignKeyConstraint(['eval_run_id'], ['eval_runs.id'], ondelete='SET NULL'),
    sa.ForeignKeyConstraint(['project_id'], ['projects.id'], ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['trace_id'], ['traces.trace_id'], ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id')
    )
    op.create_index('ix_trace_scores_eval_run', 'trace_scores', ['eval_run_id'], unique=False)
    op.create_index('ix_trace_scores_project_created', 'trace_scores', ['project_id', 'created_at'], unique=False)
    op.create_index('ix_trace_scores_project_name', 'trace_scores', ['project_id', 'name'], unique=False)
    op.create_index('ix_trace_scores_trace_id', 'trace_scores', ['trace_id'], unique=False)
    op.drop_table('evaluation_results')
    op.drop_index(op.f('ix_evaluations_project_trace'), table_name='evaluations')
    op.drop_table('evaluations')
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    op.create_table('evaluation_results',
    sa.Column('id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('evaluation_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('metric_name', sa.VARCHAR(length=255), autoincrement=False, nullable=False),
    sa.Column('score', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=False),
    sa.Column('threshold', sa.DOUBLE_PRECISION(precision=53), autoincrement=False, nullable=False),
    sa.Column('success', sa.BOOLEAN(), autoincrement=False, nullable=False),
    sa.Column('reason', sa.TEXT(), autoincrement=False, nullable=True),
    sa.Column('metadata', postgresql.JSONB(astext_type=sa.Text()), autoincrement=False, nullable=False),
    sa.Column('evaluated_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=False),
    sa.ForeignKeyConstraint(['evaluation_id'], ['evaluations.id'], name=op.f('evaluation_results_evaluation_id_fkey'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('evaluation_results_pkey'))
    )
    op.create_table('evaluations',
    sa.Column('id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('trace_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('project_id', sa.UUID(), autoincrement=False, nullable=False),
    sa.Column('metric_names', postgresql.ARRAY(sa.VARCHAR()), autoincrement=False, nullable=False),
    sa.Column('status', sa.VARCHAR(length=20), autoincrement=False, nullable=False),
    sa.Column('created_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=False),
    sa.Column('completed_at', postgresql.TIMESTAMP(timezone=True), autoincrement=False, nullable=True),
    sa.ForeignKeyConstraint(['project_id'], ['projects.id'], name=op.f('evaluations_project_id_fkey'), ondelete='CASCADE'),
    sa.ForeignKeyConstraint(['trace_id'], ['traces.trace_id'], name=op.f('evaluations_trace_id_fkey'), ondelete='CASCADE'),
    sa.PrimaryKeyConstraint('id', name=op.f('evaluations_pkey'))
    )
    op.create_index(op.f('ix_evaluations_project_trace'), 'evaluations', ['project_id', 'trace_id'], unique=False)
    op.drop_index('ix_trace_scores_trace_id', table_name='trace_scores')
    op.drop_index('ix_trace_scores_project_name', table_name='trace_scores')
    op.drop_index('ix_trace_scores_project_created', table_name='trace_scores')
    op.drop_index('ix_trace_scores_eval_run', table_name='trace_scores')
    op.drop_table('trace_scores')
    op.drop_index('ix_eval_runs_project', table_name='eval_runs')
    op.drop_table('eval_runs')
    # ### end Alembic commands ###
