import time, MySQLdb, random, configparser

from write_project_ini import write_pro_ini
from main_config import main_Config

def mysql_sampling(project, label_set, count):

#    config=configparser.ConfigParser()
#    config.read('main.ini')
#    mysql_id=config['settings']["id"]
#    mysql_pass=config['settings']["pass"]
    mysql_id, mysql_pass, projects, root_path, using_os = main_Config()

    sample_reset(project, mysql_id, mysql_pass)   #サンプリングカラムの初期化

    import_sampling(project, mysql_id, mysql_pass, label_set, count)

    update_ini(project)

def update_ini(project):
    write_pro_ini(project).main(
    sample="1",
    train_test="",
    discrimination="0"
    )   #iniファイルにプロジェクトを登録
    time.sleep(0.1)

def import_sampling(project, mysql_id, mysql_pass, label_set, count):
    time.sleep(0.2)
    for label in label_set:
        try:
            conn=MySQLdb.connect(db=project, user=mysql_id, passwd=mysql_pass, charset="utf8mb4")
            cursor=conn.cursor()

            sql="select sentence_id from text_table where label_name = '" + label + "'"

            cursor.execute(sql)
            nums=list(cursor.fetchall())

            time.sleep(0.2)
            conn.commit()
            cursor.close()
            conn.close()

            time.sleep(0.1)
            res=sampling(nums, count)

            time.sleep(0.1)
            insert_sampling(res, project, mysql_id, mysql_pass)

        except Exception as e:
            print(e)

def insert_sampling(res, project, mysql_id, mysql_pass):
    try:
        conn=MySQLdb.connect(db=project, user=mysql_id, passwd=mysql_pass, charset="utf8mb4")
        cursor=conn.cursor()

        sql="update text_table set sampling = %s where sentence_id = %s"
        cursor.executemany(sql,res)
        time.sleep(0.2)

        conn.commit()
        cursor.close()
        conn.close()

    except Exception as e:
        print(e)

def sampling(nums, count):
    sa_nums=random.sample(nums, count)#第二引数にサンプル数を記入
    sa_number=[]
    for sa_num in sa_nums:
        sa_num=sa_num[0]
        se=int("1")
        sa_num=(se,sa_num)
        sa_number.append(sa_num)
    return sa_number


def sample_reset(project, mysql_id, mysql_pass):
    try:
        conn=MySQLdb.connect(db=project, user=mysql_id, passwd=mysql_pass, charset="utf8mb4")
        cursor=conn.cursor()

        sql="update text_table set sampling = 0"
        cursor.execute(sql)
        time.sleep(0.1)

        sql="update text_table set disc_code = null"
        cursor.execute(sql)
        time.sleep(0.1)

        sql="update text_table set sentence_vector = null"
        cursor.execute(sql)
        time.sleep(0.1)

        sql="update text_table set train_test_data = null"
        cursor.execute(sql)
        time.sleep(0.1)

        sql="update text_table set k_means_label = null"
        cursor.execute(sql)
        time.sleep(0.1)

        conn.commit()
        cursor.close()
        conn.close()

    except Exception as e:
        print(e)


if __name__=="__main__":
    project="only_text"
    label_set= ['kaiyu', 'marine', 'ouji']
    count=1
    mysql_sampling(project, label_set, count)
