# pip install MySQL-python
import pymysql

FULL_LINE="jdbc:mysql://77.234.220.70:3306/competencyjpa?serverTimezone=Europe/Moscow"
URL = "77.234.220.70"
LOGIN = "mastercjpa"
#TODO: it's so safe!!! XD
PASS = "TestPassword"
DB = 'competencyjpa'
ADDITIONS = 'serverTimezone=Europe/Moscow'


def main():
    print("starting")
    # getting first-&second- level skills amount
    conn = pymysql.connect(host=URL, user=LOGIN, password=PASS, db=DB)
    cursor = conn.cursor()

    firstLevelSkillsReq = 'SELECT ID, NAME FROM SKILL \
                         WHERE PARENT IS NULL'
    cursor.execute(firstLevelSkillsReq)

    firstLevelCnt = 0
    result = cursor.fetchone()
    while result is not None:
        #print(result)
        result = cursor.fetchone()
        firstLevelCnt += 1

    print('First level Skill amount is: ' + str(firstLevelCnt))

    secondLevelSkillsReq = 'SELECT ID FROM SKILL \
                             WHERE PARENT IN \
                              (SELECT ID FROM SKILL \
                                WHERE PARENT IS NULL)'
    cursor.execute(secondLevelSkillsReq)

    secondLevelCnt = 0
    result = cursor.fetchone()
    while result is not None:
        #print(result)
        result = cursor.fetchone()
        secondLevelCnt += 1

    print('Second level Skill amount is: ' + str(secondLevelCnt))

    # RESIDENTS WITH COMPETENCY AMOUNT
    residentsReq = 'SELECT ID FROM PROFILE \
                    WHERE ID IN \
                        (SELECT owner FROM COMPETENCY \
                            WHERE SKILL IN \
                                (SELECT ID FROM SKILL \
                                     WHERE PARENT IN \
                                      (SELECT ID FROM SKILL \
                                        WHERE PARENT IS NULL OR PARENT IN \
                                            (SELECT ID FROM SKILL \
                                                 WHERE PARENT IS NULL))))'
    cursor.execute(residentsReq)

    residentCnt = 0
    result = cursor.fetchone()
    while result is not None:
        #print(result)
        result = cursor.fetchone()
        residentCnt += 1

    print('resident cnt with 1 and 2 level group is: ' + str(residentCnt))

    cursor.execute(secondLevelSkillsReq)
    residents12 = cursor.fetchall()
    #print(residents12)

    def getStr(p):
        nds = '('
        for s in p:
            if s != p[0]:
                nds += ','
            nds += str(s[0])
        nds += ')'
        return nds

    def children(p):
        #tricky
        res = ()
        nds = getStr(p)
        nodes = 'SELECT ID FROM SKILL WHERE PARENT IN ' + str(nds)
        cursor.execute(nodes)
        nodes = cursor.fetchall()
        res += nodes

        for node in nodes:
                sql = 'SELECT ID FROM SKILL WHERE PARENT IN (' + str(node[0]) + ')'
                cursor.execute(sql)
                childrs = cursor.fetchall()
                if len(childrs) is 0:
                    return res

                res += children(childrs)

        return res

    def correctSplit(p):
        return p

    for resident in residents12:
        allSkillsIds = children((resident,))

        #drawing:
        sum = 0
        for skillId in getStr(allSkillsIds)[1:][:-1].split(","):
            amountQuer = 'SELECT ID FROM PROFILE \
                            WHERE ID IN (SELECT owner FROM COMPETENCY \
                                WHERE SKILL IN (' + skillId + '))'
            cursor.execute(amountQuer)
            amount = len(getStr(cursor.fetchall())[1:][:-1].split(","))
            sum += amount
            #print('Skill: ' + skillId + ' : people : ' + str(amount))

        print(str(resident[0]) + ":" + str(sum))

    conn.close()


main()