import feedparser
import requests
from datetime import datetime
import pytz

KST = pytz.timezone('Asia/Seoul')

def main():
    # 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)
    
    print(f"✅ Found {len(feed.entries)} Form 4 filings")
    
    # MySQL 연결
    db = InsiderTradesDB(
        host='localhost',
        port=3308,
        user='admin',
        password='password',
        database='insider_trading'
    )
    
    parser = Form4Parser()
    price_fetcher = StockPriceFetcher()
    
    for i, entry in enumerate(feed.entries, 1):
        accession_number = entry.id.split('/')[-1]
        index_url = entry.link
        
        # 중복 체크 (가장 먼저!)
        if db.is_duplicate(accession_number):
            continue
        
        # Rate limiting (SEC 규정: 10 req/sec)
        time.sleep(0.15)
        
        # XML 파싱
        parsed_data = parser.parse_form4(index_url)
        
        if not parsed_data:
            continue
        
        # 🔥 가격 보정 (Yahoo Finance)
        transactions = parsed_data.get('transactions', [])
        for trans in transactions:
            if trans.get('price_per_share', 0) == 0 and \
               trans.get('transaction_type') in ['BUY', 'SELL']:
                
                ticker = parsed_data['issuer'].get('ticker')
                trans_date = trans.get('transaction_date')
                
                if ticker and trans_date:
                    price = price_fetcher.get_closing_price_with_retry(
                        ticker, trans_date
                    )
                    
                    if price:
                        trans['price_per_share'] = price
                        trans['transaction_value'] = trans['shares'] * price
        
        # MySQL 저장
        db.insert_filing(accession_number, parsed_data)

def get_xml_url_from_index(self, index_url: str):
    soup = BeautifulSoup(response.text, 'html.parser')
    
    # 방법 1: ownership.xml 링크 직접 찾기
    for link in soup.find_all('a', href=True):
        if 'ownership.xml' in link['href'].lower():
            return self._build_absolute_url(link['href'])
    
    # 방법 2: .xml 파일 찾기
    for link in soup.find_all('a', href=True):
        if link['href'].endswith('.xml'):
            return self._build_absolute_url(link['href'])
    
    # 방법 3: 테이블에서 XML 타입 찾기
    for table in soup.find_all('table'):
        for row in table.find_all('tr'):
            if 'xml' in row.get_text().lower():
                link = row.find('a', href=True)
                if link:
                    return self._build_absolute_url(link['href'])
    
    # 방법 4: Accession number로 추측 후 HEAD 요청
    match = re.search(r'/(\d{10}-\d{2}-\d{6})/', index_url)
    if match:
        accession = match.group(1)
        base_url = '/'.join(index_url.split('/')[:-1])
        
        for filename in ['ownership.xml', f'{accession}.xml']:
            test_url = f"{base_url}/{filename}"
            if requests.head(test_url).status_code == 200:
                return test_url
    
    return None

class StockPriceFetcher:
    def get_closing_price_with_retry(self, ticker, date, max_retries=3):
        """재시도 로직 포함"""
        for attempt in range(max_retries):
            try:
                stock = yf.Ticker(ticker)
                hist = stock.history(
                    start=date,
                    end=(datetime.strptime(date, '%Y-%m-%d') + 
                         timedelta(days=3)).strftime('%Y-%m-%d')
                )
                
                if not hist.empty:
                    return round(hist['Close'].iloc[0], 2)
                    
            except Exception as e:
                if attempt < max_retries - 1:
                    time.sleep(2 ** attempt)  # Exponential backoff
                    continue
                    
        return None

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime, timedelta
import pytz

KST = pytz.timezone('Asia/Seoul')

default_args = {
    'owner': 'airflow',
    'depends_on_past': False,
    'start_date': datetime(2026, 1, 1, tzinfo=KST),
    'retries': 1,
    'retry_delay': timedelta(minutes=10),
}

dag = DAG(
    'daily_insider_ranking',
    default_args=default_args,
    description='Calculate top 10 insider buy/sell rankings',
    schedule_interval='18 9 * * *',  # UTC 09:18 = KST 18:18
    catchup=False,
    tags=['insider-trading', 'ranking', 'daily'],
)

# Tasks
task_top_buys = PythonOperator(
    task_id='calculate_top_buys',
    python_callable=calculate_top_buys,
    dag=dag,
)

task_top_sells = PythonOperator(
    task_id='calculate_top_sells',
    python_callable=calculate_top_sells,
    dag=dag,
)

task_slack = PythonOperator(
    task_id='send_slack_notification',
    python_callable=send_slack_notification,
    dag=dag,
)

# Dependencies
[task_top_buys, task_top_sells] >> task_slack

def calculate_top_buys(**context):
    today_kst = datetime.now(KST).strftime('%Y-%m-%d')
    
    sql = f"""
        SELECT 
            ticker,
            company_name,
            COUNT(*) as buy_count,
            SUM(shares) as total_shares,
            ROUND(AVG(price_per_share), 2) as avg_price,
            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
    """

def send_slack_notification(**context):
    top_buys = context['task_instance'].xcom_pull(
        task_ids='calculate_top_buys',
        key='top_buys'
    )
    
    # 매수 섹션
    buy_text = "*🟢 TOP 10 INSIDER BUYS*\n"
    for i, row in enumerate(top_buys, 1):
        emoji = "🔥" if row['total_buy_value'] >= 10000000 else "⭐"
        
        buy_text += (
            f"{i}. {emoji} *{row['ticker']}* - {row['company_name'][:40]}\n"
            f"   💰 *${row['total_buy_value']:,.0f}* "
            f"({row['total_shares']:,} shares @ ${row['avg_price']})\n"
            f"   📊 {row['buy_count']} txn(s) | "
            f"👥 {row['insider_count']} insider(s)\n\n"
        )
    
    message = {
        "blocks": [
            {
                "type": "header",
                "text": {
                    "type": "plain_text",
                    "text": "📊 Daily Insider Trading Report"
                }
            },
            {
                "type": "section",
                "text": {"type": "mrkdwn", "text": buy_text}
            },
            # ... 매도 섹션, 인사이트 섹션
        ]
    }
    
    requests.post(webhook_url, json=message)