import mysql.connector
from flask import Flask, request, jsonify, redirect
import json
from datetime import datetime
from furl import furl

app = Flask(__name__)
mydb = mysql.connector.connect(
  host="127.0.0.1",
  #port=3308,
  #user="python_boi",
  user="admin",
  #passwd="<PASSWORD>",
  passwd="",
  database="test"
)
mycursor = mydb.cursor(dictionary=True)

users_table = "users"
groups_table = "groups"
#mycursor.execute("DROP TABLE users")

# mycursor.execute("CREATE TABLE IF NOT EXISTS users (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, created VARCHAR(255) NOT NULL, lastModified VARCHAR(255) NOT NULL, userName VARCHAR(255) NOT NULL, name VARCHAR(255), displayName VARCHAR(255), nickName VARCHAR(255), profileUrl VARCHAR(255), title VARCHAR(255), userType VARCHAR(255), preferredLanguage VARCHAR(255), locale VARCHAR(255), timezone VARCHAR(255), active BOOLEAN, password VARCHAR(<PASSWORD>), emails VARCHAR(255), phoneNumbers VARCHAR(255), ims VARCHAR(255), photos VARCHAR(255), addresses VARCHAR(255), groups VARCHAR(255), entitlements VARCHAR(255), roles VARCHAR(255), x509Certificates VARCHAR(255))")

# mycursor.execute("CREATE TABLE IF NOT EXISTS groups (id INT PRIMARY KEY NOT NULL AUTO_INCREMENT, created VARCHAR(255) NOT NULL, lastModified VARCHAR(255) NOT NULL, displayName VARCHAR(255) NOT NULL, members VARCHAR(255))")

# mycursor.execute("CREATE TABLE IF NOT EXISTS memberships (userID VARCHAR(255), groupID VARCHAR(255))")

mycursor.execute('SELECT * FROM users')
mycursor.fetchall()
field_names = [i[0] for i in mycursor.description]
group_field_names = ["id", "created", "lastModified", "displayName"]
print(field_names)

def user_count():
  mycursor.execute("SELECT * FROM {}".format(users_table))
  mycursor.fetchall()
  count = mycursor.rowcount
  print("\nrow count:\n"+str(count))
  return count


def create_user():
  req_data = json.loads(request.data, strict=False)
  mycursor.execute("SELECT COUNT(*) FROM "+users_table+" WHERE userName='"+json.dumps(req_data["userName"])+"' LIMIT 0,1")
  fetch = mycursor.fetchone()

  if fetch["COUNT(*)"] != 0:
    result = {}
    result["schemas"] = ["urn:ietf:params:scim:api:messages:2.0:Error"]
    result["status"] = 409
    result["detail"] = "User already exists in the database."

    return json.dumps(result), 409
  else:
    timestamp = str(datetime.utcnow())
    req_data["created"] = timestamp
    req_data["lastModified"] = timestamp
    if users_table == "users3":
      req_data["id"] = str(json.loads(req_data["externalId"])) # added for okta user id primary key
    if bool(req_data["active"]) == True:
      req_data["active"] = 1
    else:
      req_data["active"] = 0

    keys = []
    values = []
    for x in req_data:
      if x in field_names:
        keys.append(x)
        values.append(json.dumps(req_data[x]))
    keys = (str(keys)[1:-1]).replace("'","")
    values = (str(values)[1:-1])
    mycursor.execute("INSERT INTO {} ({}) VALUES ({})".format(users_table, keys, values))
    mydb.commit()

    return read_user(mycursor.lastrowid), 201

def read_all_users():
  url = request.url
  params = furl(request.url)
  if url.find('filter=') == -1:
    mycursor.execute("SELECT COUNT(*) FROM {} LIMIT 0,1".format(users_table))
    fetch = mycursor.fetchone()
  else:
    user_query = json.dumps(params.args["filter"].replace('userName eq ', ''))

    mycursor.execute("SELECT COUNT(*) FROM {} WHERE userName = {} LIMIT 0,1".format(users_table, user_query))
    fetch = mycursor.fetchone()
  result = {}

  result["schemas"] = ["urn:ietf:params:scim:api:messages:2.0:ListResponse"]
  result["totalResults"] = fetch["COUNT(*)"]
  result["startIndex"] = 1
  result["itemsPerPage"] = fetch["COUNT(*)"]
  result["Resources"] = []
  
  if url.find('filter=') == -1:
    mycursor.execute("SELECT id FROM {}".format(users_table))
    alldata = mycursor.fetchall()

    for x in alldata:
      if users_table == "users3":
        current_user = json.dumps(read_user(x['id']))
      else:
        current_user = json.loads(read_user(x['id']))
      #result["Resources"].append(json.loads(current_user))
      result["Resources"].append(current_user)
  else:
    if result["totalResults"] > 0 :
      mycursor.execute("SELECT id FROM {} WHERE userName = {} ORDER BY id ASC".format(users_table, user_query))
      fetch = mycursor.fetchone()
      
      result["Resources"].append(json.loads(read_user(fetch['id'])))
      

  return json.dumps(result).replace("\\","")

def read_user(id):
  if users_table == "users3":
    mycursor.execute("SELECT COUNT(*) FROM {} WHERE id = {} LIMIT 0,1".format(users_table, json.dumps(id)))
  else:  
    mycursor.execute("SELECT COUNT(*) FROM {} WHERE id = {} LIMIT 0,1".format(users_table, id))
  fetch = mycursor.fetchone()

  result = {}

  if fetch["COUNT(*)"] == 0:
    result["schemas"] = ["urn:ietf:params:scim:api:messages:2.0:Error"]
    result["status"] = 404
    result["detail"] = "User not found"

    return json.dumps(result), 404
  else:
    if users_table == "users3":
      mycursor.execute("SELECT * FROM {} WHERE id = {} LIMIT 0,1".format(users_table, json.dumps(id)))
    else:
      mycursor.execute("SELECT * FROM {} WHERE id = {} LIMIT 0,1".format(users_table, id))
    data = mycursor.fetchone()
    
    result["schemas"] = ["urn:ietf:params:scim:schemas:core:2.0:User"]
    if users_table == "users3":
      result["id"] = json.loads(id)
    else:
      result["id"] = id
    result["userName"] = json.loads(data["userName"])
    result["active"] = bool(data["active"])
    if data["name"] != None:
      result["name"] = json.loads(data["name"])
    if data["displayName"] != None:
      result["displayName"] = json.loads(data["displayName"])
    if data["nickName"] != None:
      result["nickName"] = json.loads(data["nickName"])
    if data["profileUrl"] != None:
      result["profileUrl"] = json.loads(data["profileUrl"])
    if data["title"] != None:
      result["title"] = json.loads(data["title"])
    if data["preferredLanguage"] != None:
      result["preferredLanguage"] = json.loads(data["preferredLanguage"])
    if data["locale"] != None:
      result["locale"] = json.loads(data["locale"])
    if data["timezone"] != None:
      result["timezone"] = json.loads(data["timezone"])
    if data["emails"] != None:
      result["emails"] = json.loads(data["emails"])
    if data["phoneNumbers"] != None:
      result["phoneNumbers"] = json.loads(data["phoneNumbers"])
    if data["ims"] != None:
      result["ims"] = json.loads(data["ims"])
    if data["photos"] != None:
      result["photos"] = json.loads(data["photos"])
    if data["addresses"] != None:
      result["addresses"] = json.loads(data["addresses"])
    if data["entitlements"] != None:
      result["entitlements"] = json.loads(data["entitlements"])
    if data["roles"] != None:
      result["roles"] = json.loads(data["roles"])
    if data["x509Certificates"] != None:
      result["x509Certificates"] = json.loads(data["x509Certificates"])
    if data["extension_enterprise"] != None:
      result["urn:ietf:params:scim:schemas:extension:enterprise:2.0:User"] = json.loads(data["extension_enterprise"])
      result["schemas"].append("urn:ietf:params:scim:schemas:extension:enterprise:2.0:User")
      result["schemas"].append("urn:ietf:params:scim:schemas:extension:lattice:attributes:1.0:User")
    result["groups"] = []
    result["meta"] = { "resourceType" : "User", "created" : json.loads(data["created"]), "lastModified" : json.loads(data["lastModified"]), "location" : "http://localhost:8080/scim/v2/Users/"+str(id)}

    return json.dumps(result).replace("\\","")

def update_user(id):

  if request.method == 'PUT':
    req_data = json.loads(request.data, strict=False)
    timestamp = str(datetime.utcnow())
    req_data["lastUpdated"] = timestamp
    if users_table == "users3":
      req_data["id"] = req_data["id"]
    else:
      req_data["id"] = int(id)
    if req_data["active"]:
      req_data["active"] = 1
    else:
      req_data["active"] = 0
      
    for data in req_data:
      if data in field_names:
        mycursor.execute("UPDATE " + users_table + " SET " + data + " = %s WHERE id=%s",(json.dumps(req_data[data]), req_data["id"]))

    if "urn:ietf:params:scim:schemas:extension:enterprise:2.0:User" in req_data["schemas"]:
      mycursor.execute("UPDATE " + users_table + " SET extension_enterprise = %s WHERE id=%s",(json.dumps(req_data["urn:ietf:params:scim:schemas:extension:enterprise:2.0:User"]), req_data["id"]))

  elif request.method == 'PATCH':
    req_data = json.loads(request.data, strict=False)
    if req_data["Operations"][0]["value"]["active"] == False:
      data = "active=0"
    else:
      data = "active=1"
    mycursor.execute("UPDATE " + users_table + " SET " + data + " WHERE id=" + id)

  mydb.commit()

  return read_user(id)


def create_group():
  req_data = json.loads(request.data, strict=False)
  timestamp = str(datetime.utcnow())
  req_data["created"] = timestamp
  req_data["lastModified"] = timestamp
  memberships = req_data["members"]
  keys = []
  values = []
  for x in req_data:
    if x in group_field_names:
      keys.append(x)
      values.append(json.dumps(req_data[x]))
  keys = (str(keys)[1:-1]).replace("'","")
  values = (str(values)[1:-1])

  mycursor.execute("INSERT INTO {} ({}) VALUES ({})".format(groups_table, keys, values))
  mydb.commit()

  group_id = mycursor.lastrowid

  for x in memberships:
    mycursor.execute("INSERT INTO {} ({}) VALUES ({},{})".format("memberships", "userID, groupID", x["value"], group_id))
    mydb.commit()

  return read_group(group_id), 201

def read_group(id):
  mycursor.execute("SELECT COUNT(*) FROM {} WHERE id = {} LIMIT 0,1".format(groups_table, id))
  fetch = mycursor.fetchone()

  result = {}
  
  if fetch["COUNT(*)"] == 0:
    result["schemas"] = ["urn:ietf:params:scim:api:messages:2.0:Error"]
    result["status"] = 404
    result["detail"] = "Group not found"

    return json.dumps(result), 404
  else:
    mycursor.execute("SELECT * FROM {} WHERE id = {} LIMIT 0,1".format(groups_table, id))
    data = mycursor.fetchone()
    
    result["schemas"] = ["urn:ietf:params:scim:schemas:core:2.0:Group"]
    result["id"] = id
    result["displayName"] = json.loads(data["displayName"])
    result["members"] = []

    mycursor.execute("SELECT userID FROM {} WHERE groupID = {}".format("memberships", id))
    usersingroup = mycursor.fetchall()
    for x in usersingroup:
      result["members"].append(x["userID"])
    result["meta"] = { "resourceType" : "Group", "created" : json.loads(data["created"]), "lastModified" : json.loads(data["lastModified"]), "location" : "http://localhost:8080/scim/v2/Groups/"+str(id)}

    return json.dumps(result).replace("\\","")

def delete_group(id):
  mycursor.execute("DELETE FROM {} WHERE id={}".format(groups_table, int(id)))
  mydb.commit()

  mycursor.execute("DELETE FROM {} WHERE groupID={}".format("memberships", int(id)))
  mydb.commit()

  return "",204

def update_group(id):
  
  return "", 204

def read_all_groups():
  result = {}
  mycursor.execute("SELECT COUNT(*) FROM {} LIMIT 0,1".format(groups_table))
  fetch = mycursor.fetchone()
  result["schemas"] = ["urn:ietf:params:scim:api:messages:2.0:ListResponse"]
  result["totalResults"] = fetch["COUNT(*)"]
  result["startIndex"] = 1
  result["itemsPerPage"] = fetch["COUNT(*)"]
  result["Resources"] = []
  
  mycursor.execute("SELECT id FROM {}".format(groups_table))
  alldata = mycursor.fetchall()

  for x in alldata:
    current_user = json.loads(read_group(x['id']))
    result["Resources"].append(current_user)

  return json.dumps(result).replace("\\","")

@app.route("/scim/v2/Users", methods=['POST', 'GET'])
def users():
  if request.method == 'GET':
    return read_all_users()
  elif request.method == 'POST':
    return create_user()

@app.route("/scim/v2/Users/<id>", methods=['GET','PATCH','PUT'])
def users_id(id):
  if request.method == 'GET':
    return read_user(id)
  else:
    return update_user(id)

@app.route("/scim/v2/Groups", methods=['POST','GET'])
def groups():
  if request.method == 'GET':
    return read_all_groups()
  elif request.method == 'POST':
    return create_group()

@app.route("/scim/v2/Groups/<id>", methods=['GET','PATCH','DELETE', 'PUT'])
def groups_id(id):
  if request.method == 'GET':
    return read_group(id)
  elif request.method == 'DELETE':
    return delete_group(id)
  elif request.method == 'PATCH':
    return update_group(id)
  elif request.method == 'PUT':
    return read_group(id)
    # return update_group(id)

@app.route("/oauth2/authorize", methods=['GET'])
def authorize():
  url = request.url
  params = furl(request.url)
  redirect_uri = params.args["redirect_uri"]
  state = params.args["state"]
  code = "abcdef"
  redirect_path = redirect_uri+"?state="+state+"&code="+code
  return redirect(redirect_path, code=200)

@app.route("/oauth2/token", methods=['POST'])
def token():
  token_response={}
  token_response["scope"] = "api:admin"
  token_response["token_type"] = "Bearer"
  token_response["expires_in"] = 360
  token_response["access_token"] = "<PASSWORD>"
  token_response["refresh_token"] = "refresh"
  return jsonify(token_response), 200


@app.route("/", methods=['GET'])
def home():
    return "", 200

if __name__ == '__main__':
    app.run(host="localhost", port=8080, debug=True)
