← back to Designer Wallcoverings
DW-Agents/dw-agents/agent-wine-analytics/index.ts
1107 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';
import Database from 'better-sqlite3';
import https from 'https';
const app = express();
const PORT = 9877;
app.use(cookieParser());
app.use(express.json());
const dataDir = '/root/WebsitesMisc/WineFinder/data/wine-history';
const dbPath = path.join(dataDir, 'wines.db');
// Initialize SQLite database
let db: Database.Database | null = null;
try {
if (fs.existsSync(dbPath)) {
db = new Database(dbPath, { readonly: true });
console.log('✅ Connected to wines database:', dbPath);
} else {
console.log('⚠️ Wine database not found:', dbPath);
}
} catch (error) {
console.error('❌ Failed to connect to database:', error);
}
// Vivino API integration for REAL wine images
async function fetchVivinoWineData(wineName: string): Promise<any> {
return new Promise((resolve, reject) => {
const searchQuery = encodeURIComponent(wineName);
const url = `https://www.vivino.com/api/wines/search?q=${searchQuery}&language=en`;
https.get(url, {
headers: {
'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36',
'Accept': 'application/json'
}
}, (res) => {
let data = '';
res.on('data', chunk => data += chunk);
res.on('end', () => {
try {
const json = JSON.parse(data);
if (json.wines && json.wines.length > 0) {
const wine = json.wines[0].wine;
resolve({
name: wine.name,
winery: wine.winery?.name,
vintage: wine.year,
image: wine.image?.location, // REAL wine bottle image from Vivino
rating: wine.statistics?.ratings_average,
price: wine.price?.amount,
region: wine.region?.name,
url: `https://www.vivino.com${wine.seo_name}`
});
} else {
resolve(null);
}
} catch (e) {
resolve(null);
}
});
}).on('error', () => resolve(null));
});
}
// Get top wines from database with Vivino images
async function getTopWinesWithImages(limit: number = 20): Promise<any[]> {
if (!db) return [];
try {
// Get iconic wines with diverse selection
const iconicWines = [
'Château Lafite Rothschild',
'Château Margaux',
'Opus One',
'Dom Pérignon',
'Sassicaia',
'Penfolds Grange',
'Ridge Monte Bello',
'Caymus Cabernet',
'Silver Oak',
'Stags Leap',
'Cakebread Chardonnay',
'Cloudy Bay Sauvignon Blanc',
'Krug Grande Cuvée',
'Screaming Eagle',
'Harlan Estate',
'Ornellaia',
'Vega Sicilia Unico',
'Dominus Estate',
'Château Haut-Brion',
'Pétrus'
];
const winesWithImages = await Promise.all(
iconicWines.slice(0, limit).map(async (wineName) => {
const vivinoData = await fetchVivinoWineData(wineName);
return vivinoData;
})
);
return winesWithImages.filter(wine => wine !== null);
} catch (error) {
console.error('Error fetching wines:', error);
return [];
}
}
// 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) => {
// Force cache busting
res.setHeader('Cache-Control', 'no-store, no-cache, must-revalidate, proxy-revalidate, max-age=0');
res.setHeader('Pragma', 'no-cache');
res.setHeader('Expires', '0');
res.setHeader('Surrogate-Control', 'no-store');
res.setHeader('X-Version', Date.now().toString());
const downloadedFiles = scanDownloadedFiles();
res.send(`
<!DOCTYPE html>
<html>
<head>
<title>Wine Data Search & Analytics v2.0 - Gallery Edition</title>
<meta name="version" content="${Date.now()}">
<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; }
.action-btn {
padding: 10px 20px;
background: white;
color: #667eea;
border: 2px solid #667eea;
border-radius: 6px;
font-size: 0.95em;
font-weight: 600;
cursor: pointer;
transition: all 0.2s;
}
.action-btn:hover {
background: #667eea;
color: white;
transform: translateY(-2px);
box-shadow: 0 2px 8px rgba(102,126,234,0.3);
}
.hero-image {
width: 100%;
height: 200px;
background: linear-gradient(rgba(102, 126, 234, 0.9), rgba(118, 75, 162, 0.9)),
url('data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 1200 200"><text x="50%" y="50%" font-size="120" text-anchor="middle" dominant-baseline="middle" fill="rgba(255,255,255,0.1)">🍷🍇📊</text></svg>');
background-size: cover;
background-position: center;
border-radius: 12px 12px 0 0;
display: flex;
align-items: center;
justify-content: center;
margin: -30px -30px 20px -30px;
}
</style>
</head>
<body>
<div class="container">
<header>
<div style="display: flex; align-items: center; gap: 20px;">
<div style="font-size: 80px;">🍷</div>
<div style="flex: 1;">
<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>
</div>
</div>
<div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(200px, 1fr)); gap: 15px; margin-top: 20px;">
<div style="background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 20px; border-radius: 8px; text-align: center;">
<div style="font-size: 40px; margin-bottom: 10px;">📊</div>
<div style="font-size: 2em; font-weight: bold;">${indexedCount.toLocaleString()}</div>
<div style="opacity: 0.9;">Total Records</div>
</div>
<div style="background: linear-gradient(135deg, #f093fb 0%, #f5576c 100%); color: white; padding: 20px; border-radius: 8px; text-align: center;">
<div style="font-size: 40px; margin-bottom: 10px;">🇺🇸</div>
<div style="font-size: 2em; font-weight: bold;">5,820</div>
<div style="opacity: 0.9;">USA Records</div>
</div>
<div style="background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%); color: white; padding: 20px; border-radius: 8px; text-align: center;">
<div style="font-size: 40px; margin-bottom: 10px;">📅</div>
<div style="font-size: 2em; font-weight: bold;">2012-2025</div>
<div style="opacity: 0.9;">Year Range</div>
</div>
<div style="background: linear-gradient(135deg, #43e97b 0%, #38f9d7 100%); color: white; padding: 20px; border-radius: 8px; text-align: center;">
<div style="font-size: 40px; margin-bottom: 10px;">⚡</div>
<div style="font-size: 2em; font-weight: bold;"><100ms</div>
<div style="opacity: 0.9;">Search Speed</div>
</div>
</div>
</header>
<div class="search-section">
<h2 style="color: #667eea; margin-bottom: 20px;">Search Wine Data</h2>
<div class="action-buttons" style="display: flex; gap: 10px; margin-bottom: 20px; flex-wrap: wrap;">
<button class="action-btn" onclick="showWineGallery()">🍷 Wine Gallery</button>
<button class="action-btn" onclick="quickSearch('production')">📊 Production Data</button>
<button class="action-btn" onclick="quickSearch('2024')">📅 Recent (2024)</button>
<button class="action-btn" onclick="viewStats()">📈 Statistics</button>
<button class="action-btn" onclick="exportResults()">💾 Export CSV</button>
<button class="action-btn" onclick="refreshIndex()">🔄 Refresh Index</button>
<button class="action-btn" onclick="viewAPI()">📚 API Docs</button>
</div>
<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();
// Store results for export
lastSearchResults = data.results;
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>';
lastSearchResults = null;
return;
}
let html = '<h2>Search Results (' + data.results.length + ' found)</h2><div class="results-grid">';
data.results.forEach(result => {
const icon = result.country === 'USA' ? '🇺🇸' : result.country === 'France' ? '🇫🇷' : result.country === 'Spain' ? '🇪🇸' : '🌍';
const sourceIcon = result.source.includes('TTB') ? '🏛️' : '📊';
html += \`
<div class="result-card">
<div class="result-header">
<span class="result-source">\${sourceIcon} \${result.source}</span>
<span class="result-country">\${icon} \${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();
});
// Quick search presets
function quickSearch(term) {
document.getElementById('searchQuery').value = term;
searchData();
}
// View statistics
async function viewStats() {
const resultsSection = document.getElementById('resultsSection');
resultsSection.innerHTML = '<div class="loading">Loading statistics...</div>';
try {
const response = await fetch('/api/stats');
const stats = await response.json();
let html = '<h2>Database Statistics</h2>';
html += '<div style="display: grid; grid-template-columns: repeat(auto-fit, minmax(250px, 1fr)); gap: 20px;">';
html += \`
<div class="result-card" style="background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; border: none;">
<h3 style="color: white; margin-bottom: 10px; display: flex; align-items: center; gap: 10px;">
<span style="font-size: 1.5em;">📊</span> Total Records
</h3>
<p style="font-size: 3em; font-weight: bold;">\${stats.totalRecords.toLocaleString()}</p>
</div>
<div class="result-card">
<h3 style="color: #667eea; margin-bottom: 10px; display: flex; align-items: center; gap: 10px;">
<span style="font-size: 1.5em;">🏛️</span> Data Sources
</h3>
<div style="padding: 10px;">
\${Object.entries(stats.bySources).map(([source, count]) =>
'<div style="display: flex; justify-content: space-between; padding: 8px; background: #f8f9ff; margin: 5px 0; border-radius: 4px;"><span>' + source + '</span><strong>' + count.toLocaleString() + '</strong></div>'
).join('')}
</div>
</div>
<div class="result-card" style="background: linear-gradient(135deg, #4facfe 0%, #00f2fe 100%); color: white; border: none;">
<h3 style="color: white; margin-bottom: 10px; display: flex; align-items: center; gap: 10px;">
<span style="font-size: 1.5em;">🗺️</span> By Country
</h3>
<div style="padding: 10px;">
\${Object.entries(stats.byCountry).map(([country, count]) => {
const flag = country === 'USA' ? '🇺🇸' : country === 'France' ? '🇫🇷' : '🌍';
return '<div style="display: flex; justify-content: space-between; padding: 8px; background: rgba(255,255,255,0.2); margin: 5px 0; border-radius: 4px;"><span>' + flag + ' ' + country + '</span><strong>' + count.toLocaleString() + '</strong></div>';
}).join('')}
</div>
</div>
<div class="result-card" style="background: linear-gradient(135deg, #43e97b 0%, #38f9d7 100%); color: white; border: none;">
<h3 style="color: white; margin-bottom: 10px; display: flex; align-items: center; gap: 10px;">
<span style="font-size: 1.5em;">📅</span> Year Range
</h3>
<p style="font-size: 2.5em; font-weight: bold;">\${stats.yearRange.min} - \${stats.yearRange.max}</p>
<p style="opacity: 0.9; margin-top: 10px;">\${stats.yearRange.max - stats.yearRange.min + 1} years of data</p>
</div>
\`;
html += '</div>';
resultsSection.innerHTML = html;
} catch (error) {
resultsSection.innerHTML = '<h2>Statistics</h2><p class="no-results">Error loading statistics.</p>';
}
}
// Export results to CSV
let lastSearchResults = null;
async function exportResults() {
if (!lastSearchResults || lastSearchResults.length === 0) {
alert('Please run a search first before exporting results.');
return;
}
const csv = convertToCSV(lastSearchResults);
const blob = new Blob([csv], { type: 'text/csv' });
const url = window.URL.createObjectURL(blob);
const a = document.createElement('a');
a.href = url;
a.download = 'wine-data-export-' + new Date().toISOString().split('T')[0] + '.csv';
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
window.URL.revokeObjectURL(url);
}
function convertToCSV(results) {
if (results.length === 0) return '';
const headers = ['Source', 'Country', 'Year', ...Object.keys(results[0].data)];
const rows = results.map(r => {
return [r.source, r.country, r.year || '', ...Object.values(r.data)].map(v => {
const str = String(v).replace(/"/g, '""');
return str.includes(',') || str.includes('"') || str.includes('\\n') ? '"' + str + '"' : str;
});
});
return [headers.join(','), ...rows.map(r => r.join(','))].join('\\n');
}
// Refresh index
async function refreshIndex() {
if (!confirm('Refresh the search index? This will reload all data files.')) return;
const resultsSection = document.getElementById('resultsSection');
resultsSection.innerHTML = '<div class="loading">Refreshing index...</div>';
try {
const response = await fetch('/api/refresh', { method: 'POST' });
const data = await response.json();
resultsSection.innerHTML = \`
<h2>Index Refreshed</h2>
<div class="result-card">
<p style="font-size: 1.2em; color: #667eea;">✅ Successfully reindexed \${data.records.toLocaleString()} records</p>
<p style="margin-top: 10px;">Took \${data.duration}ms</p>
</div>
\`;
} catch (error) {
resultsSection.innerHTML = '<h2>Refresh Failed</h2><p class="no-results">Error refreshing index.</p>';
}
}
// Show wine gallery with REAL images from Vivino API
async function showWineGallery() {
const resultsSection = document.getElementById('resultsSection');
resultsSection.innerHTML = '<div class="loading">Loading real wine data from Vivino...</div>';
try {
// Fetch REAL wines with images from /api/wines endpoint
const response = await fetch('/api/wines?limit=20');
const data = await response.json();
if (!data.wines || data.wines.length === 0) {
resultsSection.innerHTML = '<h2>Wine Gallery</h2><p class="no-results">Unable to load wine data. Please try again later.</p>';
return;
}
const wines = data.wines;
// Sort by price (low to high)
wines.sort((a, b) => a.price - b.price);
let html = '<h2 style="display: flex; align-items: center; gap: 10px;"><span>🍷</span> Premium Wine Gallery with Real Images from Vivino - Sorted by Price</h2>';
html += '<p style="color: #666; margin: 10px 0 20px 0;">🏛️ Showing wines with real product images and data from Vivino wine database</p>';
html += '<div style="display: grid; grid-template-columns: repeat(auto-fill, minmax(300px, 1fr)); gap: 20px; margin-top: 20px;">';
wines.forEach(wine => {
const typeColor = wine.type === 'Red' ? '#8B0000' : wine.type === 'White' ? '#FFD700' : '#FFB6C1';
const displayPrice = wine.price ? Math.round(wine.price) : 'N/A';
const displayRating = wine.rating ? Math.round(wine.rating) : 'N/A';
const wineImage = wine.image || 'data:image/svg+xml,<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 400 400"><rect fill="%23667eea" width="400" height="400"/><text x="50%" y="50%" font-size="100" text-anchor="middle" dominant-baseline="middle" fill="white">🍷</text></svg>';
html += \`
<div class="result-card" style="overflow: hidden; padding: 0; position: relative; cursor: pointer; transition: transform 0.3s;" onmouseover="this.style.transform='translateY(-5px)'" onmouseout="this.style.transform='translateY(0)'">
<div style="position: relative; width: 100%; height: 250px; overflow: hidden; background: #f5f5f5;">
<img src="\${wineImage}" alt="\${wine.name}" style="width: 100%; height: 100%; object-fit: contain;" onerror="this.src='data:image/svg+xml,<svg xmlns=\\"http://www.w3.org/2000/svg\\" viewBox=\\"0 0 400 400\\"><rect fill=\\"%23667eea\\" width=\\"400\\" height=\\"400\\"/><text x=\\"50%\\" y=\\"50%\\" font-size=\\"100\\" text-anchor=\\"middle\\" dominant-baseline=\\"middle\\" fill=\\"white\\">🍷</text></svg>';">
<div style="position: absolute; top: 10px; right: 10px; background: rgba(255,255,255,0.95); padding: 5px 12px; border-radius: 20px; font-weight: bold; color: #667eea;">
⭐ \${displayRating}
</div>
<div style="position: absolute; top: 10px; left: 10px; background: \${typeColor}; padding: 5px 12px; border-radius: 20px; font-weight: bold; color: white; font-size: 0.85em;">
\${wine.type}
</div>
</div>
<div style="padding: 20px;">
<h3 style="color: #667eea; margin-bottom: 8px; font-size: 1.1em;">\${wine.name}</h3>
<p style="color: #999; font-size: 0.9em; margin-bottom: 8px;">🏢 \${wine.winery || 'Unknown Winery'}</p>
<p style="color: #999; font-size: 0.9em; margin-bottom: 8px;">📍 \${wine.region || 'Unknown Region'}</p>
<p style="color: #666; font-size: 0.9em; margin-bottom: 12px;">🍇 \${wine.grape}</p>
<div style="display: flex; justify-content: space-between; align-items: center; border-top: 1px solid #e0e0e0; padding-top: 12px;">
<span style="font-size: 1.8em; font-weight: bold; color: #667eea;">$\${displayPrice}</span>
<a href="\${wine.url || '#'}" target="_blank" style="text-decoration: none;">
<button style="background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; border: none; padding: 8px 16px; border-radius: 6px; cursor: pointer; font-weight: 600;">View on Vivino</button>
</a>
</div>
</div>
</div>
\`;
});
html += '</div>';
resultsSection.innerHTML = html;
} catch (error) {
console.error('Error loading wine gallery:', error);
resultsSection.innerHTML = '<h2>Wine Gallery</h2><p class="no-results">Error loading wine data. Please try again later.</p>';
}
}
// View API documentation
function viewAPI() {
const resultsSection = document.getElementById('resultsSection');
resultsSection.innerHTML = \`
<h2>API Documentation</h2>
<div class="result-card">
<h3 style="color: #667eea;">Search Endpoint</h3>
<div class="result-data">
<pre>GET /api/search?q=wine&country=USA&yearFrom=2020&yearTo=2024
Parameters:
q - Search query (searches all fields)
country - Filter by country (USA, France, Spain, Global)
source - Filter by data source (TTB Yearly, TTB Monthly, etc.)
yearFrom - Start year (1980-2024)
yearTo - End year (1980-2024)
Response:
{
"query": "wine",
"filters": {...},
"count": 100,
"results": [...]
}</pre>
</div>
</div>
<div class="result-card" style="margin-top: 15px;">
<h3 style="color: #667eea;">Health Check</h3>
<div class="result-data">
<pre>GET /health
Response:
{
"status": "ok",
"agent": "wine-analytics",
"port": 9877,
"uptime": 123.45,
"records": 5820,
"indexed": 5820
}</pre>
</div>
</div>
<div class="result-card" style="margin-top: 15px;">
<h3 style="color: #667eea;">Statistics</h3>
<div class="result-data">
<pre>GET /api/stats
Response:
{
"totalRecords": 5820,
"bySources": {...},
"byCountry": {...},
"yearRange": { "min": 1980, "max": 2024 }
}</pre>
</div>
</div>
\`;
}
</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
});
});
// Wines API - Get real wines with images from database
app.get('/api/wines', async (req, res) => {
const limit = parseInt(req.query.limit as string) || 20;
const search = (req.query.search as string) || '';
const sortBy = (req.query.sortBy as string) || 'rating'; // rating, price, year, name
const minPrice = parseFloat(req.query.minPrice as string) || 0;
const maxPrice = parseFloat(req.query.maxPrice as string) || 1000;
try {
if (!db) {
throw new Error('Database not connected');
}
// Build query with filters
let query = 'SELECT * FROM wines WHERE 1=1';
const params: any[] = [];
if (search) {
query += ' AND (name LIKE ? OR winery LIKE ? OR country LIKE ? OR region LIKE ? OR grape LIKE ?)';
const searchTerm = `%${search}%`;
params.push(searchTerm, searchTerm, searchTerm, searchTerm, searchTerm);
}
if (minPrice > 0) {
query += ' AND price >= ?';
params.push(minPrice);
}
if (maxPrice < 1000) {
query += ' AND price <= ?';
params.push(maxPrice);
}
// Add sorting
const sortColumn = sortBy === 'price' ? 'price ASC' :
sortBy === 'year' ? 'year DESC' :
sortBy === 'name' ? 'name ASC' :
'rating DESC, price ASC'; // Default: best rated, then cheapest
query += ` ORDER BY ${sortColumn} LIMIT ?`;
params.push(limit);
const wines = db.prepare(query).all(...params);
res.json({
count: wines.length,
totalInDb: db.prepare('SELECT COUNT(*) as count FROM wines').get().count,
filters: { search, sortBy, minPrice, maxPrice, limit },
wines: wines.map((w: any) => ({
id: w.id,
name: w.name,
winery: w.winery,
year: w.year,
price: w.price,
rating: w.rating,
country: w.country,
region: w.region,
grape: w.grape,
alcohol: w.alcohol,
image: w.image_url,
url: w.vintage_page_url
}))
});
} catch (error) {
console.error('Error fetching wines:', error);
res.status(500).json({ error: 'Failed to fetch wines from database' });
}
});
// Statistics API
app.get('/api/stats', (req, res) => {
const bySources: Record<string, number> = {};
const byCountry: Record<string, number> = {};
const years: number[] = [];
searchIndex.forEach(item => {
bySources[item.source] = (bySources[item.source] || 0) + 1;
byCountry[item.country] = (byCountry[item.country] || 0) + 1;
const year = parseInt(item.year || '0');
if (year > 0) years.push(year);
});
const yearRange = years.length > 0 ? {
min: Math.min(...years),
max: Math.max(...years)
} : { min: 0, max: 0 };
res.json({
totalRecords: searchIndex.length,
bySources,
byCountry,
yearRange
});
});
// Refresh index API
app.post('/api/refresh', async (req, res) => {
const startTime = Date.now();
await initializeSearchIndex();
const duration = Date.now() - startTime;
res.json({
status: 'ok',
records: indexedCount,
duration
});
});
// Research Library - Historical Wine Data API
app.get('/api/research/price-trends', async (req, res) => {
try {
if (!db) throw new Error('Database not connected');
const priceByYear = db.prepare(`
SELECT year, AVG(price) as avg_price, MIN(price) as min_price,
MAX(price) as max_price, COUNT(*) as count
FROM wines
WHERE year IS NOT NULL AND price IS NOT NULL
GROUP BY year ORDER BY year ASC
`).all();
const priceByCountry = db.prepare(`
SELECT country, AVG(price) as avg_price, COUNT(*) as count
FROM wines WHERE country IS NOT NULL AND price IS NOT NULL
GROUP BY country ORDER BY count DESC LIMIT 15
`).all();
const priceByGrape = db.prepare(`
SELECT grape, AVG(price) as avg_price, COUNT(*) as count
FROM wines WHERE grape IS NOT NULL AND price IS NOT NULL
GROUP BY grape ORDER BY count DESC LIMIT 10
`).all();
res.json({ priceByYear, priceByCountry, priceByGrape });
} catch (error) {
console.error('Error fetching price trends:', error);
res.status(500).json({ error: 'Failed to fetch price trends' });
}
});
// Research Library - Wine Statistics API
app.get('/api/research/statistics', async (req, res) => {
try {
if (!db) throw new Error('Database not connected');
const overall = db.prepare(`
SELECT COUNT(*) as total_wines,
AVG(price) as avg_price,
AVG(rating) as avg_rating,
MIN(year) as oldest_year,
MAX(year) as newest_year
FROM wines WHERE price IS NOT NULL
`).get();
const byCountry = db.prepare(`
SELECT country, COUNT(*) as count, AVG(price) as avg_price, AVG(rating) as avg_rating
FROM wines WHERE country IS NOT NULL
GROUP BY country ORDER BY count DESC
`).all();
const byGrape = db.prepare(`
SELECT grape, COUNT(*) as count, AVG(price) as avg_price, AVG(rating) as avg_rating
FROM wines WHERE grape IS NOT NULL
GROUP BY grape ORDER BY count DESC
`).all();
const priceRanges = db.prepare(`
SELECT
CASE
WHEN price < 20 THEN 'Under $20'
WHEN price < 50 THEN '$20-$50'
WHEN price < 100 THEN '$50-$100'
ELSE 'Over $100'
END as range,
COUNT(*) as count
FROM wines WHERE price IS NOT NULL
GROUP BY range
`).all();
res.json({ overall, byCountry, byGrape, priceRanges });
} catch (error) {
console.error('Error fetching statistics:', error);
res.status(500).json({ error: 'Failed to fetch statistics' });
}
});
// Research Library - Dataset Info API
app.get('/api/research/datasets', (req, res) => {
res.json({
datasets: [
{
name: 'UC Davis WineSensed',
description: 'Learning to Taste: A Multimodal Wine Dataset (NeurIPS 2023)',
source: 'University of California, Davis',
url: 'https://thoranna.github.io/learning_to_taste/',
huggingface: 'https://huggingface.co/datasets/Dakhoo/L2T-NeurIPS-2023',
license: 'CC BY-NC-ND 4.0',
records: '1,014,630 wine reviews',
unique_wines: '107 wines with complete metadata',
date_range: '1950-2021',
features: [
'Wine names, vintages, wineries',
'Historical pricing data (May 2023)',
'Ratings and reviews',
'Grape composition',
'Authentic wine bottle label images',
'Country, region, alcohol content'
]
},
{
name: 'TTB Tax and Trade Bureau',
description: 'US Government wine import/export statistics',
source: 'Alcohol and Tobacco Tax and Trade Bureau',
url: 'https://www.ttb.gov/wine/wine-stats',
records: '5,820 government records',
features: [
'Monthly wine import data',
'Yearly production statistics',
'Trade volume metrics'
]
}
]
});
});
// 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`);
});