import os
import sys
import json
import sqlite3

DATA_DIR = sys.path[0] + "/data"
CONFIG_FILE = sys.path[0] + "/sql_config.json"
OUTPUT_FILE = sys.path[0] + "/../data/table_info.json"


def read_json_file(file_path):
    f = open(file_path, encoding = "utf-8")
    content = f.read()
    f.close()
    return json.loads(content)


def write_json_file(file_path, obj):
    f = open(file_path, "w")
    content = f.write(json.dumps(obj, indent=1))
    f.close()
    return file_path

def run_cmd(cmd):
    return os.system(cmd)

def get_sqlite3_table(file_path, table):
    mydb = sqlite3.connect(file_path)
    cur = mydb.cursor()
    cur.execute("select name from sqlite_master where type='table'")
    return

def get_column_names(cur, table):
    column_info = cur.execute("select * from " + table + " limit 1")
    column_name_list = [i[0] for i in cur.description]
    return column_name_list

def gen_import_cmd(file_path, database):
    ret_list = []
    mydb = sqlite3.connect(file_path)
    cur = mydb.cursor()
    cur.execute("select name from sqlite_master where type='table'")
    tables = cur.fetchall()
    table_list = [item[0] for item in tables]
    for table in table_list:
        sql_cmd = "select sql from sqlite_master where name='" + table + "'"
        cur.execute(sql_cmd)
        for row in cur:
            sql = row[0].replace("NUMERIC", "INT(11)").replace("INTEGER", "INT(11)")
            break
        column_name_list = get_column_names(cur, table)
        item = {"database": database, "sql": sql, "table": table, "col_names": column_name_list}
        ret_list.append(item)
        pass
    return ret_list

#run_cmd("pip3 install sqlite3-to-mysql")


g = os.walk(DATA_DIR)

total_table_list = []

for path, dir_list, file_list in g:
    for file_name in file_list:
        if not file_name.endswith("sqlite"):
            continue
        str_list = file_name.split(".")
        if len(str_list) != 2:
            continue
        file_path = os.path.join(path, file_name)
        total_table_list.extend(gen_import_cmd(file_path, str_list[0].replace("-", "_")))
        pass
    pass

write_json_file(OUTPUT_FILE, total_table_list)