import { all, batch, first, run, stmt } from "./db";
import { badRequest, conflict } from "./errors";
import { loadRepoById } from "./repos";
import { deleteRepository } from "./repo-ops";
import { deleteRunnerRow } from "./ci/runner-registration";
import { disableSite } from "./sites/site";
import { sitesOwnedBy } from "./sites/store";
import { accountDb, repoDb, stmt as scopedStmt } from "./scoped-db";
import { dropAccountState, forEachScope } from "./account-scope";
import { backfillDone } from "./backfill";
import { invitationOrgs } from "./invitation-index";
import { ACTIVITY_INDEX, touchedRepoIds } from "./activity-index";
import { userInbox } from "./user-inbox";
import { revokeAccessTokensWhere } from "./access-token-store";
import { revokeAllDeployTokensCreatedBy } from "./deploy-token-index";
import { ACTIVE_STATUSES, customerFor } from "./billing/checkout";
import { stripe, stripeEnabled } from "./billing/config";
import { cancelDomainAddon } from "./billing/domain-addon";
import { emailTemplates, queueEmail } from "./email";
import { GHOST } from "./people";
import { discardAccountExport } from "./account-export";
import type { Viewer } from "~/lib/types";

export const MAX_USER_REPOS_DELETED = 100;

/**
 * Authored rows that must outlive the account so other people's conversations stay intact. They are
 * reassigned to the shared `ghost` account (GitHub's model) because these columns are NOT NULL or carry
 * no ON DELETE action; everything else keyed to the user cascades or is nulled with the account row.
 */
const GHOSTED_COLUMNS = [
  ["issues", "author_id"],
  ["issues", "closed_by"],
  ["comments", "author_id"],
  ["issue_events", "actor_id"],
  ["reviews", "author_id"],
  ["reviews", "dismissed_by"],
  ["review_comments", "author_id"],
  ["pull_requests", "merged_by"],
  ["pull_auto_merges", "enabled_by"],
  ["merge_queue_entries", "enqueued_by"],
  ["pull_issue_links", "created_by"],
  ["discussions", "author_id"],
  ["discussion_comments", "author_id"],
  ["attachments", "uploader_id"],
  ["releases", "author_id"],
  ["workflow_runs", "actor_id"],
  ["push_events", "pusher_id"],
] as const;

/** Ghosted columns in account objects (projects belong to their owning account). */
const GHOSTED_ACCOUNT_COLUMNS = [["projects", "created_by"]] as const;

/**
 * Repositories that can hold the user's authored rows: those their activity index and involvement index list.
 * Elsewhere an authored id that no longer names an account already renders as `ghost` (`accountOrGhost`), and
 * account ids are never reused. Until the activity index is backfilled, every repository.
 */
async function authoredRepoIds(userId: string): Promise<string[]> {
  if (!(await backfillDone(ACTIVITY_INDEX))) {
    return (await all<{ id: string }>("SELECT id FROM repos")).map((row) => row.id);
  }
  const [active, involved] = await Promise.all([touchedRepoIds(userId), userInbox(userId).involvedRepoIds()]);
  return [...new Set([...active, ...involved])];
}

/**
 * Reassigns the user's authored rows to `ghost` and drops their activity in the repositories that can hold them,
 * and clears projects, invitations and security-manager seats in their own account and the organizations they
 * belong to (call before memberships are deleted). Nothing walks every repository or account.
 */
async function ghostAuthoredRows(userId: string): Promise<void> {
  await forEachScope(await authoredRepoIds(userId), async (repoId) => {
    await repoDb(repoId).batch([
      ...GHOSTED_COLUMNS.map(([table, column]) =>
        scopedStmt(`UPDATE ${table} SET ${column} = ? WHERE ${column} = ?`, GHOST.id, userId),
      ),
      scopedStmt("DELETE FROM activity WHERE actor_id = ?", userId),
    ]);
  });
  const orgs = await all<{ org_id: string }>("SELECT org_id FROM org_members WHERE user_id = ?", userId);
  // Invitations to the user can sit in orgs it never joined, and ones it sent in orgs it has since left.
  const scopes = await invitationOrgs(
    userId,
    orgs.map((row) => row.org_id),
  );
  await forEachScope([userId, ...scopes], async (accountId) => {
    await accountDb(accountId).batch([
      ...GHOSTED_ACCOUNT_COLUMNS.map(([table, column]) =>
        scopedStmt(`UPDATE ${table} SET ${column} = ? WHERE ${column} = ?`, GHOST.id, userId),
      ),
      scopedStmt("DELETE FROM org_invitations WHERE user_id = ?", userId),
      scopedStmt("UPDATE org_invitations SET invited_by = NULL WHERE invited_by = ?", userId),
      scopedStmt("DELETE FROM security_managers WHERE user_id = ?", userId),
    ]);
  });
}

/** Organizations where this user is the only owner: deleting the user would orphan them. */
export async function soleOwnedOrgs(userId: string): Promise<string[]> {
  const rows = await all<{ name: string }>(
    `SELECT a.name FROM org_members m JOIN accounts a ON a.id = m.org_id
     WHERE m.user_id = ?1 AND m.role = 'owner'
       AND NOT EXISTS (SELECT 1 FROM org_members o WHERE o.org_id = m.org_id AND o.role = 'owner' AND o.user_id != ?1)
     ORDER BY a.name`,
    userId,
  );
  return rows.map((row) => row.name);
}

/** Stops billing before anything is deleted, so a Stripe failure leaves the account intact. A later failure leaves it
 * partly deleted but still signed in, and retrying finishes the job. */
async function cancelSubscription(userId: string): Promise<void> {
  const customer = await customerFor(userId);
  if (!customer?.subscription_id || !ACTIVE_STATUSES.has(customer.subscription_status ?? "")) return;
  if (!stripeEnabled()) throw conflict("Billing is unavailable right now; try again later");
  await stripe().subscriptions.cancel(customer.subscription_id, { prorate: false });
}

/**
 * Self-service account deletion (the caller has already re-authenticated). Refuses while the user is the sole
 * owner of an organization. Cancels the subscription, deletes every owned repository through
 * `deleteRepository`, reassigns authored content on other repositories to `ghost`, retires the username
 * and its old names, and deletes the account row, which cascades sessions, tokens, keys, emails, stars,
 * follows, memberships and settings. Billing ledgers are kept (no FK) for tax records.
 */
export async function deleteUserAccount(input: {
  viewer: Viewer;
  confirm: string;
}): Promise<{ deletedRepos: string[] }> {
  const { viewer } = input;
  if (input.confirm !== viewer.name) throw badRequest(`Type ${viewer.name} to confirm`);
  if (viewer.isAdmin && !(await first("SELECT 1 AS ok FROM users WHERE is_admin = 1 AND account_id != ?", viewer.id))) {
    throw conflict("You are the only administrator of this instance; promote another administrator first");
  }
  const orgs = await soleOwnedOrgs(viewer.id);
  if (orgs.length > 0) {
    throw conflict(`You are the only owner of ${orgs.join(", ")}. Transfer ownership or delete them first.`);
  }
  const repos = await all<{ id: string; name: string }>(
    "SELECT id, name FROM repos WHERE owner_id = ? ORDER BY name",
    viewer.id,
  );
  if (repos.length > MAX_USER_REPOS_DELETED) {
    throw conflict(`Too many repositories to delete at once (${repos.length}); delete some first`);
  }
  const user = await first<{ email: string; email_verified_at: number | null }>(
    "SELECT email, email_verified_at FROM users WHERE account_id = ?",
    viewer.id,
  );
  if (!user) throw badRequest("Account not found");

  await cancelSubscription(viewer.id);
  await cancelDomainAddon(viewer.id);
  for (const { id } of repos) {
    const repo = await loadRepoById(id);
    if (repo) await deleteRepository(repo, viewer);
  }
  const runners = await accountDb(viewer.id).all<{ id: string }>(
    "SELECT id FROM runners WHERE owner_id = ?",
    viewer.id,
  );
  for (const runner of runners) await deleteRunnerRow(runner.id);
  // Sites without a repository (claimed uploads) are not torn down by deleteRepository.
  const sites = await sitesOwnedBy(viewer.id);
  for (const site of sites) await disableSite(site);
  // Revoked explicitly (not just wiped with the AccountState) so deploy tokens they minted go too.
  await revokeAccessTokensWhere(viewer.id, "1 = 1");
  await revokeAllDeployTokensCreatedBy(viewer.id);

  const now = Date.now();
  const previousNames = await all<{ old_name: string }>(
    "SELECT old_name FROM account_redirects WHERE account_id = ?",
    viewer.id,
  );
  await run(
    "INSERT OR IGNORE INTO accounts (id, name, kind, display_name, created_at) VALUES (?, ?, 'user', ?, 0)",
    GHOST.id,
    GHOST.name,
    GHOST.displayName,
  );
  // Authored rows are reassigned before the account row goes, so a crash in between leaves no dangling author.
  await ghostAuthoredRows(viewer.id);
  await batch([
    ...[viewer.name, ...previousNames.map((row) => row.old_name)].map((name) =>
      stmt("INSERT OR IGNORE INTO retired_account_names (name, retired_at) VALUES (?, ?)", name, now),
    ),
    stmt("DELETE FROM accounts WHERE id = ?", viewer.id),
  ]);
  // The account's own object (sessions, keys, tokens, notifications, settings, AI key, ...) goes whole.
  await dropAccountState(viewer.id);
  await discardAccountExport(viewer.id);

  try {
    if (user.email_verified_at) await queueEmail(user.email, emailTemplates.accountDeleted(viewer.name));
  } catch (error) {
    console.error("Account deletion email failed", error);
  }
  return { deletedRepos: repos.map((repo) => repo.name) };
}