models.py

from datetime import datetime, timedelta
from sqlalchemy import Column, String, Integer, Float, Boolean, DateTime, ForeignKey, UniqueConstraint, Index
from sqlalchemy.orm import declarative_base

Base = declarative_base()


class Company(Base):
    """상장사 기본 정보. 우선주는 수집 시 skip."""
    __tablename__ = "company"

    ticker = Column(String, primary_key=True)
    corp_code = Column(String)        # DART corp_code (8자리)
    name = Column(String, nullable=False)
    market = Column(String)           # KOSPI / KOSDAQ
    fiscal_year_end = Column(Integer, default=12)  # 결산월 (12=12월 결산)


class PriceSnapshot(Base):
    """현재 주가 + 시가총액. 네이버 컨센서스 수집 시 함께 저장."""
    __tablename__ = "price_snapshot"

    ticker = Column(String, ForeignKey("company.ticker"), primary_key=True)
    current_price = Column(Float)
    market_cap = Column(Float)        # 억원
    updated_at = Column(DateTime)


class AnnualData(Base):
    """연간 재무 데이터. 확정실적(is_estimate=False)과 컨센서스(is_estimate=True) 통합."""
    __tablename__ = "annual_data"

    id = Column(Integer, primary_key=True, autoincrement=True)
    ticker = Column(String, ForeignKey("company.ticker"), nullable=False)
    year = Column(Integer, nullable=False)
    is_estimate = Column(Boolean, nullable=False, default=False)

    # 실적
    revenue = Column(Float)           # 매출액 (억원)
    op_income = Column(Float)         # 영업이익 (억원)
    net_income = Column(Float)        # 당기순이익 (억원) — 확정만

    # 주당 지표
    eps = Column(Float)               # EPS (원)
    bps = Column(Float)               # BPS (원)
    dividend_per_share = Column(Float) # 주당배당금 (원)
    dividend_yield = Column(Float)    # 배당수익률 (%)

    # 재무상태
    equity = Column(Float)            # 자본총계 (억원)
    total_assets = Column(Float)      # 자산총계 (억원)
    shares_outstanding = Column(Float)

    # 안정성 비율 — 확정연도만 (추정연도는 NULL)
    roe = Column(Float)               # ROE (%)
    roa = Column(Float)               # ROA (%)
    debt_ratio = Column(Float)        # 부채비율 (%)
    interest_bearing_debt = Column(Float)  # 이자발생부채 (억원)

    # 메타
    is_stale = Column(Boolean, default=False)
    updated_at = Column(DateTime)

    __table_args__ = (
        Index("ix_annual_data_ticker_year_is_estimate", "ticker", "year", "is_estimate"),
        Index("ix_annual_data_ticker_is_estimate", "ticker", "is_estimate"),
    )


class User(Base):
    """Google OAuth 로그인 사용자."""
    __tablename__ = "user"

    id = Column(Integer, primary_key=True, autoincrement=True)
    google_id = Column(String, unique=True, nullable=False)
    email = Column(String, nullable=False)
    name = Column(String)
    is_admin = Column(Boolean, default=False)
    created_at = Column(DateTime, default=lambda: datetime.utcnow() + timedelta(hours=9))


class Watchlist(Base):
    """관심종목. 사용자별 격리."""
    __tablename__ = "watchlist"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("user.id"), nullable=False)
    ticker = Column(String, ForeignKey("company.ticker"), nullable=False)
    added_at = Column(DateTime, default=lambda: datetime.utcnow() + timedelta(hours=9))
    memo = Column(String, default="")

    __table_args__ = (UniqueConstraint("user_id", "ticker"),)


class BondRate(Base):
    """국채 10년물 금리. 단일 row (id=1) upsert."""
    __tablename__ = "bond_rate"

    id = Column(Integer, primary_key=True, default=1)
    rate = Column(Float, nullable=False)   # % (예: 3.2)
    fetched_at = Column(DateTime)


class Feedback(Base):
    """사용자 피드백/댓글."""
    __tablename__ = "feedback"

    id = Column(Integer, primary_key=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("user.id"), nullable=False)
    content = Column(String, nullable=False)
    created_at = Column(DateTime, default=lambda: datetime.utcnow() + timedelta(hours=9))