"""Add analytics models for tracking AI checks, user activity, and message stats

Revision ID: 20260117_analytics
Revises: 20260117_add_database_indexes
Create Date: 2026-01-17 19:30:00.000000
"""

from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

# revision identifiers, used by Alembic.
revision: str = "20260117_analytics"
down_revision: Union[str, None] = "20260117_add_database_indexes"
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    # Create ai_checks table
    op.create_table(
        "ai_checks",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("guild_id", sa.BigInteger(), nullable=False),
        sa.Column("user_id", sa.BigInteger(), nullable=False),
        sa.Column("channel_id", sa.BigInteger(), nullable=False),
        sa.Column("message_id", sa.BigInteger(), nullable=False),
        sa.Column("flagged", sa.Boolean(), nullable=False),
        sa.Column("confidence", sa.Float(), nullable=False),
        sa.Column("category", sa.String(50), nullable=True),
        sa.Column("severity", sa.Integer(), nullable=False),
        sa.Column("response_time_ms", sa.Float(), nullable=False),
        sa.Column("provider", sa.String(20), nullable=False),
        sa.Column("is_false_positive", sa.Boolean(), nullable=False),
        sa.Column("reviewed_by", sa.BigInteger(), nullable=True),
        sa.Column("reviewed_at", sa.DateTime(timezone=True), nullable=True),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
        sa.PrimaryKeyConstraint("id"),
    )

    # Add indexes for ai_checks
    op.create_index("ix_ai_checks_guild_id", "ai_checks", ["guild_id"])
    op.create_index("ix_ai_checks_user_id", "ai_checks", ["user_id"])
    op.create_index("ix_ai_checks_is_false_positive", "ai_checks", ["is_false_positive"])
    op.create_index("ix_ai_checks_created_at", "ai_checks", ["created_at"])
    op.create_index("ix_ai_checks_guild_created", "ai_checks", ["guild_id", "created_at"])

    # Create message_activity table
    op.create_table(
        "message_activity",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("guild_id", sa.BigInteger(), nullable=False),
        sa.Column("date", sa.DateTime(timezone=True), nullable=False),
        sa.Column("total_messages", sa.Integer(), nullable=False),
        sa.Column("active_users", sa.Integer(), nullable=False),
        sa.Column("new_joins", sa.Integer(), nullable=False),
        sa.Column("automod_triggers", sa.Integer(), nullable=False),
        sa.Column("ai_checks", sa.Integer(), nullable=False),
        sa.Column("manual_actions", sa.Integer(), nullable=False),
        sa.PrimaryKeyConstraint("id"),
    )

    # Add indexes for message_activity
    op.create_index("ix_message_activity_guild_id", "message_activity", ["guild_id"])
    op.create_index("ix_message_activity_date", "message_activity", ["date"])
    op.create_index(
        "ix_message_activity_guild_date", "message_activity", ["guild_id", "date"], unique=True
    )

    # Create user_activity table
    op.create_table(
        "user_activity",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("guild_id", sa.BigInteger(), nullable=False),
        sa.Column("user_id", sa.BigInteger(), nullable=False),
        sa.Column("username", sa.String(100), nullable=False),
        sa.Column("first_seen", sa.DateTime(timezone=True), nullable=False),
        sa.Column("last_seen", sa.DateTime(timezone=True), nullable=False),
        sa.Column("last_message", sa.DateTime(timezone=True), nullable=True),
        sa.Column("message_count", sa.Integer(), nullable=False),
        sa.Column("command_count", sa.Integer(), nullable=False),
        sa.Column("strike_count", sa.Integer(), nullable=False),
        sa.Column("warning_count", sa.Integer(), nullable=False),
        sa.Column("kick_count", sa.Integer(), nullable=False),
        sa.Column("ban_count", sa.Integer(), nullable=False),
        sa.Column("timeout_count", sa.Integer(), nullable=False),
        sa.Column(
            "created_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
        sa.Column(
            "updated_at", sa.DateTime(timezone=True), server_default=sa.func.now(), nullable=False
        ),
        sa.PrimaryKeyConstraint("id"),
    )

    # Add indexes for user_activity
    op.create_index("ix_user_activity_guild_id", "user_activity", ["guild_id"])
    op.create_index("ix_user_activity_user_id", "user_activity", ["user_id"])
    op.create_index(
        "ix_user_activity_guild_user", "user_activity", ["guild_id", "user_id"], unique=True
    )
    op.create_index("ix_user_activity_last_seen", "user_activity", ["last_seen"])
    op.create_index("ix_user_activity_strike_count", "user_activity", ["strike_count"])


def downgrade() -> None:
    # Drop tables in reverse order
    op.drop_table("user_activity")
    op.drop_table("message_activity")
    op.drop_table("ai_checks")