import { DurableObject } from "cloudflare:workers";
import { repoDb } from "./scoped-db";

const RETENTION_MS = 30 * 24 * 60 * 60 * 1000;
const MAX_PER_HOOK = 500;
const PRUNE_EVERY_MS = 24 * 60 * 60 * 1000;
const MAX_LIST = 100;

export type DeliveryRecord = {
  id: string;
  webhook_id: string;
  event: string;
  status_code: number | null;
  error: string | null;
  duration_ms: number | null;
  attempt: number;
  created_at: number;
};

export type StoredDelivery = DeliveryRecord & { request_body: string };

/**
 * One per repository: the outbound webhook delivery log. Every delivery and retry appends here instead of
 * to D1, so log volume scales with repos rather than landing on the shared writer. Retention (30 days,
 * MAX_PER_HOOK rows per hook) runs on the object's own alarm, which also drops rows of deleted hooks.
 */
export class WebhookLog extends DurableObject<Env> {
  private ready = false;

  private init(): void {
    if (this.ready) return;
    this.ctx.storage.sql.exec(`CREATE TABLE IF NOT EXISTS deliveries (
      id TEXT PRIMARY KEY,
      webhook_id TEXT NOT NULL,
      event TEXT NOT NULL,
      status_code INTEGER,
      error TEXT,
      request_body TEXT NOT NULL,
      duration_ms INTEGER,
      attempt INTEGER NOT NULL,
      created_at INTEGER NOT NULL
    )`);
    this.ctx.storage.sql.exec("CREATE INDEX IF NOT EXISTS deliveries_hook ON deliveries(webhook_id, created_at DESC)");
    this.ready = true;
  }

  async record(repoId: string, delivery: StoredDelivery): Promise<void> {
    this.init();
    this.ctx.storage.sql.exec(
      `INSERT OR REPLACE INTO deliveries (id, webhook_id, event, status_code, error, request_body, duration_ms, attempt, created_at)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
      delivery.id,
      delivery.webhook_id,
      delivery.event,
      delivery.status_code,
      delivery.error,
      delivery.request_body,
      delivery.duration_ms,
      delivery.attempt,
      delivery.created_at,
    );
    if ((await this.ctx.storage.getAlarm()) === null) {
      await this.ctx.storage.put("repoId", repoId);
      await this.ctx.storage.setAlarm(Date.now() + PRUNE_EVERY_MS);
    }
  }

  /** Newest first; `hookId` narrows to one hook. */
  async list(hookId: string | null, limit: number): Promise<DeliveryRecord[]> {
    this.init();
    const capped = Math.min(Math.max(1, limit), MAX_LIST);
    const columns = "id, webhook_id, event, status_code, error, duration_ms, attempt, created_at";
    return hookId
      ? this.ctx.storage.sql
          .exec<DeliveryRecord>(
            `SELECT ${columns} FROM deliveries WHERE webhook_id = ? ORDER BY created_at DESC LIMIT ?`,
            hookId,
            capped,
          )
          .toArray()
      : this.ctx.storage.sql
          .exec<DeliveryRecord>(`SELECT ${columns} FROM deliveries ORDER BY created_at DESC LIMIT ?`, capped)
          .toArray();
  }

  async get(deliveryId: string): Promise<StoredDelivery | null> {
    this.init();
    return (
      this.ctx.storage.sql
        .exec<StoredDelivery>(
          `SELECT id, webhook_id, event, status_code, error, request_body, duration_ms, attempt, created_at
           FROM deliveries WHERE id = ?`,
          deliveryId,
        )
        .toArray()[0] ?? null
    );
  }

  async clear(): Promise<void> {
    await this.ctx.storage.deleteAll();
    this.ready = false;
  }

  override async alarm(): Promise<void> {
    this.init();
    const repoId = await this.ctx.storage.get<string>("repoId");
    const sql = this.ctx.storage.sql;
    sql.exec("DELETE FROM deliveries WHERE created_at < ?", Date.now() - RETENTION_MS);
    sql.exec(
      `DELETE FROM deliveries WHERE id IN (
         SELECT id FROM (
           SELECT id, ROW_NUMBER() OVER (PARTITION BY webhook_id ORDER BY created_at DESC) AS position FROM deliveries
         ) WHERE position > ?)`,
      MAX_PER_HOOK,
    );
    if (repoId) {
      const live = new Set(
        (await repoDb(repoId).all<{ id: string }>("SELECT id FROM webhooks WHERE repo_id = ?", repoId)).map(
          (hook) => hook.id,
        ),
      );
      const hooks = sql.exec<{ webhook_id: string }>("SELECT DISTINCT webhook_id FROM deliveries").toArray();
      for (const hook of hooks) {
        if (!live.has(hook.webhook_id)) sql.exec("DELETE FROM deliveries WHERE webhook_id = ?", hook.webhook_id);
      }
    }
    const remaining = sql.exec<{ count: number }>("SELECT COUNT(*) AS count FROM deliveries").one().count;
    if (remaining === 0) {
      await this.ctx.storage.deleteAll();
      this.ready = false;
      return;
    }
    await this.ctx.storage.setAlarm(Date.now() + PRUNE_EVERY_MS);
  }
}