<?php 
// filepath: v:\controls\test\HMI\query_builder.php
include "../../session.php";
include "../../userAccess.php"; 

// Database connection
$servername = "192.168.0.13";
$username = "opce";
$password = "opcelasuca";
$dbname = "archive";

try {
    $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
    if (isset($_POST['action'])) {
        header('Content-Type: application/json');
        echo json_encode(['success' => false, 'error' => 'Database connection failed: ' . $e->getMessage()]);
        exit;
    }
    die("Connection failed: " . $e->getMessage());
}

// Handle AJAX requests
if (isset($_POST['action'])) {
    while (ob_get_level()) {
        ob_end_clean();
    }
    ob_start();
    
    header('Content-Type: application/json');
    
    try {
        switch ($_POST['action']) {
            case 'get_schema':
                // Get available tags and their data types
                $tagsStmt = $pdo->prepare("SELECT DISTINCT name FROM id_names WHERE name IS NOT NULL AND name != '' ORDER BY name");
                $tagsStmt->execute();
                $tags = $tagsStmt->fetchAll(PDO::FETCH_COLUMN);
                
                // Get sample data to determine data types and ranges
                $schema = [];
                foreach (array_slice($tags, 0, 50) as $tag) { // Limit to first 50 tags for performance
                    $sampleStmt = $pdo->prepare("
                        SELECT 
                            MIN(h.Value) as min_value,
                            MAX(h.Value) as max_value,
                            AVG(h.Value) as avg_value,
                            COUNT(*) as record_count,
                            MIN(h.TimeStamp) as earliest_date,
                            MAX(h.TimeStamp) as latest_date
                        FROM historicaldata h 
                        INNER JOIN id_names n ON h.ID = n.idnumber 
                        WHERE n.name = ? 
                        AND h.Value IS NOT NULL
                        AND h.TimeStamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
                    ");
                    $sampleStmt->execute([$tag]);
                    $stats = $sampleStmt->fetch(PDO::FETCH_ASSOC);
                    
                    if ($stats && $stats['record_count'] > 0) {
                        $schema[] = [
                            'name' => $tag,
                            'type' => 'numeric',
                            'min_value' => (float)$stats['min_value'],
                            'max_value' => (float)$stats['max_value'],
                            'avg_value' => (float)$stats['avg_value'],
                            'record_count' => (int)$stats['record_count'],
                            'earliest_date' => $stats['earliest_date'],
                            'latest_date' => $stats['latest_date']
                        ];
                    }
                }
                
                echo json_encode([
                    'success' => true,
                    'schema' => $schema,
                    'total_tags' => count($tags)
                ]);
                break;
                
            case 'execute_query':
                $queryConfig = json_decode($_POST['query_config'] ?? '{}', true);
                
                if (empty($queryConfig) || !isset($queryConfig['conditions'])) {
                    throw new Exception('Invalid query configuration');
                }
                
                // Build SQL query from configuration
                $sql = $this->buildSQLFromConfig($queryConfig, $pdo);
                
                // Execute query with limit for safety
                $limit = min((int)($queryConfig['limit'] ?? 1000), 10000);
                $sql .= " LIMIT " . $limit;
                
                $stmt = $pdo->prepare($sql['query']);
                $stmt->execute($sql['params']);
                $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
                
                echo json_encode([
                    'success' => true,
                    'data' => $results,
                    'row_count' => count($results),
                    'sql_query' => $sql['query'],
                    'parameters' => $sql['params'],
                    'execution_time' => microtime(true) - $_SERVER['REQUEST_TIME_FLOAT']
                ]);
                break;
                
            case 'save_query':
                $queryName = $_POST['query_name'] ?? '';
                $queryConfig = $_POST['query_config'] ?? '';
                $userId = $_SESSION['user_id'] ?? 'anonymous';
                
                if (empty($queryName) || empty($queryConfig)) {
                    throw new Exception('Query name and configuration required');
                }
                
                // Create saved queries table if it doesn't exist
                $pdo->exec("CREATE TABLE IF NOT EXISTS saved_queries (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    user_id VARCHAR(50),
                    query_name VARCHAR(100),
                    query_config TEXT,
                    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                )");
                
                $stmt = $pdo->prepare("INSERT INTO saved_queries (user_id, query_name, query_config) VALUES (?, ?, ?)");
                $stmt->execute([$userId, $queryName, $queryConfig]);
                
                echo json_encode([
                    'success' => true,
                    'query_id' => $pdo->lastInsertId(),
                    'message' => 'Query saved successfully'
                ]);
                break;
                
            case 'load_saved_queries':
                $userId = $_SESSION['user_id'] ?? 'anonymous';
                
                $stmt = $pdo->prepare("SELECT id, query_name, query_config, created_at FROM saved_queries WHERE user_id = ? ORDER BY created_at DESC");
                $stmt->execute([$userId]);
                $queries = $stmt->fetchAll(PDO::FETCH_ASSOC);
                
                echo json_encode([
                    'success' => true,
                    'saved_queries' => $queries
                ]);
                break;
                
            case 'get_field_values':
                $fieldName = $_POST['field_name'] ?? '';
                
                if (empty($fieldName)) {
                    throw new Exception('Field name required');
                }
                
                // Get distinct values for the field (limited for performance)
                $stmt = $pdo->prepare("
                    SELECT DISTINCT h.Value
                    FROM historicaldata h 
                    INNER JOIN id_names n ON h.ID = n.idnumber 
                    WHERE n.name = ? 
                    AND h.Value IS NOT NULL
                    AND h.TimeStamp >= DATE_SUB(NOW(), INTERVAL 7 DAY)
                    ORDER BY h.Value
                    LIMIT 100
                ");
                $stmt->execute([$fieldName]);
                $values = $stmt->fetchAll(PDO::FETCH_COLUMN);
                
                echo json_encode([
                    'success' => true,
                    'field_name' => $fieldName,
                    'values' => array_map('floatval', $values)
                ]);
                break;
                
            default:
                throw new Exception('Invalid action specified');
        }
        
    } catch (Exception $e) {
        echo json_encode(['success' => false, 'error' => $e->getMessage()]);
    } catch (Error $e) {
        echo json_encode(['success' => false, 'error' => 'PHP Error: ' . $e->getMessage()]);
    }
    
    ob_end_flush();
    exit;
}

// Helper function to build SQL from query configuration
function buildSQLFromConfig($config, $pdo) {
    $selectedTags = $config['selected_tags'] ?? [];
    $conditions = $config['conditions'] ?? [];
    $orderBy = $config['order_by'] ?? [];
    $groupBy = $config['group_by'] ?? [];
    $aggregations = $config['aggregations'] ?? [];
    
    if (empty($selectedTags)) {
        throw new Exception('No tags selected');
    }
    
    // Build SELECT clause
    $selectFields = [];
    $selectFields[] = "h.TimeStamp";
    
    if (!empty($aggregations)) {
        // Aggregation query
        foreach ($selectedTags as $tag) {
            foreach ($aggregations as $agg) {
                $selectFields[] = "{$agg['function']}(CASE WHEN n.name = '{$tag}' THEN h.Value END) as {$tag}_{$agg['function']}";
            }
        }
    } else {
        // Regular query
        $selectFields[] = "n.name as tag_name";
        $selectFields[] = "h.Value";
    }
    
    $sql = "SELECT " . implode(', ', $selectFields) . " FROM historicaldata h INNER JOIN id_names n ON h.ID = n.idnumber";
    
    // Build WHERE clause
    $whereConditions = [];
    $params = [];
    
    // Tag filter
    $tagPlaceholders = str_repeat('?,', count($selectedTags) - 1) . '?';
    $whereConditions[] = "n.name IN ($tagPlaceholders)";
    $params = array_merge($params, $selectedTags);
    
    // Custom conditions
    foreach ($conditions as $condition) {
        if (empty($condition['field']) || empty($condition['operator'])) continue;
        
        $field = $condition['field'];
        $operator = $condition['operator'];
        $value = $condition['value'];
        $logicalOp = $condition['logical'] ?? 'AND';
        
        $conditionSql = '';
        
        switch ($operator) {
            case 'equals':
                if ($field === 'TimeStamp') {
                    $conditionSql = "h.TimeStamp = ?";
                } else {
                    $conditionSql = "(n.name = ? AND h.Value = ?)";
                    $params[] = $field;
                }
                $params[] = $value;
                break;
                
            case 'not_equals':
                if ($field === 'TimeStamp') {
                    $conditionSql = "h.TimeStamp != ?";
                } else {
                    $conditionSql = "(n.name = ? AND h.Value != ?)";
                    $params[] = $field;
                }
                $params[] = $value;
                break;
                
            case 'greater_than':
                if ($field === 'TimeStamp') {
                    $conditionSql = "h.TimeStamp > ?";
                } else {
                    $conditionSql = "(n.name = ? AND h.Value > ?)";
                    $params[] = $field;
                }
                $params[] = $value;
                break;
                
            case 'less_than':
                if ($field === 'TimeStamp') {
                    $conditionSql = "h.TimeStamp < ?";
                } else {
                    $conditionSql = "(n.name = ? AND h.Value < ?)";
                    $params[] = $field;
                }
                $params[] = $value;
                break;
                
            case 'between':
                $values = explode(',', $value);
                if (count($values) === 2) {
                    if ($field === 'TimeStamp') {
                        $conditionSql = "h.TimeStamp BETWEEN ? AND ?";
                    } else {
                        $conditionSql = "(n.name = ? AND h.Value BETWEEN ? AND ?)";
                        $params[] = $field;
                    }
                    $params[] = trim($values[0]);
                    $params[] = trim($values[1]);
                }
                break;
                
            case 'in':
                $values = explode(',', $value);
                $valuePlaceholders = str_repeat('?,', count($values) - 1) . '?';
                if ($field === 'TimeStamp') {
                    $conditionSql = "h.TimeStamp IN ($valuePlaceholders)";
                } else {
                    $conditionSql = "(n.name = ? AND h.Value IN ($valuePlaceholders))";
                    $params[] = $field;
                }
                foreach ($values as $val) {
                    $params[] = trim($val);
                }
                break;
        }
        
        if ($conditionSql) {
            if (!empty($whereConditions) && count($whereConditions) > 1) {
                $whereConditions[] = $logicalOp . ' ' . $conditionSql;
            } else {
                $whereConditions[] = $conditionSql;
            }
        }
    }
    
    if (!empty($whereConditions)) {
        $sql .= " WHERE " . implode(' ', $whereConditions);
    }
    
    // Add GROUP BY
    if (!empty($groupBy)) {
        $sql .= " GROUP BY " . implode(', ', $groupBy);
    }
    
    // Add ORDER BY
    if (!empty($orderBy)) {
        $orderClauses = [];
        foreach ($orderBy as $order) {
            $orderClauses[] = $order['field'] . ' ' . ($order['direction'] ?? 'ASC');
        }
        $sql .= " ORDER BY " . implode(', ', $orderClauses);
    } else {
        $sql .= " ORDER BY h.TimeStamp DESC";
    }
    
    return ['query' => $sql, 'params' => $params];
}
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>LASUCA Controls - Advanced Query Builder</title>
    <script src="https://cdn.jsdelivr.net/npm/chart.js@4.4.0/dist/chart.umd.js"></script>
    <script src="https://cdn.jsdelivr.net/npm/sortablejs@1.15.0/Sortable.min.js"></script>
    
    <style>
        * {
            margin: 0;
            padding: 0;
            box-sizing: border-box;
        }

        body {
            font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: #fff;
            min-height: 100vh;
        }

        .dashboard-header {
            background: rgba(0, 0, 0, 0.3);
            backdrop-filter: blur(10px);
            padding: 20px;
            text-align: center;
            box-shadow: 0 4px 30px rgba(0, 0, 0, 0.3);
        }

        .dashboard-header h1 {
            color: #fff;
            font-size: 2.5rem;
            margin: 0;
            text-shadow: 2px 2px 4px rgba(0,0,0,0.5);
        }

        .dashboard-header .subtitle {
            color: #ffd700;
            font-size: 1.2rem;
            margin-top: 10px;
            font-weight: 400;
        }

        .container {
            max-width: 1800px;
            margin: 0 auto;
            padding: 20px;
        }

        .query-builder-layout {
            display: grid;
            grid-template-columns: 350px 1fr;
            gap: 20px;
            height: calc(100vh - 120px);
        }

        /* Left Sidebar */
        .sidebar {
            background: rgba(255, 255, 255, 0.1);
            backdrop-filter: blur(15px);
            border-radius: 20px;
            padding: 20px;
            border: 1px solid rgba(255, 255, 255, 0.2);
            overflow-y: auto;
        }

        .sidebar-section {
            margin-bottom: 25px;
        }

        .sidebar-section h3 {
            color: #ffd700;
            font-size: 1.1rem;
            margin-bottom: 15px;
            display: flex;
            align-items: center;
            gap: 8px;
        }

        /* Field Library */
        .field-library {
            display: grid;
            gap: 8px;
        }

        .field-item {
            background: rgba(255, 255, 255, 0.15);
            padding: 12px;
            border-radius: 10px;
            cursor: grab;
            transition: all 0.3s ease;
            border: 1px solid rgba(255, 255, 255, 0.1);
            user-select: none;
        }

        .field-item:hover {
            background: rgba(255, 255, 255, 0.25);
            transform: translateY(-2px);
            box-shadow: 0 6px 20px rgba(0, 0, 0, 0.2);
        }

        .field-item:active {
            cursor: grabbing;
        }

        .field-name {
            font-weight: 600;
            color: #fff;
            font-size: 0.9rem;
        }

        .field-info {
            font-size: 0.75rem;
            color: rgba(255, 255, 255, 0.7);
            margin-top: 4px;
        }

        /* Saved Queries */
        .saved-queries {
            max-height: 200px;
            overflow-y: auto;
        }

        .saved-query-item {
            background: rgba(72, 187, 120, 0.2);
            padding: 10px;
            border-radius: 8px;
            margin-bottom: 8px;
            cursor: pointer;
            transition: all 0.3s ease;
            border: 1px solid rgba(72, 187, 120, 0.3);
        }

        .saved-query-item:hover {
            background: rgba(72, 187, 120, 0.3);
        }

        .query-name {
            font-weight: 600;
            font-size: 0.9rem;
        }

        .query-date {
            font-size: 0.75rem;
            color: rgba(255, 255, 255, 0.7);
        }

        /* Main Query Builder */
        .main-content {
            display: grid;
            grid-template-rows: auto 1fr auto;
            gap: 20px;
        }

        /* Query Configuration Panel */
        .query-config {
            background: rgba(255, 255, 255, 0.1);
            backdrop-filter: blur(15px);
            border-radius: 20px;
            padding: 20px;
            border: 1px solid rgba(255, 255, 255, 0.2);
        }

        .config-tabs {
            display: flex;
            gap: 10px;
            margin-bottom: 20px;
        }

        .tab-button {
            background: rgba(255, 255, 255, 0.1);
            color: #fff;
            border: none;
            padding: 10px 20px;
            border-radius: 10px;
            cursor: pointer;
            transition: all 0.3s ease;
            font-weight: 500;
        }

        .tab-button.active {
            background: rgba(255, 255, 255, 0.3);
            box-shadow: 0 4px 15px rgba(0, 0, 0, 0.1);
        }

        .tab-content {
            display: none;
        }

        .tab-content.active {
            display: block;
        }

        /* Drag & Drop Areas */
        .drop-zone {
            min-height: 120px;
            border: 2px dashed rgba(255, 255, 255, 0.3);
            border-radius: 15px;
            padding: 20px;
            margin-bottom: 15px;
            transition: all 0.3s ease;
            background: rgba(255, 255, 255, 0.05);
        }

        .drop-zone.drag-over {
            border-color: #ffd700;
            background: rgba(255, 215, 0, 0.1);
            transform: scale(1.02);
        }

        .drop-zone h4 {
            color: #ffd700;
            margin-bottom: 10px;
            font-size: 1rem;
        }

        .drop-zone-hint {
            color: rgba(255, 255, 255, 0.6);
            font-style: italic;
            text-align: center;
            padding: 20px;
        }

        /* Condition Builder */
        .condition-item {
            background: rgba(255, 255, 255, 0.15);
            border-radius: 10px;
            padding: 15px;
            margin-bottom: 10px;
            display: grid;
            grid-template-columns: 100px 1fr 120px 1fr 80px;
            gap: 10px;
            align-items: center;
            border: 1px solid rgba(255, 255, 255, 0.1);
        }

        .condition-item select,
        .condition-item input {
            background: rgba(255, 255, 255, 0.1);
            color: #fff;
            border: 1px solid rgba(255, 255, 255, 0.2);
            border-radius: 6px;
            padding: 8px;
            font-size: 0.9rem;
        }

        .condition-item select:focus,
        .condition-item input:focus {
            outline: none;
            border-color: #ffd700;
            box-shadow: 0 0 0 2px rgba(255, 215, 0, 0.2);
        }

        .remove-condition {
            background: rgba(239, 68, 68, 0.7);
            color: white;
            border: none;
            border-radius: 6px;
            padding: 8px;
            cursor: pointer;
            transition: all 0.3s ease;
        }

        .remove-condition:hover {
            background: rgba(239, 68, 68, 0.9);
        }

        /* Results Panel */
        .results-panel {
            background: rgba(255, 255, 255, 0.1);
            backdrop-filter: blur(15px);
            border-radius: 20px;
            padding: 20px;
            border: 1px solid rgba(255, 255, 255, 0.2);
            overflow: hidden;
        }

        .results-header {
            display: flex;
            justify-content: space-between;
            align-items: center;
            margin-bottom: 20px;
        }

        .results-title {
            color: #ffd700;
            font-size: 1.3rem;
            font-weight: 600;
        }

        .results-actions {
            display: flex;
            gap: 10px;
        }

        /* Data Table */
        .data-table-container {
            height: 400px;
            overflow: auto;
            border-radius: 10px;
            background: rgba(255, 255, 255, 0.05);
        }

        .data-table {
            width: 100%;
            border-collapse: collapse;
        }

        .data-table th {
            background: rgba(255, 255, 255, 0.2);
            color: #fff;
            padding: 12px 15px;
            text-align: left;
            font-weight: 600;
            position: sticky;
            top: 0;
            z-index: 10;
        }

        .data-table td {
            padding: 10px 15px;
            border-bottom: 1px solid rgba(255, 255, 255, 0.1);
            color: rgba(255, 255, 255, 0.9);
        }

        .data-table tr:hover {
            background: rgba(255, 255, 255, 0.1);
        }

        /* Control Buttons */
        .control-panel {
            display: flex;
            gap: 15px;
            justify-content: center;
            margin-bottom: 20px;
        }

        button {
            background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
            color: white;
            border: none;
            padding: 12px 24px;
            border-radius: 10px;
            font-size: 0.9rem;
            font-weight: 600;
            cursor: pointer;
            transition: all 0.3s ease;
            box-shadow: 0 4px 15px rgba(0, 0, 0, 0.2);
        }

        button:hover:not(:disabled) {
            transform: translateY(-2px);
            box-shadow: 0 8px 25px rgba(0, 0, 0, 0.3);
        }

        button:disabled {
            background: rgba(255, 255, 255, 0.2);
            cursor: not-allowed;
            transform: none;
        }

        button.execute-btn {
            background: linear-gradient(135deg, #48bb78 0%, #38a169 100%);
        }

        button.save-btn {
            background: linear-gradient(135deg, #ed8936 0%, #dd6b20 100%);
        }

        button.export-btn {
            background: linear-gradient(135deg, #4299e1 0%, #3182ce 100%);
        }

        button.clear-btn {
            background: linear-gradient(135deg, #f56565 0%, #e53e3e 100%);
        }

        /* Loading and Status */
        .loading-overlay {
            position: fixed;
            top: 0;
            left: 0;
            width: 100%;
            height: 100%;
            background: rgba(0, 0, 0, 0.8);
            backdrop-filter: blur(5px);
            display: none;
            justify-content: center;
            align-items: center;
            z-index: 9999;
        }

        .loading-spinner {
            width: 60px;
            height: 60px;
            border: 4px solid rgba(255, 255, 255, 0.3);
            border-top: 4px solid #ffd700;
            border-radius: 50%;
            animation: spin 1s linear infinite;
        }

        @keyframes spin {
            0% { transform: rotate(0deg); }
            100% { transform: rotate(360deg); }
        }

        .status-message {
            padding: 15px 20px;
            margin-bottom: 20px;
            border-radius: 10px;
            font-weight: 500;
            text-align: center;
        }

        .status-message.success {
            background: rgba(72, 187, 120, 0.2);
            border: 1px solid rgba(72, 187, 120, 0.5);
            color: #48bb78;
        }

        .status-message.error {
            background: rgba(245, 101, 101, 0.2);
            border: 1px solid rgba(245, 101, 101, 0.5);
            color: #f56565;
        }

        .status-message.info {
            background: rgba(66, 153, 225, 0.2);
            border: 1px solid rgba(66, 153, 225, 0.5);
            color: #4299e1;
        }

        /* Responsive Design */
        @media (max-width: 1200px) {
            .query-builder-layout {
                grid-template-columns: 1fr;
                grid-template-rows: auto 1fr;
            }
            
            .sidebar {
                max-height: 300px;
            }
        }

        @media (max-width: 768px) {
            .condition-item {
                grid-template-columns: 1fr;
                gap: 8px;
            }
            
            .results-header {
                flex-direction: column;
                gap: 15px;
                text-align: center;
            }
        }

        /* Custom Scrollbars */
        ::-webkit-scrollbar {
            width: 8px;
            height: 8px;
        }

        ::-webkit-scrollbar-track {
            background: rgba(255, 255, 255, 0.1);
            border-radius: 4px;
        }

        ::-webkit-scrollbar-thumb {
            background: rgba(255, 255, 255, 0.3);
            border-radius: 4px;
        }

        ::-webkit-scrollbar-thumb:hover {
            background: rgba(255, 255, 255, 0.5);
        }
    </style>
</head>

<body>
    <!-- Loading Overlay -->
    <div class="loading-overlay" id="loadingOverlay">
        <div class="loading-spinner"></div>
    </div>

    <!-- Header -->
    <div class="dashboard-header">
        <h1>🎯 LASUCA Controls - Advanced Query Builder</h1>
        <p class="subtitle">Visual Data Filtering & Analysis Tool</p>
    </div>

    <div class="container">
        <!-- Status Messages -->
        <div id="statusMessages"></div>

        <div class="query-builder-layout">
            <!-- Left Sidebar -->
            <div class="sidebar">
                <!-- Field Library -->
                <div class="sidebar-section">
                    <h3>📁 Available Fields</h3>
                    <div class="field-library" id="fieldLibrary">
                        <div style="text-align: center; padding: 20px; color: rgba(255,255,255,0.6);">
                            Loading schema...
                        </div>
                    </div>
                </div>

                <!-- Quick Filters -->
                <div class="sidebar-section">
                    <h3>⚡ Quick Filters</h3>
                    <div class="control-panel" style="flex-direction: column; gap: 8px;">
                        <button onclick="addTimeRangeFilter('last_hour')">Last Hour</button>
                        <button onclick="addTimeRangeFilter('last_24h')">Last 24 Hours</button>
                        <button onclick="addTimeRangeFilter('last_week')">Last Week</button>
                        <button onclick="addValueRangeFilter()">Value Range</button>
                    </div>
                </div>

                <!-- Saved Queries -->
                <div class="sidebar-section">
                    <h3>💾 Saved Queries</h3>
                    <div class="saved-queries" id="savedQueries">
                        <div style="text-align: center; padding: 15px; color: rgba(255,255,255,0.6);">
                            No saved queries
                        </div>
                    </div>
                </div>
            </div>

            <!-- Main Content -->
            <div class="main-content">
                <!-- Query Configuration -->
                <div class="query-config">
                    <div class="config-tabs">
                        <button class="tab-button active" onclick="switchTab('fields')">📊 Fields</button>
                        <button class="tab-button" onclick="switchTab('conditions')">🔍 Conditions</button>
                        <button class="tab-button" onclick="switchTab('grouping')">📈 Grouping</button>
                        <button class="tab-button" onclick="switchTab('sorting')">🔄 Sorting</button>
                    </div>

                    <!-- Fields Tab -->
                    <div class="tab-content active" id="fieldsTab">
                        <div class="drop-zone" id="selectedFieldsZone" ondrop="dropField(event)" ondragover="allowDrop(event)">
                            <h4>Selected Fields</h4>
                            <div class="drop-zone-hint">Drag fields here to include in query results</div>
                        </div>
                    </div>

                    <!-- Conditions Tab -->
                    <div class="tab-content" id="conditionsTab">
                        <div class="drop-zone" id="conditionsZone">
                            <h4>Filter Conditions</h4>
                            <div id="conditionsList">
                                <div class="drop-zone-hint">Add conditions to filter your data</div>
                            </div>
                            <button onclick="addCondition()" style="margin-top: 10px;">+ Add Condition</button>
                        </div>
                    </div>

                    <!-- Grouping Tab -->
                    <div class="tab-content" id="groupingTab">
                        <div class="drop-zone" id="groupingZone" ondrop="dropGroupField(event)" ondragover="allowDrop(event)">
                            <h4>Group By Fields</h4>
                            <div class="drop-zone-hint">Drag fields here to group results</div>
                        </div>
                        <div class="drop-zone" id="aggregationZone" style="margin-top: 15px;">
                            <h4>Aggregations</h4>
                            <div id="aggregationsList">
                                <div class="drop-zone-hint">Define aggregation functions</div>
                            </div>
                            <button onclick="addAggregation()" style="margin-top: 10px;">+ Add Aggregation</button>
                        </div>
                    </div>

                    <!-- Sorting Tab -->
                    <div class="tab-content" id="sortingTab">
                        <div class="drop-zone" id="sortingZone" ondrop="dropSortField(event)" ondragover="allowDrop(event)">
                            <h4>Sort Fields</h4>
                            <div class="drop-zone-hint">Drag fields here to define sort order</div>
                        </div>
                    </div>
                </div>

                <!-- Control Panel -->
                <div class="control-panel">
                    <button onclick="executeQuery()" class="execute-btn" id="executeBtn">
                        ▶️ Execute Query
                    </button>
                    <button onclick="saveQuery()" class="save-btn">
                        💾 Save Query
                    </button>
                    <button onclick="exportResults('csv')" class="export-btn" id="exportBtn" disabled>
                        📄 Export CSV
                    </button>
                    <button onclick="clearQuery()" class="clear-btn">
                        🗑️ Clear All
                    </button>
                </div>

                <!-- Results Panel -->
                <div class="results-panel">
                    <div class="results-header">
                        <div class="results-title">Query Results</div>
                        <div class="results-actions">
                            <span id="rowCount" style="color: rgba(255,255,255,0.8);">No results</span>
                        </div>
                    </div>
                    <div class="data-table-container" id="dataTableContainer">
                        <div style="text-align: center; padding: 50px; color: rgba(255,255,255,0.6);">
                            Execute a query to see results here
                        </div>
                    </div>
                </div>
            </div>
        </div>
    </div>

    <script>
        // Global variables
        let schema = [];
        let selectedFields = [];
        let conditions = [];
        let groupByFields = [];
        let aggregations = [];
        let sortFields = [];
        let queryResults = [];

        // Initialize the application
        document.addEventListener('DOMContentLoaded', function() {
            loadSchema();
            loadSavedQueries();
            setupDragAndDrop();
        });

        // Load database schema
        async function loadSchema() {
            try {
                showLoading(true);
                
                const formData = new FormData();
                formData.append('action', 'get_schema');
                
                const response = await fetch(window.location.href, {
                    method: 'POST',
                    body: formData
                });
                
                const data = await response.json();
                
                if (data.success) {
                    schema = data.schema;
                    renderFieldLibrary();
                    showStatus(`${schema.length} fields loaded from ${data.total_tags} available tags`, 'success');
                } else {
                    throw new Error(data.error || 'Failed to load schema');
                }
                
            } catch (error) {
                console.error('Error loading schema:', error);
                showStatus('Failed to load database schema: ' + error.message, 'error');
            } finally {
                showLoading(false);
            }
        }

        // Render field library
        function renderFieldLibrary() {
            const library = document.getElementById('fieldLibrary');
            
            if (schema.length === 0) {
                library.innerHTML = '<div style="text-align: center; padding: 20px; color: rgba(255,255,255,0.6);">No fields available</div>';
                return;
            }
            
            let html = '';
            
            // Add special timestamp field
            html += `
                <div class="field-item" draggable="true" data-field="TimeStamp" data-type="datetime">
                    <div class="field-name">📅 TimeStamp</div>
                    <div class="field-info">DateTime field</div>
                </div>
            `;
            
            // Add schema fields
            schema.forEach((field, index) => {
                html += `
                    <div class="field-item" draggable="true" data-field="${field.name}" data-type="${field.type}">
                        <div class="field-name">📊 ${field.name}</div>
                        <div class="field-info">
                            ${field.type} • ${field.record_count} records<br>
                            Range: ${field.min_value?.toFixed(2)} - ${field.max_value?.toFixed(2)}
                        </div>
                    </div>
                `;
            });
            
            library.innerHTML = html;
        }

        // Setup drag and drop functionality
        function setupDragAndDrop() {
            // Add event listeners for drag start
            document.addEventListener('dragstart', function(e) {
                if (e.target.classList.contains('field-item')) {
                    e.dataTransfer.setData('text/plain', JSON.stringify({
                        field: e.target.dataset.field,
                        type: e.target.dataset.type
                    }));
                }
            });
            
            // Make selected fields sortable
            new Sortable(document.getElementById('selectedFieldsZone'), {
                group: 'fields',
                animation: 150,
                onAdd: function(evt) {
                    const fieldData = JSON.parse(evt.item.dataset.fieldData || '{}');
                    if (fieldData.field && !selectedFields.includes(fieldData.field)) {
                        selectedFields.push(fieldData.field);
                        updateSelectedFieldsDisplay();
                    }
                    evt.item.remove();
                }
            });
        }

        // Allow drop
        function allowDrop(ev) {
            ev.preventDefault();
            ev.currentTarget.classList.add('drag-over');
        }

        // Handle field drop
        function dropField(ev) {
            ev.preventDefault();
            ev.currentTarget.classList.remove('drag-over');
            
            try {
                const fieldData = JSON.parse(ev.dataTransfer.getData('text/plain'));
                if (fieldData.field && !selectedFields.includes(fieldData.field)) {
                    selectedFields.push(fieldData.field);
                    updateSelectedFieldsDisplay();
                }
            } catch (error) {
                console.error('Error parsing dropped field data:', error);
            }
        }

        // Update selected fields display
        function updateSelectedFieldsDisplay() {
            const zone = document.getElementById('selectedFieldsZone');
            
            if (selectedFields.length === 0) {
                zone.innerHTML = `
                    <h4>Selected Fields</h4>
                    <div class="drop-zone-hint">Drag fields here to include in query results</div>
                `;
                return;
            }
            
            let html = '<h4>Selected Fields</h4>';
            selectedFields.forEach((field, index) => {
                html += `
                    <div class="condition-item" style="grid-template-columns: 1fr auto;">
                        <span>📊 ${field}</span>
                        <button class="remove-condition" onclick="removeSelectedField(${index})">✕</button>
                    </div>
                `;
            });
            
            zone.innerHTML = html;
        }

        // Remove selected field
        function removeSelectedField(index) {
            selectedFields.splice(index, 1);
            updateSelectedFieldsDisplay();
        }

        // Tab switching
        function switchTab(tabName) {
            // Update tab buttons
            document.querySelectorAll('.tab-button').forEach(btn => btn.classList.remove('active'));
            event.target.classList.add('active');
            
            // Update tab content
            document.querySelectorAll('.tab-content').forEach(content => content.classList.remove('active'));
            document.getElementById(tabName + 'Tab').classList.add('active');
        }

        // Add condition
        function addCondition() {
            const conditionId = conditions.length;
            const conditionHtml = `
                <div class="condition-item" id="condition-${conditionId}">
                    <select onchange="updateCondition(${conditionId}, 'logical', this.value)">
                        <option value="AND">AND</option>
                        <option value="OR">OR</option>
                    </select>
                    <select onchange="updateCondition(${conditionId}, 'field', this.value)">
                        <option value="">Select Field</option>
                        <option value="TimeStamp">TimeStamp</option>
                        ${schema.map(field => `<option value="${field.name}">${field.name}</option>`).join('')}
                    </select>
                    <select onchange="updateCondition(${conditionId}, 'operator', this.value)">
                        <option value="equals">Equals</option>
                        <option value="not_equals">Not Equals</option>
                        <option value="greater_than">Greater Than</option>
                        <option value="less_than">Less Than</option>
                        <option value="between">Between</option>
                        <option value="in">In List</option>
                    </select>
                    <input type="text" placeholder="Value" onchange="updateCondition(${conditionId}, 'value', this.value)">
                    <button class="remove-condition" onclick="removeCondition(${conditionId})">✕</button>
                </div>
            `;
            
            const conditionsContainer = document.getElementById('conditionsList');
            if (conditionsContainer.querySelector('.drop-zone-hint')) {
                conditionsContainer.innerHTML = '';
            }
            conditionsContainer.insertAdjacentHTML('beforeend', conditionHtml);
            
            conditions.push({
                id: conditionId,
                logical: 'AND',
                field: '',
                operator: 'equals',
                value: ''
            });
        }

        // Update condition
        function updateCondition(id, property, value) {
            const condition = conditions.find(c => c.id === id);
            if (condition) {
                condition[property] = value;
            }
        }

        // Remove condition
        function removeCondition(id) {
            document.getElementById(`condition-${id}`).remove();
            conditions = conditions.filter(c => c.id !== id);
            
            if (conditions.length === 0) {
                document.getElementById('conditionsList').innerHTML = 
                    '<div class="drop-zone-hint">Add conditions to filter your data</div>';
            }
        }

        // Quick filter functions
        function addTimeRangeFilter(range) {
            let value = '';
            switch (range) {
                case 'last_hour':
                    value = new Date(Date.now() - 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');
                    break;
                case 'last_24h':
                    value = new Date(Date.now() - 24 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');
                    break;
                case 'last_week':
                    value = new Date(Date.now() - 7 * 24 * 60 * 60 * 1000).toISOString().slice(0, 19).replace('T', ' ');
                    break;
            }
            
            addCondition();
            const latestCondition = conditions[conditions.length - 1];
            latestCondition.field = 'TimeStamp';
            latestCondition.operator = 'greater_than';
            latestCondition.value = value;
            
            // Update the UI
            const conditionElement = document.getElementById(`condition-${latestCondition.id}`);
            conditionElement.querySelector('select:nth-child(2)').value = 'TimeStamp';
            conditionElement.querySelector('select:nth-child(3)').value = 'greater_than';
            conditionElement.querySelector('input').value = value;
            
            showStatus(`Added ${range.replace('_', ' ')} filter`, 'info');
        }

        // Execute query
        async function executeQuery() {
            try {
                if (selectedFields.length === 0) {
                    showStatus('Please select at least one field', 'error');
                    return;
                }
                
                showLoading(true);
                document.getElementById('executeBtn').disabled = true;
                
                const queryConfig = {
                    selected_tags: selectedFields,
                    conditions: conditions.filter(c => c.field && c.operator && c.value),
                    group_by: groupByFields,
                    aggregations: aggregations,
                    order_by: sortFields,
                    limit: 1000
                };
                
                const formData = new FormData();
                formData.append('action', 'execute_query');
                formData.append('query_config', JSON.stringify(queryConfig));
                
                const response = await fetch(window.location.href, {
                    method: 'POST',
                    body: formData
                });
                
                const result = await response.json();
                
                if (result.success) {
                    queryResults = result.data;
                    displayResults(result.data);
                    document.getElementById('exportBtn').disabled = false;
                    showStatus(`Query executed successfully! Retrieved ${result.row_count} rows in ${(result.execution_time * 1000).toFixed(2)}ms`, 'success');
                } else {
                    throw new Error(result.error || 'Query execution failed');
                }
                
            } catch (error) {
                console.error('Error executing query:', error);
                showStatus('Failed to execute query: ' + error.message, 'error');
            } finally {
                showLoading(false);
                document.getElementById('executeBtn').disabled = false;
            }
        }

        // Display query results
        function displayResults(data) {
            const container = document.getElementById('dataTableContainer');
            const rowCount = document.getElementById('rowCount');
            
            rowCount.textContent = `${data.length} rows`;
            
            if (data.length === 0) {
                container.innerHTML = '<div style="text-align: center; padding: 50px; color: rgba(255,255,255,0.6);">No data found</div>';
                return;
            }
            
            // Get column names
            const columns = Object.keys(data[0]);
            
            let html = '<table class="data-table"><thead><tr>';
            columns.forEach(column => {
                html += `<th>${column}</th>`;
            });
            html += '</tr></thead><tbody>';
            
            data.forEach(row => {
                html += '<tr>';
                columns.forEach(column => {
                    let value = row[column];
                    if (value === null || value === undefined) {
                        value = '<span style="color: rgba(255,255,255,0.5);">NULL</span>';
                    } else if (typeof value === 'number') {
                        value = value.toFixed(2);
                    }
                    html += `<td>${value}</td>`;
                });
                html += '</tr>';
            });
            
            html += '</tbody></table>';
            container.innerHTML = html;
        }

        // Save query
        async function saveQuery() {
            const queryName = prompt('Enter a name for this query:');
            if (!queryName) return;
            
            try {
                const queryConfig = {
                    selected_tags: selectedFields,
                    conditions: conditions.filter(c => c.field && c.operator && c.value),
                    group_by: groupByFields,
                    aggregations: aggregations,
                    order_by: sortFields
                };
                
                const formData = new FormData();
                formData.append('action', 'save_query');
                formData.append('query_name', queryName);
                formData.append('query_config', JSON.stringify(queryConfig));
                
                const response = await fetch(window.location.href, {
                    method: 'POST',
                    body: formData
                });
                
                const result = await response.json();
                
                if (result.success) {
                    showStatus('Query saved successfully!', 'success');
                    loadSavedQueries();
                } else {
                    throw new Error(result.error || 'Failed to save query');
                }
                
            } catch (error) {
                console.error('Error saving query:', error);
                showStatus('Failed to save query: ' + error.message, 'error');
            }
        }

        // Load saved queries
        async function loadSavedQueries() {
            try {
                const formData = new FormData();
                formData.append('action', 'load_saved_queries');
                
                const response = await fetch(window.location.href, {
                    method: 'POST',
                    body: formData
                });
                
                const result = await response.json();
                
                if (result.success) {
                    displaySavedQueries(result.saved_queries);
                }
                
            } catch (error) {
                console.error('Error loading saved queries:', error);
            }
        }

        // Display saved queries
        function displaySavedQueries(queries) {
            const container = document.getElementById('savedQueries');
            
            if (queries.length === 0) {
                container.innerHTML = '<div style="text-align: center; padding: 15px; color: rgba(255,255,255,0.6);">No saved queries</div>';
                return;
            }
            
            let html = '';
            queries.forEach(query => {
                html += `
                    <div class="saved-query-item" onclick="loadQuery('${query.id}')">
                        <div class="query-name">${query.query_name}</div>
                        <div class="query-date">${new Date(query.created_at).toLocaleDateString()}</div>
                    </div>
                `;
            });
            
            container.innerHTML = html;
        }

        // Export results
        function exportResults(format) {
            if (queryResults.length === 0) {
                showStatus('No data to export', 'error');
                return;
            }
            
            const timestamp = new Date().toISOString().slice(0, 19).replace(/:/g, '-');
            
            if (format === 'csv') {
                const columns = Object.keys(queryResults[0]);
                let csv = columns.join(',') + '\n';
                
                queryResults.forEach(row => {
                    const values = columns.map(col => {
                        let value = row[col];
                        if (value === null || value === undefined) return '';
                        if (typeof value === 'string' && value.includes(',')) {
                            return `"${value}"`;
                        }
                        return value;
                    });
                    csv += values.join(',') + '\n';
                });
                
                downloadFile(csv, `query_results_${timestamp}.csv`, 'text/csv');
                showStatus('CSV export completed', 'success');
            }
        }

        // Clear query
        function clearQuery() {
            selectedFields = [];
            conditions = [];
            groupByFields = [];
            aggregations = [];
            sortFields = [];
            queryResults = [];
            
            updateSelectedFieldsDisplay();
            document.getElementById('conditionsList').innerHTML = 
                '<div class="drop-zone-hint">Add conditions to filter your data</div>';
            document.getElementById('dataTableContainer').innerHTML = 
                '<div style="text-align: center; padding: 50px; color: rgba(255,255,255,0.6);">Execute a query to see results here</div>';
            document.getElementById('rowCount').textContent = 'No results';
            document.getElementById('exportBtn').disabled = true;
            
            showStatus('Query cleared', 'info');
        }

        // Utility functions
        function downloadFile(content, filename, mimeType) {
            const blob = new Blob([content], { type: mimeType });
            const link = document.createElement('a');
            link.href = window.URL.createObjectURL(blob);
            link.download = filename;
            document.body.appendChild(link);
            link.click();
            document.body.removeChild(link);
            window.URL.revokeObjectURL(link.href);
        }

        function showLoading(show) {
            document.getElementById('loadingOverlay').style.display = show ? 'flex' : 'none';
        }

        function showStatus(message, type) {
            const container = document.getElementById('statusMessages');
            const div = document.createElement('div');
            div.className = `status-message ${type}`;
            div.textContent = message;
            
            container.innerHTML = '';
            container.appendChild(div);
            
            if (type === 'success' || type === 'info') {
                setTimeout(() => {
                    if (div.parentNode === container) {
                        container.removeChild(div);
                    }
                }, 5000);
            }
        }

        // Remove drag over effect when leaving drop zone
        document.addEventListener('dragleave', function(e) {
            if (e.target.classList.contains('drop-zone')) {
                e.target.classList.remove('drag-over');
            }
        });
    </script>
</body>
</html>