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

export interface Token {
  chainId: number;
  address: string;
  name?: string;
  symbol?: string;
  decimals?: number;
  totalSupply?: string;
  logoUrl?: string;
  websiteUrl?: string;
  description?: string;
  verified?: boolean;
}

export interface TokenMarketData {
  chainId: number;
  tokenAddress: string;
  priceUsd?: number;
  priceChange24h?: number;
  volume24h: number;
  volume7d: number;
  volume30d: number;
  marketCapUsd?: number;
  liquidityUsd: number;
  holdersCount: number;
  transfers24h: number;
  lastUpdated: Date;
}

export class TokenRepository {
  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 getToken(chainId: number, address: string): Promise<Token | null> {
    try {
      const result = await this.pool.query(
        `SELECT chain_id, address, name, symbol, decimals, total_supply, logo_url, website_url, description, verified
         FROM tokens
         WHERE chain_id = $1 AND address = $2`,
        [chainId, address.toLowerCase()]
      );

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

      const row = result.rows[0];
      return {
        chainId: row.chain_id,
        address: row.address,
        name: row.name,
        symbol: row.symbol,
        decimals: row.decimals,
        totalSupply: row.total_supply?.toString(),
        logoUrl: row.logo_url,
        websiteUrl: row.website_url,
        description: row.description,
        verified: row.verified,
      };
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return null;
      }
      throw error;
    }
  }

  async getTokens(chainId: number, limit: number = 50, offset: number = 0): Promise<Token[]> {
    try {
      const result = await this.pool.query(
        `SELECT chain_id, address, name, symbol, decimals, total_supply, logo_url, website_url, description, verified
         FROM tokens
         WHERE chain_id = $1
         ORDER BY address
         LIMIT $2 OFFSET $3`,
        [chainId, limit, offset]
      );

      return result.rows.map((row) => ({
        chainId: row.chain_id,
        address: row.address,
        name: row.name,
        symbol: row.symbol,
        decimals: row.decimals,
        totalSupply: row.total_supply?.toString(),
        logoUrl: row.logo_url,
        websiteUrl: row.website_url,
        description: row.description,
        verified: row.verified,
      }));
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return [];
      }
      throw error;
    }
  }

  async upsertToken(token: Token): Promise<void> {
    try {
      await this.pool.query(
        `INSERT INTO tokens (chain_id, address, name, symbol, decimals, total_supply, logo_url, website_url, description, verified)
         VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
         ON CONFLICT (chain_id, address) DO UPDATE SET
           name = COALESCE(EXCLUDED.name, tokens.name),
           symbol = COALESCE(EXCLUDED.symbol, tokens.symbol),
           decimals = COALESCE(EXCLUDED.decimals, tokens.decimals),
           total_supply = COALESCE(EXCLUDED.total_supply, tokens.total_supply),
           logo_url = COALESCE(EXCLUDED.logo_url, tokens.logo_url),
           website_url = COALESCE(EXCLUDED.website_url, tokens.website_url),
           description = COALESCE(EXCLUDED.description, tokens.description),
           verified = COALESCE(EXCLUDED.verified, tokens.verified),
           updated_at = NOW()`,
        [
          token.chainId,
          token.address.toLowerCase(),
          token.name,
          token.symbol,
          token.decimals,
          token.totalSupply,
          token.logoUrl,
          token.websiteUrl,
          token.description,
          token.verified,
        ]
      );
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return;
      }
      throw error;
    }
  }

  async searchTokens(chainId: number, query: string, limit: number = 20): Promise<Token[]> {
    try {
      const searchPattern = `%${query.toLowerCase()}%`;
      const result = await this.pool.query(
        `SELECT chain_id, address, name, symbol, decimals, total_supply, logo_url, website_url, description, verified
         FROM tokens
         WHERE chain_id = $1
           AND (LOWER(address) LIKE $2 OR LOWER(symbol) LIKE $2 OR LOWER(name) LIKE $2)
         ORDER BY 
           CASE 
             WHEN LOWER(address) = $3 THEN 1
             WHEN LOWER(symbol) = $3 THEN 2
             WHEN LOWER(name) = $3 THEN 3
             ELSE 4
           END,
           symbol
         LIMIT $4`,
        [chainId, searchPattern, query.toLowerCase(), limit]
      );

      return result.rows.map((row) => ({
        chainId: row.chain_id,
        address: row.address,
        name: row.name,
        symbol: row.symbol,
        decimals: row.decimals,
        totalSupply: row.total_supply?.toString(),
        logoUrl: row.logo_url,
        websiteUrl: row.website_url,
        description: row.description,
        verified: row.verified,
      }));
    } catch (error) {
      if (this.isMissingRelationError(error)) {
        return [];
      }
      throw error;
    }
  }
}
