text: # -*- coding: iso-8859-1 -*-
  
  import os
  import shutil
  import datetime
  import sqlite3
  from flask import Flask, request, session, render_template, g, redirect, url_for, abort, flash, make_response
  from random import randint
  import json
  import urllib2
  import json
  from json.decoder import JSONObject
  from werkzeug.utils import secure_filename
  
  UPLOAD_FOLDER = '/tmp'
  ALLOWED_EXTENSIONS = set(['txt', 'pdf', 'png', 'jpg', 'jpeg', 'gif'])
  
  DBBACKUPPATH = os.path.abspath('db_backup')
  
  if os.path.exists(DBBACKUPPATH) == False:
      os.mkdir(DBBACKUPPATH)
  
  app = Flask(__name__)
  #app.config['UPLOAD_FOLDER'] = UPLOAD_FOLDER
  
  def allowed_file(filename):
      return '.' in filename and \
             filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS
  app = Flask(__name__)
  app.config.from_object(__name__)
  
  # Load default config and override config from an environment variable
  app.config.update(dict(
      DATABASE=os.path.join(app.root_path, 'recipes.db'),
      SECRET_KEY='development key',
      USERNAME='admin',
      PASSWORD='default',
      UPLOAD_FOLDER='/tmp'
  ))
  
  app.config['UPPLOAD_FOLDER'] = '/tmp'
  
  app.config.from_envvar('FLASKR_SETTINGS', silent=True)
  
  def connect_db():
      """Connects to the specific database."""
      if os.path.exists(app.config['DATABASE']) == False:
          cmd = 'sqlite3 recipes.db < database.sql'
          os.system(cmd)
      rv = sqlite3.connect(app.config['DATABASE'])
      rv.row_factory = sqlite3.Row
      return rv
  
  def get_db():
      """Opens a new database connection if there is none yet for the
      current application context.
      """
      if not hasattr(g, 'sqlite_db'):
          g.sqlite_db = connect_db()
      return g.sqlite_db
  
  @app.teardown_appcontext
  def close_db(error):
      """Closes the database again at the end of the request."""
      if hasattr(g, 'sqlite_db'):
          g.sqlite_db.close()
  
  def init_db():
      db = get_db()
      with app.open_resource('database.sql', mode='r') as f:
          db.cursor().executescript(f.read())
      db.commit()
  
  def queryDbFetchOne(query):
      """Query database, return one result"""
      db = get_db()
      cur = db.cursor()
      cur.execute(query)
      return cur.fetchone()
  
  def queryDbFetchAll(query):
      """Query database, return one result"""
      db = get_db()
      cur = db.cursor()
      cur.execute(query)
      return cur.fetchall()
  
  def getRecipe(recipeKey):
      """Get recipe data"""
      return queryDbFetchOne('SELECT * FROM recipes WHERE key="%s"'%recipeKey)
  
  def getIngredients(recipeKey):
      """Get all ingredients for a recipe"""
      return queryDbFetchAll('SELECT * FROM recipeAmount WHERE recipeKey="%s"'%recipeKey)
  
  def getNextKey():
      """Get next number for key"""
      currentHighKey = queryDbFetchOne('SELECT key FROM recipes ORDER BY key DESC')
      if currentHighKey is None:
          print "IS none %s"%currentHighKey
          currentHighKey = 0
      else:
          currentHighKey = int(currentHighKey[0])
      return currentHighKey +1
  
  def insertIntoDb(table, names, values):
      """Insert into database"""
      if len(values) != len(names):
          return None
      
      query = 'INSERT INTO %s (%s) VALUES(%s)'%(table, ', '.join(names), ', '.join(values))
      rowId = None
      try:
          db = get_db()
          cur = db.cursor()
          cur = get_db().cursor()
          cur.execute(query)
          db.commit()
          rowId = cur.lastrowid
      except:
          db.rollback()
      finally:
          return rowId
  
  def doRawQuery(query):
      """Do a raw query"""
      rowId = None
      try:
          db = get_db()
          cur = db.cursor()
          cur = get_db().cursor()
          cur.execute(query)
          db.commit()
          rowId = cur.lastrowid
      except:
          db.rollback()
      
      finally:
          return rowId
  
  def updateDb(table, names, values, where):
      """Update row in table"""
      if len(values) != len(names):
          return None
      
      query = 'UPDATE %s SET '%(table)
      qPairs = []
      for name, value in zip(names,values):
          qPairs.append('%s=%s'%(name,value))
      query += ', '.join(x for x in qPairs)
      query += ' %s'%where
      rowId = None
      try:
          db = get_db()
          cur = db.cursor()
          cur = get_db().cursor()
          cur.execute(query)
          db.commit()
          rowId = cur.lastrowid
      except:
          db.rollback()
      finally:
          return rowId
      
  @app.route('/prepdb')
  def prepdb():
      """Prepare database from json file"""
      f = open('recipes.json','r')
      buff = f.read()
      recipes = json.loads(buff)
      for item in recipes:
          recipeKey = getNextKey()
          rowId = insertIntoDb('recipes', ['key', 'title','instructions', 'portions'],
                       [recipeKey, '"%s"'%item['title'], '"%s"'%item['instructions'], item['portions']])
          for ingredient in item['ingredients']:
              keys = ingredient.keys()
              keys.insert(0, 'recipeKey')
              values = ingredient.values()
              values.insert(0, recipeKey)
              rId = insertIntoDb('recipeAmount', keys, values)
          for group in item['recipeTag']:
              insertIntoDb('recipeTag', ['recipeKey', 'group'], [recipeKey, '"%s"'%group])
          if 'fridge' in item:
              insertIntoDb('fridge', ['recipeKey', 'portions'], [recipeKey, item['fridge']])
              print " Fridge %d"%item['fridge']
          else:
              print "No fridge"
      return index()
          
  @app.cli.command('initdb')
  def initdb_command():
      """Initializes the database."""
      init_db()
      print 'Initialized the database.'
  
  @app.route('/help')
  def help():
      values = {'pageId': 'help',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
                }
      return render_template('help.html', **values)
  
  @app.route('/')
  def index():
      values = {'pageId': 'index',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
                }
      return render_template('index.html', **values)
  #    return redirect('login', code=304)
      
  @app.route('/login', methods=['GET','POST'])
  def login():
      error = None
      if request.method == 'POST':
          if request.form['username'] != 'admin' or request.form['password'] != 'admin':
              error = 'Invalid Credentials. Please try again.'
          else:
              return redirect(url_for('favourite'), code=304)
      values = {'pageId': 'index',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048),
                'error': error
                }
      return render_template('login.html', **values)
  
  @app.route('/editRecipe', methods=['GET'])
  def editRecipe():
      return newRecipe(request.args['recipeKey'])
  
  @app.route('/deleteRecipe', methods=['GET'])
  def deleteRecipe():
      # TODO
      if 'recipeKey' in request.args:
          pass
      pass
  
  def deleteAmount(recipeKey):
      query = 'DELETE FROM recipeAmount WHERE recipeKey=%s'%recipeKey
      try:
          db = get_db()
          cur = db.cursor()
          cur = get_db().cursor()
          cur.execute(query)
          db.commit()
          rowId = cur.lastrowid
      except:
          db.rollback()
          msg = "error in delete operation"
          print msg
      
      finally:
          return rowId
  
  @app.route('/newRecipe')
  def newRecipe(recipeKey=None):
      if recipeKey is not None:
          recipe = getRecipe(recipeKey)
          ingredients = getIngredients(recipeKey)
      else:
          recipe = None
          ingredients = None
      entries = queryDbFetchAll('SELECT name FROM ingredients ')
      measurements = queryDbFetchAll('SELECT short FROM measurements ')
      values = {'ingredientsList': entries,
                'measurements':measurements,
                'recipe':recipe,
                'ingredients':ingredients,
                'pageId': 'newRecipe',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
                }
      return render_template('newRecipe.html', **values)
  
  @app.route('/error')
  def errorHtml():
      values = {'pageId': 'error',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
                }
      return render_template('error.html', **values)
  
  def allowed_file(filename):
      return '.' in filename and \
             filename.rsplit('.', 1)[1] in ALLOWED_EXTENSIONS
  
  @app.route('/saveRecipe', methods=['POST'])
  def saveRecipe():
      # TODO add last update time
      title = request.form['title']
      names = ['title']
      values = ['"%s"'%title]
      if 'instructions' in request.form:
          names.append('instructions')
          values.append('"%s"'%request.form['instructions'])
      if 'portions' in request.form:
          names.append('portions')
          values.append(request.form['portions'])
       
      if 'recipeKey' in request.form:
          recipeKey = request.form['recipeKey']
          updateDb('recipes', names, values, 'WHERE key=%s'%recipeKey)
      else:
          recipeKey = getNextKey()
          names.insert(0, 'key')
          values.insert(0, '%d'%recipeKey)
          if insertIntoDb('recipes', names, values) is None:
              return json.dumps({'redirect':'false', 'result': 'Error creating recipe'})
       
      amount = request.form.getlist('amount')
      measurement = request.form.getlist('measurement')
      ingredients = request.form.getlist('ingredient')
      deleteAmount(recipeKey)
      for a,m,i in zip(amount, measurement, ingredients):
          names =  ['recipeKey', 'ingredient', 'amount', 'measurement']
          values = [str(recipeKey), '"%s"'%i, str(a), '"%s"'%m]
          if insertIntoDb('recipeAmount', names, values) is None:
              return json.dumps({'redirect':'false', 'result': 'Error creating recipe'})
      return json.dumps({'redirect':True, 'url': '/show/recipe?recipe=%s'%recipeKey})
  
  @app.route('/show/recipe', methods=['GET'])
  def showRecipe():
      recipeKey = request.args.get('recipe')
      recipe = getRecipe(recipeKey)
      return displayRecipe(recipe)
  
  def displayRecipe(recipe):
      values = {'key':recipe['key'],
                'title': recipe['title'],
                'instructions': recipe['instructions'],
                'portions': recipe['portions'],
                'ingredients': getIngredients(recipe['key']),
                'pageId': 'displayRecipe',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
                }
      
      return render_template('displayRecipe_template.html', **values)
  
  @app.route('/randomRecipe', methods=['GET'])
  def randomRecipe():
      recipes = queryDbFetchAll('SELECT * FROM recipes ORDER BY RANDOM() LIMIT 4')
      return render_template('listRecipes.html', header='F&ouml;rslag:', lastRecipes=recipes)
  
  @app.route('/menuSuggestion', methods=['GET'])
  def menuSuggestion():
      recipes = queryDbFetchAll('SELECT * FROM recipes ORDER BY RANDOM() LIMIT 4')
      if 'update' in request.args:
          return render_template('onlyList.html', lastRecipes=recipes)
      
      values = {'pagetitle':'Receptakuten',
                'title': 'F&ouml;rslag:',
                'lastRecipes': recipes,
                'refresh': 'true',
                'pageId': 'menuSuggestion',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
                }
      return render_template('listRecipes.html', **values)
  
  @app.route('/ajax/search', methods=['GET'])
  def searchAjax():
      if request.method == 'GET':
          patterns = request.args.getlist('searchPatterns[]')
          query = ''
          for p in patterns:
              if len(query) > 0:
                  query = '%s or '%query
              query += 'title LIKE "%%%s%%" or instructions LIKE "%%%s%%"'%(p, p)
          query = 'SELECT key, title FROM recipes WHERE %s LIMIT 10'%query
          results = queryDbFetchAll(query)
          t = []
          for p in results:
              h = {}
              for k in p.keys():
                  h[k] = p[k]
              t.append(h)
          return json.dumps(t)
  
  @app.route('/ajax/searchIngredient', methods=['GET'])
  def searchIngredient():
      if request.method == 'GET':
          patterns = request.args.getlist('searchPatterns[]')
          print patterns
          query = ''
          for p in patterns:
              if len(query) > 0:
                  query = '%s or '%query
              query += 'ingredient LIKE "%%%s%%"'%(p)
          query = 'SELECT DISTINCT ingredient FROM recipeAmount WHERE %s'%query
          print query
          results = queryDbFetchAll(query)
          t = []
          for p in results:
              h = {}
              for k in p.keys():
                  h[k] = p[k]
              t.append(h)
          return json.dumps(t)
  
  @app.route('/search')
  def search():
      values = {'pageId': 'search',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
                }
      return render_template('search.html', **values)
      
  def getFridgeJSON():
      fridgeContent = queryDbFetchAll('SELECT key, title, fridge.portions AS portions FROM recipes INNER JOIN fridge ON recipes.key = fridge.recipeKey')
      fridgeJson = []
      for row in fridgeContent:
          rowJson = {}
          for key in row.keys():
              rowJson[key] = row[key]
          fridgeJson.append(rowJson)
      return json.dumps(fridgeJson)
  
  @app.route('/fromTheFridge')
  def fromTheFridge():
      values = {'pageId': 'fromTheFridge',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
                }
      return render_template('whatsinthefridge.html', **values)
  
  # Update fridge content
  @app.route('/ajax/updateFridge', methods=['GET','POST'])
  def updateFridge():
      if request.method == 'POST':
          recipesJson = request.form.getlist('recipes')
          recipes = json.loads(recipesJson[0])
          keys = []
          for item in recipes:
              keys.append(item['key'])
              queryUpdate = 'UPDATE fridge SET portions=%d WHERE recipeKey=%d'%(item['portions'], item['key'])
              queryInsert = 'INSERT INTO fridge (recipeKey, portions) SELECT %d,%d WHERE(Select Changes() = 0)'%(item['key'], item['portions'])
              doRawQuery(queryUpdate)
              doRawQuery(queryInsert)
          currentKeys = queryDbFetchAll('SELECT recipeKey FROM fridge ORDER BY recipeKey')
          for key in currentKeys:
              if key['recipeKey'] not in keys:
                  deleteQuery = 'DELETE FROM fridge WHERE recipeKey=%s'%key['recipeKey']
                  doRawQuery(deleteQuery)
      return getFridgeJSON()
  
  
  @app.route('/groceryList')
  def groceryList():
      recipes = queryDbFetchAll('SELECT key, title, portions FROM recipes ORDER BY title')
      ingredients = {}
      for recipe in recipes:
          ingredients[recipe['key']] = getIngredients(recipe['key'])
      values = {'pageId': 'groceryList',
                'recipes': recipes,
                'ingredients': ingredients,
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
                 }
      return render_template('groceryList.html', **values)
  
  
  @app.route('/favourite')
  def favourite():
      """Show favourite recipes"""
      values = {'pageId': 'favouritePage',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
                 }
      return render_template('favourite.html', **values)
      
  @app.route('/ajax/getRecipesJson', methods=['GET','POST'])
  def getRecipesJson():
      if request.method == 'POST':
          recipeKeys = request.form.getlist('recipe')
          query = 'SELECT * FROM recipes where '
          qyeryKeys = []
          for recipes in recipeKeys:
              jsonKeys = json.loads(recipes)
              for key in jsonKeys:
                  qyeryKeys.append('key=%s'%key['recipeKey'])
          query += ' OR '.join(qyeryKeys)
          recipeList = queryDbFetchAll(query)
          jsonReply = []
          for rowRecipe in recipeList:
              tmpJson = {}
              for key in rowRecipe.keys():
                  tmpJson[key] = rowRecipe[key]
              ingredientsJson = []
              for row in getIngredients(rowRecipe['key']):
                  tmpIngredient = {}
                  for key in row.keys():
                      if key == 'recipeKey':
                          continue
                      tmpIngredient[key] = row[key]
                  ingredientsJson.append(tmpIngredient)
              tmpJson['ingredients'] = ingredientsJson
              jsonReply.append(tmpJson)
          return json.dumps(jsonReply)
      
      recipes = queryDbFetchAll('SELECT key, title FROM recipes')
      rows = []
      for i in recipes:
           rows.append(dict(i))
      return json.dumps(rows)
  
  @app.route('/manifest.json')
  def manifestJSON():
      return url_for('static', filename='manifest.json')
  
  @app.route('/manifest.appcache')
  def manifest():
      res = make_response(render_template('manifest.appcache'), 200)
      res.headers["Content-Type"] = "text/cache-manifest"
      return res
  
  @app.route('/admin/restore', methods = ['POST'])
  def dorestore():
      versionF = os.path.abspath(os.path.join(DBBACKUPPATH, request.form.get('version')))
      if os.path.exists(versionF):
          now = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
          name = '%s_bfrestore.sql'%now
          dobackup(name)
          tables = queryDbFetchAll('SELECT name FROM sqlite_master WHERE type = "table"')
          for tab in tables:
              doRawQuery('DROP TABLE %s'%tab['name'])
          cmd = 'sqlite3 recipes.db < %s'%versionF
          os.system(cmd)
      return getstatus()
  
  @app.route('/admin/backup')
  def adminbackup():
      now = datetime.datetime.now().strftime('%Y%m%d_%H%M%S')
      dobackup(now+'.sql')
      return getstatus()
  
  def dobackup(name):
      dbF =  open(os.path.join(DBBACKUPPATH, name), 'w')
      con = get_db()
      dbF.write('\n'.join(con.iterdump()).encode('utf8'))
      dbF.close()
  
  @app.route('/admin/status')
  def getstatus():
      status = {}
      status['num_of_recipes'] = queryDbFetchOne('SELECT count(*) as rows FROM recipes')['rows']
      status['num_of_fridge'] = queryDbFetchOne('SELECT count(*) as rows FROM fridge')['rows']
      status['num_of_ingredients'] = queryDbFetchOne('SELECT count(*) as rows FROM (SELECT DISTINCT ingredient FROM recipeAmount)')['rows']
      status['backups'] = sorted(os.listdir(DBBACKUPPATH), reverse=True)
      return json.dumps(status, sort_keys=True, indent=4, separators=(',', ': '))
  
  @app.route('/admin')
  def adminpage():
      values = {'pageId': 'adminPage',
                'popupMenuId': 'popupMenuId%d'%randint(1, 1048)
              }
      return render_template('admin.html', **values)
  
  if __name__ == "__main__":
  #     import logging
  #     file_handler = RotatingFileHandler('/tmp/receptakuten.log', bakupCount=5)
  #     file_handler.setLevel(logging.WARNING)
  #     app.logger.addHandler(file_handler)
      app.run(host="0.0.0.0", debug=True)
  #    app.run(debug=True)
  