import { Pool, QueryResult } from "pg";

export interface DbConfig {
  host: string;
  port: number;
  database: string;
  user: string;
  password: string;
}

export class DbTestHelper {
  private pool: Pool;

  constructor(config: DbConfig) {
    this.pool = new Pool({
      host: config.host,
      port: config.port,
      database: config.database,
      user: config.user,
      password: config.password,
      max: 10,
      idleTimeoutMillis: 30000,
      connectionTimeoutMillis: 2000,
    });
  }

  async query<T = any>(text: string, params?: any[]): Promise<QueryResult<T>> {
    return this.pool.query(text, params);
  }

  async close(): Promise<void> {
    await this.pool.end();
  }

  // ============================================================
  // Registry Queries
  // ============================================================

  async getLocalAdmin(pubkey: string) {
    const result = await this.query(
      `SELECT * FROM local_admins WHERE local_admin_pubkey = $1`,
      [pubkey]
    );
    return result.rows[0];
  }

  async getCompany(pubkey: string) {
    const result = await this.query(
      `SELECT * FROM companies WHERE company_pubkey = $1`,
      [pubkey]
    );
    return result.rows[0];
  }

  async getEuaMintingLogs(companyPubkey: string) {
    const result = await this.query(
      `SELECT * FROM eua_minting_log WHERE company_pubkey = $1 ORDER BY created_at DESC`,
      [companyPubkey]
    );
    return result.rows;
  }

  async getGhgMintingLogs(companyPubkey: string) {
    const result = await this.query(
      `SELECT * FROM ghg_minting_log WHERE company_pubkey = $1 ORDER BY created_at DESC`,
      [companyPubkey]
    );
    return result.rows;
  }

  async getEuaTransferLogs(fromCompany?: string, toCompany?: string) {
    let query = `SELECT * FROM eua_transfer_log WHERE 1=1`;
    const params: string[] = [];

    if (fromCompany) {
      params.push(fromCompany);
      query += ` AND from_company_pubkey = $${params.length}`;
    }

    if (toCompany) {
      params.push(toCompany);
      query += ` AND to_company_pubkey = $${params.length}`;
    }

    query += ` ORDER BY created_at DESC`;

    const result = await this.query(query, params);
    return result.rows;
  }

  async getLocalAdminCount(): Promise<number> {
    const result = await this.query(`SELECT COUNT(*) as count FROM local_admins`);
    return parseInt(result.rows[0].count);
  }

  async getCompanyCount(): Promise<number> {
    const result = await this.query(`SELECT COUNT(*) as count FROM companies`);
    return parseInt(result.rows[0].count);
  }

  async getActiveLocalAdmins() {
    const result = await this.query(
      `SELECT * FROM local_admins WHERE is_active = true ORDER BY created_at DESC`
    );
    return result.rows;
  }

  async getActiveCompanies() {
    const result = await this.query(
      `SELECT * FROM companies WHERE is_active = true ORDER BY created_at DESC`
    );
    return result.rows;
  }

  async getCompaniesByLocalAdmin(localAdminPubkey: string) {
    const result = await this.query(
      `SELECT * FROM companies WHERE local_admin_pubkey = $1 ORDER BY created_at DESC`,
      [localAdminPubkey]
    );
    return result.rows;
  }

  async getTotalEuaMinted(companyPubkey?: string): Promise<bigint> {
    let query = `SELECT COALESCE(SUM(amount), 0) as total FROM eua_minting_log`;
    const params: string[] = [];

    if (companyPubkey) {
      params.push(companyPubkey);
      query += ` WHERE company_pubkey = $1`;
    }

    const result = await this.query(query, params);
    return BigInt(result.rows[0].total);
  }

  async getTotalGhgMinted(companyPubkey?: string): Promise<bigint> {
    let query = `SELECT COALESCE(SUM(amount), 0) as total FROM ghg_minting_log`;
    const params: string[] = [];

    if (companyPubkey) {
      params.push(companyPubkey);
      query += ` WHERE company_pubkey = $1`;
    }

    const result = await this.query(query, params);
    return BigInt(result.rows[0].total);
  }

  // ============================================================
  // Secondary Market Queries - Orders
  // ============================================================

  async getOrder(orderId: string) {
    const result = await this.query(
      `SELECT * FROM orders WHERE order_id = $1`,
      [orderId]
    );
    return result.rows[0];
  }

  async getOrderByPubkey(orderPubkey: string) {
    const result = await this.query(
      `SELECT * FROM orders WHERE order_pubkey = $1`,
      [orderPubkey]
    );
    return result.rows[0];
  }

  async getOrdersByCompany(companyPubkey: string) {
    const result = await this.query(
      `SELECT * FROM orders WHERE company_pubkey = $1 ORDER BY created_at DESC`,
      [companyPubkey]
    );
    return result.rows;
  }

  async getOrdersByMaker(makerPubkey: string) {
    const result = await this.query(
      `SELECT * FROM orders WHERE maker_pubkey = $1 ORDER BY created_at DESC`,
      [makerPubkey]
    );
    return result.rows;
  }

  async getOrdersByStatus(status: string) {
    const result = await this.query(
      `SELECT * FROM orders WHERE status = $1 ORDER BY created_at DESC`,
      [status]
    );
    return result.rows;
  }

  async getOpenOrders(marketPubkey?: string) {
    let query = `SELECT * FROM orders WHERE status IN ('Open', 'PartiallyFilled')`;
    const params: string[] = [];

    if (marketPubkey) {
      params.push(marketPubkey);
      query += ` AND market_pubkey = $1`;
    }

    query += ` ORDER BY created_at DESC`;

    const result = await this.query(query, params);
    return result.rows;
  }

  async getOrderCount(): Promise<number> {
    const result = await this.query(`SELECT COUNT(*) as count FROM orders`);
    return parseInt(result.rows[0].count);
  }

  // ============================================================
  // Secondary Market Queries - Trades
  // ============================================================

  async getTrade(tradeId: number) {
    const result = await this.query(
      `SELECT * FROM trades WHERE id = $1`,
      [tradeId]
    );
    return result.rows[0];
  }

  async getTradesByOrder(orderId: string) {
    const result = await this.query(
      `SELECT * FROM trades WHERE maker_order_id = $1 OR taker_order_id = $1 ORDER BY created_at DESC`,
      [orderId]
    );
    return result.rows;
  }

  async getTradesByCompany(companyPubkey: string) {
    const result = await this.query(
      `SELECT * FROM trades WHERE maker_company_pubkey = $1 OR taker_company_pubkey = $1 ORDER BY created_at DESC`,
      [companyPubkey]
    );
    return result.rows;
  }

  async getTradeCount(): Promise<number> {
    const result = await this.query(`SELECT COUNT(*) as count FROM trades`);
    return parseInt(result.rows[0].count);
  }

  async getTotalTradeVolume(companyPubkey?: string): Promise<bigint> {
    let query = `SELECT COALESCE(SUM(fill_size), 0) as total FROM trades`;
    const params: string[] = [];

    if (companyPubkey) {
      params.push(companyPubkey);
      query += ` WHERE maker_company_pubkey = $1 OR taker_company_pubkey = $1`;
    }

    const result = await this.query(query, params);
    return BigInt(result.rows[0].total);
  }

  // ============================================================
  // Secondary Market Queries - USDC Logs
  // ============================================================

  async getUsdcLogs(companyPubkey: string) {
    const result = await this.query(
      `SELECT * FROM usdc_logs WHERE company_pubkey = $1 ORDER BY created_at DESC`,
      [companyPubkey]
    );
    return result.rows;
  }

  async getUsdcLogsByType(companyPubkey: string, operationType: 'Deposit' | 'Withdraw') {
    const result = await this.query(
      `SELECT * FROM usdc_logs WHERE company_pubkey = $1 AND operation_type = $2 ORDER BY created_at DESC`,
      [companyPubkey, operationType]
    );
    return result.rows;
  }

  async getTotalUsdcDeposited(companyPubkey?: string): Promise<bigint> {
    let query = `SELECT COALESCE(SUM(amount), 0) as total FROM usdc_logs WHERE operation_type = 'Deposit'`;
    const params: string[] = [];

    if (companyPubkey) {
      params.push(companyPubkey);
      query += ` AND company_pubkey = $1`;
    }

    const result = await this.query(query, params);
    return BigInt(result.rows[0].total);
  }

  async getTotalUsdcWithdrawn(companyPubkey?: string): Promise<bigint> {
    let query = `SELECT COALESCE(SUM(amount), 0) as total FROM usdc_logs WHERE operation_type = 'Withdraw'`;
    const params: string[] = [];

    if (companyPubkey) {
      params.push(companyPubkey);
      query += ` AND company_pubkey = $1`;
    }

    const result = await this.query(query, params);
    return BigInt(result.rows[0].total);
  }

  // ============================================================
  // Wait Helpers - General
  // ============================================================

  async waitForCondition(
    condition: () => Promise<boolean>,
    timeoutMs: number = 5000,
    intervalMs: number = 100
  ): Promise<boolean> {
    const startTime = Date.now();

    while (Date.now() - startTime < timeoutMs) {
      if (await condition()) {
        return true;
      }
      await new Promise(resolve => setTimeout(resolve, intervalMs));
    }

    return false;
  }

  // ============================================================
  // Wait Helpers - Registry
  // ============================================================

  async waitForCompany(pubkey: string, timeoutMs: number = 5000): Promise<boolean> {
    return this.waitForCondition(async () => {
      const company = await this.getCompany(pubkey);
      return company !== undefined;
    }, timeoutMs);
  }

  async waitForLocalAdmin(pubkey: string, timeoutMs: number = 5000): Promise<boolean> {
    return this.waitForCondition(async () => {
      const admin = await this.getLocalAdmin(pubkey);
      return admin !== undefined;
    }, timeoutMs);
  }

  async waitForEuaMintingLog(
    companyPubkey: string,
    expectedAmount: bigint,
    timeoutMs: number = 5000
  ): Promise<boolean> {
    return this.waitForCondition(async () => {
      const total = await this.getTotalEuaMinted(companyPubkey);
      return total >= expectedAmount;
    }, timeoutMs);
  }

  // ============================================================
  // Wait Helpers - Secondary Market
  // ============================================================

  async waitForOrder(orderId: string, timeoutMs: number = 5000): Promise<boolean> {
    return this.waitForCondition(async () => {
      const order = await this.getOrder(orderId);
      return order !== undefined;
    }, timeoutMs);
  }

  async waitForOrderStatus(
    orderId: string,
    expectedStatus: string,
    timeoutMs: number = 5000
  ): Promise<boolean> {
    return this.waitForCondition(async () => {
      const order = await this.getOrder(orderId);
      return order && order.status === expectedStatus;
    }, timeoutMs);
  }

  async waitForTrade(
    makerOrderId: string,
    takerOrderId: string,
    timeoutMs: number = 5000
  ): Promise<boolean> {
    return this.waitForCondition(async () => {
      const result = await this.query(
        `SELECT COUNT(*) as count FROM trades WHERE maker_order_id = $1 AND taker_order_id = $2`,
        [makerOrderId, takerOrderId]
      );
      return parseInt(result.rows[0].count) > 0;
    }, timeoutMs);
  }

  async waitForUsdcLog(
    companyPubkey: string,
    operationType: 'Deposit' | 'Withdraw',
    expectedAmount: bigint,
    timeoutMs: number = 5000
  ): Promise<boolean> {
    return this.waitForCondition(async () => {
      const logs = await this.getUsdcLogsByType(companyPubkey, operationType);
      if (logs.length === 0) return false;

      const latestLog = logs[0];
      return BigInt(latestLog.amount) === expectedAmount;
    }, timeoutMs);
  }

  async waitForCompanyUsdcBalance(
    companyPubkey: string,
    expectedBalance: string,
    timeoutMs: number = 5000
  ): Promise<boolean> {
    return this.waitForCondition(async () => {
      const company = await this.getCompany(companyPubkey);
      return company && company.usdc_balance === expectedBalance;
    }, timeoutMs);
  }
}