"""Add security_events table + security columns (Security page dashboard)

Persists per-user security events (login history, recent activity, failed-login
monitoring, suspicious checks) — the store the existing log-only audit lacked.
Also adds the per-account Email-OTP 2FA toggle, a security-notification prefs
column, and an optional user-chosen trusted-device name.

Privacy: raw IPs are never stored — only a masked display form + a salted hash;
the user-agent is reduced to coarse browser/OS/device fields.

Revision ID: e2a4c6d8f0b3
Revises: d1f3a5c7e9b2
Create Date: 2026-07-07 00:00:00.000000

"""
from alembic import op
import sqlalchemy as sa


# revision identifiers, used by Alembic.
revision = "e2a4c6d8f0b3"
down_revision = "d1f3a5c7e9b2"
branch_labels = None
depends_on = None


def upgrade():
    op.create_table(
        "security_events",
        sa.Column("user_id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), nullable=False),
        sa.Column("event", sa.String(length=48), nullable=False),
        sa.Column("result", sa.String(length=16), nullable=True),
        sa.Column("method", sa.String(length=24), nullable=True),
        sa.Column("browser", sa.String(length=48), nullable=True),
        sa.Column("os", sa.String(length=48), nullable=True),
        sa.Column("device_type", sa.String(length=24), nullable=True),
        sa.Column("ip_display", sa.String(length=45), nullable=True),
        sa.Column("ip_hash", sa.CHAR(length=64), nullable=True),
        sa.Column("location", sa.String(length=80), nullable=True),
        sa.Column("detail", sa.String(length=255), nullable=True),
        sa.Column("created_at", sa.DateTime(), nullable=False),
        sa.Column("id", sa.BigInteger().with_variant(sa.Integer(), "sqlite"), autoincrement=True, nullable=False),
        sa.ForeignKeyConstraint(["user_id"], ["users.id"], name="fk_security_events_user_id"),
        sa.PrimaryKeyConstraint("id"),
    )
    with op.batch_alter_table("security_events", schema=None) as batch_op:
        batch_op.create_index("ix_security_events_user_created", ["user_id", "created_at"], unique=False)
        batch_op.create_index("ix_security_events_event", ["event"], unique=False)

    with op.batch_alter_table("users", schema=None) as batch_op:
        batch_op.add_column(sa.Column("two_factor_enabled", sa.Boolean(), server_default="0", nullable=False))
        batch_op.add_column(sa.Column("security_notification_prefs", sa.Text(), nullable=True))

    with op.batch_alter_table("trusted_devices", schema=None) as batch_op:
        batch_op.add_column(sa.Column("name", sa.String(length=80), nullable=True))


def downgrade():
    with op.batch_alter_table("trusted_devices", schema=None) as batch_op:
        batch_op.drop_column("name")

    with op.batch_alter_table("users", schema=None) as batch_op:
        batch_op.drop_column("security_notification_prefs")
        batch_op.drop_column("two_factor_enabled")

    op.drop_table("security_events")