import sqlite3
from flask import g, request

# this function will creating the database of messages. 
# It first check whether there is a database called message_db 
# in the g attribute of the app. If not, then connect to that 
# database then check whether a table called messages exists 
# in message_db, and create it if not.
def get_message_db():
    # write some helpful comments here
    try:
        return g.message_db
    except:
        g.message_db = sqlite3.connect("messages_db.sqlite")
        cmd = """
            CREATE TABLE IF NOT EXISTS message_table(
            id INTEGER, 
            handle TEXT, 
            message TEXT
            )
            """
        cursor = g.message_db.cursor()
        cursor.execute(cmd)
    return g.message_db


# this function could inserting a user message into the database of messages.
def insert_message(request):
    name = request.form["name"]
    message = request.form["message"]

    # open a connection to messages.db
    connection_obj = get_message_db() # call get_message_db function
    cursor_obj = connection_obj.cursor()  


    id_cmd = "SELECT COUNT(*) FROM message_table" 
    cursor_obj.execute(id_cmd)
    
    # get total number of rows then add 1 to get unique new id
    id = cursor_obj.fetchone()[0] + 1

    insert_cmd = """
        INSERT INTO message_table
        (id, handle, message)
        VALUES
        (?, ?, ?)
        """
    data = (id, name, message) 
    cursor_obj.execute(insert_cmd, data) # inser data as new row

    connection_obj.commit() # save changes
    connection_obj.close() # close connection


# this function will return a collection of n random messages from the message_db
# n argument is request.form["number"] which is a html script in string form
def random_messages(n):
    # open a connection to messages.db
    connection_obj = get_message_db() # call get_message_db function
    cursor_obj = connection_obj.cursor()

    length_cmd = "SELECT COUNT(*) FROM message_table"
    cursor_obj.execute(length_cmd)
    num_len = cursor_obj.fetchone()[0]
    
    out = ""

    if (int(n) > num_len):
        out = ("The number " + n + " you input is exceed the maximun number of messages(" + 
            str(num_len) +
            "), I can only show below messages:<br><br>")
        n = str(num_len)

    cmd = "SELECT * FROM message_table ORDER BY RANDOM() LIMIT " + n

    # updateing out, out is a string that contains html script
    for row in cursor_obj.execute(cmd): # row is a tuple
        out = out + row[2] + "<br>" + "- " + row[1] + "<br><br>"

    connection_obj.close() # close connection

    return out # a html script in string form

# import modules
from flask import Flask, render_template, request
from flask import redirect, url_for, abort, g
import io
import web_helper

app = Flask(__name__)

@app.route('/') # the default page or the mian page
def main():
    return render_template('main.html') # load main.html in template file


@app.route('/submit/', methods=['POST', 'GET']) # the sumbit page to sumbit messages
def submit():
    if request.method == 'GET':
        return render_template('submit.html') # load submit.html in template file
    else:
        # if the user submits the form
        try:
            web_helper.insert_message(request) # call insert_message function
            return render_template('submit.html', thanks=True)
        except:
            return render_template('submit.html', error=True)


@app.route('/view_messages/', methods=['POST', 'GET']) # the view_message page to view messages
def view_messages():
    if request.method == 'GET':
        return render_template('view_messages.html') # load view_messages.html in template file
    else:
        try:
            out = web_helper.random_messages(request.form["number"]) # call random_messages function
            # out is a format html script in string form
            return render_template('view_messages.html', output = out) 
        except:
            return render_template('view_messages.html', error = True)

html {
    /* change text font */
    font-family: sans-serif;
    padding: 1rem;

    /* set image as background */
    background: url("background_html.jpg");

    /* Full height */
    height: 100%;

    /* Center and scale the image nicely */
    background-position: center;
    background-repeat: no-repeat;
    background-size: cover;
}

body {
    max-width: 900px;
    margin: 0 auto;
}

h1 {
    color: rgb(0, 0, 0);
    font-family: cursive;
    margin: 1rem 0;
    text-align: center;
}

a {
    color: rgb(28, 88, 229);
    font-family: cursive;
    text-decoration: none;
    font-weight: bold;
    font-size: 1.3rem; /* 1.3 times bigger than default size */
}

hr {
    border: none;
    border-top: 1px solid lightgray; /* set border */
}

nav {
    background: antiquewhite;
    padding: 0 0.5rem;
    border-radius: 25px;
}

nav ul  {
    display: flex;
    list-style: none;
    margin: 0;
    padding: 0;
}

nav ul li a {
    display: block;
    padding: 0.5rem;
}

.content {
    padding: 1rem 1rem 1rem;
    border-radius: 25px;

    /* set image as background */
    background: url("background.jpg");

    /* Full height */
    height: 100%;

    /* Center and scale the image nicely */
    background-position: center;
    background-repeat: no-repeat;
    background-size: cover;
}

.flash {
    text-align: center;
    margin: 1em 0;
    padding: 1em;
    background: #cae6f6;
    border: 1px solid #377ba8;
}