draftPicks.ts

import * as sql from "sqlite";
import * as Discord from "discord.js";
import getL10nFunction from "./src/constants/messages";

const ROUNDS = 10;
// TODO configurable
const L10n = getL10nFunction("EN");

async function getDB(): Promise<sql.Database> {
  const db = await sql.open("./pokedraft.sqlite");
  return db;
}

// Strips any commands from the input and formats the text for querying
function querify(content: string): string {
  return content; // .toUpperCase().replace(/\W/g, "_");
}

// Adds a pick to the database and moves along the draft
async function commitPick({ author, channel, content }: Discord.Message) {
  const db = await getDB();

  const pokePick = querify(content);

  // Allows you to pick by name or id
  const query = /^\d+$/.test(pokePick)
    ? `SELECT rowid, name FROM PokemonList WHERE id=${Number(pokePick)}`
    : `SELECT rowid, name FROM PokemonList WHERE name="${pokePick}"`;

  // Validates that the pick is a valid Pokemon from our list
  const pokemon = await db.get(query);

  if (!pokemon) {
    channel.send(L10n("INVALID_POKEMON", content));
    return;
  }

  // Next we check if it's already been picked and we retrieve the last pick
  const [alreadyPicked, prevPick, draft] = await Promise.all([
    db.get(`
      SELECT username, pickNumber
      FROM DraftPicksCatalog
      WHERE
        divisionId=${channel.id} AND
        pokemonId="${pokemon.rowid}";
    `),
    db.get(`
      SELECT pickNumber
      FROM DraftPicksCatalog
      WHERE
        divisionId=${channel.id}
      ORDER BY time DESC
      LIMIT 1;
    `),
    db.get(`
      SELECT pickOrder
      FROM DivisionCatalog
      WHERE divisionId=${channel.id}
    `)
  ]);

  // If the draft doesn't exist it means that an invalid divisionId
  // was provided and perhaps a draft doesn't exist on this channel.
  // It's done here and not earlier because it depends on the DivisionCatalog
  // query which we run in parallel above with the other queries.
  if (!draft) {
    channel.send(L10n("NO_DRAFT"));
  }

  const draftOrder = draft.pickOrder.split(",");

  // It's already been picked we need to let the user know to try again
  if (alreadyPicked) {
    const prevPickRound = Math.ceil(alreadyPicked.pickNumber / ROUNDS);
    channel.send(
      L10n("ALREADY_PICKED", {
        pick: alreadyPicked.pickNumber,
        pokemon: pokemon.name,
        prevPickRound,
      })
    );
    return;
  }

  // Make the pick and notify the draft room of the pick
  const pickNumber = prevPick ? prevPick.pickNumber + 1 : 1;
  const roundNumber = Math.ceil(pickNumber / ROUNDS);

  const pickOrderId = (pickNumber % draftOrder.length) - 1;
  const pickIndex =
    roundNumber % 2 === 0 ? draftOrder.length - pickOrderId : pickOrderId;

  const currentPick = draftOrder[pickIndex];

  // TODO this needs to be discordSID so you can't spoof picks
  if (currentPick !== author.username) {
    channel.send(L10n("NOT_YOUR_PICK", author.username, currentPick));
    return;
  }

  await db.run(`
    INSERT INTO DraftPicksCatalog (
      divisionId,
      discordSid,
      username,
      pokemonId,
      pickNumber,
      time
    )
    VALUES (
      ${channel.id},
      ${author.id},
      "${author.username}",
      "${pokemon.rowid}",
      ${pickNumber},
      ${Date.now()}
    );
  `);

  // TODO send down a link to the rankedboost or gamepress or serebii page info for the Pokemon picked
  channel.send(L10n('MAKE_PICK', {
    pick: pickNumber,
    pokemon: pokemon.name,
    round: roundNumber,
    user: author.username,
  }));

  // Tags the next person on the draft list
  const nextPickNumber = pickNumber + 1;
  const nextRoundNumber = Math.ceil(nextPickNumber / ROUNDS);

  const nextPickOrderId = (nextPickNumber % draftOrder.length) - 1;

  // Dealing with snake drafts
  const nextPickIndex =
    nextRoundNumber % 2 === 0
      ? draftOrder.length - nextPickOrderId
      : nextPickOrderId;

  const nextPick = draftOrder[nextPickIndex];

  channel.send(L10n('NEXT_PICK', { round: nextRoundNumber, pick: nextPickNumber, user: nextPick }));
}

// Function to kick off a draft
async function startDraft({ channel }: Discord.Message) {
  // TODO we can dry this
  const draft = await db.get(`
    SELECT pickOrder
    FROM DivisionCatalog
    WHERE divisionId=${channel.id}
  `);

  if (!draft) {
    channel.send(L10n("NO_DRAFT"));
    return;
  }

  const draftOrder = draft.pickOrder.split(",");

  const draftOrderFormat = draftOrder.map(
    (user, pickNumber) => `${pickNumber + 1}. @${user}`
  );
  const fullText = ["Welcome to PokeDraft!", "", "The draft order is:"].concat(
    draftOrderFormat,
    ["", "", `@${draftOrder[0]} you are up 1st overall.`]
  );

  channel.send(fullText);
}

// Retrieve a list of all picks made thus far
async function draftResults({ channel }: Discord.Message) {
  const db = await getDB();

  const results = await db.all(`
    SELECT username, PokemonList.name AS pokemon, pickNumber
    FROM DraftPicksCatalog
    LEFT JOIN PokemonList ON
      PokemonList.rowid = DraftPicksCatalog.pokemonId
    WHERE
      divisionId=${channel.id}
    ORDER BY time ASC
  `);

  // TODO we can DRY this with roundResults
  const text = results
    .map(row => `#${row.pickNumber} @${row.username}: ${row.pokemon}`)
    .join("\n");
  channel.send(text);
}

// Retrieve a list of all picks made in the current round
async function roundResults({ channel }: Discord.Message) {
  const db = await getDB();

  const [prevPick, draft] = await Promise.all([
    db.get(`
      SELECT pickNumber
      FROM DraftPicksCatalog
      WHERE
        divisionId=${channel.id}
      ORDER BY time DESC
      LIMIT 1;
    `),
    db.get(`
      SELECT pickOrder
      FROM DivisionCatalog
      WHERE divisionId=${channel.id}
    `)
  ]);

  if (!draft) {
    channel.send(L10n("NO_DRAFT"));
    return;
  }

  if (!prevPick) {
    channel.send(L10n("DRAFT_NOT_STARTED"));
    return;
  }

  const draftOrder = draft.pickOrder.split(",");
  const pickNumber = prevPick.pickNumber;
  const roundNumber = Math.ceil(pickNumber / ROUNDS);
  const roundsFirstPick = roundNumber * draftOrder.length;

  const results = await db.all(`
    SELECT username, PokemonList.name AS pokemon, pickNumber
    FROM DraftPicksCatalog
    LEFT JOIN PokemonList ON
      PokemonList.rowid = DraftPicksCatalog.pokemonId
    WHERE
      divisionId=${channel.id}
    ORDER BY time ASC
  `);

  const text = results
    .map(row => `#${row.pickNumber} @${row.username}: ${row.pokemon}`)
    .join("\n");
  channel.send(text);
}

awesome, makes total sense, and I really like deconstructing the dbInstance. We can go with testing this even on a separate (testing) division on PoGoRaids