GNU/_PAGE/data/upbit/db/db_data.php
<?php
/**
 * db_data.php
 * - 기존 로직 및 구조 유지
 * - 컬럼 설정 및 고도화된 JS 기능 추가
 */

date_default_timezone_set('Asia/Seoul');

// 1. 설정 및 파라미터 처리
$DEFAULT_TABLE = 'daemon_upbit_Ticker';
$LIMIT = 7; // 표시 행 수

$table = $_GET['table'] ?? $DEFAULT_TABLE;
$mode = $_GET['mode'] ?? 'html';

// 2. DB 연결
require_once '/home/www/DB/db_upbit.php';

if (!isset($db_upbit)) {
    $error_msg = 'DB 연결 설정 파일을 찾을 수 없습니다.';
    if ($mode === 'json') {
        header('Content-Type: application/json');
        echo json_encode(['ok' => false, 'error' => $error_msg]); exit;
    }
    die($error_msg);
}
$pdo = $db_upbit;

// 3. 보안 및 테이블 유효성 검사
try {
    $stmt = $pdo->query("SHOW TABLES");
    $all_tables = $stmt->fetchAll(PDO::FETCH_COLUMN);

    if (!in_array($table, $all_tables, true)) {
        throw new Exception("존재하지 않는 테이블: " . htmlspecialchars($table));
    }

    $stmt = $pdo->query("SHOW COLUMNS FROM `{$table}`");
    $columns = $stmt->fetchAll(PDO::FETCH_COLUMN);
    
} catch (Exception $e) {
    if ($mode === 'json') {
        header('Content-Type: application/json');
        echo json_encode(['ok' => false, 'error' => $e->getMessage()]); exit;
    }
    die($e->getMessage());
}

// 4. 쿼리 생성
$orderBy = "";
$priorityCols = ['timestamp_ms', 'timestamp', 'collected_ms', 'created_at', 'id'];
foreach ($priorityCols as $c) {
    if (in_array($c, $columns, true)) {
        $orderBy = " ORDER BY `{$c}` DESC ";
        break;
    }
}
if (!$orderBy && !empty($columns)) $orderBy = " ORDER BY `{$columns[0]}` DESC ";

$whereClause = "";
if (in_array('market', $columns, true)) {
    $TARGET_MARKETS = ['KRW-BTC', 'KRW-ETH', 'KRW-XRP', 'KRW-SOL', 'KRW-ADA', 'KRW-BCH', 'KRW-DOGE'];
    $whereClause = " WHERE `market` IN ('" . implode("','", $TARGET_MARKETS) . "') ";
}

$sql = "SELECT * FROM `{$table}` {$whereClause} {$orderBy} LIMIT {$LIMIT}";
$stmt = $pdo->query($sql);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

if ($mode === 'json') {
    header('Content-Type: application/json');
    echo json_encode([
        'ok' => true,
        'table' => $table,
        'columns' => $columns,
        'rows' => $rows,
        'server_time' => date('Y-m-d H:i:s')
    ], JSON_UNESCAPED_UNICODE);
    exit;
}
// 헤더 부분 포함
require_once '/home/www/GNU/_PAGE/head.php';
?>
<!doctype html>
<html lang="ko">
<head>
<meta charset="utf-8">
<title><?= htmlspecialchars($table) ?> PRO MONITOR</title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<style>
    :root {
        --bg-main: #020408;
        --bg-card: #0b101a;
        --bg-header: #151c2c;
        --border: #1e293b;
        --accent: #00f2ff;
        --text-p: #f8fafc;
        --text-s: #94a3b8;
        --row-h: 70px;
        --success: #22c55e;
        --danger: #ef4444;
    }

    body { 
        font-family: 'Pretendard', -apple-system, sans-serif;
        background: var(--bg-main); 
        color: var(--text-p); 
        margin: 0; 
        padding: 0 0 40px 0;
        overflow-x: hidden;
    }

    .container { 
        width: calc(100% - 100px); 
        margin: 0 50px; 
        animation: riseUp 0.8s cubic-bezier(0.16, 1, 0.3, 1) forwards;
    }

    @keyframes riseUp {
        0% { opacity: 0; transform: translateY(50px); }
        100% { opacity: 1; transform: translateY(0); }
    }

    .toolbar {
        display: flex;
        justify-content: space-between;
        align-items: center;
        margin-bottom: 20px;
        gap: 15px;
        flex-wrap: wrap;
    }

    .table-nav-select {
        background: var(--bg-card);
        border: 1px solid var(--border);
        color: var(--accent);
        padding: 10px 15px;
        border-radius: 8px;
        outline: none;
        cursor: pointer;
        font-weight: 600;
        font-size: 14px;
        margin-bottom: 10px;
        transition: 0.3s;
    }
    .table-nav-select:hover { border-color: var(--accent); }

    .search-box {
        flex: 1;
        min-width: 300px;
        position: relative;
    }

    .search-box input {
        float:right;
        width: 700px;
        background: var(--bg-card);
        border: 1px solid var(--border);
        color: #fff;
        padding: 12px 20px;
        border-radius: 8px;
        outline: none;
        transition: 0.3s;
    }

    .search-box input:focus { border-color: var(--accent); box-shadow: 0 0 10px rgba(0,242,255,0.1); }

    .btn-group { display: flex; gap: 8px; }
    
    button {
        background: var(--bg-header);
        border: 1px solid var(--border);
        color: var(--text-p);
        padding: 10px 16px;
        border-radius: 8px;
        cursor: pointer;
        font-size: 13px;
        font-weight: 600;
        transition: 0.2s;
    }

    button:hover { background: var(--border); border-color: var(--text-s); }
    button.active { background: var(--accent); color: var(--bg-main); border-color: var(--accent); }

    #colPanel {
        background: var(--bg-card);
        border: 1px solid var(--border);
        border-radius: 12px;
        padding: 20px;
        margin-bottom: 20px;
        display: none;
        grid-template-columns: repeat(auto-fill, minmax(150px, 1fr));
        gap: 10px;
    }

    .table-wrap { 
        background: var(--bg-card); 
        border-radius: 12px; 
        border: 1px solid var(--border);
        overflow: hidden;
        box-shadow: 0 20px 40px rgba(0,0,0,0.4);
    }

    table { width: 100%; border-collapse: collapse; font-size: 14px; }
    
    th { 
        height: var(--row-h); 
        background: var(--bg-header); 
        color: var(--text-s); 
        text-align: left; 
        padding: 0 20px; 
        font-weight: 600; 
        border-bottom: 1px solid var(--border);
        cursor: pointer;
        user-select: none;
    }

    th:hover { color: var(--accent); }
    th.sort-asc::after { content: ' ↑'; }
    th.sort-desc::after { content: ' ↓'; }

    td { 
        height: var(--row-h); 
        padding: 0 20px; 
        border-bottom: 1px solid rgba(255,255,255,0.03); 
        white-space: nowrap;
    }

    tr:hover td { background: rgba(255,255,255,0.02); }

    .inc { color: var(--success); animation: flash-g 0.8s; font-weight: bold; }
    .dec { color: var(--danger); animation: flash-r 0.8s; font-weight: bold; }
    @keyframes flash-g { from { background: rgba(34,197,94,0.2); } to { background: transparent; } }
    @keyframes flash-r { from { background: rgba(239,68,68,0.2); } to { background: transparent; } }

    .pinned { position: sticky; left: 0; background: var(--bg-card) !important; z-index: 2; border-right: 1px solid var(--border); }
    
    ::-webkit-scrollbar { width: 6px; height: 6px; }
    ::-webkit-scrollbar-thumb { background: #334155; border-radius: 10px; }
    ::-webkit-scrollbar-thumb:hover { background: var(--accent); }

    #refreshCounter { font-family: monospace; color: var(--accent); }
</style>
</head>
<body>

<div class="container">
    <div class="toolbar">
        <div>
            <select class="table-nav-select" onchange="location.href='?table='+this.value">
                <?php
                $nav_tables = [
                    'daemon_upbit_Ticker' => 'Ticker (실시간)',
                    'daemon_upbit_coin_1m' => '1분봉 (1m)',
                    'daemon_upbit_coin_5m' => '5분봉 (5m)',
                    'daemon_upbit_coin_15m' => '15분봉 (15m)',
                    'daemon_upbit_coin_30m' => '30분봉 (30m)',
                    'daemon_upbit_coin_1h' => '1시간봉 (1h)',
                    'daemon_upbit_coin_4h' => '4시간봉 (4h)',
                    'daemon_upbit_coin_8h' => '8시간봉 (8h)',
                    'daemon_upbit_coin_12h' => '12시간봉 (12h)',
                    'daemon_upbit_coin_24h' => '24시간봉 (24h)'
                ];
                foreach ($nav_tables as $val => $name) {
                    $selected = ($table === $val) ? 'selected' : '';
                    echo "<option value='{$val}' {$selected}>{$name}</option>";
                }
                ?>
            </select>
            <h1 style="margin:0; font-size:28px;"><?= htmlspecialchars($table) ?> <span id="refreshCounter"></span></h1>
            <p style="margin:5px 0 0; color:var(--text-s); font-size:12px;">Real-time AJAX Data Processing System</p>
        </div>
        <div class="search-box">
            <input type="text" id="globalSearch" placeholder="모든 컬럼에서 검색...">
        </div>
        <div class="btn-group">
            <button id="toggleRefresh">Pause</button>
            <button id="toggleColBtn">Columns</button>
            <button onclick="exportToCSV()">Export CSV</button>
        </div>
    </div>

    <div id="colPanel"></div>

    <div class="table-wrap">
        <div style="overflow-x: auto;">
            <table id="mainTable">
                <thead id="thead"></thead>
                <tbody id="tbody"></tbody>
            </table>
        </div>
    </div>
</div>

<script>
(function() {
    // 컬럼 한글 매핑 데이터
    const colMap = {
        "id": "기본키",
        "market": "업비트 마켓",
        "trade_date": "체결 날짜(UTC)",
        "trade_time": "체결 시각(UTC)",
        "trade_date_kst": "체결 날짜(KST)",
        "trade_time_kst": "체결 시각(KST)",
        "opening_price": "시가",
        "high_price": "고가",
        "low_price": "저가",
        "trade_price": "현재가",
        "prev_closing_price": "전일 종가",
        "change": "전일 대비 방향",
        "change_price": "가격 변화",
        "change_rate": "변화율",
        "signed_change_price": "부호 포함 가격 변화",
        "signed_change_rate": "부호 포함 변화율",
        "trade_volume": "최근 거래량",
        "acc_trade_volume": "누적 거래량",
        "acc_trade_volume_24h": "24시간 누적 거래량",
        "acc_trade_price": "누적 거래대금",
        "acc_trade_price_24h": "24시간 누적 거래대금",
        "highest_52_week_price": "52주 최고가",
        "highest_52_week_date": "52주 최고가 날짜",
        "lowest_52_week_price": "52주 최저가",
        "lowest_52_week_date": "52주 최저가 날짜",
        "collected_at": "서버 수집 시각",
        "collected_ms": "서버 수집 시각(ms)",
        "ob_timestamp": "오더북 타임스탬프(ms)",
        "ob_total_ask_size": "전체 매도 잔량",
        "ob_total_bid_size": "전체 매수 잔량",
        "ob_units": "호가 묶음",
        "ob_collected_at": "오더북 수집 시각",
        "ob_collected_ms": "오더북 수집 ms",
        "tr_trade_timestamp": "체결 시간 타임스탬프(ms)",
        "tr_trade_price": "체결 가격",
        "tr_trade_volume": "체결 수량",
        "tr_ask_bid": "매수/매도 구분",
        "tr_trade_date_utc": "UTC 체결일",
        "tr_trade_time_utc": "UTC 체결시간",
        "tr_trade_date_kst": "KST 체결일",
        "tr_trade_time_kst": "KST 체결시간",
        "tr_collected_at": "체결 수집 시각",
        "tr_collected_ms": "체결 수집 ms",
        "day_of_week": "요일",
        "korean_name": "마켓 한글명",
        "daemon_id": "데몬 ID",
        "daemon_pid": "데몬 PID",
        "daemon_heartbeat": "데몬 하트비트"
    };

    const state = {
        currentTable: "<?= $table ?>",
        allColumns: <?= json_encode($columns) ?>,
        hiddenCols: JSON.parse(localStorage.getItem('hide_cols_<?= $table ?>') || '["ob_units"]'),
        data: [],
        prevData: {},
        isPaused: false,
        searchTerm: '',
        sortCol: '',
        sortDir: 'desc',
        countdown: 10
    };

    function initColPanel() {
        const panel = document.getElementById('colPanel');
        panel.innerHTML = state.allColumns.map(col => `
            <label style="display:flex; align-items:center; gap:8px; cursor:pointer;">
                <input type="checkbox" ${!state.hiddenCols.includes(col) ? 'checked' : ''} 
                       onchange="window.toggleColumn('${col}')">
                <span style="font-size:13px;">${colMap[col] || col}</span>
            </label>
        `).join('');
    }

    window.toggleColumn = (col) => {
        if (state.hiddenCols.includes(col)) {
            state.hiddenCols = state.hiddenCols.filter(c => c !== col);
        } else {
            state.hiddenCols.push(col);
        }
        localStorage.setItem('hide_cols_<?= $table ?>', JSON.stringify(state.hiddenCols));
        renderTable();
    };

    async function fetchData() {
        if (state.isPaused) return;
        try {
            const res = await fetch(`?mode=json&table=${state.currentTable}&_=${Date.now()}`);
            const json = await res.json();
            if (json.ok) {
                state.data = json.rows;
                renderTable();
                state.countdown = 10;
            }
        } catch (e) {
            console.error("Fetch Error:", e);
        }
    }

    function renderTable() {
        let displayData = state.data.filter(row => {
            return Object.values(row).some(val => 
                String(val).toLowerCase().includes(state.searchTerm.toLowerCase())
            );
        });

        if (state.sortCol) {
            displayData.sort((a, b) => {
                let v1 = a[state.sortCol], v2 = b[state.sortCol];
                if (!isNaN(v1) && !isNaN(v2)) { v1 = parseFloat(v1); v2 = parseFloat(v2); }
                if (v1 < v2) return state.sortDir === 'asc' ? -1 : 1;
                if (v1 > v2) return state.sortDir === 'asc' ? 1 : -1;
                return 0;
            });
        }

        const activeCols = state.allColumns.filter(c => !state.hiddenCols.includes(c));

        document.getElementById('thead').innerHTML = `<tr>${activeCols.map(col => `
            <th class="${(col==='market'||col==='coin')?'pinned':''} ${state.sortCol===col? 'sort-'+state.sortDir : ''}" 
                onclick="window.setSort('${col}')">${colMap[col] || col}</th>
        `).join('')}</tr>`;

        document.getElementById('tbody').innerHTML = displayData.map((row, idx) => `
            <tr>${activeCols.map(col => {
                let val = row[col] ?? '';
                let key = `${idx}_${col}`;
                let diffClass = '';
                
                if (state.prevData[key] !== undefined && !isNaN(val) && val !== '') {
                    if (parseFloat(val) > parseFloat(state.prevData[key])) diffClass = 'inc';
                    else if (parseFloat(val) < parseFloat(state.prevData[key])) diffClass = 'dec';
                }
                state.prevData[key] = val;

                return `<td class="${(col==='market'||col==='coin')?'pinned':''} ${diffClass}">${val}</td>`;
            }).join('')}</tr>
        `).join('');
    }

    window.setSort = (col) => {
        if (state.sortCol === col) {
            state.sortDir = state.sortDir === 'asc' ? 'desc' : 'asc';
        } else {
            state.sortCol = col;
            state.sortDir = 'desc';
        }
        renderTable();
    };

    document.getElementById('globalSearch').oninput = (e) => {
        state.searchTerm = e.target.value;
        renderTable();
    };

    document.getElementById('toggleRefresh').onclick = function() {
        state.isPaused = !state.isPaused;
        this.innerText = state.isPaused ? 'Resume' : 'Pause';
        this.classList.toggle('active', state.isPaused);
    };

    document.getElementById('toggleColBtn').onclick = () => {
        const p = document.getElementById('colPanel');
        p.style.display = p.style.display === 'none' ? 'grid' : 'none';
    };

    window.exportToCSV = () => {
        const activeCols = state.allColumns.filter(c => !state.hiddenCols.includes(c));
        let csv = "\uFEFF"; // 한글 깨짐 방지 BOM
        csv += activeCols.map(col => colMap[col] || col).join(',') + '\n';
        state.data.forEach(row => {
            csv += activeCols.map(col => `"${row[col] ?? ''}"`).join(',') + '\n';
        });
        const blob = new Blob([csv], { type: 'text/csv;charset=utf-8;' });
        const url = window.URL.createObjectURL(blob);
        const a = document.createElement('a');
        a.href = url;
        a.download = `data_${state.currentTable}.csv`;
        a.click();
    };

    setInterval(() => {
        if(!state.isPaused) {
            state.countdown--;
            if(state.countdown <= 0) {
                fetchData();
                state.countdown = 10;
            }
            document.getElementById('refreshCounter').innerText = `(${state.countdown}s)`;
        } else {
            document.getElementById('refreshCounter').innerText = `(Paused)`;
        }
    }, 100);

    initColPanel();
    fetchData();
})();
</script>
</body>
</html>

<?php require_once '/home/www/GNU/_PAGE/tail.php'; ?>