nekonest-cloud/db/access-requests.ts

import { PUBLIC_BETA_GATE_READY_SQL } from "./launch-gates.ts";

export type BetaAccessRequestStatus =
  | "requested"
  | "approved"
  | "declined"
  | "cancelled";

/**
 * Reserve one account-level pending request by first reserving its replay key.
 * Parameters: scope, key, hash, response JSON, expiry, now, account id.
 */
export const CREATE_ACCESS_REQUEST_IDEMPOTENCY_SQL = `
  INSERT INTO idempotency_records
    (scope, key, request_hash, response_json, status_code, expires_at, created_at)
  SELECT ?1, ?2, ?3, ?4, 201, ?5, ?6
  WHERE NOT EXISTS (
    SELECT 1 FROM beta_access_requests
    WHERE account_id = ?7 AND status = 'requested'
  )
    AND NOT EXISTS (
      SELECT 1 FROM entitlement_grants
      WHERE account_id = ?7 AND state = 'active' AND starts_at <= ?6
        AND (ends_at IS NULL OR ends_at > ?6) AND revoked_at IS NULL
    )
    AND NOT EXISTS (
      SELECT 1 FROM beta_programs
      WHERE state = 'active' AND starts_at <= ?6
        AND (ends_at IS NULL OR ends_at > ?6)
        AND ${PUBLIC_BETA_GATE_READY_SQL}
    )
`;

/** Parameters: id, account, OS, slots, use case, now, scope, key, hash. */
export const CREATE_ACCESS_REQUEST_SQL = `
  INSERT INTO beta_access_requests
    (id, account_id, status, preferred_os, requested_slots, use_case,
     requested_at, created_at, updated_at)
  SELECT ?1, ?2, 'requested', ?3, ?4, ?5, ?6, ?6, ?6
  WHERE EXISTS (
    SELECT 1 FROM idempotency_records
    WHERE scope = ?7 AND key = ?8 AND request_hash = ?9
  )
`;

/**
 * Reserve cancellation only while the same account still owns a pending row.
 * Parameters: scope, key, hash, response JSON, expiry, now, request id, account.
 */
export const CREATE_ACCESS_CANCELLATION_IDEMPOTENCY_SQL = `
  INSERT INTO idempotency_records
    (scope, key, request_hash, response_json, status_code, expires_at, created_at)
  SELECT ?1, ?2, ?3, ?4, 200, ?5, ?6
  FROM beta_access_requests
  WHERE id = ?7 AND account_id = ?8 AND status = 'requested'
`;

/** Parameters: now, request id, account, scope, key, hash. */
export const CANCEL_ACCESS_REQUEST_SQL = `
  UPDATE beta_access_requests
  SET status = 'cancelled', cancelled_at = ?1, updated_at = ?1
  WHERE id = ?2 AND account_id = ?3 AND status = 'requested'
    AND EXISTS (
      SELECT 1 FROM idempotency_records
      WHERE scope = ?4 AND key = ?5 AND request_hash = ?6
    )
`;

/**
 * Reserve an administrator decision only while the request is pending.
 * Parameters: scope, key, hash, response JSON, status code, expiry, now, request.
 */
export const CREATE_ACCESS_RESOLUTION_IDEMPOTENCY_SQL = `
  INSERT INTO idempotency_records
    (scope, key, request_hash, response_json, status_code, expires_at, created_at)
  SELECT ?1, ?2, ?3, ?4, ?5, ?6, ?7
  FROM beta_access_requests
  WHERE id = ?8 AND status = 'requested'
`;

/**
 * Create the non-monetary invitation for an approved request.
 * Parameters: grant id, request id/source ref, capacity, now, end, reason,
 * actor, request id, scope, key, hash.
 */
export const CREATE_APPROVED_INVITATION_SQL = `
  INSERT INTO entitlement_grants
    (id, account_id, host_id, source, source_ref, capacity_slots,
     starts_at, ends_at, state, reason, created_by, created_at)
  SELECT ?1, account_id, NULL, 'admin_exemption', ?2, ?3,
         ?4, ?5, 'active', ?6, ?7, ?4
  FROM beta_access_requests
  WHERE id = ?8 AND status = 'requested'
    AND EXISTS (
      SELECT 1 FROM idempotency_records
      WHERE scope = ?9 AND key = ?10 AND request_hash = ?11
    )
`;

/** Parameters: response, actor, grant id, now, request id, scope, key, hash. */
export const APPROVE_ACCESS_REQUEST_SQL = `
  UPDATE beta_access_requests
  SET status = 'approved', admin_response = ?1, resolved_by = ?2,
      invitation_grant_id = ?3, resolved_at = ?4, updated_at = ?4
  WHERE id = ?5 AND status = 'requested'
    AND EXISTS (SELECT 1 FROM entitlement_grants WHERE id = ?3)
    AND EXISTS (
      SELECT 1 FROM idempotency_records
      WHERE scope = ?6 AND key = ?7 AND request_hash = ?8
    )
`;

/** Parameters: response, actor, now, request id, scope, key, hash. */
export const DECLINE_ACCESS_REQUEST_SQL = `
  UPDATE beta_access_requests
  SET status = 'declined', admin_response = ?1, resolved_by = ?2,
      resolved_at = ?3, updated_at = ?3
  WHERE id = ?4 AND status = 'requested'
    AND EXISTS (
      SELECT 1 FROM idempotency_records
      WHERE scope = ?5 AND key = ?6 AND request_hash = ?7
    )
`;

/**
 * Manual invitations are only for proactive invitations. When an account has
 * a pending request, administrators must resolve that request so user-visible
 * state and the grant cannot diverge.
 * Parameters: scope, key, hash, response JSON, expiry, now, account id.
 */
export const CREATE_MANUAL_INVITATION_IDEMPOTENCY_SQL = `
  INSERT INTO idempotency_records
    (scope, key, request_hash, response_json, status_code, expires_at, created_at)
  SELECT ?1, ?2, ?3, ?4, 201, ?5, ?6
  WHERE NOT EXISTS (
    SELECT 1 FROM beta_access_requests
    WHERE account_id = ?7 AND status = 'requested'
  )
`;

/** Parameters: grant fields followed by scope, key and hash. */
export const CREATE_MANUAL_INVITATION_SQL = `
  INSERT INTO entitlement_grants
    (id, account_id, host_id, source, source_ref, capacity_slots,
     starts_at, ends_at, state, reason, created_by, created_at)
  SELECT ?1, ?2, NULL, 'admin_exemption', ?3, ?4,
         ?5, ?6, 'active', ?7, ?8, ?9
  WHERE EXISTS (
    SELECT 1 FROM idempotency_records
    WHERE scope = ?10 AND key = ?11 AND request_hash = ?12
  )
`;

export const PUBLIC_BETA_ACCESS_RESPONSE =
  "公开免费测试已经开放，当前不再需要单独闭测邀请。";

/**
 * Close stale pending requests only when an active public beta and every P0
 * evidence gate are simultaneously true inside the same D1 batch.
 * Parameters: user-facing response, actor, now.
 */
export const FULFILL_ACCESS_REQUESTS_BY_PUBLIC_BETA_SQL = `
  UPDATE beta_access_requests
  SET status = 'approved', admin_response = ?1, resolved_by = ?2,
      invitation_grant_id = NULL, resolved_at = ?3, updated_at = ?3
  WHERE status = 'requested'
    AND EXISTS (
      SELECT 1 FROM beta_programs
      WHERE state = 'active' AND starts_at <= ?3
        AND (ends_at IS NULL OR ends_at > ?3)
        AND ${PUBLIC_BETA_GATE_READY_SQL}
    )
`;

/** Parameters: actor, response, correlation id, now. */
export const AUDIT_PUBLIC_BETA_ACCESS_FULFILLMENT_SQL = `
  INSERT OR IGNORE INTO audit_events
    (id, actor_id, action, target_type, target_id, reason,
     before_json, after_json, correlation_id, created_at)
  SELECT 'audit_public_beta_' || substr(id, 8), ?1,
         'beta_access.fulfilled_by_public_beta', 'beta_access_request', id,
         '公开免费测试开放，待审申请无需单独邀请',
         '{"status":"requested"}',
         json_object('status', 'approved', 'adminResponse', ?2,
                     'invitationGrantId', NULL),
         ?3, ?4
  FROM beta_access_requests
  WHERE status = 'approved' AND resolved_by = ?1 AND resolved_at = ?4
    AND invitation_grant_id IS NULL AND admin_response = ?2
`;