import { Pool } from 'pg';
import { getDatabasePool } from '../client';
import { TokenMarketData } from './token-repo';

export class MarketDataRepository {
  private pool: Pool;

  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 shouldUseReadFallback(error: unknown): boolean {
    if (this.isMissingRelationError(error)) return true;
    if (String(process.env.TOKEN_AGGREGATION_DB_READ_FALLBACK ?? '1').toLowerCase() === '0') return false;
    const message = (error as { message?: string })?.message || '';
    const code = (error as { code?: string })?.code || '';
    return ['ETIMEDOUT', 'ECONNREFUSED', 'ENOTFOUND'].includes(code) || /timeout|connect/i.test(message);
  }

  async getMarketData(chainId: number, tokenAddress: string): Promise<TokenMarketData | null> {
    if (String(process.env.TOKEN_AGGREGATION_SKIP_DB_READS ?? '0').toLowerCase() === '1') return null;
    try {
      const result = await this.pool.query(
        `SELECT chain_id, token_address, price_usd, price_change_24h, volume_24h, volume_7d, volume_30d,
                market_cap_usd, liquidity_usd, holders_count, transfers_24h, last_updated
         FROM token_market_data
         WHERE chain_id = $1 AND token_address = $2`,
        [chainId, tokenAddress.toLowerCase()]
      );

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

      const row = result.rows[0];
      return {
        chainId: row.chain_id,
        tokenAddress: row.token_address,
        priceUsd: row.price_usd ? parseFloat(row.price_usd) : undefined,
        priceChange24h: row.price_change_24h ? parseFloat(row.price_change_24h) : undefined,
        volume24h: parseFloat(row.volume_24h || '0'),
        volume7d: parseFloat(row.volume_7d || '0'),
        volume30d: parseFloat(row.volume_30d || '0'),
        marketCapUsd: row.market_cap_usd ? parseFloat(row.market_cap_usd) : undefined,
        liquidityUsd: parseFloat(row.liquidity_usd || '0'),
        holdersCount: row.holders_count || 0,
        transfers24h: row.transfers_24h || 0,
        lastUpdated: row.last_updated,
      };
    } catch (error) {
      if (this.shouldUseReadFallback(error)) {
        return null;
      }
      throw error;
    }
  }

  async upsertMarketData(data: TokenMarketData): Promise<void> {
    try {
      await this.pool.query(
        `INSERT INTO token_market_data (
           chain_id, token_address, price_usd, price_change_24h, volume_24h, volume_7d, volume_30d,
           market_cap_usd, liquidity_usd, holders_count, transfers_24h, last_updated
         )
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12)
         ON CONFLICT (chain_id, token_address) DO UPDATE SET
           price_usd = EXCLUDED.price_usd,
           price_change_24h = EXCLUDED.price_change_24h,
           volume_24h = EXCLUDED.volume_24h,
           volume_7d = EXCLUDED.volume_7d,
           volume_30d = EXCLUDED.volume_30d,
           market_cap_usd = EXCLUDED.market_cap_usd,
           liquidity_usd = EXCLUDED.liquidity_usd,
           holders_count = EXCLUDED.holders_count,
           transfers_24h = EXCLUDED.transfers_24h,
           last_updated = EXCLUDED.last_updated`,
        [
          data.chainId,
          data.tokenAddress.toLowerCase(),
          data.priceUsd,
          data.priceChange24h,
          data.volume24h,
          data.volume7d,
          data.volume30d,
          data.marketCapUsd,
          data.liquidityUsd,
          data.holdersCount,
          data.transfers24h,
          data.lastUpdated,
        ]
      );
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return;
      }
      throw error;
    }
  }

  async getTopTokensByVolume(chainId: number, limit: number = 50): Promise<TokenMarketData[]> {
    if (String(process.env.TOKEN_AGGREGATION_SKIP_DB_READS ?? '0').toLowerCase() === '1') return [];
    try {
      const result = await this.pool.query(
        `SELECT chain_id, token_address, price_usd, price_change_24h, volume_24h, volume_7d, volume_30d,
                market_cap_usd, liquidity_usd, holders_count, transfers_24h, last_updated
         FROM token_market_data
         WHERE chain_id = $1 AND volume_24h > 0
         ORDER BY volume_24h DESC
         LIMIT $2`,
        [chainId, limit]
      );

      return result.rows.map((row) => ({
        chainId: row.chain_id,
        tokenAddress: row.token_address,
        priceUsd: row.price_usd ? parseFloat(row.price_usd) : undefined,
        priceChange24h: row.price_change_24h ? parseFloat(row.price_change_24h) : undefined,
        volume24h: parseFloat(row.volume_24h || '0'),
        volume7d: parseFloat(row.volume_7d || '0'),
        volume30d: parseFloat(row.volume_30d || '0'),
        marketCapUsd: row.market_cap_usd ? parseFloat(row.market_cap_usd) : undefined,
        liquidityUsd: parseFloat(row.liquidity_usd || '0'),
        holdersCount: row.holders_count || 0,
        transfers24h: row.transfers_24h || 0,
        lastUpdated: row.last_updated,
      }));
    } catch (error) {
      if (this.shouldUseReadFallback(error)) {
        return [];
      }
      throw error;
    }
  }

  async getTopTokensByLiquidity(chainId: number, limit: number = 50): Promise<TokenMarketData[]> {
    if (String(process.env.TOKEN_AGGREGATION_SKIP_DB_READS ?? '0').toLowerCase() === '1') return [];
    try {
      const result = await this.pool.query(
        `SELECT chain_id, token_address, price_usd, price_change_24h, volume_24h, volume_7d, volume_30d,
                market_cap_usd, liquidity_usd, holders_count, transfers_24h, last_updated
         FROM token_market_data
         WHERE chain_id = $1 AND liquidity_usd > 0
         ORDER BY liquidity_usd DESC
         LIMIT $2`,
        [chainId, limit]
      );

      return result.rows.map((row) => ({
        chainId: row.chain_id,
        tokenAddress: row.token_address,
        priceUsd: row.price_usd ? parseFloat(row.price_usd) : undefined,
        priceChange24h: row.price_change_24h ? parseFloat(row.price_change_24h) : undefined,
        volume24h: parseFloat(row.volume_24h || '0'),
        volume7d: parseFloat(row.volume_7d || '0'),
        volume30d: parseFloat(row.volume_30d || '0'),
        marketCapUsd: row.market_cap_usd ? parseFloat(row.market_cap_usd) : undefined,
        liquidityUsd: parseFloat(row.liquidity_usd || '0'),
        holdersCount: row.holders_count || 0,
        transfers24h: row.transfers_24h || 0,
        lastUpdated: row.last_updated,
      }));
    } catch (error) {
      if (this.shouldUseReadFallback(error)) {
        return [];
      }
      throw error;
    }
  }
}
