import table
import categorizer
import cx_Oracle
import json
import os

def clear(credentials):
    conn = cx_Oracle.connect(
        credentials["login"], credentials["password"], credentials["host"], encoding="UTF-8")
    cur = conn.cursor()
    cur.execute("drop table petrushin_test")

    cur.execute(f"""
    CREATE TABLE petrushin_test (
    id NUMBER GENERATED BY DEFAULT AS identity,
    name VARCHAR2(100),
    phone_number VARCHAR2(100),
    inn VARCHAR2(12),
    post_index VARCHAR2(6),
    email VARCHAR2(100),
	website VARCHAR2(100))
    """)

    cur.executemany(f"insert into petrushin_test(name, phone_number, inn, post_index, email) values (:1, :2, :3, :4, :5)", [
        ("Филимошин Роман Владимирович", "+7 (495) 913-09-65",
         "4025062831", None, "Mail@service-nalog.ru"),
        ("Мелешкин Михаил Федорович", None,
         "7707288107", "127381", "prodez@mail.ru"),
        ("Мурашов Сергей Борисович", "+7 (812) 492-20-58", "7814001461", None, None)
    ])
    conn.commit()

def main():
    path = '.\\credentials.json'

    if not os.path.isfile(".\\credentials.json"):
        print("Отсутствует файл конфигурации логина/пароля. Поместите файл credentials.json в папку со скриптом")
        return

    csv = r"C:\\oracle\\python_homework\\data.csv"

    credentials = json.load(open(path))

    cx_Oracle.init_oracle_client(credentials["client"])

    clear(credentials)

    c = categorizer.categorizer()

    csv_table = table.csv_table(csv)
    table_name = "petrushin_test"
    oracle_table = table.oracle_table(credentials["login"], credentials["password"], credentials["host"], table_name)

    oracle_table.categories = c.get_category(oracle_table.get_data())
    csv_table.categories = c.get_category(csv_table.get_data())


    print(csv_table.get_data())
    print("=======")
    print("Данные petrushin_test до обогащения")
    print(oracle_table.get_data())
    print("=======")
    print("Данные petrushin_test после обогащения")
    oracle_table.update_from_table(csv_table)
    print(oracle_table.get_data())

main()