import { claimSignupSeat, releaseWaitlistSpot, seatGuard, SIGNUPS_FULL } from "./waitlist";
import { accountDb, stmt as scopedStmt } from "./scoped-db";
import { forgetLocator, registerLocator } from "./scoped-db/locators";
import { scopedId } from "./scoped-db/ids";
import { registerInviter } from "./invitation-index";
import { dropAccountState } from "./account-scope";
import { accountsById } from "./people";
import { env } from "cloudflare:workers";
import { all, batch, first, run, stmt } from "./db";
import { hashPassword, randomId } from "./crypto";
import { badRequest, conflict, forbidden, notFoundError } from "./errors";
import { canAdministerAccount } from "./repos";
import { scheduleRevocationSweep, sweepRevokedOrgAccess } from "./pull-guard";
import type { OrgDefaultPermission } from "./repos";
import { assertBelowCap } from "./limits";
import { assertVerifiedForWrites } from "./verified-writes";
import { purgeOrgFollowers, revokeDeployTokensCreatedBy } from "./access-revocation";
import type { Viewer } from "~/lib/types";
import { isAdminClaimableName, isReservedName } from "~/lib/reserved-names";
import { cleanDisplayName } from "~/lib/validation";
import { captureServerEvent } from "./analytics";
import { isAdminEmail, mayBootstrapAdminAtSignup, mayBypassClosedSignups } from "./admin-bootstrap";
import { userChanged } from "./user-row";

/** Names that still redirect stay retired so old URLs and git remotes can't be taken over; only their owner may reclaim one. */
export async function accountNameTaken(
  name: string,
  reclaimingAccountId: string | null = null,
  allowReserved = false,
): Promise<boolean> {
  if (isReservedName(name) && !allowReserved) return true;
  if ((await first<{ id: string }>("SELECT id FROM accounts WHERE name = ?", name)) !== null) return true;
  if ((await first("SELECT 1 AS ok FROM retired_account_names WHERE name = ? COLLATE NOCASE", name)) !== null) {
    return true;
  }
  const redirect = await first<{ account_id: string }>(
    "SELECT account_id FROM account_redirects WHERE old_name = ? COLLATE NOCASE",
    name,
  );
  return redirect !== null && redirect.account_id !== reclaimingAccountId;
}

/**
 * `SIGNUPS_ENABLED` is a boolean: `1`/`true`/`yes`/`on` (any case) open signups, anything else closes them
 * (AUTH-A7). Unset keeps the historical default of open.
 */
export function parseSignupsEnabled(raw: string | undefined): boolean {
  const value = raw?.trim();
  if (value === undefined || value === "") return true;
  return /^(1|true|yes|on)$/i.test(value);
}

export function signupsEnabled(): boolean {
  return parseSignupsEnabled(env.SIGNUPS_ENABLED);
}

/** A never-verified signup holds its email for this long before anyone else may claim it (AUTH-A2). */
export const UNVERIFIED_RESERVATION_MS = 24 * 60 * 60 * 1000;

/**
 * Unverified accounts that were never used: no verified email or GitHub link (both may write, AUTH-B1), older
 * than the reservation window, no repositories, organisations or admin flag. Stars, follows and settings never
 * count as use. Activity lives in each repository's RepoState now and is not consulted: without a verified
 * email or GitHub link the account cannot write anywhere, and it owns no repository, so the only activity it
 * can have is self-service (renames, deleting its own leftovers; AUTH-B1), which never counted. Deleting one frees its email and
 * username.
 */
const STALE_UNVERIFIED_WHERE = `u.email_verified_at IS NULL AND u.github_id IS NULL AND u.is_admin = 0
  AND a.created_at < ?
  AND NOT EXISTS (SELECT 1 FROM repos r WHERE r.owner_id = a.id)
  AND NOT EXISTS (SELECT 1 FROM org_members m WHERE m.user_id = a.id)`;

/**
 * Frees an email squatted by a stale unverified account so the inbox owner can sign up or link GitHub.
 * Returns whether a row was removed. Fresh unverified signups (inside the window) keep their reservation.
 */
export async function reclaimUnverifiedEmail(email: string): Promise<boolean> {
  const stale = await first<{ id: string }>(
    `SELECT a.id FROM users u JOIN accounts a ON a.id = u.account_id WHERE u.email = ? AND ${STALE_UNVERIFIED_WHERE}`,
    email,
    Date.now() - UNVERIFIED_RESERVATION_MS,
  );
  if (!stale) return false;
  const result = await run(
    "DELETE FROM accounts WHERE id = ? AND EXISTS (SELECT 1 FROM users u WHERE u.account_id = accounts.id AND u.email_verified_at IS NULL)",
    stale.id,
  );
  if (result.meta.changes === 0) return false;
  await dropAccountState(stale.id);
  return true;
}

/** Cron: drops stale unverified accounts so squatted emails and usernames do not stay reserved forever. */
export async function sweepUnverifiedAccounts(): Promise<void> {
  const stale = await all<{ id: string }>(
    `DELETE FROM accounts WHERE id IN (
       SELECT a.id FROM users u JOIN accounts a ON a.id = u.account_id WHERE ${STALE_UNVERIFIED_WHERE}) RETURNING id`,
    Date.now() - UNVERIFIED_RESERVATION_MS,
  );
  for (const account of stale) await dropAccountState(account.id);
}

export function githubOAuthEnabled(): boolean {
  return Boolean(env.GITHUB_CLIENT_ID && env.GITHUB_CLIENT_SECRET);
}

/**
 * The first user on an instance becomes its admin only if `mayBootstrapAdminAtSignup` allows it (a valid
 * BOOTSTRAP_TOKEN, or loopback dev); ADMIN_EMAIL is promoted on verification instead. With signups closed only
 * that same bootstrap account may be created. Both decisions are made inside the insert transaction rather
 * than by a separate COUNT, so two racing first signups cannot both win (AUTH-A6).
 */
export async function createUser(input: {
  name: string;
  email: string;
  password: string | null;
  githubId: string | null;
  displayName: string | null;
  bootstrapToken?: string | null;
  inviteToken?: string | null;
}): Promise<string> {
  if (await accountNameTaken(input.name)) throw conflict("That username is already taken");
  if (await first<{ account_id: string }>("SELECT account_id FROM users WHERE email = ?", input.email)) {
    if (!(await reclaimUnverifiedEmail(input.email))) {
      throw conflict("An account with that email already exists");
    }
  }
  if (await first("SELECT 1 FROM user_emails WHERE email = ? AND verified_at IS NOT NULL", input.email)) {
    throw conflict("An account with that email already exists");
  }
  const open = signupsEnabled();
  const bypass = !open && mayBypassClosedSignups(input.bootstrapToken);
  const hasUsers = (await first("SELECT 1 AS one FROM users LIMIT 1")) !== null;
  if (!open && (hasUsers || !bypass)) throw badRequest("Sign ups are disabled on this instance");
  // The bootstrap account is exempt from the cap: it is the only signup on an empty instance.
  const seat = open ? await claimSignupSeat(input.email, input.inviteToken) : null;
  const seatCondition = seat ? seatGuard(seat) : null;
  const id = randomId();
  const passwordHash = input.password ? await hashPassword(input.password) : null;
  // With signups closed only the bootstrap account may be created: both inserts are conditional on the
  // users table still being empty at commit time.
  const conditions = [open ? null : "NOT EXISTS (SELECT 1 FROM users)", seatCondition?.sql ?? null].filter(Boolean);
  const guard = conditions.length > 0 ? ` WHERE ${conditions.join(" AND ")}` : "";
  const guardParams = seatCondition?.params ?? [];
  const statements = [
    stmt(
      `INSERT INTO accounts (id, name, kind, display_name, created_at) SELECT ?, ?, 'user', ?, ?${guard}`,
      id,
      input.name,
      cleanDisplayName(input.displayName),
      Date.now(),
      ...guardParams,
    ),
    stmt(
      `INSERT INTO users (account_id, email, password_hash, github_id, is_admin) SELECT ?, ?, ?, ?, 0${guard}`,
      id,
      input.email,
      passwordHash,
      input.githubId,
      ...guardParams,
    ),
  ];
  if (mayBootstrapAdminAtSignup(input.bootstrapToken)) {
    statements.push(
      stmt(
        "UPDATE users SET is_admin = 1 WHERE account_id = ?1 AND NOT EXISTS (SELECT 1 FROM users WHERE account_id != ?1)",
        id,
      ),
    );
  }
  await batch(statements);
  if (!(await first("SELECT 1 AS ok FROM users WHERE account_id = ?", id))) {
    // The accounts row may have landed when only the users insert lost the race; drop the orphan.
    await run("DELETE FROM accounts WHERE id = ? AND NOT EXISTS (SELECT 1 FROM users WHERE account_id = ?)", id, id);
    throw badRequest(seatCondition ? SIGNUPS_FULL : "Sign ups are disabled on this instance");
  }
  if (seat) await releaseWaitlistSpot(input.email);
  captureServerEvent("user_signed_up", id, { method: input.githubId ? "github" : "password" });
  return id;
}

/**
 * Call once a user's primary email is proven: the ADMIN_EMAIL account becomes admin, but only while the
 * instance has no admin yet, so the variable can't be used to mint a second one later.
 */
export async function promoteAdminEmailOnVerify(userId: string): Promise<boolean> {
  const user = await first<{ email: string; email_verified_at: number | null }>(
    "SELECT email, email_verified_at FROM users WHERE account_id = ?",
    userId,
  );
  if (!user || user.email_verified_at === null || !isAdminEmail(user.email)) return false;
  const result = await run(
    "UPDATE users SET is_admin = 1 WHERE account_id = ? AND email_verified_at IS NOT NULL AND NOT EXISTS (SELECT 1 FROM users WHERE is_admin = 1)",
    userId,
  );
  if ((result.meta.changes ?? 0) > 0) await userChanged(userId);
  return (result.meta.changes ?? 0) > 0;
}

/** Creates an org owned by `ownerId`. A site admin may also take a project-reserved name (`isAdminClaimableName`). */
export async function createOrg(
  name: string,
  displayName: string | null,
  ownerId: string,
  owner: { isAdmin: boolean } = { isAdmin: false },
): Promise<string> {
  const claimable = owner.isAdmin && isAdminClaimableName(name);
  if (isReservedName(name) && !claimable) throw badRequest("That name is reserved");
  if (await accountNameTaken(name, null, claimable)) throw conflict("That name is already taken");
  const id = randomId();
  const now = Date.now();
  await batch([
    stmt(
      "INSERT INTO accounts (id, name, kind, display_name, created_at) VALUES (?, ?, 'org', ?, ?)",
      id,
      name,
      cleanDisplayName(displayName),
      now,
    ),
    stmt("INSERT INTO org_members (org_id, user_id, role, created_at) VALUES (?, ?, 'owner', ?)", id, ownerId, now),
  ]);
  captureServerEvent("org_created", ownerId, { org_id: id }, { ownerAccountId: id });
  return id;
}

export type OrgRole = "owner" | "member" | "none";

const INVITATION_TTL_MS = 7 * 24 * 60 * 60 * 1000;
const MAX_PENDING_INVITATIONS_PER_ORG = 200;

async function loadOrgByName(name: string): Promise<{ id: string; name: string }> {
  const org = await first<{ id: string; name: string }>(
    "SELECT id, name FROM accounts WHERE name = ? AND kind = 'org'",
    name,
  );
  if (!org) throw notFoundError("Organization not found");
  return org;
}

async function loadUserByName(name: string): Promise<{ id: string } | null> {
  return first<{ id: string }>(
    "SELECT a.id FROM accounts a JOIN users u ON u.account_id = a.id WHERE a.name = ?",
    name,
  );
}

const LAST_OWNER = "An organization needs at least one owner; add another owner first";

/** Checked inside the write itself so two concurrent demotions cannot both pass and leave the org ownerless. */
const KEEPS_AN_OWNER = `(role != 'owner' OR EXISTS (SELECT 1 FROM org_members o2 WHERE o2.org_id = ?1 AND o2.role = 'owner' AND o2.user_id != ?2))`;

/** A guarded write changed nothing: re-read so a concurrent change yields the right error instead of a stale one. */
async function explainBlockedWrite(orgId: string, userId: string): Promise<void> {
  const current = await first<{ role: string }>(
    "SELECT role FROM org_members WHERE org_id = ? AND user_id = ?",
    orgId,
    userId,
  );
  if (!current) throw notFoundError("That user is no longer a member of this organization");
  throw conflict(current.role === "owner" ? LAST_OWNER : "Membership changed concurrently; try again");
}

export type SetOrgMemberResult = { status: "member" | "invited" | "removed" };

/**
 * Re-roles or removes an org member, or invites a non-member. Org owners may do anything; any member may
 * remove themselves (leave). Nobody is added to an org without accepting an invitation (`acceptOrgInvitation`).
 * Removing or demoting the org's last owner is refused.
 */
export async function setOrgMember(input: {
  org: string;
  username: string;
  role: OrgRole;
  viewer: Viewer | null;
}): Promise<SetOrgMemberResult> {
  const org = await loadOrgByName(input.org);
  const user = await loadUserByName(input.username);
  const leaving = input.role === "none" && input.viewer !== null && user !== null && user.id === input.viewer.id;
  if (!leaving && !(await canAdministerAccount(org.id, input.viewer))) throw forbidden();
  if (!user) throw notFoundError("User not found");
  const membership = await first<{ role: string }>(
    "SELECT role FROM org_members WHERE org_id = ? AND user_id = ?",
    org.id,
    user.id,
  );
  if (leaving && !membership) throw notFoundError("You are not a member of this organization");
  if (input.role === "none") {
    const removed = await run(
      `DELETE FROM org_members WHERE org_id = ?1 AND user_id = ?2 AND ${KEEPS_AN_OWNER}`,
      org.id,
      user.id,
    );
    if (membership && removed.meta.changes === 0) await explainBlockedWrite(org.id, user.id);
    scheduleRevocationSweep(() => sweepRevokedOrgAccess(org.id, user.id));
    await deleteOrgInvitation(org.id, user.id);
    if (membership?.role === "owner") await deleteInvitationsSentBy(org.id, user.id);
    await purgeOrgFollowers(org.id, user.id);
    return { status: "removed" };
  }
  if (!membership) {
    await inviteOrgMember(org.id, user.id, input.role, input.viewer);
    return { status: "invited" };
  }
  const updated = await run(
    `UPDATE org_members SET role = ?3 WHERE org_id = ?1 AND user_id = ?2 AND (?3 = 'owner' OR ${KEEPS_AN_OWNER})`,
    org.id,
    user.id,
    input.role,
  );
  if (updated.meta.changes === 0) await explainBlockedWrite(org.id, user.id);
  if (membership.role === "owner" && input.role !== "owner") {
    await deleteInvitationsSentBy(org.id, user.id);
    // Demoted owners lose admin on the org's repositories: their deploy tokens there go (AUTH-P9).
    const repos = await all<{ id: string }>("SELECT id FROM repos WHERE owner_id = ?", org.id);
    await revokeDeployTokensCreatedBy(
      user.id,
      repos.map((repo) => repo.id),
    );
  }
  return { status: "member" };
}

async function inviteOrgMember(orgId: string, userId: string, role: "owner" | "member", viewer: Viewer | null) {
  const pending = await accountDb(orgId).first<{ count: number }>(
    "SELECT COUNT(*) AS count FROM org_invitations WHERE org_id = ? AND expires_at > ?",
    orgId,
    Date.now(),
  );
  assertBelowCap(pending?.count ?? 0, MAX_PENDING_INVITATIONS_PER_ORG, "pending invitations");
  const now = Date.now();
  const existing = await accountDb(orgId).first<{ id: string }>(
    "SELECT id FROM org_invitations WHERE org_id = ? AND user_id = ?",
    orgId,
    userId,
  );
  const id = existing?.id ?? scopedId(orgId);
  const upsert = scopedStmt(
    `INSERT INTO org_invitations (id, org_id, user_id, role, invited_by, created_at, expires_at) VALUES (?, ?, ?, ?, ?, ?, ?)
     ON CONFLICT (org_id, user_id) DO UPDATE SET role = excluded.role, invited_by = excluded.invited_by,
       created_at = excluded.created_at, expires_at = excluded.expires_at`,
    id,
    orgId,
    userId,
    role,
    viewer?.id ?? null,
    now,
    now + INVITATION_TTL_MS,
  );
  // The org's AccountState is the source of truth; the invitee's holds a copy for "my invitations".
  await accountDb(orgId).batch([upsert]);
  await accountDb(userId).batch([upsert]);
  await registerLocator("org_invitation", id, orgId);
  if (viewer) await registerInviter(viewer.id, id, orgId);
}

/** Removes an invitation from the org's AccountState and the invitee's copy. */
async function deleteOrgInvitation(orgId: string, userId: string): Promise<void> {
  const removed = await accountDb(orgId).all<{ id: string }>(
    "DELETE FROM org_invitations WHERE org_id = ? AND user_id = ? RETURNING id",
    orgId,
    userId,
  );
  await accountDb(userId).run("DELETE FROM org_invitations WHERE org_id = ? AND user_id = ?", orgId, userId);
  await Promise.all(removed.map((row) => forgetLocator("org_invitation", row.id)));
}

/** A former owner's pending invitations must not outlive their ownership. */
async function deleteInvitationsSentBy(orgId: string, inviterId: string): Promise<void> {
  const invitees = await accountDb(orgId).all<{ user_id: string }>(
    "SELECT user_id FROM org_invitations WHERE org_id = ? AND invited_by = ?",
    orgId,
    inviterId,
  );
  for (const invitee of invitees) await deleteOrgInvitation(orgId, invitee.user_id);
}

/** Whether the inviter still holds the standing an invitation needs: an owner of the org or a site admin. */
async function inviterStillAuthorized(orgId: string, inviterId: string | null): Promise<boolean> {
  if (!inviterId) return false;
  const standing = await first<{ ok: number }>(
    `SELECT 1 AS ok WHERE EXISTS (SELECT 1 FROM org_members WHERE org_id = ?1 AND user_id = ?2 AND role = 'owner')
       OR EXISTS (SELECT 1 FROM users WHERE account_id = ?2 AND is_admin = 1)`,
    orgId,
    inviterId,
  );
  return standing !== null;
}

export type OrgInvitation = { org: string; role: "owner" | "member"; invitedBy: string | null; expiresAt: number };

/** Invitations addressed to the viewer that have not expired. */
export async function listMyOrgInvitations(viewer: Viewer): Promise<OrgInvitation[]> {
  const rows = await accountDb(viewer.id).all<{
    org_id: string;
    role: "owner" | "member";
    invited_by: string | null;
    expires_at: number;
  }>(
    `SELECT org_id, role, invited_by, expires_at FROM org_invitations
     WHERE user_id = ? AND expires_at > ? ORDER BY created_at DESC LIMIT 50`,
    viewer.id,
    Date.now(),
  );
  const names = await accountsById(rows.flatMap((row) => [row.org_id, row.invited_by]));
  return rows.flatMap((row) => {
    const org = names.get(row.org_id);
    if (!org) return [];
    const invitedBy = row.invited_by ? (names.get(row.invited_by)?.name ?? null) : null;
    return [{ org: org.name, role: row.role, invitedBy, expiresAt: row.expires_at }];
  });
}

/** Pending invitations for an org, visible to its owners. */
export async function listOrgInvitations(org: string, viewer: Viewer | null) {
  const account = await loadOrgByName(org);
  if (!(await canAdministerAccount(account.id, viewer))) throw forbidden();
  const rows = await accountDb(account.id).all<{ user_id: string; role: string; expires_at: number }>(
    "SELECT user_id, role, expires_at FROM org_invitations WHERE org_id = ? AND expires_at > ? LIMIT 200",
    account.id,
    Date.now(),
  );
  const names = await accountsById(rows.map((row) => row.user_id));
  return rows
    .flatMap((row) => {
      const invitee = names.get(row.user_id);
      return invitee ? [{ name: invitee.name, role: row.role, expires_at: row.expires_at }] : [];
    })
    .sort((left, right) => (left.name < right.name ? -1 : left.name > right.name ? 1 : 0));
}

/** The invitee accepts (joins with the invited role) or declines; either way the invitation row is consumed. */
export async function respondToOrgInvitation(input: { org: string; accept: boolean; viewer: Viewer }): Promise<void> {
  // Declining stays open; joining is the first step to writing in the org's repositories (AUTH-B1).
  if (input.accept) assertVerifiedForWrites(input.viewer);
  const org = await loadOrgByName(input.org);
  const invitation = await accountDb(org.id).first<{
    role: "owner" | "member";
    invited_by: string | null;
    expires_at: number;
  }>(
    "SELECT role, invited_by, expires_at FROM org_invitations WHERE org_id = ? AND user_id = ?",
    org.id,
    input.viewer.id,
  );
  if (!invitation || invitation.expires_at <= Date.now()) throw notFoundError("Invitation not found or expired");
  const authorized = !input.accept || (await inviterStillAuthorized(org.id, invitation.invited_by));
  await deleteOrgInvitation(org.id, input.viewer.id);
  if (!authorized) throw notFoundError("Invitation not found or expired");
  if (input.accept) {
    await run(
      `INSERT INTO org_members (org_id, user_id, role, created_at) VALUES (?, ?, ?, ?)
       ON CONFLICT (org_id, user_id) DO UPDATE SET role = excluded.role`,
      org.id,
      input.viewer.id,
      invitation.role,
      Date.now(),
    );
  }
}

export type OrgSettings = { defaultPermission: OrgDefaultPermission; membersVisibility: "public" | "members" };

export async function loadOrgSettings(orgId: string): Promise<OrgSettings> {
  const row = await first<{ org_default_permission: string | null; members_visibility: string | null }>(
    "SELECT org_default_permission, members_visibility FROM accounts WHERE id = ?",
    orgId,
  );
  const permission = row?.org_default_permission;
  return {
    defaultPermission: permission === "none" || permission === "write" ? permission : "read",
    membersVisibility: row?.members_visibility === "members" ? "members" : "public",
  };
}

/** Owners only. Lowering the default permission to `none` drops followers who lose read access. */
export async function updateOrgSettings(input: { org: string; viewer: Viewer | null; settings: OrgSettings }) {
  const org = await loadOrgByName(input.org);
  if (!(await canAdministerAccount(org.id, input.viewer))) throw forbidden();
  await run(
    "UPDATE accounts SET org_default_permission = ?, members_visibility = ? WHERE id = ?",
    input.settings.defaultPermission,
    input.settings.membersVisibility,
    org.id,
  );
  if (input.settings.defaultPermission === "none") await purgeOrgFollowers(org.id, null);
}

/** Whether `viewer` may see who is in the org: always for public rosters, otherwise members and site admins. */
export async function canListOrgMembers(orgId: string, viewer: Viewer | null): Promise<boolean> {
  const settings = await loadOrgSettings(orgId);
  if (settings.membersVisibility === "public") return true;
  if (!viewer) return false;
  if (viewer.isAdmin) return true;
  return (await first("SELECT 1 AS ok FROM org_members WHERE org_id = ? AND user_id = ?", orgId, viewer.id)) !== null;
}