#!/usr/bin/env python3
"""Move Purview data quality rules to code: one CSV of rules, run as SQL in DuckDB, with a score per rule.

Microsoft Purview Data Quality offers eight rule types (Microsoft Learn, "Create Data Quality Rules in Unified Catalog", read 2026-11-21):
freshness, unique values, string format match, data type match, duplicate rows, empty/blank fields, table lookup
and custom. This script reads your rules from a CSV, writes each rule as SQL (rules.sql), runs every rule on your
data and scores it the way that page describes, then checks the rules themselves.

    python3 rules_as_code.py --rules rules.csv --data <folder of CSV or Parquet files> [--as-of 2026-11-21] [--out <folder>]
    python3 rules_as_code.py --make-demo <folder> [--plant-errors]

rules.csv columns: rule_id, table, column, rule_type, parameter, threshold
  rule_type  parameter
  freshness  <days>                      the newest value in the column must be at most <days> old on --as-of
  unique     (empty)                     every value appears once
  format     enum:a,b,c | like:<pattern> | regex:<pattern>
  datatype   integer | decimal | date | timestamp | boolean
  duplicate_rows  col1;col2;...          no two rows share the values in these columns ("column" is ignored)
  empty      (empty)                     no null or blank value
  lookup     <table>.<column>            every value is in that column of that table
  custom     <SQL condition on the row>  the condition is true
  threshold  the lowest score that passes, 0 to 100
As Microsoft's page describes, unique and format ignore null and blank values unless the same column also has an
empty rule; freshness scores 100 or 0. Format, lookup, unique, empty and data type rules test the text as it is in a
CSV file. A rule passes when its unrounded score reaches the threshold. With --fail-on-findings the script exits with
status 1 when any rule could not be scored or scores below its threshold, so a scheduled job can stop on it.
Guide: https://www.datameaning.com/guides/purview-data-quality-rules-as-code/
Needs Python 3.9+ and duckdb (pip install duckdb).
"""
import argparse
import csv
import datetime as dt
import os
import pathlib
import random
import sys

import duckdb

TYPES = {"freshness", "unique", "format", "datatype", "duplicate_rows", "empty", "lookup", "custom"}
CASTS = {"integer": "BIGINT", "decimal": "DOUBLE", "date": "DATE", "timestamp": "TIMESTAMP", "boolean": "BOOLEAN"}
CHECKS = [
    ("UNKNOWN_RULE_TYPE", "Every rule has one of the eight rule types"),
    ("UNKNOWN_TABLE", "Every rule names a table in the data folder"),
    ("UNKNOWN_COLUMN", "Every rule names columns the table holds"),
    ("BAD_PARAMETER", "Every rule's parameter can be read"),
    ("CHECKS_NOTHING", "Every rule checks at least one row"),
]


def q(name):
    return '"' + name.replace('"', '""') + '"'


def lit(s):
    return "'" + s.replace("'", "''") + "'"


def stop(msg):
    print(f"CANNOT CONTINUE: {msg}")
    sys.exit(2)


def enum_values(text):
    """Comma-separated values; a backslash escapes a comma or a backslash, as Microsoft's page describes."""
    out, cur, i = [], "", 0
    while i < len(text):
        ch = text[i]
        if ch == "\\" and i + 1 < len(text) and text[i + 1] in ",\\":
            cur += text[i + 1]
            i += 2
            continue
        if ch == ",":
            out.append(cur.strip())
            cur = ""
        else:
            cur += ch
        i += 1
    out.append(cur.strip())
    return [v for v in out if v != ""]


def rule_sql(r, empty_cols, as_of, con):
    """(sql that returns checked, passed) for one rule, or raises ValueError for a parameter it cannot read.
    Table t holds the typed values; txt.t holds the text as written in a CSV file."""
    t, c, kind, p = q(r["table"]), q(r["column"]), r["rule_type"], (r["parameter"] or "").strip()
    tx = "txt." + t
    present = f"{c} IS NOT NULL AND trim(CAST({c} AS VARCHAR)) <> ''"
    keep_blank = (r["table"], r["column"]) in empty_cols
    scope = "TRUE" if keep_blank else present
    if kind == "empty":
        return f"SELECT count(*), count(*) FILTER (WHERE {present}) FROM {tx}"
    if kind == "unique":
        return (f"SELECT count(*), count(*) FILTER (WHERE x IS NOT NULL AND n = 1) FROM "
                f"(SELECT {c} AS x, count(*) OVER (PARTITION BY {c}) AS n FROM {tx} WHERE {scope})")
    if kind == "format":
        how, _, pat = p.partition(":")
        if how == "enum":
            vals = enum_values(pat)
            if not vals:
                raise ValueError("enum: needs at least one value")
            cond = f"CAST({c} AS VARCHAR) IN ({', '.join(lit(v) for v in vals)})"
        elif how == "like":
            if not pat:
                raise ValueError("like: needs a pattern")
            cond = f"CAST({c} AS VARCHAR) LIKE {lit(pat)} ESCAPE '\\'"
        elif how == "regex":
            try:   # checked by DuckDB's own regular expression engine, which runs the rule
                con.execute(f"SELECT regexp_full_match('', {lit(pat)})").fetchone()
            except duckdb.Error as e:
                raise ValueError(f"regex: {str(e).splitlines()[0]}")
            cond = f"regexp_full_match(CAST({c} AS VARCHAR), {lit(pat)})"
        else:
            raise ValueError("format needs enum:, like: or regex:")
        return f"SELECT count(*), count(*) FILTER (WHERE coalesce({cond}, FALSE)) FROM {tx} WHERE {scope}"
    if kind == "datatype":
        if p not in CASTS:
            raise ValueError(f"datatype must be one of {', '.join(CASTS)}")
        text = f"trim(CAST({c} AS VARCHAR))"
        ok = (f"regexp_full_match({text}, '[+-]?[0-9]+')" if p == "integer"
              else f"TRY_CAST({text} AS {CASTS[p]}) IS NOT NULL")
        return f"SELECT count(*), count(*) FILTER (WHERE {ok}) FROM {tx} WHERE {present}"
    if kind == "duplicate_rows":
        cols = [x.strip() for x in p.split(";") if x.strip()]
        if len(cols) < 2:
            raise ValueError("duplicate_rows needs two or more columns, separated by ;")
        key = ", ".join(q(x) for x in cols)
        return (f"SELECT count(*), count(*) FILTER (WHERE n = 1) FROM "
                f"(SELECT count(*) OVER (PARTITION BY {key}) AS n FROM {t})")
    if kind == "lookup":
        if "." not in p:
            raise ValueError("lookup needs <table>.<column>")
        lt, lc = p.rsplit(".", 1)
        return (f"SELECT count(*), count(*) FILTER (WHERE CAST({c} AS VARCHAR) IN "
                f"(SELECT CAST({q(lc)} AS VARCHAR) FROM txt.{q(lt)} WHERE {q(lc)} IS NOT NULL)) FROM {tx} WHERE {scope}")
    if kind == "freshness":
        if not p.isdigit():
            raise ValueError("freshness needs a whole number of days")
        return (f"SELECT 1, CASE WHEN max(TRY_CAST(CAST({c} AS VARCHAR) AS TIMESTAMP)) >= "
                f"TIMESTAMP {lit(as_of)} - INTERVAL {int(p)} DAY THEN 1 ELSE 0 END FROM {t} HAVING count({c}) > 0")
    if kind == "custom":
        if not p or ";" in p:
            raise ValueError("custom needs one SQL condition, with no ;")
        return f"SELECT count(*), count(*) FILTER (WHERE coalesce(({p}), FALSE)) FROM {t}"
    raise ValueError("unknown rule type")


def run(rules_path, data, as_of, out, tag=""):
    data = pathlib.Path(data)
    if not data.is_dir():
        stop(f"{data} is not a folder")
    try:
        with open(rules_path, newline="", encoding="utf-8-sig") as fh:
            rd = csv.DictReader(fh)
            need = ["rule_id", "table", "column", "rule_type", "parameter", "threshold"]
            if not set(need) <= set(rd.fieldnames or []):
                stop(f"{rules_path} needs the columns {', '.join(need)}")
            rules = [{k: (v or "").strip() for k, v in row.items() if k} for row in rd]
    except OSError as e:
        stop(f"{rules_path} is not readable ({e})")
    con = duckdb.connect()
    con.execute("CREATE SCHEMA txt")
    tables = {}
    for f in sorted(data.iterdir()):
        if f.suffix.lower() in (".csv", ".parquet"):
            csv_ = f.suffix.lower() == ".csv"
            reader = "read_csv(?, header = true, sample_size = -1)" if csv_ else "read_parquet(?)"
            con.execute(f"CREATE TABLE {q(f.stem)} AS SELECT * FROM {reader}", [str(f)])
            text = "read_csv(?, header = true, all_varchar = true)" if csv_ else "read_parquet(?)"
            con.execute(f"CREATE TABLE txt.{q(f.stem)} AS SELECT * FROM {text}", [str(f)])
            tables[f.stem] = [r[0] for r in con.execute(f"DESCRIBE {q(f.stem)}").fetchall()]
    print(f"Data: {len(tables)} tables in {data}: " + ", ".join(f"{t} ({con.execute(f'SELECT count(*) FROM {q(t)}').fetchone()[0]} rows)" for t in tables))
    print(f"Rules: {len(rules)} in {rules_path}; scored as of {as_of}")
    empty_cols = {(r["table"], r["column"]) for r in rules if r["rule_type"].lower() == "empty"}
    findings, scores, sql_text = [], [], []
    for r in rules:
        r["rule_type"] = r["rule_type"].lower()
        rid = r["rule_id"] or "(no id)"
        if r["rule_type"] not in TYPES:
            findings.append(("UNKNOWN_RULE_TYPE", rid, f"rule type {r['rule_type']!r} is not one of the eight"))
            continue
        if r["table"] not in tables:
            findings.append(("UNKNOWN_TABLE", rid, f"no table {r['table']!r} in the data folder"))
            continue
        cols = [r["column"]] if r["rule_type"] not in ("duplicate_rows", "custom") else []
        if r["rule_type"] == "duplicate_rows":
            cols = [x.strip() for x in r["parameter"].split(";") if x.strip()]
        if r["rule_type"] == "lookup" and "." in r["parameter"]:
            lt, lc = r["parameter"].rsplit(".", 1)
            if lt not in tables or lc not in tables[lt]:
                findings.append(("UNKNOWN_COLUMN", rid, f"lookup target {r['parameter']!r} is not a column in the data"))
                continue
        missing = [x for x in cols if x not in tables[r["table"]]]
        if missing:
            findings.append(("UNKNOWN_COLUMN", rid, f"table {r['table']} has no column {', '.join(missing)}"))
            continue
        try:
            thr = float(r["threshold"])
            if not 0 <= thr <= 100:
                raise ValueError
        except ValueError:
            findings.append(("BAD_PARAMETER", rid, f"threshold {r['threshold']!r} is not a number from 0 to 100"))
            continue
        try:
            sql = rule_sql(r, empty_cols, as_of, con)
            res = con.execute(sql).fetchone()
        except ValueError as e:
            findings.append(("BAD_PARAMETER", rid, str(e)))
            continue
        except duckdb.Error as e:
            findings.append(("BAD_PARAMETER", rid, f"the rule's SQL failed: {str(e).splitlines()[0]}"))
            continue
        sql_text.append(f"-- {rid}: {r['rule_type']} on {r['table']}.{r['column']} {r['parameter']}\n{sql};\n")
        checked, passed = (res or (0, 0))
        if not checked:
            findings.append(("CHECKS_NOTHING", rid, "the rule checked no row, so its score means nothing"))
            continue
        score = round(100.0 * passed / checked, 1)
        scores.append((rid, r["table"], r["column"] if r["rule_type"] != "duplicate_rows" else r["parameter"],
                       r["rule_type"], checked, passed, checked - passed, score, thr,
                       "PASS" if 100.0 * passed >= thr * checked else "FAIL"))

    print()
    print(f"  {'Rule':<8} {'Type':<15} {'Table.column':<34} {'Checked':>8} {'Failed':>7} {'Score':>6} {'Needs':>6}  Result")
    for rid, t, c, k, n, ok, bad, sc, thr, res in scores:
        print(f"  {rid:<8} {k:<15} {(t + '.' + (c or '(row)'))[:34]:<34} {n:>8} {bad:>7} {sc:>6} {thr:>6g}  {res}")
    print()
    print("Checks on the rules")
    for code, text in CHECKS:
        n = sum(1 for f in findings if f[0] == code)
        print(f"  {'PASS' if n == 0 else 'FAIL'}  {text}" + (f"  ({n})" if n else ""))
    for code, rid, detail in findings:
        print(f"  {code:<18} {rid}: {detail}")

    out = pathlib.Path(out)
    out.mkdir(parents=True, exist_ok=True)
    suffix = f"-{tag}" if tag else ""
    with open(out / f"scores{suffix}.csv", "w", newline="", encoding="utf-8") as fh:
        w = csv.writer(fh)
        w.writerow(["rule_id", "table", "column", "rule_type", "rows_checked", "passed", "failed", "score", "threshold", "result"])
        w.writerows(scores)
    (out / f"rules{suffix}.sql").write_text("-- Generated by rules_as_code.py from " + pathlib.Path(rules_path).name +
                                          f"; scored as of {as_of}\n\n" + "\n".join(sql_text))
    try:
        where = os.path.relpath(out)
    except ValueError:
        where = str(out)
    failing = sum(1 for s in scores if s[-1] == "FAIL")
    print()
    print(f"Wrote scores{suffix}.csv ({len(scores)} rows) and rules{suffix}.sql ({len(sql_text)} rules) to {where}")
    print(f"RESULT: {len(scores)} rules scored, {failing} below their threshold; {len(findings)} rules could not be scored.")
    return len(findings) + failing


# ---------------------------------------------------------------------------------------------
def make_demo(folder, plant):
    rng = random.Random(275)
    folder = pathlib.Path(folder)
    (folder / "data").mkdir(parents=True, exist_ok=True)
    regions = ["North", "South", "East", "West"]
    with open(folder / "data" / "customers.csv", "w", newline="") as fh:
        w = csv.writer(fh)
        w.writerow(["customer_id", "email", "region", "segment", "updated_at"])
        for i in range(1, 201):
            email = f"customer{i}@example.com" if i % 25 else ""
            if i == 77:
                email = "customer77-at-example.com"
            w.writerow([i, email, rng.choice(regions) if i != 120 else "Central", rng.choice(["SMB", "Enterprise"]),
                        f"2026-11-{1 + i % 20:02d} 08:00:00"])
    with open(folder / "data" / "orders.csv", "w", newline="") as fh:
        w = csv.writer(fh)
        w.writerow(["order_id", "customer_id", "order_date", "amount", "currency"])
        for i in range(1, 501):
            cust = rng.randint(1, 200) if i != 333 else 999
            amount = f"{rng.randint(100, 99999) / 100:.2f}" if i != 444 else "12,50"
            w.writerow([1000 + i if i != 250 else 1249, cust, f"2026-10-{1 + i % 28:02d}", amount, "USD"])
    rules = [
        ("R01", "customers", "customer_id", "unique", "", 100),
        ("R02", "customers", "email", "empty", "", 95),
        ("R03", "customers", "email", "format", r"regex:[^@\s]+@[^@\s]+\.[a-z]+", 99),
        ("R04", "customers", "region", "format", "enum:North,South,East,West", 99),
        ("R05", "customers", "updated_at", "freshness", "30", 100),
        ("R06", "orders", "order_id", "unique", "", 100),
        ("R07", "orders", "customer_id", "lookup", "customers.customer_id", 100),
        ("R08", "orders", "amount", "datatype", "decimal", 100),
        ("R09", "orders", "currency", "format", "like:U_D", 100),
        ("R10", "orders", "", "duplicate_rows", "customer_id;order_date;amount", 100),
        ("R11", "orders", "", "custom", "TRY_CAST(amount AS DOUBLE) > 0", 99),
    ]
    planted = []
    if plant:
        rules += [("R12", "orders", "amount", "range", "0-1000", 99),               # UNKNOWN_RULE_TYPE
                  ("R13", "invoices", "invoice_id", "unique", "", 100),           # UNKNOWN_TABLE
                  ("R14", "customers", "phone", "empty", "", 90),                 # UNKNOWN_COLUMN
                  ("R15", "orders", "currency", "format", "regex:[A-Z{3}", 100)]   # BAD_PARAMETER
        planted = ["UNKNOWN_RULE_TYPE", "UNKNOWN_TABLE", "UNKNOWN_COLUMN", "BAD_PARAMETER"]
        # CHECKS_NOTHING: a column that is empty in every row
        with open(folder / "data" / "notes.csv", "w", newline="") as fh:
            w = csv.writer(fh)
            w.writerow(["note_id", "comment"])
            for i in range(1, 11):
                w.writerow([i, ""])
        rules.append(("R16", "notes", "comment", "format", "enum:ok", 100))
        planted.append("CHECKS_NOTHING")
    with open(folder / "rules.csv", "w", newline="") as fh:
        w = csv.writer(fh)
        w.writerow(["rule_id", "table", "column", "rule_type", "parameter", "threshold"])
        w.writerows(rules)
    print(f"Wrote generated data to {folder}/data (customers 200 rows, orders 500 rows) and {len(rules)} rules to "
          f"{folder}/rules.csv (seed 275)")
    if plant:
        assert set(planted) == {c for c, _ in CHECKS}, planted
        print("Planted one error for each check on the rules: " + ", ".join(sorted(planted)))


def main():
    ap = argparse.ArgumentParser(description="Run Purview-style data quality rules as SQL in DuckDB.")
    ap.add_argument("--rules")
    ap.add_argument("--data")
    ap.add_argument("--as-of", default=dt.date.today().isoformat(), help="the date freshness is measured on (default today)")
    ap.add_argument("--out", default=str(pathlib.Path(__file__).resolve().parent))
    ap.add_argument("--make-demo", metavar="FOLDER")
    ap.add_argument("--plant-errors", action="store_true")
    ap.add_argument("--tag", default="")
    ap.add_argument("--fail-on-findings", action="store_true",
                    help="exit with status 1 when a rule cannot be scored or scores below its threshold")
    a = ap.parse_args()
    if a.make_demo:
        make_demo(a.make_demo, a.plant_errors)
        return 0
    if not (a.rules and a.data):
        ap.error("give --rules and --data, or --make-demo FOLDER")
    try:
        dt.date.fromisoformat(a.as_of)
    except ValueError:
        ap.error("--as-of must be a date like 2026-11-21")
    print(f"Python {sys.version.split()[0]} duckdb {duckdb.__version__}")
    problems = run(a.rules, a.data, a.as_of, a.out, a.tag)
    return 1 if a.fail_on_findings and problems else 0


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