"""Test Registry Tables - Persistente Test-Speicherung

Revision ID: 009
Revises: 008
Create Date: 2026-02-02 10:00:00.000000

Erstellt Tabellen fuer:
- test_runs: Historie aller Test-Durchlaeufe
- test_results: Einzelne Test-Ergebnisse pro Run
- failed_tests_backlog: Persistenter Backlog fuer zu fixende Tests
- test_fixes_history: Historie aller Fix-Versuche
"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import postgresql

# revision identifiers
revision = '009'
down_revision = '008'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # test_runs: Jeder Testlauf wird gespeichert
    op.create_table(
        'test_runs',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('run_id', sa.String(50), unique=True, nullable=False),
        sa.Column('service', sa.String(100), nullable=False),
        sa.Column('framework', sa.String(50), nullable=False),
        sa.Column('started_at', sa.DateTime(), nullable=False),
        sa.Column('completed_at', sa.DateTime(), nullable=True),
        sa.Column('status', sa.String(20), nullable=False),  # queued, running, completed, failed
        sa.Column('total_tests', sa.Integer(), default=0),
        sa.Column('passed_tests', sa.Integer(), default=0),
        sa.Column('failed_tests', sa.Integer(), default=0),
        sa.Column('skipped_tests', sa.Integer(), default=0),
        sa.Column('duration_seconds', sa.Float(), default=0),
        sa.Column('git_commit', sa.String(40), nullable=True),
        sa.Column('git_branch', sa.String(100), nullable=True),
        sa.Column('triggered_by', sa.String(50), nullable=True),  # manual, ci, schedule
        sa.Column('output', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
        sa.PrimaryKeyConstraint('id')
    )

    # Indizes fuer test_runs
    op.create_index('idx_test_runs_service', 'test_runs', ['service'])
    op.create_index('idx_test_runs_started_at', 'test_runs', ['started_at'])
    op.create_index('idx_test_runs_run_id', 'test_runs', ['run_id'])

    # test_results: Einzelne Test-Ergebnisse pro Run
    op.create_table(
        'test_results',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('run_id', sa.String(50), sa.ForeignKey('test_runs.run_id', ondelete='CASCADE'), nullable=False),
        sa.Column('test_name', sa.String(500), nullable=False),
        sa.Column('test_file', sa.String(500), nullable=True),
        sa.Column('line_number', sa.Integer(), nullable=True),
        sa.Column('status', sa.String(20), nullable=False),  # passed, failed, skipped, error
        sa.Column('duration_ms', sa.Float(), nullable=True),
        sa.Column('error_message', sa.Text(), nullable=True),
        sa.Column('error_type', sa.String(100), nullable=True),
        sa.Column('output', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
        sa.PrimaryKeyConstraint('id')
    )

    # Indizes fuer test_results
    op.create_index('idx_test_results_run_id', 'test_results', ['run_id'])
    op.create_index('idx_test_results_status', 'test_results', ['status'])
    op.create_index('idx_test_results_test_name', 'test_results', ['test_name'])

    # failed_tests_backlog: Persistenter Backlog fuer Fixes
    op.create_table(
        'failed_tests_backlog',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('test_name', sa.String(500), nullable=False),
        sa.Column('test_file', sa.String(500), nullable=True),
        sa.Column('service', sa.String(100), nullable=False),
        sa.Column('framework', sa.String(50), nullable=True),
        sa.Column('error_message', sa.Text(), nullable=True),
        sa.Column('error_type', sa.String(100), nullable=True),
        sa.Column('first_failed_at', sa.DateTime(), nullable=False),
        sa.Column('last_failed_at', sa.DateTime(), nullable=False),
        sa.Column('failure_count', sa.Integer(), default=1),
        sa.Column('status', sa.String(30), default='open'),  # open, in_progress, fixed, wont_fix, flaky
        sa.Column('priority', sa.String(20), default='medium'),  # critical, high, medium, low
        sa.Column('assigned_to', sa.String(100), nullable=True),
        sa.Column('fix_suggestion', sa.Text(), nullable=True),
        sa.Column('notes', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.func.now(), onupdate=sa.func.now()),
        sa.PrimaryKeyConstraint('id'),
        sa.UniqueConstraint('test_name', 'service', name='uq_backlog_test_service')
    )

    # Indizes fuer failed_tests_backlog
    op.create_index('idx_backlog_status', 'failed_tests_backlog', ['status'])
    op.create_index('idx_backlog_service', 'failed_tests_backlog', ['service'])
    op.create_index('idx_backlog_priority', 'failed_tests_backlog', ['priority'])

    # test_fixes_history: Historie aller Fix-Versuche
    op.create_table(
        'test_fixes_history',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('backlog_id', sa.Integer(), sa.ForeignKey('failed_tests_backlog.id', ondelete='CASCADE'), nullable=False),
        sa.Column('fix_type', sa.String(50), nullable=True),  # manual, auto_claude, auto_script
        sa.Column('fix_description', sa.Text(), nullable=True),
        sa.Column('commit_hash', sa.String(40), nullable=True),
        sa.Column('success', sa.Boolean(), nullable=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
        sa.PrimaryKeyConstraint('id')
    )

    # Index fuer test_fixes_history
    op.create_index('idx_fixes_backlog_id', 'test_fixes_history', ['backlog_id'])

    # Aggregated Test Stats Tabelle (fuer schnelle Abfragen)
    op.create_table(
        'test_service_stats',
        sa.Column('id', sa.Integer(), autoincrement=True, nullable=False),
        sa.Column('service', sa.String(100), unique=True, nullable=False),
        sa.Column('total_tests', sa.Integer(), default=0),
        sa.Column('passed_tests', sa.Integer(), default=0),
        sa.Column('failed_tests', sa.Integer(), default=0),
        sa.Column('skipped_tests', sa.Integer(), default=0),
        sa.Column('pass_rate', sa.Float(), default=0.0),
        sa.Column('last_run_id', sa.String(50), nullable=True),
        sa.Column('last_run_at', sa.DateTime(), nullable=True),
        sa.Column('last_status', sa.String(20), nullable=True),
        sa.Column('updated_at', sa.DateTime(), server_default=sa.func.now(), onupdate=sa.func.now()),
        sa.PrimaryKeyConstraint('id')
    )


def downgrade() -> None:
    op.drop_table('test_service_stats')
    op.drop_table('test_fixes_history')
    op.drop_table('failed_tests_backlog')
    op.drop_table('test_results')
    op.drop_table('test_runs')
