← back to Designer Wallcoverings

DW-Agents/dw-agents/agent-sku/sku-search-agent.ts

531 lines

/**
 * Agent-SKU: Unified Product Search System
 * 
 * Central hub for searching and managing products across all vendors
 * Integrates with PostgreSQL database for real-time product data
 */

import express, { Request, Response } from 'express';
import { db, initializeDatabase } from '../shared-database';
import { requireGlobalAuth } from '../shared-global-auth';
import { AgentMemory } from '../shared-memory-system';
import cookieParser from 'cookie-parser';

const app = express();
const PORT = process.env.PORT || 9877;

// Initialize agent memory
const agentMemory = new AgentMemory('agent-sku');

// Middleware
app.use(express.json());
app.use(express.urlencoded({ extended: true }));
app.use(cookieParser());
app.use(requireGlobalAuth);

// Initialize database on startup
initializeDatabase().catch(console.error);

// API Routes

/**
 * Get all vendors
 */
app.get('/api/vendors', async (req: Request, res: Response) => {
  try {
    const vendors = await db.getVendors();
    res.json({
      success: true,
      count: vendors.length,
      vendors: vendors.map(v => ({
        id: v.vendor_id,
        name: v.name,
        website: v.website,
        productCount: 0 // Will be populated later
      }))
    });
  } catch (error: any) {
    res.status(500).json({ success: false, error: error.message });
  }
});

/**
 * Search products across all vendors
 */
app.get('/api/products/search', async (req: Request, res: Response) => {
  try {
    const { query, vendor, limit = 50 } = req.query;
    
    let products;
    if (query) {
      products = await db.searchProducts(String(query));
    } else if (vendor) {
      products = await db.getProducts(String(vendor), Number(limit));
    } else {
      products = await db.getProducts(undefined, Number(limit));
    }
    
    res.json({
      success: true,
      count: products.length,
      query: query || null,
      vendor: vendor || null,
      products
    });
  } catch (error: any) {
    res.status(500).json({ success: false, error: error.message });
  }
});

/**
 * Get products by vendor
 */
app.get('/api/vendors/:vendorId/products', async (req: Request, res: Response) => {
  try {
    const { vendorId } = req.params;
    const { limit = 100, newest = false } = req.query;
    
    const products = await db.getProducts(vendorId, Number(limit));
    
    // Sort by newest if requested
    if (newest === 'true') {
      products.sort((a, b) => new Date(b.scraped_at).getTime() - new Date(a.scraped_at).getTime());
    }
    
    res.json({
      success: true,
      vendor: vendorId,
      count: products.length,
      products
    });
  } catch (error: any) {
    res.status(500).json({ success: false, error: error.message });
  }
});

/**
 * Get newest products across all vendors
 */
app.get('/api/products/newest', async (req: Request, res: Response) => {
  try {
    const { limit = 20 } = req.query;
    
    const products = await db.getProducts(undefined, Number(limit));
    products.sort((a, b) => new Date(b.scraped_at).getTime() - new Date(a.scraped_at).getTime());
    
    res.json({
      success: true,
      count: products.length,
      products: products.slice(0, Number(limit))
    });
  } catch (error: any) {
    res.status(500).json({ success: false, error: error.message });
  }
});

/**
 * Get database statistics
 */
app.get('/api/stats', async (req: Request, res: Response) => {
  try {
    const stats = await db.getStats();
    
    // Get product counts by vendor
    const vendorCounts = await db.getClient().$queryRaw`
      SELECT vendor_id, COUNT(*) as count 
      FROM products 
      GROUP BY vendor_id 
      ORDER BY count DESC
    ` as any[];
    
    res.json({
      success: true,
      ...stats,
      vendorProductCounts: vendorCounts
    });
  } catch (error: any) {
    res.status(500).json({ success: false, error: error.message });
  }
});

/**
 * Health check endpoint
 */
app.get('/api/health', (req: Request, res: Response) => {
  res.json({
    status: 'healthy',
    agent: 'agent-sku',
    port: PORT,
    timestamp: new Date().toISOString()
  });
});

// Main dashboard
app.get('/', async (req: Request, res: Response) => {
  try {
    const stats = await db.getStats();
    const recentProducts = await db.getProducts(undefined, 10);
    
    const html = `
<!DOCTYPE html>
<html lang="en">
<head>
  <title>Agent-SKU - Unified Product Search</title>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width, initial-scale=1.0">
  <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: 15px;
      padding: 30px;
      margin-bottom: 30px;
      box-shadow: 0 10px 30px rgba(0,0,0,0.1);
    }
    h1 {
      color: #333;
      display: flex;
      align-items: center;
      gap: 15px;
      font-size: 32px;
    }
    .subtitle {
      color: #666;
      margin-top: 10px;
      font-size: 16px;
    }
    .stats {
      display: grid;
      grid-template-columns: repeat(auto-fit, minmax(200px, 1fr));
      gap: 20px;
      margin-bottom: 30px;
    }
    .stat-card {
      background: white;
      padding: 20px;
      border-radius: 10px;
      text-align: center;
      box-shadow: 0 5px 15px rgba(0,0,0,0.1);
    }
    .stat-number {
      font-size: 36px;
      font-weight: bold;
      color: #667eea;
      display: block;
      margin-bottom: 5px;
    }
    .stat-label {
      color: #666;
      font-size: 14px;
      text-transform: uppercase;
      letter-spacing: 1px;
    }
    .search-box {
      background: white;
      border-radius: 15px;
      padding: 30px;
      margin-bottom: 30px;
      box-shadow: 0 10px 30px rgba(0,0,0,0.1);
    }
    .search-grid {
      display: grid;
      grid-template-columns: 1fr 300px 150px;
      gap: 15px;
      margin-bottom: 20px;
    }
    input, select, button {
      padding: 12px;
      border: 2px solid #e0e0e0;
      border-radius: 8px;
      font-size: 16px;
    }
    input:focus, select:focus {
      outline: none;
      border-color: #667eea;
    }
    button {
      background: linear-gradient(135deg, #667eea 0%, #764ba2 100%);
      color: white;
      border: none;
      cursor: pointer;
      font-weight: bold;
      transition: transform 0.2s;
    }
    button:hover {
      transform: translateY(-2px);
    }
    .results {
      background: white;
      border-radius: 15px;
      padding: 30px;
      box-shadow: 0 10px 30px rgba(0,0,0,0.1);
      max-height: 600px;
      overflow-y: auto;
    }
    .product-grid {
      display: grid;
      gap: 15px;
    }
    .product-card {
      background: #f5f5f5;
      padding: 15px;
      border-radius: 8px;
      display: grid;
      grid-template-columns: auto 1fr auto;
      gap: 15px;
      align-items: center;
    }
    .product-sku {
      font-family: monospace;
      background: #667eea;
      color: white;
      padding: 5px 10px;
      border-radius: 5px;
      font-size: 14px;
    }
    .product-info {
      flex: 1;
    }
    .product-name {
      font-weight: bold;
      color: #333;
      margin-bottom: 5px;
    }
    .product-vendor {
      color: #666;
      font-size: 14px;
    }
    .product-link {
      background: #764ba2;
      color: white;
      padding: 8px 15px;
      border-radius: 5px;
      text-decoration: none;
      font-size: 14px;
    }
    .product-link:hover {
      background: #5a3785;
    }
    .loading {
      display: none;
      text-align: center;
      padding: 20px;
      color: #666;
    }
    .loading.show {
      display: block;
    }
  </style>
</head>
<body>
  <div class="container">
    <div class="header">
      <h1>
        🔍 Agent-SKU
      </h1>
      <p class="subtitle">Unified Product Search System - Connected to PostgreSQL Database</p>
    </div>
    
    <div class="stats">
      <div class="stat-card">
        <span class="stat-number">${stats.vendors}</span>
        <span class="stat-label">Vendors</span>
      </div>
      <div class="stat-card">
        <span class="stat-number">${stats.products}</span>
        <span class="stat-label">Products</span>
      </div>
      <div class="stat-card">
        <span class="stat-number">${stats.specs}</span>
        <span class="stat-label">Specifications</span>
      </div>
      <div class="stat-card">
        <span class="stat-number">Live</span>
        <span class="stat-label">Status</span>
      </div>
    </div>
    
    <div class="search-box">
      <h2 style="margin-bottom: 20px; color: #333;">Product Search</h2>
      <div class="search-grid">
        <input 
          type="text" 
          id="searchQuery" 
          placeholder="Search by SKU, name, or keyword..."
        />
        <select id="vendorFilter">
          <option value="">All Vendors</option>
          <option value="kravet">Kravet</option>
          <option value="designer-wallcoverings">Designer Wallcoverings</option>
          <option value="novasuede">Novasuede</option>
          <option value="arte-international">Arte International</option>
        </select>
        <button onclick="searchProducts()">Search</button>
      </div>
      <div style="display: flex; gap: 10px;">
        <button onclick="getNewest()" style="background: linear-gradient(135deg, #48bb78 0%, #38a169 100%);">
          Show Newest Products
        </button>
        <button onclick="getVendorProducts('kravet')" style="background: linear-gradient(135deg, #ed8936 0%, #dd6b20 100%);">
          Show Kravet Products
        </button>
      </div>
    </div>
    
    <div class="results">
      <h3 style="margin-bottom: 20px; color: #333;">Search Results</h3>
      <div class="loading">Loading...</div>
      <div id="productGrid" class="product-grid">
        ${recentProducts.slice(0, 5).map(p => `
          <div class="product-card">
            <span class="product-sku">${p.sku}</span>
            <div class="product-info">
              <div class="product-name">${p.name || 'No name'}</div>
              <div class="product-vendor">Vendor: ${p.vendor?.name || p.vendor_id}</div>
            </div>
            ${p.product_url ? `<a href="${p.product_url}" target="_blank" class="product-link">View →</a>` : ''}
          </div>
        `).join('')}
      </div>
    </div>
  </div>
  
  <script>
    const loading = document.querySelector('.loading');
    const productGrid = document.getElementById('productGrid');
    
    async function searchProducts() {
      const query = document.getElementById('searchQuery').value;
      const vendor = document.getElementById('vendorFilter').value;
      
      loading.classList.add('show');
      productGrid.innerHTML = '';
      
      try {
        const params = new URLSearchParams();
        if (query) params.append('query', query);
        if (vendor) params.append('vendor', vendor);
        
        const response = await fetch('/api/products/search?' + params);
        const data = await response.json();
        
        displayProducts(data.products);
      } catch (error) {
        productGrid.innerHTML = '<p>Error loading products</p>';
      } finally {
        loading.classList.remove('show');
      }
    }
    
    async function getNewest() {
      loading.classList.add('show');
      productGrid.innerHTML = '';
      
      try {
        const response = await fetch('/api/products/newest');
        const data = await response.json();
        displayProducts(data.products);
      } catch (error) {
        productGrid.innerHTML = '<p>Error loading products</p>';
      } finally {
        loading.classList.remove('show');
      }
    }
    
    async function getVendorProducts(vendorId) {
      loading.classList.add('show');
      productGrid.innerHTML = '';
      
      try {
        const response = await fetch(\`/api/vendors/\${vendorId}/products?newest=true&limit=10\`);
        const data = await response.json();
        displayProducts(data.products);
      } catch (error) {
        productGrid.innerHTML = '<p>Error loading products</p>';
      } finally {
        loading.classList.remove('show');
      }
    }
    
    function displayProducts(products) {
      if (products.length === 0) {
        productGrid.innerHTML = '<p>No products found</p>';
        return;
      }
      
      productGrid.innerHTML = products.map(p => \`
        <div class="product-card">
          <span class="product-sku">\${p.sku}</span>
          <div class="product-info">
            <div class="product-name">\${p.name || 'No name'}</div>
            <div class="product-vendor">Vendor: \${p.vendor?.name || p.vendor_id}</div>
          </div>
          \${p.product_url ? \`<a href="\${p.product_url}" target="_blank" class="product-link">View →</a>\` : ''}
        </div>
      \`).join('');
    }
    
    // Load vendors on page load
    async function loadVendors() {
      try {
        const response = await fetch('/api/vendors');
        const data = await response.json();
        
        const select = document.getElementById('vendorFilter');
        select.innerHTML = '<option value="">All Vendors</option>';
        
        data.vendors.forEach(vendor => {
          const option = document.createElement('option');
          option.value = vendor.id;
          option.textContent = vendor.name;
          select.appendChild(option);
        });
      } catch (error) {
        console.error('Failed to load vendors:', error);
      }
    }
    
    loadVendors();
    
    // Search on Enter key
    document.getElementById('searchQuery').addEventListener('keypress', (e) => {
      if (e.key === 'Enter') searchProducts();
    });
  </script>
</body>
</html>`;
    
    res.send(html);
  } catch (error: any) {
    console.error('Dashboard error:', error);
    res.status(500).send('Error loading dashboard');
  }
});

// Start server
app.listen(PORT, '0.0.0.0', () => {
  console.log(`🔍 Agent-SKU running on port ${PORT}`);
  console.log(`📊 Dashboard: http://45.61.58.125:${PORT}`);
  console.log(`🔗 Connected to PostgreSQL database`);
  
  // Store in memory for other agents
  agentMemory.add(
    `Agent-SKU started on port ${PORT} with features: product-search, vendor-filter, newest-products, unified-database`,
    'note',
    {
      port: PORT,
      startedAt: new Date().toISOString(),
      features: ['product-search', 'vendor-filter', 'newest-products', 'unified-database']
    }
  );
});