import { getSupabaseAdmin } from '@/lib/supabase/server';
import { formatABN } from '@/lib/abn-utils';
import { logger } from '@/lib/logger';

/**
 * ABN Cache Service
 * 
 * Provides caching functionality for ABN lookups to reduce API calls to ABR.
 * Cached entries expire based on ABN status:
 * - Valid ABNs (Active): 30 days expiry
 * - Invalid/Not found ABNs: 7 days expiry
 */

export interface CachedABNData {
  abn: string;
  companyName: string;
  status: string;
  statusDate?: string;
  state?: string;
  postcode?: string;
  entityType?: string;
  entityTypeCode?: string;
  gstRegistered?: boolean;
  acn?: string;
  abrResponse?: any;
}

export interface ABNCacheEntry extends CachedABNData {
  id: string;
  cachedAt: string;
  updatedAt: string;
  expiresAt: string;
  lookupCount: number;
  lastLookupAt: string;
}

/**
 * Get cached ABN data if available and not expired
 */
export async function getCachedABN(abn: string): Promise<ABNCacheEntry | null> {
  try {
    const admin = getSupabaseAdmin();
    const formattedABN = formatABN(abn);
    
    // Use the database function for cache lookup
    const { data, error } = await admin
      .rpc('get_cached_abn', { p_abn: formattedABN })
      .single();
    
    if (error) {
      logger.error('[ABN Cache] Error retrieving cached ABN:', error);
      return null;
    }
    
    if (!data) {
      return null;
    }

    // Increment lookup count for cache hit statistics
    await incrementABNLookup(formattedABN);

    const row = data as Record<string, any>;
    return {
      id: row.id,
      abn: row.abn,
      companyName: row.company_name,
      status: row.status,
      statusDate: row.status_date,
      state: row.state,
      postcode: row.postcode,
      entityType: row.entity_type,
      entityTypeCode: row.entity_type_code,
      gstRegistered: row.gst_registered,
      acn: row.acn,
      cachedAt: row.cached_at,
      updatedAt: row.updated_at,
      expiresAt: row.expires_at,
      lookupCount: row.lookup_count,
      lastLookupAt: row.last_lookup_at,
      abrResponse: row.abr_response,
    };
  } catch (error) {
    logger.error('[ABN Cache] Unexpected error in getCachedABN:', error);
    return null;
  }
}

/**
 * Cache ABN data from ABR response
 */
export async function cacheABNData(data: CachedABNData): Promise<void> {
  try {
    const admin = getSupabaseAdmin();
    const formattedABN = formatABN(data.abn);
    
    // Calculate expiry based on status
    const isActive = data.status.toLowerCase() === 'active';
    const expiryDays = isActive ? 30 : 7;
    const expiresAt = new Date(Date.now() + expiryDays * 24 * 60 * 60 * 1000).toISOString();
    
    const cacheData = {
      abn: formattedABN,
      company_name: data.companyName,
      status: data.status,
      status_date: data.statusDate,
      state: data.state,
      postcode: data.postcode,
      entity_type: data.entityType,
      entity_type_code: data.entityTypeCode,
      gst_registered: data.gstRegistered,
      acn: data.acn,
      abr_response: data.abrResponse,
      expires_at: expiresAt,
    };
    
    // Use upsert to handle both insert and update cases
    const { error } = await admin
      .from('abn_cache')
      .upsert(cacheData, {
        onConflict: 'abn',
        ignoreDuplicates: false,
      });
    
    if (error) {
      logger.error('[ABN Cache] Error caching ABN data:', error);
      throw error;
    }
    
    logger.info(`[ABN Cache] Successfully cached ABN: ${formattedABN}`);
  } catch (error) {
    logger.error('[ABN Cache] Unexpected error in cacheABNData:', error);
    throw error;
  }
}

/**
 * Increment lookup count for cache hit statistics
 */
async function incrementABNLookup(abn: string): Promise<void> {
  try {
    const admin = getSupabaseAdmin();
    
    await admin.rpc('increment_abn_lookup', { p_abn: abn });
  } catch (error) {
    // Don't throw error for stats update failure
    logger.error('[ABN Cache] Error incrementing lookup count:', error);
  }
}

/**
 * Clean up expired cache entries
 */
export async function cleanupExpiredABNCache(): Promise<number> {
  try {
    const admin = getSupabaseAdmin();
    
    const { data, error } = await admin
      .rpc('cleanup_expired_abn_cache')
      .single();
    
    if (error) {
      logger.error('[ABN Cache] Error cleaning up expired entries:', error);
      return 0;
    }
    
    const deletedCount = (data as Record<string, any>)?.deleted_count || 0;
    logger.info(`[ABN Cache] Cleaned up ${deletedCount} expired entries`);
    return deletedCount;
  } catch (error) {
    logger.error('[ABN Cache] Unexpected error in cleanup:', error);
    return 0;
  }
}

/**
 * Get cache statistics for monitoring
 */
export async function getABNCacheStats(): Promise<{
  totalCached: number;
  activeEntries: number;
  expiredEntries: number;
  avgLookups: number;
  maxLookups: number;
}> {
  try {
    const admin = getSupabaseAdmin();
    
    const { data, error } = await admin
      .rpc('get_abn_cache_stats')
      .single();
    
    if (error) {
      logger.error('[ABN Cache] Error getting cache stats:', error);
      return {
        totalCached: 0,
        activeEntries: 0,
        expiredEntries: 0,
        avgLookups: 0,
        maxLookups: 0,
      };
    }
    
    const stats = data as Record<string, any>;
    return {
      totalCached: stats?.total_cached || 0,
      activeEntries: stats?.active_entries || 0,
      expiredEntries: stats?.expired_entries || 0,
      avgLookups: parseFloat(stats?.avg_lookups) || 0,
      maxLookups: stats?.max_lookups || 0,
    };
  } catch (error) {
    logger.error('[ABN Cache] Unexpected error getting stats:', error);
    return {
      totalCached: 0,
      activeEntries: 0,
      expiredEntries: 0,
      avgLookups: 0,
      maxLookups: 0,
    };
  }
}