"""Alerts Agent Tables

Erstellt die Tabellen für:
- alert_topics: Feed-Quellen (Google Alerts, RSS)
- alert_items: Einzelne Alerts/Artikel
- alert_rules: Filterregeln
- alert_profiles: Nutzer-Profile für Relevanz-Scoring

Revision ID: 008
Revises: 007
Create Date: 2026-01-15 21:00:00

"""
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 = '008'
down_revision: Union[str, None] = '007'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


# Enum-Typen
alert_source_enum = postgresql.ENUM(
    'google_alerts_rss',
    'google_alerts_email',
    'rss_feed',
    'webhook',
    'manual',
    name='alertsourceenum',
    create_type=False
)

alert_status_enum = postgresql.ENUM(
    'new',
    'processed',
    'duplicate',
    'scored',
    'reviewed',
    'archived',
    name='alertstatusenum',
    create_type=False
)

relevance_decision_enum = postgresql.ENUM(
    'KEEP',
    'DROP',
    'REVIEW',
    name='relevancedecisionenum',
    create_type=False
)

feed_type_enum = postgresql.ENUM(
    'rss',
    'email',
    'webhook',
    name='feedtypeenum',
    create_type=False
)

rule_action_enum = postgresql.ENUM(
    'keep',
    'drop',
    'tag',
    'email',
    'webhook',
    'slack',
    name='ruleactionenum',
    create_type=False
)


def upgrade() -> None:
    # Enum-Typen erstellen
    alert_source_enum.create(op.get_bind(), checkfirst=True)
    alert_status_enum.create(op.get_bind(), checkfirst=True)
    relevance_decision_enum.create(op.get_bind(), checkfirst=True)
    feed_type_enum.create(op.get_bind(), checkfirst=True)
    rule_action_enum.create(op.get_bind(), checkfirst=True)

    # alert_topics Tabelle
    op.create_table(
        'alert_topics',
        sa.Column('id', sa.String(36), primary_key=True),
        sa.Column('user_id', sa.String(36), nullable=True, index=True),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('description', sa.Text(), default=''),
        sa.Column('feed_url', sa.String(2000), nullable=True),
        sa.Column(
            'feed_type',
            feed_type_enum,
            nullable=False,
            server_default='rss'
        ),
        sa.Column('is_active', sa.Boolean(), default=True, index=True),
        sa.Column('fetch_interval_minutes', sa.Integer(), default=60),
        sa.Column('last_fetched_at', sa.DateTime(), nullable=True),
        sa.Column('last_fetch_error', sa.Text(), nullable=True),
        sa.Column('total_items_fetched', sa.Integer(), default=0),
        sa.Column('items_kept', sa.Integer(), default=0),
        sa.Column('items_dropped', sa.Integer(), default=0),
        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()
        ),
    )

    # alert_items Tabelle
    op.create_table(
        'alert_items',
        sa.Column('id', sa.String(36), primary_key=True),
        sa.Column(
            'topic_id',
            sa.String(36),
            sa.ForeignKey('alert_topics.id', ondelete='CASCADE'),
            nullable=False,
            index=True
        ),
        sa.Column('title', sa.Text(), nullable=False),
        sa.Column('url', sa.String(2000), nullable=False),
        sa.Column('snippet', sa.Text(), default=''),
        sa.Column('article_text', sa.Text(), nullable=True),
        sa.Column('lang', sa.String(10), default='de'),
        sa.Column('published_at', sa.DateTime(), nullable=True, index=True),
        sa.Column('fetched_at', sa.DateTime(), server_default=sa.func.now(), index=True),
        sa.Column('processed_at', sa.DateTime(), nullable=True),
        sa.Column(
            'source',
            alert_source_enum,
            nullable=False,
            server_default='google_alerts_rss'
        ),
        sa.Column('url_hash', sa.String(64), unique=True, nullable=False, index=True),
        sa.Column('content_hash', sa.String(64), nullable=True),
        sa.Column('canonical_url', sa.String(2000), nullable=True),
        sa.Column(
            'status',
            alert_status_enum,
            nullable=False,
            server_default='new',
            index=True
        ),
        sa.Column('cluster_id', sa.String(36), nullable=True),
        sa.Column('relevance_score', sa.Float(), nullable=True),
        sa.Column('relevance_decision', relevance_decision_enum, nullable=True, index=True),
        sa.Column('relevance_reasons', sa.JSON(), default=list),
        sa.Column('relevance_summary', sa.Text(), nullable=True),
        sa.Column('scored_by_model', sa.String(100), nullable=True),
        sa.Column('scored_at', sa.DateTime(), nullable=True),
        sa.Column('user_marked_relevant', sa.Boolean(), nullable=True),
        sa.Column('user_tags', sa.JSON(), default=list),
        sa.Column('user_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()
        ),
    )

    # Composite Indexes für alert_items
    op.create_index(
        'ix_alert_items_topic_status',
        'alert_items',
        ['topic_id', 'status']
    )
    op.create_index(
        'ix_alert_items_topic_decision',
        'alert_items',
        ['topic_id', 'relevance_decision']
    )

    # alert_rules Tabelle
    op.create_table(
        'alert_rules',
        sa.Column('id', sa.String(36), primary_key=True),
        sa.Column(
            'topic_id',
            sa.String(36),
            sa.ForeignKey('alert_topics.id', ondelete='CASCADE'),
            nullable=True,
            index=True
        ),
        sa.Column('user_id', sa.String(36), nullable=True, index=True),
        sa.Column('name', sa.String(255), nullable=False),
        sa.Column('description', sa.Text(), default=''),
        sa.Column('conditions', sa.JSON(), nullable=False, default=list),
        sa.Column(
            'action_type',
            rule_action_enum,
            nullable=False,
            server_default='keep'
        ),
        sa.Column('action_config', sa.JSON(), default=dict),
        sa.Column('priority', sa.Integer(), default=0, index=True),
        sa.Column('is_active', sa.Boolean(), default=True, index=True),
        sa.Column('match_count', sa.Integer(), default=0),
        sa.Column('last_matched_at', sa.DateTime(), 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()
        ),
    )

    # alert_profiles Tabelle
    op.create_table(
        'alert_profiles',
        sa.Column('id', sa.String(36), primary_key=True),
        sa.Column('user_id', sa.String(36), unique=True, nullable=True, index=True),
        sa.Column('name', sa.String(255), default='Default'),
        sa.Column('priorities', sa.JSON(), default=list),
        sa.Column('exclusions', sa.JSON(), default=list),
        sa.Column('positive_examples', sa.JSON(), default=list),
        sa.Column('negative_examples', sa.JSON(), default=list),
        sa.Column('policies', sa.JSON(), default=dict),
        sa.Column('total_scored', sa.Integer(), default=0),
        sa.Column('total_kept', sa.Integer(), default=0),
        sa.Column('total_dropped', sa.Integer(), default=0),
        sa.Column('accuracy_estimate', sa.Float(), 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()
        ),
    )


def downgrade() -> None:
    # Tabellen löschen (umgekehrte Reihenfolge wegen Foreign Keys)
    op.drop_table('alert_profiles')
    op.drop_table('alert_rules')
    op.drop_index('ix_alert_items_topic_decision', 'alert_items')
    op.drop_index('ix_alert_items_topic_status', 'alert_items')
    op.drop_table('alert_items')
    op.drop_table('alert_topics')

    # Enum-Typen löschen
    rule_action_enum.drop(op.get_bind(), checkfirst=True)
    feed_type_enum.drop(op.get_bind(), checkfirst=True)
    relevance_decision_enum.drop(op.get_bind(), checkfirst=True)
    alert_status_enum.drop(op.get_bind(), checkfirst=True)
    alert_source_enum.drop(op.get_bind(), checkfirst=True)
