`lib/database-optimizations.ts`

158 lines
// Database optimization queries and indexes

export const databaseOptimizations = {
  // Create indexes for better query performance
  createIndexes: [
    // Bottles collection
    `CREATE INDEX idx_bottles_status ON bottles(status)`,
    `CREATE INDEX idx_bottles_vintage ON bottles(vintage)`,
    `CREATE INDEX idx_bottles_available_units ON bottles(availableMembershipUnits)`,
    `CREATE INDEX idx_bottles_created ON bottles(createdAt DESC)`,
    `CREATE INDEX idx_bottles_compound ON bottles(status, availableMembershipUnits)`,

    // Membership units collection
    `CREATE INDEX idx_membership_bottle_user ON membershipUnits(bottleId, userId)`,
    `CREATE INDEX idx_membership_user ON membershipUnits(userId)`,
    `CREATE INDEX idx_membership_status ON membershipUnits(status)`,

    // Votes collection
    `CREATE INDEX idx_votes_bottle ON votes(bottleId)`,
    `CREATE INDEX idx_votes_status_date ON votes(status, endDate DESC)`,
    `CREATE INDEX idx_votes_user ON votes(userId)`,

    // Marketplace collection
    `CREATE INDEX idx_marketplace_status ON marketplace(status, listedAt DESC)`,
    `CREATE INDEX idx_marketplace_bottle ON marketplace(bottleId)`,
    `CREATE INDEX idx_marketplace_seller ON marketplace(sellerId)`,
    `CREATE INDEX idx_marketplace_price ON marketplace(price)`,

    // Price history collection
    `CREATE INDEX idx_price_wine ON priceHistory(wineName)`,
    `CREATE INDEX idx_price_date ON priceHistory(date DESC)`,
    `CREATE INDEX idx_price_compound ON priceHistory(wineName, date DESC)`,
  ],

  // Optimized queries with proper indexing
  optimizedQueries: {
    // Get active bottles with available units
    getActiveBottles: `
      SELECT id, name, vintage, producer, region, size, estimatedValue,
             totalMembershipUnits, availableMembershipUnits, status
      FROM bottles
      WHERE status = 'active' AND availableMembershipUnits > 0
      ORDER BY createdAt DESC
      LIMIT ?
    `,

    // Get user's membership units with bottle info
    getUserMemberships: `
      SELECT mu.*, b.name, b.vintage, b.producer, b.estimatedValue
      FROM membershipUnits mu
      JOIN bottles b ON mu.bottleId = b.id
      WHERE mu.userId = ?
      ORDER BY mu.acquiredAt DESC
    `,

    // Get active votes with vote counts
    getActiveVotesWithCounts: `
      SELECT v.*,
             COUNT(DISTINCT vd.userId) as uniqueVoters,
             SUM(CASE WHEN vd.choice = 'for' THEN 1 ELSE 0 END) as votesFor,
             SUM(CASE WHEN vd.choice = 'against' THEN 1 ELSE 0 END) as votesAgainst
      FROM votes v
      LEFT JOIN voteDetails vd ON v.id = vd.voteId
      WHERE v.status = 'active' AND v.endDate > NOW()
      GROUP BY v.id
      ORDER BY v.endDate ASC
    `,

    // Get marketplace listings with bottle details
    getActiveListings: `
      SELECT m.*, b.name, b.vintage, b.producer, b.region,
             u.name as sellerName
      FROM marketplace m
      JOIN bottles b ON m.bottleId = b.id
      JOIN users u ON m.sellerId = u.id
      WHERE m.status = 'available'
      ORDER BY m.listedAt DESC
      LIMIT ?
    `,

    // Aggregate statistics
    getBottleStats: `
      SELECT
        COUNT(*) as totalBottles,
        SUM(totalMembershipUnits) as totalUnits,
        SUM(totalMembershipUnits - availableMembershipUnits) as soldUnits,
        SUM(estimatedValue) as totalValue,
        AVG(estimatedValue) as avgValue
      FROM bottles
      WHERE status IN ('active', 'unlocked')
    `,
  },

  // Query optimization tips
  tips: {
    pagination: 'Use LIMIT and OFFSET for large result sets',
    projection: 'Only SELECT fields that are needed',
    joins: 'Use proper indexes on JOIN columns',
    aggregation: 'Pre-calculate aggregates when possible',
    caching: 'Cache frequently accessed, rarely changed data',
    batching: 'Batch multiple queries into single request',
    connection_pooling: 'Use connection pooling for better resource utilization',
  },
};

// Database connection pool configuration
export const connectionPoolConfig = {
  min: 2,
  max: 10,
  acquireTimeoutMillis: 30000,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
};

// Query result caching strategy
export const cachingStrategy = {
  // Static data - cache for longer
  staticData: {
    ttl: 3600, // 1 hour
    tables: ['regions', 'producers', 'wineTypes'],
  },

  // Semi-static data - moderate caching
  semiStaticData: {
    ttl: 300, // 5 minutes
    tables: ['bottles', 'membershipPlans'],
  },

  // Dynamic data - short cache
  dynamicData: {
    ttl: 60, // 1 minute
    tables: ['votes', 'marketplace', 'priceHistory'],
  },

  // Real-time data - no caching
  realtimeData: {
    ttl: 0,
    tables: ['transactions', 'userSessions'],
  },
};

// Database maintenance tasks
export const maintenanceTasks = {
  // Vacuum and analyze tables
  vacuum: 'VACUUM ANALYZE',

  // Update statistics
  updateStats: 'ANALYZE',

  // Clean old data
  cleanOldData: `
    DELETE FROM sessions WHERE expiresAt < NOW() - INTERVAL '30 days';
    DELETE FROM logs WHERE createdAt < NOW() - INTERVAL '90 days';
  `,

  // Optimize table storage
  optimizeTables: 'OPTIMIZE TABLE bottles, membershipUnits, votes, marketplace',
};