import { Pool } from 'pg';
import { getDatabasePool } from '../client';
import { ProviderCapabilityRecord, PlannerResponse } from '../../services/planner-v2-types';
import { logger } from '../../utils/logger';

interface CachedPlanRow {
  plan_id: string;
  response_json: PlannerResponse;
}

export class PlannerMetricsRepository {
  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 isTransientConnectionError(error: unknown): boolean {
    if (!error || typeof error !== 'object') {
      return false;
    }

    const code = String((error as { code?: string }).code || '');
    const message = String((error as { message?: string }).message || '');
    return (
      ['ECONNREFUSED', 'ECONNRESET', 'EHOSTUNREACH', 'ENOTFOUND', 'EAI_AGAIN', 'ETIMEDOUT', '57P01'].includes(code) ||
      message.includes('connect ECONNREFUSED') ||
      message.includes('connect EHOSTUNREACH') ||
      message.includes('Connection terminated unexpectedly')
    );
  }

  private isNonFatalError(error: unknown): boolean {
    return this.isMissingRelationError(error) || this.isTransientConnectionError(error);
  }

  private logSuppressedError(action: string, error: unknown): void {
    const message = error instanceof Error ? error.message : String(error);
    logger.warn(`Planner metrics ${action} skipped: ${message}`);
  }

  async getCachedPlan(requestHash: string): Promise<PlannerResponse | null> {
    try {
      const result = await this.pool.query<CachedPlanRow>(
        `SELECT plan_id, response_json
         FROM route_plan_cache
         WHERE request_hash = $1 AND (expires_at IS NULL OR expires_at > NOW())
         ORDER BY created_at DESC
         LIMIT 1`,
        [requestHash]
      );

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

      return result.rows[0].response_json;
    } catch (error) {
      if (this.isNonFatalError(error)) {
        this.logSuppressedError('cache lookup', error);
        return null;
      }
      throw error;
    }
  }

  async cachePlan(requestHash: string, response: PlannerResponse, ttlSeconds: number = 30): Promise<void> {
    try {
      await this.pool.query(
        `INSERT INTO route_plan_cache (
           plan_id, request_hash, chain_id, destination_chain_id, decision, response_json, expires_at
         )
         VALUES ($1, $2, $3, $4, $5, $6::jsonb, NOW() + ($7::text || ' seconds')::interval)
         ON CONFLICT (plan_id) DO UPDATE SET
           request_hash = EXCLUDED.request_hash,
           chain_id = EXCLUDED.chain_id,
           destination_chain_id = EXCLUDED.destination_chain_id,
           decision = EXCLUDED.decision,
           response_json = EXCLUDED.response_json,
           expires_at = EXCLUDED.expires_at,
           created_at = NOW()`,
        [
          response.planId,
          requestHash,
          response.sourceChainId,
          response.destinationChainId,
          response.decision,
          JSON.stringify(response),
          String(ttlSeconds),
        ]
      );
    } catch (error) {
      if (this.isNonFatalError(error)) {
        this.logSuppressedError('cache write', error);
        return;
      }
      throw error;
    }
  }

  async recordProviderSnapshots(chainId: number, records: ProviderCapabilityRecord[]): Promise<void> {
    try {
      for (const record of records) {
        await this.pool.query(
          `INSERT INTO provider_health_snapshots (
             chain_id, provider, status, supports_execution, supports_quote, metadata
           )
           VALUES ($1, $2, $3, $4, $5, $6::jsonb)`,
          [
            chainId,
            record.provider,
            record.live ? 'live' : 'planned',
            record.executionLive,
            record.quoteLive,
            JSON.stringify({
              executionMode: record.executionMode,
              supportedLegTypes: record.supportedLegTypes,
              pairs: record.pairs,
              notes: record.notes || [],
            }),
          ]
        );
      }
    } catch (error) {
      if (this.isNonFatalError(error)) {
        this.logSuppressedError('provider snapshot write', error);
        return;
      }
      throw error;
    }
  }

  async recordPlannedRouteMetrics(response: PlannerResponse): Promise<void> {
    try {
      for (const [index, leg] of response.legs.entries()) {
        await this.pool.query(
          `INSERT INTO route_execution_metrics (
             plan_id, chain_id, provider, hop_index, token_in_address, token_out_address,
             estimated_amount_out, status, metadata
           )
           VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9::jsonb)`,
          [
            response.planId,
            leg.sourceChainId,
            leg.provider,
            index,
            leg.tokenInAddress,
            leg.tokenOutAddress,
            leg.estimatedAmountOut,
            'planned',
            JSON.stringify({
              kind: leg.kind,
              target: leg.target,
              poolAddress: leg.poolAddress,
              providerData: leg.providerData || {},
              bridgeType: leg.bridgeType,
              bridgeAddress: leg.bridgeAddress,
              gasEstimate: leg.gasEstimate,
              freshnessSeconds: leg.freshnessSeconds,
              notes: leg.notes || [],
            }),
          ]
        );
      }
    } catch (error) {
      if (this.isNonFatalError(error)) {
        this.logSuppressedError('route metrics write', error);
        return;
      }
      throw error;
    }
  }
}
