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

export type OHLCVInterval = '5m' | '15m' | '1h' | '4h' | '24h';

export interface OHLCVData {
  timestamp: Date;
  open: number;
  high: number;
  low: number;
  close: number;
  volume: number;
  volumeUsd: number;
}

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

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

  private normalizePoolAddress(poolAddress?: string): string {
    return poolAddress?.toLowerCase() || '';
  }

  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 (OHLCVGenerator.missingRelationWarnings.has(key)) {
      return;
    }

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

  /**
   * Generate OHLCV data for a token
   */
  async generateOHLCV(
    chainId: number,
    tokenAddress: string,
    interval: OHLCVInterval,
    from: Date,
    to: Date,
    poolAddress?: string
  ): Promise<OHLCVData[]> {
    try {
      const intervalMs = this.getIntervalMs(interval);

      // Get swap events for the time range
      let query = `
        SELECT timestamp, amount_usd, price_usd
        FROM swap_events
        WHERE chain_id = $1
          AND (token0_address = $2 OR token1_address = $2)
          AND timestamp >= $3
          AND timestamp <= $4
      `;
      const params: (string | number | Date)[] = [chainId, tokenAddress.toLowerCase(), from, to];

      if (poolAddress) {
        query += ` AND pool_address = $5`;
        params.push(poolAddress.toLowerCase());
      }

      query += ` ORDER BY timestamp ASC`;

      const result = await this.pool.query(query, params);

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

      // Group swaps by interval
      const intervals = new Map<number, OHLCVData>();

      result.rows.forEach((row) => {
        const timestamp = new Date(row.timestamp);
        const intervalStart = Math.floor(timestamp.getTime() / intervalMs) * intervalMs;
        const price = parseFloat(row.price_usd || '0');
        const volume = parseFloat(row.amount_usd || '0');

        if (!intervals.has(intervalStart)) {
          intervals.set(intervalStart, {
            timestamp: new Date(intervalStart),
            open: price,
            high: price,
            low: price,
            close: price,
            volume: 0,
            volumeUsd: 0,
          });
        }

        const ohlcv = intervals.get(intervalStart)!;
        ohlcv.high = Math.max(ohlcv.high, price);
        ohlcv.low = Math.min(ohlcv.low, price);
        ohlcv.close = price;
        ohlcv.volume += 1;
        ohlcv.volumeUsd += volume;
      });

      // Convert map to array and sort by timestamp
      return Array.from(intervals.values()).sort((a, b) => a.timestamp.getTime() - b.timestamp.getTime());
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        this.logMissingRelationOnce('OHLCV generation', error);
        return [];
      }
      throw error;
    }
  }

  /**
   * Store OHLCV data in database
   */
  async storeOHLCV(
    chainId: number,
    tokenAddress: string,
    interval: OHLCVInterval,
    data: OHLCVData[],
    poolAddress?: string
  ): Promise<void> {
    if (data.length === 0) return;

    const normalizedPoolAddress = this.normalizePoolAddress(poolAddress);

    const values = data.map((_, i) => {
      const base = i * 11;
      return `($${base + 1}, $${base + 2}, $${base + 3}, $${base + 4}, $${base + 5}, $${base + 6}, $${base + 7}, $${base + 8}, $${base + 9}, $${base + 10}, $${base + 11})`;
    });

    const params: (string | number | Date)[] = [];
    data.forEach((d) => {
      params.push(
        chainId,
        tokenAddress.toLowerCase(),
        normalizedPoolAddress,
        interval,
        d.open,
        d.high,
        d.low,
        d.close,
        d.volume,
        d.volumeUsd,
        d.timestamp
      );
    });

    try {
      await this.pool.query(
        `INSERT INTO token_ohlcv (
           chain_id, token_address, pool_address, interval_type,
           open_price, high_price, low_price, close_price, volume, volume_usd, timestamp
         )
         VALUES ${values.join(', ')}
         ON CONFLICT (chain_id, token_address, pool_address, interval_type, timestamp) DO UPDATE SET
           open_price = EXCLUDED.open_price,
           high_price = EXCLUDED.high_price,
           low_price = EXCLUDED.low_price,
           close_price = EXCLUDED.close_price,
           volume = EXCLUDED.volume,
           volume_usd = EXCLUDED.volume_usd`,
        params
      );
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        this.logMissingRelationOnce('OHLCV storage', error);
        return;
      }
      throw error;
    }
  }

  /**
   * Get OHLCV data from database
   */
  async getOHLCV(
    chainId: number,
    tokenAddress: string,
    interval: OHLCVInterval,
    from: Date,
    to: Date,
    poolAddress?: string
  ): Promise<OHLCVData[]> {
    try {
      let query = `
        SELECT timestamp, open_price, high_price, low_price, close_price, volume, volume_usd
        FROM token_ohlcv
        WHERE chain_id = $1
          AND token_address = $2
          AND interval_type = $3
          AND timestamp >= $4
          AND timestamp <= $5
      `;
      const params: (string | number | Date)[] = [chainId, tokenAddress.toLowerCase(), interval, from, to];

      if (poolAddress) {
        query += ` AND pool_address = $6`;
        params.push(this.normalizePoolAddress(poolAddress));
      } else {
        query += ` AND pool_address = ''`;
      }

      query += ` ORDER BY timestamp ASC`;

      const result = await this.pool.query(query, params);

      return result.rows.map((row) => ({
        timestamp: row.timestamp,
        open: parseFloat(row.open_price),
        high: parseFloat(row.high_price),
        low: parseFloat(row.low_price),
        close: parseFloat(row.close_price),
        volume: parseFloat(row.volume || '0'),
        volumeUsd: parseFloat(row.volume_usd || '0'),
      }));
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        this.logMissingRelationOnce('OHLCV lookup', error);
        return [];
      }
      throw error;
    }
  }

  /**
   * Get interval duration in milliseconds
   */
  private getIntervalMs(interval: OHLCVInterval): number {
    const intervals: Record<OHLCVInterval, number> = {
      '5m': 5 * 60 * 1000,
      '15m': 15 * 60 * 1000,
      '1h': 60 * 60 * 1000,
      '4h': 4 * 60 * 60 * 1000,
      '24h': 24 * 60 * 60 * 1000,
    };
    return intervals[interval];
  }

  /**
   * Generate and store OHLCV for a token
   */
  async generateAndStore(
    chainId: number,
    tokenAddress: string,
    interval: OHLCVInterval,
    from: Date,
    to: Date,
    poolAddress?: string
  ): Promise<OHLCVData[]> {
    const data = await this.generateOHLCV(chainId, tokenAddress, interval, from, to, poolAddress);
    if (data.length > 0) {
      await this.storeOHLCV(chainId, tokenAddress, interval, data, poolAddress);
    }
    return data;
  }
}
