# SEC RSS 피드 호출
url = "https://www.sec.gov/cgi-bin/browse-edgar?action=getcurrent&type=4&count=100&output=atom"

headers = {
    'User-Agent': 'MyCompany contact@example.com',  # 필수!
    'Accept': 'application/atom+xml',
}

response = requests.get(url, headers=headers, timeout=30)
feed = feedparser.parse(response.content)

class Form4Parser:
    def parse_form4(self, index_url):
        # 1. Index 페이지에서 XML URL 찾기
        xml_url = self._get_xml_url_from_index(index_url)
        
        # 2. XML 다운로드
        response = requests.get(xml_url, headers=headers)
        soup = BeautifulSoup(response.content, 'xml')
        
        # 3. 발행 회사 정보
        issuer = {
            'ticker': soup.find('issuerTradingSymbol').text,
            'name': soup.find('issuerName').text,
            'cik': soup.find('issuerCik').text,
        }
        
        # 4. 내부자 정보
        owner = {
            'name': soup.find('rptOwnerName').text,
            'is_director': soup.find('isDirector').text == '1',
            'is_officer': soup.find('isOfficer').text == '1',
        }
        
        # 5. 거래 내역 파싱
        transactions = []
        for txn in soup.find_all('nonDerivativeTransaction'):
            transactions.append({
                'transaction_type': self._map_transaction_code(
                    txn.find('transactionCode').text
                ),
                'shares': int(txn.find('transactionShares').find('value').text),
                'price_per_share': float(txn.find('transactionPricePerShare').find('value').text),
                'transaction_value': shares * price,
                'transaction_date': txn.find('transactionDate').find('value').text,
            })
        
        return {
            'issuer': issuer,
            'owner': owner,
            'transactions': transactions,
        }

def _map_transaction_code(self, code):
    mapping = {
        'P': 'BUY',    # Purchase
        'S': 'SELL',   # Sale
        'A': 'OPTION', # Award/Grant
        'M': 'OPTION', # Exercise of option
        'G': 'OTHER',  # Gift
        'X': 'OTHER',  # Exchange
    }
    return mapping.get(code, 'OTHER')

dag = DAG(
    'daily_insider_ranking',
    schedule_interval='18 9 * * *',  # UTC 09:18 = KST 18:18
    catchup=False,
)

# Task 1: 매수 랭킹 계산
calculate_top_buys → 
    # Task 2: 매도 랭킹 계산
    calculate_top_sells → 
        # Task 3: Redis 저장
        save_to_redis → 
            # Task 4: MySQL 백업
            save_to_mysql_backup → 
                # Task 5: 요약 리포트
                generate_summary → 
                    # Task 6: Slack 알림
                    send_slack_notification

def calculate_top_buys(**context):
    """오늘 크롤링된 내부자 매수 상위 10개"""
    
    today_kst = datetime.now(KST).strftime('%Y-%m-%d')
    
    sql = f"""
        SELECT 
            ticker,
            company_name,
            COUNT(*) as buy_count,
            SUM(transaction_value) as total_buy_value,
            COUNT(DISTINCT insider_name) as insider_count,
            GROUP_CONCAT(
                DISTINCT insider_name 
                ORDER BY insider_name 
                SEPARATOR ', '
            ) as insiders
        FROM insider_trades
        WHERE DATE(created_at_kst) = '{today_kst}'
          AND transaction_type IN ('BUY', 'OPTION')
          AND transaction_value > 0
        GROUP BY ticker, company_name
        ORDER BY total_buy_value DESC
        LIMIT 10
    """
    
    cursor.execute(sql)
    results = cursor.fetchall()
    
    # XCom에 저장 (다음 Task에서 사용)
    context['task_instance'].xcom_push(key='top_buys', value=results)
    
    return len(results)

def send_slack_notification(**context):
    top_buys = context['task_instance'].xcom_pull(
        task_ids='calculate_top_buys',
        key='top_buys'
    )
    
    top_sells = context['task_instance'].xcom_pull(
        task_ids='calculate_top_sells',
        key='top_sells'
    )
    
    # 통계 계산
    total_buy_value = sum(row['total_buy_value'] for row in top_buys)
    total_sell_value = sum(row['total_sell_value'] for row in top_sells)
    
    # 매수 섹션
    buy_text = "*🟢 TOP 10 INSIDER BUYS*\n"
    for i, row in enumerate(top_buys, 1):
        # 금액별 이모지
        emoji = "🔥" if row['total_buy_value'] >= 1000000 else "⭐"
        
        buy_text += (
            f"{i}. {emoji} *{row['ticker']}* - {row['company_name'][:40]}\n"
            f"   💰 ${row['total_buy_value']:,.2f} | "
            f"📊 {row['buy_count']} txn(s) | "
            f"👥 {row['insider_count']} insider(s)\n"
        )
    
    # 인사이트
    insights = "*💡 Key Insights*\n"
    if total_buy_value > 0 and total_sell_value > 0:
        ratio = total_sell_value / total_buy_value
        if ratio > 5:
            insights += f"⚠️ Heavy selling (Sell/Buy: {ratio:.1f}x)\n"
    
    # Slack Blocks API
    message = {
        "blocks": [
            {"type": "header", "text": {"type": "plain_text", "text": "📊 Daily Insider Trading Report"}},
            {"type": "section", "text": {"type": "mrkdwn", "text": buy_text}},
            {"type": "divider"},
            {"type": "section", "text": {"type": "mrkdwn", "text": sell_text}},
            {"type": "section", "text": {"type": "mrkdwn", "text": insights}},
        ]
    }
    
    requests.post(webhook_url, json=message)

2025-12-28 11:45:31 - ERROR - ❌ Error fetching RSS: 403 Client Error

# 복잡한 버전 (403)
'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X...) Chrome/120.0.0.0 Safari/537.36'

# 단순한 버전 (성공!)
'User-Agent': 'MyCompany contact@example.com'

# Python에서 변환 (매번 계산)
WHERE DATE(CONVERT_TZ(created_at, '+00:00', '+09:00')) = '2025-12-28'

# 랭킹 결과를 Redis에 캐싱
redis.setex(
    f'ranking:{today}',
    86400,  # 24시간
    json.dumps(ranking_data)
)

# API에서 빠르게 조회
ranking = redis.get(f'ranking:{today}')

# 현재: 건별 INSERT (느림)
for trade in trades:
    db.insert_filing(...)

# 개선: Bulk INSERT (빠름)
db.bulk_insert(trades)

# 평균 대비 3 sigma 이상 차이
if transaction_value > (avg + 3 * std):
    alert("이상 거래 감지!")