EasyCrawler Task별 작업 지침서

# Python 3.9+ 설치 확인
python --version

# Node.js 16+ 설치 확인  
node --version

# Git 설치 확인
git --version

mkdir easycrawler
cd easycrawler

# 폴더 구조 생성
mkdir -p extension/{popup,content,background}
mkdir -p backend/{app/{api,core,models,services},crawler,scheduler,tests}
mkdir -p templates docs deploy
mkdir -p data/{templates,results,logs}

fastapi==0.104.1
uvicorn==0.24.0
beautifulsoup4==4.12.2
selenium==4.15.2
playwright==1.40.0
pandas==2.1.3
openpyxl==3.1.2
requests==2.31.0
pydantic==2.5.0
sqlalchemy==2.0.23
redis==5.0.1
schedule==1.2.0
python-multipart==0.0.6
jinja2==3.1.2

{
  "name": "easycrawler-extension",
  "version": "1.0.0",
  "description": "Easy Web Crawling Tool",
  "scripts": {
    "build": "webpack --mode=production",
    "dev": "webpack --mode=development --watch"
  },
  "devDependencies": {
    "webpack": "^5.89.0",
    "webpack-cli": "^5.1.4"
  }
}

import requests
from urllib.robotparser import RobotFileParser
from urllib.parse import urljoin, urlparse
from typing import Dict, Optional
import logging

class SiteAnalyzer:
    def __init__(self):
        self.logger = logging.getLogger(__name__)
    
    def check_robots_txt(self, url: str, user_agent: str = "*") -> Dict:
        """
        robots.txt 확인
        
        Args:
            url: 확인할 웹사이트 URL
            user_agent: 사용할 User-Agent
            
        Returns:
            {
                "allowed": bool,
                "robots_url": str,
                "crawl_delay": int,
                "sitemap": List[str]
            }
        """
        try:
            parsed_url = urlparse(url)
            base_url = f"{parsed_url.scheme}://{parsed_url.netloc}"
            robots_url = urljoin(base_url, "/robots.txt")
            
            # RobotFileParser 사용
            rp = RobotFileParser()
            rp.set_url(robots_url)
            rp.read()
            
            # 크롤링 허용 여부 확인
            allowed = rp.can_fetch(user_agent, url)
            
            # Crawl-delay 확인
            crawl_delay = rp.crawl_delay(user_agent) or 1
            
            # Sitemap 확인
            sitemaps = list(rp.site_maps()) if hasattr(rp, 'site_maps') else []
            
            return {
                "allowed": allowed,
                "robots_url": robots_url,
                "crawl_delay": crawl_delay,
                "sitemaps": sitemaps,
                "status": "success"
            }
            
        except Exception as e:
            self.logger.error(f"robots.txt 확인 실패: {str(e)}")
            return {
                "allowed": True,  # 기본값으로 허용
                "robots_url": None,
                "crawl_delay": 1,
                "sitemaps": [],
                "status": "error",
                "error": str(e)
            }

import pytest
from app.core.site_analyzer import SiteAnalyzer
from app.models.schemas import AnalyzeRequest, AnalyzeResponse

router = APIRouter()
logger = logging.getLogger(__name__)

class AnalyzeRequest(BaseModel):
    url: HttpUrl
    user_agent: str = "*"

class AnalyzeResponse(BaseModel):
    success: bool
    url: str
    robots: Dict[str, Any]
    javascript: Dict[str, Any]
    difficulty: Dict[str, Any]
    recommendations: list[str]
    error: str = None

@router.post("/site", response_model=AnalyzeResponse)
async def analyze_site(request: AnalyzeRequest):
    """
    웹사이트 크롤링 가능성 분석
    """
    try:
        analyzer = SiteAnalyzer()
        url = str(request.url)
        
        logger.info(f"사이트 분석 시작: {url}")
        
        # robots.txt 확인
        robots_result = analyzer.check_robots_txt(url, request.user_agent)
        
        # JavaScript 렌더링 확인
        js_result = analyzer.detect_javascript_rendering(url)
        
        # 크롤링 난이도 계산
        difficulty_result = analyzer.calculate_difficulty_score(url)
        
        # 추천사항 생성
        recommendations = generate_recommendations(robots_result, js_result, difficulty_result)
        
        return AnalyzeResponse(
            success=True,
            url=url,
            robots=robots_result,
            javascript=js_result,
            difficulty=difficulty_result,
            recommendations=recommendations
        )
        
    except Exception as e:
        logger.error(f"사이트 분석 실패: {str(e)}")
        raise HTTPException(
            status_code=500,
            detail=f"사이트 분석 중 오류가 발생했습니다: {str(e)}"
        )

def generate_recommendations(robots_result, js_result, difficulty_result):
    """분석 결과를 바탕으로 추천사항 생성"""
    recommendations = []
    
    if not robots_result["allowed"]:
        recommendations.append("⚠️ robots.txt에서 크롤링을 금지하고 있습니다. 사이트 이용약관을 확인하세요.")
    
    if robots_result["crawl_delay"] > 3:
        recommendations.append(f"⏱️ {robots_result['crawl_delay']}초 지연을 권장합니다.")
    
    if js_result["needs_js"]:
        recommendations.append("🔧 JavaScript 렌더링이 필요합니다. 동적 크롤링 모드를 사용하세요.")
    else:
        recommendations.append("✅ 정적 크롤링으로 충분합니다.")
    
    if difficulty_result["score"] >= 4:
        recommendations.append("❌ 크롤링이 매우 어려울 수 있습니다. 대안을 고려해보세요.")
    elif difficulty_result["score"] >= 3:
        recommendations.append("⚡ 고급 설정이 필요할 수 있습니다.")
    else:
        recommendations.append("✨ 쉽게 크롤링할 수 있습니다!")
    
    return recommendations

@router.get("/test-connection")
async def test_connection():
    """API 연결 테스트"""
    return {"status": "connected", "message": "API가 정상적으로 작동 중입니다"}

from fastapi import APIRouter, HTTPException, BackgroundTasks
from pydantic import BaseModel, HttpUrl
from typing import List, Dict, Any, Optional
import logging
import uuid
from datetime import datetime

from app.services.crawler_service import CrawlerService
from app.services.excel_service import ExcelService
from app.models.schemas import CrawlRequest, CrawlResponse, SelectorData

router = APIRouter()
logger = logging.getLogger(__name__)

class SelectorData(BaseModel):
    name: str
    selector: str
    tagName: str
    text: str
    attributes: Dict[str, str]

class CrawlRequest(BaseModel):
    url: HttpUrl
    selectors: List[SelectorData]
    options: Optional[Dict[str, Any]] = {}

class CrawlResponse(BaseModel):
    success: bool
    job_id: str
    data: List[Dict[str, Any]]
    download_url: str
    stats: Dict[str, Any]
    error: str = None

@router.post("/execute", response_model=CrawlResponse)
async def execute_crawl(request: CrawlRequest, background_tasks: BackgroundTasks):
    """
    크롤링 실행
    """
    try:
        job_id = str(uuid.uuid4())
        url = str(request.url)
        
        logger.info(f"크롤링 시작 - Job ID: {job_id}, URL: {url}")
        
        # 크롤링 서비스 초기화
        crawler_service = CrawlerService()
        
        # 크롤링 실행
        crawl_result = await crawler_service.crawl_page(
            url=url,
            selectors=request.selectors,
            job_id=job_id,
            options=request.options
        )
        
        if not crawl_result["success"]:
            raise Exception(crawl_result["error"])
        
        # Excel 파일 생성
        excel_service = ExcelService()
        excel_file_path = await excel_service.create_excel(
            data=crawl_result["data"],
            job_id=job_id,
            url=url,
            selectors=request.selectors
        )
        
        # 다운로드 URL 생성
        download_url = f"/downloads/{excel_file_path.name}"
        
        # 통계 정보
        stats = {
            "total_items": len(crawl_result["data"]),
            "execution_time": crawl_result.get("execution_time", 0),
            "success_rate": crawl_result.get("success_rate", 100),
            "timestamp": datetime.now().isoformat()
        }
        
        logger.info(f"크롤링 완료 - Job ID: {job_id}, 수집 항목: {stats['total_items']}개")
        
        return CrawlResponse(
            success=True,
            job_id=job_id,
            data=crawl_result["data"],
            download_url=download_url,
            stats=stats
        )
        
    except Exception as e:
        logger.error(f"크롤링 실패 - Job ID: {job_id}, Error: {str(e)}")
        raise HTTPException(
            status_code=500,
            detail=f"크롤링 중 오류가 발생했습니다: {str(e)}"
        )

@router.post("/preview")
async def preview_crawl(request: CrawlRequest):
    """
    크롤링 미리보기 (최대 5개 항목만)
    """
    try:
        url = str(request.url)
        logger.info(f"크롤링 미리보기 - URL: {url}")
        
        # 미리보기용 옵션 설정
        preview_options = request.options.copy() if request.options else {}
        preview_options["limit"] = 5
        preview_options["preview_mode"] = True
        
        crawler_service = CrawlerService()
        result = await crawler_service.crawl_page(
            url=url,
            selectors=request.selectors,
            job_id="preview",
            options=preview_options
        )
        
        return {
            "success": result["success"],
            "data": result["data"][:5],  # 최대 5개만
            "total_found": len(result["data"]),
            "selectors_found": result.get("selectors_found", {})
        }
        
    except Exception as e:
        logger.error(f"미리보기 실패: {str(e)}")
        raise HTTPException(
            status_code=500,
            detail=f"미리보기 중 오류가 발생했습니다: {str(e)}"
        )

@router.get("/job/{job_id}/status")
async def get_job_status(job_id: str):
    """
    크롤링 작업 상태 확인
    """
    # 나중에 데이터베이스 연동 시 구현
    return {"job_id": job_id, "status": "completed", "message": "작업이 완료되었습니다"}

import asyncio
import time
from typing import List, Dict, Any, Optional
from urllib.parse import urljoin, urlparse
import logging

import requests
from bs4 import BeautifulSoup
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.common.exceptions import TimeoutException, NoSuchElementException

from app.core.config import settings
from app.models.schemas import SelectorData

logger = logging.getLogger(__name__)

class CrawlerService:
    def __init__(self):
        self.session = requests.Session()
        self.session.headers.update({
            'User-Agent': settings.DEFAULT_USER_AGENT
        })
    
    async def crawl_page(
        self, 
        url: str, 
        selectors: List[SelectorData], 
        job_id: str,
        options: Optional[Dict[str, Any]] = None
    ) -> Dict[str, Any]:
        """
        페이지 크롤링 실행
        
        Args:
            url: 크롤링할 URL
            selectors: 추출할 요소들의 선택자 정보
            job_id: 작업 ID
            options: 크롤링 옵션
            
        Returns:
            크롤링 결과 딕셔너리
        """
        start_time = time.time()
        options = options or {}
        
        try:
            logger.info(f"크롤링 시작 - URL: {url}")
            
            # JavaScript 렌더링 필요 여부 확인
            needs_js = await self._needs_javascript_rendering(url)
            
            if needs_js or options.get("force_selenium", False):
                logger.info("Selenium을 사용한 동적 크롤링")
                data = await self._crawl_with_selenium(url, selectors, options)
            else:
                logger.info("requests를 사용한 정적 크롤링")
                data = await self._crawl_with_requests(url, selectors, options)
            
            execution_time = time.time() - start_time
            
            return {
                "success": True,
                "data": data,
                "execution_time": execution_time,
                "method": "selenium" if needs_js else "requests",
                "total_items": len(data)
            }
            
        except Exception as e:
            logger.error(f"크롤링 실패: {str(e)}")
            return {
                "success": False,
                "error": str(e),
                "data": []
            }
    
    async def _needs_javascript_rendering(self, url: str) -> bool:
        """JavaScript 렌더링 필요 여부 간단 체크"""
        try:
            response = self.session.get(url, timeout=10)
            content = response.text.lower()
            
            # 간단한 휴리스틱으로 SPA 감지
            js_indicators = [
                'react', 'vue', 'angular', 'spa',
                'document.getelementbyid',
                'ajax', 'fetch(',
                'window.onload'
            ]
            
            js_count = sum(1 for indicator in js_indicators if indicator in content)
            return js_count >= 2
            
        except:
            return True  # 에러 시 안전하게 Selenium 사용
    
    async def _crawl_with_requests(
        self, 
        url: str, 
        selectors: List[SelectorData], 
        options: Dict[str, Any]
    ) -> List[Dict[str, Any]]:
        """requests와 BeautifulSoup을 사용한 정적 크롤링"""
        
        response = self.session.get(url, timeout=settings.REQUEST_TIMEOUT)
        response.raise_for_status()
        
        soup = BeautifulSoup(response.content, 'html.parser')
        
        # 페이지네이션 처리
        all_data = []
        current_url = url
        page_count = 0
        max_pages = options.get("max_pages", 1)
        
        while current_url and page_count < max_pages:
            page_count += 1
            logger.info(f"크롤링 중 - 페이지 {page_count}: {current_url}")
            
            if page_count > 1:
                response = self.session.get(current_url, timeout=settings.REQUEST_TIMEOUT)
                response.raise_for_status()
                soup = BeautifulSoup(response.content, 'html.parser')
            
            # 데이터 추출
            page_data = self._extract_data_from_soup(soup, selectors)
            all_data.extend(page_data)
            
            # 다음 페이지 URL 찾기
            current_url = self._find_next_page_url(soup, current_url, options)
            
            # 미리보기 모드에서는 한 페이지만
            if options.get("preview_mode", False):
                break
                
            # 제한 확인
            if options.get("limit") and len(all_data) >= options["limit"]:
                all_data = all_data[:options["limit"]]
                break
            
            # 크롤링 지연
            await asyncio.sleep(settings.DEFAULT_CRAWL_DELAY)
        
        return all_data
    
    async def _crawl_with_selenium(
        self, 
        url: str, 
        selectors: List[SelectorData], 
        options: Dict[str, Any]
    ) -> List[Dict[str, Any]]:
        """Selenium을 사용한 동적 크롤링"""
        
        chrome_options = Options()
        chrome_options.add_argument("--headless")
        chrome_options.add_argument("--no-sandbox")
        chrome_options.add_argument("--disable-dev-shm-usage")
        chrome_options.add_argument("--disable-gpu")
        chrome_options.add_argument(f"--user-agent={settings.DEFAULT_USER_AGENT}")
        
        driver = webdriver.Chrome(options=chrome_options)
        
        try:
            all_data = []
            page_count = 0
            max_pages = options.get("max_pages", 1)
            
            driver.get(url)
            
            # 페이지 로딩 대기
            WebDriverWait(driver, 10).until(
                EC.presence_of_element_located((By.TAG_NAME, "body"))
            )
            
            while page_count < max_pages:
                page_count += 1
                logger.info(f"Selenium 크롤링 중 - 페이지 {page_count}")
                
                # JavaScript 실행 완료 대기
                await asyncio.sleep(3)
                
                # 데이터 추출
                page_data = self._extract_data_from_selenium(driver, selectors)
                all_data.extend(page_data)
                
                # 미리보기 모드에서는 한 페이지만
                if options.get("preview_mode", False):
                    break
                
                # 다음 페이지 이동 시도
                if not self._navigate_to_next_page(driver, options):
                    break
                
                # 제한 확인
                if options.get("limit") and len(all_data) >= options["limit"]:
                    all_data = all_data[:options["limit"]]
                    break
                
                await asyncio.sleep(settings.DEFAULT_CRAWL_DELAY)
            
            return all_data
            
        finally:
            driver.quit()
    
    def _extract_data_from_soup(
        self, 
        soup: BeautifulSoup, 
        selectors: List[SelectorData]
    ) -> List[Dict[str, Any]]:
        """BeautifulSoup에서 데이터 추출"""
        
        # 첫 번째 선택자로 반복 요소들 찾기
        first_selector = selectors[0]
        elements = soup.select(first_selector.selector)
        
        if not elements:
            logger.warning(f"선택자로 요소를 찾을 수 없음: {first_selector.selector}")
            return []
        
        extracted_data = []
        
        for i, element in enumerate(elements):
            item_data = {}
            
            for selector_data in selectors:
                try:
                    # 상대적 선택자 처리
                    if selector_data == first_selector:
                        target_element = element
                    else:
                        # 현재 요소 내에서 찾기
                        target_element = element.select_one(
                            self._make_relative_selector(selector_data.selector)
                        )
                        if not target_element:
                            # 전체 문서에서 같은 인덱스의 요소 찾기
                            all_elements = soup.select(selector_data.selector)
                            target_element = all_elements[i] if i < len(all_elements) else None
                    
                    if target_element:
                        # 데이터 추출
                        value = self._extract_element_value(target_element, selector_data)
                        item_data[selector_data.name] = value
                    else:
                        item_data[selector_data.name] = None
                        
                except Exception as e:
                    logger.warning(f"요소 추출 실패 - {selector_data.name}: {str(e)}")
                    item_data[selector_data.name] = None
            
            extracted_data.append(item_data)
        
        return extracted_data
    
    def _extract_data_from_selenium(
        self, 
        driver: webdriver.Chrome, 
        selectors: List[SelectorData]
    ) -> List[Dict[str, Any]]:
        """Selenium WebDriver에서 데이터 추출"""
        
        first_selector = selectors[0]
        
        try:
            elements = driver.find_elements(By.CSS_SELECTOR, first_selector.selector)
        except NoSuchElementException:
            logger.warning(f"선택자로 요소를 찾을 수 없음: {first_selector.selector}")
            return []
        
        if not elements:
            return []
        
        extracted_data = []
        
        for i, element in enumerate(elements):
            item_data = {}
            
            for selector_data in selectors:
                try:
                    if selector_data == first_selector:
                        target_element = element
                    else:
                        try:
                            target_element = element.find_element(
                                By.CSS_SELECTOR, 
                                self._make_relative_selector(selector_data.selector)
                            )
                        except NoSuchElementException:
                            # 전체 문서에서 같은 인덱스의 요소 찾기
                            all_elements = driver.find_elements(By.CSS_SELECTOR, selector_data.selector)
                            target_element = all_elements[i] if i < len(all_elements) else None
                    
                    if target_element:
                        value = self._extract_selenium_element_value(target_element, selector_data)
                        item_data[selector_data.name] = value
                    else:
                        item_data[selector_data.name] = None
                        
                except Exception as e:
                    logger.warning(f"Selenium 요소 추출 실패 - {selector_data.name}: {str(e)}")
                    item_data[selector_data.name] = None
            
            extracted_data.append(item_data)
        
        return extracted_data
    
    def _extract_element_value(self, element, selector_data: SelectorData) -> str:
        """BeautifulSoup 요소에서 값 추출"""
        
        # 속성값 추출
        if 'href' in selector_data.attributes or element.name == 'a':
            href = element.get('href')
            if href:
                return href
        
        if 'src' in selector_data.attributes or element.name == 'img':
            src = element.get('src')
            if src:
                return src
        
        # 텍스트 추출
        text = element.get_text(strip=True)
        return text if text else ""
    
    def _extract_selenium_element_value(self, element, selector_data: SelectorData) -> str:
        """Selenium 요소에서 값 추출"""
        
        tag_name = element.tag_name.lower()
        
        # 속성값 추출
        if tag_name == 'a' or 'href' in selector_data.attributes:
            href = element.get_attribute('href')
            if href:
                return href
        
        if tag_name == 'img' or 'src' in selector_data.attributes:
            src = element.get_attribute('src')
            if src:
                return src
        
        # 텍스트 추출
        text = element.text.strip()
        return text if text else ""
    
    def _make_relative_selector(self, selector: str) -> str:
        """절대 선택자를 상대 선택자로 변환"""
        if selector.startswith('#') or selector.startswith('.'):
            return selector
        
        # 간단한 변환 로직
        if ' > ' in selector:
            parts = selector.split(' > ')
            return ' > '.join(parts[1:])  # 첫 번째 부분 제거
        
        return selector
    
    def _find_next_page_url(self, soup: BeautifulSoup, current_url: str, options: Dict) -> Optional[str]:
        """다음 페이지 URL 찾기"""
        
        # 일반적인 다음 페이지 링크 선택자들
        next_selectors = [
            'a[rel="next"]',
            'a.next',
            'a.pagination-next',
            '.pagination .next a',
            '.pager .next a',
            'a:contains("다음")',
            'a:contains("Next")'
        ]
        
        for selector in next_selectors:
            try:
                next_link = soup.select_one(selector)
                if next_link and next_link.get('href'):
                    next_url = urljoin(current_url, next_link['href'])
                    return next_url
            except:
                continue
        
        return None
    
    def _navigate_to_next_page(self, driver: webdriver.Chrome, options: Dict) -> bool:
        """Selenium으로 다음 페이지 이동"""
        
        next_selectors = [
            'a[rel="next"]',
            'a.next',
            'a.pagination-next',
            '.pagination .next a',
            '.pager .next a'
        ]
        
        for selector in next_selectors:
            try:
                next_button = driver.find_element(By.CSS_SELECTOR, selector)
                if next_button.is_enabled():
                    driver.execute_script("arguments[0].click();", next_button)
                    
                    # 페이지 로딩 대기
                    WebDriverWait(driver, 10).until(
                        EC.staleness_of(next_button)
                    )
                    return True
            except:
                continue
        
        return False

import pandas as pd
from pathlib import Path
from datetime import datetime
from typing import List, Dict, Any
import logging
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment, PatternFill, Border, Side
from openpyxl.utils.dataframe import dataframe_to_rows

from app.core.config import settings
from app.models.schemas import SelectorData

logger = logging.getLogger(__name__)

class ExcelService:
    def __init__(self):
        self.results_dir = Path(settings.RESULTS_DIR)
        self.results_dir.mkdir(parents=True, exist_ok=True)
    
    async def create_excel(
        self,
        data: List[Dict[str, Any]],
        job_id: str,
        url: str,
        selectors: List[SelectorData]
    ) -> Path:
        """
        크롤링 데이터를 Excel 파일로 변환
        
        Args:
            data: 크롤링된 데이터
            job_id: 작업 ID
            url: 크롤링한 URL
            selectors: 사용된 선택자들
            
        Returns:
            생성된 Excel 파일 경로
        """
        try:
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            filename = f"crawl_result_{job_id}_{timestamp}.xlsx"
            file_path = self.results_dir / filename
            
            logger.info(f"Excel 파일 생성 시작: {filename}")
            
            # 데이터가 없는 경우 빈 파일 생성
            if not data:
                await self._create_empty_excel(file_path, url, selectors)
                return file_path
            
            # DataFrame 생성
            df = pd.DataFrame(data)
            
            # Excel 파일 생성 (고급 포맷팅 포함)
            await self._create_formatted_excel(df, file_path, url, selectors, job_id)
            
            logger.info(f"Excel 파일 생성 완료: {filename}")
            return file_path
            
        except Exception as e:
            logger.error(f"Excel 파일 생성 실패: {str(e)}")
            raise
    
    async def _create_formatted_excel(
        self,
        df: pd.DataFrame,
        file_path: Path,
        url: str,
        selectors: List[SelectorData],
        job_id: str
    ):
        """포맷팅이 적용된 Excel 파일 생성"""
        
        wb = Workbook()
        
        # 메인 데이터 시트
        ws_data = wb.active
        ws_data.title = "크롤링 결과"
        
        # 헤더 스타일
        header_font = Font(bold=True, color="FFFFFF")
        header_fill = PatternFill(start_color="366092", end_color="366092", fill_type="solid")
        header_alignment = Alignment(horizontal="center", vertical="center")
        
        # 테두리 스타일
        thin_border = Border(
            left=Side(style='thin'),
            right=Side(style='thin'),
            top=Side(style='thin'),
            bottom=Side(style='thin')
        )
        
        # 메타 정보 추가
        ws_data['A1'] = "크롤링 정보"
        ws_data['A1'].font = Font(bold=True, size=14)
        ws_data['A2'] = f"URL: {url}"
        ws_data['A3'] = f"작업 ID: {job_id}"
        ws_data['A4'] = f"수집 시간: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        ws_data['A5'] = f"총 항목 수: {len(df)}"
        
        # 데이터 시작 행
        start_row = 7
        
        # DataFrame을 시트에 추가
        for r in dataframe_to_rows(df, index=False, header=True):
            ws_data.append(r)
        
        # 헤더 행 찾기 및 스타일 적용
        header_row = start_row
        for col_num, column_title in enumerate(df.columns, 1):
            cell = ws_data.cell(row=header_row, column=col_num)
            cell.value = column_title
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
            cell.border = thin_border
        
        # 데이터 행 스타일 적용
        for row_num in range(header_row + 1, header_row + len(df) + 1):
            for col_num in range(1, len(df.columns) + 1):
                cell = ws_data.cell(row=row_num, column=col_num)
                cell.border = thin_border
                cell.alignment = Alignment(vertical="center")
        
        # 컬럼 너비 자동 조정
        for column in ws_data.columns:
            max_length = 0
            column_letter = column[0].column_letter
            
            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass
            
            adjusted_width = min(max_length + 2, 50)
            ws_data.column_dimensions[column_letter].width = adjusted_width
        
        # 선택자 정보 시트 생성
        ws_selectors = wb.create_sheet("선택자 정보")
        ws_selectors['A1'] = "사용된 선택자 정보"
        ws_selectors['A1'].font = Font(bold=True, size=14)
        
        # 선택자 테이블 헤더
        selector_headers = ['이름', '선택자', '태그', '샘플 텍스트']
        for col_num, header in enumerate(selector_headers, 1):
            cell = ws_selectors.cell(row=3, column=col_num)
            cell.value = header
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
            cell.border = thin_border
        
        # 선택자 데이터 추가
        for row_num, selector in enumerate(selectors, 4):
            ws_selectors.cell(row=row_num, column=1).value = selector.name
            ws_selectors.cell(row=row_num, column=2).value = selector.selector
            ws_selectors.cell(row=row_num, column=3).value = selector.tagName
            ws_selectors.cell(row=row_num, column=4).value = selector.text[:50] + "..." if len(selector.text) > 50 else selector.text
            
            # 테두리 적용
            for col_num in range(1, 5):
                ws_selectors.cell(row=row_num, column=col_num).border = thin_border
        
        # 선택자 시트 컬럼 너비 조정
        ws_selectors.column_dimensions['A'].width = 20
        ws_selectors.column_dimensions['B'].width = 40
        ws_selectors.column_dimensions['C'].width = 15
        ws_selectors.column_dimensions['D'].width = 30
        
        # 통계 시트 생성
        ws_stats = wb.create_sheet("통계")
        ws_stats['A1'] = "크롤링 통계"
        ws_stats['A1'].font = Font(bold=True, size=14)
        
        # 기본 통계 정보
        stats_data = [
            ["총 수집 항목 수", len(df)],
            ["수집된 필드 수", len(df.columns)],
            ["빈 값 비율", f"{(df.isnull().sum().sum() / (len(df) * len(df.columns)) * 100):.1f}%"],
            ["수집 시간", datetime.now().strftime('%Y-%m-%d %H:%M:%S')]
        ]
        
        for row_num, (label, value) in enumerate(stats_data, 3):
            ws_stats.cell(row=row_num, column=1).value = label
            ws_stats.cell(row=row_num, column=2).value = value
            ws_stats.cell(row=row_num, column=1).font = Font(bold=True)
        
        # 필드별 통계
        ws_stats['A8'] = "필드별 통계"
        ws_stats['A8'].font = Font(bold=True, size=12)
        
        field_stats_headers = ['필드명', '비어있지 않은 값', '비어있는 값', '완성도']
        for col_num, header in enumerate(field_stats_headers, 1):
            cell = ws_stats.cell(row=9, column=col_num)
            cell.value = header
            cell.font = header_font
            cell.fill = header_fill
            cell.alignment = header_alignment
            cell.border = thin_border
        
        for row_num, column in enumerate(df.columns, 10):
            non_null_count = df[column].notna().sum()
            null_count = df[column].isna().sum()
            completion_rate = (non_null_count / len(df) * 100) if len(df) > 0 else 0
            
            ws_stats.cell(row=row_num, column=1).value = column
            ws_stats.cell(row=row_num, column=2).value = non_null_count
            ws_stats.cell(row=row_num, column=3).value = null_count
            ws_stats.cell(row=row_num, column=4).value = f"{completion_rate:.1f}%"
            
            for col_num in range(1, 5):
                ws_stats.cell(row=row_num, column=col_num).border = thin_border
        
        # Excel 파일 저장
        wb.save(file_path)
    
    async def _create_empty_excel(self, file_path: Path, url: str, selectors: List[SelectorData]):
        """데이터가 없는 경우 빈 Excel 파일 생성"""
        
        wb = Workbook()
        ws = wb.active
        ws.title = "크롤링 결과"
        
        ws['A1'] = "크롤링 결과 없음"
        ws['A1'].font = Font(bold=True, size=14, color="FF0000")
        ws['A2'] = f"URL: {url}"
        ws['A3'] = f"수집 시간: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}"
        ws['A4'] = "선택한 요소들에서 데이터를 찾을 수 없습니다."
        
        # 선택자 정보 추가
        ws['A6'] = "사용된 선택자:"
        ws['A6'].font = Font(bold=True)
        
        for i, selector in enumerate(selectors, 7):
            ws[f'A{i}'] = f"- {selector.name}: {selector.selector}"
        
        wb.save(file_path)

    async def create_csv(
        self,
        data: List[Dict[str, Any]],
        job_id: str,
        url: str
    ) -> Path:
        """CSV 파일 생성 (간단한 형태)"""
        
        try:
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            filename = f"crawl_result_{job_id}_{timestamp}.csv"
            file_path = self.results_dir / filename
            
            if not data:
                # 빈 CSV 파일 생성
                pd.DataFrame().to_csv(file_path, index=False, encoding='utf-8-sig')
            else:
                df = pd.DataFrame(data)
                df.to_csv(file_path, index=False, encoding='utf-8-sig')
            
            return file_path
            
        except Exception as e:
            logger.error(f"CSV 파일 생성 실패: {str(e)}")
            raise

from pydantic import BaseModel, HttpUrl
from typing import List, Dict, Any, Optional
from datetime import datetime

class SelectorData(BaseModel):
    name: str
    selector: str
    tagName: str
    text: str
    attributes: Dict[str, str]

class CrawlTemplate(BaseModel):
    id: Optional[str] = None
    name: str
    description: Optional[str] = None
    url_pattern: str
    selectors: List[SelectorData]
    options: Optional[Dict[str, Any]] = {}
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None
    usage_count: Optional[int] = 0

class TemplateCreateRequest(BaseModel):
    name: str
    description: Optional[str] = None
    url_pattern: str
    selectors: List[SelectorData]
    options: Optional[Dict[str, Any]] = {}

class TemplateUpdateRequest(BaseModel):
    name: Optional[str] = None
    description: Optional[str] = None
    url_pattern: Optional[str] = None
    selectors: Optional[List[SelectorData]] = None
    options: Optional[Dict[str, Any]] = None

class TemplateResponse(BaseModel):
    success: bool
    template: Optional[CrawlTemplate] = None
    message: str
    error: Optional[str] = None

class TemplateListResponse(BaseModel):
    success: bool
    templates: List[CrawlTemplate]
    total: int
    message: str

from fastapi import APIRouter, HTTPException, Query
from typing import List, Optional
import uuid
import json
from datetime import datetime
from pathlib import Path

from app.models.schemas import (
    CrawlTemplate, 
    TemplateCreateRequest, 
    TemplateUpdateRequest,
    TemplateResponse, 
    TemplateListResponse
)
from app.services.template_service import TemplateService

router = APIRouter()

@router.post("/", response_model=TemplateResponse)
async def create_template(template_data: TemplateCreateRequest):
    """새 템플릿 생성"""
    try:
        template_service = TemplateService()
        
        # 템플릿 객체 생성
        template = CrawlTemplate(
            id=str(uuid.uuid4()),
            name=template_data.name,
            description=template_data.description,
            url_pattern=template_data.url_pattern,
            selectors=template_data.selectors,
            options=template_data.options,
            created_at=datetime.now(),
            updated_at=datetime.now(),
            usage_count=0
        )
        
        # 템플릿 저장
        saved_template = await template_service.save_template(template)
        
        return TemplateResponse(
            success=True,
            template=saved_template,
            message="템플릿이 성공적으로 생성되었습니다"
        )
        
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"템플릿 생성 실패: {str(e)}"
        )

@router.get("/", response_model=TemplateListResponse)
async def list_templates(
    page: int = Query(1, ge=1),
    limit: int = Query(10, ge=1, le=100),
    search: Optional[str] = Query(None),
    url_pattern: Optional[str] = Query(None)
):
    """템플릿 목록 조회"""
    try:
        template_service = TemplateService()
        
        templates, total = await template_service.list_templates(
            page=page,
            limit=limit,
            search=search,
            url_pattern=url_pattern
        )
        
        return TemplateListResponse(
            success=True,
            templates=templates,
            total=total,
            message=f"{total}개의 템플릿을 찾았습니다"
        )
        
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"템플릿 목록 조회 실패: {str(e)}"
        )

@router.get("/{template_id}", response_model=TemplateResponse)
async def get_template(template_id: str):
    """특정 템플릿 조회"""
    try:
        template_service = TemplateService()
        template = await template_service.get_template(template_id)
        
        if not template:
            raise HTTPException(
                status_code=404,
                detail="템플릿을 찾을 수 없습니다"
            )
        
        return TemplateResponse(
            success=True,
            template=template,
            message="템플릿을 성공적으로 조회했습니다"
        )
        
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"템플릿 조회 실패: {str(e)}"
        )

@router.put("/{template_id}", response_model=TemplateResponse)
async def update_template(template_id: str, template_data: TemplateUpdateRequest):
    """템플릿 수정"""
    try:
        template_service = TemplateService()
        
        updated_template = await template_service.update_template(
            template_id, 
            template_data
        )
        
        if not updated_template:
            raise HTTPException(
                status_code=404,
                detail="템플릿을 찾을 수 없습니다"
            )
        
        return TemplateResponse(
            success=True,
            template=updated_template,
            message="템플릿이 성공적으로 수정되었습니다"
        )
        
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"템플릿 수정 실패: {str(e)}"
        )

@router.delete("/{template_id}")
async def delete_template(template_id: str):
    """템플릿 삭제"""
    try:
        template_service = TemplateService()
        
        success = await template_service.delete_template(template_id)
        
        if not success:
            raise HTTPException(
                status_code=404,
                detail="템플릿을 찾을 수 없습니다"
            )
        
        return {
            "success": True,
            "message": "템플릿이 성공적으로 삭제되었습니다"
        }
        
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"템플릿 삭제 실패: {str(e)}"
        )

@router.post("/{template_id}/execute")
async def execute_template(template_id: str, url: str):
    """템플릿을 사용하여 크롤링 실행"""
    try:
        template_service = TemplateService()
        template = await template_service.get_template(template_id)
        
        if not template:
            raise HTTPException(
                status_code=404,
                detail="템플릿을 찾을 수 없습니다"
            )
        
        # 사용 횟수 증가
        await template_service.increment_usage_count(template_id)
        
        # 크롤링 실행 (크롤링 서비스 호출)
        from app.services.crawler_service import CrawlerService
        crawler_service = CrawlerService()
        
        result = await crawler_service.crawl_page(
            url=url,
            selectors=template.selectors,
            job_id=f"template_{template_id}_{uuid.uuid4()}",
            options=template.options
        )
        
        return {
            "success": result["success"],
            "data": result["data"],
            "template_used": template.name,
            "message": f"템플릿 '{template.name}'을 사용하여 크롤링을 완료했습니다"
        }
        
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"템플릿 실행 실패: {str(e)}"
        )

@router.get("/search/by-url")
async def find_templates_by_url(url: str):
    """URL에 맞는 템플릿 찾기"""
    try:
        template_service = TemplateService()
        templates = await template_service.find_templates_by_url(url)
        
        return {
            "success": True,
            "templates": templates,
            "count": len(templates),
            "message": f"{len(templates)}개의 적합한 템플릿을 찾았습니다"
        }
        
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"템플릿 검색 실패: {str(e)}"
        )

@router.post("/import")
async def import_template(template_json: dict):
    """템플릿 가져오기 (JSON 형태)"""
    try:
        template_service = TemplateService()
        
        # JSON에서 템플릿 객체 생성
        template = CrawlTemplate(**template_json)
        template.id = str(uuid.uuid4())  # 새 ID 할당
        template.created_at = datetime.now()
        template.updated_at = datetime.now()
        
        saved_template = await template_service.save_template(template)
        
        return TemplateResponse(
            success=True,
            template=saved_template,
            message="템플릿을 성공적으로 가져왔습니다"
        )
        
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"템플릿 가져오기 실패: {str(e)}"
        )

@router.get("/{template_id}/export")
async def export_template(template_id: str):
    """템플릿 내보내기 (JSON 형태)"""
    try:
        template_service = TemplateService()
        template = await template_service.get_template(template_id)
        
        if not template:
            raise HTTPException(
                status_code=404,
                detail="템플릿을 찾을 수 없습니다"
            )
        
        # 민감한 정보 제거
        export_data = template.dict()
        export_data.pop('id', None)
        export_data.pop('usage_count', None)
        
        return {
            "success": True,
            "template": export_data,
            "message": "템플릿을 성공적으로 내보냈습니다"
        }
        
    except HTTPException:
        raise
    except Exception as e:
        raise HTTPException(
            status_code=500,
            detail=f"템플릿 내보내기 실패: {str(e)}"
        )