__copyright__ = "Copyright © by tracetronic GmbH, Dresden"
__license__ = "This file is distributed as an integral part of tracetronic's software products " \
              "and may only be used in connection with and pursuant to the terms and conditions " \
              "of a valid tracetronic software product license."

import xml.etree.ElementTree as ET
import re
import sys

SQL_REPLACEMENTS = [
    (
        """
        select CONSTANT_KEY,
        TCE_CONSTANTS.VALUE
        from TCE_CONSTANTS
        where TESTCASEEXECUTIONDTO_ID = ?
        order by CONSTANT_KEY
        """,
        """
        select CONSTANT_KEY,
        CONSTANT_VALUE AS "VALUE"
        from TCE_CONSTANTS
        where TESTCASEEXECUTIONDTO_ID = ?
        order by CONSTANT_KEY
        """
    ),
    (
        """
        select ATTRIBUTE_KEY,
	    GROUP_CONCAT(TCE_ATTRIBUTES.VALUE order by TCE_ATTRIBUTES.VALUE separator ', ') as CONCAT_VALUES
        from TCE_ATTRIBUTES
        where TESTCASEEXECUTIONDTO_ID = ?
        group by ATTRIBUTE_KEY
        order by ATTRIBUTE_KEY
        """,
        """
        select ATTRIBUTE_KEY,
	    GROUP_CONCAT(ATTRIBUTE_VALUE order by ATTRIBUTE_VALUE separator ', ') as CONCAT_VALUES
        from TCE_ATTRIBUTES
        where TESTCASEEXECUTIONDTO_ID = ?
        group by ATTRIBUTE_KEY
        order by ATTRIBUTE_KEY
        """
    ),
    (
        """
        select TESTENVCFG_KEY,
	        TCE_ENVIRONMENT_CONFIG.VALUE,
	        CATEGORY,
	        DESCRIPTION
        from TCE_ENVIRONMENT_CONFIG
        where TESTCASEEXECUTIONDTO_ID = ?
        order by TESTENVCFG_KEY
        """,
        """
        select TESTENVCFG_KEY,
	    TESTENVCFG_VALUE AS "VALUE",
	    CATEGORY,
	    DESCRIPTION
        from TCE_ENVIRONMENT_CONFIG
        where TESTCASEEXECUTIONDTO_ID = ?
        """
    ),
    (
        """
        select NAME,
	        TCE_PARAMETERS.VALUE,
	        DESCRIPTION,
	        DIRECTION
        from TCE_PARAMETERS
        where TESTCASEEXECUTIONDTO_ID = ?
        order by NAME
        """,
        """
        select NAME,
	        PARAMETER_VALUE AS "VALUE",
	        DESCRIPTION,
	        DIRECTION
        from TCE_PARAMETERS
        where TESTCASEEXECUTIONDTO_ID = ?
        order by NAME
        """
    ),
    (
        """
        select concat_ws(' | ', CF.LABEL, CFM.PUBLICKEY) as LABEL,
	        DATA.VALUE
        from CUSTOMFIELD CF
        left join CUSTOMFIELD_MAPPINGS CFM on CF.ID=CFM.CUSTOMFIELDDTO_ID
	        and CF.ID=CFM.CUSTOMFIELDDTO_ID
        inner join (
	        select 'ATTRIBUTE' as TYPE,
		        ATTRIBUTE_KEY AS KEY,
		        VALUE
	        from TCE_ATTRIBUTES
	        where TESTCASEEXECUTIONDTO_ID = ?
	        union all
	        select 'CONSTANT' as TYPE,
		        CONSTANT_KEY AS KEY,
		        VALUE
	        from TCE_CONSTANTS
	        where TESTCASEEXECUTIONDTO_ID = ?
        ) as DATA on CFM.INTERNALKEY = DATA.KEY
	        and CFM.TYPE = DATA.TYPE
        order by CF.LABEL,
	        CFM.PUBLICKEY
        """,
        """
        select concat_ws(' | ', CF.LABEL, CFM.PUBLICKEY) as LABEL,
	    DATA.LABEL_VALUE AS "VALUE"
        from CUSTOMFIELD CF
        left join CUSTOMFIELD_MAPPINGS CFM on CF.ID=CFM.CUSTOMFIELDDTO_ID
	        and CF.ID=CFM.CUSTOMFIELDDTO_ID
        inner join (
            select 'ATTRIBUTE' as TYPE,
                ATTRIBUTE_KEY AS CFM_KEY,
                ATTRIBUTE_VALUE AS LABEL_VALUE
            from TCE_ATTRIBUTES
            where TESTCASEEXECUTIONDTO_ID = ?
            union all
	        select 'CONSTANT' as TYPE,
		        CONSTANT_KEY AS CFM_KEY,
		        CONSTANT_VALUE AS LABEL_VALUE
	        from TCE_CONSTANTS
	        where TESTCASEEXECUTIONDTO_ID = ?
        ) as DATA on CFM.INTERNALKEY = DATA.CFM_KEY
	        and CFM.TYPE = DATA.TYPE
        order by CF.LABEL,
	        CFM.PUBLICKEY
        """
    ),
    (
        """
        select NUMBERINGSTRING,
            case when TYPE = 'NODE' then true else false end as IS_NODE,
            LABEL,
            TESTCASECOUNT,
            TESTCASEEXECUTIONCOUNT,
            VERDICTNAME
        from COVERAGETREE
        where TYPE is not 'ROOT'
        order by ELEMENTORDER
        """,
        """
        select NUMBERINGSTRING,
            case when TYPE = 'NODE' then true else false end as IS_NODE,
            LABEL,
            TESTCASECOUNT,
            TESTCASEEXECUTIONCOUNT,
            VERDICTNAME
        from COVERAGETREE
        where TYPE != 'ROOT'
        order by ELEMENTORDER
        """
    ),
]

RENAMED_TABLES = [
    "tce_attributes",
    "tce_parameters",
    "tce_constants",
    "tce_environment_config",
    "scopemap"
]

# ANSI sequences for text coloring
RED = "\033[91m"
YELLOW = "\033[93m"
RESET = "\033[0m"

def yellow(text):
    return YELLOW + text + RESET

def red(text):
    return RED + text + RESET

def main(input_file, output_file):
    print(f"Migrate BIRT template {input_file} to {output_file}")

    replacements, unmigrated_datasets = construct_replacements(input_file, SQL_REPLACEMENTS)

    with open(input_file, "r", encoding="utf-8") as f:
        text = f.read()

    for old, new in replacements.items():
        text = text.replace(old, new)

    if unmigrated_datasets:
        print(f"{len(replacements)} queries could be migrated.")
        print(yellow(f"There are {len(unmigrated_datasets)} data sets using tables which were modified in the new DataSourceSpec and could not be migrated automatically:"))
        for dataset_name in unmigrated_datasets:
            print(f"  - {dataset_name}")
        print("Please review these sql statements and migrate if necessary.")
    else:
        print(f"{len(replacements)} sql statements could be replaced automatically.")

    print(yellow("ATTENTION: This script cannot migrate breaking changes caused by the new H2 database version!"))
    print(red("!!! Please check the generated PDFs thoroughly for correctness and completeness after migration. !!!"))
    input("(Press enter to confirm and see output)")
    
    with open(output_file, "w", encoding="utf-8") as f:
        f.write(text)


def construct_replacements(input_file, sql_replacements):
    tree = ET.parse(input_file)
    root = tree.getroot()

    default_ns = root.tag.split("}")[0].strip("{")
    ns = {"birt": default_ns}

    # only oda-data-sets contain sql queries
    datasets = root.findall(".//birt:oda-data-set", ns)

    replacements = {}
    unmigrated_datasets = []

    for dataset in datasets:
        query_node = dataset.find("birt:xml-property[@name='queryText']", ns)
        if query_node is None or not query_node.text:
            continue

        node_sql = normalize_sql(query_node.text)

        for old_sql, new_sql in sql_replacements:
            if node_sql == normalize_sql(old_sql):
                replacements[query_node.text] = new_sql
                break
        else:
            # sql not in mapping; but it might not need any changes -> warn only if it referes a table with renamed columns
            if any(re.search(rf"\b{table}\b", node_sql) for table in RENAMED_TABLES):
                dataset_name = dataset.get("name", "<unnamed dataset>")
                unmigrated_datasets.append(dataset_name)

    return replacements, unmigrated_datasets


def normalize_sql(text: str) -> str:
    return re.sub(r"\s+", " ", text).strip().lower()


if __name__ == "__main__":
    if len(sys.argv) != 3:
        print("Usage: python migrate_birt_template.py input_birt_template.xml output_birt_template.xml")
        sys.exit(1)

    main(sys.argv[1], sys.argv[2])