"""initial tables

Revision ID: 01_initial_tables
Revises: 
Create Date: 2023-12-07 14:00:00.000000

"""
from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import sqlite

# revision identifiers, used by Alembic.
revision = '01_initial_tables'
down_revision = None
branch_labels = None
depends_on = None


def upgrade():
    # Create block table
    op.create_table(
        'block',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('block_height', sa.BigInteger(), nullable=False),
        sa.Column('block_hash', sa.String(), nullable=False),
        sa.Column('parent_block_hash', sa.String(), nullable=True),
        sa.Column('slot', sa.BigInteger(), nullable=False),
        sa.Column('block_time', sa.DateTime(), nullable=True),
        sa.Column('transactions_count', sa.Integer(), nullable=False, default=0),
        sa.Column('successful_transactions_count', sa.Integer(), nullable=False, default=0),
        sa.Column('processed', sa.Integer(), nullable=False, default=0),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_block_block_hash'), 'block', ['block_hash'], unique=True)
    op.create_index(op.f('ix_block_block_height'), 'block', ['block_height'], unique=True)
    op.create_index(op.f('ix_block_id'), 'block', ['id'], unique=False)
    op.create_index(op.f('ix_block_parent_block_hash'), 'block', ['parent_block_hash'], unique=False)
    op.create_index(op.f('ix_block_slot'), 'block', ['slot'], unique=False)
    op.create_index(op.f('ix_block_block_time'), 'block', ['block_time'], unique=False)
    
    # Create transaction table
    op.create_table(
        'transaction',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('block_id', sa.Integer(), nullable=False),
        sa.Column('transaction_hash', sa.String(), nullable=False),
        sa.Column('slot', sa.BigInteger(), nullable=False),
        sa.Column('signature', sa.String(), nullable=False),
        sa.Column('success', sa.Boolean(), nullable=False, default=False),
        sa.Column('fee', sa.BigInteger(), nullable=True),
        sa.Column('fee_payer', sa.String(), nullable=True),
        sa.Column('program_ids', sa.JSON(), nullable=True),
        sa.Column('accounts', sa.JSON(), nullable=True),
        sa.Column('raw_data', sa.Text(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(['block_id'], ['block.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_transaction_id'), 'transaction', ['id'], unique=False)
    op.create_index(op.f('ix_transaction_transaction_hash'), 'transaction', ['transaction_hash'], unique=True)
    op.create_index(op.f('ix_transaction_signature'), 'transaction', ['signature'], unique=True)
    op.create_index(op.f('ix_transaction_slot'), 'transaction', ['slot'], unique=False)
    op.create_index(op.f('ix_transaction_fee_payer'), 'transaction', ['fee_payer'], unique=False)
    
    # Create dex table
    op.create_table(
        'dex',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('name', sa.String(), nullable=False),
        sa.Column('address', sa.String(), nullable=False),
        sa.Column('program_id', sa.String(), nullable=False),
        sa.Column('version', sa.String(), nullable=True),
        sa.Column('description', sa.Text(), nullable=True),
        sa.Column('website', sa.String(), nullable=True),
        sa.Column('volume_24h', sa.Float(), nullable=True),
        sa.Column('volume_7d', sa.Float(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=False),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_dex_id'), 'dex', ['id'], unique=False)
    op.create_index(op.f('ix_dex_name'), 'dex', ['name'], unique=False)
    op.create_index(op.f('ix_dex_address'), 'dex', ['address'], unique=True)
    op.create_index(op.f('ix_dex_program_id'), 'dex', ['program_id'], unique=False)
    
    # Create pool table
    op.create_table(
        'pool',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('dex_id', sa.Integer(), nullable=False),
        sa.Column('address', sa.String(), nullable=False),
        sa.Column('token_a_address', sa.String(), nullable=False),
        sa.Column('token_a_symbol', sa.String(), nullable=True),
        sa.Column('token_a_name', sa.String(), nullable=True),
        sa.Column('token_a_decimals', sa.Integer(), nullable=True),
        sa.Column('token_b_address', sa.String(), nullable=False),
        sa.Column('token_b_symbol', sa.String(), nullable=True),
        sa.Column('token_b_name', sa.String(), nullable=True),
        sa.Column('token_b_decimals', sa.Integer(), nullable=True),
        sa.Column('token_a_reserve', sa.Float(), nullable=True),
        sa.Column('token_b_reserve', sa.Float(), nullable=True),
        sa.Column('last_updated_slot', sa.BigInteger(), nullable=True),
        sa.Column('volume_24h', sa.Float(), nullable=True),
        sa.Column('fees_24h', sa.Float(), nullable=True),
        sa.Column('tvl', sa.Float(), nullable=True),
        sa.Column('fee_rate', sa.Float(), nullable=True),
        sa.Column('pool_type', sa.String(), nullable=True),
        sa.Column('is_active', sa.Boolean(), nullable=False, default=True),
        sa.Column('extra_data', sa.JSON(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.Column('updated_at', sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(['dex_id'], ['dex.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_pool_id'), 'pool', ['id'], unique=False)
    op.create_index(op.f('ix_pool_address'), 'pool', ['address'], unique=True)
    op.create_index(op.f('ix_pool_token_a_address'), 'pool', ['token_a_address'], unique=False)
    op.create_index(op.f('ix_pool_token_b_address'), 'pool', ['token_b_address'], unique=False)
    
    # Create arbitrage table
    op.create_table(
        'arbitrage',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('transaction_id', sa.Integer(), nullable=False),
        sa.Column('initiator_address', sa.String(), nullable=False),
        sa.Column('start_token_address', sa.String(), nullable=False),
        sa.Column('start_token_symbol', sa.String(), nullable=True),
        sa.Column('start_amount', sa.Float(), nullable=False),
        sa.Column('end_amount', sa.Float(), nullable=False),
        sa.Column('profit_amount', sa.Float(), nullable=False),
        sa.Column('profit_percentage', sa.Float(), nullable=False),
        sa.Column('success', sa.Boolean(), nullable=False, default=False),
        sa.Column('failure_reason', sa.Text(), nullable=True),
        sa.Column('gas_cost', sa.Float(), nullable=True),
        sa.Column('net_profit', sa.Float(), nullable=True),
        sa.Column('legs_count', sa.Integer(), nullable=False, default=0),
        sa.Column('route_description', sa.Text(), nullable=True),
        sa.Column('included_dexes', sa.JSON(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(['transaction_id'], ['transaction.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_arbitrage_id'), 'arbitrage', ['id'], unique=False)
    op.create_index(op.f('ix_arbitrage_initiator_address'), 'arbitrage', ['initiator_address'], unique=False)
    op.create_index(op.f('ix_arbitrage_start_token_address'), 'arbitrage', ['start_token_address'], unique=False)
    
    # Create arbitrage_leg table
    op.create_table(
        'arbitrageleg',
        sa.Column('id', sa.Integer(), nullable=False),
        sa.Column('arbitrage_id', sa.Integer(), nullable=False),
        sa.Column('leg_index', sa.Integer(), nullable=False),
        sa.Column('pool_id', sa.Integer(), nullable=False),
        sa.Column('token_in_address', sa.String(), nullable=False),
        sa.Column('token_in_symbol', sa.String(), nullable=True),
        sa.Column('token_in_amount', sa.Float(), nullable=False),
        sa.Column('token_out_address', sa.String(), nullable=False),
        sa.Column('token_out_symbol', sa.String(), nullable=True),
        sa.Column('token_out_amount', sa.Float(), nullable=False),
        sa.Column('price_impact', sa.Float(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=False),
        sa.ForeignKeyConstraint(['arbitrage_id'], ['arbitrage.id'], ),
        sa.ForeignKeyConstraint(['pool_id'], ['pool.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_arbitrageleg_id'), 'arbitrageleg', ['id'], unique=False)
    op.create_index(op.f('ix_arbitrageleg_token_in_address'), 'arbitrageleg', ['token_in_address'], unique=False)
    op.create_index(op.f('ix_arbitrageleg_token_out_address'), 'arbitrageleg', ['token_out_address'], unique=False)


def downgrade():
    op.drop_table('arbitrageleg')
    op.drop_table('arbitrage')
    op.drop_table('pool')
    op.drop_table('dex')
    op.drop_table('transaction')
    op.drop_table('block')