from flask import Flask, request, send_file, g
import sqlite3

app = Flask(__name__)

def init_db():
    db = sqlite3.connect('users.db')
    c = db.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS users
                 (email TEXT, downloads INTEGER)''')
    c.close()
    db.commit()
    db.close()

def get_db():
    db = getattr(g, 'db', None)
    if not db:
        db = g.db = sqlite3.connect('users.db')
    return db

@app.teardown_appcontext
def close_connection(exception):
    db = getattr(g, 'db', None)
    if db:
        db.close()    

@app.route('/')
def index():
    return '''
        <form method="POST" action="/register">
        <label for="email">E-mail:</label>
        <input type="email" name="email" id="email">
        </form>
    '''

@app.route('/register', methods=['POST'])
def register():
    email = request.form['email']
    db = get_db()
    c = db.cursor()
    c.execute("INSERT INTO users VALUES ('{}', {})".format(email, 0))
    db.commit()
    download_id = c.lastrowid
    c.close()
    return '<a href="/downloads?id={}">Download</a>'.format(download_id)

@app.route('/downloads')
def download():
    download_id = request.args['id']
    db = get_db()
    c = db.cursor()
    c.execute('SELECT * FROM users WHERE rowid=?', [download_id])
    res = c.fetchone()
    if res:
        c.execute('UPDATE users SET downloads = downloads + 1 WHERE rowid=?', [download_id])
        db.commit()
        c.close()
        return send_file('foo.app', as_attachment=True)
    else:
        c.close()
        return 'Sorry, download id invalid.'

@app.route('/stats')
def stats():
    c = get_db().cursor()
    c.execute('SELECT * FROM users')
    res = ''
    for email, dcount in c:
        res += '{}\t{}<br>'.format(email, dcount)
    c.close()
    return res
    
if __name__ == "__main__":
    # init_db() # run once to create database
    app.run()