Performance & Optimization - 1/2

// The nightmare of zero observability
class ProductService {
  async getProductDetails(productId: string) {
    // No timing, no metrics, no visibility - RED FLAG #1
    const product = await this.productRepository.findById(productId);

    // Complex business logic with zero monitoring - RED FLAG #2
    if (product.category === "electronics") {
      const warranty = await this.warrantyService.getWarranty(productId);
      const reviews = await this.reviewService.getReviews(productId, {
        limit: 50,
      });
      const recommendations =
        await this.recommendationService.getSimilarProducts(
          product.category,
          product.priceRange,
          { limit: 10 }
        );

      // Multiple API calls with no timeout or circuit breaker - RED FLAG #3
      const inventory = await this.inventoryService.checkStock(productId);
      const shipping = await this.shippingService.getDeliveryOptions(
        product.weight,
        product.dimensions
      );

      return {
        product,
        warranty,
        reviews,
        recommendations,
        inventory,
        shipping,
      };
    }

    return { product };

    // Questions you can't answer:
    // - Which service is the slowest?
    // - How many requests are failing?
    // - What's the 95th percentile response time?
    // - Which queries are taking the longest?
    // - Are we hitting API rate limits?
    // - What happens when recommendationService is down?
  }
}

// Controller with zero performance insights
class ProductController {
  async getProduct(req: Request, res: Response) {
    // No request tracking, no performance metrics - RED FLAG #4
    const startTime = Date.now(); // Basic timing at best

    try {
      const product = await this.productService.getProductDetails(
        req.params.id
      );

      const duration = Date.now() - startTime;
      console.log(`Request took ${duration}ms`); // Useless logging - RED FLAG #5

      res.json(product);
    } catch (error) {
      console.error("Error:", error.message); // No context, no metrics - RED FLAG #6
      res.status(500).json({ error: "Internal server error" });
    }

    // What you don't know will kill your application:
    // - Error rates by endpoint
    // - Memory usage trends
    // - Database connection pool status
    // - Cache hit/miss ratios
    // - External service dependency health
  }
}

// Performance monitoring infrastructure
export interface IPerformanceMetrics {
  recordTimer(
    name: string,
    duration: number,
    tags?: Record<string, string>
  ): void;
  incrementCounter(name: string, tags?: Record<string, string>): void;
  recordGauge(name: string, value: number, tags?: Record<string, string>): void;
  recordHistogram(
    name: string,
    value: number,
    tags?: Record<string, string>
  ): void;
}

export interface ILogger {
  info(message: string, context?: any): void;
  warn(message: string, context?: any): void;
  error(message: string, error?: Error, context?: any): void;
}

export class PrometheusMetrics implements IPerformanceMetrics {
  private register: prometheus.Registry;
  private timers = new Map<string, prometheus.Histogram<string>>();
  private counters = new Map<string, prometheus.Counter<string>>();
  private gauges = new Map<string, prometheus.Gauge<string>>();
  private histograms = new Map<string, prometheus.Histogram<string>>();

  constructor() {
    this.register = new prometheus.Registry();
    prometheus.collectDefaultMetrics({ register: this.register });
  }

  recordTimer(
    name: string,
    duration: number,
    tags: Record<string, string> = {}
  ): void {
    const key = `${name}_duration_seconds`;

    if (!this.timers.has(key)) {
      const histogram = new prometheus.Histogram({
        name: key,
        help: `Duration of ${name} operations in seconds`,
        labelNames: Object.keys(tags),
        buckets: [0.01, 0.05, 0.1, 0.25, 0.5, 1, 2.5, 5, 10], // Customizable buckets
      });
      this.register.registerMetric(histogram);
      this.timers.set(key, histogram);
    }

    this.timers.get(key)!.observe(tags, duration / 1000);
  }

  incrementCounter(name: string, tags: Record<string, string> = {}): void {
    const key = `${name}_total`;

    if (!this.counters.has(key)) {
      const counter = new prometheus.Counter({
        name: key,
        help: `Total count of ${name}`,
        labelNames: Object.keys(tags),
      });
      this.register.registerMetric(counter);
      this.counters.set(key, counter);
    }

    this.counters.get(key)!.inc(tags, 1);
  }

  recordGauge(
    name: string,
    value: number,
    tags: Record<string, string> = {}
  ): void {
    const key = `${name}_current`;

    if (!this.gauges.has(key)) {
      const gauge = new prometheus.Gauge({
        name: key,
        help: `Current value of ${name}`,
        labelNames: Object.keys(tags),
      });
      this.register.registerMetric(gauge);
      this.gauges.set(key, gauge);
    }

    this.gauges.get(key)!.set(tags, value);
  }

  recordHistogram(
    name: string,
    value: number,
    tags: Record<string, string> = {}
  ): void {
    const key = `${name}_distribution`;

    if (!this.histograms.has(key)) {
      const histogram = new prometheus.Histogram({
        name: key,
        help: `Distribution of ${name}`,
        labelNames: Object.keys(tags),
        buckets: prometheus.exponentialBuckets(0.01, 2, 15),
      });
      this.register.registerMetric(histogram);
      this.histograms.set(key, histogram);
    }

    this.histograms.get(key)!.observe(tags, value);
  }

  getMetrics(): string {
    return this.register.metrics();
  }
}

// Performance monitoring middleware
export class PerformanceMiddleware {
  constructor(private metrics: IPerformanceMetrics, private logger: ILogger) {}

  trackRequest() {
    return (req: Request, res: Response, next: NextFunction) => {
      const startTime = Date.now();
      const requestId = uuidv4();

      // Add request tracking to context
      req.context = {
        ...req.context,
        requestId,
        startTime,
      };

      // Track request start
      this.metrics.incrementCounter("http_requests_started", {
        method: req.method,
        route: req.route?.path || "unknown",
        user_agent: this.getUserAgentCategory(req.get("User-Agent")),
      });

      // Override res.end to capture response metrics
      const originalEnd = res.end.bind(res);
      res.end = (...args: any[]) => {
        const duration = Date.now() - startTime;
        const statusCode = res.statusCode.toString();

        // Record response time
        this.metrics.recordTimer("http_request_duration", duration, {
          method: req.method,
          route: req.route?.path || "unknown",
          status_code: statusCode,
          status_class: `${Math.floor(res.statusCode / 100)}xx`,
        });

        // Track request completion
        this.metrics.incrementCounter("http_requests_completed", {
          method: req.method,
          route: req.route?.path || "unknown",
          status_code: statusCode,
        });

        // Log slow requests
        if (duration > 1000) {
          this.logger.warn("Slow request detected", {
            requestId,
            method: req.method,
            url: req.url,
            duration,
            statusCode,
            userAgent: req.get("User-Agent"),
          });
        }

        // Log errors
        if (res.statusCode >= 400) {
          this.logger.error("Request resulted in error", undefined, {
            requestId,
            method: req.method,
            url: req.url,
            duration,
            statusCode,
            userAgent: req.get("User-Agent"),
          });
        }

        originalEnd(...args);
      };

      next();
    };
  }

  trackDatabaseQueries() {
    return (req: Request, res: Response, next: NextFunction) => {
      const originalQuery = req.db?.query?.bind(req.db);

      if (originalQuery) {
        req.db.query = async (sql: string, params?: any[]) => {
          const queryStart = Date.now();
          const queryId = uuidv4().substring(0, 8);

          try {
            this.metrics.incrementCounter("database_queries_started", {
              operation: this.extractQueryOperation(sql),
              table: this.extractTableName(sql),
            });

            const result = await originalQuery(sql, params);
            const queryDuration = Date.now() - queryStart;

            this.metrics.recordTimer("database_query_duration", queryDuration, {
              operation: this.extractQueryOperation(sql),
              table: this.extractTableName(sql),
              status: "success",
            });

            // Log slow queries
            if (queryDuration > 100) {
              this.logger.warn("Slow database query", {
                queryId,
                requestId: req.context?.requestId,
                sql: sql.substring(0, 200), // Truncate for logging
                duration: queryDuration,
                rowCount: result.rows?.length || 0,
              });
            }

            return result;
          } catch (error) {
            const queryDuration = Date.now() - queryStart;

            this.metrics.recordTimer("database_query_duration", queryDuration, {
              operation: this.extractQueryOperation(sql),
              table: this.extractTableName(sql),
              status: "error",
            });

            this.metrics.incrementCounter("database_queries_failed", {
              operation: this.extractQueryOperation(sql),
              table: this.extractTableName(sql),
              error_type: error.constructor.name,
            });

            this.logger.error("Database query failed", error as Error, {
              queryId,
              requestId: req.context?.requestId,
              sql: sql.substring(0, 200),
              duration: queryDuration,
            });

            throw error;
          }
        };
      }

      next();
    };
  }

  private getUserAgentCategory(userAgent?: string): string {
    if (!userAgent) return "unknown";

    if (userAgent.includes("bot") || userAgent.includes("crawler"))
      return "bot";
    if (userAgent.includes("Mobile")) return "mobile";
    if (userAgent.includes("Chrome")) return "chrome";
    if (userAgent.includes("Firefox")) return "firefox";
    if (userAgent.includes("Safari")) return "safari";

    return "other";
  }

  private extractQueryOperation(sql: string): string {
    const operation = sql.trim().split(" ")[0].toLowerCase();
    return ["select", "insert", "update", "delete"].includes(operation)
      ? operation
      : "other";
  }

  private extractTableName(sql: string): string {
    const match = sql.match(
      /(?:from|into|update|join)\s+([a-zA-Z_][a-zA-Z0-9_]*)/i
    );
    return match ? match[1].toLowerCase() : "unknown";
  }
}

// Service-level monitoring with detailed metrics
export class MonitoredProductService {
  constructor(
    private productRepository: IProductRepository,
    private warrantyService: IWarrantyService,
    private reviewService: IReviewService,
    private recommendationService: IRecommendationService,
    private inventoryService: IInventoryService,
    private shippingService: IShippingService,
    private metrics: IPerformanceMetrics,
    private logger: ILogger
  ) {}

  async getProductDetails(productId: string): Promise<ProductDetails> {
    const startTime = Date.now();
    const operationId = uuidv4().substring(0, 8);

    this.metrics.incrementCounter("product_details_requests", {
      product_id: productId.substring(0, 8), // Partial ID for privacy
    });

    try {
      this.logger.info("Starting product details retrieval", {
        operationId,
        productId: productId.substring(0, 8),
      });

      // Step 1: Get basic product info
      const productStart = Date.now();
      const product = await this.productRepository.findById(productId);
      this.metrics.recordTimer(
        "product_repository_find",
        Date.now() - productStart
      );

      if (!product) {
        this.metrics.incrementCounter("product_details_not_found");
        throw new NotFoundError(`Product not found: ${productId}`);
      }

      // Step 2: Parallel data loading with individual tracking
      const [warranty, reviews, recommendations, inventory, shipping] =
        await this.loadProductDataInParallel(product, operationId);

      const totalDuration = Date.now() - startTime;
      this.metrics.recordTimer("product_details_total", totalDuration, {
        category: product.category,
        has_warranty: warranty ? "true" : "false",
      });

      this.logger.info("Product details retrieved successfully", {
        operationId,
        productId: productId.substring(0, 8),
        duration: totalDuration,
        category: product.category,
      });

      return {
        product,
        warranty,
        reviews,
        recommendations,
        inventory,
        shipping,
      };
    } catch (error) {
      const totalDuration = Date.now() - startTime;

      this.metrics.incrementCounter("product_details_errors", {
        error_type: error.constructor.name,
      });

      this.metrics.recordTimer("product_details_failed", totalDuration);

      this.logger.error("Product details retrieval failed", error as Error, {
        operationId,
        productId: productId.substring(0, 8),
        duration: totalDuration,
      });

      throw error;
    }
  }

  private async loadProductDataInParallel(
    product: Product,
    operationId: string
  ): Promise<
    [
      Warranty?,
      ProductReview[],
      ProductRecommendation[],
      InventoryInfo?,
      ShippingOptions?
    ]
  > {
    const promises: Promise<any>[] = [];

    // Warranty (only for electronics)
    if (product.category === "electronics") {
      promises.push(
        this.loadWithRetryAndMetrics(
          "warranty",
          () => this.warrantyService.getWarranty(product.id),
          { productId: product.id.substring(0, 8), operationId }
        )
      );
    } else {
      promises.push(Promise.resolve(null));
    }

    // Reviews (always load)
    promises.push(
      this.loadWithRetryAndMetrics(
        "reviews",
        () => this.reviewService.getReviews(product.id, { limit: 50 }),
        { productId: product.id.substring(0, 8), operationId }
      )
    );

    // Recommendations (always load)
    promises.push(
      this.loadWithRetryAndMetrics(
        "recommendations",
        () =>
          this.recommendationService.getSimilarProducts(
            product.category,
            product.priceRange,
            { limit: 10 }
          ),
        { category: product.category, operationId }
      )
    );

    // Inventory (always load)
    promises.push(
      this.loadWithRetryAndMetrics(
        "inventory",
        () => this.inventoryService.checkStock(product.id),
        { productId: product.id.substring(0, 8), operationId }
      )
    );

    // Shipping (only for physical products)
    if (product.type === "physical") {
      promises.push(
        this.loadWithRetryAndMetrics(
          "shipping",
          () =>
            this.shippingService.getDeliveryOptions(
              product.weight,
              product.dimensions
            ),
          { productType: product.type, operationId }
        )
      );
    } else {
      promises.push(Promise.resolve(null));
    }

    return Promise.all(promises);
  }

  private async loadWithRetryAndMetrics<T>(
    operationName: string,
    operation: () => Promise<T>,
    tags: Record<string, string>,
    maxRetries: number = 2
  ): Promise<T> {
    let lastError: Error;

    for (let attempt = 1; attempt <= maxRetries + 1; attempt++) {
      const startTime = Date.now();

      try {
        this.metrics.incrementCounter(`${operationName}_attempts`, {
          ...tags,
          attempt: attempt.toString(),
        });

        const result = await operation();
        const duration = Date.now() - startTime;

        this.metrics.recordTimer(`${operationName}_duration`, duration, {
          ...tags,
          status: "success",
          attempt: attempt.toString(),
        });

        if (attempt > 1) {
          this.logger.info(`${operationName} succeeded on retry`, {
            ...tags,
            attempt,
            duration,
          });
        }

        return result;
      } catch (error) {
        const duration = Date.now() - startTime;
        lastError = error as Error;

        this.metrics.recordTimer(`${operationName}_duration`, duration, {
          ...tags,
          status: "error",
          attempt: attempt.toString(),
        });

        this.metrics.incrementCounter(`${operationName}_errors`, {
          ...tags,
          error_type: error.constructor.name,
          attempt: attempt.toString(),
        });

        if (attempt <= maxRetries) {
          const backoffDelay = Math.pow(2, attempt - 1) * 100; // Exponential backoff
          this.logger.warn(`${operationName} failed, retrying`, {
            ...tags,
            attempt,
            error: error.message,
            retryIn: backoffDelay,
          });

          await new Promise((resolve) => setTimeout(resolve, backoffDelay));
        } else {
          this.logger.error(
            `${operationName} failed after all retries`,
            error as Error,
            {
              ...tags,
              totalAttempts: attempt,
            }
          );
        }
      }
    }

    throw lastError!;
  }
}

// Health check endpoint with detailed system metrics
export class HealthCheckService {
  constructor(
    private metrics: IPerformanceMetrics,
    private db: Database,
    private redis: RedisClient
  ) {}

  async getHealthStatus(): Promise<HealthStatus> {
    const checks = await Promise.allSettled([
      this.checkDatabase(),
      this.checkRedis(),
      this.checkMemoryUsage(),
      this.checkDiskSpace(),
      this.checkExternalDependencies(),
    ]);

    const [dbCheck, redisCheck, memoryCheck, diskCheck, dependencyCheck] =
      checks;

    const overallHealth = checks.every(
      (check) => check.status === "fulfilled" && check.value.healthy
    );

    const healthStatus: HealthStatus = {
      healthy: overallHealth,
      timestamp: new Date(),
      checks: {
        database:
          dbCheck.status === "fulfilled"
            ? dbCheck.value
            : { healthy: false, message: "Check failed" },
        redis:
          redisCheck.status === "fulfilled"
            ? redisCheck.value
            : { healthy: false, message: "Check failed" },
        memory:
          memoryCheck.status === "fulfilled"
            ? memoryCheck.value
            : { healthy: false, message: "Check failed" },
        disk:
          diskCheck.status === "fulfilled"
            ? diskCheck.value
            : { healthy: false, message: "Check failed" },
        dependencies:
          dependencyCheck.status === "fulfilled"
            ? dependencyCheck.value
            : { healthy: false, message: "Check failed" },
      },
      metadata: {
        uptime: process.uptime(),
        nodeVersion: process.version,
        platform: process.platform,
        environment: process.env.NODE_ENV,
      },
    };

    // Record health metrics
    this.metrics.recordGauge("system_health_overall", overallHealth ? 1 : 0);
    this.metrics.recordGauge("system_uptime_seconds", process.uptime());

    return healthStatus;
  }

  private async checkDatabase(): Promise<ComponentHealth> {
    try {
      const start = Date.now();
      await this.db.query("SELECT 1");
      const responseTime = Date.now() - start;

      this.metrics.recordTimer("health_check_database", responseTime);
      this.metrics.recordGauge("database_health", 1);

      return {
        healthy: responseTime < 1000, // Healthy if responds within 1 second
        responseTime,
        message: responseTime < 1000 ? "OK" : "Slow response",
      };
    } catch (error) {
      this.metrics.recordGauge("database_health", 0);
      return {
        healthy: false,
        message: `Database check failed: ${error.message}`,
      };
    }
  }

  private async checkRedis(): Promise<ComponentHealth> {
    try {
      const start = Date.now();
      await this.redis.ping();
      const responseTime = Date.now() - start;

      this.metrics.recordTimer("health_check_redis", responseTime);
      this.metrics.recordGauge("redis_health", 1);

      return {
        healthy: responseTime < 500,
        responseTime,
        message: responseTime < 500 ? "OK" : "Slow response",
      };
    } catch (error) {
      this.metrics.recordGauge("redis_health", 0);
      return {
        healthy: false,
        message: `Redis check failed: ${error.message}`,
      };
    }
  }

  private async checkMemoryUsage(): Promise<ComponentHealth> {
    const memUsage = process.memoryUsage();
    const totalMemory = memUsage.heapTotal;
    const usedMemory = memUsage.heapUsed;
    const memoryUsagePercent = (usedMemory / totalMemory) * 100;

    this.metrics.recordGauge("memory_usage_bytes", usedMemory);
    this.metrics.recordGauge("memory_usage_percent", memoryUsagePercent);

    return {
      healthy: memoryUsagePercent < 90, // Healthy if using less than 90% of heap
      responseTime: 0,
      message: `Memory usage: ${memoryUsagePercent.toFixed(1)}%`,
      metadata: {
        heapUsed: usedMemory,
        heapTotal: totalMemory,
        external: memUsage.external,
        rss: memUsage.rss,
      },
    };
  }

  private async checkDiskSpace(): Promise<ComponentHealth> {
    try {
      const stats = await fs.promises.statfs(".");
      const totalSpace = stats.blocks * stats.bsize;
      const freeSpace = stats.bavail * stats.bsize;
      const usedSpacePercent = ((totalSpace - freeSpace) / totalSpace) * 100;

      this.metrics.recordGauge("disk_usage_percent", usedSpacePercent);
      this.metrics.recordGauge("disk_free_bytes", freeSpace);

      return {
        healthy: usedSpacePercent < 85, // Healthy if disk less than 85% full
        responseTime: 0,
        message: `Disk usage: ${usedSpacePercent.toFixed(1)}%`,
        metadata: {
          totalSpace,
          freeSpace,
          usedSpacePercent,
        },
      };
    } catch (error) {
      return {
        healthy: false,
        message: `Disk check failed: ${error.message}`,
      };
    }
  }

  private async checkExternalDependencies(): Promise<ComponentHealth> {
    // Check critical external services
    const dependencyChecks = await Promise.allSettled([
      this.checkExternalService("payment-gateway", "https://api.stripe.com/v1"),
      this.checkExternalService("email-service", "https://api.sendgrid.com/v3"),
      this.checkExternalService("cdn", "https://cdn.example.com/health"),
    ]);

    const allHealthy = dependencyChecks.every(
      (check) => check.status === "fulfilled" && check.value.healthy
    );

    return {
      healthy: allHealthy,
      responseTime: 0,
      message: allHealthy
        ? "All dependencies healthy"
        : "Some dependencies unhealthy",
      metadata: dependencyChecks.map((check) =>
        check.status === "fulfilled" ? check.value : { healthy: false }
      ),
    };
  }

  private async checkExternalService(
    name: string,
    url: string
  ): Promise<{ name: string; healthy: boolean; responseTime?: number }> {
    try {
      const start = Date.now();
      const response = await fetch(url, {
        method: "HEAD",
        timeout: 5000, // 5 second timeout
      });
      const responseTime = Date.now() - start;

      const healthy = response.status < 400;
      this.metrics.recordGauge(`external_service_health`, healthy ? 1 : 0, {
        service: name,
      });
      this.metrics.recordTimer("external_service_response", responseTime, {
        service: name,
      });

      return { name, healthy, responseTime };
    } catch (error) {
      this.metrics.recordGauge(`external_service_health`, 0, { service: name });
      return { name, healthy: false };
    }
  }
}

// Supporting types
interface ProductDetails {
  product: Product;
  warranty?: Warranty;
  reviews: ProductReview[];
  recommendations: ProductRecommendation[];
  inventory?: InventoryInfo;
  shipping?: ShippingOptions;
}

interface HealthStatus {
  healthy: boolean;
  timestamp: Date;
  checks: Record<string, ComponentHealth>;
  metadata: Record<string, any>;
}

interface ComponentHealth {
  healthy: boolean;
  responseTime?: number;
  message: string;
  metadata?: Record<string, any>;
}

export class NotFoundError extends Error {
  constructor(message: string) {
    super(message);
    this.name = "NotFoundError";
  }
}

// The database nightmare that destroys performance
class ProductRepository {
  async searchProducts(filters: any, pagination: any) {
    // Unindexed query scanning millions of rows - RED FLAG #1
    let query = `
      SELECT p.*, c.name as category_name, b.name as brand_name,
             AVG(r.rating) as avg_rating, COUNT(r.id) as review_count
      FROM products p
      LEFT JOIN categories c ON p.category_id = c.id
      LEFT JOIN brands b ON p.brand_id = b.id
      LEFT JOIN reviews r ON p.id = r.product_id
      WHERE 1=1
    `;

    const params: any[] = [];
    let paramIndex = 1;

    // Dynamic query building without proper indexing strategy - RED FLAG #2
    if (filters.category) {
      query += ` AND c.name = $${paramIndex++}`;
      params.push(filters.category);
    }

    if (filters.priceRange) {
      query += ` AND p.price BETWEEN $${paramIndex++} AND $${paramIndex++}`;
      params.push(filters.priceRange.min, filters.priceRange.max);
    }

    if (filters.brand) {
      query += ` AND b.name LIKE $${paramIndex++}`; // LIKE without proper index - RED FLAG #3
      params.push(`%${filters.brand}%`);
    }

    if (filters.searchTerm) {
      // Full text search without search indexes - RED FLAG #4
      query += ` AND (p.name ILIKE $${paramIndex++} OR p.description ILIKE $${paramIndex++})`;
      params.push(`%${filters.searchTerm}%`, `%${filters.searchTerm}%`);
    }

    // Expensive aggregation without pre-computed values - RED FLAG #5
    query += ` GROUP BY p.id, c.name, b.name`;

    // Sorting by computed values - extremely slow - RED FLAG #6
    if (filters.sortBy === "rating") {
      query += ` ORDER BY avg_rating DESC`;
    } else if (filters.sortBy === "popularity") {
      query += ` ORDER BY review_count DESC`;
    }

    // Pagination without proper indexing - RED FLAG #7
    query += ` LIMIT $${paramIndex++} OFFSET $${paramIndex++}`;
    params.push(pagination.limit, pagination.offset);

    // This query will:
    // - Scan millions of rows every time
    // - Perform expensive JOINs and aggregations
    // - Take 5-30 seconds under load
    // - Lock tables and block other queries
    // - Use massive amounts of memory and CPU

    return await this.db.query(query, params);
  }

  async getProductWithDetails(productId: string) {
    // N+1 query problem - RED FLAG #8
    const product = await this.db.query(
      "SELECT * FROM products WHERE id = $1",
      [productId]
    );

    // Multiple separate queries instead of efficient JOINs
    const category = await this.db.query(
      "SELECT * FROM categories WHERE id = $1",
      [product.rows[0].category_id]
    );

    const reviews = await this.db.query(
      "SELECT * FROM reviews WHERE product_id = $1 ORDER BY created_at DESC",
      [productId]
    );

    const inventory = await this.db.query(
      "SELECT * FROM inventory WHERE product_id = $1",
      [productId]
    );

    // What should be 1 query becomes 4+ queries
    return {
      product: product.rows[0],
      category: category.rows[0],
      reviews: reviews.rows,
      inventory: inventory.rows[0],
    };
  }
}

// High-performance database operations with monitoring
export class OptimizedProductRepository {
  constructor(
    private db: Database,
    private metrics: IPerformanceMetrics,
    private logger: ILogger,
    private cache: ICache
  ) {}

  async searchProducts(
    filters: ProductSearchFilters,
    pagination: PaginationOptions
  ): Promise<ProductSearchResult> {
    const cacheKey = this.generateCacheKey(
      "product_search",
      filters,
      pagination
    );
    const startTime = Date.now();

    // Check cache first
    const cachedResult = await this.cache.get(cacheKey);
    if (cachedResult) {
      this.metrics.incrementCounter("product_search_cache_hit");
      return JSON.parse(cachedResult);
    }

    this.metrics.incrementCounter("product_search_cache_miss");

    try {
      // Optimized query with proper indexes and materialized views
      const { query, params } = this.buildOptimizedSearchQuery(
        filters,
        pagination
      );

      this.logger.info("Executing product search query", {
        filters,
        pagination,
        estimatedComplexity: this.estimateQueryComplexity(filters),
      });

      const result = await this.db.query(query, params);
      const queryTime = Date.now() - startTime;

      this.metrics.recordTimer("product_search_query", queryTime, {
        has_search_term: filters.searchTerm ? "true" : "false",
        has_filters: Object.keys(filters).length > 1 ? "true" : "false",
        result_count: result.rows.length.toString(),
      });

      const searchResult: ProductSearchResult = {
        products: result.rows.map((row) => this.mapProductRow(row)),
        totalCount: parseInt(result.rows[0]?.total_count || "0"),
        pageInfo: {
          hasNextPage:
            pagination.offset + pagination.limit <
            parseInt(result.rows[0]?.total_count || "0"),
          hasPreviousPage: pagination.offset > 0,
          currentPage: Math.floor(pagination.offset / pagination.limit) + 1,
        },
        executionTime: queryTime,
        fromCache: false,
      };

      // Cache result for future requests
      await this.cache.setex(cacheKey, 300, JSON.stringify(searchResult)); // 5 minute cache

      return searchResult;
    } catch (error) {
      this.metrics.incrementCounter("product_search_errors", {
        error_type: error.constructor.name,
      });

      this.logger.error("Product search query failed", error as Error, {
        filters,
        pagination,
        duration: Date.now() - startTime,
      });

      throw error;
    }
  }

  private buildOptimizedSearchQuery(
    filters: ProductSearchFilters,
    pagination: PaginationOptions
  ): { query: string; params: any[] } {
    const params: any[] = [];
    let paramIndex = 1;

    // Use materialized view for fast aggregated data
    let baseQuery = `
      SELECT 
        ps.id,
        ps.name,
        ps.description,
        ps.price,
        ps.category_name,
        ps.brand_name,
        ps.avg_rating,
        ps.review_count,
        ps.in_stock,
        ps.created_at,
        COUNT(*) OVER() as total_count
      FROM product_search_view ps
      WHERE ps.active = true
    `;

    // Add filters using properly indexed columns
    if (filters.categoryId) {
      baseQuery += ` AND ps.category_id = $${paramIndex++}`;
      params.push(filters.categoryId);
    }

    if (filters.brandId) {
      baseQuery += ` AND ps.brand_id = $${paramIndex++}`;
      params.push(filters.brandId);
    }

    if (filters.priceRange) {
      baseQuery += ` AND ps.price BETWEEN $${paramIndex++} AND $${paramIndex++}`;
      params.push(filters.priceRange.min, filters.priceRange.max);
    }

    if (filters.inStockOnly) {
      baseQuery += ` AND ps.in_stock = true`;
    }

    if (filters.minRating) {
      baseQuery += ` AND ps.avg_rating >= $${paramIndex++}`;
      params.push(filters.minRating);
    }

    // Full-text search using proper search indexes (GIN/GiST)
    if (filters.searchTerm) {
      baseQuery += ` AND ps.search_vector @@ plainto_tsquery($${paramIndex++})`;
      params.push(filters.searchTerm);
    }

    // Efficient sorting using indexed columns
    if (filters.sortBy) {
      switch (filters.sortBy) {
        case "price_asc":
          baseQuery += ` ORDER BY ps.price ASC`;
          break;
        case "price_desc":
          baseQuery += ` ORDER BY ps.price DESC`;
          break;
        case "rating":
          baseQuery += ` ORDER BY ps.avg_rating DESC, ps.review_count DESC`;
          break;
        case "newest":
          baseQuery += ` ORDER BY ps.created_at DESC`;
          break;
        case "popularity":
          baseQuery += ` ORDER BY ps.review_count DESC, ps.avg_rating DESC`;
          break;
        default:
          baseQuery += ` ORDER BY ps.created_at DESC`;
      }
    } else {
      baseQuery += ` ORDER BY ps.created_at DESC`;
    }

    // Efficient pagination
    baseQuery += ` LIMIT $${paramIndex++} OFFSET $${paramIndex++}`;
    params.push(pagination.limit, pagination.offset);

    return { query: baseQuery, params };
  }

  async getProductWithDetails(
    productId: string
  ): Promise<ProductWithDetails | null> {
    const cacheKey = `product_details:${productId}`;
    const startTime = Date.now();

    // Check cache first
    const cached = await this.cache.get(cacheKey);
    if (cached) {
      this.metrics.incrementCounter("product_details_cache_hit");
      return JSON.parse(cached);
    }

    this.metrics.incrementCounter("product_details_cache_miss");

    try {
      // Single optimized query with all necessary JOINs
      const query = `
        SELECT 
          p.id, p.name, p.description, p.price, p.created_at, p.updated_at,
          c.id as category_id, c.name as category_name, c.slug as category_slug,
          b.id as brand_id, b.name as brand_name, b.logo_url as brand_logo,
          i.quantity as stock_quantity, i.reserved as stock_reserved,
          ps.avg_rating, ps.review_count, ps.total_sales
        FROM products p
        INNER JOIN categories c ON p.category_id = c.id
        INNER JOIN brands b ON p.brand_id = b.id
        LEFT JOIN inventory i ON p.id = i.product_id
        LEFT JOIN product_stats ps ON p.id = ps.product_id
        WHERE p.id = $1 AND p.active = true
      `;

      const result = await this.db.query(query, [productId]);
      const queryTime = Date.now() - startTime;

      this.metrics.recordTimer("product_details_query", queryTime);

      if (result.rows.length === 0) {
        return null;
      }

      const productData = result.rows[0];

      // Load recent reviews in parallel (if needed)
      const [reviews, relatedProducts] = await Promise.all([
        this.getRecentReviews(productId, 10),
        this.getRelatedProducts(productData.category_id, productId, 5),
      ]);

      const productWithDetails: ProductWithDetails = {
        id: productData.id,
        name: productData.name,
        description: productData.description,
        price: productData.price,
        category: {
          id: productData.category_id,
          name: productData.category_name,
          slug: productData.category_slug,
        },
        brand: {
          id: productData.brand_id,
          name: productData.brand_name,
          logoUrl: productData.brand_logo,
        },
        inventory: {
          quantity: productData.stock_quantity || 0,
          reserved: productData.stock_reserved || 0,
          available:
            (productData.stock_quantity || 0) -
            (productData.stock_reserved || 0),
        },
        stats: {
          avgRating: productData.avg_rating || 0,
          reviewCount: productData.review_count || 0,
          totalSales: productData.total_sales || 0,
        },
        reviews,
        relatedProducts,
        createdAt: productData.created_at,
        updatedAt: productData.updated_at,
      };

      // Cache for 10 minutes
      await this.cache.setex(cacheKey, 600, JSON.stringify(productWithDetails));

      return productWithDetails;
    } catch (error) {
      this.metrics.incrementCounter("product_details_errors");
      this.logger.error("Failed to load product details", error as Error, {
        productId,
        duration: Date.now() - startTime,
      });
      throw error;
    }
  }

  private async getRecentReviews(
    productId: string,
    limit: number
  ): Promise<ProductReview[]> {
    // Optimized query for reviews with proper indexing
    const query = `
      SELECT 
        r.id, r.rating, r.title, r.content, r.created_at,
        u.name as reviewer_name, u.verified_purchase
      FROM reviews r
      INNER JOIN users u ON r.user_id = u.id
      WHERE r.product_id = $1 AND r.approved = true
      ORDER BY r.created_at DESC
      LIMIT $2
    `;

    const result = await this.db.query(query, [productId, limit]);
    return result.rows.map((row) => ({
      id: row.id,
      rating: row.rating,
      title: row.title,
      content: row.content,
      reviewerName: row.reviewer_name,
      verifiedPurchase: row.verified_purchase,
      createdAt: row.created_at,
    }));
  }

  private async getRelatedProducts(
    categoryId: string,
    excludeId: string,
    limit: number
  ): Promise<RelatedProduct[]> {
    // Optimized query for related products
    const query = `
      SELECT p.id, p.name, p.price, ps.avg_rating, ps.review_count
      FROM products p
      LEFT JOIN product_stats ps ON p.id = ps.product_id
      WHERE p.category_id = $1 AND p.id != $2 AND p.active = true
      ORDER BY ps.total_sales DESC, ps.avg_rating DESC
      LIMIT $3
    `;

    const result = await this.db.query(query, [categoryId, excludeId, limit]);
    return result.rows.map((row) => ({
      id: row.id,
      name: row.name,
      price: row.price,
      avgRating: row.avg_rating || 0,
      reviewCount: row.review_count || 0,
    }));
  }

  private generateCacheKey(prefix: string, ...args: any[]): string {
    const hash = crypto
      .createHash("md5")
      .update(JSON.stringify(args))
      .digest("hex");
    return `${prefix}:${hash}`;
  }

  private estimateQueryComplexity(
    filters: ProductSearchFilters
  ): "low" | "medium" | "high" {
    let complexity = 0;

    if (filters.searchTerm) complexity += 2;
    if (filters.priceRange) complexity += 1;
    if (filters.categoryId) complexity += 0.5;
    if (filters.brandId) complexity += 0.5;

    if (complexity < 1) return "low";
    if (complexity < 3) return "medium";
    return "high";
  }

  private mapProductRow(row: any): ProductSearchResultItem {
    return {
      id: row.id,
      name: row.name,
      price: row.price,
      categoryName: row.category_name,
      brandName: row.brand_name,
      avgRating: row.avg_rating || 0,
      reviewCount: row.review_count || 0,
      inStock: row.in_stock,
      createdAt: row.created_at,
    };
  }
}

// Database connection pool optimization
export class OptimizedDatabase {
  private pool: Pool;

  constructor(config: DatabaseConfig) {
    this.pool = new Pool({
      connectionString: config.url,
      // Connection pool optimization
      max: config.maxConnections || 20, // Maximum number of clients
      min: config.minConnections || 5, // Minimum number of clients
      idleTimeoutMillis: 30000, // Close idle clients after 30 seconds
      connectionTimeoutMillis: 10000, // Return error after 10 seconds if no connection available
      maxUses: 7500, // Close a connection after it has been used 7500 times

      // Query optimization
      statement_timeout: 30000, // Cancel queries after 30 seconds
      query_timeout: 25000, // Client-side query timeout

      // SSL configuration
      ssl: config.ssl
        ? {
            rejectUnauthorized: false,
          }
        : false,
    });

    // Monitor pool events
    this.pool.on("connect", (client) => {
      console.log("New database client connected");
    });

    this.pool.on("error", (err, client) => {
      console.error("Database pool error:", err);
    });

    // Graceful shutdown
    process.on("SIGINT", () => {
      this.pool.end(() => {
        console.log("Database pool closed");
        process.exit(0);
      });
    });
  }

  async query(text: string, params?: any[]): Promise<QueryResult> {
    const start = Date.now();
    const client = await this.pool.connect();

    try {
      const result = await client.query(text, params);
      const duration = Date.now() - start;

      // Log slow queries
      if (duration > 1000) {
        console.warn("Slow query detected:", {
          duration,
          query: text.substring(0, 100),
          rowCount: result.rowCount,
        });
      }

      return result;
    } finally {
      client.release();
    }
  }

  async getPoolStatus(): Promise<PoolStatus> {
    return {
      totalCount: this.pool.totalCount,
      idleCount: this.pool.idleCount,
      waitingCount: this.pool.waitingCount,
    };
  }
}

// Supporting interfaces
interface ProductSearchFilters {
  searchTerm?: string;
  categoryId?: string;
  brandId?: string;
  priceRange?: { min: number; max: number };
  minRating?: number;
  inStockOnly?: boolean;
  sortBy?: "price_asc" | "price_desc" | "rating" | "newest" | "popularity";
}

interface PaginationOptions {
  limit: number;
  offset: number;
}

interface ProductSearchResult {
  products: ProductSearchResultItem[];
  totalCount: number;
  pageInfo: PageInfo;
  executionTime: number;
  fromCache: boolean;
}

interface ProductWithDetails {
  id: string;
  name: string;
  description: string;
  price: number;
  category: Category;
  brand: Brand;
  inventory: InventoryInfo;
  stats: ProductStats;
  reviews: ProductReview[];
  relatedProducts: RelatedProduct[];
  createdAt: Date;
  updatedAt: Date;
}

interface DatabaseConfig {
  url: string;
  maxConnections?: number;
  minConnections?: number;
  ssl?: boolean;
}

interface PoolStatus {
  totalCount: number;
  idleCount: number;
  waitingCount: number;
}