import os
import requests
import pymysql
import recyclebin

host = "tidb.e23bdccc.cb17aaf2.us-west-2.prod.aws.tidbcloud.com"
passwd = os.getenv('passwd')

stagingUrl = "https://us-west-2.staging.shared.aws.tidbcloud.com"
nightlyUrl = "https://us-west-2.nightly.shared.aws.tidbcloud.com"

def fetch_useless_projects( env ):
    projects = {}
    tenant_projects = {}

    if env == 'nightly':
        env = 'dev'
    dbName = env + '_central'

    db = pymysql.connect(host=host,
                         port=4000,
                         user='root',
                         password=passwd,
                         db=dbName,
                         charset='utf8'
                         )
    cursor = db.cursor()
    cursor.execute("select id,name,tenant_id from projects where status = 'active' and id != 1")
    results = cursor.fetchall()
    for row in results:
        id = row[0]
        name = row[1]
        tenantId = row[2]

        # 千万不能删除任何QA的project
        if 'QA' in name \
                or 'ApiAuto' in name \
                or 'AutoTest' in name:
            continue

        cursor.execute("select id,deleted_time,status from clusters where project_id={} "
                       " and status in ('created','initializing','preparing','launching','normal','scaling','upgrading')"
                       .format(id)
                       )
        results = cursor.fetchall()
        if len( results ) > 0:
            continue

        cursor.execute("select id,deleted_time,status from clusters where project_id={} "
                       "and created_at > NOW() - INTERVAL 2 DAY"
                       .format(id))
        results = cursor.fetchall()
        if len( results ) == 0:
            projects[ str(id) ] = name
            tenant_projects[ str(id) ] = tenantId

    return projects, tenant_projects

def delete_projects(tokens, env, tenantId, projectid):
    baseUrl = ""
    if env == 'nightly':
        baseUrl = nightlyUrl
    if env == 'staging':
        baseUrl = stagingUrl

    clusters = recyclebin.fetch_recycle_bin(env, tokens, tenantId, projectid)
    for cluster in clusters:
        recyclebin.delete_recycle_bin(
            env,
            tokens,
            tenantId,
            projectid,
            cluster
        )

    header = {'Authorization': tokens}
    print( "delete projects " + baseUrl + "/api/v1/orgs/{}/projects/{}".format( tenantId, projectid ))
    r = requests.delete(
        baseUrl + "/api/v1/orgs/{}/projects/{}".format( tenantId, projectid ),
        headers = header
    )
    print( r.text )

def find_project_user_emails(env, project):
    if env == 'nightly':
        env = 'dev'
    dbName = env + '_central'

    db = pymysql.connect(host=host,
                         port=4000,
                         user='root',
                         password=passwd,
                         db=dbName,
                         charset='utf8'
                         )
    cursor = db.cursor()
    cursor.execute("select user_id from user_projects where project_id = '{}'".
                   format(project))

    results = cursor.fetchall()
    if len(results) == 0:
        return None

    userArray = []
    for row in results:
        userArray.append(
            str(row[0])
        )

    cursor.execute("select email from users where id in ( {} ) and role= 'owner';".
                   format(
                        ",".join( userArray )
                        ))

    results = cursor.fetchall()
    if len(results) == 0:
        return None

    return results[0][0]

if "__main__" == __name__:
    # projects, tenant_projects = fetch_useless_projects("nightly")
    # print( '------------nightly----------' )
    # for name in projects.values():
    #     print(name)
    #
    # for id in projects.keys():
    #     print( id )
    # projects, tenant_projects = fetch_useless_projects("staging")
    # print( '-----------staging----------' )
    # for name in projects.values():
    #     print(name)
    #
    # for id in projects.keys():
    #     print( id )

    # --------------------
    # tokens = 'Bearer CLOUD9NjEH0v5l2AcYX832vBRTJgtYp5lbh-wf6v7qRx1GTWzvZ_0DJLehFk29gJP8OwgRDe_tRqOS-WFXsIl0OUW9w=='
    # delete_projects(
    #     tokens ,
    #     'staging',
    #     '1',
    #     '1369847559691367760'
    # )
    # ---------------------
    email = find_project_user_emails('staging', '1369847559691367564')
    print( email )