__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
        """
    ),
]


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

    replacements = 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)

    with open(output_file, "w", encoding="utf-8") as f:
        f.write(text)

    print(f"Migration done: {len(replacements)} replacement(s) found.")


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}

    nodes = root.findall(".//birt:xml-property[@name='queryText']", ns)

    replacements = {}
    for node in nodes:
        if not node.text:
            continue

        node_sql = normalize_sql(node.text)

        for old_sql, new_sql in sql_replacements:
            if node_sql == normalize_sql(old_sql):
                replacements[node.text] = new_sql
                break

    return replacements


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])