def create_table():
     result = r.get_registry()['MY_SQL'].query(
         (
             "select * from information_schema.tables where "
             "TABLE_SCHEMA='scoring_system' and table_name='runs';"
         )
     )
     if result != 0:
         return
     query = (
         """CREATE TABLE IF NOT EXISTS runs(
             id INT AUTO_INCREMENT,
             level INT,
             failed_trial BOOLEAN,
             actual_time FLOAT(10,2),
             non_air BOOLEAN,
             furniture BOOLEAN,
             arbitrary_start BOOLEAN,
             return_trip BOOLEAN,
             candle_location_mode BOOLEAN,
             stopped_within_circle BOOLEAN,
             signaled_detection BOOLEAN,
             num_rooms_searched INT,
             kicked_dog BOOLEAN,
             touched_candle INT,
             cont_wall_contact INT,
             ramp_hallway BOOLEAN,
             alt_target BOOLEAN,
             all_candles BOOLEAN,
             used_versa_valve BOOLEAN,
             score FLOAT(10,2),
             robot_id VARCHAR(10),
             PRIMARY KEY (id))
             ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;""")
     r.get_registry()['MY_SQL'].query(query)

 def create_table():
     result = r.get_registry()['MY_SQL'].query(
         (
             "select * from information_schema.tables where "
             "TABLE_SCHEMA='scoring_system' and table_name='robots';"
         )
     )
     if result != 0:
         return
     query = (
         """CREATE TABLE IF NOT EXISTS robots(
         division VARCHAR(16),
         id VARCHAR(10) UNIQUE,
         volume INT,
         school VARCHAR(100),
         name VARCHAR(100),
         is_unique BOOLEAN,
         used_versa_valve BOOLEAN,
         level INT,
         is_disqualified BOOLEAN,
         passed_inspection BOOLEAN,
         PRIMARY KEY (id))
         ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;"""
     )
     r.get_registry()['MY_SQL'].query(query)
     r.get_registry()['MY_SQL'].query(
         ('ALTER TABLE robots ADD INDEX (name), ADD '
          'UNIQUE INDEX (name)')
     )

def robot_detail(robot_id, inputs=None):
    robot = r.get_registry()['ROBOTS'].get_robot(robot_id)
    if not robot:
        return render_template("not_found.html")

    runs = r.get_registry()['RUNS'].get_runs(robot_id)
    run_levels = [run['id'] for run in runs]

    # check if disqualifited and eligibility to advnace to next level
    eligibility = LevelProgressHandler.get_eligibility_for_next_run(
        runs, robot['level']
    )
    # get current best scores
    best_scores, attempted_levels, total_score, num_successful = (
        ScoreCalculator.get_best_scores(runs)
    )
    return render_template(
        "robot.html",
        attempted_levels=attempted_levels,
        total_score=total_score,
        robot_id=robot_id,
        robot=robot,
        disqualified=eligibility['disqualified'],
        eligible=eligibility['can_level_up'],
        best_scores=best_scores,
        robot_runs=runs,
        applied_factors=[applied_factors(id, robot_id) for id in run_levels],
        inputs=inputs
    )

def run():
    load_registry()
    r.get_registry()['MY_SQL'].query(
        'DROP TABLE runs;'
    )
    r.get_registry()['MY_SQL'].query(
        'DROP TABLE robots;'
    )

def applied_factors(run_id, robot_id):
    query = ("""SELECT * FROM runs where id = %(run_id)s;""")
    data = {
        'run_id': run_id
    }
    run_data = r.get_registry()['MY_SQL'].get(query, data)

    query = ("""SELECT division FROM robots where id = %(robot_id)s;""")
    data = {
        'robot_id': robot_id
    }
    robot_div = r.get_registry()['MY_SQL'].get(query, data).get('division')

    run_level = run_data['level']

    applied_oms = ""
    applied_rf = ""
    applied_pp = ""

    if run_data.get('failed_trial') == 1:
        if run_level == 1 and robot_div in ['junior', 'walking']:
            if (run_data.get('num_rooms_searched', 0) > 0):
                applied_oms += 'Task.search:-30x%d rooms\n' % (run_data.get('num_rooms_searched', 0))
            applied_oms += 'Task.detect:-30\n' if run_data.get('signaled_detection', 0) == 1 else ''
            applied_oms += 'Task.position:-30\n' if run_data.get('stopped_within_circle', 0) == 1 else ''
    else:
        if run_data.get('touched_candle', 0) > 0:
            applied_pp += 'PP.candle=%d\n' % (run_data.get('touched_candle', 0) * 50)
        if run_data.get('cont_wall_contact', 0) > 0:
            applied_pp += 'PP.slide=%dcm/2\n' % (run_data.get('cont_wall_contact', 0))
        applied_pp += 'PP.dog=50\n' if run_data.get('kicked_dog', 0) == 1 else ''

        if run_level == 1:
            applied_oms += 'OM.candle=0.75\n' if run_data.get('candle_location_mode', 0) == 1 else ''

        if run_level in [1,2]:
            applied_oms += 'OM.start=0.8\n' if run_data.get('arbitrary_start', 0) == 1 else ''
            applied_oms += 'OM.return=0.8\n' if run_data.get('return_trip', 0) == 1 else ''
            applied_oms += 'OM.extinguisher=0.75\n' if run_data.get('non_air', 0) == 1 else ''
            applied_oms += 'OM.furniture=0.75\n' if run_data.get('furniture', 0) == 1 else ''

            if run_data.get('num_rooms_searched') == 1:
                applied_rf += 'Room Factor:1\n'
            elif run_data.get('num_rooms_searched') == 2:
                applied_rf += 'Room Factor:0.85\n'
            elif run_data.get('num_rooms_searched') == 3:
                applied_rf += 'Room Factor:0.5\n'
            elif run_data.get('num_rooms_searched') == 4:
                applied_rf += 'Room Factor:0.35\n'

        elif run_level == 3:
            applied_oms += 'OM.Alt_Target=0.6\n' if run_data.get('alt_target', 0) == 1 else ''
            applied_oms += 'OM.Ramp_Hallway=0.9\n' if run_data.get('ramp_hallway', 0) == 1 else ''
            applied_oms += 'OM.All_Candles=0.6\n' if run_data.get('all_candles', 0) == 1 else '' 

    return {'applied_oms': applied_oms, 'applied_rf': applied_rf, 'applied_pp': applied_pp}

    def set_day(run_id, day):
        query = """UPDATE runs SET
            day = %(day)s
            where id = %(id)s;"""

        data = {
            'day': day,
            'id': run_id
        }
        r.get_registry()['MY_SQL'].insert(query, data)

 def get(cls, id):
     redis_key = cls.get_redis_key(id)
     cached_data = r.get_registry()['REDIS'].hgetall(redis_key)
     if cached_data:
         return Question(cached_data)
     query = "SELECT * FROM questions WHERE id=%s"
     result = r.get_registry()['MY_SQL'].get(query, [id])
     if result:
         cls.cache_result(result)
         return Question(result)
     return None

def robot_add_run(robot_id):
    robot = r.get_registry()['ROBOTS'].get_robot(robot_id)

    if not robot:
        return render_template("not_found.html")

    all_runs = r.get_registry()['RUNS'].get_runs(robot_id)
    if request.method == 'GET':
        # get all previous runs
        return render_template(
            "run.html",
            robot=robot,
            division=get_division_label(robot['division']),
            input=request.args,
            all_runs=all_runs
        )
    # For post request

    # Database query for showing past runs if the POST fails

    all_runs = r.get_registry()['RUNS'].get_runs(robot_id)

    # if invalidate input data
    params_d = bind_params(request.form, robot_id, robot['level'])

    err = validate_params(params_d,
                          robot['level'],
                          robot['division'],
                          robot['name'])

    if err:
        err['ERR'] = True
        params_and_errors = {}
        params_and_errors.update(params_d)
        # leave data already entered unchanged
        params_and_errors.update(err)  # include errors
        return render_template(
            "run.html",
            robot=robot,
            division=get_division_label(robot['division']),
            input=params_and_errors,
            all_runs=all_runs
        )

    # calculate score
    score = get_score(robot, params_d)
    # convert dict values to tuple to prepare to insert to DB
    params_t = convert_to_tuple(params_d, robot_id, score)
    # insert into databse
    r.get_registry()['RUNS'].record_run(*params_t)
    return redirect(url_for('main.robot_detail', robot_id=robot_id))

def advance_level(robot_id):
    robot = r.get_registry()['ROBOTS'].get_robot(robot_id)

    if not robot:
        return render_template("not_found.html")

    runs = r.get_registry()['RUNS'].get_runs(robot_id)

    eligible = LevelProgressHandler.get_eligibility_for_next_run(runs, robot['level'])

    if eligible.get('can_level_up') and not eligible['disqualified']:
        r.get_registry()['ROBOTS'].advance_level(robot_id, robot['level'])
        return redirect(url_for('main.robot_add_run', robot_id=robot_id))

    return "Robot not eligible to advance to next level.\n"

 def advance_level(robot_id, current_level):
     query = """UPDATE robots SET level = %(level)s where id = %(robot_id)s;"""
     data = {
         'level': current_level + 1,
         'robot_id': robot_id
     }
     return r.get_registry()['MY_SQL'].insert(query, data)

def run():
    # grant all privileges on *.* to [email protected] identified by '9a7123w4982ew3490x23pl34bz' with grant option; # NOQA
    load_registry()
    r.get_registry()['MY_SQL'].query('CREATE DATABASE challenge;')
    r.get_registry()['MY_SQL'].query(
        """CREATE TABLE questions(
            id INT PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(255),
            prompt VARCHAR(255),
            passing_score INT,
            correct_names LONGTEXT,
            finalized INT DEFAULT 1,
            active INT DEFAULT 1,
            total INT DEFAULT 0
        )"""
    )

 def get_runs_robot_level(robot_id, level):
     query = """SELECT * FROM runs where (robot_id = %(robot_id)s) AND (level = level);"""
     data = {
         'robot_id': robot_id,
         'level': level
     }
     return r.get_registry()['MY_SQL'].get_all(query, data)

def scoreboard_lisp(level):
    if not level.isdigit():
        return render_template("not_found.html")
    if int(level) not in [1, 2, 3]:
        return render_template("not_found.html")

    robots = r.get_registry()['ROBOTS'].get_all_robots()

    if not robots:
        return render_template("not_found.html")

    # add additional parameters to be displayed on scoreboard
    robots = ScoreBoard.add_scoreboard_params(robots)

    # filter robots
    filtered_robots = ScoreBoard.filter_robots_level(robots, int(level))

    # key used for sorting
    score_name = "LS" + level

    # sort based on name then this level's lowest score
    sorted_robots = sorted(list(filtered_robots), key=lambda k: k['name'])
    sorted_robots = sorted(list(sorted_robots), key=lambda k: k[score_name])

    return render_template(
        "scoreboard_lisp.html",
        robots=sorted_robots,
        level=level,
        score_name=score_name
    )

def home():
    robots = r.get_registry()['ROBOTS'].get_all_robots()
    for rb in robots:
        rb['endpoint'] = url_for('main.robot_detail', robot_id=rb['id'])
    return render_template(
        "home.html",
        robots=robots
    )

 def record_robot(division,
                  id,
                  volume,
                  school,
                  name,
                  is_unique,
                  used_versa_valve,
                  level,
                  is_disqualified,
                  passed_inspection):
     query = """INSERT INTO robots(
         division,
         id,
         volume,
         school,
         name,
         is_unique,
         used_versa_valve,
         level,
         is_disqualified,
         passed_inspection
     ) VALUES (
         %(division)s,
         %(id)s,
         %(volume)s,
         %(school)s,
         %(name)s,
         %(is_unique)s,
         %(used_versa_valve)s,
         %(level)s,
         %(is_disqualified)s,
         %(passed_inspection)s
     );"""
     data = {
         'division': division,
         'id': id,
         'volume': volume,
         'school': school,
         'name': name,
         'is_unique': is_unique,
         'used_versa_valve': used_versa_valve,
         'level': level,
         'is_disqualified': is_disqualified,
         'passed_inspection': passed_inspection
     }
     r.get_registry()['MY_SQL'].insert(query, data)

def export_to_csv():
    divisions = ['junior', 'walking', 'high_school', 'senior']

    all_robots = {}

    for division in divisions:
        all_robots[division] = r.get_registry()['ROBOTS'].get_all_robots_division(division);

    si = StringIO.StringIO()
    cw = csv.writer(si)
    cw.writerow(['Rank', 'Division', 'Name', '# of Successful Runs',
                 'Current Level', 'LS1', 'LS2', 'LS3', 'TFS', 'Volume',
                 'From NA', 'From CT', 'Unique'])

    for div in all_robots:
        for robot in all_robots[div]:
            runs = r.get_registry()['RUNS'].get_runs(robot['id'])
            # get current best scores
            best_scores, attempted_levels, total_score, num_successful = (
                ScoreCalculator.get_best_scores(runs)
            )
            robot.update(best_scores)
            robot['TFS'] = total_score
            robot['num_successful'] = num_successful
            # calculate lowes scores for each level and TFS, returns tuple
            robot['completed'] = attempted_levels

        # sort based on name then total score
        sorted_robots = sorted(list(all_robots[div]), key=lambda k: k['name'])
        sorted_robots = sorted(list(sorted_robots), key=lambda k: k['TFS'])

        for index, sorted_r in enumerate(sorted_robots, start=1):
            cw.writerow([index, sorted_r['division'], sorted_r['name'], sorted_r['num_successful'],
                        sorted_r['level'], sorted_r['LS1'], sorted_r['LS2'], sorted_r['LS3'],
                         sorted_r['TFS'], sorted_r['volume'],
                         sorted_r['from_na'], sorted_r['from_ct'],
                         sorted_r['is_unique']])

        cw.writerow('\n')

    output = make_response(si.getvalue())
    si.close()
    output.headers["Content-Disposition"] = "attachment; filename=scoreboard.csv"
    output.headers["Content-type"] = "text/csv"
    return output

 def approve_and_store_volume(robot_id, volume):
     query = """UPDATE robots SET volume = %(volume)s,
         passed_inspection = %(passed_inspection)s
         where id = %(robot_id)s;"""
     data = {
         'volume': volume,
         'robot_id': robot_id,
         'passed_inspection': True
     }
     return r.get_registry()['MY_SQL'].insert(query, data)

def load_registry():
    """ Initialize and load the global registry
    """
    r = registry.get_registry()
    if r.is_locked():
        return
    init_mysql(r)
    init_redis(r)
    init_clients(r)
    init_db_objects(r)
    r.lock()

 def add_scoreboard_params(robots):
     # adds necessary parameters to be displayed on the scoreboard
     for robot in robots:
         runs = r.get_registry()['RUNS'].get_runs(robot['id'])
         best_scores, attempted_levels, total_score, num_successful = (
             ScoreCalculator.get_best_scores(runs)
         )
         robot.update(best_scores)
         robot['TFS'] = total_score
         robot['completed'] = attempted_levels
         robot['num_successful'] = num_successful
     return robots

def robot_detail(robot_id, inputs=None):
    robot = r.get_registry()['ROBOTS'].get_robot(robot_id)
    if not robot:
        return render_template("not_found.html")

    runs = r.get_registry()['RUNS'].get_runs(robot_id)
    run_levels = [run['id'] for run in runs]

    # check if disqualifited and eligibility to advnace to next level
    eligibility = LevelProgressHandler.get_eligibility_for_next_run(
        runs, robot['level']
    )
    # get current best scores
    best_scores, attempted_levels, total_score, num_successful = (
        ScoreCalculator.get_best_scores(runs)
    )

    # check how many runs robot did on sunday (sunday = 1)
    # TODO: this logic needs to be rewritten after the competition
    sunday = 1
    filtered = filter_runs_day(runs, sunday)
    already_run_three = False
    if len(filtered) >= 3:
        already_run_three = True


    return render_template(
        "robot.html",
        attempted_levels=attempted_levels,
        total_score=total_score,
        robot_id=robot_id,
        robot=robot,
        disqualified=eligibility['disqualified'],
        eligible=eligibility['can_level_up'],
        best_scores=best_scores,
        robot_runs=runs,
        applied_factors=[applied_factors(id, robot_id) for id in run_levels],
        inputs=inputs,
        already_run_three=already_run_three
    )