app.py

import gradio as gr
import requests
import json
import os
from datetime import datetime, timedelta
from concurrent.futures import ThreadPoolExecutor, as_completed
from functools import lru_cache
from requests.adapters import HTTPAdapter
from requests.packages.urllib3.util.retry import Retry
from openai import OpenAI
from bs4 import BeautifulSoup
import re
import pathlib
import sqlite3
import pytz

# 한국 기업 리스트
KOREAN_COMPANIES = [
    "NVIDIA",
    "ALPHABET",
    "APPLE",
    "TESLA",
    "AMAZON",
    "MICROSOFT", 
    "META", 
    "INTEL",
    "SAMSUNG",
    "HYNIX",
    "BITCOIN",   
    "crypto",
    "stock",
    "Economics",
    "Finance",
    "investing" 
]


#########################################################
# 공통 함수
#########################################################

def convert_to_seoul_time(timestamp_str):
    try:
        dt = datetime.strptime(timestamp_str, '%Y-%m-%d %H:%M:%S')
        seoul_tz = pytz.timezone('Asia/Seoul')
        seoul_time = seoul_tz.localize(dt)
        return seoul_time.strftime('%Y-%m-%d %H:%M:%S KST')
    except Exception as e:
        print(f"시간 변환 오류: {str(e)}")
        return timestamp_str

def analyze_sentiment_batch(articles, client):
    """
    OpenAI API를 통해 뉴스 기사들의 종합 감성 분석을 수행
    """
    try:
        combined_text = "\n\n".join([
            f"제목: {article.get('title', '')}\n내용: {article.get('snippet', '')}"
            for article in articles
        ])
        
        prompt = f"""다음 뉴스 모음에 대해 전반적인 감성 분석을 수행하세요:

뉴스 내용:
{combined_text}

다음 형식으로 분석해주세요:
1. 전반적 감성: [긍정/부정/중립]
2. 주요 긍정적 요소:
   - [항목1]
   - [항목2]
3. 주요 부정적 요소:
   - [항목1]
   - [항목2]
4. 종합 평가: [상세 설명]
"""

        response = client.chat.completions.create(
            model="CohereForAI/c4ai-command-r-plus-08-2024",
            messages=[{"role": "user", "content": prompt}],
            temperature=0.3,
            max_tokens=1000
        )
        
        return response.choices[0].message.content
    except Exception as e:
        return f"감성 분석 실패: {str(e)}"


#########################################################
# DB 관련
#########################################################

def init_db():
    db_path = pathlib.Path("search_results.db")
    conn = sqlite3.connect(db_path)
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS searches
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                  keyword TEXT,
                  country TEXT,
                  results TEXT,
                  timestamp DATETIME DEFAULT CURRENT_TIMESTAMP)''')
    conn.commit()
    conn.close()

def save_to_db(keyword, country, results):
    conn = sqlite3.connect("search_results.db")
    c = conn.cursor()
    seoul_tz = pytz.timezone('Asia/Seoul')
    now = datetime.now(seoul_tz)
    timestamp = now.strftime('%Y-%m-%d %H:%M:%S')
    
    c.execute("""INSERT INTO searches 
                 (keyword, country, results, timestamp) 
                 VALUES (?, ?, ?, ?)""",
              (keyword, country, json.dumps(results), timestamp))
    conn.commit()
    conn.close()

def load_from_db(keyword, country):
    conn = sqlite3.connect("search_results.db")
    c = conn.cursor()
    c.execute("SELECT results, timestamp FROM searches WHERE keyword=? AND country=? ORDER BY timestamp DESC LIMIT 1",
              (keyword, country))
    result = c.fetchone()
    conn.close()
    if result:
        return json.loads(result[0]), convert_to_seoul_time(result[1])
    return None, None

#########################################################
# "id"로 직접 로딩하기 (히스토리에서 사용)
#########################################################
def load_by_id(search_id):
    """
    DB의 PRIMARY KEY(id)로 특정 검색 기록을 로딩
    """
    conn = sqlite3.connect("search_results.db")
    c = conn.cursor()
    c.execute("SELECT keyword, country, results, timestamp FROM searches WHERE id=?", (search_id,))
    row = c.fetchone()
    conn.close()
    
    if row:
        keyword, country, results_json, ts = row
        data = json.loads(results_json)
        return {
            "keyword": keyword,
            "country": country,
            "data": data,
            "timestamp": convert_to_seoul_time(ts)
        }
    return None

def display_results(articles):
    output = ""
    for idx, article in enumerate(articles, 1):
        output += f"### {idx}. {article['title']}\n"
        output += f"출처: {article['channel']}\n"
        output += f"시간: {article['time']}\n"
        output += f"링크: {article['link']}\n"
        output += f"요약: {article['snippet']}\n\n"
    return output


#########################################################
# (1) 검색 시 => 기사 + 분석 동시 출력, DB 저장
#########################################################
def search_company(company):
    error_message, articles = serphouse_search(company, "United States")
    if not error_message and articles:
        analysis = analyze_sentiment_batch(articles, client)
        store_dict = {
            "articles": articles,
            "analysis": analysis
        }
        save_to_db(company, "United States", store_dict)
        output = display_results(articles)
        output += f"\n\n### 분석 보고\n{analysis}\n"
        return output
    return f"{company}에 대한 검색 결과가 없습니다."

#########################################################
# (2) 출력 시 => DB에 저장된 기사 + 분석 함께 출력
#########################################################
def load_company(company):
    data, timestamp = load_from_db(company, "United States")
    if data:
        articles = data.get("articles", [])
        analysis = data.get("analysis", "")
        
        output = f"### {company} 검색 결과\n저장 시간: {timestamp}\n\n"
        output += display_results(articles)
        output += f"\n\n### 분석 보고\n{analysis}\n"
        return output
    return f"{company}에 대한 저장된 결과가 없습니다."


#########################################################
# (3) EarnBOT 분석 리포트
#########################################################
def show_stats():
    """
    기존 "한국 기업 뉴스 분석 리포트" -> "EarnBOT 분석 리포트"
    """
    conn = sqlite3.connect("search_results.db")
    c = conn.cursor()
    
    output = "## EarnBOT 분석 리포트\n\n"
    
    data_list = []
    for company in KOREAN_COMPANIES:
        c.execute("""
            SELECT results, timestamp 
            FROM searches 
            WHERE keyword = ? 
            ORDER BY timestamp DESC 
            LIMIT 1
        """, (company,))
        
        row = c.fetchone()
        if row:
            results_json, tstamp = row
            data_list.append((company, tstamp, results_json))
    
    conn.close()
    
    def analyze_data(item):
        comp, tstamp, results_json = item
        data = json.loads(results_json)
        articles = data.get("articles", [])
        analysis = data.get("analysis", "")
        count_articles = len(articles)
        return (comp, tstamp, count_articles, analysis)

    results_list = []
    with ThreadPoolExecutor(max_workers=5) as executor:
        futures = [executor.submit(analyze_data, dl) for dl in data_list]
        for future in as_completed(futures):
            results_list.append(future.result())
    
    for comp, tstamp, count, analysis in results_list:
        seoul_time = convert_to_seoul_time(tstamp)
        output += f"### {comp}\n"
        output += f"- 마지막 업데이트: {seoul_time}\n"
        output += f"- 저장된 기사 수: {count}건\n\n"
        if analysis:
            output += "#### 뉴스 감성 분석\n"
            output += f"{analysis}\n\n"
        output += "---\n\n"
    
    return output


#########################################################
# 전체 검색 (병렬) / 전체 출력 / 전체 리포트
#########################################################
def search_all_companies():
    overall_result = "# [전체 검색 결과]\n\n"
    
    def do_search(comp):
        return comp, search_company(comp)
    
    with ThreadPoolExecutor(max_workers=5) as executor:
        futures = [executor.submit(do_search, c) for c in KOREAN_COMPANIES]
        for future in as_completed(futures):
            comp, res_text = future.result()
            overall_result += f"## {comp}\n"
            overall_result += res_text + "\n\n"
    
    return overall_result

def load_all_companies():
    overall_result = "# [전체 출력 결과]\n\n"
    for comp in KOREAN_COMPANIES:
        overall_result += f"## {comp}\n"
        overall_result += load_company(comp)
        overall_result += "\n"
    return overall_result

def full_summary_report():
    search_result_text = search_all_companies()
    load_result_text = load_all_companies()
    stats_text = show_stats()
    
    combined_report = (
        "# 전체 분석 보고 요약\n\n"
        "아래 순서로 실행되었습니다:\n"
        "1. 모든 종목 검색(병렬) + 분석 => 2. 모든 종목 DB 결과 출력 => 3. 전체 감성 분석 통계\n\n"
        f"{search_result_text}\n\n"
        f"{load_result_text}\n\n"
        "## [전체 감성 분석 통계]\n\n"
        f"{stats_text}"
    )
    return combined_report


#########################################################
# (추가) 사용자 임의 검색 함수 (두 번째 탭)
#########################################################
def search_custom(query, country):
    """
    사용자가 입력한 (query, country)에 대해 
    1) 검색 + 분석 => DB 저장
    2) DB 로드 => 결과(기사 목록 + 분석) 출력
    """
    error_message, articles = serphouse_search(query, country)
    if error_message:
        return f"오류 발생: {error_message}"
    if not articles:
        return "검색 결과가 없습니다."
    
    analysis = analyze_sentiment_batch(articles, client)
    save_data = {
        "articles": articles,
        "analysis": analysis
    }
    save_to_db(query, country, save_data)
    
    loaded_data, timestamp = load_from_db(query, country)
    if not loaded_data:
        return "DB에서 로드 실패"

    arts = loaded_data.get("articles", [])
    analy = loaded_data.get("analysis", "")
    
    out = f"## [사용자 임의 검색 결과]\n\n"
    out += f"**키워드**: {query}\n\n"
    out += f"**국가**: {country}\n\n"
    out += f"**저장 시간**: {timestamp}\n\n"

    out += display_results(arts)
    out += f"### 뉴스 감성 분석\n{analy}\n"
    
    return out


#########################################################
# (추가) 히스토리 함수
#########################################################
def get_custom_search_history():
    """
    KOREAN_COMPANIES에 없는 keyword로 검색된 기록만 
    (id, timestamp, keyword, country) 형태로 반환
    최신순 정렬
    """
    # set으로 만들어서 빠르게 검색
    company_set = set(k.lower() for k in KOREAN_COMPANIES)
    
    conn = sqlite3.connect("search_results.db")
    c = conn.cursor()
    c.execute("""SELECT id, keyword, country, timestamp 
                 FROM searches 
                 ORDER BY timestamp DESC""")
    rows = c.fetchall()
    conn.close()
    
    history_list = []
    for (sid, kw, cty, ts) in rows:
        # KOREAN_COMPANIES 에 없는 경우 -> 사용자 임의 검색
        if kw.lower() not in company_set:
            # "id, 시간, 키워드(국가)" 로 표시할 텍스트 구성
            # 예: "12 | 2025-01-22 10:23:00 KST | Apple (United States)"
            # 실제 dropdown value 는 sid (id) 만 저장
            display_time = convert_to_seoul_time(ts)
            label = f"{sid} | {display_time} | {kw} ({cty})"
            history_list.append((str(sid), label))
    
    return history_list

def view_history_record(record_id):
    """
    Dropdown 에서 선택된 record_id (문자열)로부터
    해당 검색 결과(기사+분석)을 Markdown 형태로 반환
    """
    if not record_id:
        return "기록이 없습니다."
    
    data = load_by_id(int(record_id))
    if not data:
        return "해당 ID의 기록이 없습니다."
    
    keyword = data["keyword"]
    country = data["country"]
    timestamp = data["timestamp"]
    stored = data["data"]  # { articles, analysis }
    
    articles = stored.get("articles", [])
    analysis = stored.get("analysis", "")
    
    out = f"### [히스토리 검색 결과]\n\n"
    out += f"- ID: {record_id}\n"
    out += f"- 키워드: {keyword}\n"
    out += f"- 국가: {country}\n"
    out += f"- 저장 시간: {timestamp}\n\n"
    out += display_results(articles)
    out += f"\n\n### 분석 보고\n{analysis}\n"
    
    return out


#########################################################
# SerpHouse API
#########################################################
def is_english(text):
    return all(ord(char) < 128 for char in text.replace(' ', '').replace('-', '').replace('_', ''))

@lru_cache(maxsize=100)
def translate_query(query, country):
    try:
        if is_english(query):
            return query
        
        if country in COUNTRY_LANGUAGES:
            if country == "South Korea":
                return query
            target_lang = COUNTRY_LANGUAGES[country]
            
            url = "https://translate.googleapis.com/translate_a/single"
            params = {
                "client": "gtx",
                "sl": "auto",
                "tl": target_lang,
                "dt": "t",
                "q": query
            }
            
            session = requests.Session()
            retries = Retry(total=3, backoff_factor=0.5)
            session.mount('https://', HTTPAdapter(max_retries=retries))
            
            response = session.get(url, params=params, timeout=(5, 10))
            translated_text = response.json()[0][0][0]
            return translated_text
            
        return query
        
    except Exception as e:
        print(f"번역 오류: {str(e)}")
        return query

def search_serphouse(query, country, page=1, num_result=10):
    url = "https://api.serphouse.com/serp/live"
    
    now = datetime.utcnow()
    yesterday = now - timedelta(days=1)
    date_range = f"{yesterday.strftime('%Y-%m-%d')},{now.strftime('%Y-%m-%d')}"
    
    translated_query = translate_query(query, country)
    
    payload = {
        "data": {
            "q": translated_query,
            "domain": "google.com",
            "loc": COUNTRY_LOCATIONS.get(country, "United States"),
            "lang": COUNTRY_LANGUAGES.get(country, "en"),
            "device": "desktop",
            "serp_type": "news",
            "page": str(page),
            "num": "100",
            "date_range": date_range,
            "sort_by": "date"
        }
    }

    headers = {
        "accept": "application/json",
        "content-type": "application/json",
        "authorization": f"Bearer {API_KEY}"
    }

    try:
        session = requests.Session()
        
        retries = Retry(
            total=5,
            backoff_factor=1,
            status_forcelist=[500, 502, 503, 504, 429],
            allowed_methods=["POST"]
        )
        
        adapter = HTTPAdapter(max_retries=retries)
        session.mount('http://', adapter)
        session.mount('https://', adapter)
        
        response = session.post(
            url, 
            json=payload, 
            headers=headers, 
            timeout=(30, 30)
        )
        
        response.raise_for_status()
        return {"results": response.json(), "translated_query": translated_query}
        
    except requests.exceptions.Timeout:
        return {
            "error": "검색 시간이 초과되었습니다. 잠시 후 다시 시도해주세요.",
            "translated_query": query
        }
    except requests.exceptions.RequestException as e:
        return {
            "error": f"검색 중 오류가 발생했습니다: {str(e)}",
            "translated_query": query
        }
    except Exception as e:
        return {
            "error": f"예기치 않은 오류가 발생했습니다: {str(e)}",
            "translated_query": query
        }

def format_results_from_raw(response_data):
    if "error" in response_data:
        return "Error: " + response_data["error"], []

    try:
        results = response_data["results"]
        translated_query = response_data["translated_query"]
        
        news_results = results.get('results', {}).get('results', {}).get('news', [])
        if not news_results:
            return "검색 결과가 없습니다.", []
        
        korean_domains = [
            '.kr', 'korea', 'korean', 'yonhap', 'hankyung', 'chosun', 
            'donga', 'joins', 'hani', 'koreatimes', 'koreaherald'
        ]
        korean_keywords = [
            'korea', 'korean', 'seoul', 'busan', 'incheon', 'daegu', 
            'gwangju', 'daejeon', 'ulsan', 'sejong'
        ]

        filtered_articles = []
        for idx, result in enumerate(news_results, 1):
            url = result.get("url", result.get("link", "")).lower()
            title = result.get("title", "").lower()
            channel = result.get("channel", result.get("source", "")).lower()
            
            is_korean_content = (
                any(domain in url or domain in channel for domain in korean_domains) or
                any(keyword in title for keyword in korean_keywords)
            )
            
            if not is_korean_content:
                filtered_articles.append({
                    "index": idx,
                    "title": result.get("title", "제목 없음"),
                    "link": url,
                    "snippet": result.get("snippet", "내용 없음"),
                    "channel": result.get("channel", result.get("source", "알 수 없음")),
                    "time": result.get("time", result.get("date", "알 수 없는 시간")),
                    "image_url": result.get("img", result.get("thumbnail", "")),
                    "translated_query": translated_query
                })

        return "", filtered_articles
    except Exception as e:
        return f"결과 처리 중 오류 발생: {str(e)}", []


###################################
# 환경 변수 및 CSS
###################################
ACCESS_TOKEN = os.getenv("HF_TOKEN")
if not ACCESS_TOKEN:
    raise ValueError("HF_TOKEN environment variable is not set")

client = OpenAI(
    base_url="https://api-inference.huggingface.co/v1/",
    api_key=ACCESS_TOKEN,
)

API_KEY = os.getenv("SERPHOUSE_API_KEY")

COUNTRY_LANGUAGES = {
    "United States": "en",
    "KOREA": "ko",
    "United Kingdom": "en",
    "Taiwan": "zh-TW",  
    "Canada": "en",
    "Australia": "en",
    "Germany": "de",
    "France": "fr",
    "Japan": "ja",
    "China": "zh",
    "India": "hi",
    "Brazil": "pt",
    "Mexico": "es",
    "Russia": "ru",
    "Italy": "it",
    "Spain": "es",
    "Netherlands": "nl",
    "Singapore": "en",
    "Hong Kong": "zh-HK",
    "Indonesia": "id",
    "Malaysia": "ms",
    "Philippines": "tl",
    "Thailand": "th",
    "Vietnam": "vi",
    "Belgium": "nl",
    "Denmark": "da",
    "Finland": "fi",
    "Ireland": "en",
    "Norway": "no",
    "Poland": "pl",
    "Sweden": "sv",
    "Switzerland": "de",
    "Austria": "de",
    "Czech Republic": "cs",
    "Greece": "el",
    "Hungary": "hu",
    "Portugal": "pt",
    "Romania": "ro",
    "Turkey": "tr",
    "Israel": "he",
    "Saudi Arabia": "ar",
    "United Arab Emirates": "ar",
    "South Africa": "en",
    "Argentina": "es",
    "Chile": "es",
    "Colombia": "es",
    "Peru": "es",
    "Venezuela": "es",
    "New Zealand": "en",
    "Bangladesh": "bn",
    "Pakistan": "ur",
    "Egypt": "ar",
    "Morocco": "ar",
    "Nigeria": "en",
    "Kenya": "sw",
    "Ukraine": "uk",
    "Croatia": "hr",
    "Slovakia": "sk",
    "Bulgaria": "bg",
    "Serbia": "sr",
    "Estonia": "et",
    "Latvia": "lv",
    "Lithuania": "lt",
    "Slovenia": "sl",
    "Luxembourg": "Luxembourg",
    "Malta": "Malta",
    "Cyprus": "Cyprus",
    "Iceland": "Iceland"
}

COUNTRY_LOCATIONS = {
    "United States": "United States",
    "KOREA": "kr",
    "United Kingdom": "United Kingdom",
    "Taiwan": "Taiwan",
    "Canada": "Canada",
    "Australia": "Australia",
    "Germany": "Germany",
    "France": "France",
    "Japan": "Japan",
    "China": "China",
    "India": "India",
    "Brazil": "Brazil",
    "Mexico": "Mexico",
    "Russia": "Russia",
    "Italy": "Italy",
    "Spain": "Spain",
    "Netherlands": "Netherlands",
    "Singapore": "Singapore",
    "Hong Kong": "Hong Kong",
    "Indonesia": "Indonesia",
    "Malaysia": "Malaysia",
    "Philippines": "Philippines",
    "Thailand": "Thailand",
    "Vietnam": "Vietnam",
    "Belgium": "Belgium",
    "Denmark": "Denmark",
    "Finland": "Finland",
    "Ireland": "Ireland",
    "Norway": "Norway",
    "Poland": "Poland",
    "Sweden": "Sweden",
    "Switzerland": "Switzerland",
    "Austria": "Austria",
    "Czech Republic": "Czech Republic",
    "Greece": "Greece",
    "Hungary": "Hungary",
    "Portugal": "Portugal",
    "Romania": "Romania",
    "Turkey": "Turkey",
    "Israel": "Israel",
    "Saudi Arabia": "Saudi Arabia",
    "United Arab Emirates": "United Arab Emirates",
    "South Africa": "South Africa",
    "Argentina": "Argentina",
    "Chile": "Chile",
    "Colombia": "Colombia",
    "Peru": "Peru",
    "Venezuela": "Venezuela",
    "New Zealand": "New Zealand",
    "Bangladesh": "Bangladesh",
    "Pakistan": "Pakistan",
    "Egypt": "Egypt",
    "Morocco": "Morocco",
    "Nigeria": "Nigeria",
    "Kenya": "Kenya",
    "Ukraine": "Ukraine",
    "Croatia": "Croatia",
    "Slovakia": "Slovakia",
    "Bulgaria": "Bulgaria",
    "Serbia": "Serbia",
    "Estonia": "et",
    "Latvia": "lv",
    "Lithuania": "lt",
    "Slovenia": "sl",
    "Luxembourg": "Luxembourg",
    "Malta": "Malta",
    "Cyprus": "Cyprus",
    "Iceland": "Iceland"
}

css = """
/* 전역 스타일 */
footer {visibility: hidden;}

/* 레이아웃 컨테이너 */
#status_area {
    background: rgba(255, 255, 255, 0.9);
    padding: 15px;
    border-bottom: 1px solid #ddd;
    margin-bottom: 20px;
    box-shadow: 0 2px 5px rgba(0,0,0,0.1);
}

#results_area {
    padding: 10px;
    margin-top: 10px;
}

/* 탭 스타일 */
.tabs {
    border-bottom: 2px solid #ddd !important;
    margin-bottom: 20px !important;
}

.tab-nav {
    border-bottom: none !important;
    margin-bottom: 0 !important;
}

.tab-nav button {
    font-weight: bold !important;
    padding: 10px 20px !important;
}

.tab-nav button.selected {
    border-bottom: 2px solid #1f77b4 !important;
    color: #1f77b4 !important;
}

/* 상태 메시지 */
#status_area .markdown-text {
    font-size: 1.1em;
    color: #2c3e50;
    padding: 10px 0;
}

/* 기본 컨테이너 */
.group {
    border: 1px solid #eee;
    padding: 15px;
    margin-bottom: 15px;
    border-radius: 5px;
    background: white;
}

/* 버튼 스타일 */
.primary-btn {
    background: #1f77b4 !important;
    border: none !important;
}

/* 입력 필드 */
.textbox {
    border: 1px solid #ddd !important;
    border-radius: 4px !important;
}

/* 프로그레스바 컨테이너 */
.progress-container {
    position: fixed;
    top: 0;
    left: 0;
    width: 100%;
    height: 6px;
    background: #e0e0e0;
    z-index: 1000;
}

/* 프로그레스bar */
.progress-bar {
    height: 100%;
    background: linear-gradient(90deg, #2196F3, #00BCD4);
    box-shadow: 0 0 10px rgba(33, 150, 243, 0.5);
    transition: width 0.3s ease;
    animation: progress-glow 1.5s ease-in-out infinite;
}

/* 프로그레스 텍스트 */
.progress-text {
    position: fixed;
    top: 8px;
    left: 50%;
    transform: translateX(-50%);
    background: #333;
    color: white;
    padding: 4px 12px;
    border-radius: 15px;
    font-size: 14px;
    z-index: 1001;
    box-shadow: 0 2px 5px rgba(0,0,0,0.2);
}

/* 프로그레스바 애니메이션 */
@keyframes progress-glow {
    0% {
        box-shadow: 0 0 5px rgba(33, 150, 243, 0.5);
    }
    50% {
        box-shadow: 0 0 20px rgba(33, 150, 243, 0.8);
    }
    100% {
        box-shadow: 0 0 5px rgba(33, 150, 243, 0.5);
    }
}

/* 반응형 디자인 */
@media (max-width: 768px) {
    .group {
        padding: 10px;
        margin-bottom: 15px;
    }
    
    .progress-text {
        font-size: 12px;
        padding: 3px 10px;
    }
}

/* 로딩 상태 표시 개선 */
.loading {
    opacity: 0.7;
    pointer-events: none;
    transition: opacity 0.3s ease;
}

/* 결과 컨테이너 애니메이션 */
.group {
    transition: all 0.3s ease;
    opacity: 0;
    transform: translateY(20px);
}

.group.visible {
    opacity: 1;
    transform: translateY(0);
}

/* Examples 스타일링 */
.examples-table {
    margin-top: 10px !important;
    margin-bottom: 20px !important;
}

.examples-table button {
    background-color: #f0f0f0 !important;
    border: 1px solid #ddd !important;
    border-radius: 4px !important;
    padding: 5px 10px !important;
    margin: 2px !important;
    transition: all 0.3s ease !important;
}

.examples-table button:hover {
    background-color: #e0e0e0 !important;
    transform: translateY(-1px) !important;
    box-shadow: 0 2px 5px rgba(0,0,0,0.1) !important;
}

.examples-table .label {
    font-weight: bold !important;
    color: #444 !important;
    margin-bottom: 5px !important;
}
"""

import gradio as gr

with gr.Blocks(theme="Yntec/HaleyCH_Theme_Orange", css=css, title="NewsAI 서비스") as iface:
    init_db()
    
    with gr.Tabs():
        # 첫 번째 탭
        with gr.Tab("Earnbot"):
            gr.Markdown("## EarnBot: 글로벌 빅테크 기업 및 투자 전망 AI 자동 분석")
            gr.Markdown(" - '전체 분석 보고 요약' 클릭 시 전체 자동 보고 생성.\n - 아래 개별 종목의 '검색(DB 자동 저장)'과 '출력(DB 자동 호출)'도 가능.\n - 하단 '수동 검색 히스토리'에서 이전에 수동 입력한 검색어 기록 확인 가능.")
            
            # 전체 분석 보고 요약
            with gr.Row():
                full_report_btn = gr.Button("전체 분석 보고 요약", variant="primary")
                full_report_display = gr.Markdown()
            
            full_report_btn.click(
                fn=full_summary_report,
                outputs=full_report_display
            )
            
            # 지정된 (KOREAN_COMPANIES) 기업 검색/출력
            with gr.Column():
                for i in range(0, len(KOREAN_COMPANIES), 2):
                    with gr.Row():
                        # 왼쪽 열
                        with gr.Column():
                            company = KOREAN_COMPANIES[i]
                            with gr.Group():
                                gr.Markdown(f"### {company}")
                                with gr.Row():
                                    search_btn = gr.Button("검색", variant="primary")
                                    load_btn = gr.Button("출력", variant="secondary")
                                result_display = gr.Markdown()
                                
                                search_btn.click(
                                    fn=lambda c=company: search_company(c),
                                    inputs=[],
                                    outputs=result_display
                                )
                                load_btn.click(
                                    fn=lambda c=company: load_company(c),
                                    inputs=[],
                                    outputs=result_display
                                )
                        
                        # 오른쪽 열
                        if i + 1 < len(KOREAN_COMPANIES):
                            with gr.Column():
                                company = KOREAN_COMPANIES[i + 1]
                                with gr.Group():
                                    gr.Markdown(f"### {company}")
                                    with gr.Row():
                                        search_btn = gr.Button("검색", variant="primary")
                                        load_btn = gr.Button("출력", variant="secondary")
                                    result_display = gr.Markdown()
                                    
                                    search_btn.click(
                                        fn=lambda c=company: search_company(c),
                                        inputs=[],
                                        outputs=result_display
                                    )
                                    load_btn.click(
                                        fn=lambda c=company: load_company(c),
                                        inputs=[],
                                        outputs=result_display
                                    )
            
            # (추가) 수동 검색 히스토리
            gr.Markdown("---")
            gr.Markdown("### 수동 검색 히스토리")
            with gr.Row():
                refresh_hist_btn = gr.Button("히스토리 갱신", variant="secondary")
            
            history_dropdown = gr.Dropdown(
                label="검색 기록 목록",
                choices=[],  # 이 부분은 click 이벤트에서 동적으로 업데이트
                value=None
            )
            hist_view_btn = gr.Button("보기", variant="primary")
            hist_result_display = gr.Markdown()
            
            # 1) 히스토리 갱신 함수
            def update_history_dropdown():
                history_list = get_custom_search_history()  # [(id, label), ...]
                labels = [lbl for (id_val, lbl) in history_list]
                values = [id_val for (id_val, lbl) in history_list]
                
                # gr.Dropdown 에 choices=[..., ...] 형태로 전달해야 하므로
                # zip 하여 (value, label) 쌍을 만듦
                # Gradio 3.x에서는 choices를 list로 주고, label/value 분리 불가.
                # -> workaround: "value|label" 식 or just store label in "choices" and store value separate
                # 여기서는 choices 에 label만 넣고, id는 label 파싱 등 해야함.
                # 좀 더 편하게: return a dictionary for "choices"
                
                # 방법1) label만 전달 -> user picks label -> then parse. 
                # 간단히 "value=label"이 되도록:
                choice_list = []
                for (id_val, label) in history_list:
                    choice_list.append(label)
                return gr.update(choices=choice_list, value=None)
            
            refresh_hist_btn.click(
                fn=update_history_dropdown,
                inputs=[],
                outputs=history_dropdown
            )
            
            # 2) 히스토리 보기 함수
            def show_history_record(selected_label):
                # selected_label 이 "id | time | keyword (country)" 형식
                if not selected_label:
                    return "히스토리가 선택되지 않았습니다."
                
                # id는 가장 앞부분의 숫자
                # 예: "12 | 2025-01-22 10:23:00 KST | Apple (United States)"
                # -> id=12
                splitted = selected_label.split("|")
                if len(splitted) < 2:
                    return "형식 오류"
                record_id = splitted[0].strip()  # "12"
                return view_history_record(record_id)
            
            hist_view_btn.click(
                fn=show_history_record,
                inputs=[history_dropdown],
                outputs=hist_result_display
            )


        # 두 번째 탭: "지정 자동 검색/분석"
        with gr.Tab("지정 자동 검색/분석"):
            gr.Markdown("## 사용자 임의 키워드 + 국가 검색/분석")
            gr.Markdown("검색 결과가 DB에 저장되며, 첫 번째 탭의 '수동 검색 히스토리'에서 확인 가능합니다.")
            
            with gr.Row():
                with gr.Column():
                    user_input = gr.Textbox(
                        label="검색어 입력",
                        placeholder="예) Apple, Samsung 등 자유롭게"
                    )
                with gr.Column():
                    country_selection = gr.Dropdown(
                        choices=list(COUNTRY_LOCATIONS.keys()),
                        value="United States",
                        label="국가 선택"
                    )
                with gr.Column():
                    custom_search_btn = gr.Button("실행", variant="primary")

            custom_search_output = gr.Markdown()
            
            custom_search_btn.click(
                fn=search_custom,
                inputs=[user_input, country_selection],
                outputs=custom_search_output
            )


iface.launch(
    server_name="0.0.0.0",
    server_port=7860,
    share=True,
    ssl_verify=False,
    show_error=True
)