"""
* NOT USED
"""

import sys, mysql.connector, time, hashlib
from openpyxl import load_workbook
import labs.labs_core.stateless_functions as stateless

sheet_name = "Users"
login_col_idx = 0
first_name_col_idx = 1
last_name_col_idx = 2
email_col_idx = 3
user_type_col_idx = 4
user_id_col_idx = 5
status_col_idx = 7
language_col_idx = 9
country_col_idx = 11
phone_number_col_idx = 12
company_name_col_idx = 13

def _merge_tlms_learners_to_global_lms_learners_table(
    login,
    first_name,
    last_name,
    email,
    user_type,
    user_id,
    status,
    language,
    country,
    phone_number,
    company_name,
    cursor_query,
    cursor_insert,
    cursor_update,
):
    query_statement = "SELECT first_name, last_name, email, user_type, user_id, status, language, country, phone_number, company_name FROM global_lms_learners WHERE user_id = %s"
    insert_statement = "INSERT INTO global_lms_learners (user_gid, login, first_name, last_name, email, user_type, user_id, status, language, country, phone_number, company_name) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    update_statement = "UPDATE global_lms_learners SET first_name = %s, last_name = %s, email = %s, user_type = %s, login = %s, status = %s, language = %s, country = %s, phone_number = %s, company_name = %s WHERE user_id = %s"
    cursor_query.execute(
        query_statement,
        (user_id,),
    )
    not_exists = True
    for (
        db_first_name,
        db_last_name,
        db_email,
        db_user_type,
        db_login,
        db_status,
        db_language,
        db_country,
        db_phone_number,
        db_company_name,
    ) in cursor_query:
        not_exists = False
        if (
            db_first_name == first_name
            and db_last_name == last_name
            and db_email == email
            and db_user_type == user_type
            and db_login == login
            and db_status == status
            and db_language == language
            and db_country == country
            and db_phone_number == phone_number
            and db_company_name == company_name
        ):
            None
        else:
            cursor_update.execute(
                update_statement,
                (
                    first_name,
                    last_name,
                    email,
                    user_type,
                    login,
                    status,
                    language,
                    country,
                    phone_number,
                    company_name,
                    user_id,
                ),
            )
    if not_exists:
        cursor_insert.execute(
            insert_statement,
            (
                hashlib.sha256(str(time.time()).encode()).hexdigest()[:20],
                login,
                first_name,
                last_name,
                email,
                user_type,
                user_id,
                status,
                language,
                country,
                phone_number,
                company_name,
            ),
        )


def merge_tlms_learners_from_complete_export_xlsx(input_file: str):
  """_summary_

  Args:
      input_file (str): XLSX headers:
      Login,	Firstname,	Lastname,	Email,	User-type,	User ID,	Bio,	Status,	Deactivation-date,	Language,	Terms,	custom_field: Country, custom_field: Phone Number, custom_field: Company Name, custom_field: I agree to PingCAP's Privacy Policy,
  """
  conn = mysql.connector.connect(
      database=stateless.di["db_name"],
      host=stateless.di["host"],
      port=stateless.di["port"],
      user=stateless.di["username"],
      password=stateless.di["password"],
  )
  cursor_query = conn.cursor()
  cursor_insert = conn.cursor()
  cursor_update = conn.cursor()
  wb = load_workbook(input_file)
  c = 0
  try:
      for row in wb[sheet_name].rows:
          if c == 0:
              c += 1
              continue
          login = row[login_col_idx].value
          first_name = row[first_name_col_idx].value
          last_name = row[last_name_col_idx].value
          email = row[email_col_idx].value
          user_type = row[user_type_col_idx].value
          user_id = row[user_id_col_idx].value
          status = row[status_col_idx].value
          language = row[language_col_idx].value
          country = row[country_col_idx].value
          phone_number = row[phone_number_col_idx].value
          company_name = row[company_name_col_idx].value
          email = email.lower()
          print(email.strip() + " - " + str(login))
          _merge_tlms_learners_to_global_lms_learners_table(
              login,
              first_name,
              last_name,
              email,
              user_type,
              user_id,
              status,
              language,
              country,
              phone_number,
              company_name,
              cursor_query,
              cursor_insert,
              cursor_update,
          )
          conn.commit()
      conn.close()
  except TypeError as ex:
      print(str(ex))