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

export type DexType = 'uniswap_v2' | 'uniswap_v3' | 'sushiswap' | 'dodo' | 'custom';

export interface LiquidityPool {
  id?: number;
  chainId: number;
  poolAddress: string;
  token0Address: string;
  token1Address: string;
  dexType: DexType;
  factoryAddress?: string;
  routerAddress?: string;
  reserve0: string;
  reserve1: string;
  reserve0Usd: number;
  reserve1Usd: number;
  totalLiquidityUsd: number;
  volume24h: number;
  feeTier?: number;
  createdAtBlock?: number;
  createdAtTimestamp?: Date;
  lastUpdated: Date;
}

export interface PoolReserveSnapshot {
  chainId: number;
  poolAddress: string;
  reserve0: string;
  reserve1: string;
  reserve0Usd?: number;
  reserve1Usd?: number;
  totalLiquidityUsd?: number;
  blockNumber: number;
  timestamp: Date;
}

export class PoolRepository {
  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');
  }

  async getPool(chainId: number, poolAddress: string): Promise<LiquidityPool | null> {
    try {
      const result = await this.pool.query(
        `SELECT id, chain_id, pool_address, token0_address, token1_address, dex_type,
                factory_address, router_address, reserve0, reserve1, reserve0_usd, reserve1_usd,
                total_liquidity_usd, volume_24h, fee_tier, created_at_block, created_at_timestamp, last_updated
         FROM liquidity_pools
         WHERE chain_id = $1 AND pool_address = $2`,
        [chainId, poolAddress.toLowerCase()]
      );

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

      return this.mapRowToPool(result.rows[0]);
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return null;
      }
      throw error;
    }
  }

  async getPoolsByChain(chainId: number, limit: number = 500): Promise<LiquidityPool[]> {
    try {
      const result = await this.pool.query(
        `SELECT id, chain_id, pool_address, token0_address, token1_address, dex_type,
                factory_address, router_address, reserve0, reserve1, reserve0_usd, reserve1_usd,
                total_liquidity_usd, volume_24h, fee_tier, created_at_block, created_at_timestamp, last_updated
         FROM liquidity_pools
         WHERE chain_id = $1
         ORDER BY total_liquidity_usd DESC NULLS LAST
         LIMIT $2`,
        [chainId, limit]
      );
      return result.rows.map((row) => this.mapRowToPool(row));
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return [];
      }
      throw error;
    }
  }

  async getPoolsByToken(chainId: number, tokenAddress: string): Promise<LiquidityPool[]> {
    try {
      const result = await this.pool.query(
        `SELECT id, chain_id, pool_address, token0_address, token1_address, dex_type,
                factory_address, router_address, reserve0, reserve1, reserve0_usd, reserve1_usd,
                total_liquidity_usd, volume_24h, fee_tier, created_at_block, created_at_timestamp, last_updated
         FROM liquidity_pools
         WHERE chain_id = $1 AND (token0_address = $2 OR token1_address = $2)
         ORDER BY total_liquidity_usd DESC`,
        [chainId, tokenAddress.toLowerCase()]
      );

      return result.rows.map((row) => this.mapRowToPool(row));
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return [];
      }
      throw error;
    }
  }

  async upsertPool(pool: LiquidityPool): Promise<void> {
    try {
      await this.pool.query(
        `INSERT INTO liquidity_pools (
           chain_id, pool_address, token0_address, token1_address, dex_type,
           factory_address, router_address, reserve0, reserve1, reserve0_usd, reserve1_usd,
           total_liquidity_usd, volume_24h, fee_tier, created_at_block, created_at_timestamp, last_updated
         )
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17)
         ON CONFLICT (chain_id, pool_address) DO UPDATE SET
           token0_address = EXCLUDED.token0_address,
           token1_address = EXCLUDED.token1_address,
           dex_type = EXCLUDED.dex_type,
           factory_address = EXCLUDED.factory_address,
           router_address = EXCLUDED.router_address,
           reserve0 = EXCLUDED.reserve0,
           reserve1 = EXCLUDED.reserve1,
           reserve0_usd = EXCLUDED.reserve0_usd,
           reserve1_usd = EXCLUDED.reserve1_usd,
           total_liquidity_usd = EXCLUDED.total_liquidity_usd,
           volume_24h = EXCLUDED.volume_24h,
           fee_tier = EXCLUDED.fee_tier,
           last_updated = EXCLUDED.last_updated`,
        [
          pool.chainId,
          pool.poolAddress.toLowerCase(),
          pool.token0Address.toLowerCase(),
          pool.token1Address.toLowerCase(),
          pool.dexType,
          pool.factoryAddress?.toLowerCase(),
          pool.routerAddress?.toLowerCase(),
          pool.reserve0,
          pool.reserve1,
          pool.reserve0Usd,
          pool.reserve1Usd,
          pool.totalLiquidityUsd,
          pool.volume24h,
          pool.feeTier,
          pool.createdAtBlock,
          pool.createdAtTimestamp,
          pool.lastUpdated,
        ]
      );
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return;
      }
      throw error;
    }
  }

  async addReserveSnapshot(snapshot: PoolReserveSnapshot): Promise<void> {
    try {
      await this.pool.query(
        `INSERT INTO pool_reserves_history (
           chain_id, pool_address, reserve0, reserve1, reserve0_usd, reserve1_usd,
           total_liquidity_usd, block_number, timestamp
         )
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)`,
        [
          snapshot.chainId,
          snapshot.poolAddress.toLowerCase(),
          snapshot.reserve0,
          snapshot.reserve1,
          snapshot.reserve0Usd,
          snapshot.reserve1Usd,
          snapshot.totalLiquidityUsd,
          snapshot.blockNumber,
          snapshot.timestamp,
        ]
      );
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return;
      }
      throw error;
    }
  }

  async getReserveHistory(
    chainId: number,
    poolAddress: string,
    from: Date,
    to: Date,
    limit: number = 1000
  ): Promise<PoolReserveSnapshot[]> {
    try {
      const result = await this.pool.query(
        `SELECT chain_id, pool_address, reserve0, reserve1, reserve0_usd, reserve1_usd,
                total_liquidity_usd, block_number, timestamp
         FROM pool_reserves_history
         WHERE chain_id = $1 AND pool_address = $2 AND timestamp >= $3 AND timestamp <= $4
         ORDER BY timestamp DESC
         LIMIT $5`,
        [chainId, poolAddress.toLowerCase(), from, to, limit]
      );

      return result.rows.map((row) => ({
        chainId: row.chain_id,
        poolAddress: row.pool_address,
        reserve0: row.reserve0,
        reserve1: row.reserve1,
        reserve0Usd: row.reserve0_usd ? parseFloat(row.reserve0_usd) : undefined,
        reserve1Usd: row.reserve1_usd ? parseFloat(row.reserve1_usd) : undefined,
        totalLiquidityUsd: row.total_liquidity_usd ? parseFloat(row.total_liquidity_usd) : undefined,
        blockNumber: parseInt(row.block_number, 10),
        timestamp: row.timestamp,
      }));
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return [];
      }
      throw error;
    }
  }

  /* eslint-disable @typescript-eslint/no-explicit-any */
  private mapRowToPool(row: any): LiquidityPool {
    return {
      id: row.id,
      chainId: row.chain_id,
      poolAddress: row.pool_address,
      token0Address: row.token0_address,
      token1Address: row.token1_address,
      dexType: row.dex_type,
      factoryAddress: row.factory_address,
      routerAddress: row.router_address,
      reserve0: row.reserve0,
      reserve1: row.reserve1,
      reserve0Usd: parseFloat(row.reserve0_usd || '0'),
      reserve1Usd: parseFloat(row.reserve1_usd || '0'),
      totalLiquidityUsd: parseFloat(row.total_liquidity_usd || '0'),
      volume24h: parseFloat(row.volume_24h || '0'),
      feeTier: row.fee_tier,
      createdAtBlock: row.created_at_block,
      createdAtTimestamp: row.created_at_timestamp,
      lastUpdated: row.last_updated,
    };
  }
}
