class InquiryQueries:
    """문의사항(Inquiries) 페이지 관련 쿼리"""
    # 필터링을 위한 기본 쿼리 (WHERE 1=1 포함)
    SELECT_BASE = "SELECT * FROM inquiries WHERE 1=1"
    ORDER_BY_DESC = "ORDER BY no DESC"
    
    # 상세 조회
    SELECT_BY_ID = "SELECT * FROM inquiries WHERE id = %s"
    
    # 답변 업데이트 (handled_date 포함)
    UPDATE_REPLY = """
        UPDATE inquiries 
        SET reply = %s, status = %s, handler = %s, handled_date = %s 
        WHERE id = %s
    """
    
    # 답변 삭제 (초기화)
    DELETE_REPLY = """
        UPDATE inquiries 
        SET reply = '', status = '미확인', handled_date = '' 
        WHERE id = %s
    """

class DashboardQueries:
    """대시보드(Dashboard) 및 프로젝트 현황 관련 쿼리"""
    # 가용 날짜 목록 조회
    GET_AVAILABLE_DATES = "SELECT DISTINCT crawl_date FROM projects_history ORDER BY crawl_date DESC"
    
    # 최신 수집 날짜 조회
    GET_LAST_CRAWL_DATE = "SELECT MAX(crawl_date) as last_date FROM projects_history"
    
    # 특정 날짜 프로젝트 데이터 JOIN 조회
    GET_PROJECT_LIST = """
        SELECT m.project_nm, m.short_nm, m.department, m.master, 
               h.recent_log, h.file_count, m.continent, m.country 
        FROM projects_master m
        LEFT JOIN projects_history h ON m.project_id = h.project_id AND h.crawl_date = %s
        ORDER BY m.project_id ASC
    """
    
    # 활성도 분석을 위한 프로젝트 목록 조회
    GET_PROJECT_LIST_FOR_ANALYSIS = """
        SELECT m.project_id, m.project_nm, m.short_nm, h.recent_log, h.file_count
        FROM projects_master m
        LEFT JOIN projects_history h ON m.project_id = h.project_id AND h.crawl_date = %s
    """

class CrawlerQueries:
    """크롤러(Crawler) 데이터 동기화 관련 쿼리"""
    # 마스터 정보 UPSERT (INSERT OR UPDATE)
    UPSERT_MASTER = """
        INSERT INTO projects_master (project_id, project_nm, short_nm, master, continent, country)
        VALUES (%s, %s, %s, %s, %s, %s)
        ON DUPLICATE KEY UPDATE 
            project_nm = VALUES(project_nm), short_nm = VALUES(short_nm),
            master = VALUES(master), continent = VALUES(continent), country = VALUES(country)
    """
    
    # 부서 정보 업데이트
    UPDATE_DEPARTMENT = "UPDATE projects_master SET department = %s WHERE project_id = %s"
    
    # 히스토리(로그/파일수) 저장
    UPSERT_HISTORY = """
        INSERT INTO projects_history (project_id, crawl_date, recent_log, file_count) 
        VALUES (%s, CURRENT_DATE(), %s, %s) 
        ON DUPLICATE KEY UPDATE recent_log=VALUES(recent_log), file_count=VALUES(file_count)
    """