import os
import psycopg2
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT


USER = "postgres"
PASSWORD = "Mz9A85vmP5wSxxeNbGBrjv86L9bJoX"
PORT = 5432
HOST = "10.0.0.17"
DATABASE = "unittest"

def call_postgresql(sql, flag=1):
    # "flag = 1表示获取多条数据 0表示获取单条数据"

    conn = psycopg2.connect(database=DATABASE, port=PORT,
                            host=HOST, user=USER, password=PASSWORD)
    cursor = conn.cursor()
    cursor.execute(sql)
    if flag:
        results = cursor.fetchall()
    else:
        results = None
    conn.commit()
    conn.close()
    return results

def exec_call_postgresql(lines:list):
    # "flag = 1表示获取多条数据 0表示获取单条数据"

    conn = psycopg2.connect(database=DATABASE, port=PORT,
                            host=HOST, user=USER, password=PASSWORD)
    cursor = conn.cursor()
    cursor.execute(sql)
    if flag:
        results = cursor.fetchall()
    else:
        results = None
    conn.commit()
    conn.close()
    return results

def get_key_from_casename(casename):
    if "多层" in casename:
        return "多层"
    elif "11F" in casename:
        return "小高11F"
    elif "18F_T3" in casename:
        return "小高18F_T3"
    elif "18F_T4" in casename:
        return "小高18F_T4"
    elif "叠拼" in casename:
        return "叠拼"
    else:
        return "多层"
# 第一步得到100个基础类型的预期容积率和覆盖率
# 根据类型+用例分组更新剩余大数据部分
sql = """select distinct(testcase_id),testcase_name,expect_far,expect_coverage,type from baseline_new_algorithm_result WHERE testcase_id NOT LIKE '%bigdata%'"""
results = call_postgresql(sql)
type_dict = {"多层":{},"小高11F":{},"小高18F_T3":{},"小高18F_T4":{},"叠拼":{}}
for result in results:
    casename = result[1]
    type_ = result[4]
    key = get_key_from_casename(casename)
    type_dict[key][type_] = [result[2],result[3]]
# print(type_dict)
# 获取大数据部分
sql = """select id,testcase_id,testcase_name,expect_far,expect_coverage,type from baseline_new_algorithm_result WHERE testcase_id LIKE '%bigdata%'"""
new_results = call_postgresql(sql)
conn = psycopg2.connect(database=DATABASE, port=PORT,
                            host=HOST, user=USER, password=PASSWORD)
cursor = conn.cursor()

for line in new_results:
    casename = line[2]
    key = get_key_from_casename(casename)
    far = type_dict[key][line[5]][0]
    coverage = type_dict[key][line[5]][1]
    update_sql = """update baseline_new_algorithm_result SET expect_coverage={},expect_far={} where id={}""".format(coverage,far,line[0])
    print(update_sql)
    cursor.execute(update_sql)
    conn.commit()
conn.close()