"""create visitor monitor tables

Revision ID: a2c4e6f8b1d3
Revises: f1a3b7c5d9e2
Create Date: 2026-05-02 12:00:00.000000

Creates the four Visitor Monitor tables: vm_campaigns, vm_sessions,
vm_events, vm_settings. Each CREATE is idempotent so re-running the
migration on an already-upgraded DB is safe.
"""
from typing import Sequence, Union

import sqlalchemy as sa
from alembic import op

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


def _has_table(table: str) -> bool:
    bind = op.get_bind()
    inspector = sa.inspect(bind)
    try:
        return inspector.has_table(table)
    except Exception:  # noqa: BLE001
        return False


def _safe_create_index(name: str, table: str, cols: list[str]) -> None:
    try:
        op.create_index(name, table, cols, unique=False)
    except Exception:  # noqa: BLE001
        pass


def upgrade() -> None:
    # vm_campaigns
    if not _has_table("vm_campaigns"):
        op.create_table(
            "vm_campaigns",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("name", sa.String(length=255), nullable=False),
            sa.Column("utm_source", sa.String(length=100), nullable=False),
            sa.Column("utm_medium", sa.String(length=100), nullable=True),
            sa.Column("utm_campaign", sa.String(length=100), nullable=False),
            sa.Column("utm_term", sa.String(length=100), nullable=True),
            sa.Column("utm_content", sa.String(length=100), nullable=True),
            sa.Column(
                "landing_page",
                sa.String(length=500),
                nullable=False,
                server_default="/",
            ),
            sa.Column("target_url", sa.String(length=1000), nullable=True),
            sa.Column("start_date", sa.DateTime(timezone=True), nullable=True),
            sa.Column("end_date", sa.DateTime(timezone=True), nullable=True),
            sa.Column(
                "status",
                sa.String(length=20),
                nullable=False,
                server_default="active",
            ),
            sa.Column("notes", sa.Text(), 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"),
        )
        _safe_create_index("ix_vm_campaigns_utm_source", "vm_campaigns", ["utm_source"])
        _safe_create_index("ix_vm_campaigns_utm_medium", "vm_campaigns", ["utm_medium"])
        _safe_create_index(
            "ix_vm_campaigns_utm_campaign", "vm_campaigns", ["utm_campaign"]
        )
        _safe_create_index("ix_vm_campaigns_status", "vm_campaigns", ["status"])

    # vm_sessions
    if not _has_table("vm_sessions"):
        op.create_table(
            "vm_sessions",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("session_id", sa.String(length=64), nullable=False),
            sa.Column("visitor_id", sa.String(length=64), nullable=True),
            sa.Column("user_id", sa.String(length=255), nullable=True),
            sa.Column("ip_address", sa.String(length=64), nullable=True),
            sa.Column("country", sa.String(length=100), nullable=True),
            sa.Column("city", sa.String(length=150), nullable=True),
            sa.Column("device", sa.String(length=20), nullable=True),
            sa.Column("browser", sa.String(length=60), nullable=True),
            sa.Column("os", sa.String(length=60), nullable=True),
            sa.Column("user_agent", sa.String(length=512), nullable=True),
            sa.Column("referrer", sa.String(length=1000), nullable=True),
            sa.Column("landing_page", sa.String(length=500), nullable=True),
            sa.Column("current_page", sa.String(length=500), nullable=True),
            sa.Column("utm_source", sa.String(length=100), nullable=True),
            sa.Column("utm_medium", sa.String(length=100), nullable=True),
            sa.Column("utm_campaign", sa.String(length=100), nullable=True),
            sa.Column("utm_term", sa.String(length=100), nullable=True),
            sa.Column("utm_content", sa.String(length=100), nullable=True),
            sa.Column(
                "pageview_count", sa.Integer(), nullable=False, server_default="0"
            ),
            sa.Column(
                "conversion_stage",
                sa.String(length=20),
                nullable=False,
                server_default="visited",
            ),
            sa.Column(
                "is_excluded", sa.Boolean(), nullable=False, server_default="0"
            ),
            sa.Column(
                "started_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.Column(
                "last_activity_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.Column("ended_at", sa.DateTime(timezone=True), nullable=True),
            sa.PrimaryKeyConstraint("id"),
            sa.UniqueConstraint("session_id", name="uq_vm_sessions_session_id"),
        )
        _safe_create_index("ix_vm_sessions_session_id", "vm_sessions", ["session_id"])
        _safe_create_index("ix_vm_sessions_visitor_id", "vm_sessions", ["visitor_id"])
        _safe_create_index("ix_vm_sessions_user_id", "vm_sessions", ["user_id"])
        _safe_create_index("ix_vm_sessions_country", "vm_sessions", ["country"])
        _safe_create_index("ix_vm_sessions_device", "vm_sessions", ["device"])
        _safe_create_index("ix_vm_sessions_browser", "vm_sessions", ["browser"])
        _safe_create_index("ix_vm_sessions_utm_source", "vm_sessions", ["utm_source"])
        _safe_create_index(
            "ix_vm_sessions_utm_campaign", "vm_sessions", ["utm_campaign"]
        )
        _safe_create_index(
            "ix_vm_sessions_conversion_stage", "vm_sessions", ["conversion_stage"]
        )
        _safe_create_index("ix_vm_sessions_started_at", "vm_sessions", ["started_at"])
        _safe_create_index(
            "ix_vm_sessions_last_activity_at", "vm_sessions", ["last_activity_at"]
        )

    # vm_events
    if not _has_table("vm_events"):
        op.create_table(
            "vm_events",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column("session_id", sa.String(length=64), nullable=False),
            sa.Column("event_type", sa.String(length=30), nullable=False),
            sa.Column("label", sa.String(length=255), nullable=True),
            sa.Column("page", sa.String(length=500), nullable=True),
            sa.Column("metadata_json", sa.Text(), nullable=True),
            sa.Column(
                "created_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
        )
        _safe_create_index("ix_vm_events_session_id", "vm_events", ["session_id"])
        _safe_create_index("ix_vm_events_event_type", "vm_events", ["event_type"])
        _safe_create_index("ix_vm_events_created_at", "vm_events", ["created_at"])

    # vm_settings
    if not _has_table("vm_settings"):
        op.create_table(
            "vm_settings",
            sa.Column("id", sa.Integer(), autoincrement=True, nullable=False),
            sa.Column(
                "tracking_enabled",
                sa.Boolean(),
                nullable=False,
                server_default="1",
            ),
            sa.Column(
                "retention_days", sa.Integer(), nullable=False, server_default="90"
            ),
            sa.Column("excluded_ips", sa.Text(), nullable=True),
            sa.Column(
                "exclude_staff", sa.Boolean(), nullable=False, server_default="1"
            ),
            sa.Column(
                "mask_sensitive", sa.Boolean(), nullable=False, server_default="1"
            ),
            sa.Column(
                "updated_at",
                sa.DateTime(timezone=True),
                server_default=sa.func.now(),
                nullable=False,
            ),
            sa.PrimaryKeyConstraint("id"),
        )


def downgrade() -> None:
    for table in ("vm_events", "vm_sessions", "vm_campaigns", "vm_settings"):
        if _has_table(table):
            try:
                op.drop_table(table)
            except Exception:  # noqa: BLE001
                pass