solution.js

import { DB } from "https://deno.land/x/sqlite@v2.5.0/mod.ts";
import { Application } from "https://deno.land/x/abc@v1.3.3/mod.ts";
import AsciiTable from "https://deno.land/x/ascii_table/mod.ts";
import { createTable, createData, apiInstructions } from "./data.js";
let db;

// This is a functioning back-end.
// Everything is set up except for the database connections
const app = new Application();

app.get("/", async (context) => {
  let title = "All Rentals";
  let query = `SELECT * FROM property`;
  const { searchParams } = new URL(context.url);

  /**
   * @todo ================TASK================
   * If the user wants to hide unavailable rentals, they will add ?filter=true to the url
   * If they do this, modify the SQL query to only return available rentals
   * Also, modify the title to say "Available Rentals" instead of "All Rentals"
   *
   * The user should be able to combine both parameters (e.g. ?sort=true&filter=true)
   *
   * */
  if (searchParams.get("filter")) {
    title = "Available Rentals";
    query += " WHERE available = true";
  }
  /**
   * @todo ===============END-TASK================
   * */

  /**
   * @todo ================TASK================
   * If the user wants to sort results by price, they will add ?sort=true to the url
   * If they do this, modify the SQL query to sort by price (ascending)
   *
   * */
  if (searchParams.get("sort")) query += " ORDER BY price ASC";
  /**
   * @todo ===============END-TASK================
   * */

  const results = [...(await db.query(query)).asObjects()];

  if (searchParams.get("json")) {
    context.json(results, 200);
    return;
  }

  return `${apiInstructions}${await formatData(title, results)}`;
});

app.post("/", async (context) => {
  const { house_number, city, price } = await context.body;

  /**
   * @todo ================TASK================
   * The user has added a new rental,
   * We can find the rental data inside the post body
   *
   * If the property already exists in the database (same house_number and city)
   * Reply with: { response: "Property already exists" }
   * */
  const isExisting = `SELECT * FROM property WHERE house_number = ? AND city = ?`;
  const result = (await db.query(isExisting, [house_number, city])).asObjects();
  if ([...result].length) return { response: "Property already exists" };
  /**
   * @todo ===============END-TASK================
   * */

  /**
   * @todo ================TASK================
   * Now save this rental to the database
   * - Set created_at to the current date and time
   * - Set available to true
   * reply with { response: "Added to database" }
   *
   * */
  await db.query(
    `INSERT INTO property (house_number, city, price, created_at, available) 
    VALUES (?, ?, ?, ?, ?);`,
    [house_number, city, price, new Date(), true]
  );
  /**
   * @todo ===============END-TASK================
   * */

  return { response: "Added to database" };
});

app.get("/:id", async (context) => {
  /**
   * @todo ================TASK================
   * search this ID in the database
   * if it doesn't exist, return { response: "Property not found" }
   * If it does exist, reply with the Object
   * */
  const { id } = context.params;
  const result = await db.query(`SELECT * FROM property WHERE id = ?`, [id]);
  const json = [...result.asObjects()];
  if (json.length === 0)
    return context.json({ response: "Property not found" });

  context.json(json[0]);
  /**
   * @todo ===============END-TASK================
   * */
});

app.put("/:id/off", async (context) => {
  /**
   * @todo ================TASK================
   * Mark this propery as off the market (no longer available)
   * If id does not exist Reply with this JSON: { error: `id ${id} not found` }
   * If it was already off the market, Reply with this JSON: `{ error: Already taken }`
   * */
  const { id } = context.params;
  const results = await db.query(
    `SELECT available FROM property WHERE id = ?`,
    [id]
  );
  const [result] = results.asObjects();
  if (!result) {
    context.json({ error: `id ${id} not found` });
    return;
  }
  if (!result.available) {
    context.json({ error: "Already taken" });
    return;
  }
  await db.query(`UPDATE property SET available = false WHERE id = ?`, [id]);
  /**
   * @todo ===============END-TASK================
   * */

  context.json({ success: "marked as not available" });
});

/* #############################################################################

                              🪑 PropertyRentals
                            ---------------------
                         Do not edit below this line

############################################################################# */

async function start(instanceDB) {
  db = instanceDB;
  try {
    await db.query(createTable);
    await db.query(createData);
    if (isProduction()) console.log("🌱 Database created");
  } catch (error) {
    if (isProduction())
      console.log("✅ Database found (To reset database, delete rentals.db)");
  }

  app.start({ port: 3000 });
  if (isProduction()) console.log("Server running on http://localhost:3000");
}

async function formatData(title, results) {
  if (!results.length) return "No results found.";
  const table = new AsciiTable(title).setHeading(Object.keys(results[0]));
  for (let row of results) table.addRow(Object.values(row));
  return await table.toString();
}

function isProduction() {
  return !Deno.args.includes("--quiet");
}

await start(new DB("rentals.db"));