import json
import os

__dir__ = os.path.dirname(__file__)

with open(f"{__dir__}/users.csv") as f:
    data = f.readlines()

script = "DECLARE @inserted_row_id INT;\n"

for index, row in enumerate(data):
    if index == 0:
        continue
    
    parts = row.split(";")
    
    name, surname = parts[1].split(" ", 1)
    login = parts[2]
    password = parts[3]
    usertype = parts[7]

    script += f"INSERT INTO users (role,login,password,surname,name) VALUES ({usertype}, '{login}', '{password}', '{surname}', '{name}')\n"
    script += "SELECT @inserted_row_id = SCOPE_IDENTITY();\n"

    services = json.loads(parts[6][1:-1])
    
    for item in services:
        script += f"INSERT INTO employee_services (service_id, user_id) VALUES ((SELECT id FROM services WHERE code={item['code']}), @inserted_row_id);\n"
    
print(script)