import os
from datetime import datetime
from sqlalchemy import BigInteger,Boolean,DateTime,Float,Integer,JSON,String,UniqueConstraint,create_engine
from sqlalchemy.engine import URL
from sqlalchemy.orm import DeclarativeBase,Mapped,mapped_column,sessionmaker

class Base(DeclarativeBase): pass
class CandleRow(Base):
    __tablename__="candles"; id:Mapped[int]=mapped_column(BigInteger,primary_key=True,autoincrement=True); symbol_id:Mapped[int]=mapped_column(Integer,index=True); timeframe:Mapped[str]=mapped_column(String(8)); timestamp:Mapped[int]=mapped_column(BigInteger); open:Mapped[float]=mapped_column(Float); high:Mapped[float]=mapped_column(Float); low:Mapped[float]=mapped_column(Float); close:Mapped[float]=mapped_column(Float); volume:Mapped[float|None]=mapped_column(Float,nullable=True); provider:Mapped[str]=mapped_column(String(30),default="twelvedata"); __table_args__=(UniqueConstraint("symbol_id","timeframe","timestamp"),)
class FeatureRow(Base):
    __tablename__="feature_snapshots"; id:Mapped[int]=mapped_column(BigInteger,primary_key=True); symbol_id:Mapped[int]=mapped_column(Integer,index=True); horizon:Mapped[str]=mapped_column(String(8)); engine_version:Mapped[str]=mapped_column(String(80)); generated_at:Mapped[datetime]=mapped_column(DateTime(timezone=True)); features:Mapped[dict]=mapped_column(JSON)
class SignalRow(Base):
    __tablename__="signals"; id:Mapped[int]=mapped_column(BigInteger,primary_key=True); external_key:Mapped[str]=mapped_column(String(160),unique=True); symbol_id:Mapped[int]=mapped_column(Integer,index=True); horizon:Mapped[str]=mapped_column(String(8)); direction:Mapped[str]=mapped_column(String(16)); score:Mapped[float]=mapped_column(Float); payload:Mapped[dict]=mapped_column(JSON); generated_at:Mapped[datetime]=mapped_column(DateTime(timezone=True)); expires_at:Mapped[datetime]=mapped_column(DateTime(timezone=True))
class EngineVersion(Base):
    __tablename__="engine_versions"; id:Mapped[int]=mapped_column(Integer,primary_key=True); version:Mapped[str]=mapped_column(String(80),unique=True); rules:Mapped[dict]=mapped_column(JSON); active:Mapped[bool]=mapped_column(Boolean,default=True); created_at:Mapped[datetime]=mapped_column(DateTime(timezone=True),default=datetime.utcnow)
class BacktestRun(Base):
    __tablename__="backtest_runs"; id:Mapped[int]=mapped_column(BigInteger,primary_key=True); engine_version:Mapped[str]=mapped_column(String(80)); status:Mapped[str]=mapped_column(String(20)); parameters:Mapped[dict]=mapped_column(JSON); metrics:Mapped[dict|None]=mapped_column(JSON,nullable=True); created_at:Mapped[datetime]=mapped_column(DateTime(timezone=True),default=datetime.utcnow)

database_url=os.getenv("TRADING_AI_DATABASE_URL")
if not database_url:
    database_url=URL.create("mysql+pymysql",username=os.getenv("DB_USERNAME"),password=os.getenv("DB_PASSWORD"),host=os.getenv("DB_HOST","127.0.0.1"),port=int(os.getenv("DB_PORT","3306")),database=os.getenv("TRADING_AI_DATABASE","trading_ai"),query={"charset":"utf8mb4"})
engine=create_engine(database_url,pool_pre_ping=True,pool_recycle=300)
Session=sessionmaker(engine,expire_on_commit=False)
def init_db(): Base.metadata.create_all(engine)