database.py

"""
Signal Engine Database Schema
Audit-grade SQLite schema with full batch commitment ledger
"""

from sqlalchemy import create_engine, Column, Integer, String, Float, DateTime, Boolean, Text, ForeignKey, Index, JSON
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker, relationship
from datetime import datetime
import hashlib
import json

Base = declarative_base()

class Symbol(Base):
    __tablename__ = 'symbols'
    
    id = Column(Integer, primary_key=True)
    symbol = Column(String(50), unique=True, nullable=False, index=True)
    name = Column(String(100))
    min_price = Column(Float, default=0.10)
    min_volume_24h = Column(Float, default=50000.0)
    active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    candles = relationship("Candle", back_populates="symbol_obj")
    predictions = relationship("Prediction", back_populates="symbol_obj")

class Candle(Base):
    __tablename__ = 'candles'
    
    id = Column(Integer, primary_key=True)
    symbol = Column(String(50), nullable=False, index=True)
    timestamp = Column(DateTime, nullable=False, index=True)
    open = Column(Float, nullable=False)
    high = Column(Float, nullable=False)
    low = Column(Float, nullable=False)
    close = Column(Float, nullable=False)
    volume = Column(Float, nullable=False)
    quote_volume = Column(Float, nullable=False)
    
    symbol_obj = relationship("Symbol", back_populates="candles")
    
    __table_args__ = (
        Index('idx_symbol_timestamp', 'symbol', 'timestamp'),
    )

class PredictionBatch(Base):
    __tablename__ = 'prediction_batches'
    
    id = Column(Integer, primary_key=True)
    batch_id = Column(String(64), unique=True, nullable=False, index=True)
    target_hour = Column(DateTime, nullable=False, index=True)
    committed_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    
    # Full batch commitment hash (SHA-256 of canonical JSON)
    batch_hash = Column(String(64), nullable=False, index=True)
    
    # Previous batch hash for chain verification
    prev_batch_hash = Column(String(64), nullable=True, index=True)
    
    # Model version info
    model_version = Column(String(50))
    ensemble_weights = Column(JSON)
    
    # Batch metadata
    num_predictions = Column(Integer, default=0)
    avg_confidence = Column(Float)
    
    # Scoring (filled later)
    scored_at = Column(DateTime, nullable=True)
    accuracy = Column(Float, nullable=True)
    brier_score = Column(Float, nullable=True)
    hypothetical_pnl_bps = Column(Float, nullable=True)
    
    predictions = relationship("Prediction", back_populates="batch")
    
    __table_args__ = (
        Index('idx_target_hour', 'target_hour'),
        Index('idx_committed_at', 'committed_at'),
    )

class Prediction(Base):
    __tablename__ = 'predictions'
    
    id = Column(Integer, primary_key=True)
    batch_id = Column(String(64), nullable=False, index=True)
    symbol = Column(String(50), nullable=False, index=True)
    
    # Target info
    target_hour = Column(DateTime, nullable=False, index=True)
    entry_price = Column(Float, nullable=False)
    
    # Model output
    direction = Column(String(10), nullable=False)  # LONG, SHORT, FLAT
    probability_up = Column(Float, nullable=False)
    confidence = Column(Float, nullable=False)
    suggested_position = Column(Float, nullable=False)  # -1 to 1
    
    # Feature hash for reproducibility
    feature_hash = Column(String(64), nullable=False)
    
    # Scoring (filled later)
    exit_price = Column(Float, nullable=True)
    actual_return = Column(Float, nullable=True)
    correct = Column(Boolean, nullable=True)
    scored_at = Column(DateTime, nullable=True)
    
    batch = relationship("PredictionBatch", back_populates="predictions")
    symbol_obj = relationship("Symbol", back_populates="predictions")
    
    __table_args__ = (
        Index('idx_symbol_target', 'symbol', 'target_hour'),
    )

class LedgerEntry(Base):
    __tablename__ = 'ledger'
    
    id = Column(Integer, primary_key=True)
    entry_hash = Column(String(64), unique=True, nullable=False, index=True)
    prev_hash = Column(String(64), nullable=True, index=True)
    
    entry_type = Column(String(20), nullable=False)  # BATCH_COMMIT, BATCH_SCORE, KEY_ISSUE, etc.
    timestamp = Column(DateTime, default=datetime.utcnow, nullable=False, index=True)
    
    # Full payload for audit
    payload = Column(Text, nullable=False)
    payload_hash = Column(String(64), nullable=False)
    
    # Signature (if applicable)
    signature = Column(String(128), nullable=True)
    
    __table_args__ = (
        Index('idx_timestamp', 'timestamp'),
        Index('idx_entry_type', 'entry_type'),
    )

class Subscriber(Base):
    __tablename__ = 'subscribers'
    
    id = Column(Integer, primary_key=True)
    email = Column(String(255), unique=True, nullable=False, index=True)
    stripe_customer_id = Column(String(100), nullable=True, index=True)
    
    # Subscription status
    tier = Column(String(20), default='free')  # free, pro, enterprise
    active = Column(Boolean, default=True)
    expires_at = Column(DateTime, nullable=True)
    
    # API access
    api_key = Column(String(64), unique=True, nullable=True, index=True)
    api_key_hash = Column(String(64), nullable=True, index=True)
    rate_limit_per_hour = Column(Integer, default=100)
    
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

class ApiUsage(Base):
    __tablename__ = 'api_usage'
    
    id = Column(Integer, primary_key=True)
    subscriber_id = Column(Integer, ForeignKey('subscribers.id'), nullable=False, index=True)
    endpoint = Column(String(100), nullable=False)
    timestamp = Column(DateTime, default=datetime.utcnow, index=True)
    status_code = Column(Integer, nullable=False)
    
    __table_args__ = (
        Index('idx_subscriber_timestamp', 'subscriber_id', 'timestamp'),
    )

class ModelState(Base):
    __tablename__ = 'model_state'
    
    id = Column(Integer, primary_key=True)
    model_name = Column(String(50), unique=True, nullable=False, index=True)
    version = Column(String(50), nullable=False)
    
    # Serialized model (pickle bytes)
    model_blob = Column(Text, nullable=False)
    
    # Model metadata
    trained_at = Column(DateTime, nullable=False)
    training_samples = Column(Integer, default=0)
    feature_importance = Column(JSON, nullable=True)
    
    created_at = Column(DateTime, default=datetime.utcnow)

def compute_batch_hash(predictions: list, metadata: dict) -> str:
    """Compute SHA-256 hash of canonical prediction batch"""
    canonical = {
        'predictions': sorted(predictions, key=lambda x: (x['symbol'], x['target_hour'])),
        'metadata': metadata
    }
    canonical_str = json.dumps(canonical, sort_keys=True, separators=(',', ':'))
    return hashlib.sha256(canonical_str.encode()).hexdigest()

def compute_ledger_entry(entry_type: str, payload: dict, prev_hash: str = None) -> tuple:
    """Compute ledger entry hash and return entry data"""
    canonical = {
        'type': entry_type,
        'timestamp': datetime.utcnow().isoformat(),
        'payload': payload,
        'prev_hash': prev_hash
    }
    canonical_str = json.dumps(canonical, sort_keys=True, separators=(',', ':'))
    entry_hash = hashlib.sha256(canonical_str.encode()).hexdigest()
    payload_hash = hashlib.sha256(json.dumps(payload, sort_keys=True).encode()).hexdigest()
    
    return entry_hash, payload_hash, canonical_str

def init_db(db_url: str = "sqlite:///signal_engine.db"):
    """Initialize database with all tables"""
    engine = create_engine(db_url)
    Base.metadata.create_all(engine)
    return engine

def get_session(db_url: str = "sqlite:///signal_engine.db"):
    """Get database session"""
    engine = create_engine(db_url)
    SessionLocal = sessionmaker(bind=engine)
    return SessionLocal()