import web

db = web.database(dbn='sqlite', db='stashbox.db')

def get_folders():
    folder_result = db.query("""
SELECT d.id, d.name, u.username as owner
  FROM directory d 
  JOIN user u ON d.owner_id = u.id 
 ORDER BY d.name
""")

    folders = []

    for row in folder_result:
        folder = {}
        folder['id'] = row.id
        folder['name'] = row.name
        folder['owner'] = row.owner
        folder['users'] = []

        user_result = db.query("""
                               SELECT u.username
                                 FROM user u, directory_user a
                                WHERE a.user_id = u.id 
                                  AND a.directory_id = %s
                             ORDER BY u.username
                               """ % row.id)

        for user in user_result:
            folder['users'].append(user.username)
        
        folders.append(folder)

    return folders

def new_folder(name, owner):
    result = db.select('directory', where="name=$name", vars=locals())

    if not result:
        return db.insert('directory', name=name.replace("/",""), owner_id=owner)

def del_folder(id):
    db.delete('directory', where="id=$id", vars=locals())
    db.delete('directory_user', where="directory_id=$id", vars=locals())

def get_users():
    return db.select('user')

def authorize_user(user_id, folder_id):
    return db.insert('directory_user', directory_id=folder_id, user_id=user_id)

def add_user(username, password):
    result = db.select('user', where="username=$username", vars=locals())

    if not result:
        return db.insert('user', username=username, password=password)