
from django.db.models.expressions import F
from django.http import request, response
from django.views import View, generic
from django.shortcuts import render
from django.contrib import messages
from django.contrib.auth.views import LoginView, LogoutView
from django.db import connection as conn, models
from django.core import serializers
from django.contrib.auth.models import User,Group
from django.contrib.auth.hashers import make_password
from rest_framework import status
from rest_framework.decorators import api_view, renderer_classes
from rest_framework.response import Response



# Create your views here.
class CustomLoginView(LoginView):
    template_name = 'user/login.html'
    redirect_authenticated_user = True

class HomeView(View):
    template_name = 'layout/layout.html'

    def get(self, request):
        return render(request, self.template_name)

class CustomLogoutView(LogoutView):
    template_name = 'user/login.html'
    next_page = 'login'

class Usermanagement(generic.TemplateView):
    template_name = "layout/user_layout.html"
    
    def get(self, request):
        with conn.cursor() as userlist_:

            for g in request.user.groups.all():
                userGroup = g.id
            
            arrayGroups = []
            arrayGroups.append(userGroup)
            isChild = isHaveChild(userGroup)

            if isChild == True:
                childgroup = getGroupChilds(userGroup)
                for z in childgroup['data']:
                    arrayGroups.append(z[0])
                
                ars = ",".join(str(v) for v in arrayGroups)
            
            else:
                ars = "".join(str(v) for v in arrayGroups)

            
            # print(ars)
            if request.user.is_superuser:
                # print("is superuser")
                sql = """
                        SELECT
                        au.id,
                        au.username,
                        au.first_name,
                        au.last_name,
                        au.is_active,
                        au.last_login,
                        au.email,
                        ag.name,
                        au.is_staff,
                        au.is_superuser
                    FROM
                        auth_user au,
                        auth_user_groups aug,
                        auth_group ag
                    WHERE
                        au.id = aug.user_id AND
                        ag.id = aug.group_id
                    ORDER BY 
                        au.username ASC"""
            else:
                if request.user.is_staff:
                     sql = """
                        SELECT
                        au.id,
                        au.username,
                        au.first_name,
                        au.last_name,
                        au.is_active,
                        au.last_login,
                        au.email,
                        ag.name,
                        au.is_staff,
                        au.is_superuser
                    FROM
                        auth_user au,
                        auth_user_groups aug,
                        auth_group ag
                    WHERE
                        au.id = aug.user_id AND
                        ag.id = aug.group_id AND  
                        ag.id IN("""+ars+")"
                else:
                    print('not staff')
            userlist_.execute(sql)
            allUser = userlist_.fetchall()
        
        user_res = []
        for rows in allUser:
            datas = {
                "id": rows[0],
                "username": rows[1],
                "name": rows[2] +" "+ rows[3],
                "email": rows[6],
                "isactive": rows[4],
                "last": rows[5],
                "group": rows[7]
            }
            user_res.append(datas)

        if request.user.is_superuser:
            with conn.cursor() as grouplist_:
                grouplist_.execute("SELECT * FROM auth_group ORDER BY name ASC")
                allGroup = grouplist_.fetchall()

            group_res = []
            for rows in allGroup:
                datas = {
                    "id" : rows[0],
                    "name" : rows[1],
                }
                group_res.append(datas)
        else:
            with conn.cursor() as grouplist_:
                grouplist_.execute("SELECT * FROM auth_group WHERE id IN("+ars+")ORDER BY name ASC")
                allGroup = grouplist_.fetchall()

            group_res = []
            for rows in allGroup:
                datas = {
                    "id" : rows[0],
                    "name" : rows[1],
                }
                group_res.append(datas)
        
        context = {
            'title' : 'user management',
            'data'  : user_res,
            'dataGroup' : group_res
        } 

        
        return render(request, self.template_name, context)

@api_view(('POST',))
def addUser(request):
    
    if request.method == 'POST':
        
        usernames   = request.POST.get('username')
        first_name  = request.POST.get('first_name')
        last_name   = request.POST.get('last_name')
        group       = request.POST.get('group')
        email       = request.POST.get('email')
        password    = request.POST.get('username')
        iduser      = request.POST.get('id')
        isStaff     = request.POST.get('isStaff')

        # arrGroup    = group.split("~")

        
        user = User()
        # print("ini stag")
        # print(isStaff)
        # user.save()
        try:
            if iduser:
                with conn.cursor() as cursor:
                    cursor.execute("UPDATE auth_user SET first_name = '"+first_name+"', last_name = '"+last_name+"', email = '"+email+"' WHERE id="+iduser)
                
                with conn.cursor() as curGroup:
                    curGroup.execute("DELETE FROM auth_user_groups WHERE user_id="+iduser)

                users = User.objects.get(username=usernames)
                
                insertgroups = Group.objects.get(name=group)
                insertgroups.user_set.add(users)

                data = {
                    "code" : 0,
                    "data" : "User telah diperbaharui.",
                    "info" : "success"
                }
            else:
                user = User.objects.get(username=usernames)
                # print(user)
                data = {
                    "code" : 4,
                    "data" : "username telah tersedia, silahkan coba lagi.",
                    "info" : "failed"
                }

        except User.DoesNotExist:
            # print()
            if isStaff == "true":
                user.is_staff = True
            else:
                user.is_staff = False
            user.username    = usernames
            user.first_name  = first_name
            user.last_name   = last_name
            user.email       = email
            user.password    = make_password(password)
            user.save()
            
            
            # add user to group
            userinsert   = User.objects.get(username=usernames)
            
            
            insertgroups = Group.objects.get(name=group)
            insertgroups.user_set.add(userinsert)

            data = {
                "code" : 0,
                "info" : "success",
                "data" : "user "+usernames+" berhasil ditambahkan"
            }
    
    # data = {'sukses':'sukses'}
    return Response(data)

@api_view(('POST',))
def listGroup(request):
    if request.method == "POST":

        # print(request.user.is_superuser)
        
        if request.user.is_superuser:
            groupList = Group.objects.all().order_by('name')
            # # print(groupList)

            group = []
            for g in groupList:
                group.append(g.name)
        else:
            for g in request.user.groups.all():
                userGroup = g.id
            
            arrayGroups = []
            arrayGroups.append(userGroup)
            isChild = isHaveChild(userGroup)

            if isChild == True:
                childgroup = getGroupChilds(userGroup)
                for z in childgroup['data']:
                    arrayGroups.append(z[0])
                
                ars = ",".join(str(v) for v in arrayGroups)
            
            groupList = Group.objects.all().order_by('name')
            # # print("ini gl")
            # # print(groupList)
        
            group = []
            for g in groupList:
                for i in arrayGroups:
                    if i == g.id:
                        group.append(g.name)
        # print(group)
        if len(group) > 0:
            data = {
                "info" : "success",
                "code" : 0,
                "data" : group
            }
        else:
            data = {
                "info" : "group tidak tersedia",
                "code" : 4,
                "data" : group
            }

    return Response(data)

@api_view(('POST',))
def deactivateUser(request):
    
    user = User()
    if request.method == "POST":
        ids     = request.POST.get('id')
        status  = request.POST.get('stat')

        isActive = loadUserStatus(ids)
        # # print(type(status)+status)
        # # print(type(True))
        # print(status == 'True')
        if status == 'True':
            sql = "f"
        else:
            sql = "t"

        with conn.cursor() as cursor:
            affected_row = cursor.execute("UPDATE auth_user SET is_active = '"+sql+"' WHERE id = "+ids)
        
        # print ("UPDATE auth_user SET is_active = '"+sql+"' WHERE id = "+ids)
        # print (affected_row)
        if affected_row:
            data = {
                "info" : "success",
                "code" : 0,
                "data" : "User berhasil dinonaktifkan"
            }
        else:
            data = {
                "info" : "success",
                "code" : 0,
                "data" : "User berhasil dinonaktifkan"
            }

    return Response(data)


def loadUserStatus(id):

    with conn.cursor() as cursor:
        cursor.execute("SELECT is_active from auth_user WHERE id="+id+"")
        resData = cursor.fetchall()

    for i in resData:
        isActive = i[0]
    
    return isActive

@api_view(('POST',))
def loadUserbyId(request):
    if request.method == "POST":
        id = request.POST.get('id')

        # print(id)
        with conn.cursor() as cursor:
            cursor.execute("SELECT * FROM auth_user WHERE id ="+id)
            result = cursor.fetchall()

        group_res = loadGroupById(id)
        user_res  = []
        for rows in result:
            datas = {
                "id"       : rows[0],
                "username" : rows[4],
                "firstname": rows[5],
                "lastname" : rows[6],
                "email"    : rows[7],
                "isactive" : rows[9],
                "last"     : rows[2],
                "group"    : group_res,
                "is_staff" : rows[9]
            }
            user_res.append(datas)

        data = {
            "info" : "success",
            "code" : 0,
            "data" : datas,
        }
        

    return Response(data)

def loadGroupById(iduser):

    with conn.cursor() as cursor:
        cursor.execute("select ag.name from auth_group ag, auth_user_groups aug WHERE aug.user_id = "+iduser+" AND ag.id = aug.group_id")
        result = cursor.fetchall()

    # print(result)
    resGroup = []
    for i in result:
        resGroup.append(i[0])
    
    # print(resGroup)
    return resGroup

@api_view(('POST',))
def addGroup(request):
    
    if request.method == "POST":
        name    = request.POST.get('name')
        idgroup = request.POST.get('id')
        parent  = request.POST.get('parent')
        
        if parent != None:
            for s in Group.objects.all():
                if s.name == parent:
                    userGroup = s.id
        else:
            for g in request.user.groups.all():
                userGroup = g.id 
        # # print(userGroup)
        try:
            if idgroup:
                with conn.cursor() as cursor:
                    cursor.execute("SELECT have_parent FROM auth_group WHERE id="+str(idgroup))
                    result = cursor.fetchall()
                
                for row in result:
                    isParent = row[0]
                
                # print("ini parent ya gan")
                # print(len(result))
                if isParent == None:
                    with conn.cursor() as cursor:
                        cursor.execute("UPDATE auth_group SET name = '"+name+"' WHERE id="+str(idgroup))
                        result = cursor.rowcount
                else:
                    with conn.cursor() as cursor:
                        cursor.execute("UPDATE auth_group SET name = '"+name+"', have_parent = '"+str(userGroup)+"' WHERE id="+str(idgroup))
                        result = cursor.rowcount

                
                if result > 0:
                    data = {
                        "code" : 0,
                        "data" : "Group telah diperbaharui.",
                        "info" : "success"
                    }
                else:
                    data = {
                        "code" : 4,
                        "data" : "Group gagal diperbaharui, silahkan coba beberapa saat lagi",
                        "info" : "failed"
                    }
            else:
                group = Group.objects.get(name=name)
                data  = {
                    "code" : 4,
                    "data" : "Group telah tersedia dalam database.",
                    "info" : "failed"
                }

        except Group.DoesNotExist:
            
            with conn.cursor() as cursor:
                cursor.execute("INSERT INTO auth_group(name, have_parent) VALUES ('"+name+"', "+str(userGroup)+")")
                result = cursor.rowcount
            if result > 0:
                data = {
                    "code" : 0,
                    "info" : "success",
                    "data" : "Group berhasil ditambahkan"
                }
            else:
                data = {
                    "code" : 4,
                    "info" : "failed",
                    "data" : "Group gagal ditambahkan"
                }

    return Response(data)

@api_view(('POST',))
def deleteGroup(request):

    if request.method == "POST":
        id = request.POST.get('id')

        with conn.cursor() as cursor:
            cursor.execute("DELETE FROM auth_group WHERE id="+id)
            result = cursor.rowcount

        if result > 0:
            data = {
                "code" : 0,
                "info" : "success",
                "data" : "data berhasil dihapus"
            }
        else:
            data = {
                "code" : 4,
                "info" : "success",
                "data" : "data berhasil dihapus"
            }
        

    return Response(data)

def getGroupChilds(groupid):
    with conn.cursor() as cursor:
        cursor.execute("SELECT id FROM auth_group WHERE have_parent="+str(groupid))
        # print("query")
        # print("SELECT id FROM auth_group WHERE have_parent="+str(groupid))
        result = cursor.fetchall()
        # print("lastrow")
        # print(result[0])


        resultArray = []
        for i in result:
            resultArray.append(i)
            id = i
        
        vasrt = len(result)
        id    = id[0]
        # print(vasrt)
        # print("ini id")
        # print(id)

        while vasrt != 0:
            cursor.execute("SELECT id FROM auth_group WHERE have_parent="+str(id))
            resd = cursor.fetchall()

            # print("q res")
            # print(len(resd))
            for z in resd:
                resultArray.append(z)
                id = z[0]
                
            vasrt = len(resd)
        
        data = {
            'data' : resultArray
        }
        # print(data)
        
    return data

def isHaveChild(id):
    result = False

    with conn.cursor() as cursor:
        cursor.execute("SELECT * FROM auth_group WHERE have_parent ="+str(id))
        data = cursor.fetchall()

    if len(data) > 0:
        result = True
    
    return result

@api_view(('POST',))
def loadGroupByGroupId(request):
    if request.method == "POST":
        idgroup = request.POST.get('id')
        with conn.cursor() as cursor:
            sql = "SELECT * FROM auth_group WHERE id="+idgroup
            cursor.execute(sql)
            result = cursor.fetchall()
        
        
        arrdata = []
        for row in result:
            idparent = row[3]
            arrdata.append(row)
            
        
        if len(arrdata) > 0:

            if idparent != None:
                with conn.cursor() as cursor:
                    sql = "SELECT name FROM auth_group WHERE id = "+str(idparent)
                    cursor.execute(sql)
                    res = cursor.fetchall()
                
                arrname = []
                for x in res:
                    arrname.append(x[0])
            else:
                arrname = []
            

            data = {
                "code": 0,
                "info": "success",
                "data": arrname ,
                
            }
        else:
            data = {
                "code": 4,
                "info": "failed",
                "data": "group tidak memiliki parent" 
            }
    return Response(data)
