import { DurableObject } from "cloudflare:workers";
import { unreadNotificationCount } from "./notification-store";
import type { InvolvementEntry, InvolvementQuery, InvolvementRole } from "./involvement";

/** Bounds drift from writes that bypass `invalidateUnread`, e.g. repo deletes cascading notifications. */
const CACHE_TTL_MS = 5 * 60 * 1000;
const MAX_INVOLVEMENT_ROWS = 50_000;
const MAX_LIST = 200;

type Cached = { count: number; at: number };

type InvolvementRow = {
  issue_id: string;
  repo_id: string;
  number: number;
  is_pull: number;
  state: string;
  title: string;
  updated_at: number;
  roles: string;
};

/**
 * One per user. Holds the unread-notification count so the header badge on every page view is an
 * in-memory read on a per-user object instead of a COUNT on D1. Writers call `invalidate`; a version
 * number keeps a count computed before an invalidation from being cached after it.
 *
 * Also holds the user's involvement index: every issue and pull request they authored, are assigned to or
 * were asked to review, pushed here by each repository's RepoState. Cross-repo lists ("assigned to me",
 * "review requested") read this instead of joining across every repository.
 */
export class UserInbox extends DurableObject<Env> {
  private cached: Cached | null = null;
  private version = 0;
  private involvementReady = false;

  private get sql(): SqlStorage {
    const sql = this.ctx.storage.sql;
    if (!this.involvementReady) {
      sql.exec(`CREATE TABLE IF NOT EXISTS involvement (
        issue_id TEXT PRIMARY KEY,
        repo_id TEXT NOT NULL,
        number INTEGER NOT NULL,
        is_pull INTEGER NOT NULL,
        state TEXT NOT NULL,
        title TEXT NOT NULL,
        updated_at INTEGER NOT NULL,
        roles TEXT NOT NULL
      )`);
      sql.exec("CREATE INDEX IF NOT EXISTS involvement_state ON involvement(state, updated_at DESC)");
      sql.exec("CREATE INDEX IF NOT EXISTS involvement_repo ON involvement(repo_id)");
      this.involvementReady = true;
    }
    return sql;
  }

  async unread(userId: string): Promise<number> {
    if (this.cached && Date.now() - this.cached.at < CACHE_TTL_MS) return this.cached.count;
    const version = this.version;
    const count = await unreadNotificationCount(userId);
    if (version === this.version) this.cached = { count, at: Date.now() };
    return count;
  }

  /** Counts one email in the current window; false once `limit` is spent. Storage-backed so restarts keep it. */
  async takeEmailBudget(limit: number, windowMs: number): Promise<boolean> {
    const window = Math.floor(Date.now() / windowMs) * windowMs;
    const current = await this.ctx.storage.get<{ window: number; sent: number }>("email-budget");
    const sent = current?.window === window ? current.sent + 1 : 1;
    await this.ctx.storage.put("email-budget", { window, sent });
    return sent <= limit;
  }

  async invalidate(): Promise<void> {
    this.version++;
    this.cached = null;
  }

  /** Records (or refreshes) one item this user is involved in, with every role they hold on it. */
  async putInvolvement(entry: InvolvementEntry, roles: InvolvementRole[]): Promise<void> {
    const sql = this.sql;
    if (roles.length === 0) {
      sql.exec("DELETE FROM involvement WHERE issue_id = ?", entry.issueId);
      return;
    }
    sql.exec(
      `INSERT INTO involvement (issue_id, repo_id, number, is_pull, state, title, updated_at, roles)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?)
       ON CONFLICT (issue_id) DO UPDATE SET repo_id = excluded.repo_id, number = excluded.number,
         is_pull = excluded.is_pull, state = excluded.state, title = excluded.title,
         updated_at = excluded.updated_at, roles = excluded.roles`,
      entry.issueId,
      entry.repoId,
      entry.number,
      entry.isPull ? 1 : 0,
      entry.state,
      entry.title,
      entry.updatedAt,
      [...new Set(roles)].sort().join(","),
    );
    this.trim();
  }

  async dropInvolvement(issueIds: string[]): Promise<void> {
    const sql = this.sql;
    for (const issueId of issueIds) sql.exec("DELETE FROM involvement WHERE issue_id = ?", issueId);
  }

  /** Repositories the user is involved in (authored, assigned, reviewing), for account deletion. */
  async involvedRepoIds(): Promise<string[]> {
    return this.sql
      .exec<{ repo_id: string }>("SELECT DISTINCT repo_id FROM involvement")
      .toArray()
      .map((row) => row.repo_id);
  }

  /** Drops everything from one repository, for when it is deleted. */
  async dropRepoInvolvement(repoId: string): Promise<void> {
    this.sql.exec("DELETE FROM involvement WHERE repo_id = ?", repoId);
  }

  /** Newest first. Callers re-check repository access: this index does not know who can read what. */
  async listInvolvement(query: InvolvementQuery): Promise<InvolvementEntry[]> {
    const conditions = ["1 = 1"];
    const params: Array<string | number> = [];
    if (query.state) {
      conditions.push("state = ?");
      params.push(query.state);
    }
    if (query.kind) {
      conditions.push("is_pull = ?");
      params.push(query.kind === "pull" ? 1 : 0);
    }
    if (query.roles?.length) {
      conditions.push(`(${query.roles.map(() => "instr(',' || roles || ',', ?) > 0").join(" OR ")})`);
      params.push(...query.roles.map((role) => `,${role},`));
    }
    const limit = Math.min(Math.max(1, query.limit ?? 50), MAX_LIST);
    const rows = this.sql
      .exec<InvolvementRow>(
        `SELECT * FROM involvement WHERE ${conditions.join(" AND ")} ORDER BY updated_at DESC LIMIT ? OFFSET ?`,
        ...params,
        limit,
        Math.max(0, query.offset ?? 0),
      )
      .toArray();
    return rows.map((row) => ({
      issueId: row.issue_id,
      repoId: row.repo_id,
      number: row.number,
      isPull: row.is_pull === 1,
      state: row.state,
      title: row.title,
      updatedAt: row.updated_at,
      roles: row.roles.split(",") as InvolvementRole[],
    }));
  }

  /** Keeps a very active user's index bounded: the oldest closed items go first. */
  private trim(): void {
    const sql = this.sql;
    const count = sql.exec<{ total: number }>("SELECT COUNT(*) AS total FROM involvement").one().total;
    if (count <= MAX_INVOLVEMENT_ROWS) return;
    sql.exec(
      `DELETE FROM involvement WHERE issue_id IN (
         SELECT issue_id FROM involvement ORDER BY state = 'open', updated_at LIMIT ?)`,
      count - MAX_INVOLVEMENT_ROWS,
    );
  }
}