import { Pool } from "pg";
import { PendingBlinkInvoiceRecord } from "../providers/blink/types";
import { DepositEvent, ReconciliationCursor } from "../types";
import { IDepositQueueStore, IReconciliationCursorStore, QueuedDepositEvent } from "./StateStoreContracts";
import { IBlinkInvoiceStore } from "./StoreContracts";

interface PostgresQueueRow {
  idempotency_key: string;
  payload: DepositEvent;
  attempts: number;
  queued_at: Date;
  updated_at: Date;
  next_attempt_at: Date;
  last_error: string | null;
}

interface BlinkInvoiceRow {
  reference: string;
  payment_hash: string;
  payload: PendingBlinkInvoiceRecord;
  updated_at: Date;
}

export class PostgresDepositQueueStore implements IDepositQueueStore {
  constructor(
    private readonly pool: Pool,
    private readonly deliveredRetentionDays: number = 30
  ) {}

  async enqueue(events: DepositEvent[]): Promise<{ accepted: number; duplicates: number }> {
    await this.pruneDelivered();

    let accepted = 0;
    let duplicates = 0;
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");
      for (const event of events) {
        const delivered = await client.query(
          "SELECT 1 FROM deposit_delivered_events WHERE idempotency_key = $1 LIMIT 1",
          [event.idempotency_key]
        );
        if (delivered.rowCount) {
          duplicates += 1;
          continue;
        }

        const inserted = await client.query(
          `INSERT INTO deposit_queue_events
            (idempotency_key, payload, attempts, queued_at, updated_at, next_attempt_at, last_error)
           VALUES ($1, $2::jsonb, 0, NOW(), NOW(), NOW(), NULL)
           ON CONFLICT (idempotency_key) DO NOTHING`,
          [event.idempotency_key, JSON.stringify(event)]
        );

        if (inserted.rowCount) {
          accepted += 1;
        } else {
          duplicates += 1;
        }
      }
      await client.query("COMMIT");
    } catch (error) {
      await client.query("ROLLBACK");
      throw error;
    } finally {
      client.release();
    }

    const stats = await this.stats();
    console.info("deposit queue enqueue complete", { accepted, duplicates, queued: stats.queued });
    return { accepted, duplicates };
  }

  async hasSeen(idempotencyKey: string): Promise<boolean> {
    await this.pruneDelivered();
    const result = await this.pool.query(
      `SELECT EXISTS (
         SELECT 1 FROM deposit_delivered_events WHERE idempotency_key = $1
         UNION ALL
         SELECT 1 FROM deposit_queue_events WHERE idempotency_key = $1
       ) AS seen`,
      [idempotencyKey]
    );

    return Boolean(result.rows[0]?.seen);
  }

  async getProcessable(maxBatch: number = 100, now: Date = new Date()): Promise<QueuedDepositEvent[]> {
    const result = await this.pool.query<PostgresQueueRow>(
      `SELECT idempotency_key, payload, attempts, queued_at, updated_at, next_attempt_at, last_error
       FROM deposit_queue_events
       WHERE next_attempt_at <= $1
       ORDER BY next_attempt_at ASC, queued_at ASC
       LIMIT $2`,
      [now.toISOString(), maxBatch]
    );

    return result.rows.map((row) => this.rowToQueuedEvent(row));
  }

  async markDelivered(event: QueuedDepositEvent): Promise<void> {
    const client = await this.pool.connect();
    try {
      await client.query("BEGIN");
      await client.query("DELETE FROM deposit_queue_events WHERE idempotency_key = $1", [event.idempotency_key]);
      await client.query(
        `INSERT INTO deposit_delivered_events (idempotency_key, delivered_at)
         VALUES ($1, NOW())
         ON CONFLICT (idempotency_key) DO UPDATE SET delivered_at = EXCLUDED.delivered_at`,
        [event.idempotency_key]
      );
      await client.query("COMMIT");
    } catch (error) {
      await client.query("ROLLBACK");
      throw error;
    } finally {
      client.release();
    }
  }

  async markFailed(event: QueuedDepositEvent, errorMessage: string, now: Date = new Date()): Promise<void> {
    const attempts = event.attempts + 1;
    const delayMs = this.calculateBackoffMs(attempts);
    await this.pool.query(
      `UPDATE deposit_queue_events
       SET attempts = $2,
           updated_at = $3,
           next_attempt_at = $4,
           last_error = $5
       WHERE idempotency_key = $1`,
      [
        event.idempotency_key,
        attempts,
        now.toISOString(),
        new Date(now.getTime() + delayMs).toISOString(),
        errorMessage,
      ]
    );
  }

  async stats(): Promise<{ queued: number; delivered: number }> {
    await this.pruneDelivered();
    const [queued, delivered] = await Promise.all([
      this.pool.query("SELECT COUNT(*)::int AS count FROM deposit_queue_events"),
      this.pool.query("SELECT COUNT(*)::int AS count FROM deposit_delivered_events"),
    ]);

    return {
      queued: queued.rows[0]?.count || 0,
      delivered: delivered.rows[0]?.count || 0,
    };
  }

  private calculateBackoffMs(attempts: number): number {
    const baseMs = 5000;
    const maxMs = 5 * 60 * 1000;
    return Math.min(baseMs * Math.max(1, 2 ** (attempts - 1)), maxMs);
  }

  private rowToQueuedEvent(row: PostgresQueueRow): QueuedDepositEvent {
    return {
      ...row.payload,
      attempts: row.attempts,
      queued_at: row.queued_at.toISOString(),
      updated_at: row.updated_at.toISOString(),
      next_attempt_at: row.next_attempt_at.toISOString(),
      last_error: row.last_error || undefined,
    };
  }

  private async pruneDelivered(): Promise<void> {
    await this.pool.query(
      "DELETE FROM deposit_delivered_events WHERE delivered_at < NOW() - ($1::int * INTERVAL '1 day')",
      [this.deliveredRetentionDays]
    );
  }
}

export class PostgresReconciliationCursorStore implements IReconciliationCursorStore {
  constructor(private readonly pool: Pool) {}

  async getCursor(chainType: string): Promise<ReconciliationCursor | null> {
    const result = await this.pool.query<{
      chain_type: string;
      from_block: string;
      updated_at: Date;
    }>(
      "SELECT chain_type, from_block, updated_at FROM reconciliation_cursors WHERE chain_type = $1 LIMIT 1",
      [chainType]
    );

    const row = result.rows[0];
    if (!row) {
      return null;
    }

    return {
      chain_type: row.chain_type,
      from_block: row.from_block,
      updated_at: row.updated_at.toISOString(),
    };
  }

  async upsertCursor(chainType: string, fromBlock: string): Promise<void> {
    await this.pool.query(
      `INSERT INTO reconciliation_cursors (chain_type, from_block, updated_at)
       VALUES ($1, $2, NOW())
       ON CONFLICT (chain_type)
       DO UPDATE SET from_block = EXCLUDED.from_block, updated_at = EXCLUDED.updated_at`,
      [chainType, fromBlock]
    );
  }
}

export class PostgresBlinkInvoiceStore implements IBlinkInvoiceStore {
  constructor(private readonly pool: Pool) {}

  async savePending(record: PendingBlinkInvoiceRecord): Promise<void> {
    await this.pool.query(
      `INSERT INTO blink_pending_invoices (reference, payment_hash, payload, updated_at)
       VALUES ($1, $2, $3::jsonb, NOW())
       ON CONFLICT (reference)
       DO UPDATE SET payment_hash = EXCLUDED.payment_hash,
                     payload = EXCLUDED.payload,
                     updated_at = EXCLUDED.updated_at`,
      [record.reference, record.payment_hash.toLowerCase(), JSON.stringify(record)]
    );
  }

  async findPendingByPaymentHashOrReference(
    paymentHash?: string,
    reference?: string
  ): Promise<PendingBlinkInvoiceRecord | null> {
    const normalizedPaymentHash = String(paymentHash || "").trim().toLowerCase();
    const normalizedReference = String(reference || "").trim();

    const result = await this.pool.query<BlinkInvoiceRow>(
      `SELECT reference, payment_hash, payload, updated_at
       FROM blink_pending_invoices
       WHERE ($1 <> '' AND payment_hash = $1)
          OR ($2 <> '' AND reference = $2)
          OR ($2 <> '' AND payload->>'provider_reference' = $2)
       ORDER BY updated_at DESC
       LIMIT 1`,
      [normalizedPaymentHash, normalizedReference]
    );

    return result.rows[0]?.payload || null;
  }

  async markConfirmed(
    reference: string,
    providerReference: string | undefined,
    transactionStatus: string,
    walletCurrency: string,
    rawWebhookPayload: Record<string, unknown>
  ): Promise<void> {
    const existing = await this.findPendingByPaymentHashOrReference(undefined, reference);
    if (!existing) {
      return;
    }

    const updated: PendingBlinkInvoiceRecord = {
      ...existing,
      status: "confirmed",
      provider_reference: providerReference || null,
      transaction_status: transactionStatus,
      wallet_currency: walletCurrency,
      raw_webhook_payload: rawWebhookPayload,
      updated_at: new Date().toISOString(),
    };

    await this.savePending(updated);
  }
}

export async function createPostgresStateStore(databaseUrl: string): Promise<Pool> {
  const pool = new Pool({
    connectionString: databaseUrl,
  });

  await pool.query(`
    CREATE TABLE IF NOT EXISTS deposit_queue_events (
      idempotency_key TEXT PRIMARY KEY,
      payload JSONB NOT NULL,
      attempts INTEGER NOT NULL DEFAULT 0,
      queued_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      next_attempt_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
      last_error TEXT NULL
    )
  `);

  await pool.query(`
    CREATE TABLE IF NOT EXISTS deposit_delivered_events (
      idempotency_key TEXT PRIMARY KEY,
      delivered_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);

  await pool.query(`
    CREATE TABLE IF NOT EXISTS reconciliation_cursors (
      chain_type TEXT PRIMARY KEY,
      from_block TEXT NOT NULL,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);

  await pool.query(`
    CREATE TABLE IF NOT EXISTS blink_pending_invoices (
      reference TEXT PRIMARY KEY,
      payment_hash TEXT NOT NULL UNIQUE,
      payload JSONB NOT NULL,
      updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
    )
  `);

  await pool.query(
    "CREATE INDEX IF NOT EXISTS idx_deposit_queue_next_attempt_at ON deposit_queue_events (next_attempt_at)"
  );
  await pool.query(
    "CREATE INDEX IF NOT EXISTS idx_deposit_delivered_events_delivered_at ON deposit_delivered_events (delivered_at)"
  );
  await pool.query(
    "CREATE INDEX IF NOT EXISTS idx_blink_pending_invoices_payment_hash ON blink_pending_invoices (payment_hash)"
  );

  return pool;
}