"""add_usage_events_table

Revision ID: n8o9p0q1r2s3
Revises: m7n8o9p0q1r2
Create Date: 2025-12-30 12:00:00.000000

"""

from collections.abc import Sequence

import sqlalchemy as sa
from alembic import op
from sqlalchemy.dialects import postgresql

# revision identifiers, used by Alembic.
revision: str = "n8o9p0q1r2s3"
down_revision: str | Sequence[str] | None = "m7n8o9p0q1r2"
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    """Create usage_events table for analytics and billing."""
    op.create_table(
        "usage_events",
        sa.Column(
            "id",
            postgresql.UUID(as_uuid=True),
            primary_key=True,
            server_default=sa.text("gen_random_uuid()"),
        ),
        sa.Column("event_type", sa.String(100), nullable=False),
        sa.Column(
            "timestamp",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        # Resource identifiers
        sa.Column("meeting_id", postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column("workspace_id", postgresql.UUID(as_uuid=True), nullable=True),
        sa.Column("project_id", postgresql.UUID(as_uuid=True), nullable=True),
        # Provider/model tracking
        sa.Column("provider_name", sa.String(50), nullable=True),
        sa.Column("model_name", sa.String(100), nullable=True),
        # Usage metrics
        sa.Column("tokens_input", sa.Integer(), nullable=True),
        sa.Column("tokens_output", sa.Integer(), nullable=True),
        sa.Column("latency_ms", sa.Float(), nullable=True),
        # Status
        sa.Column("success", sa.Boolean(), nullable=False, server_default="true"),
        sa.Column("error_code", sa.String(100), nullable=True),
        # Trace context
        sa.Column("trace_id", sa.String(32), nullable=True),
        sa.Column("span_id", sa.String(16), nullable=True),
        # Additional context
        sa.Column(
            "attributes",
            postgresql.JSONB(),
            nullable=False,
            server_default=sa.text("'{}'::jsonb"),
        ),
        # Metadata
        sa.Column(
            "created_at",
            sa.DateTime(timezone=True),
            nullable=False,
            server_default=sa.text("now()"),
        ),
        schema="noteflow",
    )

    # Indexes for common query patterns
    op.create_index(
        "idx_usage_events_event_type",
        "usage_events",
        ["event_type"],
        schema="noteflow",
    )
    op.create_index(
        "idx_usage_events_timestamp",
        "usage_events",
        ["timestamp"],
        schema="noteflow",
    )
    op.create_index(
        "idx_usage_events_meeting_id",
        "usage_events",
        ["meeting_id"],
        schema="noteflow",
    )
    op.create_index(
        "idx_usage_events_workspace_id",
        "usage_events",
        ["workspace_id"],
        schema="noteflow",
    )
    op.create_index(
        "idx_usage_events_provider_name",
        "usage_events",
        ["provider_name"],
        schema="noteflow",
    )

    # Composite index for time-range queries by event type
    op.create_index(
        "idx_usage_events_type_timestamp",
        "usage_events",
        ["event_type", "timestamp"],
        schema="noteflow",
    )

    # Composite index for aggregation by provider + time
    op.create_index(
        "idx_usage_events_provider_timestamp",
        "usage_events",
        ["provider_name", "timestamp"],
        schema="noteflow",
    )


def downgrade() -> None:
    """Drop usage_events table."""
    op.drop_index(
        "idx_usage_events_provider_timestamp",
        table_name="usage_events",
        schema="noteflow",
    )
    op.drop_index(
        "idx_usage_events_type_timestamp",
        table_name="usage_events",
        schema="noteflow",
    )
    op.drop_index(
        "idx_usage_events_provider_name",
        table_name="usage_events",
        schema="noteflow",
    )
    op.drop_index(
        "idx_usage_events_workspace_id",
        table_name="usage_events",
        schema="noteflow",
    )
    op.drop_index(
        "idx_usage_events_meeting_id",
        table_name="usage_events",
        schema="noteflow",
    )
    op.drop_index(
        "idx_usage_events_timestamp",
        table_name="usage_events",
        schema="noteflow",
    )
    op.drop_index(
        "idx_usage_events_event_type",
        table_name="usage_events",
        schema="noteflow",
    )
    op.drop_table("usage_events", schema="noteflow")
