"""
数据库模型 - SQLAlchemy Models
"""
from datetime import datetime
from sqlalchemy import Column, Integer, String, Numeric, BigInteger, DateTime, Boolean, Text, ForeignKey
from sqlalchemy.orm import declarative_base, relationship

# TimescaleDB 基础类 (时序数据)
TimescaleBase = declarative_base()

# SQLite 基础类 (配置数据)
SQLiteBase = declarative_base()


# ==================== SQLite Models (配置数据) ====================

class User(SQLiteBase):
    """用户模型"""
    __tablename__ = "users"
    
    id = Column(Integer, primary_key=True, index=True)
    username = Column(String(50), unique=True, nullable=False, index=True)
    password_hash = Column(String(255), nullable=False)
    email = Column(String(100))
    role = Column(String(20), default="user")  # admin, user
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关系
    api_keys = relationship("APIKey", back_populates="user", cascade="all, delete-orphan")
    alerts = relationship("Alert", back_populates="user", cascade="all, delete-orphan")
    subscriptions = relationship("Subscription", back_populates="user", cascade="all, delete-orphan")


class APIKey(SQLiteBase):
    """API Key 模型"""
    __tablename__ = "api_keys"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    key_hash = Column(String(255), unique=True, nullable=False, index=True)
    name = Column(String(100))
    permissions = Column(Text)  # JSON 格式存储权限
    expires_at = Column(DateTime)
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    last_used_at = Column(DateTime)
    
    # 关系
    user = relationship("User", back_populates="api_keys")


class Alert(SQLiteBase):
    """告警配置模型"""
    __tablename__ = "alerts"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    symbol = Column(String(20), nullable=False, index=True)
    condition_type = Column(String(20), nullable=False)  # greater_than, less_than, equals
    condition_value = Column(Numeric(20, 8), nullable=False)
    alert_type = Column(String(20), default="price")  # price, percent_change
    status = Column(String(20), default="active")  # active, triggered, disabled
    triggered_at = Column(DateTime)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)
    
    # 关系
    user = relationship("User", back_populates="alerts")


class Subscription(SQLiteBase):
    """数据订阅模型"""
    __tablename__ = "subscriptions"
    
    id = Column(Integer, primary_key=True, index=True)
    user_id = Column(Integer, ForeignKey("users.id"), nullable=False)
    symbol = Column(String(20), nullable=False, index=True)
    period = Column(String(10))  # 1m, 5m, 1h, 1d 等
    subscription_type = Column(String(20), default="kline")  # kline, realtime
    is_active = Column(Boolean, default=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    
    # 关系
    user = relationship("User", back_populates="subscriptions")


# ==================== TimescaleDB Models (时序数据) ====================
# 注意：TimescaleDB 表使用原生 SQL 操作，这里仅作为参考

class KlineData(TimescaleBase):
    """K 线数据模型 (TimescaleDB hypertable)"""
    __tablename__ = "kline_data"
    
    time = Column(DateTime, primary_key=True)
    symbol = Column(String(20), primary_key=True)
    period = Column(String(10), primary_key=True)
    open = Column(Numeric(20, 8), nullable=False)
    high = Column(Numeric(20, 8), nullable=False)
    low = Column(Numeric(20, 8), nullable=False)
    close = Column(Numeric(20, 8), nullable=False)
    volume = Column(BigInteger, nullable=False)
    amount = Column(Numeric(30, 8), default=0)
    open_interest = Column(BigInteger, default=0)
    created_at = Column(DateTime, default=datetime.utcnow)


class RealtimeQuote(TimescaleBase):
    """实时行情模型 (TimescaleDB hypertable)"""
    __tablename__ = "realtime_quotes"
    
    time = Column(DateTime, primary_key=True)
    symbol = Column(String(20), primary_key=True)
    last_price = Column(Numeric(20, 8), nullable=False)
    open_price = Column(Numeric(20, 8))
    high_price = Column(Numeric(20, 8))
    low_price = Column(Numeric(20, 8))
    prev_close = Column(Numeric(20, 8))
    volume = Column(BigInteger)
    amount = Column(Numeric(30, 8))
    bid_price_1 = Column(Numeric(20, 8))
    bid_volume_1 = Column(BigInteger)
    ask_price_1 = Column(Numeric(20, 8))
    ask_volume_1 = Column(BigInteger)
    position = Column(BigInteger, default=0)