meshview/alembic/versions/c88468b7ab0b_initial_migration.py

"""Initial migration

Revision ID: c88468b7ab0b
Revises:
Create Date: 2025-10-26 20:56:50.285200

"""

from collections.abc import Sequence

import sqlalchemy as sa

from alembic import op

# revision identifiers, used by Alembic.
revision: str = 'c88468b7ab0b'
down_revision: str | None = None
branch_labels: str | Sequence[str] | None = None
depends_on: str | Sequence[str] | None = None


def upgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    # Get connection and inspector to check what exists
    conn = op.get_bind()
    inspector = sa.inspect(conn)
    existing_tables = inspector.get_table_names()

    # Create node table if it doesn't exist
    if 'node' not in existing_tables:
        op.create_table(
            'node',
            sa.Column('id', sa.String(), nullable=False),
            sa.Column('node_id', sa.BigInteger(), nullable=True),
            sa.Column('long_name', sa.String(), nullable=True),
            sa.Column('short_name', sa.String(), nullable=True),
            sa.Column('hw_model', sa.String(), nullable=True),
            sa.Column('firmware', sa.String(), nullable=True),
            sa.Column('role', sa.String(), nullable=True),
            sa.Column('last_lat', sa.BigInteger(), nullable=True),
            sa.Column('last_long', sa.BigInteger(), nullable=True),
            sa.Column('channel', sa.String(), nullable=True),
            sa.Column('last_update', sa.DateTime(), nullable=True),
            sa.PrimaryKeyConstraint('id'),
            sa.UniqueConstraint('node_id'),
        )
        op.create_index('idx_node_node_id', 'node', ['node_id'], unique=False)

    # Create packet table if it doesn't exist
    if 'packet' not in existing_tables:
        op.create_table(
            'packet',
            sa.Column('id', sa.BigInteger(), nullable=False),
            sa.Column('portnum', sa.Integer(), nullable=True),
            sa.Column('from_node_id', sa.BigInteger(), nullable=True),
            sa.Column('to_node_id', sa.BigInteger(), nullable=True),
            sa.Column('payload', sa.LargeBinary(), nullable=True),
            sa.Column('import_time', sa.DateTime(), nullable=True),
            sa.Column('import_time_us', sa.BigInteger(), nullable=True),
            sa.Column('channel', sa.String(), nullable=True),
            sa.PrimaryKeyConstraint('id'),
        )
        op.create_index('idx_packet_from_node_id', 'packet', ['from_node_id'], unique=False)
        op.create_index('idx_packet_to_node_id', 'packet', ['to_node_id'], unique=False)
        op.create_index(
            'idx_packet_import_time', 'packet', [sa.text('import_time DESC')], unique=False
        )
        op.create_index(
            'idx_packet_import_time_us', 'packet', [sa.text('import_time_us DESC')], unique=False
        )
        op.create_index(
            'idx_packet_from_node_time',
            'packet',
            ['from_node_id', sa.text('import_time DESC')],
            unique=False,
        )
        op.create_index(
            'idx_packet_from_node_time_us',
            'packet',
            ['from_node_id', sa.text('import_time_us DESC')],
            unique=False,
        )

    # Create packet_seen table if it doesn't exist
    if 'packet_seen' not in existing_tables:
        op.create_table(
            'packet_seen',
            sa.Column('packet_id', sa.BigInteger(), nullable=False),
            sa.Column('node_id', sa.BigInteger(), nullable=False),
            sa.Column('rx_time', sa.BigInteger(), nullable=False),
            sa.Column('hop_limit', sa.Integer(), nullable=True),
            sa.Column('hop_start', sa.Integer(), nullable=True),
            sa.Column('channel', sa.String(), nullable=True),
            sa.Column('rx_snr', sa.Float(), nullable=True),
            sa.Column('rx_rssi', sa.Integer(), nullable=True),
            sa.Column('topic', sa.String(), nullable=True),
            sa.Column('import_time', sa.DateTime(), nullable=True),
            sa.Column('import_time_us', sa.BigInteger(), nullable=True),
            sa.ForeignKeyConstraint(
                ['packet_id'],
                ['packet.id'],
            ),
            sa.PrimaryKeyConstraint('packet_id', 'node_id', 'rx_time'),
        )
        op.create_index('idx_packet_seen_node_id', 'packet_seen', ['node_id'], unique=False)
        op.create_index('idx_packet_seen_packet_id', 'packet_seen', ['packet_id'], unique=False)
        op.create_index(
            'idx_packet_seen_import_time_us', 'packet_seen', ['import_time_us'], unique=False
        )

    # Create traceroute table if it doesn't exist
    if 'traceroute' not in existing_tables:
        op.create_table(
            'traceroute',
            sa.Column('id', sa.Integer(), nullable=False),
            sa.Column('packet_id', sa.BigInteger(), nullable=True),
            sa.Column('gateway_node_id', sa.BigInteger(), nullable=True),
            sa.Column('done', sa.Boolean(), nullable=True),
            sa.Column('route', sa.LargeBinary(), nullable=True),
            sa.Column('import_time', sa.DateTime(), nullable=True),
            sa.Column('import_time_us', sa.BigInteger(), nullable=True),
            sa.ForeignKeyConstraint(
                ['packet_id'],
                ['packet.id'],
            ),
            sa.PrimaryKeyConstraint('id'),
        )
        op.create_index('idx_traceroute_import_time', 'traceroute', ['import_time'], unique=False)
        op.create_index(
            'idx_traceroute_import_time_us', 'traceroute', ['import_time_us'], unique=False
        )
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic - please adjust! ###
    # Drop traceroute table and indexes
    op.drop_index('idx_traceroute_import_time_us', table_name='traceroute')
    op.drop_index('idx_traceroute_import_time', table_name='traceroute')
    op.drop_table('traceroute')

    # Drop packet_seen table and indexes
    op.drop_index('idx_packet_seen_import_time_us', table_name='packet_seen')
    op.drop_index('idx_packet_seen_packet_id', table_name='packet_seen')
    op.drop_index('idx_packet_seen_node_id', table_name='packet_seen')
    op.drop_table('packet_seen')

    # Drop packet table and indexes
    op.drop_index('idx_packet_from_node_time_us', table_name='packet')
    op.drop_index('idx_packet_from_node_time', table_name='packet')
    op.drop_index('idx_packet_import_time_us', table_name='packet')
    op.drop_index('idx_packet_import_time', table_name='packet')
    op.drop_index('idx_packet_to_node_id', table_name='packet')
    op.drop_index('idx_packet_from_node_id', table_name='packet')
    op.drop_table('packet')

    # Drop node table and indexes
    op.drop_index('idx_node_node_id', table_name='node')
    op.drop_table('node')
    # ### end Alembic commands ###