import { all, first, run } from "../db";
import { embeddedScope } from "./ids";

/**
 * Kinds of rows that live in a scoped object but are looked up by id or credential hash alone. Add a kind here
 * (and register rows on insert) when moving a table whose rows are addressed without their repo or account.
 */
export type LocatorKind =
  | "issue"
  | "comment"
  | "review"
  | "review_comment"
  | "discussion"
  | "discussion_comment"
  | "release"
  | "release_asset"
  | "workflow_run"
  | "workflow_job"
  | "job_artifact"
  | "job_token"
  | "deploy_token"
  | "webhook"
  | "attachment"
  | "security_advisory"
  | "site"
  | "site_deployment"
  | "site_host"
  | "site_claim"
  | "session"
  | "access_token"
  | "access_token_id"
  | "auth_token"
  | "oauth_code"
  | "oauth_refresh"
  | "unsubscribe"
  | "passkey"
  | "runner"
  | "runner_token"
  | "runner_registration"
  | "org_invitation"
  | "project"
  | "project_item"
  | "stripe_customer"
  | "credit_payment"
  | "scratch_claim"
  | "scratch_account"
  | "editor_session"
  | "mergefix_run"
  // Key and scope are the account id: accounts holding a custom-domain add-on, for the quantity reconcile.
  | "domain_addon"
  // Key `<owner id>:<site id>`, scope `site:<id>`: repo-less sites an account claimed, found by key prefix.
  | "owner_site"
  // Key is the site id, scope its store: sites holding a custom domain, for the domain sweeps.
  | "site_domain"
  // Key `<repo id>:<account id>`, scope the account: accounts that opened editor containers on a repository.
  | "editor_repo_account"
  // Key `<expires_at, zero-padded>:<site id>`, scope the site's store: unclaimed scratch sites in expiry order.
  | "scratch_expiry"
  // Key `<inviter id>:<invitation id>`, scope the org: invitations a user sent, for clearing them.
  | "org_invitation_by";

/** The repo or account id that owns this row, or null when nothing is registered. */
export async function locate(kind: LocatorKind, key: string): Promise<string | null> {
  const embedded = embeddedScope(key);
  if (embedded) return embedded;
  const row = await first<{ scope_id: string }>(
    "SELECT scope_id FROM scope_locators WHERE kind = ? AND key = ?",
    kind,
    key,
  );
  return row?.scope_id ?? null;
}

/** Register when inserting a row other requests will find by id or hash; idempotent. */
export async function registerLocator(kind: LocatorKind, key: string, scopeId: string): Promise<void> {
  if (embeddedScope(key) === scopeId) return;
  await run(
    "INSERT INTO scope_locators (kind, key, scope_id) VALUES (?, ?, ?) ON CONFLICT (kind, key) DO UPDATE SET scope_id = excluded.scope_id",
    kind,
    key,
    scopeId,
  );
}

/** Registers several rows; ids that carry their scope are skipped. */
export async function registerLocators(entries: Array<[LocatorKind, string, string]>): Promise<void> {
  await Promise.all(entries.map(([kind, key, scopeId]) => registerLocator(kind, key, scopeId)));
}

export async function forgetLocator(kind: LocatorKind, key: string): Promise<void> {
  if (embeddedScope(key)) return;
  await run("DELETE FROM scope_locators WHERE kind = ? AND key = ?", kind, key);
}

/** Drops every locator pointing into a deleted repository or account. */
export async function forgetScope(scopeId: string): Promise<void> {
  await run("DELETE FROM scope_locators WHERE scope_id = ?", scopeId);
}

/** Every scope holding at least one row of `kind`, for the few sweeps that must visit them all. */
export async function locatedScopes(kind: LocatorKind): Promise<string[]> {
  const rows = await all<{ scope_id: string }>("SELECT DISTINCT scope_id FROM scope_locators WHERE kind = ?", kind);
  return rows.map((row) => row.scope_id);
}

/** Up to `limit` scopes holding `kind` rows, in id order from `from` (inclusive), for sweeps that page with a cursor. */
export async function locatedScopesFrom(kind: LocatorKind, from: string, limit: number): Promise<string[]> {
  const rows = await all<{ scope_id: string }>(
    "SELECT DISTINCT scope_id FROM scope_locators WHERE kind = ? AND scope_id >= ? ORDER BY scope_id LIMIT ?",
    kind,
    from,
    limit,
  );
  return rows.map((row) => row.scope_id);
}

/** The first `limit` `kind` rows whose key sorts before `before`, in key order, for queues keyed by time. */
export async function locatorsBefore(
  kind: LocatorKind,
  before: string,
  limit: number,
): Promise<Array<{ key: string; scope_id: string }>> {
  return all<{ key: string; scope_id: string }>(
    "SELECT key, scope_id FROM scope_locators WHERE kind = ? AND key < ? ORDER BY key LIMIT ?",
    kind,
    before,
    limit,
  );
}

/** Which of `scopeIds` hold at least one row of `kind`, through the scope index rather than every `kind` row. */
export async function locatedScopesAmong(kind: LocatorKind, scopeIds: string[]): Promise<string[]> {
  if (!scopeIds.length) return [];
  const rows = await all<{ scope_id: string }>(
    "SELECT DISTINCT scope_id FROM scope_locators WHERE scope_id IN (SELECT value FROM json_each(?)) AND kind = ?",
    JSON.stringify(scopeIds),
    kind,
  );
  return rows.map((row) => row.scope_id);
}

/** Scopes of the `kind` rows whose key starts with `prefix`, read as one range of the (kind, key) index. */
export async function locatedByPrefix(kind: LocatorKind, prefix: string): Promise<string[]> {
  const end = prefix.slice(0, -1) + String.fromCharCode(prefix.charCodeAt(prefix.length - 1) + 1);
  const rows = await all<{ scope_id: string }>(
    "SELECT DISTINCT scope_id FROM scope_locators WHERE kind = ? AND key >= ? AND key < ?",
    kind,
    prefix,
    end,
  );
  return rows.map((row) => row.scope_id);
}