/**
 * Optimisations de Performance MediFlash SOS+
 * Optimisations critiques pour base de données et cache
 */

import { db } from './db';
import { sql } from 'drizzle-orm';

interface CacheOptions {
  key: string;
  duration: number; // in seconds
}

// Cache en mémoire simple pour les données fréquentes
class MemoryCache {
  private cache = new Map<string, { data: any; expires: number }>();

  set(key: string, data: any, duration: number) {
    const expires = Date.now() + duration * 1000;
    this.cache.set(key, { data, expires });
  }

  get(key: string) {
    const item = this.cache.get(key);
    if (!item) return null;
    
    if (Date.now() > item.expires) {
      this.cache.delete(key);
      return null;
    }
    
    return item.data;
  }

  clear(pattern?: string) {
    if (!pattern) {
      this.cache.clear();
      return;
    }
    
    Array.from(this.cache.keys()).forEach(key => {
      if (key.includes(pattern)) {
        this.cache.delete(key);
      }
    });
  }

  size() {
    return this.cache.size;
  }
}

// Instance globale du cache
export const performanceCache = new MemoryCache();

/**
 * Décorateur pour mettre en cache les résultats de méthodes
 */
export function cached(options: CacheOptions) {
  return function (target: any, propertyKey: string, descriptor: PropertyDescriptor) {
    const originalMethod = descriptor.value;

    descriptor.value = async function (...args: any[]) {
      const cacheKey = `${options.key}:${JSON.stringify(args)}`;
      
      // Vérifier le cache
      const cached = performanceCache.get(cacheKey);
      if (cached !== null) {
        console.log(`[CACHE HIT] ${cacheKey}`);
        return cached;
      }

      // Exécuter la méthode originale
      console.log(`[CACHE MISS] ${cacheKey}`);
      const result = await originalMethod.apply(this, args);
      
      // Sauvegarder en cache
      performanceCache.set(cacheKey, result, options.duration);
      return result;
    };

    return descriptor;
  };
}

/**
 * Requête optimisée pour trouver les pharmacies proches
 * Utilise des index spatiaux et limite les résultats
 */
export async function findNearbyPharmaciesOptimized(
  lat: number, 
  lng: number, 
  medicineName: string, 
  exclude: string[] = [],
  maxDistance: number = 15,
  limit: number = 10
) {
  const cacheKey = `nearby_pharmacies:${lat}:${lng}:${medicineName}:${maxDistance}:${limit}`;
  
  // Vérifier le cache (5 minutes pour les pharmacies)
  const cached = performanceCache.get(cacheKey);
  if (cached !== null) {
    return cached;
  }

  // Utiliser une approche en deux étapes pour optimiser
  // 1. D'abord filtrer par rectangle approximatif (plus rapide)
  const roughDistance = maxDistance / 111; // Approximation en degrés
  
  const result = await db.execute(sql`
    WITH nearby_pharmacies AS (
      SELECT p.id, p.name, p.address, p.lat, p.lng, p.verified,
             (6371 * acos(
               cos(radians(${lat})) * 
               cos(radians(p.lat)) * 
               cos(radians(p.lng) - radians(${lng})) + 
               sin(radians(${lat})) * 
               sin(radians(p.lat))
             )) as distance
      FROM pharmacies p
      WHERE p.verified = true
        AND p.lat BETWEEN ${lat - roughDistance} AND ${lat + roughDistance}
        AND p.lng BETWEEN ${lng - roughDistance} AND ${lng + roughDistance}
        ${exclude.length > 0 ? sql`AND p.id NOT IN (${exclude})` : sql``}
    ),
    pharmacy_stock AS (
      SELECT np.*, ps.stock, ps.unit_price
      FROM nearby_pharmacies np
      LEFT JOIN pharmacy_stock ps ON ps.pharmacy_id = np.id
      LEFT JOIN medicines m ON m.id = ps.medicine_id
      WHERE np.distance <= ${maxDistance}
        AND (m.name ILIKE ${'%' + medicineName + '%'} OR m.generic_name ILIKE ${'%' + medicineName + '%'})
        AND ps.stock > 0
    )
    SELECT * FROM pharmacy_stock
    ORDER BY distance ASC
    LIMIT ${limit}
  `);

  performanceCache.set(cacheKey, result.rows, 300); // Cache 5 minutes
  return result.rows;
}

/**
 * Requête optimisée pour les statistiques admin
 * Utilise des requêtes pré-agrégées et cache longue durée
 */
export async function getAdminStatsOptimized() {
  const cacheKey = 'admin_stats';
  
  // Cache plus long pour les statistiques (10 minutes)
  const cached = performanceCache.get(cacheKey);
  if (cached !== null) {
    return cached;
  }

  // Exécuter toutes les requêtes en parallèle pour optimiser
  const [
    totalUsers,
    usersByRole,
    totalOrders,
    ordersByStatus,
    totalTransactions,
    verifiedPharmacies,
    unverifiedPharmacies
  ] = await Promise.all([
    db.execute(sql`SELECT COUNT(*) as count FROM users`),
    db.execute(sql`SELECT role, COUNT(*) as count FROM users GROUP BY role`),
    db.execute(sql`SELECT COUNT(*) as count FROM orders`),
    db.execute(sql`SELECT status, COUNT(*) as count FROM orders GROUP BY status`),
    db.execute(sql`SELECT SUM(amount) as total, COUNT(*) as count FROM transactions`),
    db.execute(sql`SELECT COUNT(*) as count FROM pharmacies WHERE verified = true`),
    db.execute(sql`SELECT COUNT(*) as count FROM pharmacies WHERE verified = false`)
  ]);

  const result = {
    users: {
      total: Number(totalUsers.rows[0]?.count || 0),
      byRole: usersByRole.rows.reduce((acc: any, item: any) => {
        acc[item.role] = Number(item.count);
        return acc;
      }, {})
    },
    orders: {
      total: Number(totalOrders.rows[0]?.count || 0),
      byStatus: ordersByStatus.rows.reduce((acc: any, item: any) => {
        acc[item.status] = Number(item.count);
        return acc;
      }, {})
    },
    finance: {
      totalAmount: Number(totalTransactions.rows[0]?.total || 0),
      totalTransactions: Number(totalTransactions.rows[0]?.count || 0)
    },
    pharmacies: {
      verified: Number(verifiedPharmacies.rows[0]?.count || 0),
      unverified: Number(unverifiedPharmacies.rows[0]?.count || 0)
    }
  };

  performanceCache.set(cacheKey, result, 600); // Cache 10 minutes
  return result;
}

/**
 * Pagination efficace pour les listes
 */
export interface PaginationOptions {
  page: number;
  limit: number;
  sortBy?: string;
  sortOrder?: 'asc' | 'desc';
}

export interface PaginatedResult<T> {
  data: T[];
  pagination: {
    page: number;
    limit: number;
    total: number;
    totalPages: number;
    hasNext: boolean;
    hasPrev: boolean;
  };
}

/**
 * Helper pour créer des requêtes paginées optimisées
 */
export function createPaginatedQuery<T>(
  baseQuery: any,
  options: PaginationOptions
): { query: any; offset: number } {
  const offset = (options.page - 1) * options.limit;
  
  let query = baseQuery;
  
  if (options.sortBy) {
    const sortOrder = options.sortOrder === 'desc' ? 'DESC' : 'ASC';
    query = query.orderBy(sql.raw(`${options.sortBy} ${sortOrder}`));
  }
  
  query = query.limit(options.limit).offset(offset);
  
  return { query, offset };
}

/**
 * Middleware pour mesurer les performances des requêtes
 */
export function measureQueryTime(queryName: string) {
  return function (target: any, propertyKey: string, descriptor: PropertyDescriptor) {
    const originalMethod = descriptor.value;

    descriptor.value = async function (...args: any[]) {
      const start = Date.now();
      
      try {
        const result = await originalMethod.apply(this, args);
        const duration = Date.now() - start;
        
        if (duration > 1000) { // Log seulement les requêtes lentes (>1s)
          console.warn(`[SLOW QUERY] ${queryName}: ${duration}ms`);
        } else {
          console.log(`[QUERY] ${queryName}: ${duration}ms`);
        }
        
        return result;
      } catch (error) {
        const duration = Date.now() - start;
        console.error(`[QUERY ERROR] ${queryName}: ${duration}ms`, error);
        throw error;
      }
    };

    return descriptor;
  };
}

/**
 * Fonction pour nettoyer le cache périodiquement
 */
export function startCacheCleanup() {
  setInterval(() => {
    const sizeBefore = performanceCache.size();
    // Le nettoyage est automatique dans notre implémentation
    console.log(`[CACHE CLEANUP] Cache size: ${sizeBefore} items`);
  }, 300000); // Tous les 5 minutes
}

/**
 * Statistiques de performance du cache
 */
export function getCacheStats() {
  return {
    size: performanceCache.size(),
    timestamp: new Date().toISOString()
  };
}