"""
Asset Management Server
자산관리 서버 - RESTful API 기반
"""

import json
import sqlite3
import datetime
import hashlib
from flask import Flask, request, jsonify, render_template_string, send_from_directory
from flask_cors import CORS
from werkzeug.middleware.proxy_fix import ProxyFix
import threading
import time
import logging
import os
from threading import RLock
from queue import Queue
import random

# 로깅 설정
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

app = Flask(__name__)
app.config['JSON_AS_ASCII'] = False  # 한글 유니코드 문제 해결
app.config['MAX_CONTENT_LENGTH'] = 16 * 1024 * 1024  # 16MB 업로드 제한

# 프록시 설정 (Gunicorn 뒤에서 실행 시)
app.wsgi_app = ProxyFix(app.wsgi_app, x_for=1, x_proto=1, x_host=1, x_prefix=1)

CORS(app)  # Cross-Origin 요청 허용

class AssetDatabase:
    def __init__(self, db_path="asset_management.db"):
        # 현재 디렉토리 또는 Windows 임시 디렉토리에 데이터베이스 생성
        import os
        import tempfile
        
        # 모든 환경에서 현재 스크립트 디렉토리에 데이터베이스 생성
        current_dir = os.path.dirname(os.path.abspath(__file__))
        self.db_path = os.path.join(current_dir, db_path)
        
        print(f"Database path: {self.db_path}")
        
        # 연결 풀 및 스레드 안전성을 위한 잠금 (400대 동시 접속 대응)
        self._lock = RLock()
        self._connection_pool = Queue(maxsize=150)  # 최대 150개 연결 (400대 대응)
        self._pool_initialized = False
        
        # 배치 처리를 위한 큐 (메모리 누수 방지를 위해 크기 제한)
        self.update_queue = Queue(maxsize=2000)  # 한 달 재부팅으로 관리되지만 안전장치
        self.batch_processor_running = False
        
        # icons.db 경로 설정
        self.icon_db_path = r'C:\Users\Administrator\Desktop\item\instance\icons.db'
        
        self.init_database()
        self._init_connection_pool()
        self._start_batch_processor()
    
    def get_rust_info(self, hostname):
        """icons.db에서 hostname으로 RUST 정보 조회"""
        try:
            logger.info(f"🔍 RUST 정보 조회 시작 - hostname: {hostname}")
            logger.info(f"📁 icons.db 경로: {self.icon_db_path}")
            
            if not os.path.exists(self.icon_db_path):
                logger.warning(f"❌ Icon database not found: {self.icon_db_path}")
                return None
                
            logger.info(f"✅ icons.db 파일 존재 확인됨")
            
            conn = sqlite3.connect(self.icon_db_path)
            cursor = conn.cursor()
            
            # 테이블 구조 확인
            cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
            tables = cursor.fetchall()
            logger.info(f"📋 사용 가능한 테이블: {tables}")
            
            # icon 테이블의 컬럼 확인
            try:
                cursor.execute("PRAGMA table_info(icon);")
                columns = cursor.fetchall()
                logger.info(f"🏷️ icon 테이블 컬럼: {columns}")
            except Exception as e:
                logger.error(f"❌ icon 테이블 정보 조회 실패: {e}")
            
            # 전체 데이터 확인 (디버깅용)
            try:
                cursor.execute("SELECT comp_no, remote_address FROM icon LIMIT 5;")
                sample_data = cursor.fetchall()
                logger.info(f"📊 icon 테이블 샘플 데이터: {sample_data}")
            except Exception as e:
                logger.error(f"❌ 샘플 데이터 조회 실패: {e}")
            
            # comp_no 컬럼으로 hostname과 매칭하여 remote_address 조회
            logger.info(f"🔎 검색 쿼리 실행 - WHERE comp_no = '{hostname}'")
            cursor.execute("""
                SELECT remote_address 
                FROM icon 
                WHERE comp_no = ?
            """, (hostname,))
            
            result = cursor.fetchone()
            logger.info(f"🎯 쿼리 결과: {result}")
            
            conn.close()
            
            if result:
                logger.info(f"✅ RUST 정보 찾음: {result[0]}")
                return result[0]  # remote_address 값 반환
            else:
                logger.warning(f"❌ hostname '{hostname}'에 대한 RUST 정보 없음")
                return None
            
        except Exception as e:
            logger.error(f"❌ Error querying icons.db for hostname {hostname}: {e}")
            return None
    
    def init_database(self):
        """데이터베이스 초기화"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 자산 테이블
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS assets (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            hostname TEXT UNIQUE NOT NULL,
            ip_address TEXT,
            mac_address TEXT,
            last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            status TEXT DEFAULT 'active',
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        
        # 하드웨어 정보 테이블
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS hardware_info (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            asset_id INTEGER,
            cpu_info TEXT,
            ram_total INTEGER,
            ram_used INTEGER,
            ram_percent REAL,
            disk_total INTEGER,
            disk_used INTEGER,
            disk_percent REAL,
            gpu_info TEXT,
            motherboard TEXT,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (asset_id) REFERENCES assets (id)
        )
        ''')
        
        # 네트워크 정보 테이블
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS network_info (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            asset_id INTEGER,
            interface_name TEXT,
            ip_address TEXT,
            mac_address TEXT,
            subnet_mask TEXT,
            gateway TEXT,
            dns_servers TEXT,
            network_speed TEXT,
            is_connected BOOLEAN,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (asset_id) REFERENCES assets (id)
        )
        ''')
        
        # 소프트웨어 정보 테이블
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS software_info (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            asset_id INTEGER,
            software_list TEXT,  -- JSON 형태로 저장
            software_count INTEGER,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (asset_id) REFERENCES assets (id)
        )
        ''')
        
        # COM 포트 정보 테이블
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS com_ports (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            asset_id INTEGER,
            port_name TEXT,
            description TEXT,
            hwid TEXT,
            is_active BOOLEAN,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (asset_id) REFERENCES assets (id)
        )
        ''')
        
        
        # 관리자 설정 테이블
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS admin_settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            setting_key TEXT UNIQUE NOT NULL,
            setting_value TEXT NOT NULL,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
        ''')
        
        # 소프트웨어 관리 요청 테이블
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS software_management_requests (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            hostname TEXT NOT NULL,
            software_name TEXT NOT NULL,
            action_type TEXT NOT NULL,
            requested_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            status TEXT DEFAULT 'pending',
            completed_at TIMESTAMP,
            FOREIGN KEY (hostname) REFERENCES assets (hostname)
        )
        ''')
        
        conn.commit()
        conn.close()
        logger.info("Database initialized successfully")
        
        # 성능 최적화를 위한 인덱스 생성
        self._create_indexes()
        
        # 에이전트 로그 테이블 생성
        self._create_agent_logs_table()
    
    def _create_indexes(self):
        """성능 최적화를 위한 인덱스 생성"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 자주 조회되는 컬럼에 인덱스 생성
        indexes = [
            "CREATE INDEX IF NOT EXISTS idx_assets_hostname ON assets(hostname)",
            "CREATE INDEX IF NOT EXISTS idx_assets_last_seen ON assets(last_seen)",
            "CREATE INDEX IF NOT EXISTS idx_assets_status ON assets(status)",
            "CREATE INDEX IF NOT EXISTS idx_software_info_asset_id ON software_info(asset_id)",
            "CREATE INDEX IF NOT EXISTS idx_hardware_info_asset_id ON hardware_info(asset_id)"
        ]
        
        for index_sql in indexes:
            try:
                cursor.execute(index_sql)
            except sqlite3.Error as e:
                logger.warning(f"Index creation failed: {e}")
        
        conn.commit()
        conn.close()
    
    def _create_agent_logs_table(self):
        """에이전트 로그 테이블 생성 (PC당 50개 제한)"""
        conn = sqlite3.connect(self.db_path)
        cursor = conn.cursor()
        
        # 에이전트 로그 테이블
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS agent_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            hostname TEXT NOT NULL,
            log_level TEXT NOT NULL,
            log_type TEXT NOT NULL,
            message TEXT NOT NULL,
            details TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (hostname) REFERENCES assets (hostname)
        )
        ''')
        
        # 인덱스 생성
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_agent_logs_hostname ON agent_logs(hostname)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_agent_logs_created_at ON agent_logs(created_at)")
        cursor.execute("CREATE INDEX IF NOT EXISTS idx_agent_logs_level ON agent_logs(log_level)")
        
        # 브로드캐스트 메시지 확인 상태 테이블 생성
        cursor.execute('''
        CREATE TABLE IF NOT EXISTS broadcast_acknowledgments (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            message_id TEXT NOT NULL,
            hostname TEXT NOT NULL,
            acknowledged_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(message_id, hostname)
        )
        ''')
        
        # 브로드캐스트 확인 인덱스 생성
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_broadcast_ack_message_id ON broadcast_acknowledgments(message_id)')
        cursor.execute('CREATE INDEX IF NOT EXISTS idx_broadcast_ack_hostname ON broadcast_acknowledgments(hostname)')
        
        conn.commit()
        conn.close()
    
    def _init_connection_pool(self):
        """연결 풀 초기화"""
        if self._pool_initialized:
            return
            
        with self._lock:
            if self._pool_initialized:
                return
                
            # 초기 연결들을 풀에 추가 (400대 대응을 위해 증가)
            for _ in range(20):  # 초기 20개 연결
                try:
                    conn = sqlite3.connect(self.db_path, check_same_thread=False)
                    conn.execute("PRAGMA journal_mode=WAL")  # WAL 모드로 성능 향상
                    conn.execute("PRAGMA synchronous=NORMAL")  # 성능 최적화
                    conn.execute("PRAGMA cache_size=20000")  # 캐시 크기 증가 (400대 대응)
                    conn.execute("PRAGMA temp_store=MEMORY")  # 임시 데이터를 메모리에 저장
                    conn.execute("PRAGMA mmap_size=268435456")  # 256MB 메모리 맵 활성화
                    self._connection_pool.put(conn)
                except sqlite3.Error as e:
                    logger.error(f"Failed to create connection: {e}")
            
            self._pool_initialized = True
            logger.info("Connection pool initialized")
    
    def get_connection(self):
        """연결 풀에서 연결 가져오기"""
        try:
            # 풀에서 연결 가져오기 (최대 1초 대기)
            conn = self._connection_pool.get(timeout=1)
            return conn
        except:
            # 풀이 비어있으면 새 연결 생성
            try:
                conn = sqlite3.connect(self.db_path, check_same_thread=False)
                conn.execute("PRAGMA journal_mode=WAL")
                conn.execute("PRAGMA synchronous=NORMAL")
                conn.execute("PRAGMA cache_size=20000")  # 400대 대응
                conn.execute("PRAGMA temp_store=MEMORY")
                conn.execute("PRAGMA mmap_size=268435456")
                return conn
            except sqlite3.Error as e:
                logger.error(f"Failed to create new connection: {e}")
                raise
    
    def return_connection(self, conn):
        """연결을 풀에 다시 반환"""
        try:
            if self._connection_pool.qsize() < 150:  # 최대 연결 수 제한 (400대 대응)
                self._connection_pool.put(conn)
            else:
                conn.close()
        except:
            try:
                conn.close()
            except:
                pass
    
    def _start_batch_processor(self):
        """배치 처리 스레드 시작"""
        if self.batch_processor_running:
            return
            
        self.batch_processor_running = True
        batch_thread = threading.Thread(target=self._batch_processor, daemon=True)
        batch_thread.start()
        logger.info("Batch processor started")
    
    def _batch_processor(self):
        """배치 처리 워커"""
        batch = []
        last_process_time = time.time()
        
        while self.batch_processor_running:
            try:
                # 큐에서 업데이트 요청 가져오기 (최대 0.1초 대기)
                try:
                    update_data = self.update_queue.get(timeout=0.1)
                    batch.append(update_data)
                except:
                    pass  # 타임아웃은 정상
                
                current_time = time.time()
                
                # 배치 처리 조건: 배치 크기 50개 이상 또는 2초 경과 (400대 대응)
                if (len(batch) >= 50 or 
                    (batch and current_time - last_process_time >= 2.0)):
                    
                    if batch:
                        self._process_batch(batch)
                        batch = []
                        last_process_time = current_time
                        
            except Exception as e:
                logger.error(f"Batch processor error: {e}")
                batch = []  # 오류 시 배치 초기화
    
    def _process_batch(self, batch):
        """배치 데이터 처리"""
        conn = self.get_connection()
        try:
            cursor = conn.cursor()
            
            # 자산 상태 업데이트를 배치로 처리
            asset_updates = []
            hardware_updates = []
            
            for item in batch:
                if item['type'] == 'asset_status':
                    asset_updates.append((
                        item['ip_address'], 
                        item['mac_address'], 
                        item['hostname']
                    ))
                elif item['type'] == 'hardware_info':
                    hardware_updates.append((
                        item['asset_id'],
                        item['cpu_info'],
                        item['ram_total'],
                        item['ram_used'],
                        item['ram_percent'],
                        item['disk_total'],
                        item['disk_used'],
                        item['disk_percent']
                    ))
            
            # 배치 자산 업데이트
            if asset_updates:
                cursor.executemany('''
                    UPDATE assets 
                    SET ip_address = ?, mac_address = ?, last_seen = CURRENT_TIMESTAMP, status = 'active'
                    WHERE hostname = ?
                ''', asset_updates)
            
            # 배치 하드웨어 정보 업데이트 (DELETE + INSERT 방식)
            if hardware_updates:
                for hw_data in hardware_updates:
                    asset_id = hw_data[0]
                    # 기존 레코드 삭제
                    cursor.execute('DELETE FROM hardware_info WHERE asset_id = ?', (asset_id,))
                    # 새 레코드 삽입
                    cursor.execute('''
                        INSERT INTO hardware_info 
                        (asset_id, cpu_info, ram_total, ram_used, ram_percent, disk_total, disk_used, disk_percent, updated_at)
                        VALUES (?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
                    ''', hw_data)
            
            conn.commit()
            logger.info(f"Processed batch of {len(batch)} items")
            
        except Exception as e:
            logger.error(f"Batch processing error: {e}")
            conn.rollback()
        finally:
            self.return_connection(conn)
    
    def register_asset(self, hostname, ip_address, mac_address):
        """자산 등록 또는 업데이트 (배치 처리 적용)"""
        conn = self.get_connection()
        try:
            cursor = conn.cursor()
            
            # 기존 자산 확인
            cursor.execute("SELECT id FROM assets WHERE hostname = ?", (hostname,))
            result = cursor.fetchone()
            
            if result:
                # 기존 자산의 경우 배치 큐에 추가
                asset_id = result[0]
                
                # 즉시 업데이트 (중요한 상태 변경)
                cursor.execute('''
                    UPDATE assets 
                    SET ip_address = ?, mac_address = ?, last_seen = CURRENT_TIMESTAMP, status = 'active'
                    WHERE id = ?
                ''', (ip_address, mac_address, asset_id))
                conn.commit()
                
            else:
                # 새 자산은 즉시 등록 (중요)
                cursor.execute('''
                    INSERT INTO assets (hostname, ip_address, mac_address) 
                    VALUES (?, ?, ?)
                ''', (hostname, ip_address, mac_address))
                asset_id = cursor.lastrowid
                conn.commit()
                
        finally:
            self.return_connection(conn)
            
        return asset_id
    
    def update_hardware_info(self, asset_id, hardware_data):
        """하드웨어 정보 업데이트 (배치 처리 사용)"""
        # 배치 큐에 추가
        try:
            self.update_queue.put({
                'type': 'hardware_info',
                'asset_id': asset_id,
                'cpu_info': hardware_data.get('cpu_info', ''),
                'ram_total': hardware_data.get('ram_total', 0),
                'ram_used': hardware_data.get('ram_used', 0),
                'ram_percent': hardware_data.get('ram_percent', 0.0),
                'disk_total': hardware_data.get('disk_total', 0),
                'disk_used': hardware_data.get('disk_used', 0),
                'disk_percent': hardware_data.get('disk_percent', 0.0),
                'gpu_info': hardware_data.get('gpu_info', ''),
                'motherboard': hardware_data.get('motherboard', '')
            }, timeout=1)
        except Exception as e:
            # 큐가 가득 찬 경우 직접 처리
            logger.warning(f"Queue full, processing directly: {e}")
            self._update_hardware_info_direct(asset_id, hardware_data)
    
    def _update_hardware_info_direct(self, asset_id, hardware_data):
        """하드웨어 정보 직접 업데이트 (큐 실패 시 백업)"""
        conn = self.get_connection()
        try:
            cursor = conn.cursor()
            
            # 기존 레코드 삭제 후 새로 삽입 (UPSERT 대신)
            cursor.execute('DELETE FROM hardware_info WHERE asset_id = ?', (asset_id,))
            
            cursor.execute('''
                INSERT INTO hardware_info 
                (asset_id, cpu_info, ram_total, ram_used, ram_percent, 
                 disk_total, disk_used, disk_percent, gpu_info, motherboard, updated_at)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CURRENT_TIMESTAMP)
            ''', (
                asset_id,
                hardware_data.get('cpu_info', ''),
                hardware_data.get('ram_total', 0),
                hardware_data.get('ram_used', 0),
                hardware_data.get('ram_percent', 0.0),
                hardware_data.get('disk_total', 0),
                hardware_data.get('disk_used', 0),
                hardware_data.get('disk_percent', 0.0),
                hardware_data.get('gpu_info', ''),
                hardware_data.get('motherboard', '')
            ))
            
            conn.commit()
        finally:
            self.return_connection(conn)
    
    def update_network_info(self, asset_id, network_data):
        """네트워크 정보 업데이트"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # 기존 정보 삭제
        cursor.execute("DELETE FROM network_info WHERE asset_id = ?", (asset_id,))
        
        # 네트워크 인터페이스별로 저장
        for interface in network_data.get('interfaces', []):
            cursor.execute('''
                INSERT INTO network_info 
                (asset_id, interface_name, ip_address, mac_address, subnet_mask, 
                 gateway, dns_servers, network_speed, is_connected)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            ''', (
                asset_id,
                interface.get('name', ''),
                interface.get('ip', ''),
                interface.get('mac', ''),
                interface.get('subnet', ''),
                interface.get('gateway', ''),
                json.dumps(interface.get('dns', [])),
                interface.get('speed', ''),
                interface.get('connected', False)
            ))
        
        conn.commit()
        conn.close()
    
    def update_software_info(self, asset_id, software_data):
        """소프트웨어 정보 업데이트"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # 기존 정보 삭제 후 새로 삽입
        cursor.execute("DELETE FROM software_info WHERE asset_id = ?", (asset_id,))
        
        software_list = software_data.get('software_list', [])
        cursor.execute('''
            INSERT INTO software_info (asset_id, software_list, software_count)
            VALUES (?, ?, ?)
        ''', (asset_id, json.dumps(software_list, ensure_ascii=False), len(software_list)))
        
        conn.commit()
        conn.close()
    
    def update_com_ports(self, asset_id, com_data):
        """COM 포트 정보 업데이트"""
        conn = self.get_connection()
        cursor = conn.cursor()
        
        # 기존 정보 삭제
        cursor.execute("DELETE FROM com_ports WHERE asset_id = ?", (asset_id,))
        
        # COM 포트별로 저장
        for port in com_data.get('ports', []):
            cursor.execute('''
                INSERT INTO com_ports (asset_id, port_name, description, hwid, is_active)
                VALUES (?, ?, ?, ?, ?)
            ''', (
                asset_id,
                port.get('port', ''),
                port.get('description', ''),
                port.get('hwid', ''),
                port.get('active', True)
            ))
        
        conn.commit()
        conn.close()
    
    def get_assets_summary(self):
        """자산 요약 정보 반환 (네트워크 정보 포함)"""
        conn = self.get_connection()
        try:
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT 
                    a.id, a.hostname, a.ip_address, a.mac_address, a.last_seen, a.status,
                    h.ram_total, h.disk_total,
                    s.software_count
                FROM assets a
                LEFT JOIN (
                    SELECT asset_id, ram_total, disk_total
                    FROM hardware_info h1
                    WHERE h1.updated_at = (
                        SELECT MAX(updated_at) 
                        FROM hardware_info h2 
                        WHERE h2.asset_id = h1.asset_id
                    )
                ) h ON a.id = h.asset_id
                LEFT JOIN (
                    SELECT asset_id, software_count
                    FROM software_info s1
                    WHERE s1.updated_at = (
                        SELECT MAX(updated_at) 
                        FROM software_info s2 
                        WHERE s2.asset_id = s1.asset_id
                    )
                ) s ON a.id = s.asset_id
                ORDER BY a.last_seen DESC
            ''')
            
            results = cursor.fetchall()
            
            assets = []
            for row in results:
                asset_id = row[0]
                
                # 네트워크 정보 조회 (무선 연결 확인)
                cursor.execute('''
                    SELECT interface_name, ip_address, is_connected
                    FROM network_info 
                    WHERE asset_id = ? AND is_connected = 1
                    ORDER BY interface_name
                ''', (asset_id,))
                
                network_info = cursor.fetchall()
                connection_type = "미연결"
                ssid = ""
                
                # 네트워크 연결 유형 판단
                if network_info:
                    # 네트워크 정보가 있는 경우
                    for net in network_info:
                        interface_name = net[0] if net[0] else ""
                        interface_lower = interface_name.lower()
                        
                        # 무선 연결 확인 - SSID 정보 추출
                        if ('wi-fi' in interface_lower or 'wireless' in interface_lower or 'wlan' in interface_lower or
                            'wifi' in interface_lower):
                            connection_type = "무선"
                            
                            # 인터페이스 이름에서 SSID 추출 (예: "Wi-Fi (MyNetwork)", "WiFi (HomeNet)")
                            if '(' in interface_name and ')' in interface_name:
                                ssid_start = interface_name.find('(')
                                ssid_end = interface_name.find(')')
                                if ssid_start < ssid_end:
                                    ssid = interface_name[ssid_start+1:ssid_end]
                                else:
                                    ssid = "(Wi-Fi)"
                            else:
                                ssid = "(Wi-Fi)"
                            break
                        elif ('ethernet' in interface_lower or 'eth' in interface_lower or 'local area' in interface_lower or
                              '유선' in interface_lower or '유선 연결' in interface_lower):
                            connection_type = "유선"
                            break
                else:
                    # 네트워크 정보가 없는 경우 IP 주소 기반으로 추정
                    ip_address = row[2]  # assets 테이블의 ip_address
                    if ip_address and ip_address != '127.0.0.1' and ip_address != '-':
                        hostname = row[1]  # assets 테이블의 hostname
                        
                        # 호스트명 기반 추정 (임시 방법)
                        if hostname and any(keyword in hostname.upper() for keyword in ['LAP', 'LAPTOP', 'NB', 'NOTEBOOK']):
                            connection_type = "무선"
                            ssid = "(추정)"
                        else:
                            # 기본값: 유선으로 추정
                            connection_type = "유선"
                
                # RUST 정보 조회 (icon.db에서 remote_address 가져오기)
                hostname = row[1]
                rust_address = self.get_rust_info(hostname) if hostname else None
                
                assets.append({
                    'hostname': hostname,
                    'ip_address': row[2],
                    'mac_address': row[3] or '-',
                    'last_seen': row[4],
                    'status': row[5],
                    'ram_total': row[6],
                    'disk_total': row[7],
                    'software_count': row[8],
                    'connection_type': connection_type,
                    'ssid': ssid,
                    'rust_address': rust_address or '-'
                })
            
            return assets
        finally:
            self.return_connection(conn)
    
    def add_agent_log(self, hostname, log_level, log_type, message, details=None):
        """에이전트 로그 추가 (50개 제한)"""
        conn = self.get_connection()
        try:
            cursor = conn.cursor()
            
            # 새 로그 추가
            cursor.execute('''
                INSERT INTO agent_logs (hostname, log_level, log_type, message, details)
                VALUES (?, ?, ?, ?, ?)
            ''', (hostname, log_level, log_type, message, details))
            
            # 해당 호스트의 로그 개수 확인 및 오래된 로그 삭제 (50개 초과 시)
            cursor.execute('''
                DELETE FROM agent_logs 
                WHERE hostname = ? AND id NOT IN (
                    SELECT id FROM agent_logs 
                    WHERE hostname = ? 
                    ORDER BY created_at DESC 
                    LIMIT 50
                )
            ''', (hostname, hostname))
            
            conn.commit()
            
        finally:
            self.return_connection(conn)
    
    def get_agent_logs(self, hostname, limit=50):
        """특정 에이전트의 로그 조회"""
        conn = self.get_connection()
        try:
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT log_level, log_type, message, details, created_at
                FROM agent_logs 
                WHERE hostname = ? 
                ORDER BY created_at DESC 
                LIMIT ?
            ''', (hostname, limit))
            
            logs = []
            for row in cursor.fetchall():
                logs.append({
                    'level': row[0],
                    'type': row[1],
                    'message': row[2],
                    'details': row[3],
                    'timestamp': row[4]
                })
            
            return logs
            
        finally:
            self.return_connection(conn)
    
    def get_all_agent_hostnames_with_logs(self):
        """로그가 있는 모든 에이전트 호스트명 조회"""
        conn = self.get_connection()
        try:
            cursor = conn.cursor()
            
            cursor.execute('''
                SELECT DISTINCT hostname, COUNT(*) as log_count,
                       MAX(created_at) as last_log_time
                FROM agent_logs 
                GROUP BY hostname 
                ORDER BY last_log_time DESC
            ''')
            
            agents = []
            for row in cursor.fetchall():
                agents.append({
                    'hostname': row[0],
                    'log_count': row[1],
                    'last_log_time': row[2]
                })
            
            return agents
            
        finally:
            self.return_connection(conn)

# 데이터베이스 인스턴스 생성
db = AssetDatabase()

@app.route('/', methods=['GET'])
def index():
    """서버 상태 확인"""
    return jsonify({
        'status': 'running',
        'service': 'Asset Management Server',
        'version': '1.0.0',
        'timestamp': datetime.datetime.now().isoformat()
    })

@app.route('/dashboard')
def dashboard():
    """메인 대시보드 웹페이지"""
    try:
        # 현재 디렉토리 기준으로 파일 경로 설정
        current_dir = os.path.dirname(os.path.abspath(__file__))
        dashboard_path = os.path.join(current_dir, 'static', 'dashboard.html')
        
        print(f"Looking for dashboard at: {dashboard_path}")  # 디버그용
        print(f"File exists: {os.path.exists(dashboard_path)}")  # 디버그용
        
        if os.path.exists(dashboard_path):
            with open(dashboard_path, 'r', encoding='utf-8') as f:
                content = f.read()
                print(f"Dashboard content length: {len(content)}")  # 디버그용
                return content, 200, {'Content-Type': 'text/html; charset=utf-8'}
        else:
            return jsonify({'error': f'Dashboard not found at {dashboard_path}'}), 404
            
    except Exception as e:
        print(f"Dashboard error: {str(e)}")  # 디버그용
        return jsonify({'error': f'Dashboard error: {str(e)}'}), 500

@app.route('/software-dashboard')
def software_dashboard():
    """소프트웨어 관리 대시보드 웹페이지"""
    try:
        current_dir = os.path.dirname(os.path.abspath(__file__))
        dashboard_path = os.path.join(current_dir, 'static', 'software_dashboard.html')
        
        if os.path.exists(dashboard_path):
            with open(dashboard_path, 'r', encoding='utf-8') as f:
                content = f.read()
                return content, 200, {'Content-Type': 'text/html; charset=utf-8'}
        else:
            return jsonify({'error': f'Software dashboard not found at {dashboard_path}'}), 404
            
    except Exception as e:
        return jsonify({'error': f'Software dashboard error: {str(e)}'}), 500

@app.route('/logs-dashboard')
def logs_dashboard():
    """로깅 대시보드 웹페이지"""
    try:
        current_dir = os.path.dirname(os.path.abspath(__file__))
        dashboard_path = os.path.join(current_dir, 'static', 'logs_dashboard.html')
        
        if os.path.exists(dashboard_path):
            with open(dashboard_path, 'r', encoding='utf-8') as f:
                content = f.read()
                return content, 200, {'Content-Type': 'text/html; charset=utf-8'}
        else:
            return jsonify({'error': f'Logs dashboard not found at {dashboard_path}'}), 404
            
    except Exception as e:
        return jsonify({'error': f'Logs dashboard error: {str(e)}'}), 500

@app.route('/static/<path:filename>')
def static_files(filename):
    """정적 파일 서빙"""
    static_dir = os.path.join(os.path.dirname(__file__), 'static')
    return send_from_directory(static_dir, filename)

@app.route('/api/asset/register', methods=['POST'])
def register_asset():
    """자산 등록"""
    try:
        data = request.get_json()
        
        # 필수 필드 검증
        required_fields = ['hostname', 'ip_address', 'mac_address']
        for field in required_fields:
            if field not in data:
                return jsonify({'error': f'Missing required field: {field}'}), 400
        
        # 자산 등록
        asset_id = db.register_asset(
            data['hostname'],
            data['ip_address'],
            data['mac_address']
        )
        
        logger.info(f"Asset registered: {data['hostname']} (ID: {asset_id})")
        
        return jsonify({
            'success': True,
            'asset_id': asset_id,
            'message': 'Asset registered successfully'
        })
        
    except Exception as e:
        logger.error(f"Asset registration error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/asset/update', methods=['POST'])
def update_asset():
    """자산 정보 업데이트"""
    try:
        data = request.get_json()
        
        # 자산 ID 확인
        if 'hostname' not in data:
            return jsonify({'error': 'Missing hostname'}), 400
        
        # 자산 등록 또는 가져오기
        asset_id = db.register_asset(
            data['hostname'],
            data.get('ip_address', ''),
            data.get('mac_address', '')
        )
        
        # 각 정보 타입별 업데이트
        if 'hardware_info' in data:
            db.update_hardware_info(asset_id, data['hardware_info'])
        
        if 'network_info' in data:
            db.update_network_info(asset_id, data['network_info'])
        
        if 'software_info' in data:
            db.update_software_info(asset_id, data['software_info'])
        
        if 'com_ports' in data:
            db.update_com_ports(asset_id, data['com_ports'])
        
        logger.info(f"Asset updated: {data['hostname']} (ID: {asset_id})")
        
        return jsonify({
            'success': True,
            'asset_id': asset_id,
            'message': 'Asset updated successfully'
        })
        
    except Exception as e:
        logger.error(f"Asset update error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/asset/batch-update', methods=['POST'])
def batch_update_assets():
    """다중 자산 배치 업데이트 (성능 최적화)"""
    try:
        data = request.get_json()
        assets_data = data.get('assets', [])
        
        if not assets_data:
            return jsonify({'error': 'No assets data provided'}), 400
        
        # 배치 크기 제한 (너무 큰 배치 방지)
        if len(assets_data) > 50:
            return jsonify({'error': 'Batch size too large (max 50)'}), 400
        
        processed_count = 0
        errors = []
        
        for asset_data in assets_data:
            try:
                hostname = asset_data.get('hostname')
                if not hostname:
                    errors.append({'hostname': 'unknown', 'error': 'Missing hostname'})
                    continue
                
                # 자산 등록 또는 업데이트
                asset_id = db.register_asset(
                    hostname,
                    asset_data.get('ip_address', ''),
                    asset_data.get('mac_address', '')
                )
                
                # 각 정보 타입별 배치 처리
                if 'hardware_info' in asset_data:
                    db.update_hardware_info(asset_id, asset_data['hardware_info'])
                
                processed_count += 1
                
            except Exception as e:
                errors.append({'hostname': hostname, 'error': str(e)})
        
        logger.info(f"Batch update completed: {processed_count}/{len(assets_data)} assets")
        
        return jsonify({
            'success': True,
            'processed': processed_count,
            'total': len(assets_data),
            'errors': errors,
            'message': f'Batch update completed: {processed_count} assets processed'
        })
        
    except Exception as e:
        logger.error(f"Batch update error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/asset/heartbeat', methods=['POST'])
def asset_heartbeat():
    """경량 하트비트 (최소한의 상태 업데이트)"""
    try:
        data = request.get_json()
        hostname = data.get('hostname')
        
        if not hostname:
            return jsonify({'error': 'Missing hostname'}), 400
        
        # 스테거링 응답 (서버 부하 분산)
        delay = random.uniform(0.1, 0.5)  # 0.1~0.5초 랜덤 지연
        time.sleep(delay)
        
        # 최소한의 정보만 업데이트
        asset_id = db.register_asset(
            hostname,
            data.get('ip_address', ''),
            data.get('mac_address', '')
        )
        
        # 다음 하트비트 간격 반환 (400대 대응으로 간격 증가)
        next_heartbeat = random.randint(300, 360)  # 5~6분 랜덤
        
        return jsonify({
            'success': True,
            'asset_id': asset_id,
            'next_heartbeat': next_heartbeat,
            'server_time': datetime.datetime.now().isoformat()
        })
        
    except Exception as e:
        logger.error(f"Heartbeat error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/assets', methods=['GET'])
def get_assets():
    """자산 목록 조회"""
    try:
        assets = db.get_assets_summary()
        return jsonify({
            'success': True,
            'count': len(assets),
            'assets': assets
        })
        
    except Exception as e:
        logger.error(f"Get assets error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/asset/<hostname>', methods=['GET'])
def get_asset_detail(hostname):
    """특정 자산 상세 정보 조회"""
    try:
        conn = db.get_connection()
        cursor = conn.cursor()
        
        # 자산 기본 정보
        cursor.execute("SELECT * FROM assets WHERE hostname = ?", (hostname,))
        asset = cursor.fetchone()
        
        if not asset:
            return jsonify({'error': 'Asset not found'}), 404
        
        asset_id = asset[0]
        
        # 하드웨어 정보
        cursor.execute("SELECT * FROM hardware_info WHERE asset_id = ? ORDER BY updated_at DESC LIMIT 1", (asset_id,))
        hardware = cursor.fetchone()
        
        # 네트워크 정보
        cursor.execute("SELECT * FROM network_info WHERE asset_id = ?", (asset_id,))
        network = cursor.fetchall()
        
        # 소프트웨어 정보
        cursor.execute("SELECT * FROM software_info WHERE asset_id = ? ORDER BY updated_at DESC LIMIT 1", (asset_id,))
        software = cursor.fetchone()
        
        # COM 포트 정보
        cursor.execute("SELECT * FROM com_ports WHERE asset_id = ?", (asset_id,))
        com_ports = cursor.fetchall()
        
        # RUST 정보 조회 (icons.db에서)
        rust_info = db.get_rust_info(hostname)
        
        conn.close()
        
        return jsonify({
            'success': True,
            'asset': {
                'basic_info': {
                    'hostname': asset[1],
                    'ip_address': asset[2],
                    'mac_address': asset[3],
                    'last_seen': asset[4],
                    'status': asset[5]
                },
                'hardware': hardware,
                'network': network,
                'software': json.loads(software[2]) if software and software[2] else [],
                'com_ports': com_ports,
                'rust_info': rust_info
            }
        })
        
    except Exception as e:
        logger.error(f"Get asset detail error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/stats', methods=['GET'])
def get_stats():
    """서버 통계 정보"""
    try:
        conn = db.get_connection()
        cursor = conn.cursor()
        
        # 총 자산 수
        cursor.execute("SELECT COUNT(*) FROM assets")
        total_assets = cursor.fetchone()[0]
        
        # 활성 자산 수 (최근 3분 내 접속으로 변경)
        cursor.execute("""
            SELECT COUNT(*) FROM assets 
            WHERE (julianday('now') - julianday(last_seen)) * 24 * 60 <= 3
        """)
        active_assets = cursor.fetchone()[0]
        
        # 비활성 자산 수
        inactive_assets = total_assets - active_assets
        
        # 최근 등록된 자산 (최근 24시간)
        cursor.execute("""
            SELECT COUNT(*) FROM assets 
            WHERE datetime(created_at) > datetime('now', '-1 day')
        """)
        recent_assets = cursor.fetchone()[0]
        
        conn.close()
        
        return jsonify({
            'success': True,
            'stats': {
                'total_assets': total_assets,
                'active_assets': active_assets,
                'inactive_assets': inactive_assets,
                'recent_registrations': recent_assets,
                'server_uptime': time.time()  # 간단한 업타임
            }
        })
        
    except Exception as e:
        logger.error(f"Get stats error: {e}")
        return jsonify({'error': str(e)}), 500

def cleanup_old_data():
    """개선된 데이터 정리 (백그라운드 작업)"""
    logger.info("🧹 Cleanup thread started")
    
    while True:
        try:
            logger.info("🔄 Starting cleanup cycle...")
            conn = db.get_connection()
            try:
                cursor = conn.cursor()
                
                # 현재 상태 확인 (디버깅용)
                cursor.execute("""
                    SELECT hostname, last_seen, status,
                           (julianday('now') - julianday(last_seen)) * 24 * 60 as minutes_ago
                    FROM assets 
                    ORDER BY last_seen DESC
                """)
                current_assets = cursor.fetchall()
                
                logger.info(f"📊 Current assets before cleanup:")
                for asset in current_assets[:5]:  # 상위 5개만 로그
                    logger.info(f"  - {asset[0]}: {asset[1]} ({asset[2]}) - {asset[3]:.1f}분 전")
                
                # 트랜잭션 시작
                cursor.execute("BEGIN IMMEDIATE")
                
                # 오프라인 상태인 자산을 비활성으로 표시 (2분 이상 미접속)
                # 시간대 문제 해결을 위해 현재 시간 직접 계산
                cursor.execute("""
                    UPDATE assets 
                    SET status = 'inactive' 
                    WHERE (julianday('now') - julianday(last_seen)) * 24 * 60 > 2 
                    AND status = 'active'
                """)
                deactivated = cursor.rowcount
                
                # 최근 접속한 자산을 다시 활성으로 표시 (1분 이내 접속)
                cursor.execute("""
                    UPDATE assets 
                    SET status = 'active' 
                    WHERE (julianday('now') - julianday(last_seen)) * 24 * 60 <= 1 
                    AND status = 'inactive'
                """)
                activated = cursor.rowcount
                
                logger.info(f"📈 Cleanup results: {deactivated} deactivated, {activated} activated")
                
                # 커밋
                cursor.execute("COMMIT")
                
                # 로깅 개선 - 항상 상태 출력
                current_time = datetime.datetime.now().strftime('%H:%M:%S')
                logger.info(f"🧹 [{current_time}] Cleanup: deactivated={deactivated}, activated={activated}")
                
                # 자산 상태 요약 출력 (디버깅용)
                cursor.execute("SELECT status, COUNT(*) FROM assets GROUP BY status")
                status_summary = cursor.fetchall()
                status_str = ", ".join([f"{status}: {count}" for status, count in status_summary])
                if status_str:
                    logger.info(f"📊 Asset status: {status_str}")
                
            except Exception as e:
                # 롤백 시도
                try:
                    cursor.execute("ROLLBACK")
                except:
                    pass
                raise e
            finally:
                db.return_connection(conn)
            
        except Exception as e:
            logger.error(f"❌ Cleanup error: {e}")
            # 예외 발생해도 스레드 계속 실행
        
        # 1분마다 실행 (더 빠른 상태 업데이트)
        # 메모리 사용량 간단 체크 (한 달 재부팅으로 관리하지만 모니터링용)
        try:
            import psutil
            memory_mb = psutil.Process().memory_info().rss / 1024 / 1024
            if memory_mb > 1024:  # 1GB 초과 시 로그
                logger.warning(f"⚠️ High memory usage: {memory_mb:.1f}MB")
        except:
            pass
        
        time.sleep(30)  # 30초마다 cleanup 실행

# 실시간 자산 상태 업데이트 API
@app.route('/api/assets/refresh-status', methods=['POST'])
def refresh_asset_status():
    """자산 상태를 즉시 업데이트합니다 (에이전트 종료 감지용)"""
    try:
        conn = db.get_connection()
        cursor = conn.cursor()
        
        # 트랜잭션 시작
        cursor.execute("BEGIN IMMEDIATE")
        
        # 3분 이상 미접속 자산을 비활성으로 표시
        cursor.execute("""
            UPDATE assets 
            SET status = 'inactive' 
            WHERE (julianday('now') - julianday(last_seen)) * 24 * 60 > 3 
            AND status = 'active'
        """)
        deactivated = cursor.rowcount
        
        # 2분 이내 접속 자산을 활성으로 표시
        cursor.execute("""
            UPDATE assets 
            SET status = 'active' 
            WHERE (julianday('now') - julianday(last_seen)) * 24 * 60 <= 2 
            AND status = 'inactive'
        """)
        activated = cursor.rowcount
        
        cursor.execute("COMMIT")
        
        # 업데이트된 상태 통계
        cursor.execute("SELECT status, COUNT(*) FROM assets GROUP BY status")
        status_summary = cursor.fetchall()
        
        db.return_connection(conn)
        
        return jsonify({
            'success': True,
            'deactivated': deactivated,
            'activated': activated,
            'status_summary': dict(status_summary),
            'message': f'Status updated: {deactivated} deactivated, {activated} activated'
        })
        
    except Exception as e:
        logger.error(f"Refresh asset status error: {e}")
        return jsonify({'error': str(e)}), 500

# 관리자 설정 관련 API
@app.route('/api/admin/keywords', methods=['GET'])
def get_admin_keywords():
    """관리자 키워드 설정 조회"""
    try:
        conn = db.get_connection()
        cursor = conn.cursor()
        
        # admin_settings 테이블 존재 확인 및 생성
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS admin_settings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                setting_key TEXT UNIQUE NOT NULL,
                setting_value TEXT NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # admin_settings 테이블에서 키워드 설정 조회
        cursor.execute("SELECT setting_value FROM admin_settings WHERE setting_key = 'license_keywords'")
        result = cursor.fetchone()
        
        if result:
            keywords = json.loads(result[0])
        else:
            # 기본 키워드 설정
            keywords = {
                "commercial": ["microsoft", "office", "adobe", "autocad", "windows"],
                "free": ["chrome", "firefox", "vlc", "7-zip", "notepad++"],
                "opensource": ["python", "node", "git", "vscode", "visual studio code"]
            }
        
        conn.close()
        
        return jsonify({
            'success': True,
            'keywords': keywords
        })
        
    except Exception as e:
        logger.error(f"Get admin keywords error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/admin/keywords', methods=['POST'])
def save_admin_keywords():
    """관리자 키워드 설정 저장"""
    try:
        data = request.get_json()
        keywords = data.get('keywords', {})
        
        conn = db.get_connection()
        cursor = conn.cursor()
        
        # admin_settings 테이블이 없으면 생성
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS admin_settings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                setting_key TEXT UNIQUE NOT NULL,
                setting_value TEXT NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 키워드 설정 저장 (UPSERT)
        cursor.execute('''
            INSERT OR REPLACE INTO admin_settings (setting_key, setting_value, updated_at)
            VALUES ('license_keywords', ?, CURRENT_TIMESTAMP)
        ''', (json.dumps(keywords, ensure_ascii=False),))
        
        conn.commit()
        conn.close()
        
        logger.info(f"Admin keywords updated: {len(keywords)} categories")
        
        return jsonify({
            'success': True,
            'message': 'Keywords saved successfully'
        })
        
    except Exception as e:
        logger.error(f"Save admin keywords error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/admin/manual-classifications', methods=['GET'])
def get_manual_classifications():
    """수동 분류 설정 조회"""
    try:
        conn = db.get_connection()
        cursor = conn.cursor()
        
        # admin_settings 테이블 존재 확인 및 생성
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS admin_settings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                setting_key TEXT UNIQUE NOT NULL,
                setting_value TEXT NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        cursor.execute("SELECT setting_value FROM admin_settings WHERE setting_key = 'manual_classifications'")
        result = cursor.fetchone()
        
        if result:
            classifications = json.loads(result[0])
        else:
            classifications = {}
        
        conn.close()
        
        return jsonify({
            'success': True,
            'classifications': classifications
        })
        
    except Exception as e:
        logger.error(f"Get manual classifications error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/admin/manual-classifications', methods=['POST'])
def save_manual_classifications():
    """수동 분류 설정 저장"""
    try:
        data = request.get_json()
        classifications = data.get('classifications', {})
        
        conn = db.get_connection()
        cursor = conn.cursor()
        
        # admin_settings 테이블이 없으면 생성
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS admin_settings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                setting_key TEXT UNIQUE NOT NULL,
                setting_value TEXT NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 수동 분류 설정 저장 (UPSERT)
        cursor.execute('''
            INSERT OR REPLACE INTO admin_settings (setting_key, setting_value, updated_at)
            VALUES ('manual_classifications', ?, CURRENT_TIMESTAMP)
        ''', (json.dumps(classifications, ensure_ascii=False),))
        
        conn.commit()
        conn.close()
        
        logger.info(f"Manual classifications updated: {len(classifications)} software")
        
        return jsonify({
            'success': True,
            'message': 'Manual classifications saved successfully'
        })
        
    except Exception as e:
        logger.error(f"Save manual classifications error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/admin/settings-backup', methods=['GET'])
def backup_admin_settings():
    """관리자 설정 백업"""
    try:
        conn = db.get_connection()
        cursor = conn.cursor()
        
        cursor.execute("SELECT setting_key, setting_value FROM admin_settings")
        results = cursor.fetchall()
        
        backup_data = {}
        for row in results:
            backup_data[row[0]] = json.loads(row[1])
        
        backup_data['backup_date'] = datetime.datetime.now().isoformat()
        backup_data['version'] = '1.0.0'
        
        conn.close()
        
        return jsonify({
            'success': True,
            'backup': backup_data
        })
        
    except Exception as e:
        logger.error(f"Backup admin settings error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/admin/settings-restore', methods=['POST'])
def restore_admin_settings():
    """관리자 설정 복원"""
    try:
        data = request.get_json()
        backup = data.get('backup', {})
        
        conn = db.get_connection()
        cursor = conn.cursor()
        
        # admin_settings 테이블이 없으면 생성
        cursor.execute('''
            CREATE TABLE IF NOT EXISTS admin_settings (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                setting_key TEXT UNIQUE NOT NULL,
                setting_value TEXT NOT NULL,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        ''')
        
        # 백업 데이터 복원
        for key, value in backup.items():
            if key not in ['backup_date', 'version']:  # 메타데이터 제외
                cursor.execute('''
                    INSERT OR REPLACE INTO admin_settings (setting_key, setting_value, updated_at)
                    VALUES (?, ?, CURRENT_TIMESTAMP)
                ''', (key, json.dumps(value, ensure_ascii=False)))
        
        conn.commit()
        conn.close()
        
        logger.info(f"Admin settings restored from backup")
        
        return jsonify({
            'success': True,
            'message': 'Settings restored successfully'
        })
        
    except Exception as e:
        logger.error(f"Restore admin settings error: {e}")
        return jsonify({'error': str(e)}), 500

# 소프트웨어 관리 API
@app.route('/api/software/deletion-request', methods=['POST'])
def request_software_deletion():
    """소프트웨어 삭제 요청"""
    try:
        data = request.get_json()
        hostname = data.get('hostname')
        software_name = data.get('software_name')
        
        if not hostname or not software_name:
            return jsonify({'error': 'hostname과 software_name이 필요합니다'}), 400
        
        # 해당 자산이 존재하는지 확인
        conn = db.get_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT id FROM assets WHERE hostname = ?", (hostname,))
        asset = cursor.fetchone()
        conn.close()
        
        if not asset:
            return jsonify({'error': '해당 자산을 찾을 수 없습니다'}), 404
        
        # 삭제 요청을 데이터베이스에 기록
        conn = db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            INSERT OR REPLACE INTO software_management_requests (
                hostname, software_name, action_type, requested_at, status
            ) VALUES (?, ?, 'deletion_request', CURRENT_TIMESTAMP, 'pending')
        ''', (hostname, software_name))
        conn.commit()
        conn.close()
        
        logger.info(f"Software deletion request: {software_name} on {hostname}")
        
        return jsonify({
            'success': True,
            'message': f'{hostname}에 {software_name} 삭제 요청이 전송되었습니다'
        })
        
    except Exception as e:
        logger.error(f"Software deletion request error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/software/force-removal', methods=['POST'])
def force_software_removal():
    """소프트웨어 강제 제거"""
    try:
        data = request.get_json()
        hostname = data.get('hostname')
        software_name = data.get('software_name')
        
        if not hostname or not software_name:
            return jsonify({'error': 'hostname과 software_name이 필요합니다'}), 400
        
        # 해당 자산이 존재하는지 확인
        conn = db.get_connection()
        cursor = conn.cursor()
        cursor.execute("SELECT id FROM assets WHERE hostname = ?", (hostname,))
        asset = cursor.fetchone()
        conn.close()
        
        if not asset:
            return jsonify({'error': '해당 자산을 찾을 수 없습니다'}), 404
        
        # 강제 제거 요청을 데이터베이스에 기록
        conn = db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            INSERT OR REPLACE INTO software_management_requests (
                hostname, software_name, action_type, requested_at, status
            ) VALUES (?, ?, 'force_removal', CURRENT_TIMESTAMP, 'pending')
        ''', (hostname, software_name))
        conn.commit()
        conn.close()
        
        logger.info(f"Software force removal: {software_name} on {hostname}")
        
        return jsonify({
            'success': True,
            'message': f'{hostname}에서 {software_name} 강제 제거가 요청되었습니다'
        })
        
    except Exception as e:
        logger.error(f"Software force removal error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/software/management-request', methods=['POST'])
def create_management_request():
    """관리 요청 생성 (대시보드용)"""
    try:
        data = request.get_json()
        hostname = data.get('hostname')
        software_name = data.get('software_name', 'GeoMedical Helper App')
        action_type = data.get('action_type', 'uninstall')
        
        if not hostname:
            return jsonify({'success': False, 'message': 'Hostname is required'}), 400
        
        conn = db.get_connection()
        cursor = conn.cursor()
        
        # 중복 요청 확인 (동일한 호스트에 대한 pending 상태의 동일한 요청이 있는지)
        cursor.execute('''
            SELECT id FROM software_management_requests 
            WHERE hostname = ? AND software_name = ? AND action_type = ? AND status = 'pending'
        ''', (hostname, software_name, action_type))
        
        existing = cursor.fetchone()
        if existing:
            # app_update의 경우 기존 요청을 취소하고 새로 생성
            if action_type == 'app_update':
                cursor.execute('''
                    UPDATE software_management_requests 
                    SET status = 'cancelled', completed_at = CURRENT_TIMESTAMP
                    WHERE hostname = ? AND action_type = ? AND status = 'pending'
                ''', (hostname, action_type))
                conn.commit()
                logger.info(f"Cancelled existing app_update request for {hostname}")
            else:
                conn.close()
                return jsonify({
                    'success': False, 
                    'message': f'이미 대기 중인 {action_type} 요청이 있습니다.'
                }), 409
        
        # 새 요청 생성
        cursor.execute('''
            INSERT INTO software_management_requests (hostname, software_name, action_type, status, requested_at)
            VALUES (?, ?, ?, 'pending', CURRENT_TIMESTAMP)
        ''', (hostname, software_name, action_type))
        
        request_id = cursor.lastrowid
        conn.commit()
        
        # 로그 기록
        logger.info(f"Management request created: ID={request_id}, Host={hostname}, Action={action_type}, Software={software_name}")
        
        conn.close()
        
        return jsonify({
            'success': True,
            'message': f'{action_type} 요청이 생성되었습니다.',
            'request_id': request_id
        })
        
    except Exception as e:
        logger.error(f"Create management request error: {e}")
        return jsonify({'success': False, 'error': str(e)}), 500

@app.route('/api/software/management-requests/<hostname>', methods=['GET'])
def get_management_requests(hostname):
    """특정 호스트의 관리 요청 조회 (에이전트용)"""
    try:
        conn = db.get_connection()
        cursor = conn.cursor()
        
        cursor.execute('''
            SELECT id, software_name, action_type, requested_at 
            FROM software_management_requests 
            WHERE hostname = ? AND status = 'pending'
            ORDER BY requested_at ASC
        ''', (hostname,))
        
        requests = cursor.fetchall()
        conn.close()
        
        result = []
        for req in requests:
            result.append({
                'id': req[0],
                'software_name': req[1],
                'action_type': req[2],
                'requested_at': req[3]
            })
        
        return jsonify({
            'success': True,
            'requests': result
        })
        
    except Exception as e:
        logger.error(f"Get management requests error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/software/management-requests/<int:request_id>/complete', methods=['POST'])
def complete_management_request(request_id):
    """관리 요청 완료 처리 (에이전트용)"""
    try:
        data = request.get_json()
        result = data.get('result', 'completed')  # completed, failed, cancelled
        
        conn = db.get_connection()
        cursor = conn.cursor()
        cursor.execute('''
            UPDATE software_management_requests 
            SET status = ?, completed_at = CURRENT_TIMESTAMP
            WHERE id = ?
        ''', (result, request_id))
        conn.commit()
        conn.close()
        
        logger.info(f"Management request {request_id} completed with result: {result}")
        
        return jsonify({
            'success': True,
            'message': '요청이 완료 처리되었습니다'
        })
        
    except Exception as e:
        logger.error(f"Complete management request error: {e}")
        return jsonify({'error': str(e)}), 500

# 에이전트 로깅 API
@app.route('/api/agent/log', methods=['POST'])
def submit_agent_log():
    """에이전트 로그 제출"""
    try:
        data = request.get_json()
        
        # 필수 필드 확인
        required_fields = ['hostname', 'level', 'type', 'message']
        for field in required_fields:
            if field not in data:
                return jsonify({'error': f'Missing required field: {field}'}), 400
        
        # 로그 추가
        db.add_agent_log(
            hostname=data['hostname'],
            log_level=data['level'],
            log_type=data['type'],
            message=data['message'],
            details=data.get('details')
        )
        
        return jsonify({
            'success': True,
            'message': 'Log submitted successfully'
        })
        
    except Exception as e:
        logger.error(f"Agent log submission error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/agent/logs/<hostname>', methods=['GET'])
def get_agent_logs_api(hostname):
    """특정 에이전트의 로그 조회"""
    try:
        logs = db.get_agent_logs(hostname)
        
        return jsonify({
            'success': True,
            'hostname': hostname,
            'logs': logs,
            'count': len(logs)
        })
        
    except Exception as e:
        logger.error(f"Get agent logs error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/agent/logs', methods=['GET'])
def get_all_agent_logs():
    """로그가 있는 모든 에이전트 목록 조회"""
    try:
        agents = db.get_all_agent_hostnames_with_logs()
        
        return jsonify({
            'success': True,
            'agents': agents,
            'count': len(agents)
        })
        
    except Exception as e:
        logger.error(f"Get all agent logs error: {e}")
        return jsonify({'error': str(e)}), 500

# 메시지 브로드캐스트 API
@app.route('/api/broadcast-message', methods=['POST'])
def broadcast_message():
    """모든 에이전트에 메시지 브로드캐스트"""
    try:
        data = request.get_json()
        
        if not data or not data.get('message'):
            return jsonify({'error': 'Message is required'}), 400
            
        message = data['message']
        sender = data.get('sender', 'System')
        
        # 메시지 ID 생성 (타임스탬프 기반)
        message_id = f"msg_{int(time.time() * 1000)}"
        
        # 현재 활성 에이전트 수 조회
        conn = db.get_connection()
        try:
            cursor = conn.cursor()
            # 최근 2분 내에 활동한 에이전트 수 계산 (30초 업데이트 주기 고려)
            cursor.execute('''
                SELECT COUNT(*) FROM assets 
                WHERE datetime(last_seen) > datetime('now', '-2 minutes')
            ''')
            target_count = cursor.fetchone()[0]
            
            # 브로드캐스트 메시지를 software_management_requests 테이블에 저장
            # 모든 활성 에이전트가 확인할 수 있도록 특별한 형태로 저장
            cursor.execute('''
                INSERT INTO software_management_requests (
                    hostname, action_type, software_name, status, requested_at
                ) VALUES (
                    'BROADCAST_ALL', 'broadcast_message', ?, 'pending', datetime('now')
                )
            ''', (json.dumps({
                'message_id': message_id,
                'message': message,
                'sender': sender,
                'timestamp': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            }),))
            
            conn.commit()
            
        finally:
            db.return_connection(conn)
        
        logger.info(f"Broadcast message sent: {message[:50]}... (Target: {target_count} agents)")
        
        return jsonify({
            'success': True,
            'message_id': message_id,
            'target_count': target_count,
            'timestamp': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        })
        
    except Exception as e:
        logger.error(f"Broadcast message error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/broadcast-messages/<hostname>', methods=['GET'])
def get_broadcast_messages(hostname):
    """특정 호스트의 브로드캐스트 메시지 조회"""
    try:
        conn = db.get_connection()
        try:
            cursor = conn.cursor()
            
            # 최근 10분간의 브로드캐스트 메시지 조회 (해당 호스트가 아직 확인하지 않은 것만)
            cursor.execute('''
                SELECT smr.id, smr.software_name, smr.requested_at
                FROM software_management_requests smr
                LEFT JOIN broadcast_acknowledgments ba ON JSON_EXTRACT(smr.software_name, '$.message_id') = ba.message_id 
                                                        AND ba.hostname = ?
                WHERE smr.hostname = 'BROADCAST_ALL' 
                  AND smr.action_type = 'broadcast_message'
                  AND smr.status = 'pending'
                  AND datetime(smr.requested_at) > datetime('now', '-10 minutes')
                  AND ba.id IS NULL  -- 해당 호스트가 아직 확인하지 않은 메시지만
                ORDER BY smr.requested_at DESC
            ''', (hostname,))
            
            messages = []
            for row in cursor.fetchall():
                try:
                    message_data = json.loads(row[1])  # software_name 필드에 JSON 저장
                    messages.append({
                        'id': row[0],
                        'message_id': message_data.get('message_id', f'msg_{row[0]}'),
                        'message': message_data.get('message', ''),
                        'sender': message_data.get('sender', 'System'),
                        'timestamp': message_data.get('timestamp', row[2]),
                        'created_at': row[2]
                    })
                except (json.JSONDecodeError, KeyError) as e:
                    logger.error(f"Failed to parse broadcast message data: {e}, row: {row}")
                    continue
            
        finally:
            db.return_connection(conn)
            
        return jsonify({
            'messages': messages,
            'count': len(messages)
        })
        
    except Exception as e:
        logger.error(f"Get broadcast messages error: {e}")
        return jsonify({'error': str(e)}), 500

@app.route('/api/broadcast-messages/<int:message_id>/acknowledge', methods=['POST'])
def acknowledge_broadcast_message(message_id):
    """브로드캐스트 메시지 확인 처리"""
    try:
        data = request.get_json()
        hostname = data.get('hostname')
        
        if not hostname:
            return jsonify({'error': 'Hostname is required'}), 400
        
        conn = db.get_connection()
        try:
            cursor = conn.cursor()
            
            # 브로드캐스트 메시지에서 message_id 추출
            cursor.execute('''
                SELECT software_name FROM software_management_requests
                WHERE id = ? AND action_type = 'broadcast_message'
            ''', (message_id,))
            
            result = cursor.fetchone()
            if not result:
                return jsonify({'error': 'Message not found'}), 404
            
            message_data = json.loads(result[0])
            actual_message_id = message_data.get('message_id', f'msg_{message_id}')
            
            # 브로드캐스트 확인 상태 기록 (중복 방지)
            cursor.execute('''
                INSERT OR IGNORE INTO broadcast_acknowledgments (message_id, hostname)
                VALUES (?, ?)
            ''', (actual_message_id, hostname))
            
            # 해당 메시지의 확인 로그 기록
            cursor.execute('''
                INSERT OR IGNORE INTO agent_logs (
                    hostname, log_level, log_type, message, details
                ) VALUES (?, ?, ?, ?, ?)
            ''', (
                hostname,
                'INFO',
                'BROADCAST_ACK',
                f'Broadcast message acknowledged',
                json.dumps({
                    'message_id': actual_message_id, 
                    'request_id': message_id,
                    'timestamp': datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
                })
            ))
            
            conn.commit()
            
        finally:
            db.return_connection(conn)
            
        logger.info(f"Broadcast message {actual_message_id} acknowledged by {hostname}")
        
        return jsonify({
            'success': True,
            'message': 'Message acknowledged',
            'message_id': actual_message_id
        })
        
    except Exception as e:
        logger.error(f"Acknowledge broadcast message error: {e}")
        return jsonify({'error': str(e)}), 500

if __name__ == '__main__':
    # 백그라운드 정리 작업 시작
    cleanup_thread = threading.Thread(target=cleanup_old_data, daemon=True)
    cleanup_thread.start()
    
    print("🚀 Asset Management Server Starting...")
    print("🌐 Web Dashboard: http://localhost:8800/dashboard")
    print("📦 Software Dashboard: http://localhost:8800/software-dashboard")
    print("📋 Logs Dashboard: http://localhost:8800/logs-dashboard")
    print("📊 Assets API: http://localhost:8800/api/assets")
    print("📈 Stats API: http://localhost:8800/api/stats")
    print("🔧 Ready to receive asset data...")
    
    # 서버 시작
    app.run(host='0.0.0.0', port=8800, debug=False, threaded=True)