# MCS 275 Spring 2022 Worksheet 14
from flask import Flask, render_template, request, redirect
import os
import sqlite3

# Make sure the working directory is the directory that contains
# this script file.  (Fixes some errors caused by running from
# VS code, for example.)
os.chdir(os.path.dirname(os.path.realpath(__file__)))

# The database filename used by the application
DBFILE = "speakerslate.db"


def reset_db_to_initial_state():
    """
    Initialize the database file, erasing existing data if present.
    Used in two ways:
      * Runs at application startup if database cannot be found
      * Is called by the route for resetting the schedule
    """
    con = sqlite3.connect(DBFILE)
    con.execute("DROP TABLE IF EXISTS schedule;")
    con.execute(
        """
    CREATE TABLE schedule (
        slotid INTEGER PRIMARY KEY,
        datetime TEXT NOT NULL,
        speaker TEXT
    );"""
    )
    for s in [
        "Saturday 9am",
        "Saturday 10am",
        "Saturday 11am",
        "Saturday 2pm",
        "Sunday 11am",
        "Sunday 2pm",
    ]:
        con.execute(
            """
        INSERT INTO schedule (datetime) VALUES (?);
        """,
            [s],
        )
    con.commit()
    con.close()


app = Flask(__name__)

# -----------------------------------------------
#        START OF FLASK ROUTE DEFINITIONS
# -----------------------------------------------


@app.route("/")
def front():
    "Show the application front page"
    return render_template("front.html")


@app.route("/reset/")
def reset():
    "Erase all scheduled lectures"
    reset_db_to_initial_state()
    return render_template("reset_done.html")


@app.route("/userinfo/")
def userinfo():
    "Show user info form"
    return render_template("userinfo.html")


@app.route("/choose/", methods=["GET", "POST"])
def showchoices():
    """
    Route for submission of the user information form.
    With the username now known, presents choices of speaking
    slots that aren't already assigned.  Each choice links to
    a URL like
      /assign/<slotid>/to/<username>/
    so that clicking the link will assign the speaking slot to
    this user.
    """
    con = sqlite3.connect(DBFILE)
    res = con.execute("SELECT slotid,datetime FROM schedule WHERE speaker IS NULL;")
    available_slots = []
    for row in res:
        available_slots.append({"slotid": row[0], "datetime": row[1]})
    con.close()
    return render_template(
        "choose.html",
        username=request.values.get("username"),
        available_slots=available_slots,
    )


@app.route("/assign/<int:slotid>/to/<username>/")
def assign_slot_to_person(slotid, username):
    """
    Record that `username` is speaking at slot with id `slotid`,
    then redirect to the schedule page.
    """
    con = sqlite3.connect(DBFILE)
    con.execute("UPDATE schedule SET speaker=? WHERE slotid=?;", (username, slotid))
    con.commit()
    con.close()
    return redirect("/schedule/")


@app.route("/schedule/")
def show_schedule():
    "Display the schedule of all assigned speaking slots"
    con = sqlite3.connect(DBFILE)
    res = con.execute(
        "SELECT slotid,datetime,speaker FROM schedule WHERE speaker IS NOT NULL;"
    )
    # Now we have all the slots as an iterable of tuples.
    # The template expects a list of dictionaries, so we convert.
    scheduled_lectures = []
    for row in res:
        scheduled_lectures.append(
            {"slotid": row[0], "datetime": row[1], "speaker": row[2]}
        )
    con.close()
    # Pass the list of dicts to the template.
    # NOTE: This template doesn't require the username, since the schedule
    # can be viewed without entering any information.
    return render_template("schedule.html", scheduled_lectures=scheduled_lectures)


# -----------------------------------------------
#        END OF FLASK ROUTE DEFINITIONS
# -----------------------------------------------


# -----------------------------------------------
#                MAIN PROGRAM
# -----------------------------------------------

# Make sure the database exists and is ready
if not os.path.exists(DBFILE):
    print("The database file '{}' does not exist.  Initializing it.".format(DBFILE))
    reset_db_to_initial_state()

# Start the web server
app.run()

<!doctype html>
<html>
  <head>
    <title>SpeakerSlate</title>
    <link rel="stylesheet" href="/static/speakerslate.css">
  </head>
  <body>
      <div class="logo-header">
      <h1>SpeakerSlate Pro XP+ 2000</h1>
      </div>

      <div class="content-main">
        <h2>Select an action:</h2>
        <ul>
            <li><a href="/userinfo/">Choose a lecture time</a></li>
            <li><a href="/schedule/">View scheduled lectures</a></li>
            <li><a href="/reset/">Clear the schedule</a></li>
            <li><a href="/newslot/">Add a new speaking slot</a></li>
        </ul>
      </div>
  </body>
</html>

<!doctype html>
<html>
  <head>
    <title>Add a new time slot</title>
    <link rel="stylesheet" href="/static/speakerslate.css">
  </head>
  <body>
      <div class="logo-header">
      <h1>SpeakerSlate Pro XP+ 2000</h1>
      </div>

      <div class="content-main">
        <h2>Add a new time slot</h2>
        <form action="/createslot/" method="post">
            <label for="slot_time">
                Time:
            </label>
            <input type="text" size="30" name="slot_time" id="slot_time">
            <input type="submit" value="Enter time">
        </form>
      </div>
  </body>
</html>

<!doctype html>
<html>
  <head>
    <title>Time slot added</title>
    <link rel="stylesheet" href="/static/speakerslate.css">
  </head>
  <body>
      <div class="logo-header">
      <h1>SpeakerSlate Pro XP+ 2000</h1>
      </div>

      <div class="content-main">
        <h2>Time slot added</h2>
        <p>The new time slot "{{slot_time}}" has successfully been created.</p>
        <p><a href="/">Return to the main page.</a></p>
      </div>
  </body>
</html>

@app.route("/newslot/")
def newslot():
    """Show datetime entry form"""
    return render_template("newslot.html")


@app.route("/createslot/", methods=["GET", "POST"])
def createslot():
    """After user has entered a datetime, we INSERT the value into the database"""
    con = sqlite3.connect(DBFILE)
    slot_time = request.values.get("slot_time") # In form `newslot.html`, the id of the user's entry is `slot_time`, so use that
    con.execute("INSERT INTO schedule (datetime) VALUES (?);", (slot_time,))
    con.commit()
    con.close()

    return render_template("newslot_done.html", slot_time = slot_time) # Show the confirmation page