Migrate Custom Reports

"""Build a Blitz Report import XML: python build_xml.py <spec.py> <out.xml>

spec.py defines CATEGORY and either REPORTS (a list of report dicts - the category deliverable, every
report plus all their assignments in one file) or the keys of a single report at top level.
A report dict: NAME, DESCRIPTION, SQL (path), GUID, ASSIGNMENTS (the converter's _assignments.xml), PARAMS,
SHORT_NAME (the short name of the program the report replaces, the view name for a custom view) and optional PROGRAM
(its user-visible name), optional NUMBER_FORMAT (default #,##0.00;[Red]-#,##0.00; 'General' prints numbers as the original prints them),
optional DB_PACKAGE (the APPS package holding beforereport/afterreport),
optional VALIDATE_FROM_LIST=True for a target build dated before 18-Oct-2026 (Build Date on the converter workbook's Parameters
sheet), which validates a list only when the file says so and is ignored by later builds,
optional TEMPLATES and DEFAULT_TEMPLATE (the name of the template every user gets without choosing one).

PARAMS lists one dict per PARAMETERS_ROW, in order:
  name, anchor, sql       sql is the SQL_TEXT; omit it for a :bind anchor
  type                    Date, Char, Number, LOV (shared Blitz LOV), LOV Oracle (value set), LOV custom
  lov, lov_query          LOV / LOV Oracle name, LOV custom query
  fbd                     LOV custom only: filter before display (a list too long to download, e.g. all suppliers)
  required, default       REQUIRED=Y, DEFAULT_VALUE
  description             the run screen tooltip (US translation row)
  matching                MATCHING_VALUE
  hidden                  negative DISPLAY_SEQUENCE
  display_sequence        explicit DISPLAY_SEQUENCE, to show a parameter elsewhere on the run screen without moving its
                          SORT_ORDER (the import's upsert key)
  sub                     a further anchor of the same parameter: no display sequence, type, LOV, default or required
  lexical                 anchor '&name' placed where the fragment goes; sql is a select returning one line per fragment
                          row (the engine substitutes all rows); hidden + a constant default so the value is always set
Shared-LOV fallback payloads (LOV_GUID, FILTER_BEFORE_DISPLAY_DSP, LOV_QUERY_DSP) are copied from the shipped content
(the repository's content/r12/reports, or the folder of report XMLs named by the BLITZ_CONTENT_DIR environment variable).
"""
import collections
import glob
import html
import os
import re
import runpy
import sys

REPO = os.path.join(os.environ.get("BLITZ_CONTENT_DIR") or os.path.join(os.path.dirname(__file__), "..", "..", "..", "content", "r12", "reports"), "")
SQL_FUNCTIONS = {"abs", "add_months", "bitand", "cast", "ceil", "chr", "coalesce", "decode", "greatest", "initcap", "instr", "last_day", "least", "length",
                 "lower", "lpad", "mod", "months_between", "next_day", "nvl", "nvl2", "power", "regexp_replace", "regexp_substr", "replace", "round", "rtrim",
                 "sign", "substr", "substrb", "to_char", "to_date", "to_number", "translate", "trim", "trunc", "upper", "userenv"}


def shared_lov(name):
    for p in glob.glob(REPO + "*.xml"):
        s = open(p, encoding="utf-8").read()
        m = re.search(r"<LOV_NAME>" + re.escape(name) + r"</LOV_NAME>\s*<LOV_GUID>(\w+)</LOV_GUID>\s*(?:<FREE_TEXT>\w</FREE_TEXT>\s*)?(<FILTER_BEFORE_DISPLAY_DSP>\w</FILTER_BEFORE_DISPLAY_DSP>\s*)?<LOV_QUERY_DSP>(.*?)</LOV_QUERY_DSP>", s, re.S)
        if m:
            return m.group(1), (m.group(2) or "").strip(), m.group(3)
    raise SystemExit("shared LOV not found: " + name)


def e(t):
    return html.escape(t, quote=False).replace("'", "&apos;")


def param_rows(rep):
    rows, visible = [], 0
    for i, p in enumerate(rep["PARAMS"], 1):
        r = [f"<SORT_ORDER>{i}</SORT_ORDER>"]
        if not p.get("sub"):
            visible += 1
            r.append(f"<DISPLAY_SEQUENCE>{p.get('display_sequence') or (-visible * 10 if p.get('hidden') else visible * 10)}</DISPLAY_SEQUENCE>")
        r.append(f"<ANCHOR>{e(p['anchor'])}</ANCHOR>")
        if p.get("sql"):
            r.append(f"<SQL_TEXT>{e(p['sql'])}</SQL_TEXT>")
        if not p.get("sub"):
            r.append(f"<PARAMETER_TYPE_DSP>{p['type']}</PARAMETER_TYPE_DSP>")
            validate = ["<VALIDATE_FROM_LIST_DSP>Y</VALIDATE_FROM_LIST_DSP>"] if rep.get("VALIDATE_FROM_LIST") else []
            if p["type"] == "LOV":
                guid, fbd, q = shared_lov(p["lov"])
                r += [f"<LOV_NAME>{e(p['lov'])}</LOV_NAME>", f"<LOV_GUID>{guid}</LOV_GUID>"] + validate + ([fbd] if fbd else []) + [f"<LOV_QUERY_DSP>{q}</LOV_QUERY_DSP>"]
            elif p["type"] == "LOV Oracle":
                r += [f"<LOV_NAME>{e(p['lov'])}</LOV_NAME>"] + validate
            elif p["type"] == "LOV custom":
                r += validate + (["<FILTER_BEFORE_DISPLAY_DSP>Y</FILTER_BEFORE_DISPLAY_DSP>"] if p.get("fbd") else []) + [f"<LOV_QUERY_DSP>{e(p['lov_query'].strip())}</LOV_QUERY_DSP>"]
        if p.get("matching"):
            r.append(f"<MATCHING_VALUE>{e(p['matching'])}</MATCHING_VALUE>")
        if not p.get("sub"):
            if p.get("default"):
                r.append(f"<DEFAULT_VALUE>{e(p['default'])}</DEFAULT_VALUE>")
            if p.get("required"):
                r.append("<REQUIRED>Y</REQUIRED>")
        description = f"\n      <DESCRIPTION>{e(p['description'])}</DESCRIPTION>" if p.get("description") and not p.get("sub") else ""
        r.append(f"<PARAMETER_TRANSLATIONS>\n     <PARAMETER_TRANSLATIONS_ROW>\n      <LANGUAGE>US</LANGUAGE>\n      <PARAMETER_NAME>{e(p['name'])}</PARAMETER_NAME>{description}\n     </PARAMETER_TRANSLATIONS_ROW>\n    </PARAMETER_TRANSLATIONS>")
        rows.append("   <PARAMETERS_ROW>\n    " + "\n    ".join(r) + "\n   </PARAMETERS_ROW>")
    return rows


def select_aliases(sql):
    """output column names: the trailing identifier of every depth-0 item of the top-level select list"""
    items, cur, depth, started = [], [], 0, False
    for t in re.findall(r"'(?:[^']|'')*'|\"[^\"]*\"|--[^\n]*|/\*.*?\*/|[(),]|[\w$#&:.]+|\S", sql, re.S):
        low = t.lower()
        if low.startswith(("--", "/*")):
            continue
        if not started:
            started = low == "select"
            continue
        if t == "(":
            depth += 1
        elif t == ")":
            depth -= 1
        elif depth == 0 and t == ",":
            items.append(cur)
            cur = []
            continue
        elif depth == 0 and low == "from":
            break
        cur.append(t)
    items.append(cur)
    return [i[-1].rsplit(".", 1)[-1].strip('"').lower() for i in items if i]


def check(rep, sql):
    # an alias is the column heading: RDF formula, placeholder and summary names must be renamed after the layout heading
    rdf = [a for a in select_aliases(sql) if re.match(r"c[fps]_", a)]
    if rdf:
        raise SystemExit(f"{rep['NAME']}: RDF column names in the select list, name them after the layout heading: {rdf}")
    display = collections.Counter(p["name"] for p in rep["PARAMS"] if not p.get("sub"))
    for name in {p["name"] for p in rep["PARAMS"]}:
        if display[name] != 1:
            raise SystemExit(f"{rep['NAME']}: parameter {name} has {display[name]} display rows")
    for a in {p["anchor"] for p in rep["PARAMS"]}:
        # the engine replaces an n=n anchor as plain text, so x.oh_row1=1 holds the anchor 1=1 too
        n = len(re.findall(re.escape(a) if re.fullmatch(r"(\d+)=\1", a) else r"(?<![\w&:])" + re.escape(a) + r"(?!\w)", sql, re.I))
        # the framework injects an n=n parameter at EVERY occurrence of its anchor; a report that wants the
        # filters inside each union branch declares the intended count as REPEATED_ANCHOR (anchor -> count)
        expected = rep.get("REPEATED_ANCHOR", {}).get(a, 1)
        if n == 0 or re.fullmatch(r"(\d+)=\1", a) and n != expected:
            raise SystemExit(f"{rep['NAME']}: anchor {a} occurs {n} times in the SQL, expected {expected}")
    # the run form wraps a custom list as select null id, x.value, x.description from (<query>) x
    for p in rep["PARAMS"]:
        q = p.get("lov_query")
        if q and not (re.search(r"\bvalue\b", q, re.I) and re.search(r"\bdescription\b", q, re.I)):
            raise SystemExit(f"{rep['NAME']}: LOV custom of {p['name']} must return value and description columns")
    # the import stores filter before display only for a custom list: a value set's comes from its Long List flag, a shared list's from the list
    for p in rep["PARAMS"]:
        if p.get("fbd") and p.get("type") != "LOV custom":
            raise SystemExit(f"{rep['NAME']}: fbd on {p['name']} needs type LOV custom, the import ignores it for {p.get('type')}")
    # xxen_report.is_function: a default that is neither a select nor one of these expressions is stored as a literal
    for p in rep["PARAMS"]:
        d = (p.get("default") or "").strip()
        c = re.sub(r"\s", "", d).lower()
        f = re.match(r"(\w+)\(", c)
        evaluated = re.match(r"(select|with)\b", d, re.I) or re.match(r"(xxen_report\w*|xxen_schub|xxen_util|fnd_global|fnd_profile|:\$flex\$)\.|sys_context\(", c) \
            or f and f.group(1) in SQL_FUNCTIONS or c.startswith("sysdate") and p.get("type", "").startswith("Date") or re.fullmatch(r"case\s.*\send", d, re.I | re.S)
        if d and not evaluated and (re.fullmatch(r"[a-z_][\w$#]*\.[a-z_][\w$#]*(\(.*\))?", c) or p.get("type") == "Number" and not re.fullmatch(r"-?\d+(\.\d+)?", d)):
            raise SystemExit(f"{rep['NAME']}: default of {p['name']} would be stored as the literal text '{d}', write it as select ... from dual")


def template_rows(rep):
    """TEMPLATES_ROW per entry of the report's TEMPLATES list: NAME, GUID, COLUMNS (ordered column names),
    PIVOT (list of (column, ROW|COLUMN|VALUE|FILTER[, aggregation])), optional DYNAMIC_COLUMNS (default N),
    PIVOT_LAYOUT (T = tabular, no subtotals) and REPEAT_LABELS (Y = every row shows all its row field values),
    SHARED (default True: a Site sharing row, so every user of the report can pick it), DISTINCT (Y = one row per distinct
    combination of the template's columns) and EXCLUDED_PARAMETERS (parameters hidden and not applied when the template is chosen).
    A layout on a NEW report ships inside its definition; the standalone template file is only for a shipped report."""
    out = []
    for t in rep.get("TEMPLATES", []):
        cols = "\n".join(f"     <TEMPLATE_COLUMNS_ROW>\n      <DISPLAY_SEQUENCE>{i}</DISPLAY_SEQUENCE>\n      <COLUMN_NAME>{e(c)}</COLUMN_NAME>\n     </TEMPLATE_COLUMNS_ROW>"
                         for i, c in enumerate(t["COLUMNS"], 1))
        seq = collections.Counter()
        piv = []
        for row in t.get("PIVOT", []):
            col, ftype = row[0], row[1]
            seq[ftype] += 1
            agg = f"\n      <AGGREGATION>{row[2]}</AGGREGATION>" if len(row) > 2 else ""
            piv.append(f"     <TEMPLATE_PIVOT_ROW>\n      <COLUMN_NAME>{e(col)}</COLUMN_NAME>\n      <FIELD_TYPE>{ftype}</FIELD_TYPE>\n      <DISPLAY_SEQUENCE>{seq[ftype]}</DISPLAY_SEQUENCE>{agg}\n     </TEMPLATE_PIVOT_ROW>")
        missing = {r[0] for r in t.get("PIVOT", [])} - set(t["COLUMNS"])
        if missing:
            raise SystemExit(f"{rep['NAME']}/{t['NAME']}: pivot columns not in TEMPLATE_COLUMNS: {sorted(missing)}")
        pivot = f"\n    <TEMPLATE_PIVOT>\n{chr(10).join(piv)}\n    </TEMPLATE_PIVOT>" if piv else ""
        pivot += "".join(f"\n    <{k}>{t[k]}</{k}>" for k in ("PIVOT_LAYOUT", "REPEAT_LABELS") if t.get(k))
        unknown = set(t.get("EXCLUDED_PARAMETERS", [])) - {p["name"] for p in rep["PARAMS"]}
        if unknown:
            raise SystemExit(f"{rep['NAME']}/{t['NAME']}: excluded parameters not in PARAMS: {sorted(unknown)}")
        if t.get("EXCLUDED_PARAMETERS"):
            pivot += "\n    <PARAMETER_EXCLUSION>" + "".join(f"\n     <PARAMETER_EXCLUSION_ROW>\n      <PARAMETER_NAME>{e(n)}</PARAMETER_NAME>\n     </PARAMETER_EXCLUSION_ROW>"
                                                   for n in t["EXCLUDED_PARAMETERS"]) + "\n    </PARAMETER_EXCLUSION>"
        # without a sharing row only the owner can pick the template
        sharing = ("\n    <TEMPLATE_SHARING>\n     <TEMPLATE_SHARING_ROW>\n      <SHARING_LEVEL>S</SHARING_LEVEL>\n"
                   "      <LEVEL_VALUE>Site</LEVEL_VALUE>\n     </TEMPLATE_SHARING_ROW>\n    </TEMPLATE_SHARING>") if t.get("SHARED", True) else ""
        out.append(f"""   <TEMPLATES_ROW>
    <GUID>{t['GUID']}</GUID>
    <TEMPLATE_NAME>{e(t['NAME'])}</TEMPLATE_NAME>{chr(10) + '    <DISTINCT_FLAG>Y</DISTINCT_FLAG>' if t.get('DISTINCT') == 'Y' else ''}
    <DYNAMIC_COLUMNS>{t.get('DYNAMIC_COLUMNS', 'N')}</DYNAMIC_COLUMNS>
    <OWNER>SYSADMIN</OWNER>
    <TEMPLATE_COLUMNS>
{cols}
    </TEMPLATE_COLUMNS>{pivot}{sharing}
   </TEMPLATES_ROW>""")
    return out


def description(rep):
    # the program link in the format of Blitz Report's own concurrent program import; the migration tracker reads it
    if not rep.get("SHORT_NAME"):
        raise SystemExit(f"{rep['NAME']}: SHORT_NAME, the short name of the program the report replaces, is required")
    source = f"Source: {rep['PROGRAM']}\n" if rep.get("PROGRAM") else ""
    return f"{rep['DESCRIPTION'].rstrip()}\n\n{source}Short Name: {rep['SHORT_NAME']}"


def report_xml(rep, category):
    sql = open(rep["SQL"], encoding="utf-8").read().replace("\r\n", "\n").strip()
    check(rep, sql)
    anchors = sorted({p["anchor"] for p in rep["PARAMS"]})
    rows = param_rows(rep)
    templates = template_rows(rep)
    # a DEFAULT_TEMPLATES_ROW without USER_NAME is the global default; it applies only to users who can use the template
    default = ""
    if rep.get("DEFAULT_TEMPLATE"):
        t = next((t for t in rep.get("TEMPLATES", []) if t["NAME"] == rep["DEFAULT_TEMPLATE"]), None)
        if not t or not t.get("SHARED", True):
            raise SystemExit(f"{rep['NAME']}: default template {rep['DEFAULT_TEMPLATE']} must be one of its TEMPLATES and shared")
        default = f"\n   <DEFAULT_TEMPLATES_ROW>\n    <TEMPLATE_GUID>{t['GUID']}</TEMPLATE_GUID>\n   </DEFAULT_TEMPLATES_ROW>"
    return f"""<!-- loader xml for Blitz Report: {e(rep['NAME'])} -->
 <REPORTS_ROW>
  <GUID>{rep['GUID']}</GUID>
  <SQL_TEXT>{e(sql)}</SQL_TEXT>
  <NUMBER_FORMAT>{e(rep.get('NUMBER_FORMAT', '#,##0.00;[Red]-#,##0.00'))}</NUMBER_FORMAT>{chr(10) + '  <DB_PACKAGE>' + rep['DB_PACKAGE'] + '</DB_PACKAGE>' if rep.get('DB_PACKAGE') else ''}
  <REPORT_TRANSLATIONS>
   <REPORT_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <REPORT_NAME>{e(rep['NAME'])}</REPORT_NAME>
    <DESCRIPTION>{e(description(rep))}</DESCRIPTION>
   </REPORT_TRANSLATIONS_ROW>
  </REPORT_TRANSLATIONS>
  <CATEGORY_ASSIGNMENTS>
   <CATEGORY_ASSIGNMENTS_ROW>
    <CATEGORY>{e(category)}</CATEGORY>
   </CATEGORY_ASSIGNMENTS_ROW>
  </CATEGORY_ASSIGNMENTS>
  <ANCHORS>
{chr(10).join(f"   <ANCHORS_ROW>{chr(10)}    <ANCHOR>{e(a)}</ANCHOR>{chr(10)}   </ANCHORS_ROW>" for a in anchors)}
  </ANCHORS>
  <PARAMETERS>
{chr(10).join(rows)}
  </PARAMETERS>
  <TEMPLATES>
{chr(10).join(templates)}
  </TEMPLATES>
  <DEFAULT_TEMPLATES>{default}
  </DEFAULT_TEMPLATES>
  <UPLOAD_COLUMNS>
  </UPLOAD_COLUMNS>
  <UPLOAD_PARAMETERS>
  </UPLOAD_PARAMETERS>
  <UPLOAD_SQLS>
  </UPLOAD_SQLS>
 </REPORTS_ROW>""", len(rows)


def assignment_rows(rep):
    asg = open(rep["ASSIGNMENTS"], encoding="utf-8").read()
    return [r.replace("<ASSIGNMENTS_ROW>", f"<ASSIGNMENTS_ROW><REPORT_NAME>{e(rep['NAME'])}</REPORT_NAME>")
            for r in re.findall(r"<ASSIGNMENTS_ROW>.*?</ASSIGNMENTS_ROW>", asg, re.S)]


spec = runpy.run_path(sys.argv[1])
category = spec["CATEGORY"]
reports = spec.get("REPORTS") or [spec]
blocks, asg = [], []
for rep in reports:
    block, n = report_xml(rep, category)
    blocks.append(block)
    asg += assignment_rows(rep)
    print(f"  {rep['NAME']}: {n} parameter rows, {len(assignment_rows(rep))} assignments")

out = f"""<ROOT>
 <APPS_INITIALIZE_DATA>
  <USER_NAME>ENGINATICS</USER_NAME>
  <RESPONSIBILITY_KEY>SYSTEM_ADMINISTRATOR</RESPONSIBILITY_KEY>
  <APPLICATION_SHORT_NAME>SYSADMIN</APPLICATION_SHORT_NAME>
 </APPS_INITIALIZE_DATA>
<CATEGORIES>
 <CATEGORIES_ROW>
  <BASE_CATEGORY>{e(category)}</BASE_CATEGORY>
  <CATEGORY_TRANSLATIONS>
   <CATEGORY_TRANSLATIONS_ROW>
    <LANGUAGE>US</LANGUAGE>
    <CATEGORY>{e(category)}</CATEGORY>
   </CATEGORY_TRANSLATIONS_ROW>
  </CATEGORY_TRANSLATIONS>
 </CATEGORIES_ROW>
</CATEGORIES>
<REPORTS>
{chr(10).join(blocks)}
</REPORTS>
<ASSIGNMENTS>
{chr(10).join(' ' + r for r in asg)}
</ASSIGNMENTS>
</ROOT>
"""
open(sys.argv[2], "w", encoding="utf-8", newline="\r\n").write(out)
print("wrote", sys.argv[2], len(out), "bytes,", len(reports), "reports,", len(asg), "assignments")

#!/usr/bin/env python
"""Classification probe for the custom-reports migration (Phase 2).

Produces the evidence needed to classify a gathered custom report as Outcome 1 / 2 / 3.
Run this BEFORE deciding the outcome and paste its output into the engagement notes.
A column-name comparison is NOT a classification - this probe compares DATA SOURCES,
which is what actually decides whether a shipped Blitz Report can produce the rows.

Example: a custom GL detail report looked like a two-column enhancement of the shipped
"GL Account Analysis" from column-name overlap alone. The shipped report has ZERO India
Localization references and 2 union branches; the custom report had 8 branches, 4 JAI tables
and 25 calls to a customer package. It was Outcome 3.

USAGE
  # 1. dump the candidate shipped report's SQL from the conversion instance FIRST:
  #    (base table, NOT xxen_reports_v - the view truncates the CLOB at 32764 bytes)
  #      select xr.sql_text from xxen_reports xr, xxen_reports_v v
  #      where xr.report_id=v.report_id and v.report_name='<shipped report>';
  #    save it as shipped.sql
  #
  # 2. python classify_probe.py --custom _extract/<PROG>_datamodel_1.sql \
  #                             --shipped shipped.sql \
  #                             --prefixes XX,YY
  #
  # --prefixes: ALL customer object prefixes, comma separated. Do not assume the one in
  #   manifest.txt is complete - the gather itself only scans for that one, which is how
  #   dependencies get missed (see `custom-reports-migration` skill "Gathering - known gaps").

VERDICT RULE (deterministic - do not override with judgement):
  * any localization object (JAI_/JA_IN_) present in custom but absent in shipped  -> OUTCOME 3
  * any customer-owned object (matching a prefix) referenced by the custom SQL     -> OUTCOME 3
  * custom has materially more union branches than shipped                         -> OUTCOME 3 (investigate)
  * otherwise                                                                      -> Outcome 1/2 candidate,
                                                                                      continue with the column diff
"""
import argparse
import os
import re
import sys

ORACLE_STD = re.compile(
    r'^(ap|ar|gl|po|inv|mtl|wip|bom|fa|pa|pn|ce|xla|hr|hz|per|fnd|ra|rcv|oe|ont|wsh|qp|cst|msc|'
    r'jtf|okc|iby|ego|eam|gmd|gme|gmf|gmi|gmp|opm|cs|csi|cse|zx|xle|xdo|ak|alr|icx|jg|qa|bne|'
    r'ben|pay|ota|pqh|pqp|ssp|pjm|pji|pjc|pn|igi|igc|igs|dual)_',
    re.I)

FROM_RE = re.compile(
    r'(?:\bfrom\b|\bjoin\b|,)\s*(?:([a-z][a-z0-9_$#]{0,60})\s*\.\s*)?([a-z][a-z0-9_$#]{2,60})\s*(?=[\s,);]|$)', re.I)  # ';' - a table may be the last token of the statement
PKG_RE = re.compile(r'(?<![.\w])([a-z][a-z0-9_$#]{2,60})\s*\.\s*[a-z][a-z0-9_$#]*\s*\(', re.I)
FUNC_RE = re.compile(r'(?<![.\w])([a-z][a-z0-9_$#]{2,60})\s*\(', re.I)  # standalone function call, e.g. xx_item_attribute(
SQL_KEYWORDS = {
    'select', 'where', 'and', 'or', 'group', 'order', 'by', 'having', 'union', 'all',
    'from', 'join', 'on', 'as', 'case', 'when', 'then', 'else', 'end', 'null', 'not',
    'in', 'exists', 'is', 'dual', 'distinct', 'left', 'right', 'outer', 'inner', 'full',
    'connect', 'start', 'with', 'over', 'partition', 'rownum', 'decode', 'nvl', 'trunc',
    'to_char', 'to_date', 'to_number', 'substr', 'instr', 'sum', 'count', 'max', 'min',
    'avg', 'round', 'lower', 'upper', 'sysdate', 'userenv', 'set', 'values', 'into',
}


def strip_noise(sql):
    """remove block/line comments and string literals so we only scan real identifiers"""
    sql = re.sub(r'/\*.*?\*/', ' ', sql, flags=re.S)
    sql = re.sub(r'--[^\n]*', ' ', sql)
    sql = re.sub(r"'(?:''|[^'])*'", " '' ", sql)
    return sql


def objects(sql, prefixes):
    def startswith_prefix(name):
        return any(name.startswith(p.lower()) for p in prefixes)

    # dynamic SQL built in string literals (a BeforeReport lexical) can call customer packages
    literals = ' '.join(re.findall(r"'((?:''|[^'])*)'", re.sub(r'--[^\n]*', ' ', re.sub(r'/\*.*?\*/', ' ', sql, flags=re.S))))
    found = {m.group(1).lower() for r in (PKG_RE, FUNC_RE) for m in r.finditer(literals) if startswith_prefix(m.group(1).lower())}
    sql = strip_noise(sql)

    for m in FROM_RE.finditer(sql):
        schema, obj = (m.group(1) or '').lower(), m.group(2).lower()
        # a schema-qualified table hides a customer object when only the SCHEMA carries
        # the prefix (xxc.some_table); keep the qualified name so it is not read as standard
        if schema and startswith_prefix(schema) and not startswith_prefix(obj):
            found.add('%s.%s' % (schema, obj))
        else:
            found.add(obj)
    for m in PKG_RE.finditer(sql):
        found.add(m.group(1).lower())
    for m in FUNC_RE.finditer(sql):
        if any(m.group(1).lower().startswith(p.lower()) for p in prefixes):
            found.add(m.group(1).lower())
    found = {f for f in found if f not in SQL_KEYWORDS and not f.isdigit()}

    loc, cust, std, other = set(), set(), set(), set()
    for f in found:
        if f.startswith('jai_') or f.startswith('ja_in') or f.startswith('jai'):
            loc.add(f)
        elif any(f.startswith(p.lower()) for p in prefixes):
            cust.add(f)
        elif ORACLE_STD.match(f):
            std.add(f)
        else:
            other.add(f)
    return loc, cust, std, other


def branches(sql):
    return len(re.findall(r'(?mi)^\s*union\s+all\s*$', strip_noise(sql))) + 1


def main():
    ap = argparse.ArgumentParser()
    ap.add_argument('--custom', required=True, help='the gathered custom report SQL')
    ap.add_argument('--shipped', help='the candidate shipped Blitz report SQL (from xxen_reports base table)')
    ap.add_argument('--prefixes', default='XX', help='comma separated customer object prefixes')
    a = ap.parse_args()

    prefixes = [p.strip() for p in a.prefixes.split(',') if p.strip()]
    csql = open(a.custom, encoding='utf-8', errors='replace').read()
    cloc, ccust, cstd, cother = objects(csql, prefixes)
    cbr = branches(csql)

    print('=' * 78)
    print('CLASSIFICATION PROBE')
    print('=' * 78)
    print('custom report : %s' % os.path.basename(a.custom))
    print('  union branches            : %d' % cbr)
    print('  localization objects (%d)  : %s' % (len(cloc), ', '.join(sorted(cloc)) or '-'))
    print('  customer objects     (%d)  : %s' % (len(ccust), ', '.join(sorted(ccust)) or '-'))
    print('  standard EBS objects (%d)' % len(cstd))
    if cother:
        print('  UNCLASSIFIED         (%d)  : %s' % (len(cother), ', '.join(sorted(cother))))
        print('     ^ check these by hand - an unrecognised prefix hides customer objects')

    sloc = scust = sstd = set()
    sbr = 0
    if a.shipped:
        ssql = open(a.shipped, encoding='utf-8', errors='replace').read()
        sloc, scust, sstd, _ = objects(ssql, prefixes)
        sbr = branches(ssql)
        print()
        print('shipped report: %s' % os.path.basename(a.shipped))
        print('  union branches            : %d' % sbr)
        print('  localization objects (%d)  : %s' % (len(sloc), ', '.join(sorted(sloc)) or '-'))
        print('  standard EBS objects (%d)' % len(sstd))

    missing_loc = cloc - sloc
    print()
    print('-' * 78)
    print('VERDICT')
    print('-' * 78)
    reasons = []
    if missing_loc:
        reasons.append('custom uses %d localization object(s) the shipped report does not: %s'
                       % (len(missing_loc), ', '.join(sorted(missing_loc))))
    if ccust:
        reasons.append('custom depends on %d customer-owned object(s): %s'
                       % (len(ccust), ', '.join(sorted(ccust))))
    if a.shipped and cbr > sbr:
        reasons.append('custom has %d union branches vs %d in the shipped report' % (cbr, sbr))

    if reasons:
        print('  OUTCOME 3 - build a new custom report')
        for r in reasons:
            print('    - %s' % r)
        if ccust:
            print()
            print('  ACTION: confirm every customer object above has a gathered .sql dependency')
            print('          file. If not, request the source from the customer BEFORE building')
            print('          (DBA Text Search report) - the gather only scans the ONE prefix in')
            print('          manifest.txt, so objects under any other naming are silently missing.')
        rc = 3
    else:
        print('  Outcome 1/2 CANDIDATE - no localization or customer-object blockers found.')
        print('  Now do the column diff to decide between 1 (complete map) and 2 (enhance),')
        print('  comparing against ALL templates of the shipped report, not just one.')
        rc = 0
    print()
    return rc


if __name__ == '__main__':
    sys.exit(main())

#!/usr/bin/env python3
"""Field inventory of an original Oracle report: every printed field traced to where the original computes it.

usage: field_inventory.py <program folder> [<PROG>] [--out <file.md>]

Reads what the gather put in the folder: <PROG>_rdf.xml (Oracle Reports, rwconverter XML) or <PROG>.rex (its text
export, when rwconverter fails), a BI Publisher data
template (*.xml with <dataTemplate>), the RTF layouts, and custom package bodies (*package_body.sql, *.pkb).
Writes <PROG>_field_inventory.md next to them. The Blitz expression and Proof columns are filled in during the
conversion; a printed field without a Blitz expression, and a row-control item without an implementation, is an
open defect (`custom-reports-migration` skill, "Value fidelity").
"""
import glob, html, os, re, sys
import xml.etree.ElementTree as ET

KEYWORDS = set("""select from where and or not null is in like between case when then else end nvl nvl2 decode to_char
to_number to_date trunc round substr substrb instr length upper lower trim ltrim rtrim replace sum count max min avg
sysdate distinct as dbms_xmlgen convert abs sign coalesce exists any all true false""".split())
FLOW = re.compile(r"^(for-each|for-each-group|end|if|choose|when|otherwise|call|template|import|sort|attribute|param|variable|split-by-page-break|section)\b", re.I)


def read(p):
    with open(p, "rb") as f:
        return f.read().decode("utf-8", "replace")


def strip_ns(root):
    for e in root.iter():
        if isinstance(e.tag, str) and "}" in e.tag:
            e.tag = e.tag.split("}", 1)[1]
    return root


def first_lines(text, n=3, width=160):
    lines = [l.strip() for l in (text or "").splitlines() if l.strip()]
    return " / ".join(lines[:n])[:width]


def can_return_false(body):
    code = re.sub(r"/\*.*?\*/|--[^\n]*", " ", body or "", flags=re.S)
    return re.search(r"return\s*\(?\s*false", code, re.I) is not None


def cell(s):
    return str(s if s is not None else "").replace("|", "\\|").replace("\n", " ")


class Inventory:
    def __init__(self, prog):
        self.prog = prog
        self.sources = {}      # UPPER name -> (kind, definition)
        self.units = {}        # lower function name -> body
        self.printed = []      # (name, use, context) in layout order
        self.formulas = []
        self.formula_unit = {} # UPPER formula name -> lower function name
        self.dt_elements = []  # data template elements in document order
        self.row_control = []  # (kind, object, detail)
        self.queries = []      # (name, sql)
        self.package_out = []
        self.notes = []

    # ---------- Oracle Reports (rwconverter XML) ----------
    def rdf(self, path):
        try:
            root = strip_ns(ET.fromstring(read(path).encode("utf-8")))
        except ET.ParseError as e:
            self.notes.append("%s does not parse as XML (%s): read it by hand or use rex_parse.py." % (os.path.basename(path), e))
            return
        for fn in root.iter("function"):
            src = fn.find("textSource")
            self.units[(fn.get("name") or "").lower()] = src.text if src is not None and src.text else ""
        for p in list(root.iter("userParameter")) + list(root.iter("systemParameter")):
            self.sources[p.get("name", "").upper()] = ("parameter", p.get("initialValue") and "initial value %s" % p.get("initialValue") or "")
        for ds in root.iter("dataSource"):
            sel = ds.find("select")
            sql = sel.text if sel is not None and sel.text else ""
            self.queries.append((ds.get("name"), sql))
            self._groups(ds, ds.get("name"), None)
        # formulas, placeholders and summaries outside any group (report level)
        data = root.find(".//data")
        if data is not None:
            for child in data:
                self._column(child, "report level", None)
        for link in root.iter("link"):
            self.row_control.append(("link", "%s -> %s" % (link.get("parentGroup"), link.get("childQuery") or link.get("childGroup")),
                                     "a filter or empty parent group suppresses the child query"))
        for e in root.iter():
            if e.tag == "advancedLayout":
                continue
            trig = e.get("formatTrigger")
            if not trig and e.find("advancedLayout") is not None:
                trig = e.find("advancedLayout").get("formatTrigger")
            if trig:
                body = self.units.get(trig.lower(), "")
                if can_return_false(body):
                    self.row_control.append(("format trigger", "%s %s (source %s)" % (e.tag, e.get("name"), e.get("source")),
                                             "%s can return FALSE: %s" % (trig, first_lines(re.sub(r"(?is)^.*?\bbegin\b", "", body, count=1)))))
        for e in root.iter("field"):
            self.printed.append((e.get("source") or e.get("name"), "paper layout field %s" % e.get("name"), ""))
        for name, body in self.units.items():
            if name in ("beforereport", "afterpform", "afterreport", "beforepform", "betweenpages") or name.endswith("trigger") and "format" not in name:
                lex = sorted(set(re.findall(r":(\w+)\s*:=", body)))
                self.row_control.append(("trigger", name, "assigns %s" % ", ".join(lex) if lex else first_lines(body, 2)))

    def _formula(self, name, where, fn, datatype, null):
        body = self.units.get(fn, "")
        assigns = sorted(set(m.upper() for m in re.findall(r":(\w+)\s*:=", body)))
        refs = sorted(set(m.upper() for m in re.findall(r":(\w+)", body)) - set(assigns) - {name})
        into = len(re.findall(r"\binto\b", body, re.I))
        handlers = [first_lines(h, 1, 80) for h in re.findall(r"(?is)\bwhen\s+(?:others|no_data_found|too_many_rows)\b.*?(?=\bwhen\b|\bend\b)", body)]
        self.sources[name] = ("formula", "%s: %s%s%s" % (where, fn, ", " + datatype if datatype else "", ", null prints %s" % null if null else ""))
        self.formulas.append((name, where, fn, datatype, null, assigns, refs, into, handlers))
        self.formula_unit[name] = fn

    def _groups(self, node, query, group):
        for child in node:
            if child.tag == "group":
                g = child.get("name")
                self._groups(child, query, g)
            else:
                self._column(child, query, group)

    def _column(self, e, query, group):
        name = (e.get("name") or "").upper()
        where = "%s%s" % (query, " / " + group if group else "")
        if e.tag == "dataItem":
            d = e.find("dataDescriptor")
            self.sources[name] = ("query column", "%s: %s" % (where, d.get("expression") if d is not None else ""))
        elif e.tag == "formula":
            self._formula(name, where, (e.get("source") or "").lower(), e.get("datatype"), e.get("valueIfNull"))
        elif e.tag == "placeholder":
            self.sources[name] = ("placeholder", "%s, %s width %s%s" % (where, e.get("datatype"), e.get("width"), ", null prints %s" % e.get("valueIfNull") if e.get("valueIfNull") else ""))
        elif e.tag == "summary":
            self.sources[name] = ("summary", "%s: %s(%s) reset %s compute %s" % (where, e.get("function"), e.get("source"), e.get("reset"), e.get("compute")))
        elif e.tag == "filter":
            if e.get("type") == "plsql":
                body = self.units.get((e.get("plsqlFilter") or "").lower(), "")
                self.row_control.append(("group filter", group, "%s: %s" % (e.get("plsqlFilter"), first_lines(re.sub(r"(?is)^.*?\bbegin\b", "", body, count=1)))))
            else:
                self.row_control.append(("group filter", group, "%s %s records" % (e.get("type"), e.get("numberOfRecords"))))

    # ---------- Oracle Reports text export (.rex), when rwconverter fails ----------
    def rex(self, path):
        sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
        import rex_parse
        recs = rex_parse.parse(path)
        by_id = {r.get("ITEMID"): r for r in recs if r.get("ITEMID")}
        groups = {r.get("ITEMID"): r for r in recs if r["_type"] == "SRW2_GROUP"}
        queries = {r.get("ITEMID"): r.get("NAME") for r in recs if r["_type"] == "SRW2_QUERY"}
        owner = {}
        for r in recs:
            if r["_type"] == "SRW2_QUERY":
                self.queries.append((r.get("NAME"), r.get("QUERYLFID") or ""))
            elif r["_type"] == "TOOL_PLSQL":
                self.units[(r.get("NAME") or "").lower()] = r.get("PLSLFID_ST") or ""
                owner.setdefault(r.get("OBJECT_ID"), []).append(r)
        for r in recs:
            if r["_type"] != "SRW2_COLUMN":
                continue
            g = groups.get(r.get("GROUP_ID"), {})
            where = "%s / %s" % (queries.get(g.get("QUERY_ID"), "report level"), g.get("NAME")) if g else "report level"
            name = (r.get("NAME") or "").upper()
            null = r.get("NULL_VALUE") if r.get("NULL_VALUE") not in (None, "NULLP") else None
            units = [u for u in owner.get(r.get("ITEMID"), []) if u.get("TYPE") == "4"]
            if units:
                self._formula(name, where, units[0].get("NAME").lower(), "", null)
            else:
                self.sources.setdefault(name, ("column", "%s (query column, summary, placeholder or parameter: see the .rex)%s" % (where, ", null prints %s" % null if null else "")))
        for r in recs:
            if r["_type"] == "TOOL_PLSQL" and r.get("TYPE") != "4":
                o = by_id.get(r.get("OBJECT_ID"), {})
                body = r.get("PLSLFID_ST") or ""
                if o.get("_type") in ("SRW2_FRAME", "SRW2_FIELD", "SRW2_BOILERPLATE"):
                    if can_return_false(body):
                        self.row_control.append(("format trigger", "%s %s" % (o["_type"][5:].lower(), o.get("NAME")),
                                                 "%s can return FALSE: %s" % (r.get("NAME"), first_lines(re.sub(r"(?is)^.*?\bbegin\b", "", body, count=1)))))
                elif o.get("_type") == "SRW2_GROUP":
                    self.row_control.append(("group filter", o.get("NAME"), "%s: %s" % (r.get("NAME"), first_lines(re.sub(r"(?is)^.*?\bbegin\b", "", body, count=1)))))
                elif r.get("TYPE") in ("7", "8", "9", "10"):
                    lex = sorted(set(re.findall(r":(\w+)\s*:=", body)))
                    self.row_control.append(("trigger", r.get("NAME"), "assigns %s" % ", ".join(lex) if lex else first_lines(body, 2)))
            elif r["_type"] == "SRW2_LINK":
                p, c = by_id.get(r.get("PARENT_ID"), {}), by_id.get(r.get("CHILD_ID"), {})
                self.row_control.append(("link", "%s -> %s" % (p.get("NAME"), c.get("NAME")), "a filter or empty parent group suppresses the child"))
            elif r["_type"] == "SRW2_FIELD":
                src = by_id.get(r.get("SOURCE_ID"), {})
                if src.get("NAME"):
                    self.printed.append((src.get("NAME"), "paper layout field %s" % r.get("NAME"), ""))

    # ---------- BI Publisher data template ----------
    def data_template(self, path):
        try:
            root = strip_ns(ET.fromstring(read(path).encode("utf-8")))
        except ET.ParseError as e:
            self.notes.append("%s does not parse as XML (%s)." % (os.path.basename(path), e))
            return
        if root.get("defaultPackage"):
            self.notes.append("Data template %s uses default package %s: read its triggers and functions." % (os.path.basename(path), root.get("defaultPackage")))
        for p in root.iter("parameter"):
            self.sources[(p.get("name") or "").upper()] = ("parameter", "")
        for q in root.iter("sqlStatement"):
            self.queries.append((q.get("name"), q.text or ""))
        for t in root.iter("dataTrigger"):
            self.row_control.append(("data trigger", t.get("name"), t.get("source")))
        for g in root.iter("group"):
            if g.get("groupFilter"):
                self.row_control.append(("group filter", g.get("name"), g.get("groupFilter")))
            for el in g.findall("element"):
                n = (el.get("name") or "").strip().upper()
                kind = "summary" if el.get("function") else "query column"
                self.sources[n] = (kind, "%s / %s: %s%s" % (g.get("source"), g.get("name"), (el.get("value") or "").strip(),
                                   " %s()" % el.get("function") if el.get("function") else ""))
                if g.get("name") != "G_PARAMETERS":
                    self.dt_elements.append(n)
        for lk in root.iter("link"):
            self.row_control.append(("link", "%s -> %s" % (lk.get("parentQuery"), lk.get("childQuery")), lk.get("condition") or ""))

    # ---------- RTF layout ----------
    def rtf(self, path):
        text = read(path)
        # a format mask's ';' and quotes are hex escapes, which must not glue the field name to the mask
        text = re.sub(r"\\'([0-9a-fA-F]{2})", lambda m: bytes([int(m.group(1), 16)]).decode("cp1252", "replace"), text)
        text = re.sub(r"\\(rquote|lquote)\s?", "'", text)
        text = re.sub(r"\\(rdblquote|ldblquote)\s?", '"', text)
        text = re.sub(r"\\[a-zA-Z]+-?\d* ?", "", text)
        text = re.sub(r"[{}\r\n]", "", text)
        # a program with an RTF layout prints the RTF; an RDF's old paper layout never reaches the output
        self.printed = [p for p in self.printed if not p[1].startswith("paper layout field")]
        seen = set(n for n, _, _ in self.printed)
        for tok in re.findall(r"<\?(.{1,400}?)\?>", text):
            tok = tok.strip()
            names = [n.upper() for n in re.findall(r"[A-Za-z_][A-Za-z0-9_]*", tok) if n.lower() not in KEYWORDS]
            if FLOW.match(tok) or re.match(r"^(xdoxslt|xdofx|xdoxliff):", tok, re.I) or not re.fullmatch(r"[A-Za-z_][A-Za-z0-9_]*", tok):
                known = [n for n in names if n in self.sources]
                if re.match(r"^(if|choose|when)\b", tok, re.I) or "position()" in tok:
                    self.row_control.append(("layout condition", os.path.basename(path), tok[:200]))
                elif re.match(r"^for-each", tok, re.I):
                    self.row_control.append(("layout loop", os.path.basename(path), tok[:200]))
                elif known:
                    for n in known:
                        if n not in seen:
                            seen.add(n)
                            self.printed.append((n, "layout expression", tok[:120]))
                continue
            n = tok.upper()
            if n not in seen:
                seen.add(n)
                self.printed.append((n, "layout field", os.path.basename(path)))

    # ---------- PL/SQL package (execution method I, or a data template's package) ----------
    def package(self, path, entry=None):
        """entry: the executable's procedure name; output is then read from that procedure only."""
        text = read(path)
        lines = text.splitlines()
        lo, hi = 0, len(text)
        if entry:
            # the definition, not a forward declaration: 'is' or 'as' comes before the first semicolon
            starts = [m.start() for m in re.finditer(r"(?im)^\s*procedure\s+%s\b" % re.escape(entry), text)
                      if re.search(r"(?i)\b(is|as)\b", text[m.end():text.find(";", m.end())])]
            if not starts:
                return
            lo = starts[-1]
            end = re.compile(r"(?im)^\s*end\s+%s\s*;" % re.escape(entry)).search(text, lo)
            nxt = re.compile(r"(?im)^\s*(procedure|function)\s+\w+").search(text, lo + 10)
            hi = end.end() if end else (nxt.start() if nxt else len(text))
            self.notes.append("Output read from procedure %s of %s (lines %d-%d); procedures it calls that print are not followed: read them by hand." % (
                entry, os.path.basename(path), text.count("\n", 0, lo) + 1, text.count("\n", 0, hi) + 1))
        literal_cols = {}
        for m in re.finditer(r"(?is)(?:select|,)\s*(null|0|-?\d+(?:\.\d+)?|'[^']*')\s+(?:as\s+)?([a-z_][a-z0-9_$#]*)\s*(?=,|\bfrom\b)", text):
            literal_cols.setdefault(m.group(2).lower(), m.group(1))
        inserts = sorted(set(m.lower() for m in re.findall(r"(?i)\binsert\s+into\s+([a-z0-9_$#.]+)", text[lo:hi])))
        if inserts:
            self.notes.append("%s inserts into %s: the rows the layout prints may be written by code, not selected." % (os.path.basename(path), ", ".join(inserts)))

        def computed_at(expr):
            where = []
            for x in [x for x in re.findall(r"[A-Za-z_][A-Za-z0-9_$#]*(?:\.[A-Za-z_][A-Za-z0-9_$#]*)?", re.sub(r"'[^']*'", "", expr)) if x.lower() not in KEYWORDS]:
                base = x.split(".")[-1].lower()
                if "." in x:
                    if base in literal_cols:
                        where.append("%s is the cursor literal %s: filled by loop code?" % (x, literal_cols[base]))
                    continue
                hits = [str(j) for j, l in enumerate(lines, 1) if re.search(r"(?i)\b%s\s*:=" % re.escape(x), l) or re.search(r"(?i)\binto\b.*\b%s\b" % re.escape(x), l)]
                if hits:
                    where.append("%s assigned at line %s" % (x, ", ".join(hits[:12]) + (" ..." if len(hits) > 12 else "")))
            return "; ".join(where)

        def add(name, line, expr):
            self.package_out.append((name, line, expr.strip()[:120], computed_at(expr)))
            self.sources.setdefault(name.upper(), ("package output", "%s line %d: %s" % (os.path.basename(path), line, expr.strip()[:100])))

        # XML elements: '<TAG>'||expr||'</TAG>'
        for m in re.finditer(r"(?i)'\s*<(\w+)>\s*'\s*\|\|\s*(.+?)\s*\|\|\s*'\s*</\1>\s*'", text[lo:hi]):
            add(m.group(1).upper(), text.count("\n", 0, lo + m.start()) + 1, m.group(2))
        # XML elements written through a helper: <helper>('TAG',expr), where the helper builds '<'||tag||'>'
        helpers = []
        for m in re.finditer(r"(?im)^\s*procedure\s+(\w+)\s*\(", text):
            body_end = re.compile(r"(?im)^\s*end\s+%s\s*;" % re.escape(m.group(1))).search(text, m.end())
            if body_end and re.search(r"'<'\s*\|\|", text[m.end():body_end.start()]):
                helpers.append(m.group(1))
        for h in set(helpers):
            for m in re.finditer(r"(?i)\b%s\s*\(\s*'(\w+)'\s*,\s*" % re.escape(h), text[lo:hi]):
                start, depth, i = lo + m.end(), 1, lo + m.end()
                while i < hi and depth:
                    depth += {"(": 1, ")": -1}.get(text[i], 0)
                    i += 1
                add(m.group(1).upper(), text.count("\n", 0, lo + m.start()) + 1, text[start:i - 1])
        # delimited text lines: a heading line of literals, then data lines of values joined with '|', ',' or a tab
        headings = []
        for m in re.finditer(r"(?i)\b(?:fnd_file\.put_line\s*\(\s*fnd_file\.output\s*,|fnd_file\.put\s*\(\s*fnd_file\.output\s*,|utl_file\.put_line\s*\(\s*\w+\s*,|utl_file\.put\s*\(\s*\w+\s*,)", text[lo:hi]):
            start = lo + m.end()
            expr, depth, q, i = None, 1, False, start
            while i < hi:
                c = text[i]
                if q:
                    if c == "'":
                        if text[i + 1:i + 2] == "'":
                            i += 1
                        else:
                            q = False
                elif c == "'":
                    q = True
                elif c == "(":
                    depth += 1
                elif c == ")":
                    depth -= 1
                    if depth == 0:
                        expr = text[start:i]
                        break
                i += 1
            if expr is None or re.search(r"(?i)'\s*<\w+>", expr):
                continue
            pieces, cur, depth, q = [], "", 0, False
            j = 0
            while j < len(expr):
                c = expr[j]
                if q:
                    cur += c
                    if c == "'":
                        if expr[j + 1:j + 2] == "'":
                            cur += "'"
                            j += 1
                        else:
                            q = False
                elif c == "'":
                    q = True
                    cur += c
                elif c == "(":
                    depth += 1
                    cur += c
                elif c == ")":
                    depth -= 1
                    cur += c
                elif expr.startswith("||", j) and depth == 0:
                    pieces.append(cur.strip())
                    cur = ""
                    j += 1
                else:
                    cur += c
                j += 1
            pieces.append(cur.strip())
            delim = lambda p: re.fullmatch(r"(?i)chr\s*\(\s*(9|44|124)\s*\)|'[\s|,~;^\"\t]*'", p) is not None
            literal = lambda p: re.fullmatch(r"'(?:[^']|'')*'", p) is not None
            values = [p for p in pieces if p and not delim(p)]
            line = text.count("\n", 0, start) + 1
            if values and all(literal(p) for p in values):
                heads = []
                for p in values:
                    heads += [h.strip() for h in re.split(r"[|\t]|\\t", p[1:-1].replace("''", "'")) if h.strip()]
                if len(heads) > 1:
                    headings = heads
                continue
            if len(values) < 2:
                continue
            for k, v in enumerate(values):
                add(headings[k] if len(headings) == len(values) else "%s value %d" % (os.path.basename(path).split(".")[1] if "." in os.path.basename(path) else "output", k + 1), line, v)

    # ---------- report ----------
    def write(self, out):
        printed = list(self.printed or [(t, "package output", "") for t, _, _, _ in self.package_out])
        if not printed and self.dt_elements:
            self.notes.append("No layout file in the folder: the printed fields below are the data template's elements. "
                              "Take the printed set, order and headings from the customer's layout or an ERP output.")
            printed = [(n, "data template element", "") for n in self.dt_elements]
        # a printed formula that concatenates other columns into a text line prints each of them
        seen = set(n.upper() for n, _, _ in printed)
        for n, _, _ in list(printed):
            body = self.units.get(self.formula_unit.get(n.upper(), ""), "")
            if body.count("||") >= 3:
                for ref in re.findall(r":(\w+)", body):
                    if ref.upper() not in seen and ref.upper() in self.sources:
                        seen.add(ref.upper())
                        printed.append((ref, "printed inside %s" % n, ""))
        w = []
        a = w.append
        a("# Field inventory: %s" % self.prog)
        a("")
        a("Every printed field, and everything that decides which rows print, traced to where the original computes it. Fill the "
          "Blitz expression and Proof columns during the conversion; an empty Blitz expression or implementation is an open defect.")
        a("")
        for n in self.notes:
            a("- " + n)
        if self.notes:
            a("")
        a("## 1. Printed fields (%d)" % len(printed))
        a("")
        a("| # | Field | Where it is used | Source in the original | Blitz expression | Proof |")
        a("|---|---|---|---|---|---|")
        for i, (n, use, ctx) in enumerate(printed, 1):
            kind, d = self.sources.get(n.upper(), ("UNRESOLVED", "not found in the data model: read the source"))
            a("| %d | %s | %s | %s: %s | | |" % (i, cell(n), cell(use if not ctx else "%s (%s)" % (use, ctx)), kind, cell(d)))
        a("")
        a("## 2. Formula columns (%d)" % len(self.formulas))
        a("")
        a("Each needs its SQL equivalent, including what its exception handlers print when a lookup finds no row or several, "
          "and every placeholder it assigns.")
        a("")
        a("| Formula | Group | Function | Returns | Null prints | Assigns | Reads | select into | Handlers | Blitz expression |")
        a("|---|---|---|---|---|---|---|---|---|---|")
        for f in self.formulas:
            a("| %s | %s | %s | %s | %s | %s | %s | %d | %s | |" % (cell(f[0]), cell(f[1]), cell(f[2]), cell(f[3]), cell(f[4]),
              cell(", ".join(f[5])), cell(", ".join(f[6])[:200]), f[7], cell("; ".join(f[8])[:200])))
        a("")
        a("## 3. Row and section control (%d)" % len(self.row_control))
        a("")
        a("Group filters, links, format triggers that can return FALSE, layout conditions and loops, and triggers that build "
          "lexicals decide which rows and sections print. Each needs an implementation or a stated reason why it never fires.")
        a("")
        a("| Kind | Object | Detail | Implementation |")
        a("|---|---|---|---|")
        for k, o, d in self.row_control:
            a("| %s | %s | %s | |" % (cell(k), cell(o), cell(d)))
        a("")
        a("## 4. Queries (%d)" % len(self.queries))
        a("")
        a("A `union` de-duplicates over its whole select list, ids and rowids included: keep the original's operator and key. "
          "Lexicals are filled by the triggers listed above.")
        a("")
        a("| Query | union | union all | order by | Lexicals |")
        a("|---|---|---|---|---|")
        for n, sql in self.queries:
            s = re.sub(r"--[^\n]*|/\*.*?\*/", " ", sql or "", flags=re.S)
            s = re.sub(r"'[^']*'", "''", s)
            a("| %s | %d | %d | %s | %s |" % (cell(n), len(re.findall(r"(?i)\bunion\b(?!\s+all\b)", s)), len(re.findall(r"(?i)\bunion\s+all\b", s)),
              "yes" if re.search(r"(?i)\border\s+by\b", s) else "no", cell(", ".join(sorted(set(re.findall(r"&(\w+)", s)))))))
        if self.package_out:
            a("")
            a("## 5. Package output elements (%d)" % len(self.package_out))
            a("")
            a("Values the program prints from variables are computed by its loop code, not by the cursor: replicate those "
              "assignments. A cursor literal (`0 basic`, `null source`) is a placeholder the loop fills.")
            a("")
            a("| Element | Line | Expression | Computed at | Blitz expression |")
            a("|---|---|---|---|---|")
            for t, i, e, wh in self.package_out:
                a("| %s | %d | %s | %s | |" % (cell(t), i, cell(e), cell(wh)))
        with open(out, "w", encoding="utf-8", newline="\n") as fh:
            fh.write("\n".join(w) + "\n")
        unresolved = sum(1 for n, _, _ in printed if n.upper() not in self.sources)
        print("%s: %d printed fields (%d unresolved), %d formulas, %d row-control items, %d queries, %d package output elements -> %s" % (
            self.prog, len(printed), unresolved, len(self.formulas), len(self.row_control), len(self.queries), len(self.package_out), out))


def main():
    args = sys.argv[1:]
    out = None
    if "--out" in args:
        i = args.index("--out")
        out = args[i + 1]
        del args[i:i + 2]
    if not args:
        sys.exit(__doc__)
    folder = args[0]
    prog = args[1] if len(args) > 1 else os.path.basename(os.path.normpath(folder))
    inv = Inventory(prog)
    # the RDF is named after the executable, which can differ from the program short name
    rdf = next(iter([os.path.join(folder, prog + "_rdf.xml")] if os.path.exists(os.path.join(folder, prog + "_rdf.xml")) else sorted(glob.glob(os.path.join(folder, "*_rdf.xml")))), None)
    rex = next(iter(sorted(glob.glob(os.path.join(folder, "*.rex")))), None)
    if rdf:
        inv.rdf(rdf)
    elif rex:
        inv.rex(rex)
    elif glob.glob(os.path.join(folder, "*.rdf")):
        inv.notes.append("No _rdf.xml or .rex: rwconverter failed. Ask the customer for the .rex text export (Reports Builder: File > Convert) and put it in this folder.")
    for x in glob.glob(os.path.join(folder, "*.xml")):
        if x != rdf and "<dataTemplate" in read(x)[:5000]:
            inv.data_template(x)
    # a PL/SQL program's output comes from its executable: PKG.PROCEDURE, or a standalone procedure
    pkg = proc = None
    ldt = os.path.join(folder, prog + ".ldt")
    if os.path.exists(ldt):
        m = re.search(r'(?s)BEGIN EXECUTABLE .*?EXECUTION_METHOD_CODE = "(\w)".*?EXECUTION_FILE_NAME = "([^"]+)"', read(ldt))
        if m and m.group(1) == "I":
            parts = m.group(2).split(".")
            pkg, proc = (parts[-2].upper(), parts[-1]) if len(parts) > 1 else (None, parts[-1])
    code = sorted(glob.glob(os.path.join(folder, "*package_body.sql")) + glob.glob(os.path.join(folder, "*.pkb")) + glob.glob(os.path.join(folder, "*procedure.sql")))
    entry_files = [p for p in code if pkg and ".%s." % pkg in os.path.basename(p).upper() or not pkg and proc and ".%s." % proc.upper() in os.path.basename(p).upper()]
    for p in entry_files or code:
        inv.package(p, proc if pkg and p in entry_files else None)
    for r in sorted(glob.glob(os.path.join(folder, "*.rtf"))):
        inv.rtf(r)
    inv.write(out or os.path.join(folder, prog + "_field_inventory.md"))


if __name__ == "__main__":
    main()

#!/usr/bin/env python
"""Parse an Oracle Reports .rex text export (Reports Builder: File > Convert, "Report Definition file" -> "Text file")
into its DEFINE records, decoding <<"...">> text and (BINARY)/(TLONG) blocks.

Fallback when rwconverter segfaults on the .rdf (no <PROG>_rdf.xml): ask the customer for the .rex and run
    python rex_parse.py <folder-with-rex> <PROG> [<PROG> ...]
which prints the record-type counts, every SRW2_QUERY SQL, every non-trivial TOOL_PLSQL unit and the
parameter columns (SRW2_COLUMN FUNCTION=3), and writes <PROG>_<QUERY>.sql + <PROG>_plsql.txt next to the .rex.
Query variants that differ only in sort/group expressions collapse into one Blitz SQL; the LDT stays the parameter source.
"""
import re,io,sys,os,collections
def parse(path):
    with io.open(path,encoding='latin-1') as f: lines=f.read().split('\n')
    recs=[];i=0;n=len(lines)
    while i<n:
        m=re.match(r'^DEFINE\s+(\S+)',lines[i])
        if not m: i+=1; continue
        typ=m.group(1); i+=1
        assert lines[i].strip()=='BEGIN',(path,i,lines[i]); i+=1
        rec={'_type':typ}
        while i<n and lines[i].strip()!='END':
            l=lines[i]
            fm=re.match(r'^\s*(\w+)\s*=\s*(.*)$',l)
            if fm:
                k,v=fm.group(1),fm.group(2).strip()
                if v.startswith('(') and v.endswith(')') or v=='':
                    # value follows as <<" ... ">> block
                    i+=1
                    buf=[]
                    if lines[i].startswith('<<"'):
                        first=lines[i][3:]
                        if first.endswith('">>'):
                            buf.append(first[:-3]); i+=1
                        else:
                            buf.append(first); i+=1
                            while i<n and not lines[i].endswith('">>'):
                                buf.append(lines[i]); i+=1
                            buf.append(lines[i][:-3]); i+=1
                    raw='\n'.join(buf)
                    if v=='(BINARY)':
                        hx=re.sub(r'\s+','',raw)
                        try: txt=bytes.fromhex(hx).decode('latin-1').rstrip('\x00')
                        except Exception: txt=raw
                        rec[k]=txt
                    else:
                        rec[k]=raw
                    continue
                if v.startswith('<<"'):
                    if v.endswith('">>'): rec[k]=v[3:-3]; i+=1; continue
                    buf=[v[3:]]; i+=1
                    while i<n and not lines[i].endswith('">>'):
                        buf.append(lines[i]); i+=1
                    buf.append(lines[i][:-3]); i+=1
                    rec[k]='\n'.join(buf); continue
                rec[k]=v.strip()
            i+=1
        recs.append(rec); i+=1
    return recs
if __name__=='__main__':
    M=sys.argv[1]
    for prog in sys.argv[2:]:
        recs=parse(os.path.join(M,prog+'.rex'))
        print('#'*20,prog,len(recs),'records')
        c=collections.Counter(r['_type'] for r in recs)
        print({k:v for k,v in c.items() if not k.startswith('VG_')})
        plsql=[]
        for r in recs:
            t=r['_type']
            if t=='SRW2_QUERY':
                sql=next((v for k,v in r.items() if isinstance(v,str) and len(v)>40 and 'select' in v.lower()),None)
                fn=os.path.join(M,'%s_%s.sql'%(prog,r.get('NAME')))
                if sql:
                    with io.open(fn,'w',encoding='utf-8',newline='\n') as f: f.write(sql)
                print('--- QUERY',r.get('NAME'),'->',fn,'(%d chars)'%len(sql or ''))
            if t=='TOOL_PLSQL':
                src=r.get('PLSLFID_ST') or ''
                plsql.append('=== PLSQL %s type %s\n%s'%(r.get('NAME'),r.get('TYPE'),src))
                body=re.sub(r'/\*.*?\*/','',src,flags=re.S); body=re.sub(r'--[^\n]*','',body)
                lines=[l for l in body.splitlines() if l.strip()]
                trivial=len(lines)<=6 and re.search(r'return\s*\(?\s*(true|false)\s*\)?\s*;',body,re.I) and not re.search(r'select|:=',body,re.I)
                print('--- PLSQL',r.get('NAME'),'type',r.get('TYPE'),'src',len(src),'(trivial)' if trivial else '')
            if t in ('SRW2_PARAMETER','SRW2_USER_PARAM','SRW2_PARAM'):
                print('--- PARAM',{k:v for k,v in r.items() if k in ('NAME','DATA_TYPE','INIT_VALUE','WIDTH','PARAM_TYPE')})
            if t=='SRW2_COLUMN' and str(r.get('FUNCTION'))=='3':
                print('--- PARAM',r.get('NAME'),'| datatype',r.get('DATA_TYPE'),'| width',r.get('WIDTH'))
        with io.open(os.path.join(M,prog+'_plsql.txt'),'w',encoding='utf-8',newline='\n') as f: f.write('\n'.join(plsql))

#!/usr/bin/env python
"""Offline assignments gate for the custom-reports migration (Phase 2).

Asserts that the <ASSIGNMENTS> block of each built report XML reproduces the gathered
<PROG>_assignments.xml EXACTLY - every request group carried, each with its OWN application
short name, and the level code normalised for the target instance.

Needs NO database. That is the point: on a conversion instance the customer's request groups
do not exist, so the import legitimately reports 0 assignments and can tell you nothing about
whether the XML was right. A zero there is UNVERIFIABLE, not PASSED - accepting it lets a
defect ship (a few of the groups carried per report, all stamped with the customer's custom
application instead of JA / SQLAP / AR / JE / SQLGL).

LEVEL CODE
  Builds before 21-Jun-2026 use single letters; from that build on the import
  decodes only the full words and SILENTLY SKIPS anything else. Gathered packages produced before
  the converter fix carry 'G'. Legacy mapping:
      S->Site  A->Application  G->Request Group  R->Responsibility
      U->User  F->Form         D->Report         (Function has no legacy letter)

USAGE
  python verify_assignments.py --gather <CONV_folder> --built <folder-with-built-XMLs> \
                               --map map.json [--level "Request Group"]

  map.json maps each built XML file to its source program(s). The deliverable is ONE category file,
  so the normal shape maps that file to {"<Blitz report name>": "<PROG>", ...} and the gate checks each
  report's rows separately; a plain string value (one XML per program) is still accepted:
      {"reports_Custom_Reports_ABC_with_assignments_v01.xml": {"ABC Item Extract": "XX_ITEM_EXTRACT",
                                                              "ABC Asset Detail": "XX_ASSET_DETAIL"}}

Exit code 0 = gate passed, 1 = gate failed.
"""
import argparse
import html
import json
import os
import re
import sys

LEGACY = {'S': 'Site', 'A': 'Application', 'G': 'Request Group', 'R': 'Responsibility',
          'U': 'User', 'F': 'Form', 'D': 'Report'}


def rows(path, report_name=None):
    """(level_value, application_short_name, assignment_level) per ASSIGNMENTS_ROW, optionally of one report"""
    x = open(path, encoding='utf-8').read()
    out = []
    for m in re.finditer(r'<ASSIGNMENTS_ROW>(.*?)</ASSIGNMENTS_ROW>', x, re.S):
        r = m.group(1)
        rn = re.search(r'<REPORT_NAME>(.*?)</REPORT_NAME>', r, re.S)
        if report_name is not None and (not rn or html.unescape(rn.group(1)) != report_name):
            continue
        lv = re.search(r'<LEVEL_VALUE>(.*?)</LEVEL_VALUE>', r, re.S)
        ap = re.search(r'<APPLICATION_SHORT_NAME>(.*?)</APPLICATION_SHORT_NAME>', r, re.S)
        al = re.search(r'<ASSIGNMENT_LEVEL>(.*?)</ASSIGNMENT_LEVEL>', r, re.S)
        out.append((html.unescape(lv.group(1)) if lv else '',
                    ap.group(1) if ap else '',
                    al.group(1) if al else ''))
    return out


def normalise(level):
    return LEGACY.get(level, level)


def main():
    p = argparse.ArgumentParser()
    p.add_argument('--gather', required=True, help='extracted CONV_<ABBR>_... folder')
    p.add_argument('--built', required=True, help='folder holding the built report XMLs')
    p.add_argument('--map', required=True, help='JSON: {"<built xml>": "<PROG>"}')
    p.add_argument('--level', default='Request Group',
                   help='level code the TARGET instance expects (default: full words)')
    a = p.parse_args()

    mapping = json.load(open(a.map, encoding='utf-8'))
    fail = 0
    checks = []
    for xml in sorted(mapping):
        if isinstance(mapping[xml], dict):
            checks += [(xml, name, prog) for name, prog in sorted(mapping[xml].items())]
        else:
            checks.append((xml, None, mapping[xml]))
    for xml, name, prog in checks:
        label = (name or xml)[:50]
        bpath = os.path.join(a.built, xml)
        gpath = os.path.join(a.gather, prog, prog + '_assignments.xml')
        if not os.path.exists(gpath):
            print('%-52s NO GATHERED FILE (%s)' % (label, gpath))
            fail += 1
            continue
        built, gathered = rows(bpath, name), rows(gpath)
        b = {(v, ap) for v, ap, _ in built}
        g = {(v, ap) for v, ap, _ in gathered}
        bad_level = {l for _, _, l in built} - {a.level}
        ok = not (g - b) and not (b - g) and not bad_level
        print('%-52s %2d/%2d  %s' % (label, len(built), len(gathered), 'OK' if ok else 'FAIL'))
        for v, ap in sorted(g - b):
            print('      MISSING from built XML : %s @ %s' % (v, ap)); fail += 1
        for v, ap in sorted(b - g):
            print('      NOT IN gathered file   : %s @ %s' % (v, ap)); fail += 1
        for l in sorted(bad_level):
            hint = ' (legacy for %s - normalise it)' % normalise(l) if l in LEGACY else ''
            print('      WRONG level code       : %s, expected %s%s' % (l, a.level, hint)); fail += 1

    print()
    print('ASSIGNMENTS GATE PASSED' if not fail else 'ASSIGNMENTS GATE FAILED (%d problems)' % fail)
    return 1 if fail else 0


if __name__ == '__main__':
    sys.exit(main())

"""Print the value-set definitions from converter *_vs_*.ldt files (table, id/value columns, where clause)."""
import glob,re,sys
def attr(t,k):
    i=t.find(k+" =")
    if i<0: return None
    j=t.index('"',i)+1; out=[]
    while True:
        c=t[j]
        if c=="\\": out.append(t[j+1]); j+=2; continue
        if c=='"': break
        out.append(c); j+=1
    return "".join(out).replace("\n","\n")
for f in sorted(glob.glob("*vs_*.ldt")):
    t=open(f,encoding="utf-8",errors="replace").read()
    print("==",re.search(r'BEGIN VALUE_SET "([^"]+)"',t).group(1),"validation",attr(t,"VALIDATION_TYPE"))
    for k in ("TABLE_NAME","ID_COLUMN_NAME","VALUE_COLUMN_NAME","MEANING_COLUMN_NAME","ADDITIONAL_WHERE_CLAUSE"):
        v=attr(t,k)
        if v: print("   ",k,"=",re.sub(r"\s+"," ",v)[:900])