from sqlalchemy import Column, Integer, String, DECIMAL, DateTime, JSON, ForeignKey
from sqlalchemy.dialects.mysql import DATETIME as MySQLDateTime
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql import func
from datetime import datetime, timezone

Base = declarative_base()

class MarketSnapshot(Base):
    __tablename__ = 'market_snapshots'
    id = Column(Integer, primary_key=True, autoincrement=True)
    symbol = Column(String(50), nullable=False)
    bid = Column(DECIMAL(20, 10))
    ask = Column(DECIMAL(20, 10))
    last = Column(DECIMAL(20, 10))
    ts_event = Column(MySQLDateTime(fsp=6), nullable=False)
    ts_ingested = Column(MySQLDateTime(fsp=6), default=lambda: datetime.now(timezone.utc))

class FundingSnapshot(Base):
    __tablename__ = 'funding_snapshots'
    id = Column(Integer, primary_key=True, autoincrement=True)
    symbol = Column(String(50), nullable=False)
    funding_rate = Column(DECIMAL(20, 10))
    ts_event = Column(MySQLDateTime(fsp=6), nullable=False)
    ts_ingested = Column(MySQLDateTime(fsp=6), default=lambda: datetime.now(timezone.utc))

class Signal(Base):
    __tablename__ = 'signals'
    id = Column(Integer, primary_key=True, autoincrement=True)
    strategy_name = Column(String(50), nullable=False)
    symbol = Column(String(50), nullable=False)
    signal_json = Column(JSON)
    ts = Column(MySQLDateTime(fsp=6), default=lambda: datetime.now(timezone.utc))

class Order(Base):
    __tablename__ = 'orders'
    order_id = Column(String(100), primary_key=True)
    strategy_name = Column(String(50), nullable=False, default="LegacyBasis")
    symbol = Column(String(50), nullable=False)
    side = Column(String(20), nullable=False)
    qty = Column(DECIMAL(20, 10), nullable=False)
    price = Column(DECIMAL(20, 10))
    status = Column(String(50), nullable=False)
    ts = Column(MySQLDateTime(fsp=6), default=lambda: datetime.now(timezone.utc))

class Fill(Base):
    __tablename__ = 'fills'
    fill_id = Column(String(100), primary_key=True)
    order_id = Column(String(100), ForeignKey('orders.order_id'), nullable=False)
    strategy_name = Column(String(50), nullable=False, default="LegacyBasis")
    symbol = Column(String(50), nullable=False)
    qty = Column(DECIMAL(20, 10), nullable=False)
    price = Column(DECIMAL(20, 10), nullable=False)
    fee = Column(DECIMAL(20, 10))
    ts = Column(MySQLDateTime(fsp=6), default=lambda: datetime.now(timezone.utc))

class Position(Base):
    __tablename__ = 'positions'
    symbol = Column(String(50), primary_key=True)
    strategy_name = Column(String(50), primary_key=True, default="LegacyBasis")
    net_qty = Column(DECIMAL(20, 10), nullable=False)
    avg_price = Column(DECIMAL(20, 10), nullable=False)
    ts_updated = Column(MySQLDateTime(fsp=6), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))

class PortfolioState(Base):
    __tablename__ = 'portfolio_state'
    id = Column(Integer, primary_key=True, autoincrement=True)
    strategy_name = Column(String(50), nullable=False, default="LegacyBasis")
    equity = Column(DECIMAL(20, 10), nullable=False)
    cash = Column(DECIMAL(20, 10), nullable=False)
    exposure = Column(DECIMAL(20, 10), nullable=False)
    drawdown = Column(DECIMAL(20, 10), nullable=False)
    ts = Column(MySQLDateTime(fsp=6), default=lambda: datetime.now(timezone.utc))

class PnlHistory(Base):
    __tablename__ = 'pnl_history'
    id = Column(Integer, primary_key=True, autoincrement=True)
    strategy_name = Column(String(50), nullable=False, default="LegacyBasis")
    realized_pnl = Column(DECIMAL(20, 10))
    unrealized_pnl = Column(DECIMAL(20, 10))
    funding_pnl = Column(DECIMAL(20, 10))
    ts = Column(MySQLDateTime(fsp=6), default=lambda: datetime.now(timezone.utc))

class FeedHeartbeat(Base):
    __tablename__ = 'feed_heartbeats'
    feed_name = Column(String(50), primary_key=True)
    last_updated = Column(MySQLDateTime(fsp=6), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))

class SystemMeta(Base):
    __tablename__ = 'system_meta'
    key = Column(String(100), primary_key=True)
    value = Column(JSON)
    ts_updated = Column(MySQLDateTime(fsp=6), default=lambda: datetime.now(timezone.utc), onupdate=lambda: datetime.now(timezone.utc))