from datetime import datetime

from sqlalchemy import (Boolean, Column, DateTime, Float, ForeignKey, Integer,
                        String, Text, JSON)
from sqlalchemy.orm import relationship

from app.db.session import Base


class Block(Base):
    __tablename__ = "blocks"
    
    id = Column(Integer, primary_key=True, index=True)
    slot = Column(Integer, unique=True, index=True)
    blockhash = Column(String, unique=True, index=True)
    parent_blockhash = Column(String, index=True)
    timestamp = Column(DateTime, default=datetime.utcnow)
    processed = Column(Boolean, default=False)
    
    transactions = relationship("Transaction", back_populates="block")


class Transaction(Base):
    __tablename__ = "transactions"
    
    id = Column(Integer, primary_key=True, index=True)
    signature = Column(String, unique=True, index=True)
    block_id = Column(Integer, ForeignKey("blocks.slot"))
    timestamp = Column(DateTime, default=datetime.utcnow)
    fee = Column(Integer)
    status = Column(String)
    raw_data = Column(Text)
    
    block = relationship("Block", back_populates="transactions")
    instructions = relationship("Instruction", back_populates="transaction", cascade="all, delete-orphan")
    token_transfers = relationship("TokenTransfer", back_populates="transaction", cascade="all, delete-orphan")
    arbitrage_events = relationship("ArbitrageEvent", back_populates="transaction", cascade="all, delete-orphan")


class Instruction(Base):
    __tablename__ = "instructions"
    
    id = Column(Integer, primary_key=True, index=True)
    transaction_id = Column(Integer, ForeignKey("transactions.id"))
    program_id = Column(String, index=True)
    instruction_type = Column(String, index=True)
    instruction_index = Column(Integer, nullable=True)
    accounts = Column(Text, nullable=True)  # JSON string of account pubkeys
    data = Column(Text, nullable=True)  # Raw instruction data
    parsed_data = Column(JSON, nullable=True)  # Parsed instruction data
    
    transaction = relationship("Transaction", back_populates="instructions")
    swaps = relationship("Swap", back_populates="instruction", cascade="all, delete-orphan")


class TokenTransfer(Base):
    __tablename__ = "token_transfers"
    
    id = Column(Integer, primary_key=True, index=True)
    transaction_id = Column(Integer, ForeignKey("transactions.id"))
    instruction_id = Column(Integer, ForeignKey("instructions.id"), nullable=True)
    token_address = Column(String, index=True)
    from_address = Column(String, index=True)
    to_address = Column(String, index=True)
    amount = Column(Float)
    program_id = Column(String, index=True)
    timestamp = Column(DateTime, default=datetime.utcnow)
    
    transaction = relationship("Transaction", back_populates="token_transfers")
    instruction = relationship("Instruction", backref="token_transfers")


class Swap(Base):
    __tablename__ = "swaps"
    
    id = Column(Integer, primary_key=True, index=True)
    transaction_id = Column(Integer, ForeignKey("transactions.id"))
    instruction_id = Column(Integer, ForeignKey("instructions.id"))
    program_id = Column(String, index=True)
    token_in_address = Column(String, index=True)
    token_out_address = Column(String, index=True)
    amount_in = Column(Float)
    amount_out = Column(Float)
    user_account = Column(String, index=True)
    timestamp = Column(DateTime, default=datetime.utcnow)
    
    transaction = relationship("Transaction", backref="swaps")
    instruction = relationship("Instruction", back_populates="swaps")


class ArbitrageEvent(Base):
    __tablename__ = "arbitrage_events"
    
    id = Column(Integer, primary_key=True, index=True)
    transaction_id = Column(Integer, ForeignKey("transactions.id"))
    profit_token_address = Column(String, index=True)
    profit_amount = Column(Float)
    profit_usd = Column(Float, nullable=True)
    path = Column(Text)  # JSON string of token paths
    swap_sequence = Column(Text, nullable=True)  # JSON string of swap IDs
    confidence_score = Column(Float)  # 0.0 to 1.0
    detected_at = Column(DateTime, default=datetime.utcnow)
    
    transaction = relationship("Transaction", back_populates="arbitrage_events")