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 { purgeOrgFollowers } from "./access-revocation";
import type { Viewer } from "~/lib/types";
import { isReservedName } from "~/lib/reserved-names";
import { cleanDisplayName } from "~/lib/validation";

/** 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): Promise<boolean> {
  if (isReservedName(name)) 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, older than the reservation window, no
 * repositories, organisations, activity or admin flag. Deleting one frees its email and username.
 */
const STALE_UNVERIFIED_WHERE = `u.email_verified_at 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)
  AND NOT EXISTS (SELECT 1 FROM activity ac WHERE ac.actor_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,
  );
  return result.meta.changes > 0;
}

/** Cron: drops stale unverified accounts so squatted emails and usernames do not stay reserved forever. */
export async function sweepUnverifiedAccounts(): Promise<void> {
  await run(
    `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})`,
    Date.now() - UNVERIFIED_RESERVATION_MS,
  );
}

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

/**
 * The first user on an instance becomes its admin. Both decisions ("may this signup happen while signups
 * are closed" and "is this the first user") are made inside the insert transaction rather than by a
 * separate COUNT, so two racing first signups cannot both become admin and a closed instance accepts at
 * most the bootstrap account (AUTH-A6).
 */
export async function createUser(input: {
  name: string;
  email: string;
  password: string | null;
  githubId: string | null;
  displayName: 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 hasUsers = ((await first<{ count: number }>("SELECT COUNT(*) AS count FROM users"))?.count ?? 0) > 0;
  if (!open && hasUsers) throw badRequest("Sign ups are disabled on this instance");
  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 guard = open ? "" : " WHERE NOT EXISTS (SELECT 1 FROM users)";
  await batch([
    stmt(
      `INSERT INTO accounts (id, name, kind, display_name, created_at) SELECT ?, ?, 'user', ?, ?${guard}`,
      id,
      input.name,
      cleanDisplayName(input.displayName),
      Date.now(),
    ),
    stmt(
      `INSERT INTO users (account_id, email, password_hash, github_id, is_admin) SELECT ?, ?, ?, ?, 0${guard}`,
      id,
      input.email,
      passwordHash,
      input.githubId,
    ),
    stmt(
      "UPDATE users SET is_admin = 1 WHERE account_id = ?1 AND NOT EXISTS (SELECT 1 FROM users WHERE account_id != ?1)",
      id,
    ),
  ]);
  if (!(await first("SELECT 1 AS ok FROM users WHERE account_id = ?", id))) {
    throw badRequest("Sign ups are disabled on this instance");
  }
  return id;
}

export async function createOrg(name: string, displayName: string | null, ownerId: string): Promise<string> {
  if (await accountNameTaken(name)) 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),
  ]);
  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,
  );
}

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 (membership?.role === "owner" && input.role !== "owner") {
    const owners = await first<{ count: number }>(
      "SELECT COUNT(*) AS count FROM org_members WHERE org_id = ? AND role = 'owner' AND user_id != ?",
      org.id,
      user.id,
    );
    if (!owners?.count) throw conflict("An organization needs at least one owner; add another owner first");
  }
  if (input.role === "none") {
    await run("DELETE FROM org_members WHERE org_id = ? AND user_id = ?", org.id, user.id);
    scheduleRevocationSweep(() => sweepRevokedOrgAccess(org.id, user.id));
    await run("DELETE FROM org_invitations WHERE org_id = ? AND user_id = ?", 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" };
  }
  await run("UPDATE org_members SET role = ? WHERE org_id = ? AND user_id = ?", input.role, org.id, user.id);
  return { status: "member" };
}

async function inviteOrgMember(orgId: string, userId: string, role: "owner" | "member", viewer: Viewer | null) {
  const pending = await 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();
  await run(
    `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`,
    randomId(),
    orgId,
    userId,
    role,
    viewer?.id ?? null,
    now,
    now + INVITATION_TTL_MS,
  );
}

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 all<{ org: string; role: "owner" | "member"; invited_by: string | null; expires_at: number }>(
    `SELECT o.name AS org, i.role, b.name AS invited_by, i.expires_at FROM org_invitations i
     JOIN accounts o ON o.id = i.org_id LEFT JOIN accounts b ON b.id = i.invited_by
     WHERE i.user_id = ? AND i.expires_at > ? ORDER BY i.created_at DESC LIMIT 50`,
    viewer.id,
    Date.now(),
  );
  return rows.map((row) => ({ org: row.org, role: row.role, invitedBy: row.invited_by, 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();
  return all<{ name: string; role: string; expires_at: number }>(
    `SELECT a.name, i.role, i.expires_at FROM org_invitations i JOIN accounts a ON a.id = i.user_id
     WHERE i.org_id = ? AND i.expires_at > ? ORDER BY a.name LIMIT 200`,
    account.id,
    Date.now(),
  );
}

/** 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> {
  const org = await loadOrgByName(input.org);
  const invitation = await first<{ role: "owner" | "member"; expires_at: number }>(
    "SELECT role, 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 statements = [stmt("DELETE FROM org_invitations WHERE org_id = ? AND user_id = ?", org.id, input.viewer.id)];
  if (input.accept) {
    statements.push(
      stmt(
        `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(),
      ),
    );
  }
  await batch(statements);
}

export async function pruneExpiredOrgInvitations(): Promise<void> {
  await run("DELETE FROM org_invitations WHERE expires_at <= ?", 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;
}