← back to Designer Wallcoverings

DW-Agents/dw-agents/agent-wine-analytics/index.ts.backup

453 lines

/**
 * DW-Agents: Wine Data Analytics Agent with Full-Text Search
 * Port: 9877
 */

import express from 'express';
import cookieParser from 'cookie-parser';
import path from 'path';
import fs from 'fs';
import { createReadStream } from 'fs';
import csvParser from 'csv-parser';

const app = express();
const PORT = 9877;

app.use(cookieParser());
app.use(express.json());

const dataDir = '/root/WebsitesMisc/WineFinder/data/wine-history';

// In-memory search index
let searchIndex: Array<{
  source: string;
  country: string;
  year?: string;
  data: any;
  searchText: string;
}> = [];

let indexedCount = 0;

// Load CSV files into search index
async function loadCSVFile(filePath: string, country: string, source: string): Promise<number> {
  return new Promise((resolve) => {
    let count = 0;
    createReadStream(filePath)
      .pipe(csvParser())
      .on('data', (row) => {
        const searchText = Object.values(row).join(' ').toLowerCase();
        searchIndex.push({
          source,
          country,
          year: row.year || row.Year || row.YEAR || '',
          data: row,
          searchText
        });
        count++;
      })
      .on('end', () => {
        console.log(`Indexed ${count} records from ${source}`);
        resolve(count);
      })
      .on('error', () => resolve(0));
  });
}

// Load JSON files into search index
function loadJSONFile(filePath: string, country: string, source: string): number {
  try {
    const content = fs.readFileSync(filePath, 'utf-8');
    const data = JSON.parse(content);

    if (Array.isArray(data)) {
      data.forEach(item => {
        const searchText = JSON.stringify(item).toLowerCase();
        searchIndex.push({
          source,
          country,
          year: item.year || item.Year || item.YEAR || '',
          data: item,
          searchText
        });
      });
      console.log(`Indexed ${data.length} records from ${source}`);
      return data.length;
    }
    return 0;
  } catch (error) {
    return 0;
  }
}

// Initialize search index
async function initializeSearchIndex() {
  console.log('Building search index...');
  searchIndex = [];
  indexedCount = 0;

  const ttbYearlyPath = path.join(dataDir, 'global-sources', 'usa', 'ttb_wine_yearly.csv');
  if (fs.existsSync(ttbYearlyPath)) {
    indexedCount += await loadCSVFile(ttbYearlyPath, 'USA', 'TTB Yearly');
  }

  const ttbMonthlyPath = path.join(dataDir, 'global-sources', 'usa', 'ttb_wine_monthly.csv');
  if (fs.existsSync(ttbMonthlyPath)) {
    indexedCount += await loadCSVFile(ttbMonthlyPath, 'USA', 'TTB Monthly');
  }

  const ttbJSONPath = path.join(dataDir, 'global-sources', 'usa', 'ttb_wine_data.json');
  if (fs.existsSync(ttbJSONPath)) {
    indexedCount += loadJSONFile(ttbJSONPath, 'USA', 'TTB JSON');
  }

  const franceCSVPath = path.join(dataDir, 'global-sources', 'france', 'france_2ed445cb.csv');
  if (fs.existsSync(franceCSVPath)) {
    indexedCount += await loadCSVFile(franceCSVPath, 'France', 'data.gouv.fr');
  }

  const franceJSONPath = path.join(dataDir, 'global-sources', 'france', 'france_57e8566f.json');
  if (fs.existsSync(franceJSONPath)) {
    indexedCount += loadJSONFile(franceJSONPath, 'France', 'data.gouv.fr JSON');
  }

  console.log(`Search index built: ${indexedCount} total records`);
}

// Search function with filters
function searchWineData(query: string, filters?: {
  country?: string;
  source?: string;
  yearFrom?: number;
  yearTo?: number;
}): Array<any> {
  const searchTerms = query.toLowerCase().split(' ').filter(t => t.length > 0);

  let results = searchIndex.filter(item => {
    if (searchTerms.length > 0) {
      const matchesSearch = searchTerms.every(term => item.searchText.includes(term));
      if (!matchesSearch) return false;
    }

    if (filters?.country && item.country !== filters.country) return false;
    if (filters?.source && item.source !== filters.source) return false;

    if (filters?.yearFrom || filters?.yearTo) {
      const year = parseInt(item.year || '0');
      if (filters.yearFrom && year < filters.yearFrom) return false;
      if (filters.yearTo && year > filters.yearTo) return false;
    }

    return true;
  });

  return results.slice(0, 100);
}

// Scan downloaded files
function scanDownloadedFiles(): Array<{ name: string; size: string; country: string }> {
  const files: Array<{ name: string; size: string; country: string }> = [];
  const countries = ['france', 'spain', 'usa', 'global'];

  countries.forEach(country => {
    const countryDir = path.join(dataDir, 'global-sources', country);
    if (!fs.existsSync(countryDir)) return;

    const countryFiles = fs.readdirSync(countryDir);
    countryFiles.forEach(file => {
      const filePath = path.join(countryDir, file);
      const stat = fs.statSync(filePath);

      if (stat.isFile() && !file.endsWith('.md') && !file.endsWith('README') && !file.startsWith('.')) {
        const sizeKB = (stat.size / 1024).toFixed(2);
        const sizeMB = (stat.size / (1024 * 1024)).toFixed(2);
        const sizeStr = stat.size > 1024 * 1024 ? `${sizeMB} MB` : `${sizeKB} KB`;

        files.push({
          name: file,
          size: sizeStr,
          country: country.charAt(0).toUpperCase() + country.slice(1)
        });
      }
    });
  });

  return files;
}

// Home page with search interface
app.get('/', (req, res) => {
  const downloadedFiles = scanDownloadedFiles();

  res.send(`
<!DOCTYPE html>
<html>
<head>
  <title>Wine Data Search & Analytics</title>
  <style>
    * { margin: 0; padding: 0; box-sizing: border-box; }
    body {
      font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif;
      background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
      min-height: 100vh;
      padding: 20px;
    }
    .container { max-width: 1400px; margin: 0 auto; }
    header {
      background: white;
      border-radius: 12px;
      padding: 30px;
      margin-bottom: 20px;
      box-shadow: 0 4px 6px rgba(0,0,0,0.1);
    }
    h1 { color: #667eea; font-size: 2.5em; margin-bottom: 10px; }
    .subtitle { color: #666; font-size: 1.1em; }
    .search-section {
      background: white;
      border-radius: 12px;
      padding: 30px;
      margin-bottom: 20px;
      box-shadow: 0 4px 6px rgba(0,0,0,0.1);
    }
    .search-box {
      display: flex;
      gap: 10px;
      margin-bottom: 20px;
    }
    .search-box input {
      flex: 1;
      padding: 15px;
      border: 2px solid #e0e0e0;
      border-radius: 8px;
      font-size: 1.1em;
    }
    .search-box input:focus {
      outline: none;
      border-color: #667eea;
    }
    .search-box button {
      padding: 15px 30px;
      background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
      color: white;
      border: none;
      border-radius: 8px;
      font-size: 1.1em;
      font-weight: 600;
      cursor: pointer;
    }
    .search-box button:hover { transform: translateY(-2px); }
    .filters {
      display: grid;
      grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
      gap: 15px;
    }
    .filter-group label {
      display: block;
      margin-bottom: 5px;
      color: #666;
      font-size: 0.9em;
      font-weight: 600;
    }
    .filter-group select, .filter-group input {
      width: 100%;
      padding: 10px;
      border: 1px solid #e0e0e0;
      border-radius: 6px;
    }
    .results-section {
      background: white;
      border-radius: 12px;
      padding: 30px;
      margin-bottom: 20px;
      box-shadow: 0 4px 6px rgba(0,0,0,0.1);
      min-height: 200px;
    }
    .results-section h2 { color: #667eea; margin-bottom: 20px; }
    .results-grid { display: grid; gap: 15px; }
    .result-card {
      padding: 20px;
      border: 1px solid #e0e0e0;
      border-radius: 8px;
      transition: all 0.2s;
    }
    .result-card:hover {
      border-color: #667eea;
      box-shadow: 0 2px 8px rgba(102,126,234,0.2);
    }
    .result-header {
      display: flex;
      justify-content: space-between;
      margin-bottom: 10px;
    }
    .result-source { font-weight: 600; color: #667eea; }
    .result-country { color: #999; font-size: 0.9em; }
    .result-data {
      background: #f8f9ff;
      padding: 10px;
      border-radius: 4px;
      font-family: 'Courier New', monospace;
      font-size: 0.85em;
      overflow-x: auto;
      max-height: 200px;
      overflow-y: auto;
    }
    .stats { color: #667eea; margin: 20px 0; font-size: 1.1em; }
    .loading, .no-results { text-align: center; padding: 40px; color: #999; }
  </style>
</head>
<body>
  <div class="container">
    <header>
      <h1>Wine Data Search & Analytics</h1>
      <p class="subtitle">Search ${indexedCount.toLocaleString()} wine records from government & academic sources</p>
      <p class="stats">${downloadedFiles.length} files indexed | France, Spain, USA, Global data</p>
    </header>

    <div class="search-section">
      <h2 style="color: #667eea; margin-bottom: 20px;">Search Wine Data</h2>
      <div class="search-box">
        <input type="text" id="searchQuery" placeholder="Search wines, regions, years, producers, varieties...">
        <button onclick="searchData()">Search</button>
      </div>

      <div class="filters">
        <div class="filter-group">
          <label>Country</label>
          <select id="filterCountry">
            <option value="">All Countries</option>
            <option value="USA">USA</option>
            <option value="France">France</option>
            <option value="Spain">Spain</option>
          </select>
        </div>
        <div class="filter-group">
          <label>Source</label>
          <select id="filterSource">
            <option value="">All Sources</option>
            <option value="TTB Yearly">TTB Yearly</option>
            <option value="TTB Monthly">TTB Monthly</option>
            <option value="data.gouv.fr">data.gouv.fr</option>
          </select>
        </div>
        <div class="filter-group">
          <label>Year From</label>
          <input type="number" id="filterYearFrom" placeholder="1980" min="1980" max="2024">
        </div>
        <div class="filter-group">
          <label>Year To</label>
          <input type="number" id="filterYearTo" placeholder="2024" min="1980" max="2024">
        </div>
      </div>
    </div>

    <div class="results-section" id="resultsSection">
      <h2>Search Results</h2>
      <p class="no-results">Enter a search query above to explore the wine data</p>
    </div>
  </div>

  <script>
    async function searchData() {
      const query = document.getElementById('searchQuery').value;
      const country = document.getElementById('filterCountry').value;
      const source = document.getElementById('filterSource').value;
      const yearFrom = document.getElementById('filterYearFrom').value;
      const yearTo = document.getElementById('filterYearTo').value;

      const resultsSection = document.getElementById('resultsSection');
      resultsSection.innerHTML = '<div class="loading">Searching...</div>';

      try {
        const params = new URLSearchParams({
          q: query,
          country: country,
          source: source,
          yearFrom: yearFrom,
          yearTo: yearTo
        });

        const response = await fetch('/api/search?' + params.toString());
        const data = await response.json();

        if (data.results.length === 0) {
          resultsSection.innerHTML = '<h2>Search Results</h2><p class="no-results">No results found. Try different search terms or filters.</p>';
          return;
        }

        let html = '<h2>Search Results (' + data.results.length + ' found)</h2><div class="results-grid">';

        data.results.forEach(result => {
          html += \`
            <div class="result-card">
              <div class="result-header">
                <span class="result-source">\${result.source}</span>
                <span class="result-country">\${result.country}\${result.year ? ' - ' + result.year : ''}</span>
              </div>
              <div class="result-data">
                <pre>\${JSON.stringify(result.data, null, 2)}</pre>
              </div>
            </div>
          \`;
        });

        html += '</div>';
        resultsSection.innerHTML = html;
      } catch (error) {
        resultsSection.innerHTML = '<h2>Search Results</h2><p class="no-results">Error searching data. Please try again.</p>';
        console.error('Search error:', error);
      }
    }

    document.getElementById('searchQuery').addEventListener('keypress', function(e) {
      if (e.key === 'Enter') searchData();
    });
  </script>
</body>
</html>
  `);
});

// Search API
app.get('/api/search', (req, res) => {
  const query = (req.query.q as string) || '';
  const filters = {
    country: req.query.country as string,
    source: req.query.source as string,
    yearFrom: req.query.yearFrom ? parseInt(req.query.yearFrom as string) : undefined,
    yearTo: req.query.yearTo ? parseInt(req.query.yearTo as string) : undefined
  };

  const results = searchWineData(query, filters);

  res.json({
    query,
    filters,
    count: results.length,
    results
  });
});

// Health check
app.get('/health', (req, res) => {
  res.json({
    status: 'ok',
    agent: 'wine-analytics',
    port: PORT,
    uptime: process.uptime(),
    records: indexedCount,
    indexed: searchIndex.length
  });
});

// Start server
app.listen(PORT, async () => {
  console.log(`\n=== Wine Analytics with Search ===`);
  console.log(`URL: http://localhost:${PORT}`);
  console.log(`Data: ${dataDir}`);

  await initializeSearchIndex();

  console.log(`Indexed: ${indexedCount} searchable records`);
  console.log(`====================================\n`);
});