# -*- coding: utf-8 -*-

'''
Author: your name
Date: 2021-07-20 19:04:27
LastEditTime: 2021-07-20 20:45:12
LastEditors: Please set LastEditors
Description: In User Settings Edit
FilePath: \evm-store\tools\database_migration.py
'''

import sqlite3
import uuid

source_conn = sqlite3.connect('/mnt/d/projects/scriptiot/evm-store/tools/app-store.db')
source_cur = source_conn.cursor()

target_conn = sqlite3.connect('/mnt/d/projects/scriptiot/evm-store/tools/evue-store.db')
target_cur = source_conn.cursor()

# 更新user表
opts = [
    [2, 39],
    [3, 40],
    [4, 41]
]

sqls = [
    "update evm_store_annex set create_by = {a} where create_by = {b};",
    "update evm_store_annex set update_by = {a} where update_by = {b};",
    "update evm_store_app_logs set create_by = {a} where create_by = {b};",
    "update evm_store_build_logs set create_by = {a} where create_by = {b};",
    "update evm_store_build_logs set update_by = {a} where update_by = {b};",
    "update evm_store_device set create_by = {a} where create_by = {b};",
    "update evm_store_device set update_by = {a} where update_by = {b};",
    "update evm_store_login_logs set create_by = {a} where create_by = {b};",
]

for s in sqls:
    for o in opts:
        string = s.format(a=o[0], b=o[1])
        source_cur.execute(string)
source_conn.commit()

# 先插入user表
source_cur.execute('SELECT account, username, password, email, phone, create_at, create_by, update_at, update_by FROM evm_store_user')
sql = "insert evm_user (id, uuid, account, username, password, email, phone, create_at, create_by, update_at, update_byis_delete, is_delete) values ({a}, {b}, {c}, {d}, {e}, {f}, {g}, {h}, {i}, {j}, {k}, 0);"
res = source_cur.fetchall()
i = 0
for line in res:
    i += 1
    sql.format(a=i, b=uuid.uuid1().hex, c=line[0], d=line[1], e=line[2], f=line[3], g=line[4], h=line[5], i=line[6], j=line[7], k=line[8])
    target_cur.execute(string)
target_conn.commit()

# login logs
source_cur.execute('SELECT id, username, ip, address, create_at, create_by, remarks FROM evm_store_login_logs')
res = source_cur.fetchall()
sql = "insert into evm_login (id, uuid, user, login_at, user_agent, ip, geo_location, operator, create_at, create_by, update_at, update_by, is_delete) values ({a}, {b}, {c}, {d}, {e}, {f}, {g}, {h}, {i}, {j}, {k}, {l}, 0);"
for line in res:
    sql.format(a=line[0], b=uuid.uuid1().hex, c=line[5], d=line[6], e="", f=line[2], g=line[3], h="", i=line[4], j=line[5], k=line[4], l=line[5])
    target_cur.execute(string)
target_conn.commit()

# app
source_cur.execute('SELECT id, app_name, app_version, app_url, category, create_by, remarks FROM evm_store_apps')
res = source_cur.fetchall()

target_conn.commit()
target_conn.close()

source_conn.commit()
source_conn.close()