EVALUATION_SYSTEM_PROMPT = """You are a financial analysis agent specialized in evaluating insider trading signals.

Your task is to analyze insider transactions combined with external market intelligence and determine if retail investors should follow this signal.

Consider:
1. Insider's role and track record
2. Transaction size relative to their holdings
3. Analyst sentiment and price targets
4. Community sentiment (Reddit, StockTwits)
5. Recent news and market conditions
6. Company fundamentals

Output a structured JSON decision with clear reasoning."""

EVALUATION_USER_PROMPT = """Analyze this insider transaction:

## Transaction Details
- Company: {ticker}
- Insider Role: {insider_role}
- Transaction Type: {transaction_type}
- Transaction Value: ${transaction_value:,.0f}
- Date: {transaction_date}

## External Intelligence

### Analyst Data
{analyst_summary}

### Community Sentiment
{community_summary}

### Recent News
{news_summary}

### Company Context
{company_context}

## Your Task
Evaluate if retail investors should follow this insider signal.

Return ONLY valid JSON:
decision,
  "risk_factors": ["risk1", "risk2"],
  "reasoning": "2-3 sentence explanation"
}}

Decision Guidelines:
- STRONG_BUY: confidence > 0.8
- BUY: confidence > 0.6
- HOLD: confidence 0.4-0.6
- PASS: confidence < 0.4
- AVOID: negative indicators
"""

# airflow/plugins/agent/evaluator.py

class AgentEvaluator:
    """LLM 기반 내부자 거래 평가 Agent"""
    
    def __init__(self, api_key: str, model: str = "claude-sonnet-4-5-20250929"):
        self.client = anthropic.Anthropic(api_key=api_key)
        self.model = model
    
    def evaluate(self, insider_trade: Dict, external_data: Dict) -> Dict:
        """내부자 거래 평가"""
        
        ticker = insider_trade['ticker']
        
        # 프롬프트 생성
        user_prompt = self._build_prompt(insider_trade, external_data)
        
        # Claude 호출
        response = self.client.messages.create(
            model=self.model,
            max_tokens=2000,
            temperature=0.3,  # 낮은 temperature로 일관성 확보
            system=EVALUATION_SYSTEM_PROMPT,
            messages=[
                {"role": "user", "content": user_prompt}
            ]
        )
        
        # JSON 파싱
        content = response.content[0].text
        evaluation = self._parse_response(content)
        
        return evaluation

# airflow/plugins/external_data/yfinance_collector.py

class YFinanceCollector:
    """Yahoo Finance 데이터 수집"""
    
    def collect_all(self, ticker: str, lookback_days: int = 7) -> Dict:
        stock = yf.Ticker(ticker)
        
        return {
            'ticker': ticker,
            'news': self._get_news(stock),              # 최근 뉴스
            'analyst_data': self._get_analyst_data(stock),  # 애널리스트 추천
            'price_history': self._get_price_history(stock, lookback_days),
            'company_info': self._get_company_info(stock)
        }
    
    def _get_analyst_data(self, stock) -> Dict:
        """애널리스트 추천 및 목표가"""
        
        info = stock.info
        recommendations = stock.recommendations
        
        # 최근 3개월 추천 요약
        recent = recommendations[recommendations.index > 
                               (datetime.now() - timedelta(days=90))]
        
        recommendation_summary = {
            'strong_buy': int(recent.get('strongBuy', 0).sum()),
            'buy': int(recent.get('buy', 0).sum()),
            'hold': int(recent.get('hold', 0).sum()),
            'sell': int(recent.get('sell', 0).sum())
        }
        
        # 상승 여력 계산
        target_mean = info.get('targetMeanPrice')
        current = info.get('currentPrice')
        upside_potential = (target_mean / current - 1) * 100 if target_mean and current else 0
        
        return {
            'recommendation_summary': recommendation_summary,
            'target_mean': target_mean,
            'upside_potential': upside_potential
        }

# airflow/plugins/external_data/stocktwits_collector.py

class StockTwitsCollector:
    """StockTwits sentiment 수집"""
    
    BASE_URL = "https://api.stocktwits.com/api/2"
    
    def collect_sentiment(self, ticker: str, limit: int = 30) -> Dict:
        url = f"{self.BASE_URL}/streams/symbol/{ticker}.json"
        response = requests.get(url, timeout=10)
        data = response.json()
        
        messages = data.get('messages', [])
        
        return {
            'ticker': ticker,
            'message_count': len(messages),
            'sentiment_summary': self._analyze_sentiment(messages),
            'top_messages': self._extract_top_messages(messages, limit=5)
        }
    
    def _analyze_sentiment(self, messages: List[Dict]) -> Dict:
        """Sentiment 집계"""
        
        bullish = sum(1 for msg in messages 
                     if msg.get('entities', {}).get('sentiment', {}).get('basic') == 'bullish')
        bearish = sum(1 for msg in messages 
                     if msg.get('entities', {}).get('sentiment', {}).get('basic') == 'bearish')
        total = len(messages)
        
        # Sentiment 점수 (-1 ~ +1)
        sentiment_score = (bullish - bearish) / total if total > 0 else 0
        
        return {
            'bullish_ratio': bullish / total * 100 if total else 0,
            'bearish_ratio': bearish / total * 100 if total else 0,
            'sentiment_score': sentiment_score
        }

# airflow/plugins/external_data/__init__.py

class ExternalDataAggregator:
    """병렬로 외부 데이터 수집"""
    
    def collect_for_ticker(self, ticker: str, parallel: bool = True) -> Dict:
        
        if not parallel:
            return self._collect_sequential(ticker)
        
        # ThreadPoolExecutor로 병렬 실행
        with concurrent.futures.ThreadPoolExecutor(max_workers=3) as executor:
            future_yf = executor.submit(self.yfinance.collect_all, ticker)
            future_st = executor.submit(self.stocktwits.collect_sentiment, ticker)
            future_rd = executor.submit(self.reddit.collect_discussions, ticker)
            
            # 결과 수집 (timeout 포함)
            result = {}
            try:
                result['yfinance'] = future_yf.result(timeout=20)
            except Exception as e:
                result['yfinance'] = {'error': str(e)}
            
            # ... StockTwits, Reddit 동일
        
        return result

# airflow/dags/backtest_all_trades.py

def calculate_returns_multi_period(ticker: str, entry_date, entry_price=None):
    """3개 기간의 수익률 계산"""
    
    stock = yf.Ticker(ticker)
    
    # Entry 가격
    if not entry_price:
        entry_hist = stock.history(start=entry_date, end=entry_date + timedelta(days=3))
        entry_price = float(entry_hist['Close'].iloc[0])
    
    results = {
        '7d': {'entry_price': entry_price, 'holding_days': 7},
        '14d': {'entry_price': entry_price, 'holding_days': 14},
        'current': {'entry_price': entry_price}
    }
    
    # 7일 후
    date_7d = entry_date + timedelta(days=7)
    price_7d = get_price_on_date(stock, date_7d)
    if price_7d:
        results['7d']['current_price'] = price_7d
        results['7d']['return_pct'] = round((price_7d / entry_price - 1) * 100, 2)
    
    # 14일 후, 현재 동일 로직...
    
    return results

# ❌ 작동 안 함
from .evaluator import AgentEvaluator

# ✅ 작동함
from agent.evaluator import AgentEvaluator

def save_agent_evaluation(self, evaluation: Dict) -> int:
    import json
    from decimal import Decimal
    
    def convert_decimals(obj):
        """Decimal을 float로 재귀적으로 변환"""
        if isinstance(obj, Decimal):
            return float(obj)
        elif isinstance(obj, dict):
            return {k: convert_decimals(v) for k, v in obj.items()}
        elif isinstance(obj, list):
            return [convert_decimals(item) for item in obj]
        return obj
    
    # JSON 직렬화 시 변환 적용
    json.dumps(convert_decimals(evaluation.get('key_factors', [])))

# 뮤추얼 펀드/ETF 제외 필터
WHERE ticker NOT LIKE 'NM%'
AND ticker NOT LIKE '%X'
AND ticker NOT LIKE '%Z'
AND company_name NOT LIKE '%FUND%'
AND company_name NOT LIKE '%ETF%'
AND ticker REGEXP '^[A-Z]{3,5}$'  -- 일반 주식만