import { Pool } from 'pg';
import { getDatabasePool } from '../database/client';
import { logger } from '../utils/logger';

export interface VolumeMetrics {
  volume5m: number;
  volume1h: number;
  volume24h: number;
  volume7d: number;
  volume30d: number;
  txCount5m: number;
  txCount1h: number;
  txCount24h: number;
}

export class VolumeCalculator {
  private pool: Pool;
  private static readonly missingRelationWarnings = new Set<string>();

  constructor() {
    this.pool = getDatabasePool();
  }

  private isMissingRelationError(error: unknown): boolean {
    if (!error || typeof error !== 'object') {
      return false;
    }

    const code = (error as { code?: string }).code;
    const message = (error as { message?: string }).message || '';
    return code === '42P01' || (message.includes('relation "') && message.includes('" does not exist'));
  }

  private getMissingRelationName(error: unknown): string {
    const message = typeof error === 'object' && error && 'message' in error
      ? String((error as { message?: string }).message || '')
      : '';
    const match = message.match(/relation "([^"]+)"/);
    return match?.[1] || 'unknown relation';
  }

  private logMissingRelationOnce(operation: string, error: unknown): void {
    const relation = this.getMissingRelationName(error);
    const key = `${operation}:${relation}`;
    if (VolumeCalculator.missingRelationWarnings.has(key)) {
      return;
    }

    VolumeCalculator.missingRelationWarnings.add(key);
    logger.info(`Skipping ${operation}; relation "${relation}" is not available yet`, {
      operation,
      relation,
    });
  }

  /**
   * Calculate volume metrics for a token across all pools
   */
  async calculateTokenVolume(
    chainId: number,
    tokenAddress: string,
    now: Date = new Date()
  ): Promise<VolumeMetrics> {
    try {
      const intervals = {
        '5m': new Date(now.getTime() - 5 * 60 * 1000),
        '1h': new Date(now.getTime() - 60 * 60 * 1000),
        '24h': new Date(now.getTime() - 24 * 60 * 60 * 1000),
        '7d': new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000),
        '30d': new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000),
      };

      // Get all pools for this token
      const poolsResult = await this.pool.query(
        `SELECT pool_address FROM liquidity_pools
         WHERE chain_id = $1 AND (token0_address = $2 OR token1_address = $2)`,
        [chainId, tokenAddress.toLowerCase()]
      );

      const poolAddresses = poolsResult.rows.map((row) => row.pool_address);

      if (poolAddresses.length === 0) {
        return this.zeroMetrics();
      }

      // Calculate volume for each interval
      const [volume5m, volume1h, volume24h, volume7d, volume30d, txCounts] = await Promise.all([
        this.calculateVolumeForInterval(chainId, poolAddresses, tokenAddress, intervals['5m'], now),
        this.calculateVolumeForInterval(chainId, poolAddresses, tokenAddress, intervals['1h'], now),
        this.calculateVolumeForInterval(chainId, poolAddresses, tokenAddress, intervals['24h'], now),
        this.calculateVolumeForInterval(chainId, poolAddresses, tokenAddress, intervals['7d'], now),
        this.calculateVolumeForInterval(chainId, poolAddresses, tokenAddress, intervals['30d'], now),
        this.calculateTxCounts(chainId, poolAddresses, intervals, now),
      ]);

      return {
        volume5m,
        volume1h,
        volume24h,
        volume7d,
        volume30d,
        txCount5m: txCounts['5m'],
        txCount1h: txCounts['1h'],
        txCount24h: txCounts['24h'],
      };
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        this.logMissingRelationOnce('volume calculation', error);
        return this.zeroMetrics();
      }
      throw error;
    }
  }

  /**
   * Calculate volume for a specific interval
   */
  private async calculateVolumeForInterval(
    chainId: number,
    poolAddresses: string[],
    tokenAddress: string,
    from: Date,
    to: Date
  ): Promise<number> {
    if (poolAddresses.length === 0) return 0;

    const result = await this.pool.query(
      `SELECT COALESCE(SUM(amount_usd), 0) as total_volume
       FROM swap_events
       WHERE chain_id = $1
         AND pool_address = ANY($2)
         AND timestamp >= $3
         AND timestamp <= $4
         AND (token0_address = $5 OR token1_address = $5)`,
      [chainId, poolAddresses, from, to, tokenAddress.toLowerCase()]
    );

    return parseFloat(result.rows[0]?.total_volume || '0');
  }

  /**
   * Calculate transaction counts for different intervals
   */
  private async calculateTxCounts(
    chainId: number,
    poolAddresses: string[],
    intervals: Record<string, Date>,
    now: Date
  ): Promise<Record<string, number>> {
    if (poolAddresses.length === 0) {
      return { '5m': 0, '1h': 0, '24h': 0 };
    }

    const [count5m, count1h, count24h] = await Promise.all([
      this.pool.query(
        `SELECT COUNT(DISTINCT transaction_hash) as count
         FROM swap_events
         WHERE chain_id = $1
           AND pool_address = ANY($2)
           AND timestamp >= $3
           AND timestamp <= $4`,
        [chainId, poolAddresses, intervals['5m'], now]
      ),
      this.pool.query(
        `SELECT COUNT(DISTINCT transaction_hash) as count
         FROM swap_events
         WHERE chain_id = $1
           AND pool_address = ANY($2)
           AND timestamp >= $3
           AND timestamp <= $4`,
        [chainId, poolAddresses, intervals['1h'], now]
      ),
      this.pool.query(
        `SELECT COUNT(DISTINCT transaction_hash) as count
         FROM swap_events
         WHERE chain_id = $1
           AND pool_address = ANY($2)
           AND timestamp >= $3
           AND timestamp <= $4`,
        [chainId, poolAddresses, intervals['24h'], now]
      ),
    ]);

    return {
      '5m': parseInt(count5m.rows[0]?.count || '0', 10),
      '1h': parseInt(count1h.rows[0]?.count || '0', 10),
      '24h': parseInt(count24h.rows[0]?.count || '0', 10),
    };
  }

  /**
   * Calculate volume for a specific pool
   */
  async calculatePoolVolume(
    chainId: number,
    poolAddress: string,
    interval: '5m' | '1h' | '24h' | '7d' | '30d',
    now: Date = new Date()
  ): Promise<number> {
    try {
      const intervals = {
        '5m': new Date(now.getTime() - 5 * 60 * 1000),
        '1h': new Date(now.getTime() - 60 * 60 * 1000),
        '24h': new Date(now.getTime() - 24 * 60 * 60 * 1000),
        '7d': new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000),
        '30d': new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000),
      };

      const result = await this.pool.query(
        `SELECT COALESCE(SUM(amount_usd), 0) as total_volume
         FROM swap_events
         WHERE chain_id = $1
           AND pool_address = $2
           AND timestamp >= $3
           AND timestamp <= $4`,
        [chainId, poolAddress.toLowerCase(), intervals[interval], now]
      );

      return parseFloat(result.rows[0]?.total_volume || '0');
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        this.logMissingRelationOnce('pool volume calculation', error);
        return 0;
      }
      throw error;
    }
  }

  private zeroMetrics(): VolumeMetrics {
    return {
      volume5m: 0,
      volume1h: 0,
      volume24h: 0,
      volume7d: 0,
      volume30d: 0,
      txCount5m: 0,
      txCount1h: 0,
      txCount24h: 0,
    };
  }
}
