#!/usr/bin/env python3
"""
Uppercase beVault export technical names.

Runs three processes in order:
  1. Staging table names (TargetTableName, View queries, .tab filenames)
  2. DataVault entity names (hubs/links/sats/pits, folders, filenames)
  3. Information mart script Code (schemas, tables, technical columns)

Usage:
  python uppercase_bevault.py --root /path/to/bevault-export
"""

from __future__ import annotations

import argparse
import re
import sys
import uuid
from pathlib import Path


# ---------------------------------------------------------------------------
# Helpers
# ---------------------------------------------------------------------------

def read_text(path: Path) -> str:
    return path.read_text(encoding="utf-8")


def write_text(path: Path, content: str) -> None:
    path.write_text(content, encoding="utf-8", newline="\n")


def case_rename(path: Path, new_name: str) -> Path:
    """Rename a file/dir, including case-only changes on Windows."""
    target = path.with_name(new_name)
    if path.name == new_name:
        return path
    if path.resolve() == target.resolve() and path.name != new_name:
        # Case-only rename: go via a temporary name
        tmp = path.with_name(f"__tmp_{uuid.uuid4().hex}__{new_name}")
        path.rename(tmp)
        tmp.rename(target)
        print(f"  renamed {path.name} -> {new_name}")
        return target
    path.rename(target)
    print(f"  renamed {path.name} -> {new_name}")
    return target


def set_attr(content: str, key: str, value: str) -> str:
    """Set a top-level Key=Value line (first exact match of ^Key=)."""
    pattern = re.compile(rf"^{re.escape(key)}=.*$", re.MULTILINE)
    if not pattern.search(content):
        return content
    return pattern.sub(lambda _: f"{key}={value}", content, count=1)


def get_attr(content: str, key: str) -> str | None:
    m = re.search(rf"^{re.escape(key)}=(.*)$", content, re.MULTILINE)
    return m.group(1) if m else None


QUERY_BLOCK_RE = re.compile(r"^Query=(.*?)(?=^QueryType=)", re.MULTILINE | re.DOTALL)


def get_query(content: str) -> str | None:
    """Get a Query value, including any physical continuation lines."""
    match = QUERY_BLOCK_RE.search(content)
    return match.group(1).rstrip("\r\n") if match else None


def set_query(content: str, query: str) -> str:
    """Store Query on one physical line, using literal \n separators."""
    query = query.replace("\r\n", "\n").replace("\r", "\n")
    query = query.replace("\n", r"\n")
    return QUERY_BLOCK_RE.sub(lambda _: f"Query={query}\n", content, count=1)


# ---------------------------------------------------------------------------
# Process 1 — Staging tables
# ---------------------------------------------------------------------------

def process_staging(root: Path) -> dict[str, str]:
    print("\n=== Process 1: Uppercase staging tables ===")
    staging_root = root / "Staging" / "Systems"
    if not staging_root.is_dir():
        print(f"  skip: {staging_root} not found")
        return {}

    tab_files = sorted(staging_root.rglob("*.tab"))
    rename_map: dict[str, str] = {}

    # Pass A: collect TargetTableName map + update attribute
    for path in tab_files:
        content = read_text(path)
        old = get_attr(content, "TargetTableName")
        if not old:
            print(f"  warn: no TargetTableName in {path}")
            continue
        new = old.upper()
        rename_map[old] = new
        # also map by current filename stem
        rename_map.setdefault(path.stem, path.stem.upper())

        if old != new:
            content = set_attr(content, "TargetTableName", new)
            write_text(path, content)
            print(f"  updated TargetTableName in {path.name}: {old} -> {new}")

    # Pass B: rewrite View queries (stg -> STG, table refs)
    # Sort longest names first to avoid partial replacements
    names_by_len = sorted(set(rename_map.keys()), key=len, reverse=True)

    for path in tab_files:
        content = read_text(path)

        qtype = get_attr(content, "QueryType")
        if qtype != "View":
            continue

        query = get_query(content)
        if query is None:
            continue

        new_query = query

        # Schema stg / "stg" / 'stg' -> STG (preserve quotes)
        new_query = re.sub(r'(?i)"stg"', '"STG"', new_query)
        new_query = re.sub(r"(?i)'stg'", "'STG'", new_query)
        new_query = re.sub(r"(?i)\bstg\b", "STG", new_query)

        for old_name in names_by_len:
            new_name = rename_map[old_name]
            if old_name == new_name:
                continue
            # quoted identifiers
            new_query = re.sub(
                rf'"{re.escape(old_name)}"',
                f'"{new_name}"',
                new_query,
                flags=re.IGNORECASE,
            )
            new_query = re.sub(
                rf"'{re.escape(old_name)}'",
                f"'{new_name}'",
                new_query,
                flags=re.IGNORECASE,
            )
            # STG.table or schema.table (unquoted)
            new_query = re.sub(
                rf"(?i)(\bSTG\s*\.\s*){re.escape(old_name)}\b",
                rf"\1{new_name}",
                new_query,
            )

        serialized_query = new_query.replace("\r\n", "\n").replace("\r", "\n")
        serialized_query = serialized_query.replace("\n", r"\n")
        if new_query != query or serialized_query != query:
            content = set_query(content, new_query)
            write_text(path, content)
            print(f"  updated Query in {path.name}")

    # Pass C: rename files to TargetTableName.tab
    for path in list(tab_files):
        if not path.exists():
            continue
        content = read_text(path)
        tt = get_attr(content, "TargetTableName")
        if not tt:
            tt = path.stem.upper()
        new_filename = f"{tt}.tab"
        if path.name != new_filename:
            case_rename(path, new_filename)

    return rename_map


# ---------------------------------------------------------------------------
# Process 2 — DataVault entities
# ---------------------------------------------------------------------------

def process_datavault(root: Path) -> None:
    print("\n=== Process 2: Uppercase DataVault entities ===")
    dv = root / "DataVault"
    hubs_dir = dv / "Hubs"
    links_dir = dv / "Links"

    entity_files: list[Path] = []
    if hubs_dir.is_dir():
        entity_files.extend(hubs_dir.glob("*.hub"))
    if links_dir.is_dir():
        entity_files.extend(links_dir.glob("*.link"))

    # A. Update Name in hub/link files
    for path in sorted(entity_files):
        content = read_text(path)
        old = get_attr(content, "Name")
        if not old:
            continue
        new = old.upper()
        if old != new:
            content = set_attr(content, "Name", new)
            write_text(path, content)
            print(f"  updated {path.name}: Name {old} -> {new}")

    # B. Update Name (and DisplayName for satellites) in satellites / pits
    sat_files: list[Path] = []
    for base in (hubs_dir, links_dir):
        if base.is_dir():
            sat_files.extend(base.rglob("*.sat"))
            sat_files.extend(base.rglob("*.pit"))

    for path in sorted(sat_files):
        content = read_text(path)
        original = content

        old_name = get_attr(content, "Name")
        if old_name:
            new_name = old_name.upper()
            if old_name != new_name:
                content = set_attr(content, "Name", new_name)
                print(f"  updated {path.name}: Name {old_name} -> {new_name}")

        if path.suffix.lower() == ".sat":
            old_display = get_attr(content, "DisplayName")
            if old_display:
                new_display = old_display.upper()
                if old_display != new_display:
                    content = set_attr(content, "DisplayName", new_display)
                    print(
                        f"  updated {path.name}: DisplayName {old_display} -> {new_display}"
                    )

        if content != original:
            write_text(path, content)

    # C. Rename .sat / .pit files
    for path in sorted(sat_files):
        if not path.exists():
            continue
        content = read_text(path)
        name = get_attr(content, "Name")
        if not name:
            name = path.stem.upper()
        new_filename = f"{name}{path.suffix}"
        if path.name != new_filename:
            case_rename(path, new_filename)

    # D. Rename satellite folders (parent entity folders)
    for base in (hubs_dir, links_dir):
        if not base.is_dir():
            continue
        for folder in sorted(p for p in base.iterdir() if p.is_dir()):
            new_name = folder.name.upper()
            if folder.name != new_name:
                case_rename(folder, new_name)

    # E. Rename hub/link files
    entity_files = []
    if hubs_dir.is_dir():
        entity_files.extend(hubs_dir.glob("*.hub"))
    if links_dir.is_dir():
        entity_files.extend(links_dir.glob("*.link"))

    for path in sorted(entity_files):
        content = read_text(path)
        name = get_attr(content, "Name")
        if not name:
            name = path.stem.upper()
        new_filename = f"{name}{path.suffix}"
        if path.name != new_filename:
            case_rename(path, new_filename)


# ---------------------------------------------------------------------------
# Process 3 — Information mart scripts
# ---------------------------------------------------------------------------

SCHEMAS = ("dv", "bk", "im", "meta", "ref", "src", "stg", "bv")

TECH_COLUMNS = (
    "bk",
    "hk",
    "load_dts",
    "src",
    "is_current",
    "load_end_dts",
    "start_dts",
    "end_dts",
)

# beVault physical object prefixes / patterns inside SQL
BV_OBJECT_RE = re.compile(
    r"""
    (?P<q>"?)                           # optional opening quote
    (?P<name>
        (?:v_)?(?:sh|sl|h|l)_[A-Za-z0-9_]+   # hubs/links/sats/views
        |
        [A-Za-z0-9_]+_effsat                 # effectivity sats
    )
    (?P=q)                              # matching closing quote if opened
    """,
    re.IGNORECASE | re.VERBOSE,
)


def uppercase_schemas_in_sql(sql: str) -> str:
    # "dv" / 'dv' / dv. / im."table"
    for schema in SCHEMAS:
        upper = schema.upper()
        sql = re.sub(rf'"{schema}"', f'"{upper}"', sql, flags=re.IGNORECASE)
        sql = re.sub(rf"'{schema}'", f"'{upper}'", sql, flags=re.IGNORECASE)
        # bare schema before a dot: im."dim" or dv.h_customer
        sql = re.sub(
            rf"(?i)\b{schema}\b(?=\s*\.)",
            upper,
            sql,
        )
    return sql


def uppercase_bv_objects_in_sql(sql: str) -> str:
    def repl(m: re.Match[str]) -> str:
        q = m.group("q") or ""
        name = m.group("name").upper()
        return f"{q}{name}{q}"

    return BV_OBJECT_RE.sub(repl, sql)


def uppercase_tech_columns_in_sql(sql: str) -> str:
    # quoted and bare technical columns
    for col in sorted(TECH_COLUMNS, key=len, reverse=True):
        upper = col.upper()
        sql = re.sub(rf'"{col}"', f'"{upper}"', sql, flags=re.IGNORECASE)
        sql = re.sub(rf"'{col}'", f"'{upper}'", sql, flags=re.IGNORECASE)
        # bare word (avoid matching inside longer identifiers like load_end_dts when doing load_dts — sorted by length)
        sql = re.sub(rf"(?i)\b{col}\b", upper, sql)

    # role hash keys: order_hk, customer_hk, ...
    def hk_repl(m: re.Match[str]) -> str:
        q = m.group(1) or ""
        return f"{q}{m.group(2).upper()}{q}"

    sql = re.sub(r'(?i)("?)(\b[a-z][a-z0-9]*_hk\b)\1', hk_repl, sql)
    return sql


def adapt_code(code: str) -> str:
    code = uppercase_schemas_in_sql(code)
    code = uppercase_bv_objects_in_sql(code)
    code = uppercase_tech_columns_in_sql(code)
    return code


def process_information_marts(root: Path) -> None:
    print("\n=== Process 3: Adapt information mart scripts ===")
    im_dir = root / "DataVault" / "InformationMarts"
    if not im_dir.is_dir():
        print(f"  skip: {im_dir} not found")
        return

    code_line_re = re.compile(
        r"^(ScriptList:[0-9a-fA-F-]+:Code=)(.*)$",
        re.MULTILINE,
    )

    for path in sorted(im_dir.glob("*.informationmart")):
        content = read_text(path)
        original = content

        schema = get_attr(content, "Schema")
        if schema and schema != schema.upper():
            content = set_attr(content, "Schema", schema.upper())

        def code_repl(m: re.Match[str]) -> str:
            prefix, code = m.group(1), m.group(2)
            return prefix + adapt_code(code)

        content = code_line_re.sub(code_repl, content)

        # Optional lineage metadata
        content = re.sub(
            r"(SourceColumnsList:[0-9a-fA-F-]+:EntityName=)([^\r\n]+)",
            lambda m: m.group(1) + m.group(2).upper(),
            content,
        )
        content = re.sub(
            r"(SourceColumnsList:[0-9a-fA-F-]+:ColumnName=)(bk|hk|load_dts|src|is_current|load_end_dts|start_dts|end_dts)\b",
            lambda m: m.group(1) + m.group(2).upper(),
            content,
            flags=re.IGNORECASE,
        )

        if content != original:
            write_text(path, content)
            print(f"  updated {path.name}")
        else:
            print(f"  no changes in {path.name}")


# ---------------------------------------------------------------------------
# Main
# ---------------------------------------------------------------------------

def main(argv: list[str] | None = None) -> int:
    parser = argparse.ArgumentParser(
        description="Uppercase beVault staging tables, DV entities, and IM scripts."
    )
    parser.add_argument(
        "--root",
        type=Path,
        default=Path("."),
        help="Path to the beVault export root (folder containing Staging/ and DataVault/).",
    )
    parser.add_argument(
        "--only",
        choices=("staging", "datavault", "im", "all"),
        default="all",
        help="Run a single process or all (default: all).",
    )
    args = parser.parse_args(argv)

    root = args.root.resolve()
    if not (root / "Staging").exists() and not (root / "DataVault").exists():
        print(f"ERROR: {root} does not look like a beVault export root.", file=sys.stderr)
        return 1

    print(f"Root: {root}")

    if args.only in ("staging", "all"):
        process_staging(root)
    if args.only in ("datavault", "all"):
        process_datavault(root)
    if args.only in ("im", "all"):
        process_information_marts(root)

    print("\nDone.")
    return 0


if __name__ == "__main__":
    raise SystemExit(main())