"""
* Version: 0.5.0

* Purposes:
    * Module `labs_core.stateless_functions` provides stateless functions for https://labs.tidb.io, it serves as the backend routines for RESTful APIs.
"""

import boto3, json, time, datetime, subprocess, importlib.resources, random, mysql.connector, re, hashlib, string
from datetime import timedelta
from io import StringIO
from botocore.exceptions import ClientError
import labs.labs_core.server_scope as server_scope
from mysql.connector.errorcode import (
    ER_ACCESS_DENIED_ERROR,
    ER_BAD_DB_ERROR,
    ER_DUP_UNIQUE
)


di = server_scope.get_database_connection_info()


def _generate_credit_voucher_code():
    random_chars = ''.join(random.choices(string.ascii_uppercase + string.digits, k=25))
    formatted_string = '-'.join(random_chars[i:i+5] for i in range(0, 25, 5))
    return formatted_string


def generate_credits_vouchers(email: str, redeemable_days: int, sfdc_campaign_code: str, credits_per_voucher: int, voucher_count: int, voucher_category: str, customer_name: str) -> list:
    if credits_per_voucher > 600:
        return None
    if redeemable_days < 1:
        return None
    if voucher_count > 200:
        return None
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    admin_role_query = "SELECT 1 FROM auth_user WHERE email = %s AND is_staff = 1"
    role_check_cursor = conn.cursor()
    role_check_cursor.execute(
        admin_role_query,
        (email,),
    )
    for x, in role_check_cursor:
        create_voucher_stmt = "INSERT INTO credit_vouchers (unique_code, credits, valid_before, marketing_code, category, customer_organization) VALUES (%s, %s, NOW() + INTERVAL '%s' DAY, %s, %s, %s)"
        cursor = conn.cursor()
        generated_vouchers = []
        for _ in range(voucher_count):
            try:
                vc = _generate_credit_voucher_code()
                cursor.execute(
                    create_voucher_stmt,
                    (vc, credits_per_voucher, redeemable_days, sfdc_campaign_code, voucher_category, customer_name),
                )
                generated_vouchers += [{
                    "voucher_code": vc,
                    "credits": credits_per_voucher,
                    "valid_before":  (datetime.datetime.now() + timedelta(days=redeemable_days)).strftime("%d/%m/%Y"),
                    "campaign_code": sfdc_campaign_code
                }]
            except mysql.connector.Error as err:
                print(err)
                vc2 = _generate_credit_voucher_code()
                cursor.execute(
                    create_voucher_stmt,
                    (vc2, credits_per_voucher, redeemable_days, sfdc_campaign_code, voucher_category, customer_name),
                )
                generated_vouchers += [{
                    "voucher_code": vc2,
                    "credits": credits_per_voucher,
                    "valid_before":  (datetime.datetime.now() + timedelta(days=redeemable_days)).strftime("%d/%m/%Y"),
                    "campaign_code": sfdc_campaign_code
                }]
        conn.commit()
        conn.close()
        log_activity(email, "generate_credits_vouchers_x_"+str(voucher_count)+"_for_"+sfdc_campaign_code+"_"+voucher_category, "N/A", "N/A", "N/A")
        return generated_vouchers
    conn.close()
    return None

def get_user_unique_tag(user_email: str) -> str:
    # TODO: we need to use a more robust way to generate unique tag, like user_id, or user_email_hash
    # AWS's stack name only accept [a-zA-Z][-a-zA-Z0-9]*, but there are many special characters in user email
    core_string = user_email.strip().lower()
    #pattern = r"^\d+@"
    pattern = r"^\d+[\w.-]*@"
    if re.match(pattern, core_string):
        return "x"+core_string.replace("@","-").replace(".","-").replace("_","-")
    return core_string.replace("@","-").replace(".","-").replace("_","-")


def record_lab_start(email: str, lab_code: str):
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    try:
        record = "INSERT INTO lab_usage_history (email, lab_code, start_count, terminate_count) VALUES (%s, %s, %s, %s)"
        cursor.execute(
            record,
            (email,lab_code,1,0,),
        )
        conn.commit()
        conn.close()
    except mysql.connector.Error as err:
        record = "UPDATE lab_usage_history SET start_count = start_count + 1 WHERE email = %s AND lab_code = %s"
        cursor.execute(
            record,
            (email,lab_code,),
        )
        conn.commit()
        conn.close()


def record_credits_usage(email: str, credits_after: int, credits_before: int, action_name: str) -> bool:
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    try:
        cursor = conn.cursor()
        record = "INSERT INTO credits_usage_history (email, credits_after, credits_before, action_name) VALUES (%s, %s, %s, %s)"
        cursor.execute(
            record,
            (email,credits_after,credits_before,action_name,),
        )
        conn.commit()
        conn.close()
    except mysql.connector.Error as err:
        print("Error during logging:", err.errno,err.msg)
        conn.rollback()
        conn.close()
        return False
    return True    


def record_event_voucher_usage(email: str, voucher_code: str) -> str:
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    try:
        cursor = conn.cursor()
        record = "INSERT INTO event_voucher_usage_history (email, voucher_unique_code, redeem_date) VALUES (%s, %s, NOW())"
        cursor.execute(
            record,
            (email,voucher_code,),
        )
        conn.commit()
        conn.close()
    except mysql.connector.Error as err:
        print("Error during logging:", err.errno,err.msg)
        conn.rollback()
        conn.close()
        return False
    return True  


def debug_activity(content: str) -> bool:
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    try:
        cursor = conn.cursor()
        record = "INSERT INTO debug_logs (content) VALUES (%s)"
        cursor.execute(
            record,
            (content,),
        )
        conn.commit()
        conn.close()
    except mysql.connector.Error as err:
        print("Error during logging:", err.errno,err.msg)
        conn.rollback()
        conn.close()
        return False
    return True


def log_activity(email: str, action_name: str, lab_code: str, region_name: str, tidb_version = "N/A") -> bool:
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    try:
        cursor = conn.cursor()
        record = "INSERT INTO lab_logs (email, lab_code, region_name, action_name, action_time, tidb_version) VALUES (%s, %s, %s, %s, NOW(), %s)"
        cursor.execute(
            record,
            (email, lab_code, region_name, action_name, tidb_version,),
        )
        if action_name == "stop_lab":
            record_cursor = conn.cursor()
            record = "UPDATE lab_usage_history SET terminate_count = terminate_count +1 WHERE email = %s AND lab_code = %s"
            record_cursor.execute(
                record,
                (email, lab_code,),
            )
        conn.commit()
        conn.close()
    except mysql.connector.Error as err:
        print("Error during logging:", err.errno,err.msg)
        conn.rollback()
        conn.close()
        return False
    return True


def _mark_active_focus_beacon(user_email: str, lab_code: str, region_name: str, tidb_version: str) -> None:
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    query = "UPDATE auth_user SET last_lab_code = %s, last_region_name = %s, last_tidb_version = %s, updated_at = NOW() WHERE email = %s"
    cursor.execute(
        query,
        (lab_code, region_name, tidb_version, user_email,),
    )
    conn.commit()
    conn.close()
    

def _clear_active_focus_beacon(user_email: str) -> None:
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    query = "UPDATE auth_user SET last_lab_code = NULL, last_region_name = NULL, last_tidb_version = NULL, updated_at = NOW() WHERE email = %s"
    cursor.execute(
        query,
        (user_email,),
    )
    conn.commit()
    conn.close()


def get_aws_client(service_name: str, region_code: str):

    session = boto3.session.Session()
    client = session.client(service_name=service_name, region_name=region_code)

    return client
    

def _get_node_instance_ids(
    user_unique_tag: str, node_role: str, nodes_per_user: int, region_code: str
) -> list:
    node_count_target = nodes_per_user
    ec2_client = get_aws_client("ec2", region_code)
    instance_ids = []
    response = ec2_client.describe_instances(
        Filters=[
            {
                "Name": "tag:Name",
                "Values": [
                    node_role,
                ],
            },
            {
                "Name": "instance-state-name",
                "Values": [
                    "running",
                ],
            },
            {
                "Name": "tag:trainer",
                "Values": [
                    user_unique_tag,
                ],
            },
        ],
        DryRun=False,
        MaxResults=500,
    )
    node_count_actual = 0
    for reservation in response["Reservations"]:
        for instances in reservation["Instances"]:
            node_count_actual += 1
            instance_ids += [instances["InstanceId"]]
    while "NextToken" in response and response["NextToken"] != None:
        print("Fetching EC2 instances information with the next token.")
        nt = response["NextToken"]
        response = ec2_client.describe_instances(
            Filters=[
                {
                    "Name": "tag:Name",
                    "Values": [
                        node_role,
                    ],
                },
                {
                    "Name": "instance-state-name",
                    "Values": [
                        "running",
                    ],
                },
                {
                    "Name": "tag:trainer",
                    "Values": [
                        user_unique_tag,
                    ],
                },
            ],
            DryRun=False,
            MaxResults=500,
            NextToken=nt,
        )
        for reservation in response["Reservations"]:
            for instances in reservation["Instances"]:
                node_count_actual += 1
                instance_ids += [instances["InstanceId"]]
    print(node_role + " node(s).")
    if node_count_actual != node_count_target:
        print(
            "ERROR: Total available "
            + node_role
            + " nodes count is "
            + str(node_count_actual)
            + " instead of "
            + str(node_count_target)
            + "."
        )
        print("SOLUTION: Please wait and retry later or contact PE team.")
    return instance_ids


def _assign_nodes_to_user(instance_ids: list, node_role: str, region_code: str):
    ec2_client = get_aws_client("ec2", region_code)
    student_node_idx = 1
    for node_instance_id in instance_ids:
        print(
            "Tagging " + node_instance_id + " with " + node_role + str(student_node_idx)
        )
        ec2_client.create_tags(
            DryRun=False,
            Resources=[
                node_instance_id,
            ],
            Tags=[
                {
                    "Key": "role",
                    "Value": node_role + str(student_node_idx),
                },
            ],
        )
        student_node_idx += 1
    print("Tagging " + node_role + " nodes completed.")


def _tag_instances_for_user(user_unique_tag: str, lab_code: str, region_code: str):
    lab_metadata = get_lab_metadata(lab_code)
    monitor_count = lab_metadata["monitor_count"]
    if monitor_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "monitor", monitor_count, region_code),
            "monitor",
            region_code,
        )

    pd_count = lab_metadata["pd_count"]
    if pd_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "pd", pd_count, region_code),
            "pd",
            region_code,
        )
    az1monitor_count = lab_metadata["az1monitor_count"]
    if az1monitor_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "az1monitor", az1monitor_count, region_code),
            "az1monitor",
            region_code,
        )
    az2monitor_count = lab_metadata["az2monitor_count"]
    if az2monitor_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "az2monitor", az2monitor_count, region_code),
            "az2monitor",
            region_code,
        )
    az1pd_count = lab_metadata["az1pd_count"]
    if az1pd_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "az1pd", az1pd_count, region_code),
            "az1pd",
            region_code,
        )
    az2pd_count = lab_metadata["az2pd_count"]
    if az2pd_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "az2pd", az2pd_count, region_code),
            "az2pd",
            region_code,
        )
    tikv_count = lab_metadata["tikv_count"]
    if tikv_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "kv", tikv_count, region_code),
            "kv",
            region_code,
        )
    az1tikv_count = lab_metadata["az1tikv_count"]
    if az1tikv_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "az1kv", az1tikv_count, region_code),
            "az1kv",
            region_code,
        )
    az2tikv_count = lab_metadata["az2tikv_count"]
    if az2tikv_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "az2kv", az2tikv_count, region_code),
            "az2kv",
            region_code,
        )
    tidb_count = lab_metadata["tidb_count"]
    if tidb_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "db", tidb_count, region_code),
            "db",
            region_code,
        )
    az1tidb_count = lab_metadata["az1tidb_count"]
    if az1tidb_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "az1db", az1tidb_count, region_code),
            "az1db",
            region_code,
        )
    az2tidb_count = lab_metadata["az2tidb_count"]
    if az2tidb_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "az2db", az2tidb_count, region_code),
            "az2db",
            region_code,
        )
    tiflash_count = lab_metadata["tiflash_count"]
    if tiflash_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "tiflash", tiflash_count, region_code),
            "tiflash",
            region_code,
        )
    mysql_count = lab_metadata["mysql_count"]
    if mysql_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "mysql", mysql_count, region_code),
            "mysql",
            region_code,
        )
    tiproxy_count = lab_metadata["tiproxy_count"]
    if tiproxy_count > 0:
        _assign_nodes_to_user(
            _get_node_instance_ids(user_unique_tag, "tiproxy", tiproxy_count, region_code),
            "tiproxy",
            region_code,
        )


def _checkout_serverless_branch(
    db_username: str,
    db_password: str,
    db_host: str,
    db_port: str,
    db_name: str,
    region_name: str,
    user_unique_tag: str,
) -> dict:
    conn = get_database_connection(db_username, db_password, db_host, db_port, db_name)
    cursor1 = conn.cursor()
    cursor2 = conn.cursor()
    query_candidate_branch = "SELECT branch_cluster_id, cluster_name, cluster_host, cluster_port, admin_name, admin_password, in_use, main_cluster_id FROM serverless_branch_clusters WHERE in_use = 'N' AND region_name = %s AND branch_cluster_id != 'N/A' AND admin_name != 'N/A' LIMIT 1 FOR UPDATE"
    dml_checkout_branch = "UPDATE serverless_branch_clusters SET in_use = 'Y', trainer_tag = %s, check_out_time = NOW() WHERE branch_cluster_id = %s"
    result = None
    conn.start_transaction(isolation_level="READ COMMITTED")
    try:
        cursor1.execute(
            query_candidate_branch,
            (region_name.strip(),),
        )
        for (
            branch_cluster_id,
            cluster_name,
            cluster_host,
            cluster_port,
            admin_name,
            admin_password,
            in_use,
            main_cluster_id,
        ) in cursor1:
            if cluster_name == "N/A":
                result = {
                    "status": 3,
                    "status_desc": "ERROR_NO_SERVERLESS_BRANCH_IN_AVAIL_POOL",
                }
                conn.rollback()
            else:
                cursor2.execute(
                    dml_checkout_branch,
                    (
                        user_unique_tag,
                        branch_cluster_id,
                    ),
                )
                result = {
                    "status": 0,
                    "serverless_branch_info": {
                        "branch_cluster_id": str(branch_cluster_id),
                        "cluster_name": cluster_name,
                        "cluster_host": cluster_host,
                        "cluster_port": str(cluster_port),
                        "admin_name": admin_name,
                        "admin_password": admin_password,
                        "main_cluster_id": str(main_cluster_id),
                    },
                }
                conn.commit()
    except mysql.connector.Error as error:
        result = {
            "status": 2,
            "status_desc": "ERROR_GET_METADATA",
        }
        conn.rollback()
    conn.close()
    return result


def run_script(command: list) -> dict:
    try:
        result = subprocess.run(command, check=True, capture_output=True, text=True)
        debug_activity(str(result.returncode))
        debug_activity(str(result.stderr))
        return {
            "error": result.stderr,
            "return_code": result.returncode,
        }
    except subprocess.CalledProcessError as e:
        print(f"Command failed with error: {e}")
        print(f"Error Output: {e.stderr}")
        debug_activity(f"Command failed with error: {e}")
        debug_activity(f"Error Output: {e.stderr}")
        return {
            "error": e.stderr,
            "return_code": e.returncode,
        }


def get_script_output(command: list) -> str:
    result = subprocess.run(command, capture_output=True, text=True)
    return result.stdout


def get_database_connection(
    db_username: str, db_password: str, db_host: str, db_port: str, db_name: str
):
    conn = mysql.connector.connect(
        database=db_name,
        host=db_host,
        port=int(db_port),
        user=db_username,
        password=db_password,
    )
    return conn


def get_assigned_serverless_branch(
    db_username: str,
    db_password: str,
    db_host: str,
    db_port: str,
    db_name: str,
    region_name: str,
    user_unique_tag: str,
) -> dict:
    """_summary_

    Args:ot
        db_username (str): database username
        db_password (str): database user password
        db_host (str): database host endpoint
        db_port (str): database port
        db_name (str): database schema name
        region_name (str): region name in the serverless pool
        user_unique_tag (str): the user unique tag used to identify the user

    Returns:
        dict: error or the serverless branch connection information
    """
    conn = get_database_connection(db_username, db_password, db_host, db_port, db_name)
    cursor = conn.cursor()
    query_branch_assigned_to_user = "SELECT branch_cluster_id, cluster_name, cluster_host, cluster_port, admin_name, admin_password, in_use, main_cluster_id FROM serverless_branch_clusters WHERE in_use = 'Y' AND trainer_tag = %s AND region_name = %s AND branch_cluster_id != 'N/A' AND admin_name != 'N/A'"
    result = {
        "status": 4,
        "status_desc": "ERROR_NO_SERVERLESS_BRANCH_FOUND",
    }
    try:
        cursor.execute(
            query_branch_assigned_to_user,
            (user_unique_tag.strip(), region_name.strip()),
        )
        for (
            branch_cluster_id,
            cluster_name,
            cluster_host,
            cluster_port,
            admin_name,
            admin_password,
            in_use,
            main_cluster_id,
        ) in cursor:
            if cluster_name == "N/A":
                result = {
                    "status": 1,
                    "status_desc": "ERROR_NO_SERVERLESS_BRANCH_AT_HAND",
                }
            else:
                result = {
                    "status": 0,
                    "serverless_branch_info": {
                        "branch_cluster_id": str(branch_cluster_id),
                        "cluster_name": cluster_name,
                        "cluster_host": cluster_host,
                        "cluster_port": str(cluster_port),
                        "admin_name": admin_name,
                        "admin_password": admin_password,
                        "main_cluster_id": str(main_cluster_id),
                    },
                }
    except mysql.connector.Error as error:
        result = {
            "status": 2,
            "status_desc": "ERROR_GET_METADATA",
        }
        conn.close()
    conn.close()
    return result


def get_serverless_branch_for_user(
    db_username: str,
    db_password: str,
    db_host: str,
    db_port: str,
    db_name: str,
    region_name: str,
    user_unique_tag: str,
) -> str:

    serverless_branch_info = get_assigned_serverless_branch(
        db_username,
        db_password,
        db_host,
        db_port,
        db_name,
        region_name,
        user_unique_tag,
    )
    if serverless_branch_info["status"] != 0:
        serverless_branch_info = _checkout_serverless_branch(
            db_username,
            db_password,
            db_host,
            db_port,
            db_name,
            region_name,
            user_unique_tag,
        )
    return serverless_branch_info


def get_lab_all() -> dict:
    with importlib.resources.open_text("labs.labs_core", "labs_metadata.json") as f:
        data = f.read()
    lab_metadata_all = json.load(StringIO(data))
    return lab_metadata_all
    
    
def get_exam_all() -> dict:
    with importlib.resources.open_text("labs.labs_core", "exams_metadata.json") as f:
        data = f.read()
    exam_metadata_all = json.load(StringIO(data))
    return exam_metadata_all


def get_exam_names_all() -> list:
    all_exams = get_exam_all()
    return [e["name"] for e in all_exams.values()]


def get_lab_metadata(lab_code: str) -> dict:
    """Return the metadata for a particular lab from the `labs_core.labs_metadata.json` metadata file.

    Args:
        lab_code (str): The lab code of the specific lab, such as `demo_001`, `demo_005`.

    Returns:
        dict: The metadata about the specific lab. Check `labs_metadata.json` for the details.
    """
    with importlib.resources.open_text("labs.labs_core", "labs_metadata.json") as f:
        data = f.read()
    lab_metadata = json.load(StringIO(data))[lab_code]
    return lab_metadata


def get_lab_metadata_by_group_name(lab_group_name: str) -> dict:
    with importlib.resources.open_text("labs.labs_core", "labs_metadata.json") as f:
        data = f.read()
    labs_metadata = json.load(StringIO(data))
    for lab_code, lab_metadata in labs_metadata.items():
        if lab_metadata["group_name"] == lab_group_name:
            return lab_metadata
    return None


def get_exam_metadata(exam_code: str) -> dict:
    with importlib.resources.open_text("labs.labs_core", "exams_metadata.json") as f:
        data = f.read()
    exam_metadata = json.load(StringIO(data))[exam_code]
    return exam_metadata


def get_exam_name_by_id(exam_id: str) -> str:
    exams = list(get_exam_all().values())
    for e in exams:
        if exam_id in e["exam_id_list"]:
            return e["name"]


def get_language_metadata(language_code: str) -> dict:
    with importlib.resources.open_text("labs.labs_core", "languages_metadata.json") as f:
        data = f.read()
    language_metadata = json.load(StringIO(data))[language_code]
    return language_metadata


def get_region_metadata(region_name: str) -> dict:
    with importlib.resources.open_text("labs.labs_core", "regions_metadata.json") as f:
        data = f.read()
    region_metadata = json.load(StringIO(data))[region_name]
    return region_metadata


def get_user_cfn_stack_in_region(user_email: str, region_name: str) -> list:
    user_unique_tag = get_user_unique_tag(user_email)
    lab_region = get_region_metadata(region_name)
    cfn_client = get_aws_client("cloudformation", lab_region["region-code"])
    cfn_stack_names = []
    cfn_stacks = []
    response = cfn_client.list_stacks(
        StackStatusFilter=['CREATE_COMPLETE','CREATE_IN_PROGRESS']
    )
    for stack in response["StackSummaries"]:
        stack_name = stack["StackName"]
        if stack_name.startswith(user_unique_tag+"-demo-env"):
            cfn_stack_names += [stack_name]
            cfn_stacks += [stack]
    while "NextToken" in response and response["NextToken"] != None:
        nt = response["NextToken"]
        response = cfn_client.list_stacks(
            StackStatusFilter=['CREATE_COMPLETE','CREATE_IN_PROGRESS'],
            NextToken=nt,
        )
        for stack in response["StackSummaries"]:
            stack_name = stack["StackName"]
            if stack_name.startswith(user_unique_tag+"-demo-env"):
                cfn_stack_names += [stack_name]
                cfn_stacks += [stack]
    return None if len(cfn_stacks) == 0 else cfn_stacks


def get_active_focus_beacon(user_email: str) -> dict:
    """_summary_

    Args:
        user_email (str): user email

    Returns:
        dict: the information about the REAL active lab environment for the user
    """
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    cursor2 = conn.cursor()
    cache_query = "SELECT last_lab_code, last_region_name, last_tidb_version FROM auth_user WHERE email = %s"
    # Check from cache
    print("Check from cache.")
    cursor.execute(
        cache_query,
        (user_email,),
    )
    for db_last_lab_code, db_last_region_name, db_last_tidb_version in cursor:
        if db_last_lab_code != None and db_last_region_name != None: # Potential active lab found from the cache
            result = get_user_cfn_stack_in_region(user_email, db_last_region_name)
            if result: # Real active lab found (CREATED or CREATING, not both)
                lab_metadata = get_lab_metadata(db_last_lab_code)
                ui_wait_seconds = lab_metadata["ui_duration_before_lab_ready_seconds"]
                lab_start_time_query = "SELECT action_time, TIMESTAMPDIFF(SECOND, action_time, NOW()), TIMESTAMPDIFF(SECOND, NOW(), action_time + INTERVAL '2' HOUR) FROM lab_logs WHERE email = %s and lab_code = %s and action_name = 'start_lab' ORDER BY action_time DESC LIMIT 1"
                cursor2.execute(
                    lab_start_time_query,
                    (user_email, db_last_lab_code,),
                )
                for db_action_time, db_ui_wait_elapsed_seconds, db_lab_remaining_seconds in cursor2:  
                    conn.close()
                    return {
                        "lab_code": db_last_lab_code,
                        "region_name": db_last_region_name,
                        "tidb_version": db_last_tidb_version,
                        "lab_ready_remaining_seconds": ui_wait_seconds - db_ui_wait_elapsed_seconds,
                        "lab_start_time": db_action_time,
                        "lab_remaining_seconds": db_lab_remaining_seconds,
                        "cfn_stack_names": result
                    }
    # Double check from log
    print("Check from log.")
    log_query = "SELECT lab_code, region_name, tidb_version FROM lab_logs WHERE email = %s AND action_name = 'start_lab' ORDER BY action_time DESC LIMIT 1"
    cursor.execute(
        log_query,
        (user_email,),
    )
    for log_lab_code, log_region_name, log_tidb_version in cursor:
        if log_lab_code != None and log_region_name != None: # Last started lab found from the log
            result = get_user_cfn_stack_in_region(user_email, log_region_name)
            if result: # Real active lab found (CREATED or CREATING, not both)
                lab_metadata = get_lab_metadata(log_lab_code)
                ui_wait_seconds = lab_metadata["ui_duration_before_lab_ready_seconds"]
                lab_start_time_query = "SELECT action_time, TIMESTAMPDIFF(SECOND, action_time, NOW()), TIMESTAMPDIFF(SECOND, NOW(), action_time + INTERVAL '2' HOUR) FROM lab_logs WHERE email = %s and lab_code = %s and action_name = 'start_lab' ORDER BY action_time DESC LIMIT 1"
                cursor2.execute(
                    lab_start_time_query,
                    (user_email, log_lab_code,),
                )
                for db_action_time, db_ui_wait_elapsed_seconds, db_lab_remaining_seconds in cursor2:  
                    conn.close()
                    _mark_active_focus_beacon(user_email, log_lab_code, log_region_name, log_tidb_version) # Mark the lost
                    return {
                        "lab_code": log_lab_code,
                        "region_name": log_region_name,
                        "tidb_version": log_tidb_version,
                        "lab_ready_remaining_seconds": ui_wait_seconds - db_ui_wait_elapsed_seconds,
                        "lab_start_time": db_action_time,
                        "lab_remaining_seconds": db_lab_remaining_seconds,
                        "cfn_stack_names": result
                    }
    conn.close()
    _clear_active_focus_beacon(user_email)
    return None 


def terminate_lab_async(
    user_email: str,
    lab_code: str,
    region_name: str
) -> dict:
    """Return the results of the lab termination operations.

    Args:
        user_email (str): The registration email for the user.
        lab_code (str): The lab code of the specific lab, such as `demo_001`, `demo_005`.
        region_name (str): The name of region, for example - oregon|tokyo|singapore|frankfurt.
        language_code (str): The code of language, for example - en|ja|zh.

    Returns:
        dict: The execution results of the lab termination operations.
    """
    user_unique_tag = get_user_unique_tag(user_email)
    lab_region = get_region_metadata(region_name)
    lab_metadata = get_lab_metadata(lab_code)
    log_activity(user_email, "stop_lab", lab_code, region_name, "N/A") # WAL
    _clear_active_focus_beacon(user_email) # No lab in the cache
    print(
        "terminating "
        + lab_code
    )
    user_stacks = get_user_cfn_stack_in_region(user_email, region_name)
    if not user_stacks:
        return None
    cfn = get_aws_client("cloudformation", lab_region["region-code"])
    lab_cleanup_script_response = None
    cloudformation_response = None
    if (
        lab_metadata["lab_cleanup_needed"]
        and lab_metadata["environment"] == "TiDB Cloud Serverless"
    ): # Lab cleanup procedure for Serverless labs
        # 1. Delete cfn stack.
        for stack in user_stacks:
            cloudformation_response = cfn.delete_stack(
                StackName=stack["StackName"],
            )
        lab_cleanup_script = lab_code + "-" + lab_metadata["lab_cleanup_script"]
        lab_cleanup_script_path = importlib.resources.files("labs.labs_core.scripts").joinpath(lab_cleanup_script).as_posix()
        response = get_assigned_serverless_branch(
            di["username"],
            di["password"],
            di["host"],
            di["port"],
            di["dbname"],
            lab_region["region-name"],
            user_unique_tag,
        )
        # 2. Run cleanup script.
        server_keys = server_scope.get_server_keys()
        lab_cleanup_script_response = get_script_output(
            [
                "/bin/bash",
                lab_cleanup_script_path,
                user_unique_tag,
                lab_region["region-code"],
                lab_region["region-name"],
                response["serverless_branch_info"]["main_cluster_id"],
                response["serverless_branch_info"]["cluster_name"],
                server_keys["cloud-serverless-pool-public-key"],
                server_keys["cloud-serverless-pool-private-key"],
            ]
        )
        print(lab_cleanup_script_response)
    elif (
        lab_metadata["lab_cleanup_needed"]
        and lab_metadata["environment"] == "TiDB Self-Managed"
    ): # Lab cleanup procedure for Self-Managed labs
        # 1. Run cleanup script.
        lab_cleanup_script = lab_code + "-" + lab_metadata["lab_cleanup_script"]
        lab_cleanup_script_path = importlib.resources.files("labs.labs_core.scripts").joinpath(lab_cleanup_script).as_posix()
        lab_cleanup_script_response = run_script(
            [
                "/bin/bash",
                lab_cleanup_script_path,
                user_unique_tag,
                lab_region["region-code"],
                lab_region["region-name"],
            ]
        )
        time.sleep(1)
        # 2. Delete cfn stack.
        for stack in user_stacks:
            cloudformation_response = cfn.delete_stack(
                StackName=stack["StackName"],
            )
    elif (
        not lab_metadata["lab_cleanup_needed"]
        and lab_metadata["environment"] == "TiDB Self-Managed"
    ): # Lab destroyer for Self-Managed labs
        # 1. Delete cfn stack.
        for stack in user_stacks:
            cloudformation_response = cfn.delete_stack(
                StackName=stack["StackName"],
            )
    ret = {
        "lab_cleanup_script_response": lab_cleanup_script_response,
        "cloudformation_response": cloudformation_response,
    }
    return ret


def check_user_lab_start_permission(user_email: str, lab_group_name: str) -> dict:
    """_summary_

    Args:
        user_email (str): user email
        lab_group_name (str): the lab group name of the lab as per the lab metadata

    Returns:
        dict: the result information with return code and suggestion
    """
    if user_email.strip().lower().endswith("@pingcap.com"):
        return {"return_code": 0, "consume_start_count": 0, "lab_group_name": lab_group_name}
    else:
        lab_metadata = get_lab_metadata_by_group_name(lab_group_name)
        conn = get_database_connection(
            di["username"],
            di["password"],
            di["host"],
            di["port"],
            di["dbname"])
        cursor = conn.cursor()
        check_rights = "SELECT lab_group_name, start_count_remains, start_begin_date, start_end_date FROM lab_user_permissions WHERE email = %s and lab_group_name = %s"
        cursor.execute(
            check_rights,
            (user_email, lab_group_name,),
        )
        for db_lab_group_name, db_start_count_remains, db_start_begin_date, db_start_end_date in cursor:
            if db_start_begin_date != None and db_start_end_date != None and db_start_begin_date <= datetime.datetime.now() <= db_start_end_date:
                conn.close()
                return {"return_code": 0, "consume_start_count": 0, "lab_group_name": db_lab_group_name}
            elif db_start_count_remains > 0:
                conn.close()
                return {"return_code": 0, "consume_start_count": 1, "lab_group_name": db_lab_group_name}
        if lab_metadata["credit_redeemable"]:
            conn.close()
            return {"return_code": 255, "suggestion": "Redeem needed.", "lab_group_name": lab_group_name}
        elif not lab_metadata["credit_redeemable"]:
            conn.close()
            return {"return_code": 254, "suggestion": "Go to the recommended URL.", "lab_group_name": lab_group_name}


def consume_lab_start_count(user_email: str, lab_group_name: str, count: int) -> bool:
    """_summary_

    Args:
        user_email (str): user email
        lab_group_name (str): the lab group name of the lab as per the lab metadata
        count (int): the count of lab start remains counter to consume

    Returns:
        dict: _description_
    """
    if user_email and user_email.endswith("@pingcap.com"):
        return True
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    consume_start_count = "UPDATE lab_user_permissions SET start_count_remains = start_count_remains - %s, updated_at = NOW() WHERE email = %s and lab_group_name = %s"
    try:
        cursor.execute(
            consume_start_count,
            (count, user_email, lab_group_name,),
        )
        conn.commit()
        conn.close()
        log_activity(user_email, "consume_lab_start_count", lab_group_name, "N/A", "N/A")
        return True
    except mysql.connector.Error as err:
        print(err.errno,err.msg)
        conn.rollback()
        conn.close()
        return False


def start_lab_async(
    user_email: str,
    tidb_version: str,
    lab_code: str,
    region_name: str
) -> dict:
    user_unique_tag = get_user_unique_tag(user_email)
    lab_region = get_region_metadata(region_name)
    lab_metadata = get_lab_metadata(lab_code)
    cfn = get_aws_client("cloudformation", lab_region["region-code"])
    stack_name = user_unique_tag + "-demo-env"+hashlib.sha256(str(time.time()).encode()).hexdigest()[:3]
    log_activity(user_email, "start_lab", lab_code, region_name, tidb_version)
    _mark_active_focus_beacon(user_email, lab_code, region_name, tidb_version)
    with importlib.resources.open_text(
        "labs.labs_core.cloudformation", lab_code + ".json"
    ) as f:
        template_body = f.read()
        # 1. Create cloudformation stack.
    print(
        "creating "
        + lab_code
        + " and UI should wait for "
        + str(lab_metadata["ui_duration_before_lab_ready_seconds"])
        + " seconds."
    )
    random_az_idx = random.randint(0, 2)
    cloudformation_response = cfn.create_stack(
        StackName=stack_name,
        TemplateBody=template_body,
        Parameters=[
            {
                "ParameterKey": "OwnerName",
                "ParameterValue": user_unique_tag,
            },
            {
                "ParameterKey": "OwnerEmail",
                "ParameterValue": user_email,
            },
            {
                "ParameterKey": "AZIdx",
                "ParameterValue": str(random_az_idx),
            },
        ],
        Capabilities=["CAPABILITY_IAM"],
        OnFailure="DO_NOTHING",
    )
    wait_secs = lab_metadata["backend_wait_for_cloudformation_seconds"]
    print(
        lab_code + " cloudformation deployed, wait for " + str(wait_secs) + " seconds."
    )
    # Additional serverless lab preparation for lab environment.
    if lab_metadata["environment"] == "TiDB Cloud Serverless":
        result = get_serverless_branch_for_user(
            di["username"],
            di["password"],
            di["host"],
            di["port"],
            di["dbname"],
            lab_region["region-name"],
            user_unique_tag,
        )
        print(
            "assigning TiDB Cloud Serverless: "
            + result["serverless_branch_info"]["cluster_host"]
            + " with "
            + result["serverless_branch_info"]["admin_name"]
        )
    time.sleep(wait_secs)
    # 2. Tag instances for user.
    _tag_instances_for_user(user_unique_tag = user_unique_tag, lab_code = lab_code, region_code = lab_region["region-code"])
    # 3. Setup lab monitor/bastion machine.
    log_activity(user_email, "prepare_lab", lab_code, region_name, tidb_version)
    setup_script = lab_code + "-setup.sh"
    setup_script_path = importlib.resources.files("labs.labs_core.scripts").joinpath(setup_script).as_posix()
    print("executing:",setup_script,user_unique_tag,lab_region["region-code"],lab_region["region-name"])
    debug_activity("executing: "+setup_script+" "+user_unique_tag+" "+lab_region["region-code"]+" "+lab_region["region-name"])
    setup_script_response = run_script(
        [
            "/bin/bash",
            setup_script_path,
            user_unique_tag,
            lab_region["region-code"],
            lab_region["region-name"],
        ]
    )
    # 4. Provision TiDB cluster or not?
    provision_script_response = None
    if lab_metadata["prepare_tidb"]:
        print("provision a TiDB cluster.")
        provision_script = lab_code + "-" + lab_metadata["prepare_tidb_script"]
        provision_script_path = importlib.resources.files("labs.labs_core.scripts").joinpath(provision_script).as_posix()
        provision_script_response = run_script(
            [
                "/bin/bash",
                provision_script_path,
                user_unique_tag,
                lab_region["region-code"],
                lab_region["region-name"],
                tidb_version,
            ]
        )
    else:
        print("provision a TiDB cluster skipped.")
    ret = {
        "cloudformation_response": cloudformation_response,
        "setup_script_response": setup_script_response,
        "provision_script_response": provision_script_response,
    }
    record_lab_start(user_email,lab_code)
    return ret


def check_lab_resources_readiness(user_email: str) -> int:
    result = get_active_focus_beacon(user_email)
    if not result:
        return 1 # No active environment, please start a new one.
    elif result["lab_ready_remaining_seconds"] > 0:
        return 2 # Lab resources preparation is not completed yet.
    elif result["lab_ready_remaining_seconds"] <= 0:
        region_metadata = get_region_metadata(result["region_name"])
        cfn_client = get_aws_client("cloudformation", region_metadata["region-code"])
        cfn_stacks = result["cfn_stack_names"]
        for s in cfn_stacks:
            response = cfn_client.describe_stacks(
                StackName=s["StackName"]
            )
            for stack in response["Stacks"]:
                if stack["StackStatus"] == "CREATE_COMPLETE":
                    return 0
                if stack["StackStatus"] == "CREATE_IN_PROGRESS":
                    return 2
    return 3 # Unknown error.


def get_valid_lab_user(user_email: str) -> dict:
    """_summary_

    Args:
        user_email (str): Get user by email.

    Returns:
        dict: The merged user information.
    """
    std_email = user_email.strip().lower()
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    query = "SELECT id, name, email, organization, is_staff, language_code, last_lab_code, last_region_name, last_tidb_version FROM auth_user WHERE email = %s"
    cursor.execute(
        query,
        (std_email,),
    )
    for db_id, db_name, db_email, db_organization, db_is_staff, db_language_code, db_last_lab_code, db_last_region_name, db_last_tidb_version in cursor:
        conn.close()
        return {
            "name": db_name,
            "email": db_email,
            "organization": db_organization,
            "role": "learner" if not db_is_staff else "admin",
            "language_code": db_language_code,
            "last_lab_code": db_last_lab_code,
            "last_region_name": db_last_region_name,
            "last_tidb_version": db_last_tidb_version,
        }
    conn.close()
    return None


def complete_valid_lab_user_information(user_email: str, name: str, organization: str) -> dict:
    """_summary_

    Args:
        user_email (str): _description_
        name (str): _description_
        organization (str): _description_

    Returns:
        dict: _description_
    """
    std_email = user_email.strip().lower()
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    update = "UPDATE auth_user SET name = %s, organization = %s, updated_at = NOW() WHERE email = %s"
    try:
        cursor.execute(
            update,
            (name,organization,std_email),
        )
        conn.commit()
    except mysql.connector.Error as err:
        print(err.errno,err.msg)
        conn.rollback()
        conn.close()
        return None
    query = "SELECT id, name, email, organization, is_staff FROM auth_user WHERE email = %s"
    cursor.execute(
        query,
        (std_email,),
    )
    for db_id, db_name, db_email, db_organization, db_is_staff in cursor:
        conn.close()
        return {
            "name": db_name,
            "email": db_email,
            "organization": db_organization,
            "role": "learner" if not db_is_staff else "admin",
        }
    conn.close()
    return None


def redeem_code(user_email: str, redeem_code: str) -> int:
    pattern = r'^[A-Z0-9]{5}-[A-Z0-9]{5}-[A-Z0-9]{5}-[A-Z0-9]{5}-[A-Z0-9]{5}$'
    if re.match(pattern, redeem_code):
        return redeem_credits_by_using_credit_voucher(user_email, redeem_code)
    else:
        return redeem_lab_groups_by_using_event_voucher_v2(user_email, redeem_code)


def redeem_lab_groups_by_using_event_voucher_v2(user_email: str, event_voucher_unique_code: str) -> int:
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor_voucher_check = conn.cursor()
    cursor_rights_check = conn.cursor()
    cursor_rights_modify = conn.cursor()
    check_voucher = "SELECT lab_group_name, start_begin_date, start_end_date, CAST(NOW() AS DATE) FROM event_vouchers WHERE unique_code = %s"
    cursor_voucher_check.execute(
        check_voucher,
        (event_voucher_unique_code,),
    )
    for db_lab_group_name, db_start_begin_date, db_start_end_date, db_now in cursor_voucher_check:
        if db_start_end_date < db_now: # The voucher is expired. Two time windows are not overlapped.
            return -3 # Failed
        # For each real lab_group_name in the lab_group_name(s) fetched from the `check_voucher` query.
        for lgn in db_lab_group_name.split(","):
            lgn = lgn.strip()
            check_user_lab_right = "SELECT email FROM lab_user_permissions WHERE email = %s AND lab_group_name = %s"
            cursor_rights_check.execute(
                check_user_lab_right,
                (user_email, lgn,),
            )
            update_operation = False
            for db_email, in cursor_rights_check: # The voucher matches the lab_group_name.
                update_operation = True
                update_existing_permission = "UPDATE lab_user_permissions SET start_begin_date = %s - INTERVAL '1' DAY, start_end_date = %s + INTERVAL '1' DAY, updated_at = NOW() WHERE email = %s AND lab_group_name = %s"
                cursor_rights_modify.execute(
                    update_existing_permission,
                    (db_start_begin_date, db_start_end_date, db_email, lgn,),
                )
            if not update_operation:
                add_user_lab_rights = "INSERT INTO lab_user_permissions (email, lab_group_name, start_count_remains, start_begin_date, start_end_date) VALUES (%s, %s, %s,  %s - INTERVAL '1' DAY, %s + INTERVAL '1' DAY)"
                cursor_rights_modify.execute(
                    add_user_lab_rights,
                    (user_email, lgn, 0, db_start_begin_date, db_start_end_date,),
                )
        if record_event_voucher_usage(user_email,event_voucher_unique_code):
            conn.commit()
            return 0 # Success
        else:
            conn.rollback()
            conn.close()
            return -4 # User redeemed this event voucher before.
    conn.rollback()
    conn.close()
    return -2 # Failed


def redeem_lab_groups_by_using_event_voucher(user_email: str, event_voucher_unique_code: str) -> int:
    """_summary_
    Deprecated because of v2.
    """
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor_voucher_check = conn.cursor()
    cursor_rights_check = conn.cursor()
    cursor_rights_modify = conn.cursor()
    check_voucher = "SELECT lab_group_name, start_begin_date, start_end_date, CAST(CONCAT(start_end_date, ' 00:00:00') AS DATETIME), NOW() FROM event_vouchers WHERE unique_code = %s"
    cursor_voucher_check.execute(
        check_voucher,
        (event_voucher_unique_code,),
    )
    for db_lab_group_name, db_start_begin_date, db_start_end_date, db_start_end_datetime, db_now in cursor_voucher_check:
        if db_start_end_datetime < db_now: # The voucher is expired.
            return -3 # Failed
        check_user_lab_right = "SELECT email FROM lab_user_permissions WHERE email = %s AND lab_group_name = %s"
        cursor_rights_check.execute(
            check_user_lab_right,
            (user_email, db_lab_group_name,),
        )
        #if cursor_rights_check.rowcount > 0: # current_user has an existing lab_group permission.
        for db_email, in cursor_rights_check: # The voucher matches the lab_group_name.
            update_existing_permission = "UPDATE lab_user_permissions SET start_begin_date = %s, start_end_date = %s, updated_at = NOW() WHERE email = %s AND lab_group_name = %s"
            cursor_rights_modify.execute(
                update_existing_permission,
                (db_start_begin_date, db_start_end_date, db_email, db_lab_group_name,),
            )
            if record_event_voucher_usage(user_email,event_voucher_unique_code):
                conn.commit()
                conn.close()
                return 0 # Success
            else:
                conn.rollback()
                conn.close()
                return -4 # User redeemed this event voucher before.
        #else: # This is a new lab_group permission for the current_user.
        #cursor_rights_check.fetchall()
        add_user_lab_rights = "INSERT INTO lab_user_permissions (email, lab_group_name, start_count_remains, start_begin_date, start_end_date) VALUES (%s, %s, %s, %s, %s)"
        cursor_rights_modify.execute(
            add_user_lab_rights,
            (user_email, db_lab_group_name, 0, db_start_begin_date, db_start_end_date,),
        )
        if record_event_voucher_usage(user_email,event_voucher_unique_code):
            conn.commit()
            conn.close()
            return 0 # Success
    conn.rollback()
    conn.close()
    return -2 # Failed


def redeem_lab_by_spending_credits(user_email: str, lab_code: str) -> dict:
    lab_metadata = get_lab_metadata(lab_code)
    if lab_metadata["credit_redeemable"]:
        lab_credits = lab_metadata["credits"]
        check_user_credits = "SELECT credits FROM auth_user WHERE email = %s FOR UPDATE"
        conn = get_database_connection(
            di["username"],
            di["password"],
            di["host"],
            di["port"],
            di["dbname"])
        cursor_credit_check = conn.cursor()
        cursor_credit_consume = conn.cursor()
        cursor_rights_check = conn.cursor()
        try:
            cursor_credit_check.execute(
                check_user_credits,
                (user_email,),
            )
            for db_credits, in cursor_credit_check:
                if db_credits - lab_credits >= 0:
                    consume_user_credits = "UPDATE auth_user SET credits = credits - %s WHERE email = %s"
                    check_user_lab_right = "SELECT email, lab_group_name, start_count_remains FROM lab_user_permissions WHERE email = %s AND lab_group_name = %s"
                    add_new_user_lab_right = "INSERT INTO lab_user_permissions (email, lab_group_name, start_count_remains) VALUES (%s, %s, %s)"
                    increase_user_lab_start_count = "UPDATE lab_user_permissions SET start_count_remains = start_count_remains + 1, updated_at = NOW() WHERE email = %s AND lab_group_name = %s"
                    cursor_credit_consume.execute(
                        consume_user_credits,
                        (lab_credits, user_email,),
                    )
                    cursor_rights_check.execute(
                        check_user_lab_right,
                        (user_email, lab_metadata["group_name"],),
                    )
                    lab_rights_not_exists = True
                    for db_email, db_lab_group_name, db_start_count_remains in cursor_rights_check:
                        if db_email and db_lab_group_name:
                            cursor_credit_consume.execute(
                                increase_user_lab_start_count,
                                (user_email, lab_metadata["group_name"],),
                            )
                            lab_rights_not_exists = False
                    if lab_rights_not_exists:
                        cursor_credit_consume.execute(
                            add_new_user_lab_right,
                            (user_email, lab_metadata["group_name"], 1,),
                        )
                    if record_credits_usage(user_email, db_credits - lab_credits, db_credits, 'redeem lab: '+lab_metadata["group_name"]):
                        conn.commit()
                        conn.close()                    
                        return {
                            "lab_code": lab_metadata["group_name"],
                            "lab_start_count_remains": db_start_count_remains + 1 if not lab_rights_not_exists else 1,
                            "account_credits_balance": db_credits - lab_credits,
                        }
        except mysql.connector.Error as error:
            print(error.errno,": ",error.msg)
        conn.rollback()
        conn.close()
    return None
    

def redeem_credits_by_using_credit_voucher(user_email: str, credit_voucher_unique_code: str):
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor_get_voucher_credits = conn.cursor()
    get_voucher_credits = "SELECT credits, unique_code FROM credit_vouchers WHERE unique_code = %s AND valid_before > NOW() AND used = 'N' FOR UPDATE"
    try:
        cursor_get_voucher_credits.execute(
            get_voucher_credits,
            (credit_voucher_unique_code,),
        )
        for db_credits, db_unique_code in cursor_get_voucher_credits:
            cursor_add_credits = conn.cursor()
            add_credits = "UPDATE auth_user SET credits = credits + %s WHERE email = %s"
            expire_voucher = "UPDATE credit_vouchers SET used = 'Y' WHERE unique_code = %s"
            cursor_add_credits.execute(
                add_credits,
                (db_credits, user_email,),
            )
            cursor_add_credits.execute(
                expire_voucher,
                (db_unique_code,),
            )
            cursor_record = conn.cursor()
            record = "INSERT INTO credit_voucher_usage_history (voucher_unique_code, email, redeem_date) VALUES (%s, %s, NOW())"
            cursor_record.execute(
                record,
                (db_unique_code, user_email,),
            )
            conn.commit()
            conn.close()
            return db_credits
    except mysql.connector.Error as error:
        conn.rollback()
    conn.close()
    return -1


def get_learner_info_by_gid(user_gid: str) -> dict:
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    query = "SELECT user_gid, login, user_id,  email, first_name, last_name, user_type, status, language, country, phone_number, company_name FROM global_lms_learners WHERE user_gid = %s"
    cursor.execute(
        query,
        (user_gid,),
    )
    for db_user_gid, db_login, db_user_id,  db_email, db_first_name, db_last_name, db_user_type, db_status, db_language, db_country, db_phone_number, db_company_name in cursor:
        conn.close()
        return {
            "user_gid": db_user_gid,
            "login": db_login,
            "user_id": db_user_id,
        }
    conn.close()
    return None


def get_learner_info_by_login(login: str) -> dict:
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    query = "SELECT user_gid, login, user_id,  email, first_name, last_name, user_type, status, language, country, phone_number, company_name FROM global_lms_learners WHERE login = %s"
    cursor.execute(
        query,
        (login,),
    )
    for db_user_gid, db_login, db_user_id,  db_email, db_first_name, db_last_name, db_user_type, db_status, db_language, db_country, db_phone_number, db_company_name in cursor:
        conn.close()
        return {
            "user_gid": db_user_gid,
            "login": db_login,
            "user_id": db_user_id,
        }
    conn.close()
    return {
        "error": {
            "type": "invalid_request_error",
            "message": "The requested user does not exist"
        }
    }


def get_learner_info_by_email_fname_and_lname(email: str, first_name: str, last_name: str) -> dict:
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    query = "SELECT user_gid, login, user_id,  email, first_name, last_name, user_type, status, language, country, phone_number, company_name FROM global_lms_learners WHERE email = %s and lower(first_name) = %s and lower(last_name) = %s"
    cursor.execute(
        query,
        (email,first_name.strip().lower(),last_name.strip().lower()),
    )
    for db_user_gid, db_login, db_user_id,  db_email, db_first_name, db_last_name, db_user_type, db_status, db_language, db_country, db_phone_number, db_company_name in cursor:
        conn.close()
        return {
            "user_gid": db_user_gid,
            "login": db_login,
            "user_id": db_user_id,
        }
    conn.close()
    return None


def get_learner_certificate_by_uid_and_cert_abbr_code(user_id, cert_abbr_code):
    try:
        exam_metadata = get_exam_metadata(cert_abbr_code)
    except KeyError as ex:
        return None
    if not exam_metadata:
        return None
    
    exam_id_list = exam_metadata["exam_id_list"]

    if not exam_id_list:
        return None

    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    query = "SELECT certificate_unique_identifier, status, exam_id FROM global_lms_learner_exams WHERE user_id = %s and exam_id IN (" + ",".join(exam_id_list) + ") LIMIT 1"
    cursor.execute(
        query,
        (user_id,),
    )
    for db_certificate_unique_identifier, db_status, db_exam_id in cursor:
        conn.close()
        return {
            "certificate_unique_identifier": db_certificate_unique_identifier.strip(),
            "status": db_status.strip(),
            "exam_course_id": db_exam_id
        }
    conn.close()
    return None 


def verify_certificate_by_id(certificate_unique_identifier: str):
    pattern = r'^[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}-[a-f0-9]{4}$'
    if not re.match(pattern, certificate_unique_identifier.strip()):
        return None
        
    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor = conn.cursor()
    query = "SELECT certificate_unique_identifier, user_id, certificate_name, complete_date FROM global_lms_learner_exams WHERE certificate_unique_identifier = %s"
    cursor.execute(
        query,
        (certificate_unique_identifier,),
    )
    for db_certificate_unique_identifier, db_user_id, db_certificate_name, db_complete_date in cursor:
        learner_cursor = conn.cursor()
        learner_query = "SELECT first_name, last_name FROM global_lms_learners WHERE user_id = %s"
        learner_cursor.execute(
            learner_query,
            (db_user_id,),
        )
        for db_first_name, db_last_name in learner_cursor:
            conn.close()
            return {
            "certificate_name": db_certificate_name,
            "certificate_unique_identifier": db_certificate_unique_identifier.strip(),
            "first_name": db_first_name,
            "last_name": db_last_name,
            "active_since": db_complete_date.date().strftime('%Y-%m-%d')
        }
    conn.close()
    return None 


def learner_exam_complete_handler(user_info: dict, course_id: str):
    debug_activity(str(user_info))
    """_summary_
    1. Merge learner into global_lms_learners.
    2. Merge completed exam into global_lms_learner_exams.
    """
        
    login = user_info["login"]
    first_name = user_info["first_name"]
    last_name = user_info["last_name"]
    email = user_info["email"]
    user_type = user_info["user_type"]
    user_id = user_info["id"]
    status = user_info["status"]
    language = user_info["language"]
    country = user_info["custom_field_1"]
    phone_number = user_info["custom_field_3"]
    company_name = user_info["custom_field_2"]

    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"

    conn = get_database_connection(
        di["username"],
        di["password"],
        di["host"],
        di["port"],
        di["dbname"])
    cursor_query = conn.cursor()
    cursor_update = conn.cursor()
    cursor_insert = conn.cursor()
    
    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_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,
            ),
        )
    conn.commit()
    log_activity(email, "learner_added_by_course_completion_event", "N/A", "N/A", "N/A")
    
    user_gid_query_statement = "SELECT user_gid, login FROM global_lms_learners WHERE user_id = %s"
    exam_query_statement = "SELECT login, certificate_unique_identifier FROM global_lms_learner_exams WHERE user_id = %s AND exam_id = %s"
    exam_update_statement = "UPDATE global_lms_learner_exams SET login = %s, complete_date = %s, certificate_unique_identifier = %s WHERE user_id = %s AND exam_id = %s"
    exam_insert_statement = "INSERT INTO global_lms_learner_exams (user_id, login, exam_id, exam_name, complete_date, status, certificate_unique_identifier, certificate_name) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"


    learner_cursor = conn.cursor()
    learner_cursor.execute(
        user_gid_query_statement,
        (user_id,),
    )
    for user_gid, db_login in learner_cursor:
        exam_certifications = user_info["certifications"]
        for ec in exam_certifications:
            if ec["course_id"] == course_id:
                exam_id = course_id
                exam_name = ec["course_name"]
                complete_date =  datetime.datetime.strptime(ec["issued_date"], "%d/%m/%Y")
                status = "Completed"
                certificate_unique_identifier = ec["unique_id"]
                certificate_name = get_exam_name_by_id(course_id)
                check_exam = conn.cursor()
                check_exam.execute(
                    exam_query_statement,
                    (user_id, exam_id,),
                )
                exam_not_exists = True
                for e_login, e_certificate_unique_identifier in check_exam:
                    exam_not_exists = False
                    if e_login == login and e_certificate_unique_identifier == certificate_unique_identifier:
                        None
                    else:
                        update_cert = conn.cursor()
                        update_cert.execute(
                            exam_update_statement,
                            (login, complete_date, certificate_unique_identifier, user_id, exam_id,),
                        )
                if exam_not_exists:
                    add_cert = conn.cursor()
                    add_cert.execute(
                        exam_insert_statement,
                        (user_id, login, exam_id, exam_name, complete_date, status, certificate_unique_identifier, certificate_name,),
                    )
    conn.commit()
    log_activity(email, exam_name +"_passed", "N/A", "N/A", "N/A")