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

import os
# Prefer /data (HF Spaces persistent storage), then /app/data, then local
_db_dir = "/data" if os.path.isdir("/data") else ("/app/data" if os.path.isdir("/app/data") else ".")
SQLALCHEMY_DATABASE_URL = f"sqlite:///{_db_dir}/alphaq.db"

engine = create_engine(
    SQLALCHEMY_DATABASE_URL, connect_args={"check_same_thread": False}
)
SessionLocal = sessionmaker(autocommit=False, autoflush=False, bind=engine)
Base = declarative_base()


class AnalysisRun(Base):
    __tablename__ = "analysis_runs"

    id = Column(Integer, primary_key=True, index=True)
    run_date = Column(String, index=True)
    created_at = Column(DateTime, default=datetime.utcnow)
    status = Column(String, default="pending")  # pending, running, complete, failed
    market_summary = Column(Text)
    scoring_methodology = Column(Text)
    total_candidates_screened = Column(Integer, default=0)
    sectors_analyzed = Column(JSON)
    master_watchlist = Column(JSON)
    disclaimer = Column(Text)
    error_message = Column(Text)
    raw_response = Column(Text)


class StockPick(Base):
    __tablename__ = "stock_picks"

    id = Column(Integer, primary_key=True, index=True)
    run_id = Column(Integer, index=True)
    rank = Column(Integer)
    ticker = Column(String, index=True)
    company = Column(String)
    sector = Column(String)
    current_price = Column(Float)
    pct_off_1mo = Column(Float)
    pct_off_52wk = Column(Float)
    pe_trailing = Column(Float)
    pe_forward = Column(Float)
    pe_vs_sector = Column(String)
    peg_ratio = Column(Float)
    price_to_fcf = Column(Float)
    fcf_positive = Column(Boolean)
    debt_to_equity = Column(Float)
    revenue_growth_3yr = Column(Float)
    eps_growth_3yr = Column(Float)
    gross_margin = Column(Float)
    operating_margin = Column(Float)
    margin_trend = Column(String)
    roic = Column(Float)
    rsi_14 = Column(Float)
    pct_below_50ma = Column(Float)
    pct_below_200ma = Column(Float)
    support_level = Column(Float)
    why_dropped = Column(Text)
    rebound_thesis = Column(Text)
    rebound_confidence = Column(String)
    bull_case_3_5yr = Column(Text)
    key_risks = Column(Text)
    suggested_entry_zone = Column(String)
    suggested_position_size = Column(String)
    stop_loss_short_term = Column(String)
    analyst_target = Column(Float)
    analyst_rating = Column(String)
    next_earnings = Column(String)
    earnings_risk = Column(Boolean)


class AppSettings(Base):
    __tablename__ = "app_settings"

    id = Column(Integer, primary_key=True, index=True)
    key = Column(String, unique=True, index=True)
    value = Column(Text)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)


def init_db():
    Base.metadata.create_all(bind=engine)


def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()