import uuid
from datetime import datetime, date
from sqlalchemy import Column, String, Integer, Text, DateTime, Date, ForeignKey, Index, UniqueConstraint, CheckConstraint, Numeric, BigInteger, Boolean
from sqlalchemy.orm import relationship
from sqlalchemy import Enum as SqlEnum
from utils.utils import Base


class Sector(Base):
    __tablename__ = "sectors"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = Column(String(255), nullable=False, unique=True, index=True)
    description = Column(Text, nullable=True)
    icon_url = Column(String(500), nullable=True)
    display_order = Column(Integer, nullable=False, default=0)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    stocks = relationship("Stock", back_populates="sector")


class Exchange(Base):
    __tablename__ = "exchanges"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    name = Column(String(255), nullable=False)
    code = Column(String(50), nullable=False, unique=True, index=True)
    country = Column(String(100), nullable=False)
    timezone = Column(String(100), nullable=False)
    trading_hours = Column(String(255), nullable=True)
    currency = Column(String(10), nullable=False)
    website_url = Column(String(500), nullable=True)
    description = Column(Text, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    stocks = relationship("Stock", back_populates="exchange")


class Stock(Base):
    __tablename__ = "stocks"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    ticker_symbol = Column(String(50), nullable=False, unique=True, index=True)
    company_name = Column(String(255), nullable=False, unique=True, index=True)
    current_price = Column(Numeric(12, 4), nullable=True)
    opening_price = Column(Numeric(12, 4), nullable=True)
    closing_price = Column(Numeric(12, 4), nullable=True)
    high_price = Column(Numeric(12, 4), nullable=True)
    low_price = Column(Numeric(12, 4), nullable=True)
    volume = Column(BigInteger, nullable=True)
    market_cap = Column(Numeric(20, 2), nullable=True)
    sector_id = Column(String(36), ForeignKey("sectors.id", ondelete="RESTRICT"), nullable=False, index=True)
    industry = Column(String(255), nullable=True)
    exchange_id = Column(String(36), ForeignKey("exchanges.id", ondelete="RESTRICT"), nullable=False, index=True)
    currency = Column(String(10), nullable=False)
    last_updated = Column(DateTime, nullable=True)
    description = Column(Text, nullable=True)
    logo_url = Column(String(500), nullable=True)
    website_url = Column(String(500), nullable=True)
    country = Column(String(100), nullable=True)
    ipo_date = Column(Date, nullable=True)
    status = Column(String(50), nullable=False, default="ACTIVE", index=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    sector = relationship("Sector", back_populates="stocks")
    exchange = relationship("Exchange", back_populates="stocks")
    stock_history = relationship("Stockhistory", back_populates="stock", cascade="all, delete-orphan", passive_deletes=True)
    watchlist_items = relationship("Watchlistitem", back_populates="stock", cascade="all, delete-orphan", passive_deletes=True)
    user_activities = relationship("Useractivity", back_populates="stock")

    __table_args__ = (
        CheckConstraint("current_price IS NULL OR current_price > 0", name="ck_stock_current_price_positive"),
        CheckConstraint("opening_price IS NULL OR opening_price > 0", name="ck_stock_opening_price_positive"),
        CheckConstraint("closing_price IS NULL OR closing_price > 0", name="ck_stock_closing_price_positive"),
        CheckConstraint("high_price IS NULL OR high_price > 0", name="ck_stock_high_price_positive"),
        CheckConstraint("low_price IS NULL OR low_price > 0", name="ck_stock_low_price_positive"),
        CheckConstraint("volume IS NULL OR volume >= 0", name="ck_stock_volume_nonneg"),
        CheckConstraint("market_cap IS NULL OR market_cap > 0", name="ck_stock_market_cap_positive"),
        Index("ix_stocks_status_updated", "status", "updated_at"),
    )


class Stockhistory(Base):
    __tablename__ = "stock_history"

    id = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    stock_id = Column(String(36), ForeignKey("stocks.id", ondelete="CASCADE"), nullable=False, index=True)
    date = Column(Date, nullable=False, index=True)
    open_price = Column(Numeric(12, 4), nullable=False)
    close_price = Column(Numeric(12, 4), nullable=False)
    high_price = Column(Numeric(12, 4), nullable=False)
    low_price = Column(Numeric(12, 4), nullable=False)
    volume = Column(BigInteger, nullable=False)
    adjusted_close_price = Column(Numeric(12, 4), nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow, nullable=False)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow, nullable=False)

    stock = relationship("Stock", back_populates="stock_history")

    __table_args__ = (
        UniqueConstraint("stock_id", "date", name="uq_stock_history_stock_date"),
        CheckConstraint("open_price > 0", name="ck_stock_history_open_price_positive"),
        CheckConstraint("close_price > 0", name="ck_stock_history_close_price_positive"),
        CheckConstraint("high_price > 0", name="ck_stock_history_high_price_positive"),
        CheckConstraint("low_price > 0", name="ck_stock_history_low_price_positive"),
        CheckConstraint("high_price >= low_price", name="ck_stock_history_high_gte_low"),
        CheckConstraint("volume >= 0", name="ck_stock_history_volume_nonneg"),
        Index("ix_stock_history_stock_date", "stock_id", "date"),
    )