"""
Database models for AI Trading System
Sprint 1: Foundation - Data storage models
"""

from datetime import datetime
from typing import Optional

from sqlalchemy import (
    Boolean,
    Column,
    Date,
    DateTime,
    Float,
    ForeignKey,
    Integer,
    String,
    Text,
    UniqueConstraint,
    Index,
    func,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()


class Stock(Base):
    """Stock/Ticker information"""

    __tablename__ = "stocks"

    id = Column(Integer, primary_key=True, autoincrement=True)
    ticker = Column(String(20), unique=True, nullable=False, index=True)
    name = Column(String(200))
    sector = Column(String(100))
    industry = Column(String(100))
    market = Column(String(50), default="Borsa Italiana")
    priority = Column(String(20))  # primary, secondary
    is_active = Column(Boolean, default=True)

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    price_data = relationship("PriceData", back_populates="stock", cascade="all, delete-orphan")
    fundamental_data = relationship("FundamentalData", back_populates="stock", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Stock(ticker='{self.ticker}', name='{self.name}')>"


class PriceData(Base):
    """Historical and real-time price data"""

    __tablename__ = "price_data"
    __table_args__ = (
        UniqueConstraint("stock_id", "date", name="uq_stock_date"),
        Index("idx_stock_date", "stock_id", "date"),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    stock_id = Column(Integer, ForeignKey("stocks.id"), nullable=False)
    date = Column(DateTime, nullable=False, index=True)

    # OHLCV Data
    open = Column(Float)
    high = Column(Float)
    low = Column(Float)
    close = Column(Float, nullable=False)
    volume = Column(Integer)
    adj_close = Column(Float)

    # Metadata
    data_source = Column(String(50), default="yfinance")
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    stock = relationship("Stock", back_populates="price_data")

    def __repr__(self):
        return f"<PriceData(stock_id={self.stock_id}, date='{self.date}', close={self.close})>"


class FundamentalData(Base):
    """Fundamental metrics for stocks"""

    __tablename__ = "fundamental_data"
    __table_args__ = (
        UniqueConstraint("stock_id", "date", name="uq_fundamental_stock_date"),
        Index("idx_fundamental_stock_date", "stock_id", "date"),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    stock_id = Column(Integer, ForeignKey("stocks.id"), nullable=False)
    date = Column(DateTime, nullable=False, index=True)

    # Valuation Metrics
    pe_ratio = Column(Float)  # Price to Earnings
    pb_ratio = Column(Float)  # Price to Book
    ps_ratio = Column(Float)  # Price to Sales
    peg_ratio = Column(Float)  # PEG Ratio
    ev_to_ebitda = Column(Float)  # EV/EBITDA (Enterprise Value to EBITDA)

    # Profitability Metrics
    profit_margin = Column(Float)
    operating_margin = Column(Float)
    roe = Column(Float)  # Return on Equity
    roa = Column(Float)  # Return on Assets

    # Dividend Metrics
    dividend_yield = Column(Float)
    payout_ratio = Column(Float)

    # Financial Health
    debt_to_equity = Column(Float)  # Final D/E value used (after all processing)
    debt_to_equity_raw = Column(Float)  # Original Yahoo Finance value (for audit)
    debt_to_equity_calculated = Column(Float)  # Calculated from balance sheet
    debt_to_equity_source = Column(String(50))  # Source: 'balance_sheet', 'yahoo_normalized', 'yahoo_raw', 'net_cash_override', 'missing'
    shareholders_equity = Column(Float)  # Stockholders equity from balance sheet
    current_ratio = Column(Float)
    quick_ratio = Column(Float)

    # Growth Metrics
    revenue_growth = Column(Float)
    earnings_growth = Column(Float)

    # Market Metrics
    market_cap = Column(Float)
    beta = Column(Float)

    # === ADVANCED FUNDAMENTAL METRICS (Schema v2) ===

    # Capital Structure (4 fields)
    total_debt = Column(Float)  # Total debt
    total_cash = Column(Float)  # Cash and cash equivalents
    net_debt = Column(Float)  # Total debt - total cash
    enterprise_value = Column(Float)  # Market cap + net debt

    # Cash Flow Metrics (3 fields)
    operating_cashflow = Column(Float)  # Operating cash flow
    capital_expenditures = Column(Float)  # CapEx (negative value)
    free_cashflow = Column(Float)  # Operating CF + CapEx

    # Margin Metrics (3 fields)
    gross_margin = Column(Float)  # Gross profit margin
    ebitda_margin = Column(Float)  # EBITDA margin
    ebitda = Column(Float)  # EBITDA absolute value

    # Revenue/Earnings (4 fields)
    total_revenue = Column(Float)  # Total revenue
    ebit = Column(Float)  # Earnings before interest and tax
    net_income = Column(Float)  # Net income
    trailing_eps = Column(Float)  # Trailing 12-month EPS

    # Shares/Dividend Details (4 fields)
    shares_outstanding = Column(Float)  # Number of shares outstanding
    dividend_rate = Column(Float)  # Annual dividend per share
    ex_dividend_date = Column(DateTime)  # Ex-dividend date
    dividend_payment_date = Column(DateTime)  # Next payment date

    # === ADVANCED QUALITY METRICS (Schema v4) ===
    roic = Column(Float)  # Return on Invested Capital (percentage)
    interest_coverage = Column(Float)  # Interest Coverage Ratio (EBIT / Interest Expense)
    piotroski_fscore = Column(Integer)  # Piotroski F-Score (0-9)

    # Schema Version
    schema_version = Column(Integer, default=4)  # v1=basic, v2=extended, v3=json_reports, v4=advanced_quality

    # Metadata
    data_source = Column(String(50), default="yfinance")
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    stock = relationship("Stock", back_populates="fundamental_data")

    def __repr__(self):
        return f"<FundamentalData(stock_id={self.stock_id}, date='{self.date}', pe_ratio={self.pe_ratio})>"


class Portfolio(Base):
    """Portfolio/Account definition"""

    __tablename__ = "portfolios"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), nullable=False, unique=True)
    description = Column(Text)
    initial_cash = Column(Float, default=0.0)
    current_cash = Column(Float, default=0.0)
    is_active = Column(Boolean, default=True)

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    transactions = relationship("Transaction", back_populates="portfolio", cascade="all, delete-orphan")
    positions = relationship("Position", back_populates="portfolio", cascade="all, delete-orphan")

    def __repr__(self):
        return f"<Portfolio(name='{self.name}', cash={self.current_cash})>"


class Transaction(Base):
    """Portfolio transactions (buy/sell)"""

    __tablename__ = "transactions"
    __table_args__ = (
        Index("idx_transaction_portfolio_date", "portfolio_id", "date"),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    portfolio_id = Column(Integer, ForeignKey("portfolios.id"), nullable=False)
    stock_id = Column(Integer, ForeignKey("stocks.id"), nullable=False)
    date = Column(DateTime, nullable=False, index=True)

    # Transaction details
    type = Column(String(10), nullable=False)  # BUY, SELL
    quantity = Column(Integer, nullable=False)
    price = Column(Float, nullable=False)
    commission = Column(Float, default=0.0)
    total_amount = Column(Float, nullable=False)  # quantity * price + commission

    # Optional metadata
    notes = Column(Text)

    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    portfolio = relationship("Portfolio", back_populates="transactions")
    stock = relationship("Stock")

    def __repr__(self):
        return f"<Transaction(type='{self.type}', stock_id={self.stock_id}, qty={self.quantity}, price={self.price})>"


class Position(Base):
    """Current portfolio positions"""

    __tablename__ = "positions"
    __table_args__ = (
        UniqueConstraint("portfolio_id", "stock_id", name="uq_portfolio_stock"),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    portfolio_id = Column(Integer, ForeignKey("portfolios.id"), nullable=False)
    stock_id = Column(Integer, ForeignKey("stocks.id"), nullable=False)

    # Position details
    quantity = Column(Integer, nullable=False)
    average_price = Column(Float, nullable=False)
    total_cost = Column(Float, nullable=False)  # Including commissions

    # Performance tracking
    current_price = Column(Float)
    current_value = Column(Float)
    unrealized_pnl = Column(Float)
    unrealized_pnl_pct = Column(Float)

    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    portfolio = relationship("Portfolio", back_populates="positions")
    stock = relationship("Stock")

    def __repr__(self):
        return f"<Position(stock_id={self.stock_id}, qty={self.quantity}, avg_price={self.average_price})>"


# ============================================================================
# REMOVED FEATURES (2025-11-29)
# ============================================================================
# The following models were removed as they were never implemented:
#
# - DataQualityMetric: Quality logging (use in-memory quality_stats instead)
# - SystemLog: System logging (use Python logging module instead)
# - Alert: Alert management (use email alerts instead)
# - News: News scraping (not implemented)
# - SentimentData: Sentiment analysis (not implemented)
# - Prediction: ML predictions (not implemented)
# - Recommendation: Duplicate of Opportunity table
# - ModelMetrics: ML model tracking (not implemented)
# - TargetAllocation: Portfolio allocation (not implemented)
# - AdvisorSession/AdvisorMessage: LLM advisor (not implemented)
#
# Portfolio, Transaction, and Position tables kept for future implementation.
# ============================================================================


class Opportunity(Base):
    """
    Warren AI - Investment Opportunities

    Stores high-quality value investing opportunities identified by Warren AI.
    Only opportunities with score >= 80 (20%+ margin of safety) are saved.
    """

    __tablename__ = "opportunities"
    __table_args__ = (
        UniqueConstraint("ticker", "found_date", "source",
                        name="uq_opportunity_ticker_date_source"),
        Index("idx_opportunities_ticker", "ticker"),
        Index("idx_opportunities_found_date", "found_date"),
        Index("idx_opportunities_status", "status"),
        Index("idx_opportunities_score", "score"),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)
    stock_id = Column(Integer, ForeignKey("stocks.id"), nullable=True)  # Can be null for initial save

    # Basic Info
    ticker = Column(String(20), nullable=False, index=True)
    company_name = Column(String(200))
    sector = Column(String(100))

    # Scoring & Recommendation
    score = Column(Integer)  # 0-100 (Warren's confidence score)
    recommendation = Column(String(20))  # BUY, STRONG_BUY, WATCH, PASS

    # Valuation Analysis
    fair_value = Column(Float)  # Warren's calculated fair value
    current_price = Column(Float)  # Price when analyzed
    margin_of_safety = Column(Float)  # Percentage discount (20%+ required)
    target_price = Column(Float)  # Expected price target

    # Warren's Analysis
    reasoning = Column(Text)  # 2-3 paragraphs explaining the opportunity
    key_strengths = Column(Text)  # JSON array of strengths
    key_risks = Column(Text)  # JSON array of risks
    catalyst = Column(Text)  # What will drive value realization

    # Action Recommendation
    suggested_action = Column(Text)  # "Buy 150 shares at €5.60"
    time_horizon = Column(String(50))  # "12-24 months", "3-5 years"

    # Metadata
    found_date = Column(DateTime, default=datetime.utcnow, nullable=False)
    analyzed_at = Column(DateTime)  # When Warren analyzed it
    source = Column(String(50))  # weekly_scan, emergency_alert, midweek_check
    status = Column(String(20), default='new')  # new, reviewed, executed, passed, outdated
    decision_date = Column(DateTime)  # When user made decision

    # User Notes
    notes = Column(Text)  # User's personal notes on the opportunity

    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    stock = relationship("Stock")

    def __repr__(self):
        return f"<Opportunity(ticker='{self.ticker}', score={self.score}, margin={self.margin_of_safety}%, status='{self.status}')>"


class OpportunityDetailed(Base):
    """
    Enhanced opportunity tracking with scoring breakdown for algorithm audit (Schema v4.1).
    Stores complete scoring breakdown (66 columns) for data-driven optimization decisions.

    Philosophy: "Measure twice, cut once" - Collect 8-12 weeks of detailed scoring breakdown
    before deciding whether to optimize the algorithm formula.
    """
    __tablename__ = 'opportunity_detailed'
    __table_args__ = (
        UniqueConstraint('stock_id', 'scan_date', name='uq_oppdet_stock_scan'),
    )

    id = Column(Integer, primary_key=True, autoincrement=True)

    # === REFERENCE ===
    stock_id = Column(Integer, ForeignKey('stocks.id'), nullable=False)
    scan_date = Column(Date, nullable=False)
    ticker = Column(String(20), nullable=False, index=True)  # Denormalized for fast queries

    # === FINAL OUTPUT ===
    final_score = Column(Integer, nullable=False)
    recommendation = Column(String(20), nullable=False)
    fair_value = Column(Float)
    current_price = Column(Float)
    margin_of_safety = Column(Float)
    reasoning = Column(Text)

    # === RAW SCORING COMPONENTS ===
    raw_score = Column(Float)
    valuation_score = Column(Float)
    quality_score = Column(Float)
    growth_score = Column(Float)
    bonus_total = Column(Float)
    penalty_total = Column(Float)

    # === VALUATION BREAKDOWN ===
    pe_score = Column(Float)
    pb_score = Column(Float)
    dividend_score = Column(Float)

    # === QUALITY BREAKDOWN ===
    roe_score = Column(Float)
    debt_score = Column(Float)

    # === GROWTH BREAKDOWN ===
    revenue_growth_score = Column(Float)
    earnings_growth_score = Column(Float)

    # === BONUSES BREAKDOWN ===
    margin_bonus = Column(Float)
    debt_coverage_bonus = Column(Float)
    fcf_payout_bonus = Column(Float)
    peg_bonus = Column(Float)
    ev_ebitda_bonus = Column(Float)

    # === ADVANCED QUALITY (Schema v4) ===
    roic_score = Column(Float)
    interest_coverage_score = Column(Float)
    piotroski_score = Column(Float)

    # === PENALTIES BREAKDOWN ===
    roe_negative_penalty = Column(Float)
    debt_excess_penalty = Column(Float)

    # === FAIR VALUE METHODS ===
    fair_value_pe = Column(Float)
    fair_value_pb = Column(Float)
    fair_value_ps = Column(Float)
    fair_value_fcf_yield = Column(Float)
    fair_value_ev_ebitda = Column(Float)
    fair_value_dividend = Column(Float)
    fair_value_pre_penalty = Column(Float)

    # === FAIR VALUE METHOD WEIGHTS ===
    weight_pe = Column(Float)
    weight_pb = Column(Float)
    weight_ps = Column(Float)
    weight_fcf_yield = Column(Float)
    weight_ev_ebitda = Column(Float)
    weight_dividend = Column(Float)

    # === FAIR VALUE ADJUSTMENTS ===
    fv_base = Column(Float)
    fv_quality_premium = Column(Float)
    fv_utility_bonus = Column(Float)
    fv_country_penalty = Column(Float)

    # === PARAMETERS USED ===
    pe_ratio = Column(Float)
    pb_ratio = Column(Float)
    ps_ratio = Column(Float)
    dividend_yield = Column(Float)
    roe = Column(Float)
    debt_to_equity = Column(Float)
    revenue_growth = Column(Float)
    earnings_growth = Column(Float)
    gross_margin = Column(Float)
    operating_margin = Column(Float)
    net_debt = Column(Float)
    ebitda = Column(Float)
    free_cashflow = Column(Float)
    roic = Column(Float)
    interest_coverage = Column(Float)
    piotroski_fscore = Column(Integer)

    # === APPLIED ADJUSTMENTS ===
    growth_rate_used = Column(Float)
    growth_rate_original = Column(Float)
    is_growth_capped = Column(Boolean)
    graham_multiplier = Column(Float)
    country_penalty_pct = Column(Float)

    # === SECTOR FLAGS ===
    sector = Column(String(100))
    is_financial = Column(Boolean)
    is_utility = Column(Boolean)
    is_luxury = Column(Boolean)
    is_auto_industrial = Column(Boolean)

    # === METADATA ===
    schema_version = Column(String(10), default='4.1')
    created_at = Column(DateTime, server_default=func.now())

    # Relationships
    stock = relationship("Stock")

    def __repr__(self):
        return f"<OpportunityDetailed(ticker='{self.ticker}', date={self.scan_date}, score={self.final_score})>"