import { all, first, MAX_IN_LIST, placeholders } from "../db";
import { accountDb, repoDb, type ScopedDb } from "../scoped-db";
import { encryptRepoSecret } from "../secrets";
import { assertBelowCap, CAPS } from "../limits";
import { canAdministerAccount } from "../repos";
import { notFoundError, forbidden } from "../errors";
import type { OwnerVisibility } from "./config-values";
import type { Viewer } from "~/lib/types";

export type ConfigScope =
  | { kind: "repo"; id: string }
  | { kind: "environment"; id: string; repoId: string }
  | { kind: "owner"; id: string; visibility: OwnerVisibility; selectedRepos: string[] };

const TABLES = {
  variable: {
    repo: ["repo_variables", "repo_id"],
    environment: ["environment_variables", "environment_id"],
    owner: ["owner_variables", "owner_id"],
  },
  secret: {
    repo: ["secrets", "repo_id"],
    environment: ["environment_secrets", "environment_id"],
    owner: ["owner_secrets", "owner_id"],
  },
} as const;

type Kind = keyof typeof TABLES;

/** Repository and environment values live in the repository's RepoState, owner values in the owner's AccountState. */
function scopeDb(scope: ConfigScope): ScopedDb {
  if (scope.kind === "owner") return accountDb(scope.id);
  return repoDb(scope.kind === "repo" ? scope.id : scope.repoId);
}

function table(kind: Kind, scope: ConfigScope): readonly [string, string] {
  return TABLES[kind][scope.kind];
}

async function assertRoom(kind: Kind, scope: ConfigScope, name: string): Promise<void> {
  const [target, column] = table(kind, scope);
  const db = scopeDb(scope);
  if (await db.first(`SELECT 1 AS ok FROM ${target} WHERE ${column} = ? AND name = ?`, scope.id, name)) return;
  const count = await db.first<{ count: number }>(
    `SELECT COUNT(*) AS count FROM ${target} WHERE ${column} = ?`,
    scope.id,
  );
  assertBelowCap(count?.count ?? 0, kind === "secret" ? CAPS.secretsPerRepo : CAPS.variablesPerScope, `${kind}s`);
}

function ownerColumns(scope: ConfigScope): { names: string; values: string; params: string[] } {
  if (scope.kind !== "owner") return { names: "", values: "", params: [] };
  return {
    names: ", visibility, selected_repos",
    values: ", ?, ?",
    params: [scope.visibility, JSON.stringify(scope.selectedRepos)],
  };
}

export async function upsertVariable(scope: ConfigScope, name: string, value: string): Promise<void> {
  await assertRoom("variable", scope, name);
  const [target, column] = table("variable", scope);
  const owner = ownerColumns(scope);
  await scopeDb(scope).run(
    `INSERT INTO ${target} (${column}, name, value, updated_at${owner.names}) VALUES (?, ?, ?, ?${owner.values})
     ON CONFLICT (${column}, name) DO UPDATE SET value = excluded.value, updated_at = excluded.updated_at
     ${scope.kind === "owner" ? ", visibility = excluded.visibility, selected_repos = excluded.selected_repos" : ""}`,
    scope.id,
    name,
    value,
    Date.now(),
    ...owner.params,
  );
}

export async function upsertSecret(scope: ConfigScope, name: string, value: string): Promise<void> {
  await assertRoom("secret", scope, name);
  const [target, column] = table("secret", scope);
  const owner = ownerColumns(scope);
  const encrypted = await encryptRepoSecret(value);
  await scopeDb(scope).run(
    `INSERT INTO ${target} (${column}, name, ciphertext, iv, updated_at${owner.names}) VALUES (?, ?, ?, ?, ?${owner.values})
     ON CONFLICT (${column}, name) DO UPDATE SET ciphertext = excluded.ciphertext, iv = excluded.iv,
       updated_at = excluded.updated_at
     ${scope.kind === "owner" ? ", visibility = excluded.visibility, selected_repos = excluded.selected_repos" : ""}`,
    scope.id,
    name,
    encrypted.ciphertext,
    encrypted.iv,
    Date.now(),
    ...owner.params,
  );
}

export async function deleteConfigValue(kind: Kind, scope: ConfigScope, name: string): Promise<void> {
  const [target, column] = table(kind, scope);
  await scopeDb(scope).run(`DELETE FROM ${target} WHERE ${column} = ? AND name = ?`, scope.id, name);
}

export async function listVariables(scope: ConfigScope) {
  const [target, column] = table("variable", scope);
  return scopeDb(scope).all<{ name: string; value: string; updatedAt: number }>(
    `SELECT name, value, updated_at AS updatedAt FROM ${target} WHERE ${column} = ? ORDER BY name`,
    scope.id,
  );
}

export async function listSecretNames(scope: ConfigScope) {
  const [target, column] = table("secret", scope);
  return scopeDb(scope).all<{ name: string; updatedAt: number }>(
    `SELECT name, updated_at AS updatedAt FROM ${target} WHERE ${column} = ? ORDER BY name`,
    scope.id,
  );
}

/** Resolves an owner account the viewer administers (themselves or an organization they own). */
export async function requireOwnerAdmin(
  ownerName: string,
  viewer: Viewer | null,
): Promise<{ id: string; name: string }> {
  const account = await first<{ id: string; name: string }>(
    "SELECT id, name FROM accounts WHERE name = ? COLLATE NOCASE",
    ownerName,
  );
  if (!account) throw notFoundError("Account not found");
  if (!(await canAdministerAccount(account.id, viewer))) throw forbidden();
  return account;
}

/** Maps repository names chosen for a "selected repositories" policy to ids owned by the account. */
export async function ownerRepoIds(ownerId: string, names: string[]): Promise<string[]> {
  const wanted = [...new Set(names)].slice(0, MAX_IN_LIST);
  if (wanted.length === 0) return [];
  const rows = await all<{ id: string }>(
    `SELECT id FROM repos WHERE owner_id = ? AND name IN (${placeholders(wanted.length)})`,
    ownerId,
    ...wanted,
  );
  return rows.map((row) => row.id);
}